Coverage for downtify/deemix_upgrade_store.py: 98%
126 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"""Durable receipts and atomic reference relinking for audio replacements."""
3from __future__ import annotations
5import contextlib
6import json
7from datetime import datetime, timezone
8from pathlib import Path
9from typing import Any, Optional
11from .db.config import Dialect
12from .db.core import Connection, Database, as_database
13from .db.dialect import table_exists
15_LINKED = ('linked', 'retained', 'removed', 'complete')
17# Authoritative tables that carry a library file path.
18_PATH_REFERENCES = (
19 ('library_tracks', 'filename'),
20 ('playlist_tracks', 'filename'),
21 ('liked_tracks', 'stored_path'),
22 ('track_owners', 'stored_path'),
23 ('hidden_library_items', 'item_path'),
24)
26_RELINK_TEMP_TABLES = ('audio_relink', 'audio_owners', 'audio_hidden')
29def _now() -> str:
30 return datetime.now(timezone.utc).isoformat()
33def replacement_for_id(
34 conn: Connection, track_id: str
35) -> Optional[dict[str, Any]]:
36 """Compatibility for a previously issued mobile id, without a feed duplicate."""
37 if not (
38 table_exists(conn, 'library_audio_sync_aliases')
39 and table_exists(conn, 'library_audio_replacements')
40 ):
41 return None
42 row = conn.execute(
43 """SELECT r.new_metadata FROM library_audio_sync_aliases a
44 JOIN library_audio_replacements r ON r.old_file = a.old_file
45 WHERE a.track_id = ? AND r.state IN ('linked','retained','removed','complete')""",
46 (track_id,),
47 ).fetchone()
48 return json.loads(row[0]) if row else None
51class AudioReplacementStore:
52 """Operation state only; never an audio backup or an age-based cleanup list."""
54 def __init__(self, path_or_db: str | Path | Database) -> None:
55 self.db = as_database(path_or_db, 'library')
56 if isinstance(path_or_db, Database):
57 self.path = str(self.db.path) if self.db.path else None
58 else:
59 self.path = str(path_or_db)
60 self._init_db()
62 def _init_db(self) -> None:
63 # PostgreSQL gets its schema from the migrations.
64 if self.db.dialect is not Dialect.SQLITE:
65 return
66 with self.connect() as db:
67 db.execute("""CREATE TABLE IF NOT EXISTS library_audio_replacements (
68 old_file TEXT PRIMARY KEY,
69 run_id INTEGER NOT NULL,
70 new_file TEXT NOT NULL,
71 old_key TEXT NOT NULL,
72 new_key TEXT NOT NULL,
73 old_sha TEXT NOT NULL,
74 new_sha TEXT NOT NULL,
75 new_metadata TEXT NOT NULL,
76 catalog TEXT NOT NULL,
77 delete_original INTEGER NOT NULL DEFAULT 0,
78 state TEXT NOT NULL DEFAULT 'prepared',
79 detail TEXT NOT NULL DEFAULT '',
80 source_deleted INTEGER NOT NULL DEFAULT 0,
81 updated_at TEXT NOT NULL
82 )""")
83 db.execute(
84 'CREATE INDEX IF NOT EXISTS idx_audio_replacement_run ON library_audio_replacements(run_id)'
85 )
86 db.execute("""CREATE TABLE IF NOT EXISTS library_audio_sync_aliases (
87 track_id TEXT PRIMARY KEY,
88 old_file TEXT NOT NULL
89 )""")
91 def connect(self) -> contextlib.AbstractContextManager[Connection]:
92 return self.db.connect()
94 @staticmethod
95 def _row(row: Any) -> dict[str, Any]:
96 out = dict(zip(row.keys(), row))
97 out['new_metadata'] = json.loads(out['new_metadata'])
98 out['catalog'] = json.loads(out['catalog'])
99 return out
101 def _binary_order(self) -> str:
102 """ORDER BY clause suffix matching SQLite's byte-wise text order."""
103 if self.db.dialect is Dialect.POSTGRES:
104 return ' COLLATE "C"'
105 return ''
107 def get(self, old_file: str) -> Optional[dict[str, Any]]:
108 with self.connect() as db:
109 row = db.execute(
110 'SELECT * FROM library_audio_replacements WHERE old_file = ?',
111 (old_file,),
112 ).fetchone()
113 return self._row(row) if row else None
115 def save_prepared(self, receipt: dict[str, Any]) -> None:
116 """Insert once; a changed original must never silently replace its receipt."""
117 with self.connect() as db:
118 db.execute(
119 """INSERT INTO library_audio_replacements
120 (old_file, run_id, new_file, old_key, new_key, old_sha, new_sha,
121 new_metadata, catalog, delete_original, updated_at)
122 VALUES (?,?,?,?,?,?,?,?,?,?,?)""",
123 (
124 receipt['old_file'],
125 int(receipt['run_id']),
126 receipt['new_file'],
127 receipt['old_key'],
128 receipt['new_key'],
129 receipt['old_sha'],
130 receipt['new_sha'],
131 json.dumps(receipt['new_metadata']),
132 json.dumps(receipt['catalog']),
133 int(receipt['delete_original']),
134 _now(),
135 ),
136 )
138 def reuse(self, old_file: str, run_id: int, delete_original: bool) -> None:
139 with self.connect() as db:
140 db.execute(
141 """UPDATE library_audio_replacements
142 SET run_id = ?, delete_original = ?, updated_at = ? WHERE old_file = ?""",
143 (run_id, int(delete_original), _now(), old_file),
144 )
146 def for_run(self, run_id: int) -> list[dict[str, Any]]:
147 with self.connect() as db:
148 rows = db.execute(
149 'SELECT * FROM library_audio_replacements WHERE run_id = ? '
150 f'ORDER BY old_file{self._binary_order()}',
151 (run_id,),
152 ).fetchall()
153 return [self._row(row) for row in rows]
155 def mark(
156 self,
157 old_file: str,
158 state: str,
159 detail: str = '',
160 *,
161 deleted: bool = False,
162 ) -> None:
163 flag = int(deleted)
164 with self.connect() as db:
165 db.execute(
166 """UPDATE library_audio_replacements
167 SET state = ?, detail = ?,
168 source_deleted = CASE WHEN source_deleted >= ?
169 THEN source_deleted ELSE ? END,
170 updated_at = ? WHERE old_file = ?""",
171 (state, detail[:200], flag, flag, _now(), old_file),
172 )
174 def path_alias(self, stored: str) -> Optional[str]:
175 with self.connect() as db:
176 row = db.execute(
177 """SELECT new_file FROM library_audio_replacements
178 WHERE old_file = ? AND state IN ('linked','retained','removed','complete')""",
179 (stored,),
180 ).fetchone()
181 return str(row[0]) if row else None
183 def summary(self, run_id: int) -> dict[str, int]:
184 with self.connect() as db:
185 rows = db.execute(
186 """SELECT state, source_deleted, count(*) FROM library_audio_replacements
187 WHERE run_id = ? GROUP BY state, source_deleted""",
188 (run_id,),
189 ).fetchall()
190 totals = {
191 'prepared': 0,
192 'linked': 0,
193 'removed': 0,
194 'retained': 0,
195 'blocked': 0,
196 }
197 for state, deleted, count in rows:
198 if deleted:
199 totals['removed'] += count
200 elif state == 'complete':
201 totals['retained'] += count
202 elif state in totals:
203 totals[state] += count
204 return totals
206 def assert_relinked(self, receipts: list[dict[str, Any]]) -> None:
207 """Fail closed if authoritative tables still reference an old path."""
208 with self.connect() as db:
209 for table, column in _PATH_REFERENCES:
210 if not table_exists(db, table):
211 continue
212 paths = [r['old_file'] for r in receipts]
213 for start in range(0, len(paths), 400):
214 chunk = paths[start : start + 400]
215 marks = ','.join('?' * len(chunk))
216 if db.execute(
217 f'SELECT 1 FROM {table} WHERE {column} IN ({marks}) LIMIT 1',
218 chunk,
219 ).fetchone():
220 raise ValueError(
221 'Old library references remain; cleanup blocked'
222 )
224 def relink(self, receipts: list[dict[str, Any]]) -> None:
225 """Update authoritative references together, after M3U publication.
227 A temporary indexed mapping avoids one full table scan per track.
228 All source files still exist. A rollback leaves those references
229 intact and a durable prepared receipt for retry.
230 """
231 if not receipts:
232 return
233 postgres = self.db.dialect is Dialect.POSTGRES
234 temp_schema = 'pg_temp' if postgres else 'temp'
235 # Temp tables vanish at COMMIT on PostgreSQL; SQLite drops them
236 # with the connection. Dropping any leftovers first keeps a second
237 # relink on the same open connection from failing.
238 on_commit = ' ON COMMIT DROP' if postgres else ''
239 with self.db.transaction(lock=True) as db:
240 for name in _RELINK_TEMP_TABLES:
241 db.execute(f'DROP TABLE IF EXISTS {temp_schema}.{name}')
242 db.execute(f"""CREATE TEMP TABLE audio_relink (
243 old_file TEXT PRIMARY KEY,new_file TEXT NOT NULL,
244 old_key TEXT NOT NULL,new_key TEXT NOT NULL,
245 title TEXT,artist TEXT,duration DOUBLE PRECISION
246 ){on_commit}""")
247 db.executemany(
248 'INSERT INTO audio_relink VALUES (?,?,?,?,?,?,?)',
249 [
250 (
251 r['old_file'],
252 r['new_file'],
253 r['old_key'],
254 r['new_key'],
255 r['new_metadata'].get('title', ''),
256 r['new_metadata'].get('artist', ''),
257 r['new_metadata'].get('duration', 0),
258 )
259 for r in receipts
260 ],
261 )
262 db.execute(
263 'CREATE INDEX audio_relink_old_key ON audio_relink(old_key)'
264 )
265 tables = {
266 name
267 for name in (
268 'library_tracks',
269 'playlist_tracks',
270 'navidrome_tracks',
271 'liked_tracks',
272 'track_owners',
273 'hidden_library_items',
274 'sync_tracks',
275 )
276 if table_exists(db, name)
277 }
278 for table in ('library_tracks', 'playlist_tracks'):
279 if table in tables:
280 db.execute(f"""UPDATE {table} SET
281 filename = (SELECT new_file FROM audio_relink WHERE old_file = {table}.filename),
282 content_key = (SELECT new_key FROM audio_relink WHERE old_file = {table}.filename)
283 WHERE filename IN (SELECT old_file FROM audio_relink)""")
284 if 'navidrome_tracks' in tables:
285 # These are cached server IDs, not authoritative playlist
286 # memberships. The new recording needs a fresh lookup.
287 db.execute(
288 'DELETE FROM navidrome_tracks WHERE filename IN (SELECT old_file FROM audio_relink)'
289 )
290 if 'liked_tracks' in tables:
291 db.execute("""INSERT INTO liked_tracks
292 (stored_path,user_id,content_key,title,artist,duration,liked_at)
293 SELECT m.new_file,l.user_id,m.new_key,m.title,m.artist,m.duration,l.liked_at
294 FROM liked_tracks l JOIN audio_relink m ON l.stored_path=m.old_file
295 WHERE true
296 ON CONFLICT(user_id,stored_path) DO UPDATE SET
297 content_key=excluded.content_key,title=excluded.title,
298 artist=excluded.artist,duration=excluded.duration,
299 liked_at=CASE WHEN liked_tracks.liked_at <= excluded.liked_at
300 THEN liked_tracks.liked_at ELSE excluded.liked_at END""")
301 db.execute(
302 'DELETE FROM liked_tracks WHERE stored_path IN (SELECT old_file FROM audio_relink)'
303 )
304 if 'track_owners' in tables:
305 db.execute(f"""CREATE TEMP TABLE audio_owners{on_commit} AS
306 SELECT m.new_key,o.user_id,o.added_at,m.new_file
307 FROM track_owners o JOIN audio_relink m
308 ON o.stored_path=m.old_file OR (o.stored_path='' AND o.content_key=m.old_key)""")
309 db.execute(
310 'DELETE FROM track_owners WHERE stored_path IN (SELECT old_file FROM audio_relink)'
311 )
312 db.execute("""DELETE FROM track_owners WHERE stored_path='' AND content_key IN
313 (SELECT old_key FROM audio_relink)""")
314 db.execute("""INSERT INTO track_owners(content_key,user_id,added_at,stored_path)
315 SELECT new_key,user_id,added_at,new_file FROM audio_owners
316 WHERE true
317 ON CONFLICT DO NOTHING""")
318 db.execute("""UPDATE track_owners SET stored_path=(SELECT new_file FROM audio_owners a
319 WHERE a.new_key=track_owners.content_key AND a.user_id=track_owners.user_id LIMIT 1)
320 WHERE EXISTS(SELECT 1 FROM audio_owners a WHERE a.new_key=track_owners.content_key AND a.user_id=track_owners.user_id)""")
321 if 'hidden_library_items' in tables:
322 db.execute(f"""CREATE TEMP TABLE audio_hidden{on_commit} AS
323 SELECT h.kind,m.new_key,m.new_file,h.label,h.detail,h.hidden_at
324 FROM hidden_library_items h JOIN audio_relink m
325 ON h.item_path=m.old_file OR (h.item_path='' AND h.item_key=m.old_key)
326 WHERE h.kind='track'""")
327 db.execute("""DELETE FROM hidden_library_items WHERE kind='track'
328 AND item_path IN (SELECT old_file FROM audio_relink)""")
329 db.execute("""DELETE FROM hidden_library_items WHERE kind='track' AND item_path=''
330 AND item_key IN (SELECT old_key FROM audio_relink)""")
331 db.execute("""INSERT INTO hidden_library_items(kind,item_key,item_path,label,detail,hidden_at)
332 SELECT kind,new_key,new_file,label,detail,hidden_at FROM audio_hidden
333 WHERE true
334 ON CONFLICT DO NOTHING""")
335 db.execute("""UPDATE hidden_library_items SET item_path=(SELECT new_file FROM audio_hidden a WHERE a.new_key=hidden_library_items.item_key LIMIT 1)
336 WHERE kind='track' AND item_key IN (SELECT new_key FROM audio_hidden)""")
337 if 'sync_tracks' in tables:
338 db.execute("""INSERT INTO library_audio_sync_aliases(track_id,old_file)
339 SELECT s.track_id,m.old_file FROM sync_tracks s JOIN audio_relink m
340 ON s.path=m.old_file OR (s.path IS NULL AND s.content_key=m.old_key
341 AND (SELECT count(*) FROM audio_relink a WHERE a.old_key=m.old_key)=1)
342 WHERE true
343 ON CONFLICT(track_id) DO UPDATE SET old_file=excluded.old_file""")
344 db.execute(
345 """UPDATE library_audio_replacements SET state='linked',detail='',updated_at=?
346 WHERE old_file IN (SELECT old_file FROM audio_relink)""",
347 (_now(),),
348 )