Tuesday, March 22nd, 2005

Hello there! FMDB has moved to Google Code:
http://code.google.com/p/flycode/source/browse/trunk/fmdb.

SQLite, the kickass embeddable SQL engine written in c, just got bumped up to version 3.2.0 with a couple of new features. So I thought I would share some cocoa classes that wrap its basic functionality.

That's right, YACWFS. (Yet another cocoa wrapper for sqlite).

Mine is pretty simple, in the spirit of Blackhole Media's bindings for sqlite version 2.x. I can think of 3 other cocoa sqlite wrappers off the top of my head that probably do a fine job, but they just don't do what I want them to do. They provide a little bit too much abstraction for me, I don't mind writing my own queries by hand. Maybe it's because I've been using sql for about 10 years now...

FMDatabase (that's the main class) treats all insert, update, or select queries as prepared statements, and you bind values to placeholders in the statement. This makes things a little bit faster for the database engine, and it also means that you don't have to escape all those single quote marks ('). And since sqlite 3 now handles blobs natively, you can just pass a NSData instance as part of your statement, and it'll do the right thing. No base64 encoding here.

Of course, if you want handle your blobs as base64 or escape your tic marks in big sql statements you still can. I am going to question your sanity however.

It also has a bit of jdbc flavor to it... if you like that sort of thing. Which I do. Did I just lose you?

Anyway, here is the download: fmdb.zip. And here is a little taste:

// Here's how we create the db.
FMDatabase* db = [FMDatabase databaseWithPath:@"/tmp/tmp.db"];
if (![db open]) {
    NSLog(@"Could not open db.");
}


// let's create a table next.  "a" for text, "d" for dates
[db executeUpdate:@"create table test (a text, d double)", nil];

// wrap multiple updates or inserts in a transaction, since that
// makes things go super fast.
[db beginTransaction];

// look!  I put in a ', and I'm not escaping it!
[db executeUpdate:@"insert into test (a, d) values (?, ?)",
        @"hi'", [NSDate date], nil];

// we end all our statements with nil so FMDatabase knows when to
// stop asking for values
... 

// end our transaction
[db commit];


// pull some data out
FMResultSet *rs = [db executeQuery:@"select * from test where a = ?", @"hi'",  nil];
while ([rs next]) {
    NSLog(@"%@, %@", [rs stringForColumn:@"a"],
                     [rs dateForColumn:@"d"]);
}
// close the result set. (it'll also close when it's dealloc'd if we are in
// an autorelease pool)
[rs close]; 

// close the database
[db close];

That's the basic cycle (create, insert, select).

Here's how blobs work. In this example we just read in Safari's icon, and then spit it out to /tmp.

[db executeUpdate:@"create table blobTable (a text, b blob)", nil];
NSData *d = [NSData dataWithContentsOfFile:
                @"/Applications/Safari.app/Contents/Resources/compass.icns"];

// just pass the nsdata as it is.
[db executeUpdate:@"insert into blobTable (a, b) values (?,?)", @"safari's compass", d, nil];

FMResultSet *rs = [db executeQuery:@"select b from blobTable where a = ?", @"safari's compass",  nil];
if ([rs next]) {
    d = [rs dataForColumn:@"b"];
    [d writeToFile:@"/tmp/compass.icns" atomically:NO];
}
[rs close];

Bugs, comments and patches accepted of course.

-- posted 6:18 pm