Support user defined functions

[lrowe]

It would be useful to support loading user defined functions to use with filters and index with an expression index. Perhaps a similar mechanism to the VFS plugin could be used to hook this up?

See also:

• Rusqlite: "functions allows you to load Rust closures into SQLite connections for use in queries."
• sql.js: create_function

[dyedgreen]

I really like this idea and think something like this could work quite nicely!

Doing this from JS would probably be relatively slow, but you could build a table of functions in the VFS side, which you can register functions into, and dynamically dispatch to these on the WASM side 🤔

[dyedgreen]

Just to capture some recent thoughts here: This is definitely possible (see https://www.sqlite.org/c3ref/create_function.html):

• We can have users register (per-db) functions; the SQLite API allows us to pass some arbitrary meta data, which we can use to e.g. number our functions. So on the SQLite/C side, functions are dispatched to by their index in an array on the Js side
• Working with function parameters/arguments is slightly annoying; but can be done as well:
  1. We attach the received argc and sqlite_values** to a global variable in the WASM binary
  2. Then we can provide functions similar to the column_count, column_int, ... functions (e.g. arg_count, arg_int, etc...) which we can call on the Js side of the dispatch to acquire the Js arguments to pass to the user defined function
  3. Finally, we also need to bind the result_int, etc ... functions to be able to call on the Js side after the user function returns / throws

While this is a little bit of work, I don't think it would be particularly difficult. I'm unsure how performant the final solution can be (we have to pay the cost of two translation layers), and it might be awkward to type (you'll essentially have to provide a variadic function that accepts any possible set of SQLite values).

Bit I think overall, this would be quite cool to have and definitely something I want to look into!

[dyedgreen]

Closing this since the basic functionality now exists.