queries.ts

1import type { Database } from "bun:sqlite";2import { createHash } from "node:crypto";3import { inflateSync } from "node:zlib";4import type { ReleaseRecord } from "../bucket/repo.ts";5import { mirrorState } from "./store.ts";67export type Save = {8	id: string;9	parent: string;10	message: string;11	created_at: number;12	machine: string;13	agent: string;14	session: string;15	is_checkpoint: boolean;16};1718export type FileEntry = { path: string; hash: string; executable: boolean; size: number };1920/** The saves that touched each path up to one save, oldest first; `line` is every one of21 * them in line order. */22export type Touches = { line: Save[]; byPath: Map<string, Save[]> };2324export type RepoStats = { saves: number; lines: number; language: string; updated: number | null };2526const SAVE_COLUMNS = `id, COALESCE(parent, '') AS parent, message, created_at, machine,27	COALESCE(agent, '') AS agent, COALESCE(session, '') AS session, is_checkpoint = 1 AS is_checkpoint`;28const LINE_COLUMNS =29	"line.id, line.parent, line.message, line.created_at, line.machine, line.agent, line.session, line.is_checkpoint, line.n";30const ID_PREFIX = /^[0-9a-f]{6,12}$/;3132// Checkpoints are working marks, not history (§21.4): every list and count below leaves them out.33const NOT_CHECKPOINT = "is_checkpoint = 0";3435export function headId(db: Database): string | null {36	return mirrorState(db, "head");37}3839/** A page of saves, newest first. Rows land oldest first, so rowid is the order of the line. */40export function history(db: Database, limit: number, offset: number): Save[] {41	return db42		.query<Save, [number, number]>(43			`SELECT ${SAVE_COLUMNS} FROM saves WHERE ${NOT_CHECKPOINT} ORDER BY rowid DESC LIMIT ? OFFSET ?`,44		)45		.all(limit, offset)46		.map(asSave);47}4849/** Every save, oldest first, for the timeline. */50export function timeline(db: Database): Save[] {51	return db52		.query<Save, []>(`SELECT ${SAVE_COLUMNS} FROM saves WHERE ${NOT_CHECKPOINT} ORDER BY rowid`)53		.all()54		.map(asSave);55}5657/** The save a unique prefix of 6–12 hex characters names (§6.1), or null when none or58 * several do. */59export function saveByPrefix(db: Database, prefix: string): Save | null {60	if (!ID_PREFIX.test(prefix)) return null;61	const rows = db62		.query<Save, [string]>(`SELECT ${SAVE_COLUMNS} FROM saves WHERE id LIKE ? || '%' LIMIT 2`)63		.all(prefix);64	return rows.length === 1 ? asSave(rows[0] as Save) : null;65}6667/** The nearest ancestor that is not a checkpoint: the parent a visitor can see (§21.4). */68export function visibleParent(db: Database, save: Save): Save | null {69	for (let id = save.parent; id !== ""; ) {70		const parent = saveByPrefix(db, id);71		if (!parent?.is_checkpoint) return parent;72		id = parent.parent;73	}74	return null;75}7677/** The reason a save is flagged, or null when it isn't: its newest flag or clear event wins (§10.1). */78export function flagReason(db: Database, saveId: string): string | null {79	const row = db80		.query<{ kind: string; reason: string }, [string]>(81			`SELECT kind, COALESCE(reason, '') AS reason FROM events82			WHERE save_id = ? AND kind IN ('flag', 'clear') ORDER BY created_at DESC, id DESC LIMIT 1`,83		)84		.get(saveId);85	return row?.kind === "flag" ? row.reason : null;86}8788/** How many strip events have landed: events are append-only, so this only ever grows. */89/**90 * What a save's tarball depends on besides the save: the strips landed, and the hidden paths and91 * epoch the mirror was built with. A change to any of them names a new key, so an old tarball is92 * never looked up again, whatever a late write or a failed cleanup leaves behind.93 */94export function tarballGeneration(db: Database): string {95	const built = `${mirrorState(db, "hidden") ?? ""}\n${mirrorState(db, "epoch") ?? ""}`;96	return `${stripCount(db)}-${createHash("sha256").update(built).digest("hex").slice(0, 12)}`;97}9899function stripCount(db: Database): number {100	return db.query<{ n: number }, []>("SELECT COUNT(*) AS n FROM events WHERE kind = 'strip'").get()101		?.n as number;102}103104export function filesAt(db: Database, saveId: string): FileEntry[] {105	return db106		.query<FileEntry, [string]>(107			`SELECT f.path, f.hash, f.executable = 1 AS executable, o.size FROM save_files f108			JOIN objects o ON o.hash = f.hash WHERE f.save_id = ? ORDER BY f.path`,109		)110		.all(saveId)111		.map((file) => ({ ...file, executable: Boolean(file.executable) }));112}113114export function fileAt(db: Database, saveId: string, path: string): FileEntry | null {115	const file = db116		.query<FileEntry, [string, string]>(117			`SELECT f.path, f.hash, f.executable = 1 AS executable, o.size FROM save_files f118			JOIN objects o ON o.hash = f.hash WHERE f.save_id = ? AND f.path = ?`,119		)120		.get(saveId, path);121	return file ? { ...file, executable: Boolean(file.executable) } : null;122}123124export function objectBytes(db: Database, hash: string): Uint8Array<ArrayBuffer> | null {125	const row = db126		.query<{ data: Uint8Array }, [string]>("SELECT data FROM objects WHERE hash = ?")127		.get(hash);128	return row ? new Uint8Array(inflateSync(row.data)) : null;129}130131export function repoStats(db: Database): RepoStats {132	const row = db133		.query<{ saves: number; updated: number | null }, []>(134			`SELECT COUNT(*) AS saves, MAX(created_at) AS updated FROM saves WHERE ${NOT_CHECKPOINT}`,135		)136		.get() ?? { saves: 0, updated: null };137	return {138		saves: row.saves,139		updated: row.updated,140		lines: Number(mirrorState(db, "lines") ?? 0),141		language: mirrorState(db, "language") ?? "",142	};143}144145/**146 * Which saves touched each path on the line up to `saveId` (§21.5's detail panel). A save touched a path when147 * the path was added, changed or removed against the save before it; checkpoints are skipped, so a148 * change first recorded by a checkpoint counts at the next real save.149 */150export function fileTouches(db: Database, saveId: string): Touches {151	const rows = db152		.query<Save & { path: string; n: number }, { at: string }>(153			`WITH line AS (154				SELECT ${SAVE_COLUMNS}, LAG(id) OVER (ORDER BY rowid) AS prev, rowid AS n FROM saves155				WHERE ${NOT_CHECKPOINT} AND rowid <= (SELECT rowid FROM saves WHERE id = $at)156			)157			SELECT ${LINE_COLUMNS}, f.path FROM line JOIN save_files f ON f.save_id = line.id158			LEFT JOIN save_files p ON p.save_id = line.prev AND p.path = f.path159			WHERE p.hash IS NOT f.hash OR p.executable IS NOT f.executable160			UNION ALL161			SELECT ${LINE_COLUMNS}, p.path FROM line JOIN save_files p ON p.save_id = line.prev162			WHERE NOT EXISTS (SELECT 1 FROM save_files f WHERE f.save_id = line.id AND f.path = p.path)163			ORDER BY n`,164		)165		.all({ at: saveId });166	const saves = new Map<string, Save>();167	const byPath = new Map<string, Save[]>();168	for (const { path, n: _, ...row } of rows) {169		const save = saves.get(row.id) ?? asSave(row);170		saves.set(save.id, save);171		const touched = byPath.get(path);172		if (touched) touched.push(save);173		else byPath.set(path, [save]);174	}175	return { line: [...saves.values()], byPath };176}177178/** Lines per language at the newest real save, most first, vendored code left out as in the header's language. */179export function languageLines(db: Database): [string, number][] {180	return JSON.parse(mirrorState(db, "languages") ?? "[]") as [string, number][];181}182183/** Every distinct object (unique file contents) a real save refers to, at any point in history. */184export function objectHashes(db: Database): string[] {185	return db186		.query<{ hash: string }, []>(187			`SELECT DISTINCT f.hash188			 FROM save_files f189			 JOIN saves s ON s.id = f.save_id190			 WHERE s.${NOT_CHECKPOINT}`,191		)192		.all()193		.map((row) => row.hash);194}195196/** Every release record the bucket holds, in no particular order. */197export function releaseRecords(db: Database): ReleaseRecord[] {198	return db199		.query<{ record: string }, []>("SELECT record FROM releases")200		.all()201		.map((row) => JSON.parse(row.record) as ReleaseRecord);202}203204function asSave(row: Save): Save {205	return { ...row, is_checkpoint: Boolean(row.is_checkpoint) };206}