Being Ruby on Rails experts, we are acutely familiar with the Active Record pattern and, more specifically, the active record gem that is the Rails default ORM.
As an iOS developer, I am acutely familiar with some of the pain points that can be associated with using SQLite data storage directly in iOS applications.
And since we’re open source software developers and both heavy contributors to and consumers of OSS libraries, we created MojoDatabase to help ease some of those pain points. There are several iOS ORM libraries for SQLite available. Each has a different take on the process, but I’d like to introduce you to MojoDatabase, and give a few of the key features available.
Default SQLite Usage on iOS
First, let’s take a look at some history and point out some of those pain points we mentioned earlier.
A typical tutorial for SQLite + iOS will include all kinds of boilerplate code like the following.
sqlite3 *db;
@try {
NSFileManager *fm = [NSFileManger defaultManager];
NSString *dbPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"DB.sqlite"];
BOOL exists = [fm fileExistsAtPath:dbPath];
if (exists) {
if ( !(sqlite3_open([dbPath UTF8String], &db) == SQLITE_OK) ) {
NSLog(@"Error opening database");
}
const char *query = "SELECT id, foo, bar FROM MyTable";
sqlite3_stmt *sql;
if ( sqlite3_prepare(db, query, -1, &sql, NULL) != SQLITE_OK ) {
NSLog(@"Error with prepare statement");
}
// Now finally run query
while ( sqlite3_step(sql) == SQLITE_ROW ) {
// Do something with DB data here
// accessing columns via:
// - sqlite3_column_int(sql, 0)
// - sqlite3_column_text(sql, 2)
}
sqlite3_finalize(sql);
sqlite3_close(db);
}
}
@catch (NSException *exception) {
…
}
Yikes, all that code just to open a connection to the database and execute a single query? No fun for anyone.
This also assumes you have created your SQLite database file outside of the project scope and brought that file into the application environment. It provides no mechanism for schema maintenance and/or alterations. It clearly doesn’t provide a nice mechanism for connecting to the database, querying the database, or iterating over result sets from a query. To make queries from other source files, you need to reopen your DB connection and go through the whole process again (assuming you don’t have a global connection handle available somewhere).
Using MojoDatabase for SQLite on iOS
MojoDatabase attempts to relieve all these pain points for you in a very straightforward way by supporting the following features:
- connection management
- schema migration support
- ActiveRecord “model” support for objects
- easy query interface
MojoDatabase consists of three main components: AppDatabase, MojoDatabase, and MojoModel.
AppDatabase is where you manage your DB schema and place your schema migration steps.
MojoDatabase is the actual DB object you interact with.
MojoModel is the parent class of objects that mirror your DB tables.
Installation
Installing MojoDatabase is simple. Just drag the MojoDatabase files into your Xcode project, making sure to “copy files to destination” when prompted.
Connecting to SQLite DB
In your application delegate header, create an instance variable that you will use to reference your database
// AppDelegate.h
@class MojoDatabase; // forward declare MojoDatabase class
@interface AppDelegate : NSObject
@property (nonatomic, retain) MojoDatabase *myDatabase;
@end
Then, inside your delegates implementation file, you can create your DB connection when the app launches
// AppDelegate.m
#import "AppDatabase.h" // your MojoDatabase configuration file
@implementation AppDelegate
@synthesize myDatabase;
-(BOOL)application(UIApplication *)application didFinishLaunchingWithOptions:(NSDictionary *)launchOptions {
self.myDatabase = [[AppDatabase alloc] initWithMigrations];
}
-(void)dealloc {
[self.myDatabase release], self.myDatabase=nil;
[super dealloc];
}
@end
This will initialize your database (creating it if the DB doesn’t already exist) and update the database to the current schema version defined in AppDatabase.h.
Schema Migration Support
Maintaining your application's schema is handled inside AppDatabase.m in two locations: the runMigrations method and the associated SQL table statements.
When you need to modify your database's schema, simply create a new method that alters the schema on the client device.
-(void)createLocalUserTable{
[self executeSql:@"CREATE TABLE LocalUser(primaryKey integer primary key auto increment, email text, name text, age integer)"];
}
And to prevent warnings, don’t forget to declare that method in the PrivateMethods category at the top of the file
@interface AppDatabase(PrivateMethods)
-(void)createLocalUserTable;
@end
The last step is to make sure your method is run as part of a schema migration step. The migration steps are declared inside the runMigrations method, and new migration steps are declared like:
// assuming we are currently on DB schema version 3
if ([self databaseVersion]
Now, when your application is launched, if the client device’s DB is pre-version 4, the migration step will be run, and the client DB will be up-to-date. This will allow you to easily version your application's database over numerous updates of your application.
ActiveRecord Model Objects
The easiest way to access tables in your database is to create an associated “Model” class that maps to the tables schema. So, assuming our earlier table of LocalUser, we can define our new model as such:
// LocalUser.h
#import "MojoModel.h"
@interface LocalUser : MojoModel
@property(nonatomic, retain) NSString *email;
@property(nonatomic, retain) NSString *name;
@property(nonatomic, retain) NSNumber *age;
@end
// LocalUser.m
#import "LocalUser.h"
@implementation LocalUser
@synthesize email=_email;
@synthesize name=_name;
@synthesize age=_age;
-(void)dealloc {
[_email release], _email=nil;
[_name release], _name=nil;
[_age release], _age=nil;
[super dealloc];
}
@end
Query Interface
To create a new object (record in the database):
LocalUser *newUser = [[[LocalUser alloc] init] autorelease];
[newUser setName:@"Han Solo"];
[newUser setEmail:@"han@rebelalliance.com"];
[newUser setAge:34];
[newUser save]; // this creates a new record
To later retrieve that LocalUser record, we can query it from the database:
NSArray *records = [self findByColumn:@"email" value:@"han@rebelalliance.com"];
if ( [records count] ) {
LocalUser *user = [records objectAtIndex:0];
[user setAge:35]
[user save]; // this updates the existing record
}
MojoModel provides many different query methods:
+(NSArray *)findWithSql:(NSString *)sql withParameters:(NSArray *)parameters
+(NSArray *)findWithSqlWithParameters:(NSString *)sql, ...
+(NSArray *)findWithSql:(NSString *)sql
+(NSArray *)findByColumn:(NSString *)column value:(id)value
+(NSArray *)findByColumn:(NSString *)column unsignedIntegerValue:(NSUInteger)value
+(NSArray *)findByColumn:(NSString *)column integerValue:(NSInteger)value
+(NSArray *)findByColumn:(NSString *)column doubleValue:(double)value
+(id)find:(NSUInteger)primaryKey
+(NSArray *)findAll
MojoModel also provides several callback hooks:
-(void)beforeSave;
-(void)afterSave;
-(void)delete;
Interested in Contributing?
Once again, MojoDatabase is a public OSS project available on GitHub. We would love to receive any feedback and or contributions. Download it, use it, and let us know what you think.