See comments in #46

According to the official SQLite documentation, one needs to pass the -DSQLITE_THREADSAFE=2 flag, when compiling SQLite, to run it in multi-thread mode. The multi-thread mode improves performance slightly, when each connection is used only by a single thread. This is often a case when using sqlite with some Pool (e.g. from the resource-pool library).

I propose to add a Cabal flag that passes enables multithreading by passing a proper option to cc-options.

Hi, all.

I am very proud of what direct-sqlite has grown to become. I don't mind admitting that it's been the most successful project I've ever placed in the wild, and several of you have contributed numerous important patches and design discussion.

I've had a consistent pattern over the past year of responding to pull requests more slowly, and I'll note that none of the most important patches have been written by me!, and I think it's probably time to, at the very least, give other people commit bits and hand off the responsibility for Hackage uploads. I think anyone willing to do this also deserves to be credited as author, so I'm more than happy to pass off ownership of the repo if someone wants to step up.

Thoughts on who it should be? I'd like this to be by consensus of all the regular contributors. My own nomination would be @nurpax because they've been very consistent and appear to understand the design issues as well as I do. @joeyadams is the second obvious choice. I feel like @lykahb, @sethfowler, and @mwotton should at the very least get to participate in this discussion as well. Did I leave anybody out? Please feel free to pull whoever you want into this thread.

It looks like sqlite 3.8.x offers some substantial performance improvements due to the new query planner. I'd love to take advantage of that in direct-sqlite.

It seems like there's a bug when building the SQLite library on systems using gcc10.

Related links:

• https://sqlite.org/forum/forumpost/845dd0be91?t=h
• https://www.sqlite.org/src/info/04e1edd8e5821a37

as an odd little side-effect, the cabal solver decided it was better to take direct-sqlite back to version 1.1 rather than downgrade text to 1.1.1.3 - might be worth bumping to text 1.2?

When I run cabal install direct-sqlite -fsystemlib, I get:

Resolving dependencies...
Downloading direct-sqlite-2.3.9...
Configuring direct-sqlite-2.3.9...
cabal: Package direct-sqlite-2.3.9 can't be built on this system.
Failed to install direct-sqlite-2.3.9
cabal: Error: some packages failed to install:
direct-sqlite-2.3.9 failed during the building phase. The exception was:
ExitFailure 1

The latest semigroup is 0.20. Can there be a direct-sqlite hackage release allowing it? Is semigroup actually needed for recent versions of GHC?

Currently, direct-sqlite contains FFI bindings, performs marshaling, and throws exceptions. This does not give some users enough control:

• persistent-sqlite wants to avoid unnecessary conversions.
• I want more precise error handling (e.g. distinguish between syntax errors and disk corruption) for an application I'm working on.

I propose we introduce the following alternative APIs:

• Database.SQLite3.Direct: Similar to Database.SQLite3, but:
  • Return errors instead of throwing them. Use data types to represent errors, not just strings.
  • Only use cheap conversions, meaning no String or Text. Accepting or returning UTF8-encoded ByteStrings is fine, since persistent-sqlite has to do that anyway.
• Database.SQLite3.Bindings: Direct FFI bindings and enumeration decoding functions.

Database.SQLite3 would use Database.SQLite3.Direct, and Database.SQLite3.Direct would use Database.SQLite3.Bindings.

I've already implemented Database.SQLite3.Bindings, and implemented Database.SQLite3 in terms of it. I would submit a pull request, but I'd rather wait until I'm done romping around in Database.SQLite3, to avoid clashing with other contributions. I plan to implement Database.SQLite3.Direct pretty soon.

Anyone have an issue with this? Thanks for the input.

As per this comment:

7b4bc72#commitcomment-2014302

the text package version in direct-sqlite.cabal should be lowered to text 0.11. There's probably no reason for the higher version. The higher version causes another instance of text to be installed, as many people already have text from Haskell Platform.

There's probably nothing in direct-sqlite's use of text that would require higher than 0.11.

I guess having at least a flag to turn on math functions like sqrt would be very useful.

I see that SQLText stores its contents as a String. This may be a problem for performance.

Would it be possible to change it to ByteString instead? (I realize this is an API change.)

It's easy to go from ByteString to String, but going from String to ByteString means increased memory thrashing.

