Integrate SQLite (or something like it)
Closed this issue · 5 comments
As a more concrete solution for #5, we should try to integrate SQLite or some related project (e.g. treelite) with libxayagame. Ideally, the library should take care of managing revisions / snapshots / DB transactions, so that games simply get a handle to the current state and modify it according to moves in SQL.
We should look into both treelite (how well it works and is maintained and how its performance compares to non-branched databases) as well as pure SQLite and its ability to snapshot databases / undo transactions.
It seems that SQLite supports snapshots when in WAL mode. This actually works exactly in the way that we need: It keeps a "base version" of the database and then newer changes as individual "commits" on top of it in the WAL. As soon as certain old commits are no longer needed, they can be "checkpointed" into the base version to compact the WAL. It also seems to be possible to make the WAL persistent, although that is not the default, and to make all snapshots from a persisted WAL available even after restarts.
In the context of a Xaya game, this model fits exactly on the need to keep "a few" old undo steps (in the WAL), while allowing to prune old, no-longer-needed revisions (checkpointing).
LiteTree seems to be very interesting as well (as mentioned already): We could simply create a branch for every snapshot we need (or work directly with commits) and then delete the branches again once no longer needed. But it seems that commits would still be retained forever, which unnecessarily increases the storage size (but see aergoio/litetree#16).
Posted about this to the sqlite-users mailing list. to get feedback from people who actually know SQLite.
It seems that savepoints are a feature of SQLite that is also pretty close to what we need. They work as uncommitted transactions (until an old savepoint is released), so that changes are visible only within the session, but that is fine for us (only one user anyway). However, they are unfortunately also not persistent, it seems, and can't be made to persist.
Perhaps the session extension with SQLite is our best bet here. It allows to record changes made to the database and then export those change sets as raw data. It also allows to apply and undo recoded changesets. This fits in pretty well with our undo-data mechanism.
The drawback is that presumably no changes to the database schema itself (like new tables) are recorded, but that should be a minor issue in practice. (Games using SQLite databases just need to set up their schema upfront and then promise to not modify it in the processing functions.) Also, sessions only support rows with a PRIMARY KEY, but I guess we can require that for games as well.