= 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 `columnText`.
-> IO ()

Statement management

prepare :: Database -> Text -> IO Statement Source

http://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 fails.

prepareUtf8 :: Database -> Utf8 -> IO Statement Source

http://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 fails.

step :: Statement -> IO StepResult Source

http://www.sqlite.org/c3ref/step.html

reset :: Statement -> IO () Source

http://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.

finalize :: Statement -> IO () Source

http://www.sqlite.org/c3ref/finalize.html

Like reset, finalize never throws an exception.

clearBindings :: Statement -> IO () Source

http://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

http://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

http://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.

columnCount :: Statement -> IO ColumnCount Source

http://www.sqlite.org/c3ref/column_count.html

columnName :: Statement -> ColumnIndex -> IO (Maybe Text) Source

http://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

http://www.sqlite.org/c3ref/bind_blob.html

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)]

bindInt :: Statement -> ParamIndex -> Int -> IO () Source

bindInt64 :: Statement -> ParamIndex -> Int64 -> IO () Source

bindDouble :: Statement -> ParamIndex -> Double -> IO () Source

bindText :: Statement -> ParamIndex -> Text -> IO () Source

bindBlob :: Statement -> ParamIndex -> ByteString -> IO () Source

bindZeroBlob :: Statement -> ParamIndex -> Int -> IO () Source

bindNull :: Statement -> ParamIndex -> IO () Source

Reading the result row

http://www.sqlite.org/c3ref/column_blob.html

Warning: column and columns will throw a DecodeError if any TEXT datum contains invalid UTF-8.

column :: Statement -> ColumnIndex -> IO SQLData Source

columns :: Statement -> IO [SQLData] Source

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 http://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

lastInsertRowId :: Database -> IO Int64 Source

http://www.sqlite.org/c3ref/last_insert_rowid.html

changes :: Database -> 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.

Create custom SQL functions

createFunction Source

Arguments

:: Database
-> Text	Name of the function.
-> Maybe ArgCount	Number of arguments. `Nothing` means that the function accepts any number of arguments.
-> Bool	Is the function deterministic?
-> (FuncContext -> FuncArgs -> IO ())	Implementation of the function.
-> IO ()

http://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.

createAggregate Source

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

funcArgInt64 :: FuncArgs -> ArgIndex -> IO Int64 Source

funcArgDouble :: FuncArgs -> ArgIndex -> IO Double Source

funcArgText :: FuncArgs -> ArgIndex -> IO Text 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

getFuncContextDatabase :: FuncContext -> IO Database Source

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

Create custom collations

createCollation Source

Arguments

:: Database
-> Text	Name of the collation.
-> (Text -> Text -> Ordering)	Comparison function.
-> IO ()

http://www.sqlite.org/c3ref/create_collation.html

deleteCollation :: Database -> Text -> IO () Source

Delete a collation.

Interrupting a long-running query

interrupt :: Database -> IO () Source

http://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

blobOpen Source

Arguments

:: Database
-> Text	The symbolic name of the database (e.g. "main").
-> Text	The table name.
-> Text	The column name.
-> Int64	The `ROWID` of the row.
-> Bool	Open the blob for read-write.
-> IO Blob

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

Open a blob for incremental I/O.

blobClose :: Blob -> IO () Source

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

blobReopen :: Blob -> Int64 -> IO () Source

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

blobBytes :: Blob -> IO Int Source

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

blobRead Source

Arguments

:: Blob
-> Int	Number of bytes to read.
-> Int	Offset within the blob.
-> IO ByteString

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

blobReadBuf :: Blob -> Ptr a -> Int -> Int -> IO () Source

blobWrite Source

Arguments

:: Blob
-> ByteString
-> Int	Offset within the blob.
-> IO ()

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

Online Backup API

https://www.sqlite.org/backup.html and https://www.sqlite.org/c3ref/backup_finish.html

backupInit Source

Arguments

:: Database	Destination database handle
-> Text	Destination database name
-> Database	Source database handle
-> Text	Source database name
-> IO Backup

backupFinish :: Backup -> IO () Source

