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
|
"""
MeshBay Node — TMDB metadata and thumbnail cache for the Videos group app.
Node-wide (not per-group, `data_dir/media_cache.db`), same rationale as
`tmdb_enabled`/`tmdb_api_token` living in `group_settings` under the
`group_id=""` sentinel (docs/mediacenter.md §5.5): TMDB is one operator's
budget and credential, and a thumbnail is the same bytes regardless of which
group happens to share the file.
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)
);
"""
# 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
class MediaCache:
"""Async SQLite cache for TMDB lookups and generated thumbnails."""
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()
# ── 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 mapping. `tmdb_meta` rows are left
alone — they're keyed by tmdb_id, not file_id, and other files (other
episodes of the same show) 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.commit()
|