diff options
Diffstat (limited to 'packages')
| -rw-r--r-- | packages/meshbay-node/src/meshbay_node/media_cache.py | 123 | ||||
| -rw-r--r-- | packages/meshbay-node/tests/test_media_cache_eviction.py | 152 |
2 files changed, 270 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) ───────────────────────────── diff --git a/packages/meshbay-node/tests/test_media_cache_eviction.py b/packages/meshbay-node/tests/test_media_cache_eviction.py new file mode 100644 index 0000000..587063a --- /dev/null +++ b/packages/meshbay-node/tests/test_media_cache_eviction.py @@ -0,0 +1,152 @@ +""" +The media cache has a ceiling, and reaching it drops the least useful rows. + +`thumbs` holds every generated thumbnail, every TMDB poster and backdrop, every +Cover Art Archive image and every cached audio transcode. Rows were removed only +when their source file left every group's index (`prune_file`), so a library +that merely *changes* over years grew this database with nothing to bound it. +Nothing in it is precious — every row is keyed off a value the node can +re-derive — which is what makes eviction the right answer rather than a bigger +disk. + +The migration is the part worth pinning hardest: `CREATE TABLE IF NOT EXISTS` +adds missing tables and never missing columns, so `used_at` would have reached a +fresh test database and never a deployed node — `CLAUDE.md`'s standing lesson +about `create_all()`. Every existing node has a `thumbs` table without it. +""" + +import sqlite3 + +import pytest + +from meshbay_node.media_cache import MediaCache + + +def _blob(n: int) -> bytes: + return b"x" * n + + +@pytest.mark.asyncio +async def test_the_cache_stays_under_its_cap(tmp_path): + cache = MediaCache(db_path=tmp_path / "media_cache.db") + await cache.open() + try: + cap = 40_000 + for i in range(20): + await cache.put_thumb(f"hash{i:03d}", f"file{i:03d}", _blob(5_000)) + await cache._evict_thumbs(cap=cap) + assert await cache.thumb_bytes() <= cap + finally: + await cache.close() + + +@pytest.mark.asyncio +async def test_what_is_evicted_is_what_nobody_asked_for(tmp_path): + """ + Least *recently used*, not least recently written: a poster fetched a year + ago and shown on every visit to a grid must outlive one cached last week and + never looked at again. + """ + cache = MediaCache(db_path=tmp_path / "media_cache.db") + await cache.open() + try: + for i in range(8): + await cache.put_thumb(f"hash{i}", f"file{i}", _blob(5_000)) + # The oldest row by write time, read now — so it is the newest by use. + assert await cache.get_thumb("hash0") is not None + await cache._evict_thumbs(cap=20_000) + assert await cache.get_thumb("hash0") is not None, ( + "evicted a row that had just been served") + assert await cache.get_thumb("hash1") is None, ( + "kept a row nothing had asked for since it was written") + finally: + await cache.close() + + +@pytest.mark.asyncio +async def test_a_lookup_by_synthetic_id_counts_as_use(tmp_path): + """ + `_fetch_and_cache_poster` finds an already-cached poster through + `get_thumb_hash_by_file_id`, which is the lookup a poster grid makes on + every visit. If that did not count as use, the images shown most often + would look like the coldest rows in the table. + """ + cache = MediaCache(db_path=tmp_path / "media_cache.db") + await cache.open() + try: + for i in range(8): + await cache.put_thumb(f"hash{i}", f"tmdb:/poster{i}.jpg", _blob(5_000)) + assert await cache.get_thumb_hash_by_file_id("tmdb:/poster0.jpg") == "hash0" + await cache._evict_thumbs(cap=20_000) + assert await cache.get_thumb("hash0") is not None + finally: + await cache.close() + + +@pytest.mark.asyncio +async def test_one_oversized_blob_does_not_empty_the_table(tmp_path): + """ + A single audio transcode larger than the whole cap would otherwise evict + everything and then itself, leaving an empty cache and the same problem. + """ + cache = MediaCache(db_path=tmp_path / "media_cache.db") + await cache.open() + try: + await cache.put_thumb("big", "file-big", _blob(50_000)) + removed = await cache._evict_thumbs(cap=10_000) + assert await cache.get_thumb("big") is not None + assert removed == 0 + finally: + await cache.close() + + +@pytest.mark.asyncio +async def test_an_existing_database_gains_the_column(tmp_path): + """ + The migration, against a database shaped exactly like a deployed node's: + `thumbs` with no `used_at`, holding a row that must survive. + """ + db_path = tmp_path / "media_cache.db" + con = sqlite3.connect(db_path) + con.executescript(""" + CREATE TABLE thumbs ( + thumb_hash TEXT PRIMARY KEY, + file_id TEXT NOT NULL, + jpeg BLOB NOT NULL + ); + CREATE INDEX idx_thumbs_file ON thumbs(file_id); + """) + con.execute("INSERT INTO thumbs VALUES (?, ?, ?)", ("old", "file-old", b"abc")) + con.commit() + con.close() + + cache = MediaCache(db_path=db_path) + await cache.open() + try: + assert await cache.get_thumb("old") == b"abc", "the migration lost a row" + # Seeded with "now", not 0: an upgrade must not make every existing row + # look infinitely old and evict the whole cache on the next write. + con = sqlite3.connect(db_path) + used_at = con.execute( + "SELECT used_at FROM thumbs WHERE thumb_hash = 'old'").fetchone()[0] + con.close() + assert used_at > 0, "existing rows were left at 0 and are first to go" + finally: + await cache.close() + + +@pytest.mark.asyncio +async def test_opening_twice_is_harmless(tmp_path): + """The migration must be idempotent — a node opens this on every start.""" + db_path = tmp_path / "media_cache.db" + for _ in range(3): + cache = MediaCache(db_path=db_path) + await cache.open() + await cache.put_thumb("h", "f", b"xyz") + await cache.close() + cache = MediaCache(db_path=db_path) + await cache.open() + try: + assert await cache.get_thumb("h") == b"xyz" + finally: + await cache.close() |