Andrew Shakinovsky <andrew.shakinovsky-9g7Z8q3WXAJWk0Htik3J/[EMAIL PROTECTED]> wrote:
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.
Nevertheless, this appears to be the best solution to the problem at this time. Why is it difficult for you?
The thing is, in 99.9% of the time, the error would be received on the first call to sqlite3_step.
100% of the time, actually. Once step succeeds, and until reset or finalize, there is a read transaction (a SHARED lock) against the database. Any schema change is a write operation and cannot proceed in the presence of active readers.
It looks like the call to sqlite3_prepare is not checking for schema changes, whereas the call to sqlite3_step is.
Prepare cannot check for schema changes. It is not unusual to prepare a statement once at the beginning of the program, and keep a prepared statement around for a long time, executing it multiple times as necessary. The existence of a prepared statement does not by itself lock the database, so schema could easily change between prepare and step.
Also, database schema is cached in memory when the database is opened, and prepare works against this cached schema. Checking for schema change at this point would require a disk read, which would be a rather poinless waste of time in view of the previous issue. Schema change is detected as a side effect of any database read - normally when you call step - at which point in-memory snapshot is updated.
Igor Tandetnik
