Coverage for downtify/track_index.py: 97%

178 statements  

« prev     ^ index     » next       coverage.py v7.17.4, created at 2026-11-21 22:18 +0000

1"""Global Spotify track id → library file mapping for cross-playlist deduplication.""" 

2 

3from __future__ import annotations 

4 

5import re 

6import sqlite3 

7from contextlib import AbstractContextManager 

8from datetime import datetime, timezone 

9from pathlib import Path 

10from typing import Any, Optional 

11 

12from .db.batching import in_short_transactions 

13from .db.config import Dialect 

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

15from .library_cache_keys import file_content_key 

16from .library_paths import ( 

17 locate_library_file, 

18 slskd_dir_from_downloader, 

19) 

20 

21_SPOTIFY_TRACK_ID = re.compile(r'^[a-zA-Z0-9]{22}$') 

22_SPOTIFY_TRACK_URL = re.compile( 

23 r'(?:open\.spotify\.com|spotify\.com)/track/([a-zA-Z0-9]{22})' 

24) 

25 

26 

27def normalize_spotify_track_id(song: dict[str, Any]) -> Optional[str]: 

28 """Return a 22-char Spotify track id when ``song`` has one, else ``None``.""" 

29 

30 raw = str(song.get('song_id') or '').strip() 

31 if not raw: 

32 url = str(song.get('url') or '').strip() 

33 if url: 

34 raw = url 

35 else: 

36 return None 

37 if raw.startswith('spotify:track:'): 

38 raw = raw.rsplit(':', 1)[-1] 

39 else: 

40 match = _SPOTIFY_TRACK_URL.search(raw) 

41 if match: 

42 raw = match.group(1) 

43 if _SPOTIFY_TRACK_ID.fullmatch(raw): 

44 return raw 

45 return None 

46 

47 

48def _now_iso() -> str: 

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

50 

51 

52class TrackIndex: 

53 """Index: one canonical relative path per Spotify track id. 

54 

55 Stored in the ``library`` database group (SQLite or PostgreSQL). 

56 """ 

57 

58 def __init__(self, path_or_db: str | Path | Database) -> None: 

59 self.db = as_database(path_or_db, 'library') 

60 if isinstance(path_or_db, Database): 

61 self._path = str(self.db.path) if self.db.path else None 

62 else: 

63 self._path = str(path_or_db) 

64 self._init_db() 

65 

66 def _connect(self) -> AbstractContextManager[Connection]: 

67 return self.db.connect() 

68 

69 def _init_db(self) -> None: 

70 # PostgreSQL gets its schema from the migrations. 

71 if self.db.dialect is not Dialect.SQLITE: 

72 return 

73 with self._connect() as conn: 

74 conn.execute(""" 

75 CREATE TABLE IF NOT EXISTS library_tracks ( 

76 spotify_track_id TEXT PRIMARY KEY, 

77 filename TEXT NOT NULL, 

78 content_key TEXT, 

79 registered_at TEXT NOT NULL 

80 ) 

81 """) 

82 try: 

83 conn.execute( 

84 'ALTER TABLE library_tracks ADD COLUMN content_key TEXT' 

85 ) 

86 except Exception: 

87 pass 

88 conn.execute(""" 

89 CREATE INDEX IF NOT EXISTS idx_library_tracks_content_key 

90 ON library_tracks (content_key) 

91 WHERE content_key IS NOT NULL 

92 """) 

93 

94 def lookup(self, spotify_track_id: str) -> Optional[str]: 

95 tid = str(spotify_track_id or '').strip() 

96 if not _SPOTIFY_TRACK_ID.fullmatch(tid): 

97 return None 

98 with self._connect() as conn: 

99 row = conn.execute( 

100 'SELECT filename FROM library_tracks WHERE spotify_track_id = ?', 

101 (tid,), 

102 ).fetchone() 

103 if row is None: 

104 return None 

105 return str(row['filename']) 

106 

107 def lookup_song(self, song: dict[str, Any]) -> Optional[str]: 

108 tid = normalize_spotify_track_id(song) 

109 if not tid: 

110 return None 

111 return self.lookup(tid) 

112 

113 def spotify_ids_by_filename(self) -> dict[str, str]: 

114 """``{filename: Spotify track id}`` for every indexed file (a 

115 backup records which track each library file was).""" 

116 

117 with self._connect() as conn: 

118 rows = conn.execute( 

119 'SELECT spotify_track_id, filename FROM library_tracks' 

120 ).fetchall() 

121 return {str(r['filename']): str(r['spotify_track_id']) for r in rows} 

122 

