mirror of https://github.com/synctv-org/synctv
You cannot select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
58 lines
2.3 KiB
SQL
58 lines
2.3 KiB
SQL
CREATE TABLE IF NOT EXISTS media (
|
|
id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
|
|
playlist_id BIGINT,
|
|
|
|
room_id BIGINT NOT NULL REFERENCES rooms(id) ON DELETE RESTRICT,
|
|
creator_id BIGINT REFERENCES users(id) ON DELETE RESTRICT,
|
|
|
|
name VARCHAR(500) NOT NULL,
|
|
description TEXT NOT NULL DEFAULT '',
|
|
|
|
position DOUBLE PRECISION NOT NULL,
|
|
|
|
source_provider SMALLINT NOT NULL,
|
|
|
|
source_config JSONB NOT NULL,
|
|
|
|
provider_instance_name VARCHAR(64),
|
|
|
|
cover_file_reference_id BIGINT NULL REFERENCES file_references(id) ON DELETE SET NULL,
|
|
thumbnail_file_reference_id BIGINT NULL REFERENCES file_references(id) ON DELETE SET NULL,
|
|
|
|
added_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
version INTEGER NOT NULL DEFAULT 0,
|
|
|
|
CONSTRAINT media_playlist_same_room_fk
|
|
FOREIGN KEY (playlist_id, room_id)
|
|
REFERENCES playlists(id, room_id)
|
|
ON DELETE RESTRICT,
|
|
CONSTRAINT media_provider_instance_fk
|
|
FOREIGN KEY (provider_instance_name)
|
|
REFERENCES media_provider_instances(name)
|
|
ON DELETE RESTRICT,
|
|
CONSTRAINT media_source_config_object
|
|
CHECK (jsonb_typeof(source_config) = 'object')
|
|
);
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_media_id_room_unique ON media(id, room_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_media_room ON media(room_id);
|
|
CREATE INDEX IF NOT EXISTS idx_media_creator ON media(creator_id);
|
|
CREATE INDEX IF NOT EXISTS idx_media_added_at ON media(added_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_media_source_provider ON media(source_provider);
|
|
CREATE INDEX IF NOT EXISTS idx_media_provider_name ON media(provider_instance_name);
|
|
CREATE INDEX IF NOT EXISTS idx_media_source_config ON media USING gin(source_config);
|
|
CREATE INDEX IF NOT EXISTS idx_media_name_trgm ON media USING gin(name gin_trgm_ops);
|
|
CREATE INDEX IF NOT EXISTS idx_media_description_trgm ON media USING gin(description gin_trgm_ops);
|
|
CREATE INDEX IF NOT EXISTS idx_media_playlist_covering ON media(playlist_id, position, id, source_provider, name);
|
|
CREATE INDEX IF NOT EXISTS idx_media_room_root_covering ON media(room_id, position, id, source_provider, name)
|
|
WHERE playlist_id IS NULL;
|
|
|
|
CREATE TRIGGER trigger_media_updated_at
|
|
BEFORE UPDATE ON media
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION update_updated_at_column();
|