Some SQLite extension functions, using the sqlite_create_function approach introduced in version 2.4.0 of SQlite
SQLite Extensions
Beginning in version 2.4.0 of SQLite, a set of functions was introduced which allows SQLite users to add new 'built-in' functions to SQLite. The functions can be 'regular' functions or 'aggregate' functions. A 'regular' function takes a single set of parameters as input. For example, the (already) built-in function abs(x) is an example of a regular function. Aggregate functions take a set of row values as input and produce a single output value. For example sum(x) or avg(x).
The API for adding additional functions is pretty straight-forward, and is documented at
the sqlite C/C++ API page, near the end of the page.
There is also a
sqlite wiki.
SQLite Extension Functions
I've created a set of simple SQLite extension functions just to fill in a few gaps in the functions already available in SQLite.
The regular functions are:
- now() - return the current time as a Unix-style time (number of seconds since 1970)
- formatGMTime(aTime, aFormat) - return a time formatted according to the optional format string, based on UTC / GMT
- formatLocalTime(aTime, aFormat) - return a time formatted according to the optional format string, based on local time
- age(aTime) - return the number of seconds between now and the specified time
- difftime(time1, time2) - return the number of seconds between 2 times
- distance(x1, y1, x2, y2) - return the Euclidean distance between 2 sets of points
The aggregate functions are:
- sigma(x) - return the standard deviation of a set of numbers
- variance(x) - the square of sigma(x)
- dispersion(x) - return the dispersion of a set of numbers (see below)
- slope(x, y) - assuming linear correlation, return the slope of a set of x/y values
- intercept(x, y) - return the intercept of a set of x/y values
- corrcoeff(x, y) - return the correlation coefficient of a set of x/y values
There is a small test program included in the distribution. It reads a script containing SQL statements, executes the SQL, and displays the results. See the file exp1.c. Here's a part of the script:
create table t3 (
pkey integer primary key,
x float,
y float
);
!repeat 10
insert into t3 values(NULL, 10, 110);
insert into t3 values(NULL, 9, 109.8);
insert into t3 values(NULL, 5, 105.1);
insert into t3 values(NULL, 4, 103.8);
insert into t3 values(NULL, 3, 103.7);
insert into t3 values(NULL, 2, 102.0);
insert into t3 values(NULL, 1, 101.0);
insert into t3 values(NULL, 8, 108.8);
insert into t3 values(NULL, 7, 107.2);
insert into t3 values(NULL, 6, 106.9);
!endrepeat
-- test the statistical functions
select sigma(x), sigma(y) from t3;
select variance(x), variance(y) from t3;
select dispersion(x), dispersion(y) from t3;
-- test the correlation functions
select slope(x, y), intercept(x, y), corrcoeff(x, y) from t3;
-- test various NULL value conditions
insert into t1 values (NULL, 'a string', 1234, NULL, NULL);
-- expect an error on the row with NULL timestamp1
select format_gmtime(time1, "") from t1;
The test program creates all the tables it needs, so no setup is required.
The test program also illustrates just what you need to do to load the extension functions:
#include <sqlite.h>
#include <sqext.h>
.
.
.
sqlite * dbh;
char * errmsg;
dbh = sqlite_open(dbname, dbmode, &errmsg);
.
.
.
sqlitex_registerFunctions(dbh);
The header file,
sqext.h is very small:
#ifndef SQEXT_H
#define SQEXT_H
#include <sqlite.h>
#ifdef _cplusplus
extern "C" {
#endif
int sqlitex_registerFunctions(sqlite * dbh);
#ifdef _cplusplus
}
#endif
#endif
That's right - it only exposes one function, the registration function.
I feel rather silly even mentioning a license for such a tiny amount of code. The attached source code is placed into the public domain with the same restrictions as
sqlite itself. If you use this code you accept full responsibility for your use of the code. You can modify it, give it to others, sell it; hell, you can rent it out for Friday night parties if you want. If you find a bug, I would appreciate it if you would let me know, but I don't promise to do anything about it.
I plan to document the specifics of each function (like what they do with NULL values and other bad data), but it may be a few days before I get to it.
If you look at the source you'll notice that I also included a couple functions related to random numbers. SQLite already provides a pseudo-random number generator called random() which returns a signed 4-byte integer with range equal to the full range of a 4-byte integer. I haven't analyzed it to find out how random it is, but I suspect it's pretty good. However, I think it would be handy to have a random number generator that produces integer numbers in the range of [0 ... large integer] and also one that produces floating point numbers in the range [0..1]. I just stubbed out a (really bad) random number generator, with plans to do better in the near future. I'm thinking of making a C version of the random number generators at
RubyExampleRandomNumbers, but with the 2 generators having a period different from one another and relatively prime to one another. Then, by adding the result of both generators I should have a generator whose period is ridiculously long - much larger than the range of generated numbers.
--
DaleBrayden - 08 Sep 2003
- Full listing at sqlite.org
- For VB
- LiteX - "LiteX is an ATL COM wrapper over sqlite3. It is not ADO-like as SQLiteDB but rather API-like."
- sqlite2X - " is Free ActiveX wrapper for SQLite 2.x embeddable SQL database engine. It's VERY fine for scripting :) It's free replacement for SQLitePlus"
- SqliteDB - "SQLiteDB's object model is similar to ADO, featuring both a Connection and Recordset object."
Books RSS
sqext_1_0.zip