How to load extensions into SQLite?

Question

I need a standard deviation function in SQLite. I have found one here:

http://www.sqlite.org/contrib?orderby=date

but its part of an extension file to SQLite. I've never installed one of these before and I don't know how to. I found this existing function, load_extension, at http://www.sqlite.org/lang_corefunc.html, but I don't understand what the parameters X and Y are.

Basically, I need someone to give me a step by step guide on how to install the aggregate extension file. Can anyone do this?

Answer 1

SQLite extensions are libraries with dynamic linkage. You can find some examples here (This is a fossil repository, click on “login/fill captcha” to enable hyperlinks). See for example md5.c.

• load_extension must be enabled in SQLite (pragma IIRC)
• it requires as first argument the path of the library
• The second argument is the name of the entry point function (in md5.c it is sqlite3_extension_init). Its prototype must be int(sqlite3*, char **, const sqlite3_api_routines *).
• In SQL you can try SELECT load_extension('md5.so', 'sqlite3_extension_init'); or simply SELECT load_extension('md5.so');

You can try to compile md5.c, and from the sqlite shell use .load md5.so

Answer 2

You can follow loadext.html:

1. Download the .c file of the extension
2. Compile the .c file locally using commands from Section 3, "Compiling A Loadable Extension" in loadext.csv (check a related SO question if you have compilation errors)
3. Load the library via sqlite3_load_extension(PATH) (.load PATH in the CLI or SELECT load_extension(PATH) in queries), where PATH is a relative or full path to the compiled library, preferably without filename extension for compatibility reasons (i.e., .load csv, not .load csv.dylib or whatever extension your system adds)

For extension-functions on MacOS, it would be:

curl --location --output extension-functions.c 'https://www.sqlite.org/contrib/download/extension-functions.c?get=25'
gcc -g -fPIC -dynamiclib extension-functions.c -o extension-functions.dylib

Testing the extension:

SELECT load_extension("extension-functions");
CREATE TABLE test(x REAL);
INSERT INTO test (x) VALUES
  (1),
  (2),
  (3),
  (4),
  (5);
SELECT stdev(x) FROM test;

Expected output: 1.58113883008419

Also Larry Brasfield at sqlite.org explains where you can put the compiled library to make it accessible across the system:

If you provide an absolute path in the .load command or to the sqlite3_load_extension() call, or to the load_extension() SQLite function from SQL (when extension loading is enabled), then the extension specified by that path will be loaded (if it can be.)

If you want to name the extension to one of the load mechanisms without an absolute path, then you must arrange that the compiled extension can be located by the search that dlopen(...) conducts. That can vary, but usually it examines directories specified by an environment variable, LD_LIBRARY_PATH, in the order listed by its colon-separated values left-to-right, then /lib and /usr/lib.

So, you can put your extension somewhere already searched, or you could use the "env" command to launch the SQLite CLI with a modified value for $LD_LIBRARY_PATH that includes where you put the extension. Or you might simply modify that environment variable for a session in which you are using the CLI. (This can lead to subtle problems, so be sure you understand the potential effects going into that.)