Extending the Plausible Database in Titanium to allow for SQLite distance function.

PLSqliteDatabase.h

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

PLSqliteDatabase.m

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