On 10/5/18 2:37 PM, Daniel Kraft wrote: > Hi! > > On 2018-10-05 20:27, Richard Damon wrote: >> On 10/5/18 2:19 PM, James K. Lowden wrote: >>> 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. > Those are interesting options for doing things manually, thanks for > pointing them out! > > Unfortunately I'm building a platform (where providing rollback-enabled > SQLite databases is one feature) and not an actual application. So > ideally I need something where I can on the platform-side create > snapshots / start a changeset and let the actual application do SQL > commands, without the need for "instrumenting" the SQL commands themselves. > > Yours, > Daniel > You aren't the first, nor the last person to plan and/or promise features that aren't available. The issue being that SQL isn't defined in a way to track how to roll back more than the current transaction. There may be ways to modify things so that the roll-back journal is saved past the transaction boundary providing the longer roll back period. There might be issues with making sure there is a definite order to transactions from different threads.
-- Richard Damon _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
