62 lines
3.3 KiB
SQL
62 lines
3.3 KiB
SQL
-- Artist photos (auto-fetched or manually overridden) reuse the
|
|
-- existing cover_art table rather than a parallel one, so photo
|
|
-- resizing/caching/serving (internal/coverart, /cover-art/{id}) works
|
|
-- unchanged for artists. That requires adding an artist_id column and
|
|
-- widening the "must belong to something" CHECK, which SQLite can only
|
|
-- do by rebuilding the table.
|
|
--
|
|
-- albums.cover_art_id and podcast_channels.cover_art_id are declared
|
|
-- "ON DELETE SET NULL" against cover_art(id), and SQLite's DROP TABLE
|
|
-- performs an implicit DELETE FROM first when foreign_keys=ON — which
|
|
-- would fire that SET NULL and silently wipe every album's/channel's
|
|
-- chosen cover on upgrade. Back those columns up and restore them
|
|
-- after the rebuild.
|
|
CREATE TEMP TABLE _cover_art_migration_backup_albums AS
|
|
SELECT id, cover_art_id FROM albums WHERE cover_art_id IS NOT NULL;
|
|
CREATE TEMP TABLE _cover_art_migration_backup_podcasts AS
|
|
SELECT id, cover_art_id FROM podcast_channels WHERE cover_art_id IS NOT NULL;
|
|
|
|
CREATE TABLE cover_art_new (
|
|
id TEXT PRIMARY KEY,
|
|
album_id TEXT REFERENCES albums(id) ON DELETE CASCADE,
|
|
media_file_id TEXT REFERENCES media_files(id) ON DELETE CASCADE,
|
|
artist_id TEXT REFERENCES artists(id) ON DELETE CASCADE,
|
|
source TEXT NOT NULL CHECK (source IN ('embedded','folder','external')),
|
|
source_path TEXT NOT NULL DEFAULT '',
|
|
mime_type TEXT NOT NULL DEFAULT '',
|
|
content_hash TEXT NOT NULL DEFAULT '',
|
|
width INTEGER NOT NULL DEFAULT 0,
|
|
height INTEGER NOT NULL DEFAULT 0,
|
|
updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
|
|
CHECK ((album_id IS NOT NULL) OR (media_file_id IS NOT NULL) OR (artist_id IS NOT NULL))
|
|
);
|
|
|
|
INSERT INTO cover_art_new (id, album_id, media_file_id, artist_id, source, source_path, mime_type, content_hash, width, height, updated_at)
|
|
SELECT id, album_id, media_file_id, NULL, source, source_path, mime_type, content_hash, width, height, updated_at FROM cover_art;
|
|
|
|
DROP TABLE cover_art;
|
|
ALTER TABLE cover_art_new RENAME TO cover_art;
|
|
|
|
CREATE INDEX idx_cover_art_album ON cover_art(album_id);
|
|
CREATE INDEX idx_cover_art_media_file ON cover_art(media_file_id);
|
|
CREATE INDEX idx_cover_art_artist ON cover_art(artist_id);
|
|
|
|
UPDATE albums SET cover_art_id = (
|
|
SELECT cover_art_id FROM _cover_art_migration_backup_albums WHERE _cover_art_migration_backup_albums.id = albums.id
|
|
) WHERE id IN (SELECT id FROM _cover_art_migration_backup_albums);
|
|
UPDATE podcast_channels SET cover_art_id = (
|
|
SELECT cover_art_id FROM _cover_art_migration_backup_podcasts WHERE _cover_art_migration_backup_podcasts.id = podcast_channels.id
|
|
) WHERE id IN (SELECT id FROM _cover_art_migration_backup_podcasts);
|
|
|
|
DROP TABLE _cover_art_migration_backup_albums;
|
|
DROP TABLE _cover_art_migration_backup_podcasts;
|
|
|
|
-- Artist bio + photo (auto-fetched or admin-overridden).
|
|
ALTER TABLE artists ADD COLUMN bio TEXT NOT NULL DEFAULT '';
|
|
ALTER TABLE artists ADD COLUMN cover_art_id TEXT REFERENCES cover_art(id);
|
|
ALTER TABLE artists ADD COLUMN photo_manual INTEGER NOT NULL DEFAULT 0;
|
|
|
|
-- Lets an admin edit a song's title/track/disc/composer/conductor
|
|
-- without a later library rescan silently overwriting the edit.
|
|
ALTER TABLE songs ADD COLUMN metadata_locked INTEGER NOT NULL DEFAULT 0;
|