Last active
February 21, 2023 15:17
-
-
Save npulidom/9f55a6f2f95bcfc91d14 to your computer and use it in GitHub Desktop.
Revisions
-
npulidom revised this gist
Feb 21, 2023 . 1 changed file with 0 additions and 1 deletion.There are no files selected for viewing
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 charactersOriginal file line number Diff line number Diff line change @@ -47,7 +47,6 @@ - (void)extendSQLiteFunctions:(FMDatabase *)db { sqlite3_result_double(context, res); }]; /** VAR & STDEV FUNCTION call using sqlite3_create_function (libsqlite3.dylib), -
Feb 8, 2023 . 1 changed file with 2 additions and 1 deletion.There are no files selected for viewing
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 charactersOriginal file line number Diff line number Diff line change @@ -48,7 +48,8 @@ - (void)extendSQLiteFunctions:(FMDatabase *)db { }]; /** VAR & STDEV FUNCTION call using sqlite3_create_function (libsqlite3.dylib), Notice sqliteHandle, iterator & callback functions args. **/ -
Feb 8, 2023 . 1 changed file with 1 addition and 0 deletions.There are no files selected for viewing
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 charactersOriginal file line number Diff line number Diff line change @@ -32,6 +32,7 @@ - (void)extendSQLiteFunctions:(FMDatabase *)db { 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 -
Feb 8, 2023 . 1 changed file with 6 additions and 6 deletions.There are no files selected for viewing
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 charactersOriginal file line number Diff line number Diff line change @@ -25,7 +25,7 @@ SELECT POWER(field_name, 2) FROM my_table #pragma mark - Helper Methods // ++ Objective-C code - (void)extendSQLiteFunctions:(FMDatabase *)db { /* -- POWER FUNCTION @@ -61,15 +61,15 @@ call using sqlite3_create_function (libsqlite3.dylib), /** 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) { @@ -83,7 +83,7 @@ static void varianceStep(sqlite3_context *context, int argc, sqlite3_value **arg p = sqlite3_aggregate_context(context, sizeof(*p)); /* only consider non-null values */ if (SQLITE_NULL != sqlite3_value_numeric_type(argv[0])) { p->cnt++; @@ -99,7 +99,7 @@ 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); @@ -110,7 +110,7 @@ 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); -
Feb 8, 2023 . 1 changed file with 18 additions and 19 deletions.There are no files selected for viewing
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 charactersOriginal file line number Diff line number Diff line change @@ -26,13 +26,12 @@ SELECT POWER(field_name, 2) FROM my_table #pragma mark - Helper Methods //objC 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 @@ -48,10 +47,10 @@ - (void)extendSQLiteFunctions:(FMDatabase *)db }]; /** 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); @@ -60,21 +59,21 @@ call using sqlite3_create_function (libsqlite3.dylib), #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; @@ -95,8 +94,8 @@ static void varianceStep(sqlite3_context *context, int argc, sqlite3_value **arg } } static void stdevFinalize(sqlite3_context *context) { StdevCtx *p; p = sqlite3_aggregate_context(context, 0); @@ -106,8 +105,8 @@ static void stdevFinalize(sqlite3_context *context) sqlite3_result_double(context, 0.0); } static void varianceFinalize(sqlite3_context *context) { StdevCtx *p; p = sqlite3_aggregate_context(context, 0); -
Mar 22, 2016 . 1 changed file with 11 additions and 10 deletions.There are no files selected for viewing
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 charactersOriginal file line number Diff line number Diff line change @@ -36,7 +36,7 @@ - (void)extendSQLiteFunctions:(FMDatabase *)db 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 @@ -82,9 +82,12 @@ static void varianceStep(sqlite3_context *context, int argc, sqlite3_value **arg 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; @@ -96,22 +99,20 @@ 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); } -
Nicolas Pulido M. revised this gist
Nov 24, 2014 . 1 changed file with 1 addition and 1 deletion.There are no files selected for viewing
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 charactersOriginal file line number Diff line number Diff line change @@ -58,7 +58,7 @@ call using sqlite3_create_function (libsqlite3.dylib), 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 -
Nov 24, 2014 . 1 changed file with 1 addition and 0 deletions.There are no files selected for viewing
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 charactersOriginal file line number Diff line number Diff line change @@ -51,6 +51,7 @@ - (void)extendSQLiteFunctions:(FMDatabase *)db /* -- 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); -
Nov 24, 2014 .There are no files selected for viewing
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 charactersOriginal file line number Diff line number Diff line change @@ -0,0 +1,116 @@ /** @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 //objC 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 & Standar 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); } }
Nicolas Pulido M.
revised
this gist