Loading Sqlite Extensions

[creichert]

Is there any method for loading sqlite extensions in persistent? I am attempting to use spatialite. I can load it in the sqlite shell with the following commands.

sqlite> SELECT load_extension('libspatialite');
sqlite> SELECT HEX(GeomFromText('POINT(10 20)'));
00010000000000000000000024400000000000003440000000000000244000000000000034407C0100000000000000000024400000000000003440FE

or

sqlite> .load 'libspatialite'

This is definitely something I would be interested in looking into if there isn't enough existing support.

My ultimate goal is to be able to work with geospatial data in spatialite and/or PostGIS. I did see one mention of this here: https://groups.google.com/forum/#!searchin/yesodweb/GIS$20geospatial/yesodweb/NQlEbtCfNio/XmcEMBME40cJ

Once I can get the extension to load, I am planning to see how far I can get with rawSql wrappers first (as Greg mentioned). Any tips would be appreciated.

[snoyberg]

Are you able to do it using rawExecute?

[creichert]

I am not. Here is a self-contained example (with libspatialite-dev installed).

{-# LANGUAGE OverloadedStrings #-}
import Data.Text (Text)
import Database.Persist
import Database.Persist.Sqlite

main :: IO ()
main = runSqlite ":memory:" $ rawExecute "SELECT load_extension('libspatialite')" []

I receive the following error:

$ runhaskell  Example.hs 
Example.hs: user error (SQLite3 returned ErrorError while attempting to perform step.)

[snoyberg]

Ahh... I think that's because it's inside a transaction. Can you try using rawExecute to execute 'COMMIT' first and 'BEGIN' after?

[creichert]

I still receive the same error message after trying:

rawExecute "COMMIT; SELECT load_extension('libspatialite'); BEGIN;" []

[creichert]

Ah, I may have understood and I did test:

main = runSqlite ":memory:" $ do
    rawExecute "COMMIT;" [] 
    rawExecute "SELECT load_extension('libspatialite');" [] 
    rawExecute " BEGIN;" [] 

EDIT: This doesn't make much sense if they are in transactions.

[snoyberg]

Can you check that you can load the extension using the raw interface: http://hackage.haskell.org/package/persistent-sqlite-2.0.0.1/docs/Database-Sqlite.html. And are you sure you're linking against the system's sqlite (via -fsystemlib) instead of the bundled one, which I do not believe would be able to handle libspatialite.

[creichert]

Ok, I've never used the Database.Sqlite implementation directly so let me know if I'm wrong here:

christopher@main:~/dev/persistent/persistent-sqlite (master)
$ cabal configure -fsystemlib
Resolving dependencies...
Configuring persistent-sqlite-2.0.0.1...

christopher@main:~/dev/persistent/persistent-sqlite (master)
$ cabal repl
Preprocessing library persistent-sqlite-2.0.0.1...
[1 of 2] Compiling Database.Sqlite  ( Database/Sqlite.hs, dist/build/Database/Sqlite.o )
[2 of 2] Compiling Database.Persist.Sqlite ( Database/Persist/Sqlite.hs, dist/build/Database/Persist/Sqlite.o )
GHCi, version 7.8.3: http://www.haskell.org/ghc/  :? for help
...
Loading package tagged-0.7.2 ... linking ... done.
Loading package persistent-2.0.0.1 ... linking ... done.
Loading object (dynamic) /usr/lib/gcc/x86_64-linux-gnu/4.9/../../../x86_64-linux-gnu/libsqlite3.so ... done
Loading object (dynamic) /usr/lib/gcc/x86_64-linux-gnu/4.9/../../../x86_64-linux-gnu/libpthread.so ... done
final link ... done
[1 of 2] Compiling Database.Sqlite  ( Database/Sqlite.hs, interpreted )
[2 of 2] Compiling Database.Persist.Sqlite ( Database/Persist/Sqlite.hs, interpreted )
Ok, modules loaded: Database.Sqlite, Database.Persist.Sqlite.
*Database.Sqlite> :m Database.Sqlite 
Prelude Database.Sqlite> import Data.Text (Text, pack)
Prelude Database.Sqlite Data.Text> conn <- open (pack "../../rets/rets.db")
Prelude Database.Sqlite Data.Text> stmt <- prepare conn $ pack "SELECT load_extension('libspatialite');" 
Prelude Database.Sqlite Data.Text> step stmt 
*** Exception: user error (SQLite3 returned ErrorError while attempting to perform step.)

I'm going to try to look into this a bit myself but I'm not sure I have a clue why this is happening yet.

[creichert]

The file cbits/sqlite3.c does include the sqlite3_load_extension function on line 5271. http://www.sqlite.org/c3ref/load_extension.html.

Extension loading must be enabled using sqlite3_enable_load_extension() prior to calling this API, otherwise an error will be returned.

Maybe this has something to do with it and the sqlite3 executable enables it?

I'm still looking into how to work around this.

[creichert]

Ok, I hacked together something which I think is starting to work:

diff --git a/persistent-sqlite/Database/Sqlite.hs b/persistent-sqlite/Database/Sqlite.hs
index fe0a2e3..75856ed 100644
--- a/persistent-sqlite/Database/Sqlite.hs
+++ b/persistent-sqlite/Database/Sqlite.hs
@@ -23,7 +23,8 @@ module Database.Sqlite  (
                          bind,
                          column,
                          columns,
-                         changes
+                         changes,
+                         enableLoadExtension
                         )
     where

@@ -220,6 +221,19 @@ step statement = do
     ErrorDone -> return Done
     _ -> sqlError Nothing "step" error

+foreign import ccall "sqlite3_enable_load_extension"
+  enableLoadExtensionC :: Ptr () -> Int -> IO Int
+enableLoadExtensionError :: Connection -> Int -> IO Error
+enableLoadExtensionError (Connection _ (Connection' db)) onoff = do
+  error <- enableLoadExtensionC db onoff
+  return $ decodeError error
+enableLoadExtension :: Connection -> Int -> IO Int
+enableLoadExtension conn onoff = do
+  error <- enableLoadExtensionError conn onoff
+  case error of
+    ErrorOK -> return 1 -- TODO: Add a sensible return type.
+    _ -> sqlError Nothing "load_extension" error
+
 foreign import ccall "sqlite3_reset"
   resetC :: Ptr () -> IO Int
 resetError :: Statement -> IO Error

any thoughts? It looks like some other functions ignore the Int return type when pattern matching on ErrorOk so I will do that.

*Database.Sqlite> :m Database.Sqlite 
Prelude Database.Sqlite> import Data.Text (Text, pack)
Prelude Database.Sqlite Data.Text> conn <- open (pack "db.sqlite")
Prelude Database.Sqlite Data.Text> enableLoadExtension conn 1
1
Prelude Database.Sqlite Data.Text> stmt <- prepare conn $ pack "SELECT load_extension('libspatialite');" 
Prelude Database.Sqlite Data.Text> step stmt 
Row
Prelude Database.Sqlite Data.Text> hex <- prepare conn $ pack "SELECT HEX(GeomFromText('POINT(10 20)'));" 
Prelude Database.Sqlite Data.Text> step hex
Row

I'm not sure how to handle the Row yet.

As a side note, I also tried looking for a way to configure default extensions for sqlite without much luck.

[snoyberg]

That looks like a good addition to me, though I have to plead ignorance of the extension API.

[creichert]

Thanks for steering me in the right direction for testing.

Unless there is an objection, I am going to look into this a bit further in my own fork before submitting a pull request.

I want to get a bit more familiar with the extension API and I'm still uncertain on where this will fit nicely in Database.Persist.Sqlite.

[snoyberg]

That sounds good to me, I'll look forward to a PR in the future.

[joeyh]

Relatedly, I want to use sqlite in WAL mode. I tried to do it using rawExecute, closing the current transaction first, similar to the two ways @creichert showed above, and neither worked. No error, but the database didn't enter WAL mode.

What did work:

Prelude Database.Sqlite Data.Text> conn <- open (pack "my.db" )
Prelude Database.Sqlite Data.Text> stmt <- prepare conn $ pack "PRAGMA journal_mode=WAL;"
Prelude Database.Sqlite Data.Text> step stmt 

[MaxGabriel]

When I was working on enabling/disabling foreign keys in SQLite for this PR https://github.com/yesodweb/yesod-scaffold/pull/57/files, I learned that PRAGMA foreign_keys = x is a silent no-op in a transaction, so I used the same approach joeyh did. Persistent functions like rawExecute create transactions so it isn't possible to use that. Perhaps the same restriction applies to enabling WAL mode?