Scalar and aggregate functions transform data by performing calculations on their input values and returning a single value.
For an explanation of the difference between scalar and aggregate functions, and examples of each, see the W3Schools webpage on SQL functions: http://www.w3schools.com/SQL/sql_functions.asp.
In your code, any function of either type must have a specific method signature that includes arguments for a SQLite context reference, the number of arguments, and a reference to memory for storing the result. You must also define a qdb_function structure that describes the function. Consider this sample scalar function that determines the sorting direction ("forward" or "reverse") based on its user data:
typedef struct my_sort_cfg_s {
unsigned rev_sort;
} my_sort_cfg_t;
static void get_my_sort_runtime(
sqlite3_context *ctx, int narg, sqlite3_value **value)
{
my_sort_cfg_t *my_sort_cfg;
char *config, char *key;
size_t len;
if ((my_sort_cfg = sqlite3_user_data(ctx)) == NULL) {
return;
}
config = my_sort_cfg->rev_sort ? "forward" : "reverse";
len = strlen(config)+1;
if (NULL != (key = sqlite3_malloc(len))) {
memcpy(key, config, len);
sqlite3_result_text(ctx, key, len, sqlite3_free);
}
}
my_sort_cfg_t my_sort_cfg_data = { .rev_sort=0 };
struct qdb_function get_my_sort_runtime_cfg = {
.name="get_my_sort_runtime_cfg",
.encoding=SQLITE_UTF8,
.narg=0,
.arg=&my_sort_cfg_data,
.func=get_my_sort_runtime,
.step=NULL,
.final=NULL
};
The tag value in this case is get_my_sort_runtime_cfg, the function name as visible to SQL is also get_my_sort_runtime_cfg, and the C function that implements the scalar operation is get_my_sort_runtime(). This last function can retrieve its input data from the fourth field in the structure, my_sort_cfg_data, by calling sqlite3_user_data().
There can be multiple functions defined (in the same or different DLLs) but each must have a Function entry in the configuration object for the associated database as well as a struct qdb_function object with a unique name describing the function.
The qdb_function structure has these members:
struct qdb_function {
char *name;
int encoding;
int narg;
void *arg;
void (*func)(struct sqlite3_context *, int, struct Mem **);
void (*step)(struct sqlite3_context *, int, struct Mem **);
void (*final)(struct sqlite3_context *);
};