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.
