Coverage for downtify/playlist_batches.py: 91%

211 statements  

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

1"""Persistent playlist batch jobs and completeness vs the library.""" 

2 

3from __future__ import annotations 

4 

5from collections.abc import Collection 

6from dataclasses import dataclass 

7from datetime import datetime, timezone 

8from pathlib import Path 

9from typing import Any, Optional 

10 

11from .db.config import Dialect 

12from .db.core import Database, as_database 

13from .db.dialect import insert_returning_id 

14from .library_cache_keys import file_content_key 

15from .library_paths import locate_library_file, slskd_dir_from_downloader 

16from .track_index import normalize_spotify_track_id, resolve_existing_download 

17 

18_BATCH_STATUSES = frozenset({'in_progress', 'incomplete', 'complete'}) 

19#: What :meth:`Downloader.existing_filename_for` accepts as a match. 

20_AUDIO_EXTENSIONS = frozenset({'mp3', 'flac', 'ogg', 'opus', 'm4a'}) 

21 

22 

23def _now_iso() -> str: 

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

25 

26 

27class PlaylistBatchStore: 

28 """Store for playlist download batches (SQLite or PostgreSQL).""" 

29 

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

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

32 if isinstance(path_or_db, Database): 

33 self._path = str(self.db.path) if self.db.path else None 

34 else: 

35 self._path = str(path_or_db) 

36 self._init_db() 

37 

38 def _connect(self): 

39 return self.db.connect() 

40 

41 def _init_db(self) -> None: 

42 # On PostgreSQL the library migrations already created the final 

43 # table (identity ``id``, ``user_id``) and its index. 

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

45 return 

46 with self.db.transaction(lock=True) as conn: 

47 conn.execute(""" 

48 CREATE TABLE IF NOT EXISTS playlist_batches ( 

49 id INTEGER PRIMARY KEY AUTOINCREMENT, 

50 spotify_playlist_id TEXT NOT NULL, 

51 playlist_name TEXT NOT NULL, 

52 playlist_url TEXT NOT NULL, 

53 expected_count INTEGER NOT NULL DEFAULT 0, 

54 started_at TEXT NOT NULL, 

55 finished_at TEXT, 

56 status TEXT NOT NULL DEFAULT 'in_progress', 

57 succeeded_count INTEGER NOT NULL DEFAULT 0, 

58 failed_count INTEGER NOT NULL DEFAULT 0 

59 ) 

60 """) 

61 columns = { 

62 row[1] 

63 for row in conn.execute('PRAGMA table_info(playlist_batches)') 

64 } 

65 if 'user_id' not in columns: 

66 # Who queued it (``0``: before accounts could own 

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

68 conn.execute( 

69 'ALTER TABLE playlist_batches ADD COLUMN user_id INTEGER ' 

70 'NOT NULL DEFAULT 0' 

71 ) 

72 conn.execute(""" 

73 CREATE INDEX IF NOT EXISTS idx_playlist_batches_spotify 

74 ON playlist_batches (spotify_playlist_id, id DESC) 

75 """) 

76 

77 def start_batch( 

78 self, 

79 spotify_playlist_id: str, 

80 playlist_name: str, 

81 playlist_url: str, 

82 expected_count: int, 

83 *, 

84 user_id: int = 0, 

85 ) -> int: 

86 sid = str(spotify_playlist_id or '').strip() 

87 name = str(playlist_name or '').strip() or sid 

88 url = str(playlist_url or '').strip() 

89 expected = max(int(expected_count), 0) 

90 if not sid or not url: 

91 raise ValueError( 

92 'spotify_playlist_id and playlist_url are required' 

93 ) 

94 with self._connect() as conn: 

95 return insert_returning_id( 

96 conn, 

97 """INSERT INTO playlist_batches 

98 (spotify_playlist_id, playlist_name, playlist_url, 

99 expected_count, started_at, status, user_id) 

100 VALUES (?, ?, ?, ?, ?, 'in_progress', ?)""", 

101 (sid, name, url, expected, _now_iso(), int(user_id or 0)), 

102 ) 

103 

104 def update_batch_name(self, batch_id: int, playlist_name: str) -> None: 

105 name = str(playlist_name or '').strip() 

106 if not name: 

107 return 

108 with self._connect() as conn: 

109 conn.execute( 

110 """UPDATE playlist_batches SET playlist_name = ? 

111 WHERE id = ?""", 

112 (name, int(batch_id)), 

113 ) 

114 

