26 Mar 2014

Fetching Objects with FMDB and SQLite

(Experienced developers know all this stuff. If that describes you, you can skip this post.)

Say you’re crazy — which is not recommended, but it could happen — and you want to write your own data layer. One way to do that is to write very specific code that you could never reuse.

A better way is, of course, to write generic, reuseable code. But how do you do that with FMDB and SQLite? How do you make it so the data layer can work with any object?

It’s pretty easy, actually.

Simple, Non-generic Example

Let’s say you have a BSPerson object with the following properties:

@property (nonatomic, assign) int64_t uniqueID;
@property (nonatomic) NSString *name;

You’ve created the table in your SQLite database with something like this:

CREATE TABLE if not exists people (uniqueID INTEGER UNIQUE NOT NULL PRIMARY KEY, name TEXT NOT NULL);

To fetch that object by its uniqueID, you’d use an FMDatabaseQueue and code like this:

FMResultSet *rs = [database executeQuery:@"select * from people where uniqueID=?;" @(uniqueID)];

(Note that uniqueID is boxed, since FMDB wants objects.)

Then loop through the result set — there’s just one result in this case, but you might have multiple results and you’d create an array of results. But we’ll stick to one to keep this simple.

BSPerson \*person = [BSPerson new];
while([rs next]) {
person.oneUniqueID = [rs longLongIntForColumn:@"uniqueID"];
person.name = [rs stringForColumn:@"name"];
}

That’s super-specific, right? The code knows about BSPerson and its properties.

Reusable Example

First let’s make BSPerson more complex. We’ll add two properties:

@property (nonatomic) NSDate \*birthDate
@property (nonatomic) NSData \*imageData;

The object creator needs some information: it needs the class of the object and some properties information. You could get this information a few different ways (in code or convention, or you could write a JSON or plist file that gets turned into a dictionary).

Let’s say the properties dictionary looks like this:

@{@"uniqueID" : @"int64", @"name" : @"string", @"birthDate" : @"date", @"imageData" : @"data"}

And of course the class is [BSPerson class]. Pass the class and dictionary to the object creator as objectClass and properties and you can do this (after the executeQuery):

id oneObject = nil;
while([rs next]) {
oneObject = [self objectOfClass:objectClass withRow:rs properties:properties];
}

(Yes, again I’m assuming a single-row result set, just for the sake of simplicity. Making an array is hardly any additional code.)

objectOfClass looks like this:

id oneObject = [objectClass new];

for (NSString \*oneProperty in properties) {

  NSString \*oneType = properties[oneProperty];
  id oneValue = nil;

  if ([oneType isEqualToString:@"date"]) {
    oneValue = [rs dateForColumn:oneProperty];
  else {
    oneValue = [rs objectForColumnName:oneProperty];
  }

  [oneObject setValue:oneValue forKey:oneProperty];
  }

return oneObject;

(Note: you wouldn’t really use @"date" and so on as literals — you’d define constants. Of course. And you might think twice about storing image data in a database. This is all just for illustration.)

A few things to note:

Even though BSPerson’s uniqueID property is not an object, setValue:forKey: does the right thing. The function is getting an object from the FMResult, but setValue:forKey: knows to set it as an int64_t, because that’s how the property is defined. This is cool. Very, very cool. (Similarly, you could have an integer in the database that goes to a BOOL property.)
FMResultSet’s objectForColumnName: handles integers, floats, blobs, and strings. The one thing to watch out for is dates. If you never have any dates, you could probably get away without having a properties dictionary — you could just use an array of property names and rely on objectForColumnName:. Or you could define a convention where a property name ending in “date” is always an NSDate property.
You could extend the type system — you might have plist and archived types where your code automatically serializes and deserializes the values. It’s best to avoid this, but it can be useful, sparingly.

It’s tempting to want to use property introspection — via class_getProperty and property_getAttributes — instead of specifying types explicitly or via convention. I haven’t done this, and I tend to shy away from runtime functions. However, this could be a case where it’s useful and warranted. You couldn’t assume that every property has a corresponding column, but you could check the column names (which you can get) against the corresponding properties to see when you have an NSDate.