The scan loaders no longer eager-load `Rom.files` (#3425 + follow-ups), so
the hash-based metadata lookups can't rely on `rom.files` being populated —
`hasheous`/`ss` `lookup_rom` read `RomFile.is_top_level`, which dereferences
`RomFile.rom.full_path` and would raise `DetachedInstanceError` once the
session closed.
Add `DBRomsHandler.rom_files_for_rom_id`, which loads a ROM's files on demand
with the `RomFile.rom` backref eager-loaded (`load_only(fs_path, fs_name)`).
The scan path uses it as a fallback only when the filesystem walk yielded no
files (e.g. an unchanged rescan), behind a per-ROM `functools.cache` helper so
the playmatch/hasheous/ss lookups share a single DB fetch.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Restore the "platform only" contract of `get_roms_by_fs_name` (per its
docstring) by dropping the `selectinload(Rom.files)` + `joinedload`. That
load only existed for `scan_rom`'s rare `fs_rom["files"] or rom.files`
fallback, but it forced files (and a per-file join back to roms) for every
ROM in a scan batch — expensive on large platforms, and only used when the
filesystem scan yielded no files.
Instead, fetch the persisted files on demand: `scan_rom` now resolves match
files via a small helper that returns the filesystem-scanned files, falling
back to `db_rom_handler.get_rom_files_by_rom_id(rom.id)` only when there are
none. The new getter eager-loads the `RomFile.rom` backref so `is_top_level`
keeps working on the detached results (the rare path was already latently
broken on master, which loaded files without the backref).
https://claude.ai/code/session_01PSXKmejPRzdxLFMN6P2QQ4
Add a shared `multi_file_rom` fixture (a game folder with multiple
RomFile rows) and an endpoint-level test that downloads it via
`GET /api/roms/{id}/content/{file_name}`. This exercises the multi-file
download path end-to-end, which builds each mod_zip manifest entry from
`file.rom.full_path` after the handler session has closed — the exact
path that 500'd with `DetachedInstanceError` before the backref fix.
The download endpoint had no test coverage for multi-file ROMs (the
`rom` fixture has no RomFile rows), which is why the regression slipped
through. Reuse the new fixture in the handler-level regression test too.
https://claude.ai/code/session_01PSXKmejPRzdxLFMN6P2QQ4
PR #3425 dropped `lazy="joined"` from `RomFile.rom` and removed the
`joinedload(RomFile.rom)` from the ROM loaders to speed up the gallery
query. That left the `RomFile.rom` backref unpopulated. Single-file
downloads only read `RomFile.full_path` (built from `file_path`/
`file_name`), so they kept working, but multi-file (game folder)
downloads call `file_name_for_download()` / `is_top_level`, which read
`self.rom.full_path`. With no eager-loaded backref, that triggered a
lazy load on a detached instance once the handler session closed,
raising `DetachedInstanceError` and returning a 500.
Rather than reverting the loader changes (and the gallery gains), wire
the `RomFile.rom` backref up in Python from the parent ROM we already
hold in memory, via `set_committed_value`. This is zero extra DB cost
and only runs on the detail/download paths (`with_details` and
`get_roms_by_fs_name`); the optimized `filter_roms` gallery query is
untouched.
https://claude.ai/code/session_01PSXKmejPRzdxLFMN6P2QQ4
Collapse the two parallel id lists and their mirrored chunked-update
loops into a `flips: dict[bool, list[int]]` keyed by desired state, and
drop unused rom assignments in the related tests.
Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>
The scan was spending excessive time on large platforms even when all ROMs
were already scanned. Root causes: per-ROM UPDATE queries for skipped ROMs
(10k individual writes), missing composite index on (platform_id, fs_name)
causing full table scans, NOT IN clauses with 10k+ values in
mark_missing_roms(), and redundant filesystem reads.
Changes:
- Add bulk_mark_present() for batch-updating skipped ROMs in one query
- Move skip detection from _identify_rom to the batch loop so skipped ROMs
never enter the async scan pipeline, and report progress for them
- Add composite index idx_roms_platform_id_fs_name via migration 0077
- Rewrite mark_missing_roms() with flip-based approach: mark all missing,
then un-mark present ones in chunks of 1000
- Cache filesystem reads in scan_platforms() to avoid double directory
traversal (precounting + scanning)
Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>
- Add migration 0071 to fix sibling_roms view: add guard against empty string matching for fs_name_no_tags
- Fix group_by_meta_id in filter_roms: use func.nullif to treat empty fs_name_no_tags as NULL in grouping key
- Add group_by_meta_id support to get_roms_scalar
- Add tests for sibling matching behavior with empty/non-empty fs_name_no_tags
Co-authored-by: gantoine <3247106+gantoine@users.noreply.github.com>
When retrieving the related screenshot for a `Save` or `State`, we were
retrieving a very heavy representation of the associated `Rom` object,
only to iterate through its screenshots to find the one we needed.
This change modifies the `Save` and `State` models to directly query the
`Screenshot` model, which is much faster and more efficient. The
`DBScreenshotsHandler` has been updated to include a new `filter` method
that will simplify building queries using SQLAlchemy, something we can
extend to other handlers in the future.
Fixes#1925.
