2012/4/16 Puneet Kishor <[email protected]>:
> 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.
CREATE TABLE doc (
id INTEGER PRIMARY KEY autoincrement,
record TEXT
);
CREATE TABLE t (
id INTEGER PRIMARY KEY autoincrement,
doc_id INTEGER,
rec TEXT,
created_on DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY(doc_id) REFERENCES doc(id)
);
SELECT doc.record, t.rec FROM doc LEFT JOIN t ON doc.id=t.doc_id
WHERE doc.id=id_xx AND created_on<=time_xx
ORDER BY created_on DESC LIMIT 1;
`id_xx` and `time_xx` are keys for search. You may use some additional indexes.
--
Kit
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
