Database.SQLite3.Direct

This API is a slightly lower-level version of Database.SQLite3. Namely:

• It returns errors instead of throwing them.
• It only uses cheap conversions. None of these bindings convert from String or Text.

Connection management

open :: Utf8 -> IO (Either (Error, Utf8) Database)

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

close :: Database -> IO (Either Error ())

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

errcode :: Database -> IO Error

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

errmsg :: Database -> IO Utf8

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

setTrace :: Database -> Maybe (Utf8 -> IO ()) -> IO ()

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 will crash. Enable only for debugging!

getAutoCommit :: Database -> IO Bool

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

Return True if the connection is in autocommit mode, or False if a transaction started with BEGIN is still active.

Be warned that some errors roll back the transaction automatically, and that ROLLBACK will throw an error if no transaction is active. Use getAutoCommit to avoid such an error:

 autocommit <- getAutoCommit conn
 when (not autocommit) $
     exec conn "ROLLBACK"

setSharedCacheEnabled :: Bool -> IO (Either Error ())

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

Enable or disable shared cache for all future connections.

Simple query execution

http://sqlite.org/c3ref/exec.html

exec :: Database -> Utf8 -> IO (Either (Error, Utf8) ())

execWithCallback :: Database -> Utf8 -> ExecCallback -> IO (Either (Error, Utf8) ())

Like exec, but invoke the callback for each result row.

If the callback throws an exception, it will be rethrown by execWithCallback.

type ExecCallback

Arguments

 = ColumnCount

Number of columns, which is the number of items in the following lists. This will be the same for every row.

-> [Utf8]

List of column names. This will be the same for every row.

-> [Maybe Utf8]

List of column values, as returned by columnText.

-> IO ()

Statement management

prepare :: Database -> Utf8 -> IO (Either Error (Maybe Statement))

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

If the query contains no SQL statements, this returns Right Nothing.

getStatementDatabase :: Statement -> IO Database

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

step :: Statement -> IO (Either Error StepResult)

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

reset :: Statement -> IO (Either Error ())

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

Warning:

• If the most recent step call failed, this will return the corresponding error.
• This does not reset the bindings on a prepared statement. Use clearBindings to do that.

finalize :: Statement -> IO (Either Error ())

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

Warning: If the most recent step call failed, this will return the corresponding error.

clearBindings :: Statement -> IO ()

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

Set all parameters in the prepared statement to null.

statementSql :: Statement -> IO (Maybe Utf8)

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

Return a copy of the original SQL text used to compile the statement.

Parameter and column information

bindParameterCount :: Statement -> IO ParamIndex

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

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

bindParameterIndex :: Statement -> Utf8 -> IO (Maybe ParamIndex)

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

columnCount :: Statement -> IO ColumnCount

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

columnName :: Statement -> ColumnIndex -> IO (Maybe Utf8)

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

Binding values to a prepared statement

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

bindInt64 :: Statement -> ParamIndex -> Int64 -> IO (Either Error ())

bindDouble :: Statement -> ParamIndex -> Double -> IO (Either Error ())

bindText :: Statement -> ParamIndex -> Utf8 -> IO (Either Error ())

bindBlob :: Statement -> ParamIndex -> ByteString -> IO (Either Error ())

bindZeroBlob :: Statement -> ParamIndex -> Int -> IO (Either Error ())

bindNull :: Statement -> ParamIndex -> IO (Either Error ())

Reading the result row

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

columnType :: Statement -> ColumnIndex -> IO ColumnType

columnInt64 :: Statement -> ColumnIndex -> IO Int64

columnDouble :: Statement -> ColumnIndex -> IO Double

columnText :: Statement -> ColumnIndex -> IO Utf8

columnBlob :: Statement -> ColumnIndex -> IO ByteString

control loading of extensions

setLoadExtensionEnabled :: Database -> Bool -> IO (Either Error ())

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

Enable or disable extension loading.

Result statistics

lastInsertRowId :: Database -> IO Int64

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

changes :: Database -> IO Int

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 :: Database -> IO Int

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.

Create custom SQL functions

createFunction

Arguments

:: Database

-> Utf8

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 (Either Error ())

http://sqlite.org/c3ref/create_function.html

Create a custom SQL function or redefine the behavior of an existing function.

createAggregate

Arguments

:: Database

-> Utf8

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 (Either Error ())

Like createFunction except that it creates an aggregate function.

