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. -- Richard Damon _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
