Coverage for downtify/navidrome_index.py: 88%
137 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"""SQLite cache of Navidrome song IDs (resolve once, reuse on playlist sync)."""
3from __future__ import annotations
5import hashlib
6from datetime import datetime, timezone
7from pathlib import Path
8from typing import Any, Optional
10from .db.config import Dialect
11from .db.core import Database, as_database
12from .library_cache_keys import file_content_key
13from .track_index import normalize_spotify_track_id
16def _now_iso() -> str:
17 return datetime.now(timezone.utc).isoformat()
20def _norm_filename(filename: str) -> str:
21 return str(filename or '').strip().replace('\\', '/')
24class NavidromeIndex:
25 """Maps library files (basename+size) and Spotify ids to Navidrome song ids."""
27 def __init__(self, path_or_db: str | Path | Database) -> None:
28 self.db = as_database(path_or_db, 'library')
29 if isinstance(path_or_db, Database):
30 self._path = str(self.db.path) if self.db.path else None
31 else:
32 self._path = str(path_or_db)
33 self._init_db()
35 def _connect(self):
36 return self.db.connect()
38 def _init_db(self) -> None:
39 # The Postgres schema is created by migrations; only SQLite needs DDL.
40 if self.db.dialect is not Dialect.SQLITE:
41 return
42 with self._connect() as conn:
43 row = conn.execute(
44 "SELECT sql FROM sqlite_master WHERE name='navidrome_tracks'"
45 ).fetchone()
46 if row is None:
47 conn.execute("""
48 CREATE TABLE navidrome_tracks (
49 content_key TEXT PRIMARY KEY,
50 filename TEXT NOT NULL,
51 navidrome_song_id TEXT NOT NULL,
52 spotify_track_id TEXT,
53 resolved_at TEXT NOT NULL
54 )
55 """)
56 conn.execute("""
57 CREATE INDEX IF NOT EXISTS idx_navidrome_filename
58 ON navidrome_tracks (filename)
59 """)
60 conn.execute("""
61 CREATE INDEX IF NOT EXISTS idx_navidrome_spotify
62 ON navidrome_tracks (spotify_track_id)
63 WHERE spotify_track_id IS NOT NULL
64 """)
65 return
66 if row[0] and 'content_key' in str(row[0]):
67 return
68 self._migrate_legacy_table(conn)
70 @staticmethod
71 def _migrate_legacy_table(conn: Any) -> None:
72 conn.execute("""
73 CREATE TABLE navidrome_tracks_new (
74 content_key TEXT PRIMARY KEY,
75 filename TEXT NOT NULL,
76 navidrome_song_id TEXT NOT NULL,
77 spotify_track_id TEXT,
78 resolved_at TEXT NOT NULL
79 )
80 """)
81 rows = conn.execute('SELECT * FROM navidrome_tracks').fetchall()
82 for row in rows:
83 filename = str(row['filename'])
84 ck = hashlib.sha256(_norm_filename(filename).encode()).hexdigest()
85 conn.execute(
86 """INSERT INTO navidrome_tracks_new
87 (content_key, filename, navidrome_song_id,
88 spotify_track_id, resolved_at)
89 VALUES (?, ?, ?, ?, ?)
90 ON CONFLICT (content_key) DO UPDATE SET
91 filename=excluded.filename,
92 navidrome_song_id=excluded.navidrome_song_id,
93 spotify_track_id=excluded.spotify_track_id,
94 resolved_at=excluded.resolved_at""",
95 (
96 ck,
97 filename,
98 str(row['navidrome_song_id']),
99 row['spotify_track_id'],
100 str(row['resolved_at']),
101 ),
102 )
103 conn.execute('DROP TABLE navidrome_tracks')
104 conn.execute(
105 'ALTER TABLE navidrome_tracks_new RENAME TO navidrome_tracks'
106 )
107 conn.execute("""
108 CREATE INDEX IF NOT EXISTS idx_navidrome_filename
109 ON navidrome_tracks (filename)
110 """)
111 conn.execute("""
112 CREATE INDEX IF NOT EXISTS idx_navidrome_spotify
113 ON navidrome_tracks (spotify_track_id)
114 WHERE spotify_track_id IS NOT NULL
115 """)
117 def lookup_filename(self, filename: str) -> Optional[str]:
118 name = _norm_filename(filename)
119 if not name:
120 return None
121 with self._connect() as conn:
122 row = conn.execute(
123 'SELECT navidrome_song_id FROM navidrome_tracks WHERE filename = ?',
124 (name,),
125 ).fetchone()
126 if row is None:
127 return None
128 return str(row['navidrome_song_id'] or '').strip() or None
130 def lookup_file(self, full_path: Path) -> Optional[str]:
131 ck = file_content_key(full_path)
132 if not ck:
133 return None
134 with self._connect() as conn:
135 row = conn.execute(
136 'SELECT navidrome_song_id FROM navidrome_tracks WHERE content_key = ?',
137 (ck,),
138 ).fetchone()
139 if row is None:
140 return None
141 return str(row['navidrome_song_id'] or '').strip() or None
143 def matched_files(self, keys_by_path: dict[str, str]) -> set[str]:
144 """The library paths of ``{path: content key}`` that have a
145 Navidrome song id - matched by content key or by path."""
147 wanted = {
148 _norm_filename(path): str(key or '')
149 for path, key in keys_by_path.items()
150 }
151 wanted.pop('', None)
152 if not wanted:
153 return set()
154 found_keys: set[str] = set()
155 found_paths: set[str] = set()
156 keys = sorted({key for key in wanted.values() if key})
157 paths = sorted(wanted)
158 with self._connect() as conn:
159 for start in range(0, len(keys), 400):
160 chunk = keys[start : start + 400]
161 marks = ','.join('?' * len(chunk))
162 found_keys.update(
163 str(row['content_key'])
164 for row in conn.execute(
165 'SELECT content_key FROM navidrome_tracks '
166 f'WHERE content_key IN ({marks})',
167 chunk,
168 )
169 )
170 for start in range(0, len(paths), 400):
171 chunk = paths[start : start + 400]
172 marks = ','.join('?' * len(chunk))
173 found_paths.update(
174 _norm_filename(row['filename'])
175 for row in conn.execute(
176 'SELECT filename FROM navidrome_tracks '
177 f'WHERE filename IN ({marks})',
178 chunk,
179 )
180 )
181 return {
182 path
183 for path, key in wanted.items()
184 if path in found_paths or (key and key in found_keys)
185 }
187 def lookup_spotify(self, spotify_track_id: str) -> Optional[str]:
188 tid = normalize_spotify_track_id({'song_id': spotify_track_id})
189 if not tid:
190 return None
191 with self._connect() as conn:
192 row = conn.execute(
193 """SELECT navidrome_song_id FROM navidrome_tracks
194 WHERE spotify_track_id = ?
195 ORDER BY resolved_at DESC LIMIT 1""",
196 (tid,),
197 ).fetchone()
198 if row is None:
199 return None
200 return str(row['navidrome_song_id'] or '').strip() or None
202 def lookup_song(
203 self,
204 song: dict[str, Any],
205 *,
206 full_path: Optional[Path] = None,
207 ) -> Optional[str]:
208 if full_path is not None:
209 hit = self.lookup_file(full_path)
210 if hit:
211 filename = str(song.get('filename') or '').strip()
212 if filename:
213 self._touch_filename(full_path, filename)
214 return hit
215 filename = str(song.get('filename') or '')
216 hit = self.lookup_filename(filename)
217 if hit:
218 return hit
219 tid = normalize_spotify_track_id(song)
220 if tid:
221 return self.lookup_spotify(tid)
222 return None
224 def store(
225 self,
226 filename: str,
227 navidrome_song_id: str,
228 *,
229 spotify_track_id: Optional[str] = None,
230 full_path: Optional[Path] = None,
231 ) -> None:
232 name = _norm_filename(filename)
233 sid = str(navidrome_song_id or '').strip()
234 if not name or not sid:
235 return
236 ck = file_content_key(full_path) if full_path is not None else None
237 if not ck:
238 return
239 tid = normalize_spotify_track_id(
240 {'song_id': spotify_track_id} if spotify_track_id else {}
241 )
242 with self._connect() as conn:
243 conn.execute(
244 """INSERT INTO navidrome_tracks
245 (content_key, filename, navidrome_song_id,
246 spotify_track_id, resolved_at)
247 VALUES (?, ?, ?, ?, ?)
248 ON CONFLICT(content_key) DO UPDATE SET
249 filename=excluded.filename,
250 navidrome_song_id=excluded.navidrome_song_id,
251 spotify_track_id=excluded.spotify_track_id,
252 resolved_at=excluded.resolved_at""",
253 (ck, name, sid, tid, _now_iso()),
254 )
256 def _touch_filename(self, full_path: Path, filename: str) -> None:
257 ck = file_content_key(full_path)
258 name = _norm_filename(filename)
259 if not ck or not name:
260 return
261 with self._connect() as conn:
262 conn.execute(
263 'UPDATE navidrome_tracks SET filename = ? WHERE content_key = ?',
264 (name, ck),
265 )
267 def forget_filename(
268 self,
269 filename: str,
270 *,
271 full_path: Optional[Path] = None,
272 ) -> None:
273 name = _norm_filename(filename)
274 if not name:
275 return
276 with self._connect() as conn:
277 conn.execute(
278 'DELETE FROM navidrome_tracks WHERE filename = ?', (name,)
279 )
280 if full_path is not None:
281 ck = file_content_key(full_path)
282 if ck:
283 conn.execute(
284 'DELETE FROM navidrome_tracks WHERE content_key = ?',
285 (ck,),
286 )