Coverage for downtify/library_upgrade_db.py: 96%

184 statements  

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

1"""Storage for library upgrade runs, their queue and what was checked. 

2 

3A library upgrade can take hours, so the queue lives in 

4``downtify_library.db`` rather than in memory: stopping the container 

5mid-run and starting it again resumes where it left off. The same 

6database remembers when each track was last looked at, so a second run 

7doesn't ask iTunes and Spotify about tracks that were already checked. 

8""" 

9 

10from __future__ import annotations 

11 

12import json 

13from contextlib import AbstractContextManager 

14from datetime import datetime, timedelta, timezone 

15from pathlib import Path 

16from typing import Any, Iterable, Optional 

17 

18from .db.config import Dialect 

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

20from .db.dialect import insert_returning_id 

21 

22# Run states. 

23STATE_SCANNING = 'scanning' 

24STATE_READY = 'ready' 

25STATE_RUNNING = 'running' 

26STATE_PAUSED = 'paused' 

27STATE_DONE = 'done' 

28STATE_CANCELLED = 'cancelled' 

29 

30#: States a run can still be resumed from after a restart. 

31LIVE_STATES = (STATE_SCANNING, STATE_READY, STATE_RUNNING, STATE_PAUSED) 

32 

33# Job states. 

34JOB_QUEUED = 'queued' 

35JOB_RUNNING = 'running' 

36JOB_DONE = 'done' 

37JOB_SKIPPED = 'skipped' 

38JOB_FAILED = 'failed' 

39 

40 

41def _now_iso() -> str: 

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

43 

44 

45def _json_list(raw: Any) -> list[str]: 

46 try: 

47 value = json.loads(str(raw or '[]')) 

48 except ValueError: 

49 return [] 

50 return [str(item) for item in value] if isinstance(value, list) else [] 

51 

52 

53def _json_dict(raw: Any) -> dict[str, Any]: 

54 try: 

55 value = json.loads(str(raw or '{}')) 

56 except ValueError: 

57 return {} 

58 return value if isinstance(value, dict) else {} 

59 

60 

61def _run_row(row: Any) -> dict[str, Any]: 

62 return { 

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

64 'state': str(row['state']), 

65 'categories': _json_list(row['categories']), 

66 'options': _json_dict(row['options']), 

67 'total_tracks': int(row['total_tracks'] or 0), 

68 'total_bytes': int(row['total_bytes'] or 0), 

69 'summary': _json_dict(row['summary']), 

70 'created_at': str(row['created_at'] or ''), 

71 'finished_at': str(row['finished_at'] or ''), 

72 } 

73 

74 

75def _job_row(row: Any) -> dict[str, Any]: 

76 return { 

77 'file': str(row['stored_path']), 

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

79 'stage': str(row['stage'] or ''), 

80 'categories': _json_list(row['categories']), 

81 'size': int(row['size_bytes'] or 0), 

82 'title': str(row['title'] or ''), 

83 'artist': str(row['artist'] or ''), 

84 'detail': str(row['detail'] or ''), 

85 'changed': _json_list(row['changed']), 

86 'updated_at': str(row['updated_at'] or ''), 

87 } 

88 

89 

90class LibraryUpgradeDB: 

91 """Runs, their queued tracks, and per-track check history.""" 

92 

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

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

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

96 if self.db.dialect is Dialect.SQLITE: 

97 self._init_db() 

98 

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

100 return self.db.connect() 

101 

102 def _init_db(self) -> None: 

103 # SQLite only: on PostgreSQL the migration made this schema. 

104 with self._connect() as conn: 

105 conn.execute(""" 

106 CREATE TABLE IF NOT EXISTS library_upgrade_runs ( 

107 id INTEGER PRIMARY KEY AUTOINCREMENT, 

108 state TEXT NOT NULL, 

109 categories TEXT NOT NULL DEFAULT '[]', 

110 options TEXT NOT NULL DEFAULT '{}', 

111 total_tracks INTEGER NOT NULL DEFAULT 0, 

112 total_bytes INTEGER NOT NULL DEFAULT 0, 

113 summary TEXT NOT NULL DEFAULT '{}', 

114 created_at TEXT NOT NULL, 

115 finished_at TEXT NOT NULL DEFAULT '' 

116 ) 

117 """) 

