Luigit
repositories / pi-ext

pi-ext

bugabingas pi extensions

owned by admin

extensions/memory/src/exec.ts

Raw
import { spawn } from "node:child_process";
import {
	readdirSync,
	rmSync,
	statSync,
	unlinkSync,
	writeFileSync,
} from "node:fs";
import { tmpdir } from "node:os";
import { join } from "node:path";
import { fileURLToPath } from "node:url";
import {
	DEFAULT_TIMEOUT_MS,
	MAX_INLINE_ROWS,
	MAX_SQL_LENGTH,
	MAX_TIMEOUT_MS,
	SPILL_MAX_AGE_HOURS,
} from "./guide.ts";
import type { StatementKind } from "./sql.ts";
import { classify, guard, guidanceFor, referencedTables } from "./sql.ts";
import type {
	JournalEntry,
	JournalMode,
	JournalRow,
	MemoryStore,
} from "./store.ts";

export type JsonCell = string | number | boolean | null;

function cell(value: unknown): JsonCell {
	if (value instanceof Uint8Array) return `<bytes:${value.length}>`;
	if (typeof value === "bigint") return value.toString();
	if (value === null) return null;
	if (typeof value === "string") return value;
	if (typeof value === "number")
		return Number.isFinite(value) ? value : String(value);
	if (typeof value === "boolean") return value;
	return String(value);
}

export type SqlOutcome = {
	columns: string[];
	rows: JsonCell[][];
	rowCount: number;
	truncated: boolean;
	spillPath: string | null;
	changes: number;
	durationMs: number;
	tablesTouched: string[];
	schemaChanged: boolean;
	attachedEscaped: boolean;
	journalSeq: number;
};

export type RunOptions = {
	actor: "agent" | "human";
	readonly?: boolean;
	params?: unknown[];
	timeoutMs?: number;
	/** Explicit viewer journal modes; write/read/ddl derive from the SQL. */
	mode?: "undo" | "fix";
	/** Set false for internal calls that must not consume undo capacity. */
	snapshot?: boolean;
};

const SPILL_PREFIX = "pi-ext-memory-";
const JOURNAL_SCAN = 200;

function workerPath(): string {
	// Dist packages emit extension chunks with .ts names but Node entries as
	// flat .js files; resolve the built worker first, then the source sibling.
	const built = fileURLToPath(new URL("./sql-worker.js", import.meta.url));
	if (statSync(built, { throwIfNoEntry: false })) return built;
	return fileURLToPath(new URL("../sql-worker.ts", import.meta.url));
}

type WorkerResponse = {
	ok: boolean;
	columns?: string[];
	rows?: unknown[][];
	rowCount?: number;
	truncated?: boolean;
	changes?: number;
	durationMs: number;
	attached: string[];
	error?: string;
};

function dispatchWorker(
	dbPath: string,
	sql: string,
	params: unknown[],
	readonly: boolean,
	timeoutMs: number,
): Promise<WorkerResponse> {
	return new Promise((resolve, reject) => {
		// A child process, not a worker thread: SIGKILL is the only reliable way
		// to stop a statement stuck inside native SQLite code.
		let stdout = "";
		let settled = false;
		const child = spawn(process.execPath, [
			workerPath(),
			JSON.stringify({ dbPath, sql, params, readonly }),
		]);
		const finish = (fn: () => void) => {
			if (settled) return;
			settled = true;
			clearTimeout(timer);
			child.kill("SIGKILL");
			fn();
		};
		const timer = setTimeout(() => {
			finish(() =>
				reject(
					new Error(
						`Statement exceeded ${timeoutMs} ms and was killed. Split it into smaller batches (bounded UPDATE/DELETE loops), or pass a larger timeoutMs (up to ${MAX_TIMEOUT_MS}). Long statements also hold the write lock and stall other sessions.`,
					),
				),
			);
		}, timeoutMs);
		child.stdout?.on("data", (chunk: Buffer) => {
			stdout += chunk.toString();
		});
		child.on("error", (error) => {
			finish(() => reject(error));
		});
		child.on("exit", (code, signal) => {
			finish(() => {
				const line = stdout
					.split("\n")
					.find((entry) => entry.trim().startsWith("{"));
				if (line) {
					try {
						resolve(JSON.parse(line) as WorkerResponse);
						return;
					} catch {
						// Fall through to the process-level failure below.
					}
				}
				reject(
					new Error(
						`SQL worker failed (exit ${code ?? "null"}${signal ? `, signal ${signal}` : ""}) without a response`,
					),
				);
			});
		});
	});
}

function spillRows(rows: unknown[][]): string {
	const path = join(
		tmpdir(),
		`${SPILL_PREFIX}${Date.now()}-${Math.random().toString(36).slice(2, 8)}.ndjson`,
	);
	writeFileSync(
		path,
		rows.map((row) => JSON.stringify(row)).join("\n") + "\n",
		{ mode: 0o600 },
	);
	return path;
}

export function pruneSpills(now: Date): number {
	let removed = 0;
	const cutoff = now.getTime() - SPILL_MAX_AGE_HOURS * 3_600_000;
	try {
		for (const name of readdirSync(tmpdir())) {
			if (!name.startsWith(SPILL_PREFIX) || !name.endsWith(".ndjson")) continue;
			const full = join(tmpdir(), name);
			const info = statSync(full, { throwIfNoEntry: false });
			if (!info || info.mtimeMs < cutoff) {
				try {
					if (!info) unlinkSync(full);
					else rmSync(full, { force: true });
					removed++;
				} catch {
					// A missing spill file survives this pass.
				}
			}
		}
	} catch {
		// Unreadable temp dir simply means nothing pruned.
	}
	return removed;
}