backupStep :: Backup -> Int -> IO BackupStepResult Source

backupRemaining :: Backup -> IO Int Source

backupPagecount :: Backup -> IO Int Source

Types

data Database Source

Instances

Eq Database
Show Database

data Statement Source

Instances

Eq Statement
Show Statement

data SQLData Source

Constructors

SQLInteger !Int64
SQLFloat !Double
SQLText !Text
SQLBlob !ByteString
SQLNull

Instances

Eq SQLData
Show SQLData
Typeable * SQLData

data SQLError Source

Exception thrown when SQLite3 reports an error.

direct-sqlite may throw other types of exceptions if you misuse the API.

Constructors

SQLError
Fields sqlError :: !Error Error code returned by API call sqlErrorDetails :: Text Text describing the error sqlErrorContext :: Text Indicates what action produced this error, e.g. `exec "SELECT * FROM foo"`

Instances

Eq SQLError
Show SQLError
Exception SQLError
Typeable * SQLError

data ColumnType Source

Constructors

IntegerColumn
FloatColumn
TextColumn
BlobColumn
NullColumn

Instances

Eq ColumnType
Show ColumnType
FFIType ColumnType CColumnType

data FuncContext Source

The context in which a custom SQL function is executed.

Instances

Eq FuncContext
Show FuncContext

data FuncArgs Source

The arguments of a custom SQL function.

data Blob Source

The type of blob handles used for incremental blob I/O

Instances

Eq Blob
Show Blob

data Backup Source

A handle for an online backup process.

Instances

Eq Backup
Show Backup

Results and errors

data StepResult Source

Constructors

Row
Done

Instances

Eq StepResult
Show StepResult

data BackupStepResult Source

Constructors

BackupOK	There are still more pages to be copied.
BackupDone	All pages were successfully copied.

Instances

Eq BackupStepResult
Show BackupStepResult

data Error Source

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 `malloc()` failed
ErrorReadOnly	Attempt to write a readonly database
ErrorInterrupt	Operation terminated by `sqlite3_interrupt()`
ErrorIO	Some kind of disk I/O error occurred
ErrorCorrupt	The database disk image is malformed
ErrorNotFound	Unknown opcode in `sqlite3_file_control()`
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
ErrorRow	`sqlite3_step()` has another row ready
ErrorDone	`sqlite3_step()` has finished executing

Instances

Eq Error
Show Error
FFIType Error CError

Special integers

newtype ParamIndex Source

Index of a parameter in a parameterized query. Parameter indices start from 1.

When a query is prepared, 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 http://www.sqlite.org/lang_expr.html#varparam for the syntax of parameter placeholders, and how parameter indices are assigned.

Constructors

ParamIndex Int

Instances

Bounded ParamIndex	Limit min/max bounds to fit into SQLite's native parameter ranges.
Enum ParamIndex
Eq ParamIndex
Integral ParamIndex
Num ParamIndex
Ord ParamIndex
Real ParamIndex
Show ParamIndex	This just shows the underlying integer, without the data constructor.
FFIType ParamIndex CParamIndex

newtype ColumnIndex Source

Index of a column in a result set. Column indices start from 0.

Constructors

ColumnIndex Int

Instances

Bounded ColumnIndex	Limit min/max bounds to fit into SQLite's native parameter ranges.
Enum ColumnIndex
Eq ColumnIndex
Integral ColumnIndex
Num ColumnIndex
Ord ColumnIndex
Real ColumnIndex
Show ColumnIndex	This just shows the underlying integer, without the data constructor.
FFIType ColumnIndex CColumnIndex

type ColumnCount = ColumnIndex Source

Number of columns in a result set.

newtype ArgCount Source

Number of arguments of a user defined SQL function.

Constructors

ArgCount Int

Instances

Bounded ArgCount
Enum ArgCount
Eq ArgCount
Integral ArgCount
Num ArgCount
Ord ArgCount
Real ArgCount
Show ArgCount	This just shows the underlying integer, without the data constructor.
FFIType ArgCount CArgCount

type ArgIndex = ArgCount Source

Index of an argument to a custom function. Indices start from 0.