SQLite Forum

sqlite3_stmt_readonly is dependent on the current database
Login

sqlite3_stmt_readonly is dependent on the current database

(1) By anonymous on 2021-05-13 05:01:04 [link] [source]

sqlite3_stmt_readonly gives valid results only if you check right after you prepare the statement.

char *err;
sqlite3 *db;
sqlite3_stmt *s1, *s2;

sqlite3_open(":memory:", &db);

sqlite3_prepare_v3(db, "CREATE TABLE IF NOT EXISTS data(key TEXT);",-1, SQLITE_PREPARE_PERSISTENT, &s1, NULL);

// Prints 0, not readonly
printf("Readonly : %d \n", sqlite3_stmt_readonly(s1));

sqlite3_exec(db,"CREATE TABLE IF NOT EXISTS data(key TEXT);", NULL,NULL, &err);

sqlite3_prepare_v3(db, "CREATE TABLE IF NOT EXISTS data(key TEXT);",-1, SQLITE_PREPARE_PERSISTENT, &s2, NULL);

// Prints 1, readonly
printf("Readonly : %d \n", sqlite3_stmt_readonly(s2));


sqlite3_exec(db,"DROP TABLE data;", NULL, NULL, &err);

// Prints 1, readonly
printf("Readonly : %d \n", sqlite3_stmt_readonly(s2));

Also, I'd expect sqlite3_stmt_readonly would be more like a generic API. E.g it gives 'false' for UPDATE clauses even the statement will not change the database.

Overall, I find this behavior confusing. Can this be improved somehow? If not, could you consider adding documentation for that please?

(2) By Joshua Wise (wisej12) on 2021-05-13 16:54:11 in reply to 1 [link] [source]

I agree, if it would be nice if there were a generic API for this. Considering that statements created by sqlite3_prepare_v3 transparently re-prepare themselves when the schema changes, it would be nice to have APIs that classify statements consistently, regardless of re-prepares.

(3) By Richard Hipp (drh) on 2021-05-13 17:15:09 in reply to 2 [link] [source]

But how would that work in the example given, in as much as the re-prepare changes the classification.

A "CREATE TABLE IF NOT EXISTS" statement is read-only if the table already exists. But it is read/write if the table does not exist.

Do we create a new return code from sqlite3_stmt_readonly() that means "Maybe - it depends on what has changed in the schema since the statement was last run?"

I looked into modifying sqlite3_stmt_readonly() so that it always returns FALSE for CREATE TABLE statements, even if the statement will be a no-op. I could still do that, but I'm worried that the change in behavior might break legacy applications.

(6) By Joshua Wise (wisej12) on 2021-05-13 23:20:49 in reply to 3 [link] [source]

I think returning either FALSE or MAYBE for CREATE TABLE statements is desirable behavior. At least, it's important for people to be able to detect these situations as "potentially mutating the database" instead of incorrectly assuming "will not mutate the database".

I suggest implementing sqlite3_stmt_readonly_v2() to maintain legacy compatibility.

(4) By Richard Hipp (drh) on 2021-05-13 18:27:38 in reply to 1 [link] [source]

Check-in cf8eb465974e596a changes the sqlite3_stmt_readonly() interface so that it returns false for a CREATE TABLE IF NOT EXISTS statement even if the table already exists and the statement is really a read-only no-op.

(5) By ddevienne on 2021-05-13 19:09:41 in reply to 4 [link] [source]

I personally think that's the correct behavior.

Statement classification should not depend on the current schema at all in fact.
And DDL statements are all basically non read-only, by design in my opinion.
And for DMLs, only SELECT can be read-only.

Unless I misunderstand what that API is supposed to return, just from its name (haven't read the doc).

(7) By Joshua Wise (wisej12) on 2021-05-14 14:55:40 in reply to 4 [source]

Thank you! If this causes issues with legacy applications, we can always consider sqlite3_stmt_readonly_v2(). Either way, I'm happy this has been updated. Thanks :)