ER/Schema Visualizer — PHP source

<?php
// schema-visualizer — pure CREATE TABLE DDL → layered ER diagram as SVG.
// PHP 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.

const SV_LAYOUT = ['rowHeight' => 24, 'charWidth' => 7, 'padding' => 8, 'layerGap' => 60, 'columnGap' => 40];
const SV_QUOTES = ["'" => "'", '"' => '"', '`' => '`', '[' => ']'];

function sv_round(float $v): int { return (int)($v + 0.5); } // half-up, JS parity

function sv_upper(string $s): string { return strtoupper($s); }

/** Split on `;` outside strings/quoted identifiers (depth-agnostic: an
 *  unterminated paren cannot swallow the statements after it). */
function sv_split_statements(string $ddl): array
{
    $out = [];
    $cur = '';
    $i = 0;
    $n = strlen($ddl);
    while ($i < $n) {
        $ch = $ddl[$i];
        if (isset(SV_QUOTES[$ch])) {
            $close = SV_QUOTES[$ch];
            $cur .= $ch;
            $i++;
            while ($i < $n) {
                $cur .= $ddl[$i];
                if ($ddl[$i] === $close) {
                    if ($close === "'" && $i + 1 < $n && $ddl[$i + 1] === "'") {
                        $cur .= $ddl[$i + 1];
                        $i += 2;
                        continue;
                    }
                    break;
                }
                $i++;
            }
            $i++;
            continue;
        }
        if ($ch === ';') { $out[] = $cur; $cur = ''; $i++; continue; }
        $cur .= $ch;
        $i++;
    }
    if (trim($cur) !== '') $out[] = $cur;
    return $out;
}

/** Tokens: [kind, text] — qident/string carry text with quotes stripped. */
function sv_tokenize(string $s): array
{
    $toks = [];
    $i = 0;
    $n = strlen($s);
    while ($i < $n) {
        $ch = $s[$i];
        if (ctype_space($ch)) { $i++; continue; }
        if (isset(SV_QUOTES[$ch])) {
            $close = SV_QUOTES[$ch];
            $text = '';
            $i++;
            while ($i < $n) {
                if ($s[$i] === $close) {
                    if ($close === "'" && $i + 1 < $n && $s[$i + 1] === "'") {
                        $text .= "'";
                        $i += 2;
                        continue;
                    }
                    break;
                }
                $text .= $s[$i++];
            }
            $i++;
            $toks[] = [$ch === "'" ? 'string' : 'qident', $text];
            continue;
        }
        if (strpos('(),.', $ch) !== false) { $toks[] = ['punct', $ch]; $i++; continue; }
        $j = $i;
        while ($j < $n && !ctype_space($s[$j]) && strpos("',().`[]", $s[$j]) === false) $j++;
        $toks[] = ['word', substr($s, $i, $j - $i)];
        $i = $j;
    }
    return $toks;
}

function sv_is_p(?array $t, string $p): bool
{
    return $t !== null && $t[0] === 'punct' && $t[1] === $p;
}

function sv_kw(?array $t, string $w): bool
{
    return $t !== null && $t[0] === 'word' && sv_upper($t[1]) === $w;
}

function sv_g(array $toks, int $i): ?array
{
    return $i < count($toks) ? $toks[$i] : null;
}

function sv_take_name(array $toks, int $i): ?array
{
    $first = sv_g($toks, $i);
    if ($first === null || ($first[0] !== 'qident' && $first[0] !== 'word')) return null;
    $name = $first[1];
    $j = $i + 1;
    while (sv_is_p(sv_g($toks, $j), '.') && sv_g($toks, $j + 1) !== null
        && (sv_g($toks, $j + 1)[0] === 'qident' || sv_g($toks, $j + 1)[0] === 'word')) {
        $name .= '.' . $toks[$j + 1][1];
        $j += 2;
    }
    return [$name, $j];
}

