A C# command line program to convert SQL DDL into nice DrawIO diagrams

SQL-DrawIO-README.md

SQL-DRAWIO

John Henckel, June 2019

This parses the SQL DDL for a database and generates a XML file that is suitable to IMPORT into the https://draw.io

This will only generate tables, you have to manually create the arrows between the tables.

INPUT FILE

The input must be a SQL DDL script that is delimited by slashes, for example...

create sequence SQ_MESSAGEID
    minvalue 0
/

create table JMSMESSAGE
(
    MESSAGEID NUMBER(12) not null
        constraint NX_MSG_X
            primary key,
    TIMETOLIVE NUMBER,
    DESTID NUMBER,
    STATE NUMBER(1),
    MESSAGE LONG RAW
)
/

create table JMSMESSAGEQUEUE
(
    CONSUMERID NUMBER,
    MESSAGEID NUMBER(12) not null
        constraint NX_MSGQ_X
            primary key,
    STATE NUMBER
)
/

one way to generate this is with DATA GRIP SQL GENERATOR with in-line constraints.

OUTPUT FILE

The output file uses the DRAWIO XML format, which can be imported directly into DRAWIO. The output will contain a single "mxCell" for each database table. The content of the mxCell is an HTML table. The output is text, so you can open it in NOTEPAD and edit it directly.

USAGE

This compiles to a command line program.

The command line parameters are: inputfile outputfile

If outputfile is missing, the output will be "result.xml"

The options are

• -v verbose
• -p pause at the end
• -c clobber the output (default merge changes, see note below)
• -h max height of table (in pixels)
• -w width of paper (in inches, default 24)

Don't put space after the h/w option, for example -h25 -w8 The paper height is always the width plus 30%.

Note: if you clobber, this will replace the entire output file. Otherwise, if you output to an existing file, this will preserve the layout and only update the HTML data inside each mxCell (or create new mxCells for new DB tables).

.

SQL-Drawio.cs

using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Linq.Expressions;
using System.Text.RegularExpressions;

/*
    SQL-DRAWIO                        John Henckel, June 2019

    This parses the SQL DDL for a database and generates a XML file that is 
    suitable to IMPORT into the https://draw.io
    This will only generate tables, you have to manually create the arrows between the tables.
    The input must be a SQL DDL script that is delimited by slashes, for example...

    ----------- begin input file ------------
    create sequence SQ_MESSAGEID
	    minvalue 0
    /

    create table JMSMESSAGE
    (
	    MESSAGEID NUMBER(12) not null
		    constraint NX_MSG_X
			    primary key,
	    TIMETOLIVE NUMBER,
	    DESTID NUMBER,
	    STATE NUMBER(1),
	    MESSAGE LONG RAW
    )
    /

    create table JMSMESSAGEQUEUE
    (
	    CONSUMERID NUMBER,
	    MESSAGEID NUMBER(12) not null
		    constraint NX_MSGQ_X
			    primary key,
	    STATE NUMBER
    )
    /
    ---------- end input file ----------

    one way to generate this is with DATA GRIP SQL GENERATOR.

 */
namespace ParseSQL
{
    class Column
    {
        public string name;
        public string type;
        public string title;
        public bool pk;
        public bool fk;
        public bool wk;
        public bool uniq;
        public bool index;
        public bool nullable;
        public bool seq;
        public bool def;

        public void SetWk(string s)
        {
            wk = true;
            title += ", wk=" + s;
        }
    }

    class Table
    {
        public string Name;
        public List<Column> Columns;
        public string Title;

        public Table(string n)
        {
            Name = n;
            Columns = new List<Column>();
            Title = "";
        }
    }

    //------------------------------------------------------------------------------------------
    // Main program (static entry point is at the bottom)

    class Program
    {
        private List<Table> Tables;
        private List<string> Sequences;
        private List<string> Cells;
        private StreamReader file;
        private bool verbose;
        private bool pause;
        private bool clobber;
        private int maxheight;
        private int papersize;
        private string inputFileName;
        private string outputFileName;

