rm-hull/sql_graphviz

Generates graphviz commands to graphically display tables and show foreign key links.

SQL Graphviz

SQL Graphviz is a small python 3 script that generates a Graphviz visualization of a SQL schema dump.

Dependencies

SQL Graphviz relies on pyparsing to grok the schema dump. Ubuntu users should install using:

$ sudo apt-get install python3-pyparsing

while CheeseShop frequenters should install with pip:

$ sudo pip3 install pyparsing

Arch linux users can install the dependencies like this:

$ sudo pacman -S python-pyparsing

Alternatively, using pipenv as follows (ensure it is installed first):

$ pipenv install
$ pipenv shell

Usage

Using PostgreSQL, for example, to generate as a PNG file:

$ pg_dump --schema-only dbname | python sql_graphviz.py | dot -Tpng > graph.png

The program will accept a named file, or if omitted as above, will take from stdin. Output to SVG:

$ pg_dump --schema-only dbname > dump.sql
$ python sql_graphviz.py dump.sql > graph.dot
$ dot -Tsvg graph.dot > graph.svg

Example

Credits

Extended from http://energyblog.blogspot.co.uk/2006/04/blog-post_20.html by EnErGy [CSDX]

References

• http://pythonhosted.org/pyparsing/

The MIT License (MIT)

Copyright (c) 2014 Richard Hull & EnErGy [CSDX]

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

sql_graphviz's Issues

Support SQL Server

Hello,

Your project seems very nice for me.
Would be nice if the grammar would support SQL Server syntax as well.

Here's an example:

