Repository Public
1// The owner's tree: folders, files, versions, trash. Every function that changes names or structure must run inside a2// transaction that holds lockOwner(ownerId); the unique indexes are the backstop, not the mechanism.3import { nameKey, nextFreeName, previewKind, sanitizeName } from '@spd/shared';4import type { ObjectRow } from './blob.ts';5import { notFound, UserError } from './config.ts';6import type { Db } from './db.ts';7import { enqueue } from './jobs.ts';89export type OnConflict = 'fail' | 'rename' | 'version';10export interface FolderRow { id: string; owner_id: string; parent_id: string | null; name: string; name_key: string; starred_at: Date | null; trashed_at: Date | null; trash_root: string | null; created_at: Date; updated_at: Date }11export interface FileRow {12 id: string; owner_id: string; folder_id: string; name: string; name_key: string; current_version_id: string; size: string; sha256: string; origin: string;13 starred_at: Date | null; trashed_at: Date | null; trash_root: string | null; created_at: Date; updated_at: Date;14 scan_status?: string; thumb?: string | null; tag_ids?: string[]; versions?: number; object_id?: string;15}1617export const folderJson = (f: FolderRow) => ({ id: f.id, type: 'folder' as const, name: f.name, parentId: f.parent_id, starred: !!f.starred_at, createdAt: f.created_at, updatedAt: f.updated_at, trashedAt: f.trashed_at });18export const fileJson = (f: FileRow) => ({19 id: f.id, type: 'file' as const, name: f.name, folderId: f.folder_id, size: Number(f.size), sha256: f.sha256, kind: previewKind(f.name), origin: f.origin, starred: !!f.starred_at,20 createdAt: f.created_at, updatedAt: f.updated_at, trashedAt: f.trashed_at, scan: f.scan_status ?? 'pending', thumb: f.thumb === 'ready', tagIds: f.tag_ids ?? [], versions: f.versions ?? 1,21});22/** Columns every file listing needs; alias the files table as f. */23export const fileSelect = (t: Db) => t`f.*, f.size::text as size, o.scan_status, o.id as object_id,24 (select status from previews p where p.object_id = o.id and p.kind = 'thumb') as thumb,25 coalesce((select array_agg(ft.tag_id) from file_tags ft where ft.file_id = f.id), '{}') as tag_ids,26 (select count(*)::int from file_versions v2 where v2.file_id = f.id) as versions`;27export const fileJoin = (t: Db) => t`join file_versions v on v.id = f.current_version_id join storage_objects o on o.id = v.object_id`;2829const conflict = (name: string) => new UserError('name_conflict', `an item named "${name}" already exists here`, 409, { name });3031export async function rootOf(t: Db, ownerId: string): Promise<FolderRow> {32 const [r] = await t<FolderRow[]>`select * from folders where owner_id = ${ownerId} and parent_id is null`;33 if (!r) throw notFound('root folder missing');34 return r;35}36/** A live folder of this owner ('root' = the root). Foreign or unknown ids are indistinguishable: 404. */37export async function getFolder(t: Db, ownerId: string, id: string): Promise<FolderRow> {38 if (id === 'root') return rootOf(t, ownerId);39 const [r] = await t<FolderRow[]>`select * from folders where id = ${id} and owner_id = ${ownerId} and trashed_at is null`;40 if (!r) throw notFound('folder not found');41 return r;42}43export async function getFile(t: Db, ownerId: string, id: string, o: { trashed?: boolean } = {}): Promise<FileRow> {44 const [r] = await t<FileRow[]>`select ${fileSelect(t)} from files f ${fileJoin(t)} where f.id = ${id} and f.owner_id = ${ownerId} ${o.trashed ? t`` : t`and f.trashed_at is null`}`;45 if (!r) throw notFound('file not found');46 return r;47}48export async function breadcrumbs(t: Db, folder: FolderRow): Promise<{ id: string; name: string }[]> {49 const rows = await t<{ id: string; name: string; depth: number }[]>`50 with recursive up as (select id, parent_id, name, 0 as depth from folders where id = ${folder.id}51 union all select p.id, p.parent_id, p.name, up.depth + 1 from folders p join up on p.id = up.parent_id where up.depth < 200)52 select id, name, depth from up order by depth desc`;53 return rows.map((r) => ({ id: r.id, name: r.name }));54}55/** Name keys of the live children (folders and files) of a folder. */56async function takenKeys(t: Db, folderId: string, except?: { folderId?: string; fileId?: string }): Promise<Map<string, { type: 'folder' | 'file'; id: string }>> {57 const rows = await t<{ id: string; name_key: string; type: 'folder' | 'file' }[]>`58 select id, name_key, 'folder' as type from folders where parent_id = ${folderId} and trashed_at is null59 union all select id, name_key, 'file' as type from files where folder_id = ${folderId} and trashed_at is null`;60 const m = new Map<string, { type: 'folder' | 'file'; id: string }>();61 for (const r of rows) if (r.id !== except?.folderId && r.id !== except?.fileId) m.set(r.name_key, { type: r.type, id: r.id });62 return m;63}64const touch = (t: Db, folderId: string) => t`update folders set updated_at = now() where id = ${folderId}`;6566/** Create a folder. `reuse` returns the existing folder of that name (folder uploads, portal sub-folders). */67export async function createFolder(t: Db, ownerId: string, parentId: string, rawName: string, onConflict: 'fail' | 'rename' | 'reuse' = 'fail'): Promise<FolderRow> {68 let name = sanitizeName(rawName, 'Nouveau dossier');69 const taken = await takenKeys(t, parentId);70 const hit = taken.get(nameKey(name));71 if (hit) {72 if (onConflict === 'reuse' && hit.type === 'folder') { const [f] = await t<FolderRow[]>`select * from folders where id = ${hit.id}`; return f!; }73 if (onConflict === 'fail') throw conflict(name);74 name = nextFreeName(name, (k) => taken.has(k));75 }76 const [f] = await t<FolderRow[]>`insert into folders (owner_id, parent_id, name, name_key) values (${ownerId}, ${parentId}, ${name}, ${nameKey(name)}) returning *`;77 await touch(t, parentId);78 return f!;79}80/** Walk/create "a/b/c" below a folder, reusing existing folders. */81export async function ensurePath(t: Db, ownerId: string, baseId: string, segments: string[]): Promise<string> {82 let cur = baseId;83 for (const s of segments.slice(0, 32)) cur = (await createFolder(t, ownerId, cur, s, 'reuse')).id;84 return cur;85}8687/**88 * Put content at folder/name. A name already used by a file: `version` appends a version, `rename` picks a free name,89 * `fail` raises 409. A name used by a folder can never be versioned. Returns the file and whether it is new.90 */91export async function addFile(t: Db, o: { ownerId: string; folderId: string; name: string; object: ObjectRow; onConflict: OnConflict; origin?: 'upload' | 'request' | 'copy'; actorId?: string | null }): Promise<{ file: FileRow; created: boolean }> {92 let name = sanitizeName(o.name);93 const taken = await takenKeys(t, o.folderId);94 const hit = taken.get(nameKey(name));95 if (hit) {96 if (o.onConflict === 'version' && hit.type === 'file') return { file: await addVersion(t, o.ownerId, hit.id, o.object, o.actorId ?? null), created: false };97 if (o.onConflict !== 'rename') throw conflict(name);98 name = nextFreeName(name, (k) => taken.has(k));99 }100 const [f] = await t<{ id: string }[]>`insert into files (owner_id, folder_id, name, name_key, size, sha256, origin)101 values (${o.ownerId}, ${o.folderId}, ${name}, ${nameKey(name)}, ${o.object.size}, ${o.object.sha256}, ${o.origin ?? 'upload'}) returning id`;102 const [v] = await t<{ id: string }[]>`insert into file_versions (file_id, seq, object_id, size, sha256, created_by)103 values (${f!.id}, 1, ${o.object.id}, ${o.object.size}, ${o.object.sha256}, ${o.actorId ?? null}) returning id`;104 await t`update files set current_version_id = ${v!.id} where id = ${f!.id}`;105 await touch(t, o.folderId);106 await afterNewContent(t, o.object);107 return { file: await getFile(t, o.ownerId, f!.id), created: true };108}109export async function addVersion(t: Db, ownerId: string, fileId: string, object: ObjectRow, actorId: string | null, note = ''): Promise<FileRow> {110 const [cur] = await t<{ seq: number; sha256: string }[]>`select v.seq, v.sha256 from files f join file_versions v on v.id = f.current_version_id where f.id = ${fileId} and f.owner_id = ${ownerId} and f.trashed_at is null for update of f`;111 if (!cur) throw notFound('file not found');112 if (cur.sha256 === object.sha256) return getFile(t, ownerId, fileId); // identical content: nothing to record113 const [m] = await t<{ seq: number }[]>`select max(seq) as seq from file_versions where file_id = ${fileId}`;114 const [v] = await t<{ id: string }[]>`insert into file_versions (file_id, seq, object_id, size, sha256, created_by, note)115 values (${fileId}, ${m!.seq + 1}, ${object.id}, ${object.size}, ${object.sha256}, ${actorId}, ${note}) returning id`;116 await t`update files set current_version_id = ${v!.id}, size = ${object.size}, sha256 = ${object.sha256}, updated_at = now() where id = ${fileId}`;117 await afterNewContent(t, object);118 return getFile(t, ownerId, fileId);119}120/** Antivirus scan and previews for content that just became reachable. */121async function afterNewContent(t: Db, object: ObjectRow): Promise<void> {122 if (object.scan_status === 'pending') await enqueue(t, 'scan_object', { objectId: object.id }, { dedupeKey: `scan:${object.id}` });123 await enqueue(t, 'preview', { objectId: object.id }, { dedupeKey: `preview:${object.id}` });124}125126export async function renameFolder(t: Db, ownerId: string, id: string, rawName: string): Promise<void> {127 const f = await getFolder(t, ownerId, id);128 if (!f.parent_id) throw new UserError('root_immutable', 'the root folder cannot be changed', 400);129 const name = sanitizeName(rawName);130 if ((await takenKeys(t, f.parent_id, { folderId: id })).has(nameKey(name))) throw conflict(name);131 await t`update folders set name = ${name}, name_key = ${nameKey(name)}, updated_at = now() where id = ${id}`;132}133export async function renameFile(t: Db, ownerId: string, id: string, rawName: string): Promise<void> {134 const f = await getFile(t, ownerId, id);135 const name = sanitizeName(rawName);136 if ((await takenKeys(t, f.folder_id, { fileId: id })).has(nameKey(name))) throw conflict(name);137 await t`update files set name = ${name}, name_key = ${nameKey(name)}, updated_at = now() where id = ${id}`;138}139140export interface Selection { fileIds: string[]; folderIds: string[] }141/** Move items into a folder. `rename` resolves collisions with a free name; `fail` aborts the whole move. */142export async function moveItems(t: Db, ownerId: string, sel: Selection, destId: string, onConflict: 'fail' | 'rename' = 'fail'): Promise<void> {143 const dest = await getFolder(t, ownerId, destId);144 const taken = await takenKeys(t, dest.id);145 const free = (name: string) => {146 if (!taken.has(nameKey(name))) return name;147 if (onConflict === 'fail') throw conflict(name);148 return nextFreeName(name, (k) => taken.has(k));149 };150 for (const id of sel.folderIds) {151 const f = await getFolder(t, ownerId, id);152 if (!f.parent_id) throw new UserError('root_immutable', 'the root folder cannot be moved', 400);153 if (f.parent_id === dest.id) continue;154 const [cycle] = await t`with recursive up as (select id, parent_id from folders where id = ${dest.id}155 union all select p.id, p.parent_id from folders p join up on p.id = up.parent_id) select 1 from up where id = ${id}`;156 if (cycle) throw new UserError('move_into_self', 'a folder cannot be moved into itself', 409);157 const name = free(f.name);158 await t`update folders set parent_id = ${dest.id}, name = ${name}, name_key = ${nameKey(name)}, updated_at = now() where id = ${id}`;159 taken.set(nameKey(name), { type: 'folder', id });160 }161 for (const id of sel.fileIds) {162 const f = await getFile(t, ownerId, id);163 if (f.folder_id === dest.id) continue;164 const name = free(f.name);165 await t`update files set folder_id = ${dest.id}, name = ${name}, name_key = ${nameKey(name)}, updated_at = now() where id = ${id}`;166 taken.set(nameKey(name), { type: 'file', id });167 }168 await touch(t, dest.id);169}170171const COPY_LIMIT = 20000;172/** Copy items into a folder (current versions only). Content is shared with the originals: no extra quota. */173export async function copyItems(t: Db, ownerId: string, sel: Selection, destId: string): Promise<{ files: number; folders: number }> {174 const dest = await getFolder(t, ownerId, destId);175 const count = { files: 0, folders: 0 };176 const copyFile = async (id: string, folderId: string, taken: Map<string, unknown>) => {177 const f = await getFile(t, ownerId, id);178 const name = nextFreeName(f.name, (k) => taken.has(k));179 const [n] = await t<{ id: string }[]>`insert into files (owner_id, folder_id, name, name_key, size, sha256, origin) values (${ownerId}, ${folderId}, ${name}, ${nameKey(name)}, ${f.size}, ${f.sha256}, 'copy') returning id`;180 const [v] = await t<{ id: string }[]>`insert into file_versions (file_id, seq, object_id, size, sha256, created_by) values (${n!.id}, 1, ${f.object_id!}, ${f.size}, ${f.sha256}, ${ownerId}) returning id`;181 await t`update files set current_version_id = ${v!.id} where id = ${n!.id}`;182 await t`insert into file_tags (file_id, tag_id) select ${n!.id}, tag_id from file_tags where file_id = ${id}`;183 taken.set(nameKey(name), true);184 if (++count.files + count.folders > COPY_LIMIT) throw new UserError('too_many_items', 'too many items to copy at once', 413);185 };186 const copyFolder = async (id: string, parentId: string, taken: Map<string, unknown>, depth: number) => {187 if (depth > 100) throw new UserError('too_deep', 'folder nesting too deep', 413);188 const src = await getFolder(t, ownerId, id);189 const name = nextFreeName(src.name, (k) => taken.has(k));190 const [n] = await t<{ id: string }[]>`insert into folders (owner_id, parent_id, name, name_key) values (${ownerId}, ${parentId}, ${name}, ${nameKey(name)}) returning id`;191 taken.set(nameKey(name), true);192 if (count.files + ++count.folders > COPY_LIMIT) throw new UserError('too_many_items', 'too many items to copy at once', 413);193 const inner = new Map<string, unknown>();194 // children are read before any insert below `n`, so copying a folder into itself terminates195 const subs = await t<{ id: string }[]>`select id from folders where parent_id = ${id} and trashed_at is null and id <> ${n!.id}`;196 const files = await t<{ id: string }[]>`select id from files where folder_id = ${id} and trashed_at is null`;197 for (const f of files) await copyFile(f.id, n!.id, inner);198 for (const s of subs) await copyFolder(s.id, n!.id, inner, depth + 1);199 };200 const taken = await takenKeys(t, dest.id);201 for (const id of sel.folderIds) await copyFolder(id, dest.id, taken, 0);202 for (const id of sel.fileIds) await copyFile(id, dest.id, taken);203 await touch(t, dest.id);204 return count;205}206207/** Move items to the trash. A trashed folder takes its whole live subtree with it, under one trash_root. */208export async function trashItems(t: Db, ownerId: string, sel: Selection): Promise<void> {209 for (const id of sel.folderIds) {210 const f = await getFolder(t, ownerId, id);211 if (!f.parent_id) throw new UserError('root_immutable', 'the root folder cannot be deleted', 400);212 const sub = t`with recursive sub as (select id from folders where id = ${id}213 union all select c.id from folders c join sub on c.parent_id = sub.id where c.trashed_at is null) select id from sub`;214 await t`update files set trashed_at = now(), trash_root = ${id} where trashed_at is null and folder_id in (${sub})`;215 await t`update folders set trashed_at = now(), trash_root = ${id} where trashed_at is null and id in (${sub})`;216 }217 for (const id of sel.fileIds) {218 await getFile(t, ownerId, id);219 await t`update files set trashed_at = now(), trash_root = ${id} where id = ${id}`;220 }221}222/** Restore trash roots. Falls back to the root folder when the parent is gone; renames on collision. */223export async function restoreItems(t: Db, ownerId: string, sel: Selection): Promise<void> {224 const root = await rootOf(t, ownerId);225 const liveParent = async (parentId: string | null) => {226 if (!parentId) return root.id;227 const [p] = await t`select 1 from folders where id = ${parentId} and owner_id = ${ownerId} and trashed_at is null`;228 return p ? parentId : root.id;229 };230 for (const id of sel.folderIds) {231 const [f] = await t<FolderRow[]>`select * from folders where id = ${id} and owner_id = ${ownerId} and trash_root = ${id}`;232 if (!f) throw notFound('item not in trash');233 const parent = await liveParent(f.parent_id);234 const taken = await takenKeys(t, parent);235 const name = nextFreeName(f.name, (k) => taken.has(k));236 await t`update files set trashed_at = null, trash_root = null where trash_root = ${id} and owner_id = ${ownerId}`;237 await t`update folders set trashed_at = null, trash_root = null where trash_root = ${id} and owner_id = ${ownerId} and id <> ${id}`;238 await t`update folders set trashed_at = null, trash_root = null, parent_id = ${parent}, name = ${name}, name_key = ${nameKey(name)}, updated_at = now() where id = ${id}`;239 }240 for (const id of sel.fileIds) {241 const [f] = await t<FileRow[]>`select * from files where id = ${id} and owner_id = ${ownerId} and trash_root = ${id}`;242 if (!f) throw notFound('item not in trash');243 const parent = await liveParent(f.folder_id);244 const taken = await takenKeys(t, parent);245 const name = nextFreeName(f.name, (k) => taken.has(k));246 await t`update files set trashed_at = null, trash_root = null, folder_id = ${parent}, name = ${name}, name_key = ${nameKey(name)}, updated_at = now() where id = ${id}`;247 }248}249/** Delete trash roots for good (or the whole trash). Content is reclaimed by the garbage collector. */250export async function purgeItems(t: Db, ownerId: string, sel: Selection | 'all'): Promise<void> {251 const root = await rootOf(t, ownerId);252 if (sel === 'all') {253 await t`delete from files where owner_id = ${ownerId} and trashed_at is not null`;254 await t`delete from folders where owner_id = ${ownerId} and trashed_at is not null`;255 } else {256 for (const id of sel.folderIds) {257 const [f] = await t`select 1 from folders where id = ${id} and owner_id = ${ownerId} and trash_root = ${id}`;258 if (!f) throw notFound('item not in trash');259 // items trashed on their own that still sit inside this subtree are separate trash entries: they survive, re-homed at the root260 const inside = t`select id from folders where owner_id = ${ownerId} and trash_root = ${id}`;261 await t`update files set folder_id = ${root.id} where owner_id = ${ownerId} and trash_root is distinct from ${id} and folder_id in (${inside})`;262 await t`update folders set parent_id = ${root.id} where owner_id = ${ownerId} and trash_root is distinct from ${id} and parent_id in (${inside})`;263 await t`delete from files where owner_id = ${ownerId} and trash_root = ${id}`;264 await t`delete from folders where owner_id = ${ownerId} and trash_root = ${id}`;265 }266 for (const id of sel.fileIds) {267 const r = await t`delete from files where id = ${id} and owner_id = ${ownerId} and trash_root = ${id} returning id`;268 if (!r.length) throw notFound('item not in trash');269 }270 }271 await enqueue(t, 'gc_objects', { ownerId }, { dedupeKey: `gc:${ownerId}`, delayS: 2 });272}273274/** True when `itemFolderId` is `ancestorId` or a live descendant of it. */275export async function isWithin(t: Db, ancestorId: string, itemFolderId: string): Promise<boolean> {276 const [r] = await t`with recursive up as (select id, parent_id, trashed_at, 0 as depth from folders where id = ${itemFolderId}277 union all select p.id, p.parent_id, p.trashed_at, up.depth + 1 from folders p join up on p.id = up.parent_id where up.id <> ${ancestorId} and up.depth < 200)278 select bool_and(trashed_at is null) and bool_or(id = ${ancestorId}) as ok from up`;279 return !!r?.ok;280}281/** Live files below a folder with their relative paths (ZIP export, share listing). Capped. */282export async function subtreeFiles(t: Db, folderId: string, limit = 20000): Promise<{ id: string; path: string; size: string }[]> {283 return t<{ id: string; path: string; size: string }[]>`284 with recursive sub as (select id, ''::text as path, 0 as depth from folders where id = ${folderId} and trashed_at is null285 union all select c.id, sub.path || c.name || '/', sub.depth + 1 from folders c join sub on c.parent_id = sub.id where c.trashed_at is null and sub.depth < 100)286 select f.id, sub.path || f.name as path, f.size::text as size from files f join sub on sub.id = f.folder_id where f.trashed_at is null order by 2 limit ${limit + 1}`;287}288