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

1"""What each Playlist Monitor watch added to the library, and failed to. 

2 

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: 

6 

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. 

16 

17:mod:`downtify.watch_stats` turns both into provenance and statistics. 

18""" 

19 

20from __future__ import annotations 

21 

22from contextlib import AbstractContextManager 

23from datetime import datetime, timezone 

24from typing import TYPE_CHECKING, Any, Callable, Optional 

25 

26from downtify.db.config import Dialect 

27from downtify.db.dialect import days_between_sql 

28 

29if TYPE_CHECKING: 

30 from downtify.db.core import Connection 

31 

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' 

39 

40_EVENT_COLUMNS = ( 

41 'playlist_id, watch_key, watch_name, user_id, track_id, outcome, ' 

42 'filename, provider, initial, happened_at' 

43) 

44 

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

79 

80 

81def _now_iso() -> str: 

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

83 

84 

85def _marks(values: list[Any]) -> str: 

86 return ','.join('?' * len(values)) 

87 

88 

89class WatchHistoryStore: 

90 """``watch_events`` and ``watch_failures``, beside the watches.""" 

91 

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) 

119 

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

131 

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 ) 

152 

153 def rename_file(self, track_id: str, filename: str) -> None: 

154 """Point the events of *track_id* at the file now holding it.""" 

155 

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 ) 

163 

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

173 

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] 

194 

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

200 

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] 

215 

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

220 

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 ) 

249 

250 def forget_failures(self, playlist_id: int) -> int: 

251 """Drop every failure of a watch (the user starts the count over).""" 

252 

253 with self._connect() as conn: 

254 return conn.execute( 

255 'DELETE FROM watch_failures WHERE playlist_id = ?', 

256 (playlist_id,), 

257 ).rowcount 

258 

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

263 

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) 

280 

281 def failures(self, playlist_id: int) -> list[dict[str, Any]]: 

282 """The tracks the watch keeps failing to download, latest first.""" 

283 

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] 

293 

294 def failure_counts(self) -> dict[int, int]: 

295 """``{watch id: tracks failing}``.""" 

296 

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} 

303 

304 

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

309 

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 )