I am having a minor issue with schema changes and am looking for the
best solution/workaround. I am using the latest released version of
Sqlite, by the way.
I have a database which needs to be accessed by multiple
users/processes. During it's lifetime it will have tables added to it
periodically through a CREATE TABLE statement. The problem I am running
into is that
when the sqlite3_step function is called, I receive a SQLITE_SCHEMA
error notifying me that the schema
has changed. According to the docs, I am asked to finalize the
statement, and re-prepare it. Unfortunately
this doesn't fit nicely into my architecture. The thing is, in 99.9% of
the time, the error would be received on the first call to sqlite3_step.
It looks like the call to sqlite3_prepare is not checking for schema
changes, whereas the call to sqlite3_step is. Example with two processes
connected to the db:
Process 1 issues CREATE TABLE XYZ
Process 2 issues a sqlite3_prepare to run a SELECT STATEMENT (no error
returned even though the schema has changed since process 2 opened the
database).
Process 2 then issues a sqlite3_step which comes into the code handled
by the "case OP_VerifyCookie". At this point it sees that the schema has
changed and I get the error.
Obviously it would be more helpful if the prepare function would do the
schema check. I am also aware that between the time the prepare is run,
and the first step is called, process 1 may have issued a CREATE TABLE
(as opposed to before prepare is called). To mitigate that, I was
thinking to have process 1 start an exclusive transaction thereby
causing process 2 to wait before running the select. The problem is, as
I mentioned, the schema check is not done during a prepare. My other
problem is that if I start an exclusive transaction on process 1,
process 2 still allows the prepare to go through, but fails (or waits)
on the first call to step.
Has anyone else encountered this?
My workaround would be to use pragma schema_version to get the initial
schema, and then call it again before the prepare. If it has changed,
since I can find no clean way of getting the schema reloaded, I would
either close and reopen the database, or I would issue a SELECT against
a bogus table which apparently causes it to flag the schema to be
updated. This would solve most of my issue. To solve the other part of
my issue, I might try to start an exclusive transaction in process 2
before it does the prepare, and end it after the data has been read.
However this would make the database less responsive to multiple users.
Any other ideas?
