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

1"""Durable receipts and atomic reference relinking for audio replacements.""" 

2 

3from __future__ import annotations 

4 

5import contextlib 

6import json 

7from datetime import datetime, timezone 

8from pathlib import Path 

9from typing import Any, Optional 

10 

11from .db.config import Dialect 

12from .db.core import Connection, Database, as_database 

13from .db.dialect import table_exists 

14 

15_LINKED = ('linked', 'retained', 'removed', 'complete') 

16 

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) 

25 

26_RELINK_TEMP_TABLES = ('audio_relink', 'audio_owners', 'audio_hidden') 

27 

28 

29def _now() -> str: 

30 return datetime.now(timezone.utc).isoformat() 

31 

32 

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 

49 

50 

51class AudioReplacementStore: 

52 """Operation state only; never an audio backup or an age-based cleanup list.""" 

53 

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

61 

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

90 

91 def connect(self) -> contextlib.AbstractContextManager[Connection]: 

92 return self.db.connect() 

93 

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 

100 

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

106 

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 

114 

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 ) 

137 

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 ) 

145 

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] 

154 

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 ) 

173 

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 

182 

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 

205 

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 ) 

223 

224 def relink(self, receipts: list[dict[str, Any]]) -> None: 

225 """Update authoritative references together, after M3U publication. 

226 

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 )