Luigit
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;
}