Expose SQLite internals

[merijn]

The SQL dialect of SQLite is rather limited, but this is usually not a problem as it's easy to extend SQLite with custom functions when needed. Unfortunately the API exposed by selda-sqlite completely encapsulates the underlying SQLite connection with SeldaConnection, making it impossible to make use of this ability when using Selda. I would like to be able to get access to the SQLite connection for this kind of power use.

[valderman]

This sounds a bit fragile, but possibly a candidate for a Database.Selda.SQLite.Unsafe module. Is direct access to the connection all you need here, or do you have any other ideas for making such an API more helpful?

[merijn]

It's fragile in a "not portable across different Selda implementations/databases", yeah. On the other hand I think such a SQLite.Unsafe module is invaluable. In one of my current projects I'm using persistent and SQLite, and there I ran into the issue that I'd started with persistent implemented a lot of things. Halfway through the project found out I needed a custom SQL function to get adequate performance, but that meant either manually reimplementing all the persistent stuff I was using in something like direct-sqlite, or patching persistent to expose the connection (which is what I ended up doing).

I'm not really happy with persistent as a whole, so I'm hoping Selda can replace it for me in future projects, so I'm basically trying to avoid ending up in the same situation in the future ;)

I can imagine a more elaborate API that also exports some of the other SQLite functions not covered by the Selda API. But designing such an API is hard and a substantial amount of work. I think exposing the raw SQLite connection gives the most "bang for the buck", as that at least makes it possible to use the "global" SQLite API to do what you need, without needing a lot of changes in selda-sqlite or lots of maintenance.

To give you an idea, my current persistent approach looks like this: https://github.com/merijn/GPU-benchmarks/blob/master/benchmark-analysis/src/Core.hs#L205-L206 So just registering some functions for the duration of the entire connection/transaction. There's also some other APIs like setting timeouts, setting concurrency/writer mode, etc.

It's probably possible to have a more complete/useful API on top of this, but I think it makes more sense to first have people experiment with the raw connection to see what sort of functionality is used/needed and then anything nicer/more advanced can always be added later to selda-sqlite.

[tungd]

If you just need the raw connection you can probably just import Database.Selda.Backend. Something like this:

import Database.Selda
import Database.Selda.Backend
import Database.Selda.SQLite

main :: IO ()
main = print =<< withSQLite "db.sqlite" $ do
  backend <- seldaBackend
  liftIO $ runStmt backend "select * from items" []

The down side is that you'll need to do the value exaction from SqlValue yourself.

[merijn]

@tungd That doesn't seem to address the issue at all. Your example is merely running an SQL statement and the SqlBackend does not expose the SQLite connection pointer at all.

[tungd]

@merijn sorry I misunderstood. In that case Selda SQLite is based on the direct-sqlite package, and it has already supported creating custom function (https://hackage.haskell.org/package/direct-sqlite-2.3.23/docs/Database-SQLite3.html#g:8). I guess the simplest way for us would be to also export the sqliteBackend from selda-sqlite, so that you'll be able to construct the backend manually.

As far as I can tell this would also make more sense since this functionality is specific to SQLite.

main :: IO ()
main = do
  let filePath = "file:data.db"

  conn <- open filePath
  createFunction conn ...

  seldaConnection <- newConnection (sqliteBackend conn) filePath
  ...

[merijn]

Excellent, this works for me! Thanks!