June 28, 2008

iPhone Objective-C SQLite development

Posted in iPhone development tagged , , , , , , , , , , , , , , at 5:46 pm by tetontech

The code included here is now available in QuickConnectiPhone 1.1.3 and can be downloaded from the sourceforge repository. A 1.5 Beta 8 version is also available.  The Beta 8 version is much more flexible and stable than the simple code you see here.

I have been working on the Objective-C version of QuickConnect.  Included in it is a simple wrapper for interacting with the SQLite database.  QuickConnectOC will, when I have finished testing it, work for both the iPhone and for Mac applications.  Of course on a Mac you could use the CoreData Framework but this is not available, from what I can tell, as a framework for the iPhone.  

This SQLiteDataAccess class depends on Shawn Ericksons’ good work in creating an easy to use class for creating singletons in Objective-C, FTSWAbstractSingleton.

Since SQLiteDataAccess is a singleton, you can call it from anywhere in your code without passing pointers to it all over the place.  It is also thread safe for those who are creating multi-threaded apps.

Here is an example of me using it to retrieve some information from a database for an iPhone eBook application framework I am writing.

I have just refreshed the code on this page to include some bug fixes.

SQLiteDataAccess *theDatabase = [SQLiteDataAccess getInstance:@"data.sqlite" isWriteable:FALSE];
NSString* SQL = @"Select * from book_info";
DataAccessResult *aDAResult = [theDatabase getData:SQL withParameters:nil];
/*
* Retrieve the data from the DAResult and return it.
* You do not want to return the DAResult since that would tightly couple
* the database implementation to many other areas of the application.
*
* If you are going to use the error checking or the other information
* found in the DAResult object you need to use it here.
*/
//we are only interested in the first row. That is all there should be anyway.
NSArray *data = [[aDAResult results] objectAtIndex:0];
[aDAResult release];

As you can see it is simple to use.  When you want to retrieve information you use the ‘getData’ method and when you want to modify the database in any way you use the ‘setData’ method.  This is the same as the other QuickConnect DataAccess classes in the various languages and for various databases.  It is also the same API as when using the ServerAccessObject JavaScript class, an AJAX wrapper, in the QuickConnectiPhone framework.  

I am posting here the incomplete and only partially tested source code so that you can see how to use prepared statements, etc. with SQLite and Objective-C.  This code has only been tested for simple SQL statements that are not prepared statements or transactions at this time.  I expect that the current code is close to being ‘right’ but can’t guarantee it.  I expect to soon have it tested to a degree that I am more comfortable with.

When I am more confident in the code I will post it as part of QuickConnectOC on the QuickConnect sourceForge web site.

When complete it will also include such items as helper functions that return the auto-generated id for calls that INSERT INTO tables with auto-incrementing id fields, etc.

Header File

 
// SQLiteDataAccess.h

/*
Copyright 2008 Lee S. Barney

This file is part of QuickConnectOC.

QuickConnectOC is free software: you can redistribute it and/or modify
it under the terms of the GNU Lesser General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.

QuickConnectOC is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU Lesser General Public License for more details.

You should have received a copy of the GNU Lesser General Public License
along with QuickConnectOC. If not, see .

*/

#import
#import
#import "DataAccessResult.h"
#import "FTSWAbstractSingleton.h"

@interface SQLiteDataAccess : FTSWAbstractSingleton {
// Opaque reference to the SQLite database.
sqlite3 *database;
// Opaque reference to the dictionary that maps strings to binding function names
NSDictionary *bindTypeDictionary;
}

- (DataAccessResult*)getData:(NSString*)SQL withParameters:(NSArray*)parameters;
- (DataAccessResult*)setData:(NSString*)SQL withParameters:(NSArray*)parameters;
- (DataAccessResult*)startTransaction;
- (void)endTransaction;
- (void)rollback;
- (void)close;

+ (SQLiteDataAccess*)getInstance: (NSString*) dbName isWriteable: (BOOL) isWriteable;

 

 

 

 

@end
Implementation file
// SQLiteDataAccess.m

/*
Copyright 2008 Lee S. Barney

This file is part of QuickConnectOC.

QuickConnectOC is free software: you can redistribute it and/or modify
it under the terms of the GNU Lesser General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.

QuickConnectOC is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU Lesser General Public License for more details.

You should have received a copy of the GNU Lesser General Public License
along with QuickConnectOC. If not, see .

*/

#import "SQLiteDataAccess.h"
#import "QCParameter.h"

