Coverage for downtify/playlist_catalog.py: 99%

273 statements  

« prev     ^ index     » next       coverage.py v7.17.4, created at 2026-11-21 22:18 +0000

1"""Playlist membership catalog (tracks per playlist, survives path moves).""" 

2 

3from __future__ import annotations 

4 

5from contextlib import AbstractContextManager 

6from datetime import datetime, timezone 

7from pathlib import Path 

8from typing import Any, Optional 

9 

10from .db.batching import in_short_transactions 

11from .db.config import Dialect 

12from .db.core import Connection, Database, as_database 

13from .library_cache_keys import file_content_key 

14from .library_paths import locate_library_file 

15from .track_index import normalize_spotify_track_id, read_monitor_rows 

16 

17 

18def _now_iso() -> str: 

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

20 

21 

22def _norm_path(filename: str) -> str: 

23 return str(filename or '').strip().replace('\\', '/') 

24 

25 

26def _catalog_track_id(song: dict[str, Any]) -> Optional[str]: 

27 """Stable playlist membership key; never put Deezer in TrackIndex. 

28 

29 The legacy column name is ``track_spotify_id``, but its TEXT key can 

30 also hold the Deezer resolver's explicitly namespaced track identity. 

31 """ 

32 spotify_id = normalize_spotify_track_id(song) 

33 if spotify_id: 

34 return spotify_id 

35 track_id = str(song.get('song_id') or '') 

36 if ( 

37 song.get('source') == 'deezer' 

38 and track_id.startswith('deezer-') 

39 and track_id.removeprefix('deezer-').isascii() 

40 and track_id.removeprefix('deezer-').isdigit() 

41 ): 

42 return track_id 

43 return None 

44 

45 

46def _chunked(values: list[str], size: int): 

47 for index in range(0, len(values), size): 

48 chunk = values[index : index + size] 

49 if chunk: 

50 yield chunk 

51 

52 

53def _text_order(dialect: Dialect) -> str: 

54 """ORDER BY suffix for text columns that sorts bytewise, like SQLite. 

55 

56 PostgreSQL sorts by the database collation (case and punctuation 

57 aware); the ``"C"`` collation is PostgreSQL-only, so SQLite gets none. 

58 """ 

59 return ' COLLATE "C"' if dialect is Dialect.POSTGRES else '' 

60 

61 

62class PlaylistCatalog: 

63 """Maps playlist names to ordered track identities and library paths.""" 

64 

65 def __init__(self, path_or_db: str | Path | Database) -> None: 

66 self.db = as_database(path_or_db, 'library') 

67 self._path = str(self.db.path) if self.db.path is not None else None 

68 self._init_db() 

69 

70 def _connect(self) -> AbstractContextManager[Connection]: 

71 return self.db.connect() 

72 

73 def _init_db(self) -> None: 

74 if self.db.dialect is not Dialect.SQLITE: 

75 # The migrations already created the final schema. 

76 return 

77 with self._connect() as conn: 

78 conn.execute(""" 

79 CREATE TABLE IF NOT EXISTS playlists ( 

80 name TEXT PRIMARY KEY, 

81 spotify_id TEXT, 

82 updated_at TEXT NOT NULL 

83 ) 

84 """) 

85 columns = { 

86 row[1] for row in conn.execute('PRAGMA table_info(playlists)') 

87 } 

88 if 'user_id' not in columns: 

89 # Who downloaded it (``0``: before accounts could own 

90 # anything - admins only, like everywhere else). 

91 conn.execute( 

92 'ALTER TABLE playlists ADD COLUMN user_id INTEGER ' 

93 'NOT NULL DEFAULT 0' 

94 ) 

95 conn.execute(""" 

96 CREATE TABLE IF NOT EXISTS manual_playlist_owners ( 

97 name_key TEXT PRIMARY KEY, 

98 name TEXT NOT NULL, 

99 user_id INTEGER NOT NULL 

100 ) 

101 """) 

