Suspense
808883cf8f
Add mixed PostgreSQL and Meilisearch episode search with access-safe hydration, incremental indexing, and regression coverage for explicit all-media searches.\n\nPart of #396
54 lines
1.6 KiB
SQL
54 lines
1.6 KiB
SQL
-- +goose NO TRANSACTION
|
|
|
|
-- +goose Up
|
|
-- Search evaluates these exact expressions in the episode candidate branch.
|
|
-- Build concurrently so large episode catalogs remain readable and writable.
|
|
-- Remove INVALID remnants first: IF NOT EXISTS otherwise accepts a failed
|
|
-- concurrent build and Goose would record a search index that cannot be used.
|
|
-- +goose StatementBegin
|
|
DO $$
|
|
DECLARE
|
|
index_name text;
|
|
BEGIN
|
|
FOREACH index_name IN ARRAY ARRAY[
|
|
'idx_episodes_search_title',
|
|
'idx_episodes_search_overview'
|
|
] LOOP
|
|
IF EXISTS (
|
|
SELECT 1
|
|
FROM pg_class c
|
|
JOIN pg_namespace n ON n.oid = c.relnamespace
|
|
JOIN pg_index i ON i.indexrelid = c.oid
|
|
WHERE n.nspname = 'public'
|
|
AND c.relname = index_name
|
|
AND NOT i.indisvalid
|
|
) THEN
|
|
EXECUTE format('DROP INDEX public.%I', index_name);
|
|
END IF;
|
|
END LOOP;
|
|
END;
|
|
$$;
|
|
-- +goose StatementEnd
|
|
|
|
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_episodes_search_title
|
|
ON public.episodes USING gin (
|
|
setweight(
|
|
to_tsvector(
|
|
'simple',
|
|
public.normalize_search_text(
|
|
COALESCE(NULLIF(BTRIM(title), ''), 'Episode ' || episode_number::text)
|
|
)
|
|
),
|
|
'A'
|
|
)
|
|
);
|
|
|
|
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_episodes_search_overview
|
|
ON public.episodes USING gin (
|
|
to_tsvector('english', COALESCE(overview, ''))
|
|
);
|
|
|
|
-- +goose Down
|
|
DROP INDEX CONCURRENTLY IF EXISTS public.idx_episodes_search_overview;
|
|
DROP INDEX CONCURRENTLY IF EXISTS public.idx_episodes_search_title;
|