music-assistant-server

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