Hi! I need the ability to make multiple changes / commits to my SQLite database but keep snapshots of previous states and potentially roll back to those states later on. All of that needs to be persistent, i.e. survive closing the database and restarting the process. After some time, I can get rid of old snapshots (my process determines by itself when and which snapshots can get discarded, it is not based on some fixed TTL or something like that).
Is it possible to do all that with SQLite? From reading the docs, it seems to me that (persistent) WAL mode basically does *exactly that* internally: Changes are recorded in the logs so that previous versions are retained. Rollbacks would be possible by "simply" discarding the WAL entries after the desired snapshot. And discarding of very old snapshots corresponds to checkpointing. However, I'm not sure if all of that functionality is (officially) exposed to me as a user. There are in particular two points where I think that my requirements differ from the functionality that WAL mode exposes: 1) Handles to snapshots can be obtained and stored, but they are read-only. It seems to be not possible to tell SQLite to restore the WAL to a previous version and then continue modifying from that version. (Which basically means truncating the WAL file at a certain point.) 2) From what I have seen, checkpointing can only be triggered for the full WAL (or whatever is possible with existing readers) and not selectively up to a desired point. Of course I could work around that by creating a reader at the point I want to keep. But then I wonder if it is a problem if the WAL can never be *fully* checkpointed (as in my requirement). Would that mean that it keeps on growing forever, or is checkpointing able to remove parts from the beginning of the WAL? Is my understanding here correct? And is there some way in which I could achieve my requirements using WAL mode (or somehow else)? Thank you very much! Yours, Daniel -- https://www.domob.eu/ OpenPGP: 1142 850E 6DFF 65BA 63D6 88A8 B249 2AC4 A733 0737 Namecoin: id/domob -> https://nameid.org/?name=domob -- 3.6.0: Bar-Pri-Ran-Rog-Sam-Val-Wiz To go: Arc-Cav-Hea-Kni-Mon-Tou
signature.asc
Description: OpenPGP digital signature
_______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
