- (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

// 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