Mike Cohen

A lot has been written about the relative benefits of Core Data or SQLite in an iPhone app. In most cases, Core Data is easier and has some performance benefits, but the data store is difficult to create & modify from outside of your application. SQLite, on the other hand, has some excellent tools like SQLiteManager for creating & editing data. Therefore, if you have a large amount of pre-loaded data that you want to be able to edit easily, SQLite makes more sense.

This is exactly the case with Removr. Game levels are defined by an object that specifies the background image and a bitmap of the layout of pieces on the screen. Although Core Data could easily fetch the object as needed in Removr, it would be very difficult for an external level editor to create those objects and update the persistent store.

My level object happens to be very simple and can easily be mapped to a database structure.

// Level.h
@interface Level : NSObject {
    NSData * _map;
    NSString * _background;
    NSNumber * _index;
}

@property (nonatomic, retain) NSData * map;
@property (nonatomic, retain) NSString * background;
@property (nonatomic, retain) NSNumber * index;

@end

// Level.m
#import "Level.h"

@implementation Level
@synthesize map = _map, background = _background, index = _index;

- (void)dealloc
{
    self.map = nil;
    self.background = nil;
    self.index = nil;
    [super dealloc];
}
@end

The corresponding database structure looks like this:

CREATE TABLE levels (
	ix integer NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
	background text,
	map blob NOT NULL
);

Fetching the objects from sqlite takes more code than using Core Data but isn’t too difficult. I need to deal with two different SQLite3 objects for the database connection and the prepared query. Rather than interpreting the SQL query every time you execute it, you will prepare the query once and execute it as many times as you want.

A good introduction to the SQLite3 C interface is available here.

Here is my level manager. My init method opens the database connection and prepares the query that will be used later. Note that the query includes a ‘?’ for variable substitution.

The GetLevel method is where the interesting stuff happens. The function sqlite3_bind_int() tells it which level number we’re looking for and sqlite3_step() actually executes the query. In this case, I’m only interested in a single row, but in many cases it will be called repeatedly as long as it returns SQLITE_ROW. I then use the sqlite3_column functions to extract data from the result row and populate my Level object. Finally, calling sqlite3_reset() will allow the prepared statement to be reused.

// LevelManager.h
@interface LevelManager : NSObject {

    NSString *_dbpath;

    sqlite3 * db;
    sqlite3_stmt * query;
}

@property (retain,nonatomic) NSString *dbpath;

@end

// LevelManager.m
#import "LevelManager.h"
#import "Level.m"

@implementation LevelManager

@synthesize dbpath = _dbpath;

- (id) init
{
    if ((self = [super init])) {
        self.dbpath = [[NSBundle mainBundle] pathForResource:@"levels" ofType:@"sqlite3"];
        sqlite3_open([self.dbpath UTF8String] , &db);

		// this query will be used to obtain a level from the database.
		// The '?' will be replaced with the level number when we perform the query
        sqlite3_prepare_v2(db, "SELECT * FROM levels WHERE ix=?", -1, &query, NULL);

    }
    return self;
}

- (void)dealloc
{
    sqlite3_finalize(query);
    sqlite3_close(db);

    self.dbpath = nil;

    [super dealloc];
}

- (Level*)GetLevel: (int)number
{
    Level *lvl = nil;

	// specify the level number we want for the query
    sqlite3_bind_int(query, 1, number);

	// request a row from the query result
    if (sqlite3_step(query) == SQLITE_ROW) {
        void *blob;
        int nbytes;

		// first, create a new level object
        lvl = [[Level alloc] init];

		// integer columns are easy
        lvl.index = [NSNumber numberWithInt: sqlite3_column_int(query, 0)];

		// string columns are a bit more complex since we need to convert a C string to a NSString
        lvl.background = [NSString stringWithCString:(char*)sqlite3_column_text(query, 1)
                                                                 encoding:NSUTF8StringEncoding];

		// BLOB columns require two calls to obtain the actual data and the length
        blob = (void*)sqlite3_column_blob(query,2);
        nbytes = sqlite3_column_bytes(query,2);

		// we use the bytes & length to create a NSData
        if (blob && (nbytes > 0)) {
            lvl.map = [NSData dataWithBytes:blob length:nbytes];
        }
    }

	// get ready to reuse the query
    sqlite3_reset(query);

	// we should return an autoreleased object
    return [lvl autorelease];
}

@end