1// Every file and directory in the file store, as the indexer last saw it.
2// Paths fold case, like the stores the files live on (zfs smb datasets,
3// apfs), so two spellings of a path are one file.
4const db = getDatabase("cache.sqlite");
5
6export const kinds = { directory: 0, file: 1 } as const;
7export type Kind = keyof typeof kinds;
8
9export const table = sqliteTable("media_files", {
10 id: integer().primaryKey({ autoIncrement: true }),
11 parentId: integer("parent_id").references((): AnySQLiteColumn => table.id, { onDelete: "cascade" }),
12 /**
13 * has a leading slash, and no `/file` prefix.
14 * @example "/2025/waterfalls/waterfalls.mp3"
15 */
16 path: text().notNull(),
17 kind: sqliteNumericEnum(kinds)().notNull(),
18 /** milliseconds */
19 timestamp: integer().notNull(),
20 timestampUpdated: integer("timestamp_updated").notNull().default(sql`current_timestamp`),
21 /**
22 * sha1 of a file's contents, or of a directory's listing and readme. it
23 * names a file's derived assets, and busts caches.
24 */
25 hash: text().notNull(),
26 /** bytes */
27 size: integer().notNull(),
28 /** seconds, for audio and video */
29 duration: integer().notNull().default(0),
30 /** two numbers split by `x`, for images and video */
31 dimensions: text().notNull().default(""),
32 /** a directory's `readme.txt`, or a text file's highlighted text */
33 contents: text().notNull(),
34 /** a directory's explicit order, as json, from its `.dirsort` */
35 dirsort: text(),
36 /** a directory's json config from special files, like `.date` */
37 config: text().notNull().default(""),
38 /** 1 when the metadata pass must visit this directory again */
39 dirReindex: integer("dir_reindex").notNull().default(0),
40 /**
41 * processors queued or running for this file. at zero, its duration,
42 * dimensions, contents, and derived assets are as complete as they get.
43 */
44 pending: integer().notNull().default(0),
45}, (t) => [
46 uniqueIndex("media_files_path").on(sql`${t.path} collate nocase`),
47 index("media_files_parent_id").on(t.parentId),
48 // recursive file children
49 index("media_files_file_children").on(t.kind, t.path),
50 index("media_files_dir_reindex").on(t.kind, t.dirReindex),
51]);
52db.migrate(table, 3, {
53 // a `.date` day moved from midnight to noon utc, so the directories with one are read again
54 3: (db) =>
55 db.run(
56 sql`update media_files set dir_reindex = 1 where id in (select parent_id from media_files where path like '%/.date')`,
57 ),
58});
59
60export type MediaFile = ReturnType<typeof toMediaFile>;
61
62function toMediaFile(row: typeof table.$inferSelect) {
63 const basename = path.basename(row.path);
64 const extension = path.extname(row.path);
65 return {
66 ...row,
67 date: new Date(row.timestamp),
68 lastUpdateDate: new Date(row.timestampUpdated),
69 basename,
70 basenameWithoutExt: path.basename(row.path, extension),
71 extension,
72 /** the extension, or the whole name of a file without one */
73 extensionNonEmpty: extension || basename,
74 };
75}
76
77/** a file or directory by path, in any case. the root exists even before the first scan */
78export function getByPath(filePath: string): MediaFile | null {
79 const row = db.select().from(table).where(sql`${table.path} = ${filePath} collate nocase`).get();
80 if (row) return toMediaFile(row);
81 if (filePath !== "/") return null;
82 return toMediaFile({
83 id: 0,
84 parentId: null,
85 path: "/",
86 kind: "directory",
87 timestamp: 0,
88 timestampUpdated: Date.now(),
89 hash: "0".repeat(40),
90 size: 0,
91 duration: 0,
92 dimensions: "",
93 contents: "the file scanner has not been run yet",
94 dirsort: null,
95 config: "",
96 dirReindex: 0,
97 pending: 0,
98 });
99}
100
101export function getChildren(directory: { id: number }): MediaFile[] {
102 return db.select().from(table).where(eq(table.parentId, directory.id)).all().map(toMediaFile);
103}
104
105/** children without hidden or unlisted names, or ones with their own permissions in a public directory */
106export function getPublicChildren(directory: MediaFile): MediaFile[] {
107 const children = getChildren(directory).filter((child) =>
108 !child.basename.startsWith(".") && !child.basename.startsWith("_unlisted")
109 );
110 if (permissions.getByPrefix(directory.path) !== 0) return children;
111 return children.filter((child) => permissions.getExact(child.path) === 0);
112}
113
114export function getParent(file: MediaFile): MediaFile | null {
115 if (file.path === "/") return null;
116 const parent = getByPath(path.dirname(file.path));
117 ASSERT(!parent || parent.kind === "directory");
118 return parent;
119}
120
121/** every file under a directory, at any depth */
122export function getRecursiveFileChildren(directory: MediaFile): MediaFile[] {
123 if (directory.kind !== "directory") return [];
124 return db.select().from(table)
125 .where(and(like(table.path, `${directory.path}/%`), eq(table.kind, "file")))
126 .all().map(toMediaFile);
127}
128
129/** directories the metadata pass must visit, with every directory above them, by path */
130export function getDirectoriesToReindex(): Array<{ id: number; parentId: number | null; path: string }> {
131 return db.all(sql`
132 with recursive chain as (
133 select id, parent_id, path from media_files where kind = ${kinds.directory} and dir_reindex = 1
134 -- a directory's size and hash cover its children, so its parents change too
135 union
136 select m.id, m.parent_id, m.path from media_files m join chain on m.id = chain.parent_id
137 )
138 select distinct id, parent_id as parentId, path from chain order by path
139 `);
140}
141
142export function parseDimensions(file: MediaFile): { width: number; height: number } | null {
143 if (!file.dimensions) return null;
144 const [width, height] = file.dimensions.split("x").map(Number);
145 ASSERT(width);
146 ASSERT(height);
147 return { width, height };
148}
149
150// -- what the indexer writes --
151
152/** create or update a file, and the directories above it */
153export function createFile(file: {
154 path: string;
155 date: Date;
156 hash: string;
157 size: number;
158 duration: number;
159 dimensions: string;
160 contents: string;
161}): MediaFile {
162 ASSERT(!file.path.includes("\\") && file.path.startsWith("/"), `Invalid path: ${file.path}`);
163 const { id } = db.get<{ id: number }>(sql`
164 insert into media_files (path, parent_id, kind, timestamp, timestamp_updated, hash, size, duration, dimensions, contents)
165 values (${file.path}, ${getOrPutDirectoryId(path.dirname(file.path))}, ${kinds.file}, ${file.date.getTime()},
166 ${Date.now()}, ${file.hash}, ${file.size}, ${file.duration}, ${file.dimensions}, ${file.contents})
167 on conflict (path collate nocase) do update set
168 path = excluded.path,
169 timestamp = excluded.timestamp,
170 timestamp_updated = excluded.timestamp_updated,
171 hash = excluded.hash,
172 duration = excluded.duration,
173 size = excluded.size,
174 contents = excluded.contents
175 returning id
176 `);
177 return toMediaFile(UNWRAP(db.select().from(table).where(eq(table.id, id)).get()));
178}
179
180/** a directory's id, creating it and the directories above it when they are missing */
181export function getOrPutDirectoryId(dirPath: string): number {
182 ASSERT(!dirPath.includes("\\") && dirPath.startsWith("/"), `Invalid path: ${dirPath}`);
183 dirPath = path.normalize(dirPath);
184 const id = (dir: string) =>
185 db.select({ id: table.id }).from(table)
186 .where(and(sql`${table.path} = ${dir} collate nocase`, eq(table.kind, "directory")))
187 .get()?.id ?? null;
188 const create = (dir: string, parentId: number | null) =>
189 UNWRAP(
190 db.insert(table).values({
191 path: dir,
192 parentId,
193 kind: "directory",
194 timestamp: 0,
195 timestampUpdated: Date.now(),
196 hash: "",
197 size: 0,
198 duration: 0,
199 dimensions: "",
200 contents: "",
201 dirsort: "",
202 dirReindex: 1,
203 }).returning({ id: table.id }).get(),
204 ).id;
205
206 const existing = id(dirPath);
207 if (existing !== null) return existing;
208 if (dirPath === "/") return create(dirPath, null);
209 // walk up to a directory that exists, then back down, creating the rest
210 const parts: string[] = [];
211 let current = dirPath;
212 let parentId: number | null = null;
213 do {
214 parts.unshift(path.basename(current));
215 current = path.dirname(current);
216 parentId = id(current);
217 } while (parentId === null && current !== "/");
218 parentId ??= create(current, null);
219 for (const part of parts) {
220 current = path.join(current, part);
221 parentId = create(current, parentId);
222 }
223 return parentId;
224}
225
226/** a directory's metadata, once the metadata pass has visited it */
227export function markDirectoryProcessed(directory: {
228 id: number;
229 timestamp: Date;
230 contents: string;
231 size: number;
232 hash: string;
233 dirsort: null | string[];
234}) {
235 db.update(table).set({
236 dirReindex: 0,
237 timestamp: directory.timestamp.getTime(),
238 contents: directory.contents,
239 dirsort: directory.dirsort ? JSON.stringify(directory.dirsort) : "",
240 hash: directory.hash,
241 size: directory.size,
242 }).where(eq(table.id, directory.id)).run();
243}
244
245export function createOrUpdateDirectory(dirPath: string) {
246 markDirReindex({ id: getOrPutDirectoryId(dirPath) });
247}
248
249// the setters update the row they are given too, which callers keep using
250
251export function setConfig(file: { id: number; config?: string }, config: string) {
252 db.update(table).set({ config }).where(eq(table.id, file.id)).run();
253 file.config = config;
254}
255
256export function markDirReindex(file: { id: number; dirReindex?: number }) {
257 db.update(table).set({ dirReindex: 1 }).where(eq(table.id, file.id)).run();
258 file.dirReindex = 1;
259}
260
261/** what is in a directory changed, so the metadata pass visits it again */
262export function markParentDirReindex(file: MediaFile) {
263 const parent = getParent(file);
264 if (parent) markDirReindex(parent);
265}
266
267export function setPending(file: MediaFile, pending: number) {
268 db.update(table).set({ pending }).where(eq(table.id, file.id)).run();
269 file.pending = pending;
270}
271
272export function decPending(file: MediaFile) {
273 file.pending = UNWRAP(
274 db.update(table).set({ pending: sql`max(0, ${table.pending} - 1)` })
275 .where(eq(table.id, file.id)).returning({ pending: table.pending }).get(),
276 ).pending;
277}
278
279export function setDuration(file: MediaFile, duration: number) {
280 db.update(table).set({ duration }).where(eq(table.id, file.id)).run();
281 file.duration = duration;
282}
283
284export function setDimensions(file: MediaFile, dimensions: string) {
285 db.update(table).set({ dimensions }).where(eq(table.id, file.id)).run();
286 file.dimensions = dimensions;
287}
288
289export function setContents(file: MediaFile, contents: string) {
290 db.update(table).set({ contents }).where(eq(table.id, file.id)).run();
291 file.contents = contents;
292}
293
294/** delete a file, and the directories above it that it leaves empty */
295export function remove(file: { id: number }) {
296 db.run(sql`
297 with recursive items as (
298 select id, parent_id from media_files where id = ${file.id}
299 union all
300 select p.id, p.parent_id from media_files p
301 join items c on p.id = c.parent_id
302 where p.parent_id is not null
303 and not exists (select 1 from media_files child where child.parent_id = p.id and child.id <> c.id)
304 )
305 delete from media_files where id in (select id from items order by parent_id nulls last)
306 `);
307}
308
309/** adopt a new spelling of the same path, for a directory's children too */
310export function updatePath(file: MediaFile, newPath: string) {
311 if (file.kind === "directory") {
312 const [from, to] = [file.path + "/", newPath + "/"];
313 db.run(sql`
314 update media_files set path = ${to} || substr(path, length(${from}) + 1)
315 where lower(substr(path, 1, length(${from}))) = lower(${from})
316 `);
317 }
318 db.update(table).set({ path: newPath }).where(eq(table.id, file.id)).run();
319 Object.assign(file, toMediaFile({ ...file, path: newPath }));
320}
321
322export { db };
323
324import { getDatabase, sqliteNumericEnum } from "#sitegen/drizzle";
325import { ASSERT, UNWRAP } from "@clo/lib/assert";
326import { and, eq, like, sql } from "drizzle-orm";
327import { type AnySQLiteColumn, index, integer, sqliteTable, text, uniqueIndex } from "drizzle-orm/sqlite-core";
328import * as path from "node:path/posix";
329import * as permissions from "./permissions.ts";