diff options
Diffstat (limited to 'packages/meshbay-node/src')
| -rw-r--r-- | packages/meshbay-node/src/meshbay_node/media_cache.py | 123 |
1 files changed, 118 insertions, 5 deletions
diff --git a/packages/meshbay-node/src/meshbay_node/media_cache.py b/packages/meshbay-node/src/meshbay_node/media_cache.py index 8233270..2898dea 100644 --- a/packages/meshbay-node/src/meshbay_node/media_cache.py +++ b/packages/meshbay-node/src/meshbay_node/media_cache.py @@ -52,9 +52,18 @@ CREATE TABLE IF NOT EXISTS tmdb_meta ( CREATE TABLE IF NOT EXISTS thumbs ( thumb_hash TEXT PRIMARY KEY, file_id TEXT NOT NULL, - jpeg BLOB NOT NULL + jpeg BLOB NOT NULL, + -- Last time these bytes were served or written. The only thing that makes + -- eviction possible: without it the cache had no notion of "least useful" + -- and so no way to have a ceiling at all. + used_at REAL NOT NULL DEFAULT 0 ); CREATE INDEX IF NOT EXISTS idx_thumbs_file ON thumbs(file_id); +-- idx_thumbs_used is NOT here: on a database that predates `used_at`, this +-- script runs before the ALTER TABLE that adds the column, and CREATE INDEX on +-- a column that does not exist yet fails -- which would have been every +-- existing node refusing to open its cache on the first start after upgrading. +-- It is created in _migrate(), after the column is guaranteed to be there. CREATE TABLE IF NOT EXISTS season_meta ( tmdb_id TEXT NOT NULL, season INTEGER NOT NULL, @@ -115,6 +124,21 @@ TMDB_META_TTL_SECS = 30 * 86400 MUSICBRAINZ_META_TTL_SECS = 30 * 86400 +# The blob store's ceiling. +# +# `thumbs` holds every generated thumbnail, every TMDB poster and backdrop, +# every Cover Art Archive image and every cached audio transcode. Rows were only +# ever removed when their source file left every group's index, so a library +# that merely *changes* over years — films watched once, albums added and +# removed, posters re-fetched after a rename — grew this database without any +# bound. Nothing here is precious: every row is keyed off a value the node can +# re-derive, which is what makes evicting the least recently used ones safe. +# +# 512 MB holds many thousands of posters and thumbnails; the audio transcodes +# are what actually consume it, at a few MB apiece. +MAX_THUMB_CACHE_BYTES = 512 * 1024 * 1024 + + class MediaCache: """Async SQLite cache for TMDB/MusicBrainz lookups and generated thumbnails/cover art.""" @@ -126,8 +150,35 @@ class MediaCache: 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._migrate() await self._db.commit() + async def _migrate(self) -> None: + """Add columns to databases that predate them. + + `CREATE TABLE IF NOT EXISTS` creates missing *tables* and never a + missing *column*, so a new column reaches a fresh test database and + never reaches a deployed node — the lesson `CLAUDE.md` records against + `create_all()`. Every existing node has a `thumbs` table without + `used_at`, and the eviction below reads it on every write. + """ + async with self._db.execute("PRAGMA table_info(thumbs)") as cur: + columns = {row[1] for row in await cur.fetchall()} + if "used_at" not in columns: + await self._db.execute( + "ALTER TABLE thumbs ADD COLUMN used_at REAL NOT NULL DEFAULT 0") + # Existing rows get "now" rather than 0: the alternative is that the + # first write after an upgrade evicts the entire cache at once, + # which is a correct-but-hostile reading of "least recently used" + # for rows whose real age nothing recorded. + await self._db.execute("UPDATE thumbs SET used_at = ?", (time.time(),)) + log.info("media_cache: added thumbs.used_at and seeded it") + # Unconditional, and after the column is certain to exist: this is also + # where a brand-new database gets the index, since _SCHEMA deliberately + # does not carry it. + await self._db.execute( + "CREATE INDEX IF NOT EXISTS idx_thumbs_used ON thumbs(used_at)") + async def close(self) -> None: if self._db: await self._db.close() @@ -301,7 +352,18 @@ class MediaCache: "SELECT jpeg FROM thumbs WHERE thumb_hash = ?", (thumb_hash,), ) as cur: row = await cur.fetchone() - return bytes(row[0]) if row else None + if row is None: + return None + await self._touch_thumb(thumb_hash) + return bytes(row[0]) + + async def _touch_thumb(self, thumb_hash: str) -> None: + """Record that these bytes were wanted, so eviction can tell what is + still in use from what was cached once and never looked at again.""" + await self._db.execute( + "UPDATE thumbs SET used_at = ? WHERE thumb_hash = ?", + (time.time(), thumb_hash)) + await self._db.commit() async def get_thumb_hash_by_file_id(self, file_id: str) -> str | None: """ @@ -315,14 +377,65 @@ class MediaCache: "SELECT thumb_hash FROM thumbs WHERE file_id = ?", (file_id,), ) as cur: row = await cur.fetchone() - return row[0] if row else None + if row is None: + return None + # A poster resolved through its synthetic id is in use just as much as + # one fetched by hash — this is the lookup `_fetch_and_cache_poster` + # makes on every visit to a grid, and missing it would let the images a + # busy library shows most often look like the coldest rows here. + await self._touch_thumb(row[0]) + return row[0] 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), + "INSERT OR REPLACE INTO thumbs (thumb_hash, file_id, jpeg, used_at) " + "VALUES (?, ?, ?, ?)", + (thumb_hash, file_id, jpeg, time.time()), ) await self._db.commit() + await self._evict_thumbs() + + async def thumb_bytes(self) -> int: + """Total size of the blob store, as SQLite reports it.""" + async with self._db.execute( + "SELECT COALESCE(SUM(LENGTH(jpeg)), 0) FROM thumbs") as cur: + return int((await cur.fetchone())[0]) + + async def _evict_thumbs(self, cap: int = MAX_THUMB_CACHE_BYTES) -> int: + """Drop least-recently-used rows until the store is back under `cap`. + + Run on write rather than on a timer: a cache only grows when something + is written to it, and a timer is one more thing to own and to get wrong. + Writes are rare — one per new thumbnail, poster or transcode. + + The row just written is never the one evicted: it carries the newest + `used_at` by construction. A single blob larger than the whole cap would + otherwise evict everything and then itself, so the loop stops when only + it is left rather than emptying the table for nothing. + + Note the database file does not shrink; SQLite reuses the freed pages. + The point is the plateau, not the file size. + """ + total = await self.thumb_bytes() + if total <= cap: + return 0 + removed = 0 + async with self._db.execute( + "SELECT thumb_hash, LENGTH(jpeg) FROM thumbs ORDER BY used_at ASC" + ) as cur: + rows = await cur.fetchall() + for thumb_hash, size in rows: + if total <= cap or len(rows) - removed <= 1: + break + await self._db.execute( + "DELETE FROM thumbs WHERE thumb_hash = ?", (thumb_hash,)) + total -= int(size) + removed += 1 + if removed: + await self._db.commit() + log.info("media_cache: evicted %d cached image(s), now %.1f MB", + removed, total / 1048576) + return removed # ── photo technical/EXIF fields (Photos app) ───────────────────────────── |