I realize that in some cases it's possible to work-around this by using SQLBlob instead. But using the "columns" function after "step" means many fields often get converted to Strings.

As a library consumer, I want to open sqllite3 from byte string and perform operations (as supported by library).

I think sqllite3_deserialize directive can enable this.

Usecase

When working within (non-standard filesystem) - S3, tarball, zip, etc - We usually retrieve byte string of SQLite file. We are interested in working with this.

Workaround

Right now, the workaround is: to persist this SQLite file in some temp file, do necessary operations and remove temp file.

Reference

https://www.sqlite.org/c3ref/deserialize.html

Steps to reproduce:

1. stack unpack direct-sqlite
2. cd direct-sqlite-*
3. stack init
4. stack test

Output:

Creating dist/test/direct-sqlite-test-database.db
test: SQLite3 returned ErrorCan'tOpen while attempting to perform open "dist/test/direct-sqlite-test-database.db": unable to open database file

Completed 2 action(s).
Test suite failure for package direct-sqlite-2.3.16
    test:  exited with: ExitFailure 1
Logs printed to console

This is not a Stack-specific issue, it was originally discovered via the Stackage build process, which calls out to the Setup.hs file directly.

When many threads use the same connection, exceptions thrown by some calls like step can be misleading, because of race conditions. This happens because when step gets some error code, it does a followup call on c_sqlite3_errmsg which brings the error message from SQlite. However, it is reported by SQlite that in Serialized mode, this error message can change if between the two calls, some other thread use the same connection https://www.sqlite.org/c3ref/errcode.html (search "serialized threading mode").

This can get critical if applications depend on this error message: for example they expect that a Constraint Violation of a Primary key is thrown, but they get that a Not Null Constraint Violation. Note that the main error (in this case Constraint Violation) is not prone to this race condition, but the additional details are.

I have run the following example and I got all sorts of colorful messages:

import Database.SQLite.Simple

createPeopleTableSQL :: Query
createPeopleTableSQL =
  "CREATE TABLE People (name varchar(50) NOT NULL UNIQUE               \
  \                    ,id   int         NOT NULL UNIQUE              \
  \                    );"

testErrors :: IO ()
testErrors = do
    conn <- open ":memory:"
    execute_ conn createPeopleTableSQL
    _  <- toMsg $ execute conn "INSERT INTO People (name, id) VALUES (?, ?)" ( ("Ann", 0) :: (Text, Int) )
    forkIO $ thread0 conn
    forkIO $ thread1 conn
    threadDelay 100000

thread0 :: Connection -> IO ()
thread0 conn = do
    forM_ [1..10000] $ \n -> do
        result1 <- toMsg $ execute conn "INSERT INTO People (name, id) VALUES (?, ?)" ( ("Ann", n) :: (Text, Int) )
        unless (result1 == "SQLite3 returned ErrorConstraint while attempting to perform step: UNIQUE constraint failed: People.name")
            (error $ ("thread 0 trial " <> show n <> " got: " :: String) <> result1)

thread1 :: Connection -> IO ()
thread1 conn = do
    forM_ [1..10000] $ \n -> do
        _ <- toMsg $ execute conn "INSERT INTO People (name, id) VALUES (?, ?)" ( (show n, 0) :: (Text, Int) )
        return ()

toMsg :: IO () -> IO String
toMsg action = do
    res <- try action
    case res of
        Left (e :: SomeException)  -> return $ displayException e
        Right _ -> return $ "no error"

Some results I got:

• main: thread 0 trial 17 got: SQLite3 returned ErrorConstraint while attempting to perform step: UNIQUE constraint failed: People.id
  CallStack (from HasCallStack): ...
• main : thread 0 trial 21 got: SQLite3 returned ErrorConstraint while attempting to perform step: not an error
  CallStack (from HasCallStack): ...

In first result note that the message UNIQUE constraint failed: People.id is what thread1 excepts, as it adds an entry with the same id, which should be Unique. But because of the race condition this result is thrown on thread0!

I understand this is not exactly a bug of this library, but a strange behaviour of SQlite. SQlite suggests the use of sqlite3_mutex_enter(), if we want to have reliably the correct message (sqlite-direct does not have bindings for this one) or to use the extended error codes. Are there any plans to add any of those?

