ER/Schema Visualizer — JavaScript source

// schema-visualizer — pure CREATE TABLE DDL → layered ER diagram as SVG.
// JavaScript 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, so every port draws the identical diagram.
'use strict';

const LAYOUT = { rowHeight: 24, charWidth: 7, padding: 8, layerGap: 60, columnGap: 40 };
const MODIFIERS = new Set(['NOT', 'NULL', 'PRIMARY', 'KEY', 'UNIQUE', 'DEFAULT', 'REFERENCES',
  'AUTO_INCREMENT', 'AUTOINCREMENT', 'ON', 'COMMENT', 'CHECK', 'CONSTRAINT']);

function splitStatements(ddl) {
  const out = [];
  let cur = '';
  let i = 0;
  while (i < ddl.length) {
    const ch = ddl[i];
    if (ch === "'" || ch === '"' || ch === '`' || ch === '[') {
      const close = ch === '[' ? ']' : ch;
      cur += ch; i++;
      while (i < ddl.length) {
        cur += ddl[i];
        if (ddl[i] === close) {
          if (close === "'" && ddl[i + 1] === "'") { cur += ddl[i + 1]; i += 2; continue; }
          break;
        }
        i++;
      }
      i++; continue;
    }
    if (ch === ';') { out.push(cur); cur = ''; i++; continue; }
    cur += ch; i++;
  }
  if (cur.trim()) out.push(cur);
  return out;
}

