Devon’s Cocoa Blog

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/

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.

Note: This example web services uses the nusoap helper classes to do all the back end soap generation for me. The included php file requires this to work. Download here: nusoap

I’m now hosting the whole web service minus the sending of the SMS message since it costs money.  Point your SOAP client to Web service

Updated WSDL at WSDL

1. First you need to have access to your WSDL file if available. This will make your job much much easier later on. Presumably you have the developer tools installed. Go into Terminal.app and change directory(cd) to your project directory.  If you’re using my above code and you want to use your own web server, you MUST download nusoap and put the whole folder in the same directory as the php file that implements the web service.  Also, if you’re going to generate your own stub files from my WSDL file you can change the URL to your own server by changing the line <soap:address location=”http://localhost/webservice/se411webservice.php”/> to <soap:address location=”http://www.yourhost.com/yourdirectory/se411webservice.php”/>

2. Execute /usr/bin/WSMakeStubs -x Obj-C -name “WebService” -url http://www.yourhost.com/webservice.wsdl
This assumes that you have installed XCode 3.x and installed the command line tools. If not then WSMakeStubs will be in DEVELOPER_DIR/usr/bin where DEVELOPER_DIR is where you installed XCode and the related IDE applications to.

In the above command, change your URL to that of your WSDL file which can also be(I think) just the path on disk to the file as well.  Also, change your -name argument to be whatever you want your Objective-C files to be called.  Using “Webservice” will output “Webservice.h” and “Webservice.m”.

3. Now you have two Objective-C files that you can add to your XCode project.  I assume you know how to do this.

4.  Check the attached WSDL file to check the functions that it implements.  Also check the example php file that implements the web service.  Example SOAP web service WSDL file     Example PHP SOAP implementation

5.  The attached web service implements a function called getAirportInformationByAirportCode.  In your newly generated .h and .m files you will have a class for each function defined in the WSDL.  The getAirportInformationByAirportCode function takes a single parameter that is a 3 character airport code like “SFO”, “YXU”, etc..  

6.  All you have to do now in your Objective-C code is the following:

getAirportInformationByAirportCode* webservice = [[getAirportInformationByAirportCode alloc] init];
 [webservice setParameters:[NSDictionary dictionaryWithObject:[airportCode stringValue] forKey:@"airportCode"]];

This creates your web service and sets the parameters.  Your parameter can be from an NSTextField or whatever input you want.

7.  In your WSMakeStubs generated files, look for the functions -(id)resultValue and change it to this if you would like which will return the entire result to your code when you call it:

 - (id) resultValue
{
    return [super getResultDictionary];//return [[super getResultDictionary] objectForKey: @"parameters"];
}

The reason I do this is because then in your calling code you can just do this:

 NSDictionary* results = [[webservice resultValue] objectForKey:@"/Result"];

The commented out code would return nothing unless your result gets returned in a very specific format.  My implementation just returns a raw SOAP message and the generated code doesn’t seem to like that.  So commenting out the code in the resultValue function allows you to receive the entire result back and you can do what you please after wards.