115 def finish_batch( 

116 self, 

117 batch_id: int, 

118 succeeded_count: int, 

119 failed_count: int, 

120 *, 

121 status: str, 

122 ) -> None: 

123 st = str(status or '').strip().lower() 

124 if st not in _BATCH_STATUSES: 

125 raise ValueError(f'invalid batch status: {status!r}') 

126 with self._connect() as conn: 

127 conn.execute( 

128 """UPDATE playlist_batches SET 

129 succeeded_count = ?, 

130 failed_count = ?, 

131 status = ?, 

132 finished_at = ? 

133 WHERE id = ?""", 

134 ( 

135 max(int(succeeded_count), 0), 

136 max(int(failed_count), 0), 

137 st, 

138 _now_iso(), 

139 int(batch_id), 

140 ), 

141 ) 

142 

143 def mark_complete(self, batch_id: int) -> None: 

144 with self._connect() as conn: 

145 conn.execute( 

146 """UPDATE playlist_batches SET 

147 status = 'complete', 

148 finished_at = COALESCE(finished_at, ?) 

149 WHERE id = ?""", 

150 (_now_iso(), int(batch_id)), 

151 ) 

152 

153 @staticmethod 

154 def _latest_ids_clause(statuses: Optional[set[str]]) -> str: 

155 if statuses: 

156 marks = ','.join('?' * len(statuses)) 

157 return ( 

158 'SELECT spotify_playlist_id, MAX(id) AS max_id ' 

159 'FROM playlist_batches ' 

160 f'WHERE status IN ({marks}) GROUP BY spotify_playlist_id' 

161 ) 

162 return ( 

163 'SELECT spotify_playlist_id, MAX(id) AS max_id ' 

164 'FROM playlist_batches GROUP BY spotify_playlist_id' 

165 ) 

166 

167 def _latest_batches( 

168 self, statuses: Optional[set[str]], user_id: Optional[int] 

169 ) -> list[dict[str, Any]]: 

170 clause = self._latest_ids_clause(statuses) 

171 args: list[Any] = list(statuses) if statuses else [] 

172 owner = '' 

173 if user_id is not None: 

174 owner = 'AND b.user_id = ?' 

175 args.append(user_id) 

176 with self._connect() as conn: 

177 rows = conn.execute( 

178 'SELECT b.* FROM playlist_batches b INNER JOIN (' 

179 + clause 

180 + f') latest ON b.id = latest.max_id WHERE 1 = 1 {owner} ' 

181 'ORDER BY b.started_at DESC', 

182 args, 

183 ).fetchall() 

184 return [_row_to_dict(row) for row in rows] 

185 

186 def list_open_batches( 

187 self, user_id: Optional[int] = None 

188 ) -> list[dict[str, Any]]: 

189 """Latest open batch row per Spotify playlist id. 

190 

191 With *user_id*: only theirs. Without it: everything (admins and 

192 internal flows). 

193 """ 

194 

195 return self._latest_batches({'in_progress', 'incomplete'}, user_id) 

196 

197 def list_latest_batches( 

198 self, user_id: Optional[int] = None 

199 ) -> list[dict[str, Any]]: 

200 """Latest batch row per Spotify playlist id (any status).""" 

201 

202 return self._latest_batches(None, user_id) 

203 

204 def get_batch(self, batch_id: int) -> Optional[dict[str, Any]]: 

205 with self._connect() as conn: 

206 row = conn.execute( 

207 'SELECT * FROM playlist_batches WHERE id = ?', 

208 (int(batch_id),), 

209 ).fetchone() 

210 if row is None: 

211 return None 

212 return _row_to_dict(row) 

213 

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

215 """Give pre-feature batches (``user_id`` ``0``) to *user_id*. 

216 

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

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

219 """ 

220 

221 if not user_id: 

222 return 0 

223 with self._connect() as conn: 

224 cur = conn.execute( 

225 'UPDATE playlist_batches SET user_id = ? WHERE user_id = 0', 

226 (int(user_id),), 

227 ) 

228 return cur.rowcount 

229 

230 def delete_for_spotify_playlist( 

231 self, spotify_playlist_id: str, user_id: Optional[int] = None 

232 ) -> int: 

233 """Drop batch rows for a playlist (only *user_id*'s when given).""" 

234 

235 sid = str(spotify_playlist_id or '').strip() 

236 if not sid: 

237 return 0 

238 with self._connect() as conn: 

239 if user_id is None: 

240 cur = conn.execute( 

241 'DELETE FROM playlist_batches WHERE spotify_playlist_id ' 

242 '= ?', 

243 (sid,), 

244 ) 

