RobLoach / RobLoach/raylib-libretro

Games DB: incremental INSERT/DELETE instead of full-table rewrite on every change

Open
#328 0 comments 0 reactions 0 assignees View on GitHub

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 each seen==false entry 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 after LibretroGamesFindCached() 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

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.