function sv_paren_list(array $toks, int $i): ?array
{
    if (!sv_is_p(sv_g($toks, $i), '(')) return null;
    $names = [];
    $j = $i + 1;
    for (;;) {
        $name = sv_take_name($toks, $j);
        if ($name === null) return null;
        $names[] = $name[0];
        $j = $name[1];
        if (sv_is_p(sv_g($toks, $j), ',')) { $j++; continue; }
        if (sv_is_p(sv_g($toks, $j), ')')) return [$names, $j + 1];
        return null;
    }
}

function sv_is_modifier(array $t): bool
{
    static $mods = ['NOT', 'NULL', 'PRIMARY', 'KEY', 'UNIQUE', 'DEFAULT', 'REFERENCES',
        'AUTO_INCREMENT', 'AUTOINCREMENT', 'ON', 'COMMENT', 'CHECK', 'CONSTRAINT'];
    return $t[0] === 'word' && in_array(sv_upper($t[1]), $mods, true);
}

function sv_join_type(array $toks): string
{
    $raw = implode(' ', array_map(fn ($t) => $t[1], $toks));
    $raw = preg_replace('/\s*\(\s*/', '(', $raw);
    $raw = preg_replace('/\s*\)\s*/', ')', $raw);
    $raw = preg_replace('/\s*,\s*/', ',', $raw);
    return sv_upper(trim($raw));
}

function sv_parse_column(array $line, string $tableName, array &$fks): ?array
{
    $name = sv_take_name($line, 0);
    if ($name === null) return null;
    $i = $name[1];
    $typeToks = [];
    while ($i < count($line) && !sv_is_modifier($line[$i])) $typeToks[] = $line[$i++];
    $nullable = true;
    $pk = false;
    while ($i < count($line)) {
        $t = $line[$i];
        if (sv_kw($t, 'NOT') && sv_kw(sv_g($line, $i + 1), 'NULL')) { $nullable = false; $i += 2; continue; }
        if (sv_kw($t, 'NULL')) { $i++; continue; }
        if (sv_kw($t, 'PRIMARY') && sv_kw(sv_g($line, $i + 1), 'KEY')) { $pk = true; $nullable = false; $i += 2; continue; }
        if (sv_kw($t, 'UNIQUE') || sv_kw($t, 'AUTO_INCREMENT') || sv_kw($t, 'AUTOINCREMENT')) { $i++; continue; }
        if (sv_kw($t, 'DEFAULT')) {
            $i++;
            if (sv_is_p(sv_g($line, $i), '(')) {
                $depth = 0;
                while ($i < count($line)) {
                    if (sv_is_p($line[$i], '(')) $depth++;
                    if (sv_is_p($line[$i], ')')) $depth--;
                    $i++;
                    if ($depth === 0) break;
                }
            } elseif ($i < count($line)) $i++;
            continue;
        }
        if (sv_kw($t, 'COMMENT')) {
            $i++;
            if (sv_g($line, $i) !== null && $line[$i][0] === 'string') $i++;
            continue;
        }
        if (sv_kw($t, 'ON')) {
            $i += 2;
            if (sv_kw(sv_g($line, $i), 'SET') || sv_kw(sv_g($line, $i), 'NO')) $i += 2;
            elseif ($i < count($line)) $i++;
            continue;
        }
        if (sv_kw($t, 'REFERENCES')) {
            $i++;
            $target = sv_take_name($line, $i);
            if ($target !== null) {
                $i = $target[1];
                $toCol = null;
                if (sv_is_p(sv_g($line, $i), '(')) {
                    $list = sv_paren_list($line, $i);
                    if ($list !== null) { $toCol = $list[0][0]; $i = $list[1]; }
                }
                $fks[] = ['fromTable' => $tableName, 'fromColumn' => $name[0],
                    'toTable' => $target[0], 'toColumn' => $toCol];
            }
            continue;
        }
        $i++; // unknown modifier tolerated
    }
    return ['name' => $name[0], 'type' => sv_join_type($typeToks), 'nullable' => $nullable, 'isPrimaryKey' => $pk];
}

