I have a system that is based on the SQLite database. Each client has a local database, and once in a while the update arrives from the main server, just a small delta .db file. The task is to merge to local database with the delta file, the schema is identical in both.
For my database management I use fmdb wrapper that can be found here. In the main thread, I keep the connection to the local database open. The delta file arrives in the background, and I want to do the merge in the background to avoid any user interface freezes that this could cause.
As for the merge itself, the only option that I found is to attach the delta database to the local database, then insert/update the rows, and finally detach the delta. This does not work as smooth as I expected.
Code description:
- The onDeltaGenerated method is invoked in a background thread whenever delta database is ready to be processed (arrives from the server and is saved in the readable location).
- The deltaDBPath is the absolute location of the delta database in the filesystem.
- The db variable references open FMDataBase connection.
Code:
- (void)onDeltaGenerated:(NSNotification*)n {
NSString* deltaDBPath = [[n userInfo] objectForKey:@"deltaPath"];
@synchronized(db) {
[db executeUpdate:@"ATTACH DATABASE ? AS delta", deltaDBPath];
if ([db hadError]) {
NSLog(@" ****ERROR*** %d: %@", [db lastErrorCode], [db lastErrorMessage]);
} else {
NSLog(@"Delta attached from %@", deltaDBPath);
}
[db beginTransaction];
BOOL update1 = NO;
BOOL update2 = NO;
BOOL transaction = NO;
update1 = [db executeUpdate:@"INSERT OR REPLACE INTO equipment SELECT * FROM delta.equipment"];
if (!update1) {
NSLog(@" *** ERROR *** update 1 failed!");
NSLog(@" ****ERROR*** %d: %@", [db lastErrorCode], [db lastErrorMessage]);
}
update2 = [db executeUpdate:@"INSERT OR REPLACE INTO equipmentExt SELECT * FROM delta.equipmentExt"];
if (!update2) {
NSLog(@" *** ERROR *** update 2 failed!");
NSLog(@" ****ERROR*** %d: %@", [db lastErrorCode], [db lastErrorMessage]);
}
transaction = [db commit];
if (!transaction) {
NSLog(@" *** ERROR *** transaction failed!");
NSLog(@" ****ERROR*** %d: %@", [db lastErrorCode], [db lastErrorMessage]);
}
[db executeUpdate:@"DETACH DATABASE delta"];
if ([db hadError]) {
NSLog(@" ****ERROR*** %d: %@", [db lastErrorCode], [db lastErrorMessage]);
} else {
NSLog(@"Delta detached");
}
}
}
After this method is invoked for the first time, all seem to be fine until I try to detach the database. When I try to do it, I get the following error:
2012-01-11 12:08:52.106 DBApp[1415:11507] Error calling sqlite3_step (1: SQL logic error or missing database) SQLITE_ERROR
2012-01-11 12:08:52.107 DBApp[1415:11507] DB Query: DETACH delta
2012-01-11 12:08:52.107 DBApp[1415:11507] ****ERROR*** 1: database delta is locked
I also tried to the same but without putting inserts into transaction, the result is identical. Another thing was to remove the @synchronized clause, but also no luck. My guess is that if fails when trying to access local database connection from the background thread, but then how come it manages to attach and insert? Any help appreciated.
Edit
I moved the code to the main thread, so the db is now accessed from the main thread only. The problem remains.
Edit2
Ok, so after trying everything, I gave up on this for a moment and then came back when the first answer appeared here. Surprisingly, everything seems to work fine now, so my code must be correct. I suspect this was the problem with different threads locking the file, as I used XCode, SQLiteDatabaseBrowser and my application to open the database. Even though the lsof showed that the file was not locked, I think it was wrong and either XCode or SQLiteDatabaseBrowser was locking it. I consider the problem solved, and the lesson taken from this is not to thrust lsof so much and also plan the debugging better next time.