CREATE TABLE [DEBUG](
[row] [int] IDENTITY(1,1) NOT NULL,
[text] nvarchar NULL,
CONSTRAINT [PK_debug] PRIMARY KEY CLUSTERED
(
[row] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

CREATE TABLE [T_CLIENT_CLI](
[CLI_ID] [bigint] IDENTITY(1,1) NOT NULL,
[CLI_NAME] nvarchar NOT NULL,
[CLI_PARENT_ID] [bigint] NULL,
CONSTRAINT [PK_T_CLIENT_CLI] PRIMARY KEY CLUSTERED
(
[CLI_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [T_DASHBOARD_DAB](
[DAB_ID] [bigint] IDENTITY(1,1) NOT NULL,
[DAB_CLI_ID] [bigint] NULL,
[DAB_USR_ID] [bigint] NULL,
[DAB_DAS_ID] [bigint] NULL,
[DAB_REFERENCE] nvarchar NOT NULL,
[DAB_REFRESH] [int] NOT NULL,
[DAB_TRANSITION] [int] NOT NULL,
[DAB_DISPLAY_TYPE] [tinyint] NOT NULL,
[DAB_CONFIG] nvarchar NOT NULL,
CONSTRAINT [PK_T_DASHBOARD_DAC] PRIMARY KEY CLUSTERED
(
[DAB_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

ALTER TABLE [T_DASHBOARD_DAB] ADD CONSTRAINT [DF_T_DASHBOARD_DAB_DAB_REFRESH] DEFAULT ((1)) FOR [DAB_REFRESH]

ALTER TABLE [T_CLIENT_CLI] WITH CHECK ADD CONSTRAINT [FK_CLI_PARENT] FOREIGN KEY([CLI_PARENT_ID])
REFERENCES [T_CLIENT_CLI] ([CLI_ID])

ALTER TABLE [T_CLIENT_CLI] CHECK CONSTRAINT [FK_CLI_PARENT]

ALTER TABLE [T_DASHBOARD_DAB] WITH CHECK ADD CONSTRAINT [FK_DAB_CLI_ID] FOREIGN KEY([DAB_CLI_ID])
REFERENCES [T_CLIENT_CLI] ([CLI_ID])
ON DELETE CASCADE

ALTER TABLE [T_DASHBOARD_DAB] CHECK CONSTRAINT [FK_DAB_CLI_ID]

not working on mysqldump

Hi

I have the following SQL dump produced using mysqldump 5.1.73:
Command used:
mysqldump --add-drop-table -d --skip-lock-tables -h ${db_host} -P ${db_port} -u ${db_user} -p${db_pass} {$db_name}

This is the dump:

CREATE TABLE `active_directory` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `hostname` varchar(100) COLLATE utf8_bin NOT NULL,
  `path` varchar(200) COLLATE utf8_bin NOT NULL COMMENT 'human friendly path, ie. AA/BB/Computers',
  `domain` varchar(100) COLLATE utf8_bin NOT NULL COMMENT 'DNS FQDN, ie. domain.com',
  `dn` varchar(255) COLLATE utf8_bin NOT NULL COMMENT 'LDAP distinguished name, ie. CN=HOST,OU=Computers,OU=AA,OU=BB,DC=domain,DC=com',
  `os` varchar(100) COLLATE utf8_bin DEFAULT NULL COMMENT 'ie. Windows 7 Enterprise SP1',
  `last_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `hostname_fqdn` varchar(200) COLLATE utf8_bin DEFAULT NULL COMMENT 'for faster index without CONCAT',
  PRIMARY KEY (`id`),
  UNIQUE KEY `hostname_domain` (`hostname`,`domain`) USING BTREE,
  KEY `hostname_idx` (`hostname`),
  KEY `domain` (`domain`),
  KEY `hostname_fqdn_idx` (`hostname_fqdn`)
  CONSTRAINT `dns_servers_zones_ibfk_1` FOREIGN KEY (`domain`) REFERENCES `dns_zones` (`domain`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `dns_servers_zones_ibfk_2` FOREIGN KEY (`hostname`) REFERENCES `dns_servers` (`hostname`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4566624 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='computer objects from active directory';

CREATE TABLE `other_table` (
  `id2` int(11) NOT NULL AUTO_INCREMENT,
  `hostname2` varchar(100) COLLATE utf8_bin NOT NULL,
) ENGINE=InnoDB AUTO_INCREMENT=4566624 DEFAULT CHARSET=utf8 COLLATE=utf8_bin 

For that parser produces nothing (just the initial line: digraph g { graph [ rankdir = "LR" ];

So I came with this enhancement of table definition, which at least detects the tables properly (it accomodates for extra commands after the closing ) parenthesis for table definition:
create_table_def = Literal("CREATE") + "TABLE" + tablename_def.setResultsName("tableName") + "(" + field_list_def.setResultsName("fields") + ")" + ZeroOrMore (CharsNotIn(";")) + ";"

But for foreign keys it is still not detecting them.
I don't know pyparsing grammar too much, but from experiments, I was able to get this to detect a single foreign key:

mysql_fkey_def = Literal("CONSTRAINT") + Word(alphanums + "_`").setResultsName("tableName") + "FOREIGN" + "KEY" + "(" + Word(alphanums + "_`").setResultsName("keyName") + ")" + "REFERENCES" + Word(alphanums + "_`").setResultsName("fkTable") + "(" + Word(alphanums + "_`").setResultsName("fkCol") + ")" + Optional(Literal("ON") + "DELETE" + Word(alphanums) ) + Optional(Literal("ON") + "UPDATE" + Word(alphanums + ",") )

But I cannot integrate it into field definition, I thought of this which doesn't work - they are still detected as normal fields

field_def = mysql_fkey_def | OneOrMore(Word(alphanums + "_\"'`:-") | parenthesis)

Some problems with this:

1. sometimes there are more than 1 foreign key, as in my example above you have 2, but you can have zero as well
2. name of the table is not on the row where foreign key is defined. You would have to reach back somehow to the table name that was parsed (I guess it's doable for someone who knows pyparsing.py)
3. action for the printing the foreign key relation to other database need to be deferred until you completed parsing the table, so it is not part of the "label" section for GraphViz.

I don't know how to do it, that's why I'm posting it here, hoping that someone who knows could do it, based on my input and example MySQL dump.

Best Regards

Parser doesn't work for a simple table

CREATE TABLE administrators (
    id uuid,
    created_at timestamp,
    updated_at timestamp,
    authentication_token text,
    roles text
);

returns

/*
 * Graphviz of 'simple.sql', created 2018-08-10 10:51:10.505699
 * Generated from https://github.com/rm-hull/sql_graphviz
 */
digraph g { graph [ rankdir = "LR" ];
}

Command used is python sql_graphviz.py simple.sql, and my python version is Python 3.6.5 :: Anaconda, Inc.

Add support for postgress enum types

Add support for case insensitive literals

Hello, I've tried to convert the program to ignore case differences in keywords like create on a local copy, it seems to work fine, would you be interested in merging this feature? I've just changed the Literal objects to CaselessLitteral.

Foreign key not represented in the diagram

Related to #4?

The following SQL now correctly generates a .dot file and a .png. However, there are no relationships between the tables represented in the diagram.

I'm wondering if the schema name on the constraints is causing that?

--
-- PostgreSQL database dump
--

-- Dumped from database version 9.6.9
-- Dumped by pg_dump version 11.1 (Ubuntu 11.1-3.pgdg18.04+1)

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;

SET default_tablespace = '';

SET default_with_oids = false;

CREATE TABLE public.app_disclosure_templates (
    id bigint NOT NULL,
    inserted_at timestamp without time zone NOT NULL,
    updated_at timestamp without time zone NOT NULL,
    linked_template_id bigint,
    name character varying(255),
    kind character varying(255),
    de_simple_template_id character varying(255),
    library_id integer,
    shared boolean DEFAULT true,
    active boolean DEFAULT true,
    unpublished_changes boolean,
    processing_status character varying(255) DEFAULT 'complete'::character varying,
    legacy_id integer,
    jurisdiction_state character varying(255) DEFAULT 'ALL'::character varying,
    jurisdiction character varying(255),
    type character varying(255) DEFAULT 'Disclosure'::character varying NOT NULL,
    mime_type character varying(255) DEFAULT 'application/pdf'::character varying NOT NULL,
    signature_type character varying(255) DEFAULT 'name'::character varying,
    signature_text character varying(255),
    owner_name character varying(255),
    use_raw_editor boolean DEFAULT false,
    override_billing_plan boolean DEFAULT false,
    platform character varying(255) DEFAULT 'qlp'::character varying
);


CREATE SEQUENCE public.app_disclosure_templates_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER SEQUENCE public.app_disclosure_templates_id_seq OWNED BY public.app_disclosure_templates.id;


CREATE TABLE public.app_disclosures (
    id bigint NOT NULL,
    inserted_at timestamp without time zone NOT NULL,
    updated_at timestamp without time zone NOT NULL,
    app_disclosure_template_id bigint,
    person_id integer,
    signed boolean DEFAULT false,
    kind character varying(255),
    de_packet_id character varying(255),
    de_document_id character varying(255),
    completed boolean DEFAULT false,
    legacy_id integer,
    app_disclosure_template_content_id bigint,
    text_content text,
    type character varying(255),
    signature_ip character varying(255),
    signature_timestamp timestamp without time zone,
    pdf character varying(255)
);


CREATE SEQUENCE public.app_disclosures_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER SEQUENCE public.app_disclosures_id_seq OWNED BY public.app_disclosures.id;


ALTER TABLE ONLY public.app_disclosure_templates ALTER COLUMN id SET DEFAULT nextval('public.app_disclosure_templates_id_seq'::regclass);


ALTER TABLE ONLY public.app_disclosures ALTER COLUMN id SET DEFAULT nextval('public.app_disclosures_id_seq'::regclass);


ALTER TABLE ONLY public.app_disclosure_templates
    ADD CONSTRAINT app_disclosure_templates_pkey PRIMARY KEY (id);


ALTER TABLE ONLY public.app_disclosures
    ADD CONSTRAINT app_disclosures_pkey PRIMARY KEY (id);


ALTER TABLE ONLY public.app_disclosure_templates
    ADD CONSTRAINT app_disclosure_templates_linked_template_id_fkey FOREIGN KEY (linked_template_id) REFERENCES public.app_disclosure_templates(id) ON DELETE CASCADE;


ALTER TABLE ONLY public.app_disclosures
    ADD CONSTRAINT app_disclosures_app_disclosure_template_content_id_fkey FOREIGN KEY (app_disclosure_template_content_id) REFERENCES public.app_disclosure_template_content(id) ON DELETE RESTRICT;


ALTER TABLE ONLY public.app_disclosures
    ADD CONSTRAINT app_disclosures_app_disclosure_template_id_fkey FOREIGN KEY (app_disclosure_template_id) REFERENCES public.app_disclosure_templates(id) ON DELETE CASCADE;


--
-- PostgreSQL database dump complete
--

Support mysql

Mysql table and field names contain apostrophes:

CREATE TABLE `foo` (
  `id` int NOT NULL AUTO_INCREMENT,
  `bar` varchar(255) NOT_NULL,
  PRIMARY KEY (`id`)
);

Problem with escaping "<" or ">" (notably in constraints)

The output of this unfortunately doesn't correctly escape the angle-brackets, so if the schema has a constraint-check in it, it fails.

$ python3 sql_graphviz.py dump.sql  > graph.dot
$ dot -Tsvg graph.dot > graph.svg
Error: graph.dot: syntax error in line 1051 near ':'

This is a rather unhelpful message from dot, because that line 1051 is nowhere near the error!

Here is an example of the kind of line that sql_graphviz emits that is wrong:

<tr><td bgcolor="grey96" align="left" port="CONSTRAINT"><font face="Times-bold">CONSTRAINT</font>  <font color="#535353">location_users_spot_sanity CHECK (((role <> 'Location'::users_role_enum) OR ((manager_id IS NULL) AND (division_id IS NULL) AND (job_title IS NULL) AND (employee_key IS NULL) AND (ldap_key IS NULL) AND (provenance = 'Local'::users_provenance_enum))))</font></td></tr>

For now, a fixup for "<>", "<=" and ">=" can be done using sed:

$ python3 sql_graphviz.py dump.sql | sed -e 's/<>/\&lt;\&gt;/g' -e 's/<=/\&lt;=/g' -e 's/>=/\&gt;=/g' > graph.dot

Thanks very much - great tool now I can get it working!

Support mysql #2

Advertise this in Postgresql documentation

Would have saved me a few clicks to find sql_graphviz here: https://wiki.postgresql.org/wiki/Design_Tools#DbVisualizer_2

I don't have an Editor access to the pq wiki, perhaps someone here would ?

doesn't draw relationship arrows with output from pg_dump 9.6.10...

Unfortunately this seems not to draw relationship arrows with output from pg_dump 9.6.10...

pyparsing

Make a note in readme that pyparsing is required.

Table fails to correctly parse these default values and generate dot format

Here is another SQL table that fails to generate a table output.

I've narrowed it down to 2 problems.

1. "welcome_message" is a text field with multi-line text content (markdown format) for a column default value.
2. "steps" is a jsonb field type with a default value of '[]'.

Both columns are breaking the parsing.

--
-- PostgreSQL database dump
--


CREATE TABLE public.driver_forms (
    id uuid NOT NULL,
    name character varying(255) NOT NULL,
    platform character varying(255) NOT NULL,
    reseller_id integer,
    client_id integer,
    inserted_at timestamp without time zone NOT NULL,
    updated_at timestamp without time zone NOT NULL,
    online boolean DEFAULT true,
    slug character varying(255) NOT NULL,
    description character varying(255),
    active boolean DEFAULT true NOT NULL,
    welcome_message text DEFAULT '# Welcome!

Thank you for your interest.'::text,
    steps jsonb DEFAULT '[]'::jsonb,
    test_mode boolean DEFAULT false,
    confirmation_mode character varying(255) DEFAULT 'message'::character varying
);

--
-- PostgreSQL database dump complete
--

Turning this into a CLI app

Hey!

Really awesome tool out there. I was wondering if it's possible to package this into an executable that can be run from the command line.
One can make use typer.

I'm happy to work on this if you think it's a good thing to have. cc @rm-hull