118 conn.execute(""" 

119 CREATE TABLE IF NOT EXISTS library_upgrade_jobs ( 

120 run_id INTEGER NOT NULL, 

121 stored_path TEXT NOT NULL, 

122 status TEXT NOT NULL, 

123 stage TEXT NOT NULL DEFAULT '', 

124 categories TEXT NOT NULL DEFAULT '[]', 

125 size_bytes INTEGER NOT NULL DEFAULT 0, 

126 title TEXT NOT NULL DEFAULT '', 

127 artist TEXT NOT NULL DEFAULT '', 

128 detail TEXT NOT NULL DEFAULT '', 

129 changed TEXT NOT NULL DEFAULT '[]', 

130 updated_at TEXT NOT NULL, 

131 PRIMARY KEY (run_id, stored_path) 

132 ) 

133 """) 

134 conn.execute(""" 

135 CREATE INDEX IF NOT EXISTS idx_upgrade_jobs_status 

136 ON library_upgrade_jobs (run_id, status) 

137 """) 

138 # One row per track *and category*: repairing artwork says 

139 # nothing about whether the track's lyrics were ever looked 

140 # for, so a later run for another category must not treat the 

141 # track as already handled. 

142 conn.execute(""" 

143 CREATE TABLE IF NOT EXISTS library_upgrade_checks ( 

144 stored_path TEXT NOT NULL, 

145 category TEXT NOT NULL, 

146 checked_at TEXT NOT NULL, 

147 app_version TEXT NOT NULL DEFAULT '', 

148 artwork_px INTEGER NOT NULL DEFAULT 0, 

149 artwork_source TEXT NOT NULL DEFAULT '', 

150 PRIMARY KEY (stored_path, category) 

151 ) 

152 """) 

153 

154 # ── Runs ────────────────────────────────────────────────────── 

155 def create_run( 

156 self, categories: list[str], options: dict[str, Any] 

157 ) -> int: 

158 with self._connect() as conn: 

159 return insert_returning_id( 

160 conn, 

161 """INSERT INTO library_upgrade_runs 

162 (state, categories, options, created_at) 

163 VALUES (?, ?, ?, ?)""", 

164 ( 

165 STATE_SCANNING, 

166 json.dumps(list(categories)), 

167 json.dumps(dict(options)), 

168 _now_iso(), 

169 ), 

170 ) 

171 

172 def latest_run(self) -> Optional[dict[str, Any]]: 

173 with self._connect() as conn: 

174 row = conn.execute( 

175 'SELECT * FROM library_upgrade_runs ORDER BY id DESC LIMIT 1' 

176 ).fetchone() 

177 return _run_row(row) if row is not None else None 

178 

179 def get_run(self, run_id: int) -> Optional[dict[str, Any]]: 

180 with self._connect() as conn: 

181 row = conn.execute( 

182 'SELECT * FROM library_upgrade_runs WHERE id = ?', (run_id,) 

183 ).fetchone() 

184 return _run_row(row) if row is not None else None 

185 

186 def set_state(self, run_id: int, state: str) -> None: 

187 finished = _now_iso() if state in {STATE_DONE, STATE_CANCELLED} else '' 

188 with self._connect() as conn: 

189 conn.execute( 

190 """UPDATE library_upgrade_runs 

191 SET state = ?, finished_at = ? 

192 WHERE id = ?""", 

193 (state, finished, run_id), 

194 ) 

195 

196 def set_categories(self, run_id: int, categories: list[str]) -> None: 

197 with self._connect() as conn: 

198 conn.execute( 

199 'UPDATE library_upgrade_runs SET categories = ? WHERE id = ?', 

200 (json.dumps(list(categories)), run_id), 

201 ) 

202 

203 def set_summary(self, run_id: int, summary: dict[str, Any]) -> None: 

204 """Store what the scan found. 

205 

206 It can't change after the scan, and every progress message 

207 carries it, so it is computed once here rather than aggregated 

208 out of tens of thousands of job rows on each broadcast. 

209 """ 

210 

211 with self._connect() as conn: 

