import { existsSync, mkdtempSync, readdirSync, rmSync } from "node:fs"; import { tmpdir } from "node:os"; import { join } from "node:path"; import { afterEach, describe, expect, it } from "vitest"; import { runSql, undoEntry } from "../src/exec.ts"; import { MemoryStore } from "../src/store.ts"; let dir: string | undefined; let store: MemoryStore | undefined; afterEach(() => { store?.close(); store = undefined; if (dir) { rmSync(dir, { recursive: true, force: true }); dir = undefined; } }); async function fresh(): Promise { dir = mkdtempSync(join(tmpdir(), "memory-exec-")); store = await MemoryStore.open(join(dir, "memory.sqlite")); return store; } describe("memory exec", () => { it("journals every call: ddl, write, read, and denials alike", async () => { const db = await fresh(); const ddl = await runSql(db, "CREATE TABLE facts(a TEXT, value TEXT)", { actor: "agent", }); expect(ddl.schemaChanged).toBe(true); expect(ddl.journalSeq).toBe(1); const write = await runSql( db, "INSERT INTO facts VALUES('node', 'needs 26')", { actor: "agent", }, ); expect(write.changes).toBe(1); const read = await runSql(db, "SELECT value FROM facts WHERE a='node'", { actor: "agent", readonly: true, }); expect(read.rows).toEqual([["needs 26"]]); expect(read.tablesTouched).toEqual(["facts"]); await expect( runSql(db, "ATTACH '/tmp/evil.db' AS evil", { actor: "agent" }), ).rejects.toThrow(/ATTACH is blocked/u); const journal = db.recentJournal(10); expect(journal).toHaveLength(4); // recentJournal is newest-first; oldest-first: ddl, insert, read, denial. expect([...journal].reverse().map((row) => row.mode)).toEqual([ "ddl", "write", "read", "write", ]); const denial = journal.find((row) => row.ok === 0); expect(denial?.error).toContain("audit boundary"); }); it("binds params for single statements and refuses multi-statement params", async () => { const db = await fresh(); await runSql(db, "CREATE TABLE t(a TEXT)", { actor: "agent" }); const write = await runSql(db, "INSERT INTO t VALUES(?)", { actor: "agent", params: ["x"], }); expect(write.changes).toBe(1); const read = await runSql(db, "SELECT a FROM t WHERE a=?", { actor: "agent", readonly: true, params: ["x"], }); expect(read.rows).toEqual([["x"]]); await expect( runSql(db, "CREATE TABLE u(a); INSERT INTO t VALUES(?)", { actor: "agent", params: ["x"], }), ).rejects.toThrow(/params bind to exactly one statement/u); }); it("enforces readonly connections at the SQLite level", async () => { const db = await fresh(); await runSql(db, "CREATE TABLE t(a TEXT)", { actor: "agent" }); await expect( runSql(db, "DELETE FROM t", { actor: "agent", readonly: true }), ).rejects.toThrow(/readonly/u); }); it("kills long statements at the timeout and still journals", async () => { const db = await fresh(); const start = Date.now(); await expect( runSql( db, "WITH RECURSIVE c(x) AS (SELECT 1 UNION ALL SELECT x+1 FROM c) SELECT sum(x) FROM c", { actor: "agent", readonly: true, timeoutMs: 600 }, ), ).rejects.toThrow(/exceeded 600 ms/u); expect(Date.now() - start).toBeLessThan(5000); const last = db.recentJournal(1)[0]; expect(last?.ok).toBe(0); expect(last?.error).toContain("killed"); }); it("returns rows for queries on writable connections too", async () => { const db = await fresh(); const plain = await runSql(db, "SELECT 1 AS one", { actor: "agent" }); expect(plain.rowCount).toBe(1); expect(plain.rows).toEqual([[1]]); await runSql(db, "CREATE TABLE t(a TEXT)", { actor: "agent" }); const bound = await runSql(db, "INSERT INTO t VALUES(?)", { actor: "agent", params: ["x"], }); expect(bound.changes).toBe(1); expect(bound.rowCount).toBe(0); const boundRead = await runSql(db, "SELECT a FROM t WHERE a=?", { actor: "agent", params: ["x"], }); expect(boundRead.rowCount).toBe(1); expect(boundRead.rows).toEqual([["x"]]); const mixed = await runSql( db, "INSERT INTO t VALUES('y'); SELECT a FROM t", { actor: "agent", }, ); expect(mixed.rowCount).toBe(2); expect(mixed.changes).toBe(1); }); it("journals human writes and skips snapshots when asked", async () => { const db = await fresh(); await runSql(db, "CREATE TABLE t(a TEXT)", { actor: "agent" }); const human = await runSql(db, "INSERT INTO t VALUES('by hand')", { actor: "human", mode: "fix", snapshot: false, }); expect(human.changes).toBe(1); const entry = db.recentJournal(1)[0]; expect(entry?.actor).toBe("human"); expect(entry?.mode).toBe("fix"); expect(entry?.undoPath).toBeNull(); const checkpoint = await runSql(db, "PRAGMA wal_checkpoint(TRUNCATE)", { actor: "human", mode: "fix", snapshot: false, }); expect(checkpoint.rowCount).toBeGreaterThanOrEqual(0); const vacuum = await runSql(db, "VACUUM", { actor: "human", mode: "fix", snapshot: false, }); expect(vacuum.changes).toBe(0); }); it("spills large reads to temp NDJSON and prunes old spills", async () => { const db = await fresh(); await runSql(db, "CREATE TABLE t(a INTEGER)", { actor: "agent" }); await runSql( db, "WITH RECURSIVE c(x) AS (SELECT 1 UNION ALL SELECT x+1 FROM c WHERE x<200) INSERT INTO t SELECT x FROM c", { actor: "agent" }, ); const read = await runSql(db, "SELECT * FROM t", { actor: "agent", readonly: true, }); expect(read.rowCount).toBe(200); expect(read.truncated).toBe(true); expect(read.rows.length).toBe(50); expect(read.spillPath).toBeTruthy(); expect(existsSync(read.spillPath!)).toBe(true); }); it("restores a write from its snapshot and marks the entry undone", async () => { const db = await fresh(); await runSql(db, "CREATE TABLE t(a TEXT)", { actor: "agent" }); await runSql(db, "INSERT INTO t VALUES('keep')", { actor: "agent" }); const doomed = await runSql(db, "DELETE FROM t", { actor: "agent" }); const before = await runSql(db, "SELECT count(*) FROM t", { actor: "agent", readonly: true, }); expect(Number(before.rows[0]?.[0])).toBe(0); const undo = undoEntry(db, doomed.journalSeq); expect(undo.ok).toBe(true); expect(undo.restored).toContain("t"); const after = await runSql(db, "SELECT count(*) FROM t", { actor: "agent", readonly: true, }); expect(Number(after.rows[0]?.[0])).toBe(1); const undone = db.recentJournal(5); expect(undone.find((row) => row.seq === doomed.journalSeq)?.undone).toBe(1); expect(undone.find((row) => row.mode === "undo")?.ok).toBe(1); expect(undoEntry(db, doomed.journalSeq).ok).toBe(false); }); it("gives guidance for missing tables and schema drift", async () => { const db = await fresh(); await expect( runSql(db, "SELECT * FROM factz", { actor: "agent", readonly: true }), ).rejects.toThrow(/_schema_docs/u); await runSql(db, "CREATE TABLE facts(a TEXT NOT NULL)", { actor: "agent" }); await expect( runSql(db, "INSERT INTO facts VALUES(NULL)", { actor: "agent" }), ).rejects.toThrow(/NOT NULL/u); }); it("bounds undo snapshots", async () => { const db = await fresh(); await runSql(db, "CREATE TABLE t(a TEXT)", { actor: "agent" }); for (let index = 0; index < 22; index++) { await runSql(db, `INSERT INTO t VALUES('${index}')`, { actor: "agent" }); } expect(readdirSync(db.undoDir()).length).toBeLessThanOrEqual(20); }); });