function sv_parse_ddl(string $ddl): array
{
    if (trim($ddl) === '') return ['tables' => [], 'foreignKeys' => [], 'notes' => ['No DDL input.']];
    $tables = [];
    $fks = [];
    $notes = [];
    foreach (sv_split_statements($ddl) as $stmt) {
        if (trim($stmt) === '') continue;
        $toks = sv_tokenize($stmt);
        try {
            $i = 0;
            if (!sv_kw(sv_g($toks, $i), 'CREATE')) throw new RuntimeException('bad');
            $i++;
            while (sv_kw(sv_g($toks, $i), 'TEMP') || sv_kw(sv_g($toks, $i), 'TEMPORARY') || sv_kw(sv_g($toks, $i), 'UNLOGGED')) $i++;
            if (!sv_kw(sv_g($toks, $i), 'TABLE')) {
                $notes[] = 'Skipped non-table statement.';
                continue;
            }
            $i++;
            if (sv_kw(sv_g($toks, $i), 'IF') && sv_kw(sv_g($toks, $i + 1), 'NOT') && sv_kw(sv_g($toks, $i + 2), 'EXISTS')) $i += 3;
            $name = sv_take_name($toks, $i);
            if ($name === null || !sv_is_p(sv_g($toks, $name[1]), '(')) throw new RuntimeException('bad');
            $i = $name[1] + 1;
            $body = [];
            $depth = 0;
            for (; $i < count($toks); $i++) {
                if (sv_is_p($toks[$i], '(')) $depth++;
                if (sv_is_p($toks[$i], ')')) {
                    if ($depth === 0) break;
                    $depth--;
                }
                $body[] = $toks[$i];
            }
            if ($i >= count($toks)) throw new RuntimeException('bad');
            $lines = [];
            $line = [];
            $depth = 0;
            foreach ($body as $t) {
                if (sv_is_p($t, '(')) $depth++;
                if (sv_is_p($t, ')')) $depth--;
                if (sv_is_p($t, ',') && $depth === 0) { $lines[] = $line; $line = []; continue; }
                $line[] = $t;
            }
            if ($line) $lines[] = $line;
            $table = ['name' => $name[0], 'columns' => []];
            $tables[] = &$table;
            unset($table);
            $tidx = count($tables) - 1;
            foreach ($lines as $t2) {
                if (!$t2) continue;
                $first = $t2[0];
                $u = $first[0] === 'word' ? sv_upper($first[1]) : '';
                if ($u === 'PRIMARY' && sv_kw(sv_g($t2, 1), 'KEY')) {
                    $list = sv_paren_list($t2, 2);
                    if ($list) {
                        foreach ($list[0] as $cn) {
                            foreach ($tables[$tidx]['columns'] as &$col) {
                                if ($col['name'] === $cn) { $col['isPrimaryKey'] = true; $col['nullable'] = false; }
                            }
                            unset($col);
                        }
                    }
                    continue;
                }
                if ($u === 'FOREIGN' && sv_kw(sv_g($t2, 1), 'KEY')) {
                    $from = sv_paren_list($t2, 2);
                    if ($from !== null && sv_kw(sv_g($t2, $from[1]), 'REFERENCES')) {
                        $target = sv_take_name($t2, $from[1] + 1);
                        if ($target !== null) {
                            $toCols = null;
                            if (sv_is_p(sv_g($t2, $target[1]), '(')) {
                                $to = sv_paren_list($t2, $target[1]);
                                if ($to !== null) $toCols = $to[0];
                            }
                            foreach ($from[0] as $idx => $fc) {
                                $toCol = null;
                                if ($toCols !== null) {
                                    $toCol = $idx < count($toCols) ? $toCols[$idx] : $toCols[count($toCols) - 1];
                                }
                                $fks[] = ['fromTable' => $tables[$tidx]['name'], 'fromColumn' => $fc,
                                    'toTable' => $target[0], 'toColumn' => $toCol];
                            }
                        }
                    }
                    continue;
                }
                if (in_array($u, ['UNIQUE', 'KEY', 'INDEX', 'CHECK', 'EXCLUDE', 'CONSTRAINT'], true)) continue;
                $col = sv_parse_column($t2, $tables[$tidx]['name'], $fks);
                if ($col !== null) $tables[$tidx]['columns'][] = $col;
            }
        } catch (RuntimeException $e) {
            $notes[] = 'Skipped unparseable statement.';
        }
    }
    $foreignKeys = array_map(function ($fk) use ($tables) {
        if ($fk['toColumn'] !== null) return $fk;
        $target = null;
        foreach ($tables as $t) if ($t['name'] === $fk['toTable']) { $target = $t; break; }
        $pk = null;
        if ($target !== null) foreach ($target['columns'] as $c) if ($c['isPrimaryKey']) { $pk = $c; break; }
        $fk['toColumn'] = $pk !== null ? $pk['name'] : 'id';
        return $fk;
    }, $fks);
    return ['tables' => $tables, 'foreignKeys' => $foreignKeys, 'notes' => $notes];
}

