ER/Schema Visualizer — Java source

// schema-visualizer — pure CREATE TABLE DDL → layered ER diagram as SVG.
// Java 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.
//
// The class is deliberately package-private: the display filename is
// java.java, which differs from the class name, and a public top-level class
// may only be declared in a file named after itself.

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import java.util.Set;
import java.util.TreeMap;

final class SchemaVisualizer {

    private SchemaVisualizer() {}

    /** Layout constants shared by every port: 24/7/8/60/40. */
    private static final int ROW_HEIGHT = 24, CHAR_WIDTH = 7, PADDING = 8, LAYER_GAP = 60, COLUMN_GAP = 40;

    /** Keywords that end a column's type and start its modifiers. */
    private static final Set<String> MODIFIERS = Set.of("NOT", "NULL", "PRIMARY", "KEY", "UNIQUE",
            "DEFAULT", "REFERENCES", "AUTO_INCREMENT", "AUTOINCREMENT", "ON", "COMMENT", "CHECK",
            "CONSTRAINT");

    /** Opening quote -> closing quote (SQL Server [ident] included). */
    private static final Map<Character, Character> QUOTES =
            Map.of('\'', '\'', '"', '"', '`', '`', '[', ']');

    /** Round half up: (int)(v + 0.5), JS Math.round parity. */
    static int r(double v) { return (int) (v + 0.5); }

    // ---- types ------------------------------------------------------------

    /** Token: kind is "word" | "punct" | "string" | "qident"; quotes stripped. */
    record Tok(String kind, String text) {}

    /** takeName result: the dotted name plus the index just past it. */
    record NameRef(String name, int next) {}

    /** parenList result: the names plus the index just past the ')'. */
    record ListRef(List<String> names, int next) {}

    record ForeignKey(String fromTable, String fromColumn, String toTable, String toColumn) {}

    static final class Column {
        final String name;
        final String type;
        boolean nullable = true;
        boolean isPrimaryKey;
        Column(String name, String type, boolean nullable, boolean isPrimaryKey) {
            this.name = name;
            this.type = type;
            this.nullable = nullable;
            this.isPrimaryKey = isPrimaryKey;
        }
    }

    static final class Table {
        final String name;
        final List<Column> columns = new ArrayList<>();
        Table(String name) { this.name = name; }
    }

    record Schema(List<Table> tables, List<ForeignKey> foreignKeys, List<String> notes) {}

    /** Mutable during layout, read-only afterwards. */
    static final class Box {
        int x, y, w, h;
        Box(int x, int y, int w, int h) { this.x = x; this.y = y; this.w = w; this.h = h; }
    }

    record LaidTable(Table table, Box box, Box titleBar, List<Box> columnRows) {}

    record Edge(ForeignKey fk, String path, String label) {}

    record Geometry(int width, int height, List<LaidTable> tables, List<Edge> edges) {}

    record Result(String svg, Schema schema) {}

    /** Control-flow escape mirroring the ports' `throw 0` / `raise ValueError`. */
    private static final class Unparseable extends RuntimeException {}

    // ---- lexer ------------------------------------------------------------

    /** JS /\s/ — ASCII whitespace is the practical DDL subset. */
    private static boolean isSpace(char c) {
        return c == ' ' || c == '\t' || c == '\n' || c == '\f' || c == '\r' || c == 0x0B;
    }

    /** Split on `;` outside strings/quoted identifiers (depth-agnostic: an
     *  unterminated paren cannot swallow the statements after it). */
    static List<String> splitStatements(String ddl) {
        List<String> out = new ArrayList<>();
        StringBuilder cur = new StringBuilder();
        int i = 0, n = ddl.length();
        while (i < n) {
            char ch = ddl.charAt(i);
            Character close = QUOTES.get(ch);
            if (close != null) {
                cur.append(ch);
                i++;
                while (i < n) {
                    cur.append(ddl.charAt(i));
                    if (ddl.charAt(i) == close) {
                        if (close == '\'' && i + 1 < n && ddl.charAt(i + 1) == '\'') {
                            cur.append(ddl.charAt(i + 1));
                            i += 2;
                            continue;
                        }
                        break;
                    }
                    i++;
                }
                i++;
                continue;
            }
            if (ch == ';') {
                out.add(cur.toString());
                cur.setLength(0);
                i++;
                continue;
            }
            cur.append(ch);
            i++;
        }
        if (!cur.toString().trim().isEmpty()) out.add(cur.toString());
        return out;
    }

