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

1"""Persist Spotify playlist and track metadata in the library database.""" 

2 

3from __future__ import annotations 

4 

5import asyncio 

6import json 

7from datetime import datetime, timezone 

8from pathlib import Path 

9from typing import Any, Callable, Optional 

10 

11from loguru import logger 

12 

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 

17 

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) 

31 

32 

33def _now_iso() -> str: 

34 return datetime.now(timezone.utc).isoformat() 

35 

36 

37def _binary_order(dialect: Dialect, column: str) -> str: 

38 """ORDER BY term giving SQLite's byte-wise text order on both backends. 

39 

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 

46 

47 

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 

63 

64 

65class PlaylistSpotifyCache: 

66 """Store for Spotify playlist membership and track metadata.""" 

67 

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() 

72 

73 def _connect(self) -> Connection: 

74 return self.db.connect() 

75 

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 """) 

111 

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 """) 

139 

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 

151 

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 ) 

179 

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 

202 

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 ] 

223 

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']) 

242 

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']) 

259 

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) 

274 

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']) 

289 

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 ) 

312 

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) 

318 

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) 

347 

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 

359 

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 ) 

373 

374 

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*.""" 

382 

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 

394 

395 

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.""" 

404 

405 tid = str(track_spotify_id or '').strip() 

406 if not tid: 

407 raise ValueError('track_spotify_id required') 

408 

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 

420 

421 track = spotify.track_from_id(tid) 

422 if cache is not None: 

423 cache.store_track(tid, track) 

424 return track 

425 

426 

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.""" 

434 

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 

456 

457 

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.""" 

467 

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)