123 def spotify_id_for_filename(self, filename: str) -> Optional[str]: 

124 """The Spotify track a library file was downloaded for, if known. 

125 

126 The reverse of :meth:`lookup`: a library upgrade starts from a 

127 file on disk and needs the track it came from to re-fetch its 

128 metadata and cover. 

129 """ 

130 

131 name = str(filename or '').strip().replace('\\', '/') 

132 if not name: 

133 return None 

134 with self._connect() as conn: 

135 row = conn.execute( 

136 """SELECT spotify_track_id FROM library_tracks 

137 WHERE filename = ? LIMIT 1""", 

138 (name,), 

139 ).fetchone() 

140 return str(row['spotify_track_id']) if row is not None else None 

141 

142 def register( 

143 self, 

144 spotify_track_id: str, 

145 filename: str, 

146 *, 

147 full_path: Optional[Path] = None, 

148 ) -> None: 

149 tid = str(spotify_track_id or '').strip() 

150 name = str(filename or '').strip().replace('\\', '/') 

151 if not _SPOTIFY_TRACK_ID.fullmatch(tid) or not name: 

152 return 

153 ck: Optional[str] = None 

154 if full_path is not None: 

155 ck = file_content_key(full_path) 

156 with self._connect() as conn: 

157 conn.execute( 

158 """INSERT INTO library_tracks 

159 (spotify_track_id, filename, content_key, registered_at) 

160 VALUES (?, ?, ?, ?) 

161 ON CONFLICT(spotify_track_id) DO UPDATE SET 

162 filename=excluded.filename, 

163 content_key=COALESCE(excluded.content_key, library_tracks.content_key), 

164 registered_at=excluded.registered_at""", 

165 (tid, name, ck, _now_iso()), 

166 ) 

167 

168 def register_song( 

169 self, 

170 song: dict[str, Any], 

171 filename: str, 

172 *, 

173 full_path: Optional[Path] = None, 

174 ) -> None: 

175 tid = normalize_spotify_track_id(song) 

176 if tid: 

177 self.register(tid, filename, full_path=full_path) 

178 

179 def update_filename( 

180 self, 

181 spotify_track_id: str, 

182 filename: str, 

183 *, 

184 content_key: Optional[str] = None, 

185 ) -> bool: 

186 tid = str(spotify_track_id or '').strip() 

187 name = str(filename or '').strip().replace('\\', '/') 

188 if not _SPOTIFY_TRACK_ID.fullmatch(tid) or not name: 

189 return False 

190 with self._connect() as conn: 

191 cur = conn.execute( 

192 """UPDATE library_tracks SET filename = ?, 

193 content_key = COALESCE(?, content_key) 

194 WHERE spotify_track_id = ? AND filename != ?""", 

195 (name, content_key, tid, name), 

196 ) 

197 return cur.rowcount > 0 

198 

199 def all_rows(self) -> list[dict[str, Any]]: 

200 with self._connect() as conn: 

201 rows = conn.execute( 

202 """SELECT spotify_track_id, filename, content_key 

203 FROM library_tracks""" 

204 ).fetchall() 

205 return [ 

206 { 

207 'spotify_track_id': str(row['spotify_track_id']), 

208 'filename': str(row['filename']).replace('\\', '/'), 

209 'content_key': row['content_key'], 

210 } 

211 for row in rows 

212 ] 

213 

214 def set_content_key(self, spotify_track_id: str, content_key: str) -> bool: 

215 tid = str(spotify_track_id or '').strip() 

216 ck = str(content_key or '').strip() 

217 if not _SPOTIFY_TRACK_ID.fullmatch(tid) or not ck: 

218 return False 

219 with self._connect() as conn: 

220 cur = conn.execute( 

221 """UPDATE library_tracks SET content_key = ? 

222 WHERE spotify_track_id = ? AND COALESCE(content_key, '') = ''""", 

223 (ck, tid), 

224 ) 

225 return cur.rowcount > 0 

226 

227 def remove_by_filename(self, filename: str) -> bool: 

228 name = str(filename or '').strip().replace('\\', '/') 

229 if not name: 

230 return False 

231 with self._connect() as conn: 

232 cur = conn.execute( 

233 'DELETE FROM library_tracks WHERE filename = ?', (name,) 

234 ) 

235 return cur.rowcount > 0 

236 

237 def list_filenames(self) -> list[str]: 

238 with self._connect() as conn: 

239 rows = conn.execute( 

240 'SELECT filename FROM library_tracks' 

241 ).fetchall() 

242 # Sort in Python: PostgreSQL's collation order differs from SQLite's. 

