ER/Schema Visualizer — C# source

// schema-visualizer -- pure CREATE TABLE DDL -> layered ER diagram as SVG.
// C# port (canonical TS: src/lib/schema-visualizer.ts; Go twin:
// cli/schema-visualizer). Tolerant common subset of Postgres/MySQL/SQLite:
// unparseable statements degrade to notes, never throw. Integer geometry
// only (half-up rounding -- JS Math.round parity), so every port draws the
// byte-identical diagram.
//
// Build/run: dotnet run csharp.cs   (or drop into any .NET 8 console app;
// BCL only, no NuGet packages).

#nullable enable

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;

/// <summary>One column of a parsed table. PRIMARY KEY / FOREIGN KEY table
/// constraints flip IsPrimaryKey/Nullable after construction, mirroring the
/// TS lib.</summary>
public sealed class Column
{
    public Column(string name, string type) { Name = name; Type = type; }
    public string Name { get; }
    public string Type { get; }
    public bool Nullable { get; set; } = true;
    public bool IsPrimaryKey { get; set; }
}

/// <summary>One parsed CREATE TABLE.</summary>
public sealed record Table(string Name, List<Column> Columns);

/// <summary>Foreign-key edge. ToColumn is null until resolved to the target
/// table's first PK column (with an "id" fallback).</summary>
public sealed record Fk(string FromTable, string FromColumn, string ToTable, string? ToColumn);

/// <summary>Parse result: tables, resolved FKs, degradation notes.</summary>
public sealed record Schema(List<Table> Tables, List<Fk> ForeignKeys, List<string> Notes);

/// <summary>Mutable box placed by the layout pass (integer geometry).</summary>
public sealed class Box { public int X, Y, W, H; }

/// <summary>Immutable integer rectangle (title bar / column row).</summary>
public sealed record Rect(int X, int Y, int W, int H);

/// <summary>One FK edge: orthogonal path + "from -&gt; to" label.</summary>
public sealed record Edge(Fk Fk, string Path, string Label);

/// <summary>A table with its placed box, title bar and per-column rows.</summary>
public sealed record TableGeo(Table Table, Box Box, Rect TitleBar, List<Rect> ColumnRows);

/// <summary>Diagram geometry: canvas size, placed tables, edges.</summary>
public sealed record Geo(int Width, int Height, List<TableGeo> Tables, List<Edge> Edges);

public static class SchemaVisualizer
{
    // Layout defaults (LAYOUT in the TS lib) -- shared by every port.
    private const int RowHeight = 24, CharWidth = 7, Padding = 8, LayerGap = 60, ColumnGap = 40;

    // Keyword that terminates a column's type tokens.
    private static readonly HashSet<string> Modifiers = new HashSet<string>
    {
        "NOT", "NULL", "PRIMARY", "KEY", "UNIQUE", "DEFAULT", "REFERENCES",
        "AUTO_INCREMENT", "AUTOINCREMENT", "ON", "COMMENT", "CHECK", "CONSTRAINT",
    };

    // Quote characters: opener -> closer ('[' closes with ']').
    private static readonly Dictionary<char, char> Quotes = new Dictionary<char, char>
    {
        { '\'', '\'' }, { '"', '"' }, { '`', '`' }, { '[', ']' },
    };

    private static readonly Regex RxOpen = new Regex(@"\s*\(\s*", RegexOptions.Compiled);
    private static readonly Regex RxClose = new Regex(@"\s*\)\s*", RegexOptions.Compiled);
    private static readonly Regex RxComma = new Regex(@"\s*,\s*", RegexOptions.Compiled);

    /// <summary>A token: kind is "word", "punct", "string" or "qident"
    /// (quoted identifiers carry their text with quotes stripped).</summary>
    private sealed record Tok(string Kind, string Text);

    /// <summary>Control-flow sentinel: a statement failed to parse and
    /// degrades to a note (JS `throw 0` / Python ValueError).</summary>
    private sealed class ParseError : Exception { }

    /// <summary>Round half up (JS Math.round parity -- C# Math.Round is
    /// banker's rounding by default).</summary>
    private static int R(double v) => (int)(v + 0.5);

    /// <summary>Safe token lookup: null when out of range (JS indexed
    /// access yields undefined; Python _g helper).</summary>
    private static Tok? TokAt(List<Tok> toks, int i) => i >= 0 && i < toks.Count ? toks[i] : null;

