db: upgrade a v12.0 or v12.1 database in place (branch v12.2-upgrade) #239

New issue

Closed

opened 2026-10-09 17:40:28 +02:00 by Claude · 1 comment

Claude commented

2026-10-09 17:40:28 +02:00

Collaborator

Branch v12.2-upgrade (4 commits on top of main at bacb90ec, pushed, not merged). It adds Database::upgrade() in crates/ephyra-db/src/upgrade.rs, called from open_or_create in crates/ephyra-server/src/serve.rs: a database upstream v12.0 or v12.1 wrote is brought to what upstream v12.2's migrations would leave.

Why it matters

The deployed database on alphard is at upstream v12.0 shape (newest history row 20260831100000_EnableLocalSimilarityProviders, 102 rows, ~57k items, ~44k UserData; MetadataCountryCode=US). Nothing on main upgrades it; it is served as it is.

What the branch does

Decides from __EFMigrationsHistory against the ids in the pinned baseline: Current / Applied / Newer / Older. Newer and Older are left untouched with a warning.
Steps, in upstream's stage order: MakeOwnerIdIndexesPartial, RepairAlternateVersionLinks, StripEmbeddedLinkedChildren, HarmonizeConflictingUserData, MigrateRatingLevels (0915). FixNullEncoderPreset is left to upstream and not stamped.
One BEGIN IMMEDIATE transaction; a VACUUM INTO copy to <db dir>/SQLiteBackups/<yyyyMMddHHmmss>_<name> first, and no upgrade if the copy fails.
18 tests in crates/ephyra-db/tests/upgrade.rs, expectations hand-derived from the C#; full suite green on the branch (3612).

Blocker before it may run on a real database

The rating step nulls inherited scores and the port never refills them. Upstream's routine (ported faithfully) sets InheritedParentalRatingValue/SubValue to NULL on every item with no OfficialRating of its own — every season and episode that carried its series' score. Upstream refills on the item's next save/refresh (OnMetadataChanged, via OfficialRatingForComparison walking DisplayParent). The port has no inheritance: scan/row.rs and items/writes.rs score the row's own rating only. A NULL score passes the max-rating filter on both servers, so after the upgrade the episodes of an over-limit series would be visible to a capped account unless block-unrated covers the type. See the parental-rating inheritance issue; one of the two has to land first, or the step has to preserve/recompute inherited scores.

Review state

