Adding New SQL Functions

The SQLite library allows the SQL language to be extended with new functions implemented as C code. In fact all of SQLite's built-in functions are now written this way.

Creating a regular function begins with a call to sqlite_create_function(), and this is the prototype:

 int sqlite_create_function() {   sqlite *db,   const char *zName,   int nArg,   void (*xFunc)(sqlite_func *, int, const char **),   void *pUserData ); 

The example in Listing 6.8 defines a trivial function in C that capitalizes every alternate letter in a string. The example uses sqlite_create_function() to register the function defined in capitalize_alternate() for use within SQLite under the name altcaps().

Listing 6.8. Creating a Custom Function Using sqlite_create_function()

 #include <stdio.h> #include <sqlite.h> void capitalize_alternate(sqlite_func *context, int argc, const char **argv) {   int i;   static char str[80];   for (i=0; i<strlen(argv[0]); i++) {     if (i%2 == 0)       str[i] = toupper(argv[0][i]);     else       str[i] = tolower(argv[0][i]);   }   str[i] = '\0';   sqlite_set_result_string(context, str, -1); } main() {   char *errmsg;   char **result;   char str[80];   int ret, rows, cols, i, j;   sqlite *db = sqlite_open("progdb", 0777, &errmsg);   if (db == 0)   {     fprintf(stderr, "Could not open database: %s\n", errmsg);     sqlite_freemem(errmsg);     exit(1);   }   sqlite_create_function(db, "altcaps", 1, capitalize_alternate, NULL);   ret = sqlite_get_table(db, "SELECT altcaps('this is a test')",                                               &result, &rows, &cols, &errmsg);   if (ret != SQLITE_OK)   {     fprintf(stderr, "SQL error: %s\n", errmsg);   }   else {     for (i=0; i< cols * (rows+1); i++) {       printf("result[%d] = %s\n", i, result[i]);     }   }   sqlite_close(db); } 

First of all we declare the function capitalize_alternate() in C, which will do the work we want to include in SQL.

The prototype for a function that can be used with sqlite_create_function() is fixed; the context argument can be used to pass arbitrary data to the function. The argc and argv arguments tell the C function how many parameters have been passed when it is called from SQL.

 void capitalize_alternate(sqlite_func *context, int argc, const char **argv) 

The loop cycles through each item in the string passed inargv[0] will contain the only parameter in this caseand uses toupper() and tolower() in turn to alternate the case of each character, adding to str one character at a time.

 for (i=0; i<strlen(argv[0]); i++) {   if (i%2 == 0)     str[i] = toupper(argv[0][i]);   else     str[i] = tolower(argv[0][i]); } str[i] = '\0'; 

Finally, we tell the interface that str is our return value using sqlite_set_result_string():

 sqlite_set_result_string(context, str, -1); 

Three different functions enable return values of different data types to be used:

 sqlite_set_result_string(sqlite_func *, const char *, int) sqlite_set_result_int(sqlite_func *, int); sqlite_set_result_double(sqlite_func *,double); 

Alternatively, the sqlite_set_result_error() function can be used to return an error code to SQLite from the custom function:

 sqlite_set_result_error(sqlite func *, const char *, int); 

The third int parameter to sqlite_set_result_string() and sqlite_set_result_error() is the number of characters to take from the string. If the value is negative, as in our example, all characters up to and including the first \0 are returned.

So with the custom function defined in C, a call is made to sqlite_create_function() to add it to the SQL language.

 sqlite_create_function(db, "altcaps", 1, capitalize_alternate, NULL); 

The zName parameter here tells SQLite that the function is called altcaps(). The name used in SQL does not have to match the C function name, although it is possible to do so.

To test that the function has been registered correctly, sqlite_get_table() is called to execute a simple query using altcaps().

 ret = sqlite_get_table(db, "SELECT altcaps('this is a test')",                                             &result, &rows, &cols, &errmsg); 

If the query is successful, the contents of the returned array are displayed:

 for (i=0; i< cols * (rows+1); i++) {   printf("result[%d] = %s\n", i, result[i]); 

Running the compiled program will produce the following output, showing that the function has indeed capitalized every other letter of the string.

 $ ./listing6.8 result[0] = altcaps('this is a test') result[1] = ThIs iS A TeSt 

Note

Although sqlite_create_function() takes a database resource parameter and registers the function only to that database, the function is not stored in the database file. Therefore when the program that calls sqlite_create_function() has terminated, the user-defined function is no longer available to SQLite.

If required within a function's code, the data contained in the pointer pUserData in the sqlite_create_function() call can be referenced using the sqlite_user_data() function, which has this prototype:

 void *sqlite_user_data(sqlite_func *) 

Creating Aggregating Functions

The process to create an aggregating functionone that applies a calculation across all the rows returned by a query or across similar rows indicated by a GROUP BY clauseis slightly different from a regular function.

The interface call sqlite_create_aggregate() takes two function pointer arguments. One function is called for each row in the result setthe step functionand one is called once all the rows have been processedthe finalize function. The prototype is as follows:

 int sqlite_create_aggregate (   sqlite *db,   const char *zName,   int nArg,   void (*xStep) (sqlite func *, int, const char **),   void (*xFinalize)(sqlite_func*),  void pUserData ); 

To see an example, let's take a look at how SQLite actually implements the sum() aggregating function. The source code for this and many other built-in functions can be found in func.c in the SQLite source distribution.

The step function sumStep() is as follows:

 static void sumStep(sqlite_func *context, int argc, const char **argv){   SumCtx *p;   if( argc<1 ) return;   p = sqlite_aggregate_context(context, sizeof(*p));   if( p && argv[0] ){     p->sum += sqliteAtoF(argv[0], 0);     p->cnt++;   } } 

SumCtx is a data structure suitable for storing the values needed to compute the return value; in this case a running total is added to each step to produce the sum. In fact because the avg() function is computed from the sum divided by the number of elements, the SumCtx structure also includes a running count of the number of terms and is used for both functions.

 typedef struct SumCtx SumCtx; struct SumCtx {   double sum;     /* Sum of terms */   int cnt;        /* Number of elements summed */ }; 

The sqlite_aggregate_context() function provides access to an area of memory that is available only to the particular instance of a function called from SQL, and the memory is automatically freed after the related finalize function has been called.

The work done by the step function for sum() is actually quite simple. The running total is incremented by the current value and the running count is incremented by one:

 p->sum += sqliteAtoF(argv[0], 0); p->cnt++; 

The function sqliteAtoF() is a SQLite-safe version of the regular atof() function to convert an ASCII string to a floating-point number and is defined in util.c. If the locale setting requires it, sqliteAtoF() will accept a comma as a decimal point instead of a period character.

The sumFinalize() function references the context structure containing the totals computed in the step function and uses sqlite_set_result_double() to return a floating-point result containing the sum of all the values:

 static void sumFinalize(sqlite_func *context){   SumCtx *p;   p = sqlite_aggregate_context(context, sizeof(*p));   sqlite_set_result_double(context, p ? p->sum : 0.0); } 

We mentioned before that the avg() function can reuse much of the code for the sum() function, and in fact the same step function is used to create avg() in SQLite. The following code shows the avgFinalize() function.

 static void avgFinalize(sqlite_func *context){   SumCtx *p;   p = sqlite_aggregate_context(context, sizeof(*p));   if( p && p->cnt>0 ){     sqlite_set_result_double(context, p->sum/(double)p->cnt);   } }