How use sqlite + fdbm library with threading on the iPhone

Question

Related to this SO question, I want to put the data loading in the background.

However, I get 'library routine called out of sequence' errors.

In this SO thread say that the way is using NSOperation, but looking on the samples on the web I not know how that could solve the issue.

I share a single sqlite connection with the singleton pattern:

@interface Db : NSObject {
    NSString *path;
    FMDatabase* theDb;
    BOOL isOpen;
}

@property (retain, nonatomic) FMDatabase *theDb;
@property (retain, nonatomic) NSString *path;
@property (nonatomic) BOOL isOpen;
--------
static Db *currentDbSingleton = nil;
#pragma mark Global access

+(id)currentDb {
    @synchronized(self) {
        if (!currentDbSingleton) {
            NSString *reason = NSLocalizedString(@"The database is not set globally",
                                                 @"Error Db: database is not set");
            NSException *e = [NSException exceptionWithName:@"DBError"         
                                                     reason:reason;
                                                   userInfo:nil];
            @throw e;
        }
    }
    return currentDbSingleton;  
}

So is harder open twice the same db....

Any ideas?

EDIT:

I confirmed the error is in calling sqlite. I use FDBM as thin wrapper for calling it.

I'm running 2 threads: the main and a background task for loading of the data. I run it this way:

- (void) fillCache:(NSString *)theTable {
    [NSThread detachNewThreadSelector:@selector(fillCacheBackground:)
           toTarget:self
            withObject:theTable];
}

- (void)loadComplete {
    [self.table reloadData];
}

- (void) fillCacheBackground:(NSString *)theTable {
    NSAutoreleasePool *pool = [[NSAutoreleasePool alloc] init];
    Db *db= [Db currentDb];
    [db beginTransaction];
        ..... STUFF HERE
    [db commitTransaction];
    //Tell our callback what we've done
    [self performSelectorOnMainThread:@selector(loadComplete) 
            withObject:nil 
         waitUntilDone:YES];
    [pool drain];
}

The code for the db interface is at http://code.google.com/p/chibiorm/source/browse/#svn/trunk/src — specifically the Db.h/m that are the only units that interface with fdbm/sqlite.

The error happend when try to call sqlite functions from FDBM.

For example happend here:

-(void) checkError {
    if ([self.theDb hadError]) { // <====ERROR HERE
     NSLog(@"Err %d: %@", [self.theDb lastErrorCode], [self.theDb]);
    }
}

This call the FDBM code:

- (BOOL) hadError {
    int lastErrCode = sqlite3_errcode(db);
    return (lastErrCode > SQLITE_OK && lastErrCode < SQLITE_ROW);
}

Answer 1

The singleton method is a fine idea, although it doesn't look like you're actually initializing currentDbSingleton anywhere... Assuming you fix that and you return a valid DB connection, I don't think that's you're problem.

The 'library routine called out of sequence' errors you mention tip me off that the library you're using (SQLite or FMDB) expects method/function calls to be made in a particular sequence. What you likely have is a concurrency problem where two or more threads are making calls to the same library, and while each may use the correct order, if they're "talking at the same time", so to speak, the library may receive calls out of the expected sequence. What you want is for a set of calls to be treated as an atomic unit, such that they cannot overlap or intermingle.

This is where NSOperation comes in. Subclassing it allows you treat a bunch of code as a "single encapsulated task" — you'll probably want to design for non-concurrent operation. The class documentation has details that explain how to implement your logic within an NSOperation.

---

Edit: (After asker clarified the question with addition context)

Since the problem is occurring in checkError, and that method is called from multiple places in your linked .m file, there's a good chance that you're calling hadError at an incorrect time. (Is it called after a transaction is closed? What if it is called after the next transaction has begun?)

For example, what would happen if fillCache is called while the previous call is still accessing the database? Your transaction management methods look extremely suspect in this regard. For example, if someone calls beginTransaction and inTransaction is YES, the method returns without doing anything (that is, it doesn't call to the FMDatabase ivar at all). What you probably want is for the second caller to wait until the first caller finishes its transaction. (Unless FMDatabase supports concurrent transactions, in which case you'd want to call beginTransaction on it regardless.)

If you haven't already, read this Apple article on Objective-C thread synchronization. Next, consult the documentation for NSLock (particularly lockBeforeDate:) and associated sample code. Inside your -[Db beginTransaction] method, you'll probably want to block on obtaining a lock.

You also have several peculiar class methods, such as +allocWithZone: — opt to use +inizialize (which is called automatically by the runtime when the class is first referenced) if you can, so the class can take care of initializing itself without the need for a manual call. (I'm guessing you call +alloc, then -initWithName:, then feed it back to +setCurrentDb. A convenience method such as +initializeWithPath: that handles all that would be much cleaner.)

There are numerous other gotchas, such as the fact that +setCurrentDb: could swap out the singleton object regardless of whether a transaction is in process (and the old singleton is not released), +currentDb raises an exception where it should probably just create the singleton instance, etc. However, the biggest problems you're facing are getting concurrency right. I think that implementing locks to protect the FMDatabase reference is a step in the right direction, but just wrapping method X in an NSOperation isn't going to do it for you. Every point in your code that references theDb without the guarantee that nobody else is doing so risks a crash. Don't feel bad if this seems hard, because it is.

Last random suggestion: change your methods TypeForField:Type: and ValueForField:Name:Type: to typeForFieldName:typeName: and valueForResultSet:fieldName:typeName: respectively. Strive for accuracy, readability, and matching convention.

Answer 2

I finally found a working solution.

The idea is build a database pool of connection and open twice the db. Use one connection for the main thread and the another for the background.

Everything is now in http://code.google.com/p/chibiorm/