I was once told of an idea (decades ago) of versioning data within a table where one column has a real/float value that is the version number. The data in the table can be committed as necessary. If the data needs to be rolled back the data can be rolled back/deleted to the table based on the real/float value. If necessary data can be inserted into the table between real/float values, sort of inserting data into the table like ?fixing? the events in time.
Mike > On May 15, 2016, at 10:23, Mikael <mikael.trash at gmail.com> wrote: > > 2016-05-15 22:14 GMT+07:00 Simon Slavin <slavins at bigfraud.org>: > >> >> On 15 May 2016, at 4:02pm, Mikael <mikael.trash at gmail.com> wrote: >> >>> Simon, yes using only INSERT and DELETE in total would be fine with me >> (and >>> perhaps BEGIN and COMMIT if it's OK). >>> >>> What are you thinking about? >>> >>> Did you see a purely SQL-based solution that I don't? >> >> BEGIN and COMMIT make no changes to stored data so you can ignore them for >> these purposes. >> >> Add a "timestamp" column to your table. When you INSERT a row, have this >> default to the current time: >> >> , timestamp TEXT DEFAULT CURRENT_TIMESTAMP, ... >> >> Add a "deleted" column to your table, defaulting to NIL >> >> , deleted TEXT DEFAULT NIL, ... >> >> When you delete or overwrite a row, instead of actually using the DELETE >> command, replace the NIL in this column with the time the row was deleted. >> >> UPDATE myTable SET deleted=CURRENT_TIMESTAMP WHERE rowid=1234 >> >> You now have a way of tracking when each row was created and, if it has >> been deleted, when it was deleted. Creative use of a WHERE clause in your >> SELECT commands will allow you to pick out only the rows which exist at a >> certain time. >> >> If you have defined a clever primary key rather than just using a rowid, >> you will need to think through adjustments to your schema and programming >> accordingly. >> >> Simon. >> > > > Aha. > > Do you see any way that I could implement *one* layer of branches atop of > this? > > Say 1000 or 10^30 of them. > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
