What would it take to build a WASM distribution of SQLite with percentile or other common stats functions built in?

A good deal of work not to easily summarized ;).

For starters, every function exported from C to wasm has to be manually accounted for in the build process. There is no magic with which one can just link in new C code and have it automatically integrated. You would need to extend the build system found in the ext/wasm directory of the canonical build tree, adding JS and C code, as well as modifying metadata files used by the makefile.

Unless, that is, the functionality you want can be written in Javascript, in which case we have numerous code examples of how to do that from client-level code. We do not, however, have any such functions readily available.

Please pardon my brevity - typing on a handheld device and there is neither a brief answer nor an existing recipe for doing what you are asking. Perhaps there will be in 6 months, but we're not there yet.

Why do you think there would be any difference when compiling the amalgamation with WASM vs compiling it with a normal compiler? I realize that WASM is a make believe CPU operating in an extremely limited environment, but I shouldn't think there would be any difference since nothing you are talking about does anything un-primitive.

. but I shouldn't think there would be any difference since nothing you are talking about does anything un-primitive.

The compilation is trivial, indeed, but getting it integrated into the JS environment requires manual effort per function. Not tons of effort, but it's not intuitive and it's very dependent on the Emscripten (build tools) way of doing things.

Edit: that said... The auto-extension API could probably be used to do the heavy lifting. i'll look into that.

Edit: that said... The auto-extension API could probably be used to do the heavy lifting. i'll look into that.

THe question was about "built-in" extensions. Just like the date.c functions or the func.c functions are "built-in". There is no "external references" to any of these functions -- they are entirely internal.

If you add functions to the amalgamation (ie, build them into sqlite3.c) and use the regular hooks to load them, why would they need any JS whatsoever?

When the "unhex" function was added, did you have to provide a Javascript wrapper?

It would seem rather painful if every single C function needed a JavaScript wrapper -- there are many thousands of C functions included in the amalgamation sqlite3.c file, of which only a handful have external exposure. I would have expected that only functions with "external visibility" would need javascript wrappers.

If you add functions to the amalgamation (ie, build them into sqlite3.c) and use the regular hooks to load them, why would they need any JS whatsoever?

Indeed, none is needed if they're only visible to C/SQL. i'll get the build extended to enable drop-in extensions that way.

If you're willing to dig through some code, the cr-sqlite extension compiles into the SQLite WASM distribution.

You can see the required makefile changes here (note that this includes a number of changes to allow us to link to extensions written in Rust. You can ignore the RS_ bits):

https://github.com/vlcn-io/sqlite/pull/2/files#diff-1d1f80d9fce4f00960c8f4150442c08951bc352a12b2350ebbff40a906dec256

You'll need to pass a compilation flag to tell SQLite to call your extension at startup. In my case I made a C function called "core_init" and told SQLite to call it with this flag:

-DSQLITE_EXTRA_INIT=core_init

The core_init definition being here:

https://github.com/vlcn-io/cr-sqlite/blob/main/core/src/core_init.c

Given sqlean is a set of functions that are invoked from SQL, you won't have to do any JS wrapping. Modifying the makefile to link sqlean and call it at startup should be all you need to do.

With respect to extension-functions, the wa-sqlite project bundles them in its WASM port so that's another example you can look at.

They register them on DB open here:

https://github.com/rhashimoto/wa-sqlite/blob/ae68e8a46e08e4723d36253d7759fdbfe1d612b6/src/sqlite-api.js#L488

And include them in the build here:

https://github.com/rhashimoto/wa-sqlite/blob/ae68e8a46e08e4723d36253d7759fdbfe1d612b6/Makefile#L174-L176

You could do something similar with the official WASM port and, rather than register them from JS, I believe you could use the core_init trick here too.

I guess if I were to summarize:

• link against the library in ext/wasm/GNUMakefile
• add SQLITE_EXTRA_INIT to register the library at startup (what cr-sqlite does) or register it from JavaScript on DBOpen (what wa-sqlite does)

I guess if I were to summarize:

Thank you very much for the details :). i will extend the build such that clients just need to drop in a C file with a well-defined name and SQLITE_EXTRA_INIT symbol, and that will be compiled in automatically.

I'm trying to understand my options for building statistics functions into a WASM distribution of SQLite ...

