db.ts

1import { Database } from "bun:sqlite";2import { mkdirSync } from "node:fs";3import { join } from "node:path";45// §19: only what the pages and the sync loop read so far; later phases add their tables.6const SCHEMA = `7CREATE TABLE IF NOT EXISTS settings (8	key   TEXT PRIMARY KEY,9	value TEXT NOT NULL10);11CREATE TABLE IF NOT EXISTS repos (12	name            TEXT PRIMARY KEY,13	public          INTEGER NOT NULL DEFAULT 0,14	category        TEXT NOT NULL DEFAULT '',15	homepage        TEXT NOT NULL DEFAULT '',16	sort_order      INTEGER NOT NULL DEFAULT 0,17	install1        TEXT NOT NULL DEFAULT '',18	install2        TEXT NOT NULL DEFAULT '',19	install3        TEXT NOT NULL DEFAULT '',20	last_sync_at    INTEGER,21	last_sync_error TEXT22);23-- §24: the admin console hides a version or replaces its notes; NULL notes keep the record's.24CREATE TABLE IF NOT EXISTS release_settings (25	repo    TEXT NOT NULL,26	version TEXT NOT NULL,27	hidden  INTEGER NOT NULL DEFAULT 0,28	notes   TEXT,29	PRIMARY KEY (repo, version)30);31-- §22.2: one count per release file; installs are Nix tarball fetches, per repo.32CREATE TABLE IF NOT EXISTS downloads (33	repo     TEXT NOT NULL,34	version  TEXT NOT NULL,35	platform TEXT NOT NULL,36	count    INTEGER NOT NULL DEFAULT 0,37	PRIMARY KEY (repo, version, platform)38);39CREATE TABLE IF NOT EXISTS installs (40	repo  TEXT PRIMARY KEY,41	count INTEGER NOT NULL DEFAULT 042);43`;4445export type RepoSettings = {46	name: string;47	category: string;48	homepage: string;49	installLines: string[];50};5152type RepoRow = {53	name: string;54	category: string;55	homepage: string;56	install1: string;57	install2: string;58	install3: string;59};6061export function openKeprDb(dataDir: string): Database {62	mkdirSync(dataDir, { recursive: true });63	const db = new Database(join(dataDir, "kepr.db"), { strict: true });64	db.run("PRAGMA journal_mode = WAL");65	db.run("PRAGMA busy_timeout = 5000");66	db.run(SCHEMA);67	return db;68}6970export function setting(db: Database, key: string): string | null {71	const row = db72		.query<{ value: string }, [string]>("SELECT value FROM settings WHERE key = ?")73		.get(key);74	return row?.value ?? null;75}7677/** Every repo starts private (§20); registering a known repo changes nothing. */78export function registerRepo(db: Database, name: string): void {79	db.query("INSERT OR IGNORE INTO repos (name) VALUES (?)").run(name);80}8182export function recordSync(db: Database, name: string, at: number, error: string | null): void {83	db.query("UPDATE repos SET last_sync_at = ?, last_sync_error = ? WHERE name = ?").run(84		at,85		error,86		name,87	);88}8990export function publicRepoNames(db: Database): string[] {91	return db92		.query<{ name: string }, []>(93			"SELECT name FROM repos WHERE public = 1 ORDER BY sort_order, name",94		)95		.all()96		.map((row) => row.name);97}9899/** The settings of a public repo, or null when it is private or unknown: both must look missing. */100export function publicRepo(db: Database, name: string): RepoSettings | null {101	const row = db102		.query<RepoRow, [string]>(103			"SELECT name, category, homepage, install1, install2, install3 FROM repos WHERE name = ? AND public = 1",104		)105		.get(name);106	if (!row) return null;107	const installLines = [row.install1, row.install2, row.install3].filter((line) => line !== "");108	return { name: row.name, category: row.category, homepage: row.homepage, installLines };109}