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
« 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."""
3from __future__ import annotations
5import re
6import sqlite3
7from contextlib import AbstractContextManager
8from datetime import datetime, timezone
9from pathlib import Path
10from typing import Any, Optional
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)
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)
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``."""
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
48def _now_iso() -> str:
49 return datetime.now(timezone.utc).isoformat()
52class TrackIndex:
53 """Index: one canonical relative path per Spotify track id.
55 Stored in the ``library`` database group (SQLite or PostgreSQL).
56 """
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()
66 def _connect(self) -> AbstractContextManager[Connection]:
67 return self.db.connect()
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 """)
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'])
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)
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)."""
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}
123 def spotify_id_for_filename(self, filename: str) -> Optional[str]:
124 """The Spotify track a library file was downloaded for, if known.
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 """
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
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 )
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)
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
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 ]
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
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
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 ]
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."""
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 }
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 )
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).
281 ``monitor_db_path`` is either the monitor's ``Database`` (any
282 backend) or a legacy SQLite file path.
283 """
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
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.
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 """
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 []
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."""
345 download_dir = Path(downloader.download_dir)
346 slskd_dir = slskd_dir_from_downloader(downloader)
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)
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'
366 return None