Coverage for downtify/library_metadata_cache.py: 80%
250 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"""SQLite/PostgreSQL cache of per-file library display metadata."""
3from __future__ import annotations
5import json
6import stat
7from concurrent.futures import ThreadPoolExecutor
8from datetime import datetime, timezone
9from pathlib import Path
10from typing import Any, Optional
12from .db.config import Dialect
13from .db.core import Connection, Database, as_database
14from .library_cache_keys import (
15 file_content_key,
16 file_content_key_from_name_and_size,
17)
18from .library_metadata import library_entry_for_file
19from .library_paths import locate_library_file
21_TAG_READ_THREADS = 8
23#: Bumped whenever a row gains fields read from the file's tags: rows
24#: cached by an older version are treated as stale and re-read once.
25#: 4: the audio format (codec, bitrate, sample rate, channels).
26#: 5: genre (from tags, or iTunes when Sync cannot write the file).
27#: 6: the explicit mark.
28#: 7: every credited artist (``artists``, from the ARTISTS tag), with an
29#: act split into its members read as its album artist
30#: (``library_metadata.split_artists``), and the compilation flag.
31#: 8: the full release date from audio tags.
32#: 9: the ISRC (downtify.isrc), which songs are matched by first.
33META_VERSION = 9
35#: Columns added after the table was first created, with their SQL type.
36_ADDED_COLUMNS = {
37 'has_cover': 'INTEGER NOT NULL DEFAULT 0',
38 'cover_px': 'INTEGER NOT NULL DEFAULT 0',
39 'album_artist': "TEXT NOT NULL DEFAULT ''",
40 'track_number': 'INTEGER NOT NULL DEFAULT 0',
41 'year': "TEXT NOT NULL DEFAULT ''",
42 'release_date': "TEXT NOT NULL DEFAULT ''",
43 'duration': 'REAL NOT NULL DEFAULT 0',
44 'meta_version': 'INTEGER NOT NULL DEFAULT 1',
45 'codec': "TEXT NOT NULL DEFAULT ''",
46 'bitrate': 'INTEGER NOT NULL DEFAULT 0',
47 'sample_rate': 'INTEGER NOT NULL DEFAULT 0',
48 'channels': 'INTEGER NOT NULL DEFAULT 0',
49 'genre': "TEXT NOT NULL DEFAULT ''",
50 'explicit': 'INTEGER NOT NULL DEFAULT 0',
51 'artists': "TEXT NOT NULL DEFAULT '[]'",
52 'compilation': 'INTEGER NOT NULL DEFAULT 0',
53 'isrc': "TEXT NOT NULL DEFAULT ''",
54}
56_ROW_COLUMNS = (
57 'content_key, filename, file_mtime_ns, file_size, title, artist, '
58 'album, has_cover, cover_px, album_artist, track_number, year, '
59 'release_date, '
60 'duration, meta_version, codec, bitrate, sample_rate, channels, '
61 'genre, explicit, artists, compilation, isrc'
62)
64#: Columns written by the SQLite-only legacy table rebuild.
65_LEGACY_COLUMNS = (
66 'content_key',
67 'filename',
68 'title',
69 'artist',
70 'album',
71 'file_mtime_ns',
72 'file_size',
73 'cached_at',
74)
75_LEGACY_INSERT = (
76 f'INSERT INTO library_metadata_new ({", ".join(_LEGACY_COLUMNS)}) '
77 f'VALUES ({", ".join("?" * len(_LEGACY_COLUMNS))}) '
78 'ON CONFLICT (content_key) DO UPDATE SET '
79 + ', '.join(
80 f'{column} = excluded.{column}'
81 for column in _LEGACY_COLUMNS
82 if column != 'content_key'
83 )
84)
87def _now_iso() -> str:
88 return datetime.now(timezone.utc).isoformat()
91def _norm_filename(filename: str) -> str:
92 return str(filename or '').strip().replace('\\', '/')
95def _file_stat(full_path: Path) -> tuple[int, int]:
96 st = full_path.stat()
97 return int(st.st_mtime_ns), int(st.st_size)
100def _chunked(values: list[str], size: int):
101 for index in range(0, len(values), size):
102 chunk = values[index : index + size]
103 if chunk:
104 yield chunk
107def _row_entry(stored: str, row: Any, item: dict[str, Any]) -> dict[str, Any]:
108 """A ``/tracks`` row from a fresh cache row plus the file's stat."""
110 return {
111 'file': stored,
112 'title': str(row['title'] or ''),
113 'artist': str(row['artist'] or ''),
114 'artists': _artists_from_json(row['artists']),
115 'album': str(row['album'] or ''),
116 'album_artist': str(row['album_artist'] or ''),
117 'track_number': int(row['track_number'] or 0),
118 'year': str(row['year'] or ''),
119 'release_date': str(row['release_date'] or ''),
120 'duration': float(row['duration'] or 0.0),
121 'codec': str(row['codec'] or ''),
122 'bitrate': int(row['bitrate'] or 0),
123 'sample_rate': int(row['sample_rate'] or 0),
124 'channels': int(row['channels'] or 0),
125 'genre': str(row['genre'] or '') if 'genre' in row.keys() else '',
126 'explicit': bool(int(row['explicit'] or 0))
127 if 'explicit' in row.keys()
128 else False,
129 'compilation': bool(int(row['compilation'] or 0)),
130 'isrc': str(row['isrc'] or '') if 'isrc' in row.keys() else '',
131 'has_cover': bool(int(row['has_cover'] or 0)),
132 'cover_px': int(row['cover_px'] or 0),
133 'added': int(item['mtime_ns']) // 1_000_000_000,
134 'size': int(item['size']),
135 }
138def _artists_from_json(raw: Any) -> list[str]:
139 try:
140 value = json.loads(raw or '[]')
141 except (TypeError, ValueError):
142 return []
143 if not isinstance(value, list):
144 return []
145 return [str(name) for name in value if str(name).strip()]
148class LibraryMetadataCache:
149 """Caches ``/list`` rows; primary key is basename+size, not full path."""
151 def __init__(self, path_or_db: str | Path | Database) -> None:
152 self.db = as_database(path_or_db, 'library')
153 if isinstance(path_or_db, Database):
154 self._path = str(self.db.path) if self.db.path else None
155 else:
156 self._path = str(path_or_db)
157 self._init_db()
159 def _connect(self):
160 return self.db.connect()
162 def _init_db(self) -> None:
163 # The Postgres schema is created by migrations; only SQLite needs DDL.
164 if self.db.dialect is not Dialect.SQLITE:
165 return
166 with self._connect() as conn:
167 row = conn.execute(
168 "SELECT sql FROM sqlite_master WHERE name='library_metadata'"
169 ).fetchone()
170 if row is None:
171 conn.execute("""
172 CREATE TABLE library_metadata (
173 content_key TEXT PRIMARY KEY,
174 filename TEXT NOT NULL,
175 title TEXT NOT NULL DEFAULT '',
176 artist TEXT NOT NULL DEFAULT '',
177 album TEXT NOT NULL DEFAULT '',
178 has_cover INTEGER NOT NULL DEFAULT 0,
179 cover_px INTEGER NOT NULL DEFAULT 0,
180 album_artist TEXT NOT NULL DEFAULT '',
181 track_number INTEGER NOT NULL DEFAULT 0,
182 year TEXT NOT NULL DEFAULT '',
183 release_date TEXT NOT NULL DEFAULT '',
184 duration REAL NOT NULL DEFAULT 0,
185 meta_version INTEGER NOT NULL DEFAULT 1,
186 codec TEXT NOT NULL DEFAULT '',
187 bitrate INTEGER NOT NULL DEFAULT 0,
188 sample_rate INTEGER NOT NULL DEFAULT 0,
189 channels INTEGER NOT NULL DEFAULT 0,
190 genre TEXT NOT NULL DEFAULT '',
191 explicit INTEGER NOT NULL DEFAULT 0,
192 artists TEXT NOT NULL DEFAULT '[]',
193 compilation INTEGER NOT NULL DEFAULT 0,
194 isrc TEXT NOT NULL DEFAULT '',
195 file_mtime_ns INTEGER NOT NULL,
196 file_size INTEGER NOT NULL,
197 cached_at TEXT NOT NULL
198 )
199 """)
200 conn.execute("""
201 CREATE INDEX IF NOT EXISTS idx_library_metadata_filename
202 ON library_metadata (filename)
203 """)
204 return
205 if not (row[0] and 'content_key' in str(row[0])):
206 self._migrate_legacy_table(conn)
207 self._ensure_columns(conn)
209 @staticmethod
210 def _ensure_columns(conn: Connection) -> None:
211 cols = {
212 str(r[1])
213 for r in conn.execute('PRAGMA table_info(library_metadata)')
214 }
215 for name, sql_type in _ADDED_COLUMNS.items():
216 if name not in cols:
217 conn.execute(
218 f'ALTER TABLE library_metadata ADD COLUMN {name} {sql_type}'
219 )
221 @staticmethod
222 def _migrate_legacy_table(conn: Connection) -> None:
223 conn.execute("""
224 CREATE TABLE library_metadata_new (
225 content_key TEXT PRIMARY KEY,
226 filename TEXT NOT NULL,
227 title TEXT NOT NULL DEFAULT '',
228 artist TEXT NOT NULL DEFAULT '',
229 album TEXT NOT NULL DEFAULT '',
230 file_mtime_ns INTEGER NOT NULL,
231 file_size INTEGER NOT NULL,
232 cached_at TEXT NOT NULL
233 )
234 """)
235 rows = conn.execute('SELECT * FROM library_metadata').fetchall()
236 for row in rows:
237 filename = str(row['filename'])
238 file_size = int(row['file_size'])
239 ck = file_content_key_from_name_and_size(filename, file_size)
240 if not ck:
241 continue
242 conn.execute(
243 _LEGACY_INSERT,
244 (
245 ck,
246 filename,
247 str(row['title'] or ''),
248 str(row['artist'] or ''),
249 str(row['album'] or ''),
250 int(row['file_mtime_ns']),
251 file_size,
252 str(row['cached_at']),
253 ),
254 )
255 conn.execute('DROP TABLE library_metadata')
256 conn.execute(
257 'ALTER TABLE library_metadata_new RENAME TO library_metadata'
258 )
259 conn.execute("""
260 CREATE INDEX IF NOT EXISTS idx_library_metadata_filename
261 ON library_metadata (filename)
262 """)
264 def get_entry(self, stored_path: str, full_path: Path) -> dict[str, Any]:
265 """Return a ``/list`` row, reading tags only when cache is missing or stale."""
267 rows = self.get_entries_batch([(stored_path, full_path)])
268 return (
269 rows[0] if rows else library_entry_for_file(stored_path, full_path)
270 )
272 def get_entries_batch(
273 self, items: list[tuple[str, Path]]
274 ) -> list[dict[str, Any]]:
275 """Resolve many ``/tracks`` rows with one DB connection and batched lookups."""
277 if not items:
278 return []
280 prepared: list[dict[str, Any]] = []
281 for stored_path, full_path in items:
282 name = _norm_filename(stored_path)
283 if not name:
284 prepared.append({
285 'stored': stored_path,
286 'full': full_path,
287 'ready': False,
288 })
289 continue
290 try:
291 file_stat = full_path.stat()
292 except OSError:
293 prepared.append({
294 'stored': stored_path,
295 'full': full_path,
296 'ready': False,
297 })
298 continue
299 if not stat.S_ISREG(file_stat.st_mode):
300 prepared.append({
301 'stored': stored_path,
302 'full': full_path,
303 'ready': False,
304 })
305 continue
306 mtime_ns, size = int(file_stat.st_mtime_ns), int(file_stat.st_size)
307 ck = file_content_key_from_name_and_size(full_path.name, size)
308 prepared.append({
309 'stored': stored_path,
310 'full': full_path,
311 'name': name,
312 'ck': ck,
313 'mtime_ns': mtime_ns,
314 'size': size,
315 'ready': True,
316 })
318 content_keys = [
319 str(p['ck']) for p in prepared if p.get('ready') and p.get('ck')
320 ]
321 filenames = [str(p['name']) for p in prepared if p.get('ready')]
323 by_ck: dict[str, Any] = {}
324 by_name: dict[str, Any] = {}
325 with self._connect() as conn:
326 for chunk in _chunked(content_keys, 400):
327 placeholders = ','.join('?' * len(chunk))
328 query = f"""SELECT {_ROW_COLUMNS}
329 FROM library_metadata
330 WHERE content_key IN ({placeholders})"""
331 for row in conn.execute(query, chunk):
332 by_ck[str(row['content_key'])] = row
333 for chunk in _chunked(filenames, 400):
334 placeholders = ','.join('?' * len(chunk))
335 query = f"""SELECT {_ROW_COLUMNS}
336 FROM library_metadata
337 WHERE filename IN ({placeholders})"""
338 for row in conn.execute(query, chunk):
339 by_name[_norm_filename(str(row['filename']))] = row
341 results: list[Optional[dict[str, Any]]] = []
342 filename_updates: list[tuple[str, str]] = []
343 misses: list[tuple[int, dict[str, Any]]] = []
345 for item in prepared:
346 stored = str(item['stored'])
347 if not item.get('ready'):
348 misses.append((len(results), item))
349 results.append(None)
350 continue
352 name = str(item['name'])
353 ck = item.get('ck')
354 row = by_ck.get(str(ck)) if ck else None
355 if row is None:
356 row = by_name.get(name)
357 if (
358 row is not None
359 and int(row['file_mtime_ns']) == int(item['mtime_ns'])
360 and int(row['file_size']) == int(item['size'])
361 and int(row['meta_version'] or 1) >= META_VERSION
362 ):
363 if _norm_filename(str(row['filename'])) != name and ck:
364 filename_updates.append((name, str(ck)))
365 results.append(_row_entry(stored, row, item))
366 continue
367 misses.append((len(results), item))
368 results.append(None)
370 # Tag reads on a miss mostly wait on the disk (HDD seeks, NAS
371 # round-trips), where threads parallelize well — 2,000 files with a
372 # simulated 2 ms I/O latency went from ~5 s sequential to ~0.6 s on
373 # 8 threads.
374 with ThreadPoolExecutor(
375 max_workers=_TAG_READ_THREADS, thread_name_prefix='downtify-tags'
376 ) as pool:
377 read = list(
378 pool.map(
379 lambda miss: library_entry_for_file(
380 str(miss[1]['stored']), miss[1]['full']
381 ),
382 misses,
383 )
384 )
386 pending_stores: list[
387 tuple[str, Optional[str], dict[str, Any], int, int]
388 ] = []
389 for (index, item), entry in zip(misses, read):
390 results[index] = entry
391 if item.get('ready'):
392 pending_stores.append((
393 str(item['name']),
394 item.get('ck'),
395 entry,
396 int(item['mtime_ns']),
397 int(item['size']),
398 ))
400 if filename_updates or pending_stores:
401 with self._connect() as conn:
402 for index, (name, ck, entry, mtime_ns, size) in enumerate(
403 pending_stores,
404 start=1,
405 ):
406 self._store_conn(conn, name, ck, entry, mtime_ns, size)
407 if index % 100 == 0:
408 conn.commit()
409 for name, ck in filename_updates:
410 conn.execute(
411 'UPDATE library_metadata SET filename = ? WHERE content_key = ?',
412 (name, ck),
413 )
415 return [entry for entry in results if entry is not None]
417 def refresh(
418 self, stored_path: str, full_path: Path
419 ) -> Optional[dict[str, Any]]:
420 """Re-read tags from disk and update the cache (e.g. after download)."""
422 name = _norm_filename(stored_path)
423 if not name or not full_path.is_file():
424 self.forget(stored_path, full_path=full_path)
425 return None
426 try:
427 mtime_ns, size = _file_stat(full_path)
428 except OSError:
429 self.forget(stored_path, full_path=full_path)
430 return None
431 entry = library_entry_for_file(stored_path, full_path)
432 self._store(name, full_path, entry, mtime_ns, size)
433 return entry
435 def refresh_stored_path(
436 self,
437 stored_path: str,
438 *,
439 download_dir: Path,
440 slskd_dir: Optional[Path] = None,
441 ) -> None:
442 full = locate_library_file(stored_path, download_dir, slskd_dir)
443 if full is not None:
444 self.refresh(stored_path, full)
446 def forget(
447 self,
448 filename: str,
449 *,
450 full_path: Optional[Path] = None,
451 ) -> None:
452 name = _norm_filename(filename)
453 if not name:
454 return
455 keys: list[str] = []
456 if full_path is not None:
457 ck = file_content_key(full_path)
458 if ck:
459 keys.append(ck)
460 with self._connect() as conn:
461 conn.execute(
462 'DELETE FROM library_metadata WHERE filename = ?', (name,)
463 )
464 for ck in keys:
465 conn.execute(
466 'DELETE FROM library_metadata WHERE content_key = ?', (ck,)
467 )
469 def _fetch_valid(
470 self,
471 stored_path: str,
472 full_path: Path,
473 mtime_ns: int,
474 size: int,
475 ) -> Optional[tuple[int, int, str, str, str]]:
476 ck = file_content_key(full_path)
477 if ck:
478 row = self._fetch_by_content_key(ck)
479 if row is not None and row[0] == mtime_ns and row[1] == size:
480 self._touch_filename(ck, stored_path)
481 return row
482 row = self._fetch_by_filename(stored_path)
483 if row is not None and row[0] == mtime_ns and row[1] == size:
484 return row
485 return None
487 def _touch_filename(self, content_key: str, filename: str) -> None:
488 name = _norm_filename(filename)
489 if not name:
490 return
491 with self._connect() as conn:
492 conn.execute(
493 'UPDATE library_metadata SET filename = ? WHERE content_key = ?',
494 (name, content_key),
495 )
497 def _fetch_by_content_key(
498 self, content_key: str
499 ) -> Optional[tuple[int, int, str, str, str]]:
500 with self._connect() as conn:
501 row = conn.execute(
502 """SELECT file_mtime_ns, file_size, title, artist, album
503 FROM library_metadata WHERE content_key = ?""",
504 (content_key,),
505 ).fetchone()
506 if row is None:
507 return None
508 return (
509 int(row['file_mtime_ns']),
510 int(row['file_size']),
511 str(row['title'] or ''),
512 str(row['artist'] or ''),
513 str(row['album'] or ''),
514 )
516 def _fetch_by_filename(
517 self, filename: str
518 ) -> Optional[tuple[int, int, str, str, str]]:
519 with self._connect() as conn:
520 row = conn.execute(
521 """SELECT file_mtime_ns, file_size, title, artist, album
522 FROM library_metadata WHERE filename = ?""",
523 (filename,),
524 ).fetchone()
525 if row is None:
526 return None
527 return (
528 int(row['file_mtime_ns']),
529 int(row['file_size']),
530 str(row['title'] or ''),
531 str(row['artist'] or ''),
532 str(row['album'] or ''),
533 )
535 @staticmethod
536 def _store_conn(
537 conn: Connection,
538 filename: str,
539 content_key: Optional[str],
540 entry: dict[str, Any],
541 mtime_ns: int,
542 file_size: int,
543 ) -> None:
544 if not content_key:
545 return
546 name = _norm_filename(filename)
547 conn.execute(
548 """INSERT INTO library_metadata
549 (content_key, filename, title, artist, album, has_cover,
550 cover_px, album_artist, track_number, year, release_date,
551 duration,
552 meta_version, codec, bitrate, sample_rate, channels, genre,
553 explicit, artists, compilation, isrc, file_mtime_ns,
554 file_size, cached_at)
555 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
556 ?, ?, ?, ?, ?, ?, ?)
557 ON CONFLICT(content_key) DO UPDATE SET
558 filename=excluded.filename,
559 title=excluded.title,
560 artist=excluded.artist,
561 album=excluded.album,
562 has_cover=excluded.has_cover,
563 cover_px=excluded.cover_px,
564 album_artist=excluded.album_artist,
565 track_number=excluded.track_number,
566 year=excluded.year,
567 release_date=excluded.release_date,
568 duration=excluded.duration,
569 meta_version=excluded.meta_version,
570 codec=excluded.codec,
571 bitrate=excluded.bitrate,
572 sample_rate=excluded.sample_rate,
573 channels=excluded.channels,
574 genre=excluded.genre,
575 explicit=excluded.explicit,
576 artists=excluded.artists,
577 compilation=excluded.compilation,
578 isrc=excluded.isrc,
579 file_mtime_ns=excluded.file_mtime_ns,
580 file_size=excluded.file_size,
581 cached_at=excluded.cached_at""",
582 (
583 content_key,
584 name,
585 str(entry.get('title') or ''),
586 str(entry.get('artist') or ''),
587 str(entry.get('album') or ''),
588 1 if entry.get('has_cover') else 0,
589 int(entry.get('cover_px') or 0),
590 str(entry.get('album_artist') or ''),
591 int(entry.get('track_number') or 0),
592 str(entry.get('year') or ''),
593 str(entry.get('release_date') or ''),
594 float(entry.get('duration') or 0.0),
595 META_VERSION,
596 str(entry.get('codec') or ''),
597 int(entry.get('bitrate') or 0),
598 int(entry.get('sample_rate') or 0),
599 int(entry.get('channels') or 0),
600 str(entry.get('genre') or ''),
601 1 if entry.get('explicit') else 0,
602 json.dumps(
603 [str(a) for a in entry.get('artists') or []],
604 ensure_ascii=False,
605 ),
606 1 if entry.get('compilation') else 0,
607 str(entry.get('isrc') or ''),
608 mtime_ns,
609 file_size,
610 _now_iso(),
611 ),
612 )
614 def _store(
615 self,
616 filename: str,
617 full_path: Path,
618 entry: dict[str, Any],
619 mtime_ns: int,
620 file_size: int,
621 ) -> None:
622 ck = file_content_key(full_path)
623 if not ck:
624 return
625 with self._connect() as conn:
626 self._store_conn(
627 conn,
628 filename,
629 ck,
630 entry,
631 mtime_ns,
632 file_size,
633 )
635 def merge_fields(
636 self,
637 stored_path: str,
638 full_path: Path,
639 fields: dict[str, Any],
640 ) -> None:
641 """Patch cached display fields without re-reading the audio file."""
643 name = _norm_filename(stored_path)
644 ck = file_content_key(full_path)
645 if not name or not ck or not fields:
646 return
647 assignments: list[str] = []
648 values: list[Any] = []
649 if 'has_cover' in fields:
650 assignments.append('has_cover = ?')
651 values.append(1 if fields.get('has_cover') else 0)
652 if 'cover_px' in fields:
653 assignments.append('cover_px = ?')
654 values.append(int(fields.get('cover_px') or 0))
655 if 'genre' in fields:
656 assignments.append('genre = ?')
657 values.append(str(fields.get('genre') or ''))
658 if not assignments:
659 return
660 assignments.append('cached_at = ?')
661 values.append(_now_iso())
662 values.append(ck)
663 with self._connect() as conn:
664 conn.execute(
665 f"""UPDATE library_metadata SET {', '.join(assignments)}
666 WHERE content_key = ?""",
667 values,
668 )