Commits 969e006f, 9d87da38 had one independent review: no corruption or atomicity problem; findings (v12.0 skipped, no backup, tie-break, country) were addressed in 54c9eb90, 9248fdcf.
54c9eb90 and 9248fdcf are unreviewed. They add the two v12.1 routines (alternate-version graph resolved in Rust, then two set UPDATEs; the strip statement is upstream's SQL verbatim), the backup, and the tie-break.

Rests on inference, not measurement

No running upstream has opened an upgraded database.
Harmonize's final tie-break (Played ASC, rowid ASC) and RepairAlternateVersionLinks' "earlier-filed link wins" both rest on SQLite's query plan for upstream's unordered queries (IX_UserData_ItemId_UserId_Played; table scan), not on anything the C# guarantees.
LastPlayedDate is ordered as text (holds for EF's and the port's format).
The backup is taken just before the transaction, not inside it.
For ~34 ambiguous non-US rating strings (M, A, 18+, R18, …) the score depends on the order rating systems are consulted; upstream's is MSBuild's glob order over Localization/Ratings/*.json, the port's is file-name order. Not established that they agree.
The rating step is right only while the server country is US (METADATA_COUNTRY_CODE).
Disk: the copy is the size of the database and the WAL grows by about as much (the strip step rewrites every row carrying the keys).

To finish

Resolve the blocker above.
One review of 54c9eb90/9248fdcf.
Run it on a copy of the household database (~/repos/worktrees/ephyra-local, see the memory note on driving it locally) and diff before/after; ideally let upstream v12.2 open the result and confirm only FixNullEncoderPreset is pending.
Decide whether Older/Newer should stay a warning or refuse the start, and record the decision (DECISIONS.md says there is no migration runner).

Branch `v12.2-upgrade` (4 commits on top of `main` at `bacb90ec`, pushed, **not merged**). It adds `Database::upgrade()` in `crates/ephyra-db/src/upgrade.rs`, called from `open_or_create` in `crates/ephyra-server/src/serve.rs`: a database upstream v12.0 or v12.1 wrote is brought to what upstream v12.2's migrations would leave. ## Why it matters The deployed database on alphard is at upstream **v12.0** shape (newest history row `20260831100000_EnableLocalSimilarityProviders`, 102 rows, ~57k items, ~44k `UserData`; `MetadataCountryCode=US`). Nothing on `main` upgrades it; it is served as it is. ## What the branch does - Decides from `__EFMigrationsHistory` against the ids in the pinned baseline: `Current` / `Applied` / `Newer` / `Older`. `Newer` and `Older` are left untouched with a warning. - Steps, in upstream's stage order: `MakeOwnerIdIndexesPartial`, `RepairAlternateVersionLinks`, `StripEmbeddedLinkedChildren`, `HarmonizeConflictingUserData`, `MigrateRatingLevels` (0915). `FixNullEncoderPreset` is left to upstream and not stamped. - One `BEGIN IMMEDIATE` transaction; a `VACUUM INTO` copy to `<db dir>/SQLiteBackups/<yyyyMMddHHmmss>_<name>` first, and no upgrade if the copy fails. - 18 tests in `crates/ephyra-db/tests/upgrade.rs`, expectations hand-derived from the C#; full suite green on the branch (3612). ## Blocker before it may run on a real database **The rating step nulls inherited scores and the port never refills them.** Upstream's routine (ported faithfully) sets `InheritedParentalRatingValue`/`SubValue` to NULL on every item with no `OfficialRating` of its own — every season and episode that carried its series' score. Upstream refills on the item's next save/refresh (`OnMetadataChanged`, via `OfficialRatingForComparison` walking `DisplayParent`). The port has no inheritance: `scan/row.rs` and `items/writes.rs` score the row's own rating only. A NULL score passes the max-rating filter on both servers, so after the upgrade the episodes of an over-limit series would be visible to a capped account unless block-unrated covers the type. See the parental-rating inheritance issue; one of the two has to land first, or the step has to preserve/recompute inherited scores. ## Review state - Commits `969e006f`, `9d87da38` had one independent review: no corruption or atomicity problem; findings (v12.0 skipped, no backup, tie-break, country) were addressed in `54c9eb90`, `9248fdcf`. - **`54c9eb90` and `9248fdcf` are unreviewed.** They add the two v12.1 routines (alternate-version graph resolved in Rust, then two set `UPDATE`s; the strip statement is upstream's SQL verbatim), the backup, and the tie-break. ## Rests on inference, not measurement - No running upstream has opened an upgraded database. - Harmonize's final tie-break (`Played ASC, rowid ASC`) and RepairAlternateVersionLinks' "earlier-filed link wins" both rest on SQLite's query plan for upstream's unordered queries (`IX_UserData_ItemId_UserId_Played`; table scan), not on anything the C# guarantees. - `LastPlayedDate` is ordered as text (holds for EF's and the port's format). - The backup is taken just before the transaction, not inside it. - For ~34 ambiguous non-US rating strings (`M`, `A`, `18+`, `R18`, …) the score depends on the order rating systems are consulted; upstream's is MSBuild's glob order over `Localization/Ratings/*.json`, the port's is file-name order. Not established that they agree. - The rating step is right only while the server country is US (`METADATA_COUNTRY_CODE`). - Disk: the copy is the size of the database and the WAL grows by about as much (the strip step rewrites every row carrying the keys). ## To finish 1. Resolve the blocker above. 2. One review of `54c9eb90`/`9248fdcf`. 3. Run it on a copy of the household database (`~/repos/worktrees/ephyra-local`, see the memory note on driving it locally) and diff before/after; ideally let upstream v12.2 open the result and confirm only `FixNullEncoderPreset` is pending. 4. Decide whether `Older`/`Newer` should stay a warning or refuse the start, and record the decision (DECISIONS.md says there is no migration runner).

Claude commented

2026-10-11 11:15:18 +02:00

Author

Collaborator

Landed (c657ecba … 41058006). The blocker is resolved by #240: the rating step now refills, in the upgrade's own transaction, the score every row inherits (fa339d99), so rows end at what upstream's next save writes rather than at the nulls its routine leaves; three tests fail without it, one proving the cleared scores roll back with the rest.

Measured: upstream v12.2's own migrations and the port's, run on the same household copy and on a synthetic stress copy (link loop with a tail, exact-tie UserData pairs, 28 odd rating strings), gave identical rows before the refill was added; both tie-breaks resolved as assumed. Upstream opening the upgraded copy had only FixNullEncoderPreset pending. On the household copy (333 MB, 57,021 items): 2.2 s including the copy, row counts unchanged, backup 297 MB, WAL peak 16 MB; no score lost, and 8,117 seasons and episodes that were NULL now carry their series' score. Rating-system order agrees with upstream's manifest order and is pinned by a test (9e48e721).

54c9eb90/9248fdcf reviewed line by line against the five C# routines: no divergence. Recorded as D54: Older/Newer are served untouched with a warning.

Still as stated in the issue: the backup is taken just before the transaction, not inside it (VACUUM INTO cannot run in one); the rating step scores under the port's own country constant. After the refill, upstream's full clean start on the result was not re-observed (the run timed out in its optimise step); the pre-refill run started cleanly.

Landed (c657ecba … 41058006). The blocker is resolved by #240: the rating step now refills, in the upgrade's own transaction, the score every row inherits (fa339d99), so rows end at what upstream's next save writes rather than at the nulls its routine leaves; three tests fail without it, one proving the cleared scores roll back with the rest. Measured: upstream v12.2's own migrations and the port's, run on the same household copy and on a synthetic stress copy (link loop with a tail, exact-tie UserData pairs, 28 odd rating strings), gave identical rows before the refill was added; both tie-breaks resolved as assumed. Upstream opening the upgraded copy had only `FixNullEncoderPreset` pending. On the household copy (333 MB, 57,021 items): 2.2 s including the copy, row counts unchanged, backup 297 MB, WAL peak 16 MB; no score lost, and 8,117 seasons and episodes that were NULL now carry their series' score. Rating-system order agrees with upstream's manifest order and is pinned by a test (9e48e721). `54c9eb90`/`9248fdcf` reviewed line by line against the five C# routines: no divergence. Recorded as D54: `Older`/`Newer` are served untouched with a warning. Still as stated in the issue: the backup is taken just before the transaction, not inside it (`VACUUM INTO` cannot run in one); the rating step scores under the port's own country constant. After the refill, upstream's full clean start on the result was not re-observed (the run timed out in its optimise step); the pre-refill run started cleanly.

Claude closed this issue

2026-10-11 11:15:18 +02:00