| Safe Haskell | None |
|---|---|
| Language | Haskell2010 |
Database.SQLite3
Contents
- Connection management
- Simple query execution
- Statement management
- Parameter and column information
- Binding values to a prepared statement
- Reading the result row
- Result statistics
- Create custom SQL functions
- Create custom collations
- Interrupting a long-running query
- Incremental blob I/O
- Online Backup API
- Types
Synopsis
- open :: Text -> IO Database
- close :: Database -> IO ()
- exec :: Database -> Text -> IO ()
- execPrint :: Database -> Text -> IO ()
- execWithCallback :: Database -> Text -> ExecCallback -> IO ()
- type ExecCallback = ColumnCount -> [ Text ] -> [ Maybe Text ] -> IO ()
- prepare :: Database -> Text -> IO Statement
- prepareUtf8 :: Database -> Utf8 -> IO Statement
- step :: Statement -> IO StepResult
- stepNoCB :: Statement -> IO StepResult
- reset :: Statement -> IO ()
- finalize :: Statement -> IO ()
- clearBindings :: Statement -> IO ()
- bindParameterCount :: Statement -> IO ParamIndex
- bindParameterName :: Statement -> ParamIndex -> IO ( Maybe Text )
- columnCount :: Statement -> IO ColumnCount
- columnName :: Statement -> ColumnIndex -> IO ( Maybe Text )
- bindSQLData :: Statement -> ParamIndex -> SQLData -> IO ()
- bind :: Statement -> [ SQLData ] -> IO ()
- bindNamed :: Statement -> [( Text , SQLData )] -> IO ()
- bindInt :: Statement -> ParamIndex -> Int -> IO ()
- bindInt64 :: Statement -> ParamIndex -> Int64 -> IO ()
- bindDouble :: Statement -> ParamIndex -> Double -> IO ()
- bindText :: Statement -> ParamIndex -> Text -> IO ()
- bindBlob :: Statement -> ParamIndex -> ByteString -> IO ()
- bindZeroBlob :: Statement -> ParamIndex -> Int -> IO ()
- bindNull :: Statement -> ParamIndex -> IO ()
- column :: Statement -> ColumnIndex -> IO SQLData
- columns :: Statement -> IO [ SQLData ]
- typedColumns :: Statement -> [ Maybe ColumnType ] -> IO [ SQLData ]
- columnType :: Statement -> ColumnIndex -> IO ColumnType
- columnInt64 :: Statement -> ColumnIndex -> IO Int64
- columnDouble :: Statement -> ColumnIndex -> IO Double
- columnText :: Statement -> ColumnIndex -> IO Text
- columnBlob :: Statement -> ColumnIndex -> IO ByteString
- lastInsertRowId :: Database -> IO Int64
- changes :: Database -> IO Int
- createFunction :: Database -> Text -> Maybe ArgCount -> Bool -> ( FuncContext -> FuncArgs -> IO ()) -> IO ()
- createAggregate :: Database -> Text -> Maybe ArgCount -> a -> ( FuncContext -> FuncArgs -> a -> IO a) -> ( FuncContext -> a -> IO ()) -> IO ()
- deleteFunction :: Database -> Text -> Maybe ArgCount -> IO ()
- funcArgCount :: FuncArgs -> ArgCount
- funcArgType :: FuncArgs -> ArgIndex -> IO ColumnType
- funcArgInt64 :: FuncArgs -> ArgIndex -> IO Int64
- funcArgDouble :: FuncArgs -> ArgIndex -> IO Double
- funcArgText :: FuncArgs -> ArgIndex -> IO Text
- funcArgBlob :: FuncArgs -> ArgIndex -> IO ByteString
- funcResultSQLData :: FuncContext -> SQLData -> IO ()
- funcResultInt64 :: FuncContext -> Int64 -> IO ()
- funcResultDouble :: FuncContext -> Double -> IO ()
- funcResultText :: FuncContext -> Text -> IO ()
- funcResultBlob :: FuncContext -> ByteString -> IO ()
- funcResultZeroBlob :: FuncContext -> Int -> IO ()
- funcResultNull :: FuncContext -> IO ()
- getFuncContextDatabase :: FuncContext -> IO Database
- createCollation :: Database -> Text -> ( Text -> Text -> Ordering ) -> IO ()
- deleteCollation :: Database -> Text -> IO ()
- interrupt :: Database -> IO ()
- interruptibly :: Database -> IO a -> IO a
- blobOpen :: Database -> Text -> Text -> Text -> Int64 -> Bool -> IO Blob
- blobClose :: Blob -> IO ()
- blobReopen :: Blob -> Int64 -> IO ()
- blobBytes :: Blob -> IO Int
- blobRead :: Blob -> Int -> Int -> IO ByteString
- blobReadBuf :: Blob -> Ptr a -> Int -> Int -> IO ()
- blobWrite :: Blob -> ByteString -> Int -> IO ()
- backupInit :: Database -> Text -> Database -> Text -> IO Backup
- backupFinish :: Backup -> IO ()
- backupStep :: Backup -> Int -> IO BackupStepResult
- backupRemaining :: Backup -> IO Int
- backupPagecount :: Backup -> IO Int
- data Database
- data Statement
-
data
SQLData
- = SQLInteger ! Int64
- | SQLFloat ! Double
- | SQLText ! Text
- | SQLBlob ! ByteString
- | SQLNull
-
data
SQLError
=
SQLError
{
- sqlError :: ! Error
- sqlErrorDetails :: Text
- sqlErrorContext :: Text
-
data
ColumnType
- = IntegerColumn
- | FloatColumn
- | TextColumn
- | BlobColumn
- | NullColumn
- data FuncContext
- data FuncArgs
- data Blob
- data Backup
- data StepResult
-
data
BackupStepResult
- = BackupOK
- | BackupDone
-
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
- newtype ParamIndex = ParamIndex Int
- newtype ColumnIndex = ColumnIndex Int
- type ColumnCount = ColumnIndex
- newtype ArgCount = ArgCount Int
- type ArgIndex = ArgCount
Connection management
Simple query execution
exec :: Database -> Text -> IO () Source #
Execute zero or more SQL statements delimited by semicolons.
execWithCallback :: Database -> Text -> ExecCallback -> IO () Source #
Like
exec
, but invoke the callback for each result row.
type ExecCallback Source #
Arguments
| = ColumnCount |
Number of columns, which is the number of items in the following lists. This will be the same for every row. |
| -> [ Text ] |
List of column names. This will be the same for every row. |
| -> [ Maybe Text ] |
List of column values, as returned by
|
| -> IO () |
Statement management
prepare :: Database -> Text -> IO Statement Source #
https://www.sqlite.org/c3ref/prepare.html
Unlike
exec
,
prepare
only executes the first statement, and ignores
subsequent statements.
If the query string contains no SQL statements, this
fail
s.
prepareUtf8 :: Database -> Utf8 -> IO Statement Source #
https://www.sqlite.org/c3ref/prepare.html
It can help to avoid redundant Utf8 to Text conversion if you already have Utf8
If the query string contains no SQL statements, this
fail
s.
stepNoCB :: Statement -> IO StepResult Source #
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).
reset :: Statement -> IO () Source #
https://www.sqlite.org/c3ref/reset.html
Note that in the C API,
sqlite3_reset
returns an error code if the most
recent
sqlite3_step
indicated an error. We do not replicate that behavior
here.
reset
never throws an exception.
clearBindings :: Statement -> IO () Source #
https://www.sqlite.org/c3ref/clear_bindings.html
Set all parameters in the prepared statement to null.
Parameter and column information
bindParameterCount :: Statement -> IO ParamIndex Source #
https://www.sqlite.org/c3ref/bind_parameter_count.html
This returns the index of the largest (rightmost) parameter. Note that this
is not necessarily the number of parameters. If numbered parameters like
?5
are used, there may be gaps in the list.
See
ParamIndex
for more information.
bindParameterName :: Statement -> ParamIndex -> IO ( Maybe Text ) Source #
https://www.sqlite.org/c3ref/bind_parameter_name.html
Return the N-th SQL parameter name.
Named parameters are returned as-is. E.g. ":v" is returned as
Just ":v"
. Unnamed parameters, however, are converted to
Nothing
.
Note that the parameter index starts at 1, not 0.
columnName :: Statement -> ColumnIndex -> IO ( Maybe Text ) Source #
https://www.sqlite.org/c3ref/column_name.html
Return the name of a result column. If the column index is out of range,
return
Nothing
.
Binding values to a prepared statement
bindSQLData :: Statement -> ParamIndex -> SQLData -> IO () Source #
If the index is not between 1 and
bindParameterCount
inclusive, this
fails with
ErrorRange
. Otherwise, it succeeds, even if the query skips
this index by using numbered parameters.
Example:
> stmt <- prepare conn "SELECT ?1, ?3, ?5" > bindSQLData stmt 1 (SQLInteger 1) > bindSQLData stmt 2 (SQLInteger 2) > bindSQLData stmt 6 (SQLInteger 6) *** Exception: SQLite3 returned ErrorRange while attempting to perform bind int64. > step stmt >> columns stmt [SQLInteger 1,SQLNull,SQLNull]
bind :: Statement -> [ SQLData ] -> IO () Source #
Convenience function for binding values to all parameters. This will
fail
if the list has the wrong number of parameters.
bindNamed :: Statement -> [( Text , SQLData )] -> IO () Source #
Convenience function for binding named values to all parameters.
This will
fail
if the list has the wrong number of parameters or
if an unknown name is used.
Example:
stmt <- prepare conn "SELECT :foo + :bar"
bindNamed stmt [(":foo", SQLInteger 1), (":bar", SQLInteger 2)]
bindDouble :: Statement -> ParamIndex -> Double -> IO () Source #
bindBlob :: Statement -> ParamIndex -> ByteString -> IO () Source #
bindZeroBlob :: Statement -> ParamIndex -> Int -> IO () Source #
Reading the result row
https://www.sqlite.org/c3ref/column_blob.html
Warning:
column
and
columns
will throw a
DecodeError
if any
TEXT
datum contains invalid UTF-8.
typedColumns :: Statement -> [ Maybe ColumnType ] -> IO [ SQLData ] Source #
This avoids extra API calls using the list of column types. If passed types do not correspond to the actual types, the values will be converted according to the rules at https://www.sqlite.org/c3ref/column_blob.html . If the list contains more items that number of columns, the result is undefined.
columnType :: Statement -> ColumnIndex -> IO ColumnType Source #
columnInt64 :: Statement -> ColumnIndex -> IO Int64 Source #
columnDouble :: Statement -> ColumnIndex -> IO Double Source #
columnText :: Statement -> ColumnIndex -> IO Text Source #
This will throw a
DecodeError
if the datum contains invalid UTF-8.
If this behavior is undesirable, you can use
columnText
from
Database.SQLite3.Direct
, which does not perform conversion to
Text
.
columnBlob :: Statement -> ColumnIndex -> IO ByteString Source #
Result statistics
changes :: Database -> IO Int Source #
https://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.
Create custom SQL functions
Arguments
| :: Database | |
| -> Text |
Name of the function. |
| -> Maybe ArgCount |
Number of arguments.
|
| -> Bool |
Is the function deterministic? |
| -> ( FuncContext -> FuncArgs -> IO ()) |
Implementation of the function. |
| -> IO () |
https://sqlite.org/c3ref/create_function.html
Create a custom SQL function or redefine the behavior of an existing
function. If the function is deterministic, i.e. if it always returns the
same result given the same input, you can set the boolean flag to let
sqlite
perform additional optimizations.
Arguments
| :: Database | |
| -> Text |
Name of the function. |
| -> Maybe ArgCount |
Number of arguments. |
| -> a |
Initial aggregate state. |
| -> ( FuncContext -> FuncArgs -> a -> IO a) |
Process one row and update the aggregate state. |
| -> ( FuncContext -> a -> IO ()) |
Called after all rows have been processed. Can be used to construct the returned value from the aggregate state. |
| -> IO () |
Like
createFunction
except that it creates an aggregate function.
deleteFunction :: Database -> Text -> Maybe ArgCount -> IO () Source #
Delete an SQL function (scalar or aggregate).
Extract function arguments
funcArgCount :: FuncArgs -> ArgCount Source #
funcArgType :: FuncArgs -> ArgIndex -> IO ColumnType Source #
funcArgBlob :: FuncArgs -> ArgIndex -> IO ByteString Source #
Set the result of a function
funcResultSQLData :: FuncContext -> SQLData -> IO () Source #
funcResultInt64 :: FuncContext -> Int64 -> IO () Source #
funcResultDouble :: FuncContext -> Double -> IO () Source #
funcResultText :: FuncContext -> Text -> IO () Source #
funcResultBlob :: FuncContext -> ByteString -> IO () Source #
funcResultZeroBlob :: FuncContext -> Int -> IO () Source #
funcResultNull :: FuncContext -> IO () Source #
Create custom collations
Interrupting a long-running query
interrupt :: Database -> IO () Source #
https://www.sqlite.org/c3ref/interrupt.html
Cause any pending operation on the
Database
handle to stop at its earliest
opportunity. This simply sets a flag and returns immediately. It does not
wait for the pending operation to finish.
You'll need to compile with
-threaded
for this to do any good.
Without
-threaded
, FFI calls block the whole RTS, meaning
interrupt
would never run at the same time as
step
.
interruptibly :: Database -> IO a -> IO a Source #
Make it possible to interrupt the given database operation with an
asynchronous exception. This only works if the program is compiled with
base >= 4.3 and
-threaded
.
It works by running the callback in a forked thread. If interrupted,
it uses
interrupt
to try to stop the operation.
Incremental blob I/O
Arguments
| :: Database | |
| -> Text |
The symbolic name of the database (e.g. "main"). |
| -> Text |
The table name. |
| -> Text |
The column name. |
| -> Int64 |
The
|
| -> Bool |
Open the blob for read-write. |
| -> IO Blob |
https://www.sqlite.org/c3ref/blob_open.html
Open a blob for incremental I/O.
Arguments
| :: Blob | |
| -> Int |
Number of bytes to read. |
| -> Int |
Offset within the blob. |
| -> IO ByteString |
Arguments
| :: Blob | |
| -> ByteString | |
| -> Int |
Offset within the blob. |
| -> IO () |
Online Backup API
backupFinish :: Backup -> IO () Source #
backupStep :: Backup -> Int -> IO BackupStepResult Source #
Types
Constructors
| SQLInteger ! Int64 | |
| SQLFloat ! Double | |
| SQLText ! Text | |
| SQLBlob ! ByteString | |
| SQLNull |
Exception thrown when SQLite3 reports an error.
direct-sqlite may throw other types of exceptions if you misuse the API.
Constructors
| SQLError | |
|
Fields
|
|
Instances
| Eq SQLError Source # | |
| Show SQLError Source # | |
| Exception SQLError Source # | |
|
Defined in Database.SQLite3 Methods toException :: SQLError -> SomeException Source # fromException :: SomeException -> Maybe SQLError Source # displayException :: SQLError -> String Source # |
|
data ColumnType Source #
Constructors
| IntegerColumn | |
| FloatColumn | |
| TextColumn | |
| BlobColumn | |
| NullColumn |
Instances
| Eq ColumnType Source # | |
|
Defined in Database.SQLite3.Bindings.Types Methods (==) :: ColumnType -> ColumnType -> Bool Source # (/=) :: ColumnType -> ColumnType -> Bool Source # |
|
| Show ColumnType Source # | |
|
Defined in Database.SQLite3.Bindings.Types |
|
| FFIType ColumnType CColumnType Source # | |
|
Defined in Database.SQLite3.Bindings.Types |
|
data FuncContext Source #
The context in which a custom SQL function is executed.
Instances
| Eq FuncContext Source # | |
|
Defined in Database.SQLite3.Direct Methods (==) :: FuncContext -> FuncContext -> Bool Source # (/=) :: FuncContext -> FuncContext -> Bool Source # |
|
| Show FuncContext Source # | |
|
Defined in Database.SQLite3.Direct |
|
The type of blob handles used for incremental blob I/O
Results and errors
data StepResult Source #
Instances
| Eq StepResult Source # | |
|
Defined in Database.SQLite3.Direct Methods (==) :: StepResult -> StepResult -> Bool Source # (/=) :: StepResult -> StepResult -> Bool Source # |
|
| Show StepResult Source # | |
|
Defined in Database.SQLite3.Direct |
|
data BackupStepResult Source #
Constructors
| BackupOK |
There are still more pages to be copied. |
| BackupDone |
All pages were successfully copied. |
Instances
| Eq BackupStepResult Source # | |
|
Defined in Database.SQLite3.Direct Methods (==) :: BackupStepResult -> BackupStepResult -> Bool Source # (/=) :: BackupStepResult -> BackupStepResult -> Bool Source # |
|
| Show BackupStepResult Source # | |
|
Defined in Database.SQLite3.Direct |
|
Constructors
| ErrorOK |
Successful result |
| ErrorError |
SQL error or missing database |
| ErrorInternal |
Internal logic error in SQLite |
| ErrorPermission |
Access permission denied |
| ErrorAbort |
Callback routine requested an abort |
| ErrorBusy |
The database file is locked |
| ErrorLocked |
A table in the database is locked |
| ErrorNoMemory |
A
|
| ErrorReadOnly |
Attempt to write a readonly database |
| ErrorInterrupt |
Operation terminated by
|
| ErrorIO |
Some kind of disk I/O error occurred |
| ErrorCorrupt |
The database disk image is malformed |
| ErrorNotFound |
Unknown opcode in
|
| ErrorFull |
Insertion failed because database is full |
| ErrorCan'tOpen |
Unable to open the database file |
| ErrorProtocol |
Database lock protocol error |
| ErrorEmpty |
Database is empty |
| ErrorSchema |
The database schema changed |
| ErrorTooBig |
String or BLOB exceeds size limit |
| ErrorConstraint |
Abort due to constraint violation |
| ErrorMismatch |
Data type mismatch |
| ErrorMisuse |
Library used incorrectly |
| ErrorNoLargeFileSupport |
Uses OS features not supported on host |
| ErrorAuthorization |
Authorization denied |
| ErrorFormat |
Auxiliary database format error |
| ErrorRange |
2nd parameter to sqlite3_bind out of range |
| ErrorNotADatabase |
File opened that is not a database file |
| ErrorNotice |
Notifications from sqlite3_log() |
| ErrorWarning |
Warnings from sqlite3_log() |
| ErrorRow |
|
| ErrorDone |
|
Special integers
newtype ParamIndex Source #
Index of a parameter in a parameterized query. Parameter indices start from 1.
When a query is
prepare
d, SQLite allocates an
array indexed from 1 to the highest parameter index. For example:
>Right stmt <- prepare conn "SELECT ?1, ?5, ?3, ?" >bindParameterCount stmt ParamIndex 6
This will allocate an array indexed from 1 to 6 (
?
takes the highest
preceding index plus one). The array is initialized with null values.
When you bind a parameter with
bindSQLData
, it assigns a
new value to one of these indices.
See https://www.sqlite.org/lang_expr.html#varparam for the syntax of parameter placeholders, and how parameter indices are assigned.
Constructors
| ParamIndex Int |
Instances
newtype ColumnIndex Source #
Index of a column in a result set. Column indices start from 0.
Constructors
| ColumnIndex Int |
Instances
type ColumnCount = ColumnIndex Source #
Number of columns in a result set.
Number of arguments of a user defined SQL function.