    /** Tokens: quoted identifiers/strings carry their text (quotes stripped);
     *  ( ) , . are punct; everything else is a word. */
    static List<Tok> tokenize(String s) {
        List<Tok> toks = new ArrayList<>();
        int i = 0, n = s.length();
        while (i < n) {
            char ch = s.charAt(i);
            if (isSpace(ch)) { i++; continue; }
            Character close = QUOTES.get(ch);
            if (close != null) {
                StringBuilder text = new StringBuilder();
                i++;
                while (i < n) {
                    if (s.charAt(i) == close) {
                        if (close == '\'' && i + 1 < n && s.charAt(i + 1) == '\'') {
                            text.append('\'');
                            i += 2;
                            continue;
                        }
                        break;
                    }
                    text.append(s.charAt(i));
                    i++;
                }
                i++;
                toks.add(new Tok(ch == '\'' ? "string" : "qident", text.toString()));
                continue;
            }
            if (ch == '(' || ch == ')' || ch == ',' || ch == '.') {
                toks.add(new Tok("punct", String.valueOf(ch)));
                i++;
                continue;
            }
            StringBuilder word = new StringBuilder();
            while (i < n && !isWordBreak(s.charAt(i))) word.append(s.charAt(i++));
            toks.add(new Tok("word", word.toString()));
        }
        return toks;
    }

    private static boolean isWordBreak(char c) {
        return isSpace(c) || c == '\'' || c == '"' || c == '(' || c == ')' || c == ','
                || c == '.' || c == '`' || c == '[' || c == ']';
    }

    // ---- token helpers ----------------------------------------------------

    private static Tok g(List<Tok> toks, int i) { return i >= 0 && i < toks.size() ? toks.get(i) : null; }

    private static boolean isP(Tok t, String p) {
        return t != null && t.kind().equals("punct") && t.text().equals(p);
    }

    private static boolean kw(Tok t, String w) {
        return t != null && t.kind().equals("word") && t.text().toUpperCase(Locale.ROOT).equals(w);
    }

    /** [schema.]name — dotted parts keep the port's textual join. */
    static NameRef takeName(List<Tok> toks, int i) {
        Tok first = g(toks, i);
        if (first == null || !(first.kind().equals("qident") || first.kind().equals("word"))) return null;
        StringBuilder name = new StringBuilder(first.text());
        int j = i + 1;
        while (isP(g(toks, j), ".") && g(toks, j + 1) != null
                && (g(toks, j + 1).kind().equals("qident") || g(toks, j + 1).kind().equals("word"))) {
            name.append('.').append(toks.get(j + 1).text());
            j += 2;
        }
        return new NameRef(name.toString(), j);
    }

    /** ( name [, name]* ) — anything else is unparseable (null). */
    static ListRef parenList(List<Tok> toks, int i) {
        if (!isP(g(toks, i), "(")) return null;
        List<String> names = new ArrayList<>();
        int j = i + 1;
        for (;;) {
            NameRef name = takeName(toks, j);
            if (name == null) return null;
            names.add(name.name());
            j = name.next();
            if (isP(g(toks, j), ",")) { j++; continue; }
            if (isP(g(toks, j), ")")) return new ListRef(names, j + 1);
            return null;
        }
    }

    // ---- parser -----------------------------------------------------------

    /** Join type tokens, squeezing the spaces around ( ) , then uppercasing. */
    private static String joinType(List<Tok> toks) {
        StringBuilder raw = new StringBuilder();
        for (int k = 0; k < toks.size(); k++) {
            if (k > 0) raw.append(' ');
            raw.append(toks.get(k).text());
        }
        return raw.toString()
                .replaceAll("\\s*\\(\\s*", "(")
                .replaceAll("\\s*\\)\\s*", ")")
                .replaceAll("\\s*,\\s*", ",")
                .trim()
                .toUpperCase(Locale.ROOT);
    }

