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