RobLoach / RobLoach/raylib-libretro
Games DB: incremental INSERT/DELETE instead of full-table rewrite on every change
Nobody has claimed this yet.
- Dominant language
- C
- Stars
- 38
- Forks
- 8
- PR merge metrics
- No merged PRs in 30d
Description
Surfaced by a games-DB design review (adversarially verified against source).
Problem. LibretroGamesSaveToDB() (include/raylib-libretro-games.h) runs DELETE FROM games; then re-INSERTs the entire in-memory list whenever GAMES.dirty is set — and dirty is set for even a single new or pruned file. At 10k–100k games, adding one ROM rewrites every row and rebuilds the relpath PRIMARY KEY b-tree from empty (one WAL frame per row). That is O(total) disk work for an O(1) logical change.
Approach. In LibretroGamesFinishScan(), before the kept-vector rebuild destroys the partition, do incremental DML in one transaction:
- the cache/new-entry partition is still intact there — survivors are
[0, oldCount), new finds are[oldCount, size); - prepared
DELETE FROM games WHERE relpath=?for eachseen==falseentry in the prune loop; - prepared
INSERT INTO games(...) VALUES(?,?,?)for each new entry (a plain INSERT is safe — new relpaths are provably absent because they are only pushed afterLibretroGamesFindCached()misses).
Keep the full DELETE+re-INSERT (SaveToDB) only for the content-dir-changed full-rebuild branch (where the old in-memory list was freed). Track adds/prunes with counters rather than a single bool.
Trade-off. More code than the current wholesale rewrite, which cuts against the "keep it simple" goal — only worth doing if very large libraries (50k+) are a target. Severity: performance-at-scale.
Contributor guide
No contributing guide indexed for this repository
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
Start in include/raylib-libretro-games.h, reading LibretroGamesFinishScan() and LibretroGamesSaveToDB(), especially the prune loop and cache/new-entry partition. Implement the requested transactional incremental DELETE and INSERT operations, retain the full rewrite only for the content-dir-changed branch, and track adds and prunes with counters.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- c, sql
- Domain
- databases, performance
- Issue type
- Refactor
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Quiet
- Clarity
- Mostly clear
- Newbie friendliness
- 55/100