How to compile a user defined function in SQLite

Question

I have an example C function that I would like to attach to a database temp.sqlite (it's from an O'Reilly book, so I know it works). I read the section in the book and sqlite.org, but they assume away that I know how and where to compile this thing with the proper settings. I'm on either Mac (with XCode) or Ubuntu.

I know enough C to alter the code to do what I want, but I have no idea what to do call it from my database temp.sqlite.

Thanks for your help! I've been churning on this!

Update: A few more hours in and I've scrapped together enough stuff from abandoned webpages to create a compile command and generate an error:

richard$ gcc -o wtavg wtavg.c -Wall -W -O2 -L/usr/local/lib -lsqlite3
wtavg.c: In function ‘wtavg_init’:
wtavg.c:63: warning: unused parameter ‘error’
Undefined symbols:
  "_main", referenced from:
      start in crt1.10.6.o
ld: symbol(s) not found
collect2: ld returned 1 exit status

FWIW, here's wtavg.c, which is straight from the O'Reilly site provided in my book:

/* wtavg.c */

#include "sqlite3ext.h"
SQLITE_EXTENSION_INIT1;

#include <stdlib.h>




typedef struct wt_avg_state_s {
   double   total_data;  /* sum of (data * weight) values */
   double   total_wt;    /* sum of weight values */
} wt_avg_state;


static void wt_avg_step( sqlite3_context *ctx, int num_values, sqlite3_value **values )
{
    double         row_wt = 1.0;
    int            type;
    wt_avg_state   *st = (wt_avg_state*)sqlite3_aggregate_context( ctx,
                                               sizeof( wt_avg_state ) );
    if ( st == NULL ) {
        sqlite3_result_error_nomem( ctx );
        return;
    }

    /* Extract weight, if we have a weight and it looks like a number */
    if ( num_values == 2 ) {
        type = sqlite3_value_numeric_type( values[1] );
        if ( ( type == SQLITE_FLOAT )||( type == SQLITE_INTEGER ) ) {
            row_wt = sqlite3_value_double( values[1] );
        }
    }

    /* Extract data, if we were given something that looks like a number. */
    type = sqlite3_value_numeric_type( values[0] );
    if ( ( type == SQLITE_FLOAT )||( type == SQLITE_INTEGER ) ) {
        st->total_data += row_wt * sqlite3_value_double( values[0] );
        st->total_wt   += row_wt;
    }
}


static void wt_avg_final( sqlite3_context *ctx )
{
    double         result = 0.0;
    wt_avg_state   *st = (wt_avg_state*)sqlite3_aggregate_context( ctx,
                                               sizeof( wt_avg_state ) );
    if ( st == NULL ) {
        sqlite3_result_error_nomem( ctx );
        return;
    }

    if ( st->total_wt != 0.0 ) {
        result = st->total_data / st->total_wt;
    }
    sqlite3_result_double( ctx, result );
}


int wtavg_init( sqlite3 *db, char **error, const sqlite3_api_routines *api )
{
    SQLITE_EXTENSION_INIT2(api);

    sqlite3_create_function( db, "wtavg", 1, SQLITE_UTF8,
            NULL, NULL, wt_avg_step, wt_avg_final );
    sqlite3_create_function( db, "wtavg", 2, SQLITE_UTF8,
            NULL, NULL, wt_avg_step, wt_avg_final );

    return SQLITE_OK;
}

gcc by default compiles and link a full program that's why it complains about a missing main entry point. You may have more luck compiling a shared library `gcc -shared -fPIC` — Alex Jasmin, Nov 12 '10 at 14:08
`mbp:eco821 richard$ gcc -shared -fPIC wtavg wtavg.c -Wall -W -O2 -L/usr/local/lib -lsqlite3 i686-apple-darwin10-gcc-4.2.1: wtavg: No such file or directory wtavg.c: In function ‘wtavg_init’: wtavg.c:63: warning: unused parameter ‘error’` — Richard Herron, Nov 12 '10 at 14:26
Try something like `gcc -shared -fPIC -o wtavg.so wtavg.c -Wall -W -O2 -L/usr/local/lib -lsqlite3` — Alex Jasmin, Nov 12 '10 at 14:28
`mbp:eco821 richard$ gcc -shared -fPIC -o wtavg.so wtavg.c -Wall -W -O2 -L/usr/local/lib -lsqlite3 wtavg.c: In function ‘wtavg_init’: wtavg.c:63: warning: unused parameter ‘error’` — Richard Herron, Nov 12 '10 at 14:33
I'm on a Mac right now. Maybe the SQLite install is atypical? When I took C++ as an elective, I used Visual Studio, so these mechanics are lost on me. — Richard Herron, Nov 12 '10 at 14:34
That's what it says: a waring about not using the error parameter in wtavg_init. If you know what you are doing just ignore it. — Alex Jasmin, Nov 12 '10 at 14:34
SQLite still doesn't see the function. `sqlite> SELECT wtavg(A) FROM temp; SQL error: no such function: wtavg` — Richard Herron, Nov 12 '10 at 14:36
Don't you have to load your extension first? `SELECT load_extension('wtavg.so');` — Alex Jasmin, Nov 12 '10 at 14:46
... or `SELECT load_extension('wtavg.so', 'wtavg_init');` I guess since wtavg_init appears to be your entry point. — Alex Jasmin, Nov 12 '10 at 14:56
`sqlite> SELECT load_extension('wt_avg.so', 'wtavg_init'); SQL error: no such function: load_extension` — Richard Herron, Nov 12 '10 at 14:59
Just tried it on a Mac and it seems /usr/bin/sqlite3 doesn't support dynamic extension loading. I was able to compile your extension and load it with the sqlite3 from MacPorts though. — Alex Jasmin, Nov 12 '10 at 15:26
MacPorts! OK, I'll try that. I was wondering why I saw forum posts about MacPorts... — Richard Herron, Nov 12 '10 at 15:30
JACKPOT! it took me a while to figure out how to install sqlite3 in MacPorts (I had to force a bunch of stuff), but now it works!!! Thanks!!! One last question: Do I need to change the link for SQLite? I had to load the db with `/opt/local/bin/sqlite temp.sqlite` and I call SQLite from another program (R) via a library. — Richard Herron, Nov 12 '10 at 16:13
That's getting somewhat unrelated from you initial question.... Perhaps the safest way to use the MacPorts version of the SQLite library from another program is to use a MacPorts version of that program. Say Python MacPort instead of the Python from Apple. — Alex Jasmin, Nov 12 '10 at 16:43
Okay... That's a long stream of comments. I'll try to summarize that in an answer you can accept — Alex Jasmin, Nov 12 '10 at 16:50

Alex Jasmin · Accepted Answer · 2010-11-12T18:14:55.603

The user defined function should be compiled as shared library file:

gcc -shared -fPIC -o wtavg.so wtavg.c -lsqlite3

That shared library can then be loaded using an SQLite statement:

SELECT load_extension('/path/to/wt_avg.so', 'wtavg_init');

Unfortunately the version of sqlite3 provided by Apple doesn't support loading shared libraries. You can use sqlite from MacPorts instead. MacPorts programs linking against sqlite should also have the ability to load user defined functions this way.

When using SQLite inside another program however the extension loading mechanism may be disabled for security reasons. In Python for instance you have to call con.enable_load_extension(True) to enable it.

THANK YOU SO MUCH! I learned a lot this morning with your help! — Richard Herron, Nov 12 '10 at 18:20

How to compile a user defined function in SQLite

1 Answers1