From 7ba5d09b509459b476bff9d56802a54825597f6c Mon Sep 17 00:00:00 2001 From: Marcel van der Veldt Date: Mon, 12 Aug 2024 02:16:18 +0200 Subject: [PATCH] Cleanup database from wrong matched info (#1556) --- .../server/controllers/media/base.py | 15 ++-- music_assistant/server/controllers/music.py | 78 ++++++++++++------- music_assistant/server/helpers/database.py | 2 +- 3 files changed, 59 insertions(+), 36 deletions(-) diff --git a/music_assistant/server/controllers/media/base.py b/music_assistant/server/controllers/media/base.py index 1dca9f02..ccde9921 100644 --- a/music_assistant/server/controllers/media/base.py +++ b/music_assistant/server/controllers/media/base.py @@ -743,16 +743,13 @@ class MediaControllerBase(Generic[ItemCls], metaclass=ABCMeta): if overwrite: # on overwrite, clear the provider_mappings table first # this is done for filesystem provider changing the path (and thus item_id) - for provider_mapping in provider_mappings: - await self.mass.music.database.delete( - DB_TABLE_PROVIDER_MAPPINGS, - { - "media_type": self.media_type.value, - "item_id": db_id, - "provider_instance": provider_mapping.provider_instance, - }, - ) + await self.mass.music.database.delete( + DB_TABLE_PROVIDER_MAPPINGS, + {"media_type": self.media_type.value, "item_id": db_id}, + ) for provider_mapping in provider_mappings: + if not provider_mapping.provider_instance: + continue await self.mass.music.database.insert_or_replace( DB_TABLE_PROVIDER_MAPPINGS, { diff --git a/music_assistant/server/controllers/music.py b/music_assistant/server/controllers/music.py index 4db16ee7..3ef48e05 100644 --- a/music_assistant/server/controllers/music.py +++ b/music_assistant/server/controllers/music.py @@ -66,7 +66,7 @@ DEFAULT_SYNC_INTERVAL = 3 * 60 # default sync interval in minutes CONF_SYNC_INTERVAL = "sync_interval" CONF_DELETED_PROVIDERS = "deleted_providers" CONF_ADD_LIBRARY_ON_PLAY = "add_library_on_play" -DB_SCHEMA_VERSION: Final[int] = 3 +DB_SCHEMA_VERSION: Final[int] = 4 class MusicController(CoreController): @@ -953,8 +953,32 @@ class MusicController(CoreController): self.logger.info( "Migrating database from version %s to %s", prev_version, DB_SCHEMA_VERSION ) - if prev_version == 2: - # migrate from version 2 to 3 + + if prev_version < 2: + # unhandled schema version + # we do not try to handle more complex migrations + self.logger.warning( + "Database schema too old - Resetting library/database - " + "a full rescan will be performed, this can take a while!" + ) + for table in ( + DB_TABLE_TRACKS, + DB_TABLE_ALBUMS, + DB_TABLE_ARTISTS, + DB_TABLE_PLAYLISTS, + DB_TABLE_RADIOS, + DB_TABLE_ALBUM_TRACKS, + DB_TABLE_PLAYLOG, + DB_TABLE_TRACK_LOUDNESS, + DB_TABLE_PROVIDER_MAPPINGS, + ): + await self.database.execute(f"DROP TABLE IF EXISTS {table}") + await self.database.commit() + # recreate missing tables + await self.__create_database_tables() + return + + if prev_version < 3: # convert musicbrainz external id's await self.database.execute( f"UPDATE {DB_TABLE_ARTISTS} SET external_ids = " @@ -969,31 +993,33 @@ class MusicController(CoreController): f"UPDATE {DB_TABLE_TRACKS} SET external_ids = " "replace(external_ids, 'musicbrainz', 'musicbrainz_recordingid')" ) - await self.database.commit() - return - # all other versions: reset the database - # we only migrate from prev version to current, we do not try to handle - # more complex migrations - self.logger.warning( - "Database schema too old - Resetting library/database - " - "a full rescan will be performed, this can take a while!" - ) - for table in ( - DB_TABLE_TRACKS, - DB_TABLE_ALBUMS, - DB_TABLE_ARTISTS, - DB_TABLE_PLAYLISTS, - DB_TABLE_RADIOS, - DB_TABLE_ALBUM_TRACKS, - DB_TABLE_PLAYLOG, - DB_TABLE_TRACK_LOUDNESS, - DB_TABLE_PROVIDER_MAPPINGS, - ): - await self.database.execute(f"DROP TABLE IF EXISTS {table}") + if prev_version < 4: + # remove all additional track provider mappings to cleanup the mess caused + # by a bug that mapped the wrong track artists. + async for track in self.tracks.iter_library_items(): + if len(track.provider_mappings) <= 2: + continue + # get the primary provider mapping from the db table + # as that is sorted on insertion order + primary_mapping = await self.database.get_row( + DB_TABLE_PROVIDER_MAPPINGS, {"item_id": track.item_id, "media_type": "track"} + ) + if not primary_mapping: + continue + # remove all other mappings except the primary + track.provider_mappings = { + x + for x in track.provider_mappings + if x.provider_instance == primary_mapping["provider_instance"] + and x.item_id == primary_mapping["provider_item_id"] + } + # reset the metadata timestamp to force a full metadata refresh later + track.metadata.last_refresh = None + await self.tracks.update_item_in_library(track.item_id, track, True) + + # save changes await self.database.commit() - # recreate missing tables - await self.__create_database_tables() async def __create_database_tables(self) -> None: """Create database tables.""" diff --git a/music_assistant/server/helpers/database.py b/music_assistant/server/helpers/database.py index 12375061..3c598506 100644 --- a/music_assistant/server/helpers/database.py +++ b/music_assistant/server/helpers/database.py @@ -207,7 +207,7 @@ class DatabaseConnection: sql_query = f"DELETE FROM {table} " if match: sql_query += " WHERE " + " AND ".join(f"{x} = :{x}" for x in match) - elif query and "query" not in query.lower(): + elif query and "where" not in query.lower(): sql_query += "WHERE " + query elif query: sql_query += query -- 2.34.1