On Fri, 14 Nov 2008 18:34:04 -0800, Roger Binns <[EMAIL PROTECTED]> wrote in General Discussion of SQLite Database <sqlite-users@sqlite.org>:
>-----BEGIN PGP SIGNED MESSAGE----- >Hash: SHA1 > >Kyle Cronin wrote: >> In essence what I'm looking for is a way to defer database writes in a >> process so that they can all be done at once. However, while I'm >> accumulating update queries, > >Why not attach a memory database and accumulate your updates in there. >Once ready you do something like: > > INSERT INTO permanent_table SELECT * from memdb.table I assume the OP wants to collect updates from more than one webpage into one logical transaction, where every webpage contains another part of the data to be changed. In that case a memory database wouldn't work because it would disappear between page requests. >You could also attach another disk database and accumulate updates in >there, periodically copying across to the main database if you don't >want to lose the accumulating updates. That would work. I would use the main database all the way though, because it enables triggers that can act over the tables with persistent data and the tables with transient data meant to accumulate updates in. An PHP application like this (see my assumption above) always needs session data that spans over page requests. Session data could be used to store the updates. >Or you could follow the sage advice about premature optimization and >actually wait till you have problem and then address whatever that >problem turns out to be :-) One common design pattern is to flag every row that is "logically opened for update" with the user_id or session_id and a timestamp or transaction_id. In the update phase (when the user commits the logical update), the application should check those to verify the same rows have been grabbed by another user, and report a "Your update is blocked by another user" if they are. In the OP's scenario his users would never get that message: >> All I can say is that in my case there's no >> danger in performing these updates simultaneously. To get back to the original question: >> One solution is that in each process I could accumulate >> the text of the queries I wish to invoke in an array, >> then loop down it once I'm ready to write, but I'm >> wondering if it's possible the SQLite transaction can >> be made to do this for me automatically. There is no immediate support for that. SQLite is a database engine, not a transaction manager. As a side note: SQLite could be used to store session data, one could rewrite the "PHP4 MySQL Session Handler" by Ying Zhang for SQLite. http://coding.derkeiler.com/Archive/PHP/comp.lang.php/2005-06/msg01492.html HTH -- ( Kees Nuyt ) c[_] _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
