/** * Pure SQL classification: statement splitting, guard scanning, table * attribution, and error guidance. No database access, fully unit-testable. * * Attribution and guards are best-effort lexical analysis. Single-quoted * string literal bodies and comments are replaced by spaces before scanning; * quoted identifier text ([x], "x", `x`) is kept because it names schema * objects. The scanner is deliberately conservative: it may over-block odd * SQL, never under-block ATTACH, VACUUM INTO, or ownership pragma writes on * normal input. */ export type StatementKind = "read" | "write" | "ddl"; export type GuardDecision = | { ok: true } | { ok: false; reason: string; guidance: string }; const RESERVED_TABLES = ["_journal", "_stats_samples", "_health_runs"] as const; /** Assignment forms of extension-owned pragmas; bare read forms stay legal. */ const OWNED_PRAGMA_WRITES = /\bpragma\s+(?:schema\.)?(application_id|user_version|journal_mode|locking_mode)\s*(?:=|\()/iu; const IDENTIFIER_KEYWORDS = new Set([ "all", "alter", "analyze", "and", "as", "asc", "attach", "between", "by", "case", "check", "collate", "constraint", "create", "cross", "current_date", "current_time", "current_timestamp", "default", "delete", "desc", "detach", "distinct", "drop", "else", "end", "escape", "except", "exists", "foreign", "from", "full", "glob", "group", "having", "in", "index", "inner", "insert", "intersect", "into", "is", "isnull", "join", "key", "left", "like", "limit", "match", "natural", "not", "notnull", "null", "offset", "on", "or", "order", "outer", "pragma", "primary", "references", "regexp", "reindex", "release", "replace", "right", "rollback", "rowid", "savepoint", "select", "set", "table", "then", "transaction", "trigger", "union", "unique", "update", "using", "values", "view", "when", "where", "with", ]); const WRITE_KEYWORDS = /\b(insert|update|delete|replace|create|drop|alter|reindex|analyze|vacuum|attach|detach|savepoint|rollback|commit)\b/iu; const DDL_KEYWORDS = /\b(create|drop|alter)\b/iu; const TABLE_REF = /\b(?:from|join|into|update|table|view|trigger|index)\s+(?:if\s+(?:not\s+)?exists\s+)?(?:[a-z_][\w$]*\s*\.\s*)*([a-z_][\w$]*)/giu; /** Replace string-literal bodies and comments with spaces; keep identifiers. */ export function stripLiteralsAndComments(sql: string): string { let out = ""; let index = 0; const keep = (text: string) => { out += text; index += text.length; }; const blank = (closing: string) => { keep("'"); index++; while (index < sql.length) { if (sql[index] === closing) { if (sql[index + 1] === closing) { index += 2; continue; } out += "' "; index++; return; } if (sql[index] === "\n") out += "\n"; index++; } out += "'"; }; while (index < sql.length) { const char = sql[index]; if (char === "'") { blank("'"); continue; } if (char === "-" && sql[index + 1] === "-") { const end = sql.indexOf("\n", index); index = end === -1 ? sql.length : end; out += " "; continue; } if (char === "/" && sql[index + 1] === "*") { const end = sql.indexOf("*/", index + 2); index = end === -1 ? sql.length : end + 2; out += " "; continue; } keep(sql[index] ?? ""); } return out; } /** Split SQL into statements on top-level semicolons (quote/comment aware). */ export function splitStatements(sql: string): string[] { const statements: string[] = []; let current = ""; let index = 0; while (index < sql.length) { const char = sql[index]; if (char === "'") { current += sql[index]; index++; while (index < sql.length) { current += sql[index]; if (sql[index] === "'") { if (sql[index + 1] === "'") { current += sql[index + 1]; index += 2; continue; } index++; break; } index++; } continue; } if (char === "-" && sql[index + 1] === "-") { const end = sql.indexOf("\n", index); const stop = end === -1 ? sql.length : end; current += sql.slice(index, stop); index = stop; continue; } if (char === "/" && sql[index + 1] === "*") { const end = sql.indexOf("*/", index + 2); const stop = end === -1 ? sql.length : end + 2; current += sql.slice(index, stop); index = stop; continue; } if (char === ";") { if (current.trim()) statements.push(current.trim()); current = ""; index++; continue; } current += char; index++; } if (current.trim()) statements.push(current.trim()); return statements; } /** * Best-effort guard. Boundary escapes (ATTACH, VACUUM INTO) and ownership * pragma writes are denied for every kind; reserved-table and sqlite_* * mutations are denied for write/ddl. Reads of reserved tables stay legal: * the seeded _guide promises agents read access to the audit journal. */ export function guard(sql: string, kind: StatementKind): GuardDecision { const cleaned = stripLiteralsAndComments(sql); if (/\battach\b/iu.test(cleaned)) return { ok: false, reason: "ATTACH is blocked", guidance: "Everything must live in the one journaled database; ATTACH would write outside the audit boundary. Use a prefixed table, a context column, or CREATE TEMP TABLE for scratch work.", }; if (/\bvacuum\s+into\b/iu.test(cleaned)) return { ok: false, reason: "VACUUM INTO is blocked", guidance: "VACUUM INTO writes a database file outside the audit boundary. Plain VACUUM is allowed.", }; if (OWNED_PRAGMA_WRITES.test(cleaned)) return { ok: false, reason: "This PRAGMA assignment is reserved", guidance: "application_id, user_version, journal_mode, and locking_mode identify and protect the extension-owned database format. Everything else about the schema is yours.", }; if (kind !== "read") { for (const table of RESERVED_TABLES) { if (new RegExp(`\\b${table}\\b`, "iu").test(cleaned)) return { ok: false, reason: `${table} is reserved`, guidance: "That table is extension-owned (journal, size samples, health runs): readable, but agents cannot write it. Store your data in your own tables and document them in _schema_docs.", }; } if (/\bsqlite_/iu.test(cleaned)) return { ok: false, reason: "sqlite_ names are reserved by SQLite", guidance: "Use your own table names; read schema via sqlite_master queries.", }; } return { ok: true }; } export function classify(sql: string): StatementKind { const cleaned = stripLiteralsAndComments(sql); if (DDL_KEYWORDS.test(cleaned)) return "ddl"; if (WRITE_KEYWORDS.test(cleaned)) return "write"; return "read"; } /** Best-effort table attribution; intersect with real table names for truth. */ export function referencedTables( sql: string, known: ReadonlySet, ): string[] { const cleaned = stripLiteralsAndComments(sql) .replaceAll("(", " ") .replaceAll(")", " "); const found = new Set(); for (const match of cleaned.matchAll(TABLE_REF)) { const name = match[1]?.toLowerCase(); if (name && !IDENTIFIER_KEYWORDS.has(name) && known.has(name)) found.add(name); } return [...found].sort(); } /** Error enrichment: turn SQLite failures into what + why + do instead. */ export function guidanceFor( error: unknown, knownTables: readonly string[], ): string { const message = error instanceof Error ? error.message : String(error); const noSuchTable = /no such table:\s*([\w.]+)/iu.exec(message); if (noSuchTable?.[1]) { const wanted = noSuchTable[1]; const prefix = wanted.split(".")[0] ?? wanted; const nearest = knownTables .filter((table) => table.startsWith(prefix.slice(0, 2))) .slice(0, 5); return `No table '${wanted}'. Existing tables: ${knownTables.slice(0, 12).join(", ") || "(none yet; create one)"}.${nearest.length && !knownTables.slice(0, 12).includes(wanted) ? ` Nearest match: ${nearest.join(", ")}.` : ""} CREATE your own table and document it in _schema_docs; see _guide for suggested shapes.`; } if (/no such column/iu.test(message)) return `${message} — inspect columns with PRAGMA table_info() or SELECT * ... LIMIT 1, then adjust.`; if (/readonly|read-only/iu.test(message)) return `${message} — this call ran with readonly=true; writes need readonly=false (default).`; if (/locked|busy/iu.test(message)) return `${message} — another session holds the write lock. Keep transactions short, set busy_timeout via your own PRAGMA, or retry shortly.`; if (/syntax error/iu.test(message)) return `${message} — check SQLite syntax (https://sqlite.org/lang.html); run one statement per call while debugging.`; if ( /NOT NULL constraint|UNIQUE constraint|FOREIGN KEY constraint|CHECK constraint/iu.test( message, ) ) return `${message} — your schema constraint rejected the row; change the value or evolve the table with ALTER TABLE.`; if (/database is full|disk/iu.test(message)) return `${message} — free disk space is exhausted; the viewer's Health tab shows growth and disk pressure.`; return message; }