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 -- 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
signature.asc
Description: OpenPGP digital signature
_______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