The build process has just been extended to enable inclusion of custom client-side code. Simply drop a file named sqlite3_wasm_extra_init.c into the ext/wasm directory of the source tree and run "make" (or, depending on the platform, "gmake" - it must be GNU make).

If the canonical build process finds the file sqlite3_wasm_extra_init.c in the main wasm build directory, it arranges to include that file in the build of sqlite3.wasm and defines SQLITE_EXTRA_INIT=sqlite3_wasm_extra_init.

That file must define the function sqlite3_wasm_extra_init() with this signature:

int sqlite3_wasm_extra_init(const char *)

SQLite will call it with an argument of NULL one time during sqlite3_initialize(). If it returns non-0, initialization of the library will fail.

With that hook in place, clients should be able to bundle any 3rd-party extensions they like, plugging them in via a call to sqlite3_auto_extension() made from sqlite3_wasm_extra_init().

This is a first draft of this mechanism and it will be improved based on feedback.

This seems to work quite well.

One minor issue is that the out-of-the-box build process also includes sqlite3_wasm_extra_init.c when building the fiddle app. This can cause errors such as error: duplicate symbol: sqlite3_regexp_init if extensions built into fiddle are included in sqlite3_wasm_extra_init.c.

One minor issue is that the out-of-the-box build process also includes sqlite3_wasm_extra_init.c when building the fiddle app. This can cause errors such as error: duplicate symbol: sqlite3_regexp_init if extensions built into fiddle are included in sqlite3_wasm_extra_init.c.

Good point. Perhaps we need two different extension options - one for fiddle and one for the main lib (which fiddle includes). Maybe fiddle_extra_init.c?

Edit, for clarity: the root of the problem here is that fiddle includes shell.c, which itself may contain any number of extensions which aren't in the core amalgamation.

One minor issue is that the out-of-the-box build process also includes sqlite3_wasm_extra_init.c when building the fiddle app.

We might already (accidentally) have a solution for this: the fiddle build includes -DSQLITE_SHELL_FIDDLE. Client code in sqlite3_wasm_extra_init.c could use that to block out inclusions of extensions which they know to be in the shell. The problem with that, though, is that the list of extensions included in the shell evolves over time and future additions may well re-introduce duplicate definitions until the client code is updated to account for the change. It seems unlikely that we can avoid that eventuality entirely, though.

Would this option for avoiding duplicate definitions suffice for you? If not, i'm open to suggestions for how best to resolve it.

My goal is to add extension functions to sqlite3.wasm without the build process failing.

Perhaps something like this to prevent fiddle from being built if sqlite3_wasm_extra_init.c exists:

ifeq (,$(sqlite3_wasm_extra_init.c))
include fiddle.make
endif

Perhaps something like this to prevent fiddle from being built if sqlite3_wasm_extra_init.c exists:

Something functionally identical to that had crossed my mind (unconditionally not adding sqlite3_wasm_extra_init.c to the fiddle build), but it seems likely that someone out there will want to build such extensions into their own copy of fiddle.

If we can't find a prettier solution, that would be my fallback (until someone explicitly wants to extend their fiddle copy, then we'll need a better solution).

Perhaps something like this to prevent fiddle from being built if sqlite3_wasm_extra_init.c exists:

Just checked in: both fiddle and speedtest1 (which we use for benchmarking) no longer include the extra-init stuff. At some point someone will want to extend their copy of fiddle, so this may change, but as George Clooney famously said in Dusk 'til Dawn, "it's not a problem until you make it a problem," so we'll wait until that's a problem before fixing it (unless, perhaps, some elegant idea comes to mind before then).

This has also been a longstanding issue for the normal (non-WASM) version of SQLite3. For some reason, there seems to be an insistance on adding extensions to the CLI (from what I understand, the equivalent of your fiddle) rather than to the core SQLite3 library (your generic WASM thing), when it is clear that the applicability of the extension is to the core and not simply to the CLI.

This has been an ongoing and unsolved problem for many years -- still to this day general applicability extensions are being added in the wrong place -- added to the CLI when they have applicability outside the CLI.

You will just have to put up with this like everyone else. This is apparently by design.

This has been an ongoing and unsolved problem for many years -- still to this day general applicability extensions are being added in the wrong place -- added to the CLI when they have applicability outside the CLI.

Now that the project has, in effect, two "big" client-level APIs to maintain - the CLI and the wasm build - that may slowly change. That's just speculation/suspicion on my part - i have no insider information suggesting that it will. Only time will tell.