102 conn.execute(""" 

103 CREATE TABLE IF NOT EXISTS playlist_tracks ( 

104 playlist_name TEXT NOT NULL, 

105 track_spotify_id TEXT NOT NULL, 

106 content_key TEXT, 

107 filename TEXT NOT NULL, 

108 track_order INTEGER NOT NULL DEFAULT 0, 

109 PRIMARY KEY (playlist_name, track_spotify_id), 

110 FOREIGN KEY (playlist_name) REFERENCES playlists(name) 

111 ON DELETE CASCADE 

112 ) 

113 """) 

114 conn.execute(""" 

115 CREATE INDEX IF NOT EXISTS idx_playlist_tracks_content_key 

116 ON playlist_tracks (content_key) 

117 WHERE content_key IS NOT NULL 

118 """) 

119 

120 def ensure_playlist( 

121 self, 

122 name: str, 

123 *, 

124 spotify_id: Optional[str] = None, 

125 user_id: Optional[int] = None, 

126 ) -> None: 

127 """Make sure the playlist row exists. *user_id* claims ownership 

128 when the row is created - or when nobody claimed it yet 

129 (``0``); re-saving someone else's playlist never steals it.""" 

130 

131 pl_name = str(name or '').strip() 

132 if not pl_name: 

133 return 

134 sid = str(spotify_id or '').strip() or None 

135 owner = int(user_id or 0) 

136 with self._connect() as conn: 

137 conn.execute( 

138 """INSERT INTO playlists (name, spotify_id, updated_at, 

139 user_id) 

140 VALUES (?, ?, ?, ?) 

141 ON CONFLICT(name) DO UPDATE SET 

142 spotify_id=COALESCE(excluded.spotify_id, 

143 playlists.spotify_id), 

144 updated_at=excluded.updated_at, 

145 user_id=CASE WHEN playlists.user_id = 0 

146 THEN excluded.user_id ELSE playlists.user_id END""", 

147 (pl_name, sid, _now_iso(), owner), 

148 ) 

149 

150 def upsert_track( 

151 self, 

152 playlist_name: str, 

153 song: dict[str, Any], 

154 filename: str, 

155 full_path: Path, 

156 *, 

157 track_order: int = 0, 

158 ) -> None: 

159 pl_name = str(playlist_name or '').strip() 

160 tid = _catalog_track_id(song) 

161 name = _norm_path(filename) 

162 if not pl_name or not tid or not name: 

163 return 

164 ck = file_content_key(full_path) 

165 self.ensure_playlist(pl_name, spotify_id=None) 

166 with self._connect() as conn: 

167 conn.execute( 

168 """INSERT INTO playlist_tracks 

169 (playlist_name, track_spotify_id, content_key, filename, track_order) 

170 VALUES (?, ?, ?, ?, ?) 

171 ON CONFLICT(playlist_name, track_spotify_id) DO UPDATE SET 

172 content_key=excluded.content_key, 

173 filename=excluded.filename, 

174 track_order=excluded.track_order""", 

175 (pl_name, tid, ck, name, int(track_order)), 

176 ) 

177 

178 def replace_playlist_tracks( 

179 self, 

180 playlist_name: str, 

181 rows: list[tuple[dict[str, Any], str, Path]], 

182 *, 

183 spotify_id: Optional[str] = None, 

184 user_id: Optional[int] = None, 

185 ) -> None: 

186 """Replace all tracks for a playlist (batch download). 

187 

188 *user_id* claims the playlist when the row is created; sweeps 

189 re-saving someone else's playlist never steal it. 

190 """ 

191 

192 pl_name = str(playlist_name or '').strip() 

193 if not pl_name: 

194 return 

195 self.ensure_playlist(pl_name, spotify_id=spotify_id, user_id=user_id) 

196 with self._connect() as conn: 

197 conn.execute( 

198 'DELETE FROM playlist_tracks WHERE playlist_name = ?', 

199 (pl_name,), 

200 ) 

201 for index, (song, filename, full_path) in enumerate(rows): 

202 self.upsert_track( 

203 pl_name, song, filename, full_path, track_order=index 

204 ) 

205 