212 conn.execute( 

213 'UPDATE library_upgrade_runs SET summary = ? WHERE id = ?', 

214 (json.dumps(dict(summary)), run_id), 

215 ) 

216 

217 def set_totals(self, run_id: int, tracks: int, size_bytes: int) -> None: 

218 with self._connect() as conn: 

219 conn.execute( 

220 """UPDATE library_upgrade_runs 

221 SET total_tracks = ?, total_bytes = ? 

222 WHERE id = ?""", 

223 (int(tracks), int(size_bytes), run_id), 

224 ) 

225 

226 # ── Queue ───────────────────────────────────────────────────── 

227 def add_jobs(self, run_id: int, jobs: Iterable[dict[str, Any]]) -> int: 

228 rows = [ 

229 ( 

230 run_id, 

231 str(job['file']), 

232 JOB_QUEUED, 

233 json.dumps(list(job.get('categories') or [])), 

234 int(job.get('size') or 0), 

235 str(job.get('title') or ''), 

236 str(job.get('artist') or ''), 

237 _now_iso(), 

238 ) 

239 for job in jobs 

240 ] 

241 if not rows: 

242 return 0 

243 with self._connect() as conn: 

244 conn.executemany( 

245 """INSERT INTO library_upgrade_jobs 

246 (run_id, stored_path, status, categories, size_bytes, 

247 title, artist, updated_at) 

248 VALUES (?, ?, ?, ?, ?, ?, ?, ?) 

249 ON CONFLICT(run_id, stored_path) DO NOTHING""", 

250 rows, 

251 ) 

252 return len(rows) 

253 

254 def take_next(self, run_id: int) -> Optional[dict[str, Any]]: 

255 """Claim the next queued track, marking it running. 

256 

257 The read and the update share one transaction. On PostgreSQL the 

258 row is locked, skipping rows another worker already holds, so two 

259 claimers never take the same track. 

260 """ 

261 

262 claim = '' 

263 if self.db.dialect is Dialect.POSTGRES: 

264 claim = ' FOR UPDATE SKIP LOCKED' 

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

266 row = conn.execute( 

267 'SELECT * FROM library_upgrade_jobs ' 

268 'WHERE run_id = ? AND status = ? ' 

269 'ORDER BY stored_path LIMIT 1' + claim, 

270 (run_id, JOB_QUEUED), 

271 ).fetchone() 

272 if row is None: 

273 return None 

274 conn.execute( 

275 """UPDATE library_upgrade_jobs 

276 SET status = ?, updated_at = ? 

277 WHERE run_id = ? AND stored_path = ?""", 

278 (JOB_RUNNING, _now_iso(), run_id, row['stored_path']), 

279 ) 

280 job = _job_row(row) 

281 job['status'] = JOB_RUNNING 

282 return job 

283 

284 def set_stage(self, run_id: int, stored_path: str, stage: str) -> None: 

285 with self._connect() as conn: 

286 conn.execute( 

287 """UPDATE library_upgrade_jobs SET stage = ?, updated_at = ? 

288 WHERE run_id = ? AND stored_path = ?""", 

289 (stage, _now_iso(), run_id, stored_path), 

290 ) 

291 

292 def finish_job( 

293 self, 

294 run_id: int, 

295 stored_path: str, 

296 status: str, 

297 *, 

298 detail: str = '', 

299 changed: Optional[list[str]] = None, 

300 ) -> None: 

301 with self._connect() as conn: 

302 conn.execute( 

303 """UPDATE library_upgrade_jobs 

304 SET status = ?, stage = '', detail = ?, changed = ?, 

305 updated_at = ? 

306 WHERE run_id = ? AND stored_path = ?""", 

307 ( 

308 status, 

309 detail[:500], 

310 json.dumps(list(changed or [])), 

311 _now_iso(), 

312 run_id, 

313 stored_path, 

314 ), 

315 ) 

316 

317 def requeue_running(self, run_id: int) -> int: 

318 """Put jobs interrupted by a restart back in the queue.""" 

319 

320 with self._connect() as conn: 