245 else: 

246 cur = conn.execute( 

247 'DELETE FROM playlist_batches WHERE spotify_playlist_id ' 

248 '= ? AND user_id = ?', 

249 (sid, int(user_id)), 

250 ) 

251 return int(cur.rowcount or 0) 

252 

253 

254def ensure_batch_records( 

255 store: PlaylistBatchStore, 

256 playlists: list[dict[str, Any]], 

257) -> int: 

258 """Register batch rows for known Spotify playlists not yet tracked.""" 

259 

260 existing = {b['spotify_playlist_id'] for b in store.list_latest_batches()} 

261 created = 0 

262 for row in playlists: 

263 sid = str(row.get('spotify_id') or '').strip() 

264 if not sid or sid in existing: 

265 continue 

266 name = str(row.get('name') or sid).strip() or sid 

267 url = str(row.get('url') or f'https://open.spotify.com/playlist/{sid}') 

268 expected = int(row.get('track_count') or row.get('expected') or 0) 

269 if expected <= 0: 

270 expected = 1 

271 batch_id = store.start_batch(sid, name, url, expected) 

272 store.finish_batch(batch_id, 0, 0, status='incomplete') 

273 existing.add(sid) 

274 created += 1 

275 return created 

276 

277 

278def _row_to_dict(row: Any) -> dict[str, Any]: 

279 return { 

280 'id': int(row['id']), 

281 'spotify_playlist_id': str(row['spotify_playlist_id']), 

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

283 'playlist_url': str(row['playlist_url']), 

284 'expected_count': int(row['expected_count']), 

285 'started_at': str(row['started_at']), 

286 'finished_at': row['finished_at'], 

287 'status': str(row['status']), 

288 'succeeded_count': int(row['succeeded_count']), 

289 'failed_count': int(row['failed_count']), 

290 } 

291 

292 

293def _existing_content_key( 

294 filename: str, download_dir: Path, slskd_dir: Optional[Path] 

295) -> str: 

296 """The ownership key of an on-disk file (``''`` when it moved on).""" 

297 

298 try: 

299 full = locate_library_file(filename, download_dir, slskd_dir) 

300 if full is None: 

301 return '' 

302 return file_content_key(full) or '' 

303 except Exception: 

304 return '' 

305 

306 

307def _norm_stored(name: str) -> str: 

308 return str(name or '').strip().replace('\\', '/') 

309 

310 

311@dataclass(frozen=True) 

312class LibrarySnapshot: 

313 """The library as already known in memory, for checking thousands of 

314 playlist tracks without asking the disk (or a NAS) about each one. 

315 

316 *track_files* is the track index (``{spotify_id: filename}``), 

317 *stored* the playable library paths from the listing snapshot. 

318 """ 

319 

320 track_files: dict[str, str] 

321 stored: frozenset[str] 

322 by_stem: dict[str, str] 

323 

324 @classmethod 

325 def build( 

326 cls, track_files: dict[str, str], stored_paths: Collection[str] 

327 ) -> LibrarySnapshot: 

328 stored = frozenset(_norm_stored(p) for p in stored_paths if p) 

329 by_stem: dict[str, str] = {} 

330 for path in sorted(stored): 

331 stem, dot, ext = path.rpartition('.') 

332 if dot and ext.lower() in _AUDIO_EXTENSIONS: 

333 by_stem.setdefault(stem, path) 

334 return cls( 

335 track_files={ 

336 tid: _norm_stored(name) for tid, name in track_files.items() 

337 }, 

338 stored=stored, 

339 by_stem=by_stem, 

340 ) 

341 

342 def has(self, filename: str) -> bool: 

343 return _norm_stored(filename) in self.stored 

344 

345 def file_for_stem(self, stem: str) -> Optional[str]: 

346 return self.by_stem.get(_norm_stored(stem)) 

347 

348 

349def _file_on_disk( 

350 track: dict[str, Any], 

351 *, 

352 downloader: Any, 

353 track_index: Any, 

354 subdir: Optional[str], 

355 catalog_filenames: Optional[dict[str, str]], 

356) -> Optional[str]: 

357 """The library file holding *track*, checked on disk.""" 

358 

359 hit = resolve_existing_download( 

360 downloader, 

361 track, 

362 subdir=subdir, 

363 track_index=track_index, 

364 ) 

365 if hit: 

366 return hit[0] 

367 tid = normalize_spotify_track_id(track) 

368 if tid and catalog_filenames: 

