I haven’t found a whole lot about integrating SQLite3 databases in a Cocoa application. There’s no native Objective-C API for SQLite except for Core Data. Core Data may be good but if you want to access the database in your own way you will want to program directly to the SQLite APIs.
You will be programming in C (unfortunately?) but the API is very easy and integrating it into your Cocoa application can be as easy as writing your own helper wrappers around the C API.
The examples I’ll be talking about in this post assume the following database schema:
CREATE TABLE volumes('volumeID' INTEGER PRIMARY KEY AUTOINCREMENT, 'volumeName' TEXT, 'indexed' INTEGER(1));
CREATE TABLE files('volumeID' INTEGER, 'fileName' TEXT, 'fileType' TEXT, 'fileExtension' TEXT, 'fileSize' INTEGER, 'creatorCode' TEXT, 'modDate' TEXT, 'fullPath' TEXT);
CREATE INDEX fileNameIndex ON files(fileName);
CREATE INDEX fullPathIndex ON files(fullPath);
If you are on Leopard you are in luck because SQLite is built in. You have command line access to the sqlite3 binary in your $PATH. Check man sqlite3 for detailed instructions. Using the above schema, you can create your database in the command line by using the command:
cd your_destination_dir
sqlite3 yourdatabase.extension
Change the above name and extension you want to use for the database. The extension doesn’t matter. Copy and paste in your schema and hit enter. Now you have your database created. Type “.quit” to quit the program when you’re done.
In the Finder, you will have to type Command+G and then type in /usr/lib/
Alternatively, if you are using XCode 3.x and using the default install location for the tools, you can go to /Developer/SDKs/MacOSX10.5.sdk/usr/lib/
or
/Developer/SDKs/MacOSX10.5.sdk/usr/lib/
Drag the file libsqlite3.0.dylib into your “Other Frameworks” group in XCode and answer yes to copy to destination folder if needed. This will copy the libsqlite3.0.dylib file to your project directory. Probably not needed but it doesn’t hurt anything.
An example function you can write to open a database is this:
-(BOOL)openDatabaseAtPath:(NSString*)path
{
NSFileManager *fileManager = [NSFileManager defaultManager];
//check if file is already there
if([fileManager fileExistsAtPath:path])
{
if(sqlite3_open([path UTF8String], &db) == SQLITE_OK)
{
NSLog(@"Database was opened at %@", path);
dbOpened = YES;
return YES;
}
}
NSLog(@"Database not found at location %@", path);
return NO;
}
This code will take an NSString* which is the full path to your database file. The reason you want to check if the file exists is because the sqlite3_open function will create the file if it doesn’t exist. This is probably not what you want, so first you may want to check if the file is there before opening it.
Example code to do a SELECT on a table:
-(int)volumeIsIndexed:(NSString*)volumnName
{
if(!dbOpened)
return NO;
const char *sql = [[NSString stringWithFormat:@"SELECT volumeID FROM volumes WHERE volumeName ='%@'", volumnName] cStringUsingEncoding:NSUTF8StringEncoding];
sqlite3_stmt *statement;
BOOL ret;
if (sqlite3_prepare_v2(db, sql, -1, &statement, NULL) == SQLITE_OK)
{
int result = sqlite3_step(statement);
if(result == SQLITE_ROW)
ret = sqlite3_column_int(statement, 0);
else
ret = NO;
}
sqlite3_finalize(statement);
return ret;
}Example code to do an INSERT on your table:
-(int)addVolumeToDatabaseWithVolumeName:(NSString*)name
{
if(!dbOpened)
return -1;
int volumeID = [self volumeIsIndexed:name];
//volume is already in the database so just return the primary key for it
if(volumeID > 0)
return volumeID;
const char* sql = [[NSString stringWithFormat:@"INSERT INTO volumes(volumeName, indexed) VALUES('%@', 1);", name] cStringUsingEncoding:NSUTF8StringEncoding];
sqlite3_stmt *statement;
if (sqlite3_prepare_v2(db, sql, -1, &statement, NULL) == SQLITE_OK)
{
int result = sqlite3_step(statement);
sqlite3_finalize(statement);
if(result != SQLITE_ERROR)
return sqlite3_last_insert_rowid(db);
}
return -1;
}
As you can see, integrating SQLite3 in your Cocoa application is not that hard. Unfortunately at the time of this writing there’s not a lot that I could find of integrating it into your Cocoa code. Really, because Cocoa is a super set of C, you are not doing anything special with this. But this will give you an example of how easily you can integrate your own embedded database with your application without having to go through Core Data.