TODO: Currently, there is no tests for loadable SQLite extensions. We could probably add one of the example extensions SQLite provides (for example rot13).

cc. @asg017

Thanks for putting this together @erlend-aasland , this looks awesome! Will try it out myself soon.

Re tests: Feel free to use this sample extension. I wrote it myself, also contributed to the Datasette project, and includes multiple entrypoints.

/*
** This file implements a SQLite extension with multiple entrypoints.
**
** The default entrypoint, sqlite3_ext_init, has a single function "a".
** The 1st alternate entrypoint, sqlite3_ext_b_init, has a single function "b".
** The 2nd alternate entrypoint, sqlite3_ext_c_init, has a single function "c".
**
** Compiling instructions: 
**     https://www.sqlite.org/loadext.html#compiling_a_loadable_extension
**
*/

#include "sqlite3ext.h"

SQLITE_EXTENSION_INIT1

// SQL function that returns back the value supplied during sqlite3_create_function()
static void func(sqlite3_context *context, int argc, sqlite3_value **argv) {
  sqlite3_result_text(context, (char *) sqlite3_user_data(context), -1, SQLITE_STATIC);
}


// The default entrypoint, since it matches the "ext.dylib"/"ext.so" name
#ifdef _WIN32
__declspec(dllexport)
#endif
int sqlite3_ext_init(sqlite3 *db, char **pzErrMsg, const sqlite3_api_routines *pApi) {
  SQLITE_EXTENSION_INIT2(pApi);
  return sqlite3_create_function(db, "a", 0, 0, "a", func, 0, 0);
}

// Alternate entrypoint #1
#ifdef _WIN32
__declspec(dllexport)
#endif
int sqlite3_ext_b_init(sqlite3 *db, char **pzErrMsg, const sqlite3_api_routines *pApi) {
  SQLITE_EXTENSION_INIT2(pApi);
  return sqlite3_create_function(db, "b", 0, 0, "b", func, 0, 0);
}

// Alternate entrypoint #2
#ifdef _WIN32
__declspec(dllexport)
#endif
int sqlite3_ext_c_init(sqlite3 *db, char **pzErrMsg, const sqlite3_api_routines *pApi) {
  SQLITE_EXTENSION_INIT2(pApi);
  return sqlite3_create_function(db, "c", 0, 0, "c", func, 0, 0);
}

Compiling SQLite extensions are always tricky, but it should need just something like this:

gcc ext.c -fPIC -shared -o ext.dylib

With possibly -I path/to/sqlite if it's not globally installed, and only .dylib for MacOS system. For windows, change the suffx to .dll, and Linux to .so. To test that extension, it should be something like:

import sqlite3
db = sqlite3.connect(":memory:");

# You can leave off the dylib/so/dll suffix, since SQLite will implicitly add it

# loading without an entrypoint will resolve to the default sqlite3_ext_init entrypoint
db.load_extension("./ext");
assert db.execute("select a()").fetchone()[0] == "a"

db.load_extension("./ext", "sqlite3_ext_b_init");
assert db.execute("select b()").fetchone()[0] == "b"

db.load_extension("./ext", "sqlite3_ext_c_init");
assert db.execute("select c()").fetchone()[0] == "c"

Let me know if you want some help adding an extension to the test suite!

Will try it out myself soon.

@asg017: did you get to try it out?

@erlend-aasland Just tried this out, and it works as expected!

I compiled the above ext.c extension to ext.so, compiled a version of python on this branch, and ran the following:

import sqlite3    
db = sqlite3.connect(":memory:")

db.load_extension('./ext')
assert db.execute("select a()").fetchone()[0] == "a"

db.load_extension("./ext", entrypoint="sqlite3_ext_b_init")
assert db.execute("select b()").fetchone()[0] == "b"

db.load_extension("./ext", entrypoint="sqlite3_ext_c_init")
assert db.execute("select c()").fetchone()[0] == "c"

And it worked! Thanks again for this work.

@asg017, thanks! I'll revisit this PR soon; I need to tweak the sqlite3 extension module build to incorporate the SQLite extension check first. I think I'll do that as a separate PR.

(Ping me in two weeks if you did not hear anything yet.)

Hey @erlend-aasland , just pinging here after two weeks! Let me know if I can help in any way

Hey @erlend-aasland , just pinging here after two weeks! Let me know if I can help in any way

Thanks, I'll revisit this shortly!

I need to tweak the sqlite3 extension module build to incorporate the SQLite extension check first. I think I'll do that as a separate PR.

FTR, I won't let this block this PR.

@AlexWaygood, do you want to take a look at the added docs?

Thanks for the reviews; I'll land this later today.