// Private interface for AppDelegate - internal only methods.
@interface SQLiteDataAccess (Private)
- (DataAccessResult*)dbAccess:(NSString*)SQL withParameters:(NSArray*)parameters treatAsChangeData:(BOOL)treatAsChangeData;
//internal bind methods
- (int) bind_blob:(sqlite3_stmt*)statement withIndex:(int) withBindVariable:(id)aVariable;
- (int) bind_double:(sqlite3_stmt*)statement withIndex:(int) withBindVariable:(id)aVariable;
- (int) bind_int:(sqlite3_stmt*)statement withIndex:(int) withBindVariable:(id)aVariable;
- (int) bind_text:(sqlite3_stmt*)statement withIndex:(int) withBindVariable:(id)aVariable;
- (int) bind_zeroblob:(sqlite3_stmt*)statement withIndex:(int) withBindVariable:(id)aVariable;
- (int) bind_null:(sqlite3_stmt*)statement withIndex:(int) withBindVariable:(id)aVariable;
/*
* private initialization method
*/
- (SQLiteDataAccess*)initWithDatabase: (NSString*) dbName isWriteable: (BOOL) isWriteable;

@end

// SQLiteDataAccess.m

/*
Copyright 2008 Lee S. Barney

This file is part of QuickConnectOC.

QuickConnectAJAX is free software: you can redistribute it and/or modify
it under the terms of the GNU Lesser General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.

QuickConnectAJAX is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU Lesser General Public License for more details.

You should have received a copy of the GNU Lesser General Public License
along with QuickConnectAJAX. If not, see .

*/

#import "SQLiteDataAccess.h"
#import "QCParameter.h"

// Private interface for AppDelegate - internal only methods.
@interface SQLiteDataAccess (Private)
- (DataAccessResult*)dbAccess:(NSString*)SQL withParameters:(NSArray*)parameters treatAsChangeData:(BOOL)treatAsChangeData;
//internal bind methods
- (int) bind_blob:(sqlite3_stmt*)statement withIndex:(int) withBindVariable:(id)aVariable;
- (int) bind_double:(sqlite3_stmt*)statement withIndex:(int) withBindVariable:(id)aVariable;
- (int) bind_int:(sqlite3_stmt*)statement withIndex:(int) withBindVariable:(id)aVariable;
- (int) bind_text:(sqlite3_stmt*)statement withIndex:(int) withBindVariable:(id)aVariable;
- (int) bind_zeroblob:(sqlite3_stmt*)statement withIndex:(int) withBindVariable:(id)aVariable;
- (int) bind_null:(sqlite3_stmt*)statement withIndex:(int) withBindVariable:(id)aVariable;
/*
* private initialization method
*/
- (SQLiteDataAccess*)initWithDatabase: (NSString*) dbName isWriteable: (BOOL) isWriteable;

@end

@implementation SQLiteDataAccess

/*
* static method to implement singleton pattern
*/
+ (SQLiteDataAccess*)getInstance: (NSString*) dbName isWriteable:(BOOL)isWriteable{
/*SQLiteDataAccess* mySelf = [self singleton];
return [mySelf initWithDatabase:dbName isWriteable:isWriteable];
*/
//since this line is declared static it will only be executed once.
static SQLiteDataAccess *mySelfSQLiteDA = nil;

@synchronized([SQLiteDataAccess class]) {
if (mySelfSQLiteDA == nil) {
mySelfSQLiteDA = [SQLiteDataAccess singleton];
mySelfSQLiteDA = [mySelfSQLiteDA initWithDatabase:dbName isWriteable:isWriteable];
}
}
return mySelfSQLiteDA;
}
- (SQLiteDataAccess*)initWithDatabase: (NSString*) dbName isWriteable: (BOOL) isWriteable{
//if (self = [super init]) {
NSString *path = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"data.sqlite"];
if(isWriteable){

// The application ships with a default database in its bundle. If anything in the application
// bundle is altered, the code sign will fail. We want the database to be editable by users,
// so we need to create a copy of it in the application's Documents directory.

BOOL success;
NSFileManager *fileManager = [NSFileManager defaultManager];
NSError *error;
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDirectory = [paths objectAtIndex:0];
NSString *writableDBPath = [documentsDirectory stringByAppendingPathComponent:@"data.sqlite"];
NSLog(writableDBPath);
success = [fileManager fileExistsAtPath:writableDBPath];
if (!success){
// The writable database does not exist, so copy the default to the appropriate location.
success = [fileManager copyItemAtPath:path toPath:writableDBPath error:&error];
if (!success) {
NSAssert1(0, @"Failed to create writable database file with message '%@'.", [error localizedDescription]);
return nil;
}
}
path = writableDBPath;
}
sqlite3 *aDatabase;
NSLog(@"path: %@",path);
if (sqlite3_open([path UTF8String], &aDatabase) == SQLITE_OK) {
NSLog(@"database opened");
self->database = aDatabase;

NSLog(@"assigned");
//create the dictionary that maps parameter types to bind method calls
NSArray *keys = [NSArray arrayWithObjects:@"b", @"d", @"i", @"i63", @"t", @"t16", @"z", @"nil",nil];
NSLog(@"keys");
NSArray *values = [NSArray arrayWithObjects:@"bind_blob:withIndex:withBindVariable", @"bind_double:withIndex:withBindVariable",
@"bind_int:withIndex:withBindVariable", @"bind_int64:withIndex:withBindVariable",
@"bind_text:withIndex:withBindVariable", @"bind_text16:withIndex:withBindVariable",
@"bind_zeroblob:withIndex:withBindVariable", @"bind_null:withIndex:withBindVariable", nil];

NSLog(@"keys and objects ready");
NSDictionary *aDictionary = [NSDictionary dictionaryWithObjects:values forKeys:keys];

NSLog(@"dictionary ready");
self->bindTypeDictionary = aDictionary;
NSLog(@"dictionary set");
NSLog(@"successfully loaded database");
return self;
}
else{
//since we failed to open the database completely close it down to make sure that everyting is cleaned up
sqlite3_close(aDatabase);
NSAssert1(0, @"Failed to open database with message '%s'.", sqlite3_errmsg(aDatabase));
}
//}
return nil;
}