    /** One column definition; also collects inline REFERENCES foreign keys. */
    static Column parseColumn(List<Tok> line, String tableName, List<ForeignKey> fks) {
        NameRef name = takeName(line, 0);
        if (name == null) return null;
        int i = name.next();
        List<Tok> typeToks = new ArrayList<>();
        while (i < line.size()
                && !(line.get(i).kind().equals("word") && MODIFIERS.contains(line.get(i).text().toUpperCase(Locale.ROOT)))) {
            typeToks.add(line.get(i));
            i++;
        }
        boolean nullable = true, pk = false;
        while (i < line.size()) {
            Tok t = line.get(i);
            if (kw(t, "NOT") && kw(g(line, i + 1), "NULL")) { nullable = false; i += 2; continue; }
            if (kw(t, "NULL")) { i++; continue; }
            if (kw(t, "PRIMARY") && kw(g(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(g(line, i), "(")) {               // swallow ( expr ) balanced
                    int d = 0;
                    do {
                        if (isP(g(line, i), "(")) d++;
                        if (isP(g(line, i), ")")) d--;
                        i++;
                    } while (i < line.size() && d > 0);
                } else if (i < line.size()) {
                    i++;
                }
                continue;
            }
            if (kw(t, "COMMENT")) {
                i++;
                if (i < line.size() && line.get(i).kind().equals("string")) i++;
                continue;
            }
            if (kw(t, "ON")) {                            // ON UPDATE/DELETE ...
                i += 2;
                if (kw(g(line, i), "SET") || kw(g(line, i), "NO")) i += 2;
                else if (i < line.size()) i++;
                continue;
            }
            if (kw(t, "REFERENCES")) {
                i++;
                NameRef target = takeName(line, i);
                if (target != null) {
                    i = target.next();
                    String toCol = null;
                    if (isP(g(line, i), "(")) {
                        ListRef list = parenList(line, i);
                        if (list != null) { toCol = list.names().get(0); i = list.next(); }
                    }
                    fks.add(new ForeignKey(tableName, name.name(), target.name(), toCol));
                }
                continue;
            }
            i++; // unknown modifier tolerated
        }
        return new Column(name.name(), joinType(typeToks), nullable, pk);
    }

    /** DDL text -> schema (tables, foreign keys, notes). Never throws. */
    static Schema parseDdl(String ddl) {
        if (ddl.trim().isEmpty()) return new Schema(List.of(), List.of(), List.of("No DDL input."));
        List<Table> tables = new ArrayList<>();
        List<ForeignKey> fks = new ArrayList<>();
        List<String> notes = new ArrayList<>();
        for (String stmt : splitStatements(ddl)) {
            if (stmt.trim().isEmpty()) continue;
            List<Tok> toks = tokenize(stmt);
            try {
                int i = 0;
                if (!kw(g(toks, i), "CREATE")) throw new Unparseable();
                i++;
                while (kw(g(toks, i), "TEMP") || kw(g(toks, i), "TEMPORARY") || kw(g(toks, i), "UNLOGGED")) i++;
                if (!kw(g(toks, i), "TABLE")) { notes.add("Skipped non-table statement."); continue; }
                i++;
                if (kw(g(toks, i), "IF") && kw(g(toks, i + 1), "NOT") && kw(g(toks, i + 2), "EXISTS")) i += 3;
                NameRef name = takeName(toks, i);
                if (name == null || !isP(g(toks, name.next()), "(")) throw new Unparseable();
                i = name.next() + 1;
                // body = tokens until the matching close paren (depth-aware)
                List<Tok> body = new ArrayList<>();
                int depth = 0;
                for (; i < toks.size(); i++) {
                    if (isP(toks.get(i), "(")) depth++;
                    if (isP(toks.get(i), ")")) { if (depth == 0) break; depth--; }
                    body.add(toks.get(i));
                }
                if (i >= toks.size()) throw new Unparseable();
                // split body into top-level comma lines
                List<List<Tok>> lines = new ArrayList<>();
                List<Tok> line = new ArrayList<>();
                depth = 0;
                for (Tok t : body) {
                    if (isP(t, "(")) depth++;
                    if (isP(t, ")")) depth--;
                    if (isP(t, ",") && depth == 0) { lines.add(line); line = new ArrayList<>(); continue; }
                    line.add(t);
                }
                if (!line.isEmpty()) lines.add(line);
                Table table = new Table(name.name());
                tables.add(table);
                for (List<Tok> toks2 : lines) {
                    if (toks2.isEmpty()) continue;
                    Tok first = toks2.get(0);
                    String u = first.kind().equals("word") ? first.text().toUpperCase(Locale.ROOT) : "";
                    if (u.equals("PRIMARY") && kw(g(toks2, 1), "KEY")) {
                        ListRef list = parenList(toks2, 2);
                        if (list != null) for (String cn : list.names()) {
                            for (Column col : table.columns) {
                                if (col.name.equals(cn)) { col.isPrimaryKey = true; col.nullable = false; break; }
                            }
                        }
                        continue;
                    }
                    if (u.equals("FOREIGN") && kw(g(toks2, 1), "KEY")) {
                        ListRef from = parenList(toks2, 2);
                        if (from != null && kw(g(toks2, from.next()), "REFERENCES")) {
                            NameRef target = takeName(toks2, from.next() + 1);
                            if (target != null) {
                                List<String> toCols = null;
                                if (isP(g(toks2, target.next()), "(")) {
                                    ListRef to = parenList(toks2, target.next());
                                    if (to != null) toCols = to.names();
                                }
                                for (int idx = 0; idx < from.names().size(); idx++) {
                                    String toCol = null;
                                    if (toCols != null) {
                                        toCol = idx < toCols.size() ? toCols.get(idx) : toCols.get(toCols.size() - 1);
                                    }
                                    fks.add(new ForeignKey(table.name, from.names().get(idx),
                                            target.name(), toCol));
                                }
                            }
                        }
                        continue;
                    }
                    if (u.equals("UNIQUE") || u.equals("KEY") || u.equals("INDEX") || u.equals("CHECK")
                            || u.equals("EXCLUDE") || u.equals("CONSTRAINT")) continue;
                    Column col = parseColumn(toks2, table.name, fks);
                    if (col != null) table.columns.add(col);
                }
            } catch (Unparseable e) {
                notes.add("Skipped unparseable statement.");
            }
        }
        // resolve omitted FK target columns to the referenced table's first PK
        List<ForeignKey> resolved = new ArrayList<>();
        for (ForeignKey fk : fks) {
            if (fk.toColumn() != null && !fk.toColumn().isEmpty()) { resolved.add(fk); continue; }
            Table target = null;
            for (Table t : tables) if (t.name.equals(fk.toTable())) { target = t; break; }
            String pk = null;
            if (target != null) for (Column c : target.columns) if (c.isPrimaryKey) { pk = c.name; break; }
            resolved.add(new ForeignKey(fk.fromTable(), fk.fromColumn(), fk.toTable(), pk != null ? pk : "id"));
        }
        return new Schema(tables, resolved, notes);
    }

    // ---- layout -----------------------------------------------------------

    /** Layered boxes + orthogonal edges; integer geometry throughout. */
    static Geometry layoutSchema(Schema schema) {
        if (schema.tables().isEmpty()) return new Geometry(0, 0, List.of(), List.of());
        Map<String, Integer> index = new HashMap<>();     // first occurrence wins
        for (int i = 0; i < schema.tables().size(); i++) index.putIfAbsent(schema.tables().get(i).name, i);
        List<Box> boxes = new ArrayList<>();
        for (Table t : schema.tables) {
            int textLen = Math.max(t.name.length(), 1);
            for (Column c : t.columns) textLen = Math.max(textLen, (c.name + " " + c.type).length());
            boxes.add(new Box(0, 0,
                    r(textLen * CHAR_WIDTH + 2 * PADDING),
                    r(ROW_HEIGHT * (1 + t.columns.size()) + PADDING)));
        }
        // longest-path layering (iterate to fixpoint; cycles cap at n passes)
        int[] layerOf = new int[schema.tables().size()];
        for (int pass = 0; pass < schema.tables().size(); pass++) {
            boolean changed = false;
            for (ForeignKey fk : schema.foreignKeys()) {
                Integer ti = index.get(fk.fromTable()), tj = index.get(fk.toTable());
                if (ti == null || tj == null || ti.intValue() == tj.intValue()) continue;
                if (layerOf[ti] < layerOf[tj] + 1) { layerOf[ti] = layerOf[tj] + 1; changed = true; }
            }
            if (!changed) break;
        }
        Map<Integer, List<Integer>> layers = new TreeMap<>();
        for (int i = 0; i < layerOf.length; i++) layers.computeIfAbsent(layerOf[i], k -> new ArrayList<>()).add(i);
        int y = 0, width = 0, height = 0;
        for (List<Integer> layer : layers.values()) {
            int x = 0, layerH = 0;
            for (int i : layer) {
                Box b = boxes.get(i);
                b.x = x;
                b.y = y;
                x += b.w + COLUMN_GAP;
                layerH = Math.max(layerH, b.h);
            }
            width = Math.max(width, x - COLUMN_GAP);
            height = Math.max(height, y + layerH);
            y += layerH + LAYER_GAP;
        }
        List<Edge> edges = new ArrayList<>();
        for (ForeignKey fk : schema.foreignKeys()) {
            Integer fi = index.get(fk.fromTable()), ti = index.get(fk.toTable());
            if (fi == null || ti == null) continue;
            Box from = boxes.get(fi), to = boxes.get(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()));
        }
        List<LaidTable> laid = new ArrayList<>();
        for (int i = 0; i < schema.tables().size(); i++) {
            Table t = schema.tables().get(i);
            Box b = boxes.get(i);
            List<Box> rows = new ArrayList<>();
            for (int ci = 0; ci < t.columns.size(); ci++) {
                rows.add(new Box(b.x, b.y + ROW_HEIGHT * (1 + ci), b.w, ROW_HEIGHT));
            }
            laid.add(new LaidTable(t, b, new Box(b.x, b.y, b.w, ROW_HEIGHT), rows));
        }
        return new Geometry(width, height, laid, edges);
    }

