On Sun, Sep 20, 2015 at 8:11 AM, Simon Slavin <slavins at bigfraud.org> wrote:
> > On 20 Sep 2015, at 2:55pm, gwenn <gwenn.kahz at gmail.com> wrote: > > > But if a column is added to one table in your database, your statement > > is successfully/transparently recompiled (when calling sqlite3_step) > > and the column count may be changed. > > Let's assume that you have not used any PRAGMAs to defeat the concurrency > and locking operations of SQLite, to share the cache, or anything else > which is obviously unsafe in a multi-access scenario. If this is so then > you do not have to worry about this problem. > > Depending on your settings one of two things will happen. > > A) Use of ALTER TABLE ... ADD COLUMN requires locking the database. This > will not be permitted because the database will already be locked, from the > first execution of sqlite3_step() until sqlite3_step() returns SQLITE_DONE > or an error message. > > B) The connection executing sqlite3_step() reads from the database. The > connection executing ALTER TABLE ... ADD COLUMN creates a modification of > the database which the first connection can't see. It won't be able to see > that change until it has released its lock on the database. > > Which of these happens depends at least on the journal mode of the > database, and perhaps on other things I can't think of right now. But it > will always be one or the other. > If I am understanding correctly, what the OP wants to know is: 1. Prepare a statement such as "select * from some_table". 2. Step through it, caching column count information after the first step. 3. Some time passes and some external process may alter the schema. 4. Step through the prepared statement from #1 again after doing any necessary reset. No finalize / re-prepare step. 4a. The column count from the first run of the query was cached back during step 2. Can it be determined if the schema changed without making an "expensive" call to get the column count again? Obviously sqlite is able to detect that the schema changed and only re-prepare the query if necessary. Is that functionality available to ordinary applications? I suspect that the answer is either "no" it is not directly available, or it will involve calling an equally expensive API function to detect the schema signature value. Either way, something will have to be called in the sqlite3 api and that will be potentially expensive if it involves a boundary transition in JNI or go or whatever examples were cited previously. -- Scott Robison
