SQL Playground — PHP source

<?php
/**
 * sql-playground — polyglot showcase port (PHP)
 *
 * Pure helper logic for the CosmoDev "SQL Playground" tool: splitting a SQL
 * script into statements, classifying read-only ones, serializing result sets
 * to CSV / Markdown / JSON, and extracting a schema summary from CREATE TABLE
 * scripts.
 *
 * Ported from src/lib/sql-playground.ts (the canonical TypeScript lib that
 * powers the live tool). Functionally equivalent: same inputs -> same outputs.
 *
 * Display source — part of CosmoDev's polyglot tool pages.
 *
 * NOTE on value types: PHP only has int/float/string/bool/null/array/object.
 * There is no native distinction between a "row array" and an "object", so
 * cells are passed as plain PHP values (int|float|string|bool|null|array).
 * Arrays/objects are JSON-encoded for display, mirroring the TypeScript
 * behaviour where any non-primitive becomes JSON.
 */

namespace CosmoDev\SqlPlayground;

/**
 * Split a SQL script into top-level statements on ';', respecting single-quote
 * string literals. A doubled '' inside a string is an escaped literal quote
 * (SQL standard) and does not end the string. Whitespace-only statements are
 * dropped.
 *
 * @param string $sql
 * @return string[] Trimmed, non-empty statements.
 */
function split_statements(string $sql): array
{
    $stmts = [];
    $current = '';
    $inString = false;
    $len = strlen($sql);

    for ($i = 0; $i < $len; $i++) {
        $ch = $sql[$i];

        if ($inString) {
            $current .= $ch;
            if ($ch === "'") {
                if ($i + 1 < $len && $sql[$i + 1] === "'") {
                    // Escaped literal quote: consume both chars.
                    $current .= $sql[$i + 1];
                    $i++;
                } else {
                    $inString = false;
                }
            }
        } elseif ($ch === "'") {
            $inString = true;
            $current .= $ch;
        } elseif ($ch === ';') {
            $trimmed = trim($current);
            if ($trimmed !== '') {
                $stmts[] = $trimmed;
            }
            $current = '';
        } else {
            $current .= $ch;
        }
    }

    // Flush any trailing statement that wasn't terminated by ';'.
    $trimmed = trim($current);
    if ($trimmed !== '') {
        $stmts[] = $trimmed;
    }

    return $stmts;
}

/**
 * True when the statement is read-only (safe to run against a snapshot without
 * mutating state). Recognises the common query-leading keywords: SELECT,
 * WITH, VALUES, EXPLAIN, PRAGMA.
 */
function is_read_only_statement(string $sql): bool
{
    return (bool) preg_match('/^(SELECT|WITH|VALUES|EXPLAIN|PRAGMA)\b/i', ltrim($sql));
}

/**
 * Render one cell value as the textual form used in tables/CSV:
 * - null           -> "NULL"
 * - bool/int/float -> native string form
 * - string         -> verbatim
 * - array/object   -> JSON
 *
 * @param mixed $v
 */
function format_scalar($v): string
{
    if ($v === null) {
        return 'NULL';
    }
    if (is_bool($v)) {
        return $v ? 'true' : 'false';
    }
    if (is_int($v) || is_float($v)) {
        // PHP renders floats like 1.0 as "1", matching JS Number->String for
        // whole numbers; for non-whole floats it produces shortest form too.
        return (string) $v;
    }
    if (is_string($v)) {
        return $v;
    }
    // Arrays / objects -> JSON. JSON_THROW_ON_ERROR would change the return
    // shape on failure; mirror the TS path which always succeeds for arrays.
    $encoded = json_encode($v);
    return $encoded === false ? 'NULL' : $encoded;
}

/**
 * Quote one cell per RFC-4180-ish CSV: if the formatted value contains a
 * comma, double quote, or newline, wrap it in double quotes and double any
 * embedded double quotes.
 *
 * @param mixed $v
 */
function csv_field($v): string
{
    $s = format_scalar($v);
    if (preg_match('/[,"\n\r]/', $s) === 1) {
        return '"' . str_replace('"', '""', $s) . '"';
    }
    return $s;
}

/**
 * Serialize a result set to CSV (header row + one row per record, trailing LF).
 *
 * @param string[] $columns
 * @param array<array<mixed>> $rows
 */
function rows_to_csv(array $columns, array $rows): string
{
    $lines = [implode(',', array_map(__NAMESPACE__ . '\\csv_field', $columns))];
    foreach ($rows as $row) {
        $lines[] = implode(',', array_map(__NAMESPACE__ . '\\csv_field', $row));
    }
    return implode("\n", $lines) . "\n";
}

/**
 * Escape a pipe so it doesn't break the Markdown table layout.
 */
function md_escape(string $s): string
{
    return str_replace('|', '\\|', $s);
}

/**
 * Serialize a result set as a GitHub-flavored Markdown table.
 *
 * @param string[] $columns
 * @param array<array<mixed>> $rows
 */
