Did you check out
http://www.sqlite.org/inmemorydb.html
Could you use an in-memory db to act as a db for a save point?
When you are ready to commit, do so from 1 in memory db, while accumulating
into another in preparation for that save point.
Adam
On Tue, May 15, 2012 at 1:55 PM, Charles Samuels <char...@cariden.com>wrote:
>
> 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
>
--
VerifEye Technologies Inc.
905-948-0015x245
151 Whitehall Dr, Unit 2
Markham ON, L3R 9T1
Canada
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
