Created
October 2, 2013 09:54
-
-
Save iantearle/6791443 to your computer and use it in GitHub Desktop.
Extending the Plausible Database in Titanium to allow for SQLite distance function.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
* Copyright (c) 2008 Plausible Labs Cooperative, Inc. | |
* All rights reserved. | |
* | |
* Redistribution and use in source and binary forms, with or without | |
* modification, are permitted provided that the following conditions | |
* are met: | |
* 1. Redistributions of source code must retain the above copyright | |
* notice, this list of conditions and the following disclaimer. | |
* 2. Redistributions in binary form must reproduce the above copyright | |
* notice, this list of conditions and the following disclaimer in the | |
* documentation and/or other materials provided with the distribution. | |
* 3. Neither the name of the copyright holder nor the names of any contributors | |
* may be used to endorse or promote products derived from this | |
* software without specific prior written permission. | |
* | |
* THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" | |
* AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE | |
* IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE | |
* ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE | |
* LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR | |
* CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF | |
* SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS | |
* INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN | |
* CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) | |
* ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE | |
* POSSIBILITY OF SUCH DAMAGE. | |
*/ | |
#import <sqlite3.h> | |
extern NSString *PLSqliteException; | |
@interface PLSqliteDatabase : NSObject <PLDatabase> { | |
@private | |
/** Path to the database file. */ | |
NSString *_path; | |
/** Underlying sqlite database reference. */ | |
sqlite3 *_sqlite; | |
} | |
+ (id) databaseWithPath: (NSString *) dbPath; | |
- (id) initWithPath: (NSString*) dbPath; | |
- (BOOL) open; | |
- (BOOL) openAndReturnError: (NSError **) error; | |
- (int64_t) lastInsertRowId; | |
- (NSString *) path; | |
- (sqlite3 *) sqliteDB; | |
@end | |
#ifdef PL_DB_PRIVATE | |
@interface PLSqliteDatabase (PLSqliteDatabaseLibraryPrivate) | |
- (int) lastErrorCode; | |
- (NSString *) lastErrorMessage; | |
- (BOOL) populateError: (NSError **) result withErrorCode: (PLDatabaseError) errorCode | |
description: (NSString *) localizedDescription queryString: (NSString *) queryString; | |
-(void)distanceFunc:(id)args; | |
@end | |
#endif |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
* Copyright (c) 2008 Plausible Labs Cooperative, Inc. | |
* All rights reserved. | |
* | |
* Redistribution and use in source and binary forms, with or without | |
* modification, are permitted provided that the following conditions | |
* are met: | |
* 1. Redistributions of source code must retain the above copyright | |
* notice, this list of conditions and the following disclaimer. | |
* 2. Redistributions in binary form must reproduce the above copyright | |
* notice, this list of conditions and the following disclaimer in the | |
* documentation and/or other materials provided with the distribution. | |
* 3. Neither the name of the copyright holder nor the names of any contributors | |
* may be used to endorse or promote products derived from this | |
* software without specific prior written permission. | |
* | |
* THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" | |
* AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE | |
* IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE | |
* ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE | |
* LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR | |
* CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF | |
* SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS | |
* INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN | |
* CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) | |
* ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE | |
* POSSIBILITY OF SUCH DAMAGE. | |
*/ | |
#import "PlausibleDatabase.h" | |
/* Keep trying for up to 5 seconds */ | |
#define SQLITE_BUSY_TIMEOUT 5000 | |
/** | |
* An SQLite Distance function | |
* | |
* Extracted from http://www.thismuchiknow.co.uk/?p=71 | |
*/ | |
#define DEG2RAD(degrees) (degrees * 0.01745327) // degrees * pi over 180 | |
static void distanceFunc(sqlite3_context *context, int argc, sqlite3_value **argv) { | |
assert(argc == 4); | |
if (sqlite3_value_type(argv[0]) == SQLITE_NULL || sqlite3_value_type(argv[1]) == SQLITE_NULL || sqlite3_value_type(argv[2]) == SQLITE_NULL || sqlite3_value_type(argv[3]) == SQLITE_NULL) { | |
sqlite3_result_null(context); | |
return; | |
} | |
double lat1 = sqlite3_value_double(argv[0]); | |
double lon1 = sqlite3_value_double(argv[1]); | |
double lat2 = sqlite3_value_double(argv[2]); | |
double lon2 = sqlite3_value_double(argv[3]); | |
double lat1rad = DEG2RAD(lat1); | |
double lat2rad = DEG2RAD(lat2); | |
sqlite3_result_double(context, acos(sin(lat1rad) * sin(lat2rad) + cos(lat1rad) * cos(lat2rad) * cos(DEG2RAD(lon2) - DEG2RAD(lon1))) * 6378.1); | |
} | |
/** A generic SQLite exception. */ | |
NSString *PLSqliteException = @"PLSqliteException"; | |
@interface PLSqliteDatabase (PLSqliteDatabasePrivate) | |
- (NSObject<TI_PLPreparedStatement> *) prepareStatement: (NSString *) statement error: (NSError **) outError closeAtCheckin: (BOOL) closeAtCheckin; | |
- (sqlite3_stmt *) createStatement: (NSString *) statement error: (NSError **) error; | |
@end | |
/** | |
* An SQLite PLDatabase driver. | |
* | |
* @par Thread Safety | |
* PLSqliteDatabase instances implement no locking and must not be shared between threads | |
* without external synchronization. | |
*/ | |
@implementation PLSqliteDatabase | |
/** | |
* Creates and returns an SQLite database with the provided | |
* file path. | |
*/ | |
+ (id) databaseWithPath: (NSString *) dbPath { | |
return [[[self alloc] initWithPath: dbPath] autorelease]; | |
} | |
/** | |
* Initialize the SQLite database with the provided | |
* file path. | |
* | |
* @param dbPath Path to the sqlite database file. | |
* | |
* @par Designated Initializer | |
* This method is the designated initializer for the PLSqliteDatabase class. | |
*/ | |
- (id) initWithPath: (NSString*) dbPath { | |
if ((self = [super init]) == nil) | |
return nil; | |
_path = [dbPath retain]; | |
return self; | |
} | |
- (NSString *) path; | |
{ | |
return _path; | |
} | |
/* GC */ | |
- (void) finalize { | |
[self close]; | |
[super finalize]; | |
} | |
/* Manual */ | |
- (void) dealloc { | |
[self close]; | |
/* Release our backing path */ | |
[_path release]; | |
[super dealloc]; | |
} | |
/** | |
* Open the database connection. May be called once and only once. | |
* | |
* @return YES on success, NO on failure. | |
*/ | |
- (BOOL) open { | |
return [self openAndReturnError: nil]; | |
} | |
/** | |
* Opens the database connection, and returns any errors. May | |
* be called once and only once. | |
* | |
* @param error A pointer to an NSError object variable. If an error occurs, this | |
* pointer will contain an error object indicating why the database could | |
* not be opened. If no error occurs, this parameter will be left unmodified. | |
* You may specify nil for this parameter, and no error information will be provided. | |
* | |
* @return YES if the database was successfully opened, NO on failure. | |
*/ | |
- (BOOL) openAndReturnError: (NSError **) error { | |
int err; | |
/* Do not call open twice! */ | |
if (_sqlite != nil) | |
[NSException raise: TI_PLSqliteException format: @"Attempted to open already-open SQLite database instance at '%@'. Called -[PLSqliteDatabase open] twice?", _path]; | |
/* Open the database */ | |
err = sqlite3_open([_path fileSystemRepresentation], &_sqlite); | |
if (err != SQLITE_OK) { | |
[self populateError: error | |
withErrorCode: PLDatabaseErrorFileNotFound | |
description: NSLocalizedString(@"The SQLite database file could not be found.", @"") | |
queryString: nil]; | |
return NO; | |
} | |
/* Set a busy timeout */ | |
err = sqlite3_busy_timeout(_sqlite, SQLITE_BUSY_TIMEOUT); | |
if (err != SQLITE_OK) { | |
/* This should never happen. */ | |
[self populateError: error | |
withErrorCode: PLDatabaseErrorUnknown | |
description: NSLocalizedString(@"The SQLite database busy timeout could not be set due to an internal error.", @"") | |
queryString: nil]; | |
return NO; | |
} | |
/* add distance function to sqlite instance */ | |
sqlite3_create_function(_sqlite, "distance", 4, SQLITE_UTF8, NULL, &distanceFunc, NULL, NULL); | |
/* Success */ | |
return YES; | |
} | |
/* from PLDatabase. */ | |
- (BOOL) goodConnection { | |
/* If the connection wasn't opened, we have our answer */ | |
if (_sqlite == nil) | |
return NO; | |
return YES; | |
} | |
/* From PLDatabase */ | |
- (void) close { | |
int err; | |
if (_sqlite == nil) | |
return; | |
/* Close the connection and release any sqlite resources (if open was ever called) */ | |
err = sqlite3_close(_sqlite); | |
/* Leaking prepared statements is programmer error, and is the only cause for SQLITE_BUSY */ | |
if (err == SQLITE_BUSY) | |
[NSException raise: TI_PLSqliteException format: @"The SQLite database at '%@' can not be closed, as the implementation has leaked prepared statements", _path]; | |
/* Unexpected! This should not happen */ | |
if (err != SQLITE_OK) | |
NSLog(@"[WARN] Unexpected error closing SQLite database at '%@': %s", self,sqlite3_errmsg(_sqlite)); | |
/* Reset the variable. If any of the above failed, it is programmer error. */ | |
_sqlite = nil; | |
} | |
/* from PLDatabase */ | |
- (NSObject<TI_PLPreparedStatement> *) prepareStatement: (NSString *) statement { | |
return [self prepareStatement: statement error: nil]; | |
} | |
/* from PLDatabase */ | |
- (NSObject<TI_PLPreparedStatement> *) prepareStatement: (NSString *) statement error: (NSError **) outError { | |
return [self prepareStatement: statement error: outError closeAtCheckin: YES]; | |
} | |
/** | |
* @internal | |
* Utility method to convert an va_list of objects to an NSArray | |
*/ | |
- (NSArray *) arrayWithVaList: (va_list) ap count: (int) count { | |
NSMutableArray *result = [NSMutableArray arrayWithCapacity: count]; | |
/* Iterate over count and create our array */ | |
for (int i = 0; i < count; i++) { | |
id obj; | |
/* Fetch value -- handle nil */ | |
obj = va_arg(ap, id); | |
if (obj == nil) | |
obj = [NSNull null]; | |
[result addObject: obj]; | |
} | |
return result; | |
} | |
#pragma mark Execute Update | |
/* varargs version */ | |
- (BOOL) executeUpdateAndReturnError: (NSError **) error statement: (NSString *) statement args: (va_list) args { | |
NSObject<TI_PLPreparedStatement> *stmt; | |
BOOL ret; | |
/* Create the statement */ | |
stmt = [self prepareStatement: statement error: error]; | |
if (stmt == nil) | |
return NO; | |
/* Bind the arguments and execute the update */ | |
[stmt bindParameters: [self arrayWithVaList: args count: [stmt parameterCount]]]; | |
ret = [stmt executeUpdateAndReturnError: error]; | |
/* Close the statement */ | |
[stmt close]; | |
return ret; | |
} | |
/* from PLDatabase. */ | |
- (BOOL) executeUpdateAndReturnError: (NSError **) error statement: (NSString *) statement, ... { | |
BOOL ret; | |
va_list ap; | |
va_start(ap, statement); | |
ret = [self executeUpdateAndReturnError: error statement: statement args: ap]; | |
va_end(ap); | |
return ret; | |
} | |
/* from PLDatabase. */ | |
- (BOOL) executeUpdate: (NSString *) statement, ... { | |
BOOL ret; | |
va_list ap; | |
va_start(ap, statement); | |
ret = [self executeUpdateAndReturnError: nil statement: statement args: ap]; | |
va_end(ap); | |
return ret; | |
} | |
#pragma mark Execute Query | |
/* varargs version */ | |
- (NSObject<TI_PLResultSet> *) executeQueryAndReturnError: (NSError **) error statement: (NSString *) statement args: (va_list) args { | |
NSObject<TI_PLResultSet> *result; | |
NSObject<TI_PLPreparedStatement> *stmt; | |
/* Create the statement */ | |
stmt = [self prepareStatement: statement error: error closeAtCheckin: YES]; | |
if (stmt == nil) | |
return NO; | |
/* Bind the arguments */ | |
[stmt bindParameters: [self arrayWithVaList: args count: [stmt parameterCount]]]; | |
result = [stmt executeQueryAndReturnError: error]; | |
return result; | |
} | |
- (NSObject<TI_PLResultSet> *) executeQueryAndReturnError: (NSError **) error statement: (NSString *) statement, ... { | |
NSObject<TI_PLResultSet> *result; | |
va_list ap; | |
va_start(ap, statement); | |
result = [self executeQueryAndReturnError: error statement: statement args: ap]; | |
va_end(ap); | |
return result; | |
} | |
/* from PLDatabase. */ | |
- (NSObject<TI_PLResultSet> *) executeQuery: (NSString *) statement, ... { | |
NSObject<TI_PLResultSet> *result; | |
va_list ap; | |
va_start(ap, statement); | |
result = [self executeQueryAndReturnError: nil statement: statement args: ap]; | |
va_end(ap); | |
return result; | |
} | |
#pragma mark Transactions | |
/* from PLDatabase. */ | |
- (BOOL) beginTransaction { | |
return [self beginTransactionAndReturnError: nil]; | |
} | |
/* from PLDatabase */ | |
- (BOOL) beginTransactionAndReturnError: (NSError **) error { | |
return [self executeUpdateAndReturnError: error statement: @"BEGIN DEFERRED"]; | |
} | |
/* from PLDatabase. */ | |
- (BOOL) commitTransaction { | |
return [self commitTransactionAndReturnError: nil]; | |
} | |
/* from PLDatabase */ | |
- (BOOL) commitTransactionAndReturnError: (NSError **) error { | |
return [self executeUpdateAndReturnError: error statement: @"COMMIT"]; | |
} | |
/* from PLDatabase. */ | |
- (BOOL) rollbackTransaction { | |
return [self rollbackTransactionAndReturnError: nil]; | |
} | |
/* from PLDatabase */ | |
- (BOOL) rollbackTransactionAndReturnError: (NSError **) error { | |
return [self executeUpdateAndReturnError: error statement: @"ROLLBACK"]; | |
} | |
#pragma mark Metadata | |
/* from PLDatabase */ | |
- (BOOL) tableExists: (NSString *) tableName { | |
NSObject<TI_PLResultSet> *rs; | |
BOOL exists; | |
/* If there are any results, the table exists */ | |
rs = [self executeQuery: @"SELECT name FROM SQLITE_MASTER WHERE name = ? and type = ?", tableName, @"table"]; | |
exists = [rs next]; | |
[rs close]; | |
return exists; | |
} | |
/** | |
* Returns the row ID of the most recent successful INSERT. If the table | |
* has a column of type INTEGER PRIMARY KEY, then the value assigned will | |
* be an alias for the row ID. | |
* | |
* @return Returns the row ID (integer primary key) of the most recent successful INSERT. | |
*/ | |
- (int64_t) lastInsertRowId { | |
return sqlite3_last_insert_rowid(_sqlite); | |
} | |
- (sqlite3 *) sqliteDB; | |
{ | |
return _sqlite; | |
} | |
@end | |
#pragma mark Library Private | |
/** | |
* @internal | |
* | |
* Library Private PLSqliteDatabase methods | |
*/ | |
@implementation PLSqliteDatabase (PLSqliteDatabaseLibraryPrivate) | |
/** | |
* @internal | |
* Return the last error code encountered by the underlying sqlite database. | |
*/ | |
- (int) lastErrorCode { | |
return sqlite3_errcode(_sqlite); | |
} | |
/** | |
* @internal | |
* Return the last error message encountered by the underlying sqlite database. | |
*/ | |
- (NSString *) lastErrorMessage { | |
return [NSString stringWithUTF8String: sqlite3_errmsg(_sqlite)]; | |
} | |
/** | |
* @internal | |
* | |
* Populate an NSError (if not nil) and log it. | |
* | |
* @param error Pointer to NSError instance to populate. If nil, the error message will be logged instead. | |
* @param errorCode A PLDatabaseError error code. | |
* @param description A localized description of the error message. | |
* @param queryString The optional SQL query which caused the error. | |
*/ | |
- (BOOL) populateError: (NSError **) error withErrorCode: (PLDatabaseError) errorCode | |
description: (NSString *) localizedDescription queryString: (NSString *) queryString | |
{ | |
NSString *vendorString = [self lastErrorMessage]; | |
NSNumber *vendorError = [NSNumber numberWithInt: [self lastErrorCode]]; | |
NSError *result; | |
/* Create the error */ | |
result = [PlausibleDatabase errorWithCode: errorCode | |
localizedDescription: localizedDescription | |
queryString: queryString | |
vendorError: vendorError | |
vendorErrorString: vendorString]; | |
/* Log it and optionally return it */ | |
NSLog(@"[ERROR] A SQLite database error occurred on database '%@': %@ (SQLite #%@: %@) (query: '%@')", | |
_path, result, vendorError, vendorString, queryString != nil ? queryString : @"<none>"); | |
if (error!=NULL) | |
{ | |
*error = result; | |
return YES; | |
} | |
return NO; | |
} | |
@end | |
#pragma mark Private | |
/** | |
* @internal | |
* | |
* Private PLSqliteDatabase methods. | |
*/ | |
@implementation PLSqliteDatabase (PLSqliteDatabasePrivate) | |
/** | |
* @internal | |
* | |
* Prepare and return a new PLPreparedStatement. If closeAtCheckin is YES, the statement | |
* will be closed upon the first checkin from its child PLSqliteResultSet. This should | |
* only be used when returning a result set directly to an API client, in which case the statement | |
* is not available and can not otherwise be explicitly closed. | |
*/ | |
- (NSObject<TI_PLPreparedStatement> *) prepareStatement: (NSString *) statement error: (NSError **) outError closeAtCheckin: (BOOL) closeAtCheckin { | |
sqlite3_stmt *sqlite_stmt; | |
/* Prepare our statement */ | |
sqlite_stmt = [self createStatement: statement error: outError]; | |
if (sqlite_stmt == nil) | |
return nil; | |
/* Create a new prepared statement. | |
* | |
* MEMORY OWNERSHIP WARNING: | |
* We pass our sqlite3_stmt reference to the PLSqlitePreparedStatement, which now must assume authority for releasing | |
* that statement using sqlite3_finalize(). */ | |
return [[[PLSqlitePreparedStatement alloc] initWithDatabase: self sqliteStmt: sqlite_stmt queryString: statement closeAtCheckin: closeAtCheckin] autorelease]; | |
} | |
/** | |
* @internal | |
* | |
* Create an SQLite statement, returning nil on error. | |
* MEMORY OWNERSHIP WARNING: | |
* The returned statement is owned by the caller, and MUST be free'd using sqlite3_finalize(). | |
*/ | |
- (sqlite3_stmt *) createStatement: (NSString *) statement error: (NSError **) error { | |
sqlite3_stmt *sqlite_stmt; | |
const char *unused; | |
int ret; | |
/* Prepare */ | |
ret = sqlite3_prepare_v2(_sqlite, [statement UTF8String], -1, &sqlite_stmt, &unused); | |
/* Prepare failed */ | |
if (ret != SQLITE_OK) { | |
[self populateError: error | |
withErrorCode: PLDatabaseErrorInvalidStatement | |
description: NSLocalizedString(@"An error occured parsing the provided SQL statement.", @"") | |
queryString: statement]; | |
return nil; | |
} | |
/* Multiple statements were provided */ | |
if (*unused != '\0') { | |
[self populateError: error | |
withErrorCode: PLDatabaseErrorInvalidStatement | |
description: NSLocalizedString(@"Multiple SQL statements were provided for a single query.", @"") | |
queryString: statement]; | |
return nil; | |
} | |
return sqlite_stmt; | |
} | |
- (sqlite3 *) sqliteDB; | |
{ | |
return _sqlite; | |
} | |
@end |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment