SQL Playground — JavaScript source

/**
 * sql-playground - polyglot showcase port (JavaScript)
 *
 * Pure helper logic for the CosmoDev "SQL Playground" tool: splitting a SQL
 * script into individual statements, classifying read-only statements, and
 * rendering result sets (columns + rows) as CSV / Markdown / JSON. Also
 * extracts a lightweight schema summary (table -> column list) from a CREATE
 * TABLE script.
 *
 * 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.
 */

// ---------------------------------------------------------------------------
// Statement splitting
// ---------------------------------------------------------------------------

/**
 * Split a SQL script into top-level statements on `;`, respecting single-quote
 * string literals. SQL string-literal escaping is honoured: a doubled `''`
 * inside a string is treated as an escaped quote (per the SQL standard), not as
 * the end of the string. Whitespace-only statements are dropped.
 *
 * @param {string} sql
 * @returns {string[]} trimmed, non-empty statements
 */
export function splitStatements(sql) {
  const stmts = [];
  let current = '';
  let inString = false;

  for (let i = 0; i < sql.length; i++) {
    const ch = sql[i];

    if (inString) {
      current += ch;
      if (ch === "'") {
        // Doubled single quote = an escaped literal quote, consume both chars.
        if (i + 1 < sql.length && sql[i + 1] === "'") {
          current += sql[i + 1];
          i++;
        } else {
          inString = false;
        }
      }
    } else if (ch === "'") {
      inString = true;
      current += ch;
    } else if (ch === ';') {
      const trimmed = current.trim();
      if (trimmed) stmts.push(trimmed);
      current = '';
    } else {
      current += ch;
    }
  }

  // Flush any trailing statement that wasn't terminated by `;`.
  const trimmed = current.trim();
  if (trimmed) stmts.push(trimmed);

  return stmts;
}

// ---------------------------------------------------------------------------
// Read-only classification
// ---------------------------------------------------------------------------

/**
 * True when the statement is a read-only one (safe to run against a snapshot
 * without mutating state). Recognises the common query-leading keywords.
 *
 * @param {string} sql
 * @returns {boolean}
 */
export function isReadOnlyStatement(sql) {
  return /^(SELECT|WITH|VALUES|EXPLAIN|PRAGMA)\b/i.test(sql.trim());
}

// ---------------------------------------------------------------------------
// Scalar formatting
// ---------------------------------------------------------------------------

/**
 * Render a single cell value as the textual form shown in tables/CSV.
 * - null/undefined -> "NULL"
 * - numbers/booleans -> their native string form
 * - strings -> the string verbatim
 * - anything else (objects/arrays) -> JSON
 *
 * @param {unknown} v
 * @returns {string}
 */
export function formatScalar(v) {
  if (v === null || v === undefined) return 'NULL';
  if (typeof v === 'number' || typeof v === 'boolean') return String(v);
  if (typeof v === 'string') return v;
  return JSON.stringify(v);
}

/**
 * Format one cell per RFC-4180-ish CSV: quote the field if it contains a
 * comma, double quote, or newline; double any embedded double quotes.
 */
function csvField(v) {
  const s = formatScalar(v);
  if (/[,"\n\r]/.test(s)) {
    return '"' + s.replace(/"/g, '""') + '"';
  }
  return s;
}

// ---------------------------------------------------------------------------
// Row serializers
// ---------------------------------------------------------------------------

/**
 * Serialize a result set to CSV (header row + one row per record, trailing LF).
 *
 * @param {string[]} columns
 * @param {unknown[][]} rows
 * @returns {string}
 */
export function rowsToCsv(columns, rows) {
  const lines = [columns.map((c) => csvField(c)).join(',')];
  for (const row of rows) {
    lines.push(row.map((cell) => csvField(cell)).join(','));
  }
  return lines.join('\n') + '\n';
}

/**
 * Serialize a result set as a GitHub-flavored Markdown table. Pipes inside
 * cells are escaped with a backslash so they don't break the table layout.
 *
 * @param {string[]} columns
 * @param {unknown[][]} rows
 * @returns {string}
 */
export function rowsToMarkdown(columns, rows) {
  const esc = (s) => s.replace(/\|/g, '\\|');
  const header = '| ' + columns.map(esc).join(' | ') + ' |';
  const sep = '| ' + columns.map(() => '---').join(' | ') + ' |';
  const dataRows = rows.map(
    (row) => '| ' + row.map((cell) => esc(formatScalar(cell))).join(' | ') + ' |',
  );
  return [header, sep, ...dataRows].join('\n') + '\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 {unknown[][]} rows
 * @returns {string}
 */
export function rowsToJson(columns, rows) {
  const arr = rows.map((row) => {
    const obj = {};
    columns.forEach((col, i) => {
      obj[col] = row[i] ?? null;
    });
    return obj;
  });
  return JSON.stringify(arr, null, 2);
}

// ---------------------------------------------------------------------------
// Schema summary
// ---------------------------------------------------------------------------

/**
 * 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) - those are not columns.
 */
function extractColumnName(def) {
  if (!def) return null;
  if (/^(PRIMARY\s+KEY|FOREIGN\s+KEY|UNIQUE|CHECK|CONSTRAINT)\b/i.test(def)) {
    return null;
  }
  const m = def.match(/^["'`]?(\w+)["'`]?/);
  return m ? m[1].toLowerCase() : null;
}

/**
 * Best-effort extraction of `{ table, columns }` summaries from a CREATE TABLE
 * script. Handles IF NOT EXISTS, quoted identifiers, and parenthesised types
 * / constraint bodies (depth-tracked so a `,` inside `NUMERIC(10,2)` doesn't
 * split a column). Returns [] if anything goes wrong.
 *
 * @param {string} createSql
 * @returns {{ table: string, columns: string[] }[]}
 */
export function summarizeSchema(createSql) {
  try {
    const results = [];
    const headerRe = /CREATE\s+TABLE\s+(?:IF\s+NOT\s+EXISTS\s+)?["'`]?(\w+)["'`]?\s*\(/gi;
    let match;

    while ((match = headerRe.exec(createSql)) !== null) {
      const table = match[1].toLowerCase();
      const startIdx = match.index + match[0].length;

      // Walk forward to find the matching close paren of the table body.
      let depth = 1;
      let i = startIdx;
      while (i < createSql.length && depth > 0) {
        if (createSql[i] === '(') depth++;
        else if (createSql[i] === ')') depth--;
        i++;
      }

      // Unbalanced parens -> malformed; skip this match.
      if (depth !== 0) continue;

      // Resume the next header search after this table's body.
      headerRe.lastIndex = i;

      const body = createSql.slice(startIdx, i - 1);
      const columns = [];

      // Split the body on top-level commas only.
      let colDepth = 0;
      let current = '';
      for (const ch of body) {
        if (ch === '(') colDepth++;
        else if (ch === ')') colDepth--;
        else if (ch === ',' && colDepth === 0) {
          const col = extractColumnName(current.trim());
          if (col) columns.push(col);
          current = '';
          continue;
        }
        current += ch;
      }
      // Flush the trailing column definition.
      const lastCol = extractColumnName(current.trim());
      if (lastCol) columns.push(lastCol);

      results.push({ table, columns });
    }

    return results;
  } catch {
    return [];
  }
}