    // ---- render -----------------------------------------------------------

    /** XML-escape the four characters SVG text nodes care about. */
    static String esc(String s) {
        StringBuilder out = new StringBuilder(s.length());
        for (int i = 0; i < s.length(); i++) {
            switch (s.charAt(i)) {
                case '&' -> out.append("&amp;");
                case '<' -> out.append("&lt;");
                case '>' -> out.append("&gt;");
                case '"' -> out.append("&quot;");
                default -> out.append(s.charAt(i));
            }
        }
        return out.toString();
    }

    static String renderSvg(Geometry geo) {
        StringBuilder out = new StringBuilder();
        out.append("<svg xmlns=\"http://www.w3.org/2000/svg\" viewBox=\"0 0 ").append(geo.width())
                .append(' ').append(geo.height())
                .append("\" class=\"sv-root\" role=\"img\"><title>Schema diagram</title>");
        Map<String, Box> boxOf = new HashMap<>();        // last occurrence wins
        for (LaidTable t : geo.tables()) boxOf.put(t.table().name, t.box());
        for (Edge e : geo.edges()) {
            Box from = boxOf.get(e.fk().fromTable());
            if (from == null) continue;
            int ax = from.x + r(from.w / 2.0);
            out.append("<path class=\"sv-edge\" d=\"").append(e.path()).append("\"/>")
                    .append("<polygon class=\"sv-arrow\" points=\"")
                    .append(ax - 5).append(',').append(from.y - 8).append(' ')
                    .append(ax + 5).append(',').append(from.y - 8).append(' ')
                    .append(ax).append(',').append(from.y).append("\"/>");
        }
        for (LaidTable t : geo.tables()) {
            Box b = t.box(), tb = t.titleBar();
            out.append("<g class=\"sv-table\">")
                    .append("<rect class=\"sv-box\" x=\"").append(b.x).append("\" y=\"").append(b.y)
                    .append("\" width=\"").append(b.w).append("\" height=\"").append(b.h).append("\" rx=\"6\"/>")
                    .append("<rect class=\"sv-titlebar\" x=\"").append(tb.x).append("\" y=\"").append(tb.y)
                    .append("\" width=\"").append(tb.w).append("\" height=\"").append(tb.h).append("\" rx=\"6\"/>")
                    .append("<text class=\"sv-title\" x=\"").append(b.x + 8).append("\" y=\"").append(tb.y + 17)
                    .append("\">").append(esc(t.table().name)).append("</text>");
            for (int ci = 0; ci < t.table().columns.size(); ci++) {
                Column c = t.table().columns.get(ci);
                Box row = t.columnRows().get(ci);
                out.append("<text class=\"").append(c.isPrimaryKey ? "sv-pk" : "sv-col")
                        .append("\" x=\"").append(row.x + 8).append("\" y=\"").append(row.y + 17)
                        .append("\">").append(esc(c.name)).append(' ').append(esc(c.type)).append("</text>");
            }
            out.append("</g>");
        }
        out.append("</svg>");
        return out.toString();
    }

    static Result ddlToSvg(String ddl) {
        Schema schema = parseDdl(ddl);
        return new Result(renderSvg(layoutSchema(schema)), schema);
    }

    // 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/… ports (integer geometry, same defaults).
}