    private static bool IsP(Tok? t, string p) => t != null && t.Kind == "punct" && t.Text == p;

    private static bool Kw(Tok? t, string w) => t != null && t.Kind == "word" && t.Text.ToUpperInvariant() == w;

    // ---------------------------------------------------------------------
    // Statement splitting and tokenization
    // ---------------------------------------------------------------------

    /// <summary>Split on ';' outside strings/quoted identifiers. Depth-
    /// agnostic: an unterminated paren cannot swallow the statements after
    /// it.</summary>
    private static List<string> SplitStatements(string ddl)
    {
        var result = new List<string>();
        var cur = new StringBuilder();
        int i = 0, n = ddl.Length;
        while (i < n)
        {
            char ch = ddl[i];
            if (Quotes.ContainsKey(ch))
            {
                char close = Quotes[ch];
                cur.Append(ch);
                i++;
                while (i < n)
                {
                    cur.Append(ddl[i]);
                    if (ddl[i] == close)
                    {
                        // '' is an escaped quote inside a '...' literal.
                        if (close == '\'' && i + 1 < n && ddl[i + 1] == '\'')
                        {
                            cur.Append(ddl[i + 1]);
                            i += 2;
                            continue;
                        }
                        break;
                    }
                    i++;
                }
                i++;
                continue;
            }
            if (ch == ';')
            {
                result.Add(cur.ToString());
                cur.Clear();
                i++;
                continue;
            }
            cur.Append(ch);
            i++;
        }
        if (cur.Length > 0 && cur.ToString().Trim().Length > 0) result.Add(cur.ToString());
        return result;
    }

    /// <summary>Tokens: ( ) , . are punct; quoted identifiers/strings carry
    /// their text (quotes stripped); everything else is a word.</summary>
    private static List<Tok> Tokenize(string s)
    {
        var toks = new List<Tok>();
        int i = 0, n = s.Length;
        while (i < n)
        {
            char ch = s[i];
            if (char.IsWhiteSpace(ch)) { i++; continue; }
            if (Quotes.ContainsKey(ch))
            {
                char close = Quotes[ch];
                var text = new StringBuilder();
                i++;
                while (i < n)
                {
                    if (s[i] == close)
                    {
                        if (close == '\'' && i + 1 < n && s[i + 1] == '\'')
                        {
                            text.Append('\'');
                            i += 2;
                            continue;
                        }
                        break;
                    }
                    text.Append(s[i]);
                    i++;
                }
                i++;
                toks.Add(new Tok(ch == '\'' ? "string" : "qident", text.ToString()));
                continue;
            }
            if (ch == '(' || ch == ')' || ch == ',' || ch == '.')
            {
                toks.Add(new Tok("punct", ch.ToString()));
                i++;
                continue;
            }
            int j = i;
            while (j < n && !char.IsWhiteSpace(s[j]) && !"'\",().`[]".Contains(s[j])) j++;
            toks.Add(new Tok("word", s.Substring(i, j - i)));
            i = j;
        }
        return toks;
    }

    // ---------------------------------------------------------------------
    // Token helpers
    // ---------------------------------------------------------------------

    /// <summary>Name (possibly schema-qualified a.b.c) starting at toks[i];
    /// returns null when there is no name there.</summary>
    private static (string Name, int Next)? TakeName(List<Tok> toks, int i)
    {
        if (i >= toks.Count) return null;
        var first = toks[i];
        if (first.Kind != "qident" && first.Kind != "word") return null;
        string name = first.Text;
        int j = i + 1;
        while (IsP(TokAt(toks, j), ".")
               && TokAt(toks, j + 1) != null
               && (TokAt(toks, j + 1)!.Kind == "qident" || TokAt(toks, j + 1)!.Kind == "word"))
        {
            name += "." + toks[j + 1].Text;
            j += 2;
        }
        return (name, j);
    }

