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