DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

Snippets Manager

Snippets has posted 5883 posts at DZone. View Full User Profile

Add A Concat() Aggregate Function To SQLite

03.22.2007

| 13801 views |

        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...

#include <stdlib.h>
#include "sqlite3ext.h"
SQLITE_EXTENSION_INIT1


typedef struct SCtx SCtx;
struct SCtx {
  int rowCnt;
  int charCnt;
  char *result;
};

static void concat_step(sqlite3_context* ctx, int argc, sqlite3_value**argv) {

  SCtx *p = (SCtx *) sqlite3_aggregate_context(ctx, sizeof(*p));

  char *sep = sqlite3_value_text(argv[1]);

  char *txt = sqlite3_value_text(argv[0]);

  if (p->rowCnt) {
    char *txt2 = malloc(strlen(txt) + strlen(sep) + 1);
    strcpy(txt2,sep);
    strcat(txt2,txt);
    txt = txt2;
  }

  //  printf("%d. Txt: [%s] len %d\n", p->rowCnt, txt, strlen(txt));

  int len = strlen(txt);

  if (!p->result) {
    p->result = malloc(len + 1);
    strcpy(p->result, txt);
  } else {
    p->result = realloc(p->result, strlen(p->result) + len + 1);
    strcat(p->result,txt);
  }
  //  printf ("intermediate [%s]\n", p->result);
  p->rowCnt++;
}

static void concat_final(sqlite3_context* ctx,
                         int argc,
                         sqlite3_value** argv) {

  SCtx *p = (SCtx *) sqlite3_aggregate_context(ctx, sizeof(*p));
  //  printf("Finally: %s\n", p->result);
  sqlite3_result_text(ctx,  p->result, strlen(p->result), NULL);
}

int sqlite3_extension_init(
  sqlite3 *db,
  char **pzErrMsg,
  const sqlite3_api_routines *pApi
){
  SQLITE_EXTENSION_INIT2(pApi)
  sqlite3_create_function(db, "concat", 2, SQLITE_ANY, 0, NULL, concat_step, concat_final);
  return 0;
}


I compiled this with the following (here, ./src is the SQLite code - I used
http://www.sqlite.org/sqlite-source-3_3_13.zip).

gcc -fpic -c agg.c -I./src
gcc -shared -Wl,-soname,libagg.so -o libagg.so agg.o


And here's how it works:

sqlite> CREATE TABLE test (animals VARCHAR, interjection VARCHAR);

sqlite> insert into test (animals, interjection) values ('lions', 'oh my');

sqlite> insert into test (animals, interjection) values ('tigers', 'oh my'); 

sqlite> insert into test (animals, interjection) values ('bears', 'oh my');

sqlite> select load_extension('./libagg.so');

sqlite> select concat(animals, ' and '), interjection from test group by interjection;

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

Tags:

Comments

Snippets Manager replied on Mon, 2012/05/07 - 2:37pm

No doubt - I wrote this on a lark; I am happy someone found it useful!

Snippets Manager replied on Wed, 2007/09/12 - 11:15am

There are some memory leaks inside this code:


...
static void concat_step(sqlite3_context* ctx, int argc, sqlite3_value**argv) {
  ...
  if (p->rowCnt) {
    char *txt2 = malloc(strlen(txt) + strlen(sep) + 1);
    ...
  }
  ...
  if (!p->result) {
    p->result = malloc(len + 1);
    ...
  }
  ...
}

Both txt2 and p->result are never freed again. Here is a modified version with also some minor optimizations of the string/memory handling:

#include 
#include "sqlite3ext.h"
SQLITE_EXTENSION_INIT1


typedef struct SCtx SCtx;
struct SCtx {
  int chrCnt;
  char *result;
};

static void concat_step(sqlite3_context* ctx, int argc, sqlite3_value**argv) {

  SCtx *p = (SCtx *) sqlite3_aggregate_context(ctx, sizeof(*p));

  char *sep = sqlite3_value_text(argv[1]);

  char *txt = sqlite3_value_text(argv[0]);

  int len = strlen(txt);

  if (!p->result) {
    p->result = malloc(len + 1);
    memcpy(p->result, txt, len + 1);
    p->chrCnt = len;
  } else {
    int sepLen = strlen(sep);
    p->result = realloc(p->result, p->chrCnt + len + sepLen + 1);
    memcpy(p->result +  p->chrCnt, sep, sepLen);
    p->chrCnt += sepLen;
    memcpy(p->result +  p->chrCnt, txt, len + 1);
    p->chrCnt += len;
  }
}

static void concat_free(void* oldstr) {
  free((char*)oldstr);
}

static void concat_final(sqlite3_context* ctx,
                         int argc,
                         sqlite3_value** argv) {
  SCtx *p = (SCtx *) sqlite3_aggregate_context(ctx, sizeof(*p));
  sqlite3_result_text(ctx, p->result, p->chrCnt, concat_free);
}

int sqlite3_extension_init(
  sqlite3 *db,
  char **pzErrMsg,
  const sqlite3_api_routines *pApi
){
  SQLITE_EXTENSION_INIT2(pApi)
  sqlite3_create_function(db, "concat", 2, SQLITE_ANY, 0, NULL, concat_step, concat_final);
  return 0;
}