    /// <summary>Parse "(a, b, c)" starting at toks[i]; null when malformed.
    /// Returns the names and the index just past ')'.</summary>
    private static (List<string> Names, int Next)? ParenList(List<Tok> toks, int i)
    {
        if (!IsP(TokAt(toks, i), "(")) return null;
        var names = new List<string>();
        int j = i + 1;
        for (; ; )
        {
            var name = TakeName(toks, j);
            if (name == null) return null;
            names.Add(name.Value.Name);
            j = name.Value.Next;
            if (IsP(TokAt(toks, j), ",")) { j++; continue; }
            if (IsP(TokAt(toks, j), ")")) return (names, j + 1);
            return null;
        }
    }

    /// <summary>Re-join type tokens: collapse inner spacing around ( ) ,
    /// then uppercase ("VARCHAR ( 255 )" -> "VARCHAR(255)").</summary>
    private static string JoinType(List<Tok> toks)
    {
        string raw = string.Join(" ", toks.Select(t => t.Text));
        raw = RxOpen.Replace(raw, "(");
        raw = RxClose.Replace(raw, ")");
        raw = RxComma.Replace(raw, ",");
        return raw.Trim().ToUpperInvariant();
    }

    // ---------------------------------------------------------------------
    // DDL parsing
    // ---------------------------------------------------------------------

    /// <summary>Parse one comma-line of a CREATE TABLE body into a column;
    /// inline REFERENCES also feed the FK list.</summary>
    private static Column? ParseColumn(List<Tok> line, string tableName, List<Fk> fks)
    {
        var name = TakeName(line, 0);
        if (name == null) return null;
        int i = name.Value.Next;
        var typeToks = new List<Tok>();
        while (i < line.Count && !(line[i].Kind == "word" && Modifiers.Contains(line[i].Text.ToUpperInvariant())))
        {
            typeToks.Add(line[i]);
            i++;
        }
        bool nullable = true, pk = false;
        while (i < line.Count)
        {
            var t = line[i];
            if (Kw(t, "NOT") && Kw(TokAt(line, i + 1), "NULL")) { nullable = false; i += 2; continue; }
            if (Kw(t, "NULL")) { i++; continue; }
            if (Kw(t, "PRIMARY") && Kw(TokAt(line, i + 1), "KEY")) { pk = true; nullable = false; i += 2; continue; }
            if (Kw(t, "UNIQUE") || Kw(t, "AUTO_INCREMENT") || Kw(t, "AUTOINCREMENT")) { i++; continue; }
            if (Kw(t, "DEFAULT"))
            {
                i++;
                if (IsP(TokAt(line, i), "("))
                {
                    int depth = 0;
                    do
                    {
                        if (IsP(TokAt(line, i), "(")) depth++;
                        if (IsP(TokAt(line, i), ")")) depth--;
                        i++;
                    } while (i < line.Count && depth > 0);
                }
                else if (i < line.Count) i++;
                continue;
            }
            if (Kw(t, "COMMENT")) { i++; if (i < line.Count && line[i].Kind == "string") i++; continue; }
            if (Kw(t, "ON"))
            {
                i += 2;
                if (Kw(TokAt(line, i), "SET") || Kw(TokAt(line, i), "NO")) i += 2;
                else if (i < line.Count) i++;
                continue;
            }
            if (Kw(t, "REFERENCES"))
            {
                i++;
                var target = TakeName(line, i);
                if (target != null)
                {
                    i = target.Value.Next;
                    string? toCol = null;
                    if (IsP(TokAt(line, i), "("))
                    {
                        var list = ParenList(line, i);
                        if (list != null) { toCol = list.Value.Names[0]; i = list.Value.Next; }
                    }
                    fks.Add(new Fk(tableName, name.Value.Name, target.Value.Name, toCol));
                }
                continue;
            }
            i++; // unknown modifier tolerated
        }
        return new Column(name.Value.Name, JoinType(typeToks)) { Nullable = nullable, IsPrimaryKey = pk };
    }

