admin
540 lines
27 KiB
TypeScript
540 lines
27 KiB
TypeScript
import "server-only";
|
|
import Database from "better-sqlite3";
|
|
import { drizzle } from "drizzle-orm/better-sqlite3";
|
|
import * as schema from "./schema";
|
|
import path from "node:path";
|
|
import fs from "node:fs";
|
|
import { safeJoin } from "../safePath";
|
|
|
|
const DB_PATH = path.join(process.cwd(), "data", "library.db");
|
|
|
|
fs.mkdirSync(path.dirname(DB_PATH), { recursive: true });
|
|
fs.mkdirSync(path.join(process.cwd(), "data", "thumbs"), { recursive: true });
|
|
fs.mkdirSync(path.join(process.cwd(), "data", "portraits"), { recursive: true });
|
|
fs.mkdirSync(path.join(process.cwd(), "library"), { recursive: true });
|
|
|
|
declare global {
|
|
// eslint-disable-next-line no-var
|
|
var __sqlite: Database.Database | undefined;
|
|
}
|
|
|
|
const sqlite = global.__sqlite ?? new Database(DB_PATH);
|
|
if (!global.__sqlite) {
|
|
sqlite.pragma("journal_mode = WAL");
|
|
sqlite.pragma("foreign_keys = ON");
|
|
sqlite.pragma("synchronous = NORMAL");
|
|
global.__sqlite = sqlite;
|
|
}
|
|
|
|
bootstrap(sqlite);
|
|
|
|
function bootstrap(db: Database.Database) {
|
|
db.exec(`
|
|
CREATE TABLE IF NOT EXISTS images (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
filename TEXT NOT NULL,
|
|
rel_path TEXT NOT NULL UNIQUE,
|
|
thumb_path TEXT NOT NULL,
|
|
sha256 TEXT NOT NULL UNIQUE,
|
|
width INTEGER NOT NULL,
|
|
height INTEGER NOT NULL,
|
|
bytes INTEGER NOT NULL,
|
|
raw_metadata TEXT,
|
|
created_at INTEGER NOT NULL DEFAULT (unixepoch() * 1000),
|
|
imported_at INTEGER NOT NULL DEFAULT (unixepoch() * 1000),
|
|
deleted_at INTEGER,
|
|
parent_image_id INTEGER REFERENCES images(id) ON DELETE CASCADE,
|
|
code TEXT,
|
|
title TEXT,
|
|
release_date TEXT,
|
|
runtime_min INTEGER,
|
|
director TEXT,
|
|
studio_id INTEGER REFERENCES studios(id) ON DELETE SET NULL,
|
|
label_id INTEGER REFERENCES labels(id) ON DELETE SET NULL,
|
|
series_id INTEGER REFERENCES series(id) ON DELETE SET NULL,
|
|
rating INTEGER,
|
|
watched INTEGER NOT NULL DEFAULT 0,
|
|
is_vip INTEGER NOT NULL DEFAULT 0,
|
|
is_favorite INTEGER NOT NULL DEFAULT 0,
|
|
is_owned INTEGER NOT NULL DEFAULT 0,
|
|
notes TEXT,
|
|
phash TEXT
|
|
);
|
|
CREATE INDEX IF NOT EXISTS images_created_idx ON images(created_at);
|
|
CREATE INDEX IF NOT EXISTS images_deleted_idx ON images(deleted_at);
|
|
CREATE INDEX IF NOT EXISTS images_parent_idx ON images(parent_image_id);
|
|
CREATE INDEX IF NOT EXISTS images_code_idx ON images(code);
|
|
CREATE INDEX IF NOT EXISTS images_studio_idx ON images(studio_id);
|
|
CREATE INDEX IF NOT EXISTS images_label_idx ON images(label_id);
|
|
CREATE INDEX IF NOT EXISTS images_series_idx ON images(series_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS studios (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT NOT NULL UNIQUE,
|
|
slug TEXT NOT NULL UNIQUE,
|
|
notes TEXT
|
|
);
|
|
CREATE TABLE IF NOT EXISTS labels (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT NOT NULL UNIQUE,
|
|
slug TEXT NOT NULL UNIQUE,
|
|
notes TEXT
|
|
);
|
|
CREATE TABLE IF NOT EXISTS series (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT NOT NULL UNIQUE,
|
|
slug TEXT NOT NULL UNIQUE,
|
|
notes TEXT
|
|
);
|
|
CREATE TABLE IF NOT EXISTS actresses (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT NOT NULL UNIQUE,
|
|
slug TEXT NOT NULL UNIQUE,
|
|
alt_names TEXT,
|
|
notes TEXT,
|
|
portrait_path TEXT,
|
|
portrait_zoom REAL NOT NULL DEFAULT 1,
|
|
portrait_offset_x REAL NOT NULL DEFAULT 0,
|
|
portrait_offset_y REAL NOT NULL DEFAULT 0
|
|
);
|
|
CREATE TABLE IF NOT EXISTS genres (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT NOT NULL UNIQUE,
|
|
slug TEXT NOT NULL UNIQUE
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS image_actresses (
|
|
image_id INTEGER NOT NULL REFERENCES images(id) ON DELETE CASCADE,
|
|
actress_id INTEGER NOT NULL REFERENCES actresses(id) ON DELETE CASCADE,
|
|
PRIMARY KEY (image_id, actress_id)
|
|
);
|
|
CREATE INDEX IF NOT EXISTS image_actresses_actress_idx ON image_actresses(actress_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS image_genres (
|
|
image_id INTEGER NOT NULL REFERENCES images(id) ON DELETE CASCADE,
|
|
genre_id INTEGER NOT NULL REFERENCES genres(id) ON DELETE CASCADE,
|
|
PRIMARY KEY (image_id, genre_id)
|
|
);
|
|
CREATE INDEX IF NOT EXISTS image_genres_genre_idx ON image_genres(genre_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS tag_categories (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT NOT NULL UNIQUE,
|
|
slug TEXT NOT NULL UNIQUE,
|
|
color TEXT,
|
|
description TEXT,
|
|
cover_portrait_path TEXT,
|
|
cover_portrait_zoom REAL NOT NULL DEFAULT 1,
|
|
cover_portrait_offset_x REAL NOT NULL DEFAULT 0,
|
|
cover_portrait_offset_y REAL NOT NULL DEFAULT 0,
|
|
cover_landscape_path TEXT,
|
|
cover_landscape_zoom REAL NOT NULL DEFAULT 1,
|
|
cover_landscape_offset_x REAL NOT NULL DEFAULT 0,
|
|
cover_landscape_offset_y REAL NOT NULL DEFAULT 0
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS tags (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT NOT NULL UNIQUE,
|
|
color TEXT,
|
|
category_id INTEGER REFERENCES tag_categories(id) ON DELETE SET NULL,
|
|
last_used_at INTEGER NOT NULL DEFAULT 0
|
|
);
|
|
-- tags_category_idx is created AFTER the idempotent ALTER below, so
|
|
-- it doesn't fire on an old DB whose tags table predates category_id.
|
|
CREATE TABLE IF NOT EXISTS image_tags (
|
|
image_id INTEGER NOT NULL REFERENCES images(id) ON DELETE CASCADE,
|
|
tag_id INTEGER NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
|
|
PRIMARY KEY (image_id, tag_id)
|
|
);
|
|
CREATE INDEX IF NOT EXISTS image_tags_tag_idx ON image_tags(tag_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS collections (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT NOT NULL,
|
|
slug TEXT NOT NULL UNIQUE,
|
|
description TEXT,
|
|
cover_image_id INTEGER REFERENCES images(id) ON DELETE SET NULL,
|
|
created_at INTEGER NOT NULL DEFAULT (unixepoch() * 1000),
|
|
position INTEGER NOT NULL DEFAULT 0,
|
|
last_used_at INTEGER NOT NULL DEFAULT 0,
|
|
cover_portrait_path TEXT,
|
|
cover_portrait_zoom REAL NOT NULL DEFAULT 1,
|
|
cover_portrait_offset_x REAL NOT NULL DEFAULT 0,
|
|
cover_portrait_offset_y REAL NOT NULL DEFAULT 0,
|
|
cover_landscape_path TEXT,
|
|
cover_landscape_zoom REAL NOT NULL DEFAULT 1,
|
|
cover_landscape_offset_x REAL NOT NULL DEFAULT 0,
|
|
cover_landscape_offset_y REAL NOT NULL DEFAULT 0
|
|
);
|
|
CREATE TABLE IF NOT EXISTS collection_images (
|
|
collection_id INTEGER NOT NULL REFERENCES collections(id) ON DELETE CASCADE,
|
|
image_id INTEGER NOT NULL REFERENCES images(id) ON DELETE CASCADE,
|
|
position INTEGER NOT NULL DEFAULT 0,
|
|
PRIMARY KEY (collection_id, image_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS actress_categories (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT NOT NULL UNIQUE,
|
|
slug TEXT NOT NULL UNIQUE,
|
|
color TEXT,
|
|
icon TEXT,
|
|
priority INTEGER NOT NULL DEFAULT 0,
|
|
builtin INTEGER NOT NULL DEFAULT 0
|
|
);
|
|
CREATE TABLE IF NOT EXISTS actress_categories_map (
|
|
actress_id INTEGER NOT NULL REFERENCES actresses(id) ON DELETE CASCADE,
|
|
category_id INTEGER NOT NULL REFERENCES actress_categories(id) ON DELETE CASCADE,
|
|
PRIMARY KEY (actress_id, category_id)
|
|
);
|
|
CREATE INDEX IF NOT EXISTS actress_cat_map_cat_idx ON actress_categories_map(category_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS app_settings (
|
|
key TEXT PRIMARY KEY,
|
|
value TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS video_metadata (
|
|
abs_path TEXT PRIMARY KEY,
|
|
rel_path TEXT NOT NULL,
|
|
code TEXT NOT NULL,
|
|
size_bytes INTEGER NOT NULL,
|
|
mtime_ms REAL NOT NULL,
|
|
probed_at INTEGER,
|
|
probe_error TEXT,
|
|
duration_sec REAL,
|
|
video_codec TEXT,
|
|
video_b_frames INTEGER,
|
|
width INTEGER,
|
|
height INTEGER,
|
|
video_bitrate INTEGER,
|
|
playback_mode TEXT
|
|
);
|
|
CREATE INDEX IF NOT EXISTS video_metadata_code_idx ON video_metadata(code);
|
|
|
|
CREATE TABLE IF NOT EXISTS whisperjav_jobs (
|
|
id TEXT PRIMARY KEY,
|
|
code TEXT NOT NULL,
|
|
video_abs TEXT NOT NULL,
|
|
job_dir TEXT NOT NULL,
|
|
target_subtitle_path TEXT,
|
|
status TEXT NOT NULL CHECK(status IN ('queued','running','completed','warning','failed','cancelled')),
|
|
enqueued_at INTEGER NOT NULL,
|
|
started_at INTEGER,
|
|
ended_at INTEGER,
|
|
exit_code INTEGER,
|
|
error TEXT,
|
|
stage TEXT,
|
|
stage_index INTEGER,
|
|
stage_total INTEGER,
|
|
cue_count INTEGER,
|
|
cli_args TEXT NOT NULL,
|
|
log_path TEXT NOT NULL,
|
|
stats_path TEXT,
|
|
video_duration_sec REAL,
|
|
mode TEXT
|
|
);
|
|
CREATE INDEX IF NOT EXISTS whisperjav_jobs_code_idx ON whisperjav_jobs(code);
|
|
CREATE INDEX IF NOT EXISTS whisperjav_jobs_status_idx ON whisperjav_jobs(status);
|
|
CREATE INDEX IF NOT EXISTS whisperjav_jobs_enqueued_idx ON whisperjav_jobs(enqueued_at);
|
|
|
|
-- User-attached subtitle files that live outside the indexed roots
|
|
-- and subtitleExtraPaths. Browse... in the player records the pick
|
|
-- here so the entry survives modal close / server restart.
|
|
CREATE TABLE IF NOT EXISTS manual_subtitles (
|
|
code TEXT NOT NULL,
|
|
part_idx INTEGER NOT NULL,
|
|
abs_path TEXT NOT NULL,
|
|
attached_at INTEGER NOT NULL,
|
|
PRIMARY KEY (code, part_idx, abs_path)
|
|
);
|
|
CREATE INDEX IF NOT EXISTS manual_subtitles_code_idx ON manual_subtitles(code);
|
|
CREATE INDEX IF NOT EXISTS manual_subtitles_abs_idx ON manual_subtitles(abs_path);
|
|
|
|
CREATE VIRTUAL TABLE IF NOT EXISTS covers_fts USING fts5(
|
|
code, title, director, notes,
|
|
content='images', content_rowid='id',
|
|
tokenize='porter unicode61'
|
|
);
|
|
|
|
CREATE TRIGGER IF NOT EXISTS covers_ai AFTER INSERT ON images BEGIN
|
|
INSERT INTO covers_fts(rowid, code, title, director, notes)
|
|
VALUES (new.id, COALESCE(new.code, ''), COALESCE(new.title, ''), COALESCE(new.director, ''), COALESCE(new.notes, ''));
|
|
END;
|
|
CREATE TRIGGER IF NOT EXISTS covers_ad AFTER DELETE ON images BEGIN
|
|
INSERT INTO covers_fts(covers_fts, rowid, code, title, director, notes)
|
|
VALUES ('delete', old.id, COALESCE(old.code, ''), COALESCE(old.title, ''), COALESCE(old.director, ''), COALESCE(old.notes, ''));
|
|
END;
|
|
CREATE TRIGGER IF NOT EXISTS covers_au AFTER UPDATE ON images BEGIN
|
|
INSERT INTO covers_fts(covers_fts, rowid, code, title, director, notes)
|
|
VALUES ('delete', old.id, COALESCE(old.code, ''), COALESCE(old.title, ''), COALESCE(old.director, ''), COALESCE(old.notes, ''));
|
|
INSERT INTO covers_fts(rowid, code, title, director, notes)
|
|
VALUES (new.id, COALESCE(new.code, ''), COALESCE(new.title, ''), COALESCE(new.director, ''), COALESCE(new.notes, ''));
|
|
END;
|
|
`);
|
|
|
|
// Existing databases may have images that predate the FTS table/triggers.
|
|
// Rebuild once per process bootstrap so metadata search is complete.
|
|
try {
|
|
db.prepare(`INSERT INTO covers_fts(covers_fts) VALUES ('rebuild')`).run();
|
|
} catch {}
|
|
|
|
// Seed built-in actress categories (idempotent — only inserts if absent).
|
|
const seedCat = db.prepare(`
|
|
INSERT OR IGNORE INTO actress_categories (name, slug, color, icon, priority, builtin)
|
|
VALUES (?, ?, ?, ?, ?, 1)
|
|
`);
|
|
seedCat.run("Favorite", "favorite", "#fbbf24", "star", 100);
|
|
seedCat.run("VIP", "vip", "#22d3ee", "gem", 90);
|
|
|
|
// Idempotent ALTERs for columns added after initial release.
|
|
const actressCols = db.prepare(`PRAGMA table_info(actresses)`).all() as Array<{ name: string }>;
|
|
const hasCol = (n: string) => actressCols.some((c) => c.name === n);
|
|
if (!hasCol("portrait_path")) db.exec(`ALTER TABLE actresses ADD COLUMN portrait_path TEXT`);
|
|
if (!hasCol("portrait_zoom")) db.exec(`ALTER TABLE actresses ADD COLUMN portrait_zoom REAL NOT NULL DEFAULT 1`);
|
|
if (!hasCol("portrait_offset_x")) db.exec(`ALTER TABLE actresses ADD COLUMN portrait_offset_x REAL NOT NULL DEFAULT 0`);
|
|
if (!hasCol("portrait_offset_y")) db.exec(`ALTER TABLE actresses ADD COLUMN portrait_offset_y REAL NOT NULL DEFAULT 0`);
|
|
for (const slot of ["2", "3", "4", "h"]) {
|
|
if (!hasCol(`portrait${slot}_path`)) db.exec(`ALTER TABLE actresses ADD COLUMN portrait${slot}_path TEXT`);
|
|
if (!hasCol(`portrait${slot}_zoom`)) db.exec(`ALTER TABLE actresses ADD COLUMN portrait${slot}_zoom REAL NOT NULL DEFAULT 1`);
|
|
if (!hasCol(`portrait${slot}_offset_x`)) db.exec(`ALTER TABLE actresses ADD COLUMN portrait${slot}_offset_x REAL NOT NULL DEFAULT 0`);
|
|
if (!hasCol(`portrait${slot}_offset_y`)) db.exec(`ALTER TABLE actresses ADD COLUMN portrait${slot}_offset_y REAL NOT NULL DEFAULT 0`);
|
|
}
|
|
if (!hasCol("born_on")) db.exec(`ALTER TABLE actresses ADD COLUMN born_on TEXT`);
|
|
if (!hasCol("height_cm")) db.exec(`ALTER TABLE actresses ADD COLUMN height_cm INTEGER`);
|
|
if (!hasCol("weight_kg")) db.exec(`ALTER TABLE actresses ADD COLUMN weight_kg INTEGER`);
|
|
if (!hasCol("cup_size")) db.exec(`ALTER TABLE actresses ADD COLUMN cup_size TEXT`);
|
|
|
|
// VIP / Favorite toggles on covers (separate from the actress categories of the same name).
|
|
const imageCols = db.prepare(`PRAGMA table_info(images)`).all() as Array<{ name: string }>;
|
|
const hasImgCol = (n: string) => imageCols.some((c) => c.name === n);
|
|
if (!hasImgCol("is_vip")) db.exec(`ALTER TABLE images ADD COLUMN is_vip INTEGER NOT NULL DEFAULT 0`);
|
|
if (!hasImgCol("is_favorite")) db.exec(`ALTER TABLE images ADD COLUMN is_favorite INTEGER NOT NULL DEFAULT 0`);
|
|
if (!hasImgCol("is_owned")) db.exec(`ALTER TABLE images ADD COLUMN is_owned INTEGER NOT NULL DEFAULT 0`);
|
|
// Set by the video scanner whenever the on-disk index is rebuilt — see lib/video/index.ts.
|
|
if (!hasImgCol("has_video")) {
|
|
db.exec(`ALTER TABLE images ADD COLUMN has_video INTEGER NOT NULL DEFAULT 0`);
|
|
db.exec(`CREATE INDEX IF NOT EXISTS images_has_video_idx ON images(has_video)`);
|
|
}
|
|
// Mirrors the on-disk subtitle index — true when at least one sidecar
|
|
// (.srt/.vtt/.ass/.ssa) exists for the code in any of: the video's
|
|
// folder, configured subtitleExtraPaths, or data/generated-subtitles/.
|
|
// Embedded streams are NOT counted (cheap-only signal).
|
|
if (!hasImgCol("has_subtitle")) {
|
|
db.exec(`ALTER TABLE images ADD COLUMN has_subtitle INTEGER NOT NULL DEFAULT 0`);
|
|
db.exec(`CREATE INDEX IF NOT EXISTS images_has_subtitle_idx ON images(has_subtitle)`);
|
|
}
|
|
// Lazy-probed video metadata used by playback auto-modes. Populated on
|
|
// first play of a file; never written by the scanner.
|
|
if (!hasImgCol("video_codec")) db.exec(`ALTER TABLE images ADD COLUMN video_codec TEXT`);
|
|
if (!hasImgCol("video_b_frames")) db.exec(`ALTER TABLE images ADD COLUMN video_b_frames INTEGER`);
|
|
if (!hasImgCol("playback_mode")) db.exec(`ALTER TABLE images ADD COLUMN playback_mode TEXT`);
|
|
|
|
const videoMetaCols = db.prepare(`PRAGMA table_info(video_metadata)`).all() as Array<{ name: string }>;
|
|
const hasVideoMetaCol = (n: string) => videoMetaCols.some((c) => c.name === n);
|
|
if (!hasVideoMetaCol("rel_path")) db.exec(`ALTER TABLE video_metadata ADD COLUMN rel_path TEXT NOT NULL DEFAULT ''`);
|
|
if (!hasVideoMetaCol("code")) db.exec(`ALTER TABLE video_metadata ADD COLUMN code TEXT NOT NULL DEFAULT ''`);
|
|
if (!hasVideoMetaCol("size_bytes")) db.exec(`ALTER TABLE video_metadata ADD COLUMN size_bytes INTEGER NOT NULL DEFAULT 0`);
|
|
if (!hasVideoMetaCol("mtime_ms")) db.exec(`ALTER TABLE video_metadata ADD COLUMN mtime_ms REAL NOT NULL DEFAULT 0`);
|
|
if (!hasVideoMetaCol("probed_at")) db.exec(`ALTER TABLE video_metadata ADD COLUMN probed_at INTEGER`);
|
|
if (!hasVideoMetaCol("probe_error")) db.exec(`ALTER TABLE video_metadata ADD COLUMN probe_error TEXT`);
|
|
if (!hasVideoMetaCol("duration_sec")) db.exec(`ALTER TABLE video_metadata ADD COLUMN duration_sec REAL`);
|
|
if (!hasVideoMetaCol("video_codec")) db.exec(`ALTER TABLE video_metadata ADD COLUMN video_codec TEXT`);
|
|
if (!hasVideoMetaCol("video_b_frames")) db.exec(`ALTER TABLE video_metadata ADD COLUMN video_b_frames INTEGER`);
|
|
if (!hasVideoMetaCol("width")) db.exec(`ALTER TABLE video_metadata ADD COLUMN width INTEGER`);
|
|
if (!hasVideoMetaCol("height")) db.exec(`ALTER TABLE video_metadata ADD COLUMN height INTEGER`);
|
|
if (!hasVideoMetaCol("video_bitrate")) db.exec(`ALTER TABLE video_metadata ADD COLUMN video_bitrate INTEGER`);
|
|
if (!hasVideoMetaCol("playback_mode")) db.exec(`ALTER TABLE video_metadata ADD COLUMN playback_mode TEXT`);
|
|
if (!hasVideoMetaCol("part_kind")) db.exec(`ALTER TABLE video_metadata ADD COLUMN part_kind TEXT`);
|
|
if (!hasVideoMetaCol("part_index")) db.exec(`ALTER TABLE video_metadata ADD COLUMN part_index INTEGER`);
|
|
if (!hasVideoMetaCol("variant_group")) db.exec(`ALTER TABLE video_metadata ADD COLUMN variant_group TEXT`);
|
|
// dir_path enables incremental rescan: reuse cached rows for any
|
|
// directory whose mtime hasn't changed since last scan. Backfilled
|
|
// below for any pre-existing rows.
|
|
if (!hasVideoMetaCol("dir_path")) db.exec(`ALTER TABLE video_metadata ADD COLUMN dir_path TEXT NOT NULL DEFAULT ''`);
|
|
db.exec(`CREATE INDEX IF NOT EXISTS video_metadata_code_idx ON video_metadata(code)`);
|
|
db.exec(`CREATE INDEX IF NOT EXISTS video_metadata_dir_idx ON video_metadata(dir_path)`);
|
|
|
|
// Per-directory mtime cache. On rescan, dirs whose stat mtime
|
|
// matches the stored value are treated as unchanged and their cached
|
|
// file rows are reused without readdir/stat per file.
|
|
db.exec(`
|
|
CREATE TABLE IF NOT EXISTS video_dir_mtimes (
|
|
abs_dir TEXT PRIMARY KEY,
|
|
mtime_ms REAL NOT NULL,
|
|
last_seen_at INTEGER NOT NULL
|
|
);
|
|
`);
|
|
// Backfill dir_path for existing rows that predate this column.
|
|
// Cheap: one UPDATE per missing row, derived in SQL via rtrim/instr.
|
|
// Do nothing if there's nothing to fill (default '' marker).
|
|
const missing = db.prepare(`SELECT COUNT(*) AS n FROM video_metadata WHERE dir_path = ''`).get() as { n: number };
|
|
if (missing.n > 0) {
|
|
type Row = { abs_path: string };
|
|
const rows = db.prepare(`SELECT abs_path FROM video_metadata WHERE dir_path = ''`).all() as Row[];
|
|
const upd = db.prepare(`UPDATE video_metadata SET dir_path = ? WHERE abs_path = ?`);
|
|
const tx = db.transaction(() => {
|
|
for (const r of rows) {
|
|
const last = Math.max(r.abs_path.lastIndexOf("/"), r.abs_path.lastIndexOf("\\"));
|
|
const dir = last >= 0 ? r.abs_path.slice(0, last) : "";
|
|
upd.run(dir, r.abs_path);
|
|
}
|
|
});
|
|
tx();
|
|
}
|
|
|
|
// whisperjav_jobs: ETA fields added in v1.1 — idempotent migration.
|
|
const wjCols = db.prepare(`PRAGMA table_info(whisperjav_jobs)`).all() as Array<{ name: string }>;
|
|
const hasWjCol = (n: string) => wjCols.some((c) => c.name === n);
|
|
if (!hasWjCol("video_duration_sec")) db.exec(`ALTER TABLE whisperjav_jobs ADD COLUMN video_duration_sec REAL`);
|
|
if (!hasWjCol("mode")) db.exec(`ALTER TABLE whisperjav_jobs ADD COLUMN mode TEXT`);
|
|
db.exec(`CREATE INDEX IF NOT EXISTS whisperjav_jobs_mode_idx ON whisperjav_jobs(mode)`);
|
|
// Perceptual dHash (16-char hex, 64 bits) for near-duplicate detection.
|
|
// Backfilled lazily by the maintenance scanner.
|
|
if (!hasImgCol("phash")) db.exec(`ALTER TABLE images ADD COLUMN phash TEXT`);
|
|
|
|
// Recency tracking on tags + collections so the context menu can
|
|
// surface "recent" chips. Updated whenever the entity is attached to
|
|
// an image. Default backfill = current time so existing rows show up.
|
|
const tagCols2 = db.prepare(`PRAGMA table_info(tags)`).all() as Array<{ name: string }>;
|
|
if (!tagCols2.some((c) => c.name === "last_used_at")) {
|
|
db.exec(`ALTER TABLE tags ADD COLUMN last_used_at INTEGER NOT NULL DEFAULT 0`);
|
|
db.exec(`UPDATE tags SET last_used_at = (unixepoch() * 1000)`);
|
|
}
|
|
const collectionCols = db.prepare(`PRAGMA table_info(collections)`).all() as Array<{ name: string }>;
|
|
if (!collectionCols.some((c) => c.name === "last_used_at")) {
|
|
db.exec(`ALTER TABLE collections ADD COLUMN last_used_at INTEGER NOT NULL DEFAULT 0`);
|
|
db.exec(`UPDATE collections SET last_used_at = COALESCE(created_at, unixepoch() * 1000)`);
|
|
}
|
|
|
|
// Manual reorder support for the collections index page. Backfill
|
|
// positions from created_at so existing libraries get a sensible
|
|
// initial order on first launch with the new schema.
|
|
const colCols = db.prepare(`PRAGMA table_info(collections)`).all() as Array<{ name: string }>;
|
|
if (!colCols.some((c) => c.name === "position")) {
|
|
db.exec(`ALTER TABLE collections ADD COLUMN position INTEGER NOT NULL DEFAULT 0`);
|
|
const rows = db.prepare(`SELECT id FROM collections ORDER BY created_at ASC, id ASC`).all() as Array<{ id: number }>;
|
|
const update = db.prepare(`UPDATE collections SET position = ? WHERE id = ?`);
|
|
for (let i = 0; i < rows.length; i++) update.run(i, rows[i].id);
|
|
}
|
|
|
|
// Tag categories: umbrellas grouping related tags (e.g. "BDSM" containing
|
|
// bondage / shibari / cuffs). Each tag belongs to at most one category.
|
|
const tagCols = db.prepare(`PRAGMA table_info(tags)`).all() as Array<{ name: string }>;
|
|
if (!tagCols.some((c) => c.name === "category_id")) {
|
|
db.exec(`ALTER TABLE tags ADD COLUMN category_id INTEGER REFERENCES tag_categories(id) ON DELETE SET NULL`);
|
|
}
|
|
// Create the index unconditionally — both the fresh-DB CREATE TABLE path
|
|
// and the migrated path need it, and IF NOT EXISTS makes it idempotent.
|
|
db.exec(`CREATE INDEX IF NOT EXISTS tags_category_idx ON tags(category_id)`);
|
|
|
|
// Tag-category cover art: separate portrait + landscape slots with
|
|
// pan/zoom transforms, mirroring the actress-portrait shape. Files
|
|
// live in data/category-covers/.
|
|
const catCols = db.prepare(`PRAGMA table_info(tag_categories)`).all() as Array<{ name: string }>;
|
|
const hasCatCol = (n: string) => catCols.some((c) => c.name === n);
|
|
if (!hasCatCol("cover_portrait_path")) db.exec(`ALTER TABLE tag_categories ADD COLUMN cover_portrait_path TEXT`);
|
|
if (!hasCatCol("cover_portrait_zoom")) db.exec(`ALTER TABLE tag_categories ADD COLUMN cover_portrait_zoom REAL NOT NULL DEFAULT 1`);
|
|
if (!hasCatCol("cover_portrait_offset_x")) db.exec(`ALTER TABLE tag_categories ADD COLUMN cover_portrait_offset_x REAL NOT NULL DEFAULT 0`);
|
|
if (!hasCatCol("cover_portrait_offset_y")) db.exec(`ALTER TABLE tag_categories ADD COLUMN cover_portrait_offset_y REAL NOT NULL DEFAULT 0`);
|
|
if (!hasCatCol("cover_landscape_path")) db.exec(`ALTER TABLE tag_categories ADD COLUMN cover_landscape_path TEXT`);
|
|
if (!hasCatCol("cover_landscape_zoom")) db.exec(`ALTER TABLE tag_categories ADD COLUMN cover_landscape_zoom REAL NOT NULL DEFAULT 1`);
|
|
if (!hasCatCol("cover_landscape_offset_x")) db.exec(`ALTER TABLE tag_categories ADD COLUMN cover_landscape_offset_x REAL NOT NULL DEFAULT 0`);
|
|
if (!hasCatCol("cover_landscape_offset_y")) db.exec(`ALTER TABLE tag_categories ADD COLUMN cover_landscape_offset_y REAL NOT NULL DEFAULT 0`);
|
|
fs.mkdirSync(path.join(process.cwd(), "data", "category-covers"), { recursive: true });
|
|
|
|
// Collection cover art: same shape as tag_categories — separate
|
|
// portrait + landscape slots with pan/zoom transforms. Files live in
|
|
// data/collection-covers/.
|
|
const collCols = db.prepare(`PRAGMA table_info(collections)`).all() as Array<{ name: string }>;
|
|
const hasCollCol = (n: string) => collCols.some((c) => c.name === n);
|
|
if (!hasCollCol("cover_portrait_path")) db.exec(`ALTER TABLE collections ADD COLUMN cover_portrait_path TEXT`);
|
|
if (!hasCollCol("cover_portrait_zoom")) db.exec(`ALTER TABLE collections ADD COLUMN cover_portrait_zoom REAL NOT NULL DEFAULT 1`);
|
|
if (!hasCollCol("cover_portrait_offset_x")) db.exec(`ALTER TABLE collections ADD COLUMN cover_portrait_offset_x REAL NOT NULL DEFAULT 0`);
|
|
if (!hasCollCol("cover_portrait_offset_y")) db.exec(`ALTER TABLE collections ADD COLUMN cover_portrait_offset_y REAL NOT NULL DEFAULT 0`);
|
|
if (!hasCollCol("cover_landscape_path")) db.exec(`ALTER TABLE collections ADD COLUMN cover_landscape_path TEXT`);
|
|
if (!hasCollCol("cover_landscape_zoom")) db.exec(`ALTER TABLE collections ADD COLUMN cover_landscape_zoom REAL NOT NULL DEFAULT 1`);
|
|
if (!hasCollCol("cover_landscape_offset_x")) db.exec(`ALTER TABLE collections ADD COLUMN cover_landscape_offset_x REAL NOT NULL DEFAULT 0`);
|
|
if (!hasCollCol("cover_landscape_offset_y")) db.exec(`ALTER TABLE collections ADD COLUMN cover_landscape_offset_y REAL NOT NULL DEFAULT 0`);
|
|
fs.mkdirSync(path.join(process.cwd(), "data", "collection-covers"), { recursive: true });
|
|
|
|
// Auto-purge expired trash. Reads retention from app_settings; bails if 0
|
|
// (forever) or no rows are old enough yet.
|
|
const retentionRow = db.prepare(`SELECT value FROM app_settings WHERE key = 'trashRetentionDays'`).get() as { value: string } | undefined;
|
|
const retentionDays = retentionRow ? Number(retentionRow.value) : 30;
|
|
if (Number.isFinite(retentionDays) && retentionDays > 0) {
|
|
const cutoff = Date.now() - retentionDays * 86400_000;
|
|
const stale = db.prepare(`
|
|
WITH targets AS (
|
|
SELECT id FROM images WHERE deleted_at IS NOT NULL AND deleted_at < ?
|
|
)
|
|
SELECT id, rel_path, thumb_path FROM images
|
|
WHERE id IN (SELECT id FROM targets)
|
|
OR parent_image_id IN (SELECT id FROM targets)
|
|
`).all(cutoff) as Array<{ id: number; rel_path: string; thumb_path: string }>;
|
|
if (stale.length > 0) {
|
|
const purgeRow = db.prepare(`SELECT value FROM app_settings WHERE key = 'purgeFilesOnDelete'`).get() as { value: string } | undefined;
|
|
const purgeFiles = purgeRow ? purgeRow.value === "1" : true;
|
|
if (purgeFiles) {
|
|
const libRoot = path.join(process.cwd(), "library");
|
|
const thumbRoot = path.join(process.cwd(), "data", "thumbs");
|
|
for (const r of stale) {
|
|
const fileAbs = safeJoin(libRoot, r.rel_path);
|
|
const thumbAbs = safeJoin(thumbRoot, r.thumb_path);
|
|
try { if (fileAbs) fs.rmSync(fileAbs, { force: true }); } catch {}
|
|
try { if (thumbAbs) fs.rmSync(thumbAbs, { force: true }); } catch {}
|
|
}
|
|
}
|
|
const placeholders = stale.map(() => "?").join(",");
|
|
db.prepare(`DELETE FROM images WHERE id IN (${placeholders})`).run(...stale.map((r) => r.id));
|
|
}
|
|
}
|
|
|
|
// Auto-purge old .superseded/ files. These are recovery snapshots
|
|
// written by the collision-replace path; once N days have passed
|
|
// without rolling back, they're safe to drop. 0 = keep forever.
|
|
const supersededRow = db.prepare(`SELECT value FROM app_settings WHERE key = 'supersededRetentionDays'`).get() as { value: string } | undefined;
|
|
const supersededDays = supersededRow ? Number(supersededRow.value) : 30;
|
|
if (Number.isFinite(supersededDays) && supersededDays > 0) {
|
|
const supersededRoot = path.join(process.cwd(), "library", ".superseded");
|
|
if (fs.existsSync(supersededRoot)) {
|
|
const cutoff = Date.now() - supersededDays * 86400_000;
|
|
try {
|
|
const entries = fs.readdirSync(supersededRoot, { withFileTypes: true });
|
|
for (const e of entries) {
|
|
if (!e.isFile()) continue;
|
|
const abs = path.join(supersededRoot, e.name);
|
|
try {
|
|
const stat = fs.statSync(abs);
|
|
if (stat.mtimeMs < cutoff) {
|
|
fs.rmSync(abs, { force: true });
|
|
}
|
|
} catch {}
|
|
}
|
|
} catch {}
|
|
}
|
|
}
|
|
}
|
|
|
|
function slugify(s: string): string {
|
|
return s.toLowerCase().normalize("NFKD").replace(/[̀-ͯ]/g, "")
|
|
.replace(/[^a-z0-9]+/g, "-").replace(/^-+|-+$/g, "") || "untitled";
|
|
}
|
|
|
|
/** Pick a unique slug for a row in a slug-bearing table (collections, studios, labels, series, actresses, genres). */
|
|
export function uniqueSlug(database: Database.Database, table: string, name: string, excludeId?: number): string {
|
|
const base = slugify(name);
|
|
let slug = base;
|
|
let i = 1;
|
|
const stmt = database.prepare(`SELECT 1 FROM ${table} WHERE slug = ? AND id != ? LIMIT 1`);
|
|
while (stmt.get(slug, excludeId ?? -1)) {
|
|
i++;
|
|
slug = `${base}-${i}`;
|
|
}
|
|
return slug;
|
|
}
|
|
|
|
export const db = drizzle(sqlite, { schema });
|
|
export const rawDb = sqlite;
|