        //------------------------------------------------------------------------------------------
        Program()
        {
            Tables = new List<Table>();
            Sequences = new List<string>();
            outputFileName = "result.xml";
        }

        //------------------------------------------------------------------------------------------
        void ReadFile()
        {
            if (!File.Exists(inputFileName))
            {
                Console.WriteLine("File not found " + inputFileName);
                return;
            }

            using (file = new StreamReader(inputFileName))
            {
                string line;

                while ((line = file.ReadLine()) != null)
                {
                    if (line.StartsWith("create table"))
                    {
                        if (verbose) Console.WriteLine("T" + line.Substring(8) + "...");
                        ParseTable(line);
                    }
                    else if (line.StartsWith("create sequence"))
                    {
                        if (verbose) Console.WriteLine("S" + line.Substring(8) + "...");
                        var s = line.Split(' ');
                        if (s.Length > 2)
                            Sequences.Add(s[2]);
                        else
                            Console.WriteLine("ERROR invalid sequence " + line);
                    }
                    else if (line.StartsWith("create index"))
                    {
                        if (verbose) Console.WriteLine("I" + line.Substring(8) + "...");
                        if (!line.Contains("("))
                            line += " " + (file.ReadLine() ?? "").Trim();

                        var m = new Regex(@"\son\s*?(\S+)\s*\((.*?)\)").Match(line);
                        if (!m.Success)
                        {
                            Console.WriteLine("ERROR unable to parse " + line);
                        }

                        var t = Tables.Find(x => x.Name == m.Groups[1].Value.Trim());
                        if (t == null)
                        {
                            Console.WriteLine("ERROR unable to find table " + m.Groups[1].Value);
                            continue;
                        }

                        var fields = m.Groups[2].Value.Split(',');
                        foreach (var f in fields)
                            t.Columns.FindAll(x => x.name == f.Trim()).ForEach(x => x.index = true);
                    }

                    /*
                    else if(line.StartsWith("create view"))
                    {
                        if (verbose) Console.WriteLine("Parsing" + line.Substring(6) + "...");
                    }
                    else if (line.StartsWith("create PACKAGE"))
                    {
                        if (verbose) Console.WriteLine("Parsing" + line.Substring(6) + "...");
                    }
                    else if(line.StartsWith("create FUNCTION"))
                    {
                        if (verbose) Console.WriteLine("Parsing" + line.Substring(6) + "...");
                    }*/
                }

                file.Close();
            }
        }

        //------------------------------------------------------------------------------------------
        void ParseTable(string line)
        {
            var w = line.Split(' ');
            if (w.Length < 3)
            {
                Console.WriteLine("ERROR missing table name");
                return;
            }

            var t = new Table(w[2]);
            Tables.Add(t);
            var s = ReadBody().Trim();
            if (s.StartsWith("("))
            {
                s = s.Substring(1);
                if (s.EndsWith(")")) s = s.Substring(0, s.Length - 1);
            }

            var cols = s.Split(',');
            for (var i = 0; i < cols.Length; ++i)
            {
                var col = PopString(cols, ref i);
                w = col.Split(new[] { ' ' }, 3);
                if (w.Length < 2)
                {
                    Console.WriteLine("ERROR missing column name/type " + col);
                    continue;
                }

                if (w[0] == "constraint")
                {
                    ParseConstraint(t, col, w[2]);
                    continue;
                }

                var typ = w[1].ToLowerInvariant();
                typ = typ.Replace("varchar", "vchar");
                typ = typ.Replace("number", "num");
                var titl = w.Length > 2 ? w[2] : "";
                var c = new Column() { name = w[0], type = typ, title = titl};
                t.Columns.Add(c);
                c.fk = col.Contains("references");
                c.pk = col.Contains("primary key");
                c.nullable = !col.Contains("not null");
                c.def = col.Contains("default");
                if (verbose) Console.WriteLine("   |  " + col);
            }
        }

