bvandeusen
a86897b35e
Adds the schema for the pluggable cover-art provider abstraction: - Extends albums.cover_art_source CHECK to accept 'theaudiodb' (the constraint from migration 0016 only allowed embedded/sidecar/mbcaa/ none, which would block writes from the new provider). - Adds artist_thumb_path / artist_fanart_path / artist_art_source on artists, with a parallel CHECK constraint and source index. - Adds *_sources_version stamps on both albums and artists for the per-row recheck eligibility logic. Default 0 so every existing row becomes stale relative to the seeded current_version=1, retrying through the new chain on first scan after migrate. - New cover_art_provider_settings table (per-provider enabled / api_key / display_order) and cover_art_sources_meta singleton holding current_version. Seeds both v1 providers (mbcaa + theaudiodb) as enabled. - New artist_art_enrich jsonb column on scan_runs for the 4th scan-orchestrator stage tally. The seed leaves theaudiodb.api_key NULL; the application supplies the upstream's documented test key (2) as the default when the column is NULL, per the no-coercive-settings principle.
63 lines
2.6 KiB
SQL
63 lines
2.6 KiB
SQL
-- M7 cover-sources: schema for additional cover-art providers (TheAudioDB
|
|
-- as the v1 implementation), artist art (thumb + fanart), and the
|
|
-- recheck-on-source-change trigger.
|
|
|
|
-- (0) Extend the existing albums.cover_art_source CHECK to accept the
|
|
-- new 'theaudiodb' value. The constraint was created in migration 0016
|
|
-- with the IN list ('embedded','sidecar','mbcaa','none'); without this
|
|
-- extension, the enricher's UPDATE setting cover_art_source='theaudiodb'
|
|
-- would fail with a CHECK violation.
|
|
ALTER TABLE albums DROP CONSTRAINT IF EXISTS albums_cover_art_source_check;
|
|
ALTER TABLE albums
|
|
ADD CONSTRAINT albums_cover_art_source_check
|
|
CHECK (cover_art_source IS NULL
|
|
OR cover_art_source IN ('embedded','sidecar','mbcaa','theaudiodb','none'));
|
|
|
|
-- (1) Artist art columns parallel albums.cover_art_*.
|
|
ALTER TABLE artists
|
|
ADD COLUMN artist_thumb_path text,
|
|
ADD COLUMN artist_fanart_path text,
|
|
ADD COLUMN artist_art_source text;
|
|
|
|
ALTER TABLE artists
|
|
ADD CONSTRAINT artists_artist_art_source_check
|
|
CHECK (artist_art_source IS NULL
|
|
OR artist_art_source IN ('theaudiodb','none'));
|
|
|
|
CREATE INDEX artists_artist_art_source_idx ON artists (artist_art_source);
|
|
|
|
-- (2) Recheck-trigger version stamps. Default 0; current_version starts
|
|
-- at 1, so every existing row is "stale" relative to current — first
|
|
-- scan after migrate retries every settled row through the new chain.
|
|
ALTER TABLE albums
|
|
ADD COLUMN cover_art_sources_version int NOT NULL DEFAULT 0;
|
|
ALTER TABLE artists
|
|
ADD COLUMN artist_art_sources_version int NOT NULL DEFAULT 0;
|
|
|
|
-- (3) Per-provider settings + singleton meta row.
|
|
CREATE TABLE cover_art_provider_settings (
|
|
provider_id text PRIMARY KEY,
|
|
enabled boolean NOT NULL DEFAULT true,
|
|
api_key text,
|
|
display_order int NOT NULL DEFAULT 0,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE cover_art_sources_meta (
|
|
id boolean PRIMARY KEY DEFAULT true,
|
|
current_version int NOT NULL DEFAULT 1,
|
|
CONSTRAINT cover_art_sources_meta_singleton CHECK (id = true)
|
|
);
|
|
INSERT INTO cover_art_sources_meta (id, current_version) VALUES (true, 1);
|
|
|
|
-- Seed both v1 providers as enabled. TheAudioDB's api_key stays NULL;
|
|
-- the application supplies the test key '2' when the column is NULL,
|
|
-- per the no-coercive-settings principle.
|
|
INSERT INTO cover_art_provider_settings (provider_id, enabled, display_order) VALUES
|
|
('mbcaa', true, 0),
|
|
('theaudiodb', true, 1);
|
|
|
|
-- (4) Scan-runs gains a stage tally column for artist art enrichment.
|
|
ALTER TABLE scan_runs ADD COLUMN artist_art_enrich jsonb;
|