39 lines
1.5 KiB
SQL
39 lines
1.5 KiB
SQL
|
-- +goose Up
|
||
|
|
-- +goose StatementBegin
|
||
|
|
-- Remote promotional/supplemental videos (trailers, teasers, featurettes, ...)
|
||
|
|
-- discovered by metadata providers during match/refresh. Rows are replaced
|
||
|
|
-- wholesale per refresh (item_people pattern); the (content_id, provider,
|
||
|
|
-- provider_key) key dedupes re-fetches of the same provider video.
|
||
|
|
CREATE TABLE item_videos (
|
||
|
|
id BIGINT PRIMARY KEY,
|
||
|
|
content_id TEXT NOT NULL REFERENCES media_items(content_id) ON DELETE CASCADE,
|
||
|
|
provider TEXT NOT NULL,
|
||
|
|
provider_key TEXT NOT NULL,
|
||
|
|
-- Lowercase snake_case kind vocabulary shared with media_extras.kind:
|
||
|
|
-- trailer, teaser, featurette, clip, behind_the_scenes, bloopers, other.
|
||
|
|
-- Deliberately CHECK-free: unknown provider kinds normalize to 'other' in
|
||
|
|
-- code, and new kinds must not require a migration.
|
||
|
|
kind TEXT NOT NULL,
|
||
|
|
site TEXT NOT NULL DEFAULT 'youtube',
|
||
|
|
site_key TEXT NOT NULL,
|
||
|
|
name TEXT NOT NULL DEFAULT '',
|
||
|
|
language TEXT NOT NULL DEFAULT '',
|
||
|
|
is_official BOOLEAN NOT NULL DEFAULT FALSE,
|
||
|
|
size_hint INTEGER NOT NULL DEFAULT 0,
|
||
|
|
published_at TIMESTAMPTZ,
|
||
|
|
sort_order INTEGER NOT NULL DEFAULT 0,
|
||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
||
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
||
|
|
UNIQUE (content_id, provider, provider_key)
|
||
|
|
);
|
||
|
|
-- +goose StatementEnd
|
||
|
|
|
||
|
|
-- +goose StatementBegin
|
||
|
|
CREATE INDEX idx_item_videos_content_id ON item_videos (content_id);
|
||
|
|
-- +goose StatementEnd
|
||
|
|
|
||
|
|
-- +goose Down
|
||
|
|
-- +goose StatementBegin
|
||
|
|
DROP TABLE IF EXISTS item_videos;
|
||
|
|
-- +goose StatementEnd
|