        //------------------------------------------------------------------------------------------
        // Remove string from list and extra strings if necessary to balance the parentheses.
        string PopString(string[] list, ref int i)
        {
            var s = list[i].Trim();
            if (s.Contains("("))
            {
                while (i < list.Length - 1)
                {
                    var n1 = s.Count(x => x == '(');
                    var n2 = s.Count(x => x == ')');
                    if (n1 <= n2) break;
                    s += "," + list[++i].Trim();
                }
            }
            return s;
        }

        //------------------------------------------------------------------------------------------
        // Parse a constraint that is found at the end of a table definition
        void ParseConstraint(Table t, string col, string cons)
        {
            t.Title += col;
            if (verbose) Console.WriteLine("   :  " + col);
            var m = new Regex(@"(\S.*?)\s+\((.*?)\)").Match(cons);
            if (!m.Success)
            {
                Console.WriteLine("ERROR unable to parse constraint " + cons);
                return;
            }
            var typ = m.Groups[1].Value.Trim();
            var fields = m.Groups[2].Value.Split(',');
            if (typ == "primary key")
            {
                foreach (var f in fields)
                    ForEachColumn(typ, t, f.Trim(), x => x.pk = true);
            }
            else if (typ == "foreign key")
            {
                foreach (var f in fields)
                    ForEachColumn(typ, t, f.Trim(), x => x.fk = true);
            }
            else if (typ == "unique")
            {
                foreach (var f in fields)
                    ForEachColumn(typ, t, f.Trim(), x => x.uniq = true);
            }
            else
            {
                Console.WriteLine("ERROR unknown constraint type " + typ);
            }
        }

        //------------------------------------------------------------------------------------------
        // Read the entire table definition into a single string
        string ReadBody()
        {
            string line;
            var result = "";
            while ((line = file.ReadLine()) != null)
            {
                line = line.Trim();
                if (line.Equals("/")) break;
                result += " " + line;
            }
            return result;
        }

        //------------------------------------------------------------------------------------------
        void ForEachColumn(string typ, Table table, string s, Action<Column> func)
        {
            var match = table.Columns.FindAll(x => x.name == s);
            if (match.Count == 0)
            {
                Console.WriteLine("ERROR "+table.Name+" "+typ+" unknown column "+s);
            }   
            match.ForEach(func);
        }


        //------------------------------------------------------------------------------------------
        // match sequence names to names of primary key fields.
        void MatchSequences()
        {
            var found = new List<Column>();
            foreach (var seq in Sequences)
            {
                var name = seq;
                if (name.StartsWith("ECHSQ_")) name = seq.Substring(6);
                found.Clear();
                foreach (var t in Tables)
                {
                    foreach (var c in t.Columns)
                        if (name == c.name && c.pk)
                            found.Add(c);
                }
                if (found.Count == 0)
                {
                    if (verbose) Console.WriteLine("WARN sequence " + seq + " did not match the key of any table");
                }
                else if (found.Count > 1)
                {
                    if (verbose) Console.WriteLine("INFO sequence " + seq + " matches multiple columns");
                }
                found.ForEach(c =>
                {
                    c.seq = true;
                    c.title += ", s=" + seq;
                });
            }
        }


        //------------------------------------------------------------------------------------------
        // cross match field names.
        void MatchWeakKeys()
        {
            // Iterate over each pair of tables (t1, t2)
            var i = 0;
            foreach (var t1 in Tables)
            {
                var n1 = t1.Columns.Count(c => c.pk);
                for (int j = i + 1; j < Tables.Count; ++j)
                {
                    var t2 = Tables[j];
                    var n2 = t2.Columns.Count(c => c.pk);
                    foreach (var c1 in t1.Columns)
                    {
                        // Only do matching on NUMERIC columns
                        if (!c1.type.StartsWith("num")) continue;
                        foreach (var c2 in t2.Columns)
                        {
                            if (!c2.type.StartsWith("num")) continue;
                            if (c1.name == c2.name && !c1.fk && !c2.fk)
                            {
                                // give preference to solitary primary keys
                                if (n1 == 1 && c1.pk) c2.SetWk(t1.Name);
                                if (n2 == 1 && c2.pk) c1.SetWk(t2.Name);
                                if (!c1.wk && !c2.wk)
                                {
                                    if (c1.pk) c2.SetWk(t1.Name);
                                    if (c2.pk) c1.SetWk(t2.Name);
                                }
                            }
                        }
                    }
                }
                ++i;
            }
        }

