Persist subtitle files and per-language attempts #186
No Assignees
Notifications
Due Date
No due date set.
Reference: yolo/arr#186
Reference in New Issue
Block a user
Add the migration and queries that let arr remember what subtitles exist and
what it has tried, per DESIGN.md §15 (added by #183).
Two shapes:
the owning
MediaFile, language, where it came from (embedded, extractedfrom an embedded track, fetched from a named provider, or machine
translated), the provider and candidate id where applicable, the translation
engine where applicable, forced and SDH flags, whether
alasssynced it andwhether that sync was rejected, and the path on disk for anything that is a
sidecar. Embedded tracks that were never extracted have no path — they are
recorded because they satisfy a language, not because there is a file.
attempt count, last attempt, and the reason the last attempt failed. This is
what the reconcile loop reads to decide whether a language is a gap, what
backs off a failing provider, and what the missing-subtitles queue renders.
Follow the existing migration conventions in
crates/arr-db/migrations, keepqueries
sqlxcompile-time-checked, and keep checksums stable — see the0021/0022pair and the fix ina3897dffor what that costs when it goeswrong.
Non-goals: any settings rows (that is #198), any budget accounting (#197).
Depends on: #183
Implemented in
crates/arr-db: migration0024_subtitles.sqladds two STRICTtables and a query module
crates/arr-db/src/subtitles.rson top of them.subtitle_filesis one row per subtitle arr knows about — owning media file,language, origin (
embedded,extracted,provider,translated), providerand candidate id, translation engine, forced and SDH flags, the two
alassflags, and the sidecar path.
CHECKconstraints tie each optional column to theorigin, so an embedded row has no path, a provider row names its provider, a
translated row names its engine, and a subtitle can never be both synced and
sync-rejected.
subtitle_attemptsis one row per (media file, wanted language)with state, attempt count, last attempt and last failure reason. Recording a
subtitle is idempotent on both keys the schema carries — the sidecar path, and
the language an embedded track satisfies — so a second probe or a re-import
converges instead of duplicating.
pending()is the loop's work list: everylanguage that is not satisfied, newest import first, least recently attempted
within an import. Both tables cascade from
media_files. Nine unit tests coverthe constraints, idempotency, backoff counting and the work-list ordering; the
offline query data in
.sqlxis regenerated and a build with noDATABASE_URLworks.
Branch:
subtitles/186-subs-db(based onblitz/subtitles). No PR opened.just cipassed through the shared gate — 476 tests, fmt, clippy, machete andthe web checks.
Findings
text formats (
subrip,ass,mov_text) from image formats (PGS, VobSub),and #189 already scopes teaching
arr-probeto report it. Right now that factwould live only in
media_files.probed, so anything asking "is there a textsource to translate from" has to re-read the probe JSON rather than the
subtitle rows. If #189 wants the rows to answer it, that is one nullable
column and one migration — worth deciding when #189 lands rather than after.
The unique index is
(media_file_id, language, forced, sdh) WHERE origin = 'embedded', so a file with two English SDH tracks records one row. Thatmatches §15 — the row exists because it satisfies a language — but #189 should
know it will not get one row per stream.
subtitle_attempts.statealready allowscapped. No budget accounting ishere (that is #197), but the state is in the
CHECKlist so #197 does not haveto rebuild the table to add it. SQLite cannot alter a
CHECKin place.