Coverage for downtify/watch_history.py: 100%
92 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"""What each Playlist Monitor watch added to the library, and failed to.
3``downloaded_tracks`` (see :mod:`downtify.monitor`) only says which
4tracks a watch holds right now. This keeps the history behind it, in the
5same database:
7* ``watch_events`` - one row each time a watch adds a track: when, the
8 file, which provider fetched it, whether the watch downloaded it or
9 found it already in the library (``OUTCOME_*``) and whether it came
10 with the watch's first check (its backfill). Rows name the watch as it
11 was then (id, target key, name, owner) and outlive it - deleting or
12 retargeting a watch keeps them - so a song's origin can be told later.
13* ``watch_failures`` - the tracks a watch keeps failing to download,
14 until one succeeds, the track leaves the playlist or the watch is
15 retargeted.
17:mod:`downtify.watch_stats` turns both into provenance and statistics.
18"""
20from __future__ import annotations
22from contextlib import AbstractContextManager
23from datetime import datetime, timezone
24from typing import TYPE_CHECKING, Any, Callable, Optional
26from downtify.db.config import Dialect
27from downtify.db.dialect import days_between_sql
29if TYPE_CHECKING:
30 from downtify.db.core import Connection
32#: How a track got into a watch: the watch fetched new audio for it, or
33#: the song was already in the library (a file the track index knew, or
34#: one already at the expected path) and was only linked. ``unknown``:
35#: recorded before outcomes were kept.
36OUTCOME_DOWNLOADED = 'downloaded'
37OUTCOME_LINKED = 'linked'
38OUTCOME_UNKNOWN = 'unknown'
40_EVENT_COLUMNS = (
41 'playlist_id, watch_key, watch_name, user_id, track_id, outcome, '
42 'filename, provider, initial, happened_at'
43)
45_SQLITE_DDL = """
46 CREATE TABLE IF NOT EXISTS watch_events (
47 id INTEGER PRIMARY KEY AUTOINCREMENT,
48 playlist_id INTEGER NOT NULL,
49 watch_key TEXT NOT NULL,
50 watch_name TEXT NOT NULL,
51 user_id INTEGER NOT NULL DEFAULT 0,
52 track_id TEXT NOT NULL,
53 outcome TEXT NOT NULL,
54 filename TEXT,
55 provider TEXT NOT NULL DEFAULT '',
56 initial INTEGER NOT NULL DEFAULT 0,
57 happened_at TEXT NOT NULL
58 );
59 CREATE INDEX IF NOT EXISTS idx_watch_events_watch
60 ON watch_events (playlist_id, happened_at);
61 CREATE INDEX IF NOT EXISTS idx_watch_events_track
62 ON watch_events (track_id);
63 CREATE INDEX IF NOT EXISTS idx_watch_events_file
64 ON watch_events (filename);
65 CREATE TABLE IF NOT EXISTS watch_failures (
66 playlist_id INTEGER NOT NULL,
67 track_id TEXT NOT NULL,
68 name TEXT NOT NULL DEFAULT '',
69 artists TEXT NOT NULL DEFAULT '',
70 error TEXT NOT NULL DEFAULT '',
71 attempts INTEGER NOT NULL DEFAULT 1,
72 first_failed_at TEXT NOT NULL,
73 last_failed_at TEXT NOT NULL,
74 PRIMARY KEY (playlist_id, track_id),
75 FOREIGN KEY (playlist_id)
76 REFERENCES monitored_playlists(id) ON DELETE CASCADE
77 );
78"""
81def _now_iso() -> str:
82 return datetime.now(timezone.utc).isoformat()
85def _marks(values: list[Any]) -> str:
86 return ','.join('?' * len(values))
89class WatchHistoryStore:
90 """``watch_events`` and ``watch_failures``, beside the watches."""
92 def __init__(
93 self, connect: Callable[[], AbstractContextManager[Connection]]
94 ) -> None:
95 self._connect = connect
96 with self._connect() as conn:
97 dialect = getattr(conn, 'dialect', Dialect.SQLITE)
98 if dialect is Dialect.SQLITE:
99 had_events = (
100 conn.execute(
101 "SELECT 1 FROM sqlite_master WHERE type = 'table' "
102 "AND name = 'watch_events'"
103 ).fetchone()
104 is not None
105 )
106 conn.executescript(_SQLITE_DDL)
107 else:
108 # The migrations created the tables. Without any event
109 # yet, rows from before the history existed still need
110 # their backfill.
111 had_events = (
112 conn.execute(
113 'SELECT 1 FROM watch_events LIMIT 1'
114 ).fetchone()
115 is not None
116 )
117 if not had_events:
118 _backfill(conn)
120 def record_added(
121 self,
122 playlist_id: int,
123 track_id: str,
124 filename: Optional[str],
125 *,
126 outcome: str,
127 provider: str = '',
128 initial: bool = False,
129 ) -> None:
130 """Log that the watch added *track_id* (and clear its failure)."""
132 with self._connect() as conn:
133 conn.execute(
134 f"""INSERT INTO watch_events ({_EVENT_COLUMNS})
135 SELECT id, spotify_id, name, user_id, ?, ?, ?, ?, ?, ?
136 FROM monitored_playlists WHERE id = ?""",
137 (
138 track_id,
139 outcome,
140 filename,
141 str(provider or ''),
142 int(initial),
143 _now_iso(),
144 playlist_id,
145 ),
146 )
147 conn.execute(
148 'DELETE FROM watch_failures '
149 'WHERE playlist_id = ? AND track_id = ?',
150 (playlist_id, track_id),
151 )
153 def rename_file(self, track_id: str, filename: str) -> None:
154 """Point the events of *track_id* at the file now holding it."""
156 with self._connect() as conn:
157 conn.execute(
158 """UPDATE watch_events SET filename = ?
159 WHERE track_id = ?
160 AND (filename IS NULL OR filename != ?)""",
161 (filename, track_id, filename),
162 )
164 def events(
165 self,
166 *,
167 playlist_id: Optional[int] = None,
168 filename: Optional[str] = None,
169 track_ids: Optional[set[str]] = None,
170 ) -> list[dict[str, Any]]:
171 """Events, oldest first: every one, one watch's (*playlist_id*),
172 or those about *filename* or any of *track_ids*."""
174 query = f'SELECT {_EVENT_COLUMNS} FROM watch_events'
175 params: list[Any] = []
176 if playlist_id is not None:
177 query += ' WHERE playlist_id = ?'
178 params = [int(playlist_id)]
179 elif filename is not None or track_ids:
180 clauses = []
181 if filename is not None:
182 clauses.append('filename = ?')
183 params.append(filename)
184 ids = sorted(track_ids or ())
185 if ids:
186 clauses.append(f'track_id IN ({_marks(ids)})')
187 params.extend(ids)
188 query += f' WHERE {" OR ".join(clauses)}'
189 with self._connect() as conn:
190 rows = conn.execute(
191 f'{query} ORDER BY happened_at, id', params
192 ).fetchall()
193 return [dict(r) for r in rows]
195 def holdings(
196 self, filename: str, track_ids: Optional[set[str]] = None
197 ) -> list[dict[str, Any]]:
198 """The ``downloaded_tracks`` rows (every watch) holding the file
199 *filename*, or any of *track_ids*: who holds the song now."""
201 ids = sorted(track_ids or ())
202 clauses = ['filename = ?']
203 params: list[Any] = [filename]
204 if ids:
205 clauses.append(f'track_spotify_id IN ({_marks(ids)})')
206 params.extend(ids)
207 with self._connect() as conn:
208 rows = conn.execute(
209 'SELECT playlist_id, track_spotify_id, filename, '
210 'downloaded_at FROM downloaded_tracks '
211 f'WHERE {" OR ".join(clauses)}',
212 params,
213 ).fetchall()
214 return [dict(r) for r in rows]
216 def record_failure(
217 self, playlist_id: int, song: dict[str, Any], error: str = ''
218 ) -> None:
219 """Remember that the watch could not download *song* this time."""
221 tid = str(song.get('song_id') or '').strip()
222 if not tid:
223 return
224 now = _now_iso()
225 artists = song.get('artists') or []
226 if isinstance(artists, str):
227 artists = [artists]
228 with self._connect() as conn:
229 conn.execute(
230 """INSERT INTO watch_failures
231 (playlist_id, track_id, name, artists, error, attempts,
232 first_failed_at, last_failed_at)
233 VALUES (?, ?, ?, ?, ?, 1, ?, ?)
234 ON CONFLICT(playlist_id, track_id) DO UPDATE SET
235 name = excluded.name, artists = excluded.artists,
236 error = excluded.error,
237 attempts = watch_failures.attempts + 1,
238 last_failed_at = excluded.last_failed_at""",
239 (
240 playlist_id,
241 tid,
242 str(song.get('name') or ''),
243 ', '.join(str(a) for a in artists if a),
244 str(error or '')[:500],
245 now,
246 now,
247 ),
248 )
250 def forget_failures(self, playlist_id: int) -> int:
251 """Drop every failure of a watch (the user starts the count over)."""
253 with self._connect() as conn:
254 return conn.execute(
255 'DELETE FROM watch_failures WHERE playlist_id = ?',
256 (playlist_id,),
257 ).rowcount
259 def forget_failures_except(
260 self, playlist_id: int, track_ids: set[str]
261 ) -> int:
262 """Drop the failures of tracks no longer in the playlist."""
264 with self._connect() as conn:
265 stale = [
266 (playlist_id, row['track_id'])
267 for row in conn.execute(
268 'SELECT track_id FROM watch_failures '
269 'WHERE playlist_id = ?',
270 (playlist_id,),
271 )
272 if row['track_id'] not in track_ids
273 ]
274 conn.executemany(
275 'DELETE FROM watch_failures '
276 'WHERE playlist_id = ? AND track_id = ?',
277 stale,
278 )
279 return len(stale)
281 def failures(self, playlist_id: int) -> list[dict[str, Any]]:
282 """The tracks the watch keeps failing to download, latest first."""
284 with self._connect() as conn:
285 rows = conn.execute(
286 """SELECT track_id, name, artists, error, attempts,
287 first_failed_at, last_failed_at
288 FROM watch_failures WHERE playlist_id = ?
289 ORDER BY last_failed_at DESC""",
290 (playlist_id,),
291 ).fetchall()
292 return [dict(r) for r in rows]
294 def failure_counts(self) -> dict[int, int]:
295 """``{watch id: tracks failing}``."""
297 with self._connect() as conn:
298 rows = conn.execute(
299 'SELECT playlist_id, COUNT(*) AS n FROM watch_failures '
300 'GROUP BY playlist_id'
301 ).fetchall()
302 return {int(r['playlist_id']): int(r['n']) for r in rows}
305def _backfill(conn: Connection) -> None:
306 """Once, when the table is new: the tracks watches recorded before
307 events were kept become events of unknown outcome. Those from a
308 watch's first day are taken for its first check's backfill."""
310 dialect = getattr(conn, 'dialect', Dialect.SQLITE)
311 first_day = (
312 days_between_sql('dt.downloaded_at', 'p.created_at', dialect) + ' <= 1'
313 )
314 conn.execute(
315 f"""INSERT INTO watch_events ({_EVENT_COLUMNS})
316 SELECT dt.playlist_id, p.spotify_id, p.name, p.user_id,
317 dt.track_spotify_id, '{OUTCOME_UNKNOWN}', dt.filename, '',
318 CASE WHEN {first_day} THEN 1 ELSE 0 END,
319 dt.downloaded_at
320 FROM downloaded_tracks dt
321 JOIN monitored_playlists p ON p.id = dt.playlist_id
322 ORDER BY dt.downloaded_at, dt.id"""
323 )