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

1"""SQLite cache of Navidrome song IDs (resolve once, reuse on playlist sync).""" 

2 

3from __future__ import annotations 

4 

5import hashlib 

6from datetime import datetime, timezone 

7from pathlib import Path 

8from typing import Any, Optional 

9 

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 

14 

15 

16def _now_iso() -> str: 

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

18 

19 

20def _norm_filename(filename: str) -> str: 

21 return str(filename or '').strip().replace('\\', '/') 

22 

23 

24class NavidromeIndex: 

25 """Maps library files (basename+size) and Spotify ids to Navidrome song ids.""" 

26 

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

34 

35 def _connect(self): 

36 return self.db.connect() 

37 

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) 

69 

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

116 

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 

129 

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 

142 

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

146 

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 } 

186 

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 

201 

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 

223 

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 ) 

255 

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 ) 

266 

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 )