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();