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