sqlite - Loadable Extensions

Beginning after version 3.3.6, SQLite has the ability to load new SQL functions and collating sequences from shared libraries and DLLs. This means that you no longer have to recompile SQLite in order to add new functions and collations.

There is a new experimental API call sqlite3_load_extension() that does the loading. It is accessed from the shell using the ".load" command:

.load filename  ?entrypoint?

The filename is the name of the shared library or DLL. The entrypoint is the name of an initialization function within the shared library. If the entry point is omitted, a suitable entry point name is constructed from the filename.

The following code is an example of how to build a loadable extension:

#include <sqlite3ext.h>
SQLITE_EXTENSION_INIT1


/*
** The half() SQL function returns half of its input value.
*/
static void halfFunc(
  sqlite3_context *context,
  int argc,
  sqlite3_value **argv
){
  sqlite3_result_double(context, 0.5*sqlite3_value_double(argv[0]));
}

int demo2_init(sqlite3 *db, char **pzErrMsg, const sqlite3_api_routines *pApi){
  SQLITE_EXTENSION_INIT2(pApi)
  sqlite3_create_function(db, "half", 1, SQLITE_ANY, 0, halfFunc, 0, 0);
  return 0;
}

In the example above, the green text is boiler-plate that should appear in every loadable extension (except that you may want to change the name of the initialization function to something other than "demo2".) The blue text is stuff you enter. In the example, a single SQL function that does nothing but multiply its input by 0.5 is shown. A real loadable extension would probably do something more useful.

Additional documentation is forthcoming...

Wish List

Automatic loading of extension shared libraries when a database is first opened. This would benefit SQLite users that do not use the SQLite shell program, and only use the sqlite3 shared library. An sqlite specific table in each database loaded could be consulted with a list of shared library names to load. These extension shared libraries would not have paths or file suffixes (.dll or .so). The user would be expected to set up an appropriate search path via the native OS' shared library loading mechanism (PATH, LD_LIBRARY_PATH, or equivalent). The suffix would be determined at sqlite3 compile time to be .dll, .so, or whatever is appropriate for the platform. Such a table might look like this:

  create table sqlite_extensions (
    load INTEGER PRIMARY KEY, -- the order in which the extensions
                              -- are loaded and initialized

    name TEXT, -- name of the extension shared library.
               -- Note: both the path and suffix is NOT to be included in name.

    init TEXT, -- name of the shared library entry point init function

    fini TEXT  -- name of the shared library entry point unload function.
               -- shared library unloading will be in the reverse order
               -- of loading.
  );