[sqlite] Access to connection handle

[agentsim]

It would be useful to be able to get the underlying sqlite3* handle so that the database can be further configured, or to be able to customize the creation and configuration of the connection using the handle.

For instance, I would like to be able to call sqlite3_create_collation_v2 to set up a collating sequence using the unicase crate.

[mehcode]

SQLite calls it a database connection handle so I'd probably expect a method named as_raw_handle() ( trying to mimic how the std exposes raw internals ) on SqliteConnection that returns the raw sqlite3 pointer.

[agentsim]

Thanks for the tip.
Looking at this a little more, as_raw_handle() doesn't seem like it would be enough. It is unlikely (I think) that a user would want some connections configured with a custom collation, and others not.

So perhaps the correct approach is to extend SqliteConnectOptions to allow for this kind of customization?

[abonander]

We're actually discussing the option to configure an on-connect callback to Pool here: #263

[agentsim]

Yes, that sounds like the right idea.

[abonander]

While the method to get the raw handle is being added in #438, it also sounds like having a way to safely introduce new collations would be a good feature-add for the SQLite API. Maybe something like:

impl SqliteConnection {
    pub fn add_collation(&mut self, name: &str, collation: impl Fn(&str, &str) -> Ordering + Send + Sync + 'static) { ... }
}

[abonander]

The implementation would be simpler but the interface less flexible if the callback was defined as extern "C" fn(&str, &str) -> Ordering.

[agentsim]

I'll give this a shot. The feature already exists in rusqlite. Should I aim to copy what they've done or pull in that crate as a dependency?

It would still require #263 to ensure that connections are configured consistently.

[abonander]

We probably don't want to pull in rusqlite just for this feature. However, their implementation looks a lot more sensible than the one I was formulating in my head so I think it makes a decent starting point.

Let's not directly copy it though. Now I'm thinking that add_collation() should actually be a part of SqliteConnectionOptions which adds it to a map, and then if that map is available/nonempty when a connection is created it then adds the collations to the database. This also means it doesn't need #263 to work with Pool, but the data pointer needs to be based on Arc instead of Box since the destroy callback would be invoked for each connection.

I would like to somehow make this work with sqlite3_collation_needed to lazily add the collations but I'm not sure what we're supposed to do if we don't have a collation for a given name; the callback itself is supposed to add the collation but if it doesn't add the collation does the callback just continually get invoked in a loop or does it error? If it errors we can propagate that out but otherwise it could cause a deadlock. I'll need to dig through the source to see because the documentation doesn't say.

[abonander]

It looks like it invokes the callback once for any collation that it can't find and emit error if it still can't find the collation after invoking the callback: https://github.com/sqlite/sqlite/blob/42a630b1daf1df13850095251cc73be84dbdeac8/src/callback.c#L217-L231

This means we can definitely use sqlite3_collation_needed if we want to lazily install the collations, although I'm not sure what benefit that would confer, maybe slightly lower memory usage?