repositories / pi-ext
pi-ext
bugabingas pi extensions
owned by admin
extensions/memory/src/sql.ts
Raw/**
* 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>,
): string[] {
const cleaned = stripLiteralsAndComments(sql)
.replaceAll("(", " ")
.replaceAll(")", " ");
const found = new Set<string>();
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(<table>) 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;
}