        //------------------------------------------------------------------------------------------
        // Steps over all tables and generates a "cell" for each one, which is the graphical representation
        void GenerateGraphCells()
        {
            // convert each table to a <mxCell>....
            Cells = new List<string>();

            var dpi = 100;
            var w10 = 70; // typical width of 10 characters in pixels
            var liney = 16; // line height pixels
            var pagew = papersize > 0 ? papersize : 24; // inches
            var pageh = pagew * 22/17;

            var pkey = "🔑";
            var fkey = "🔹";
            var wkey = "🔸";
            var nul = "𝒏";
            var indx = "𝒊";
            var uniq = "𝒖";
            var seq = "𝒔";
            var deft = "𝒅";

            Cells.Add("<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n" +
                "<!-- Generated with SQL-DRAWIO from " + inputFileName + " on " + DateTime.Now.ToString("yyyy-MM-dd HH:mm") + " -->\n" +
                "<mxGraphModel dx=\"0\" dy=\"0\" grid=\"1\" gridSize=\"10\" guides=\"1\" tooltips=\"1\" connect=\"1\" arrows=\"1\" " +
                $"fold=\"1\" page=\"1\" pageScale=\"1\" pageWidth=\"{pagew*dpi}\" pageHeight=\"{pageh*dpi}\" math=\"0\" shadow=\"0\"><root>");
            Cells.Add("<mxCell id=\"0\"/>");
            Cells.Add("<mxCell id=\"1\" parent=\"0\"/>");

            var px = 0;
            var py = 0;
            var mh = 50;

            // Sort tables
            Tables.Sort((a, b) => a.Name.CompareTo(b.Name));

            foreach (var t in Tables)
            {
                var cell =
                    $"<mxCell id=\"{t.Name}\" value=\"&lt;table style=&quot;border-spacing: 8px 0&quot;&gt;" +
                    "&lt;tbody&gt;&lt;tr style=&quot;background-color: #bed5ff&quot;&gt;" +
                    $"&lt;th colspan=&quot;2&quot; style=&quot;padding: 5px&quot;{Wrap(t.Title)}&gt;{t.Name}&lt;/th&gt;&lt;/tr&gt;";

                var len = 5;
                foreach (var c in t.Columns)
                {
                    len = Math.Max(len, c.name.Length);
                    cell += $"&lt;tr&gt;&lt;td{Wrap(c.title)}&gt;{c.name}&lt;/td&gt;&lt;td&gt;{c.type} ";
                    if (c.pk) cell += pkey;
                    if (c.fk) cell += fkey;
                    if (c.wk) cell += wkey;
                    if (c.nullable) cell += nul;
                    if (c.index) cell += indx;
                    if (c.uniq) cell += uniq;
                    if (c.seq) cell += seq;
                    if (c.def) cell += deft;
                    cell += "&lt;/td&gt;&lt;/tr&gt;";
                }

                var w = (len + 12) * w10 / 10;
                var h = t.Columns.Count * liney + liney * 2;
                if (maxheight>0) h = Math.Min(h, maxheight);
                mh = Math.Max(mh, h);

                cell +=
                    "&lt;/tbody&gt;&lt;/table&gt;\" style=\"perimeterSpacing=0;verticalAlign=middle;" +
                    "align=left;overflow=fill;fontSize=12;fontFamily=Arial Narrow;" +
                    "html=1;rounded=1;comic=1;labelBackgroundColor=none;strokeColor=#000000;" +
                    "endFill=0;endArrow=none;endSize=8;arcSize=5;\" vertex=\"1\" parent=\"1\">" +
                    $"<mxGeometry x=\"{px}\" y=\"{py}\" width=\"{w}\" height=\"{h}\" as=\"geometry\"/>" +
                    "</mxCell>";

                // The following logic places the tables left-to-right and wraps at the page width
                px += w + dpi / 8;
                if (px + 1.5*w > pagew * dpi)
                {
                    px = 0;
                    py += Math.Min(mh, 400) + dpi / 8;
                    mh = 50;
                }

                Cells.Add(cell);
            }
            Cells.Add("</root></mxGraphModel>");
        }