function rows_to_markdown(array $columns, array $rows): string
{
    $header = '| ' . implode(' | ', array_map(__NAMESPACE__ . '\\md_escape', $columns)) . ' |';
    $sep    = '| ' . implode(' | ', array_fill(0, count($columns), '---')) . ' |';

    $lines = [$header, $sep];
    foreach ($rows as $row) {
        $cells = [];
        foreach ($row as $cell) {
            $cells[] = md_escape(format_scalar($cell));
        }
        $lines[] = '| ' . implode(' | ', $cells) . ' |';
    }
    return implode("\n", $lines) . "\n";
}

/**
 * Serialize a result set as a pretty-printed JSON array of objects keyed by
 * column name. Missing cells (row shorter than columns) become null.
 *
 * @param string[] $columns
 * @param array<array<mixed>> $rows
 */
function rows_to_json(array $columns, array $rows): string
{
    $out = [];
    foreach ($rows as $row) {
        $obj = [];
        foreach ($columns as $i => $col) {
            // ?? null mirrors the TS `row[i] ?? null`: a missing or null cell
            // serialises as JSON null.
            $obj[$col] = array_key_exists($i, $row) ? $row[$i] : null;
        }
        $out[] = $obj;
    }
    // JSON_PRETTY_PRINT = 2-space indent, matching JSON.stringify(arr, null, 2).
    // JSON_UNESCAPED_SLASHES keeps paths like "a/b" readable (TS leaves them).
    return json_encode($out, JSON_PRETTY_PRINT | JSON_UNESCAPED_SLASHES);
}

/**
 * Pull the column name (lower-cased) from a single CREATE TABLE column
 * definition. Returns null for table-level constraint lines (PRIMARY KEY,
 * FOREIGN KEY, UNIQUE, CHECK, CONSTRAINT), which are not columns.
 *
 * @return string|null
 */
function extract_column_name(string $def): ?string
{
    $def = trim($def);
    if ($def === '') {
        return null;
    }
    if (preg_match('/^(PRIMARY\s+KEY|FOREIGN\s+KEY|UNIQUE|CHECK|CONSTRAINT)\b/i', $def) === 1) {
        return null;
    }
    if (preg_match('/^["\'`]?(\w+)["\'`]?/', $def, $m) === 1) {
        return strtolower($m[1]);
    }
    return null;
}

/**
 * Best-effort extraction of `['table' => name, 'columns' => [...]]` summaries
 * from a CREATE TABLE script. Handles IF NOT EXISTS, quoted identifiers, and
 * parenthesised type/constraint bodies (depth-tracked so a `,` inside
 * `NUMERIC(10,2)` doesn't split a column). Returns an empty array on any
 * error.
 *
 * @return array<int, array{table: string, columns: string[]}>
 */
function summarize_schema(string $createSql): array
{
    // PHP has no global regex `lastIndex` cursor like JS, so we scan with
    // preg_match + an explicit byte offset (PREG_OFFSET_CAPTURE) and walk the
    // string manually for the table body.
    $results = [];
    $headerRe = '/CREATE\s+TABLE\s+(?:IF\s+NOT\s+EXISTS\s+)?[\"\'\`]?\b(\w+)\b[\"\'\`]?\s*\(/i';

    $offset = 0;
    while (true) {
        if (preg_match($headerRe, $createSql, $m, PREG_OFFSET_CAPTURE, $offset) !== 1) {
            break;
        }
        [$tableMatch, $matchStart] = $m[0];
        $table = strtolower($m[1][0]);
        $bodyStart = $matchStart + strlen($tableMatch);

        // Walk forward to find the matching close paren of the table body.
        $depth = 1;
        $i = $bodyStart;
        $len = strlen($createSql);
        while ($i < $len && $depth > 0) {
            $c = $createSql[$i];
            if ($c === '(') {
                $depth++;
            } elseif ($c === ')') {
                $depth--;
            }
            $i++;
        }
        if ($depth !== 0) {
            // Unbalanced parens -> malformed; skip past this match and retry.
            $offset = $bodyStart;
            continue;
        }

        $body = substr($createSql, $bodyStart, $i - 1 - $bodyStart);
        $columns = [];

        // Split body on top-level commas only (depth-tracked).
        $colDepth = 0;
        $current = '';
        $bodyLen = strlen($body);
        for ($j = 0; $j < $bodyLen; $j++) {
            $ch = $body[$j];
            if ($ch === '(') {
                $colDepth++;
                $current .= $ch;
            } elseif ($ch === ')') {
                $colDepth--;
                $current .= $ch;
            } elseif ($ch === ',' && $colDepth === 0) {
                $col = extract_column_name($current);
                if ($col !== null) {
                    $columns[] = $col;
                }
                $current = '';
            } else {
                $current .= $ch;
            }
        }
        $lastCol = extract_column_name($current);
        if ($lastCol !== null) {
            $columns[] = $lastCol;
        }

        $results[] = ['table' => $table, 'columns' => $columns];
        // Resume scanning after this table's closing paren.
        $offset = $i;
    }

    return $results;
}