deleteFunction :: Database -> Utf8 -> Maybe ArgCount -> IO (Either Error ())

Delete an SQL function (scalar or aggregate).

Extract function arguments

funcArgCount :: FuncArgs -> ArgCount

funcArgType :: FuncArgs -> ArgIndex -> IO ColumnType

funcArgInt64 :: FuncArgs -> ArgIndex -> IO Int64

funcArgDouble :: FuncArgs -> ArgIndex -> IO Double

funcArgText :: FuncArgs -> ArgIndex -> IO Utf8

funcArgBlob :: FuncArgs -> ArgIndex -> IO ByteString

Set the result of a function

funcResultInt64 :: FuncContext -> Int64 -> IO ()

funcResultDouble :: FuncContext -> Double -> IO ()

funcResultText :: FuncContext -> Utf8 -> IO ()

funcResultBlob :: FuncContext -> ByteString -> IO ()

funcResultZeroBlob :: FuncContext -> Int -> IO ()

funcResultNull :: FuncContext -> IO ()

getFuncContextDatabase :: FuncContext -> IO Database

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

Create custom collations

createCollation

Arguments

:: Database

-> Utf8

Name of the collation.

-> (Utf8 -> Utf8 -> Ordering)

Comparison function.

-> IO (Either Error ())

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

deleteCollation :: Database -> Utf8 -> IO (Either Error ())

Delete a collation.

Interrupting a long-running query

interrupt :: Database -> IO ()

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.

Incremental blob I/O

blobOpen

Arguments

:: Database

-> Utf8

The symbolic name of the database (e.g. "main").

-> Utf8

The table name.

-> Utf8

The column name.

-> Int64

The ROWID of the row.

-> Bool

Open the blob for read-write.

-> IO (Either Error Blob)

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

Open a blob for incremental I/O.

blobClose :: Blob -> IO (Either Error ())

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

blobReopen :: Blob -> Int64 -> IO (Either Error ())

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

blobBytes :: Blob -> IO Int

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

blobRead

Arguments

:: Blob

-> Int

Number of bytes to read.

-> Int

Offset within the blob.

-> IO (Either Error ByteString)

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

blobReadBuf :: Blob -> Ptr a -> Int -> Int -> IO (Either Error ())

blobWrite

Arguments

:: Blob

-> ByteString

-> Int

Offset within the blob.

-> IO (Either Error ())

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

Arguments

:: Database

Destination database handle

-> Utf8

Destination database name

-> Database

Source database handle

-> Utf8

Source database name

-> IO (Either Error Backup)

backupFinish :: Backup -> IO (Either Error ())

backupStep :: Backup -> Int -> IO (Either Error BackupStepResult)

backupRemaining :: Backup -> IO Int

backupPagecount :: Backup -> IO Int

Types

newtype Database

Constructors

Database (Ptr CDatabase)

Instances

Eq Database

Show Database

newtype Statement

Constructors

Statement (Ptr CStatement)

Instances

Eq Statement

Show Statement

data ColumnType

Constructors

IntegerColumn

FloatColumn

TextColumn

BlobColumn

NullColumn

Instances

Eq ColumnType

Show ColumnType

FFIType ColumnType CColumnType

newtype FuncContext

The context in which a custom SQL function is executed.

Constructors

FuncContext (Ptr CContext)

Instances

Eq FuncContext

Show FuncContext

data FuncArgs

The arguments of a custom SQL function.

Constructors

FuncArgs CArgCount (Ptr (Ptr CValue))

data Blob

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

Constructors

Blob Database (Ptr CBlob)

Instances

Eq Blob

Show Blob

data Backup

A handle for an online backup process.

Constructors

Backup Database (Ptr CBackup)

Instances

Eq Backup

Show Backup

Results and errors

data StepResult

Constructors

Row

Done

Instances

Eq StepResult

Show StepResult

data BackupStepResult

Constructors

BackupOK

There are still more pages to be copied.

BackupDone

All pages were successfully copied.

Instances

Eq BackupStepResult

Show BackupStepResult

data Error

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 types

newtype Utf8

A ByteString containing UTF8-encoded text with no NUL characters.

Constructors

Utf8 ByteString

Instances

Eq Utf8

Ord Utf8

Show Utf8

IsString Utf8

fromString = Utf8 . encodeUtf8 . pack

Monoid Utf8

newtype ParamIndex

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

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

Number of columns in a result set.

newtype ArgCount

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

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