    /// <summary>Parse DDL text into tables + foreign keys + notes. Tolerant:
    /// unparseable statements degrade to notes, never throw.</summary>
    public static Schema ParseDdl(string ddl)
    {
        if (ddl.Trim().Length == 0)
        {
            return new Schema(new List<Table>(), new List<Fk>(), new List<string> { "No DDL input." });
        }
        var tables = new List<Table>();
        var fks = new List<Fk>();
        var notes = new List<string>();
        foreach (string stmt in SplitStatements(ddl))
        {
            if (stmt.Trim().Length == 0) continue;
            var toks = Tokenize(stmt);
            try
            {
                int i = 0;
                if (!Kw(TokAt(toks, i), "CREATE")) throw new ParseError();
                i++;
                while (Kw(TokAt(toks, i), "TEMP") || Kw(TokAt(toks, i), "TEMPORARY") || Kw(TokAt(toks, i), "UNLOGGED")) i++;
                if (!Kw(TokAt(toks, i), "TABLE"))
                {
                    notes.Add("Skipped non-table statement.");
                    continue;
                }
                i++;
                if (Kw(TokAt(toks, i), "IF") && Kw(TokAt(toks, i + 1), "NOT") && Kw(TokAt(toks, i + 2), "EXISTS")) i += 3;
                var name = TakeName(toks, i);
                if (name == null || !IsP(TokAt(toks, name.Value.Next), "(")) throw new ParseError();
                i = name.Value.Next + 1;
                // Body = tokens until the matching close paren (depth-aware).
                var body = new List<Tok>();
                int depth = 0;
                for (; i < toks.Count; i++)
                {
                    if (IsP(toks[i], "(")) depth++;
                    if (IsP(toks[i], ")"))
                    {
                        if (depth == 0) break;
                        depth--;
                    }
                    body.Add(toks[i]);
                }
                if (i >= toks.Count) throw new ParseError();
                // Split body into top-level comma lines.
                var lines = new List<List<Tok>>();
                var line = new List<Tok>();
                depth = 0;
                foreach (var t in body)
                {
                    if (IsP(t, "(")) depth++;
                    if (IsP(t, ")")) depth--;
                    if (IsP(t, ",") && depth == 0)
                    {
                        lines.Add(line);
                        line = new List<Tok>();
                        continue;
                    }
                    line.Add(t);
                }
                if (line.Count > 0) lines.Add(line);
                var table = new Table(name.Value.Name, new List<Column>());
                tables.Add(table);
                foreach (var toks2 in lines)
                {
                    if (toks2.Count == 0) continue;
                    var first = toks2[0];
                    string u = first.Kind == "word" ? first.Text.ToUpperInvariant() : "";
                    if (u == "PRIMARY" && Kw(TokAt(toks2, 1), "KEY"))
                    {
                        var list = ParenList(toks2, 2);
                        if (list != null)
                        {
                            foreach (string cn in list.Value.Names)
                            {
                                foreach (var col in table.Columns)
                                {
                                    if (col.Name == cn) { col.IsPrimaryKey = true; col.Nullable = false; }
                                }
                            }
                        }
                        continue;
                    }
                    if (u == "FOREIGN" && Kw(TokAt(toks2, 1), "KEY"))
                    {
                        var from = ParenList(toks2, 2);
                        if (from != null && Kw(TokAt(toks2, from.Value.Next), "REFERENCES"))
                        {
                            var target = TakeName(toks2, from.Value.Next + 1);
                            if (target != null)
                            {
                                List<string>? toCols = null;
                                if (IsP(TokAt(toks2, target.Value.Next), "("))
                                {
                                    var to = ParenList(toks2, target.Value.Next);
                                    if (to != null) toCols = to.Value.Names;
                                }
                                for (int idx = 0; idx < from.Value.Names.Count; idx++)
                                {
                                    string fc = from.Value.Names[idx];
                                    string? toCol = null;
                                    if (toCols != null)
                                    {
                                        toCol = idx < toCols.Count ? toCols[idx] : toCols[toCols.Count - 1];
                                    }
                                    fks.Add(new Fk(table.Name, fc, target.Value.Name, toCol));
                                }
                            }
                        }
                        continue;
                    }
                    if (u == "UNIQUE" || u == "KEY" || u == "INDEX"
                        || u == "CHECK" || u == "EXCLUDE" || u == "CONSTRAINT") continue;
                    var col = ParseColumn(toks2, table.Name, fks);
                    if (col != null) table.Columns.Add(col);
                }
            }
            catch (ParseError)
            {
                notes.Add("Skipped unparseable statement.");
            }
            catch (Exception) // never-throw contract (TS: bare `catch`)
            {
                notes.Add("Skipped unparseable statement.");
            }
        }
        // Resolve omitted FK target columns to the referenced table's first PK.
        var resolved = new List<Fk>();
        foreach (var fk in fks)
        {
            if (!string.IsNullOrEmpty(fk.ToColumn)) { resolved.Add(fk); continue; }
            var target = tables.FirstOrDefault(t => t.Name == fk.ToTable);
            var pk = target?.Columns.FirstOrDefault(c => c.IsPrimaryKey);
            resolved.Add(fk with { ToColumn = pk?.Name ?? "id" });
        }
        return new Schema(tables, resolved, notes);
    }