- (DataAccessResult*)getData:(NSString*)SQL withParameters:(NSArray*)parameters{
NSLog(@"getting data");
return [self dbAccess:SQL withParameters:parameters treatAsChangeData:FALSE];
}

- (DataAccessResult*)setData:(NSString*)SQL withParameters:(NSArray*)parameters{
return [self dbAccess:SQL withParameters:parameters treatAsChangeData:TRUE];
}

- (DataAccessResult*)dbAccess:(NSString*)SQL withParameters:(NSArray*)parameters treatAsChangeData:(BOOL)treatAsChangeData{
NSLog(@"in dbAccess");
DataAccessResult *theResult;
theResult = [DataAccessResult alloc];
if(parameters != nil && [parameters count] > 0){
//make sure the the number of parameters is equal to the number of qestion marks in the SQL string
}
NSMutableArray* results = [[NSMutableArray alloc] initWithCapacity:0];
int numResultColumns = 0;
sqlite3_stmt *statement = nil; // Preparing a statement compiles the SQL query into a byte-code program in the SQLite library.
// The third parameter is either the length of the SQL string or -1 to read up to the first null terminator.
const char* SQLChar = [SQL UTF8String];
NSLog(@"about to prepare %@",SQL);
if (sqlite3_prepare_v2(database, SQLChar, -1, &statement, NULL) == SQLITE_OK) {
if(!treatAsChangeData){
NSLog(@"columns not changing");
//retrieve the number of columns in the result of the execution of the select statement
numResultColumns = sqlite3_column_count(statement);
NSLog(@"numRecentColumns: %i",numResultColumns);
NSMutableArray *fieldNames = [[NSMutableArray alloc] initWithCapacity:0];
NSAutoreleasePool *pool = [[NSAutoreleasePool alloc] init];
for(int i = 0; i < numResultColumns; i++){
const char *name = sqlite3_column_name(statement, i);
NSString * columnName = [[NSString alloc]initWithCString:name encoding:NSUTF8StringEncoding];
[fieldNames addObject:columnName];
}
[theResult setFieldNames:fieldNames];

[pool release];
}
if(parameters != nil){
int numParams = [parameters count];
NSAutoreleasePool *pool = [[NSAutoreleasePool alloc] init];
for (int i = 0; i < numParams; i++) {
QCParameter *parameter = [parameters objectAtIndex:i];
NSString *name = [parameter name];
id value = [parameter value];
NSString *funcType = [bindTypeDictionary objectForKey:name];
SEL aSelector = NSSelectorFromString(funcType);
//bind the variables here
objc_msgSend(self, aSelector, statement, i, value);

}
[pool release];
}
NSMutableArray *results = [[NSMutableArray alloc] initWithCapacity:0];

NSAutoreleasePool *pool = [[NSAutoreleasePool alloc] init];
// We "step" through the results - once for each row.
// if the statement executed is not a select statement sqlite3_step will return SQLITE_DONE on the first iteration.
while (sqlite3_step(statement) == SQLITE_ROW) {
if([theResult columnTypes] == nil){
NSMutableArray *columnTypes = [[NSMutableArray alloc] initWithCapacity:0];
NSAutoreleasePool *pool = [[NSAutoreleasePool alloc] init];
for(int i = 0; i < numResultColumns; i++){
NSNumber * columnType = [NSNumber numberWithInt:sqlite3_column_type(statement,i)];
[columnTypes addObject:columnType];
}
[theResult setColumnTypes:columnTypes];
[pool release];
}
NSMutableArray *row = [[NSMutableArray alloc] initWithCapacity:numResultColumns];
/*
* Iterate over all of the columns. Determine their type and retrieve its value
* SQLITE_INTEGER
* SQLITE_FLOAT
* SQLITE_BLOB
* SQLITE_NULL
* SQLITE_TEXT
*/

for(int i = 0; i < numResultColumns; i++){
int type = [[[theResult columnTypes] objectAtIndex:i] intValue];
if(type == SQLITE_INTEGER){
NSLog(@"integer: %i",sqlite3_column_int(statement, i));
NSNumber *aNum = [[NSNumber alloc] initWithInt:sqlite3_column_int(statement, i)];
[row addObject:aNum];
}
else if(type == SQLITE_FLOAT){
NSLog(@"float");
NSNumber *aFloat = [[NSNumber alloc] initWithFloat:sqlite3_column_double(statement, i)];
[row addObject:aFloat];
}
else if(type == SQLITE_TEXT){
NSLog(@"text");
NSString *aText = [[NSString alloc]initWithCString:sqlite3_column_text(statement, i) encoding:NSASCIIStringEncoding];
[row addObject:aText];
}
else if(type == SQLITE_BLOB){
NSLog(@"blob");
NSData *aData = [[NSData alloc]dataWithBytes:sqlite3_column_blob(statement, i) length:sqlite3_column_bytes(statement,i)];
[row addObject:aData];

}
else{//if([[columnTypes objectAtIndex:i] intValue] == SQLITE_NULL){
[row addObject:@"null"];
}
}
[results addObject:row];
}
[pool release];
[theResult setResults:results];
}
else{
NSString *error;
error = [[NSString alloc]initWithCString:sqlite3_errmsg(database) encoding:NSASCIIStringEncoding];
[theResult setErrorDescription:error];
[error release];
}
// "Finalize" the statement - releases the resources associated with the statement.
sqlite3_finalize(statement);

return theResult;
}

