Parser doesn't work for a simple table

[BuonOmo]

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.

[rm-hull]

Hmm..

If I run python sql_graphviz.py simple.sql it returns the folllowing:

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

  "administrators" [
    shape=none
    label=<
      <table border="0" cellspacing="0" cellborder="1">
        <tr><td bgcolor="lightblue2"><font face="Times-bold" point-size="20">administrators</font></td></tr>
        <tr><td bgcolor="grey96" align="left" port="id"><font face="Times-bold">id</font>  <font color="#535353">uuid</font></td></tr>
        <tr><td bgcolor="grey96" align="left" port="created_at"><font face="Times-bold">created_at</font>  <font color="#535353">timestamp</font></td></tr>
        <tr><td bgcolor="grey96" align="left" port="updated_at"><font face="Times-bold">updated_at</font>  <font color="#535353">timestamp</font></td></tr>
        <tr><td bgcolor="grey96" align="left" port="authentication_token"><font face="Times-bold">authentication_token</font>  <font color="#535353">text</font></td></tr>
        <tr><td bgcolor="grey96" align="left" port="roles"><font face="Times-bold">roles</font>  <font color="#535353">text</font></td></tr>
      </table>
    >];
}

and converting it to an image:

So I am puzzled as to why it wouldn't work for you.

BTW ... I was using python 2 though.

[rm-hull]

I tried it in python 3.7.0 .. works there too.

[BuonOmo]

Well, maybe 3.6 doesn't work...

Also, how could you use python 2 with the print() syntax? It should not work

[duncan-bayne]

Broken for me, too - I generated a schema-only dump of a PostgreSQL database, ran it as suggested, and got just the following output:

/*
 * Graphviz of './schema.sql', created 2018-09-05 11:15:18.872147
 * Generated from https://github.com/rm-hull/sql_graphviz
 */
digraph g { graph [ rankdir = "LR" ];
}

Relevant versions of things:

• OSX 10.13.2
• pg_dump (PostgreSQL) 10.5
• Python 2.7.15

[rm-hull]

@duncan-bayne Could you post the postgres dump you were using please?
If it contains sensitive/proprietory info, let me know and I'll contact you via email

[brainlid]

I'm also unable to get any output in the dot file.

Here's the dump file contents:

--
-- 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;

--
-- Name: app_disclosure_templates; Type: TABLE; Schema: public
--

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
);


--
-- PostgreSQL database dump complete
--

Command used for testing:

python sql_graphviz.py test.sql > graph.dot

Dot file contents:

/*
 * Graphviz of '<open file '<stdin>', mode 'r' at 0x7f85c12820c0>', created 2019-02-08 13:25:02.680230
 * Generated from https://github.com/rm-hull/sql_graphviz
 */
digraph g { graph [ rankdir = "LR" ];
}

I'm on an Ubuntu-based system.

$ python --version
Python 2.7.15rc1

$ apt list python-pyparsing
Listing... Done
python-pyparsing/bionic,bionic,now 2.2.0+dfsg1-2 all [installed]

Looking forward to using it. :)

[rm-hull]

@brainlid - so it turns out there are two causes why it wasn't able to parse your SQL (not the fault of pg_dump of course, but sql_graphviz)

• It doesn't support tables with a schema prefix (e.g public.app_disclosure_templates) - it fails because it just expects a table name without a dot.
• The field-spec accepts limited characters, which doesn't include a forward slash, so the mime_type character varying(255) DEFAULT 'application/pdf'::character varying NOT NULL, line also failed to parse.

So I've fixed those small snags, and your sample sql now renders as follows:

I'll make a commit and push shortly...

[brainlid]

Awesome! Thanks!

[brainlid]

@rm-hull Your fix worked for my issue reported here. 👍