I often need string concatenation to behave just like an aggregate function.
Once again I find a need to do that in SQLite, and to do that without recompiling
SQLite for every platform we distribute for...
1
2
3
4 SQLITE_EXTENSION_INIT1
5
6
7 typedef struct SCtx SCtx;
8 struct SCtx {
9 int rowCnt;
10 int charCnt;
11 char *result;
12 };
13
14 static void concat_step(sqlite3_context* ctx, int argc, sqlite3_value**argv) {
15
16 SCtx *p = (SCtx *) sqlite3_aggregate_context(ctx, sizeof(*p));
17
18 char *sep = sqlite3_value_text(argv[1]);
19
20 char *txt = sqlite3_value_text(argv[0]);
21
22 if (p->rowCnt) {
23 char *txt2 = malloc(strlen(txt) + strlen(sep) + 1);
24 strcpy(txt2,sep);
25 strcat(txt2,txt);
26 txt = txt2;
27 }
28
29 // printf("%d. Txt: [%s] len %d\n", p->rowCnt, txt, strlen(txt));
30
31 int len = strlen(txt);
32
33 if (!p->result) {
34 p->result = malloc(len + 1);
35 strcpy(p->result, txt);
36 } else {
37 p->result = realloc(p->result, strlen(p->result) + len + 1);
38 strcat(p->result,txt);
39 }
40 // printf ("intermediate [%s]\n", p->result);
41 p->rowCnt++;
42 }
43
44 static void concat_final(sqlite3_context* ctx,
45 int argc,
46 sqlite3_value** argv) {
47
48 SCtx *p = (SCtx *) sqlite3_aggregate_context(ctx, sizeof(*p));
49 // printf("Finally: %s\n", p->result);
50 sqlite3_result_text(ctx, p->result, strlen(p->result), NULL);
51 }
52
53 int sqlite3_extension_init(
54 sqlite3 *db,
55 char **pzErrMsg,
56 const sqlite3_api_routines *pApi
57 ){
58 SQLITE_EXTENSION_INIT2(pApi)
59 sqlite3_create_function(db, "concat", 2, SQLITE_ANY, 0, NULL, concat_step, concat_final);
60 return 0;
61 }
I compiled this with the following (here, ./src is the SQLite code - I used
http://www.sqlite.org/sqlite-source-3_3_13.zip).
1
2 gcc -fpic -c agg.c -I./src
3 gcc -shared -Wl,-soname,libagg.so -o libagg.so agg.o
And here's how it works:
1
2 sqlite> CREATE TABLE test (animals VARCHAR, interjection VARCHAR);
3
4 sqlite> insert into test (animals, interjection) values ('lions', 'oh my');
5
6 sqlite> insert into test (animals, interjection) values ('tigers', 'oh my');
7
8 sqlite> insert into test (animals, interjection) values ('bears', 'oh my');
9
10 sqlite> select load_extension('./libagg.so');
11
12 sqlite> select concat(animals, ' and '), interjection from test group by interjection;
13
14 sqlite> lions and tigers and bears|oh my
See also:
1. http://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions
2. http://souptonuts.sourceforge.net/readme_sqlite_tutorial.html
3. http://www.sqlite.org/capi3ref.html