127 lines
5.5 KiB
SQL
127 lines
5.5 KiB
SQL
-- +goose Up
|
|
-- +goose StatementBegin
|
|
ALTER TABLE media_files
|
|
ADD COLUMN IF NOT EXISTS observed_root_path TEXT NOT NULL DEFAULT '',
|
|
ADD COLUMN IF NOT EXISTS content_group_key TEXT NOT NULL DEFAULT '',
|
|
ADD COLUMN IF NOT EXISTS group_key_version INTEGER NOT NULL DEFAULT 1,
|
|
ADD COLUMN IF NOT EXISTS base_title TEXT NOT NULL DEFAULT '',
|
|
ADD COLUMN IF NOT EXISTS base_year INTEGER NOT NULL DEFAULT 0,
|
|
ADD COLUMN IF NOT EXISTS base_type TEXT NOT NULL DEFAULT '',
|
|
ADD COLUMN IF NOT EXISTS identity_confidence TEXT NOT NULL DEFAULT 'low',
|
|
ADD COLUMN IF NOT EXISTS identity_json JSONB NOT NULL DEFAULT '{}'::jsonb;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_media_files_folder_group
|
|
ON media_files (media_folder_id, group_key_version, content_group_key);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_media_files_folder_observed_root
|
|
ON media_files (media_folder_id, observed_root_path);
|
|
|
|
CREATE TABLE IF NOT EXISTS scanned_media_groups (
|
|
media_folder_id INTEGER NOT NULL REFERENCES media_folders(id) ON DELETE CASCADE,
|
|
group_key_version INTEGER NOT NULL,
|
|
content_group_key TEXT NOT NULL,
|
|
state TEXT NOT NULL DEFAULT 'resolved',
|
|
inferred_type TEXT NOT NULL DEFAULT '',
|
|
type_confidence TEXT NOT NULL DEFAULT 'low',
|
|
base_title TEXT NOT NULL DEFAULT '',
|
|
base_year INTEGER NOT NULL DEFAULT 0,
|
|
tmdb_id TEXT NOT NULL DEFAULT '',
|
|
imdb_id TEXT NOT NULL DEFAULT '',
|
|
tvdb_id TEXT NOT NULL DEFAULT '',
|
|
observed_file_count INTEGER NOT NULL DEFAULT 0,
|
|
sample_file_path TEXT NOT NULL DEFAULT '',
|
|
sample_observed_root_path TEXT NOT NULL DEFAULT '',
|
|
evidence_json JSONB NOT NULL DEFAULT '{}'::jsonb,
|
|
override_source TEXT NOT NULL DEFAULT 'none',
|
|
first_seen_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
last_seen_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
PRIMARY KEY (media_folder_id, group_key_version, content_group_key)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_scanned_media_groups_state_last_seen
|
|
ON scanned_media_groups (media_folder_id, state, last_seen_at DESC);
|
|
|
|
CREATE TABLE IF NOT EXISTS observed_media_locations (
|
|
media_folder_id INTEGER NOT NULL REFERENCES media_folders(id) ON DELETE CASCADE,
|
|
observed_root_path TEXT NOT NULL,
|
|
location_type TEXT NOT NULL DEFAULT '',
|
|
sample_file_path TEXT NOT NULL DEFAULT '',
|
|
observed_file_count INTEGER NOT NULL DEFAULT 0,
|
|
content_group_count INTEGER NOT NULL DEFAULT 0,
|
|
primary_group_key_version INTEGER NOT NULL DEFAULT 0,
|
|
primary_content_group_key TEXT NOT NULL DEFAULT '',
|
|
state TEXT NOT NULL DEFAULT 'resolved',
|
|
evidence_json JSONB NOT NULL DEFAULT '{}'::jsonb,
|
|
first_seen_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
last_seen_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
PRIMARY KEY (media_folder_id, observed_root_path)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS media_group_locations (
|
|
media_folder_id INTEGER NOT NULL REFERENCES media_folders(id) ON DELETE CASCADE,
|
|
group_key_version INTEGER NOT NULL,
|
|
content_group_key TEXT NOT NULL,
|
|
observed_root_path TEXT NOT NULL,
|
|
is_primary BOOLEAN NOT NULL DEFAULT false,
|
|
first_seen_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
last_seen_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
PRIMARY KEY (media_folder_id, group_key_version, content_group_key, observed_root_path)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_media_group_locations_observed_root
|
|
ON media_group_locations (media_folder_id, observed_root_path);
|
|
|
|
CREATE TABLE IF NOT EXISTS media_item_groups (
|
|
media_folder_id INTEGER NOT NULL REFERENCES media_folders(id) ON DELETE CASCADE,
|
|
group_key_version INTEGER NOT NULL,
|
|
content_group_key TEXT NOT NULL,
|
|
content_id TEXT NOT NULL REFERENCES media_items(content_id) ON DELETE CASCADE,
|
|
first_seen_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
last_seen_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
PRIMARY KEY (media_folder_id, group_key_version, content_group_key)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_media_item_groups_content_id
|
|
ON media_item_groups (content_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS media_group_overrides (
|
|
media_folder_id INTEGER NOT NULL REFERENCES media_folders(id) ON DELETE CASCADE,
|
|
group_key_version INTEGER NOT NULL,
|
|
content_group_key TEXT NOT NULL,
|
|
forced_type TEXT NOT NULL DEFAULT '',
|
|
forced_title TEXT NOT NULL DEFAULT '',
|
|
forced_year INTEGER NOT NULL DEFAULT 0,
|
|
forced_tmdb_id TEXT NOT NULL DEFAULT '',
|
|
forced_imdb_id TEXT NOT NULL DEFAULT '',
|
|
forced_tvdb_id TEXT NOT NULL DEFAULT '',
|
|
note TEXT NOT NULL DEFAULT '',
|
|
created_by_user_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
|
|
updated_by_user_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
PRIMARY KEY (media_folder_id, group_key_version, content_group_key)
|
|
);
|
|
-- +goose StatementEnd
|
|
|
|
-- +goose Down
|
|
-- +goose StatementBegin
|
|
DROP TABLE IF EXISTS media_group_overrides;
|
|
DROP TABLE IF EXISTS media_item_groups;
|
|
DROP TABLE IF EXISTS media_group_locations;
|
|
DROP TABLE IF EXISTS observed_media_locations;
|
|
DROP TABLE IF EXISTS scanned_media_groups;
|
|
|
|
DROP INDEX IF EXISTS idx_media_files_folder_observed_root;
|
|
DROP INDEX IF EXISTS idx_media_files_folder_group;
|
|
|
|
ALTER TABLE media_files
|
|
DROP COLUMN IF EXISTS identity_json,
|
|
DROP COLUMN IF EXISTS identity_confidence,
|
|
DROP COLUMN IF EXISTS base_type,
|
|
DROP COLUMN IF EXISTS base_year,
|
|
DROP COLUMN IF EXISTS base_title,
|
|
DROP COLUMN IF EXISTS group_key_version,
|
|
DROP COLUMN IF EXISTS content_group_key,
|
|
DROP COLUMN IF EXISTS observed_root_path;
|
|
-- +goose StatementEnd
|