243 rows = sorted(rows, key=lambda row: str(row['filename'] or '')) 

244 return [ 

245 str(row['filename']).replace('\\', '/') 

246 for row in rows 

247 if row['filename'] 

248 ] 

249 

250 def filenames_by_spotify_id(self) -> dict[str, str]: 

251 """Every ``{spotify_track_id: filename}``, in one query - for 

252 checking many tracks at once instead of one :meth:`lookup` each.""" 

253 

254 with self._connect() as conn: 

255 rows = conn.execute( 

256 'SELECT spotify_track_id, filename FROM library_tracks' 

257 ).fetchall() 

258 return { 

259 str(row['spotify_track_id']): str(row['filename']).replace( 

260 '\\', '/' 

261 ) 

262 for row in rows 

263 if row['spotify_track_id'] and row['filename'] 

264 } 

265 

266 def forget(self, spotify_track_id: str) -> None: 

267 tid = str(spotify_track_id or '').strip() 

268 if not _SPOTIFY_TRACK_ID.fullmatch(tid): 

269 return 

270 with self._connect() as conn: 

271 conn.execute( 

272 'DELETE FROM library_tracks WHERE spotify_track_id = ?', 

273 (tid,), 

274 ) 

275 

276 def backfill_from_monitor_db( 

277 self, monitor_db_path: Path | Database 

278 ) -> int: 

279 """Import rows from monitored-playlist history (best-effort, idempotent). 

280 

281 ``monitor_db_path`` is either the monitor's ``Database`` (any 

282 backend) or a legacy SQLite file path. 

283 """ 

284 

285 rows = read_monitor_rows( 

286 self.db, 

287 monitor_db_path, 

288 """SELECT track_spotify_id, filename 

289 FROM downloaded_tracks 

290 WHERE filename IS NOT NULL AND filename != ''""", 

291 ) 

292 imported = 0 

293 # A few commits instead of one per row (downtify/db/batching.py). 

294 for track_id, filename in in_short_transactions(self.db, rows): 

295 tid = str(track_id or '').strip() 

296 if not _SPOTIFY_TRACK_ID.fullmatch(tid): 

297 continue 

298 before = self.lookup(tid) 

299 self.register(tid, str(filename)) 

300 if before != str(filename): 

301 imported += 1 

302 return imported 

303 

304 

305def read_monitor_rows( 

306 store: Database, 

307 source: Path | Database, 

308 sql: str, 

309) -> list[tuple[Any, ...]]: 

310 """Run a read-only monitor-history query and return plain tuples. 

311 

312 A ``Database`` source is queried through its own connections on any 

313 backend. A file path is the legacy SQLite monitor history: it is read 

314 only when ``store`` is SQLite too (PostgreSQL stores never read it), 

315 and a missing file or a failed query yields no rows. 

316 """ 

317 

318 if isinstance(source, Database): 

319 try: 

320 with source.connect() as conn: 

321 return [tuple(row) for row in conn.execute(sql).fetchall()] 

322 except sqlite3.Error: 

323 return [] 

324 if store.dialect is not Dialect.SQLITE: 

325 return [] 

326 path = Path(source) 

327 if not path.is_file(): 

328 return [] 

329 try: 

330 with sqlite3.connect(str(path)) as src: 

331 return [tuple(row) for row in src.execute(sql).fetchall()] 

332 except sqlite3.Error: 

333 return [] 

334 

335 

336def resolve_existing_download( 

337 downloader: Any, 

338 song: dict[str, Any], 

339 *, 

340 subdir: Optional[str] = None, 

341 track_index: Optional[TrackIndex] = None, 

342) -> Optional[tuple[str, str]]: 

343 """Return ``(relative_filename, skip_message)`` when a file already exists.""" 

344 

345 download_dir = Path(downloader.download_dir) 

346 slskd_dir = slskd_dir_from_downloader(downloader) 

347 

348 if track_index is not None: 

349 tid = normalize_spotify_track_id(song) 

350 if tid: 

351 canonical = track_index.lookup(tid) 

352 if canonical: 

353 if locate_library_file(canonical, download_dir, slskd_dir): 

354 return canonical, 'Already in library' 

355 track_index.forget(tid) 

356 

357 local = downloader.existing_filename_for(song, subdir=subdir) 

358 if local and locate_library_file(local, download_dir, slskd_dir): 

359 if track_index is not None: 

360 full = locate_library_file(local, download_dir, slskd_dir) 

361 track_index.register_song( 

362 song, local, full_path=full if full is not None else None 

363 ) 

364 return local, 'Already on disk' 

365 

366 return None