On 15.03.2011 15:34, Philip Martin wrote: > Philip Martin <philip.mar...@wandisco.com> writes: > >> Branko Čibej <br...@e-reka.si> writes: >> >>> The temporary table is: >>> >>> * in a different database, so the read lock on it does not affect >>> the main wc-db; >>> * per-connection, so even the same process using a different >>> database connection will not even see that temporary table. >> OK, so the process is: >> >> - read lock on main database >> - write lock on temporary database >> - populate temporary table >> - release write lock >> - release read lock >> - read lock on temporary database >> - make callbacks >> - release read lock >> >> without the temporary table it could be: >> >> - read lock on main database >> - make callbacks >> - release lock >> >> What we gain is that the callback can modify the main database, because >> there is no read lock. It still can't modify it using any functions >> that require write access to the temporary database. >> >> What we lose is that the callback cannot call any "read-only" functions >> that use temporary tables because the step that requires a write lock >> will fail. > So with the temporary table approach the callback really has to use a > separate database connection to read/write the database from within the > callback. > > However I think that is also the case if we were to avoid the table and > simply lock the main database; if just one connection was reused it > might be attempt to reuse an SQLite statement.
There's a trick we could use if that's a problem, namely: instead of simply creating temporary tables by using the connection's default temp database; create another temp *database* per query. It's anonymous, so only the code that holds its handle knows about it, and there's no way for the callback to access it. This would require a bit more code, however, the concept is a reusable pattern (and the code for the temp database handling would be reusable, too.) -- Brane
