Coverage for downtify/playlist_catalog.py: 99%
273 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"""Playlist membership catalog (tracks per playlist, survives path moves)."""
3from __future__ import annotations
5from contextlib import AbstractContextManager
6from datetime import datetime, timezone
7from pathlib import Path
8from typing import Any, Optional
10from .db.batching import in_short_transactions
11from .db.config import Dialect
12from .db.core import Connection, Database, as_database
13from .library_cache_keys import file_content_key
14from .library_paths import locate_library_file
15from .track_index import normalize_spotify_track_id, read_monitor_rows
18def _now_iso() -> str:
19 return datetime.now(timezone.utc).isoformat()
22def _norm_path(filename: str) -> str:
23 return str(filename or '').strip().replace('\\', '/')
26def _catalog_track_id(song: dict[str, Any]) -> Optional[str]:
27 """Stable playlist membership key; never put Deezer in TrackIndex.
29 The legacy column name is ``track_spotify_id``, but its TEXT key can
30 also hold the Deezer resolver's explicitly namespaced track identity.
31 """
32 spotify_id = normalize_spotify_track_id(song)
33 if spotify_id:
34 return spotify_id
35 track_id = str(song.get('song_id') or '')
36 if (
37 song.get('source') == 'deezer'
38 and track_id.startswith('deezer-')
39 and track_id.removeprefix('deezer-').isascii()
40 and track_id.removeprefix('deezer-').isdigit()
41 ):
42 return track_id
43 return None
46def _chunked(values: list[str], size: int):
47 for index in range(0, len(values), size):
48 chunk = values[index : index + size]
49 if chunk:
50 yield chunk
53def _text_order(dialect: Dialect) -> str:
54 """ORDER BY suffix for text columns that sorts bytewise, like SQLite.
56 PostgreSQL sorts by the database collation (case and punctuation
57 aware); the ``"C"`` collation is PostgreSQL-only, so SQLite gets none.
58 """
59 return ' COLLATE "C"' if dialect is Dialect.POSTGRES else ''
62class PlaylistCatalog:
63 """Maps playlist names to ordered track identities and library paths."""
65 def __init__(self, path_or_db: str | Path | Database) -> None:
66 self.db = as_database(path_or_db, 'library')
67 self._path = str(self.db.path) if self.db.path is not None else None
68 self._init_db()
70 def _connect(self) -> AbstractContextManager[Connection]:
71 return self.db.connect()
73 def _init_db(self) -> None:
74 if self.db.dialect is not Dialect.SQLITE:
75 # The migrations already created the final schema.
76 return
77 with self._connect() as conn:
78 conn.execute("""
79 CREATE TABLE IF NOT EXISTS playlists (
80 name TEXT PRIMARY KEY,
81 spotify_id TEXT,
82 updated_at TEXT NOT NULL
83 )
84 """)
85 columns = {
86 row[1] for row in conn.execute('PRAGMA table_info(playlists)')
87 }
88 if 'user_id' not in columns:
89 # Who downloaded it (``0``: before accounts could own
90 # anything - admins only, like everywhere else).
91 conn.execute(
92 'ALTER TABLE playlists ADD COLUMN user_id INTEGER '
93 'NOT NULL DEFAULT 0'
94 )
95 conn.execute("""
96 CREATE TABLE IF NOT EXISTS manual_playlist_owners (
97 name_key TEXT PRIMARY KEY,
98 name TEXT NOT NULL,
99 user_id INTEGER NOT NULL
100 )
101 """)
102 conn.execute("""
103 CREATE TABLE IF NOT EXISTS playlist_tracks (
104 playlist_name TEXT NOT NULL,
105 track_spotify_id TEXT NOT NULL,
106 content_key TEXT,
107 filename TEXT NOT NULL,
108 track_order INTEGER NOT NULL DEFAULT 0,
109 PRIMARY KEY (playlist_name, track_spotify_id),
110 FOREIGN KEY (playlist_name) REFERENCES playlists(name)
111 ON DELETE CASCADE
112 )
113 """)
114 conn.execute("""
115 CREATE INDEX IF NOT EXISTS idx_playlist_tracks_content_key
116 ON playlist_tracks (content_key)
117 WHERE content_key IS NOT NULL
118 """)
120 def ensure_playlist(
121 self,
122 name: str,
123 *,
124 spotify_id: Optional[str] = None,
125 user_id: Optional[int] = None,
126 ) -> None:
127 """Make sure the playlist row exists. *user_id* claims ownership
128 when the row is created - or when nobody claimed it yet
129 (``0``); re-saving someone else's playlist never steals it."""
131 pl_name = str(name or '').strip()
132 if not pl_name:
133 return
134 sid = str(spotify_id or '').strip() or None
135 owner = int(user_id or 0)
136 with self._connect() as conn:
137 conn.execute(
138 """INSERT INTO playlists (name, spotify_id, updated_at,
139 user_id)
140 VALUES (?, ?, ?, ?)
141 ON CONFLICT(name) DO UPDATE SET
142 spotify_id=COALESCE(excluded.spotify_id,
143 playlists.spotify_id),
144 updated_at=excluded.updated_at,
145 user_id=CASE WHEN playlists.user_id = 0
146 THEN excluded.user_id ELSE playlists.user_id END""",
147 (pl_name, sid, _now_iso(), owner),
148 )
150 def upsert_track(
151 self,
152 playlist_name: str,
153 song: dict[str, Any],
154 filename: str,
155 full_path: Path,
156 *,
157 track_order: int = 0,
158 ) -> None:
159 pl_name = str(playlist_name or '').strip()
160 tid = _catalog_track_id(song)
161 name = _norm_path(filename)
162 if not pl_name or not tid or not name:
163 return
164 ck = file_content_key(full_path)
165 self.ensure_playlist(pl_name, spotify_id=None)
166 with self._connect() as conn:
167 conn.execute(
168 """INSERT INTO playlist_tracks
169 (playlist_name, track_spotify_id, content_key, filename, track_order)
170 VALUES (?, ?, ?, ?, ?)
171 ON CONFLICT(playlist_name, track_spotify_id) DO UPDATE SET
172 content_key=excluded.content_key,
173 filename=excluded.filename,
174 track_order=excluded.track_order""",
175 (pl_name, tid, ck, name, int(track_order)),
176 )
178 def replace_playlist_tracks(
179 self,
180 playlist_name: str,
181 rows: list[tuple[dict[str, Any], str, Path]],
182 *,
183 spotify_id: Optional[str] = None,
184 user_id: Optional[int] = None,
185 ) -> None:
186 """Replace all tracks for a playlist (batch download).
188 *user_id* claims the playlist when the row is created; sweeps
189 re-saving someone else's playlist never steal it.
190 """
192 pl_name = str(playlist_name or '').strip()
193 if not pl_name:
194 return
195 self.ensure_playlist(pl_name, spotify_id=spotify_id, user_id=user_id)
196 with self._connect() as conn:
197 conn.execute(
198 'DELETE FROM playlist_tracks WHERE playlist_name = ?',
199 (pl_name,),
200 )
201 for index, (song, filename, full_path) in enumerate(rows):
202 self.upsert_track(
203 pl_name, song, filename, full_path, track_order=index
204 )
206 def backfill_from_monitor_db(
207 self,
208 monitor_db_path: Path | Database,
209 *,
210 download_dir: Path,
211 slskd_dir: Optional[Path] = None,
212 ) -> int:
213 """Import playlist membership from monitor history (older downloads).
215 ``monitor_db_path`` is either the monitor's ``Database`` (any
216 backend) or a legacy SQLite file path.
217 """
219 rows = read_monitor_rows(
220 self.db,
221 monitor_db_path,
222 """SELECT p.name, p.spotify_id, dt.track_spotify_id, dt.filename
223 FROM downloaded_tracks dt
224 JOIN monitored_playlists p ON p.id = dt.playlist_id
225 WHERE dt.filename IS NOT NULL AND dt.filename != ''""",
226 )
228 linked = 0
229 # A few commits instead of three per row (downtify/db/batching.py).
230 for row in in_short_transactions(self.db, rows):
231 pl_name = str(row[0] or '').strip()
232 spotify_id = str(row[1] or '').strip() or None
233 tid = str(row[2] or '').strip()
234 filename = _norm_path(str(row[3] or ''))
235 if not pl_name or not tid or not filename:
236 continue
237 full = locate_library_file(filename, download_dir, slskd_dir)
238 if full is None:
239 continue
240 self.ensure_playlist(pl_name, spotify_id=spotify_id)
241 self.upsert_track(pl_name, {'song_id': tid}, filename, full)
242 linked += 1
243 return linked
245 def list_playlist_names(self) -> list[str]:
246 order = _text_order(self.db.dialect)
247 with self._connect() as conn:
248 rows = conn.execute(
249 f'SELECT name FROM playlists ORDER BY name{order}'
250 ).fetchall()
251 return [str(row['name']) for row in rows]
253 def playlist_owner_map(self) -> dict[str, int]:
254 """``{name: user_id}`` for downloaded playlists (one query)."""
256 with self._connect() as conn:
257 rows = conn.execute(
258 'SELECT name, user_id FROM playlists'
259 ).fetchall()
260 return {str(row['name']): int(row['user_id'] or 0) for row in rows}
262 def list_playlists_with_spotify_id(self) -> list[dict[str, Any]]:
263 """Playlists in the catalog that have a linked Spotify playlist id."""
265 order = _text_order(self.db.dialect)
266 with self._connect() as conn:
267 rows = conn.execute(
268 f"""SELECT p.name, p.spotify_id,
269 (SELECT COUNT(*) FROM playlist_tracks pt
270 WHERE pt.playlist_name = p.name) AS track_count
271 FROM playlists p
272 WHERE p.spotify_id IS NOT NULL AND TRIM(p.spotify_id) != ''
273 ORDER BY p.name{order}"""
274 ).fetchall()
275 return [
276 {
277 'name': str(row['name']),
278 'spotify_id': str(row['spotify_id']),
279 'track_count': int(row['track_count'] or 0),
280 }
281 for row in rows
282 ]
284 def list_tracks(self, playlist_name: str) -> list[dict[str, Any]]:
285 pl_name = str(playlist_name or '').strip()
286 if not pl_name:
287 return []
288 order = _text_order(self.db.dialect)
289 with self._connect() as conn:
290 rows = conn.execute(
291 f"""SELECT track_spotify_id, content_key, filename, track_order
292 FROM playlist_tracks
293 WHERE playlist_name = ?
294 ORDER BY track_order ASC, track_spotify_id{order} ASC""",
295 (pl_name,),
296 ).fetchall()
297 return [
298 {
299 'track_spotify_id': str(row['track_spotify_id']),
300 'content_key': row['content_key'],
301 'filename': _norm_path(str(row['filename'])),
302 'track_order': int(row['track_order']),
303 }
304 for row in rows
305 ]
307 def playlists_for_track(self, spotify_track_id: str) -> list[str]:
308 """Playlist names that catalog this Spotify track ID."""
310 tid = str(spotify_track_id or '').strip()
311 if not tid:
312 return []
313 order = _text_order(self.db.dialect)
314 with self._connect() as conn:
315 rows = conn.execute(
316 f"""SELECT DISTINCT playlist_name{order} AS playlist_name
317 FROM playlist_tracks
318 WHERE track_spotify_id = ?
319 ORDER BY playlist_name""",
320 (tid,),
321 ).fetchall()
322 return [str(row['playlist_name']) for row in rows]
324 def spotify_id_for_playlist(self, playlist_name: str) -> Optional[str]:
325 pl_name = str(playlist_name or '').strip()
326 if not pl_name:
327 return None
328 with self._connect() as conn:
329 row = conn.execute(
330 'SELECT spotify_id FROM playlists WHERE name = ?',
331 (pl_name,),
332 ).fetchone()
333 if row is None or not row['spotify_id']:
334 return None
335 return str(row['spotify_id'])
337 def update_filename_by_content_key(
338 self, content_key: str, filename: str
339 ) -> list[str]:
340 """Update rows for *content_key*; return affected playlist names."""
342 ck = str(content_key or '').strip()
343 name = _norm_path(filename)
344 if not ck or not name:
345 return []
346 with self._connect() as conn:
347 conn.execute(
348 """UPDATE playlist_tracks SET filename = ?
349 WHERE content_key = ? AND filename != ?""",
350 (name, ck, name),
351 )
352 rows = conn.execute(
353 """SELECT DISTINCT playlist_name FROM playlist_tracks
354 WHERE content_key = ?""",
355 (ck,),
356 ).fetchall()
357 return [str(row['playlist_name']) for row in rows]
359 def playlists_by_content_keys(
360 self, content_keys: list[str]
361 ) -> dict[str, list[str]]:
362 keys = [str(k).strip() for k in content_keys if str(k).strip()]
363 if not keys:
364 return {}
365 result: dict[str, list[str]] = {k: [] for k in keys}
366 order = _text_order(self.db.dialect)
367 with self._connect() as conn:
368 for chunk in _chunked(keys, 400):
369 placeholders = ','.join('?' * len(chunk))
370 rows = conn.execute(
371 f"""SELECT content_key, playlist_name FROM playlist_tracks
372 WHERE content_key IN ({placeholders})
373 ORDER BY playlist_name{order}""",
374 chunk,
375 ).fetchall()
376 for row in rows:
377 ck = str(row['content_key'])
378 pl = str(row['playlist_name'])
379 if pl not in result[ck]:
380 result[ck].append(pl)
381 return result
383 def playlists_by_filenames(
384 self, filenames: list[str]
385 ) -> dict[str, list[str]]:
386 names = [_norm_path(n) for n in filenames if _norm_path(n)]
387 if not names:
388 return {}
389 result: dict[str, list[str]] = {n: [] for n in names}
390 order = _text_order(self.db.dialect)
391 with self._connect() as conn:
392 for chunk in _chunked(names, 400):
393 placeholders = ','.join('?' * len(chunk))
394 rows = conn.execute(
395 f"""SELECT filename, playlist_name FROM playlist_tracks
396 WHERE filename IN ({placeholders})
397 ORDER BY playlist_name{order}""",
398 chunk,
399 ).fetchall()
400 for row in rows:
401 fn = _norm_path(str(row['filename']))
402 pl = str(row['playlist_name'])
403 if pl not in result[fn]:
404 result[fn].append(pl)
405 return result
407 def set_content_key(
408 self,
409 playlist_name: str,
410 track_spotify_id: str,
411 content_key: str,
412 ) -> None:
413 pl_name = str(playlist_name or '').strip()
414 tid = str(track_spotify_id or '').strip()
415 ck = str(content_key or '').strip()
416 if not pl_name or not tid or not ck:
417 return
418 with self._connect() as conn:
419 conn.execute(
420 """UPDATE playlist_tracks SET content_key = ?
421 WHERE playlist_name = ? AND track_spotify_id = ?""",
422 (ck, pl_name, tid),
423 )
425 def remove_track(self, playlist_name: str, track_spotify_id: str) -> bool:
426 pl_name = str(playlist_name or '').strip()
427 tid = str(track_spotify_id or '').strip()
428 if not pl_name or not tid:
429 return False
430 with self._connect() as conn:
431 cur = conn.execute(
432 """DELETE FROM playlist_tracks
433 WHERE playlist_name = ? AND track_spotify_id = ?""",
434 (pl_name, tid),
435 )
436 return cur.rowcount > 0
438 def reassign_legacy(self, user_id: int) -> int:
439 """Give pre-feature playlists (``user_id`` ``0``) to *user_id*.
441 Runs once on the first start with ownership; later system rows
442 keep ``0`` (admins-only), so this must not run again.
443 """
445 if not user_id:
446 return 0
447 with self._connect() as conn:
448 cur = conn.execute(
449 'UPDATE playlists SET user_id = ? WHERE user_id = 0',
450 (int(user_id),),
451 )
452 return cur.rowcount
454 def delete_playlist(self, playlist_name: str) -> list[str]:
455 """Remove playlist and all track rows; return registered filenames."""
457 pl_name = str(playlist_name or '').strip()
458 if not pl_name:
459 return []
460 with self._connect() as conn:
461 rows = conn.execute(
462 """SELECT filename FROM playlist_tracks
463 WHERE playlist_name = ?""",
464 (pl_name,),
465 ).fetchall()
466 filenames = list(
467 dict.fromkeys(
468 _norm_path(str(row['filename']))
469 for row in rows
470 if row['filename']
471 )
472 )
473 # Children first. PostgreSQL's ON DELETE CASCADE would do this
474 # too, but SQLite does not enforce foreign keys here.
475 conn.execute(
476 'DELETE FROM playlist_tracks WHERE playlist_name = ?',
477 (pl_name,),
478 )
479 conn.execute('DELETE FROM playlists WHERE name = ?', (pl_name,))
480 return filenames
482 def remove_tracks_for_filename(
483 self, filename: str, playlist_name: Optional[str] = None
484 ) -> list[str]:
485 """Delete rows pointing at *filename* - only *playlist_name*'s
486 when given (a song hidden from it); return affected playlist
487 names."""
489 name = _norm_path(filename)
490 if not name:
491 return []
492 where = 'filename = ?'
493 params: tuple[str, ...] = (name,)
494 if playlist_name is not None:
495 where += ' AND playlist_name = ?'
496 params = (name, str(playlist_name))
497 with self._connect() as conn:
498 rows = conn.execute(
499 f'SELECT DISTINCT playlist_name FROM playlist_tracks '
500 f'WHERE {where}',
501 params,
502 ).fetchall()
503 conn.execute(f'DELETE FROM playlist_tracks WHERE {where}', params)
504 return [str(row['playlist_name']) for row in rows]
506 def all_track_rows(self) -> list[dict[str, Any]]:
507 with self._connect() as conn:
508 rows = conn.execute(
509 """SELECT playlist_name, track_spotify_id, content_key, filename
510 FROM playlist_tracks"""
511 ).fetchall()
512 return [
513 {
514 'playlist_name': str(row['playlist_name']),
515 'track_spotify_id': str(row['track_spotify_id']),
516 'content_key': row['content_key'],
517 'filename': _norm_path(str(row['filename'])),
518 }
519 for row in rows
520 ]
523class ManualPlaylistOwners:
524 """Who created each Library playlist (M3U files carry no owner).
526 A user opens their own manual playlists only; unclaimed
527 (pre-feature) ones are admins-only, like everywhere else.
528 """
530 def __init__(self, path_or_db: str | Path | Database) -> None:
531 self.db = as_database(path_or_db, 'library')
532 self._path = str(self.db.path) if self.db.path is not None else None
533 self._init_db()
535 def _connect(self) -> AbstractContextManager[Connection]:
536 return self.db.connect()
538 def _init_db(self) -> None:
539 if self.db.dialect is not Dialect.SQLITE:
540 # The migrations already created the final schema.
541 return
542 with self._connect() as conn:
543 conn.execute("""
544 CREATE TABLE IF NOT EXISTS manual_playlist_owners (
545 name_key TEXT PRIMARY KEY,
546 name TEXT NOT NULL,
547 user_id INTEGER NOT NULL
548 )
549 """)
551 @staticmethod
552 def _key(name: str) -> str:
553 return str(name or '').strip().casefold()
555 def set_owner(self, name: str, user_id: int) -> None:
556 """Claim a Library-created playlist for *user_id*."""
558 key = self._key(name)
559 if not key or not user_id:
560 return
561 with self._connect() as conn:
562 conn.execute(
563 """INSERT INTO manual_playlist_owners
564 (name_key, name, user_id)
565 VALUES (?, ?, ?)
566 ON CONFLICT(name_key) DO UPDATE SET
567 name=excluded.name, user_id=excluded.user_id""",
568 (key, str(name or '').strip(), int(user_id)),
569 )
571 def claim_owner(self, name: str, user_id: int) -> bool:
572 """Claim an unowned playlist without replacing an existing owner."""
574 key = self._key(name)
575 if not key or not user_id:
576 return False
577 with self._connect() as conn:
578 cursor = conn.execute(
579 """INSERT INTO manual_playlist_owners
580 (name_key, name, user_id) VALUES (?, ?, ?)
581 ON CONFLICT DO NOTHING""",
582 (key, str(name or '').strip(), int(user_id)),
583 )
584 return cursor.rowcount == 1
586 def owner(self, name: str) -> Optional[int]:
587 """The owner's user id, or ``None`` when nobody claimed it."""
589 key = self._key(name)
590 if not key:
591 return None
592 with self._connect() as conn:
593 row = conn.execute(
594 'SELECT user_id FROM manual_playlist_owners WHERE name_key '
595 '= ?',
596 (key,),
597 ).fetchone()
598 if row is None:
599 return None
600 return int(row['user_id'] or 0) or None
602 def forget_owner(self, name: str) -> None:
603 with self._connect() as conn:
604 conn.execute(
605 'DELETE FROM manual_playlist_owners WHERE name_key = ?',
606 (self._key(name),),
607 )
609 def rename_owner(self, name: str, new_name: str) -> None:
610 """Move a manual playlist's owner to its new name (if any)."""
612 owner = self.owner(name)
613 self.forget_owner(name)
614 if owner is not None:
615 self.set_owner(new_name, owner)
617 def owner_map(self) -> dict[str, int]:
618 """``{casefolded name: user_id}`` (one query)."""
620 with self._connect() as conn:
621 rows = conn.execute(
622 'SELECT name_key, user_id FROM manual_playlist_owners'
623 ).fetchall()
624 return {str(row['name_key']): int(row['user_id'] or 0) for row in rows}
626 def remove_user(self, user_id: int) -> None:
627 """Release a deleted account's playlists (files stay put)."""
629 with self._connect() as conn:
630 conn.execute(
631 'DELETE FROM manual_playlist_owners WHERE user_id = ?',
632 (int(user_id),),
633 )