/**
 * Execute one SQL call end to end: classify, guard, snapshot for undo,
 * dispatch to the worker, journal the outcome. Throws guiding errors; the
 * journal records every attempt including denials and failures.
 */
export async function runSql(
	store: MemoryStore,
	sql: string,
	options: RunOptions,
): Promise<SqlOutcome> {
	if (typeof sql !== "string" || !sql.trim())
		throw new Error(
			"sql is required; write one SQLite statement (or several separated by ';')",
		);
	if (sql.length > MAX_SQL_LENGTH)
		throw new Error(
			`sql exceeds ${MAX_SQL_LENGTH} characters; split the work into several calls`,
		);
	const readonly = options.readonly ?? false;
	const kind: StatementKind = classify(sql);
	const mode: JournalMode =
		options.mode ?? (readonly ? "read" : kind === "ddl" ? "ddl" : "write");
	const decision = guard(sql, kind);
	const known = store.knownTables();
	const tables = referencedTables(sql, new Set(known));
	const started = Date.now();
	const base: JournalEntry = {
		ts: new Date().toISOString(),
		actor: options.actor,
		mode,
		readonly,
		sql,
		params: JSON.stringify(options.params ?? []),
		rowsReturned: 0,
		rowsWritten: 0,
		spilledTo: null,
		durationMs: 0,
		ok: false,
		error: null,
		tablesTouched: JSON.stringify(tables),
		schemaChanged: kind === "ddl",
		attachedEscaped: false,
		undoPath: null,
	};
	if (!decision.ok) {
		base.durationMs = Date.now() - started;
		base.error = `${decision.reason}: ${decision.guidance}`;
		const seq = store.appendJournal(base);
		throw new Error(
			`${decision.reason}. ${decision.guidance} (journaled #${seq})`,
		);
	}
	let undoPath: string | null = null;
	if (!readonly && options.snapshot !== false) {
		undoPath = store.snapshotForUndo();
		base.undoPath = undoPath;
	}
	let response: WorkerResponse;
	try {
		response = await dispatchWorker(
			store.path,
			sql,
			options.params ?? [],
			readonly,
			options.timeoutMs ?? DEFAULT_TIMEOUT_MS,
		);
	} catch (error) {
		base.durationMs = Date.now() - started;
		base.undoPath = undoPath;
		base.error = guidanceFor(error, known);
		const seq = store.appendJournal(base);
		const failure = new Error(`${base.error} (journaled #${seq})`);
		throw failure;
	}
	base.durationMs = response.durationMs;
	base.rowsReturned = response.rowCount ?? 0;
	base.rowsWritten = response.changes ?? 0;
	base.ok = response.ok;
	base.attachedEscaped = response.attached.length > 0;
	if (!response.ok) {
		base.error = guidanceFor(
			new Error(response.error ?? "unknown SQLite error"),
			known,
		);
		const seq = store.appendJournal(base);
		throw new Error(`${base.error} (journaled #${seq})`);
	}
	if (base.attachedEscaped)
		base.error = `attached schemas detected post-exec: ${response.attached.join(", ")}; they were not journaled`;
	const truncated = response.truncated ?? false;
	const allRows = response.rows ?? [];
	if (truncated || allRows.length > MAX_INLINE_ROWS)
		base.spilledTo = spillRows(allRows);
	const seq = store.appendJournal(base);
	const rows = (response.rows ?? []).map((row) => row.map(cell));
	return {
		columns: response.columns ?? [],
		rows: base.spilledTo ? rows.slice(0, MAX_INLINE_ROWS) : rows,
		rowCount: response.rowCount ?? 0,
		truncated: Boolean(base.spilledTo) || truncated,
		spillPath: base.spilledTo,
		changes: response.changes ?? 0,
		durationMs: response.durationMs,
		tablesTouched: tables,
		schemaChanged: kind === "ddl",
		attachedEscaped: base.attachedEscaped,
		journalSeq: seq,
	};
}

/** Undo one journaled write/ddl entry by restoring its pre-write snapshot. */
export function undoEntry(
	store: MemoryStore,
	seq: number,
): { ok: boolean; restored: string[]; failed: string[] } {
	const entry = store
		.recentJournal(JOURNAL_SCAN)
		.find(
			(row: JournalRow) =>
				row.seq === seq &&
				row.ok === 1 &&
				row.undone === 0 &&
				(row.mode === "write" || row.mode === "ddl"),
		);
	if (!entry)
		return {
			ok: false,
			restored: [],
			failed: [
				`#${seq} has no live undo: only successful, not-yet-undone write/ddl entries can be undone, and the journal keeps ${JOURNAL_SCAN} recent entries`,
			],
		};
	if (!entry.undoPath)
		return {
			ok: false,
			restored: [],
			failed: [
				"no snapshot exists for this entry (database exceeded the undo budget at write time)",
			],
		};
	const result = store.restoreSnapshot(entry.undoPath);
	const ok = result.failed.length === 0;
	store.markUndone(seq);
	store.appendJournal({
		ts: new Date().toISOString(),
		actor: "human",
		mode: "undo",
		readonly: false,
		sql: `undo #${seq}: ${entry.sql.slice(0, 200)}`,
		params: "[]",
		rowsReturned: 0,
		rowsWritten: 0,
		spilledTo: null,
		durationMs: 0,
		ok,
		error: ok ? null : result.failed.join("; "),
		tablesTouched: entry.tablesTouched,
		schemaChanged: false,
		attachedEscaped: false,
		undoPath: null,
	});
	store.pruneSnapshots(new Date());
	return { ok, restored: result.restored, failed: result.failed };
}