> Is there a way to trigger a refresh of the internal schema cache?
There are many ways to force a check of the schema to see if it needs
to be reread. Here is one:
~~~~~
PRAGMA user_version;
~~~~~
If the schema has already been parsed and symbol tables for the schema
are in memory, then sqlite3_prepare_v2() does *not* check to see if the
schema has been changed. This is because the answer is almost always
"no" and doing the test involves disk I/O. Instead, the byte-code generated
by sqlite3_prepare_v2() checks for a schema change when it runs and if
the schema has changed, the sqlite3_step() aborts, rereads the schema,
reprepares the statement, and tries again.
So, if you do a CREATE TABLE and that table is already in the schema cache,
you will get an error message, because the schema cache does not know that
the table has been dropped, and no byte-code is run to check for this.
If you have multiple threads that are racing to change the schema, then
you would do well to put each change inside of a transaction:
~~~~~
BEGIN IMMEDIATE;
CREATE TABLE test(...);
COMMIT;
~~~~~
Do this for every sequence of CREATE and DROP commands. Note that I mean
that the sqlite3_prepare_v2() for each CREATE and DROP should be run after
the BEGIN IMMEDIATE returns successfully.