Coverage for downtify/playlist_spotify_cache.py: 93%
230 statements
« prev ^ index » next coverage.py v7.17.4, created at 2026-11-21 22:18 +0000
« prev ^ index » next coverage.py v7.17.4, created at 2026-11-21 22:18 +0000
1"""Persist Spotify playlist and track metadata in the library database."""
3from __future__ import annotations
5import asyncio
6import json
7from datetime import datetime, timezone
8from pathlib import Path
9from typing import Any, Callable, Optional
11from loguru import logger
13from . import library_only, spotify
14from .db.config import Dialect
15from .db.core import Connection, Database, as_database
16from .track_index import normalize_spotify_track_id
18_TRACK_KEYS = (
19 'song_id',
20 'name',
21 'artists',
22 'artist',
23 'album_name',
24 'cover_url',
25 'url',
26 'duration',
27 'year',
28 'track_number',
29 'album_track_total',
30)
33def _now_iso() -> str:
34 return datetime.now(timezone.utc).isoformat()
37def _binary_order(dialect: Dialect, column: str) -> str:
38 """ORDER BY term giving SQLite's byte-wise text order on both backends.
40 PostgreSQL sorts text by the database collation; the "C" collation
41 compares bytes, as SQLite's default BINARY collation does.
42 """
43 if dialect is Dialect.POSTGRES:
44 return f'{column} COLLATE "C"'
45 return column
48def _compact_track(song: dict[str, Any]) -> dict[str, Any]:
49 row: dict[str, Any] = {}
50 for key in _TRACK_KEYS:
51 value = song.get(key)
52 if value is None or value is False:
53 continue
54 if isinstance(value, str) and not value:
55 continue
56 if isinstance(value, list) and not value:
57 continue
58 row[key] = value
59 tid = normalize_spotify_track_id(row)
60 if tid:
61 row['song_id'] = tid
62 return row
65class PlaylistSpotifyCache:
66 """Store for Spotify playlist membership and track metadata."""
68 def __init__(self, db_path: str | Path | Database) -> None:
69 self.db = as_database(db_path, 'library')
70 self._path = None if self.db.path is None else str(self.db.path)
71 self._init_db()
73 def _connect(self) -> Connection:
74 return self.db.connect()
76 def _init_db(self) -> None:
77 # Postgres gets the final schema from the migrations; the CREATE,
78 # rebuild and index steps below are SQLite-only.
79 if self.db.dialect is not Dialect.SQLITE:
80 return
81 with self._connect() as conn:
82 conn.execute("""
83 CREATE TABLE IF NOT EXISTS spotify_playlist_cache (
84 spotify_playlist_id TEXT PRIMARY KEY,
85 playlist_name TEXT NOT NULL,
86 fetched_at TEXT NOT NULL,
87 tracks_json TEXT NOT NULL
88 )
89 """)
90 conn.execute("""
91 CREATE TABLE IF NOT EXISTS spotify_playlist_tracks (
92 spotify_playlist_id TEXT NOT NULL,
93 track_spotify_id TEXT NOT NULL,
94 track_order INTEGER NOT NULL DEFAULT 0,
95 track_json TEXT NOT NULL,
96 PRIMARY KEY (spotify_playlist_id, track_order)
97 )
98 """)
99 self._migrate_playlist_tracks_table(conn)
100 conn.execute("""
101 CREATE INDEX IF NOT EXISTS idx_spotify_pl_tracks_track
102 ON spotify_playlist_tracks (track_spotify_id)
103 """)
104 conn.execute("""
105 CREATE TABLE IF NOT EXISTS spotify_track_cache (
106 track_spotify_id TEXT PRIMARY KEY,
107 track_json TEXT NOT NULL,
108 fetched_at TEXT NOT NULL
109 )
110 """)
112 @staticmethod
113 def _migrate_playlist_tracks_table(conn: Connection) -> None:
114 row = conn.execute(
115 """SELECT sql FROM sqlite_master
116 WHERE type = 'table' AND name = 'spotify_playlist_tracks'"""
117 ).fetchone()
118 if row is None:
119 return
120 ddl = str(row['sql'] or '')
121 if 'PRIMARY KEY (spotify_playlist_id, track_order)' in ddl:
122 return
123 conn.executescript("""
124 CREATE TABLE spotify_playlist_tracks_new (
125 spotify_playlist_id TEXT NOT NULL,
126 track_spotify_id TEXT NOT NULL,
127 track_order INTEGER NOT NULL DEFAULT 0,
128 track_json TEXT NOT NULL,
129 PRIMARY KEY (spotify_playlist_id, track_order)
130 );
131 INSERT OR IGNORE INTO spotify_playlist_tracks_new
132 (spotify_playlist_id, track_spotify_id, track_order, track_json)
133 SELECT spotify_playlist_id, track_spotify_id, track_order, track_json
134 FROM spotify_playlist_tracks;
135 DROP TABLE spotify_playlist_tracks;
136 ALTER TABLE spotify_playlist_tracks_new
137 RENAME TO spotify_playlist_tracks;
138 """)
140 @staticmethod
141 def _track_from_json(raw: Any) -> Optional[dict[str, Any]]:
142 if isinstance(raw, dict):
143 return dict(raw)
144 if not raw:
145 return None
146 try:
147 parsed = json.loads(str(raw))
148 except (TypeError, json.JSONDecodeError):
149 return None
150 return parsed if isinstance(parsed, dict) else None
152 @staticmethod
153 def _sync_playlist_track_rows(
154 conn: Connection,
155 spotify_playlist_id: str,
156 tracks: list[dict[str, Any]],
157 ) -> None:
158 sid = str(spotify_playlist_id or '').strip()
159 conn.execute(
160 'DELETE FROM spotify_playlist_tracks WHERE spotify_playlist_id = ?',
161 (sid,),
162 )
163 for index, track in enumerate(tracks):
164 compact = _compact_track(track)
165 tid = normalize_spotify_track_id(compact)
166 if not tid:
167 continue
168 conn.execute(
169 """INSERT INTO spotify_playlist_tracks
170 (spotify_playlist_id, track_spotify_id, track_order, track_json)
171 VALUES (?, ?, ?, ?)""",
172 (
173 sid,
174 tid,
175 int(index),
176 json.dumps(compact, separators=(',', ':')),
177 ),
178 )
180 def get(
181 self, spotify_playlist_id: str
182 ) -> Optional[tuple[str, list[dict[str, Any]]]]:
183 sid = str(spotify_playlist_id or '').strip()
184 if not sid:
185 return None
186 with self._connect() as conn:
187 row = conn.execute(
188 """SELECT playlist_name, tracks_json
189 FROM spotify_playlist_cache
190 WHERE spotify_playlist_id = ?""",
191 (sid,),
192 ).fetchone()
193 if row is None:
194 return None
195 try:
196 tracks = json.loads(str(row['tracks_json']))
197 except (TypeError, json.JSONDecodeError):
198 return None
199 if not isinstance(tracks, list):
200 return None
201 return str(row['playlist_name']), tracks
203 def list_playlists(self) -> list[dict[str, Any]]:
204 order = _binary_order(self.db.dialect, 'playlist_name')
205 with self._connect() as conn:
206 rows = conn.execute(
207 f"""SELECT spotify_playlist_id, playlist_name, fetched_at,
208 (SELECT COUNT(*) FROM spotify_playlist_tracks pt
209 WHERE pt.spotify_playlist_id = c.spotify_playlist_id)
210 AS track_count
211 FROM spotify_playlist_cache c
212 ORDER BY {order}, spotify_playlist_id"""
213 ).fetchall()
214 return [
215 {
216 'spotify_playlist_id': str(row['spotify_playlist_id']),
217 'playlist_name': str(row['playlist_name']),
218 'fetched_at': str(row['fetched_at']),
219 'track_count': int(row['track_count'] or 0),
220 }
221 for row in rows
222 ]
224 def get_playlist_track(
225 self,
226 spotify_playlist_id: str,
227 track_spotify_id: str,
228 ) -> Optional[dict[str, Any]]:
229 sid = str(spotify_playlist_id or '').strip()
230 tid = str(track_spotify_id or '').strip()
231 if not sid or not tid:
232 return None
233 with self._connect() as conn:
234 row = conn.execute(
235 """SELECT track_json FROM spotify_playlist_tracks
236 WHERE spotify_playlist_id = ? AND track_spotify_id = ?""",
237 (sid, tid),
238 ).fetchone()
239 if row is None:
240 return None
241 return self._track_from_json(row['track_json'])
243 def find_track_in_playlists(
244 self, track_spotify_id: str
245 ) -> Optional[dict[str, Any]]:
246 tid = str(track_spotify_id or '').strip()
247 if not tid:
248 return None
249 with self._connect() as conn:
250 row = conn.execute(
251 """SELECT track_json FROM spotify_playlist_tracks
252 WHERE track_spotify_id = ?
253 ORDER BY track_order ASC LIMIT 1""",
254 (tid,),
255 ).fetchone()
256 if row is None:
257 return None
258 return self._track_from_json(row['track_json'])
260 def playlists_for_track(self, track_spotify_id: str) -> list[str]:
261 tid = str(track_spotify_id or '').strip()
262 if not tid:
263 return []
264 with self._connect() as conn:
265 rows = conn.execute(
266 """SELECT DISTINCT spotify_playlist_id
267 FROM spotify_playlist_tracks
268 WHERE track_spotify_id = ?""",
269 (tid,),
270 ).fetchall()
271 # Sorted in Python: PostgreSQL rejects ORDER BY on a collated
272 # expression with DISTINCT, and str order is UTF-8 byte order.
273 return sorted(str(row['spotify_playlist_id']) for row in rows)
275 def get_cached_track(
276 self, track_spotify_id: str
277 ) -> Optional[dict[str, Any]]:
278 tid = str(track_spotify_id or '').strip()
279 if not tid:
280 return None
281 with self._connect() as conn:
282 row = conn.execute(
283 'SELECT track_json FROM spotify_track_cache WHERE track_spotify_id = ?',
284 (tid,),
285 ).fetchone()
286 if row is None:
287 return None
288 return self._track_from_json(row['track_json'])
290 @staticmethod
291 def _store_track_row(
292 conn: Connection,
293 track_spotify_id: str,
294 track: dict[str, Any],
295 ) -> None:
296 tid = (
297 normalize_spotify_track_id(track)
298 or str(track_spotify_id or '').strip()
299 )
300 if not tid:
301 return
302 payload = json.dumps(_compact_track(track), separators=(',', ':'))
303 conn.execute(
304 """INSERT INTO spotify_track_cache
305 (track_spotify_id, track_json, fetched_at)
306 VALUES (?, ?, ?)
307 ON CONFLICT(track_spotify_id) DO UPDATE SET
308 track_json=excluded.track_json,
309 fetched_at=excluded.fetched_at""",
310 (tid, payload, _now_iso()),
311 )
313 def store_track(
314 self, track_spotify_id: str, track: dict[str, Any]
315 ) -> None:
316 with self._connect() as conn:
317 self._store_track_row(conn, track_spotify_id, track)
319 def store(
320 self,
321 spotify_playlist_id: str,
322 playlist_name: str,
323 tracks: list[dict[str, Any]],
324 ) -> None:
325 sid = str(spotify_playlist_id or '').strip()
326 name = str(playlist_name or '').strip() or sid
327 if not sid:
328 return
329 compact_rows = [_compact_track(track) for track in tracks]
330 payload = json.dumps(compact_rows, separators=(',', ':'))
331 with self._connect() as conn:
332 conn.execute(
333 """INSERT INTO spotify_playlist_cache
334 (spotify_playlist_id, playlist_name, fetched_at, tracks_json)
335 VALUES (?, ?, ?, ?)
336 ON CONFLICT(spotify_playlist_id) DO UPDATE SET
337 playlist_name=excluded.playlist_name,
338 fetched_at=excluded.fetched_at,
339 tracks_json=excluded.tracks_json""",
340 (sid, name, _now_iso(), payload),
341 )
342 self._sync_playlist_track_rows(conn, sid, tracks)
343 for track in compact_rows:
344 tid = normalize_spotify_track_id(track)
345 if tid:
346 self._store_track_row(conn, tid, track)
348 def track_count(self, spotify_playlist_id: str) -> int:
349 sid = str(spotify_playlist_id or '').strip()
350 if not sid:
351 return 0
352 with self._connect() as conn:
353 row = conn.execute(
354 """SELECT COUNT(*) AS n FROM spotify_playlist_tracks
355 WHERE spotify_playlist_id = ?""",
356 (sid,),
357 ).fetchone()
358 return int(row['n'] or 0) if row is not None else 0
360 def delete_playlist(self, spotify_playlist_id: str) -> None:
361 sid = str(spotify_playlist_id or '').strip()
362 if not sid:
363 return
364 with self._connect() as conn:
365 conn.execute(
366 'DELETE FROM spotify_playlist_tracks WHERE spotify_playlist_id = ?',
367 (sid,),
368 )
369 conn.execute(
370 'DELETE FROM spotify_playlist_cache WHERE spotify_playlist_id = ?',
371 (sid,),
372 )
375def fetch_playlist_tracks(
376 spotify_playlist_id: str,
377 *,
378 cache: Optional[PlaylistSpotifyCache] = None,
379 refresh: bool = False,
380) -> tuple[str, list[dict[str, Any]]]:
381 """Return ``(playlist_name, tracks)``, using DB unless *refresh*."""
383 sid = str(spotify_playlist_id or '').strip()
384 if not sid:
385 return '', []
386 if cache is not None and not refresh:
387 hit = cache.get(sid)
388 if hit is not None:
389 return hit
390 name, tracks = spotify.playlist_info_and_tracks(sid)
391 if cache is not None:
392 cache.store(sid, name, tracks)
393 return name, tracks
396def fetch_track_metadata(
397 track_spotify_id: str,
398 *,
399 cache: Optional[PlaylistSpotifyCache] = None,
400 playlist_id: Optional[str] = None,
401 refresh: bool = False,
402) -> dict[str, Any]:
403 """Return track metadata from DB when available, else Spotify embed."""
405 tid = str(track_spotify_id or '').strip()
406 if not tid:
407 raise ValueError('track_spotify_id required')
409 if cache is not None and not refresh:
410 if playlist_id:
411 hit = cache.get_playlist_track(playlist_id, tid)
412 if hit is not None:
413 return hit
414 hit = cache.get_cached_track(tid)
415 if hit is not None:
416 return hit
417 hit = cache.find_track_in_playlists(tid)
418 if hit is not None:
419 return hit
421 track = spotify.track_from_id(tid)
422 if cache is not None:
423 cache.store_track(tid, track)
424 return track
427async def warm_uncached_playlists(
428 cache: PlaylistSpotifyCache,
429 spotify_ids: list[str],
430 *,
431 delay_seconds: float = 1.0,
432) -> int:
433 """Fetch Spotify playlists missing from *cache* and persist them."""
435 warmed = 0
436 for raw_id in spotify_ids:
437 sid = str(raw_id or '').strip()
438 if not sid or await asyncio.to_thread(cache.get, sid) is not None:
439 continue
440 try:
441 await asyncio.to_thread(
442 fetch_playlist_tracks,
443 sid,
444 cache=cache,
445 refresh=True,
446 )
447 warmed += 1
448 except Exception:
449 logger.opt(exception=True).warning(
450 'Spotify cache warm failed for {}',
451 sid,
452 )
453 if delay_seconds > 0:
454 await asyncio.sleep(delay_seconds)
455 return warmed
458async def playlist_spotify_cache_loop(
459 cache: PlaylistSpotifyCache,
460 list_spotify_ids: Callable[[], list[str]],
461 *,
462 delay_seconds: float = 1.0,
463 idle_seconds: float = 300.0,
464 settings: Optional[dict[str, Any]] = None,
465) -> None:
466 """Background task: keep known playlists cached from Spotify."""
468 while True:
469 try:
470 # SQLite lookups across the batch store, catalog and monitor:
471 # off the event loop.
472 ids = await asyncio.to_thread(list_spotify_ids)
473 if library_only.enabled(settings or {}):
474 warmed = 0
475 else:
476 with library_only.policy.external(
477 settings if settings is not None else {}
478 ):
479 warmed = await warm_uncached_playlists(
480 cache,
481 ids,
482 delay_seconds=delay_seconds,
483 )
484 if warmed:
485 logger.info('Spotify cache: stored {} playlist(s)', warmed)
486 except Exception:
487 logger.exception('Spotify playlist cache loop failed')
488 await asyncio.sleep(idle_seconds)