Luigit
repositories / pi-ext

pi-ext

bugabingas pi extensions

owned by admin

extensions/memory/__tests__/exec.test.ts

Raw
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<MemoryStore> {
	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);
	});
});