Objective-C - FMDB - Large SQLite Dump Import

Question

I have a large SQLite file filled up with queries to create my database tables and insert all records. The file is rather large and running the SQL file seems to take much longer than I would have expected.

I am using FMDB for an iPad app I am working on, and I really want to just replace the current DB file with a new one, but I am not sure that an sql file is the same as a DB file. It doens't contain any of the same header info, etc...

What's the best way to go about doing this?

Answer 1

If doing a lot of separate UPDATE or INSERT calls with FMDatabase, consider doing beginTransaction at the start and commit at the end:

[db beginTransaction];
// do all of your updates
[db commit];

Or, if using FMDatabaseQueue, use inTransaction:

[databaseQueue inTransaction:^(FMDatabase *db , BOOL *rollback) { 
    // do all of your updates
}];

If you don't use one of those, it will commit after each insert, which makes it much slower. The difference can be dramatic if adding lots of rows (e.g. I've seen performance differences of two orders of magnitude when adding/updating lots of little records).

---

The above assumes that you are trying to perform a series of separate SQL commands. If it's all in one file (such as .dump output), FMDB hasn't historically had an interface to do that (even though there is a SQLite function, sqlite3_exec, that does precisely this). There was a recent addition to the extra folder called FMDatabaseSplitter, which attempts to splits a long string of SQL into separate calls which you can then invoke separately.

Personally, it makes me nervous to use a third-party SQL parsing routine, so I'd just be inclined to call the SQLite function sqlite3_exec directly. To do that, you can access the sqlite3 pointer from your FMDatabase object using the FMDB sqliteHandle method, and then use that in conjunction with the sqlite3_exec function directly:

NSError *error = nil;
NSString *dumpSQL = [NSString stringWithContentsOfFile:dumpFilePath encoding:NSUTF8StringEncoding error:&error];
NSAssert(dumpSQL, @"Loading of SQL failed: %@", error);

int rc = sqlite3_exec(db.sqliteHandle, [dumpSQL UTF8String], NULL, NULL, NULL);
if (rc != SQLITE_OK) {
    NSLog(@"sqlite3_exec error: %@", [db lastErrorMessage]);
}

I must confess, it makes me a little nervous to just take bulk SQL an import it into an app's database. An innocent mistake in the SQL could brick the app of your entire install-base if you're not extremely careful. I'd rather see the app request JSON or XML feed from the server, and then do the updates itself, but if you want to use the .dump output to update the app's database with FMDB, this is one way to do it.

---

FMDB v2.3 has introduced a wrapper for sqlite3_exec called executeStatements:

BOOL success;

NSString *sql = @"create table bulktest1 (id integer primary key autoincrement, x text);"
                 "create table bulktest2 (id integer primary key autoincrement, y text);"
                 "create table bulktest3 (id integer primary key autoincrement, z text);"
                 "insert into bulktest1 (x) values ('XXX');"
                 "insert into bulktest2 (y) values ('YYY');"
                 "insert into bulktest3 (z) values ('ZZZ');";

success = [db executeStatements:sql];