321 cur = conn.execute( 

322 """UPDATE library_upgrade_jobs 

323 SET status = ?, stage = '', updated_at = ? 

324 WHERE run_id = ? AND status = ?""", 

325 (JOB_QUEUED, _now_iso(), run_id, JOB_RUNNING), 

326 ) 

327 return cur.rowcount 

328 

329 def counts(self, run_id: int) -> dict[str, Any]: 

330 with self._connect() as conn: 

331 rows = conn.execute( 

332 """SELECT status, COUNT(*) AS n, 

333 COALESCE(SUM(size_bytes), 0) AS bytes 

334 FROM library_upgrade_jobs WHERE run_id = ? 

335 GROUP BY status""", 

336 (run_id,), 

337 ).fetchall() 

338 by_status = {str(row['status']): int(row['n']) for row in rows} 

339 done_bytes = sum( 

340 int(row['bytes']) 

341 for row in rows 

342 if str(row['status']) in {JOB_DONE, JOB_SKIPPED, JOB_FAILED} 

343 ) 

344 total = sum(by_status.values()) 

345 finished = ( 

346 by_status.get(JOB_DONE, 0) 

347 + by_status.get(JOB_SKIPPED, 0) 

348 + by_status.get(JOB_FAILED, 0) 

349 ) 

350 return { 

351 'total': total, 

352 'queued': by_status.get(JOB_QUEUED, 0), 

353 'running': by_status.get(JOB_RUNNING, 0), 

354 'completed': by_status.get(JOB_DONE, 0), 

355 'skipped': by_status.get(JOB_SKIPPED, 0), 

356 'failed': by_status.get(JOB_FAILED, 0), 

357 'finished': finished, 

358 'processed_bytes': done_bytes, 

359 } 

360 

