mbehan

Basic ORM on top of FMDB and SQLite with Objective-C

The FMDB wrapper simplifies using SQLite it from Objective-C. A typical scenario is that you'll execute a query and get back a lovely FMResultSet object and you can extract values from that using your database column names - nice.

What would be slightly nicer thought is automatically mapping that result set onto a model object. So lets make that a thing.

Example #

We have a table in our database called People with the following fields:

And it makes sense for us to have a Person class in our app because maybe we'll want to maintain a table of people and be able view the detail of a person by passing a Person from the table view to the detail view. The Person class will be defined something like this:

@interface Person : NSObject

@property(nonatomic) NSInteger personId;
@property(nonatomic, copy) NSString *firstName;
@property(nonatomic, copy) NSString *lastName;
@property(nonatomic, copy) NSString *address;
@property(nonatomic, copy) NSString *favouriteSimpsonsReference;

@end

So to create some Person objects we could alloc init a bunch of them and set their properties based on what we get back from the database, alternatively we could create a custom initialiser method to take a FMResultSet and set them all that way. All of which is perfectly fine until you find yourself repeating it over and over again.

Homer Simpson making OJ the old fashioned way

For simple situations like this though, there is a better way.

Here's the class that I use as a base class for all my model objects, it provides an initialiser that takes a result set as a parameter and looks for columns in that result set with the same name as its properties.

@interface MBSimpleDBMappedObject : NSObject

-(instancetype)initWithDatabaseResultSet:(FMResultSet *)resultSet;

@end
-(instancetype)initWithDatabaseResultSet:(FMResultSet *)resultSet
{
    self = [super init];
    if(self)
    {
        unsigned int propertyCount = 0;
        objc_property_t * properties = class_copyPropertyList([self class], &propertyCount);
        
        for (unsigned int i = 0; i < propertyCount; ++i)
        {
            objc_property_t property = properties[i];
            NSString *propertyName = [NSString stringWithUTF8String:property_getName(property)];
            
            [self setValue:[resultSet objectForColumnName:propertyName] forKey:propertyName];
        }
        free(properties);
    }
    
    return self;
}

@end

What we're doing here is quite simple, but it's enabled by a couple of powerful Objective C features. Firstly, at runtime we can dynamically retrieve the names of a loaded classes properties, then we can simply set the values of properties using key value coding.

Those are the only 2 things happening here, get a list of the properties, then for each property set its value to the one from the result set with a matching column name.

This means all our model subclasses have to do is declare a bunch of properties, so all there is to those classes is the interface I described before, just subclassing MBSimpleDBMappedObject instead of NSObject like so.

@interface Person : MBSimpleDBMappedObject

@property(nonatomic, readonly) NSInteger personId;
@property(nonatomic, readonly, copy) NSString *firstName;
@property(nonatomic, readonly, copy) NSString *lastName;
@property(nonatomic, readonly, copy) NSString *address;
@property(nonatomic, readonly, copy) NSString *favouriteSimpsonsReference;

@end

I've marked the properties read only, because all I'm interested in is a copy of what's in the database, changing the values of those properties won't update the database, though I do plan to add that functionality in the future. If this is all you need then you're done, your Person implementation can be left blank.

A Note About Dates #

If you're familiar with SQLite and FMDB you'll know they don't really do dates, but you'll probably find yourself wanting to keep track of some dates in the database. FMResultSet's objectForColumnName will gladly give you a number or a string, but it doesn't do NSDate's. Here's how I deal with that.

Better Example Time #

Let's change our People table a bit to make it a bit more useful, so our list of fields looks like:

and update our Person interface too

@interface Person : MBSimpleDBMappedObject

@property(nonatomic, readonly) NSInteger personId;
@property(nonatomic, readonly, copy) NSString *firstName;
@property(nonatomic, readonly, copy) NSString *lastName;
@property(nonatomic, readonly, copy) NSString *address;
@property(nonatomic, readonly)NSTimeInterval dateOfBirthTimestamp;
@property(nonatomic, readonly, strong)NSDate *dateOfBirth;

@end

With no other changes the dateOfBirthTimestamp property will be set correctly which may be enough, but you'd probably have to make an NSDate with it anytime you wanted to do anything useful with it. We've added an NSDate property, but as there is no corresponding column name, it will remain nil. That is until we override the initialiser as follows.

-(instancetype)initWithDatabaseResultSet:(FMResultSet *)resultSet
    {
        self = [super initWithDatabaseResultSet:resultSet];
        if(self)
        {
            _dateOfBirth = [NSDate dateWithTimeIntervalSince1970:self.dateOfBirthTimestamp];
        }
   return self;

}

@end

The base class will still map all the other properties, we just construct the NSDate.