Bitmask grouping using SQLite

Question

I have an sqlite table when each table has a field which I use to hold some bitmask enum.

I would like to perform a query which will return me all results and group all enums into one field:

Services: TEXT name,INTEGER service,INTEGER mask

SELECT service,XXX FROM Services GROUP BY service

Basically, I would like XXX to be the result of bitmask OR (|) of all masks per that service.

Data:

'a1',1,1
'a2',1,2,
'a3',1,4,
'a4',1,8,

'b1',2,1,
'b2',2,3,
'b3',2,2

So as a result, I would like to get the following rows:

1,15 (1|2|4|8)
2,3 (1|3|2)

Thanks

EDIT: In my original question, I've forgot to mention that each mask is not a single bit but can be a mask of multiple bits (I've changed the second example to reflect that).

Answer 1

SQLite supports custom aggregate functions; depending on your set up you could register a custom function to do this pretty easily. Using the C API:

void bitwise_or_step(sqlite3_context *context, int argc, sqlite3_value** argv)
{
    int *buffer = (int *)sqlite3_aggregate_context(context, sizeof(int));
    int x = sqlite3_value_int(argv[0]);
    *buffer |= x;
}

void bitwise_or_final(sqlite3_context *context)
{
    int *buffer = (int *)sqlite3_aggregate_context(context, sizeof(int));
    sqlite3_result_int(context, *buffer);
}

sqlite3_create_function_v2(db, "BITWISE_OR", 1, SQLITE_ANY, NULL,
    NULL, bitwise_or_step, bitwise_or_final, NULL);

Then within your SQL, you should be able to do:

SELECT service,BITWISE_OR(mask) FROM Services GROUP BY service

If you're using PHP, you can define a custom aggregate function from PHP, too.