NotAShelf
6a73d11c4b
Signed-off-by: NotAShelf <raf@notashelf.dev> Change-Id: I4a6b498153eccd5407510dd541b7f4816a6a6964
73 lines
2.1 KiB
SQL
73 lines
2.1 KiB
SQL
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
|
|
CREATE EXTENSION IF NOT EXISTS pg_trgm;
|
|
|
|
CREATE TABLE IF NOT EXISTS root_dirs (
|
|
path TEXT PRIMARY KEY NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS media_items (
|
|
id UUID 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 BIGINT NOT NULL,
|
|
title TEXT,
|
|
artist TEXT,
|
|
album TEXT,
|
|
genre TEXT,
|
|
year INTEGER,
|
|
duration_secs DOUBLE PRECISION,
|
|
description TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL,
|
|
updated_at TIMESTAMPTZ NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS tags (
|
|
id UUID PRIMARY KEY NOT NULL,
|
|
name TEXT NOT NULL,
|
|
parent_id UUID REFERENCES tags(id) ON DELETE SET NULL,
|
|
created_at TIMESTAMPTZ NOT NULL
|
|
);
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_tags_name_parent ON tags(name, COALESCE(parent_id, '00000000-0000-0000-0000-000000000000'));
|
|
|
|
CREATE TABLE IF NOT EXISTS media_tags (
|
|
media_id UUID NOT NULL REFERENCES media_items(id) ON DELETE CASCADE,
|
|
tag_id UUID NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
|
|
PRIMARY KEY (media_id, tag_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS collections (
|
|
id UUID PRIMARY KEY NOT NULL,
|
|
name TEXT NOT NULL,
|
|
description TEXT,
|
|
kind TEXT NOT NULL,
|
|
filter_query TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL,
|
|
updated_at TIMESTAMPTZ NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS collection_members (
|
|
collection_id UUID NOT NULL REFERENCES collections(id) ON DELETE CASCADE,
|
|
media_id UUID NOT NULL REFERENCES media_items(id) ON DELETE CASCADE,
|
|
position INTEGER NOT NULL DEFAULT 0,
|
|
added_at TIMESTAMPTZ NOT NULL,
|
|
PRIMARY KEY (collection_id, media_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS audit_log (
|
|
id UUID PRIMARY KEY NOT NULL,
|
|
media_id UUID REFERENCES media_items(id) ON DELETE SET NULL,
|
|
action TEXT NOT NULL,
|
|
details TEXT,
|
|
timestamp TIMESTAMPTZ NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS custom_fields (
|
|
media_id UUID NOT NULL REFERENCES media_items(id) ON DELETE CASCADE,
|
|
field_name TEXT NOT NULL,
|
|
field_type TEXT NOT NULL,
|
|
field_value TEXT NOT NULL,
|
|
PRIMARY KEY (media_id, field_name)
|
|
);
|