/
/
/
1"""
2Database schema migration logic for the music library database.
3
4Holds the versioned, step-by-step migrations that bring an existing library
5database up to the current ``DB_SCHEMA_VERSION``. Kept separate from the
6controller/connection setup so this (large) migration code stays isolated and
7individually testable.
8"""
9
10from __future__ import annotations
11
12from contextlib import suppress
13from datetime import datetime
14from typing import TYPE_CHECKING, cast
15
16from music_assistant_models.enums import MediaType
17from music_assistant_models.errors import MusicAssistantError
18from music_assistant_models.helpers import create_safe_string
19
20from music_assistant.constants import (
21 DB_TABLE_ALBUMS,
22 DB_TABLE_ARTISTS,
23 DB_TABLE_AUDIO_ANALYSIS,
24 DB_TABLE_AUDIOBOOKS,
25 DB_TABLE_EXTERNAL_ID_LOOKUP,
26 DB_TABLE_GENRE_MEDIA_ITEM_EXCLUSION,
27 DB_TABLE_GENRE_MEDIA_ITEM_MAPPING,
28 DB_TABLE_GENRES,
29 DB_TABLE_LOUDNESS_MEASUREMENTS,
30 DB_TABLE_PLAYLISTS,
31 DB_TABLE_PLAYLOG,
32 DB_TABLE_PODCASTS,
33 DB_TABLE_PROVIDER_MAPPINGS,
34 DB_TABLE_RADIOS,
35 DB_TABLE_TRACKS,
36 DEFAULT_GENRE_MAPPING,
37 GENRE_ICONS_DIR_NAME,
38 LOUDNESS_MEASUREMENT_MIN_LUFS,
39 MEDIA_ITEM_DB_TABLES,
40)
41from music_assistant.controllers.music.constants import DB_SCHEMA_VERSION
42from music_assistant.controllers.music.media.genres import GenreController
43from music_assistant.helpers.json import json_dumps, json_loads, serialize_to_json
44from music_assistant.helpers.lyrics import normalize_lrc_lyrics
45
46if TYPE_CHECKING:
47 import logging
48 from collections.abc import Awaitable, Callable
49
50 from music_assistant import MusicAssistant
51 from music_assistant.helpers.database import DatabaseConnection
52
53
54async def migrate_database( # noqa: PLR0915
55 mass: MusicAssistant,
56 database: DatabaseConnection,
57 logger: logging.Logger,
58 prev_version: int,
59 create_tables: Callable[[], Awaitable[None]],
60) -> None:
61 """
62 Migrate the library database from a previous schema version to the current one.
63
64 :param prev_version: the schema version currently stored in the database.
65 :param create_tables: callback that (re)creates the current table schema, used by
66 the migration steps that rebuild a table from scratch.
67 """
68 logger.info("Migrating database from version %s to %s", prev_version, DB_SCHEMA_VERSION)
69
70 if prev_version < 15:
71 raise MusicAssistantError("Database schema version too old to migrate")
72
73 if prev_version <= 15:
74 # add search_name and search_sort_name columns to all tables
75 # and populate them with the name and sort_name values
76 # this is to allow for local/case independent searches
77 for table in (
78 DB_TABLE_TRACKS,
79 DB_TABLE_ALBUMS,
80 DB_TABLE_ARTISTS,
81 DB_TABLE_RADIOS,
82 DB_TABLE_PLAYLISTS,
83 DB_TABLE_AUDIOBOOKS,
84 DB_TABLE_PODCASTS,
85 ):
86 try:
87 await database.execute(
88 f"ALTER TABLE {table} ADD COLUMN search_name TEXT DEFAULT '' NOT NULL"
89 )
90 await database.execute(
91 f"ALTER TABLE {table} ADD COLUMN search_sort_name TEXT DEFAULT '' NOT NULL"
92 )
93 except Exception as err:
94 if "duplicate column" not in str(err):
95 raise
96 # migrate all existing values
97 async for db_row in database.iter_items(table):
98 await database.update(
99 table,
100 {"item_id": db_row["item_id"]},
101 {
102 "search_name": create_safe_string(db_row["name"], True, True),
103 "search_sort_name": create_safe_string(db_row["sort_name"], True, True),
104 },
105 )
106
107 if prev_version <= 16:
108 # cleanup invalid release_date field in metadata
109 for table in (
110 DB_TABLE_TRACKS,
111 DB_TABLE_ALBUMS,
112 DB_TABLE_AUDIOBOOKS,
113 DB_TABLE_PODCASTS,
114 ):
115 async for db_row in database.iter_items(table):
116 if '"release_date":null' in db_row["metadata"]:
117 continue
118 metadata = json_loads(db_row["metadata"])
119 try:
120 datetime.fromisoformat(metadata["release_date"])
121 except KeyError, ValueError:
122 # this is not a valid date, so we set it to None
123 metadata["release_date"] = None
124 await database.update(
125 table,
126 {"item_id": db_row["item_id"]},
127 {
128 "metadata": serialize_to_json(metadata),
129 },
130 )
131
132 if prev_version <= 17:
133 # migrate triggers to auto update timestamps
134 # it had an error in the previous version where it was not created
135 for db_table in (
136 "artists",
137 "albums",
138 "tracks",
139 "playlists",
140 "radios",
141 "audiobooks",
142 "podcasts",
143 ):
144 await database.execute(f"DROP TRIGGER IF EXISTS update_{db_table}_timestamp;")
145
146 if prev_version <= 18:
147 # add in_library column to provider_mappings table
148 await database.execute(
149 f"ALTER TABLE {DB_TABLE_PROVIDER_MAPPINGS} ADD COLUMN in_library "
150 "BOOLEAN NOT NULL DEFAULT 0;"
151 )
152 # migrate existing entries in provider_mappings which are filesystem
153 await database.execute(
154 f"UPDATE {DB_TABLE_PROVIDER_MAPPINGS} SET in_library = 1 "
155 "WHERE provider_domain in ('filesystem_local', 'filesystem_smb');"
156 )
157
158 if prev_version <= 20:
159 # drop column cache_checksum from playlists table
160 # this is no longer used and is a leftover from previous designs
161 try:
162 await database.execute(f"ALTER TABLE {DB_TABLE_PLAYLISTS} DROP COLUMN cache_checksum")
163 except Exception as err:
164 if "no such column" not in str(err):
165 raise
166
167 if prev_version <= 21:
168 # drop table for smart fades analysis - it will be recreated with needed columns
169 await database.execute("DROP TABLE IF EXISTS smart_fades_analysis")
170 await create_tables()
171
172 if prev_version <= 22:
173 # add userid column to playlog table
174 try:
175 await database.execute(f"ALTER TABLE {DB_TABLE_PLAYLOG} ADD COLUMN userid TEXT")
176 except Exception as err:
177 if "duplicate column" not in str(err):
178 raise
179 # Note: SQLite doesn't support modifying constraints directly
180 # The UNIQUE constraint will be updated when the table is recreated
181 # For now, we'll keep the old constraint and add a new one via unique index
182 try:
183 await database.execute(f"DROP INDEX IF EXISTS {DB_TABLE_PLAYLOG}_unique_idx")
184 await database.execute(
185 f"CREATE UNIQUE INDEX {DB_TABLE_PLAYLOG}_unique_idx "
186 f"ON {DB_TABLE_PLAYLOG}(item_id,provider,media_type,userid)"
187 )
188 except Exception as err:
189 # If we can't create the index due to duplicate entries, log and continue
190 logger.warning("Could not create unique index on playlog: %s", err)
191
192 if prev_version <= 23:
193 # add is_unique column to provider_mappings table
194 try:
195 await database.execute(
196 f"ALTER TABLE {DB_TABLE_PROVIDER_MAPPINGS} ADD COLUMN is_unique BOOLEAN"
197 )
198 except Exception as err:
199 if "duplicate column" not in str(err):
200 raise
201
202 if prev_version <= 24:
203 # add queue_id and user_initiated columns to playlog table
204 try:
205 await database.execute(f"ALTER TABLE {DB_TABLE_PLAYLOG} ADD COLUMN queue_id TEXT")
206 except Exception as err:
207 if "duplicate column" not in str(err):
208 raise
209 try:
210 await database.execute(
211 f"ALTER TABLE {DB_TABLE_PLAYLOG} "
212 "ADD COLUMN user_initiated BOOLEAN NOT NULL DEFAULT 1"
213 )
214 except Exception as err:
215 if "duplicate column" not in str(err):
216 raise
217
218 if prev_version <= 26:
219 # force in_library=True for provider mappings from non-streaming providers
220 # streaming providers will be automatically added to library when synced
221 await database.execute(
222 f"UPDATE {DB_TABLE_PROVIDER_MAPPINGS} SET in_library = 1 "
223 "WHERE provider_domain NOT IN "
224 "('spotify', 'deezer', 'tidal', 'qobuz', 'apple_music', 'ytmusic');"
225 )
226 # also set in_library=True for all radio items
227 await database.execute(
228 f"UPDATE {DB_TABLE_PROVIDER_MAPPINGS} SET in_library = 1 WHERE media_type = 'radio';"
229 )
230 # remove invalid playlist provider mappings for playlists which are not in library
231 await database.execute(
232 f"DELETE FROM {DB_TABLE_PROVIDER_MAPPINGS} "
233 "WHERE media_type = 'playlist' AND in_library = 0;"
234 )
235
236 if prev_version <= 27:
237 # set streaming provider mappings to in_library=True, but only for items
238 # that do not already have any mapping with in_library=True
239 # (to avoid overwriting explicit values in multi-instance setups)
240 await database.execute(
241 f"UPDATE {DB_TABLE_PROVIDER_MAPPINGS} SET in_library = 1 "
242 "WHERE provider_domain NOT IN "
243 "('filesystem_local', 'builtin', 'test', 'jellyfin', 'emby', "
244 "'plex', 'opensubsonic', 'audiobookshelf', 'gpodder', 'podcastfeed') "
245 "AND NOT EXISTS ("
246 f"SELECT 1 FROM {DB_TABLE_PROVIDER_MAPPINGS} AS pm2 "
247 f"WHERE pm2.media_type = {DB_TABLE_PROVIDER_MAPPINGS}.media_type "
248 f"AND pm2.item_id = {DB_TABLE_PROVIDER_MAPPINGS}.item_id "
249 "AND pm2.in_library = 1)"
250 )
251
252 if prev_version <= 28:
253 # create genre/alias tables
254 await create_tables()
255
256 # Use raw aiosqlite connection for bulk operations.
257 db = database._db
258
259 empty_metadata = serialize_to_json({})
260
261 def _normalize_name(raw_name: str) -> tuple[str, str, str, str]:
262 name = raw_name.strip()
263 sort_name = name
264 search_name = create_safe_string(name, True, True)
265 search_sort_name = create_safe_string(sort_name or "", True, True)
266 return name, sort_name, search_name, search_sort_name
267
268 genre_cache: dict[str, int] = {}
269
270 genre_insert_sql = (
271 f"INSERT OR IGNORE INTO {DB_TABLE_GENRES}"
272 "(name, sort_name, translation_key, description, favorite, "
273 "metadata, genre_aliases, play_count, last_played, "
274 "search_name, search_sort_name) "
275 "VALUES (?, ?, ?, NULL, 0, ?, ?, 0, 0, ?, ?)"
276 )
277 genre_select_sql = f"SELECT item_id FROM {DB_TABLE_GENRES} WHERE search_name = ?"
278
279 async def _get_or_create_genre(
280 raw_name: str,
281 aliases: list[str] | None = None,
282 translation_key: str | None = None,
283 ) -> int:
284 name, sort_name, search_name, search_sort_name = _normalize_name(raw_name)
285 if not search_name:
286 return 0
287 if search_name in genre_cache:
288 return genre_cache[search_name]
289 aliases_json = serialize_to_json(aliases or [name])
290 icon_metadata = GenreController._get_genre_icon_metadata(translation_key)
291 metadata_json = (
292 serialize_to_json(icon_metadata.to_dict()) if icon_metadata else empty_metadata
293 )
294 row_id = await db.execute_insert(
295 genre_insert_sql,
296 (
297 name,
298 sort_name,
299 translation_key,
300 metadata_json,
301 aliases_json,
302 search_name,
303 search_sort_name,
304 ),
305 )
306 if row_id and row_id[0]:
307 genre_cache[search_name] = row_id[0]
308 return cast("int", row_id[0])
309 async with db.execute(genre_select_sql, (search_name,)) as cursor:
310 row = await cursor.fetchone()
311 if row:
312 genre_cache[search_name] = row[0]
313 return cast("int", row[0])
314 return 0
315
316 # Phase 1: Seed DEFAULT_GENRE_MAPPING â create genres with aliases.
317 # Build n:n lookup: normalized alias name -> list of genre_ids.
318 # One alias can belong to multiple genres (e.g. "funk" is both
319 # a standalone genre and an alias of Soul/R&B).
320 alias_to_genre: dict[str, list[int]] = {}
321 for entry in DEFAULT_GENRE_MAPPING:
322 genre_name = entry.get("genre")
323 if not genre_name:
324 continue
325 all_aliases = [genre_name, *entry.get("aliases", [])]
326 genre_id = await _get_or_create_genre(
327 genre_name,
328 aliases=all_aliases,
329 translation_key=entry.get("translation_key"),
330 )
331 if not genre_id:
332 continue
333 for alias in all_aliases:
334 norm = create_safe_string(alias.strip(), True, True)
335 if norm:
336 alias_to_genre.setdefault(norm, [])
337 if genre_id not in alias_to_genre[norm]:
338 alias_to_genre[norm].append(genre_id)
339 await db.commit()
340
341 # Phase 2: Discover unique genre names from all media items,
342 # create genres for unknown names, then bulk-insert mappings.
343 media_tables = (
344 (DB_TABLE_TRACKS, MediaType.TRACK),
345 (DB_TABLE_ALBUMS, MediaType.ALBUM),
346 (DB_TABLE_ARTISTS, MediaType.ARTIST),
347 (DB_TABLE_PLAYLISTS, MediaType.PLAYLIST),
348 (DB_TABLE_RADIOS, MediaType.RADIO),
349 (DB_TABLE_AUDIOBOOKS, MediaType.AUDIOBOOK),
350 (DB_TABLE_PODCASTS, MediaType.PODCAST),
351 )
352
353 # 2a: Extract all unique raw genre names from metadata
354 union_parts = [
355 f"SELECT DISTINCT TRIM(g.value) AS raw_name "
356 f"FROM {table}, json_each(json_extract({table}.metadata, '$.genres')) AS g "
357 f"WHERE json_extract({table}.metadata, '$.genres') IS NOT NULL "
358 f"AND json_extract({table}.metadata, '$.genres') != '[]'"
359 for table, _ in media_tables
360 ]
361 unique_names_sql = " UNION ".join(union_parts)
362 logger.debug("Genre migration - unique names query:\n%s", unique_names_sql)
363 async with db.execute(unique_names_sql) as cursor:
364 unique_raw_names = [row[0] for row in await cursor.fetchall() if row[0]]
365 logger.info("Genre migration - discovered %d unique genre names", len(unique_raw_names))
366
367 # 2b: Ensure genres exist for all discovered names.
368 # Names already covered by Phase 1 aliases just reuse those genre(s).
369 # New names get their own genre. One alias can map to multiple genres (n:n).
370 raw_name_to_genres: dict[str, list[int]] = {}
371 for raw_name in unique_raw_names:
372 norm = create_safe_string(raw_name.strip(), True, True)
373 if not norm:
374 continue
375 if norm in alias_to_genre:
376 raw_name_to_genres[raw_name] = list(alias_to_genre[norm])
377 logger.debug(
378 "Genre migration - resolved %r -> genre_ids %s (alias match)",
379 raw_name,
380 alias_to_genre[norm],
381 )
382 else:
383 genre_id = await _get_or_create_genre(raw_name)
384 if genre_id:
385 raw_name_to_genres[raw_name] = [genre_id]
386 alias_to_genre[norm] = [genre_id]
387 logger.debug(
388 "Genre migration - resolved %r -> genre_id %d (new genre)",
389 raw_name,
390 genre_id,
391 )
392 await db.commit()
393 logger.info("Genre migration - resolved %d unique genre names", len(raw_name_to_genres))
394
395 # 2c: Add discovered raw names as aliases to their resolved genres
396 # so that frontend searches by raw name find the parent genre.
397 genre_new_aliases: dict[int, list[str]] = {}
398 for raw_name, gids in raw_name_to_genres.items():
399 for gid in gids:
400 genre_new_aliases.setdefault(gid, []).append(raw_name)
401 for gid, new_aliases in genre_new_aliases.items():
402 async with db.execute(
403 f"SELECT genre_aliases FROM {DB_TABLE_GENRES} WHERE item_id = :gid",
404 {"gid": gid},
405 ) as cursor:
406 row = await cursor.fetchone()
407 if not row:
408 continue
409 existing = json_loads(row[0]) if row[0] else []
410 existing_norms = {create_safe_string(a, True, True) for a in existing}
411 to_add = [
412 a for a in new_aliases if create_safe_string(a, True, True) not in existing_norms
413 ]
414 if to_add:
415 merged = existing + to_add
416 await db.execute(
417 f"UPDATE {DB_TABLE_GENRES} SET genre_aliases = :aliases WHERE item_id = :gid",
418 {"aliases": json_dumps(merged), "gid": gid},
419 )
420 await db.commit()
421
422 # 2d: Build CTE with (raw_name, genre_id) and do one INSERT per
423 # media type using json_each to map media items directly to genres.
424 # One raw_name can map to multiple genre_ids (n:n).
425 if raw_name_to_genres:
426 cte_values = ", ".join(
427 f"(LOWER('{name.replace(chr(39), chr(39) + chr(39))}'), {gid})"
428 for name, gids in raw_name_to_genres.items()
429 for gid in gids
430 )
431 cte = f"WITH genre_lookup(raw_name, genre_id) AS (VALUES {cte_values})"
432
433 for table, media_type in media_tables:
434 full_query = (
435 f"{cte} INSERT OR REPLACE INTO {DB_TABLE_GENRE_MEDIA_ITEM_MAPPING}"
436 f"(genre_id, media_id, media_type, alias) "
437 f"SELECT gl.genre_id, {table}.item_id, "
438 f"'{media_type.value}', TRIM(g.value) "
439 f"FROM {table}, "
440 f"json_each(json_extract({table}.metadata, '$.genres')) AS g "
441 f"JOIN genre_lookup gl ON gl.raw_name = LOWER(TRIM(g.value)) "
442 f"WHERE json_extract({table}.metadata, '$.genres') IS NOT NULL "
443 f"AND json_extract({table}.metadata, '$.genres') != '[]'"
444 )
445 logger.debug("Genre migration - %s query:\n%s", media_type.value, full_query)
446 await db.execute(full_query)
447 await db.commit()
448
449 if prev_version <= 29:
450 # Smart fades analyses were previously computed on silence-stripped audio,
451 # so beat timestamps are misaligned with the unstripped buffers now passed
452 # to the crossfade mixer. Truncate the table so all analyses are re-computed.
453 with suppress(Exception):
454 await database.execute("DELETE FROM smart_fades_analysis")
455
456 if prev_version <= 30:
457 # add supported_mediatypes column to playlist table, and make {MediaType.TRACK},
458 # i.e. ["track"] the default, as this was the only media type supported.
459 try:
460 await database.execute(
461 f"ALTER TABLE {DB_TABLE_PLAYLISTS} ADD COLUMN supported_mediatypes"
462 " json DEFAULT '[\"track\"]' NOT NULL"
463 )
464 except Exception as err:
465 if "duplicate column" not in str(err):
466 raise
467
468 if prev_version <= 31:
469 # create the genre_media_item_exclusion table (new in schema 31)
470 await database.execute(
471 f"""
472 CREATE TABLE IF NOT EXISTS {DB_TABLE_GENRE_MEDIA_ITEM_EXCLUSION}(
473 [genre_id] INTEGER NOT NULL,
474 [media_id] INTEGER NOT NULL,
475 [media_type] TEXT NOT NULL,
476 FOREIGN KEY([genre_id]) REFERENCES [genres]([item_id]),
477 UNIQUE(genre_id, media_id, media_type)
478 );"""
479 )
480 await database.execute(
481 f"CREATE INDEX IF NOT EXISTS {DB_TABLE_GENRE_MEDIA_ITEM_EXCLUSION}_media_idx "
482 f"on {DB_TABLE_GENRE_MEDIA_ITEM_EXCLUSION}(media_id,media_type);"
483 )
484 await database.execute(
485 f"CREATE INDEX IF NOT EXISTS {DB_TABLE_GENRE_MEDIA_ITEM_EXCLUSION}_genre_idx "
486 f"on {DB_TABLE_GENRE_MEDIA_ITEM_EXCLUSION}(genre_id);"
487 )
488
489 if prev_version <= 32:
490 # recreate genre_media_item_mapping with nullable alias and is_derived column
491 # (new in schema 33 to support propagated genre mappings from tracks)
492 await database.execute(
493 f"ALTER TABLE {DB_TABLE_GENRE_MEDIA_ITEM_MAPPING} "
494 f"RENAME TO {DB_TABLE_GENRE_MEDIA_ITEM_MAPPING}_old;"
495 )
496 await database.execute(
497 f"""
498 CREATE TABLE {DB_TABLE_GENRE_MEDIA_ITEM_MAPPING}(
499 [genre_id] INTEGER NOT NULL,
500 [media_id] INTEGER NOT NULL,
501 [media_type] TEXT NOT NULL,
502 [alias] TEXT,
503 [is_derived] BOOLEAN NOT NULL DEFAULT 0,
504 FOREIGN KEY([genre_id]) REFERENCES [genres]([item_id]),
505 UNIQUE(genre_id, media_id, media_type)
506 );"""
507 )
508 await database.execute(
509 f"INSERT INTO {DB_TABLE_GENRE_MEDIA_ITEM_MAPPING} "
510 f"(genre_id, media_id, media_type, alias) "
511 f"SELECT genre_id, media_id, media_type, alias "
512 f"FROM {DB_TABLE_GENRE_MEDIA_ITEM_MAPPING}_old;"
513 )
514 await database.execute(f"DROP TABLE {DB_TABLE_GENRE_MEDIA_ITEM_MAPPING}_old;")
515
516 if prev_version <= 33:
517 # add is_excluded column to genres table (new in schema 34)
518 try:
519 await database.execute(
520 f"ALTER TABLE {DB_TABLE_GENRES} "
521 "ADD COLUMN [is_excluded] BOOLEAN NOT NULL DEFAULT 0;"
522 )
523 except Exception as err:
524 if "duplicate column" not in str(err):
525 raise
526 # drop the old genre_global_exclusion table (replaced by is_excluded column)
527 await database.execute("DROP TABLE IF EXISTS genre_global_exclusion;")
528 # add is_default column to genres table (new in schema 34)
529 try:
530 await database.execute(
531 f"ALTER TABLE {DB_TABLE_GENRES} ADD COLUMN [is_default] BOOLEAN NOT NULL DEFAULT 0;"
532 )
533 except Exception as err:
534 if "duplicate column" not in str(err):
535 raise
536 # mark all existing genres with a translation_key as default
537 await database.execute(
538 f"UPDATE {DB_TABLE_GENRES} SET is_default = 1 WHERE translation_key IS NOT NULL;"
539 )
540 if prev_version <= 34:
541 # fix filesystem playlists missing in_library flag
542 await database.execute(
543 f"UPDATE {DB_TABLE_PROVIDER_MAPPINGS} SET in_library = 1 "
544 "WHERE media_type = 'playlist' "
545 "AND provider_domain IN ('filesystem_local', 'filesystem_smb', 'filesystem_nfs');"
546 )
547
548 if prev_version <= 35:
549 # add is_dynamic column to playlist table
550 try:
551 await database.execute(
552 f"ALTER TABLE {DB_TABLE_PLAYLISTS} ADD COLUMN is_dynamic BOOLEAN NOT NULL DEFAULT 0"
553 )
554 except Exception as err:
555 if "duplicate column" not in str(err):
556 raise
557 # backfill is_dynamic for existing Apple Music station playlists
558 await database.execute(
559 f"UPDATE {DB_TABLE_PLAYLISTS} SET is_dynamic = 1 "
560 f"WHERE item_id IN ("
561 f" SELECT item_id FROM {DB_TABLE_PROVIDER_MAPPINGS} "
562 f" WHERE media_type = 'playlist' "
563 f" AND provider_domain = 'apple_music' "
564 f" AND provider_item_id LIKE 'ra.%'"
565 f")"
566 )
567
568 if prev_version <= 36:
569 # drop legacy smart_fades_analysis table â analysis is now handled by
570 # audio analysis providers and stored in the audio_analysis table.
571 await database.execute("DROP TABLE IF EXISTS smart_fades_analysis")
572
573 if prev_version <= 37:
574 # purge unreliable loudness measurements persisted by earlier versions
575 # (ebur128 reports ~-70 LUFS on near-silence / early-cancelled streams,
576 # which caused huge gain corrections on subsequent plays)
577 await database.execute(
578 f"DELETE FROM {DB_TABLE_LOUDNESS_MEASUREMENTS} "
579 f"WHERE loudness <= {LOUDNESS_MEASUREMENT_MIN_LUFS}"
580 )
581 await database.execute(
582 f"UPDATE {DB_TABLE_LOUDNESS_MEASUREMENTS} "
583 f"SET loudness_album = NULL "
584 f"WHERE loudness_album <= {LOUDNESS_MEASUREMENT_MIN_LUFS}"
585 )
586
587 if prev_version <= 38:
588 # stable 2.8.9 shipped schema v38 without the smart_fades_analysis drop
589 # (that drop is gated at <= 36, which v38 users leapfrog). re-run it here
590 # so stable->2.9.0 upgraders also lose the legacy table. idempotent: a
591 # no-op for beta users who already dropped it at v36.
592 await database.execute("DROP TABLE IF EXISTS smart_fades_analysis")
593 # migrate loudness measurements to the unified audio_analysis table
594 # under the new builtin loudness_analysis provider, then drop the
595 # legacy table. album loudness rides along when present.
596 await database.execute(
597 f"INSERT OR IGNORE INTO {DB_TABLE_AUDIO_ANALYSIS} "
598 f"(media_type, item_id, provider, aa_provider_domain, "
599 f" analysis_data, analysis_version) "
600 f"SELECT media_type, item_id, provider, 'loudness_analysis', "
601 f" json_object("
602 f" 'loudness_integrated', loudness, "
603 f" 'loudness_album', loudness_album"
604 f" ), 1 "
605 f"FROM {DB_TABLE_LOUDNESS_MEASUREMENTS} "
606 f"WHERE loudness IS NOT NULL "
607 f" AND loudness > {LOUDNESS_MEASUREMENT_MIN_LUFS}"
608 )
609 await database.execute(f"DROP TABLE IF EXISTS {DB_TABLE_LOUDNESS_MEASUREMENTS}")
610
611 if prev_version <= 39:
612 # add is_manual column to genre_media_item_mapping
613 try:
614 await database.execute(
615 f"ALTER TABLE {DB_TABLE_GENRE_MEDIA_ITEM_MAPPING} "
616 "ADD COLUMN [is_manual] BOOLEAN NOT NULL DEFAULT 0;"
617 )
618 except Exception as err:
619 if "duplicate column" not in str(err):
620 raise
621
622 if prev_version <= 40:
623 # genre icons were previously stored with an absolute filesystem path to
624 # the builtin SVG, which is install-location dependent. after a runtime
625 # upgrade or relocation (e.g. the python3.13 -> python3.14 site-packages
626 # move) that path no longer existed, so genre icons 404'd via imageproxy.
627 # rewrite them to the install-independent "<GENRE_ICONS_DIR_NAME>/<file>"
628 # form; the builtin provider resolves that against RESOURCES_DIR at serve
629 # time.
630 genre_dir_marker = f"/resources/{GENRE_ICONS_DIR_NAME}/"
631 async for db_row in database.iter_items(DB_TABLE_GENRES):
632 raw_metadata = db_row["metadata"]
633 if not raw_metadata:
634 continue
635 metadata = json_loads(raw_metadata)
636 images = metadata.get("images")
637 if not images:
638 continue
639 changed = False
640 for image in images:
641 path = image.get("path")
642 if not (image.get("provider") == "builtin" and isinstance(path, str)):
643 continue
644 norm = path.replace("\\", "/")
645 if genre_dir_marker in norm and norm.endswith(".svg"):
646 image["path"] = f"{GENRE_ICONS_DIR_NAME}/{norm.rsplit('/', 1)[-1]}"
647 changed = True
648 if changed:
649 await database.update(
650 DB_TABLE_GENRES,
651 {"item_id": db_row["item_id"]},
652 {"metadata": serialize_to_json(metadata)},
653 )
654
655 if prev_version <= 41:
656 # add playback_speed column to playlog (per-item speed for audiobooks/episodes)
657 try:
658 await database.execute(
659 f"ALTER TABLE {DB_TABLE_PLAYLOG} "
660 "ADD COLUMN playback_speed REAL NOT NULL DEFAULT 1.0"
661 )
662 except Exception as err:
663 if "duplicate column" not in str(err):
664 raise
665
666 if prev_version <= 42:
667 # add translation_key/translation_params columns to the playlist table so localizable
668 # builtin/provider playlist names (incl. parameterized ones like Spotify's per-account
669 # "Liked Songs") survive the library round-trip; existing rows backfill on the next sync.
670 for column in ("[translation_key] TEXT", "[translation_params] json"):
671 try:
672 await database.execute(f"ALTER TABLE {DB_TABLE_PLAYLISTS} ADD COLUMN {column}")
673 except Exception as err:
674 if "duplicate column" not in str(err):
675 raise
676
677 if prev_version <= 43:
678 # add content_type column to the genres table to namespace spoken-word taxonomies
679 # (podcast/audiobook) apart from music genres. NULL = music/general; existing rows
680 # stay NULL so nothing re-keys.
681 try:
682 await database.execute(f"ALTER TABLE {DB_TABLE_GENRES} ADD COLUMN [content_type] TEXT")
683 except Exception as err:
684 if "duplicate column" not in str(err):
685 raise
686
687 if prev_version <= 44:
688 # add artist_type column to artist table, and make
689 # artist_type=ARTIST_TYPE.SINGER the default, as this was the only artist type supported
690 try:
691 await database.execute(
692 f"ALTER TABLE {DB_TABLE_ARTISTS} ADD COLUMN artist_type TEXT DEFAULT 'singer' NOT NULL"
693 )
694 except Exception as err:
695 if "duplicate column" not in str(err):
696 raise
697
698 if prev_version <= 46:
699 # add artists column to playlog (lightweight artist mappings for track rows) so
700 # recency matching can recognize the same song across different releases/providers
701 try:
702 await database.execute(f"ALTER TABLE {DB_TABLE_PLAYLOG} ADD COLUMN artists json")
703 except Exception as err:
704 if "duplicate column" not in str(err):
705 raise
706
707 if prev_version <= 48:
708 # databases from before the userid column still carry the original inline
709 # UNIQUE(item_id, provider, media_type) constraint, which ALTER TABLE could not
710 # remove. It collides with the per-user upsert (ON CONFLICT on 4 columns) and
711 # raises IntegrityError on every replay of an item. SQLite can only drop an
712 # inline constraint by rebuilding the table.
713 stale_unique = False
714 for index in await database.get_rows_from_query(
715 f"PRAGMA index_list({DB_TABLE_PLAYLOG})", limit=0
716 ):
717 if not index["unique"]:
718 continue
719 index_columns = {
720 column["name"]
721 for column in await database.get_rows_from_query(
722 f"PRAGMA index_info({index['name']})", limit=0
723 )
724 }
725 if "userid" not in index_columns:
726 stale_unique = True
727 break
728 if stale_unique:
729 logger.info("Rebuilding playlog table to update its unique constraint")
730 await database.execute(
731 f"ALTER TABLE {DB_TABLE_PLAYLOG} RENAME TO {DB_TABLE_PLAYLOG}_old"
732 )
733 await database.execute(
734 f"""CREATE TABLE {DB_TABLE_PLAYLOG}(
735 [id] INTEGER PRIMARY KEY AUTOINCREMENT,
736 [item_id] TEXT NOT NULL,
737 [provider] TEXT NOT NULL,
738 [media_type] TEXT NOT NULL,
739 [name] TEXT NOT NULL,
740 [image] json,
741 [artists] json,
742 [timestamp] INTEGER DEFAULT 0,
743 [fully_played] BOOLEAN,
744 [seconds_played] INTEGER,
745 [userid] TEXT NOT NULL,
746 [queue_id] TEXT,
747 [user_initiated] BOOLEAN NOT NULL DEFAULT 1,
748 [playback_speed] REAL NOT NULL DEFAULT 1.0,
749 UNIQUE(item_id, provider, media_type, userid));"""
750 )
751 # rows from before the userid column existed have no owner and cannot be
752 # kept under the NOT NULL schema
753 await database.execute(
754 f"INSERT INTO {DB_TABLE_PLAYLOG} "
755 "(id, item_id, provider, media_type, name, image, artists, timestamp, "
756 "fully_played, seconds_played, userid, queue_id, user_initiated, "
757 "playback_speed) "
758 "SELECT id, item_id, provider, media_type, name, image, artists, timestamp, "
759 "fully_played, seconds_played, userid, queue_id, user_initiated, "
760 f"playback_speed FROM {DB_TABLE_PLAYLOG}_old WHERE userid IS NOT NULL"
761 )
762 await database.execute(f"DROP TABLE {DB_TABLE_PLAYLOG}_old")
763
764 if prev_version <= 50:
765 # external id matching moved from a (unindexable) LIKE scan on the external_ids
766 # JSON column to the new external_id_lookup table, which is now the single source
767 # of truth: backfill the lookup rows from the external_ids JSON of all media item
768 # tables, then drop that column and its old index (which could never be used by
769 # the LIKE scan anyway). The backfill is idempotent, so v50 databases (which
770 # already have a populated lookup table) simply get the column drop.
771 for media_type, table in (
772 (MediaType.ARTIST, DB_TABLE_ARTISTS),
773 (MediaType.ALBUM, DB_TABLE_ALBUMS),
774 (MediaType.TRACK, DB_TABLE_TRACKS),
775 (MediaType.PLAYLIST, DB_TABLE_PLAYLISTS),
776 (MediaType.RADIO, DB_TABLE_RADIOS),
777 (MediaType.AUDIOBOOK, DB_TABLE_AUDIOBOOKS),
778 (MediaType.PODCAST, DB_TABLE_PODCASTS),
779 (MediaType.GENRE, DB_TABLE_GENRES),
780 ):
781 # tables (re)created by an earlier migration step already use the current
782 # schema (no external_ids column) and have nothing to backfill
783 table_columns = {
784 column["name"]
785 for column in await database.get_rows_from_query(
786 f"PRAGMA table_info({table})", limit=0
787 )
788 }
789 if "external_ids" not in table_columns:
790 continue
791 # the column must not be indexed for DROP COLUMN to succeed
792 await database.execute(f"DROP INDEX IF EXISTS {table}_external_ids_idx")
793 # external_ids is a JSON array of [type, value] pairs; the NOCASE unique
794 # index may collapse case-variants of the same id, hence OR IGNORE
795 await database.execute(
796 f"INSERT OR IGNORE INTO {DB_TABLE_EXTERNAL_ID_LOOKUP} "
797 "(media_type, external_id_type, external_id, item_id) "
798 f"SELECT '{media_type.value}', json_extract(ext.value, '$[0]'), "
799 f"json_extract(ext.value, '$[1]'), {table}.item_id "
800 f"FROM {table}, json_each({table}.external_ids) AS ext "
801 "WHERE json_extract(ext.value, '$[0]') IS NOT NULL "
802 "AND json_extract(ext.value, '$[1]') IS NOT NULL"
803 )
804 await database.execute(f"ALTER TABLE {table} DROP COLUMN external_ids")
805
806 if prev_version <= 52:
807 audio_analysis_table_exists = await database.get_rows_from_query(
808 "SELECT 1 FROM sqlite_master WHERE type = 'table' AND name = :table_name",
809 {"table_name": DB_TABLE_AUDIO_ANALYSIS},
810 limit=1,
811 )
812 if audio_analysis_table_exists:
813 # SQLite does not guarantee WHERE-term evaluation order, so a bare
814 # json_valid() term cannot reliably shield json_each()/json_type()
815 # from raising on malformed rows - guard their input directly instead.
816 # The json() wrapper is required: the JSON subtype does not reliably
817 # survive the scalar-subquery boundary, so without it the rebuilt
818 # array would be stored as an escaped string on some SQLite versions.
819 result = await database.execute(
820 f"""UPDATE {DB_TABLE_AUDIO_ANALYSIS} AS aa
821 SET analysis_data = json_replace(
822 aa.analysis_data,
823 '$.spectral_centroid',
824 json((
825 SELECT json_group_array(
826 CASE WHEN centroid.type = 'null'
827 THEN 0.0 ELSE centroid.value END
828 )
829 FROM json_each(
830 aa.analysis_data, '$.spectral_centroid'
831 ) AS centroid
832 ))
833 )
834 WHERE aa.aa_provider_domain = :aa_provider_domain
835 AND aa.analysis_data LIKE '%null%'
836 AND json_type(
837 CASE WHEN json_valid(aa.analysis_data)
838 THEN aa.analysis_data END,
839 '$.spectral_centroid'
840 ) = 'array'
841 AND EXISTS (
842 SELECT 1
843 FROM json_each(
844 CASE WHEN json_valid(aa.analysis_data)
845 THEN aa.analysis_data END,
846 '$.spectral_centroid'
847 ) AS centroid
848 WHERE centroid.type = 'null'
849 )""",
850 {"aa_provider_domain": "smart_fades"},
851 )
852 if result.rowcount:
853 logger.info(
854 "Repaired null spectral centroid values in %d Smart Fades "
855 "audio analysis row(s)",
856 result.rowcount,
857 )
858
859 if prev_version <= 53:
860 # normalize stored synced lyrics: strip LRC ID tags and expand multi-timestamp
861 # (repeating) lines into one line per timestamp
862 tracks_columns = {
863 x["name"]
864 for x in await database.get_rows_from_query(
865 f"PRAGMA table_info({DB_TABLE_TRACKS})", limit=0
866 )
867 }
868 repaired_lyrics_rows = 0
869 if "metadata" in tracks_columns:
870 # guard against (test) databases with stand-in tables
871 async for db_row in database.iter_items(DB_TABLE_TRACKS):
872 if not db_row["metadata"] or '"lrc_lyrics"' not in db_row["metadata"]:
873 continue
874 try:
875 metadata = json_loads(db_row["metadata"])
876 except ValueError:
877 # corrupt metadata rows are handled elsewhere (diagnostics), skip here
878 continue
879 lrc_lyrics = metadata.get("lrc_lyrics")
880 if not isinstance(lrc_lyrics, str):
881 continue
882 normalized = normalize_lrc_lyrics(lrc_lyrics)
883 if normalized == lrc_lyrics:
884 continue
885 metadata["lrc_lyrics"] = normalized
886 await database.update(
887 DB_TABLE_TRACKS,
888 {"item_id": db_row["item_id"]},
889 {"metadata": serialize_to_json(metadata)},
890 )
891 repaired_lyrics_rows += 1
892 if repaired_lyrics_rows:
893 logger.info("Normalized synced lyrics of %d track(s)", repaired_lyrics_rows)
894
895 if prev_version <= 54:
896 # apple music blobstore artwork URLs are presigned with a ~24h expiry and are
897 # no longer persisted: replace the stored (long-dead) signed URLs with the
898 # stable artwork token the provider resolves to a fresh URL on demand
899 migrated_artwork_rows = 0
900 for table, media_type_value in (
901 (DB_TABLE_ARTISTS, "artist"),
902 (DB_TABLE_ALBUMS, "album"),
903 (DB_TABLE_TRACKS, "track"),
904 (DB_TABLE_PLAYLISTS, "playlist"),
905 ):
906 table_columns = {
907 x["name"]
908 for x in await database.get_rows_from_query(f"PRAGMA table_info({table})", limit=0)
909 }
910 if "metadata" not in table_columns:
911 # guard against (test) databases with stand-in tables
912 continue
913 # the (provider_instance, item_id) -> provider item id lookup needed to
914 # derive each item's artwork token from its apple music mapping
915 apple_item_ids = {
916 (row["item_id"], row["provider_instance"]): row["provider_item_id"]
917 for row in await database.get_rows_from_query(
918 f"SELECT item_id, provider_instance, provider_item_id "
919 f"FROM {DB_TABLE_PROVIDER_MAPPINGS} "
920 "WHERE media_type = :media_type AND provider_domain = 'apple_music'",
921 {"media_type": media_type_value},
922 limit=0,
923 )
924 }
925 async for db_row in database.iter_items(table):
926 if not db_row["metadata"] or "blobstore.apple.com" not in db_row["metadata"]:
927 continue
928 try:
929 metadata = json_loads(db_row["metadata"])
930 except ValueError:
931 # corrupt metadata rows are handled elsewhere (diagnostics), skip here
932 continue
933 images = metadata.get("images")
934 if not isinstance(images, list):
935 continue
936 migrated_images = []
937 changed = False
938 for image in images:
939 if not isinstance(image, dict) or "blobstore.apple.com" not in (
940 image.get("path") or ""
941 ):
942 migrated_images.append(image)
943 continue
944 changed = True
945 prov_item_id = apple_item_ids.get((db_row["item_id"], image.get("provider")))
946 if prov_item_id is None:
947 # no mapping left to resolve through; drop the dead url
948 continue
949 image["path"] = f"{media_type_value}/{prov_item_id}"
950 image["remotely_accessible"] = False
951 migrated_images.append(image)
952 if not changed:
953 continue
954 metadata["images"] = migrated_images
955 await database.update(
956 table,
957 {"item_id": db_row["item_id"]},
958 {"metadata": serialize_to_json(metadata)},
959 )
960 migrated_artwork_rows += 1
961 if migrated_artwork_rows:
962 logger.info(
963 "Migrated the Apple Music artwork of %d library item(s) to resolvable tokens",
964 migrated_artwork_rows,
965 )
966
967 if prev_version <= 55:
968 # drop the sound effect media type from the stored playlists: clients that do not
969 # know it yet refuse to parse a playlist that advertises it. Rewriting the rows
970 # here makes upgrading enough, instead of having to wait for the next library sync.
971 await database.execute(
972 f"UPDATE {DB_TABLE_PLAYLISTS} SET supported_mediatypes = json(("
973 "SELECT json_group_array(value) FROM json_each"
974 f"({DB_TABLE_PLAYLISTS}.supported_mediatypes) WHERE value != 'sound_effect'))"
975 " WHERE json_valid(supported_mediatypes)"
976 " AND supported_mediatypes LIKE '%sound_effect%'"
977 )
978
979 if prev_version <= 56:
980 # the stable branch numbers its schema versions independently of this one, so a
981 # stable database can report a version that leapfrogs steps it never ran: stable
982 # 41-43 never got the columns this branch adds at <= 41 and <= 42. Re-add them for
983 # every pre-57 database; the ALTERs are no-ops where the column already exists.
984 for table, column in (
985 (DB_TABLE_PLAYLISTS, "[translation_key] TEXT"),
986 (DB_TABLE_PLAYLISTS, "[translation_params] json"),
987 (DB_TABLE_PLAYLOG, "[playback_speed] REAL NOT NULL DEFAULT 1.0"),
988 ):
989 try:
990 await database.execute(f"ALTER TABLE {table} ADD COLUMN {column}")
991 except Exception as err:
992 if "duplicate column" not in str(err):
993 raise
994
995 if prev_version <= 57:
996 # add is_dynamic column to radio table
997 try:
998 await database.execute(
999 f"ALTER TABLE {DB_TABLE_RADIOS} ADD COLUMN is_dynamic BOOLEAN NOT NULL DEFAULT 0"
1000 )
1001 except Exception as err:
1002 if "duplicate column" not in str(err):
1003 raise
1004
1005 # NOTE: this genre restore runs after the <= 50 step on purpose: it inserts genres
1006 # with the current code/schema, so the external_ids column must be gone first.
1007 if prev_version <= 47:
1008 # seed the curated podcast & audiobook default genres into their namespaces so existing
1009 # installs get them on upgrade (music defaults already exist and are skipped), and
1010 # refresh 46/47-seeded genres so they pick up their (later added) icon metadata.
1011 # A partial restore is idempotent; failures here are non-fatal â defaults can be
1012 # restored later via the admin API rather than discarding the whole library.
1013 await database.commit()
1014 try:
1015 await mass.music.genres.restore_default_genres(full_restore=False)
1016 except Exception as err:
1017 logger.warning("Could not seed default podcast/audiobook genres: %s", err)
1018
1019 # (re)build the FTS search tables so they are in sync with the content tables;
1020 # this both populates them on first migration to the FTS-enabled schema and
1021 # repairs them after any migration that rewrote rows without the sync triggers active
1022 for table in MEDIA_ITEM_DB_TABLES:
1023 table_columns = {
1024 x["name"]
1025 for x in await database.get_rows_from_query(f"PRAGMA table_info({table})", limit=0)
1026 }
1027 if "search_name" not in table_columns:
1028 # guard against (test) databases with stand-in tables
1029 continue
1030 await database.execute(
1031 f"""CREATE VIRTUAL TABLE IF NOT EXISTS {table}_fts USING fts5(
1032 search_name,
1033 content='{table}',
1034 content_rowid='item_id',
1035 tokenize='trigram'
1036 );"""
1037 )
1038 await database.execute(f"INSERT INTO {table}_fts({table}_fts) VALUES('rebuild')")
1039
1040 # save changes
1041 await database.commit()
1042
1043 # always clear the cache after a db migration
1044 await mass.cache.clear()
1045