/
/
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 [is_dynamic] BOOLEAN NOT NULL DEFAULT 0
364 );"""
365 )
366 await self.database.execute(
367 f"""
368 CREATE TABLE IF NOT EXISTS {DB_TABLE_AUDIOBOOKS}(
369 [item_id] INTEGER PRIMARY KEY AUTOINCREMENT,
370 [name] TEXT NOT NULL,
371 [sort_name] TEXT NOT NULL,
372 [version] TEXT,
373 [favorite] BOOLEAN NOT NULL DEFAULT 0,
374 [publisher] TEXT,
375 [authors] json NOT NULL,
376 [narrators] json NOT NULL,
377 [metadata] json NOT NULL,
378 [duration] INTEGER,
379 [play_count] INTEGER DEFAULT 0,
380 [last_played] INTEGER DEFAULT 0,
381 [timestamp_added] INTEGER DEFAULT (cast(strftime('%s','now') as int)),
382 [timestamp_modified] INTEGER NOT NULL DEFAULT 0,
383 [search_name] TEXT NOT NULL,
384 [search_sort_name] TEXT NOT NULL
385 );"""
386 )
387 await self.database.execute(
388 f"""
389 CREATE TABLE IF NOT EXISTS {DB_TABLE_PODCASTS}(
390 [item_id] INTEGER PRIMARY KEY AUTOINCREMENT,
391 [name] TEXT NOT NULL,
392 [sort_name] TEXT NOT NULL,
393 [version] TEXT,
394 [favorite] BOOLEAN NOT NULL DEFAULT 0,
395 [publisher] TEXT,
396 [total_episodes] INTEGER NOT NULL,
397 [metadata] json NOT NULL,
398 [play_count] INTEGER NOT NULL DEFAULT 0,
399 [last_played] INTEGER NOT NULL DEFAULT 0,
400 [timestamp_added] INTEGER DEFAULT (cast(strftime('%s','now') as int)),
401 [timestamp_modified] INTEGER NOT NULL DEFAULT 0,
402 [search_name] TEXT NOT NULL,
403 [search_sort_name] TEXT NOT NULL
404 );"""
405 )
406 await self.database.execute(
407 f"""
408 CREATE TABLE IF NOT EXISTS {DB_TABLE_GENRES}(
409 [item_id] INTEGER PRIMARY KEY AUTOINCREMENT,
410 [name] TEXT NOT NULL,
411 [sort_name] TEXT NOT NULL,
412 [translation_key] TEXT,
413 [description] TEXT,
414 [favorite] BOOLEAN NOT NULL DEFAULT 0,
415 [metadata] json NOT NULL,
416 [genre_aliases] json NOT NULL DEFAULT '[]',
417 [play_count] INTEGER NOT NULL DEFAULT 0,
418 [last_played] INTEGER NOT NULL DEFAULT 0,
419 [timestamp_added] INTEGER DEFAULT (cast(strftime('%s','now') as int)),
420 [timestamp_modified] INTEGER NOT NULL DEFAULT 0,
421 [search_name] TEXT NOT NULL,
422 [search_sort_name] TEXT NOT NULL,
423 [is_excluded] BOOLEAN NOT NULL DEFAULT 0,
424 [is_default] BOOLEAN NOT NULL DEFAULT 0,
425 [content_type] TEXT
426 );"""
427 )
428 await self.database.execute(
429 f"""
430 CREATE TABLE IF NOT EXISTS {DB_TABLE_GENRE_MEDIA_ITEM_MAPPING}(
431 [genre_id] INTEGER NOT NULL,
432 [media_id] INTEGER NOT NULL,
433 [media_type] TEXT NOT NULL,
434 [alias] TEXT,
435 [is_derived] BOOLEAN NOT NULL DEFAULT 0,
436 [is_manual] BOOLEAN NOT NULL DEFAULT 0,
437 FOREIGN KEY([genre_id]) REFERENCES [genres]([item_id]),
438 UNIQUE(genre_id, media_id, media_type)
439 );"""
440 )
441 await self.database.execute(
442 f"""
443 CREATE TABLE IF NOT EXISTS {DB_TABLE_GENRE_MEDIA_ITEM_EXCLUSION}(
444 [genre_id] INTEGER NOT NULL,
445 [media_id] INTEGER NOT NULL,
446 [media_type] TEXT NOT NULL,
447 FOREIGN KEY([genre_id]) REFERENCES [genres]([item_id]),
448 UNIQUE(genre_id, media_id, media_type)
449 );"""
450 )
451 await self.database.execute(
452 f"""
453 CREATE TABLE IF NOT EXISTS {DB_TABLE_ALBUM_TRACKS}(
454 [id] INTEGER PRIMARY KEY AUTOINCREMENT,
455 [track_id] INTEGER NOT NULL,
456 [album_id] INTEGER NOT NULL,
457 [disc_number] INTEGER NOT NULL,
458 [track_number] INTEGER NOT NULL,
459 FOREIGN KEY([track_id]) REFERENCES [tracks]([item_id]),
460 FOREIGN KEY([album_id]) REFERENCES [albums]([item_id]),
461 UNIQUE(track_id, album_id)
462 );"""
463 )
464 await self.database.execute(
465 f"""
466 CREATE TABLE IF NOT EXISTS {DB_TABLE_PROVIDER_MAPPINGS}(
467 [media_type] TEXT NOT NULL,
468 [item_id] INTEGER NOT NULL,
469 [provider_domain] TEXT NOT NULL,
470 [provider_instance] TEXT NOT NULL,
471 [provider_item_id] TEXT NOT NULL,
472 [available] BOOLEAN NOT NULL DEFAULT 1,
473 [in_library] BOOLEAN NOT NULL DEFAULT 0,
474 [is_unique] BOOLEAN,
475 [url] text,
476 [audio_format] json,
477 [details] TEXT,
478 UNIQUE(media_type, provider_instance, provider_item_id)
479 );"""
480 )
481 await self.database.execute(
482 f"""
483 CREATE TABLE IF NOT EXISTS {DB_TABLE_EXTERNAL_ID_LOOKUP}(
484 [media_type] TEXT NOT NULL,
485 [external_id_type] TEXT NOT NULL,
486 [external_id] TEXT NOT NULL COLLATE NOCASE,
487 [item_id] INTEGER NOT NULL,
488 UNIQUE(media_type, external_id, external_id_type, item_id)
489 );"""
490 )
491 await self.database.execute(
492 f"""CREATE TABLE IF NOT EXISTS {DB_TABLE_TRACK_ARTISTS}(
493 [track_id] INTEGER NOT NULL,
494 [artist_id] INTEGER NOT NULL,
495 FOREIGN KEY([track_id]) REFERENCES [tracks]([item_id]),
496 FOREIGN KEY([artist_id]) REFERENCES [artists]([item_id]),
497 UNIQUE(track_id, artist_id)
498 );"""
499 )
500 await self.database.execute(
501 f"""CREATE TABLE IF NOT EXISTS {DB_TABLE_ALBUM_ARTISTS}(
502 [album_id] INTEGER NOT NULL,
503 [artist_id] INTEGER NOT NULL,
504 FOREIGN KEY([album_id]) REFERENCES [albums]([item_id]),
505 FOREIGN KEY([artist_id]) REFERENCES [artists]([item_id]),
506 UNIQUE(album_id, artist_id)
507 );"""
508 )
509 await self.database.execute(
510 f"""CREATE TABLE IF NOT EXISTS {DB_TABLE_AUDIOBOOK_ARTISTS}(
511 [audiobook_id] INTEGER NOT NULL,
512 [artist_id] INTEGER NOT NULL,
513 FOREIGN KEY([audiobook_id]) REFERENCES [audiobooks]([item_id]),
514 FOREIGN KEY([artist_id]) REFERENCES [artists]([item_id]),
515 UNIQUE(audiobook_id, artist_id)
516 );"""
517 )
518
519 await self.database.execute(
520 f"""CREATE TABLE IF NOT EXISTS {DB_TABLE_AUDIO_ANALYSIS}(
521 [id] INTEGER PRIMARY KEY AUTOINCREMENT,
522 [media_type] TEXT NOT NULL,
523 [item_id] TEXT NOT NULL,
524 [provider] TEXT NOT NULL,
525 [aa_provider_domain] TEXT NOT NULL,
526 [analysis_data] json NOT NULL,
527 [analysis_version] INTEGER DEFAULT 1,
528 [timestamp_created] INTEGER DEFAULT (cast(strftime('%s','now') as int)),
529 UNIQUE(item_id,provider,aa_provider_domain,media_type));"""
530 )
531
532 await self.database.execute(
533 f"""CREATE TABLE IF NOT EXISTS {DB_TABLE_AUDIO_ANALYSIS_FAILURES}(
534 [id] INTEGER PRIMARY KEY AUTOINCREMENT,
535 [media_type] TEXT NOT NULL,
536 [item_id] TEXT NOT NULL,
537 [provider] TEXT NOT NULL,
538 [aa_provider_domain] TEXT NOT NULL,
539 [reason] TEXT NOT NULL,
540 [analysis_version] INTEGER NOT NULL DEFAULT 1,
541 [next_retry] INTEGER,
542 [timestamp_created] INTEGER DEFAULT (cast(strftime('%s','now') as int)),
543 UNIQUE(item_id,provider,aa_provider_domain,media_type));"""
544 )
545
546 # full-text search tables (trigram tokenizer for substring matching on search_name)
547 for db_table in MEDIA_ITEM_DB_TABLES:
548 try:
549 await self.database.execute(
550 f"""CREATE VIRTUAL TABLE IF NOT EXISTS {db_table}_fts USING fts5(
551 search_name,
552 content='{db_table}',
553 content_rowid='item_id',
554 tokenize='trigram'
555 );"""
556 )
557 except sqlite3.OperationalError as err:
558 msg = (
559 "The library database requires SQLite 3.34+ with FTS5 support "
560 f"(detected version: {sqlite3.sqlite_version})"
561 )
562 raise MusicAssistantError(msg) from err
563
564 await self.database.commit()
565
566 async def __create_database_indexes(self) -> None:
567 """Create database indexes."""
568 for db_table in (
569 DB_TABLE_ARTISTS,
570 DB_TABLE_ALBUMS,
571 DB_TABLE_TRACKS,
572 DB_TABLE_PLAYLISTS,
573 DB_TABLE_RADIOS,
574 DB_TABLE_AUDIOBOOKS,
575 DB_TABLE_PODCASTS,
576 DB_TABLE_GENRES,
577 ):
578 # index on favorite column
579 await self.database.execute(
580 f"CREATE INDEX IF NOT EXISTS {db_table}_favorite_idx on {db_table}(favorite);"
581 )
582 # index on name
583 await self.database.execute(
584 f"CREATE INDEX IF NOT EXISTS {db_table}_name_idx on {db_table}(name);"
585 )
586 # index on search_name (=lowercase name without diacritics)
587 await self.database.execute(
588 f"CREATE INDEX IF NOT EXISTS {db_table}_name_nocase_idx ON {db_table}(search_name);"
589 )
590 # index on sort_name
591 await self.database.execute(
592 f"CREATE INDEX IF NOT EXISTS {db_table}_sort_name_idx on {db_table}(sort_name);"
593 )
594 # index on search_sort_name (=lowercase sort_name without diacritics)
595 await self.database.execute(
596 f"CREATE INDEX IF NOT EXISTS {db_table}_search_sort_name_idx "
597 f"ON {db_table}(search_sort_name);"
598 )
599 # index on timestamp_added
600 await self.database.execute(
601 f"CREATE INDEX IF NOT EXISTS {db_table}_timestamp_added_idx "
602 f"on {db_table}(timestamp_added);"
603 )
604 # index on play_count
605 await self.database.execute(
606 f"CREATE INDEX IF NOT EXISTS {db_table}_play_count_idx on {db_table}(play_count);"
607 )
608 # index on last_played
609 await self.database.execute(
610 f"CREATE INDEX IF NOT EXISTS {db_table}_last_played_idx on {db_table}(last_played);"
611 )
612
613 # indexes on provider_mappings table
614 await self.database.execute(
615 f"CREATE INDEX IF NOT EXISTS {DB_TABLE_PROVIDER_MAPPINGS}_media_type_item_id_idx "
616 f"on {DB_TABLE_PROVIDER_MAPPINGS}(media_type,item_id);"
617 )
618 await self.database.execute(
619 f"CREATE INDEX IF NOT EXISTS {DB_TABLE_PROVIDER_MAPPINGS}_provider_domain_idx "
620 f"on {DB_TABLE_PROVIDER_MAPPINGS}(media_type,provider_domain,provider_item_id);"
621 )
622 await self.database.execute(
623 f"CREATE UNIQUE INDEX IF NOT EXISTS {DB_TABLE_PROVIDER_MAPPINGS}_provider_instance_idx "
624 f"on {DB_TABLE_PROVIDER_MAPPINGS}(media_type,provider_instance,provider_item_id);"
625 )
626 await self.database.execute(
627 "CREATE INDEX IF NOT EXISTS "
628 f"{DB_TABLE_PROVIDER_MAPPINGS}_media_type_provider_instance_idx "
629 f"on {DB_TABLE_PROVIDER_MAPPINGS}(media_type,provider_instance);"
630 )
631 await self.database.execute(
632 "CREATE INDEX IF NOT EXISTS "
633 f"{DB_TABLE_PROVIDER_MAPPINGS}_media_type_provider_domain_idx "
634 f"on {DB_TABLE_PROVIDER_MAPPINGS}(media_type,provider_domain);"
635 )
636 await self.database.execute(
637 "CREATE INDEX IF NOT EXISTS "
638 f"{DB_TABLE_PROVIDER_MAPPINGS}_media_type_provider_instance_library_idx "
639 f"on {DB_TABLE_PROVIDER_MAPPINGS}(media_type,provider_instance,in_library);"
640 )
641
642 # index on external_id_lookup table to serve the per-item delete/rewrite path;
643 # the typed and untyped external id lookups are served by the table's unique
644 # index, which is deliberately ordered (media_type,external_id,...) for that
645 await self.database.execute(
646 f"CREATE INDEX IF NOT EXISTS {DB_TABLE_EXTERNAL_ID_LOOKUP}_item_id_idx "
647 f"on {DB_TABLE_EXTERNAL_ID_LOOKUP}(media_type,item_id);"
648 )
649
650 # indexes on track_artists table
651 await self.database.execute(
652 f"CREATE INDEX IF NOT EXISTS {DB_TABLE_TRACK_ARTISTS}_track_id_idx "
653 f"on {DB_TABLE_TRACK_ARTISTS}(track_id);"
654 )
655 await self.database.execute(
656 f"CREATE INDEX IF NOT EXISTS {DB_TABLE_TRACK_ARTISTS}_artist_id_idx "
657 f"on {DB_TABLE_TRACK_ARTISTS}(artist_id);"
658 )
659 # indexes on album_artists table
660 await self.database.execute(
661 f"CREATE INDEX IF NOT EXISTS {DB_TABLE_ALBUM_ARTISTS}_album_id_idx "
662 f"on {DB_TABLE_ALBUM_ARTISTS}(album_id);"
663 )
664 await self.database.execute(
665 f"CREATE INDEX IF NOT EXISTS {DB_TABLE_ALBUM_ARTISTS}_artist_id_idx "
666 f"on {DB_TABLE_ALBUM_ARTISTS}(artist_id);"
667 )
668 # indexes on genre_media_item_mapping table
669 await self.database.execute(
670 f"CREATE INDEX IF NOT EXISTS {DB_TABLE_GENRE_MEDIA_ITEM_MAPPING}_media_idx "
671 f"on {DB_TABLE_GENRE_MEDIA_ITEM_MAPPING}(media_id,media_type);"
672 )
673 await self.database.execute(
674 f"CREATE INDEX IF NOT EXISTS {DB_TABLE_GENRE_MEDIA_ITEM_MAPPING}_genre_alias_idx "
675 f"on {DB_TABLE_GENRE_MEDIA_ITEM_MAPPING}(genre_id,alias);"
676 )
677 # indexes on genre_media_item_exclusion table
678 await self.database.execute(
679 f"CREATE INDEX IF NOT EXISTS {DB_TABLE_GENRE_MEDIA_ITEM_EXCLUSION}_media_idx "
680 f"on {DB_TABLE_GENRE_MEDIA_ITEM_EXCLUSION}(media_id,media_type);"
681 )
682 await self.database.execute(
683 f"CREATE INDEX IF NOT EXISTS {DB_TABLE_GENRE_MEDIA_ITEM_EXCLUSION}_genre_idx "
684 f"on {DB_TABLE_GENRE_MEDIA_ITEM_EXCLUSION}(genre_id);"
685 )
686 # unique index on playlog table
687 await self.database.execute(
688 f"CREATE UNIQUE INDEX IF NOT EXISTS {DB_TABLE_PLAYLOG}_unique_idx "
689 f"on {DB_TABLE_PLAYLOG}(item_id,provider,media_type,userid);"
690 )
691 # speed up recency lookups (smart shuffle / dedup) by user and time window
692 await self.database.execute(
693 f"CREATE INDEX IF NOT EXISTS {DB_TABLE_PLAYLOG}_userid_timestamp_idx "
694 f"on {DB_TABLE_PLAYLOG}(userid,timestamp);"
695 )
696 # serves the podcast episode resume lookup, which no existing index can: they all
697 # lead with item_id or userid, neither of which that query filters on. Column order
698 # matches its filter, so with a userid it needs no sort for the ORDER BY either
699 await self.database.execute(
700 f"CREATE INDEX IF NOT EXISTS {DB_TABLE_PLAYLOG}_provider_media_type_idx "
701 f"on {DB_TABLE_PLAYLOG}(provider,media_type,userid,timestamp);"
702 )
703 await self.database.commit()
704
705 async def __create_database_triggers(self) -> None:
706 """Create database triggers."""
707 # triggers to auto update timestamps
708 for db_table in MEDIA_ITEM_DB_TABLES:
709 await self.database.execute(
710 f"""
711 CREATE TRIGGER IF NOT EXISTS update_{db_table}_timestamp
712 AFTER UPDATE ON {db_table}
713 BEGIN
714 UPDATE {db_table} SET timestamp_modified=cast(strftime('%s','now') as int)
715 WHERE rowid = new.rowid;
716 END;
717 """
718 )
719 # triggers to keep the FTS search tables in sync with the content tables
720 for db_table in MEDIA_ITEM_DB_TABLES:
721 await self.database.execute(
722 f"""
723 CREATE TRIGGER IF NOT EXISTS {db_table}_fts_insert
724 AFTER INSERT ON {db_table}
725 BEGIN
726 INSERT INTO {db_table}_fts(rowid, search_name)
727 VALUES (new.item_id, new.search_name);
728 END;
729 """
730 )
731 await self.database.execute(
732 f"""
733 CREATE TRIGGER IF NOT EXISTS {db_table}_fts_delete
734 AFTER DELETE ON {db_table}
735 BEGIN
736 INSERT INTO {db_table}_fts({db_table}_fts, rowid, search_name)
737 VALUES ('delete', old.item_id, old.search_name);
738 END;
739 """
740 )
741 await self.database.execute(
742 f"""
743 CREATE TRIGGER IF NOT EXISTS {db_table}_fts_update
744 AFTER UPDATE OF search_name ON {db_table}
745 BEGIN
746 INSERT INTO {db_table}_fts({db_table}_fts, rowid, search_name)
747 VALUES ('delete', old.item_id, old.search_name);
748 INSERT INTO {db_table}_fts(rowid, search_name)
749 VALUES (new.item_id, new.search_name);
750 END;
751 """
752 )
753 await self.database.commit()
754