Copyright | (c) 2011 MailRank Inc. (c) 2011-2012 Leon P Smith (c) 2012-2013 Janne Hellsten |
---|---|
License | BSD3 |
Maintainer | Janne Hellsten <jjhellst@gmail.com> |
Portability | portable |
Safe Haskell | Safe-Inferred |
Language | Haskell2010 |
Synopsis
- newtype Query = Query {}
- data Connection = Connection {}
- class ToRow a where
- class FromRow a where
- newtype Only a = Only {
- fromOnly :: a
- data h :. t = h :. t
- data SQLData
- = SQLInteger !Int64
- | SQLFloat !Double
- | SQLText !Text
- | SQLBlob !ByteString
- | SQLNull
- newtype Statement = Statement {}
- newtype ColumnIndex = ColumnIndex ColumnIndex
- data NamedParam where
- (:=) :: ToField v => Text -> v -> NamedParam
- open :: String -> IO Connection
- close :: Connection -> IO ()
- withConnection :: String -> (Connection -> IO a) -> IO a
- setTrace :: Connection -> Maybe (Text -> IO ()) -> IO ()
- query :: (ToRow q, FromRow r) => Connection -> Query -> q -> IO [r]
- query_ :: FromRow r => Connection -> Query -> IO [r]
- queryWith :: ToRow q => RowParser r -> Connection -> Query -> q -> IO [r]
- queryWith_ :: RowParser r -> Connection -> Query -> IO [r]
- queryNamed :: FromRow r => Connection -> Query -> [NamedParam] -> IO [r]
- lastInsertRowId :: Connection -> IO Int64
- changes :: Connection -> IO Int
- totalChanges :: Connection -> IO Int
- fold :: (FromRow row, ToRow params) => Connection -> Query -> params -> a -> (a -> row -> IO a) -> IO a
- fold_ :: FromRow row => Connection -> Query -> a -> (a -> row -> IO a) -> IO a
- foldNamed :: FromRow row => Connection -> Query -> [NamedParam] -> a -> (a -> row -> IO a) -> IO a
- execute :: ToRow q => Connection -> Query -> q -> IO ()
- execute_ :: Connection -> Query -> IO ()
- executeMany :: ToRow q => Connection -> Query -> [q] -> IO ()
- executeNamed :: Connection -> Query -> [NamedParam] -> IO ()
- field :: FromField a => RowParser a
- withTransaction :: Connection -> IO a -> IO a
- withImmediateTransaction :: Connection -> IO a -> IO a
- withExclusiveTransaction :: Connection -> IO a -> IO a
- withSavepoint :: Connection -> IO a -> IO a
- openStatement :: Connection -> Query -> IO Statement
- closeStatement :: Statement -> IO ()
- withStatement :: Connection -> Query -> (Statement -> IO a) -> IO a
- bind :: ToRow params => Statement -> params -> IO ()
- bindNamed :: Statement -> [NamedParam] -> IO ()
- reset :: Statement -> IO ()
- columnName :: Statement -> ColumnIndex -> IO Text
- columnCount :: Statement -> IO ColumnIndex
- withBind :: ToRow params => Statement -> params -> IO a -> IO a
- nextRow :: FromRow r => Statement -> IO (Maybe r)
- data FormatError = FormatError {}
- data ResultError
- = Incompatible { }
- | UnexpectedNull { }
- | ConversionFailed { }
- data SQLError = SQLError {
- sqlError :: !Error
- sqlErrorDetails :: Text
- sqlErrorContext :: Text
- data Error
- = ErrorOK
- | ErrorError
- | ErrorInternal
- | ErrorPermission
- | ErrorAbort
- | ErrorBusy
- | ErrorLocked
- | ErrorNoMemory
- | ErrorReadOnly
- | ErrorInterrupt
- | ErrorIO
- | ErrorCorrupt
- | ErrorNotFound
- | ErrorFull
- | ErrorCan'tOpen
- | ErrorProtocol
- | ErrorEmpty
- | ErrorSchema
- | ErrorTooBig
- | ErrorConstraint
- | ErrorMismatch
- | ErrorMisuse
- | ErrorNoLargeFileSupport
- | ErrorAuthorization
- | ErrorFormat
- | ErrorRange
- | ErrorNotADatabase
- | ErrorNotice
- | ErrorWarning
- | ErrorRow
- | ErrorDone
- | ErrorErrorMissingCollatingSquence
- | ErrorErrorRetry
- | ErrorErrorSnapshot
- | ErrorIORead
- | ErrorIOShortRead
- | ErrorIOWrite
- | ErrorIOFsync
- | ErrorIODirectoryFsync
- | ErrorIOTruncate
- | ErrorIOFstat
- | ErrorIOUnlock
- | ErrorIOReadLock
- | ErrorIOBlocked
- | ErrorIODelete
- | ErrorIONoMemory
- | ErrorIOAccess
- | ErrorIOCheckReservedLock
- | ErrorIOLock
- | ErrorIOClose
- | ErrorIODirectoryClose
- | ErrorIOShmOpen
- | ErrorIOShmSize
- | ErrorIOShmLock
- | ErrorIOShmMap
- | ErrorIOSeek
- | ErrorIODeleteNoEntity
- | ErrorIOMmap
- | ErrorIOGetTempPath
- | ErrorIOConvertedPath
- | ErrorIOVNode
- | ErrorIOAuth
- | ErrorIOBeginAtomic
- | ErrorIOCommitAtomic
- | ErrorIORollbackAtomic
- | ErrorIOData
- | ErrorIOCorruptFilesystem
- | ErrorLockedSharedCache
- | ErrorLockedVirtualTable
- | ErrorBusyRecovery
- | ErrorBusySnapshot
- | ErrorBusyTimeout
- | ErrorCan'tOpenNotTempDirectory
- | ErrorCan'tOpenIsDirectory
- | ErrorCan'tOpenFullPath
- | ErrorCan'tOpenConvertedPath
- | ErrorCan'tOpenDirtyWriteAheadLog
- | ErrorCan'tOpenSymlink
- | ErrorCorruptVirtualTable
- | ErrorCorruptSequence
- | ErrorCorruptIndex
- | ErrorReadOnlyRecovery
- | ErrorReadOnlyCan'tLock
- | ErrorReadOnlyRollback
- | ErrorReadOnlyDatabaseMoved
- | ErrorReadOnlyCan'tInit
- | ErrorReadOnlyDirectory
- | ErrorAbortRollback
- | ErrorConstraintCheck
- | ErrorConstraintCommitHook
- | ErrorConstraintForeignKey
- | ErrorConstraintFunction
- | ErrorConstraintNotNull
- | ErrorConstraintPrimaryKey
- | ErrorConstraintTrigger
- | ErrorConstraintUnique
- | ErrorConstraintVirtualTable
- | ErrorConstraintRowId
- | ErrorConstraintPinned
- | ErrorConstraintDataType
- | ErrorNoticeRecoverWriteAheadLog
- | ErrorNoticeRecoverRollback
- | ErrorWarningAutoIndex
- | ErrorAuthUser
- | ErrorOkLoadPermanently
Examples of use
An example that creates a table test
, inserts a couple of rows
and proceeds to showcase how to update or delete rows. This
example also demonstrates the use of lastInsertRowId
(how to
refer to a previously inserted row) and executeNamed
(an easier
to maintain form of query parameter naming).
{-# LANGUAGE OverloadedStrings #-} import Control.Applicative import qualified Data.Text as T import Database.SQLite.Simple import Database.SQLite.Simple.FromRow data TestField = TestField Int T.Text deriving (Show) instance FromRow TestField where fromRow = TestField <$> field <*> field instance ToRow TestField where toRow (TestField id_ str) = toRow (id_, str) main :: IO () main = do conn <- open "test.db" execute_ conn "CREATE TABLE IF NOT EXISTS test (id INTEGER PRIMARY KEY, str TEXT)" execute conn "INSERT INTO test (str) VALUES (?)" (Only ("test string 2" :: String)) execute conn "INSERT INTO test (id, str) VALUES (?,?)" (TestField 13 "test string 3") rowId <- lastInsertRowId conn executeNamed conn "UPDATE test SET str = :str WHERE id = :id" [":str" := ("updated str" :: T.Text), ":id" := rowId] r <- query_ conn "SELECT * from test" :: IO [TestField] mapM_ print r execute conn "DELETE FROM test WHERE id = ?" (Only rowId) close conn
The Query type
SQL-based applications are somewhat notorious for their susceptibility to attacks through the injection of maliciously crafted data. The primary reason for widespread vulnerability to SQL injections is that many applications are sloppy in handling user data when constructing SQL queries.
This library provides a Query
type and a parameter substitution
facility to address both ease of use and security. A Query
is a
newtype
-wrapped Text
. It intentionally exposes a tiny API that
is not compatible with the Text
API; this makes it difficult to
construct queries from fragments of strings. The query
and
execute
functions require queries to be of type Query
.
To most easily construct a query, enable GHC's OverloadedStrings
language extension and write your query as a normal literal string.
{-# LANGUAGE OverloadedStrings, ScopedTypeVariables #-} import Database.SQLite.Simple hello = do conn <- open "test.db" [[x :: Int]] <- query_ conn "select 2 + 2" print x
A Query
value does not represent the actual query that will be
executed, but is a template for constructing the final query.
Parameter substitution
Since applications need to be able to construct queries with parameters that change, this library uses SQLite's parameter binding query substitution capability.
This library restricts parameter substitution to work only with
named parameters and positional arguments with the "?
" syntax.
The API does not support for mixing these two types of bindings.
Unsupported parameters will be rejected and a FormatError
will be
thrown.
You should always use parameter substitution instead of inlining your dynamic parameters into your queries with messy string concatenation. SQLite will automatically quote and escape your data into these placeholder parameters; this defeats the single most common injection vector for malicious data.
Positional parameters
The Query
template accepted by query
, execute
and fold
can
contain any number of "?
" characters. Both query
and
execute
accept a third argument, typically a tuple. When the
query executes, the first "?
" in the template will be replaced
with the first element of the tuple, the second "?
" with the
second element, and so on. This substitution happens inside the
native SQLite implementation.
For example, given the following Query
template:
select * from user where first_name = ? and age > ?
And a tuple of this form:
("Boris" :: String, 37 :: Int)
The query to be executed will look like this after substitution:
select * from user where first_name = 'Boris' and age > 37
If there is a mismatch between the number of "?
" characters in
your template and the number of elements in your tuple, a
FormatError
will be thrown.
Note that the substitution functions do not attempt to parse or
validate your query. It's up to you to write syntactically valid
SQL, and to ensure that each "?
" in your query template is
matched with the right tuple element.
Named parameters
Named parameters are accepted by queryNamed
, executeNamed
and
foldNamed
. These functions take a list of NamedParam
s which
are key-value pairs binding a value to an argument name. As is the
case with "?
" parameters, named parameters are automatically
escaped by the SQLite library. The parameter names are prefixed
with either :
or @
, e.g. :foo
or @foo
.
Example:
r <-queryNamed
c "SELECT id,text FROM posts WHERE id = :id AND date >= :date" [":id":=
postId, ":date":=
afterDate]
Note that you can mix different value types in the same list. E.g., the following is perfectly legal:
[":id":=
(3 :: Int), ":str":=
("foo" :: String)]
The parameter name (or key) in the NamedParam
must match exactly
the name written in the SQL query. E.g., if you used :foo
in
your SQL statement, you need to use ":foo"
as the parameter
key, not "foo"
. Some libraries like Python's sqlite3
automatically drop the :
character from the name.
Type inference
Automated type inference means that you will often be able to avoid supplying explicit type signatures for the elements of a tuple. However, sometimes the compiler will not be able to infer your types. Consider a case where you write a numeric literal in a parameter tuple:
query conn "select ? + ?" (40,2)
The above query will be rejected by the compiler, because it does
not know the specific numeric types of the literals 40
and 2
.
This is easily fixed:
query conn "select ? + ?" (40 :: Double, 2 :: Double)
The same kind of problem can arise with string literals if you have
the OverloadedStrings
language extension enabled. Again, just
use an explicit type signature if this happens.
Substituting a single parameter
Haskell lacks a single-element tuple type, so if you have just one value you want substituted into a query, what should you do?
To represent a single value val
as a parameter, write a singleton
list [val]
, use Just
val
, or use Only
val
.
Here's an example using a singleton list:
execute conn "insert into users (first_name) values (?)" ["Nuala"]
Or you can use named parameters which do not have this restriction.
Extracting results
The query
and query_
functions return a list of values in the
FromRow
typeclass. This class performs automatic extraction
and type conversion of rows from a query result.
Here is a simple example of how to extract results:
import qualified Data.Text as T xs <- query_ conn "select name,age from users" forM_ xs $ \(name,age) -> putStrLn $ T.unpack name ++ " is " ++ show (age :: Int)
Notice two important details about this code:
- The number of columns we ask for in the query template must
exactly match the number of elements we specify in a row of the
result tuple. If they do not match, a
ResultError
exception will be thrown. - Sometimes, the compiler needs our help in specifying types. It
can infer that
name
must be aText
, due to our use of theunpack
function. However, we have to tell it the type ofage
, as it has no other information to determine the exact type.
Handling null values
The type of a result tuple will look something like this:
(Text, Int, Int)
Although SQL can accommodate NULL
as a value for any of these
types, Haskell cannot. If your result contains columns that may be
NULL
, be sure that you use Maybe
in those positions of your
tuple.
(Text, Maybe Int, Int)
If query
encounters a NULL
in a row where the corresponding
Haskell type is not Maybe
, it will throw a ResultError
exception.
Type conversions
Conversion of SQL values to Haskell values is somewhat permissive. Here are the rules.
- For numeric types, any Haskell type that can accurately represent an SQLite INTEGER is considered "compatible".
- If a numeric incompatibility is found,
query
will throw aResultError
. - SQLite's TEXT type is always encoded in UTF-8. Thus any text
data coming from an SQLite database should always be compatible
with Haskell
String
andText
types. - SQLite's BLOB type will only be conversible to a Haskell
ByteString
.
You can extend conversion support to your own types be adding your
own FromField
/ ToField
instances.
Conversion to/from UTCTime
SQLite's datetime allows for multiple string representations of UTC time. The following formats are supported for reading SQLite times into Haskell UTCTime values:
- YYYY-MM-DD HH:MM
- YYYY-MM-DD HH:MM:SS
- YYYY-MM-DD HH:MM:SS.SSS
- YYYY-MM-DDTHH:MM
- YYYY-MM-DDTHH:MM:SS
- YYYY-MM-DDTHH:MM:SS.SSS
The above may also be optionally followed by a timezone indicator of the form "[+-]HH:MM" or just "Z".
When Haskell UTCTime values are converted into SQLite values (e.g.,
parameters for a query
), the following format is used:
- YYYY-MM-DD HH:MM:SS.SSS
The last ".SSS" subsecond part is dropped if it's zero. No timezone indicator is used when converting from a UTCTime value into an SQLite string. SQLite assumes all datetimes are in UTC time.
The parser and printers are implemented in Database.SQLite.Simple.Time.
Read more about SQLite's time strings in http://sqlite.org/lang_datefunc.html
A query string. This type is intended to make it difficult to construct a SQL query by concatenating string fragments, as that is an extremely common way to accidentally introduce SQL injection vulnerabilities into an application.
This type is an instance of IsString
, so the easiest way to
construct a query is to enable the OverloadedStrings
language
extension and then simply write the query in double quotes.
{-# LANGUAGE OverloadedStrings #-} import Database.SQLite.Simple q :: Query q = "select ?"
The underlying type is a Text
, and literal Haskell strings that
contain Unicode characters will be correctly transformed to UTF-8.
data Connection Source #
Connection to an open database.
You can use connectionHandle
to gain access to the underlying
http://hackage.haskell.org/package/direct-sqlite connection.
This may be useful if you need to access some direct-sqlite
functionality that's not exposed in the sqlite-simple API. This
should be a safe thing to do although mixing both APIs is
discouraged.
A collection type that can be turned into a list of SQLData
elements.
Since version 0.4.18.1 it is possible in some cases to derive a
generic implementation for ToRow
. Refer to the documentation for
FromRow
to see how this can be
done.
Nothing
Instances
class FromRow a where Source #
A collection type that can be converted from a sequence of fields. Instances are provided for tuples up to 10 elements and lists of any length.
Note that instances can defined outside of sqlite-simple, which is often useful. For example, here's an instance for a user-defined pair:
data User = User { name :: String, fileQuota :: Int } instanceFromRow
User where fromRow = User <$>field
<*>field
The number of calls to field
must match the number of fields returned
in a single row of the query result. Otherwise, a ConversionFailed
exception will be thrown.
Note the caveats associated with user-defined implementations of
fromRow
.
Generic implementation
Since version 0.4.18.1 it is possible in some cases to derive a
generic implementation for FromRow
. With a Generic
instance
for User
, the example above could be written:
instance FromRow
User where
With -XDeriveAnyClass -XDerivingStrategies
the same can be written:
deriving anyclass instance FromRow
User
For more details refer to GFromRow
.
Nothing
Instances
The 1-tuple type or single-value "collection".
This type is structurally equivalent to the
Identity
type, but its intent is more
about serving as the anonymous 1-tuple type missing from Haskell for attaching
typeclass instances.
Parameter usage example:
encodeSomething (Only
(42::Int))
Result usage example:
xs <- decodeSomething
forM_ xs $ \(Only
id) -> {- ... -}
Instances
Functor Only | |
Data a => Data (Only a) | |
Defined in Data.Tuple.Only gfoldl :: (forall d b. Data d => c (d -> b) -> d -> c b) -> (forall g. g -> c g) -> Only a -> c (Only a) # gunfold :: (forall b r. Data b => c (b -> r) -> c r) -> (forall r. r -> c r) -> Constr -> c (Only a) # toConstr :: Only a -> Constr # dataTypeOf :: Only a -> DataType # dataCast1 :: Typeable t => (forall d. Data d => c (t d)) -> Maybe (c (Only a)) # dataCast2 :: Typeable t => (forall d e. (Data d, Data e) => c (t d e)) -> Maybe (c (Only a)) # gmapT :: (forall b. Data b => b -> b) -> Only a -> Only a # gmapQl :: (r -> r' -> r) -> r -> (forall d. Data d => d -> r') -> Only a -> r # gmapQr :: forall r r'. (r' -> r -> r) -> r -> (forall d. Data d => d -> r') -> Only a -> r # gmapQ :: (forall d. Data d => d -> u) -> Only a -> [u] # gmapQi :: Int -> (forall d. Data d => d -> u) -> Only a -> u # gmapM :: Monad m => (forall d. Data d => d -> m d) -> Only a -> m (Only a) # gmapMp :: MonadPlus m => (forall d. Data d => d -> m d) -> Only a -> m (Only a) # gmapMo :: MonadPlus m => (forall d. Data d => d -> m d) -> Only a -> m (Only a) # | |
Generic (Only a) | |
Read a => Read (Only a) | |
Show a => Show (Only a) | |
NFData a => NFData (Only a) | |
Defined in Data.Tuple.Only | |
Eq a => Eq (Only a) | |
Ord a => Ord (Only a) | |
FromField a => FromRow (Only a) Source # | |
ToField a => ToRow (Only a) Source # | |
type Rep (Only a) | |
Defined in Data.Tuple.Only |
A composite type to parse your custom data structures without having to define dummy newtype wrappers every time.
instance FromRow MyData where ...
instance FromRow MyData2 where ...
then I can do the following for free:
res <- query' c "..." forM res $ \(MyData{..} :. MyData2{..}) -> do ....
h :. t infixr 3 |
Instances
(Read h, Read t) => Read (h :. t) Source # | |
(Show h, Show t) => Show (h :. t) Source # | |
(Eq h, Eq t) => Eq (h :. t) Source # | |
(Ord h, Ord t) => Ord (h :. t) Source # | |
Defined in Database.SQLite.Simple.Types | |
(FromRow a, FromRow b) => FromRow (a :. b) Source # | |
(ToRow a, ToRow b) => ToRow (a :. b) Source # | |
Instances
Generic SQLData | |
Show SQLData | |
Eq SQLData | |
FromField SQLData Source # | |
Defined in Database.SQLite.Simple.FromField | |
ToField SQLData Source # | |
type Rep SQLData | |
Defined in Database.SQLite3 type Rep SQLData = D1 ('MetaData "SQLData" "Database.SQLite3" "direct-sqlite-2.3.29-2L5GhZ6SNrD5GJ9vI8wbDw" 'False) ((C1 ('MetaCons "SQLInteger" 'PrefixI 'False) (S1 ('MetaSel ('Nothing :: Maybe Symbol) 'NoSourceUnpackedness 'SourceStrict 'DecidedStrict) (Rec0 Int64)) :+: C1 ('MetaCons "SQLFloat" 'PrefixI 'False) (S1 ('MetaSel ('Nothing :: Maybe Symbol) 'NoSourceUnpackedness 'SourceStrict 'DecidedStrict) (Rec0 Double))) :+: (C1 ('MetaCons "SQLText" 'PrefixI 'False) (S1 ('MetaSel ('Nothing :: Maybe Symbol) 'NoSourceUnpackedness 'SourceStrict 'DecidedStrict) (Rec0 Text)) :+: (C1 ('MetaCons "SQLBlob" 'PrefixI 'False) (S1 ('MetaSel ('Nothing :: Maybe Symbol) 'NoSourceUnpackedness 'SourceStrict 'DecidedStrict) (Rec0 ByteString)) :+: C1 ('MetaCons "SQLNull" 'PrefixI 'False) (U1 :: Type -> Type)))) |
newtype ColumnIndex Source #
Index of a column in a result set. Column indices start from 0.
Instances
data NamedParam where Source #
(:=) :: ToField v => Text -> v -> NamedParam infixr 3 |
Instances
Show NamedParam Source # | |
Defined in Database.SQLite.Simple showsPrec :: Int -> NamedParam -> ShowS # show :: NamedParam -> String # showList :: [NamedParam] -> ShowS # |
Connections
open :: String -> IO Connection Source #
Open a database connection to a given file. Will throw an exception if it cannot connect.
Every open
must be closed with a call to close
.
If you specify ":memory:" or an empty string as the input filename, then a private, temporary in-memory database is created for the connection. This database will vanish when you close the connection.
close :: Connection -> IO () Source #
Close a database connection.
withConnection :: String -> (Connection -> IO a) -> IO a Source #
Opens a database connection, executes an action using this connection, and closes the connection, even in the presence of exceptions.
setTrace :: Connection -> Maybe (Text -> IO ()) -> IO () Source #
http://www.sqlite.org/c3ref/profile.html
Enable/disable tracing of SQL execution. Tracing can be disabled
by setting Nothing
as the logger callback.
Warning: If the logger callback throws an exception, your whole program may crash. Enable only for debugging!
Queries that return results
query :: (ToRow q, FromRow r) => Connection -> Query -> q -> IO [r] Source #
Perform a SELECT
or other SQL query that is expected to return
results. All results are retrieved and converted before this
function returns.
When processing large results, this function will consume a lot of
client-side memory. Consider using fold
instead.
Exceptions that may be thrown:
FormatError
: the query string mismatched with given arguments.ResultError
: result conversion failed.
query_ :: FromRow r => Connection -> Query -> IO [r] Source #
A version of query
that does not perform query substitution.
queryWith_ :: RowParser r -> Connection -> Query -> IO [r] Source #
queryNamed :: FromRow r => Connection -> Query -> [NamedParam] -> IO [r] Source #
A version of query
where the query parameters (placeholders)
are named.
Example:
r <-queryNamed
c "SELECT * FROM posts WHERE id=:id AND date>=:date" [":id":=
postId, ":date":=
afterDate]
lastInsertRowId :: Connection -> IO Int64 Source #
Returns the rowid of the most recent successful INSERT on the given database connection.
See also http://www.sqlite.org/c3ref/last_insert_rowid.html.
changes :: Connection -> IO Int Source #
http://www.sqlite.org/c3ref/changes.html
Return the number of rows that were changed, inserted, or deleted
by the most recent INSERT
, DELETE
, or UPDATE
statement.
totalChanges :: Connection -> IO Int Source #
http://www.sqlite.org/c3ref/total_changes.html
Return the total number of row changes caused by INSERT
, DELETE
,
or UPDATE
statements since the Database
was opened.
Queries that stream results
fold :: (FromRow row, ToRow params) => Connection -> Query -> params -> a -> (a -> row -> IO a) -> IO a Source #
Perform a SELECT
or other SQL query that is expected to return results.
Results are converted and fed into the action
callback as they are being
retrieved from the database.
This allows gives the possibility of processing results in constant space (for instance writing them to disk).
Exceptions that may be thrown:
FormatError
: the query string mismatched with given arguments.ResultError
: result conversion failed.
fold_ :: FromRow row => Connection -> Query -> a -> (a -> row -> IO a) -> IO a Source #
A version of fold
which does not perform parameter substitution.
foldNamed :: FromRow row => Connection -> Query -> [NamedParam] -> a -> (a -> row -> IO a) -> IO a Source #
A version of fold
where the query parameters (placeholders) are
named.
Statements that do not return results
execute :: ToRow q => Connection -> Query -> q -> IO () Source #
Execute an INSERT
, UPDATE
, or other SQL query that is not
expected to return results.
Throws FormatError
if the query could not be formatted correctly.
execute_ :: Connection -> Query -> IO () Source #
A version of execute
that does not perform query substitution.
executeMany :: ToRow q => Connection -> Query -> [q] -> IO () Source #
Execute a multi-row INSERT
, UPDATE
, or other SQL query that is not
expected to return results.
Throws FormatError
if the query could not be formatted correctly.
executeNamed :: Connection -> Query -> [NamedParam] -> IO () Source #
A version of execute
where the query parameters (placeholders)
are named.
Transactions
withTransaction :: Connection -> IO a -> IO a Source #
Run an IO action inside a SQL transaction started with BEGIN
TRANSACTION
. If the action throws any kind of an exception, the
transaction will be rolled back with ROLLBACK TRANSACTION
.
Otherwise the results are committed with COMMIT TRANSACTION
.
withImmediateTransaction :: Connection -> IO a -> IO a Source #
Run an IO action inside a SQL transaction started with BEGIN IMMEDIATE
TRANSACTION
, which immediately blocks all other database connections from
writing. The default SQLite3 BEGIN TRANSACTION
does not acquire the write
lock on BEGIN
nor on SELECT
but waits until you try to change data. If
the action throws any kind of an exception, the transaction will be rolled
back with ROLLBACK TRANSACTION
. Otherwise the results are committed with
COMMIT TRANSACTION
.
withExclusiveTransaction :: Connection -> IO a -> IO a Source #
Run an IO action inside a SQL transaction started with BEGIN EXCLUSIVE
TRANSACTION
, which immediately blocks all other database connections from
writing, and other connections from reading (exception: read_uncommitted
connections are allowed to read.) If the action throws any kind of an
exception, the transaction will be rolled back with ROLLBACK TRANSACTION
.
Otherwise the results are committed with COMMIT TRANSACTION
.
withSavepoint :: Connection -> IO a -> IO a Source #
Run an IO action inside an SQLite SAVEPOINT
. If the action throws any
kind of an exception, the transaction will be rolled back to the savepoint
with ROLLBACK TO
. Otherwise the results are released to the outer
transaction if any with RELEASE
.
See https://sqlite.org/lang_savepoint.html for a full description of savepoint semantics.
Low-level statement API for stream access and prepared statements
openStatement :: Connection -> Query -> IO Statement Source #
Opens a prepared statement. A prepared statement must always be closed with
a corresponding call to closeStatement
before closing the connection. Use
nextRow
to iterate on the values returned. Once nextRow
returns
Nothing
, you need to invoke reset
before reexecuting the statement again
with nextRow
.
closeStatement :: Statement -> IO () Source #
Closes a prepared statement.
withStatement :: Connection -> Query -> (Statement -> IO a) -> IO a Source #
Opens a prepared statement, executes an action using this statement, and closes the statement, even in the presence of exceptions.
bindNamed :: Statement -> [NamedParam] -> IO () Source #
Binds named parameters to a prepared statement.
reset :: Statement -> IO () Source #
Resets a statement. This does not reset bound parameters, if any, but
allows the statement to be reexecuted again by invoking nextRow
.
columnName :: Statement -> ColumnIndex -> IO Text Source #
Return the name of a particular column in the result set of a
Statement
. Throws an ArrayException
if the colum index is out
of bounds.
columnCount :: Statement -> IO ColumnIndex Source #
Return number of columns in the query
withBind :: ToRow params => Statement -> params -> IO a -> IO a Source #
Binds parameters to a prepared statement, and reset
s the statement when
the callback completes, even in the presence of exceptions.
Use withBind
to reuse prepared statements. Because it reset
s the
statement after each usage, it avoids a pitfall involving implicit
transactions. SQLite creates an implicit transaction if you don't say
BEGIN
explicitly, and does not commit it until all active statements are
finished with either reset
or closeStatement
.
nextRow :: FromRow r => Statement -> IO (Maybe r) Source #
Extracts the next row from the prepared statement.
Exceptions
data FormatError Source #
Exception thrown if a Query
was malformed.
This may occur if the number of '?
' characters in the query
string does not match the number of parameters provided.
Instances
Exception FormatError Source # | |
Defined in Database.SQLite.Simple | |
Show FormatError Source # | |
Defined in Database.SQLite.Simple showsPrec :: Int -> FormatError -> ShowS # show :: FormatError -> String # showList :: [FormatError] -> ShowS # | |
Eq FormatError Source # | |
Defined in Database.SQLite.Simple (==) :: FormatError -> FormatError -> Bool # (/=) :: FormatError -> FormatError -> Bool # |
data ResultError Source #
Exception thrown if conversion from a SQL value to a Haskell value fails.
Incompatible | The SQL and Haskell types are not compatible. |
| |
UnexpectedNull | A SQL |
| |
ConversionFailed | The SQL value could not be parsed, or could not be represented as a valid Haskell value, or an unexpected low-level error occurred (e.g. mismatch between metadata and actual data in a row). |
|
Instances
Exception ResultError Source # | |
Defined in Database.SQLite.Simple.FromField | |
Show ResultError Source # | |
Defined in Database.SQLite.Simple.FromField showsPrec :: Int -> ResultError -> ShowS # show :: ResultError -> String # showList :: [ResultError] -> ShowS # | |
Eq ResultError Source # | |
Defined in Database.SQLite.Simple.FromField (==) :: ResultError -> ResultError -> Bool # (/=) :: ResultError -> ResultError -> Bool # |
Exception thrown when SQLite3 reports an error.
direct-sqlite may throw other types of exceptions if you misuse the API.
SQLError | |
|
Instances
Exception SQLError | |
Defined in Database.SQLite3 toException :: SQLError -> SomeException # fromException :: SomeException -> Maybe SQLError # displayException :: SQLError -> String # | |
Generic SQLError | |
Show SQLError | |
Eq SQLError | |
type Rep SQLError | |
Defined in Database.SQLite3 type Rep SQLError = D1 ('MetaData "SQLError" "Database.SQLite3" "direct-sqlite-2.3.29-2L5GhZ6SNrD5GJ9vI8wbDw" 'False) (C1 ('MetaCons "SQLError" 'PrefixI 'True) (S1 ('MetaSel ('Just "sqlError") 'NoSourceUnpackedness 'SourceStrict 'DecidedStrict) (Rec0 Error) :*: (S1 ('MetaSel ('Just "sqlErrorDetails") 'NoSourceUnpackedness 'NoSourceStrictness 'DecidedLazy) (Rec0 Text) :*: S1 ('MetaSel ('Just "sqlErrorContext") 'NoSourceUnpackedness 'NoSourceStrictness 'DecidedLazy) (Rec0 Text)))) |
Instances
Generic Error | |
Show Error | |
Eq Error | |
FFIType Error CError | |
type Rep Error | |
Defined in Database.SQLite3.Bindings.Types type Rep Error = D1 ('MetaData "Error" "Database.SQLite3.Bindings.Types" "direct-sqlite-2.3.29-2L5GhZ6SNrD5GJ9vI8wbDw" 'False) ((((((C1 ('MetaCons "ErrorOK" 'PrefixI 'False) (U1 :: Type -> Type) :+: (C1 ('MetaCons "ErrorError" 'PrefixI 'False) (U1 :: Type -> Type) :+: C1 ('MetaCons "ErrorInternal" 'PrefixI 'False) (U1 :: Type -> Type))) :+: (C1 ('MetaCons "ErrorPermission" 'PrefixI 'False) (U1 :: Type -> Type) :+: (C1 ('MetaCons "ErrorAbort" 'PrefixI 'False) (U1 :: Type -> Type) :+: C1 ('MetaCons "ErrorBusy" 'PrefixI 'False) (U1 :: Type -> Type)))) :+: ((C1 ('MetaCons "ErrorLocked" 'PrefixI 'False) (U1 :: Type -> Type) :+: (C1 ('MetaCons "ErrorNoMemory" 'PrefixI 'False) (U1 :: Type -> Type) :+: C1 ('MetaCons "ErrorReadOnly" 'PrefixI 'False) (U1 :: Type -> Type))) :+: ((C1 ('MetaCons "ErrorInterrupt" 'PrefixI 'False) (U1 :: Type -> Type) :+: C1 ('MetaCons "ErrorIO" 'PrefixI 'False) (U1 :: Type -> Type)) :+: (C1 ('MetaCons "ErrorCorrupt" 'PrefixI 'False) (U1 :: Type -> Type) :+: C1 ('MetaCons "ErrorNotFound" 'PrefixI 'False) (U1 :: Type -> Type))))) :+: (((C1 ('MetaCons "ErrorFull" 'PrefixI 'False) (U1 :: Type -> Type) :+: (C1 ('MetaCons "ErrorCan'tOpen" 'PrefixI 'False) (U1 :: Type -> Type) :+: C1 ('MetaCons "ErrorProtocol" 'PrefixI 'False) (U1 :: Type -> Type))) :+: (C1 ('MetaCons "ErrorEmpty" 'PrefixI 'False) (U1 :: Type -> Type) :+: (C1 ('MetaCons "ErrorSchema" 'PrefixI 'False) (U1 :: Type -> Type) :+: C1 ('MetaCons "ErrorTooBig" 'PrefixI 'False) (U1 :: Type -> Type)))) :+: ((C1 ('MetaCons "ErrorConstraint" 'PrefixI 'False) (U1 :: Type -> Type) :+: (C1 ('MetaCons "ErrorMismatch" 'PrefixI 'False) (U1 :: Type -> Type) :+: C1 ('MetaCons "ErrorMisuse" 'PrefixI 'False) (U1 :: Type -> Type))) :+: ((C1 ('MetaCons "ErrorNoLargeFileSupport" 'PrefixI 'False) (U1 :: Type -> Type) :+: C1 ('MetaCons "ErrorAuthorization" 'PrefixI 'False) (U1 :: Type -> Type)) :+: (C1 ('MetaCons "ErrorFormat" 'PrefixI 'False) (U1 :: Type -> Type) :+: C1 ('MetaCons "ErrorRange" 'PrefixI 'False) (U1 :: Type -> Type)))))) :+: ((((C1 ('MetaCons "ErrorNotADatabase" 'PrefixI 'False) (U1 :: Type -> Type) :+: (C1 ('MetaCons "ErrorNotice" 'PrefixI 'False) (U1 :: Type -> Type) :+: C1 ('MetaCons "ErrorWarning" 'PrefixI 'False) (U1 :: Type -> Type))) :+: (C1 ('MetaCons "ErrorRow" 'PrefixI 'False) (U1 :: Type -> Type) :+: (C1 ('MetaCons "ErrorDone" 'PrefixI 'False) (U1 :: Type -> Type) :+: C1 ('MetaCons "ErrorErrorMissingCollatingSquence" 'PrefixI 'False) (U1 :: Type -> Type)))) :+: ((C1 ('MetaCons "ErrorErrorRetry" 'PrefixI 'False) (U1 :: Type -> Type) :+: (C1 ('MetaCons "ErrorErrorSnapshot" 'PrefixI 'False) (U1 :: Type -> Type) :+: C1 ('MetaCons "ErrorIORead" 'PrefixI 'False) (U1 :: Type -> Type))) :+: ((C1 ('MetaCons "ErrorIOShortRead" 'PrefixI 'False) (U1 :: Type -> Type) :+: C1 ('MetaCons "ErrorIOWrite" 'PrefixI 'False) (U1 :: Type -> Type)) :+: (C1 ('MetaCons "ErrorIOFsync" 'PrefixI 'False) (U1 :: Type -> Type) :+: C1 ('MetaCons "ErrorIODirectoryFsync" 'PrefixI 'False) (U1 :: Type -> Type))))) :+: (((C1 ('MetaCons "ErrorIOTruncate" 'PrefixI 'False) (U1 :: Type -> Type) :+: (C1 ('MetaCons "ErrorIOFstat" 'PrefixI 'False) (U1 :: Type -> Type) :+: C1 ('MetaCons "ErrorIOUnlock" 'PrefixI 'False) (U1 :: Type -> Type))) :+: (C1 ('MetaCons "ErrorIOReadLock" 'PrefixI 'False) (U1 :: Type -> Type) :+: (C1 ('MetaCons "ErrorIOBlocked" 'PrefixI 'False) (U1 :: Type -> Type) :+: C1 ('MetaCons "ErrorIODelete" 'PrefixI 'False) (U1 :: Type -> Type)))) :+: ((C1 ('MetaCons "ErrorIONoMemory" 'PrefixI 'False) (U1 :: Type -> Type) :+: (C1 ('MetaCons "ErrorIOAccess" 'PrefixI 'False) (U1 :: Type -> Type) :+: C1 ('MetaCons "ErrorIOCheckReservedLock" 'PrefixI 'False) (U1 :: Type -> Type))) :+: ((C1 ('MetaCons "ErrorIOLock" 'PrefixI 'False) (U1 :: Type -> Type) :+: C1 ('MetaCons "ErrorIOClose" 'PrefixI 'False) (U1 :: Type -> Type)) :+: (C1 ('MetaCons "ErrorIODirectoryClose" 'PrefixI 'False) (U1 :: Type -> Type) :+: C1 ('MetaCons "ErrorIOShmOpen" 'PrefixI 'False) (U1 :: Type -> Type))))))) :+: (((((C1 ('MetaCons "ErrorIOShmSize" 'PrefixI 'False) (U1 :: Type -> Type) :+: (C1 ('MetaCons "ErrorIOShmLock" 'PrefixI 'False) (U1 :: Type -> Type) :+: C1 ('MetaCons "ErrorIOShmMap" 'PrefixI 'False) (U1 :: Type -> Type))) :+: (C1 ('MetaCons "ErrorIOSeek" 'PrefixI 'False) (U1 :: Type -> Type) :+: (C1 ('MetaCons "ErrorIODeleteNoEntity" 'PrefixI 'False) (U1 :: Type -> Type) :+: C1 ('MetaCons "ErrorIOMmap" 'PrefixI 'False) (U1 :: Type -> Type)))) :+: ((C1 ('MetaCons "ErrorIOGetTempPath" 'PrefixI 'False) (U1 :: Type -> Type) :+: (C1 ('MetaCons "ErrorIOConvertedPath" 'PrefixI 'False) (U1 :: Type -> Type) :+: C1 ('MetaCons "ErrorIOVNode" 'PrefixI 'False) (U1 :: Type -> Type))) :+: ((C1 ('MetaCons "ErrorIOAuth" 'PrefixI 'False) (U1 :: Type -> Type) :+: C1 ('MetaCons "ErrorIOBeginAtomic" 'PrefixI 'False) (U1 :: Type -> Type)) :+: (C1 ('MetaCons "ErrorIOCommitAtomic" 'PrefixI 'False) (U1 :: Type -> Type) :+: C1 ('MetaCons "ErrorIORollbackAtomic" 'PrefixI 'False) (U1 :: Type -> Type))))) :+: (((C1 ('MetaCons "ErrorIOData" 'PrefixI 'False) (U1 :: Type -> Type) :+: (C1 ('MetaCons "ErrorIOCorruptFilesystem" 'PrefixI 'False) (U1 :: Type -> Type) :+: C1 ('MetaCons "ErrorLockedSharedCache" 'PrefixI 'False) (U1 :: Type -> Type))) :+: (C1 ('MetaCons "ErrorLockedVirtualTable" 'PrefixI 'False) (U1 :: Type -> Type) :+: (C1 ('MetaCons "ErrorBusyRecovery" 'PrefixI 'False) (U1 :: Type -> Type) :+: C1 ('MetaCons "ErrorBusySnapshot" 'PrefixI 'False) (U1 :: Type -> Type)))) :+: ((C1 ('MetaCons "ErrorBusyTimeout" 'PrefixI 'False) (U1 :: Type -> Type) :+: (C1 ('MetaCons "ErrorCan'tOpenNotTempDirectory" 'PrefixI 'False) (U1 :: Type -> Type) :+: C1 ('MetaCons "ErrorCan'tOpenIsDirectory" 'PrefixI 'False) (U1 :: Type -> Type))) :+: ((C1 ('MetaCons "ErrorCan'tOpenFullPath" 'PrefixI 'False) (U1 :: Type -> Type) :+: C1 ('MetaCons "ErrorCan'tOpenConvertedPath" 'PrefixI 'False) (U1 :: Type -> Type)) :+: (C1 ('MetaCons "ErrorCan'tOpenDirtyWriteAheadLog" 'PrefixI 'False) (U1 :: Type -> Type) :+: C1 ('MetaCons "ErrorCan'tOpenSymlink" 'PrefixI 'False) (U1 :: Type -> Type)))))) :+: ((((C1 ('MetaCons "ErrorCorruptVirtualTable" 'PrefixI 'False) (U1 :: Type -> Type) :+: (C1 ('MetaCons "ErrorCorruptSequence" 'PrefixI 'False) (U1 :: Type -> Type) :+: C1 ('MetaCons "ErrorCorruptIndex" 'PrefixI 'False) (U1 :: Type -> Type))) :+: (C1 ('MetaCons "ErrorReadOnlyRecovery" 'PrefixI 'False) (U1 :: Type -> Type) :+: (C1 ('MetaCons "ErrorReadOnlyCan'tLock" 'PrefixI 'False) (U1 :: Type -> Type) :+: C1 ('MetaCons "ErrorReadOnlyRollback" 'PrefixI 'False) (U1 :: Type -> Type)))) :+: ((C1 ('MetaCons "ErrorReadOnlyDatabaseMoved" 'PrefixI 'False) (U1 :: Type -> Type) :+: (C1 ('MetaCons "ErrorReadOnlyCan'tInit" 'PrefixI 'False) (U1 :: Type -> Type) :+: C1 ('MetaCons "ErrorReadOnlyDirectory" 'PrefixI 'False) (U1 :: Type -> Type))) :+: ((C1 ('MetaCons "ErrorAbortRollback" 'PrefixI 'False) (U1 :: Type -> Type) :+: C1 ('MetaCons "ErrorConstraintCheck" 'PrefixI 'False) (U1 :: Type -> Type)) :+: (C1 ('MetaCons "ErrorConstraintCommitHook" 'PrefixI 'False) (U1 :: Type -> Type) :+: C1 ('MetaCons "ErrorConstraintForeignKey" 'PrefixI 'False) (U1 :: Type -> Type))))) :+: (((C1 ('MetaCons "ErrorConstraintFunction" 'PrefixI 'False) (U1 :: Type -> Type) :+: (C1 ('MetaCons "ErrorConstraintNotNull" 'PrefixI 'False) (U1 :: Type -> Type) :+: C1 ('MetaCons "ErrorConstraintPrimaryKey" 'PrefixI 'False) (U1 :: Type -> Type))) :+: ((C1 ('MetaCons "ErrorConstraintTrigger" 'PrefixI 'False) (U1 :: Type -> Type) :+: C1 ('MetaCons "ErrorConstraintUnique" 'PrefixI 'False) (U1 :: Type -> Type)) :+: (C1 ('MetaCons "ErrorConstraintVirtualTable" 'PrefixI 'False) (U1 :: Type -> Type) :+: C1 ('MetaCons "ErrorConstraintRowId" 'PrefixI 'False) (U1 :: Type -> Type)))) :+: ((C1 ('MetaCons "ErrorConstraintPinned" 'PrefixI 'False) (U1 :: Type -> Type) :+: (C1 ('MetaCons "ErrorConstraintDataType" 'PrefixI 'False) (U1 :: Type -> Type) :+: C1 ('MetaCons "ErrorNoticeRecoverWriteAheadLog" 'PrefixI 'False) (U1 :: Type -> Type))) :+: ((C1 ('MetaCons "ErrorNoticeRecoverRollback" 'PrefixI 'False) (U1 :: Type -> Type) :+: C1 ('MetaCons "ErrorWarningAutoIndex" 'PrefixI 'False) (U1 :: Type -> Type)) :+: (C1 ('MetaCons "ErrorAuthUser" 'PrefixI 'False) (U1 :: Type -> Type) :+: C1 ('MetaCons "ErrorOkLoadPermanently" 'PrefixI 'False) (U1 :: Type -> Type)))))))) |