Expose `sqlite3_create_function_v2` similar to rusqlite

[nyurik]

Is your feature request related to a problem? Please describe. I need to register and use custom SQLite functions, such as hashing functions like md5(...) similar to how sqlite-hashes crate implements them.

Describe the solution you'd like Add create_scalar_function and create_aggregate_function, as done here. Some other functions like the window and removal might also be useful.

Describe alternatives you've considered

• While holding a SQLx SQLite connection, also open the same file using rusqlite crate.
• Or use unsafe code to extract underlying sqlite connection handler and pass it to rusqlite create from raw connection. Neither is very safe by any stretch of imagination

Additional context See also #2656 and the comment by @abonander

[abonander]

The best place for this would be in SqliteConnectOptions, similar to collations: https://docs.rs/sqlx-sqlite/0.7.1/sqlx_sqlite/struct.SqliteConnectOptions.html#method.collation

Making this work with the macros at compile time would require some shenanigans, though.

[JumpyLionnn]

are there any updates on this? i think this feature can have a lot of uses

[nyurik]

For now, I ended up using this approach - lock and get the raw handle, use rusqlite to register , unlock, and rely on sqlite's cleanup to free the registration.

• https://github.com/maplibre/martin/blob/c4af48bba905887b1fd19caa1c943010a823413b/mbtiles/src/mbtiles.rs#L222-L230
• https://github.com/nyurik/sqlite-hashes/blob/8649a51b4a9e77ecc598c54d4f0ab33c0fc129f0/src/scalar.rs#L124

pub async fn attach_hash_fn(conn: &mut SqliteConnection) -> MbtResult<()> {
    let mut handle_lock = conn.lock_handle().await?;
    let handle = handle_lock.as_raw_handle().as_ptr();
    // Safety: we know that the handle is a SQLite connection is locked and is not used anywhere else.
    // The registered functions will be dropped when SQLX drops DB connection.
    let rc = unsafe { sqlite_hashes::rusqlite::Connection::from_handle(handle) }?;
    register_md5_function(&rc)?;    // this calls rc.create_scalar_function(...)
    Ok(())
}