How to load extensions into SQLite?

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 aggre开发者_如何学JAVAgate extension file. Can anyone do this?

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

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.)

Here is the complete way to do this on Windows:

1. Install MinGW with GCC
2. Download the .c file of wanted extension (for example uuid.c)
3. Download from this page Source Code and Precompiled Binaries for Windows
4. Put files from "sqlite-dll-win64-x64-XXXXXXX.zip" (or win32) and "sqlite-tools-win32-x86-XXXXXXX.zip" to the System32 folder
5. Put wanted uuid.c file and files from "sqlite-amalgamation-XXXXXXX.zip" to any folder you want to
6. Run CMD and go to the folder with uuid.c file
7. Type gcc -g -shared uuid.c -o uuid.so
8. If someone (even yourself) told you that on Windows you should use .dll extension, spit in this person's face
9. Shove the resulting .so file into the folder System32
10. Run CMD again and type .load uuid OR type SELECT load_extension('uuid') as SQL script

You're done here!

@James Kerfoot, I mention you as my "contemporary". So, you have to compile .c file into .so and put into the System32 folder