206 def backfill_from_monitor_db( 

207 self, 

208 monitor_db_path: Path | Database, 

209 *, 

210 download_dir: Path, 

211 slskd_dir: Optional[Path] = None, 

212 ) -> int: 

213 """Import playlist membership from monitor history (older downloads). 

214 

215 ``monitor_db_path`` is either the monitor's ``Database`` (any 

216 backend) or a legacy SQLite file path. 

217 """ 

218 

219 rows = read_monitor_rows( 

220 self.db, 

221 monitor_db_path, 

222 """SELECT p.name, p.spotify_id, dt.track_spotify_id, dt.filename 

223 FROM downloaded_tracks dt 

224 JOIN monitored_playlists p ON p.id = dt.playlist_id 

225 WHERE dt.filename IS NOT NULL AND dt.filename != ''""", 

226 ) 

227 

228 linked = 0 

229 # A few commits instead of three per row (downtify/db/batching.py). 

230 for row in in_short_transactions(self.db, rows): 

231 pl_name = str(row[0] or '').strip() 

232 spotify_id = str(row[1] or '').strip() or None 

233 tid = str(row[2] or '').strip() 

234 filename = _norm_path(str(row[3] or '')) 

235 if not pl_name or not tid or not filename: 

236 continue 

237 full = locate_library_file(filename, download_dir, slskd_dir) 

238 if full is None: 

239 continue 

240 self.ensure_playlist(pl_name, spotify_id=spotify_id) 

241 self.upsert_track(pl_name, {'song_id': tid}, filename, full) 

242 linked += 1 

243 return linked 

244 

245 def list_playlist_names(self) -> list[str]: 

246 order = _text_order(self.db.dialect) 

247 with self._connect() as conn: 

248 rows = conn.execute( 

249 f'SELECT name FROM playlists ORDER BY name{order}' 

250 ).fetchall() 

251 return [str(row['name']) for row in rows] 

252 

253 def playlist_owner_map(self) -> dict[str, int]: 

254 """``{name: user_id}`` for downloaded playlists (one query).""" 

255 

256 with self._connect() as conn: 

257 rows = conn.execute( 

258 'SELECT name, user_id FROM playlists' 

259 ).fetchall() 

260 return {str(row['name']): int(row['user_id'] or 0) for row in rows} 

261 

262 def list_playlists_with_spotify_id(self) -> list[dict[str, Any]]: 

263 """Playlists in the catalog that have a linked Spotify playlist id.""" 

264 

265 order = _text_order(self.db.dialect) 

266 with self._connect() as conn: 

267 rows = conn.execute( 

268 f"""SELECT p.name, p.spotify_id, 

269 (SELECT COUNT(*) FROM playlist_tracks pt 

270 WHERE pt.playlist_name = p.name) AS track_count 

271 FROM playlists p 

272 WHERE p.spotify_id IS NOT NULL AND TRIM(p.spotify_id) != '' 

273 ORDER BY p.name{order}""" 

274 ).fetchall() 

275 return [ 

276 { 

277 'name': str(row['name']), 

278 'spotify_id': str(row['spotify_id']), 

279 'track_count': int(row['track_count'] or 0), 

280 } 

281 for row in rows 

282 ] 

283 

284 def list_tracks(self, playlist_name: str) -> list[dict[str, Any]]: 

285 pl_name = str(playlist_name or '').strip() 

286 if not pl_name: 

287 return [] 

288 order = _text_order(self.db.dialect) 

289 with self._connect() as conn: 

290 rows = conn.execute( 

291 f"""SELECT track_spotify_id, content_key, filename, track_order 

292 FROM playlist_tracks 

293 WHERE playlist_name = ? 

294 ORDER BY track_order ASC, track_spotify_id{order} ASC""", 

295 (pl_name,), 

296 ).fetchall() 

297 return [ 

298 { 

299 'track_spotify_id': str(row['track_spotify_id']), 

300 'content_key': row['content_key'], 

301 'filename': _norm_path(str(row['filename'])), 

302 'track_order': int(row['track_order']), 

303 } 

304 for row in rows 

305 ] 

306 

307 def playlists_for_track(self, spotify_track_id: str) -> list[str]: 

