SPB Git

Repository Public

db.ts 15.1 KB 275 lines typescript

1import { DatabaseSync, type SQLInputValue } from "node:sqlite";2import fs from "node:fs";3import path from "node:path";4import { env } from "./env";56// One SQLite file shared by the web process and the worker (WAL mode: readers never block the writer).7const MIGRATIONS: string[] = [8  `9  CREATE TABLE settings (key TEXT PRIMARY KEY, value TEXT NOT NULL);10  CREATE TABLE sources (11    id INTEGER PRIMARY KEY, kind TEXT NOT NULL, name TEXT NOT NULL, url TEXT NOT NULL UNIQUE,12    country TEXT, enabled INTEGER NOT NULL DEFAULT 1, builtin INTEGER NOT NULL DEFAULT 0,13    last_sync_at INTEGER, last_status TEXT, last_error TEXT, stream_count INTEGER NOT NULL DEFAULT 0, created_at INTEGER NOT NULL14  );15  CREATE TABLE channels (16    id TEXT PRIMARY KEY, name TEXT NOT NULL, alt_names TEXT NOT NULL DEFAULT '', country TEXT,17    categories TEXT NOT NULL DEFAULT '', languages TEXT NOT NULL DEFAULT '', logo TEXT, website TEXT, network TEXT,18    known INTEGER NOT NULL DEFAULT 0, active INTEGER NOT NULL DEFAULT 1, has_guide INTEGER NOT NULL DEFAULT 0,19    first_seen INTEGER NOT NULL, updated_at INTEGER NOT NULL20  );21  CREATE INDEX channels_country ON channels(country) WHERE active = 1;22  CREATE TABLE streams (23    id INTEGER PRIMARY KEY, channel_id TEXT NOT NULL REFERENCES channels(id) ON DELETE CASCADE,24    source_id INTEGER NOT NULL REFERENCES sources(id) ON DELETE CASCADE,25    url TEXT NOT NULL, title TEXT, feed TEXT, quality TEXT, labels TEXT NOT NULL DEFAULT '',26    referrer TEXT, user_agent TEXT, first_seen INTEGER NOT NULL, last_seen INTEGER NOT NULL,27    status TEXT NOT NULL DEFAULT 'unknown', http_status INTEGER, check_error TEXT, checked_at INTEGER,28    cors INTEGER, content_type TEXT, latency_ms INTEGER, fail_count INTEGER NOT NULL DEFAULT 0,29    play_ok INTEGER, play_at INTEGER, play_error TEXT,30    UNIQUE(url)31  );32  CREATE INDEX streams_channel ON streams(channel_id);33  CREATE INDEX streams_checked ON streams(checked_at);34  CREATE TABLE categories (id TEXT PRIMARY KEY, name TEXT NOT NULL);35  CREATE TABLE countries (code TEXT PRIMARY KEY, name TEXT NOT NULL, flag TEXT);36  CREATE TABLE languages (code TEXT PRIMARY KEY, name TEXT NOT NULL);37  CREATE TABLE guides (38    channel_id TEXT NOT NULL, feed TEXT, site TEXT NOT NULL, site_id TEXT NOT NULL, site_name TEXT, lang TEXT39  );40  CREATE INDEX guides_channel ON guides(channel_id);41  CREATE TABLE epg_state (42    channel_id TEXT PRIMARY KEY, candidate INTEGER NOT NULL DEFAULT 0, site TEXT, last_try_at INTEGER, last_ok_at INTEGER, last_count INTEGER NOT NULL DEFAULT 043  );44  CREATE TABLE epg_sources (45    id INTEGER PRIMARY KEY, name TEXT NOT NULL, url TEXT NOT NULL UNIQUE, enabled INTEGER NOT NULL DEFAULT 1,46    last_sync_at INTEGER, last_status TEXT, last_error TEXT, program_count INTEGER NOT NULL DEFAULT 0, created_at INTEGER NOT NULL47  );48  CREATE TABLE programs (49    channel_id TEXT NOT NULL, start INTEGER NOT NULL, stop INTEGER NOT NULL, title TEXT NOT NULL,50    subtitle TEXT, description TEXT, category TEXT, origin TEXT NOT NULL,51    PRIMARY KEY (channel_id, start)52  ) WITHOUT ROWID;53  CREATE INDEX programs_stop ON programs(stop);5455  CREATE TABLE lists (id INTEGER PRIMARY KEY, name TEXT NOT NULL, position INTEGER NOT NULL, created_at INTEGER NOT NULL);56  CREATE TABLE list_items (57    list_id INTEGER NOT NULL REFERENCES lists(id) ON DELETE CASCADE, channel_id TEXT NOT NULL,58    position INTEGER NOT NULL, added_at INTEGER NOT NULL, PRIMARY KEY (list_id, channel_id)59  ) WITHOUT ROWID;60  CREATE INDEX list_items_channel ON list_items(channel_id);61  CREATE TABLE hidden (channel_id TEXT PRIMARY KEY, hidden_at INTEGER NOT NULL);62  CREATE TABLE history (channel_id TEXT PRIMARY KEY, last_watched_at INTEGER NOT NULL, watch_count INTEGER NOT NULL DEFAULT 1);6364  CREATE TABLE passkeys (65    id TEXT PRIMARY KEY, public_key BLOB NOT NULL, counter INTEGER NOT NULL DEFAULT 0, transports TEXT,66    name TEXT NOT NULL, created_at INTEGER NOT NULL, last_used_at INTEGER67  );68  CREATE TABLE sessions (69    token_hash TEXT PRIMARY KEY, created_at INTEGER NOT NULL, expires_at INTEGER NOT NULL, last_seen_at INTEGER NOT NULL,70    user_agent TEXT, ip TEXT, method TEXT NOT NULL71  );72  CREATE TABLE login_attempts (id INTEGER PRIMARY KEY, ip TEXT NOT NULL, at INTEGER NOT NULL, ok INTEGER NOT NULL, method TEXT NOT NULL);73  CREATE INDEX login_attempts_at ON login_attempts(at);7475  CREATE TABLE jobs (76    id INTEGER PRIMARY KEY, type TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'queued', payload TEXT,77    requested_at INTEGER NOT NULL, started_at INTEGER, finished_at INTEGER, result TEXT, error TEXT, trigger TEXT NOT NULL DEFAULT 'manual'78  );79  CREATE INDEX jobs_status ON jobs(status, id);80  CREATE TABLE logs (id INTEGER PRIMARY KEY, at INTEGER NOT NULL, level TEXT NOT NULL, scope TEXT NOT NULL, message TEXT NOT NULL, meta TEXT);81  CREATE INDEX logs_at ON logs(at);82  `,83  // 2 — films, series, personal library, playback progress.84  `85  CREATE TABLE titles (86    id INTEGER PRIMARY KEY, kind TEXT NOT NULL, origin TEXT NOT NULL, origin_id TEXT NOT NULL,87    title TEXT NOT NULL, original_title TEXT, year INTEGER, synopsis TEXT, synopsis_source TEXT, runtime_sec INTEGER,88    genres TEXT NOT NULL DEFAULT '', directors TEXT NOT NULL DEFAULT '', cast_names TEXT NOT NULL DEFAULT '',89    languages TEXT NOT NULL DEFAULT '', countries TEXT NOT NULL DEFAULT '', poster TEXT, backdrop TEXT,90    is_documentary INTEGER NOT NULL DEFAULT 0, is_animation INTEGER NOT NULL DEFAULT 0, is_short INTEGER NOT NULL DEFAULT 0,91    rights_status TEXT NOT NULL, rights_basis TEXT NOT NULL, rights_url TEXT, source_name TEXT NOT NULL, source_url TEXT,92    imdb_id TEXT, wikidata_id TEXT, library_dir INTEGER, active INTEGER NOT NULL DEFAULT 1,93    added_at INTEGER NOT NULL, updated_at INTEGER NOT NULL, checked_at INTEGER,94    UNIQUE(origin, origin_id)95  );96  CREATE INDEX titles_kind ON titles(kind) WHERE active = 1;97  CREATE TABLE episodes (98    id INTEGER PRIMARY KEY, title_id INTEGER NOT NULL REFERENCES titles(id) ON DELETE CASCADE,99    season INTEGER NOT NULL, episode INTEGER NOT NULL, name TEXT, synopsis TEXT, air_date TEXT,100    UNIQUE(title_id, season, episode)101  );102  CREATE TABLE media (103    id INTEGER PRIMARY KEY, title_id INTEGER NOT NULL REFERENCES titles(id) ON DELETE CASCADE,104    episode_id INTEGER REFERENCES episodes(id) ON DELETE CASCADE,105    kind TEXT NOT NULL, url TEXT, path TEXT, container TEXT, vcodec TEXT, acodec TEXT, width INTEGER, height INTEGER,106    duration_sec INTEGER, size_bytes INTEGER, mtime INTEGER, playback TEXT NOT NULL DEFAULT 'direct',107    subtitles TEXT NOT NULL DEFAULT '[]', audio TEXT NOT NULL DEFAULT '[]', error TEXT, added_at INTEGER NOT NULL108  );109  CREATE INDEX media_title ON media(title_id);110  CREATE UNIQUE INDEX media_path ON media(path) WHERE path IS NOT NULL;111  CREATE TABLE progress (112    media_id INTEGER PRIMARY KEY REFERENCES media(id) ON DELETE CASCADE, position_sec REAL NOT NULL, duration_sec REAL,113    completed INTEGER NOT NULL DEFAULT 0, plays INTEGER NOT NULL DEFAULT 1, updated_at INTEGER NOT NULL114  );115  CREATE TABLE title_favs (title_id INTEGER PRIMARY KEY REFERENCES titles(id) ON DELETE CASCADE, added_at INTEGER NOT NULL);116  CREATE TABLE vod_sources (117    id INTEGER PRIMARY KEY, kind TEXT NOT NULL, name TEXT NOT NULL, ref TEXT NOT NULL, as_kind TEXT NOT NULL DEFAULT 'movie',118    rights_status TEXT, rights_note TEXT, enabled INTEGER NOT NULL DEFAULT 1, builtin INTEGER NOT NULL DEFAULT 0,119    last_sync_at INTEGER, last_status TEXT, last_error TEXT, title_count INTEGER NOT NULL DEFAULT 0, created_at INTEGER NOT NULL,120    UNIQUE(kind, ref)121  );122  CREATE TABLE library_dirs (123    id INTEGER PRIMARY KEY, path TEXT NOT NULL UNIQUE, kind TEXT NOT NULL, label TEXT NOT NULL, enabled INTEGER NOT NULL DEFAULT 1,124    last_scan_at INTEGER, last_status TEXT, last_error TEXT, file_count INTEGER NOT NULL DEFAULT 0, new_count INTEGER NOT NULL DEFAULT 0,125    error_count INTEGER NOT NULL DEFAULT 0, created_at INTEGER NOT NULL126  );127  `,128  // 3 — raw playlist identifier of each stream: lets a guide (XMLTV) published for a playlist find its channels.129  `130  ALTER TABLE streams ADD COLUMN tvg_id TEXT;131  CREATE INDEX streams_tvg ON streams(tvg_id) WHERE tvg_id IS NOT NULL;132  ALTER TABLE epg_sources ADD COLUMN builtin INTEGER NOT NULL DEFAULT 0;133  `,134  // 4 — personal M3U playlists for TV apps, each reachable through its own secret address.135  `136  CREATE TABLE playlists (137    id INTEGER PRIMARY KEY, name TEXT NOT NULL, token TEXT NOT NULL UNIQUE, filter TEXT NOT NULL DEFAULT '{}',138    created_at INTEGER NOT NULL, last_used_at INTEGER, last_used_by TEXT, hits INTEGER NOT NULL DEFAULT 0139  );140  `,141  // 5 — universal source engine: per-source configuration and encrypted credentials, uploaded playlists, playback priority,142  // dated rights for catalogues that rotate (ARTE).143  `144  ALTER TABLE sources ADD COLUMN config TEXT;145  ALTER TABLE sources ADD COLUMN secret TEXT;146  ALTER TABLE sources ADD COLUMN content TEXT;147  ALTER TABLE sources ADD COLUMN priority INTEGER NOT NULL DEFAULT 100;148  ALTER TABLE vod_sources ADD COLUMN config TEXT;149  ALTER TABLE vod_sources ADD COLUMN secret TEXT;150  ALTER TABLE titles ADD COLUMN expires_at INTEGER;151  CREATE INDEX titles_expires ON titles(expires_at) WHERE expires_at IS NOT NULL;152  `,153  // 6 — TMDB enrichment bookkeeping.154  `155  ALTER TABLE titles ADD COLUMN tmdb_id TEXT;156  ALTER TABLE titles ADD COLUMN tmdb_checked_at INTEGER;157  `,158  // 7 — relay engine (MediaMTX + FFmpeg): per-source authorisation, relays, playback sessions, events and samples.159  `160  ALTER TABLE sources ADD COLUMN relay TEXT;161  CREATE TABLE relays (162    id TEXT PRIMARY KEY, stream_id INTEGER NOT NULL, channel_id TEXT NOT NULL, source_id INTEGER NOT NULL,163    shared INTEGER NOT NULL DEFAULT 1, record INTEGER NOT NULL DEFAULT 0, owner TEXT,164    plan TEXT, mode TEXT, vcodec TEXT, acodec TEXT, width INTEGER, height INTEGER, probed_at INTEGER,165    state TEXT NOT NULL DEFAULT 'idle', pid INTEGER, ffmpeg_pid INTEGER, created_at INTEGER NOT NULL, started_at INTEGER, ready_at INTEGER,166    stopped_at INTEGER, restarts INTEGER NOT NULL DEFAULT 0, last_error TEXT,167    bytes_in INTEGER NOT NULL DEFAULT 0, bytes_out INTEGER NOT NULL DEFAULT 0, in_changed_at INTEGER, clients INTEGER NOT NULL DEFAULT 0168  );169  CREATE INDEX relays_state ON relays(state);170  CREATE TABLE relay_sessions (171    id TEXT PRIMARY KEY, relay_id TEXT NOT NULL, stream_id INTEGER NOT NULL, channel_id TEXT NOT NULL, auth TEXT NOT NULL,172    ip TEXT, user_agent TEXT, created_at INTEGER NOT NULL, expires_at INTEGER NOT NULL, last_seen_at INTEGER NOT NULL, ended_at INTEGER,173    end_reason TEXT, bytes_out INTEGER NOT NULL DEFAULT 0, requests INTEGER NOT NULL DEFAULT 0174  );175  CREATE INDEX relay_sessions_open ON relay_sessions(ended_at, relay_id);176  CREATE TABLE relay_events (id INTEGER PRIMARY KEY, at INTEGER NOT NULL, relay_id TEXT, level TEXT NOT NULL, kind TEXT NOT NULL, message TEXT NOT NULL);177  CREATE INDEX relay_events_at ON relay_events(at);178  CREATE TABLE relay_samples (179    at INTEGER NOT NULL, relay_id TEXT NOT NULL, cpu REAL NOT NULL DEFAULT 0, rss_mb REAL NOT NULL DEFAULT 0,180    in_kbps REAL NOT NULL DEFAULT 0, out_kbps REAL NOT NULL DEFAULT 0, clients INTEGER NOT NULL DEFAULT 0, PRIMARY KEY (at, relay_id)181  ) WITHOUT ROWID;182  `,183];184185type G = typeof globalThis & { __sptvDb?: DatabaseSync };186187function open(): DatabaseSync {188  fs.mkdirSync(path.dirname(env.dbPath), { recursive: true, mode: 0o700 });189  const db = new DatabaseSync(env.dbPath, { timeout: 15_000 });190  db.exec("PRAGMA journal_mode = WAL; PRAGMA synchronous = NORMAL; PRAGMA foreign_keys = ON; PRAGMA busy_timeout = 15000;");191  const version = (db.prepare("PRAGMA user_version").get() as { user_version: number }).user_version;192  for (let i = version; i < MIGRATIONS.length; i++) {193    db.exec("BEGIN IMMEDIATE");194    try {195      // Another process may have migrated while we waited for the lock.196      const now = (db.prepare("PRAGMA user_version").get() as { user_version: number }).user_version;197      if (now <= i) { db.exec(MIGRATIONS[i]); db.exec(`PRAGMA user_version = ${i + 1}`); }198      db.exec("COMMIT");199    } catch (e) { db.exec("ROLLBACK"); throw e; }200  }201  try { fs.chmodSync(env.dbPath, 0o600); } catch { /* best effort */ }202  return db;203}204205export function db(): DatabaseSync {206  const g = globalThis as G;207  return (g.__sptvDb ??= open());208}209export function closeDb() { const g = globalThis as G; g.__sptvDb?.close(); g.__sptvDb = undefined; }210211type P = SQLInputValue;212export const all = <T = Record<string, unknown>>(sql: string, ...p: P[]) => db().prepare(sql).all(...p) as T[];213export const get = <T = Record<string, unknown>>(sql: string, ...p: P[]) => db().prepare(sql).get(...p) as T | undefined;214export const run = (sql: string, ...p: P[]) => db().prepare(sql).run(...p);215216export function tx<T>(fn: () => T): T {217  const d = db();218  d.exec("BEGIN IMMEDIATE");219  try { const r = fn(); d.exec("COMMIT"); return r; } catch (e) { d.exec("ROLLBACK"); throw e; }220}221222export const now = () => Date.now();223224// --- settings -------------------------------------------------------------------------------------------------------225export const DEFAULT_SETTINGS = {226  sync_interval_hours: "24",227  check_enabled: "1",228  check_concurrency: "6",229  check_host_delay_ms: "1500",230  check_timeout_ms: "10000",231  check_recheck_hours: "24",232  check_priority_hours: "6",233  epg_enabled: "1",234  epg_interval_hours: "12",235  epg_countries: "CA,FR",236  epg_max_channels: "250",237  epg_days: "2",238  session_days: "60",239  auto_resume: "1",240  premium_filter: "1",241  vod_interval_hours: "168",242  library_interval_minutes: "15",243  transcode_cache_gb: "30",244  sources_interval_hours: "24",245  relay_enabled: "1",246  relay_max_relays: "6",247  relay_max_transcodes: "2",248  relay_max_sessions: "12",249  relay_buffer_sec: "12",250  relay_idle_sec: "30",251  relay_out_kbps: "0",252  relay_video_kbps: "4500",253  relay_timeshift_min: "120",254  relay_reconnect_max: "8",255} as const;256export type SettingKey = keyof typeof DEFAULT_SETTINGS;257258export function getSetting(key: string): string | undefined {259  return get<{ value: string }>("SELECT value FROM settings WHERE key = ?", key)?.value ?? (DEFAULT_SETTINGS as Record<string, string>)[key];260}261export const getNum = (key: SettingKey) => Number(getSetting(key));262export function setSetting(key: string, value: string) {263  run("INSERT INTO settings(key, value) VALUES(?, ?) ON CONFLICT(key) DO UPDATE SET value = excluded.value", key, value);264}265266// --- logs -----------------------------------------------------------------------------------------------------------267export function log(level: "info" | "warn" | "error", scope: string, message: string, meta?: unknown) {268  const line = `${new Date().toISOString()} ${level.toUpperCase()} [${scope}] ${message}${meta ? " " + JSON.stringify(meta) : ""}`;269  (level === "error" ? console.error : console.log)(line);270  try { run("INSERT INTO logs(at, level, scope, message, meta) VALUES(?,?,?,?,?)", now(), level, scope, message.slice(0, 2000), meta ? JSON.stringify(meta).slice(0, 4000) : null); } catch { /* never let logging break the caller */ }271}272/** Bumped whenever the catalogue changes; the web process rebuilds its in-memory index when it sees a new value. */273export function bumpCatalog() { setSetting("catalog_version", String(now())); }274export function bumpTitles() { setSetting("titles_version", String(now())); }275
Files main
Menu

Theme