From 8682decf67232dcc684cf058dfda67f18f479624 Mon Sep 17 00:00:00 2001 From: Marcel van der Veldt Date: Thu, 20 Feb 2025 01:26:44 +0100 Subject: [PATCH] Fix: natural sort order and searching --- music_assistant/controllers/media/albums.py | 26 ++++--- music_assistant/controllers/media/artists.py | 14 ++-- .../controllers/media/audiobooks.py | 13 +++- music_assistant/controllers/media/base.py | 20 +++-- .../controllers/media/playlists.py | 13 +++- music_assistant/controllers/media/podcasts.py | 13 +++- music_assistant/controllers/media/radio.py | 14 ++-- music_assistant/controllers/media/tracks.py | 24 ++++-- music_assistant/controllers/music.py | 76 +++++++++++++++---- music_assistant/helpers/compare.py | 9 +++ 10 files changed, 161 insertions(+), 61 deletions(-) diff --git a/music_assistant/controllers/media/albums.py b/music_assistant/controllers/media/albums.py index 9ad20ca7..37c9e5f6 100644 --- a/music_assistant/controllers/media/albums.py +++ b/music_assistant/controllers/media/albums.py @@ -16,6 +16,7 @@ from music_assistant.helpers.compare import ( compare_album, compare_artists, compare_media_item, + create_safe_string, loose_compare_strings, ) from music_assistant.helpers.json import serialize_to_json @@ -123,15 +124,17 @@ class AlbumsController(MediaControllerBase[Album]): # handle combined artist + title search artist_str, title_str = search.split(" - ", 1) search = None - extra_query_parts.append("albums.name LIKE :search_title") + title_str = create_safe_string(title_str, True, True) + artist_str = create_safe_string(artist_str, True, True) + extra_query_parts.append("albums.search_name LIKE :search_title") extra_query_params["search_title"] = f"%{title_str}%" # use join with artists table to filter on artist name extra_join_parts.append( "JOIN album_artists ON album_artists.album_id = albums.item_id " "JOIN artists ON artists.item_id = album_artists.artist_id " - "AND artists.name LIKE :search_artist" + "AND artists.search_name LIKE :search_artist" if not artist_table_joined - else "AND artists.name LIKE :search_artist" + else "AND artists.search_name LIKE :search_artist" ) artist_table_joined = True extra_query_params["search_artist"] = f"%{artist_str}%" @@ -148,12 +151,13 @@ class AlbumsController(MediaControllerBase[Album]): ) if search and len(result) < 25 and not offset: # append artist items to result + search = create_safe_string(search, True, True) extra_join_parts.append( "JOIN album_artists ON album_artists.album_id = albums.item_id " "JOIN artists ON artists.item_id = album_artists.artist_id " - "AND artists.name LIKE :search_artist" + "AND artists.search_name LIKE :search_artist" if not artist_table_joined - else "AND artists.name LIKE :search_artist" + else "AND artists.search_name LIKE :search_artist" ) extra_query_params["search_artist"] = f"%{search}%" return result + await self._get_library_items_by_query( @@ -304,6 +308,8 @@ class AlbumsController(MediaControllerBase[Album]): "year": item.year, "metadata": serialize_to_json(item.metadata), "external_ids": serialize_to_json(item.external_ids), + "search_name": create_safe_string(item.name, True, True), + "search_sort_name": create_safe_string(item.sort_name, True, True), }, ) # update/set provider_mappings table @@ -330,14 +336,14 @@ class AlbumsController(MediaControllerBase[Album]): if overwrite else {*cur_item.provider_mappings, *update.provider_mappings} ) + name = update.name if overwrite else cur_item.name + sort_name = update.sort_name if overwrite else cur_item.sort_name or update.sort_name await self.mass.music.database.update( self.db_table, {"item_id": db_id}, { - "name": update.name if overwrite else cur_item.name, - "sort_name": update.sort_name - if overwrite - else cur_item.sort_name or update.sort_name, + "name": name, + "sort_name": sort_name, "version": update.version if overwrite else cur_item.version or update.version, "year": update.year if overwrite else cur_item.year or update.year, "album_type": album_type.value, @@ -345,6 +351,8 @@ class AlbumsController(MediaControllerBase[Album]): "external_ids": serialize_to_json( update.external_ids if overwrite else cur_item.external_ids ), + "search_name": create_safe_string(name, True, True), + "search_sort_name": create_safe_string(sort_name, True, True), }, ) # update/set provider_mappings table diff --git a/music_assistant/controllers/media/artists.py b/music_assistant/controllers/media/artists.py index d3f224ce..576c227a 100644 --- a/music_assistant/controllers/media/artists.py +++ b/music_assistant/controllers/media/artists.py @@ -22,7 +22,7 @@ from music_assistant.constants import ( VARIOUS_ARTISTS_NAME, ) from music_assistant.controllers.media.base import MediaControllerBase -from music_assistant.helpers.compare import compare_artist, compare_strings +from music_assistant.helpers.compare import compare_artist, compare_strings, create_safe_string from music_assistant.helpers.json import serialize_to_json if TYPE_CHECKING: @@ -352,6 +352,8 @@ class ArtistsController(MediaControllerBase[Artist]): "favorite": item.favorite, "external_ids": serialize_to_json(item.external_ids), "metadata": serialize_to_json(item.metadata), + "search_name": create_safe_string(item.name, True, True), + "search_sort_name": create_safe_string(item.sort_name, True, True), }, ) # update/set provider_mappings table @@ -381,18 +383,20 @@ class ArtistsController(MediaControllerBase[Artist]): if update.mbid == VARIOUS_ARTISTS_MBID: update.name = VARIOUS_ARTISTS_NAME + name = update.name if overwrite else cur_item.name + sort_name = update.sort_name if overwrite else cur_item.sort_name or update.sort_name await self.mass.music.database.update( self.db_table, {"item_id": db_id}, { - "name": update.name if overwrite else cur_item.name, - "sort_name": update.sort_name - if overwrite - else cur_item.sort_name or update.sort_name, + "name": name, + "sort_name": sort_name, "external_ids": serialize_to_json( update.external_ids if overwrite else cur_item.external_ids ), "metadata": serialize_to_json(metadata), + "search_name": create_safe_string(name, True, True), + "search_sort_name": create_safe_string(sort_name, True, True), }, ) self.logger.debug("updated %s in database: %s", update.name, db_id) diff --git a/music_assistant/controllers/media/audiobooks.py b/music_assistant/controllers/media/audiobooks.py index af4c699e..5663db23 100644 --- a/music_assistant/controllers/media/audiobooks.py +++ b/music_assistant/controllers/media/audiobooks.py @@ -13,6 +13,7 @@ from music_assistant.controllers.media.base import MediaControllerBase from music_assistant.helpers.compare import ( compare_audiobook, compare_media_item, + create_safe_string, loose_compare_strings, ) from music_assistant.helpers.datetime import utc_timestamp @@ -140,6 +141,8 @@ class AudiobooksController(MediaControllerBase[Audiobook]): "authors": serialize_to_json(item.authors), "narrators": serialize_to_json(item.narrators), "duration": item.duration, + "search_name": create_safe_string(item.name, True, True), + "search_sort_name": create_safe_string(item.sort_name, True, True), }, ) # update/set provider_mappings table @@ -161,14 +164,14 @@ class AudiobooksController(MediaControllerBase[Audiobook]): if overwrite else {*cur_item.provider_mappings, *update.provider_mappings} ) + name = update.name if overwrite else cur_item.name + sort_name = update.sort_name if overwrite else cur_item.sort_name or update.sort_name await self.mass.music.database.update( self.db_table, {"item_id": db_id}, { - "name": update.name if overwrite else cur_item.name, - "sort_name": update.sort_name - if overwrite - else cur_item.sort_name or update.sort_name, + "name": name, + "sort_name": sort_name, "version": update.version if overwrite else cur_item.version or update.version, "metadata": serialize_to_json(metadata), "external_ids": serialize_to_json( @@ -182,6 +185,8 @@ class AudiobooksController(MediaControllerBase[Audiobook]): update.narrators if overwrite else cur_item.narrators or update.narrators ), "duration": update.duration or update.duration, + "search_name": create_safe_string(name, True, True), + "search_sort_name": create_safe_string(sort_name, True, True), }, ) # update/set provider_mappings table diff --git a/music_assistant/controllers/media/base.py b/music_assistant/controllers/media/base.py index 66a9885b..f6eb374d 100644 --- a/music_assistant/controllers/media/base.py +++ b/music_assistant/controllers/media/base.py @@ -27,7 +27,7 @@ from music_assistant_models.media_items import ( ) from music_assistant.constants import DB_TABLE_PLAYLOG, DB_TABLE_PROVIDER_MAPPINGS, MASS_LOGGER_NAME -from music_assistant.helpers.compare import compare_media_item +from music_assistant.helpers.compare import compare_media_item, create_safe_string from music_assistant.helpers.json import json_loads, serialize_to_json if TYPE_CHECKING: @@ -50,10 +50,13 @@ JSON_KEYS = ( ) SORT_KEYS = { - "name": "name COLLATE NOCASE ASC", - "name_desc": "name COLLATE NOCASE DESC", - "sort_name": "sort_name COLLATE NOCASE ASC", - "sort_name_desc": "sort_name COLLATE NOCASE DESC", + # sqlite has no builtin support for natural sorting + # so we have use an additional column for this + # this also improves searching and sorting performance + "name": "search_name ASC", + "name_desc": "search_name DESC", + "sort_name": "search_sort_name ASC", + "sort_name_desc": "search_sort_name DESC", "timestamp_added": "timestamp_added ASC", "timestamp_added_desc": "timestamp_added DESC", "timestamp_modified": "timestamp_modified ASC", @@ -66,8 +69,8 @@ SORT_KEYS = { "year_desc": "year DESC", "position": "position ASC", "position_desc": "position DESC", - "artist_name": "artists.name COLLATE NOCASE ASC", - "artist_name_desc": "artists.name COLLATE NOCASE DESC", + "artist_name": "artists.search_name ASC", + "artist_name_desc": "artists.search_name DESC", "random": "RANDOM()", "random_play_count": "RANDOM(), play_count ASC", } @@ -717,8 +720,9 @@ class MediaControllerBase(Generic[ItemCls], metaclass=ABCMeta): ) # handle search if search: + search = create_safe_string(search, True, True) query_params["search"] = f"%{search}%" - query_parts.append(f"{self.db_table}.name LIKE :search") + query_parts.append(f"{self.db_table}.search_name LIKE :search") # handle favorite filter if favorite is not None: query_parts.append(f"{self.db_table}.favorite = :favorite") diff --git a/music_assistant/controllers/media/playlists.py b/music_assistant/controllers/media/playlists.py index 365a83d7..fe2b0dc4 100644 --- a/music_assistant/controllers/media/playlists.py +++ b/music_assistant/controllers/media/playlists.py @@ -15,6 +15,7 @@ from music_assistant_models.errors import ( from music_assistant_models.media_items import Playlist, Track from music_assistant.constants import DB_TABLE_PLAYLISTS +from music_assistant.helpers.compare import create_safe_string from music_assistant.helpers.json import serialize_to_json from music_assistant.helpers.uri import create_uri, parse_uri from music_assistant.models.music_provider import MusicProvider @@ -291,6 +292,8 @@ class PlaylistController(MediaControllerBase[Playlist]): "metadata": serialize_to_json(item.metadata), "external_ids": serialize_to_json(item.external_ids), "cache_checksum": item.cache_checksum, + "search_name": create_safe_string(item.name, True, True), + "search_sort_name": create_safe_string(item.sort_name, True, True), }, ) # update/set provider_mappings table @@ -306,15 +309,15 @@ class PlaylistController(MediaControllerBase[Playlist]): cur_item = await self.get_library_item(db_id) metadata = update.metadata if overwrite else cur_item.metadata.update(update.metadata) cur_item.external_ids.update(update.external_ids) + name = update.name if overwrite else cur_item.name + sort_name = update.sort_name if overwrite else cur_item.sort_name or update.sort_name await self.mass.music.database.update( self.db_table, {"item_id": db_id}, { # always prefer name/owner from updated item here - "name": update.name, - "sort_name": update.sort_name - if (overwrite or update.name != cur_item.name) - else cur_item.sort_name, + "name": name, + "sort_name": sort_name, "owner": update.owner or cur_item.owner, "is_editable": update.is_editable, "metadata": serialize_to_json(metadata), @@ -322,6 +325,8 @@ class PlaylistController(MediaControllerBase[Playlist]): update.external_ids if overwrite else cur_item.external_ids ), "cache_checksum": update.cache_checksum or cur_item.cache_checksum, + "search_name": create_safe_string(name, True, True), + "search_sort_name": create_safe_string(sort_name, True, True), }, ) # update/set provider_mappings table diff --git a/music_assistant/controllers/media/podcasts.py b/music_assistant/controllers/media/podcasts.py index 314c4d0f..6a6fd529 100644 --- a/music_assistant/controllers/media/podcasts.py +++ b/music_assistant/controllers/media/podcasts.py @@ -14,6 +14,7 @@ from music_assistant.controllers.media.base import MediaControllerBase from music_assistant.helpers.compare import ( compare_media_item, compare_podcast, + create_safe_string, loose_compare_strings, ) from music_assistant.helpers.json import serialize_to_json @@ -168,6 +169,8 @@ class PodcastsController(MediaControllerBase[Podcast]): "external_ids": serialize_to_json(item.external_ids), "publisher": item.publisher, "total_episodes": item.total_episodes, + "search_name": create_safe_string(item.name, True, True), + "search_sort_name": create_safe_string(item.sort_name, True, True), }, ) # update/set provider_mappings table @@ -188,14 +191,14 @@ class PodcastsController(MediaControllerBase[Podcast]): if overwrite else {*cur_item.provider_mappings, *update.provider_mappings} ) + name = update.name if overwrite else cur_item.name + sort_name = update.sort_name if overwrite else cur_item.sort_name or update.sort_name await self.mass.music.database.update( self.db_table, {"item_id": db_id}, { - "name": update.name if overwrite else cur_item.name, - "sort_name": update.sort_name - if overwrite - else cur_item.sort_name or update.sort_name, + "name": name, + "sort_name": sort_name, "version": update.version if overwrite else cur_item.version or update.version, "metadata": serialize_to_json(metadata), "external_ids": serialize_to_json( @@ -203,6 +206,8 @@ class PodcastsController(MediaControllerBase[Podcast]): ), "publisher": cur_item.publisher or update.publisher, "total_episodes": cur_item.total_episodes or update.total_episodes, + "search_name": create_safe_string(name, True, True), + "search_sort_name": create_safe_string(sort_name, True, True), }, ) # update/set provider_mappings table diff --git a/music_assistant/controllers/media/radio.py b/music_assistant/controllers/media/radio.py index dbb13df3..163ea109 100644 --- a/music_assistant/controllers/media/radio.py +++ b/music_assistant/controllers/media/radio.py @@ -8,7 +8,7 @@ from music_assistant_models.enums import MediaType from music_assistant_models.media_items import Radio, Track from music_assistant.constants import DB_TABLE_RADIOS -from music_assistant.helpers.compare import loose_compare_strings +from music_assistant.helpers.compare import create_safe_string, loose_compare_strings from music_assistant.helpers.json import serialize_to_json from .base import MediaControllerBase @@ -64,6 +64,8 @@ class RadioController(MediaControllerBase[Radio]): "favorite": item.favorite, "metadata": serialize_to_json(item.metadata), "external_ids": serialize_to_json(item.external_ids), + "search_name": create_safe_string(item.name, True, True), + "search_sort_name": create_safe_string(item.sort_name, True, True), }, ) # update/set provider_mappings table @@ -80,19 +82,21 @@ class RadioController(MediaControllerBase[Radio]): metadata = update.metadata if overwrite else cur_item.metadata.update(update.metadata) cur_item.external_ids.update(update.external_ids) match = {"item_id": db_id} + name = update.name if overwrite else cur_item.name + sort_name = update.sort_name if overwrite else cur_item.sort_name or update.sort_name await self.mass.music.database.update( self.db_table, match, { # always prefer name from updated item here - "name": update.name if overwrite else cur_item.name, - "sort_name": update.sort_name - if overwrite - else cur_item.sort_name or update.sort_name, + "name": name, + "sort_name": sort_name, "metadata": serialize_to_json(metadata), "external_ids": serialize_to_json( update.external_ids if overwrite else cur_item.external_ids ), + "search_name": create_safe_string(name, True, True), + "search_sort_name": create_safe_string(sort_name, True, True), }, ) # update/set provider_mappings table diff --git a/music_assistant/controllers/media/tracks.py b/music_assistant/controllers/media/tracks.py index d67f252b..75bd9f19 100644 --- a/music_assistant/controllers/media/tracks.py +++ b/music_assistant/controllers/media/tracks.py @@ -33,6 +33,7 @@ from music_assistant.helpers.compare import ( compare_artists, compare_media_item, compare_track, + create_safe_string, loose_compare_strings, ) from music_assistant.helpers.json import serialize_to_json @@ -171,13 +172,15 @@ class TracksController(MediaControllerBase[Track]): # handle combined artist + title search artist_str, title_str = search.split(" - ", 1) search = None - extra_query_parts.append("tracks.name LIKE :search_title") + title_str = create_safe_string(title_str, True, True) + artist_str = create_safe_string(artist_str, True, True) + extra_query_parts.append("tracks.search_name LIKE :search_title") extra_query_params["search_title"] = f"%{title_str}%" # use join with artists table to filter on artist name extra_join_parts.append( "JOIN track_artists ON track_artists.track_id = tracks.item_id " "JOIN artists ON artists.item_id = track_artists.artist_id " - "AND artists.name LIKE :search_artist" + "AND artists.search_name LIKE :search_artist" ) extra_query_params["search_artist"] = f"%{artist_str}%" result = await self._get_library_items_by_query( @@ -193,12 +196,13 @@ class TracksController(MediaControllerBase[Track]): ) if search and len(result) < 25 and not offset: # append artist items to result + artist_search_str = create_safe_string(search, True, True) extra_join_parts.append( "JOIN track_artists ON track_artists.track_id = tracks.item_id " "JOIN artists ON artists.item_id = track_artists.artist_id " - "AND artists.name LIKE :search_artist" + "AND artists.search_name LIKE :search_artist" ) - extra_query_params["search_artist"] = f"%{search}%" + extra_query_params["search_artist"] = f"%{artist_search_str}%" return result + await self._get_library_items_by_query( favorite=favorite, search=None, @@ -442,6 +446,8 @@ class TracksController(MediaControllerBase[Track]): "favorite": item.favorite, "external_ids": serialize_to_json(item.external_ids), "metadata": serialize_to_json(item.metadata), + "search_name": create_safe_string(item.name, True, True), + "search_sort_name": create_safe_string(item.sort_name, True, True), }, ) # update/set provider_mappings table @@ -467,20 +473,22 @@ class TracksController(MediaControllerBase[Track]): cur_item = await self.get_library_item(db_id) metadata = update.metadata if overwrite else cur_item.metadata.update(update.metadata) cur_item.external_ids.update(update.external_ids) + name = update.name if overwrite else cur_item.name + sort_name = update.sort_name if overwrite else cur_item.sort_name or update.sort_name await self.mass.music.database.update( self.db_table, {"item_id": db_id}, { - "name": update.name if overwrite else cur_item.name, - "sort_name": update.sort_name - if overwrite - else cur_item.sort_name or update.sort_name, + "name": name, + "sort_name": sort_name, "version": update.version if overwrite else cur_item.version or update.version, "duration": update.duration if overwrite else cur_item.duration or update.duration, "metadata": serialize_to_json(metadata), "external_ids": serialize_to_json( update.external_ids if overwrite else cur_item.external_ids ), + "search_name": create_safe_string(name, True, True), + "search_sort_name": create_safe_string(sort_name, True, True), }, ) # update/set provider_mappings table diff --git a/music_assistant/controllers/music.py b/music_assistant/controllers/music.py index d8950aae..3a24f75c 100644 --- a/music_assistant/controllers/music.py +++ b/music_assistant/controllers/music.py @@ -54,6 +54,7 @@ from music_assistant.constants import ( PROVIDERS_WITH_SHAREABLE_URLS, ) from music_assistant.helpers.api import api_command +from music_assistant.helpers.compare import create_safe_string from music_assistant.helpers.database import DatabaseConnection from music_assistant.helpers.datetime import utc_timestamp from music_assistant.helpers.json import json_loads, serialize_to_json @@ -80,7 +81,7 @@ DEFAULT_SYNC_INTERVAL = 12 * 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] = 15 +DB_SCHEMA_VERSION: Final[int] = 16 class MusicController(CoreController): @@ -1328,6 +1329,40 @@ class MusicController(CoreController): await self.database.execute(f"DROP TABLE IF EXISTS {DB_TABLE_PLAYLOG}") await self.__create_database_tables() + if prev_version <= 15: + # add search_name and search_sort_name columns to all tables + # and populate them with the name and sort_name values + # this is to allow for local/case independent searches + for table in ( + DB_TABLE_TRACKS, + DB_TABLE_ALBUMS, + DB_TABLE_ARTISTS, + DB_TABLE_RADIOS, + DB_TABLE_PLAYLISTS, + DB_TABLE_AUDIOBOOKS, + DB_TABLE_PODCASTS, + ): + try: + await self.database.execute( + f"ALTER TABLE {table} ADD COLUMN search_name TEXT DEFAULT '' NOT NULL" + ) + await self.database.execute( + f"ALTER TABLE {table} ADD COLUMN search_sort_name TEXT DEFAULT '' NOT NULL" + ) + except Exception as err: + if "duplicate column" not in str(err): + raise + # migrate all existing values + async for db_row in self.database.iter_items(table): + await self.database.update( + table, + {"item_id": db_row["item_id"]}, + { + "search_name": create_safe_string(db_row["name"], True, True), + "search_sort_name": create_safe_string(db_row["sort_name"], True, True), + }, + ) + # save changes await self.database.commit() @@ -1379,7 +1414,9 @@ class MusicController(CoreController): [play_count] INTEGER DEFAULT 0, [last_played] INTEGER DEFAULT 0, [timestamp_added] INTEGER DEFAULT (cast(strftime('%s','now') as int)), - [timestamp_modified] INTEGER + [timestamp_modified] INTEGER, + [search_name] TEXT NOT NULL, + [search_sort_name] TEXT NOT NULL );""" ) await self.database.execute( @@ -1394,7 +1431,9 @@ class MusicController(CoreController): [play_count] INTEGER DEFAULT 0, [last_played] INTEGER DEFAULT 0, [timestamp_added] INTEGER DEFAULT (cast(strftime('%s','now') as int)), - [timestamp_modified] INTEGER + [timestamp_modified] INTEGER, + [search_name] TEXT NOT NULL, + [search_sort_name] TEXT NOT NULL );""" ) await self.database.execute( @@ -1411,7 +1450,9 @@ class MusicController(CoreController): [play_count] INTEGER DEFAULT 0, [last_played] INTEGER DEFAULT 0, [timestamp_added] INTEGER DEFAULT (cast(strftime('%s','now') as int)), - [timestamp_modified] INTEGER + [timestamp_modified] INTEGER, + [search_name] TEXT NOT NULL, + [search_sort_name] TEXT NOT NULL );""" ) await self.database.execute( @@ -1429,7 +1470,9 @@ class MusicController(CoreController): [play_count] INTEGER DEFAULT 0, [last_played] INTEGER DEFAULT 0, [timestamp_added] INTEGER DEFAULT (cast(strftime('%s','now') as int)), - [timestamp_modified] INTEGER + [timestamp_modified] INTEGER, + [search_name] TEXT NOT NULL, + [search_sort_name] TEXT NOT NULL );""" ) await self.database.execute( @@ -1444,7 +1487,9 @@ class MusicController(CoreController): [play_count] INTEGER DEFAULT 0, [last_played] INTEGER DEFAULT 0, [timestamp_added] INTEGER DEFAULT (cast(strftime('%s','now') as int)), - [timestamp_modified] INTEGER + [timestamp_modified] INTEGER, + [search_name] TEXT NOT NULL, + [search_sort_name] TEXT NOT NULL );""" ) await self.database.execute( @@ -1464,7 +1509,9 @@ class MusicController(CoreController): [play_count] INTEGER DEFAULT 0, [last_played] INTEGER DEFAULT 0, [timestamp_added] INTEGER DEFAULT (cast(strftime('%s','now') as int)), - [timestamp_modified] INTEGER + [timestamp_modified] INTEGER, + [search_name] TEXT NOT NULL, + [search_sort_name] TEXT NOT NULL );""" ) await self.database.execute( @@ -1482,7 +1529,9 @@ class MusicController(CoreController): [play_count] INTEGER DEFAULT 0, [last_played] INTEGER DEFAULT 0, [timestamp_added] INTEGER DEFAULT (cast(strftime('%s','now') as int)), - [timestamp_modified] INTEGER + [timestamp_modified] INTEGER, + [search_name] TEXT NOT NULL, + [search_sort_name] TEXT NOT NULL );""" ) await self.database.execute( @@ -1564,19 +1613,18 @@ class MusicController(CoreController): await self.database.execute( f"CREATE INDEX IF NOT EXISTS {db_table}_name_idx on {db_table}(name);" ) - # index on name (without case sensitivity) + # index on search_name (=lowercase name without diacritics) await self.database.execute( - f"CREATE INDEX IF NOT EXISTS {db_table}_name_nocase_idx " - f"ON {db_table}(name COLLATE NOCASE);" + f"CREATE INDEX IF NOT EXISTS {db_table}_name_nocase_idx ON {db_table}(search_name);" ) # index on sort_name await self.database.execute( f"CREATE INDEX IF NOT EXISTS {db_table}_sort_name_idx on {db_table}(sort_name);" ) - # index on sort_name (without case sensitivity) + # index on search_sort_name (=lowercase sort_name without diacritics) await self.database.execute( - f"CREATE INDEX IF NOT EXISTS {db_table}_sort_name_nocase_idx " - f"ON {db_table}(sort_name COLLATE NOCASE);" + f"CREATE INDEX IF NOT EXISTS {db_table}_search_sort_name_idx " + f"ON {db_table}(search_sort_name);" ) # index on external_ids await self.database.execute( diff --git a/music_assistant/helpers/compare.py b/music_assistant/helpers/compare.py index b3e6d0f9..2162aaed 100644 --- a/music_assistant/helpers/compare.py +++ b/music_assistant/helpers/compare.py @@ -495,6 +495,15 @@ def compare_external_ids( def create_safe_string(input_str: str, lowercase: bool = True, replace_space: bool = False) -> str: """Return clean lowered string for compare actions.""" + # handle some special cases + if input_str in ("P!nk", "p!nk"): + input_str = input_str.replace("!", "i") + if input_str in ("Wh♂", "wh♂"): + input_str = input_str.replace("♂", "o") + if input_str in ("KoЯn", "koЯn"): + input_str = input_str.replace("Я", "r") + if input_str == "$hort": + input_str = input_str.replace("$hort", "short") input_str = input_str.lower().strip() if lowercase else input_str.strip() unaccented_string = unidecode.unidecode(input_str) regex = r"[^a-zA-Z0-9]" if replace_space else r"[^a-zA-Z0-9 ]" -- 2.34.1