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
