Last active
February 21, 2023 15:17
-
-
Save npulidom/9f55a6f2f95bcfc91d14 to your computer and use it in GitHub Desktop.
iOS SQLite helper for math extended functions (sqlite3_create_function) using FMDB lib
This file contains hidden or 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
| /** | |
| @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); | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment