Skip to content

Instantly share code, notes, and snippets.

@jdhenckel

jdhenckel/SQL-DrawIO-README.md

Last active June 25, 2024 16:09
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save jdhenckel/2ac57553404a4f76874e84ccf00527c2 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save jdhenckel/2ac57553404a4f76874e84ccf00527c2 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. jdhenckel revised this gist Jun 21, 2019. 1 changed file with 2 additions and 0 deletions.
    2 changes: 2 additions & 0 deletions SQL-DrawIO-README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -6,6 +6,8 @@ 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

    ![Sql-DrawIO](https://user-images.githubusercontent.com/7905600/59931758-3d0dc180-9435-11e9-877f-d5bb4f3364f9.png)

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

    INPUT FILE
  2. jdhenckel created this gist Jun 21, 2019.
    80 changes: 80 additions & 0 deletions SQL-DrawIO-README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,80 @@
    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...

    ```sql
    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).




    .
    568 changes: 568 additions & 0 deletions SQL-Drawio.cs
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,568 @@
    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();
    }

    }
    }