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
« 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.
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"""
10from __future__ import annotations
12import json
13from contextlib import AbstractContextManager
14from datetime import datetime, timedelta, timezone
15from pathlib import Path
16from typing import Any, Iterable, Optional
18from .db.config import Dialect
19from .db.core import Connection, Database, as_database
20from .db.dialect import insert_returning_id
22# Run states.
23STATE_SCANNING = 'scanning'
24STATE_READY = 'ready'
25STATE_RUNNING = 'running'
26STATE_PAUSED = 'paused'
27STATE_DONE = 'done'
28STATE_CANCELLED = 'cancelled'
30#: States a run can still be resumed from after a restart.
31LIVE_STATES = (STATE_SCANNING, STATE_READY, STATE_RUNNING, STATE_PAUSED)
33# Job states.
34JOB_QUEUED = 'queued'
35JOB_RUNNING = 'running'
36JOB_DONE = 'done'
37JOB_SKIPPED = 'skipped'
38JOB_FAILED = 'failed'
41def _now_iso() -> str:
42 return datetime.now(timezone.utc).isoformat()
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 []
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 {}
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 }
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 }
90class LibraryUpgradeDB:
91 """Runs, their queued tracks, and per-track check history."""
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()
99 def _connect(self) -> AbstractContextManager[Connection]:
100 return self.db.connect()
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 """)
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 )
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
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
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 )
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 )
203 def set_summary(self, run_id: int, summary: dict[str, Any]) -> None:
204 """Store what the scan found.
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 """
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 )
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 )
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)
254 def take_next(self, run_id: int) -> Optional[dict[str, Any]]:
255 """Claim the next queued track, marking it running.
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 """
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
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 )
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 )
317 def requeue_running(self, run_id: int) -> int:
318 """Put jobs interrupted by a restart back in the queue."""
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
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 }
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]
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 )
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."""
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 )
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.
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 """
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)
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."""
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
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
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.
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 """
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)
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``)."""
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