Luigit
repositories / pi-ext

pi-ext

bugabingas pi extensions

owned by admin

extensions/memory/sql-worker.ts

Raw
// Node-entry worker: executes one SQL request against the memory database,
// writes one JSON response line to stdout, then exits. Spawned per call by
// src/exec.ts as a child process so a statement stuck in native SQLite code
// can always be killed with SIGKILL (worker threads cannot be interrupted).
// Strip-mode compatible. Keep standalone: spawned as its own bundle entry.
import {
	DatabaseSync,
	type SQLInputValue,
	type StatementSync,
} from "node:sqlite";
import { splitStatements } from "./src/sql.ts";

// Row cap mirrors the tool contract: bounded memory, spill handled by parent.
const MAX_QUERY_ROWS = 10_000;

type Request = {
	dbPath: string;
	sql: string;
	params: unknown[];
	readonly: boolean;
};
type Response = {
	ok: boolean;
	columns: string[];
	rows: unknown[][];
	rowCount: number;
	truncated: boolean;
	changes?: number;
	durationMs: number;
	attached: string[];
	error?: string;
};

function isQueryStatement(statement: string): boolean {
	const first = statement.trimStart().slice(0, 6).toUpperCase();
	return (
		first.startsWith("SELECT") ||
		first.startsWith("WITH") ||
		first.startsWith("PRAGMA")
	);
}

function collectIterating(response: Response, prepared: StatementSync): void {
	let columnsFixed = false;
	let collected = 0;
	for (const row of prepared.iterate()) {
		if (!columnsFixed) {
			response.columns = Object.keys(row);
			columnsFixed = true;
		}
		if (collected >= MAX_QUERY_ROWS) {
			response.truncated = true;
			break;
		}
		response.rows.push(response.columns.map((column) => safe(row[column])));
		collected++;
	}
	response.rowCount += collected;
}

function collectedTotal(response: Response): number {
	return response.rows.length;
}

function safe(value: unknown): unknown {
	if (value instanceof Uint8Array) return `<bytes:${value.length}>`;
	if (typeof value === "bigint") return value.toString();
	return value;
}

function collectRows(
	response: Response,
	rows: Record<string, unknown>[],
): void {
	response.truncated = rows.length > MAX_QUERY_ROWS;
	const bounded = rows.slice(0, MAX_QUERY_ROWS);
	response.columns = bounded[0] ? Object.keys(bounded[0]) : [];
	response.rows = bounded.map((row) =>
		response.columns.map((column) => safe(row[column])),
	);
	response.rowCount = rows.length;
}
function main(): void {
	const start = performance.now();
	const request = JSON.parse(process.argv[2] ?? "{}") as Request;
	const response: Response = {
		ok: false,
		columns: [],
		rows: [],
		rowCount: 0,
		truncated: false,
		durationMs: 0,
		attached: [],
	};
	let db: DatabaseSync | undefined;
	try {
		db = new DatabaseSync(request.dbPath, {
			readOnly: request.readonly,
			open: true,
		});
		db.exec("PRAGMA busy_timeout=2000");
		const attachedRows = db.prepare("PRAGMA database_list").all() as Array<{
			name: string;
		}>;
		response.attached = attachedRows
			.map((row) => row.name)
			.filter((name) => name !== "main" && name !== "temp");
		const statements = splitStatements(request.sql);
		if (request.params.length > 0 && statements.length > 1)
			throw new Error(
				"params bind to exactly one statement; use literal SQL or split the call",
			);
		const before = Number(
			(db.prepare("SELECT total_changes() AS c").get() as { c: number }).c,
		);
		if (!request.readonly) {
			// The tool contract returns rows for queries on any connection; only
			// true write statements yield no rows.
			if (request.params.length > 0) {
				// The tool schema restricts params to JSON scalars; SQLite accepts
				// exactly those as bound values.
				const params = request.params as SQLInputValue[];
				const prepared = db.prepare(request.sql);
				if (isQueryStatement(request.sql)) {
					collectRows(
						response,
						prepared.all(...params) as Record<string, unknown>[],
					);
				} else {
					prepared.run(...params);
				}
			} else {
				for (const statement of statements) {
					const prepared = db.prepare(statement);
					if (isQueryStatement(statement)) {
						collectIterating(response, prepared);
						if (response.truncated) break;
					} else {
						prepared.run();
					}
				}
			}
		} else if (request.params.length > 0) {
			collectRows(
				response,
				db
					.prepare(request.sql)
					.all(...(request.params as SQLInputValue[])) as Record<
					string,
					unknown
				>[],
			);
		} else {
			for (const statement of statements) {
				collectIterating(response, db.prepare(statement));
				if (response.truncated) break;
			}
			response.rowCount = collectedTotal(response);
		}
		const after = Number(
			(db.prepare("SELECT total_changes() AS c").get() as { c: number }).c,
		);
		response.changes = after - before;
		response.ok = true;
	} catch (error) {
		response.ok = false;
		response.error = error instanceof Error ? error.message : String(error);
	} finally {
		response.durationMs = Math.round(performance.now() - start);
		try {
			db?.close();
		} catch {
			// Close is best effort; the process exits regardless.
		}
		process.stdout.write(JSON.stringify(response) + "\n");
	}
}

main();