1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
|
"""
MeshBay Node — TMDB/MusicBrainz metadata and thumbnail cache, shared by the
Videos and Music group apps.
Node-wide (not per-group, `data_dir/media_cache.db`), same rationale as
`tmdb_enabled`/`tmdb_api_token` (and `musicbrainz_enabled`/
`musicbrainz_contact`, docs/musicbay.md §6) living in `group_settings` under
the `group_id=""` sentinel (docs/mediacenter.md §5.5): the credential/budget
is one operator's, and a thumbnail or cover image is the same bytes
regardless of which group happens to share the file. The `file_mbid`/
`mbid_meta` tables below are the Music app's equivalent of `file_tmdb`/
`tmdb_meta`, sharing the same `thumbs` table for cover art (a MusicBrainz
release's cover is cached under a synthetic `musicbrainz:{mbid}` file_id,
the same trick `_fetch_and_cache_poster` uses for a TMDB poster_path).
Disposable and rebuildable, like the rest of the file index (§1, §2) — never
a second identity for a file. Every row here is keyed off a value the node
can already derive (a file's own blake3 id, or a TMDB id), so losing this
database costs re-probing/re-fetching, not data.
"""
import json
import logging
import time
from pathlib import Path
import aiosqlite
log = logging.getLogger(__name__)
_SCHEMA = """
CREATE TABLE IF NOT EXISTS file_tmdb (
file_id TEXT PRIMARY KEY,
tmdb_id TEXT NOT NULL,
media_type TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS tmdb_meta (
tmdb_id TEXT NOT NULL,
media_type TEXT NOT NULL,
json TEXT NOT NULL,
fetched_at REAL NOT NULL,
PRIMARY KEY (tmdb_id, media_type)
);
CREATE TABLE IF NOT EXISTS thumbs (
thumb_hash TEXT PRIMARY KEY,
file_id TEXT NOT NULL,
jpeg BLOB NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_thumbs_file ON thumbs(file_id);
CREATE TABLE IF NOT EXISTS season_meta (
tmdb_id TEXT NOT NULL,
season INTEGER NOT NULL,
json TEXT NOT NULL,
fetched_at REAL NOT NULL,
PRIMARY KEY (tmdb_id, season)
);
CREATE TABLE IF NOT EXISTS file_mbid (
file_id TEXT PRIMARY KEY,
mbid TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS mbid_meta (
mbid TEXT PRIMARY KEY,
json TEXT NOT NULL,
fetched_at REAL NOT NULL
);
"""
# TMDB overviews/ratings do drift; a file's own resolved tmdb_id does not
# need re-checking on this schedule, only the metadata blob (§5.4, V3).
TMDB_META_TTL_SECS = 30 * 86400
# Same default as TMDB (docs/musicbay.md §6) — MusicBrainz release data is
# not expected to drift faster; revisit if that proves wrong in practice.
MUSICBRAINZ_META_TTL_SECS = 30 * 86400
class MediaCache:
"""Async SQLite cache for TMDB/MusicBrainz lookups and generated thumbnails/cover art."""
def __init__(self, db_path: Path):
self._db_path = db_path
self._db: aiosqlite.Connection | None = None
async def open(self) -> None:
self._db_path.parent.mkdir(parents=True, exist_ok=True)
self._db = await aiosqlite.connect(str(self._db_path))
await self._db.executescript(_SCHEMA)
await self._db.commit()
async def close(self) -> None:
if self._db:
await self._db.close()
self._db = None
# ── file -> tmdb id mapping ──────────────────────────────────────────────
async def get_file_tmdb(self, file_id: str) -> tuple[str, str] | None:
"""Returns (tmdb_id, media_type), or None if this file was never resolved."""
async with self._db.execute(
"SELECT tmdb_id, media_type FROM file_tmdb WHERE file_id = ?",
(file_id,),
) as cur:
row = await cur.fetchone()
return (row[0], row[1]) if row else None
async def set_file_tmdb(self, file_id: str, tmdb_id: str, media_type: str) -> None:
await self._db.execute(
"INSERT OR REPLACE INTO file_tmdb (file_id, tmdb_id, media_type) "
"VALUES (?, ?, ?)",
(file_id, tmdb_id, media_type),
)
await self._db.commit()
# ── tmdb id -> metadata json ─────────────────────────────────────────────
async def get_tmdb_meta(self, tmdb_id: str, media_type: str) -> dict | None:
"""Returns None on a miss or on an entry older than TMDB_META_TTL_SECS."""
async with self._db.execute(
"SELECT json, fetched_at FROM tmdb_meta WHERE tmdb_id = ? AND media_type = ?",
(tmdb_id, media_type),
) as cur:
row = await cur.fetchone()
if not row:
return None
raw_json, fetched_at = row
if time.time() - fetched_at > TMDB_META_TTL_SECS:
return None
return json.loads(raw_json)
async def set_tmdb_meta(self, tmdb_id: str, media_type: str, meta: dict) -> None:
await self._db.execute(
"INSERT OR REPLACE INTO tmdb_meta (tmdb_id, media_type, json, fetched_at) "
"VALUES (?, ?, ?, ?)",
(tmdb_id, media_type, json.dumps(meta), time.time()),
)
await self._db.commit()
# ── tmdb id + season number -> season-level metadata json ────────────────
#
# A show's own overview (tmdb_meta above) is one static field an operator
# found does not necessarily describe every season alike (mediacenter.md
# §5.4) — this is TMDB's per-season `overview`/`air_date`/`poster_path`,
# fetched and cached independently, on the same staleness schedule.
async def get_season_meta(self, tmdb_id: str, season: int) -> dict | None:
async with self._db.execute(
"SELECT json, fetched_at FROM season_meta WHERE tmdb_id = ? AND season = ?",
(tmdb_id, season),
) as cur:
row = await cur.fetchone()
if not row:
return None
raw_json, fetched_at = row
if time.time() - fetched_at > TMDB_META_TTL_SECS:
return None
return json.loads(raw_json)
async def set_season_meta(self, tmdb_id: str, season: int, meta: dict) -> None:
await self._db.execute(
"INSERT OR REPLACE INTO season_meta (tmdb_id, season, json, fetched_at) "
"VALUES (?, ?, ?, ?)",
(tmdb_id, season, json.dumps(meta), time.time()),
)
await self._db.commit()
# ── file -> musicbrainz release id mapping (Music app) ───────────────────
async def get_file_mbid(self, file_id: str) -> str | None:
async with self._db.execute(
"SELECT mbid FROM file_mbid WHERE file_id = ?", (file_id,),
) as cur:
row = await cur.fetchone()
return row[0] if row else None
async def set_file_mbid(self, file_id: str, mbid: str) -> None:
await self._db.execute(
"INSERT OR REPLACE INTO file_mbid (file_id, mbid) VALUES (?, ?)",
(file_id, mbid),
)
await self._db.commit()
# ── musicbrainz release id -> metadata json (Music app) ───────────────────
async def get_mbid_meta(self, mbid: str) -> dict | None:
"""Returns None on a miss or on an entry older than MUSICBRAINZ_META_TTL_SECS."""
async with self._db.execute(
"SELECT json, fetched_at FROM mbid_meta WHERE mbid = ?", (mbid,),
) as cur:
row = await cur.fetchone()
if not row:
return None
raw_json, fetched_at = row
if time.time() - fetched_at > MUSICBRAINZ_META_TTL_SECS:
return None
return json.loads(raw_json)
async def set_mbid_meta(self, mbid: str, meta: dict) -> None:
await self._db.execute(
"INSERT OR REPLACE INTO mbid_meta (mbid, json, fetched_at) VALUES (?, ?, ?)",
(mbid, json.dumps(meta), time.time()),
)
await self._db.commit()
# ── thumbnails ────────────────────────────────────────────────────────────
async def get_thumb(self, thumb_hash: str) -> bytes | None:
async with self._db.execute(
"SELECT jpeg FROM thumbs WHERE thumb_hash = ?", (thumb_hash,),
) as cur:
row = await cur.fetchone()
return bytes(row[0]) if row else None
async def get_thumb_hash_by_file_id(self, file_id: str) -> str | None:
"""
A TMDB poster/backdrop is stored under a synthetic file_id
(`tmdb:{poster_path}`, stable across requests for the same image) —
this is how `_fetch_and_cache_poster` recognizes "already fetched"
without knowing the content hash up front (that's only known once
the bytes are downloaded).
"""
async with self._db.execute(
"SELECT thumb_hash FROM thumbs WHERE file_id = ?", (file_id,),
) as cur:
row = await cur.fetchone()
return row[0] if row else None
async def put_thumb(self, thumb_hash: str, file_id: str, jpeg: bytes) -> None:
await self._db.execute(
"INSERT OR REPLACE INTO thumbs (thumb_hash, file_id, jpeg) VALUES (?, ?, ?)",
(thumb_hash, file_id, jpeg),
)
await self._db.commit()
# ── pruning ───────────────────────────────────────────────────────────────
async def prune_file(self, file_id: str) -> None:
"""
Called when a file leaves the index (deletion, unshared root). Removes
its thumbnail and its file->tmdb/file->mbid mappings. `tmdb_meta`/
`mbid_meta` rows are left alone — they're keyed by tmdb_id/mbid, not
file_id, and other files (other episodes of the same show, other
tracks of the same release) may still reference the same entry.
"""
await self._db.execute("DELETE FROM thumbs WHERE file_id = ?", (file_id,))
await self._db.execute("DELETE FROM file_tmdb WHERE file_id = ?", (file_id,))
await self._db.execute("DELETE FROM file_mbid WHERE file_id = ?", (file_id,))
await self._db.commit()
|