The Cocoa API provides Cocoa Touch which is a great (and powerful!) abstraction layer over the underlying SQLite libraries, but sometimes you just want to keep things simple with simple operations directly on a database. Unfortunately, the SQLite libraries themselves are implemented in C, so not object-oriented, and are fairly complex.

With that in mind, for a recent project I developed my own small wrapper around the underlying SQLite libraries to allow me to perform queries and get back responses as Objective-C objects rather than C primitives. I’m not going to reproduce all of the code here, but I will provide a starter that will get you up & running and give you most of the functionality you would need. Of course there is more you can do (particularly error handling) that I don’t get into here, but feel free to post any improvements you may have in the comments.

The first thing you need to do is link libsqlite3.0.dylib to your build target. To do this in Xcode 4, select your project at the top of the left navigation panel, then under “Targets” at the left choose your build target and then go to the “Build Phases” tab. Click on the plus and add libsqlite.3.0.dylib (you can find it by typing “libsq” into the search box).

You will now want to create a standard  class which will act as the wrapper around libsqlite. In this case, I called mine “SQLiteDatabase”. In SQLiteDatabase.h, you will then want to import sqlite3.h (header for the library) and create an ivar to hold a reference to the database.

I have also added two methods which will handle loading the database from a given path and also performing a given query and returning the response as an NSArray object.

#import <Foundation/Foundation.h>
#import <sqlite3.h>

@interface SQLiteDatabase : NSObject {
    sqlite3 *database;
}

- (id)initWithPath:(NSString *)path;
- (NSArray *)performQuery:(NSString *)query;

@end

With that out of the way, you can now turn your attention to SQLiteDatabase.m. In here, you can now specify your implementation of each method. First, let’s look at loading the database:

- (id)initWithPath:(NSString *)path {
    if (self = [super init]) {
        sqlite3 *dbConnection;
        if (sqlite3_open([path UTF8String], &dbConnection) != SQLITE_OK) {

            NSLog(@"[SQLITE] Unable to open database!");
            return nil; // if it fails, return nil obj
        }
        database = dbConnection;
    }
    return self;
}

This is simple enough, it calls the underlying C function sqlite3_open() to handle the actual opening of the database and stores a reference to it in the database ivar we created — unless the operation fails of course.

Once our SQLiteDatabase has been initialised with a database, typically the next step is to perform a query. As with init, the goal of the method is to wrap the C code into object-oriented Objective-C classes:

 (NSArray *)performQuery:(NSString *)query {
    sqlite3_stmt *statement = nil;
    const char *sql = [query UTF8String];
    if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) != SQLITE_OK) {
        NSLog(@"[SQLITE] Error when preparing query!");
    } else {
        NSMutableArray *result = [NSMutableArray array];
        while (sqlite3_step(statement) == SQLITE_ROW) {
            NSMutableArray *row = [NSMutableArray array];
            for (int i=0; i<sqlite3_column_count(statement); i++) {
                int colType = sqlite3_column_type(statement, i);
                id value;
                if (colType == SQLITE_TEXT) {
                    const unsigned char *col = sqlite3_column_text(statement, i);
                    value = [NSString stringWithFormat:@"%s", col];
                } else if (colType == SQLITE_INTEGER) {
                    int col = sqlite3_column_int(statement, i);
                    value = [NSNumber numberWithInt:col];
                } else if (colType == SQLITE_FLOAT) {
                    double col = sqlite3_column_double(statement, i);
                    value = [NSNumber numberWithDouble:col];
                } else if (colType == SQLITE_NULL) {
                    value = [NSNull null];
                } else {
                    NSLog(@"[SQLITE] UNKNOWN DATATYPE");
                }

                [row addObject:value];
            }
            [result addObject:row];
        }
        return result;
    }
    return nil;
}

Let’s take a look at what’s going on here. The first thing is that the query NSString is turned into a C-style string (you may have noticed we did this in the initWithPath: method too!). Next, the sqlite3_prepare_v2() function is used to query the database. Providing that is successful, an NSMutableArray is created which holds all the returned rows. Each row itself consists of an NSMutableArray with each element being another column in the row.

Each of the types are then converted from their underlying C types (obtained from their SQLite types) into their corresponding Objective-C types. For example, SQLITE_TEXT is an immutable string, which is represented in C as a const unsigned char * — This is then converted to the more familiar NSString. The same goes for integers, floats and nulls which are all converted to their object counterparts (Pay attention here! Since NSArray only supports objects, not primitives or nils, NSNumber is used to represent numbers and [NSNull null] for nil — watch out for that!)

The returned result is then a 2-dimensional array of rows and columns. To see how this would be used in an application, consider the following example:

    SQLiteDatabase *database = [[SQLiteDatabase alloc] initWithPath:@"houses.sqlite"];
    NSArray *result = [database performQuery:@"SELECT * FROM houses WHERE cost > 500000 AND location = 'London'"];
    for (NSArray *row in result) {
        int houseID = [[row objectAtIndex:0] intValue];
        NSString *address = [row objectAtIndex:5];
        NSLog(@"%d -- %@", houseID, address);
    }

Of course, there is a lot that could be done to improve this code (mainly improved error handling and additional features) but this should at least get you started in your adventures with SQLite!

This entry was posted on Thursday, January 26th, 2012 at 2:19 pm and is filed under iOS Development. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.