How do I use sqlite3 PRAGMA user_version in Object

Question

I am trying to check the user_version of the sqlite DB. I have an admin tool to bump the version, but I don't understand the syntax of the pragma statement. I am expecting to test the value in an if-statement. Can someone provide a code sample? When I embed the pragma statement in my objective-c code, the compiler throws an error.

Answer 1

I figured it out with the inspiration from newtover, digging into FMDB and re-reading the sqlite3 documentation (it is still very vague in my opinion). This code returns the value that I bumped in the admin tool when I make notable changes to the schema that require migration.

-(int)queryUserVersion: (sqlite3*) db {
    // get current database version of schema
    static sqlite3_stmt *stmt_version;
    int databaseVersion;

    if(sqlite3_prepare_v2(db, "PRAGMA user_version;", -1, &stmt_version, NULL) == SQLITE_OK) {
        while(sqlite3_step(stmt_version) == SQLITE_ROW) {
            databaseVersion = sqlite3_column_int(stmt_version, 0);
            NSLog(@"%s: version %d", __FUNCTION__, databaseVersion);
        }
        NSLog(@"%s: the databaseVersion is: %d", __FUNCTION__, databaseVersion);
    } else {
        NSLog(@"%s: ERROR Preparing: , %s", __FUNCTION__, sqlite3_errmsg(db) );
    }
    sqlite3_finalize(stmt_version);

    return databaseVersion;
}

I have a similar method for the schema version where the sql-statement is changed to "PRAGMA schema_version;"

Answer 2

If you are using FMDB wrapper (which is recommended if you don't want to deal with C handler interface of sqlite)

Use

[self.db setUserVersion:yourUserVersion]; // yourUserVersion is of uint32_t type

To read current user_version user

[self.db userVersion];                   // returned value is of uint32_t type

Documentation:

http://ccgus.github.io/fmdb/html/Categories/FMDatabase+FMDatabaseAdditions.html

This answer is basically copy of this answer: https://stackoverflow.com/a/27807125/1364174

Answer 3

Pragma statements can not be used within other statements (there are no references to pragma-stmt from other statements).

But you can use the user_version value by making two requests: querying pragma and using the selected value as literal in the next query.

UPD: if you are interested in PRAGMA syntax, it is rather simple:

sqlite> pragma user_version=10;
sqlite> pragma user_version;
user_version
------------------------------
10
sqlite> pragma user_version='12.3.124';
sqlite> pragma user_version;
user_version
------------------------------
12

That is the result will come as a row with a single value.

If you are interested in a way to issue statements to SQLite in objective-c, try looking at neighbour questions: example. Unfortunatelly, I've never coded in objective-c.