308 """Playlist names that catalog this Spotify track ID.""" 

309 

310 tid = str(spotify_track_id or '').strip() 

311 if not tid: 

312 return [] 

313 order = _text_order(self.db.dialect) 

314 with self._connect() as conn: 

315 rows = conn.execute( 

316 f"""SELECT DISTINCT playlist_name{order} AS playlist_name 

317 FROM playlist_tracks 

318 WHERE track_spotify_id = ? 

319 ORDER BY playlist_name""", 

320 (tid,), 

321 ).fetchall() 

322 return [str(row['playlist_name']) for row in rows] 

323 

324 def spotify_id_for_playlist(self, playlist_name: str) -> Optional[str]: 

325 pl_name = str(playlist_name or '').strip() 

326 if not pl_name: 

327 return None 

328 with self._connect() as conn: 

329 row = conn.execute( 

330 'SELECT spotify_id FROM playlists WHERE name = ?', 

331 (pl_name,), 

332 ).fetchone() 

333 if row is None or not row['spotify_id']: 

334 return None 

335 return str(row['spotify_id']) 

336 

337 def update_filename_by_content_key( 

338 self, content_key: str, filename: str 

339 ) -> list[str]: 

340 """Update rows for *content_key*; return affected playlist names.""" 

341 

342 ck = str(content_key or '').strip() 

343 name = _norm_path(filename) 

344 if not ck or not name: 

345 return [] 

346 with self._connect() as conn: 

347 conn.execute( 

348 """UPDATE playlist_tracks SET filename = ? 

349 WHERE content_key = ? AND filename != ?""", 

350 (name, ck, name), 

351 ) 

352 rows = conn.execute( 

353 """SELECT DISTINCT playlist_name FROM playlist_tracks 

354 WHERE content_key = ?""", 

355 (ck,), 

356 ).fetchall() 

357 return [str(row['playlist_name']) for row in rows] 

358 

359 def playlists_by_content_keys( 

360 self, content_keys: list[str] 

361 ) -> dict[str, list[str]]: 

362 keys = [str(k).strip() for k in content_keys if str(k).strip()] 

363 if not keys: 

364 return {} 

365 result: dict[str, list[str]] = {k: [] for k in keys} 

366 order = _text_order(self.db.dialect) 

367 with self._connect() as conn: 

368 for chunk in _chunked(keys, 400): 

369 placeholders = ','.join('?' * len(chunk)) 

370 rows = conn.execute( 

371 f"""SELECT content_key, playlist_name FROM playlist_tracks 

372 WHERE content_key IN ({placeholders}) 

373 ORDER BY playlist_name{order}""", 

374 chunk, 

375 ).fetchall() 

376 for row in rows: 

377 ck = str(row['content_key']) 

378 pl = str(row['playlist_name']) 

379 if pl not in result[ck]: 

380 result[ck].append(pl) 

381 return result 

382 

383 def playlists_by_filenames( 

384 self, filenames: list[str] 

385 ) -> dict[str, list[str]]: 

386 names = [_norm_path(n) for n in filenames if _norm_path(n)] 

387 if not names: 

388 return {} 

389 result: dict[str, list[str]] = {n: [] for n in names} 

390 order = _text_order(self.db.dialect) 

391 with self._connect() as conn: 

392 for chunk in _chunked(names, 400): 

393 placeholders = ','.join('?' * len(chunk)) 

394 rows = conn.execute( 

395 f"""SELECT filename, playlist_name FROM playlist_tracks 

396 WHERE filename IN ({placeholders}) 

397 ORDER BY playlist_name{order}""", 

398 chunk, 

399 ).fetchall() 

400 for row in rows: 

401 fn = _norm_path(str(row['filename'])) 

402 pl = str(row['playlist_name']) 

403 if pl not in result[fn]: 

404 result[fn].append(pl) 

405 return result 

406 

407 def set_content_key( 

408 self, 

409 playlist_name: str, 

410 track_spotify_id: str, 

411 content_key: str, 

412 ) -> None: 

413 pl_name = str(playlist_name or '').strip() 

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

415 ck = str(content_key or '').strip() 