    // ---------------------------------------------------------------------
    // Layout
    // ---------------------------------------------------------------------

    /// <summary>Layered layout: FK source tables sit one layer below their
    /// target. Integer geometry, half-up rounding -- byte-identical to the
    /// TS/Go/JS/Python ports.</summary>
    public static Geo LayoutSchema(Schema schema)
    {
        if (schema.Tables.Count == 0)
        {
            return new Geo(0, 0, new List<TableGeo>(), new List<Edge>());
        }
        var index = new Dictionary<string, int>();
        for (int i = 0; i < schema.Tables.Count; i++)
        {
            if (!index.ContainsKey(schema.Tables[i].Name)) index[schema.Tables[i].Name] = i;
        }
        var boxes = new List<Box>();
        foreach (var t in schema.Tables)
        {
            int textLen = t.Name.Length;
            foreach (var c in t.Columns)
            {
                textLen = Math.Max(textLen, (c.Name + " " + c.Type).Length);
            }
            textLen = Math.Max(textLen, 1);
            boxes.Add(new Box
            {
                W = R(textLen * CharWidth + 2 * Padding),
                H = R(RowHeight * (1 + t.Columns.Count) + Padding),
            });
        }
        var layerOf = new int[schema.Tables.Count];
        for (int pass = 0; pass < schema.Tables.Count; pass++)
        {
            bool changed = false;
            foreach (var fk in schema.ForeignKeys)
            {
                if (!index.TryGetValue(fk.FromTable, out int ti)
                    || !index.TryGetValue(fk.ToTable, out int tj) || ti == tj) continue;
                if (layerOf[ti] < layerOf[tj] + 1) { layerOf[ti] = layerOf[tj] + 1; changed = true; }
            }
            if (!changed) break;
        }
        var layers = new Dictionary<int, List<int>>();
        for (int i = 0; i < layerOf.Length; i++)
        {
            if (!layers.TryGetValue(layerOf[i], out var layer)) { layer = new List<int>(); layers[layerOf[i]] = layer; }
            layer.Add(i);
        }
        int y = 0, width = 0, height = 0;
        foreach (int li in layers.Keys.OrderBy(k => k))
        {
            int x = 0, layerH = 0;
            foreach (int i in layers[li])
            {
                boxes[i].X = x;
                boxes[i].Y = y;
                x += boxes[i].W + ColumnGap;
                layerH = Math.Max(layerH, boxes[i].H);
            }
            width = Math.Max(width, x - ColumnGap);
            height = Math.Max(height, y + layerH);
            y += layerH + LayerGap;
        }
        var edges = new List<Edge>();
        foreach (var fk in schema.ForeignKeys)
        {
            if (!index.TryGetValue(fk.FromTable, out int fi) || !index.TryGetValue(fk.ToTable, out int ti)) continue;
            Box from = boxes[fi], to = boxes[ti];
            int x1 = to.X + R(to.W / 2.0), y1 = to.Y + to.H;
            int x2 = from.X + R(from.W / 2.0), y2 = from.Y;
            int midY = R((y1 + y2) / 2.0);
            edges.Add(new Edge(fk,
                $"M {x1} {y1} V {midY} H {x2} V {y2}",
                $"{fk.FromColumn} → {fk.ToColumn}"));
        }
        var laid = new List<TableGeo>();
        for (int i = 0; i < schema.Tables.Count; i++)
        {
            var t = schema.Tables[i];
            var rows = new List<Rect>();
            for (int ci = 0; ci < t.Columns.Count; ci++)
            {
                rows.Add(new Rect(boxes[i].X, boxes[i].Y + RowHeight * (1 + ci), boxes[i].W, RowHeight));
            }
            laid.Add(new TableGeo(t, boxes[i],
                new Rect(boxes[i].X, boxes[i].Y, boxes[i].W, RowHeight), rows));
        }
        return new Geo(width, height, laid, edges);
    }

