The sessions extension: https://www.sqlite.org/sessionintro.html
2018-10-05 17:39 GMT+02:00, Daniel Kraft <d...@domob.eu>: > 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 > > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