        string Wrap(string title)
        {
            return string.IsNullOrEmpty(title) ? "" : $" title=&quot;{title}&quot;";
        }

        //------------------------------------------------------------------------------------------
        void WriteFile()
        {
            if (clobber || !File.Exists(outputFileName))
            {
                Console.WriteLine("write to " + outputFileName);
                File.WriteAllLines(outputFileName, Cells);
            }
            else
            {
                Console.WriteLine("update " + outputFileName);
                var data = File.ReadAllText(outputFileName);
                foreach (var cell in Cells)
                {
                    data = SwapValue(data, cell);
                }
                File.WriteAllText(outputFileName,data);
            }
        }

        //------------------------------------------------------------------------------------------
        // Find the cell in the data and swap out the value, otherwise append cell to the end of the data.
        string SwapValue(string data, string cell)
        {
            var i1 = cell.IndexOf(" value=\"");
            if (i1 < 0) return data;
            i1 += 8;
            var i2 = cell.IndexOf("\"", i1) - i1;
            if (i2 < 0) return data;
            var j1 = data.IndexOf(cell.Substring(0,i1));
            if (j1 > 0)
            {
                j1 += i1;
                var j2 = data.IndexOf("\"", j1) - j1;
                if (j2 > 0)
                {
                    if (i2 != j2 || data.Substring(j1, j2) != cell.Substring(i1, i2))
                    {
                        if (verbose) Console.WriteLine("Update " + cell.Substring(0,i1));
                        data = data.Substring(0, j1) + cell.Substring(i1, i2) + data.Substring(j1 + j2);
                        return data;
                    }
                    if (verbose) Console.WriteLine("No Change " + cell.Substring(0, i1));
                    return data;
                }
                Console.WriteLine("ERROR failed to parse output file for " + cell.Substring(0, i1));
                return data;
            }
            j1 = data.IndexOf("</root>");
            if (j1 < 0)
            {
                Console.WriteLine("ERROR no </root> found in output.");
                return data;
            }
            if (verbose) Console.WriteLine("Append " + cell.Substring(12, i1-21) + " at "+cell.Substring(cell.IndexOf(" x=")+1,28));
            return data.Insert(j1,cell + "\n");
        }

        //------------------------------------------------------------------------------------------
        static void Main(string[] args)
        {
            var p = new Program();
            foreach (var a in args)
            {
                if (a == "-v") p.verbose = true;
                else if (a == "-p") p.pause = true;
                else if (a == "-c") p.clobber = true;
                else if (a.StartsWith("-h")) p.maxheight = int.Parse(a.Substring(2));
                else if (a.StartsWith("-w")) p.papersize = int.Parse(a.Substring(2));
                else if (a.StartsWith("-")) Console.WriteLine("ERROR unknown option "+a);
                else if (p.inputFileName == null) p.inputFileName = a;
                else p.outputFileName = a;
            }
            if (p.inputFileName == null)
            {
                Console.WriteLine("Specify the input SQL DDL file and output XML file\n"+
                   "Options: -v verbose, -p pause at the end, -c clobber output, "+
                   "-h25 max cell height (px), -w8 paper width (inches)");
                return;
            }

            Console.WriteLine("begin parse " + p.inputFileName);
            p.ReadFile();
            p.MatchSequences();
            p.MatchWeakKeys();
            Console.WriteLine("done parse, found " + p.Tables.Count + " tables");
            p.GenerateGraphCells();
            Console.WriteLine("begin output");
            p.WriteFile();
            Console.WriteLine("done");
            if (p.pause) Console.ReadKey();
        }

    }
}

this is the image

Shameless plug.

I’ve created a sql plugin for drawio.

See https://github.com/funktechno/sqltooling-drawio. You can import and export sql DDLs using this plugin in drawio.