416 if not pl_name or not tid or not ck: 

417 return 

418 with self._connect() as conn: 

419 conn.execute( 

420 """UPDATE playlist_tracks SET content_key = ? 

421 WHERE playlist_name = ? AND track_spotify_id = ?""", 

422 (ck, pl_name, tid), 

423 ) 

424 

425 def remove_track(self, playlist_name: str, track_spotify_id: str) -> bool: 

426 pl_name = str(playlist_name or '').strip() 

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

428 if not pl_name or not tid: 

429 return False 

430 with self._connect() as conn: 

431 cur = conn.execute( 

432 """DELETE FROM playlist_tracks 

433 WHERE playlist_name = ? AND track_spotify_id = ?""", 

434 (pl_name, tid), 

435 ) 

436 return cur.rowcount > 0 

437 

438 def reassign_legacy(self, user_id: int) -> int: 

439 """Give pre-feature playlists (``user_id`` ``0``) to *user_id*. 

440 

441 Runs once on the first start with ownership; later system rows 

442 keep ``0`` (admins-only), so this must not run again. 

443 """ 

444 

445 if not user_id: 

446 return 0 

447 with self._connect() as conn: 

448 cur = conn.execute( 

449 'UPDATE playlists SET user_id = ? WHERE user_id = 0', 

450 (int(user_id),), 

451 ) 

452 return cur.rowcount 

453 

454 def delete_playlist(self, playlist_name: str) -> list[str]: 

455 """Remove playlist and all track rows; return registered filenames.""" 

456 

457 pl_name = str(playlist_name or '').strip() 

458 if not pl_name: 

459 return [] 

460 with self._connect() as conn: 

461 rows = conn.execute( 

462 """SELECT filename FROM playlist_tracks 

463 WHERE playlist_name = ?""", 

464 (pl_name,), 

465 ).fetchall() 

466 filenames = list( 

467 dict.fromkeys( 

468 _norm_path(str(row['filename'])) 

469 for row in rows 

470 if row['filename'] 

471 ) 

472 ) 

473 # Children first. PostgreSQL's ON DELETE CASCADE would do this 

474 # too, but SQLite does not enforce foreign keys here. 

475 conn.execute( 

476 'DELETE FROM playlist_tracks WHERE playlist_name = ?', 

477 (pl_name,), 

478 ) 

479 conn.execute('DELETE FROM playlists WHERE name = ?', (pl_name,)) 

480 return filenames 

481 

482 def remove_tracks_for_filename( 

483 self, filename: str, playlist_name: Optional[str] = None 

484 ) -> list[str]: 

485 """Delete rows pointing at *filename* - only *playlist_name*'s 

486 when given (a song hidden from it); return affected playlist 

487 names.""" 

488 

489 name = _norm_path(filename) 

490 if not name: 

491 return [] 

492 where = 'filename = ?' 

493 params: tuple[str, ...] = (name,) 

494 if playlist_name is not None: 

495 where += ' AND playlist_name = ?' 

496 params = (name, str(playlist_name)) 

497 with self._connect() as conn: 

498 rows = conn.execute( 

499 f'SELECT DISTINCT playlist_name FROM playlist_tracks ' 

500 f'WHERE {where}', 

501 params, 

502 ).fetchall() 

503 conn.execute(f'DELETE FROM playlist_tracks WHERE {where}', params) 

504 return [str(row['playlist_name']) for row in rows] 

505 

506 def all_track_rows(self) -> list[dict[str, Any]]: 

507 with self._connect() as conn: 

508 rows = conn.execute( 

509 """SELECT playlist_name, track_spotify_id, content_key, filename 

510 FROM playlist_tracks""" 

511 ).fetchall() 

512 return [ 

513 { 

514 'playlist_name': str(row['playlist_name']), 

515 'track_spotify_id': str(row['track_spotify_id']), 

516 'content_key': row['content_key'], 

517 'filename': _norm_path(str(row['filename'])), 

518 } 

519 for row in rows 

520 ] 

521 

522 

523class ManualPlaylistOwners: 

