| 1 | // Copies of the site's databases, which `node run download-db` saves into a |
| 2 | // clone of this repository so that it can build the site. With the source of |
| 3 | // truth's key, a copy is whole. Without it, a copy has only the tables listed |
| 4 | // below, less their private rows, which is what the site shows anyone. |
| 5 | export const app = new Hono(); |
| 6 | |
| 7 | const token = process.env.CLOVER_SOT_KEY ?? null; |
| 8 | |
| 9 | /** per database, the tables a public copy keeps, and the statements that first delete what is private */ |
| 10 | const publicCopies = new Map([ |
| 11 | ["cache.sqlite", { |
| 12 | tables: ["media_files", "permissions", "derived_roots", "derived_files", "derived_refs"], |
| 13 | redact: [ |
| 14 | // what listings leave out: paths behind a password, and hidden or unlisted names. |
| 15 | // a directory takes its children with it, and a file its derived refs |
| 16 | `delete from media_files where exists (select 1 from permissions where lower(media_files.path) glob lower(prefix) || '*') |
| 17 | or path like '%/.%' or path like '%/\\_unlisted%' escape '\\'`, |
| 18 | "delete from permissions", |
| 19 | "delete from derived_roots where id not in (select root from derived_refs)", |
| 20 | ], |
| 21 | }], |
| 22 | ["questions.sqlite", { |
| 23 | tables: ["questions"], |
| 24 | redact: [`delete from questions where type <> ${questions.types.normal}`], |
| 25 | }], |
| 26 | ]); |
| 27 | |
| 28 | /** a connection of each database's own. copies read through it, and its `data_version` changes once another connection commits */ |
| 29 | const readers = new Map<string, sqlite.DatabaseSync>(); |
| 30 | incremental.onDispose(import.meta, () => readers.forEach((reader) => reader.close())); |
| 31 | /** the `data_version` each copy was written at, by its file */ |
| 32 | const versions = new Map<string, number>(); |
| 33 | |
| 34 | app.get("/db/:name", (c) => { |
| 35 | const name = c.req.param("name"); |
| 36 | const publicCopy = publicCopies.get(name); |
| 37 | if (!publicCopy) return c.notFound(); |
| 38 | const authorization = c.req.header("Authorization"); |
| 39 | // a wrong key fails, so a deploy never builds from a public copy |
| 40 | if (authorization !== undefined && authorization !== token) { |
| 41 | return c.json({ error: "invalid authorization header" }, 401); |
| 42 | } |
| 43 | const file = getCopy(name, authorization === undefined ? publicCopy : null); |
| 44 | // a newer copy can replace the file, while this response reads the one it opened |
| 45 | const fd = fs.openSync(file, "r"); |
| 46 | c.header("Content-Type", "application/vnd.sqlite3"); |
| 47 | c.header("Content-Length", String(fs.fstatSync(fd).size)); |
| 48 | return c.body(stream.Readable.toWeb(fs.createReadStream(file, { fd })) as ReadableStream); |
| 49 | }); |
| 50 | |
| 51 | /** a file with a copy of a database, written again once the database changes */ |
| 52 | function getCopy(name: string, publicCopy: { tables: string[]; redact: string[] } | null): string { |
| 53 | const { location } = getDatabase(name); |
| 54 | let reader = readers.get(location); |
| 55 | if (!reader) readers.set(location, reader = new sqlite.DatabaseSync(location, { readOnly: true })); |
| 56 | const version = Number(reader.prepare("pragma data_version;").get()?.data_version); |
| 57 | const file = `${location}.${publicCopy ? "public" : "full"}`; |
| 58 | if (versions.get(file) === version && fs.existsSync(file)) return file; |
| 59 | |
| 60 | const temp = `${file}.tmp`; |
| 61 | fs.rmSync(temp, { force: true }); |
| 62 | reader.prepare("vacuum into ?").run(temp); |
| 63 | if (publicCopy) { |
| 64 | using copy = new sqlite.DatabaseSync(temp); |
| 65 | copy.exec("pragma foreign_keys = on;"); |
| 66 | for (const statement of publicCopy.redact) copy.exec(statement); |
| 67 | // every other table goes. a virtual table goes first, taking the tables that hold its rows |
| 68 | const objects = copy.prepare(` |
| 69 | select type, name from sqlite_master |
| 70 | where type in ('table', 'view', 'trigger') and name not like 'sqlite_%' |
| 71 | order by sql like 'create virtual%' desc |
| 72 | `).all() as Array<{ type: string; name: string }>; |
| 73 | for (const { type, name } of objects) { |
| 74 | if (type === "table" && (name === "clover_migrations" || publicCopy.tables.includes(name))) continue; |
| 75 | copy.exec(`drop ${type} if exists "${name}";`); |
| 76 | } |
| 77 | // a model creates the table again, empty, once the copy no longer says it was migrated |
| 78 | copy.exec("delete from clover_migrations where key not in (select name from sqlite_master where type = 'table');"); |
| 79 | // without `secure_delete`, deleted rows linger in a file until a vacuum writes it anew |
| 80 | copy.exec("vacuum;"); |
| 81 | } |
| 82 | fs.renameSync(temp, file); |
| 83 | versions.set(file, version); |
| 84 | return file; |
| 85 | } |
| 86 | |
| 87 | import { getDatabase } from "#sitegen/drizzle"; |
| 88 | import * as questions from "#src/q+a/models/questions.ts"; |
| 89 | import * as incremental from "@clo/lib/incremental"; |
| 90 | import { Hono } from "hono"; |
| 91 | import * as fs from "node:fs"; |
| 92 | import * as sqlite from "node:sqlite"; |
| 93 | import * as stream from "node:stream"; |