1const db = getDb("cache.sqlite");
2db.table(
3 "media_files",
4 /* SQL */ `
5 create table media_files (
6 id integer primary key autoincrement,
7 parent_id integer,
8 path text,
9 kind integer not null,
10 timestamp integer not null,
11 timestamp_updated integer not null default current_timestamp,
12 hash text not null,
13 size integer not null,
14 duration integer not null default 0,
15 dimensions text not null default "",
16 contents text not null,
17 dirsort text,
18 config text not null default "",
19 dir_reindex integer not null default 0,
20 pending integer not null default 0,
21 foreign key (parent_id) references media_files(id) on delete cascade
22 );
23 -- path lookups fold case: the underlying file stores (zfs smb datasets,
24 -- apfs) are case-insensitive, so two case spellings are one file
25 create unique index media_files_path on media_files (path collate nocase);
26 -- index for quickly looking up children
27 create index media_files_parent_id on media_files (parent_id);
28 -- index for quickly looking up recursive file children
29 create index media_files_file_children on media_files (kind, path);
30 -- index for finding directories that need re-indexing
31 create index media_files_dir_reindex on media_files (kind, dir_reindex);
32`,
33);
34
35export enum MediaFileKind {
36 directory = 0,
37 file = 1,
38}
39export class MediaFile {
40 id!: number;
41 parent_id!: number | null;
42 /**
43 * Has leading slash, does not have `/file` prefix.
44 * @example "/2025/waterfalls/waterfalls.mp3"
45 */
46 path!: string;
47 kind!: MediaFileKind;
48 private timestamp!: number;
49 private timestamp_updated!: number;
50 /** for mp3/mp4 files, measured in seconds */
51 duration?: number;
52 /** for images and videos, the dimensions. Two numbers split by `x` */
53 dimensions?: string;
54 /**
55 * sha1 of
56 * - files: the contents
57 * - directories: the JSON array of strings + the content of `readme.txt`
58 * this is used
59 * - to inform changes in caching mechanisms (etag, page render cache)
60 * - as a filename for compressed files (.clover/compressed/<hash>.{gz,zstd})
61 */
62 hash!: string;
63 /**
64 * Depends on the file kind.
65 *
66 * - For directories, this is the contents of `readme.txt`, if it exists.
67 * - Otherwise, it is an empty string.
68 */
69 contents!: string;
70 /**
71 * For directories, if this is set, it is a JSON-encoded array of the explicit
72 * sorting order. Derived off of `.dirsort` files.
73 */
74 dirsort!: string | null;
75 /** in bytes */
76 size!: number;
77 /**
78 * For directories, a JSON-encoded object derived from special files
79 * (`.date` sets hideChildrenDates). Empty string otherwise.
80 */
81 config!: string;
82 /** for directories: 1 when the metadata pass must revisit this dir */
83 dir_reindex!: number;
84 /**
85 * number of processors queued or running for this file. when zero, all
86 * derived data (duration, dimensions, contents, derived assets) is as
87 * complete as it will get. the UI uses this to show "still processing".
88 */
89 pending!: number;
90
91 // -- instance ops --
92 get date() {
93 return new Date(this.timestamp);
94 }
95 get lastUpdateDate() {
96 return new Date(this.timestamp_updated);
97 }
98 parseDimensions() {
99 const dimensions = this.dimensions;
100 if (!dimensions) return null;
101 const [width, height] = dimensions.split("x").map(Number);
102 ASSERT(width);
103 ASSERT(height);
104 return { width, height };
105 }
106 get basename() {
107 return path.basename(this.path);
108 }
109 get basenameWithoutExt() {
110 return path.basename(this.path, path.extname(this.path));
111 }
112 get extension() {
113 return path.extname(this.path);
114 }
115 get extensionNonEmpty() {
116 const { basename } = this;
117 const ext = path.extname(basename);
118 if (ext === "") return basename;
119 return ext;
120 }
121 getChildren() {
122 return MediaFile.getChildren(this.id);
123 }
124 getPublicChildren() {
125 const children = MediaFile.getChildren(this.id).filter((x) =>
126 !x.basename.startsWith(".") && !x.basename.startsWith("_unlisted")
127 );
128 if (FilePermissions.getByPrefix(this.path) == 0) {
129 return children.filter(({ path }) => FilePermissions.getExact(path) == 0);
130 }
131 return children;
132 }
133 getParent() {
134 const dirPath = this.path;
135 if (dirPath === "/") return null;
136 const parentPath = path.dirname(dirPath);
137 if (parentPath === dirPath) return null;
138 const result = MediaFile.getByPath(parentPath);
139 if (!result) return null;
140 ASSERT(result.kind === MediaFileKind.directory);
141 return result;
142 }
143 setConfig(config: string) {
144 setConfigQuery.run({ id: this.id, config });
145 this.config = config;
146 }
147 markDirReindex() {
148 markDirReindexQuery.run(this.id);
149 this.dir_reindex = 1;
150 }
151 setPending(pending: number) {
152 setPendingQuery.run({ id: this.id, pending });
153 this.pending = pending;
154 }
155 decPending() {
156 this.pending = decPendingQuery.getNonNull(this.id).pending;
157 }
158 setDuration(duration: number) {
159 setDurationQuery.run({ id: this.id, duration });
160 this.duration = duration;
161 }
162 setDimensions(dimensions: string) {
163 setDimensionsQuery.run({ id: this.id, dimensions });
164 this.dimensions = dimensions;
165 }
166 setContents(contents: string) {
167 setContentsQuery.run({ id: this.id, contents });
168 this.contents = contents;
169 }
170 getRecursiveFileChildren() {
171 if (this.kind !== MediaFileKind.directory) return [];
172 return getChildrenFilesRecursiveQuery.array(this.path + "/");
173 }
174 delete() {
175 deleteCascadeQuery.run({ id: this.id });
176 }
177 /** adopt a new spelling of the same path (the file stores fold case) */
178 updatePath(newPath: string) {
179 if (this.kind === MediaFileKind.directory) {
180 updatePathPrefixQuery.run({ old: this.path + "/", new: newPath + "/" });
181 }
182 updatePathQuery.run({ id: this.id, path: newPath });
183 this.path = newPath;
184 }
185
186 // -- static ops --
187 static getByPath(filePath: string): MediaFile | null {
188 const result = getByPathQuery.get(filePath);
189 if (result) return result;
190 if (filePath === "/") {
191 return Object.assign(new MediaFile(), {
192 id: 0,
193 parent_id: null,
194 path: "/",
195 kind: MediaFileKind.directory,
196 timestamp: 0,
197 timestamp_updated: Date.now(),
198 hash: "0".repeat(40),
199 contents: "the file scanner has not been run yet",
200 dirsort: null,
201 size: 0,
202 config: "",
203 dir_reindex: 0,
204 pending: 0,
205 });
206 }
207 return null;
208 }
209 static createFile({
210 path: filePath,
211 date,
212 hash,
213 size,
214 duration,
215 dimensions,
216 contents,
217 }: CreateFile) {
218 ASSERT(
219 !filePath.includes("\\") && filePath.startsWith("/"),
220 `Invalid path: ${filePath}`,
221 );
222 return createFileQuery.getNonNull({
223 path: filePath,
224 parentId: MediaFile.getOrPutDirectoryId(path.dirname(filePath)),
225 timestamp: date.getTime(),
226 timestampUpdated: Date.now(),
227 hash,
228 size,
229 duration,
230 dimensions,
231 contents,
232 });
233 }
234 static getOrPutDirectoryId(filePath: string) {
235 ASSERT(
236 !filePath.includes("\\") && filePath.startsWith("/"),
237 `Invalid path: ${filePath}`,
238 );
239 filePath = path.normalize(filePath);
240 const row = getDirectoryIdQuery.get(filePath);
241 if (row) return row.id;
242 let current = filePath;
243 let parts = [];
244 let parentId: null | number = null;
245 if (filePath === "/") {
246 return createDirectoryQuery.getNonNull({
247 path: filePath,
248 parentId,
249 time: Date.now(),
250 }).id;
251 }
252 // walk up the path until we find a directory that exists
253 do {
254 parts.unshift(path.basename(current));
255 current = path.dirname(current);
256 parentId = getDirectoryIdQuery.get(current)?.id ?? null;
257 } while (parentId == undefined && current !== "/");
258 if (parentId == undefined) {
259 parentId = createDirectoryQuery.getNonNull({
260 path: current,
261 parentId,
262 time: Date.now(),
263 }).id;
264 }
265 // walk back down the path, creating directories as needed
266 for (const part of parts) {
267 current = path.join(current, part);
268 ASSERT(parentId != undefined);
269 parentId = createDirectoryQuery.getNonNull({
270 path: current,
271 parentId,
272 time: Date.now(),
273 }).id;
274 }
275 return parentId;
276 }
277 static markDirectoryProcessed({
278 id,
279 timestamp,
280 contents,
281 size,
282 hash,
283 dirsort,
284 }: MarkDirectoryProcessed) {
285 markDirectoryProcessedQuery.get({
286 id,
287 timestamp: timestamp.getTime(),
288 contents,
289 dirsort: dirsort ? JSON.stringify(dirsort) : "",
290 hash,
291 size,
292 });
293 }
294 static createOrUpdateDirectory(dirPath: string) {
295 const id = MediaFile.getOrPutDirectoryId(dirPath);
296 return updateDirectoryQuery.get(id);
297 }
298 static getChildren(id: number) {
299 return getChildrenQuery.array(id);
300 }
301 static getDirectoriesToReindex() {
302 return getDirectoriesToReindexQuery.array();
303 }
304 static db = db;
305}
306
307// Create a `file` entry with a given path, date, file hash, size, and duration
308// If the file already exists, update the date and duration.
309// If the file exists and the hash is different, sets `compress` to 0.
310interface CreateFile {
311 path: string;
312 date: Date;
313 hash: string;
314 size: number;
315 duration: number;
316 dimensions: string;
317 contents: string;
318}
319
320// Set the `processed` flag true and update the metadata for a directory
321export interface MarkDirectoryProcessed {
322 id: number;
323 timestamp: Date;
324 contents: string;
325 size: number;
326 hash: string;
327 dirsort: null | string[];
328}
329
330export interface DirConfig {
331 /** Overridden sorting */
332 sort: string[];
333}
334
335// -- queries --
336
337// Get a directory ID by path, creating it if it doesn't exist
338const createDirectoryQuery = db.prepare<
339 [{ path: string; parentId: number | null; time: number }],
340 { id: number }
341>(
342 /* SQL */ `
343 insert into media_files (
344 path, parent_id, kind, timestamp, timestamp_updated, hash,
345 size, duration, dimensions, contents, dirsort, dir_reindex)
346 values (
347 $path, $parentId, ${MediaFileKind.directory}, 0, $time, '',
348 0, 0, '', '', '', 1)
349 returning id;
350`,
351);
352const getDirectoryIdQuery = db.prepare<[string], { id: number }>(/* SQL */ `
353 SELECT id FROM media_files
354 WHERE path = ? collate nocase AND kind = ${MediaFileKind.directory};
355`);
356const createFileQuery = db.prepare<[{
357 path: string;
358 parentId: number;
359 timestamp: number;
360 timestampUpdated: number;
361 hash: string;
362 size: number;
363 duration: number;
364 dimensions: string;
365 contents: string;
366}], void>(/* SQL */ `
367 insert into media_files (
368 path, parent_id, kind, timestamp, timestamp_updated, hash,
369 size, duration, dimensions, contents)
370 values (
371 $path, $parentId, ${MediaFileKind.file}, $timestamp, $timestampUpdated,
372 $hash, $size, $duration, $dimensions, $contents)
373 on conflict(path collate nocase) do update set
374 path = excluded.path,
375 timestamp = excluded.timestamp,
376 timestamp_updated = excluded.timestamp_updated,
377 hash = excluded.hash,
378 duration = excluded.duration,
379 size = excluded.size,
380 contents = excluded.contents
381 returning *;
382`).as(MediaFile);
383const setConfigQuery = db.prepare<[{
384 id: number;
385 config: string;
386}]>(/* SQL */ `
387 update media_files set config = $config where id = $id;
388`);
389const markDirReindexQuery = db.prepare<[id: number]>(/* SQL */ `
390 update media_files set dir_reindex = 1 where id = ?;
391`);
392const setPendingQuery = db.prepare<[{
393 id: number;
394 pending: number;
395}]>(/* SQL */ `
396 update media_files set pending = $pending where id = $id;
397`);
398const decPendingQuery = db.prepare<[id: number], { pending: number }>(
399 /* SQL */ `
400 update media_files set pending = max(0, pending - 1) where id = ?
401 returning pending;
402`,
403);
404const setDurationQuery = db.prepare<[{
405 id: number;
406 duration: number;
407}]>(/* SQL */ `
408 update media_files set duration = $duration where id = $id;
409`);
410const setDimensionsQuery = db.prepare<[{
411 id: number;
412 dimensions: string;
413}]>(/* SQL */ `
414 update media_files set dimensions = $dimensions where id = $id;
415`);
416const setContentsQuery = db.prepare<[{
417 id: number;
418 contents: string;
419}]>(/* SQL */ `
420 update media_files set contents = $contents where id = $id;
421`);
422const getByPathQuery = db.prepare<[string]>(/* SQL */ `
423 select * from media_files where path = ? collate nocase;
424`).as(MediaFile);
425const updatePathQuery = db.prepare<[{ id: number; path: string }]>(/* SQL */ `
426 update media_files set path = $path where id = $id;
427`);
428const updatePathPrefixQuery = db.prepare<[{ old: string; new: string }]>(
429 /* SQL */ `
430 update media_files
431 set path = $new || substr(path, length($old) + 1)
432 where lower(substr(path, 1, length($old))) = lower($old);
433`,
434);
435const markDirectoryProcessedQuery = db.prepare<[{
436 timestamp: number;
437 contents: string;
438 dirsort: string;
439 hash: string;
440 size: number;
441 id: number;
442}]>(/* SQL */ `
443 update media_files set
444 dir_reindex = 0,
445 timestamp = $timestamp,
446 contents = $contents,
447 dirsort = $dirsort,
448 hash = $hash,
449 size = $size
450 where id = $id;
451`);
452const updateDirectoryQuery = db.prepare<[id: number]>(/* SQL */ `
453 update media_files set dir_reindex = 1 where id = ?;
454`);
455
456const getChildrenQuery = db.prepare<[id: number]>(/* SQL */ `
457 select * from media_files where parent_id = ?;
458`).as(MediaFile);
459const getChildrenFilesRecursiveQuery = db.prepare<[dir: string]>(/* SQL */ `
460 select * from media_files
461 where path like ? || '%'
462 and kind = ${MediaFileKind.file}
463`).as(MediaFile);
464const deleteCascadeQuery = db.prepare<[{ id: number }]>(/* SQL */ `
465 with recursive items as (
466 select id, parent_id from media_files where id = $id
467 union all
468 select p.id, p.parent_id
469 from media_files p
470 join items c on p.id = c.parent_id
471 where p.parent_id is not null
472 and not exists (
473 select 1 from media_files child
474 where child.parent_id = p.id
475 and child.id <> c.id
476 )
477 )
478 delete from media_files
479 where id in (select id from items order by parent_id nulls last)
480`);
481const getDirectoriesToReindexQuery = db.prepare(`
482 with recursive directory_chain as (
483 -- base case
484 select id, parent_id, path from media_files
485 where kind = 0 and dir_reindex = 1
486 -- recurse to find all parents so that size/hash can be updated
487 union
488 select m.id, m.parent_id, m.path
489 from media_files m
490 inner join directory_chain d on m.id = d.parent_id
491 )
492 select distinct id, parent_id, path
493 from directory_chain
494 order by path;
495`).as(MediaFile);
496
497import { getDb } from "#sitegen/sqlite";
498import { ASSERT } from "@clo/lib/assert";
499import * as path from "node:path/posix";
500import { FilePermissions } from "./FilePermissions.ts";