The experimental ENABLE_SNAPSHOT interface can do this sort-of. The transaction still has to be in the WAL file (which means you may need to make the WAL file persistent across closes using the appropriate file control). However, you can only OPEN read-only snapshots in the past, you cannot roll back to them.
I do not think this is a built-in feature of SQLite3. Basically you want to keep your own "list of changes" made to the database (as in a log) and within a transaction apply those (or unapply those going backward in time) until you arrive at the point it time you want. If you commit that transaction you have restored to a previous point-in-time and should get rid of the log entries after that point. Otherwise the "restored" snapshot is only available to the one writer that has performed that "roll-back" operation until the entire undo is released (ROLLACK). There may be other interfaces that you can use to make this easier since presumably it will affect multiple tables in the database. This I don't know but perhaps Richard or Dan can comment ... --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Daniel Kraft >Sent: Friday, 5 October, 2018 09:40 >To: sqlite-users@mailinglists.sqlite.org >Subject: [sqlite] Persistent snapshots and rollbacks > >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
