Savepoints (<https://www.sqlite.org/lang_savepoint.html>)?
On Fri, Oct 5, 2018 at 11:40 AM Daniel Kraft <[email protected]> wrote:
>
> 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
> [email protected]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