function sv_layout_schema(array $schema): array
{
    $o = SV_LAYOUT;
    if (!count($schema['tables'])) return ['width' => 0, 'height' => 0, 'tables' => [], 'edges' => []];
    $index = [];
    foreach ($schema['tables'] as $i => $t) if (!isset($index[$t['name']])) $index[$t['name']] = $i;
    $boxes = [];
    foreach ($schema['tables'] as $t) {
        $lens = [mb_strlen($t['name'])];
        foreach ($t['columns'] as $c) $lens[] = mb_strlen($c['name'] . ' ' . $c['type']);
        $lens[] = 1;
        $boxes[] = ['x' => 0, 'y' => 0,
            'w' => sv_round(max($lens) * $o['charWidth'] + 2 * $o['padding']),
            'h' => sv_round($o['rowHeight'] * (1 + count($t['columns'])) + $o['padding'])];
    }
    $layerOf = array_fill(0, count($schema['tables']), 0);
    for ($pass = 0; $pass < count($schema['tables']); $pass++) {
        $changed = false;
        foreach ($schema['foreignKeys'] as $fk) {
            $ti = $index[$fk['fromTable']] ?? null;
            $tj = $index[$fk['toTable']] ?? null;
            if ($ti === null || $tj === null || $ti === $tj) continue;
            if ($layerOf[$ti] < $layerOf[$tj] + 1) { $layerOf[$ti] = $layerOf[$tj] + 1; $changed = true; }
        }
        if (!$changed) break;
    }
    $layers = [];
    foreach ($layerOf as $i => $l) $layers[$l][] = $i;
    ksort($layers);
    $y = 0; $width = 0; $height = 0;
    foreach ($layers as $layer) {
        $x = 0; $layerH = 0;
        foreach ($layer as $i) {
            $boxes[$i]['x'] = $x;
            $boxes[$i]['y'] = $y;
            $x += $boxes[$i]['w'] + $o['columnGap'];
            $layerH = max($layerH, $boxes[$i]['h']);
        }
        $width = max($width, $x - $o['columnGap']);
        $height = max($height, $y + $layerH);
        $y += $layerH + $o['layerGap'];
    }
    $edges = [];
    foreach ($schema['foreignKeys'] as $fk) {
        $frm = $index[$fk['fromTable']] ?? null;
        $to = $index[$fk['toTable']] ?? null;
        if ($frm === null || $to === null) continue;
        $x1 = $boxes[$to]['x'] + sv_round($boxes[$to]['w'] / 2);
        $y1 = $boxes[$to]['y'] + $boxes[$to]['h'];
        $x2 = $boxes[$frm]['x'] + sv_round($boxes[$frm]['w'] / 2);
        $y2 = $boxes[$frm]['y'];
        $midY = sv_round(($y1 + $y2) / 2);
        $edges[] = ['fk' => $fk, 'path' => "M $x1 $y1 V $midY H $x2 V $y2",
            'label' => $fk['fromColumn'] . ' → ' . $fk['toColumn']];
    }
    $laid = [];
    foreach ($schema['tables'] as $i => $t) {
        $rows = [];
        foreach ($t['columns'] as $ci => $_) {
            $rows[] = ['x' => $boxes[$i]['x'], 'y' => $boxes[$i]['y'] + $o['rowHeight'] * (1 + $ci),
                'w' => $boxes[$i]['w'], 'h' => $o['rowHeight']];
        }
        $laid[] = ['table' => $t, 'box' => $boxes[$i],
            'titleBar' => ['x' => $boxes[$i]['x'], 'y' => $boxes[$i]['y'], 'w' => $boxes[$i]['w'], 'h' => $o['rowHeight']],
            'columnRows' => $rows];
    }
    return ['width' => $width, 'height' => $height, 'tables' => $laid, 'edges' => $edges];
}