function tokenize(s) {
  const toks = [];
  let i = 0;
  while (i < s.length) {
    const ch = s[i];
    if (/\s/.test(ch)) { i++; continue; }
    if (ch === "'" || ch === '"' || ch === '`' || ch === '[') {
      const close = ch === '[' ? ']' : ch;
      let text = ''; i++;
      while (i < s.length) {
        if (s[i] === close) {
          if (close === "'" && s[i + 1] === "'") { text += "'"; i += 2; continue; }
          break;
        }
        text += s[i++];
      }
      i++;
      toks.push({ text, kind: ch === "'" ? 'string' : 'qident' });
      continue;
    }
    if ('(),.'.includes(ch)) { toks.push({ text: ch, kind: 'punct' }); i++; continue; }
    let word = '';
    while (i < s.length && !/[\s"',().`\[\]]/.test(s[i])) word += s[i++];
    toks.push({ text: word, kind: 'word' });
  }
  return toks;
}

const isP = (t, p) => !!t && t.kind === 'punct' && t.text === p;
const kw = (t, w) => !!t && t.kind === 'word' && t.text.toUpperCase() === w;

function takeName(toks, i) {
  const first = toks[i];
  if (!first || (first.kind !== 'qident' && first.kind !== 'word')) return null;
  let name = first.text, j = i + 1;
  while (isP(toks[j], '.') && toks[j + 1] && (toks[j + 1].kind === 'qident' || toks[j + 1].kind === 'word')) {
    name += '.' + toks[j + 1].text; j += 2;
  }
  return [name, j];
}

function parenList(toks, i) {
  if (!isP(toks[i], '(')) return null;
  const names = []; let j = i + 1;
  for (;;) {
    const n = takeName(toks, j);
    if (!n) return null;
    names.push(n[0]); j = n[1];
    if (isP(toks[j], ',')) { j++; continue; }
    if (isP(toks[j], ')')) return [names, j + 1];
    return null;
  }
}

function parseColumn(line, table, fks) {
  const name = takeName(line, 0);
  if (!name) return;
  let i = name[1];
  const typeToks = [];
  while (i < line.length && !(line[i].kind === 'word' && MODIFIERS.has(line[i].text.toUpperCase()))) {
    typeToks.push(line[i++]);
  }
  const type = typeToks.map((t) => t.text).join(' ')
    .replace(/\s*\(\s*/g, '(').replace(/\s*\)\s*/g, ')').replace(/\s*,\s*/g, ',')
    .trim().toUpperCase();
  let nullable = true, pk = false;
  while (i < line.length) {
    const t = line[i];
    if (kw(t, 'NOT') && kw(line[i + 1], 'NULL')) { nullable = false; i += 2; continue; }
    if (kw(t, 'NULL')) { i++; continue; }
    if (kw(t, 'PRIMARY') && kw(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(line[i], '(')) { let d = 0; do { if (isP(line[i], '(')) d++; if (isP(line[i], ')')) d--; i++; } while (i < line.length && d > 0); }
      else if (line[i]) i++;
      continue;
    }
    if (kw(t, 'COMMENT')) { i++; if (line[i] && line[i].kind === 'string') i++; continue; }
    if (kw(t, 'ON')) {
      i += 2;
      if (kw(line[i], 'SET') || kw(line[i], 'NO')) i += 2;
      else if (line[i]) i++;
      continue;
    }
    if (kw(t, 'REFERENCES')) {
      i++;
      const target = takeName(line, i);
      if (target) {
        i = target[1];
        let toCol = null;
        if (isP(line[i], '(')) { const list = parenList(line, i); if (list) { toCol = list[0][0]; i = list[1]; } }
        fks.push({ fromTable: table, fromColumn: name[0], toTable: target[0], toColumn: toCol });
      }
      continue;
    }
    i++; // unknown modifier tolerated
  }
  return { name: name[0], type, nullable, isPrimaryKey: pk };
}

function parseDdl(ddl) {
  if (!ddl.trim()) return { tables: [], foreignKeys: [], notes: ['No DDL input.'] };
  const tables = [], fks = [], notes = [];
  for (const stmt of splitStatements(ddl)) {
    if (!stmt.trim()) continue;
    const toks = tokenize(stmt);
    try {
      let i = 0;
      if (!kw(toks[i], 'CREATE')) throw 0;
      i++;
      while (kw(toks[i], 'TEMP') || kw(toks[i], 'TEMPORARY') || kw(toks[i], 'UNLOGGED')) i++;
      if (!kw(toks[i], 'TABLE')) { notes.push('Skipped non-table statement.'); continue; }
      i++;
      if (kw(toks[i], 'IF') && kw(toks[i + 1], 'NOT') && kw(toks[i + 2], 'EXISTS')) i += 3;
      const name = takeName(toks, i);
      if (!name || !isP(toks[name[1]], '(')) throw 0;
      i = name[1] + 1;
      // body = tokens until the matching close paren (depth-aware)
      const body = []; let depth = 0;
      for (; i < toks.length; i++) {
        if (isP(toks[i], '(')) depth++;
        if (isP(toks[i], ')')) { if (depth === 0) break; depth--; }
        body.push(toks[i]);
      }
      if (i >= toks.length) throw 0;
      // split body into top-level comma lines
      const lines = []; let line = [];
      for (const t of body) {
        if (isP(t, '(')) depth++;
        if (isP(t, ')')) depth--;
        if (isP(t, ',') && depth === 0) { lines.push(line); line = []; continue; }
        line.push(t);
      }
      if (line.length) lines.push(line);
      const table = { name: name[0], columns: [] };
      tables.push(table);
      for (const toks2 of lines) {
        if (!toks2.length) continue;
        const first = toks2[0];
        const U = first.kind === 'word' ? first.text.toUpperCase() : '';
        if (U === 'PRIMARY' && kw(toks2[1], 'KEY')) {
          const list = parenList(toks2, 2);
          if (list) for (const cn of list[0]) {
            const col = table.columns.find((c) => c.name === cn);
            if (col) { col.isPrimaryKey = true; col.nullable = false; }
          }
          continue;
        }
        if (U === 'FOREIGN' && kw(toks2[1], 'KEY')) {
          const from = parenList(toks2, 2);
          if (from && kw(toks2[from[1]], 'REFERENCES')) {
            const target = takeName(toks2, from[1] + 1);
            if (target) {
              let toCols = null;
              if (isP(toks2[target[1]], '(')) { const to = parenList(toks2, target[1]); if (to) toCols = to[0]; }
              from[0].forEach((fc, idx) => fks.push({
                fromTable: table.name, fromColumn: fc, toTable: target[0],
                toColumn: toCols ? (toCols[idx] ?? toCols[toCols.length - 1]) : null,
              }));
            }
          }
          continue;
        }
        if (['UNIQUE', 'KEY', 'INDEX', 'CHECK', 'EXCLUDE', 'CONSTRAINT'].includes(U)) continue;
        const col = parseColumn(toks2, table.name, fks);
        if (col) table.columns.push(col);
      }
    } catch { notes.push('Skipped unparseable statement.'); }
  }
  // resolve omitted FK target columns to the referenced table's first PK
  const foreignKeys = fks.map((fk) => {
    if (fk.toColumn) return fk;
    const target = tables.find((t) => t.name === fk.toTable);
    return { ...fk, toColumn: target?.columns.find((c) => c.isPrimaryKey)?.name ?? 'id' };
  });
  return { tables, foreignKeys, notes };
}

function layoutSchema(schema) {
  const o = LAYOUT;
  if (!schema.tables.length) return { width: 0, height: 0, tables: [], edges: [] };
  const index = new Map();
  schema.tables.forEach((t, i) => { if (!index.has(t.name)) index.set(t.name, i); });
  const boxes = schema.tables.map((t) => {
    const textLen = Math.max(t.name.length, ...t.columns.map((c) => `${c.name} ${c.type}`.length), 1);
    return { x: 0, y: 0, w: Math.round(textLen * o.charWidth + 2 * o.padding),
      h: Math.round(o.rowHeight * (1 + t.columns.length) + o.padding) };
  });
  const layerOf = schema.tables.map(() => 0);
  for (let pass = 0; pass < schema.tables.length; pass++) {
    let changed = false;
    for (const fk of schema.foreignKeys) {
      const ti = index.get(fk.fromTable), tj = index.get(fk.toTable);
      if (ti === undefined || tj === undefined || ti === tj) continue;
      if (layerOf[ti] < layerOf[tj] + 1) { layerOf[ti] = layerOf[tj] + 1; changed = true; }
    }
    if (!changed) break;
  }
  const layers = [];
  schema.tables.forEach((_, i) => (layers[layerOf[i]] ??= []).push(i));
  let y = 0, width = 0, height = 0;
  for (const layer of layers) {
    let x = 0, layerH = 0;
    for (const i of layer) {
      boxes[i].x = x; boxes[i].y = y;
      x += boxes[i].w + o.columnGap;
      layerH = Math.max(layerH, boxes[i].h);
    }
    width = Math.max(width, x - o.columnGap);
    height = Math.max(height, y + layerH);
    y += layerH + o.layerGap;
  }
  const edges = [];
  for (const fk of schema.foreignKeys) {
    const from = boxes[index.get(fk.fromTable)], to = boxes[index.get(fk.toTable)];
    if (!from || !to) continue;
    const x1 = to.x + Math.round(to.w / 2), y1 = to.y + to.h;
    const x2 = from.x + Math.round(from.w / 2), y2 = from.y;
    const midY = Math.round((y1 + y2) / 2);
    edges.push({ fk, path: `M ${x1} ${y1} V ${midY} H ${x2} V ${y2}`, label: `${fk.fromColumn} → ${fk.toColumn}` });
  }
  return {
    width, height, edges,
    tables: schema.tables.map((table, i) => ({
      table, box: boxes[i],
      titleBar: { x: boxes[i].x, y: boxes[i].y, w: boxes[i].w, h: o.rowHeight },
      columnRows: table.columns.map((_, ci) => ({
        x: boxes[i].x, y: boxes[i].y + o.rowHeight * (1 + ci), w: boxes[i].w, h: o.rowHeight })),
    })),
  };
}

const esc = (s) => s.replace(/[&<>"]/g, (c) => ({ '&': '&amp;', '<': '&lt;', '>': '&gt;', '"': '&quot;' }[c]));

function renderSvg(geo) {
  const out = [`<svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 ${geo.width} ${geo.height}" class="sv-root" role="img"><title>Schema diagram</title>`];
  const boxOf = new Map(geo.tables.map((t) => [t.table.name, t.box]));
  for (const e of geo.edges) {
    const from = boxOf.get(e.fk.fromTable);
    if (!from) continue;
    const ax = from.x + Math.round(from.w / 2);
    out.push(`<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}"/>`);
  }
  for (const t of geo.tables) {
    out.push(`<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>`);
    t.table.columns.forEach((c, ci) => {
      out.push(`<text class="${c.isPrimaryKey ? 'sv-pk' : 'sv-col'}" x="${t.box.x + 8}" y="${t.columnRows[ci].y + 17}">${esc(c.name)} ${esc(c.type)}</text>`);
    });
    out.push('</g>');
  }
  out.push('</svg>');
  return out.join('');
}

function ddlToSvg(ddl) {
  const schema = parseDdl(ddl);
  return { svg: 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).