1// One-shot migration of cache.sqlite from the old bitfield processor
2// tracking ("v1") to the processors/file_processors tables ("v2").
3//
4// CLOVER_DB=.clover node run migrate-db
5//
6// There is exactly one real copy of this database; migrate it once locally,
7// verify the site works, then place the migrated file on all machines
8// alongside the new code. The old `processed` column packed a 16-bit hash of
9// the applicable processor set plus per-processor "ran" bits indexed into
10// the `processors` string, whose entries were [letter id][2-char hash of
11// the processor's source code]. Completions are carried over as
12// done-at-current-version: the first sweep after migration must re-run
13// NOTHING (re-encoding the entire store would take weeks). Force re-runs
14// later by bumping a processor's version.
15//
16// This file intentionally avoids importing the models (their prepared
17// statements require the new schema) and instead uses raw SQL. It imports
18// the registry only for processor names, versions, and applicability.
19
20// the old positional letter ids, frozen. a..q matched the old array order.
21const letterMap: Record<string, string> = {
22 a: "dimensions",
23 b: "duration",
24 c: "text-contents",
25 d: "highlight-code",
26 e: "image-subsets",
27 f: "av1-au",
28 g: "av1-ah",
29 h: "av1-am",
30 i: "av1-al",
31 j: "av1-ad",
32 k: "opus-h",
33 l: "opus-m",
34 m: "opus-d",
35 n: "dash",
36 o: "gzip",
37 p: "zstd",
38 q: "h264-hls",
39};
40
41export async function main() {
42 const db = getDb("cache.sqlite");
43 const raw = db.node;
44 console.info(`migrating ${db.file}`);
45
46 // -- preflight --
47 const columns = raw.prepare(`pragma table_info(media_files)`).all() as {
48 name: string;
49 }[];
50 if (columns.length === 0) {
51 console.error("media_files does not exist; nothing to migrate");
52 process.exit(1);
53 }
54 if (!columns.some((c) => c.name === "processed")) {
55 console.info("already migrated (no `processed` column); nothing to do");
56 return;
57 }
58
59 // -- backup --
60 raw.exec(`pragma wal_checkpoint(truncate);`);
61 const backup = db.file + ".pre-v2";
62 if (fs.existsSync(backup) && !process.argv.includes("--force")) {
63 console.error(`backup ${backup} already exists; pass --force to continue`);
64 process.exit(1);
65 }
66 // content-only copy: fs.copyFile's metadata preservation gets EPERM'd on
67 // the NAS datasets (restrictive ACL mode), plain writes do not.
68 await stream.promises.pipeline(
69 fs.createReadStream(db.file),
70 fs.createWriteStream(backup),
71 );
72 console.info(`backed up to ${backup}`);
73
74 const now = Date.now();
75 const summary = new Map<string, { done: number; pending: number }>();
76 for (const p of registry.processors) {
77 summary.set(p.name, { done: 0, pending: 0 });
78 }
79
80 raw.exec(`pragma foreign_keys = off;`);
81 raw.exec(`begin;`);
82 try {
83 // -- new tables (kept in sync with models/ProcessorState.ts) --
84 raw.exec(/* SQL */ `
85 create table if not exists processors (
86 id integer primary key autoincrement,
87 name text not null unique,
88 version integer not null
89 );
90 create table if not exists file_processors (
91 file integer not null references media_files(id) on delete cascade,
92 processor integer not null references processors(id) on delete cascade,
93 version integer not null,
94 status integer not null,
95 updated integer not null,
96 error text,
97 primary key (file, processor)
98 );
99 create index if not exists file_processors_processor
100 on file_processors (processor);
101 `);
102 // mark the table-creation key so models/ProcessorState.ts skips its DDL
103 raw.prepare(
104 `insert or ignore into clover_migrations (key, version) values (?, ?);`,
105 ).run("processor_state", 1);
106
107 const ids = new Map<string, number>();
108 const insertProcessor = raw.prepare(
109 `insert into processors (name, version) values (?, ?)
110 on conflict(name) do update set version = excluded.version
111 returning id;`,
112 );
113 for (const p of registry.processors) {
114 const { id } = insertProcessor.get(p.name, p.version) as { id: number };
115 ids.set(p.name, id);
116 }
117
118 // -- carry over completions from the bitfield --
119 const files = raw.prepare(
120 `select id, path, processed, processors from media_files where kind = 1;`,
121 ).all() as {
122 id: number;
123 path: string;
124 processed: number;
125 processors: string;
126 }[];
127 const insertState = raw.prepare(
128 `insert or replace into file_processors
129 (file, processor, version, status, updated, error)
130 values (?, ?, ?, 1, ?, null);`,
131 );
132 let doneRows = 0;
133 let undecodable = 0;
134 for (const file of files) {
135 let entries: string[];
136 try {
137 entries = decodeProcessorLetters(file.processors);
138 } catch {
139 undecodable += 1;
140 continue;
141 }
142 for (let i = 0; i < entries.length; i += 1) {
143 if ((file.processed & (1 << (16 + i))) === 0) continue;
144 const name = letterMap[UNWRAP(entries[i])];
145 if (!name) continue; // processor no longer exists
146 const proc = registry.byName(name);
147 if (!proc) continue;
148 insertState.run(file.id, UNWRAP(ids.get(name)), proc.version, now);
149 UNWRAP(summary.get(name)).done += 1;
150 doneRows += 1;
151 }
152 }
153
154 // -- rebuild media_files without the bitfield columns --
155 raw.exec(/* SQL */ `
156 create table media_files_new (
157 id integer primary key autoincrement,
158 parent_id integer,
159 path text,
160 kind integer not null,
161 timestamp integer not null,
162 timestamp_updated integer not null default current_timestamp,
163 hash text not null,
164 size integer not null,
165 duration integer not null default 0,
166 dimensions text not null default "",
167 contents text not null,
168 dirsort text,
169 config text not null default "",
170 dir_reindex integer not null default 0,
171 pending integer not null default 0,
172 foreign key (parent_id) references media_files(id) on delete cascade
173 );
174 insert into media_files_new (
175 id, parent_id, path, kind, timestamp, timestamp_updated, hash, size,
176 duration, dimensions, contents, dirsort, config, dir_reindex, pending)
177 select
178 id, parent_id, path, kind, timestamp, timestamp_updated, hash, size,
179 duration, dimensions, contents, dirsort,
180 case when kind = 0 and processors like '{%' then processors else '' end,
181 case when kind = 0 and processed = 0 then 1 else 0 end,
182 0
183 from media_files;
184 drop table media_files;
185 alter table media_files_new rename to media_files;
186 `);
187
188 // -- collapse case duplicates --
189 // the file stores are case-insensitive, so two case spellings of one
190 // path are the same physical file. the old scanner could record both;
191 // keep the newest row (matching the new unique nocase index) and move
192 // any children over.
193 const dupeGroups = raw.prepare(
194 `select group_concat(id) ids, max(id) keep, lower(path) lp
195 from media_files group by lower(path) having count(*) > 1;`,
196 ).all() as { ids: string; keep: number; lp: string }[];
197 for (const group of dupeGroups) {
198 const drop = group.ids.split(",").map(Number)
199 .filter((id) => id !== group.keep);
200 console.warn(
201 `case-duplicate rows for ${group.lp}: keeping ${group.keep}, dropping ${drop.join(", ")}`,
202 );
203 const dropList = drop.join(",");
204 raw.exec(/* SQL */ `
205 update media_files set parent_id = ${group.keep}
206 where parent_id in (${dropList});
207 delete from file_processors where file in (${dropList});
208 delete from derived_refs where file in (${dropList});
209 delete from media_files where id in (${dropList});
210 `);
211 }
212
213 raw.exec(/* SQL */ `
214 create unique index media_files_path
215 on media_files (path collate nocase);
216 create index media_files_parent_id on media_files (parent_id);
217 create index media_files_file_children on media_files (kind, path);
218 create index media_files_dir_reindex on media_files (kind, dir_reindex);
219 `);
220
221 // -- compute `pending` from applicability minus completions --
222 const states = raw.prepare(
223 `select processor, version from file_processors where file = ?;`,
224 );
225 const setPending = raw.prepare(
226 `update media_files set pending = ? where id = ?;`,
227 );
228 const pendingPaths: string[] = [];
229 for (const file of files) {
230 const ext = extensionNonEmpty(file.path).toLowerCase();
231 const applicable = registry.applicableFor(ext);
232 if (applicable.length === 0) continue;
233 const done = new Map(
234 (states.all(file.id) as { processor: number; version: number }[])
235 .map((row) => [row.processor, row.version]),
236 );
237 const missing = applicable.filter(
238 (p) => done.get(UNWRAP(ids.get(p.name))) !== p.version,
239 );
240 if (missing.length === 0) continue;
241 setPending.run(missing.length, file.id);
242 for (const p of missing) UNWRAP(summary.get(p.name)).pending += 1;
243 if (pendingPaths.length < 32) {
244 pendingPaths.push(
245 `${file.path} (${missing.map((p) => p.name).join(", ")})`,
246 );
247 }
248 }
249
250 raw.exec(`commit;`);
251 raw.exec(`pragma foreign_keys = on;`);
252
253 const violations = raw.prepare(`pragma foreign_key_check;`).all();
254 if (violations.length > 0) {
255 console.error("foreign key violations after migration:", violations);
256 process.exit(1);
257 }
258 raw.exec(`vacuum;`);
259 raw.exec(`pragma wal_checkpoint(truncate);`);
260
261 // -- summary --
262 console.info("");
263 console.info(`migrated ${files.length} files, ${doneRows} completions`);
264 if (undecodable) {
265 console.warn(`${undecodable} files had undecodable processor strings`);
266 }
267 console.info("per-processor state (done / pending):");
268 for (const [name, { done, pending }] of summary) {
269 const warn = pending > 0 && heavyProcessors.has(name) ? " <-- WILL RUN" : "";
270 console.info(
271 ` ${name.padEnd(16)} ${String(done).padStart(6)} / ${String(pending).padStart(4)}${warn}`,
272 );
273 }
274 if (pendingPaths.length > 0) {
275 console.info("");
276 console.info("files with pending work (first 32):");
277 for (const line of pendingPaths) console.info(" " + line);
278 } else {
279 console.info("no files have pending work; first sweep will be a no-op");
280 }
281 } catch (err) {
282 raw.exec(`rollback;`);
283 raw.exec(`pragma foreign_keys = on;`);
284 console.error("migration failed and was rolled back");
285 throw err;
286 }
287}
288
289/** decode the old `processors` column into its letter ids */
290function decodeProcessorLetters(input: string): string[] {
291 return input
292 .split(";")
293 .filter(Boolean)
294 .map(([a, b, c]) => {
295 UNWRAP(b);
296 UNWRAP(c);
297 return UNWRAP(a);
298 });
299}
300
301/** mirror of MediaFile.extensionNonEmpty for a raw path string */
302function extensionNonEmpty(filePath: string) {
303 const basename = path.basename(filePath);
304 const ext = path.extname(basename);
305 if (ext === "") return basename;
306 return ext;
307}
308
309// processors expensive enough that an accidental re-run is a disaster
310const heavyProcessors = new Set([
311 "image-subsets",
312 "av1-au",
313 "av1-ah",
314 "av1-am",
315 "av1-al",
316 "av1-ad",
317 "opus-h",
318 "opus-m",
319 "opus-d",
320 "dash",
321 "h264-hls",
322]);
323
324import * as fs from "node:fs";
325import * as path from "node:path";
326import * as stream from "node:stream";
327
328import { getDb } from "#sitegen/sqlite";
329import { UNWRAP } from "@clo/lib/assert";
330
331import * as registry from "#src/file-viewer/indexer/registry.ts";