Coverage for downtify/library_sync.py: 99%
264 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"""The library as a mobile app syncs it: stable track ids and a change feed.
3A phone keeps its own copy of the library (to browse offline, and to
4remember offline files, likes, play history and a queue), so it needs to
5recognise a track across time. The file path isn't enough - files move
6when *Organize by artist/album* changes or a playlist folder is renamed,
7and get renamed when the output template changes - so each track gets a
8**track id** of its own the first time it's seen, and keeps it:
101. same path as before -> same id (a re-tag in place keeps its id, even
11 though the file's size changes);
122. a new path, when a track that was there before has vanished: the same
13 id if exactly one vanished track has the same file name and size (a
14 move), or else the same title/artist/album/length (a rename);
153. anything else is a new track with a new id. A file both moved *and*
16 re-tagged in the same sweep comes back as a new track; the old id is
17 reported deleted.
19The feed: every change - a track added, changed (tags, cover, file) or
20removed - bumps a single sequence number, and a client asks
21``GET /api/v1/library?since=<cursor>`` for everything after the cursor
22it last saw. Removed tracks are remembered (as ids) for
23:data:`TOMBSTONE_DAYS` days; a client whose cursor is older than that
24gets a full list back (``full: true``) and replaces what it has.
26Albums and artists aren't stored: a row carries the keys the web app
27groups by (``album_id``, ``artist_id``), so a client groups exactly the
28same way without reimplementing the rules.
29"""
31from __future__ import annotations
33import contextlib
34import hashlib
35import json
36import secrets
37import threading
38from collections.abc import Collection, Iterable
39from datetime import datetime, timedelta, timezone
40from pathlib import Path
41from typing import Any, Optional
43from . import library_metadata
44from .db.config import Dialect
45from .db.core import Database, as_database
46from .db.dialect import table_exists
47from .deemix_upgrade_store import replacement_for_id
48from .library_cache_keys import file_content_key_from_name_and_size
50#: How long a removed track's id is kept for clients to hear about it.
51TOMBSTONE_DAYS = 90
53#: The fields of a library row a client gets (besides ``id``).
54ROW_FIELDS = (
55 'file',
56 'title',
57 'artist',
58 'artists',
59 'album',
60 'album_artist',
61 'album_id',
62 'artist_id',
63 'track_number',
64 'year',
65 'release_date',
66 'genre',
67 'compilation',
68 'duration',
69 'codec',
70 'bitrate',
71 'sample_rate',
72 'channels',
73 'size',
74 'added',
75 'has_cover',
76 'playlists',
77)
80def _now() -> datetime:
81 return datetime.now(timezone.utc)
84def split_artists(artist: str, album_artist: str = '') -> list[str]:
85 """The artists in a tag's artist string - the web app's own rule
86 (``frontend/src/lib/library.js``): split on the first of ``;``,
87 `` / `` or ``, `` it contains, unless it is the album artist too (see
88 :func:`downtify.library_metadata.split_artists`)."""
90 return library_metadata.split_artists(artist, album_artist)
93def row_artists(row: dict[str, Any]) -> list[str]:
94 """Every credited artist of a ``/tracks`` row: its ``artists`` (read
95 from the ARTISTS tag when the file has one), else its artist text
96 split by :func:`split_artists`."""
98 artists = row.get('artists')
99 if isinstance(artists, list):
100 names = [str(name).strip() for name in artists if str(name).strip()]
101 if names:
102 return names
103 return split_artists(
104 row.get('artist', ''), str(row.get('album_artist') or '')
105 )
108def _short_hash(text: str) -> str:
109 return hashlib.sha256(text.encode()).hexdigest()[:16]
112def grouping_keys(row: dict[str, Any]) -> dict[str, str]:
113 """``album_artist`` as the web app shows it (the tag, else the first
114 artist), and the ids it groups albums and artists by: ``album_id``
115 (album artist + album title, case-insensitive; ``''`` when the track
116 has no album) and ``artist_id`` (the album artist)."""
118 artists = row_artists(row)
119 album_artist = str(row.get('album_artist') or '').strip() or (
120 artists[0] if artists else ''
121 )
122 album = str(row.get('album') or '').strip()
123 return {
124 'album_artist': album_artist,
125 'album_id': (
126 _short_hash(json.dumps([album_artist.lower(), album.lower()]))
127 if album
128 else ''
129 ),
130 'artist_id': _short_hash(album_artist.lower()) if album_artist else '',
131 }
134def public_row(entry: dict[str, Any]) -> dict[str, Any]:
135 """A ``/tracks`` entry as a sync row (without its id)."""
137 row = {
138 'file': str(entry.get('file') or ''),
139 'title': str(entry.get('title') or ''),
140 'artist': str(entry.get('artist') or ''),
141 'artists': row_artists(entry),
142 'album': str(entry.get('album') or ''),
143 'track_number': int(entry.get('track_number') or 0),
144 'year': str(entry.get('year') or ''),
145 'release_date': str(entry.get('release_date') or ''),
146 'genre': str(entry.get('genre') or ''),
147 'compilation': bool(entry.get('compilation')),
148 'duration': float(entry.get('duration') or 0.0),
149 'codec': str(entry.get('codec') or ''),
150 'bitrate': int(entry.get('bitrate') or 0),
151 'sample_rate': int(entry.get('sample_rate') or 0),
152 'channels': int(entry.get('channels') or 0),
153 'size': int(entry.get('size') or 0),
154 'added': int(entry.get('added') or 0),
155 'has_cover': bool(entry.get('has_cover')),
156 'playlists': sorted(entry.get('playlists') or []),
157 }
158 row.update(grouping_keys(entry))
159 public = {key: row[key] for key in ROW_FIELDS}
160 # Present (true) only on explicit tracks: absent means "not explicit",
161 # and the rows of every other track - hash included - stay as they were.
162 if entry.get('explicit'):
163 public['explicit'] = True
164 public['revision'] = hashlib.sha256(
165 json.dumps(public, sort_keys=True).encode()
166 ).hexdigest()
167 return public
170def _row_hash(row: dict[str, Any]) -> str:
171 return hashlib.sha256(json.dumps(row, sort_keys=True).encode()).hexdigest()
174def _content_key(row: dict[str, Any]) -> str:
175 return (
176 file_content_key_from_name_and_size(row['file'], int(row['size']))
177 or ''
178 )
181def _tag_key(row: dict[str, Any]) -> str:
182 """Title, artist, album and length: what identifies a song whose file
183 was renamed (and whose name and size no longer match)."""
185 if not row['title']:
186 return ''
187 return '\n'.join([
188 row['title'].casefold(),
189 row['artist'].casefold(),
190 row['album'].casefold(),
191 str(round(float(row['duration']))),
192 ])
195def new_track_id() -> str:
196 return 't' + secrets.token_hex(8)
199#: Optional tables (audio replacement) read by the id lookups below.
200_REPLACEMENT_TABLES = (
201 'library_audio_sync_aliases',
202 'library_audio_replacements',
203)
206def _tables_exist(conn, names: Iterable[str]) -> bool:
207 return all(table_exists(conn, name) for name in names)
210def _replacement_for_id(conn, track_id: str) -> Optional[dict[str, Any]]:
211 """:func:`replacement_for_id`, skipped when its tables are absent.
213 Checked up front, not by catching an error: a failed statement
214 aborts the whole PostgreSQL transaction.
215 """
217 if not _tables_exist(conn, _REPLACEMENT_TABLES):
218 return None
219 return replacement_for_id(conn, track_id)
222def _path_order(conn) -> str:
223 """Byte order for paths: PostgreSQL would otherwise sort by its
224 locale collation, SQLite by bytes."""
226 return ' COLLATE "C"' if conn.dialect is Dialect.POSTGRES else ''
229class LibrarySync:
230 """Track ids and the change feed, in ``downtify_library.db``."""
232 def __init__(self, db_path: str | Path | Database) -> None:
233 self.db = as_database(db_path, 'library')
234 if isinstance(db_path, Database):
235 self._path = str(self.db.path) if self.db.path else None
236 else:
237 self._path = str(db_path)
238 self._lock = threading.Lock()
239 self._init_db()
241 def _connect(self):
242 return self.db.connect()
244 def _init_db(self) -> None:
245 # PostgreSQL gets its schema from the migrations.
246 if self.db.dialect is not Dialect.SQLITE:
247 return
248 with self._connect() as conn:
249 conn.execute("""
250 CREATE TABLE IF NOT EXISTS sync_tracks (
251 track_id TEXT PRIMARY KEY,
252 path TEXT,
253 content_key TEXT NOT NULL DEFAULT '',
254 tag_key TEXT NOT NULL DEFAULT '',
255 row_json TEXT NOT NULL DEFAULT '{}',
256 row_hash TEXT NOT NULL DEFAULT '',
257 seq INTEGER NOT NULL,
258 deleted_at TEXT
259 )
260 """)
261 conn.execute(
262 'CREATE UNIQUE INDEX IF NOT EXISTS idx_sync_tracks_path '
263 'ON sync_tracks (path) WHERE path IS NOT NULL'
264 )
265 conn.execute(
266 'CREATE INDEX IF NOT EXISTS idx_sync_tracks_seq '
267 'ON sync_tracks (seq)'
268 )
269 conn.execute("""
270 CREATE TABLE IF NOT EXISTS sync_meta (
271 key TEXT PRIMARY KEY,
272 value INTEGER NOT NULL
273 )
274 """)
276 @staticmethod
277 def _meta(conn, key: str) -> int:
278 row = conn.execute(
279 'SELECT value FROM sync_meta WHERE key = ?', (key,)
280 ).fetchone()
281 return int(row['value']) if row else 0
283 @staticmethod
284 def _set_meta(conn, key: str, value: int) -> None:
285 conn.execute(
286 """INSERT INTO sync_meta (key, value) VALUES (?, ?)
287 ON CONFLICT(key) DO UPDATE SET value = excluded.value""",
288 (key, value),
289 )
291 def _write_guard(self):
292 """The in-process lock: SQLite only. PostgreSQL serialises writers
293 on the ``sync_meta`` row instead (see :meth:`_allocate`), which
294 also covers other processes."""
296 if self.db.dialect is Dialect.SQLITE:
297 return self._lock
298 return contextlib.nullcontext()
300 @staticmethod
301 def _allocate(conn, count: int) -> int:
302 """Reserve *count* seq numbers and return the last one.
304 Every writing transaction calls this first. On PostgreSQL the
305 upsert takes the row lock on ``sync_meta.seq`` until the
306 transaction commits, so writers queue and commit in seq order
307 (a database SEQUENCE would not guarantee that). ``count=0`` only
308 takes the lock. On SQLite ``BEGIN IMMEDIATE`` already serialises
309 writers, so a plain read-modify-write is used there.
310 """
312 if conn.dialect is Dialect.POSTGRES:
313 row = conn.execute(
314 """INSERT INTO sync_meta (key, value) VALUES (?, ?)
315 ON CONFLICT (key) DO UPDATE
316 SET value = sync_meta.value + excluded.value
317 RETURNING value""",
318 ('seq', count),
319 ).fetchone()
320 return int(row['value'])
321 last = LibrarySync._meta(conn, 'seq') + count
322 LibrarySync._set_meta(conn, 'seq', last)
323 return last
325 # Reconciling with what's on disk
327 def refresh(
328 self, entries: Iterable[dict[str, Any]], now: Optional[datetime] = None
329 ) -> int:
330 """Bring the ids and the feed in line with *entries* (the rows of
331 ``GET /tracks``, i.e. the whole library as it is now). Returns the
332 current cursor."""
334 now = now or _now()
335 rows = {}
336 for entry in entries:
337 row = public_row(entry)
338 if row['file']:
339 rows[row['file']] = row
340 with self._write_guard(), self.db.transaction(lock=True) as conn:
341 # First statement: takes the seq row lock (PostgreSQL).
342 seq = self._allocate(conn, 0)
343 reserved = set()
344 restored = set()
345 if table_exists(conn, 'library_trash'):
346 reserved = {
347 r[0]
348 for r in conn.execute(
349 "SELECT track_id FROM library_trash WHERE state NOT IN ('preview','restored') AND track_id<>''"
350 )
351 }
352 restored = {
353 r[0]
354 for r in conn.execute(
355 "SELECT track_id FROM library_trash WHERE (state='restored' OR blocked=0) AND track_id<>''"
356 )
357 }
358 live = {
359 r['path']: r
360 for r in conn.execute(
361 'SELECT track_id, path, content_key, tag_key, row_hash, deleted_at '
362 'FROM sync_tracks WHERE deleted_at IS NULL'
363 ).fetchall()
364 }
365 if restored:
366 for r in conn.execute(
367 'SELECT track_id,path,content_key,tag_key,row_hash,deleted_at FROM sync_tracks WHERE deleted_at IS NOT NULL AND path IS NOT NULL'
368 ):
369 if r['track_id'] in restored and r['path'] in rows:
370 live[r['path']] = r
371 vanished = {
372 path: r for path, r in live.items() if path not in rows
373 }
374 by_content: dict[str, list[str]] = {}
375 by_tags: dict[str, list[str]] = {}
376 for path, r in vanished.items():
377 if r['content_key']:
378 by_content.setdefault(r['content_key'], []).append(path)
379 if r['tag_key']:
380 by_tags.setdefault(r['tag_key'], []).append(path)
382 def claim(index: dict[str, list[str]], key: str) -> str:
383 paths = [p for p in index.get(key, []) if p in vanished]
384 if key and len(paths) == 1:
385 return paths[0]
386 return ''
388 for path, row in rows.items():
389 content_key = _content_key(row)
390 tag_key = _tag_key(row)
391 digest = _row_hash(row)
392 known = live.get(path)
393 if known is not None:
394 if known['row_hash'] == digest and not known['deleted_at']:
395 continue
396 seq += 1
397 conn.execute(
398 'UPDATE sync_tracks SET content_key = ?, tag_key = ?, '
399 'row_json = ?, row_hash = ?, seq = ?, deleted_at = NULL '
400 'WHERE track_id = ?',
401 (
402 content_key,
403 tag_key,
404 json.dumps(row),
405 digest,
406 seq,
407 known['track_id'],
408 ),
409 )
410 continue
411 old_path = claim(by_content, content_key) or claim(
412 by_tags, tag_key
413 )
414 seq += 1
415 if old_path:
416 moved = vanished.pop(old_path)
417 conn.execute(
418 'UPDATE sync_tracks SET path = ?, content_key = ?, '
419 'tag_key = ?, row_json = ?, row_hash = ?, seq = ? '
420 'WHERE track_id = ?',
421 (
422 path,
423 content_key,
424 tag_key,
425 json.dumps(row),
426 digest,
427 seq,
428 moved['track_id'],
429 ),
430 )
431 continue
432 conn.execute(
433 'INSERT INTO sync_tracks (track_id, path, content_key, '
434 'tag_key, row_json, row_hash, seq) '
435 'VALUES (?, ?, ?, ?, ?, ?, ?)',
436 (
437 new_track_id(),
438 path,
439 content_key,
440 tag_key,
441 json.dumps(row),
442 digest,
443 seq,
444 ),
445 )
447 for gone in vanished.values():
448 seq += 1
449 if gone['track_id'] in reserved:
450 conn.execute(
451 'UPDATE sync_tracks SET deleted_at=?,seq=? WHERE track_id=?',
452 (now.isoformat(), seq, gone['track_id']),
453 )
454 continue
455 conn.execute(
456 'UPDATE sync_tracks SET path = NULL, deleted_at = ?, '
457 "seq = ?, row_json = '{}' WHERE track_id = ?",
458 (now.isoformat(), seq, gone['track_id']),
459 )
461 self._purge(conn, now)
462 self._set_meta(conn, 'seq', seq)
463 return seq
465 def _purge(self, conn, now: datetime) -> None:
466 """Forget removed tracks older than :data:`TOMBSTONE_DAYS`; a
467 client whose cursor predates them then needs a full sync."""
469 cutoff = (now - timedelta(days=TOMBSTONE_DAYS)).isoformat()
470 protected = ''
471 if table_exists(conn, 'library_trash'):
472 protected = " AND track_id NOT IN (SELECT track_id FROM library_trash WHERE state NOT IN ('preview','restored','purged'))"
473 rows = conn.execute(
474 'SELECT MAX(seq) AS seq FROM sync_tracks '
475 'WHERE deleted_at IS NOT NULL AND deleted_at < ?' + protected,
476 (cutoff,),
477 ).fetchone()
478 if rows and rows['seq'] is not None:
479 conn.execute(
480 'DELETE FROM sync_tracks '
481 'WHERE deleted_at IS NOT NULL AND deleted_at < ?' + protected,
482 (cutoff,),
483 )
484 floor = max(self._meta(conn, 'floor'), int(rows['seq']))
485 self._set_meta(conn, 'floor', floor)
487 def touch(self, path: str) -> bool:
488 """Poke a live row so the next feed carries it, without changing it.
490 Ownership (who may see the track) lives outside this table, so
491 gaining or losing it doesn't bump ``seq`` by itself - and the
492 owner's next incremental sync would then miss the change. Returns
493 whether a row was poked.
494 """
496 if not path:
497 return False
498 with self._write_guard(), self.db.transaction(lock=True) as conn:
499 seq = self._allocate(conn, 1)
500 cur = conn.execute(
501 'UPDATE sync_tracks SET seq = ? WHERE path = ? '
502 'AND deleted_at IS NULL',
503 (seq, path),
504 )
505 if cur.rowcount == 0:
506 # Nothing to stamp: hand the number back.
507 self._set_meta(conn, 'seq', seq - 1)
508 return False
509 return True
511 def touch_all(self) -> int:
512 """Poke every live row so the next feed re-emits the library.
514 The ownership backfill doesn't change files, so without this a
515 phone that synced before it would keep tracks it may no longer
516 see. Returns how many rows were poked.
517 """
519 with self._write_guard(), self.db.transaction(lock=True) as conn:
520 seq = self._allocate(conn, 1)
521 cur = conn.execute(
522 'UPDATE sync_tracks SET seq = ? WHERE deleted_at IS NULL',
523 (seq,),
524 )
525 if cur.rowcount == 0:
526 self._set_meta(conn, 'seq', seq - 1)
527 return 0
528 return cur.rowcount
530 # Reading the feed
532 def cursor(self) -> int:
533 with self._connect() as conn:
534 return self._meta(conn, 'seq')
536 def changes(
537 self,
538 since: int = 0,
539 *,
540 allowed_keys: Optional[Collection[str]] = None,
541 ) -> dict[str, Any]:
542 """``{cursor, full, tracks, deleted}`` after *since*.
544 ``full`` is ``True`` for *since* ``0`` (or older than what's
545 remembered): ``tracks`` is then the whole library and the client
546 should drop anything it has that isn't in it. ``allowed_keys``
547 keeps only a non-admin's own rows (by content key); live rows
548 outside it join ``deleted`` on incremental syncs, so a client
549 that loses a track (ownership released) drops it too.
550 """
552 allowed = None if allowed_keys is None else set(allowed_keys)
553 with self._connect() as conn:
554 cursor = self._meta(conn, 'seq')
555 floor = self._meta(conn, 'floor')
556 full = since <= 0 or since < floor or since > cursor
557 if full:
558 live = conn.execute(
559 'SELECT track_id, content_key, row_json FROM sync_tracks '
560 'WHERE deleted_at IS NULL ORDER BY path'
561 + _path_order(conn)
562 ).fetchall()
563 deleted: list[str] = []
564 else:
565 live = conn.execute(
566 'SELECT track_id, content_key, row_json FROM sync_tracks '
567 'WHERE deleted_at IS NULL AND seq > ? ORDER BY seq',
568 (since,),
569 ).fetchall()
570 deleted = [
571 r['track_id']
572 for r in conn.execute(
573 'SELECT track_id FROM sync_tracks '
574 'WHERE deleted_at IS NOT NULL AND seq > ? '
575 'ORDER BY seq',
576 (since,),
577 ).fetchall()
578 ]
579 tracks = []
580 for row in live:
581 key = str(row['content_key'] or '')
582 if allowed is None or key in allowed:
583 tracks.append({
584 'id': row['track_id'],
585 **json.loads(row['row_json']),
586 })
587 elif not full:
588 deleted.append(row['track_id'])
589 return {
590 'cursor': cursor,
591 'full': full,
592 'tracks': tracks,
593 'deleted': deleted,
594 }
596 # Lookups
598 def path_for(self, track_id: str) -> Optional[str]:
599 with self._connect() as conn:
600 replacement = _replacement_for_id(conn, track_id)
601 if replacement is not None:
602 return replacement['file']
603 row = conn.execute(
604 'SELECT path FROM sync_tracks WHERE track_id = ? '
605 'AND deleted_at IS NULL',
606 (track_id,),
607 ).fetchone()
608 return row['path'] if row else None
610 def row_for(self, track_id: str) -> Optional[dict[str, Any]]:
611 with self._connect() as conn:
612 replacement = _replacement_for_id(conn, track_id)
613 if replacement is not None:
614 current = conn.execute(
615 'SELECT row_json FROM sync_tracks WHERE path=? AND deleted_at IS NULL',
616 (replacement['file'],),
617 ).fetchone()
618 if current is not None:
619 return {'id': track_id, **json.loads(current['row_json'])}
620 return {'id': track_id, **public_row(replacement)}
621 row = conn.execute(
622 'SELECT track_id, row_json FROM sync_tracks '
623 'WHERE track_id = ? AND deleted_at IS NULL',
624 (track_id,),
625 ).fetchone()
626 if row is None:
627 return None
628 return {'id': row['track_id'], **json.loads(row['row_json'])}
630 def rows_for(self, track_ids: Iterable[str]) -> dict[str, dict[str, Any]]:
631 """Resolve many live ids in one database connection.
633 Includes the compatibility aliases handled by :meth:`row_for`.
634 Missing and deleted ids are omitted from the returned mapping.
635 """
637 wanted = list(dict.fromkeys(track_ids))
638 found: dict[str, dict[str, Any]] = {}
639 with self._connect() as conn:
640 for start in range(0, len(wanted), 400):
641 chunk = wanted[start : start + 400]
642 marks = ','.join('?' * len(chunk))
643 replacements: dict[str, dict[str, Any]] = {}
644 if _tables_exist(conn, _REPLACEMENT_TABLES):
645 for row in conn.execute(
646 f"""SELECT a.track_id, r.new_metadata
647 FROM library_audio_sync_aliases a
648 JOIN library_audio_replacements r
649 ON r.old_file = a.old_file
650 WHERE a.track_id IN ({marks})
651 AND r.state IN ('linked','retained','removed','complete')""",
652 chunk,
653 ).fetchall():
654 replacements[row['track_id']] = json.loads(
655 row['new_metadata']
656 )
658 # row_for gives compatibility aliases precedence over a
659 # same-named live id, so resolve those first.
660 replacement_paths = list({
661 row['file'] for row in replacements.values()
662 })
663 current_rows = {}
664 if replacement_paths:
665 marks = ','.join('?' * len(replacement_paths))
666 current_rows = {
667 row['path']: json.loads(row['row_json'])
668 for row in conn.execute(
669 f'SELECT path, row_json FROM sync_tracks '
670 f'WHERE deleted_at IS NULL AND path IN ({marks})',
671 replacement_paths,
672 ).fetchall()
673 }
674 for track_id, replacement in replacements.items():
675 current = current_rows.get(replacement['file'])
676 found[track_id] = (
677 {'id': track_id, **current}
678 if current is not None
679 else {'id': track_id, **public_row(replacement)}
680 )
681 remaining = [key for key in chunk if key not in replacements]
682 if not remaining:
683 continue
684 marks = ','.join('?' * len(remaining))
685 for row in conn.execute(
686 f'SELECT track_id, row_json FROM sync_tracks '
687 f'WHERE deleted_at IS NULL AND track_id IN ({marks})',
688 remaining,
689 ).fetchall():
690 found[row['track_id']] = {
691 'id': row['track_id'],
692 **json.loads(row['row_json']),
693 }
694 return found
696 def ids_for_paths(self, paths: Iterable[str]) -> dict[str, str]:
697 """``{path: track_id}`` for the paths that are known."""
699 wanted = [p for p in paths if p]
700 found: dict[str, str] = {}
701 with self._connect() as conn:
702 for start in range(0, len(wanted), 500):
703 chunk = wanted[start : start + 500]
704 marks = ','.join('?' * len(chunk))
705 for row in conn.execute(
706 f'SELECT track_id, path FROM sync_tracks '
707 f'WHERE deleted_at IS NULL AND path IN ({marks})',
708 chunk,
709 ).fetchall():
710 found[row['path']] = row['track_id']
711 return found