383973ec22
* feat(metadata): improve match accuracy and localized titles * fix(metadata): address matching review findings * test(catalog): align empty alias snapshot scope --------- Co-authored-by: Quick104 <31828688+Quick104@users.noreply.github.com>
39 lines
1.4 KiB
SQL
39 lines
1.4 KiB
SQL
-- +goose Up
|
|
-- An alias can describe one language while belonging to a snapshot requested
|
|
-- in another language. Track the request scope separately so replacing a
|
|
-- complete snapshot removes every alias that snapshot previously supplied.
|
|
-- NULL is reserved for rows that predate request-scope tracking; every new
|
|
-- writer supplies either a normalized language or '' for a provider-wide
|
|
-- snapshot.
|
|
ALTER TABLE media_item_aliases
|
|
ADD COLUMN snapshot_language text;
|
|
|
|
DROP INDEX idx_media_item_aliases_unique;
|
|
CREATE UNIQUE INDEX idx_media_item_aliases_unique
|
|
ON media_item_aliases (
|
|
content_id,
|
|
normalized_title,
|
|
language,
|
|
kind,
|
|
provider,
|
|
snapshot_language
|
|
);
|
|
|
|
-- +goose Down
|
|
-- Multiple request scopes may contain the same public alias. Keep one before
|
|
-- restoring the original uniqueness definition.
|
|
DELETE FROM media_item_aliases duplicate
|
|
USING media_item_aliases keeper
|
|
WHERE duplicate.id > keeper.id
|
|
AND duplicate.content_id = keeper.content_id
|
|
AND duplicate.normalized_title = keeper.normalized_title
|
|
AND duplicate.language = keeper.language
|
|
AND duplicate.kind = keeper.kind
|
|
AND duplicate.provider = keeper.provider;
|
|
|
|
DROP INDEX idx_media_item_aliases_unique;
|
|
ALTER TABLE media_item_aliases
|
|
DROP COLUMN snapshot_language;
|
|
CREATE UNIQUE INDEX idx_media_item_aliases_unique
|
|
ON media_item_aliases (content_id, normalized_title, language, kind, provider);
|