I'd like to propose an idea that allows third-party extensions to be statically built into SQLite without code modifications to SQLite or third-party packages that use it.

One of the applications I support heavily uses SQLite in its ecosystem. This includes a number of custom virtual table extensions and SQL functions. We build our own static library of SQLite, which is used to build our own sqlite3 CLI tool, our own build of the SQLite Python module (we embed Python into our application), and a number of other "off the self" tools. These environments make it very difficult to use something like sqlite3_auto_extension() to register our extensions, so we usually modify the sqlite3.c file directly, adding our extension(s) to the built-in init list.

Perhaps I'm missing something, but I haven't been able to figure out how to do this without modifying the sqlite3.c file directly. This is less than ideal, as having to copy and modify external project code makes version control and source management that much more complex. In addition to change management, as corporate developers are likely aware, modifying third-party FOOS packages often triggers policy and compliance concerns, even for projects such as SQLite that have extremely liberal licensing terms. It's a pain.

What I would propose is a simple function stub that is normally disabled, but becomes active with the right compile directive. Should a developer define the SQLITE_ENABLE_USER_EXTENSION def, they would be responsible for defining an init function and providing it at link time. That's it. This would enable us to link our extension directly into the build, without having to augment the SQLite source code, nor any of the third-party tools we build SQLite into (such as Python).

As an example, I picked the name xsqlite3UserExtensionInit(), but I wasn't sure what name made the most sense. I would suggest something that's likely to be globally unique in any source base, so it seems like the name should include the substring sqlite3, but know that's reserved when used as a prefix. The x seemed the least-worst choice, but perhaps the SQLite team already has a naming convention that fits this type of situation a bit more tidy.

Thoughts? Am I missing something about how this can already be done?

-j

Patch against sqlite-amalgamation-3420000:

*** sqlite3.c	2023-05-16 08:45:23.000000000 -0500
--- sqlite3_mod.c	2023-08-16 13:45:28.000000000 -0500
*************** SQLITE_PRIVATE int sqlite3Fts5Init(sqlit
*** 174943,174948 ****
--- 174943,174952 ----
  SQLITE_PRIVATE int sqlite3StmtVtabInit(sqlite3*);
  #endif
  
+ #ifdef SQLITE_ENABLE_USER_EXTENSION
+ int xsqlite3UserExtensionInit(sqlite3*);
+ #endif
+ 
  /*
  ** An array of pointers to extension initializer functions for
  ** built-in extensions.
*************** static int (*const sqlite3BuiltinExtensi
*** 174982,174987 ****
--- 174986,174995 ----
  #ifdef SQLITE_ENABLE_BYTECODE_VTAB
    sqlite3VdbeBytecodeVtabInit,
  #endif
+ 
+ #ifdef SQLITE_ENABLE_USER_EXTENSION
+   xsqlite3UserExtensionInit,
+ #endif
  };
  
  #ifndef SQLITE_AMALGAMATION

Should a developer define the SQLITE_ENABLE_USER_EXTENSION def, they would be responsible for defining an init function and providing it at link time.

Isn't this basically what SQLITE_EXTRA_INIT provides? The builder of sqlite3.o provides a function name with -DSQLITE_EXTRA_INIT=foo and must define/link in int foo(const char *). The function is always passed a NULL pointer - i'm not sure what the idea behind the string argument was.

Similarly, there's a shutdown counterpart. From sqlite3.c:

#ifdef SQLITE_EXTRA_SHUTDOWN
    void SQLITE_EXTRA_SHUTDOWN(void);
    SQLITE_EXTRA_SHUTDOWN();
#endif

That's not quite the same as your proposal, in that the function signature differs, but the EXTRA_INIT routine could install auto-extensions, which might(?) resolve your timing issues with installing auto-extensions.

Edit: of course, that approach would leave you subject to client code clearing the list of auto-extensions, so wouldn't be as bullet-proof as your proposal.

Isn't this basically what SQLITE_EXTRA_INIT provides?

Interesting. I wasn't aware of this, and yes, it provides about 90% of what I want... it would be perfect except for, as you pointed out, code that clears auto-extensions. I also like how it allows me to define the function name. Thank you for pointing it out.

I still think my idea is worth consideration, but I might look into using this to refactor some of our code.

I just saw check-in 423e77277a from Stephan that is almost exactly what is needed.

The only thing missing is a change to "makefile.in" and "main.mk" to include the user-extension in the amalgamation.

Stephan, could you add that, please?

The only thing missing is a change to "makefile.in" and "main.mk" to include the user-extension in the amalgamation.

In order to link in custom content you'd need to set that new flag when building sqlite3.c and link in your custom files. Those aren't things the canonical build files will do for you.

Or perhaps i'm misunderstanding what you're asking for?

(Note, also, that that change is preliminary. Whether or not that feature will get merged to trunk is as yet undetermined.)

Yes, a minor misunderstanding.

What I always do in builds on Linux and Termux is adding my extension as source into the amalgamation by adding it after the line $(TOP)/ext/misc/stmt.c in "makefile.in" (line 353 in trunk) and "main.mk" (line 231 in trunk).

I figured it was experimental and would not make it into 3.44 - but one can hope...

I meant 3.43 - don't want to sound to pessimistic...

I just noticed check-in fe7365254d adding this to trunk.

Unfortunately that is still missing the changes to makefile.in and main.mk that would add the extension to the amalgamation.

Could this be added?

Unfortunately that is still missing the changes to makefile.in and main.mk that would add the extension to the amalgamation.

i haven't forgotten about looking into that, but it's not on my very-near-term TODO list.

Could this be added?

If you're looking to have the literal names of your files injected into the resulting Makefile, you're probably out of luck. That would require edits to the GNU autotools scripts, and we avoid changing those unless absolutely necessary. We're limited to POSIX-make functionality for the top-level build, so cannot look for files to add at build-time via make features.

What we can easily do is add something like:

# Makefile.in:
...
SRC += \
  $(TOP)/ext/misc/stmt.c
SRC += $(EXTRA_SRC)
# main.mk:
...
SRC += \
  $(TOP)/ext/misc/stmt.c
SRC += $(EXTRA_SRC)

We'd have to convince Richard, but it seems like a potentially nice thing to have and is trivial to add.

That is exactly what I mean.

My apologies for having let this fall through the cracks, but it's now checked in:

$ make sqlite3.c EXTRA_SRC="my.c my-other.c"

will create sqlite3.c with the given list of EXTRA_SRC files appended to it.

I'd like to propose an idea that allows third-party extensions to be statically built into SQLite without code modifications to SQLite or third-party packages that use it.

Jay, the current trunk has your request feature: -DSQLITE_EXTRA_AUTOEXT=funcname and you must then link in funcname with the signature of an auto-extension.

(The makefile part of the request, from the responses, is still TODO.)

Browsing the source, it looks like the secret hidden parameters (pzErrMsg, pThunk) for extensions added with sqlite3_auto_extension() aren't supplied if the init function is on the builtin list. Someone wanting to make an existing extension may need to take that into account.