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

1"""The library as a mobile app syncs it: stable track ids and a change feed. 

2 

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: 

9 

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. 

18 

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. 

25 

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""" 

30 

31from __future__ import annotations 

32 

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 

42 

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 

49 

50#: How long a removed track's id is kept for clients to hear about it. 

51TOMBSTONE_DAYS = 90 

52 

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) 

78 

79 

80def _now() -> datetime: 

81 return datetime.now(timezone.utc) 

82 

83 

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`).""" 

89 

90 return library_metadata.split_artists(artist, album_artist) 

91 

92 

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`.""" 

97 

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 ) 

106 

107 

108def _short_hash(text: str) -> str: 

109 return hashlib.sha256(text.encode()).hexdigest()[:16] 

110 

111 

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).""" 

117 

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 } 

132 

133 

134def public_row(entry: dict[str, Any]) -> dict[str, Any]: 

135 """A ``/tracks`` entry as a sync row (without its id).""" 

136 

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 

168 

169 

170def _row_hash(row: dict[str, Any]) -> str: 

171 return hashlib.sha256(json.dumps(row, sort_keys=True).encode()).hexdigest() 

172 

173 

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 ) 

179 

180 

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).""" 

184 

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 ]) 

193 

194 

195def new_track_id() -> str: 

196 return 't' + secrets.token_hex(8) 

197 

198 

199#: Optional tables (audio replacement) read by the id lookups below. 

200_REPLACEMENT_TABLES = ( 

201 'library_audio_sync_aliases', 

202 'library_audio_replacements', 

203) 

204 

205 

206def _tables_exist(conn, names: Iterable[str]) -> bool: 

207 return all(table_exists(conn, name) for name in names) 

208 

209 

210def _replacement_for_id(conn, track_id: str) -> Optional[dict[str, Any]]: 

211 """:func:`replacement_for_id`, skipped when its tables are absent. 

212 

213 Checked up front, not by catching an error: a failed statement 

214 aborts the whole PostgreSQL transaction. 

215 """ 

216 

217 if not _tables_exist(conn, _REPLACEMENT_TABLES): 

218 return None 

219 return replacement_for_id(conn, track_id) 

220 

221 

222def _path_order(conn) -> str: 

223 """Byte order for paths: PostgreSQL would otherwise sort by its 

224 locale collation, SQLite by bytes.""" 

225 

226 return ' COLLATE "C"' if conn.dialect is Dialect.POSTGRES else '' 

227 

228 

229class LibrarySync: 

230 """Track ids and the change feed, in ``downtify_library.db``.""" 

231 

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() 

240 

241 def _connect(self): 

242 return self.db.connect() 

243 

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 """) 

275 

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 

282 

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 ) 

290 

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.""" 

295 

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

297 return self._lock 

298 return contextlib.nullcontext() 

299 

300 @staticmethod 

301 def _allocate(conn, count: int) -> int: 

302 """Reserve *count* seq numbers and return the last one. 

303 

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 """ 

311 

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 

324 

325 # Reconciling with what's on disk 

326 

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.""" 

333 

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) 

381 

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 '' 

387 

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 ) 

446 

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 ) 

460 

461 self._purge(conn, now) 

462 self._set_meta(conn, 'seq', seq) 

463 return seq 

464 

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.""" 

468 

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) 

486 

487 def touch(self, path: str) -> bool: 

488 """Poke a live row so the next feed carries it, without changing it. 

489 

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 """ 

495 

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 

510 

511 def touch_all(self) -> int: 

512 """Poke every live row so the next feed re-emits the library. 

513 

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 """ 

518 

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 

529 

530 # Reading the feed 

531 

532 def cursor(self) -> int: 

533 with self._connect() as conn: 

534 return self._meta(conn, 'seq') 

535 

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*. 

543 

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 """ 

551 

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 } 

595 

596 # Lookups 

597 

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 

609 

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'])} 

629 

630 def rows_for(self, track_ids: Iterable[str]) -> dict[str, dict[str, Any]]: 

631 """Resolve many live ids in one database connection. 

632 

633 Includes the compatibility aliases handled by :meth:`row_for`. 

634 Missing and deleted ids are omitted from the returned mapping. 

635 """ 

636 

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 ) 

657 

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 

695 

696 def ids_for_paths(self, paths: Iterable[str]) -> dict[str, str]: 

697 """``{path: track_id}`` for the paths that are known.""" 

698 

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