Re: [sqlite] Schema updates invalidating connection
The only way for a database connection to know if the schema has changed is to open and read the database file. Sqlite_prepare() tries to avoid reading the database file in order to reduce contention, though, so it is unlikely to discover a database change. The change is only discovered when sqlite3_step() is run. Having sqlite3_prepare() avoid opening and reading the database file is a desirable optimization since it makes sqlite3_prepare() run faster in the common case where no schema change occurs. A fair enough optimisation. The only way I know of to force sqlite3_prepare() to check the database schema is to close and reopen the database connection. But doing so will dramatically increase the amount of time it takes to prepare each statement. When the step fails (with SQLITE_ERROR not SQLITE_SCHEMA) the finalize is called and it returns SQLITE_SCHEMA. (If sqlite3_step could return SQLITE_SCHEMA the condition would be easier to detect). Having finalized the previous statement, the next statement prepared and stepped works perfectly. Does this mean that sqlite3_finalize is closing and reopening the database under the hood? If so, does this mean that sqlite3_finalize is very expensive? If not, then there is something that sqlite3_finalize does that updates the connection's view of the schema. Would it possible to do this prior to the prepare? (should one wish to do so and knowing that there is an overhead involved) Your best approach is to modify your code so that you are prepared to deal with SQLITE_SCHEMA errors returned by sqlite3_step. You'll need this anyway for the rare case when the schema changes in between sqlite3_prepare and sqlite3_step. And, presumably, the less rare case where subsequent steps are called to retrieve data. Indeed, this is easy to cope with from a users perspective, an active query is terminated due to a schema change. It just seems a little strange (to us) that a schema change by another thread/process that may have completed "ages" ago means that the next query we attempt fails. We can program for this in that if the first sqlite3_step of a prepared statement fails then, like sqlite3_exec, we can re-try the prepare and step. If it isn't the first step then we can terminate the select and somehow indicate that the schema has changed during the select. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Schema updates invalidating connection
Matthew Jones <[EMAIL PROTECTED]> wrote: > what should our program do prior to > preparing a statement to ensure that its view of the database schema is > correct? > The only way for a database connection to know if the schema has changed is to open and read the database file. Sqlite_prepare() tries to avoid reading the database file in order to reduce contention, though, so it is unlikely to discover a database change. The change is only discovered when sqlite3_step() is run. Having sqlite3_prepare() avoid opening and reading the database file is a desirable optimization since it makes sqlite3_prepare() run faster in the common case where no schema change occurs. The only way I know of to force sqlite3_prepare() to check the database schema is to close and reopen the database connection. But doing so will dramatically increase the amount of time it takes to prepare each statement. Your best approach is to modify your code so that you are prepared to deal with SQLITE_SCHEMA errors returned by sqlite3_step. You'll need this anyway for the rare case when the schema changes in between sqlite3_prepare and sqlite3_step. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Schema updates invalidating connection
We have a problem that is obviously to do with our failing to understand
sqlite properly. Using a test program (enclosed) we create a table in a
new database and then wait (for use input). If we then open the same
sqlite database using sqlite3 and create another table in the database
when we continue with our program it fails to insert data into the table
it has created. Now, we know that schema changes invalidate any
previously prepared statements but we always prepare and execute (step)
our statements in one go. So what should our program do prior to
preparing a statement to ensure that its view of the database schema is
correct?
If we use sqlite3_exec instead it all works as this has a built in retry
for the step failing and then the finalize returning SQLITE_SCHEMA. I
thought this was to cater for the case of preparing before the schema
change and then stepping afterwards, the documented failure, but it also
covers our case.
I raised a bug at sqlite.org, ticket 1936, but have been told that I
should post to the list to receive support. Obviously, we are missing
something very basic here or .
Our problem occurs in a multi-threaded environment but we have minimized
the code in the attached example to remove any threading issues.
code follows:
/* Prepare, execute, finalize as an atomic operation */
void execute(struct sqlite3 *db, const char *sql)
{
int status = 0;
struct sqlite3_stmt *stmt;
const char *tail = 0;
fprintf(stdout, "%s\n", sql);
if((status = sqlite3_prepare(db, sql, strlen(sql), ,
)) != SQLITE_OK)
{
fprintf(stderr, "sqlite3_prepare: %d\n", status);
}
status = sqlite3_step(stmt);
if(status != SQLITE_DONE)
{
fprintf(stderr, "sqlite3_step: %d, %s\n", status,
sqlite3_errmsg(db));
}
if((status = sqlite3_finalize(stmt)) != SQLITE_OK)
{
fprintf(stderr, "sqlite3_finalize: %d, %s\n", status,
sqlite3_errmsg(db));
}
}
int main(int ac, char* av[])
{
int status = 0;
struct sqlite3 *db = 0;
char buffer[256];
if((status = sqlite3_open("test.db", )) != SQLITE_OK)
{
fprintf(stderr, "sqlite3_open: %d\n", status);
}
sprintf(buffer, "create table %s (i int)", av[1]);
execute(db, buffer);
puts("type to continue:");
getchar();
sprintf(buffer, "insert into %s values (4)", av[1]);
execute(db, buffer);
if((status = sqlite3_close(db)) != SQLITE_OK)
{
fprintf(stderr, "sqlite3_close: %d\n", status);
}
return 0;
}
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
