61 lines
2.3 KiB
SQL
61 lines
2.3 KiB
SQL
-- Core library schema per spec §5. Dual-like, session, scrobble, and
|
|
-- similarity tables land with their respective feature milestones.
|
|
|
|
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
|
|
|
|
CREATE TABLE artists (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
name text NOT NULL,
|
|
sort_name text NOT NULL,
|
|
mbid text,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
CREATE UNIQUE INDEX artists_mbid_unique ON artists (mbid) WHERE mbid IS NOT NULL;
|
|
CREATE INDEX artists_sort_name ON artists (sort_name);
|
|
|
|
CREATE TABLE albums (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
title text NOT NULL,
|
|
sort_title text NOT NULL,
|
|
artist_id uuid NOT NULL REFERENCES artists (id) ON DELETE CASCADE,
|
|
release_date date,
|
|
mbid text,
|
|
cover_art_path text,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
CREATE UNIQUE INDEX albums_mbid_unique ON albums (mbid) WHERE mbid IS NOT NULL;
|
|
CREATE INDEX albums_artist ON albums (artist_id);
|
|
CREATE INDEX albums_sort_title ON albums (sort_title);
|
|
|
|
CREATE TABLE tracks (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
title text NOT NULL,
|
|
album_id uuid NOT NULL REFERENCES albums (id) ON DELETE CASCADE,
|
|
artist_id uuid NOT NULL REFERENCES artists (id) ON DELETE CASCADE,
|
|
track_number integer,
|
|
disc_number integer,
|
|
duration_ms integer NOT NULL,
|
|
file_path text NOT NULL UNIQUE,
|
|
file_size bigint NOT NULL,
|
|
file_format text NOT NULL,
|
|
bitrate integer,
|
|
mbid text,
|
|
genre text,
|
|
added_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
CREATE UNIQUE INDEX tracks_mbid_unique ON tracks (mbid) WHERE mbid IS NOT NULL;
|
|
CREATE INDEX tracks_album ON tracks (album_id, disc_number, track_number);
|
|
CREATE INDEX tracks_artist ON tracks (artist_id);
|
|
|
|
CREATE TABLE users (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
username text NOT NULL UNIQUE,
|
|
password_hash text NOT NULL,
|
|
api_token text NOT NULL UNIQUE,
|
|
is_admin boolean NOT NULL DEFAULT false,
|
|
created_at timestamptz NOT NULL DEFAULT now()
|
|
);
|