/**
@author: 
Nicolas Pulido

@description:
Snippet code to extended SQLite math functions in an iOS project, using ccgus/FMDB library.

Due to the fact that iOS doesn't support dynamic libraries, we should create SQLite extended functions on runtime with 
sqlite3_create_function.
(http://stackoverflow.com/questions/6283496/xcode-sqlite-extension-for-use-on-iphone)

Testing example:
SELECT STDEV(field_name) FROM my_table
SELECT VAR(field_name) FROM my_table
SELECT POWER(field_name, 2) FROM my_table

Notes:
Open your SQLite database, and then call extendSQLiteFunctions

References: 
http://www.sqlite.org/contrib (extension-functions.c  Liam Healy)
http://sqlite.org/c3ref/create_function.html
https://github.com/ccgus/fmdb
**/

#pragma mark - Helper Methods

// ++ Objective-C code
- (void)extendSQLiteFunctions:(FMDatabase *)db {

    /* -- POWER FUNCTION
      call example using FMDB makeFunctionNamed:maximumArguments:withBlock: method
    */
    [db makeFunctionNamed:@"POWER" maximumArguments:2 withBlock:^(sqlite3_context *context, int aargc, sqlite3_value **aargv) {
         
         assert(aargc == 2); //make sure n parameters are passed.
         
         //simple validation
         if (sqlite3_value_type(aargv[0]) == SQLITE_TEXT)
            sqlite3_result_double(context, 0);
         
         double num = sqlite3_value_double( aargv[0] ); // get the first arg to the function
         double exp = sqlite3_value_double( aargv[1] ); // get the second arg
         double res = pow(num, exp);                    // calculate the result
         
         //return result
         sqlite3_result_double(context, res);
     }];
    
    /** 
        VAR & STDEV FUNCTION 
        call using sqlite3_create_function (libsqlite3.dylib), 
        Notice sqliteHandle, iterator & callback functions args.
    **/
    void *pArg = 0;
    //create fns
    sqlite3_create_function([db sqliteHandle], [@"STDEV" UTF8String], 1, SQLITE_UTF8, pArg, 0, &varianceStep, &stdevFinalize);
    sqlite3_create_function([db sqliteHandle], [@"VAR" UTF8String], 1, SQLITE_UTF8, pArg, 0, &varianceStep, &varianceFinalize);
}

#pragma mark - Variance & Standard Deviation

/**
    Extracted from Math SQLite contributions extension-functions.c
    ++ C code
**/

typedef struct StdevCtx StdevCtx;
struct StdevCtx {

    double rM;
    double rS;
    int cnt; // number of elements
};

static void varianceStep(sqlite3_context *context, int argc, sqlite3_value **argv) {

    StdevCtx *p;
    
    double delta;
    double x;
    
    assert( argc==1 );
    p = sqlite3_aggregate_context(context, sizeof(*p));
    
    /* only consider non-null values */
    if (SQLITE_NULL != sqlite3_value_numeric_type(argv[0])) {
    
        p->cnt++;
        
        x = sqlite3_value_double(argv[0]);
        delta = (x-p->rM);
        p->rM += delta/p->cnt;
        p->rS += delta*(x-p->rM);
    }
}

static void stdevFinalize(sqlite3_context *context) {

    StdevCtx *p;
    p = sqlite3_aggregate_context(context, 0);
    
    if ( p && p->cnt>1 )
        sqlite3_result_double(context, sqrt(p->rS/(p->cnt-1)));
    else
        sqlite3_result_double(context, 0.0);
}

static void varianceFinalize(sqlite3_context *context) {

    StdevCtx *p;
    p = sqlite3_aggregate_context(context, 0);
    
    if (p && p->cnt>1)
        sqlite3_result_double(context, p->rS/(p->cnt-1));
    else
        sqlite3_result_double(context, 0.0);
}