    // ---------------------------------------------------------------------
    // SVG rendering
    // ---------------------------------------------------------------------

    private static string Esc(string s) => s
        .Replace("&", "&amp;")
        .Replace("<", "&lt;")
        .Replace(">", "&gt;")
        .Replace("\"", "&quot;");

    /// <summary>Render the diagram geometry as one SVG string (edges under
    /// tables, fixed class names and attribute order for CSS parity).</summary>
    public static string RenderSvg(Geo geo)
    {
        var parts = new List<string>
        {
            "<svg xmlns=\"http://www.w3.org/2000/svg\" viewBox=\"0 0 " + geo.Width + " " + geo.Height
                + "\" class=\"sv-root\" role=\"img\"><title>Schema diagram</title>",
        };
        var boxOf = new Dictionary<string, Box>();
        foreach (var t in geo.Tables) boxOf[t.Table.Name] = t.Box;
        foreach (var e in geo.Edges)
        {
            if (!boxOf.TryGetValue(e.Fk.FromTable, out var from)) continue;
            int ax = from.X + R(from.W / 2.0);
            parts.Add("<path class=\"sv-edge\" d=\"" + e.Path + "\"/>"
                + "<polygon class=\"sv-arrow\" points=\"" + (ax - 5) + "," + (from.Y - 8)
                + " " + (ax + 5) + "," + (from.Y - 8) + " " + ax + "," + from.Y + "\"/>");
        }
        foreach (var t in geo.Tables)
        {
            parts.Add("<g class=\"sv-table\"><rect class=\"sv-box\" x=\"" + t.Box.X + "\" y=\"" + t.Box.Y
                + "\" width=\"" + t.Box.W + "\" height=\"" + t.Box.H + "\" rx=\"6\"/>"
                + "<rect class=\"sv-titlebar\" x=\"" + t.TitleBar.X + "\" y=\"" + t.TitleBar.Y
                + "\" width=\"" + t.TitleBar.W + "\" height=\"" + t.TitleBar.H + "\" rx=\"6\"/>"
                + "<text class=\"sv-title\" x=\"" + (t.Box.X + 8) + "\" y=\"" + (t.TitleBar.Y + 17)
                + "\">" + Esc(t.Table.Name) + "</text>");
            for (int ci = 0; ci < t.Table.Columns.Count; ci++)
            {
                var c = t.Table.Columns[ci];
                string cls = c.IsPrimaryKey ? "sv-pk" : "sv-col";
                parts.Add("<text class=\"" + cls + "\" x=\"" + (t.ColumnRows[ci].X + 8)
                    + "\" y=\"" + (t.ColumnRows[ci].Y + 17) + "\">" + Esc(c.Name) + " " + Esc(c.Type) + "</text>");
            }
            parts.Add("</g>");
        }
        parts.Add("</svg>");
        return string.Concat(parts);
    }

    /// <summary>Full pipeline: DDL text -> (SVG string, parsed schema).</summary>
    public static (string Svg, Schema Schema) DdlToSvg(string ddl)
    {
        var schema = ParseDdl(ddl);
        return (RenderSvg(LayoutSchema(schema)), schema);
    }
}

/// <summary>Showcase: parse the docs sample schema and print the SVG.
/// Run: dotnet run csharp.cs</summary>
public static class Program
{
    public static void Main()
    {
        const string ddl = "CREATE TABLE users (id INT PRIMARY KEY);"
            + "CREATE TABLE posts (id INT PRIMARY KEY, user_id INT REFERENCES users(id), title TEXT);";
        var (svg, schema) = SchemaVisualizer.DdlToSvg(ddl);
        Console.WriteLine(svg);
        Console.Error.WriteLine("tables=" + schema.Tables.Count
            + " fks=" + schema.ForeignKeys.Count + " notes=" + schema.Notes.Count);
    }
}

// Example:
//   DdlToSvg("CREATE TABLE users (id INT PRIMARY KEY);"
//            + "CREATE TABLE posts (id INT PRIMARY KEY, user_id INT REFERENCES users(id), title TEXT);")
//   -> users box on layer 0, posts below, one FK edge -- byte-identical to
//      the TS/Go/JS/... ports (integer geometry, same defaults).