> So, if a query returns one or more rows today, the same query (that is, the > same query params with an additional time stamp param) should return exactly > the same result 3 years from now even if the rows themselves may have been > modified.
I just want to note that to support this function you probably want to add 2 dates to each row - one when this version's life started and another one - when it's ended. Otherwise your queries to the past will be very complicated (but it seems to me queries about present are pretty complicated too). For auto-incrementing maybe you want to implement your own auxiliary table a-la sqlite_sequence: when you need to insert new row you select current value from this table, update it and insert row with selected value. Pavel On Mon, Apr 16, 2012 at 2:29 PM, Puneet Kishor <punk.k...@gmail.com> wrote: > > On Apr 16, 2012, at 1:14 PM, Kit wrote: > >> 2012/4/16 Puneet Kishor <punk.k...@gmail.com>: >>> I am experimenting with a home-grown versioning system where every >>> "significant" modification to row would be performed on a copy of the row, >>> the original being preserved. >>> Any other suggestions to achieve a similar functionality would be welcome. >>> -- >>> Puneet Kishor >> >> 1. Use Git or Mercurial > > > My statement might have been misunderstood. I am not trying to create a > versioning system a la Git, Mercurial or Fossil. I am trying to create a data > versioning system so that a query done at a particular time can be reproduced > identically as to the original query even if the data have been modified in > the interim time. > > So, if a query returns one or more rows today, the same query (that is, the > same query params with an additional time stamp param) should return exactly > the same result 3 years from now even if the rows themselves may have been > modified. > > In Postgres world they call it timetravel. See "F.39.2. timetravel — > Functions for Implementing Time Travel" at > http://www.postgresql.org/docs/9.1/static/contrib-spi.html for reference. > > > > >> 2. Try this: >> >> CREATE TABLE instance ( >> filename TEXT, >> version INT, >> size INT, >> md5sum TEXT, >> creation_date TEXT, >> last_write_time TEXT, >> PRIMARY KEY(filename,version), >> FOREIGN KEY (md5sum) REFERENCES resource (md5sum) >> ); >> >> CREATE TABLE resource ( >> md5sum TEXT, >> data BLOB, >> primary key(md5sum) >> ); > > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