524 """Who created each Library playlist (M3U files carry no owner). 

525 

526 A user opens their own manual playlists only; unclaimed 

527 (pre-feature) ones are admins-only, like everywhere else. 

528 """ 

529 

530 def __init__(self, path_or_db: str | Path | Database) -> None: 

531 self.db = as_database(path_or_db, 'library') 

532 self._path = str(self.db.path) if self.db.path is not None else None 

533 self._init_db() 

534 

535 def _connect(self) -> AbstractContextManager[Connection]: 

536 return self.db.connect() 

537 

538 def _init_db(self) -> None: 

539 if self.db.dialect is not Dialect.SQLITE: 

540 # The migrations already created the final schema. 

541 return 

542 with self._connect() as conn: 

543 conn.execute(""" 

544 CREATE TABLE IF NOT EXISTS manual_playlist_owners ( 

545 name_key TEXT PRIMARY KEY, 

546 name TEXT NOT NULL, 

547 user_id INTEGER NOT NULL 

548 ) 

549 """) 

550 

551 @staticmethod 

552 def _key(name: str) -> str: 

553 return str(name or '').strip().casefold() 

554 

555 def set_owner(self, name: str, user_id: int) -> None: 

556 """Claim a Library-created playlist for *user_id*.""" 

557 

558 key = self._key(name) 

559 if not key or not user_id: 

560 return 

561 with self._connect() as conn: 

562 conn.execute( 

563 """INSERT INTO manual_playlist_owners 

564 (name_key, name, user_id) 

565 VALUES (?, ?, ?) 

566 ON CONFLICT(name_key) DO UPDATE SET 

567 name=excluded.name, user_id=excluded.user_id""", 

568 (key, str(name or '').strip(), int(user_id)), 

569 ) 

570 

571 def claim_owner(self, name: str, user_id: int) -> bool: 

572 """Claim an unowned playlist without replacing an existing owner.""" 

573 

574 key = self._key(name) 

575 if not key or not user_id: 

576 return False 

577 with self._connect() as conn: 

578 cursor = conn.execute( 

579 """INSERT INTO manual_playlist_owners 

580 (name_key, name, user_id) VALUES (?, ?, ?) 

581 ON CONFLICT DO NOTHING""", 

582 (key, str(name or '').strip(), int(user_id)), 

583 ) 

584 return cursor.rowcount == 1 

585 

586 def owner(self, name: str) -> Optional[int]: 

587 """The owner's user id, or ``None`` when nobody claimed it.""" 

588 

589 key = self._key(name) 

590 if not key: 

591 return None 

592 with self._connect() as conn: 

593 row = conn.execute( 

594 'SELECT user_id FROM manual_playlist_owners WHERE name_key ' 

595 '= ?', 

596 (key,), 

597 ).fetchone() 

598 if row is None: 

599 return None 

600 return int(row['user_id'] or 0) or None 

601 

602 def forget_owner(self, name: str) -> None: 

603 with self._connect() as conn: 

604 conn.execute( 

605 'DELETE FROM manual_playlist_owners WHERE name_key = ?', 

606 (self._key(name),), 

607 ) 

608 

609 def rename_owner(self, name: str, new_name: str) -> None: 

610 """Move a manual playlist's owner to its new name (if any).""" 

611 

612 owner = self.owner(name) 

613 self.forget_owner(name) 

614 if owner is not None: 

615 self.set_owner(new_name, owner) 

616 

617 def owner_map(self) -> dict[str, int]: 

618 """``{casefolded name: user_id}`` (one query).""" 

619 

620 with self._connect() as conn: 

621 rows = conn.execute( 

622 'SELECT name_key, user_id FROM manual_playlist_owners' 

623 ).fetchall() 

624 return {str(row['name_key']): int(row['user_id'] or 0) for row in rows} 

625 

626 def remove_user(self, user_id: int) -> None: 

627 """Release a deleted account's playlists (files stay put).""" 

628 

629 with self._connect() as conn: 

630 conn.execute( 

631 'DELETE FROM manual_playlist_owners WHERE user_id = ?', 

632 (int(user_id),), 

633 )