METANOIA/schema.sql

CREATE EXTENSION IF NOT EXISTS hstore;
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS btree_gin;

CREATE TYPE name_kind AS ENUM ('main', 'original', 'romaji', 'kanji', 'english');
CREATE TYPE artist_kind AS ENUM ('main', 'original', 'performer', 'composer', 'arranger');


-- groups resources in specific releases / groupings
CREATE TABLE releases (
    id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    identifiers TEXT[] NOT NULL DEFAULT '{}'::TEXT[], -- array of "semi-unique" identifiers, like site sources or identifiers
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb
);

CREATE INDEX idx_releases_identifiers_gin ON releases USING GIN (identifiers);

CREATE TABLE resources (
    id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    hash bytea UNIQUE NOT NULL, -- hash of the resource, SHA256
    size BIGINT NOT NULL,
    path bytea, -- can be null if not available locally
    mime TEXT NOT NULL
);

CREATE INDEX idx_resources_hash ON resources USING BTREE (hash); -- TODO: check if you can do partial prefix queries
CREATE INDEX idx_resources_path ON resources USING BTREE (path);
CREATE INDEX idx_resources_path_gin ON resources USING GIN (path); -- Allows for partial path queries

CREATE TABLE resource_alternate_identifiers (
    resource BIGINT NOT NULL,
    name TEXT NOT NULL,
    identifier bytea NOT NULL,

    CONSTRAINT fk_resource FOREIGN KEY (resource) REFERENCES resources(id),
    CONSTRAINT idx_resource_alternate_identifiers UNIQUE (resource, name, identifier)
);
CREATE INDEX idx_resource_alternate_identifiers_resource ON resource_alternate_identifiers USING BTREE (resource) INCLUDE (name, identifier);
CREATE INDEX idx_resource_alternate_identifiers_name_identifier ON resource_alternate_identifiers USING BTREE (name, identifier) INCLUDE (resource);

CREATE TABLE resource_releases (
    resource BIGINT NOT NULL,
    release BIGINT NOT NULL,

    CONSTRAINT fk_resource FOREIGN KEY (resource) REFERENCES resources(id),
    CONSTRAINT fk_release FOREIGN KEY (release) REFERENCES releases(id),
    CONSTRAINT idx_resource_releases UNIQUE (resource, release)
);

CREATE INDEX idx_resource_releases_resource ON resource_releases USING BTREE (resource) INCLUDE (release);
CREATE INDEX idx_resource_releases_release ON resource_releases USING BTREE (release) INCLUDE (resource);

CREATE TABLE albums (
    id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    cover BIGINT, -- can be null if not available
    identifiers TEXT[] NOT NULL DEFAULT '{}'::TEXT[], -- array of "semi-unique" identifiers, like catalog number
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,

    CONSTRAINT fk_cover FOREIGN KEY (cover) REFERENCES resources(id)
);
CREATE INDEX idx_albums_identifiers_gin ON albums USING GIN (identifiers);


CREATE TABLE albums_names (
    album INTEGER NOT NULL,
    kind name_kind NOT NULL,
    name TEXT NOT NULL,
    CONSTRAINT idx_albums_names UNIQUE (album, kind),
    CONSTRAINT fk_album FOREIGN KEY (album) REFERENCES albums(id)
);
CREATE INDEX idx_albums_names_album ON albums_names USING BTREE (album);
CREATE INDEX idx_albums_names_name_gin ON albums_names USING GIN (name gin_trgm_ops);


CREATE TABLE artists (
    id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb
);

CREATE TABLE artists_names (
    artist INTEGER NOT NULL,
    kind name_kind NOT NULL,
    name TEXT NOT NULL,
    CONSTRAINT idx_artists_names UNIQUE (artist, kind),
    CONSTRAINT fk_artist FOREIGN KEY (artist) REFERENCES artists(id)
);
CREATE INDEX idx_artists_names_artist ON artists_names USING BTREE (artist);
CREATE INDEX idx_artists_names_name_gin ON artists_names USING GIN (name gin_trgm_ops);

CREATE TABLE songs (
    id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    resource BIGINT NOT NULL,
    cover BIGINT, -- set to override album cover. can be null if not available
    album INTEGER, -- can be null
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,

    CONSTRAINT fk_resource FOREIGN KEY (resource) REFERENCES resources(id),
    CONSTRAINT fk_cover FOREIGN KEY (cover) REFERENCES resources(id),
    CONSTRAINT fk_album FOREIGN KEY (album) REFERENCES albums(id)
);
CREATE INDEX idx_songs_resource ON songs USING BTREE (resource);
CREATE INDEX idx_songs_album ON songs USING BTREE (album);

