repositories / pi-ext
pi-ext
bugabingas pi extensions
owned by admin
extensions/memory/__tests__/sql.test.ts
Rawimport { 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",
);
});
});