724 lines
29 KiB
PL/PgSQL
724 lines
29 KiB
PL/PgSQL
-- +goose Up
|
|
-- +goose StatementBegin
|
|
CREATE TABLE IF NOT EXISTS public.episode_catalog_entries (
|
|
media_folder_id integer NOT NULL,
|
|
episode_id text NOT NULL,
|
|
series_id text NOT NULL,
|
|
sort_key text NOT NULL,
|
|
title text NOT NULL,
|
|
added_at timestamp with time zone NOT NULL,
|
|
episode_air_date date,
|
|
year integer NOT NULL,
|
|
genres text[] NOT NULL DEFAULT '{}',
|
|
studios text[] NOT NULL DEFAULT '{}',
|
|
networks text[] NOT NULL DEFAULT '{}',
|
|
countries text[] NOT NULL DEFAULT '{}',
|
|
original_language text NOT NULL DEFAULT '',
|
|
content_rating text NOT NULL DEFAULT '',
|
|
content_rating_label text NOT NULL DEFAULT '~~~~',
|
|
content_rating_rank integer NOT NULL DEFAULT 2147483647,
|
|
status text NOT NULL DEFAULT 'matched',
|
|
runtime integer NOT NULL DEFAULT 0,
|
|
rating_imdb double precision,
|
|
rating_tmdb double precision,
|
|
max_resolution_rank integer,
|
|
resolution_codes text[] NOT NULL DEFAULT '{}',
|
|
max_bitrate integer,
|
|
min_bitrate integer,
|
|
has_hdr boolean NOT NULL DEFAULT false,
|
|
has_non_hdr boolean NOT NULL DEFAULT false,
|
|
has_dolby_vision boolean NOT NULL DEFAULT false,
|
|
has_non_dolby_vision boolean NOT NULL DEFAULT false,
|
|
audio_language_codes text[] NOT NULL DEFAULT '{}',
|
|
subtitle_language_codes text[] NOT NULL DEFAULT '{}',
|
|
episode_created_at timestamp with time zone NOT NULL,
|
|
updated_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT episode_catalog_entries_pkey PRIMARY KEY (media_folder_id, episode_id),
|
|
CONSTRAINT episode_catalog_entries_episode_id_fkey FOREIGN KEY (episode_id) REFERENCES public.episodes(content_id) ON DELETE CASCADE,
|
|
CONSTRAINT episode_catalog_entries_media_folder_id_fkey FOREIGN KEY (media_folder_id) REFERENCES public.media_folders(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TEMP TABLE episode_catalog_entries_migration_clock AS
|
|
SELECT transaction_timestamp() AS started_at;
|
|
|
|
CREATE OR REPLACE FUNCTION public.episode_catalog_rating_rank(rating text)
|
|
RETURNS integer
|
|
LANGUAGE sql
|
|
IMMUTABLE
|
|
AS $$
|
|
SELECT CASE UPPER(NULLIF(BTRIM(rating), ''))
|
|
WHEN 'G' THEN 0
|
|
WHEN 'TV-Y' THEN 0
|
|
WHEN 'TV-G' THEN 0
|
|
WHEN 'PG' THEN 1
|
|
WHEN 'TV-Y7' THEN 1
|
|
WHEN 'TV-PG' THEN 1
|
|
WHEN 'PG-13' THEN 2
|
|
WHEN 'TV-14' THEN 2
|
|
WHEN 'R' THEN 3
|
|
WHEN 'NC-17' THEN 3
|
|
WHEN 'TV-MA' THEN 3
|
|
ELSE 2147483647
|
|
END
|
|
$$;
|
|
|
|
CREATE OR REPLACE FUNCTION public.episode_catalog_normalized_resolution(resolution text)
|
|
RETURNS text
|
|
LANGUAGE sql
|
|
IMMUTABLE
|
|
AS $$
|
|
SELECT CASE LOWER(NULLIF(BTRIM(resolution), ''))
|
|
WHEN '4k' THEN '2160p'
|
|
WHEN 'uhd' THEN '2160p'
|
|
ELSE LOWER(NULLIF(BTRIM(resolution), ''))
|
|
END
|
|
$$;
|
|
|
|
CREATE OR REPLACE FUNCTION public.episode_catalog_resolution_rank(resolution text)
|
|
RETURNS integer
|
|
LANGUAGE sql
|
|
IMMUTABLE
|
|
AS $$
|
|
SELECT CASE UPPER(public.episode_catalog_normalized_resolution(resolution))
|
|
WHEN '480P' THEN 1
|
|
WHEN '720P' THEN 2
|
|
WHEN '1080P' THEN 3
|
|
WHEN '2160P' THEN 4
|
|
WHEN '4320P' THEN 5
|
|
ELSE NULL
|
|
END
|
|
$$;
|
|
|
|
CREATE OR REPLACE FUNCTION public.refresh_episode_catalog_entry(p_episode_id text, p_media_folder_id integer)
|
|
RETURNS void
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
BEGIN
|
|
IF p_episode_id IS NULL OR p_media_folder_id IS NULL THEN
|
|
RETURN;
|
|
END IF;
|
|
|
|
INSERT INTO public.episode_catalog_entries (
|
|
media_folder_id,
|
|
episode_id,
|
|
series_id,
|
|
sort_key,
|
|
title,
|
|
added_at,
|
|
episode_air_date,
|
|
year,
|
|
genres,
|
|
studios,
|
|
networks,
|
|
countries,
|
|
original_language,
|
|
content_rating,
|
|
content_rating_label,
|
|
content_rating_rank,
|
|
status,
|
|
runtime,
|
|
rating_imdb,
|
|
rating_tmdb,
|
|
max_resolution_rank,
|
|
resolution_codes,
|
|
max_bitrate,
|
|
min_bitrate,
|
|
has_hdr,
|
|
has_non_hdr,
|
|
has_dolby_vision,
|
|
has_non_dolby_vision,
|
|
audio_language_codes,
|
|
subtitle_language_codes,
|
|
episode_created_at,
|
|
updated_at
|
|
)
|
|
SELECT
|
|
el.media_folder_id,
|
|
e.content_id,
|
|
e.series_id,
|
|
LOWER(COALESCE(NULLIF(BTRIM(e.title), ''), 'Episode ' || e.episode_number::text)) AS sort_key,
|
|
COALESCE(NULLIF(BTRIM(e.title), ''), 'Episode ' || e.episode_number::text) AS title,
|
|
el.first_seen_at,
|
|
e.air_date,
|
|
COALESCE(si.year, EXTRACT(YEAR FROM e.air_date)::integer, 0) AS year,
|
|
COALESCE(si.genres, '{}'::text[]) AS genres,
|
|
COALESCE(si.studios, '{}'::text[]) AS studios,
|
|
COALESCE(si.networks, '{}'::text[]) AS networks,
|
|
COALESCE(si.countries, '{}'::text[]) AS countries,
|
|
COALESCE(si.original_language, '') AS original_language,
|
|
COALESCE(si.content_rating, '') AS content_rating,
|
|
LOWER(COALESCE(NULLIF(BTRIM(si.content_rating), ''), '~~~~')) AS content_rating_label,
|
|
public.episode_catalog_rating_rank(si.content_rating) AS content_rating_rank,
|
|
COALESCE(NULLIF(BTRIM(si.status), ''), 'matched') AS status,
|
|
COALESCE(NULLIF(e.runtime, 0), COALESCE(si.runtime, 0)) AS runtime,
|
|
e.rating_imdb,
|
|
e.rating_tmdb,
|
|
stats.max_resolution_rank,
|
|
COALESCE(stats.resolution_codes, '{}'::text[]) AS resolution_codes,
|
|
stats.max_bitrate,
|
|
stats.min_bitrate,
|
|
COALESCE(stats.has_hdr, false) AS has_hdr,
|
|
COALESCE(stats.has_non_hdr, false) AS has_non_hdr,
|
|
COALESCE(stats.has_dolby_vision, false) AS has_dolby_vision,
|
|
COALESCE(stats.has_non_dolby_vision, false) AS has_non_dolby_vision,
|
|
COALESCE(stats.audio_language_codes, '{}'::text[]) AS audio_language_codes,
|
|
COALESCE(stats.subtitle_language_codes, '{}'::text[]) AS subtitle_language_codes,
|
|
e.created_at,
|
|
NOW()
|
|
FROM public.episode_libraries el
|
|
JOIN public.episodes e ON e.content_id = el.episode_id
|
|
JOIN public.media_items si ON si.content_id = e.series_id
|
|
LEFT JOIN LATERAL (
|
|
SELECT
|
|
MAX(public.episode_catalog_resolution_rank(mf.resolution)) AS max_resolution_rank,
|
|
ARRAY(
|
|
SELECT DISTINCT code
|
|
FROM public.media_files mf_res
|
|
CROSS JOIN LATERAL (
|
|
SELECT public.episode_catalog_normalized_resolution(mf_res.resolution) AS code
|
|
) normalized
|
|
WHERE mf_res.episode_id = e.content_id
|
|
AND mf_res.media_folder_id = el.media_folder_id
|
|
AND mf_res.missing_since IS NULL
|
|
AND normalized.code IS NOT NULL
|
|
ORDER BY code
|
|
) AS resolution_codes,
|
|
MAX(mf.bitrate) FILTER (WHERE mf.bitrate IS NOT NULL AND mf.bitrate > 0) AS max_bitrate,
|
|
MIN(mf.bitrate) FILTER (WHERE mf.bitrate IS NOT NULL AND mf.bitrate > 0) AS min_bitrate,
|
|
BOOL_OR(mf.hdr IS TRUE) AS has_hdr,
|
|
BOOL_OR(mf.hdr IS FALSE) AS has_non_hdr,
|
|
BOOL_OR(EXISTS (
|
|
SELECT 1
|
|
FROM jsonb_array_elements(COALESCE(mf.video_tracks, '[]'::jsonb)) AS vt
|
|
WHERE NULLIF(BTRIM(vt->>'dolby_vision'), '') IS NOT NULL
|
|
)) AS has_dolby_vision,
|
|
BOOL_OR(NOT EXISTS (
|
|
SELECT 1
|
|
FROM jsonb_array_elements(COALESCE(mf.video_tracks, '[]'::jsonb)) AS vt
|
|
WHERE NULLIF(BTRIM(vt->>'dolby_vision'), '') IS NOT NULL
|
|
)) AS has_non_dolby_vision,
|
|
ARRAY(
|
|
SELECT DISTINCT LOWER(NULLIF(BTRIM(lang), ''))
|
|
FROM public.media_files mf_audio
|
|
CROSS JOIN LATERAL UNNEST(COALESCE(mf_audio.audio_language_codes, '{}'::text[])) AS lang
|
|
WHERE mf_audio.episode_id = e.content_id
|
|
AND mf_audio.media_folder_id = el.media_folder_id
|
|
AND mf_audio.missing_since IS NULL
|
|
AND NULLIF(BTRIM(lang), '') IS NOT NULL
|
|
ORDER BY LOWER(NULLIF(BTRIM(lang), ''))
|
|
) AS audio_language_codes,
|
|
ARRAY(
|
|
SELECT DISTINCT lang_code
|
|
FROM (
|
|
SELECT LOWER(NULLIF(BTRIM(lang), '')) AS lang_code
|
|
FROM public.media_files mf_sub
|
|
CROSS JOIN LATERAL UNNEST(COALESCE(mf_sub.subtitle_language_codes, '{}'::text[])) AS lang
|
|
WHERE mf_sub.episode_id = e.content_id
|
|
AND mf_sub.media_folder_id = el.media_folder_id
|
|
AND mf_sub.missing_since IS NULL
|
|
UNION
|
|
SELECT LOWER(NULLIF(BTRIM(track->>'language'), '')) AS lang_code
|
|
FROM public.media_files mf_ext
|
|
CROSS JOIN LATERAL jsonb_array_elements(COALESCE(mf_ext.external_subtitles, '[]'::jsonb)) AS track
|
|
WHERE mf_ext.episode_id = e.content_id
|
|
AND mf_ext.media_folder_id = el.media_folder_id
|
|
AND mf_ext.missing_since IS NULL
|
|
) subtitle_codes
|
|
WHERE lang_code IS NOT NULL
|
|
ORDER BY lang_code
|
|
) AS subtitle_language_codes
|
|
FROM public.media_files mf
|
|
WHERE mf.episode_id = e.content_id
|
|
AND mf.media_folder_id = el.media_folder_id
|
|
AND mf.missing_since IS NULL
|
|
) stats ON TRUE
|
|
WHERE el.episode_id = p_episode_id
|
|
AND el.media_folder_id = p_media_folder_id
|
|
ON CONFLICT (media_folder_id, episode_id) DO UPDATE SET
|
|
series_id = EXCLUDED.series_id,
|
|
sort_key = EXCLUDED.sort_key,
|
|
title = EXCLUDED.title,
|
|
added_at = EXCLUDED.added_at,
|
|
episode_air_date = EXCLUDED.episode_air_date,
|
|
year = EXCLUDED.year,
|
|
genres = EXCLUDED.genres,
|
|
studios = EXCLUDED.studios,
|
|
networks = EXCLUDED.networks,
|
|
countries = EXCLUDED.countries,
|
|
original_language = EXCLUDED.original_language,
|
|
content_rating = EXCLUDED.content_rating,
|
|
content_rating_label = EXCLUDED.content_rating_label,
|
|
content_rating_rank = EXCLUDED.content_rating_rank,
|
|
status = EXCLUDED.status,
|
|
runtime = EXCLUDED.runtime,
|
|
rating_imdb = EXCLUDED.rating_imdb,
|
|
rating_tmdb = EXCLUDED.rating_tmdb,
|
|
max_resolution_rank = EXCLUDED.max_resolution_rank,
|
|
resolution_codes = EXCLUDED.resolution_codes,
|
|
max_bitrate = EXCLUDED.max_bitrate,
|
|
min_bitrate = EXCLUDED.min_bitrate,
|
|
has_hdr = EXCLUDED.has_hdr,
|
|
has_non_hdr = EXCLUDED.has_non_hdr,
|
|
has_dolby_vision = EXCLUDED.has_dolby_vision,
|
|
has_non_dolby_vision = EXCLUDED.has_non_dolby_vision,
|
|
audio_language_codes = EXCLUDED.audio_language_codes,
|
|
subtitle_language_codes = EXCLUDED.subtitle_language_codes,
|
|
episode_created_at = EXCLUDED.episode_created_at,
|
|
updated_at = NOW();
|
|
|
|
IF NOT FOUND THEN
|
|
DELETE FROM public.episode_catalog_entries
|
|
WHERE episode_id = p_episode_id
|
|
AND media_folder_id = p_media_folder_id;
|
|
END IF;
|
|
END;
|
|
$$;
|
|
|
|
CREATE OR REPLACE FUNCTION public.refresh_episode_catalog_entries_for_episode(p_episode_id text)
|
|
RETURNS void
|
|
LANGUAGE sql
|
|
AS $$
|
|
SELECT public.refresh_episode_catalog_entry(el.episode_id, el.media_folder_id)
|
|
FROM public.episode_libraries el
|
|
WHERE el.episode_id = p_episode_id;
|
|
$$;
|
|
|
|
CREATE OR REPLACE FUNCTION public.refresh_episode_catalog_entries_for_series(p_series_id text)
|
|
RETURNS void
|
|
LANGUAGE sql
|
|
AS $$
|
|
SELECT public.refresh_episode_catalog_entry(el.episode_id, el.media_folder_id)
|
|
FROM public.episode_libraries el
|
|
JOIN public.episodes e ON e.content_id = el.episode_id
|
|
WHERE e.series_id = p_series_id;
|
|
$$;
|
|
|
|
CREATE OR REPLACE FUNCTION public.episode_catalog_entries_episode_libraries_trigger()
|
|
RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
BEGIN
|
|
IF TG_OP = 'DELETE' THEN
|
|
DELETE FROM public.episode_catalog_entries
|
|
WHERE episode_id = OLD.episode_id
|
|
AND media_folder_id = OLD.media_folder_id;
|
|
RETURN OLD;
|
|
END IF;
|
|
|
|
PERFORM public.refresh_episode_catalog_entry(NEW.episode_id, NEW.media_folder_id);
|
|
IF TG_OP = 'UPDATE'
|
|
AND (OLD.episode_id IS DISTINCT FROM NEW.episode_id OR OLD.media_folder_id IS DISTINCT FROM NEW.media_folder_id) THEN
|
|
PERFORM public.refresh_episode_catalog_entry(OLD.episode_id, OLD.media_folder_id);
|
|
END IF;
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
|
|
CREATE OR REPLACE FUNCTION public.episode_catalog_entries_media_files_trigger()
|
|
RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
BEGIN
|
|
IF TG_OP = 'DELETE' THEN
|
|
PERFORM public.refresh_episode_catalog_entry(OLD.episode_id, OLD.media_folder_id);
|
|
RETURN OLD;
|
|
END IF;
|
|
|
|
IF TG_OP = 'UPDATE' THEN
|
|
PERFORM public.refresh_episode_catalog_entry(OLD.episode_id, OLD.media_folder_id);
|
|
END IF;
|
|
|
|
PERFORM public.refresh_episode_catalog_entry(NEW.episode_id, NEW.media_folder_id);
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
|
|
CREATE OR REPLACE FUNCTION public.episode_catalog_entries_episodes_trigger()
|
|
RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
BEGIN
|
|
IF TG_OP = 'DELETE' THEN
|
|
DELETE FROM public.episode_catalog_entries
|
|
WHERE episode_id = OLD.content_id;
|
|
RETURN OLD;
|
|
END IF;
|
|
|
|
PERFORM public.refresh_episode_catalog_entries_for_episode(NEW.content_id);
|
|
IF TG_OP = 'UPDATE' AND OLD.content_id IS DISTINCT FROM NEW.content_id THEN
|
|
DELETE FROM public.episode_catalog_entries
|
|
WHERE episode_id = OLD.content_id;
|
|
END IF;
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
|
|
CREATE OR REPLACE FUNCTION public.episode_catalog_entries_series_trigger()
|
|
RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
BEGIN
|
|
IF TG_OP = 'DELETE' THEN
|
|
DELETE FROM public.episode_catalog_entries
|
|
WHERE series_id = OLD.content_id;
|
|
RETURN OLD;
|
|
END IF;
|
|
|
|
IF COALESCE(NEW.type, '') = 'series' THEN
|
|
PERFORM public.refresh_episode_catalog_entries_for_series(NEW.content_id);
|
|
END IF;
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
|
|
WITH active_episode_files AS (
|
|
SELECT
|
|
mf.episode_id,
|
|
mf.media_folder_id,
|
|
public.episode_catalog_normalized_resolution(mf.resolution) AS normalized_resolution,
|
|
public.episode_catalog_resolution_rank(mf.resolution) AS resolution_rank,
|
|
mf.bitrate,
|
|
mf.hdr,
|
|
EXISTS (
|
|
SELECT 1
|
|
FROM jsonb_array_elements(COALESCE(mf.video_tracks, '[]'::jsonb)) AS vt
|
|
WHERE NULLIF(BTRIM(vt->>'dolby_vision'), '') IS NOT NULL
|
|
) AS has_dolby_vision,
|
|
mf.audio_language_codes,
|
|
mf.subtitle_language_codes,
|
|
mf.external_subtitles
|
|
FROM public.media_files mf
|
|
WHERE mf.episode_id IS NOT NULL
|
|
AND mf.missing_since IS NULL
|
|
),
|
|
file_stats AS (
|
|
SELECT
|
|
episode_id,
|
|
media_folder_id,
|
|
MAX(resolution_rank) AS max_resolution_rank,
|
|
ARRAY_AGG(DISTINCT normalized_resolution ORDER BY normalized_resolution)
|
|
FILTER (WHERE normalized_resolution IS NOT NULL) AS resolution_codes,
|
|
MAX(bitrate) FILTER (WHERE bitrate IS NOT NULL AND bitrate > 0) AS max_bitrate,
|
|
MIN(bitrate) FILTER (WHERE bitrate IS NOT NULL AND bitrate > 0) AS min_bitrate,
|
|
BOOL_OR(hdr IS TRUE) AS has_hdr,
|
|
BOOL_OR(hdr IS FALSE) AS has_non_hdr,
|
|
BOOL_OR(has_dolby_vision) AS has_dolby_vision,
|
|
BOOL_OR(NOT has_dolby_vision) AS has_non_dolby_vision
|
|
FROM active_episode_files
|
|
GROUP BY episode_id, media_folder_id
|
|
),
|
|
audio_stats AS (
|
|
SELECT
|
|
episode_id,
|
|
media_folder_id,
|
|
ARRAY_AGG(DISTINCT lang_code ORDER BY lang_code) AS audio_language_codes
|
|
FROM (
|
|
SELECT
|
|
aef.episode_id,
|
|
aef.media_folder_id,
|
|
LOWER(NULLIF(BTRIM(lang), '')) AS lang_code
|
|
FROM active_episode_files aef
|
|
CROSS JOIN LATERAL UNNEST(COALESCE(aef.audio_language_codes, '{}'::text[])) AS lang
|
|
) codes
|
|
WHERE lang_code IS NOT NULL
|
|
GROUP BY episode_id, media_folder_id
|
|
),
|
|
subtitle_stats AS (
|
|
SELECT
|
|
episode_id,
|
|
media_folder_id,
|
|
ARRAY_AGG(DISTINCT lang_code ORDER BY lang_code) AS subtitle_language_codes
|
|
FROM (
|
|
SELECT
|
|
aef.episode_id,
|
|
aef.media_folder_id,
|
|
LOWER(NULLIF(BTRIM(lang), '')) AS lang_code
|
|
FROM active_episode_files aef
|
|
CROSS JOIN LATERAL UNNEST(COALESCE(aef.subtitle_language_codes, '{}'::text[])) AS lang
|
|
UNION
|
|
SELECT
|
|
aef.episode_id,
|
|
aef.media_folder_id,
|
|
LOWER(NULLIF(BTRIM(track->>'language'), '')) AS lang_code
|
|
FROM active_episode_files aef
|
|
CROSS JOIN LATERAL jsonb_array_elements(COALESCE(aef.external_subtitles, '[]'::jsonb)) AS track
|
|
) codes
|
|
WHERE lang_code IS NOT NULL
|
|
GROUP BY episode_id, media_folder_id
|
|
)
|
|
INSERT INTO public.episode_catalog_entries (
|
|
media_folder_id,
|
|
episode_id,
|
|
series_id,
|
|
sort_key,
|
|
title,
|
|
added_at,
|
|
episode_air_date,
|
|
year,
|
|
genres,
|
|
studios,
|
|
networks,
|
|
countries,
|
|
original_language,
|
|
content_rating,
|
|
content_rating_label,
|
|
content_rating_rank,
|
|
status,
|
|
runtime,
|
|
rating_imdb,
|
|
rating_tmdb,
|
|
max_resolution_rank,
|
|
resolution_codes,
|
|
max_bitrate,
|
|
min_bitrate,
|
|
has_hdr,
|
|
has_non_hdr,
|
|
has_dolby_vision,
|
|
has_non_dolby_vision,
|
|
audio_language_codes,
|
|
subtitle_language_codes,
|
|
episode_created_at,
|
|
updated_at
|
|
)
|
|
SELECT
|
|
el.media_folder_id,
|
|
e.content_id,
|
|
e.series_id,
|
|
LOWER(COALESCE(NULLIF(BTRIM(e.title), ''), 'Episode ' || e.episode_number::text)) AS sort_key,
|
|
COALESCE(NULLIF(BTRIM(e.title), ''), 'Episode ' || e.episode_number::text) AS title,
|
|
el.first_seen_at,
|
|
e.air_date,
|
|
COALESCE(si.year, EXTRACT(YEAR FROM e.air_date)::integer, 0) AS year,
|
|
COALESCE(si.genres, '{}'::text[]) AS genres,
|
|
COALESCE(si.studios, '{}'::text[]) AS studios,
|
|
COALESCE(si.networks, '{}'::text[]) AS networks,
|
|
COALESCE(si.countries, '{}'::text[]) AS countries,
|
|
COALESCE(si.original_language, '') AS original_language,
|
|
COALESCE(si.content_rating, '') AS content_rating,
|
|
LOWER(COALESCE(NULLIF(BTRIM(si.content_rating), ''), '~~~~')) AS content_rating_label,
|
|
public.episode_catalog_rating_rank(si.content_rating) AS content_rating_rank,
|
|
COALESCE(NULLIF(BTRIM(si.status), ''), 'matched') AS status,
|
|
COALESCE(NULLIF(e.runtime, 0), COALESCE(si.runtime, 0)) AS runtime,
|
|
e.rating_imdb,
|
|
e.rating_tmdb,
|
|
file_stats.max_resolution_rank,
|
|
COALESCE(file_stats.resolution_codes, '{}'::text[]) AS resolution_codes,
|
|
file_stats.max_bitrate,
|
|
file_stats.min_bitrate,
|
|
COALESCE(file_stats.has_hdr, false) AS has_hdr,
|
|
COALESCE(file_stats.has_non_hdr, false) AS has_non_hdr,
|
|
COALESCE(file_stats.has_dolby_vision, false) AS has_dolby_vision,
|
|
COALESCE(file_stats.has_non_dolby_vision, false) AS has_non_dolby_vision,
|
|
COALESCE(audio_stats.audio_language_codes, '{}'::text[]) AS audio_language_codes,
|
|
COALESCE(subtitle_stats.subtitle_language_codes, '{}'::text[]) AS subtitle_language_codes,
|
|
e.created_at,
|
|
NOW()
|
|
FROM public.episode_libraries el
|
|
JOIN public.episodes e ON e.content_id = el.episode_id
|
|
JOIN public.media_items si ON si.content_id = e.series_id
|
|
LEFT JOIN file_stats ON file_stats.episode_id = el.episode_id AND file_stats.media_folder_id = el.media_folder_id
|
|
LEFT JOIN audio_stats ON audio_stats.episode_id = el.episode_id AND audio_stats.media_folder_id = el.media_folder_id
|
|
LEFT JOIN subtitle_stats ON subtitle_stats.episode_id = el.episode_id AND subtitle_stats.media_folder_id = el.media_folder_id
|
|
ON CONFLICT (media_folder_id, episode_id) DO NOTHING;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_episode_catalog_entries_title
|
|
ON public.episode_catalog_entries USING btree (media_folder_id, sort_key, episode_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_episode_catalog_entries_added
|
|
ON public.episode_catalog_entries USING btree (media_folder_id, added_at DESC, sort_key, episode_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_episode_catalog_entries_air_date
|
|
ON public.episode_catalog_entries USING btree (media_folder_id, episode_air_date DESC NULLS LAST, sort_key, episode_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_episode_catalog_entries_year
|
|
ON public.episode_catalog_entries USING btree (media_folder_id, year DESC, sort_key, episode_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_episode_catalog_entries_content_rating
|
|
ON public.episode_catalog_entries USING btree (media_folder_id, content_rating_rank, content_rating_label, sort_key, episode_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_episode_catalog_entries_runtime
|
|
ON public.episode_catalog_entries USING btree (media_folder_id, runtime DESC NULLS LAST, sort_key, episode_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_episode_catalog_entries_imdb
|
|
ON public.episode_catalog_entries USING btree (media_folder_id, rating_imdb DESC NULLS LAST, sort_key, episode_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_episode_catalog_entries_tmdb
|
|
ON public.episode_catalog_entries USING btree (media_folder_id, rating_tmdb DESC NULLS LAST, sort_key, episode_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_episode_catalog_entries_resolution
|
|
ON public.episode_catalog_entries USING btree (media_folder_id, max_resolution_rank DESC NULLS LAST, sort_key, episode_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_episode_catalog_entries_bitrate
|
|
ON public.episode_catalog_entries USING btree (media_folder_id, max_bitrate DESC NULLS LAST, sort_key, episode_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_episode_catalog_entries_status
|
|
ON public.episode_catalog_entries USING btree (media_folder_id, status, sort_key, episode_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_episode_catalog_entries_hdr
|
|
ON public.episode_catalog_entries USING btree (media_folder_id, sort_key, episode_id)
|
|
WHERE has_hdr;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_episode_catalog_entries_non_hdr
|
|
ON public.episode_catalog_entries USING btree (media_folder_id, sort_key, episode_id)
|
|
WHERE has_non_hdr;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_episode_catalog_entries_dolby_vision
|
|
ON public.episode_catalog_entries USING btree (media_folder_id, sort_key, episode_id)
|
|
WHERE has_dolby_vision;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_episode_catalog_entries_non_dolby_vision
|
|
ON public.episode_catalog_entries USING btree (media_folder_id, sort_key, episode_id)
|
|
WHERE has_non_dolby_vision;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_episode_catalog_entries_genres_gin
|
|
ON public.episode_catalog_entries USING gin (genres);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_episode_catalog_entries_studios_gin
|
|
ON public.episode_catalog_entries USING gin (studios);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_episode_catalog_entries_networks_gin
|
|
ON public.episode_catalog_entries USING gin (networks);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_episode_catalog_entries_countries_gin
|
|
ON public.episode_catalog_entries USING gin (countries);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_episode_catalog_entries_resolution_codes_gin
|
|
ON public.episode_catalog_entries USING gin (resolution_codes);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_episode_catalog_entries_audio_gin
|
|
ON public.episode_catalog_entries USING gin (audio_language_codes);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_episode_catalog_entries_subtitle_gin
|
|
ON public.episode_catalog_entries USING gin (subtitle_language_codes);
|
|
|
|
DROP TRIGGER IF EXISTS trg_episode_catalog_entries_episode_libraries ON public.episode_libraries;
|
|
CREATE TRIGGER trg_episode_catalog_entries_episode_libraries
|
|
AFTER INSERT OR UPDATE OR DELETE ON public.episode_libraries
|
|
FOR EACH ROW EXECUTE FUNCTION public.episode_catalog_entries_episode_libraries_trigger();
|
|
|
|
DROP TRIGGER IF EXISTS trg_episode_catalog_entries_media_files ON public.media_files;
|
|
DROP TRIGGER IF EXISTS trg_episode_catalog_entries_media_files_insert_delete ON public.media_files;
|
|
DROP TRIGGER IF EXISTS trg_episode_catalog_entries_media_files_update ON public.media_files;
|
|
CREATE TRIGGER trg_episode_catalog_entries_media_files_insert_delete
|
|
AFTER INSERT OR DELETE ON public.media_files
|
|
FOR EACH ROW EXECUTE FUNCTION public.episode_catalog_entries_media_files_trigger();
|
|
|
|
CREATE TRIGGER trg_episode_catalog_entries_media_files_update
|
|
AFTER UPDATE ON public.media_files
|
|
FOR EACH ROW
|
|
WHEN (
|
|
OLD.episode_id IS DISTINCT FROM NEW.episode_id OR
|
|
OLD.media_folder_id IS DISTINCT FROM NEW.media_folder_id OR
|
|
OLD.missing_since IS DISTINCT FROM NEW.missing_since OR
|
|
OLD.resolution IS DISTINCT FROM NEW.resolution OR
|
|
OLD.bitrate IS DISTINCT FROM NEW.bitrate OR
|
|
OLD.hdr IS DISTINCT FROM NEW.hdr OR
|
|
OLD.video_tracks IS DISTINCT FROM NEW.video_tracks OR
|
|
OLD.external_subtitles IS DISTINCT FROM NEW.external_subtitles OR
|
|
OLD.audio_language_codes IS DISTINCT FROM NEW.audio_language_codes OR
|
|
OLD.subtitle_language_codes IS DISTINCT FROM NEW.subtitle_language_codes
|
|
)
|
|
EXECUTE FUNCTION public.episode_catalog_entries_media_files_trigger();
|
|
|
|
DROP TRIGGER IF EXISTS trg_episode_catalog_entries_episodes ON public.episodes;
|
|
CREATE TRIGGER trg_episode_catalog_entries_episodes
|
|
AFTER INSERT OR UPDATE OF content_id, series_id, title, episode_number, air_date, runtime, rating_imdb, rating_tmdb, still_path, still_thumbhash, created_at OR DELETE
|
|
ON public.episodes
|
|
FOR EACH ROW EXECUTE FUNCTION public.episode_catalog_entries_episodes_trigger();
|
|
|
|
DROP TRIGGER IF EXISTS trg_episode_catalog_entries_series ON public.media_items;
|
|
CREATE TRIGGER trg_episode_catalog_entries_series
|
|
AFTER UPDATE OF content_id, type, year, genres, studios, networks, countries, original_language, content_rating, status, runtime ON public.media_items
|
|
FOR EACH ROW
|
|
WHEN (OLD.type = 'series' OR NEW.type = 'series')
|
|
EXECUTE FUNCTION public.episode_catalog_entries_series_trigger();
|
|
|
|
WITH migration_clock AS (
|
|
SELECT started_at FROM episode_catalog_entries_migration_clock LIMIT 1
|
|
),
|
|
changed_entries AS (
|
|
SELECT DISTINCT mf.episode_id, mf.media_folder_id
|
|
FROM public.media_files mf, migration_clock mc
|
|
WHERE mf.episode_id IS NOT NULL
|
|
AND mf.updated_at >= mc.started_at
|
|
UNION
|
|
SELECT DISTINCT el.episode_id, el.media_folder_id
|
|
FROM public.episode_libraries el, migration_clock mc
|
|
WHERE el.first_seen_at >= mc.started_at
|
|
UNION
|
|
SELECT DISTINCT el.episode_id, el.media_folder_id
|
|
FROM public.episode_libraries el
|
|
JOIN public.episodes e ON e.content_id = el.episode_id
|
|
CROSS JOIN migration_clock mc
|
|
WHERE e.updated_at >= mc.started_at
|
|
UNION
|
|
SELECT DISTINCT el.episode_id, el.media_folder_id
|
|
FROM public.episode_libraries el
|
|
JOIN public.episodes e ON e.content_id = el.episode_id
|
|
JOIN public.media_items si ON si.content_id = e.series_id
|
|
CROSS JOIN migration_clock mc
|
|
WHERE si.updated_at >= mc.started_at
|
|
AND si.type = 'series'
|
|
UNION
|
|
SELECT DISTINCT ece.episode_id, ece.media_folder_id
|
|
FROM public.episode_catalog_entries ece
|
|
WHERE NOT EXISTS (
|
|
SELECT 1
|
|
FROM public.episode_libraries el
|
|
WHERE el.episode_id = ece.episode_id
|
|
AND el.media_folder_id = ece.media_folder_id
|
|
)
|
|
)
|
|
SELECT COUNT(*)
|
|
FROM (
|
|
SELECT public.refresh_episode_catalog_entry(episode_id, media_folder_id)
|
|
FROM changed_entries
|
|
) refreshed;
|
|
-- +goose StatementEnd
|
|
|
|
-- +goose Down
|
|
-- +goose StatementBegin
|
|
DROP TRIGGER IF EXISTS trg_episode_catalog_entries_series ON public.media_items;
|
|
DROP TRIGGER IF EXISTS trg_episode_catalog_entries_episodes ON public.episodes;
|
|
DROP TRIGGER IF EXISTS trg_episode_catalog_entries_media_files_update ON public.media_files;
|
|
DROP TRIGGER IF EXISTS trg_episode_catalog_entries_media_files_insert_delete ON public.media_files;
|
|
DROP TRIGGER IF EXISTS trg_episode_catalog_entries_media_files ON public.media_files;
|
|
DROP TRIGGER IF EXISTS trg_episode_catalog_entries_episode_libraries ON public.episode_libraries;
|
|
|
|
DROP FUNCTION IF EXISTS public.episode_catalog_entries_series_trigger();
|
|
DROP FUNCTION IF EXISTS public.episode_catalog_entries_episodes_trigger();
|
|
DROP FUNCTION IF EXISTS public.episode_catalog_entries_media_files_trigger();
|
|
DROP FUNCTION IF EXISTS public.episode_catalog_entries_episode_libraries_trigger();
|
|
DROP FUNCTION IF EXISTS public.refresh_episode_catalog_entries_for_series(text);
|
|
DROP FUNCTION IF EXISTS public.refresh_episode_catalog_entries_for_episode(text);
|
|
DROP FUNCTION IF EXISTS public.refresh_episode_catalog_entry(text, integer);
|
|
DROP FUNCTION IF EXISTS public.episode_catalog_resolution_rank(text);
|
|
DROP FUNCTION IF EXISTS public.episode_catalog_normalized_resolution(text);
|
|
DROP FUNCTION IF EXISTS public.episode_catalog_rating_rank(text);
|
|
|
|
DROP INDEX IF EXISTS public.idx_episode_catalog_entries_subtitle_gin;
|
|
DROP INDEX IF EXISTS public.idx_episode_catalog_entries_audio_gin;
|
|
DROP INDEX IF EXISTS public.idx_episode_catalog_entries_resolution_codes_gin;
|
|
DROP INDEX IF EXISTS public.idx_episode_catalog_entries_countries_gin;
|
|
DROP INDEX IF EXISTS public.idx_episode_catalog_entries_networks_gin;
|
|
DROP INDEX IF EXISTS public.idx_episode_catalog_entries_studios_gin;
|
|
DROP INDEX IF EXISTS public.idx_episode_catalog_entries_genres_gin;
|
|
DROP INDEX IF EXISTS public.idx_episode_catalog_entries_non_dolby_vision;
|
|
DROP INDEX IF EXISTS public.idx_episode_catalog_entries_dolby_vision;
|
|
DROP INDEX IF EXISTS public.idx_episode_catalog_entries_non_hdr;
|
|
DROP INDEX IF EXISTS public.idx_episode_catalog_entries_hdr;
|
|
DROP INDEX IF EXISTS public.idx_episode_catalog_entries_status;
|
|
DROP INDEX IF EXISTS public.idx_episode_catalog_entries_bitrate;
|
|
DROP INDEX IF EXISTS public.idx_episode_catalog_entries_resolution;
|
|
DROP INDEX IF EXISTS public.idx_episode_catalog_entries_tmdb;
|
|
DROP INDEX IF EXISTS public.idx_episode_catalog_entries_imdb;
|
|
DROP INDEX IF EXISTS public.idx_episode_catalog_entries_runtime;
|
|
DROP INDEX IF EXISTS public.idx_episode_catalog_entries_content_rating;
|
|
DROP INDEX IF EXISTS public.idx_episode_catalog_entries_year;
|
|
DROP INDEX IF EXISTS public.idx_episode_catalog_entries_air_date;
|
|
DROP INDEX IF EXISTS public.idx_episode_catalog_entries_added;
|
|
DROP INDEX IF EXISTS public.idx_episode_catalog_entries_title;
|
|
|
|
DROP TABLE IF EXISTS public.episode_catalog_entries;
|
|
-- +goose StatementEnd
|