NotAShelf
6a73d11c4b
Signed-off-by: NotAShelf <raf@notashelf.dev> Change-Id: I4a6b498153eccd5407510dd541b7f4816a6a6964
77 lines
2.1 KiB
SQL
77 lines
2.1 KiB
SQL
CREATE TABLE IF NOT EXISTS root_dirs (
|
|
path TEXT PRIMARY KEY NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS media_items (
|
|
id TEXT PRIMARY KEY NOT NULL,
|
|
path TEXT NOT NULL UNIQUE,
|
|
file_name TEXT NOT NULL,
|
|
media_type TEXT NOT NULL,
|
|
content_hash TEXT NOT NULL UNIQUE,
|
|
file_size INTEGER NOT NULL,
|
|
title TEXT,
|
|
artist TEXT,
|
|
album TEXT,
|
|
genre TEXT,
|
|
year INTEGER,
|
|
duration_secs REAL,
|
|
description TEXT,
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS tags (
|
|
id TEXT PRIMARY KEY NOT NULL,
|
|
name TEXT NOT NULL,
|
|
parent_id TEXT,
|
|
created_at TEXT NOT NULL,
|
|
FOREIGN KEY (parent_id) REFERENCES tags(id) ON DELETE SET NULL
|
|
);
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_tags_name_parent ON tags(name, parent_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS media_tags (
|
|
media_id TEXT NOT NULL,
|
|
tag_id TEXT NOT NULL,
|
|
PRIMARY KEY (media_id, tag_id),
|
|
FOREIGN KEY (media_id) REFERENCES media_items(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS collections (
|
|
id TEXT PRIMARY KEY NOT NULL,
|
|
name TEXT NOT NULL,
|
|
description TEXT,
|
|
kind TEXT NOT NULL,
|
|
filter_query TEXT,
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS collection_members (
|
|
collection_id TEXT NOT NULL,
|
|
media_id TEXT NOT NULL,
|
|
position INTEGER NOT NULL DEFAULT 0,
|
|
added_at TEXT NOT NULL,
|
|
PRIMARY KEY (collection_id, media_id),
|
|
FOREIGN KEY (collection_id) REFERENCES collections(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (media_id) REFERENCES media_items(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS audit_log (
|
|
id TEXT PRIMARY KEY NOT NULL,
|
|
media_id TEXT,
|
|
action TEXT NOT NULL,
|
|
details TEXT,
|
|
timestamp TEXT NOT NULL,
|
|
FOREIGN KEY (media_id) REFERENCES media_items(id) ON DELETE SET NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS custom_fields (
|
|
media_id TEXT NOT NULL,
|
|
field_name TEXT NOT NULL,
|
|
field_type TEXT NOT NULL,
|
|
field_value TEXT NOT NULL,
|
|
PRIMARY KEY (media_id, field_name),
|
|
FOREIGN KEY (media_id) REFERENCES media_items(id) ON DELETE CASCADE
|
|
);
|