CREATE TABLE songs_names (
    song INTEGER NOT NULL,
    kind name_kind NOT NULL,
    name TEXT NOT NULL,
    CONSTRAINT idx_songs_names UNIQUE (song, kind),
    CONSTRAINT fk_song FOREIGN KEY (song) REFERENCES songs(id)
);
CREATE INDEX idx_songs_names_song ON songs_names USING BTREE (song);
CREATE INDEX idx_songs_names_name_gin ON songs_names USING GIN (name gin_trgm_ops);

-- extra mapping table of artists <-> song
CREATE TABLE album_artists (
    album INTEGER NOT NULL,
    artist INTEGER NOT NULL,
    kind artist_kind NOT NULL,
    CONSTRAINT idx_album_artists UNIQUE (album, artist, kind),
    CONSTRAINT fk_album FOREIGN KEY (album) REFERENCES albums(id),
    CONSTRAINT fk_artist FOREIGN KEY (artist) REFERENCES artists(id)
);

CREATE INDEX idx_album_artists_album ON album_artists USING BTREE (album) INCLUDE (artist, kind);
CREATE INDEX idx_album_artists_artist ON album_artists USING BTREE (artist) INCLUDE (album);
-- CREATE INDEX idx_album_artists_artist_kind ON album_artists (artist, kind);

-- extra mapping table of artists <-> song
CREATE TABLE song_artists (
    song INTEGER NOT NULL,
    artist INTEGER NOT NULL,
    kind artist_kind NOT NULL,
    CONSTRAINT idx_song_artists UNIQUE (song, artist, kind),
    CONSTRAINT fk_song FOREIGN KEY (song) REFERENCES songs(id),
    CONSTRAINT fk_artist FOREIGN KEY (artist) REFERENCES artists(id)
);

CREATE INDEX idx_song_artists_song ON song_artists USING BTREE (song) INCLUDE (artist, kind);
CREATE INDEX idx_song_artists_artist ON song_artists USING BTREE (artist) INCLUDE (song);
-- CREATE INDEX idx_song_artists_artist_kind ON song_artists (artist, kind);

CREATE TABLE tags (
    id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    name TEXT UNIQUE NOT NULL
);

-- primary key covering index
CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS tags_pkey_new ON tags USING BTREE (id) INCLUDE (name);
ALTER TABLE tags DROP CONSTRAINT tags_pkey, ADD CONSTRAINT tags_pkey PRIMARY KEY USING INDEX tags_pkey_new;

CREATE INDEX idx_tags_name ON tags USING BTREE (name) INCLUDE (id);


CREATE TABLE song_taggings (
    tag INTEGER NOT NULL,
    song BIGINT NOT NULL,

    CONSTRAINT fk_tag FOREIGN KEY (tag) REFERENCES tags(id),
    CONSTRAINT fk_song FOREIGN KEY (song) REFERENCES songs(id),
    CONSTRAINT idx_song_taggings UNIQUE (tag, song)
);

CREATE INDEX idx_song_taggings_tag ON song_taggings USING BTREE (tag) INCLUDE (song);
CREATE INDEX idx_song_taggings_song ON song_taggings USING BTREE (song) INCLUDE (tag);


CREATE TABLE album_taggings (
    tag INTEGER NOT NULL,
    album INTEGER NOT NULL,

    CONSTRAINT fk_tag FOREIGN KEY (tag) REFERENCES tags(id),
    CONSTRAINT fk_song FOREIGN KEY (album) REFERENCES albums(id),
    CONSTRAINT idx_album_taggings UNIQUE (tag, album)
);

CREATE INDEX idx_album_taggings_tag ON album_taggings USING BTREE (tag) INCLUDE (album);
CREATE INDEX idx_album_taggings_album ON album_taggings USING BTREE (album) INCLUDE (tag);

-- -- Panako fingerprints
-- CREATE TABLE song_fingerprints (
--     song BIGINT NOT NULL,
--     hash INTEGER NOT NULL, -- 32-bit value
--     packed INTEGER NOT NULL, -- 32-bit value, packed Time + Frequency
--     CONSTRAINT idx_song_fingerprints UNIQUE (song, hash, packed),
--     CONSTRAINT fk_song FOREIGN KEY (song) REFERENCES songs(id)
-- );

-- -- TODO: check this again when CLUSTER works for BRIN: CREATE INDEX idx_song_fingerprints_hash ON song_fingerprints USING BRIN (hash) WITH (pages_per_range = 32, autosummarize = on); -- Use BRIN indexes to have small cache size, BTREE would have more overhead on top
-- CREATE INDEX idx_song_fingerprints_hash ON song_fingerprints USING BTREE (hash) WITH (fillfactor = 80); -- lower fillfactor for faster insertions
-- CLUSTER VERBOSE song_fingerprints USING idx_song_fingerprints_hash;