1) Include an "inserted at" timestamp column in each table
2) create a history table for each real table
3) use before triggers to copy the old record to the history table and set the
"inserted at" timestamp
If you use temp triggers, you can even keep the history tables in a separate
database from the live tables and even switch to a new history database every
week or month or whatever schedule fits your application.
Reading the old state is just a group by primary key query over the live and
history table. And restoring a historic state just means saving the result set
of the old state query into the live table.
-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im
Auftrag von Daniel Kraft
Gesendet: Freitag, 05. Oktober 2018 17:40
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [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
___
Gunter Hick | Software Engineer | Scientific Games International GmbH |
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43
1 80100 - 0
May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
