Objective-C SQLite wrapper

Easy to use object-oriented Objective-C wrapper around SQLite (http://sqlite.org/).

If you use these classes in your project, I’d love to know about it :) Let me know: zoran@simicweb.com This code is otherwise released under the MIT license (http://www.opensource.org/licenses/mit-license.php)

How to add it to your project

• Add ESDatabase.h and ESDatabase.m to your project

• Add libsqlite3.dylib to your target

  • In XCode 4, click on your project in the project navigator

  • Select your target, then the "Build Phases" tab

  • in "Link Binary With Libraries" add libsqlite3.dylib

• Define ES_DEBUG for your Debug configuration

Overview

The goal of this SQLite wrapper is to:

• provide an Objective-C interface to SQLite

• be as good as FMDatabase (http://github.com/ccgus/fmdb)

• allow to automatically copy a database from the .app bundle to the user directory on the device

• there are 4 classes in the package: ESDatabase, ESStatement, ESResultSet and ESColumnInfo

• you can create 4 kinds of ESDatabase objects:

  • read-write: the database is copied from the .app bundle to the user document folder on the device

  • read-only: the database remains in the .app bundle, you can only read from it

  • cache: the database is created once (when object is first opened) in the user document folder on the device

  • "in memory": same as "cache", but with a temporary in memory only DB

• nice debugging support that is designed to disappear completely once you build for release

How to use this wrapper

• First, create a ESDatabase object:

#import "ESDatabase.h"
ESDatabase *db = [ESDatabase newReadWrite:@"mydb.sqlite"];

• Then, call open and check if it succeeded

if ([db open]) {
	// OK, can use 'db'
} else {
	// At this point, you will see an explanation as to why the DB
	// couldn't be open in the debugger console (but in debug mode only)
	[db release];
}

• The call to open above will copy your database from the .app bundle to the documents directory on the device if needed

• You can then call exectute, prepare etc (see code samples below and the interface in ESDatabase.h)

• If ES_DEBUG is defined, meaningful error messages are output in the debugger console and ESDatabase generates an exception that will stop the debugger as soon as an error is detected

• When ES_DEBUG is defined, you have 3 properties at your disposal to help track problems: logError, crashOnError and traceExecution

• In release mode (ie: ES_DEBUG is not defined), the 3 properties mentioned above do nothing

• Setting logLevel to YES will generate messages in the debugger console (this is the default when ES_DEBUG is defined)

• Setting crashOnError to YES will generate a hard crash as soon as an error is detected so you can inspect it right away in the debugger (this is the default when ES_DEBUG is defined)

• 4 trace levels are also available (none by default), you can have ESDatabase generate a log message when:

  • objects are being released (EST_RELEASE)

  • when preparing/opening/closing statement (EST_LIFE)

  • when running queuries (EST_QUERY)

  • when file operations are performed, such as DB copied to device (EST_FILE_OPERATIONS)

• For example, to have a db object not crash on error, but generate info when preparing/opening/closing statements and copying the DB file to the device you would write:

db.crashOnError = NO
db.traceExecution = EST_LIFE | EST_FILE_OPERATIONS

Sample code

ESDatabase *db = [ESDatabase newReadWrite:@"mydb.sqlite"];
[db open];
ESResultSet *r = [db execute:@"SELECT name,age,birthday FROM person"];
while ([r next]) {
	NSLog(@"name: %@, age: %i", [r stringValue:0], [r intValue:1]);
}

ESStatement *sthr = [db prepare:@"SELECT name,age,birthday FROM person"];
ESResultSet *r = [sthr execute];
while ([r next]) {
	NSLog(@"name: %@, age: %i", [r stringValue:0], [r intValue:1]);
}

// Convenience update feature in ESDatabase
[db execute:@"UPDATE age=?,birthday=? FROM person WHERE name=?", ESDbInt(21), date, @"Joe", nil];

// Prepare a statement to execute many times later
ESStatement *sthu = [db prepare:@"UPDATE age=?,birthday=? FROM person WHERE name=?"];
[sthu execute ESDbInt(21), date, @"Joe", nil];

// Example cache DB:
ESDatabase *db = [ESDatabase newCache:@"person.sqlite"];
[db setSchema:[NSArray arrayWithObjects:
			   @"CREATE TABLE person (name VARCHAR(8) NOT NULL,age INTEGER NOT NULL,PRIMARY KEY (name))",
			   @"CREATE UNIQUE INDEX person_name ON person (name ASC)",
			   @"INSERT INTO person VALUES ('Johnny', 24)",
			   @"INSERT INTO person VALUES ('Fred', 5)",
			   nil]];
// The statement given above will be performed only once: the first time the DB is created