This suggestion refers to temporal DB. To those interested by this
approach, this tutorial could help (implementation coded in SQLite):
https://staff.info.unamur.be/dbm/Documents/Tutorials/SQLfast/SQLfast-Case09-Temporal-DB(1).pdf
<https://staff.info.unamur.be/dbm/Documents/Tutorials/SQLfast/SQLfast-Case09-Temporal-DB%281%29.pdf>
J-L Hainaut
On 05/10/2018 20:27, Richard Damon wrote:
On 10/5/18 2:19 PM, James K. Lowden wrote:
On Fri, 5 Oct 2018 17:39:57 +0200
Daniel Kraft <d...@domob.eu> wrote:
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).
"The totality of data in a data bank may be viewed
as a collection of time-varying relations."
-- E.F. Codd in
"A Relational Model of Data for Large Shared Data Banks"
You're not the first. Data change over time. SQL doesn't support data
versions as a language feature, but you can implement it yourself
in your database design.
Add a "version" column to your table. Create views that (using a
self-join) show only the latest version. Periodically purge old
versions. Roll back by deleting new versions.
One design that I have used is to add two timestamps to every record
(with sufficient precision for your versioning, it could be a version
number too), one is the starting time for the record, and the second for
the ending time (NULL if to 'now'). To update a record, you get the
current time stamp (or next version number), alter the existing record
to have that as its end and create a new record with it as the start
time and NULL for the end. To get 'current' data, you condition selects
with ISNULL(endtime), to get a historical record you select such that
start is less than or equal to the time, and the end is greater than the
time or NULL.
You can purge old records based on the end time being old enough, or
total roll back by deleting records with start greater than the time,
and changing end date greater to NULL.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