369 candidate = str(catalog_filenames.get(tid) or '').strip() 

370 if candidate and locate_library_file( 

371 candidate, 

372 Path(downloader.download_dir), 

373 slskd_dir_from_downloader(downloader), 

374 ): 

375 return candidate 

376 return None 

377 

378 

379def _file_in_snapshot( 

380 track: dict[str, Any], 

381 snapshot: LibrarySnapshot, 

382 *, 

383 downloader: Any, 

384 subdir: Optional[str], 

385 catalog_filenames: Optional[dict[str, str]], 

386) -> Optional[str]: 

387 """The library file holding *track*, by the same rules as 

388 :func:`_file_on_disk` (index, expected name, catalog), in memory.""" 

389 

390 tid = normalize_spotify_track_id(track) 

391 canonical = snapshot.track_files.get(tid) if tid else None 

392 if canonical and snapshot.has(canonical): 

393 return canonical 

394 found = snapshot.file_for_stem( 

395 downloader.expected_stem_for(track, subdir=subdir) 

396 ) 

397 if found: 

398 return found 

399 if tid and catalog_filenames: 

400 candidate = str(catalog_filenames.get(tid) or '').strip() 

401 if candidate and snapshot.has(candidate): 

402 return candidate 

403 return None 

404 

405 

406def split_tracks_by_library( 

407 tracks: list[dict[str, Any]], 

408 *, 

409 downloader: Any, 

410 track_index: Any, 

411 subdir: Optional[str] = None, 

412 catalog_filenames: Optional[dict[str, str]] = None, 

413 owned_keys: Optional[Collection[str]] = None, 

414 snapshot: Optional[LibrarySnapshot] = None, 

415) -> tuple[int, list[dict[str, Any]]]: 

416 """Return ``(downloaded_count, missing_tracks)`` for *tracks*. 

417 

418 With *owned_keys*: on-disk files nobody listed owns count as 

419 missing, so queuing them grants ownership through the usual dedup 

420 hit instead of skipping them silently. 

421 

422 With *snapshot*: tracks are matched against it instead of the disk 

423 - no file system access and no track index writes, for summaries 

424 that check every tracked playlist at once. It may lag the disk by a 

425 listing refresh; callers that act on the result check the disk. 

426 """ 

427 

428 download_dir = Path(downloader.download_dir) 

429 slskd_dir = slskd_dir_from_downloader(downloader) 

430 owned = set(owned_keys) if owned_keys is not None else None 

431 downloaded = 0 

432 missing: list[dict[str, Any]] = [] 

433 for track in tracks: 

434 if not track.get('song_id') and not normalize_spotify_track_id(track): 

435 continue 

436 if snapshot is not None: 

437 filename = _file_in_snapshot( 

438 track, 

439 snapshot, 

440 downloader=downloader, 

441 subdir=subdir, 

442 catalog_filenames=catalog_filenames, 

443 ) 

444 else: 

445 filename = _file_on_disk( 

446 track, 

447 downloader=downloader, 

448 track_index=track_index, 

449 subdir=subdir, 

450 catalog_filenames=catalog_filenames, 

451 ) 

452 if filename is not None and owned is not None: 

453 key = _existing_content_key(filename, download_dir, slskd_dir) 

454 if not key or key not in owned: 

455 filename = None 

456 if filename is not None: 

457 downloaded += 1 

458 else: 

459 missing.append(track) 

460 return downloaded, missing 

461 

462 

463def active_queue_count_for_playlist( 

464 spotify_playlist_id: str, 

465 download_jobs: dict[str, dict[str, Any]], 

466 user_id: Optional[int] = None, 

467) -> int: 

468 """Count in-flight queue jobs tied to a Spotify playlist id. 

469 

470 With *user_id*: only the jobs they are waiting on (a shared job 

471 counts for every waiter). 

472 """ 

473 

474 sid = str(spotify_playlist_id or '').strip() 

475 if not sid: 

476 return 0 

477 needle = f'/playlist/{sid}' 

478 count = 0 

479 for job in download_jobs.values(): 

480 status = str(job.get('status') or '') 

481 if status in {'done', 'error'}: 

482 continue 

483 if user_id is not None: 

484 users = job.get('user_ids') 

485 if isinstance(users, set): 

486 if user_id not in users: 

487 continue 

488 elif int(job.get('user_id') or 0) != user_id: 

489 continue 

490 song = job.get('song') or {} 

491 pl_url = str(song.get('downtify_playlist_url') or '') 

492 if needle in pl_url: 

493 count += 1 

494 return count