Luigit
repositories / pi-ext

pi-ext

bugabingas pi extensions

owned by admin

extensions/memory/__tests__/sql.test.ts

Raw
import { describe, expect, it } from "vitest";
import {
	classify,
	guard,
	guidanceFor,
	referencedTables,
	splitStatements,
} from "../src/sql.ts";

describe("memory sql classification", () => {
	it("classifies statements and never trips on string literals", () => {
		expect(classify("SELECT * FROM facts")).toBe("read");
		expect(classify("WITH x AS (SELECT 1) SELECT * FROM x")).toBe("read");
		expect(classify("PRAGMA table_info(facts)")).toBe("read");
		expect(classify("INSERT INTO facts VALUES(1)")).toBe("write");
		expect(classify("UPDATE facts SET value='x'")).toBe("write");
		expect(classify("DELETE FROM facts")).toBe("write");
		expect(classify("CREATE TABLE t(a TEXT) STRICT")).toBe("ddl");
		expect(classify("DROP TABLE t")).toBe("ddl");
		expect(classify("ALTER TABLE t ADD COLUMN b TEXT")).toBe("ddl");
		expect(classify("SELECT 'please DROP this thought'")).toBe("read");
		expect(classify("-- CREATE TABLE evil\nSELECT 1")).toBe("read");
		expect(classify("/* attach */ SELECT 1")).toBe("read");
	});

	it("splits statements on top-level semicolons", () => {
		expect(splitStatements("SELECT 1; SELECT 2;")).toEqual([
			"SELECT 1",
			"SELECT 2",
		]);
		expect(splitStatements("SELECT ';'")).toEqual(["SELECT ';'"]);
		expect(
			splitStatements(
				"CREATE TRIGGER t AFTER INSERT ON x BEGIN INSERT INTO log VALUES(1); END",
			),
		).toHaveLength(2);
		expect(splitStatements("  ")).toEqual([]);
		// Leading comments stay part of the statement; SQLite accepts them.
		expect(splitStatements("-- only a comment\nSELECT 1")).toHaveLength(1);
	});

	it("guards boundary escapes for every statement kind", () => {
		for (const kind of ["read", "write", "ddl"] as const) {
			expect(guard("ATTACH '/tmp/x.db' AS x", kind).ok).toBe(false);
			expect(guard("VACUUM INTO '/tmp/x.db'", kind).ok).toBe(false);
			expect(guard("PRAGMA user_version=9", kind).ok).toBe(false);
			expect(guard("PRAGMA journal_mode=DELETE", kind).ok).toBe(false);
		}
		expect(guard("PRAGMA journal_mode", "read").ok).toBe(true);
		expect(guard("PRAGMA table_info(t)", "read").ok).toBe(true);
	});

	it("lets agents read reserved tables but never mutate them", () => {
		expect(guard("SELECT * FROM _journal ORDER BY seq", "read").ok).toBe(true);
		expect(guard("SELECT * FROM _schema_docs", "read").ok).toBe(true);
		expect(guard("INSERT INTO _journal VALUES(1)", "write").ok).toBe(false);
		expect(guard("DELETE FROM _journal", "write").ok).toBe(false);
		expect(guard("DROP TABLE _stats_samples", "ddl").ok).toBe(false);
		expect(guard("UPDATE _health_runs SET verdict='ok'", "write").ok).toBe(
			false,
		);
	});

	it("keeps literal text out of the guard scan", () => {
		const decision = guard(
			"INSERT INTO notes VALUES('the agent mentioned attach and _journal in prose')",
			"write",
		);
		expect(decision.ok).toBe(true);
	});

	it("attributes touched tables against the known set only", () => {
		const known = new Set(["facts", "events", "edges"]);
		expect(
			referencedTables("SELECT * FROM facts JOIN edges ON 1=1", known),
		).toEqual(["edges", "facts"]);
		expect(
			referencedTables("WITH cte AS (SELECT 1) SELECT * FROM cte", known),
		).toEqual([]);
		expect(referencedTables("INSERT INTO facts VALUES(1)", known)).toEqual([
			"facts",
		]);
		expect(referencedTables("SELECT * FROM sqlite_master", known)).toEqual([]);
		expect(referencedTables("SELECT * FROM nosuchtable", known)).toEqual([]);
	});

	it("turns common SQLite failures into guidance", () => {
		const withTables = guidanceFor(new Error("no such table: factz"), [
			"facts",
			"events",
		]);
		expect(withTables).toContain("_schema_docs");
		expect(withTables).toContain("facts");
		expect(
			guidanceFor(new Error("attempt to write a readonly database"), []),
		).toContain("readonly=true");
		expect(guidanceFor(new Error("database is locked"), [])).toContain(
			"transactions short",
		);
		expect(guidanceFor(new Error('near "SELEC": syntax error'), [])).toContain(
			"sqlite.org",
		);
	});
});