music-assistant-server

1"""
2Database setup logic for the MusicController.
3
4Handles initialization of the library database, schema creation
5(tables/indexes/triggers) and periodic maintenance. The (large) version-by-version
6migration logic lives in the sibling ``migrations`` module.
7
8This module provides the MusicDatabaseSetupMixin class which is inherited by
9MusicController to add database setup capabilities, keeping this code separated
10from the main controller logic.
11"""
12
13from __future__ import annotations
14
15import asyncio
16import os
17import shutil
18import sqlite3
19from typing import TYPE_CHECKING, Final
20
21from music_assistant_models.errors import MusicAssistantError
22
23from music_assistant.constants import (
24    DB_TABLE_ALBUM_ARTISTS,
25    DB_TABLE_ALBUM_TRACKS,
26    DB_TABLE_ALBUMS,
27    DB_TABLE_ARTISTS,
28    DB_TABLE_AUDIO_ANALYSIS,
29    DB_TABLE_AUDIO_ANALYSIS_FAILURES,
30    DB_TABLE_AUDIOBOOK_ARTISTS,
31    DB_TABLE_AUDIOBOOKS,
32    DB_TABLE_EXTERNAL_ID_LOOKUP,
33    DB_TABLE_GENRE_MEDIA_ITEM_EXCLUSION,
34    DB_TABLE_GENRE_MEDIA_ITEM_MAPPING,
35    DB_TABLE_GENRES,
36    DB_TABLE_PLAYLISTS,
37    DB_TABLE_PLAYLOG,
38    DB_TABLE_PODCASTS,
39    DB_TABLE_PROVIDER_MAPPINGS,
40    DB_TABLE_RADIOS,
41    DB_TABLE_SETTINGS,
42    DB_TABLE_TRACK_ARTISTS,
43    DB_TABLE_TRACKS,
44    MEDIA_ITEM_DB_TABLES,
45    VACUUM_MIN_RECLAIM_RATIO,
46)
47from music_assistant.controllers.music.constants import DB_SCHEMA_VERSION
48from music_assistant.controllers.music.media.genres import GenreController
49from music_assistant.controllers.music.migrations import migrate_database
50from music_assistant.controllers.tasks.context import update_current_task_progress_text
51from music_assistant.helpers.database import DatabaseConnection
52
53if TYPE_CHECKING:
54    import logging
55
56    from music_assistant_models.background_task import BackgroundTask
57    from music_assistant_models.enums import MediaType
58
59    from music_assistant import MusicAssistant
60    from music_assistant.controllers.music.media.albums import AlbumsController
61    from music_assistant.controllers.music.media.artists import ArtistsController
62    from music_assistant.controllers.music.media.playlists import PlaylistController
63    from music_assistant.controllers.music.media.radio import RadioController
64    from music_assistant.controllers.music.media.tracks import TracksController
65
66# the playlog's unique constraint: one row per item, per media type, per user
67PLAYLOG_CONFLICT_KEYS: Final[tuple[str, ...]] = ("item_id", "provider", "media_type", "userid")
68
69
70class MusicDatabaseSetupMixin:
71    """
72    Mixin class providing database setup and migration for the MusicController.
73
74    Handles initialization of the library database connection, creation of the
75    schema (tables, indexes and triggers), migration between schema versions and
76    periodic cleanup/maintenance.
77
78    This mixin expects to be mixed with a class that provides:
79    - mass: MusicAssistant instance
80    - logger: logging.Logger instance
81    - database: the active DatabaseConnection
82    - the per-media-type controllers (albums, artists, tracks, playlists, radio, genres)
83    - close() and start_sync() methods
84    """
85
86    # Type hints for attributes/methods provided by the class this mixin is used with
87    if TYPE_CHECKING:
88        mass: MusicAssistant
89        logger: logging.Logger
90        _database: DatabaseConnection | None
91        albums: AlbumsController
92        artists: ArtistsController
93        tracks: TracksController
94        playlists: PlaylistController
95        radio: RadioController
96        genres: GenreController
97
98        @property
99        def database(self) -> DatabaseConnection: ...  # noqa: D102
100
101        async def close(self) -> None: ...  # noqa: D102
102
103        async def start_sync(  # noqa: D102
104            self,
105            media_types: list[MediaType] | None = None,
106            providers: list[str] | None = None,
107        ) -> list[BackgroundTask]: ...
108
109    async def _cleanup_database(self) -> None:
110        """Perform database cleanup/maintenance."""
111        self.logger.debug("Performing database cleanup...")
112        update_current_task_progress_text("Cleaning old playlog entries")
113        # Remove playlog entries older than 90 days
114        await self.database.delete_where_query(
115            DB_TABLE_PLAYLOG, f"timestamp < strftime('%s','now') - {3600 * 24 * 90}"
116        )
117        # db tables cleanup
118        for ctrl in (
119            self.albums,
120            self.artists,
121            self.tracks,
122            self.playlists,
123            self.radio,
124        ):
125            update_current_task_progress_text(f"Cleaning {ctrl.media_type.value} library records")
126            # Provider mappings where the db item is removed
127            query = (
128                f"item_id not in (SELECT item_id from {ctrl.db_table}) "
129                f"AND media_type = '{ctrl.media_type}'"
130            )
131            await self.database.delete_where_query(DB_TABLE_PROVIDER_MAPPINGS, query)
132            # Orphaned db items
133            query = (
134                f"item_id not in (SELECT item_id from {DB_TABLE_PROVIDER_MAPPINGS} "
135                f"WHERE media_type = '{ctrl.media_type}')"
136            )
137            await self.database.delete_where_query(ctrl.db_table, query)
138            # External id lookup rows where the db item is removed
139            query = (
140                f"item_id not in (SELECT item_id from {ctrl.db_table}) "
141                f"AND media_type = '{ctrl.media_type}'"
142            )
143            await self.database.delete_where_query(DB_TABLE_EXTERNAL_ID_LOOKUP, query)
144            # Cleanup removed db items from the playlog
145            where_clause = (
146                f"media_type = '{ctrl.media_type}' AND provider = 'library' "
147                f"AND item_id not in (select item_id from {ctrl.db_table})"
148            )
149            await self.mass.music.database.delete_where_query(DB_TABLE_PLAYLOG, where_clause)
150        update_current_task_progress_text("Database cleanup finished")
151        self.logger.debug("Database cleanup done")
152
153    async def _setup_database(self) -> None:
154        """Initialize database."""
155        db_path = os.path.join(self.mass.storage_path, "library.db")
156        self._database = DatabaseConnection(db_path)
157        await self._database.setup()
158
159        # always create db tables if they don't exist to prevent errors trying to access them later
160        await self.__create_database_tables()
161        try:
162            if db_row := await self._database.get_row(DB_TABLE_SETTINGS, {"key": "version"}):
163                prev_version = int(db_row["value"])
164            else:
165                prev_version = 0
166        except KeyError, ValueError:
167            prev_version = 0
168
169        if prev_version not in (0, DB_SCHEMA_VERSION):
170            # db version mismatch - we need to do a migration
171            # make a backup of db file
172            db_path_backup = db_path + ".backup"
173            await asyncio.to_thread(shutil.copyfile, db_path, db_path_backup)
174
175            # handle db migration from previous schema(s) to this one
176            try:
177                await migrate_database(
178                    self.mass,
179                    self.database,
180                    self.logger,
181                    prev_version,
182                    self.__create_database_tables,
183                )
184            except Exception as err:
185                # if the migration fails completely we reset the db
186                # so the user at least can have a working situation back
187                # a backup file is made with the previous version
188                self.logger.error(
189                    "Database migration failed - starting with a fresh library database, "
190                    "a full rescan will be performed, this can take a while!",
191                )
192                if not isinstance(err, MusicAssistantError):
193                    self.logger.exception(err)
194
195                await self._database.close()
196                await asyncio.to_thread(os.remove, db_path)
197                self._database = DatabaseConnection(db_path)
198                await self._database.setup()
199                await self.mass.cache.clear()
200                await self.__create_database_tables()
201                prev_version = 0
202
203        # store current schema version
204        await self._database.insert_or_replace(
205            DB_TABLE_SETTINGS,
206            {"key": "version", "value": str(DB_SCHEMA_VERSION), "type": "str"},
207        )
208        # create indexes and triggers if needed
209        await self.__create_database_indexes()
210        await self.__create_database_triggers()
211        if prev_version == 0:
212            # fresh install - populate default genres
213            await self.genres.restore_default_genres()
214        # compact db - skip the full rebuild unless a meaningful share is reclaimable
215        try:
216            reclaimable_ratio = await self._database.get_reclaimable_ratio()
217            if reclaimable_ratio < VACUUM_MIN_RECLAIM_RATIO:
218                self.logger.debug(
219                    "Skipping database compaction (only %.1f%% reclaimable)",
220                    reclaimable_ratio * 100,
221                )
222            else:
223                self.logger.debug(
224                    "Compacting database (%.1f%% reclaimable)...", reclaimable_ratio * 100
225                )
226                await self._database.vacuum()
227                self.logger.debug("Compacting database done")
228        except Exception as err:
229            self.logger.warning("Database vacuum failed: %s", str(err))
230
231    async def _reset_database(self) -> None:
232        """Reset the database."""
233        await self.close()
234        db_path = os.path.join(self.mass.storage_path, "library.db")
235        await asyncio.to_thread(os.remove, db_path)
236        await self._setup_database()
237        # initiate full sync
238        await self.start_sync()
239
240    async def __create_database_tables(self) -> None:
241        """Create database tables."""
242        await self.database.execute(
243            f"""CREATE TABLE IF NOT EXISTS {DB_TABLE_SETTINGS}(
244                    [key] TEXT PRIMARY KEY,
245                    [value] TEXT,
246                    [type] TEXT
247                );"""
248        )
249        await self.database.execute(
250            f"""CREATE TABLE IF NOT EXISTS {DB_TABLE_PLAYLOG}(
251                [id] INTEGER PRIMARY KEY AUTOINCREMENT,
252                [item_id] TEXT NOT NULL,
253                [provider] TEXT NOT NULL,
254                [media_type] TEXT NOT NULL,
255                [name] TEXT NOT NULL,
256                [image] json,
257                [artists] json,
258                [timestamp] INTEGER DEFAULT 0,
259                [fully_played] BOOLEAN,
260                [seconds_played] INTEGER,
261                [userid] TEXT NOT NULL,
262                [queue_id] TEXT,
263                [user_initiated] BOOLEAN NOT NULL DEFAULT 1,
264                [playback_speed] REAL NOT NULL DEFAULT 1.0,
265                UNIQUE(item_id, provider, media_type, userid));"""
266        )
267        await self.database.execute(
268            f"""CREATE TABLE IF NOT EXISTS {DB_TABLE_ALBUMS}(
269                    [item_id] INTEGER PRIMARY KEY AUTOINCREMENT,
270                    [name] TEXT NOT NULL,
271                    [sort_name] TEXT NOT NULL,
272                    [version] TEXT,
273                    [album_type] TEXT NOT NULL,
274                    [year] INTEGER,
275                    [favorite] BOOLEAN NOT NULL DEFAULT 0,
276                    [metadata] json NOT NULL,
277                    [play_count] INTEGER NOT NULL DEFAULT 0,
278                    [last_played] INTEGER NOT NULL DEFAULT 0,
279                    [timestamp_added] INTEGER DEFAULT (cast(strftime('%s','now') as int)),
280                    [timestamp_modified] INTEGER NOT NULL DEFAULT 0,
281                    [search_name] TEXT NOT NULL,
282                    [search_sort_name] TEXT NOT NULL
283                );"""
284        )
285        await self.database.execute(
286            f"""
287            CREATE TABLE IF NOT EXISTS {DB_TABLE_ARTISTS}(
288            [item_id] INTEGER PRIMARY KEY AUTOINCREMENT,
289            [name] TEXT NOT NULL,
290            [sort_name] TEXT NOT NULL,
291            [favorite] BOOLEAN NOT NULL DEFAULT 0,
292            [metadata] json NOT NULL,
293            [play_count] INTEGER DEFAULT 0,
294            [last_played] INTEGER DEFAULT 0,
295            [timestamp_added] INTEGER DEFAULT (cast(strftime('%s','now') as int)),
296            [timestamp_modified] INTEGER NOT NULL DEFAULT 0,
297            [search_name] TEXT NOT NULL,
298            [search_sort_name] TEXT NOT NULL,
299            [artist_type] TEXT NOT NULL
300            );"""
301        )
302        await self.database.execute(
303            f"""
304            CREATE TABLE IF NOT EXISTS {DB_TABLE_TRACKS}(
305            [item_id] INTEGER PRIMARY KEY AUTOINCREMENT,
306            [name] TEXT NOT NULL,
307            [sort_name] TEXT NOT NULL,
308            [version] TEXT,
309            [duration] INTEGER,
310            [favorite] BOOLEAN NOT NULL DEFAULT 0,
311            [metadata] json NOT NULL,
312            [play_count] INTEGER DEFAULT 0,
313            [last_played] INTEGER DEFAULT 0,
314            [timestamp_added] INTEGER DEFAULT (cast(strftime('%s','now') as int)),
315            [timestamp_modified] INTEGER NOT NULL DEFAULT 0,
316            [search_name] TEXT NOT NULL,
317            [search_sort_name] TEXT NOT NULL
318            );"""
319        )
320        await self.database.execute(
321            f"""
322            CREATE TABLE IF NOT EXISTS {DB_TABLE_PLAYLISTS}(
323            [item_id] INTEGER PRIMARY KEY AUTOINCREMENT,
324            [name] TEXT NOT NULL,
325            [sort_name] TEXT NOT NULL,
326            [translation_key] TEXT,
327            [translation_params] json,
328            [owner] TEXT NOT NULL,
329            [is_editable] BOOLEAN NOT NULL,
330            [favorite] BOOLEAN NOT NULL DEFAULT 0,
331            [metadata] json NOT NULL,
332            [play_count] INTEGER DEFAULT 0,
333            [last_played] INTEGER DEFAULT 0,
334            [timestamp_added] INTEGER DEFAULT (cast(strftime('%s','now') as int)),
335            [timestamp_modified] INTEGER NOT NULL DEFAULT 0,
336            [search_name] TEXT NOT NULL,
337            [search_sort_name] TEXT NOT NULL,
338            [supported_mediatypes] json NOT NULL DEFAULT '[\"track\"]',
339            [is_dynamic] BOOLEAN NOT NULL DEFAULT 0
340            );"""
341        )
342        await self.database.execute(
343            f"""
344            CREATE TABLE IF NOT EXISTS {DB_TABLE_RADIOS}(
345            [item_id] INTEGER PRIMARY KEY AUTOINCREMENT,
346            [name] TEXT NOT NULL,
347            [sort_name] TEXT NOT NULL,
348            [favorite] BOOLEAN NOT NULL DEFAULT 0,
349            [metadata] json NOT NULL,
350            [play_count] INTEGER DEFAULT 0,
351            [last_played] INTEGER DEFAULT 0,
352            [timestamp_added] INTEGER DEFAULT (cast(strftime('%s','now') as int)),
353            [timestamp_modified] INTEGER NOT NULL DEFAULT 0,
354            [search_name] TEXT NOT NULL,
355            [search_sort_name] TEXT NOT NULL
356            );"""
357        )
358        await self.database.execute(
359            f"""
360            CREATE TABLE IF NOT EXISTS {DB_TABLE_AUDIOBOOKS}(
361            [item_id] INTEGER PRIMARY KEY AUTOINCREMENT,
362            [name] TEXT NOT NULL,
363            [sort_name] TEXT NOT NULL,
364            [version] TEXT,
365            [favorite] BOOLEAN NOT NULL DEFAULT 0,
366            [publisher] TEXT,
367            [authors] json NOT NULL,
368            [narrators] json NOT NULL,
369            [metadata] json NOT NULL,
370            [duration] INTEGER,
371            [play_count] INTEGER DEFAULT 0,
372            [last_played] INTEGER DEFAULT 0,
373            [timestamp_added] INTEGER DEFAULT (cast(strftime('%s','now') as int)),
374            [timestamp_modified] INTEGER NOT NULL DEFAULT 0,
375            [search_name] TEXT NOT NULL,
376            [search_sort_name] TEXT NOT NULL
377            );"""
378        )
379        await self.database.execute(
380            f"""
381            CREATE TABLE IF NOT EXISTS {DB_TABLE_PODCASTS}(
382            [item_id] INTEGER PRIMARY KEY AUTOINCREMENT,
383            [name] TEXT NOT NULL,
384            [sort_name] TEXT NOT NULL,
385            [version] TEXT,
386            [favorite] BOOLEAN NOT NULL DEFAULT 0,
387            [publisher] TEXT,
388            [total_episodes] INTEGER NOT NULL,
389            [metadata] json NOT NULL,
390            [play_count] INTEGER NOT NULL DEFAULT 0,
391            [last_played] INTEGER NOT NULL DEFAULT 0,
392            [timestamp_added] INTEGER DEFAULT (cast(strftime('%s','now') as int)),
393            [timestamp_modified] INTEGER NOT NULL DEFAULT 0,
394            [search_name] TEXT NOT NULL,
395            [search_sort_name] TEXT NOT NULL
396            );"""
397        )
398        await self.database.execute(
399            f"""
400            CREATE TABLE IF NOT EXISTS {DB_TABLE_GENRES}(
401            [item_id] INTEGER PRIMARY KEY AUTOINCREMENT,
402            [name] TEXT NOT NULL,
403            [sort_name] TEXT NOT NULL,
404            [translation_key] TEXT,
405            [description] TEXT,
406            [favorite] BOOLEAN NOT NULL DEFAULT 0,
407            [metadata] json NOT NULL,
408            [genre_aliases] json NOT NULL DEFAULT '[]',
409            [play_count] INTEGER NOT NULL DEFAULT 0,
410            [last_played] INTEGER NOT NULL DEFAULT 0,
411            [timestamp_added] INTEGER DEFAULT (cast(strftime('%s','now') as int)),
412            [timestamp_modified] INTEGER NOT NULL DEFAULT 0,
413            [search_name] TEXT NOT NULL,
414            [search_sort_name] TEXT NOT NULL,
415            [is_excluded] BOOLEAN NOT NULL DEFAULT 0,
416            [is_default] BOOLEAN NOT NULL DEFAULT 0,
417            [content_type] TEXT
418            );"""
419        )
420        await self.database.execute(
421            f"""
422            CREATE TABLE IF NOT EXISTS {DB_TABLE_GENRE_MEDIA_ITEM_MAPPING}(
423            [genre_id] INTEGER NOT NULL,
424            [media_id] INTEGER NOT NULL,
425            [media_type] TEXT NOT NULL,
426            [alias] TEXT,
427            [is_derived] BOOLEAN NOT NULL DEFAULT 0,
428            [is_manual] BOOLEAN NOT NULL DEFAULT 0,
429            FOREIGN KEY([genre_id]) REFERENCES [genres]([item_id]),
430            UNIQUE(genre_id, media_id, media_type)
431            );"""
432        )
433        await self.database.execute(
434            f"""
435            CREATE TABLE IF NOT EXISTS {DB_TABLE_GENRE_MEDIA_ITEM_EXCLUSION}(
436            [genre_id] INTEGER NOT NULL,
437            [media_id] INTEGER NOT NULL,
438            [media_type] TEXT NOT NULL,
439            FOREIGN KEY([genre_id]) REFERENCES [genres]([item_id]),
440            UNIQUE(genre_id, media_id, media_type)
441            );"""
442        )
443        await self.database.execute(
444            f"""
445            CREATE TABLE IF NOT EXISTS {DB_TABLE_ALBUM_TRACKS}(
446            [id] INTEGER PRIMARY KEY AUTOINCREMENT,
447            [track_id] INTEGER NOT NULL,
448            [album_id] INTEGER NOT NULL,
449            [disc_number] INTEGER NOT NULL,
450            [track_number] INTEGER NOT NULL,
451            FOREIGN KEY([track_id]) REFERENCES [tracks]([item_id]),
452            FOREIGN KEY([album_id]) REFERENCES [albums]([item_id]),
453            UNIQUE(track_id, album_id)
454            );"""
455        )
456        await self.database.execute(
457            f"""
458            CREATE TABLE IF NOT EXISTS {DB_TABLE_PROVIDER_MAPPINGS}(
459            [media_type] TEXT NOT NULL,
460            [item_id] INTEGER NOT NULL,
461            [provider_domain] TEXT NOT NULL,
462            [provider_instance] TEXT NOT NULL,
463            [provider_item_id] TEXT NOT NULL,
464            [available] BOOLEAN NOT NULL DEFAULT 1,
465            [in_library] BOOLEAN NOT NULL DEFAULT 0,
466            [is_unique] BOOLEAN,
467            [url] text,
468            [audio_format] json,
469            [details] TEXT,
470            UNIQUE(media_type, provider_instance, provider_item_id)
471            );"""
472        )
473        await self.database.execute(
474            f"""
475            CREATE TABLE IF NOT EXISTS {DB_TABLE_EXTERNAL_ID_LOOKUP}(
476            [media_type] TEXT NOT NULL,
477            [external_id_type] TEXT NOT NULL,
478            [external_id] TEXT NOT NULL COLLATE NOCASE,
479            [item_id] INTEGER NOT NULL,
480            UNIQUE(media_type, external_id, external_id_type, item_id)
481            );"""
482        )
483        await self.database.execute(
484            f"""CREATE TABLE IF NOT EXISTS {DB_TABLE_TRACK_ARTISTS}(
485            [track_id] INTEGER NOT NULL,
486            [artist_id] INTEGER NOT NULL,
487            FOREIGN KEY([track_id]) REFERENCES [tracks]([item_id]),
488            FOREIGN KEY([artist_id]) REFERENCES [artists]([item_id]),
489            UNIQUE(track_id, artist_id)
490            );"""
491        )
492        await self.database.execute(
493            f"""CREATE TABLE IF NOT EXISTS {DB_TABLE_ALBUM_ARTISTS}(
494            [album_id] INTEGER NOT NULL,
495            [artist_id] INTEGER NOT NULL,
496            FOREIGN KEY([album_id]) REFERENCES [albums]([item_id]),
497            FOREIGN KEY([artist_id]) REFERENCES [artists]([item_id]),
498            UNIQUE(album_id, artist_id)
499            );"""
500        )
501        await self.database.execute(
502            f"""CREATE TABLE IF NOT EXISTS {DB_TABLE_AUDIOBOOK_ARTISTS}(
503            [audiobook_id] INTEGER NOT NULL,
504            [artist_id] INTEGER NOT NULL,
505            FOREIGN KEY([audiobook_id]) REFERENCES [audiobooks]([item_id]),
506            FOREIGN KEY([artist_id]) REFERENCES [artists]([item_id]),
507            UNIQUE(audiobook_id, artist_id)
508            );"""
509        )
510
511        await self.database.execute(
512            f"""CREATE TABLE IF NOT EXISTS {DB_TABLE_AUDIO_ANALYSIS}(
513                    [id] INTEGER PRIMARY KEY AUTOINCREMENT,
514                    [media_type] TEXT NOT NULL,
515                    [item_id] TEXT NOT NULL,
516                    [provider] TEXT NOT NULL,
517                    [aa_provider_domain] TEXT NOT NULL,
518                    [analysis_data] json NOT NULL,
519                    [analysis_version] INTEGER DEFAULT 1,
520                    [timestamp_created] INTEGER DEFAULT (cast(strftime('%s','now') as int)),
521                    UNIQUE(item_id,provider,aa_provider_domain,media_type));"""
522        )
523
524        await self.database.execute(
525            f"""CREATE TABLE IF NOT EXISTS {DB_TABLE_AUDIO_ANALYSIS_FAILURES}(
526                    [id] INTEGER PRIMARY KEY AUTOINCREMENT,
527                    [media_type] TEXT NOT NULL,
528                    [item_id] TEXT NOT NULL,
529                    [provider] TEXT NOT NULL,
530                    [aa_provider_domain] TEXT NOT NULL,
531                    [reason] TEXT NOT NULL,
532                    [analysis_version] INTEGER NOT NULL DEFAULT 1,
533                    [next_retry] INTEGER,
534                    [timestamp_created] INTEGER DEFAULT (cast(strftime('%s','now') as int)),
535                    UNIQUE(item_id,provider,aa_provider_domain,media_type));"""
536        )
537
538        # full-text search tables (trigram tokenizer for substring matching on search_name)
539        for db_table in MEDIA_ITEM_DB_TABLES:
540            try:
541                await self.database.execute(
542                    f"""CREATE VIRTUAL TABLE IF NOT EXISTS {db_table}_fts USING fts5(
543                        search_name,
544                        content='{db_table}',
545                        content_rowid='item_id',
546                        tokenize='trigram'
547                        );"""
548                )
549            except sqlite3.OperationalError as err:
550                msg = (
551                    "The library database requires SQLite 3.34+ with FTS5 support "
552                    f"(detected version: {sqlite3.sqlite_version})"
553                )
554                raise MusicAssistantError(msg) from err
555
556        await self.database.commit()
557
558    async def __create_database_indexes(self) -> None:
559        """Create database indexes."""
560        for db_table in (
561            DB_TABLE_ARTISTS,
562            DB_TABLE_ALBUMS,
563            DB_TABLE_TRACKS,
564            DB_TABLE_PLAYLISTS,
565            DB_TABLE_RADIOS,
566            DB_TABLE_AUDIOBOOKS,
567            DB_TABLE_PODCASTS,
568            DB_TABLE_GENRES,
569        ):
570            # index on favorite column
571            await self.database.execute(
572                f"CREATE INDEX IF NOT EXISTS {db_table}_favorite_idx on {db_table}(favorite);"
573            )
574            # index on name
575            await self.database.execute(
576                f"CREATE INDEX IF NOT EXISTS {db_table}_name_idx on {db_table}(name);"
577            )
578            # index on search_name (=lowercase name without diacritics)
579            await self.database.execute(
580                f"CREATE INDEX IF NOT EXISTS {db_table}_name_nocase_idx ON {db_table}(search_name);"
581            )
582            # index on sort_name
583            await self.database.execute(
584                f"CREATE INDEX IF NOT EXISTS {db_table}_sort_name_idx on {db_table}(sort_name);"
585            )
586            # index on search_sort_name (=lowercase sort_name without diacritics)
587            await self.database.execute(
588                f"CREATE INDEX IF NOT EXISTS {db_table}_search_sort_name_idx "
589                f"ON {db_table}(search_sort_name);"
590            )
591            # index on timestamp_added
592            await self.database.execute(
593                f"CREATE INDEX IF NOT EXISTS {db_table}_timestamp_added_idx "
594                f"on {db_table}(timestamp_added);"
595            )
596            # index on play_count
597            await self.database.execute(
598                f"CREATE INDEX IF NOT EXISTS {db_table}_play_count_idx on {db_table}(play_count);"
599            )
600            # index on last_played
601            await self.database.execute(
602                f"CREATE INDEX IF NOT EXISTS {db_table}_last_played_idx on {db_table}(last_played);"
603            )
604
605        # indexes on provider_mappings table
606        await self.database.execute(
607            f"CREATE INDEX IF NOT EXISTS {DB_TABLE_PROVIDER_MAPPINGS}_media_type_item_id_idx "
608            f"on {DB_TABLE_PROVIDER_MAPPINGS}(media_type,item_id);"
609        )
610        await self.database.execute(
611            f"CREATE INDEX IF NOT EXISTS {DB_TABLE_PROVIDER_MAPPINGS}_provider_domain_idx "
612            f"on {DB_TABLE_PROVIDER_MAPPINGS}(media_type,provider_domain,provider_item_id);"
613        )
614        await self.database.execute(
615            f"CREATE UNIQUE INDEX IF NOT EXISTS {DB_TABLE_PROVIDER_MAPPINGS}_provider_instance_idx "
616            f"on {DB_TABLE_PROVIDER_MAPPINGS}(media_type,provider_instance,provider_item_id);"
617        )
618        await self.database.execute(
619            "CREATE INDEX IF NOT EXISTS "
620            f"{DB_TABLE_PROVIDER_MAPPINGS}_media_type_provider_instance_idx "
621            f"on {DB_TABLE_PROVIDER_MAPPINGS}(media_type,provider_instance);"
622        )
623        await self.database.execute(
624            "CREATE INDEX IF NOT EXISTS "
625            f"{DB_TABLE_PROVIDER_MAPPINGS}_media_type_provider_domain_idx "
626            f"on {DB_TABLE_PROVIDER_MAPPINGS}(media_type,provider_domain);"
627        )
628        await self.database.execute(
629            "CREATE INDEX IF NOT EXISTS "
630            f"{DB_TABLE_PROVIDER_MAPPINGS}_media_type_provider_instance_library_idx "
631            f"on {DB_TABLE_PROVIDER_MAPPINGS}(media_type,provider_instance,in_library);"
632        )
633
634        # index on external_id_lookup table to serve the per-item delete/rewrite path;
635        # the typed and untyped external id lookups are served by the table's unique
636        # index, which is deliberately ordered (media_type,external_id,...) for that
637        await self.database.execute(
638            f"CREATE INDEX IF NOT EXISTS {DB_TABLE_EXTERNAL_ID_LOOKUP}_item_id_idx "
639            f"on {DB_TABLE_EXTERNAL_ID_LOOKUP}(media_type,item_id);"
640        )
641
642        # indexes on track_artists table
643        await self.database.execute(
644            f"CREATE INDEX IF NOT EXISTS {DB_TABLE_TRACK_ARTISTS}_track_id_idx "
645            f"on {DB_TABLE_TRACK_ARTISTS}(track_id);"
646        )
647        await self.database.execute(
648            f"CREATE INDEX IF NOT EXISTS {DB_TABLE_TRACK_ARTISTS}_artist_id_idx "
649            f"on {DB_TABLE_TRACK_ARTISTS}(artist_id);"
650        )
651        # indexes on album_artists table
652        await self.database.execute(
653            f"CREATE INDEX IF NOT EXISTS {DB_TABLE_ALBUM_ARTISTS}_album_id_idx "
654            f"on {DB_TABLE_ALBUM_ARTISTS}(album_id);"
655        )
656        await self.database.execute(
657            f"CREATE INDEX IF NOT EXISTS {DB_TABLE_ALBUM_ARTISTS}_artist_id_idx "
658            f"on {DB_TABLE_ALBUM_ARTISTS}(artist_id);"
659        )
660        # indexes on genre_media_item_mapping table
661        await self.database.execute(
662            f"CREATE INDEX IF NOT EXISTS {DB_TABLE_GENRE_MEDIA_ITEM_MAPPING}_media_idx "
663            f"on {DB_TABLE_GENRE_MEDIA_ITEM_MAPPING}(media_id,media_type);"
664        )
665        await self.database.execute(
666            f"CREATE INDEX IF NOT EXISTS {DB_TABLE_GENRE_MEDIA_ITEM_MAPPING}_genre_alias_idx "
667            f"on {DB_TABLE_GENRE_MEDIA_ITEM_MAPPING}(genre_id,alias);"
668        )
669        # indexes on genre_media_item_exclusion table
670        await self.database.execute(
671            f"CREATE INDEX IF NOT EXISTS {DB_TABLE_GENRE_MEDIA_ITEM_EXCLUSION}_media_idx "
672            f"on {DB_TABLE_GENRE_MEDIA_ITEM_EXCLUSION}(media_id,media_type);"
673        )
674        await self.database.execute(
675            f"CREATE INDEX IF NOT EXISTS {DB_TABLE_GENRE_MEDIA_ITEM_EXCLUSION}_genre_idx "
676            f"on {DB_TABLE_GENRE_MEDIA_ITEM_EXCLUSION}(genre_id);"
677        )
678        # unique index on playlog table
679        await self.database.execute(
680            f"CREATE UNIQUE INDEX IF NOT EXISTS {DB_TABLE_PLAYLOG}_unique_idx "
681            f"on {DB_TABLE_PLAYLOG}(item_id,provider,media_type,userid);"
682        )
683        # speed up recency lookups (smart shuffle / dedup) by user and time window
684        await self.database.execute(
685            f"CREATE INDEX IF NOT EXISTS {DB_TABLE_PLAYLOG}_userid_timestamp_idx "
686            f"on {DB_TABLE_PLAYLOG}(userid,timestamp);"
687        )
688        # serves the podcast episode resume lookup, which no existing index can: they all
689        # lead with item_id or userid, neither of which that query filters on. Column order
690        # matches its filter, so with a userid it needs no sort for the ORDER BY either
691        await self.database.execute(
692            f"CREATE INDEX IF NOT EXISTS {DB_TABLE_PLAYLOG}_provider_media_type_idx "
693            f"on {DB_TABLE_PLAYLOG}(provider,media_type,userid,timestamp);"
694        )
695        await self.database.commit()
696
697    async def __create_database_triggers(self) -> None:
698        """Create database triggers."""
699        # triggers to auto update timestamps
700        for db_table in MEDIA_ITEM_DB_TABLES:
701            await self.database.execute(
702                f"""
703                CREATE TRIGGER IF NOT EXISTS update_{db_table}_timestamp
704                AFTER UPDATE ON {db_table}
705                BEGIN
706                    UPDATE {db_table} SET timestamp_modified=cast(strftime('%s','now') as int)
707                    WHERE rowid = new.rowid;
708                END;
709                """
710            )
711        # triggers to keep the FTS search tables in sync with the content tables
712        for db_table in MEDIA_ITEM_DB_TABLES:
713            await self.database.execute(
714                f"""
715                CREATE TRIGGER IF NOT EXISTS {db_table}_fts_insert
716                AFTER INSERT ON {db_table}
717                BEGIN
718                    INSERT INTO {db_table}_fts(rowid, search_name)
719                    VALUES (new.item_id, new.search_name);
720                END;
721                """
722            )
723            await self.database.execute(
724                f"""
725                CREATE TRIGGER IF NOT EXISTS {db_table}_fts_delete
726                AFTER DELETE ON {db_table}
727                BEGIN
728                    INSERT INTO {db_table}_fts({db_table}_fts, rowid, search_name)
729                    VALUES ('delete', old.item_id, old.search_name);
730                END;
731                """
732            )
733            await self.database.execute(
734                f"""
735                CREATE TRIGGER IF NOT EXISTS {db_table}_fts_update
736                AFTER UPDATE OF search_name ON {db_table}
737                BEGIN
738                    INSERT INTO {db_table}_fts({db_table}_fts, rowid, search_name)
739                    VALUES ('delete', old.item_id, old.search_name);
740                    INSERT INTO {db_table}_fts(rowid, search_name)
741                    VALUES (new.item_id, new.search_name);
742                END;
743                """
744            )
745        await self.database.commit()
746