/
/
/
1"""Tests for the music library database migrations."""
2
3from __future__ import annotations
4
5import json
6from typing import TYPE_CHECKING
7from unittest.mock import AsyncMock, MagicMock
8
9import pytest
10from music_assistant_models.enums import ExternalID
11from music_assistant_models.errors import MusicAssistantError
12
13from music_assistant.constants import (
14 DB_TABLE_AUDIO_ANALYSIS,
15 DB_TABLE_EXTERNAL_ID_LOOKUP,
16 DB_TABLE_PLAYLOG,
17 DB_TABLE_SETTINGS,
18)
19from music_assistant.controllers.music import MusicController
20from music_assistant.controllers.music.migrations import migrate_database
21from music_assistant.helpers.database import DatabaseConnection
22from music_assistant.mass import MusicAssistant
23
24from .helpers import ISRC, create_track
25
26if TYPE_CHECKING:
27 from collections.abc import AsyncGenerator
28 from pathlib import Path
29
30MEDIA_TABLES = (
31 "artists",
32 "albums",
33 "tracks",
34 "playlists",
35 "radios",
36 "audiobooks",
37 "podcasts",
38 "genres",
39)
40
41
42@pytest.fixture
43async def database(tmp_path: Path) -> AsyncGenerator[DatabaseConnection]:
44 """Return an initialized DatabaseConnection backed by a temp file."""
45 db = DatabaseConnection(str(tmp_path / "library.db"))
46 await db.setup()
47 # minimal stand-ins for the tables that create_tables() would provide, so
48 # migration steps other than the one under test can run against this bare db
49 for table in MEDIA_TABLES:
50 await db.execute(
51 f"CREATE TABLE {table}([item_id] INTEGER PRIMARY KEY, "
52 "[external_ids] json NOT NULL DEFAULT '[]'"
53 # every playlists table at the schema versions under test carries this column
54 + (
55 ", [supported_mediatypes] json NOT NULL DEFAULT '[\"track\"]'"
56 if table == "playlists"
57 else ""
58 )
59 + ")"
60 )
61 await db.execute(
62 f"CREATE TABLE {DB_TABLE_EXTERNAL_ID_LOOKUP}([media_type] TEXT NOT NULL, "
63 "[external_id_type] TEXT NOT NULL, [external_id] TEXT NOT NULL, "
64 "[item_id] INTEGER NOT NULL)"
65 )
66 # tests that exercise a specific playlog layout replace this stand-in
67 await db.execute(
68 f"CREATE TABLE {DB_TABLE_PLAYLOG}([id] INTEGER PRIMARY KEY, [userid] TEXT NOT NULL, "
69 "[playback_speed] REAL NOT NULL DEFAULT 1.0, "
70 "UNIQUE(userid))"
71 )
72 await db.commit()
73 yield db
74 await db.close()
75
76
77# the exact upsert used by MusicController._credit_artist_plays - it targets the
78# 4-column unique constraint, so it raises IntegrityError on databases that still
79# carry the legacy 3-column constraint (issue #5754)
80PLAYLOG_UPSERT = (
81 f"INSERT INTO {DB_TABLE_PLAYLOG} "
82 "(item_id, provider, media_type, name, image, fully_played, "
83 "seconds_played, timestamp, queue_id, user_initiated, userid) "
84 "VALUES (:item_id, :provider, :media_type, :name, :image, :fully_played, "
85 ":seconds_played, :timestamp, :queue_id, :user_initiated, :userid) "
86 "ON CONFLICT(item_id, provider, media_type, userid) DO UPDATE SET "
87 "timestamp = excluded.timestamp"
88)
89
90
91def _playlog_entry(userid: str, timestamp: int = 100) -> dict[str, object]:
92 return {
93 "item_id": "1",
94 "provider": "library",
95 "media_type": "track",
96 "name": "Test Track",
97 "image": None,
98 "fully_played": 1,
99 "seconds_played": 195,
100 "timestamp": timestamp,
101 "queue_id": "queue1",
102 "user_initiated": 1,
103 "userid": userid,
104 }
105
106
107async def _create_legacy_playlog_table(database: DatabaseConnection) -> None:
108 """Create the playlog table as it exists on pre-userid installs."""
109 await database.execute(f"DROP TABLE {DB_TABLE_PLAYLOG}")
110 # original table layout (schema version <= 22) with the 3-column UNIQUE constraint
111 await database.execute(
112 f"""CREATE TABLE {DB_TABLE_PLAYLOG}(
113 [id] INTEGER PRIMARY KEY AUTOINCREMENT,
114 [item_id] TEXT NOT NULL,
115 [provider] TEXT NOT NULL,
116 [media_type] TEXT NOT NULL,
117 [name] TEXT NOT NULL,
118 [image] json,
119 [timestamp] INTEGER DEFAULT 0,
120 [fully_played] BOOLEAN,
121 [seconds_played] INTEGER,
122 UNIQUE(item_id, provider, media_type));"""
123 )
124 # columns + index added in-place by the later ALTER TABLE migrations
125 await database.execute(f"ALTER TABLE {DB_TABLE_PLAYLOG} ADD COLUMN userid TEXT")
126 await database.execute(f"ALTER TABLE {DB_TABLE_PLAYLOG} ADD COLUMN queue_id TEXT")
127 await database.execute(
128 f"ALTER TABLE {DB_TABLE_PLAYLOG} ADD COLUMN user_initiated BOOLEAN NOT NULL DEFAULT 1"
129 )
130 await database.execute(
131 f"ALTER TABLE {DB_TABLE_PLAYLOG} ADD COLUMN playback_speed REAL NOT NULL DEFAULT 1.0"
132 )
133 await database.execute(f"ALTER TABLE {DB_TABLE_PLAYLOG} ADD COLUMN artists json")
134 await database.execute(
135 f"CREATE UNIQUE INDEX {DB_TABLE_PLAYLOG}_unique_idx "
136 f"ON {DB_TABLE_PLAYLOG}(item_id,provider,media_type,userid)"
137 )
138 await database.commit()
139
140
141async def _table_columns(database: DatabaseConnection, table: str) -> set[str]:
142 """Return the column names of the given table."""
143 return {
144 column["name"]
145 for column in await database.get_rows_from_query(f"PRAGMA table_info({table})", limit=0)
146 }
147
148
149async def test_migration_rebuilds_playlog_with_stale_unique_constraint(
150 database: DatabaseConnection,
151) -> None:
152 """The legacy 3-column UNIQUE constraint on playlog is dropped by a table rebuild."""
153 await _create_legacy_playlog_table(database)
154 await database.execute(PLAYLOG_UPSERT, _playlog_entry("user1"))
155 # a legacy row from before the userid column existed
156 await database.execute(
157 f"INSERT INTO {DB_TABLE_PLAYLOG} (item_id, provider, media_type, name) "
158 "VALUES ('2', 'library', 'track', 'Legacy Track')"
159 )
160 await database.commit()
161
162 mass = MagicMock()
163 mass.cache.clear = AsyncMock()
164 await migrate_database(
165 mass,
166 database,
167 MagicMock(),
168 prev_version=48,
169 create_tables=AsyncMock(),
170 )
171
172 # replaying the same item for the same user updates the existing row in place
173 await database.execute(PLAYLOG_UPSERT, _playlog_entry("user1", timestamp=200))
174 # another user playing the same item gets their own row
175 await database.execute(PLAYLOG_UPSERT, _playlog_entry("user2"))
176 rows = await database.get_rows(DB_TABLE_PLAYLOG, {"item_id": "1"})
177 assert len(rows) == 2
178 user1_row = next(row for row in rows if row["userid"] == "user1")
179 assert user1_row["timestamp"] == 200
180 assert user1_row["name"] == "Test Track"
181 # legacy rows without a userid cannot be kept under the NOT NULL schema
182 assert not await database.get_rows(DB_TABLE_PLAYLOG, {"item_id": "2"})
183
184
185async def test_migration_leaves_correct_playlog_untouched(
186 database: DatabaseConnection,
187) -> None:
188 """A playlog table that already has the 4-column constraint is not rebuilt."""
189 await database.execute(f"DROP TABLE {DB_TABLE_PLAYLOG}")
190 await database.execute(
191 f"""CREATE TABLE {DB_TABLE_PLAYLOG}(
192 [id] INTEGER PRIMARY KEY AUTOINCREMENT,
193 [item_id] TEXT NOT NULL,
194 [provider] TEXT NOT NULL,
195 [media_type] TEXT NOT NULL,
196 [name] TEXT NOT NULL,
197 [image] json,
198 [artists] json,
199 [timestamp] INTEGER DEFAULT 0,
200 [fully_played] BOOLEAN,
201 [seconds_played] INTEGER,
202 [userid] TEXT NOT NULL,
203 [queue_id] TEXT,
204 [user_initiated] BOOLEAN NOT NULL DEFAULT 1,
205 [playback_speed] REAL NOT NULL DEFAULT 1.0,
206 UNIQUE(item_id, provider, media_type, userid));"""
207 )
208 await database.execute(PLAYLOG_UPSERT, _playlog_entry("user1"))
209 await database.commit()
210 table_sql_query = (
211 f"SELECT sql FROM sqlite_master WHERE type = 'table' AND name = '{DB_TABLE_PLAYLOG}'"
212 )
213 table_sql_before = (await database.get_rows_from_query(table_sql_query))[0]["sql"]
214
215 mass = MagicMock()
216 mass.cache.clear = AsyncMock()
217 await migrate_database(
218 mass,
219 database,
220 MagicMock(),
221 prev_version=48,
222 create_tables=AsyncMock(),
223 )
224
225 assert (await database.get_rows_from_query(table_sql_query))[0]["sql"] == table_sql_before
226 rows = await database.get_rows(DB_TABLE_PLAYLOG)
227 assert len(rows) == 1
228
229
230async def test_migrate_database_rejects_too_old_schema() -> None:
231 """Schema versions older than the minimum supported version are refused up-front."""
232 create_tables = AsyncMock()
233 with pytest.raises(MusicAssistantError):
234 await migrate_database(
235 MagicMock(), # mass
236 MagicMock(), # database
237 MagicMock(), # logger
238 prev_version=14,
239 create_tables=create_tables,
240 )
241 # the guard fires before any schema work happens
242 create_tables.assert_not_awaited()
243
244
245async def test_migrate_database_backfills_external_id_lookup(
246 mass_minimal: MusicAssistant,
247) -> None:
248 """A pre-lookup-table database with populated external_ids columns upgrades cleanly."""
249 # populate a fresh library database with a track carrying external ids
250 music = MusicController(mass_minimal)
251 mass_minimal.music = music
252 await music._setup_database()
253 library_track = await music.tracks.add_item_to_library(create_track("spotify_1", "track_abc"))
254 db_id = int(library_track.item_id)
255 # revert the database to its v49 state: no lookup table, external ids stored
256 # in an (indexed) external_ids JSON column on every media item table
257 await music.database.execute(f"DROP TABLE {DB_TABLE_EXTERNAL_ID_LOOKUP}")
258 for table in MEDIA_TABLES:
259 await music.database.execute(
260 f"ALTER TABLE {table} ADD COLUMN external_ids json NOT NULL DEFAULT '[]'"
261 )
262 for table in ("tracks", "artists"):
263 await music.database.execute(
264 f"CREATE INDEX IF NOT EXISTS {table}_external_ids_idx on {table}(external_ids)"
265 )
266 await music.database.execute(
267 "UPDATE tracks SET external_ids = :external_ids WHERE item_id = :item_id",
268 {"external_ids": f'[["isrc","{ISRC}"]]', "item_id": db_id},
269 )
270 await music.database.insert_or_replace(
271 DB_TABLE_SETTINGS, {"key": "version", "value": "49", "type": "str"}
272 )
273 await music.database.commit()
274 await music.database.close()
275
276 # setting up the database again triggers the migration
277 mass_minimal.cache.clear = AsyncMock() # type: ignore[method-assign]
278 await music._setup_database()
279
280 # the lookup table is backfilled from the external_ids JSON columns
281 lookup_rows = await music.database.get_rows(DB_TABLE_EXTERNAL_ID_LOOKUP)
282 assert {
283 (x["media_type"], x["external_id_type"], x["external_id"], x["item_id"])
284 for x in lookup_rows
285 } == {("track", str(ExternalID.ISRC), ISRC, db_id)}
286 match = await music.tracks.get_library_item_by_external_id(ISRC, ExternalID.ISRC)
287 assert match is not None
288 assert int(match.item_id) == db_id
289 # the external_ids columns (and their unusable indexes) are dropped;
290 # the lookup table is now the single source of truth
291 for table in MEDIA_TABLES:
292 assert "external_ids" not in await _table_columns(music.database, table)
293 old_indexes = await music.database.get_rows_from_query(
294 "SELECT name FROM sqlite_master WHERE type = 'index' AND name LIKE '%_external_ids_idx'"
295 )
296 assert not old_indexes
297 await music.database.close()
298
299
300async def test_migration_repairs_null_smart_fades_centroids(
301 database: DatabaseConnection,
302) -> None:
303 """Null spectral centroid values in legacy Smart Fades analysis rows become 0.0."""
304 await database.execute(
305 f"""CREATE TABLE {DB_TABLE_AUDIO_ANALYSIS}(
306 [id] INTEGER PRIMARY KEY AUTOINCREMENT,
307 [aa_provider_domain] TEXT NOT NULL,
308 [analysis_data] json NOT NULL)"""
309 )
310 rows = {
311 1: ("smart_fades", '{"spectral_centroid": [1.5, null, 2.5, null], "bpm": 120}'),
312 2: ("smart_fades", '{"spectral_centroid": [1.0, 2.0], "bpm": 100}'),
313 # null centroids from another analysis provider must not be touched
314 3: ("other_domain", '{"spectral_centroid": [null], "bpm": 100}'),
315 # a corrupt payload must not abort the migration
316 4: ("smart_fades", '{"spectral_centroid": [null'),
317 # a non-array centroid value must not be touched
318 5: ("smart_fades", '{"spectral_centroid": null, "bpm": 90}'),
319 # "null" appearing only inside a string value must not trigger a rewrite
320 6: ("smart_fades", '{"spectral_centroid": [3.5], "key": "nullish"}'),
321 }
322 for row_id, (domain, analysis_data) in rows.items():
323 await database.execute(
324 f"INSERT INTO {DB_TABLE_AUDIO_ANALYSIS} (id, aa_provider_domain, analysis_data) "
325 "VALUES (:id, :domain, :analysis_data)",
326 {"id": row_id, "domain": domain, "analysis_data": analysis_data},
327 )
328 await database.commit()
329
330 mass = MagicMock()
331 mass.cache.clear = AsyncMock()
332 await migrate_database(
333 mass,
334 database,
335 MagicMock(),
336 prev_version=52,
337 create_tables=AsyncMock(),
338 )
339
340 repaired = {
341 row["id"]: row["analysis_data"] for row in await database.get_rows(DB_TABLE_AUDIO_ANALYSIS)
342 }
343 assert json.loads(repaired[1]) == {"spectral_centroid": [1.5, 0.0, 2.5, 0.0], "bpm": 120}
344 # untouched rows must not be rewritten at all, hence the exact-string compare
345 for untouched_id in (2, 3, 4, 5, 6):
346 assert repaired[untouched_id] == rows[untouched_id][1]
347
348
349async def test_migration_populates_fts_tables(database: DatabaseConnection) -> None:
350 """Migrating a pre-FTS database builds and fills the FTS search tables."""
351 await database.execute("DROP TABLE tracks")
352 await database.execute(
353 "CREATE TABLE tracks([item_id] INTEGER PRIMARY KEY, "
354 "[external_ids] json NOT NULL DEFAULT '[]', [search_name] TEXT NOT NULL)"
355 )
356 await database.execute(
357 "INSERT INTO tracks(item_id, search_name) VALUES (1, 'bohemianrhapsody')"
358 )
359 await database.execute("INSERT INTO tracks(item_id, search_name) VALUES (2, 'radiogaga')")
360 await database.commit()
361
362 mass = MagicMock()
363 mass.cache.clear = AsyncMock()
364 await migrate_database(
365 mass,
366 database,
367 MagicMock(),
368 prev_version=51,
369 create_tables=AsyncMock(),
370 )
371
372 rows = await database.get_rows_from_query(
373 "SELECT rowid FROM tracks_fts WHERE tracks_fts MATCH :term", {"term": '"rhapsody"'}
374 )
375 assert [row["rowid"] for row in rows] == [1]
376 # tables without a search_name column (stand-ins in this bare test db) are skipped
377 rows = await database.get_rows_from_query(
378 "SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'albums_fts'"
379 )
380 assert not rows
381
382
383async def test_migration_rewrites_apple_music_artwork_to_tokens(
384 database: DatabaseConnection,
385) -> None:
386 """Persisted (expired) blobstore artwork URLs are rewritten to resolvable tokens."""
387 await database.execute("ALTER TABLE albums ADD COLUMN metadata json")
388 await database.execute(
389 "CREATE TABLE provider_mappings([media_type] TEXT, [item_id] INTEGER, "
390 "[provider_domain] TEXT, [provider_instance] TEXT, [provider_item_id] TEXT)"
391 )
392 signed_url = "https://store-033.blobstore.apple.com/pic/image?X-Amz-Signature=dead"
393 metadata = {
394 "images": [
395 {
396 "type": "thumb",
397 "path": signed_url,
398 "provider": "apple_music--1",
399 "remotely_accessible": True,
400 },
401 {
402 "type": "fanart",
403 "path": "https://tadb/fanart.jpg",
404 "provider": "theaudiodb",
405 "remotely_accessible": True,
406 },
407 {
408 "type": "thumb",
409 "path": signed_url,
410 "provider": "apple_music--removed",
411 "remotely_accessible": True,
412 },
413 ]
414 }
415 await database.execute(
416 "INSERT INTO albums (item_id, metadata) VALUES (1, :metadata)",
417 {"metadata": json.dumps(metadata)},
418 )
419 # an unrelated row without apple artwork must be left untouched
420 await database.execute(
421 "INSERT INTO albums (item_id, metadata) VALUES (2, :metadata)",
422 {"metadata": json.dumps({"images": [{"path": "https://x/y.jpg", "provider": "spotify"}]})},
423 )
424 await database.execute(
425 "INSERT INTO provider_mappings "
426 "(media_type, item_id, provider_domain, provider_instance, provider_item_id) "
427 "VALUES ('album', 1, 'apple_music', 'apple_music--1', 'l.abc123')"
428 )
429 await database.commit()
430
431 mass = MagicMock()
432 mass.cache.clear = AsyncMock()
433 await migrate_database(
434 mass,
435 database,
436 MagicMock(),
437 prev_version=54,
438 create_tables=AsyncMock(),
439 )
440
441 rows = await database.get_rows_from_query(
442 "SELECT item_id, metadata FROM albums ORDER BY item_id"
443 )
444 images = json.loads(rows[0]["metadata"])["images"]
445 # the mapped entry became a token, the metadata-provider entry survived and
446 # the entry whose apple instance no longer exists was dropped
447 assert [(img["path"], img["provider"], img["remotely_accessible"]) for img in images] == [
448 ("album/l.abc123", "apple_music--1", False),
449 ("https://tadb/fanart.jpg", "theaudiodb", True),
450 ]
451 assert json.loads(rows[1]["metadata"])["images"] == [
452 {"path": "https://x/y.jpg", "provider": "spotify"}
453 ]
454
455
456async def test_migration_strips_sound_effect_from_playlists(
457 database: DatabaseConnection,
458) -> None:
459 """The sound effect media type is removed from the stored playlists."""
460 await database.execute(
461 "INSERT INTO playlists (item_id, supported_mediatypes) VALUES "
462 '(1, \'["track","sound_effect","radio"]\'), '
463 "(2, '[\"track\"]'), "
464 "(3, 'corrupt value naming sound_effect'), "
465 "(4, '[\"sound_effect\"]')"
466 )
467 await database.commit()
468
469 mass = MagicMock()
470 mass.cache.clear = AsyncMock()
471 await migrate_database(
472 mass,
473 database,
474 MagicMock(),
475 prev_version=55,
476 create_tables=AsyncMock(),
477 )
478
479 rows = await database.get_rows_from_query(
480 "SELECT item_id, supported_mediatypes FROM playlists ORDER BY item_id"
481 )
482 assert json.loads(rows[0]["supported_mediatypes"]) == ["track", "radio"]
483 # playlists without the media type, and rows we cannot parse, are left alone
484 assert json.loads(rows[1]["supported_mediatypes"]) == ["track"]
485 assert rows[2]["supported_mediatypes"] == "corrupt value naming sound_effect"
486 # a playlist left with nothing yields an empty list, not NULL (the column is NOT NULL)
487 assert json.loads(rows[3]["supported_mediatypes"]) == []
488
489
490async def test_migration_adds_columns_leapfrogged_by_the_stable_schema_version(
491 database: DatabaseConnection,
492) -> None:
493 """A stable database gets the columns its own schema version made it skip."""
494 # the stable branch numbers its schema versions independently: its v43 already has the
495 # 4-column playlog constraint, but never got playback_speed or the playlist translation
496 # columns, which this branch gates behind steps a v43 database no longer runs
497 await database.execute(f"DROP TABLE {DB_TABLE_PLAYLOG}")
498 await database.execute(
499 f"""CREATE TABLE {DB_TABLE_PLAYLOG}(
500 [id] INTEGER PRIMARY KEY AUTOINCREMENT,
501 [item_id] TEXT NOT NULL,
502 [provider] TEXT NOT NULL,
503 [media_type] TEXT NOT NULL,
504 [name] TEXT NOT NULL,
505 [image] json,
506 [timestamp] INTEGER DEFAULT 0,
507 [fully_played] BOOLEAN,
508 [seconds_played] INTEGER,
509 [userid] TEXT NOT NULL,
510 [queue_id] TEXT,
511 [user_initiated] BOOLEAN NOT NULL DEFAULT 1,
512 UNIQUE(item_id, provider, media_type, userid));"""
513 )
514 await database.commit()
515
516 mass = MagicMock()
517 mass.cache.clear = AsyncMock()
518 await migrate_database(
519 mass,
520 database,
521 MagicMock(),
522 prev_version=43,
523 create_tables=AsyncMock(),
524 )
525
526 assert {"translation_key", "translation_params"} <= await _table_columns(database, "playlists")
527 assert "playback_speed" in await _table_columns(database, DB_TABLE_PLAYLOG)
528
529
530async def test_migration_adds_is_dynamic_column_to_radios(database: DatabaseConnection) -> None:
531 """A pre-58 database gets the radios.is_dynamic column, mirroring the playlist one."""
532 assert "is_dynamic" not in await _table_columns(database, "radios")
533
534 mass = MagicMock()
535 mass.cache.clear = AsyncMock()
536 await migrate_database(
537 mass,
538 database,
539 MagicMock(),
540 prev_version=57,
541 create_tables=AsyncMock(),
542 )
543
544 assert "is_dynamic" in await _table_columns(database, "radios")
545