First of all, thank you for this wonderful library that powers sqlite-simple which I use.

I’ve been trying to run a small web service using SQLite and I’ve been running into unexpected database is locked errors. I then stumbled upon http://beets.io/blog/sqlite-nightmare.html and https://www.sqlite.org/threadsafe.html.

Could you please document what threading mode this binding was compiled with? Alternatively, is there a way to set the threading mode at runtime using sqlite3_open_v2 and SQLITE_OPEN_NOMUTEX (multi-thread mode_ or SQLITE_OPEN_FULLMUTEX (serialized mode)?

I would be happy to help out but I am fairly new to Haskell and have even less experience with C. However, I’d be happy to help test any new implementations.

Thanks 😄

I have a Haskell program that inserts data into an SQLite file. This program runs fairly slowly, it processes ~3900 rows in ~3 seconds.

I have also written a Python counterpart, and it processes the same dataset in ~0.16 seconds, or about 18 times faster.

After a lot of experiments, I simplified the core part of the program to the following function, which isolates the other factors by only using necessary functions provided by the library:

          add stmt time stock = do {-# SCC bind #-} bind stmt $ toFields time stock
                                   {-# SCC step #-} stepNoCB stmt
                                   {-# SCC reset #-} reset stmt

The profile shows that stepNoCB consumes most of the time:

         step                                    QuantRating                                  src/QuantRating.hs:74:53-65                                     12395        3903    0.0    0.0    89.0    0.1
          stepNoCB                               Database.SQLite3                             Database/SQLite3.hs:(417,1)-(418,83)                            12396        3903    0.0    0.0    89.0    0.0
           checkError                            Database.SQLite3                             Database/SQLite3.hs:277:1-54                                    12400        3903    0.0    0.0     0.0    0.0
           stepNoCB                              Database.SQLite3.Direct                      Database/SQLite3/Direct.hs:(461,1)-(462,47)                     12397        3903   89.0    0.0    89.0    0.0
            toStepResult                         Database.SQLite3.Direct                      Database/SQLite3/Direct.hs:(223,1)-(227,29)                     12398        3903    0.0    0.0     0.0    0.0
             decodeError                         Database.SQLite3.Bindings.Types              Database/SQLite3/Bindings/Types.hsc:(264,1)-(296,66)            12399        3903    0.0    0.0     0.0    0.0

I checked the definition of stepNoCB and it is merely a thin wrapper around an FFI call:

-- | <https://www.sqlite.org/c3ref/step.html>
--
-- Faster step for statements that don't callback to Haskell
-- functions (e.g. by using custom SQL functions).
stepNoCB :: Statement -> IO (Either Error StepResult)
stepNoCB (Statement stmt) =
    toStepResult <$> c_sqlite3_step_unsafe stmt

The system is macOS Mojave 10.14.6. Does anyone have any idea about why it is slow? Thanks.

Hey,

I wanted to use SQLite with ICU enabled, so here's a patch to enable it. Perhaps useful to add to Direct-SQLite-proper, too.

diff --git a/direct-sqlite.cabal b/direct-sqlite.cabal
index deb5d30..67bcc4e 100644
--- a/direct-sqlite.cabal
+++ b/direct-sqlite.cabal
@@ -48,6 +48,10 @@ flag json1
   description: Enable json1 extension.
   default: True
 
+flag icu
+  description: Enable the ICU extension.
+  default: False
+
 Library
   exposed-modules:
     Database.SQLite3
@@ -86,6 +90,11 @@ Library
     if flag(json1) {
       cc-options: -DSQLITE_ENABLE_JSON1
     }
+
+    if flag(icu) {
+      cc-options: -DSQLITE_ENABLE_ICU
+      extra-libraries: icui18n icuuc icudata
+    }
   }
 
   include-dirs: .

There should be a list of contributors in the readme, and it should include Janne Hellsten and Joey Adams. It's not just common courtesy, though it's that too; it's necessary to track for copyright purposes. When projects don't carefully track code donations, anyone could retroactively assert a claim and force them to no longer be open-source, which I'm sure nobody would want.

I'm guessing this is a 64-bit issue, but that's really just a guess. With both GHC 7.4.2 and 7.6.3, I get:

### Error in:   11:ColumnName               
user error (Pattern match failure in do expression at test/Main.hs:434:7-13)

This is when testing the assertion that columnName for minBound returns Nothing. In fact, it returns Just "id".

I might have just missed how to do this, but I'd like to open databases in a read-only manner - seems like we'd need to use sqlite3_open_v2, to pass extra flags?

Hi Irene,

thank you very much for this nice package, it works great.

However, I tried today to build a project of mine on MS Windows XP, and it failed on the package :

Loading package direct-sqlite-2.3.13 ... linking ... ghc.exe: C:\MinGW\msys\1.0
home(...).cabal-sandbox\i386-windows-ghc-7.4.1\direct-sqlite-2.3.13\libHSdirect-
sqlite-2.3.13.a: unknown symbol ___fixunsdfdi' ghc.exe: unable to load packagedirect-sqlite-2.3.13'

I don't know what to do from here. Can you help me please ?

The setup is WinXP 32 + MINGW + GHC 7.4 + cabal 1.18.0
All happen in a clean cabal sandox.

Thank you, regards,

I was trying to upgrade SQLite to 3.38.0, but this test failed after replacing the C files with the newer version. A discussion is available here, and I am about to change this test in my pull request.

Int64 is used in direct-sqlite in all functions where sqlite3_int64 is used in the C interface. Do you think it's worth considering that sqlite3_int64 is not required to be a 64 bits integer?

One option is to use some kind of autoconf script to define SQLITE_INT64_TYPE, but this seems an overkill. Another is to replace the typedefs in .h and .c with int64_t and uint64_t. Of course, I'm almost sure there are no platforms today where you could actually have problems if you let everything exactly as it is now.

The C api defines functions sqlite3_status and sqlite3_status64 that provide access to a number of global memory statistics. It would be useful to have proper Haskell bindings to these functions.

Hey,

If I'm not mistaken, shouldn't direct-sqlite.cabal contain a reference to hsc2hs? There is a Types.hsc file in the repo, after all. I stumbled upon this seeing the following error from the Haskell Language Server/HIE BIOS:

cabal-3.6.2.0: The program 'hsc2hs' is required but it could not be found.

Regular compiling via Cabal works, of course, but perhaps because Cabal deduces more than HLS/HIE BIOS.

Anywho, throwing the following to direct-sqlite.cabal seems to have fixed the problem:

build-tool-depends: hsc2hs:hsc2hs

Hey

Given Direct SQLite comes with SQLite bundled, maybe it's time to upgrade SQLite again? :) As of today, at least, v3.27.2 is the latest (https://www.sqlite.org/news.html).

Thanks!

https://www.sqlite.org/c3ref/busy_timeout.html

in python

Query parameter binding function

bind :: Statement -> [SQLData] -> IO ()

should check for the error case when the length of [SQLData] doesn't match with how many unique query parameters were present in the SQL query.

You can check the # of query params from a prepared statement by using bindParameterCount.

Would be a good idea to check the sqlite3 API specs for these parts first though.

I know this is kind of a long shot to post here, but I'm kind of desperate. I have an application written on top of sqlite-simple, which uses direct-sqlite under the hood; and on some repeated runs of the same unit tests, I get ErrorMisuse and no idea of where to look next. All of the connections are executing from a single application thread. I don't prepare any of the statements myself, that all happens in library code or in deterministic instances.

From a successful run:

Sqlite's debug output (Sqlite.setTrace conn (Just $ traceM . Text.unpack)):

SELECT base32 FROM hash WHERE id = 2

My debug output:

query "SELECT base32 FROM hash WHERE id = ?"
  input: Only {fromOnly = HashId 2}
 output: [Only {fromOnly = UnsafeBase32Hex {toText = "iipvjg8ufk4mohtnad5du7nk1ge6cp4gpoe1pbaa4il910b1t19eam27nk3nmufomplej5g520uqv1qo2kambnolaqpfbbbif111ch0"}}]

From a failing run:

Sqlite debug output: (none)
My debug output:

query "SELECT base32 FROM hash WHERE id = ?"
  input: Only {fromOnly = HashId 2}
(and crashed)

SQLite3 returned ErrorMisuse while attempting to perform prepare "SELECT base32 FROM hash WHERE id = ?": bad parameter or other API misuse

I know there's not a lot to go on here, but it seems like "not a me thing". Can you offer any suggestions for how to find out more about what's going wrong?

text-1.0 is released and 1.1 is coming soon

I wonder if this was intentional in @joeyadams new code:

newtype ParamIndex = ParamIndex CInt
    deriving (Eq, Ord, Enum, Num, Real, Integral)

This specifies ParamIndex to be a CInt instead of just an Int. This has surprising consequences, like:

-- in sqlite-simple code that uses direct-sqlite
  ParamIndex stmtParamCount <- bindParameterCount stmt
  when (length qp /= stmtParamCount) (throwColumnMismatch qp stmtParamCount)

Leads to a type error:

Database/SQLite/Simple.hs:115:22:
    Couldn't match expected type `Int'
                with actual type `Foreign.C.Types.CInt'
    In the second argument of `(/=)', namely `stmtParamCount'
    In the first argument of `when', namely
      `(length qp /= stmtParamCount)'
    In a stmt of a 'do' block:
      when
        (length qp /= stmtParamCount)
        (throwColumnMismatch qp stmtParamCount)

stmtParamCount here will be a CInt which at least to me was a surprise. I would expect counts like these to just use normal Ints, rather than types that are used for C bindings.

I think this will surprise users.

IMO the direct-sqlite public API should use just Ints, not CInts.

This is pursuant to a request from "yitz" in #haskell-blah, who wants to use full-text search with direct-sqlite. After discussion it turns out that no API change is needed to support his usage scenario; he only needs to ensure that it's actually built in the first place.

As you can see at https://matrix.hackage.haskell.org/package/direct-sqlite@1550399426 the most recent direct-sqlite release failed to build on GHC 7.8 and older due to <$> not being in scope. This had a wide ranging fallout as it effectively broke all default-computed build-plans for those GHC versions that depend on direct-sqlite, such as e.g. https://matrix.hackage.haskell.org/package/sqlite-simple@1550483883

I've already revised the affected release via https://hackage.haskell.org/package/direct-sqlite-2.3.24/revisions/

You can inspect the resulting build-report matrix over at https://matrix.hackage.haskell.org/package/direct-sqlite@1550518781

There's no immediate need for action as the metadata has been corrected already on Hackage. But please either adapt the version bound for future releases (or restore compatibility with pre-GHC 7.10) to avoid this regression repeating with the next release of direct-sqlite.

Please let me know if you have any questions.

I was hoping I could use FTS5 by blindly adding -DSQLITE_ENABLE_FTS5 to the cabal file, since it looks like the FTS5 code is included in sqlite3.c. The library builds and passes cabal test, but it doesn't actually work. Here's what my ghci session looks like (I'm using sqlite-simple, but I don't see why that would make a difference. My sqlite-simple is built against the modified direct-sqlite.)

:set -XOverloadedStrings
import Database.SQLite.Simple
conn <- open "test.sqlite3"
execute_ conn "CREATE VIRTUAL TABLE fts5test USING fts5(data)"
execute_ conn "INSERT INTO fts5test values (\"This is a test\")"
query_ conn "PRAGMA compile_options;" :: IO [(Only String)]

Output

[Only {fromOnly = "COMPILER=gcc-7.2.0"},Only {fromOnly = "ENABLE_FTS3"},Only {fromOnly = "ENABLE_FTS3_PARENTHESIS"},Only {fromOnly = "ENABLE_FTS4"},Only {fromOnly = "ENABLE_FTS5"},Only {fromOnly = "SYSTEM_MALLOC"},Only {fromOnly = "THREADSAFE=1"}]

So far so good, it looks like FTS5 is enabled. But then:

query_ conn "SELECT * FROM fts5test WHERE data MATCH \'test\'" :: IO [Only String]

gives me

*** Exception: Sqlite3 return ErrorError while attempting to perform step: unable to use function MATCH in the request context

[sic]. However, the same query works fine in my system's sqlite3 executable. Accordingly, when I repeat all of the above steps with cabal configure -f systemlib, the last query works just fine.

This ticket seems to suggest the stat64 workaround is no longer needed since GHC 7.8.1

I ran across this while updating sqlite in persistent-sqlite.

http://permalink.gmane.org/gmane.comp.db.sqlite.general/90549

exciting we should probably upgrade:)