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