Feature request: custom aggregate functions

[azplanlos]

Hi there,

I would need to use a custom aggregate function which seems not to be possible with FMDB at the moment as there is no option to implement a block for a step function. According to sqlite API this kind implementation needs 2 functions implemented, one step function which is called for every row and one finishing function.

I try to implement a custom AVR like function with weighting of values according to a second column.

Tried to implement it in my fork of FMDB but stuck halfway...

[ccgus]

If you have very specific examples (with data and such), then I can look into adding it in a future release.

[azplanlos]

Yes, imagine the following table myTable:

ID	height	timeDif
1	10.5	0
2	8.5	100
3	9.5	1000
4	10.5	1001

now I want to create a function to use the following SQL statement:

SELECT AVR_RATED(height, timeDif) FROM myTable;

which should weight the values by timeDif, meaning height stayed the same till next value for a timespan of timeDif.

To implement this I need to define the aggregate function AVR_RATED. SQLite is supporting this but it is not implemented in FMDB.

Using FMDB to implement a custom aggregate function would be something like this:

[self.dbQueue inDatabase:^(FMDatabase *db) {
    [db makeAggregateFunctionNamed:@"AVG_RATED" maximumArguments:2 withBlock:^(sqlite3_context *context, int argc, sqlite3_value **argv) {
        id userData = (__bridge id)(sqlite3_user_data(context));
        NSLog(@"sql step: %@", userData);
        double val = (double)sqlite3_value_double(argv[0]);
        double rate = (double)sqlite3_value_double(argv[1]);
        // do something with this values and store in *context
    } andFinalBlock:^(sqlite3_context *context) {
        NSLog(@"final step");
        id userData = (__bridge id)(sqlite3_user_data(context));
        NSLog(@"sql step: %@", userData);
        sqlite3_result_double(context, 0);
    }];
}]; 

The problem of implementing this is the way FMDB stores a pointer to the custom function block as we need two pointers.

My suggestion is to introduce a c structure to store the two pointers plus a user value for these callbacks:

struct FMDBCallBackBlocks {
    void (*stepFunction)(sqlite3_context *context, int argc, sqlite3_value **argv);
    void (*finalFunction)(sqlite3_context *context);
    double value;
};

I tried implementing it similar to the existing makeFunctionNamed:

- (void)makeAggregateFunctionNamed:(NSString*)name maximumArguments:(int)count withBlock:(void (^)(sqlite3_context *context, int argc, sqlite3_value **argv))block andFinalBlock:(void (^)(sqlite3_context *context))finalBlock {

    if (!_openFunctions) {
        _openFunctions = [NSMutableSet new];
    }

    id b = FMDBReturnAutoreleased([block copy]);

    [_openFunctions addObject:b];

    id bl = FMDBReturnAutoreleased([finalBlock copy]);

    [_openFunctions addObject:bl];

    struct FMDBCallBackBlocks * callback = malloc(sizeof(struct FMDBCallBackBlocks));
    callback->finalFunction = (__bridge void *)(bl);
    callback->stepFunction = (__bridge void *)(b);
    callback->value = 0;

    int res = sqlite3_create_function([self sqliteHandle], [name UTF8String], count, SQLITE_UTF8, (struct FMDBCallBackBlocks*)callback, 0x00, &FMDBSQLiteStepCallBackFunction, &FMDBSQLiteFinalCallBackFunction);

    NSLog(@"made function '%@' with result %i", name, res);
}

void FMDBSQLiteFinalCallBackFunction(sqlite3_context *context);
void FMDBSQLiteFinalCallBackFunction(sqlite3_context *context) {
    (*(((struct FMDBCallBackBlocks*) sqlite3_user_data(context))->finalFunction))(context);
}

void FMDBSQLiteStepCallBackFunction(sqlite3_context *context, int argc, sqlite3_value **argv);
void FMDBSQLiteStepCallBackFunction(sqlite3_context *context, int argc, sqlite3_value **argv) {
    (*(((struct FMDBCallBackBlocks*) sqlite3_user_data(context))->stepFunction))(context, argc, argv);
}

which seems to create the function but I am not able to call it.

[ccgus]

Awesome, thanks for the info and example and code and such. I'll look into it for a future release.

[azplanlos]

Great! Also, if I get this code to work I'll file a pull request.

---

Von: ccgus notifications@github.com Gesendet: Mittwoch, 8. April 2015 20:05 An: ccgus/fmdb Cc: Andreas Zöllner Betreff: Re: [fmdb] Feature request: custom aggregate functions (#360)

Awesome, thanks for the info and example and code and such. I'll look into it for a future release.

Reply to this email directly or view it on GitHubhttps://github.com/ccgus/fmdb/issues/360#issuecomment-90976456.

[zhangjirui]

4 years later，still not supported. @ccgus