11:03 Wed, 07 May 2008 PDT -0700

PLDatabase - Simple Objective-C SQLite Library

Jonathan and I just put together Plausible Database, a small Objective-C SQLite library for our iPhone development. The API is intended to allow support for additional databases, but SQLite is clearly the primary target on the phone.

The 1.0 "preview release", including doxygen-generated API documentation, is available via Google Code (BSD license). The code has 100% unit test coverage, and should work for both Mac OS X and NDA-covered SDKs of an indeterminate nature. It's a relatively small bit of code, but we'd appreciate any feedback on the API or implementation -- including your thoughts on supporting future database back-ends, such as PostgreSQL. The API was inspired by public domain SQLite code (FMDB) from Gus Mueller of Flying Meat.

We have used the library to implement a small transactional schema migration library for our applications -- SQLite has a super handy per-database "user_version" which can be used for this.

Here are some usage examples:

/* Create and open an in-memory database */
PLSqliteDatabase *db = [[PLSqliteDatabase alloc] initWithPath: @":memory:"];
if (![db open])
    NSLog(@"Could not open database");
/* Create a table and add some data */
if (![db executeUpdate: @"CREATE TABLE example (id INTEGER)"])
    NSLog(@"Table creation failed");
 
if (![db executeUpdate: @"INSERT INTO example (id) VALUES (?)", [NSNumber numberWithInteger: 42]])
    NSLog(@"Data insert failed");
/* Execute a query */
NSObject *results = [db executeQuery: @"SELECT id FROM example WHERE id = ?", [NSNumber numberWithInteger: 42]];
while ([results next]) {
    NSLog(@"Value of column id is %d", [results intForColumn: @"id"]);
}
 
/* Failure to close the result set will not leak memory, but may
 * retain database resources until the instance is deallocated. */
[results close];

[/code/iphone] permanent link