6f551ba439
Dockerized media library scanner for NAS drives. Identifies movies and series from filenames, fetches posters via the public IMDB suggestion API (no key required), and exposes a dark-themed web UI with multilingual search support. Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
120 lines
6.2 KiB
SQL
120 lines
6.2 KiB
SQL
-- MediaTracker database schema
|
|
-- MariaDB 11 / utf8mb4
|
|
|
|
CREATE DATABASE IF NOT EXISTS mediatracker
|
|
CHARACTER SET utf8mb4
|
|
COLLATE utf8mb4_unicode_ci;
|
|
|
|
USE mediatracker;
|
|
|
|
-- ─────────────────────────────────────────
|
|
-- NAS sources
|
|
-- ─────────────────────────────────────────
|
|
CREATE TABLE IF NOT EXISTS nas_sources (
|
|
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
name VARCHAR(255) NOT NULL,
|
|
path VARCHAR(1024) NOT NULL,
|
|
last_scan DATETIME NULL,
|
|
PRIMARY KEY (id),
|
|
UNIQUE KEY uq_nas_path (path(512))
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- ─────────────────────────────────────────
|
|
-- Movies
|
|
-- ─────────────────────────────────────────
|
|
CREATE TABLE IF NOT EXISTS movies (
|
|
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
title VARCHAR(512) NOT NULL,
|
|
year SMALLINT NULL,
|
|
quality VARCHAR(64) NULL,
|
|
codec VARCHAR(64) NULL,
|
|
file_path VARCHAR(2048) NOT NULL,
|
|
file_size BIGINT UNSIGNED NULL,
|
|
nas_id INT UNSIGNED NOT NULL,
|
|
added_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (id),
|
|
UNIQUE KEY uq_movie_path (file_path(512)),
|
|
INDEX idx_movie_title (title(255)),
|
|
CONSTRAINT fk_movie_nas FOREIGN KEY (nas_id) REFERENCES nas_sources (id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- ─────────────────────────────────────────
|
|
-- Series
|
|
-- ─────────────────────────────────────────
|
|
CREATE TABLE IF NOT EXISTS series (
|
|
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
title VARCHAR(512) NOT NULL,
|
|
title_normalized VARCHAR(512) NOT NULL,
|
|
year SMALLINT NULL,
|
|
added_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (id),
|
|
UNIQUE KEY uq_series_normalized (title_normalized(255)),
|
|
INDEX idx_series_title (title(255))
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- ─────────────────────────────────────────
|
|
-- Seasons
|
|
-- ─────────────────────────────────────────
|
|
CREATE TABLE IF NOT EXISTS seasons (
|
|
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
series_id INT UNSIGNED NOT NULL,
|
|
season_number SMALLINT NOT NULL,
|
|
nas_id INT UNSIGNED NOT NULL,
|
|
PRIMARY KEY (id),
|
|
UNIQUE KEY uq_season (series_id, season_number, nas_id),
|
|
CONSTRAINT fk_season_series FOREIGN KEY (series_id) REFERENCES series (id) ON DELETE CASCADE,
|
|
CONSTRAINT fk_season_nas FOREIGN KEY (nas_id) REFERENCES nas_sources (id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- ─────────────────────────────────────────
|
|
-- Episodes
|
|
-- ─────────────────────────────────────────
|
|
CREATE TABLE IF NOT EXISTS episodes (
|
|
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
season_id INT UNSIGNED NOT NULL,
|
|
episode_number SMALLINT NOT NULL,
|
|
title VARCHAR(512) NULL,
|
|
quality VARCHAR(64) NULL,
|
|
codec VARCHAR(64) NULL,
|
|
file_path VARCHAR(2048) NOT NULL,
|
|
file_size BIGINT UNSIGNED NULL,
|
|
added_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (id),
|
|
UNIQUE KEY uq_episode_path (file_path(512)),
|
|
CONSTRAINT fk_episode_season FOREIGN KEY (season_id) REFERENCES seasons (id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- ─────────────────────────────────────────
|
|
-- Unmatched files
|
|
-- ─────────────────────────────────────────
|
|
CREATE TABLE IF NOT EXISTS unmatched_files (
|
|
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
file_path VARCHAR(2048) NOT NULL,
|
|
nas_id INT UNSIGNED NOT NULL,
|
|
reason VARCHAR(512) NOT NULL,
|
|
scanned_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (id),
|
|
INDEX idx_unmatched_nas (nas_id),
|
|
CONSTRAINT fk_unmatched_nas FOREIGN KEY (nas_id) REFERENCES nas_sources (id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- ─────────────────────────────────────────
|
|
-- Scan history
|
|
-- ─────────────────────────────────────────
|
|
CREATE TABLE IF NOT EXISTS scan_history (
|
|
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
scan_type ENUM('full','incremental') NOT NULL,
|
|
status ENUM('running','completed','failed') NOT NULL DEFAULT 'running',
|
|
started_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
finished_at DATETIME NULL,
|
|
files_found INT UNSIGNED NOT NULL DEFAULT 0,
|
|
files_added INT UNSIGNED NOT NULL DEFAULT 0,
|
|
files_removed INT UNSIGNED NOT NULL DEFAULT 0,
|
|
files_unmatched INT UNSIGNED NOT NULL DEFAULT 0,
|
|
error_message TEXT NULL,
|
|
PRIMARY KEY (id),
|
|
INDEX idx_scan_started (started_at)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|