361 def jobs( 

362 self, 

363 run_id: int, 

364 *, 

365 status: Optional[str] = None, 

366 limit: int = 200, 

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

368 query = 'SELECT * FROM library_upgrade_jobs WHERE run_id = ?' 

369 params: list[Any] = [run_id] 

370 if status: 

371 query += ' AND status = ?' 

372 params.append(status) 

373 query += ' ORDER BY updated_at DESC, stored_path LIMIT ?' 

374 params.append(int(limit)) 

375 with self._connect() as conn: 

376 rows = conn.execute(query, params).fetchall() 

377 return [_job_row(row) for row in rows] 

378 

379 def delete_run_jobs(self, run_id: int) -> None: 

380 with self._connect() as conn: 

381 conn.execute( 

382 'DELETE FROM library_upgrade_jobs WHERE run_id = ?', (run_id,) 

383 ) 

384 

385 # ── Per-track check history ─────────────────────────────────── 

386 def record_check( 

387 self, 

388 stored_path: str, 

389 categories: list[str], 

390 *, 

391 app_version: str, 

392 artwork_px: int = 0, 

393 artwork_source: str = '', 

394 ) -> None: 

395 """Remember that these categories were just looked at for a track.""" 

396 

397 rows = [ 

398 ( 

399 stored_path, 

400 category, 

401 _now_iso(), 

402 app_version, 

403 int(artwork_px), 

404 artwork_source, 

405 ) 

406 for category in categories 

407 ] 

408 if not rows: 

409 return 

410 with self._connect() as conn: 

411 conn.executemany( 

412 """INSERT INTO library_upgrade_checks 

413 (stored_path, category, checked_at, app_version, 

414 artwork_px, artwork_source) 

415 VALUES (?, ?, ?, ?, ?, ?) 

416 ON CONFLICT(stored_path, category) DO UPDATE SET 

417 checked_at = excluded.checked_at, 

418 app_version = excluded.app_version, 

419 artwork_px = CASE 

420 WHEN excluded.artwork_px 

421 > library_upgrade_checks.artwork_px 

422 THEN excluded.artwork_px 

423 ELSE library_upgrade_checks.artwork_px 

424 END, 

425 artwork_source = excluded.artwork_source""", 

426 rows, 

427 ) 

428 

429 def record_checks( 

430 self, 

431 entries: Iterable[tuple[str, list[str]]], 

432 *, 

433 app_version: str, 

434 ) -> int: 

435 """Record many tracks' checks at once. 

436 

437 The scan uses this for the categories it examined and found 

438 nothing to do for: looking and finding nothing is a check like 

439 any other, and writing them one connection per track would cost 

440 tens of thousands of round-trips on a large library. 

441 """ 

442 

443 now = _now_iso() 

444 rows = [ 

445 (path, category, now, app_version) 

446 for path, categories in entries 

447 for category in categories 

448 ] 

449 if not rows: 

450 return 0 

451 with self._connect() as conn: 

452 conn.executemany( 

453 """INSERT INTO library_upgrade_checks 

454 (stored_path, category, checked_at, app_version) 

455 VALUES (?, ?, ?, ?) 

456 ON CONFLICT(stored_path, category) DO UPDATE SET 

457 checked_at = excluded.checked_at, 

458 app_version = excluded.app_version""", 

459 rows, 

460 ) 

461 return len(rows) 

462 

463 def checks_for( 

464 self, paths: list[str] 

465 ) -> dict[str, dict[str, dict[str, Any]]]: 

466 """``{stored_path: {category: check}}`` for the paths given.""" 

467 

468 if not paths: 

469 return {} 

470 found: dict[str, dict[str, dict[str, Any]]] = {} 

471 with self._connect() as conn: 

472 for start in range(0, len(paths), 400): 

473 chunk = paths[start : start + 400] 

474 marks = ','.join('?' * len(chunk)) 

475 rows = conn.execute( 

476 f"""SELECT * FROM library_upgrade_checks 

477 WHERE stored_path IN ({marks})""", # noqa: S608 

478 chunk, 

479 ).fetchall() 

480 for row in rows: 

481 found.setdefault(str(row['stored_path']), {})[ 

482 str(row['category']) 

483 ] = { 

484 'checked_at': str(row['checked_at'] or ''), 

485 'app_version': str(row['app_version'] or ''), 

486 'artwork_px': int(row['artwork_px'] or 0), 

487 'artwork_source': str(row['artwork_source'] or ''), 

488 } 

489 return found 

490 

491 def forget_checks(self) -> int: 

492 with self._connect() as conn: 

493 cur = conn.execute('DELETE FROM library_upgrade_checks') 

494 return cur.rowcount 

495 

496 

497def check_is_fresh( 

498 check: Optional[dict[str, Any]], 

499 *, 

500 app_version: str, 

501 max_age_days: int, 

502) -> bool: 

503 """True when this track was checked recently by this Downtify version. 

504 

505 A newer Downtify may match or tag better than the one that ran 

506 before, so a check made by another version is never treated as 

507 fresh — that is what lets an upgraded install re-examine a library 

508 that an older one already went through. 

509 """ 

510 

511 if not check or max_age_days <= 0: 

512 return False 

513 if str(check.get('app_version') or '') != app_version: 

514 return False 

515 try: 

516 checked = datetime.fromisoformat(str(check.get('checked_at') or '')) 

517 except ValueError: 

518 return False 

519 if checked.tzinfo is None: 

520 checked = checked.replace(tzinfo=timezone.utc) 

521 age = datetime.now(timezone.utc) - checked 

522 return age < timedelta(days=max_age_days) 

523 

524 

525def lyrics_outcome(db: LibraryUpgradeDB, run_id: int) -> dict[str, int]: 

526 """Of a run's tracks queued for lyrics, how many got them (``added``), 

527 came back without (``not_found``), failed, or are still to go 

528 (``pending``).""" 

529 

530 found = {'added': 0, 'not_found': 0, 'failed': 0, 'pending': 0} 

531 with db.db.connect() as conn: 

532 rows = conn.execute( 

533 """SELECT status, changed FROM library_upgrade_jobs 

534 WHERE run_id = ? AND categories LIKE ?""", 

535 (run_id, '%"lyrics"%'), 

536 ).fetchall() 

537 for row in rows: 

538 status = str(row['status']) 

539 if status == JOB_FAILED: 

540 found['failed'] += 1 

541 elif status in {JOB_DONE, JOB_SKIPPED}: 

542 added = 'lyrics' in _json_list(row['changed']) 

543 found['added' if added else 'not_found'] += 1 

544 else: 

545 found['pending'] += 1 

546 return found