Reading Lyrion's SQLite databases

All DB access lives in services/database.py. These databases belong to Lyrion, not to this app — the app opens them read-only and never writes. (Web lyrics can't be persisted here for exactly this reason; they go to a cache or into file tags — see the lyrics skill.)

The connection model

Use the get_db_conn() context manager for every query — don't open sqlite inline in a route or elsewhere:

with get_db_conn() as conn:
    row = conn.execute("SELECT lyrics FROM tracks WHERE id = ?", (track_id,)).fetchone()

It opens library.db in read-only URI mode (file:...?mode=ro), ATTACHes persist.db (also RO) as the persist schema, sets row_factory = sqlite3.Row (access columns by name), applies read-tuning pragmas (mmap/cache/temp), wraps the work in BEGIN DEFERRED … COMMIT, and defines the play_counts TEMP view (see below). Two databases, one connection:

• library.db (default schema) — the music library: tracks, albums, contributor_track, genres.
• persist.db (referenced as persist.<table>) — persistent per-track state: persist.tracks_persistent (ratings, lastplayed, …), and the Alternative Play Count table alternativeplaycount when the plugin is installed.

The DB paths come from config (DB_PATH, DB_PERSIST_PATH). They are derived from LYRION_DATA_DIR, the directory holding Lyrion's own prefs/ and cache/ — {dir}/cache/library.db and {dir}/prefs/persist.db — which DB_DIR and DB_PERSIST_DIR override one at a time when either was relocated. Tests set the two overrides directly (see the testing skill), so they never depend on that layout.

Key tables and columns

• tracks — one row per track. id (the Lyrion track id used everywhere, e.g. lyrics lookup), url, urlmd5 (join key to the play counts), album (→ albums.id), audio (filter audio = 1 for real tracks), lyrics, year, coverid.
• albums — id, artwork (the coverid of the album's artwork track, the same id /cover/<coverid>.jpg serves).
• contributor_track — track↔artist links with a role: role 5 = ALBUMARTIST, role 1 = ARTIST (track artist). "Album artists" filter role = 5; the track_artists_* stats filter role IN (1, 5) — the union, shown in the UI as "All artists".
• persist.tracks_persistent — rating, playcount, lastplayed (unix seconds; bumped on skips too). Lyrion's own counters, and the fallback source of play_counts.

Play counts: play_counts, not the plugin table

Real listening data comes from the Alternative Play Count plugin's alternativeplaycount table, which keeps plays separate from skips:

• playcount / lastplayed — genuine plays. "Played" means playcount > 0.
• skipcount / lastskipped — skips.

The plugin is recommended, not required, so no query names that table. get_db_conn defines a TEMP view play_counts (urlmd5, playcount, skipcount, lastplayed) over whichever source _use_apc picks — it looks for the table in both schemas, since the plugin keeps it in persist.db so a library rescan cannot drop it, and the view's unqualified name then resolves to whichever holds it — the plugin's table, else persist.tracks_persistent with skipcount forced to 0 — and every query joins the view, aliased apc. PLAY_COUNTS_SOURCE=lyrion forces the fallback even when the plugin is installed, for a library whose history predates it. A TEMP view is writable on a read-only connection: it lives in the connection's temp schema, never in Lyrion's files. Use the view for anything about what was played; add a column to both definitions rather than referencing a source table directly.

The play history and the velocity

The plugin also keeps a play-by-play history, on by default, in a file of its own: apc_external.db, beside persist.db (APC_HISTORY_PATH). Its play_history table has one row per genuine play (urlmd5, played in unix seconds, client_id, rating, remote), streams included. _history_velocity opens it read-only on a connection of its own (an ATTACH cannot happen inside get_db_conn's transaction) and counts the plays in the velocity windows (velocity_30d, velocity_1year). Without that file, or with PLAY_COUNTS_SOURCE=lyrion, the velocity counts library tracks whose last genuine play falls in the window, through play_counts — a number of tracks, not of plays. Either way the windows start at local midnight, through the TZ environment. A history only starts when the plugin started recording it, so a window reaching further back undercounts.

On the fallback two things degrade, and the code says so where it matters: skips do not exist at all, and lastplayed is bumped on a skip too, so get_recent_album_covers can surface an album that was only skipped past. _compute_stats reports which source it used as apc_available — the plugin's table and not overridden — and the template drops the "Skips" row when it is false.

The stats cache (single-flight)

get_stats() returns library statistics cached for STATS_TTL (60s) behind _stats_lock. The lock makes the recompute single-flight: when the cache expires, simultaneous clients wait for one computation instead of each firing their own set of full-library aggregations. It returns a copy so callers can't mutate the cached dict. Keep both properties if you touch it — the stats are four full-library scans and the page polls them from every open client.

_compute_stats() runs four queries on the library (plus one on the play history, above) (albums+songs, album artists, track artists, misc) using CTEs to scan tracks JOIN play_counts once each, then derives percentages via the local pct() helper. Every count coalesces to 0 (row[...] or 0) so an empty library yields zeros, not None.

Adding or changing a statistic

1. Add the aggregation to the right query in _compute_stats (reuse the track_play CTE pattern; scan once, don't add a query per number).
2. Add the key to the stats dict (coalesce to 0); add a _pct via pct() if it's a proportion.
3. Surface it: the /stats.json route returns the whole dict, and the template renders named fields — a new stat shown on the page needs an i18n.py label (both fr and en) and a mention in docs/endpoints.md or docs/configuration.md if it's part of the documented surface (see the i18n and add-route skills).
4. Test it: build a small temp SQLite DB with the tables/rows you need and assert the computed number, with and without alternativeplaycount if the number depends on play counts (see tests/test_get_recent_album_covers.py, tests/test_without_alternativeplaycount.py, tests/test_stats_cache.py). Don't hit a real Lyrion DB. See the testing skill.

Rules

• Read-only, always. No INSERT/UPDATE/DELETE; the app must never write to Lyrion's databases. The one CREATE is the play_counts TEMP view, which lands in the connection's temp schema.
• Always go through get_db_conn(); parameterize queries (? placeholders), never string-format user input into SQL.
• Filter real tracks with audio = 1; join play data via urlmd5.
• Join play_counts (never alternativeplaycount directly) when you mean "actually played".