- (DataAccessResult*)startTransaction{
NSString* sql = @"BEGIN EXCLUSIVE TRANSACTION";
return [self setData:sql withParameters:nil];
}
- (void)endTransaction{
NSString* sql = @"COMMIT";
[self setData:sql withParameters:nil];

}
- (void)rollback{
NSString* sql = @"ROLLBACK";
[self setData:sql withParameters:nil];
}

- (void)close{
if (sqlite3_close(database) != SQLITE_OK) {
NSAssert1(0, @"Error: failed to close database with message '%s'.", sqlite3_errmsg(database));
}
}

// internal bind methods

- (int) bind_blob:(sqlite3_stmt*)statement withIndex:(int)parameterIndex withBindVariable:(NSData*)aVariable{

if (![aVariable respondsToSelector:@selector(lengthOfBytes:)]) {
return -1;
}
//by default have the library make a copy, SQLITE_TRANSIENT, since we don't know if the variable may be changed
//by something else in the application.
return sqlite3_bind_blob(statement, parameterIndex, aVariable, [aVariable length], SQLITE_TRANSIENT);
}
- (int) bind_double:(sqlite3_stmt*)statement withIndex:(int)parameterIndex withBindVariable:(double)aVariable{
return sqlite3_bind_double(statement, parameterIndex, aVariable);
}
- (int) bind_int:(sqlite3_stmt*)statement withIndex:(int)parameterIndex withBindVariable:(int)aVariable{
return sqlite3_bind_int(statement, parameterIndex, aVariable);
}
- (int) bind_text:(sqlite3_stmt*)statement withIndex:(int)parameterIndex withBindVariable:(id)aVariable{
//assume an ASCII string
return sqlite3_bind_blob(statement, parameterIndex, aVariable, [aVariable lengthOfBytesUsingEncoding:NSASCIIStringEncoding], SQLITE_TRANSIENT);

}
- (int) bind_zeroblob:(sqlite3_stmt*)statement withIndex:(int)parameterIndex withBindVariable:(int)aVariable{
return sqlite3_bind_zeroblob(statement, parameterIndex, aVariable);
}
- (int) bind_null:(sqlite3_stmt*)statement withIndex:(int)parameterIndex withBindVariable:(id)aVariable{
return sqlite3_bind_null(statement, parameterIndex);
}

@end

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: