I'm using sqlite in addition to another database ("otherdb") storing data in a
specific manner. I'm trying to keep atomicity of my disk commits. It can take
several minutes for otherdb to commit, and while it commits it can already
start accumulating data for a future transaction.
Some of the data coming into this application also goes into the sqlite
database. But I'd like to keep what's "on the oxide" between sqlite and
otherdb consistent with eachother. Let's accept that otherdb
At some point, we get a checkpoint; at this instant, what is in otherdb and
what is in sqlite is what we want committed to sqlite, if either of them
fails, we can rollback both of them and both databases return to a consistent
state of a previous checkpoint. The problem is that in the time between
checkpoint 1 and checkpoint 1 being committed to disk, more data is arriving.
The question here is: where can I put that "more data" so that it won't be
part of checkpoint 1, but is still accessable by sqlite select statements?
(Accept that otherdb allows asychronous commits such that I can add more data
to it that doesn't wind up on disk).
There's a few possibilities with some serious disadvantages:
* When otherdb completes its checkpoint, I commit sqlite; until otherdb and
sqlite finish their commits, any data going into sqlite instead goes into a
"mirror" sqlite that I can do queries against meanwhile (but then I have to
replay *all* of those modifications against the primary sqlite). This can cost
huge amounts of memory because the sqlite database can get big: 3GiB or more.
It's also slow because all of a sudden I have to do a whole bunch of sqlite
statements. It's even slower because now any update I do *normally* has to be
cloned.
* I could write a virtual filesystem layer for sqlite that somehow accumulates
changes that I can merge in with insert statements. So it's like the previous
solution but I use some arm waving in combination with smoke and mirrors to at
least not make me have two total copies of the database. The problem with this
one is I don't know how to do it, and even if I did, I wouldn't know how
reliable it was.
* If sqlite had a "commit transaction to savepoint X", then sqlite commits to
the oxide everything up to a specific savepoint, keeping the savepoints after
those committed still as active and uncommitted savepoints. The only
disadvantage I can think of to this is that sqlite has no such feature.
So how could I do this?
Charles
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
