How to load extensions into SQLite?
Solution 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 beint(sqlite3*, char **, const sqlite3_api_routines *)
. - In SQL you can try
SELECT load_extension('md5.so', 'sqlite3_extension_init');
or simplySELECT load_extension('md5.so');
You can try to compile md5.c
, and from the sqlite shell use .load md5.so
Solution 2
You can follow loadext.html:
- Download the
.c
file of the extension - 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) - Load the library via
sqlite3_load_extension(PATH)
(.load PATH
in the CLI orSELECT load_extension(PATH)
in queries), wherePATH
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.)
Comments
-
Chucky almost 2 years
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?
-
user3116232 almost 8 yearsI am currently having a similar question. I am trying to execute SELECT load_extension('mod_spatialite') in sqlite, I have the corresponding mod_spatialite.dll - file, but I can't get it to work. Where do I need to put the .dll-file?
-
lef over 6 years@user3116232 Are you using full path to the .dll in load_extension() statement? If so, try not to do so. I realised that the deduction of extension entry point name is not working with Windows-style paths.
-
SL5net over 5 years@user3116232 i have the same problem. how you solved it?
-
James Kerfoot about 2 years@user3116232 Same problem here! Any resolution?