function sv_esc(string $s): string
{
    return htmlspecialchars($s, ENT_QUOTES | ENT_XML1, 'UTF-8', false);
}

function sv_render_svg(array $geo): string
{
    $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>';
    $boxOf = [];
    foreach ($geo['tables'] as $t) if (!isset($boxOf[$t['table']['name']])) $boxOf[$t['table']['name']] = $t['box'];
    foreach ($geo['edges'] as $e) {
        $frm = $boxOf[$e['fk']['fromTable']] ?? null;
        if ($frm === null) continue;
        $ax = $frm['x'] + sv_round($frm['w'] / 2);
        $out .= '<path class="sv-edge" d="' . $e['path'] . '"/>'
            . '<polygon class="sv-arrow" points="' . ($ax - 5) . ',' . ($frm['y'] - 8) . ' '
            . ($ax + 5) . ',' . ($frm['y'] - 8) . ' ' . $ax . ',' . $frm['y'] . '"/>';
    }
    foreach ($geo['tables'] as $t) {
        $b = $t['box'];
        $tb = $t['titleBar'];
        $out .= '<g class="sv-table"><rect class="sv-box" x="' . $b['x'] . '" y="' . $b['y']
            . '" width="' . $b['w'] . '" height="' . $b['h'] . '" rx="6"/>'
            . '<rect class="sv-titlebar" x="' . $tb['x'] . '" y="' . $tb['y'] . '" width="' . $tb['w']
            . '" height="' . $tb['h'] . '" rx="6"/>'
            . '<text class="sv-title" x="' . ($b['x'] + 8) . '" y="' . ($tb['y'] + 17) . '">'
            . sv_esc($t['table']['name']) . '</text>';
        foreach ($t['table']['columns'] as $ci => $c) {
            $row = $t['columnRows'][$ci];
            $cls = $c['isPrimaryKey'] ? 'sv-pk' : 'sv-col';
            $out .= '<text class="' . $cls . '" x="' . ($row['x'] + 8) . '" y="' . ($row['y'] + 17) . '">'
                . sv_esc($c['name']) . ' ' . sv_esc($c['type']) . '</text>';
        }
        $out .= '</g>';
    }
    return $out . '</svg>';
}

function sv_ddl_to_svg(string $ddl): array
{
    $schema = sv_parse_ddl($ddl);
    return ['svg' => sv_render_svg(sv_layout_schema($schema)), 'schema' => $schema];
}

// Example:
//   sv_ddl_to_svg('CREATE TABLE users (id INT PRIMARY KEY);' .
//                 'CREATE TABLE posts (id INT PRIMARY KEY, user_id INT REFERENCES users(id), title TEXT);')['svg']
// → users box on layer 0, posts below, one FK edge — byte-identical to the
//   TS/Go/… ports (integer geometry, same defaults).