Squeather

Bindings to the SQLite3 C library. This is a very simple library that puts a minimal number of abstractions between the user and the underlying C library. Some notable abstractions that do appear:

• use of Text and ByteString in the user-facing API rather than the underlying C types
• use of the SQLite3 extended error codes
• all errors indicated with exceptions (embraces the philosophy that exceptions are inevitable in IO)
• extensive contextual information given in exceptions, to help you figure out where the problem arose
• Database and Statement handles are cleaned up for you, so there is no need to manually de-allocate them

The SQLite library is built along with Squeather, so there is no dependency on a system-wide library and there is nothing else to build. The following run-time loadable extensions are enabled in the build:

• FTS3
• FTS4
• FTS5
• JSON1

However, at this point none of the run-time loadable extensions have been tested.

In addition, the SQLite C library is compiled with the -DSQLITE_DQS=0 compile-time option, so that double-quoted string literals in SQL are NOT accepted. Be sure to single-quote your string literals and double-quote your identifiers in SQL. For details on what this is about, see

https://sqlite.org/quirks.html#double_quoted_string_literals_are_accepted

The name comes from SQL and the feather which is used in the SQLite logo.

Database handles, opening databases

data Database

Database handle. To create a database handle, use open. The resources behind the handle are automatically destroyed when there are no remaining references to the Database, so Squeather provides no close function.

Instances

Eq Database

Defined in Squeather.Internal

Methods

(==) :: Database -> Database -> Bool

(/=) :: Database -> Database -> Bool

Ord Database

Defined in Squeather.Internal

Methods

compare :: Database -> Database -> Ordering

(<) :: Database -> Database -> Bool

(<=) :: Database -> Database -> Bool

(>) :: Database -> Database -> Bool

(>=) :: Database -> Database -> Bool

max :: Database -> Database -> Database

min :: Database -> Database -> Database

Show Database

Defined in Squeather.Internal

Methods

showsPrec :: Int -> Database -> ShowS

show :: Database -> String

showList :: [Database] -> ShowS

open

Arguments

:: Text

Database filename

-> IO Database

Opens a new Database. The openFlags are used.

Opening with flags

data Create

Whether to create a new database if it does not already exist.

Constructors

Create

NoCreate

Instances

Eq Create

Defined in Squeather.Internal.Types

Methods

(==) :: Create -> Create -> Bool

(/=) :: Create -> Create -> Bool

Ord Create

Defined in Squeather.Internal.Types

Methods

compare :: Create -> Create -> Ordering

(<) :: Create -> Create -> Bool

(<=) :: Create -> Create -> Bool

(>) :: Create -> Create -> Bool

(>=) :: Create -> Create -> Bool

max :: Create -> Create -> Create

min :: Create -> Create -> Create

Show Create

Defined in Squeather.Internal.Types

Methods

showsPrec :: Int -> Create -> ShowS

show :: Create -> String

showList :: [Create] -> ShowS

data WriteMode

Constructors

ReadOnly

ReadWrite Create

Instances

Eq WriteMode

Defined in Squeather.Internal.Types

Methods

(==) :: WriteMode -> WriteMode -> Bool

(/=) :: WriteMode -> WriteMode -> Bool

Ord WriteMode

Defined in Squeather.Internal.Types

Methods

compare :: WriteMode -> WriteMode -> Ordering

(<) :: WriteMode -> WriteMode -> Bool

(<=) :: WriteMode -> WriteMode -> Bool

(>) :: WriteMode -> WriteMode -> Bool

(>=) :: WriteMode -> WriteMode -> Bool

max :: WriteMode -> WriteMode -> WriteMode

min :: WriteMode -> WriteMode -> WriteMode

Show WriteMode

Defined in Squeather.Internal.Types

Methods

showsPrec :: Int -> WriteMode -> ShowS

show :: WriteMode -> String

showList :: [WriteMode] -> ShowS

data ThreadMode

Whether to use multi-thread mode or serialized mode, see

https://www.sqlite.org/threadsafe.html

It is not possible to use the SQLite single-thread mode.

Constructors

MultiThread

Serialized

Instances

Eq ThreadMode

Defined in Squeather.Internal.Types

Methods

(==) :: ThreadMode -> ThreadMode -> Bool

(/=) :: ThreadMode -> ThreadMode -> Bool

Ord ThreadMode

Defined in Squeather.Internal.Types

Methods

compare :: ThreadMode -> ThreadMode -> Ordering

(<) :: ThreadMode -> ThreadMode -> Bool

(<=) :: ThreadMode -> ThreadMode -> Bool

(>) :: ThreadMode -> ThreadMode -> Bool

(>=) :: ThreadMode -> ThreadMode -> Bool

max :: ThreadMode -> ThreadMode -> ThreadMode

min :: ThreadMode -> ThreadMode -> ThreadMode

Show ThreadMode

Defined in Squeather.Internal.Types

Methods

showsPrec :: Int -> ThreadMode -> ShowS

show :: ThreadMode -> String

showList :: [ThreadMode] -> ShowS

data CacheMode

Whether to use shared cache or private cache mode, see

https://www.sqlite.org/sharedcache.html

Constructors

Shared

Private

Instances

Eq CacheMode

Defined in Squeather.Internal.Types

Methods

(==) :: CacheMode -> CacheMode -> Bool

(/=) :: CacheMode -> CacheMode -> Bool

Ord CacheMode

Defined in Squeather.Internal.Types

Methods

compare :: CacheMode -> CacheMode -> Ordering

(<) :: CacheMode -> CacheMode -> Bool

(<=) :: CacheMode -> CacheMode -> Bool

(>) :: CacheMode -> CacheMode -> Bool

(>=) :: CacheMode -> CacheMode -> Bool

max :: CacheMode -> CacheMode -> CacheMode

min :: CacheMode -> CacheMode -> CacheMode

Show CacheMode

Defined in Squeather.Internal.Types

Methods

showsPrec :: Int -> CacheMode -> ShowS

show :: CacheMode -> String

showList :: [CacheMode] -> ShowS

data OpenFlags

Various options when opening a database.

Constructors

OpenFlags

Fields

• writeMode :: WriteMode
• uri :: Bool

  Filename can be interpreted as a URI if True.

• memory :: Bool

  Database will be opened in-memory. If you use this flag, the filename is ignored.

• threadMode :: ThreadMode
• cacheMode :: CacheMode
• noFollow :: Bool

  If True, the database filename is not allowed to be a symbolic link.

Instances

Eq OpenFlags

Defined in Squeather.Internal.Types

Methods

(==) :: OpenFlags -> OpenFlags -> Bool

(/=) :: OpenFlags -> OpenFlags -> Bool

Ord OpenFlags

Defined in Squeather.Internal.Types

Methods

compare :: OpenFlags -> OpenFlags -> Ordering

(<) :: OpenFlags -> OpenFlags -> Bool

(<=) :: OpenFlags -> OpenFlags -> Bool

(>) :: OpenFlags -> OpenFlags -> Bool

(>=) :: OpenFlags -> OpenFlags -> Bool

max :: OpenFlags -> OpenFlags -> OpenFlags

min :: OpenFlags -> OpenFlags -> OpenFlags

Show OpenFlags

Defined in Squeather.Internal.Types

Methods

showsPrec :: Int -> OpenFlags -> ShowS

show :: OpenFlags -> String

showList :: [OpenFlags] -> ShowS

openFlags :: OpenFlags

Default settings for OpenFlags, where the writeMode is ReadWrite Create, threadMode is Serialized, cacheMode is Private, and all other flags are set to False.

openWithFlags

Arguments

:: OpenFlags

-> Text

Database filename

-> IO Database

Opens a new Database, with settings specified with openFlags.

Easy execution of statements

Often this is all you will need to execute statements.

exec

Arguments

:: Database

-> Text

SQL to be evaluated. Multiple, semicolon-separated statements will be executed.

-> IO ()

Evaluate one or more SQL statements. There is no way to obtain the results; for that you will need execute or executeNamed. There is no way to use SQL parameters; for that you will need executeNamed.

data SQLData

Various types of SQL data; used both when obtaining query results and when providing named parameters.

Constructors

SQLNull

SQLText Text

SQLFloat Double

SQLInteger Int64

SQLBlob ByteString

Instances

Eq SQLData

Defined in Squeather.Internal.Bindings

Methods

(==) :: SQLData -> SQLData -> Bool

(/=) :: SQLData -> SQLData -> Bool

Ord SQLData

Defined in Squeather.Internal.Bindings

Methods

compare :: SQLData -> SQLData -> Ordering

(<) :: SQLData -> SQLData -> Bool

(<=) :: SQLData -> SQLData -> Bool

(>) :: SQLData -> SQLData -> Bool

(>=) :: SQLData -> SQLData -> Bool

max :: SQLData -> SQLData -> SQLData

min :: SQLData -> SQLData -> SQLData

Show SQLData

Defined in Squeather.Internal.Bindings

Methods

showsPrec :: Int -> SQLData -> ShowS

show :: SQLData -> String

showList :: [SQLData] -> ShowS

execute

Arguments

:: Database

-> Text

SQL text

-> IO [[SQLData]]

All SQL data from the query.

Execute a query without any parameters. Executes only one query - there is no need to terminate it with a semicolon, although you can. If you use a semicolon-separated list of queries, only the first query will be run. There is no way to use SQL parameters; for that you will need executeNamed.

executeNamed

Arguments

:: Database

-> Text

SQL text

-> [(Text, SQLData)]

Pairs, where each Text is a named parameter and each SQLData is the corresponding data to bind to that parameter. This page describes the different parameter syntax that is allowed. Squeather makes no effort to support the plain ? syntax. Note that the leading mark (?, :, @, or $) is part of the parameter name and must appear as part of the Text.

-> IO [[SQLData]]

All SQL data from the query.

Execute a query with named parameters. Executes only one query - there is no need to terminate it with a semicolon, although you can. If you use a semicolon-separated list of queries, only the first query will be run.

executeNamedWithColumns

Arguments

:: Database

-> Text

SQL text

-> [(Text, SQLData)]

Pairs, where each Text is a named parameter and each SQLData is the corresponding data to bind to that parameter. This page describes the different parameter syntax that is allowed. Squeather makes no effort to support the plain ? syntax. Note that the leading mark (?, :, @, or $) is part of the parameter name and must appear as part of the Text.

-> IO ([Text], [[SQLData]])

The column names, and all SQL data from the query.

Like executeNamed but also returns the names of the columns in addition to the SQL results.

Statistics

lastInsertRowId :: Database -> IO Int64

Get the rowid of the most recent successful INSERT.

changes :: Database -> IO Int

Count the number of rows modified by the most recent INSERT, UPDATE, or DELETE statement.

Backups

data Source

Backup source

Constructors

Source

Fields

• sourceConnection :: Database
• sourceName :: Text

  The name for the source database. Use main for the main database, temp for the temporary database, or the name specified after the AS keyword in an ATTACH statement for an attached database.

Instances

Eq Source

Defined in Squeather.Internal

Methods

(==) :: Source -> Source -> Bool

(/=) :: Source -> Source -> Bool

Ord Source

Defined in Squeather.Internal

Methods

compare :: Source -> Source -> Ordering

(<) :: Source -> Source -> Bool

(<=) :: Source -> Source -> Bool

(>) :: Source -> Source -> Bool

(>=) :: Source -> Source -> Bool

max :: Source -> Source -> Source

min :: Source -> Source -> Source

Show Source

Defined in Squeather.Internal

Methods

showsPrec :: Int -> Source -> ShowS

show :: Source -> String

showList :: [Source] -> ShowS

data Destination

Backup destination

Constructors

Destination

Fields

• destConnection :: Database
• destName :: Text

  The name for the destination database. Use main for the main database, temp for the temporary database, or the name specified after the AS keyword in an ATTACH statement for an attached database.

Instances

Eq Destination

Defined in Squeather.Internal

Methods

(==) :: Destination -> Destination -> Bool

(/=) :: Destination -> Destination -> Bool

Ord Destination

Defined in Squeather.Internal

Methods

compare :: Destination -> Destination -> Ordering

(<) :: Destination -> Destination -> Bool

(<=) :: Destination -> Destination -> Bool

(>) :: Destination -> Destination -> Bool

(>=) :: Destination -> Destination -> Bool

max :: Destination -> Destination -> Destination

min :: Destination -> Destination -> Destination

Show Destination

Defined in Squeather.Internal

Methods

showsPrec :: Int -> Destination -> ShowS

show :: Destination -> String

showList :: [Destination] -> ShowS

backup :: Source -> Destination -> IO ()

Use the SQLite backup API to copy the content of one database to another. Can be used to safely copy databases while they are in use, or to copy in-memory databases to or from persistent files.

Statements

For more control over statement execution.

data Statement

Statement handle. To create a statement handle, use prepare. The resources behind the Statement are automatically destroyed when there are no remaining references to the Statement, so Squeather provides no finalize function.

Instances

Eq Statement

Defined in Squeather.Internal

Methods

(==) :: Statement -> Statement -> Bool

(/=) :: Statement -> Statement -> Bool

Ord Statement

Defined in Squeather.Internal

Methods

compare :: Statement -> Statement -> Ordering

(<) :: Statement -> Statement -> Bool

(<=) :: Statement -> Statement -> Bool

(>) :: Statement -> Statement -> Bool

(>=) :: Statement -> Statement -> Bool

max :: Statement -> Statement -> Statement

min :: Statement -> Statement -> Statement

Show Statement

Defined in Squeather.Internal

Methods

showsPrec :: Int -> Statement -> ShowS

show :: Statement -> String

showList :: [Statement] -> ShowS

data StepResult

Result from applying step to a Statement.

Constructors

Row

A row is ready to be processed

Done

There are no more rows

Instances

Eq StepResult

Defined in Squeather.Internal.Types

Methods

(==) :: StepResult -> StepResult -> Bool

(/=) :: StepResult -> StepResult -> Bool

Ord StepResult

Defined in Squeather.Internal.Types

Methods

compare :: StepResult -> StepResult -> Ordering

(<) :: StepResult -> StepResult -> Bool

(<=) :: StepResult -> StepResult -> Bool

(>) :: StepResult -> StepResult -> Bool

(>=) :: StepResult -> StepResult -> Bool

max :: StepResult -> StepResult -> StepResult

min :: StepResult -> StepResult -> StepResult

Show StepResult

Defined in Squeather.Internal.Types

Methods

showsPrec :: Int -> StepResult -> ShowS

show :: StepResult -> String

showList :: [StepResult] -> ShowS

prepare

Arguments

:: Database

Database handle

-> Text

SQL Statement, UTF-8

-> IO Statement

Prepares a statement. The corresponding C SQLite function allows you to pass in a multi-statement SQL text, and retrieve the unused portion for later use. Squeather does not allow this. Squeather will prepare only the first statement.

columnCount :: Statement -> IO Int

The number of columns that a given Statement will return. Works regardless of whether step has been applied or not; however, just because this returns a positive value does not mean that step will ever actually return a Row.

columnName

Arguments

:: Statement

-> Int

Index. The leftmost column is 0.

-> IO Text

Gets the name of a column. The name is the value of the AS clause if it exists, or is an undefined string otherwise.

columnNames :: Statement -> IO [Text]

Gets all column names, in order.

bindParams :: Statement -> [(Text, SQLData)] -> IO ()

Bind multiple named parameters to a Statement.

step :: Statement -> IO StepResult

Evaluate a prepared statement. Returns Row if the Statement has returned a row of data. In that case, use column or columns to get individual columns or all columns, respectively. Returns Done if there is no data to retrieve. In that case, step should not be called again without first calling reset.

column

Arguments

:: Statement

-> Int

Index

-> IO SQLData

Retrieves a column with a given index from the Statement. Assumes that step was already called and that it returned Row.

columns :: Statement -> IO [SQLData]

Return all available columns, in order, from a Statement on which step returned Row. You should already have applied step.

allRows :: Statement -> IO [[SQLData]]

Retrieves all remaining rows from a Statement. Applies step for you for as many times as needed.

reset :: Statement -> IO ()

Resets a Statement so it may be re-executed. Does not clear bindings. In SQLite, sqlite3_reset returns an error code if the most recent step statement returned an error. reset does not do this. It does not check the error code returned by sqlite3_reset.

clearBindings :: Statement -> IO ()

Clears all bindings on the Statement.

Errors

data ErrorFlag

Errors produced by the underlying SQLite3 C library.

Constructors

SQLITE_ERROR

SQLITE_INTERNAL

SQLITE_PERM

SQLITE_ABORT

SQLITE_BUSY

SQLITE_LOCKED

SQLITE_NOMEM

SQLITE_READONLY

SQLITE_INTERRUPT

SQLITE_IOERR

SQLITE_CORRUPT

SQLITE_NOTFOUND

SQLITE_FULL

SQLITE_CANTOPEN

SQLITE_PROTOCOL

SQLITE_EMPTY

SQLITE_SCHEMA

SQLITE_TOOBIG

SQLITE_CONSTRAINT

SQLITE_MISMATCH

SQLITE_MISUSE

SQLITE_NOLFS

SQLITE_AUTH

SQLITE_FORMAT

SQLITE_RANGE

SQLITE_NOTADB

SQLITE_NOTICE

SQLITE_WARNING

SQLITE_ERROR_MISSING_COLLSEQ

SQLITE_ERROR_RETRY

SQLITE_ERROR_SNAPSHOT

SQLITE_IOERR_READ

SQLITE_IOERR_SHORT_READ

SQLITE_IOERR_WRITE

SQLITE_IOERR_FSYNC

SQLITE_IOERR_DIR_FSYNC

SQLITE_IOERR_TRUNCATE

SQLITE_IOERR_FSTAT

SQLITE_IOERR_UNLOCK

SQLITE_IOERR_RDLOCK

SQLITE_IOERR_DELETE

SQLITE_IOERR_BLOCKED

SQLITE_IOERR_NOMEM

SQLITE_IOERR_ACCESS

SQLITE_IOERR_CHECKRESERVEDLOCK

SQLITE_IOERR_LOCK

SQLITE_IOERR_CLOSE

SQLITE_IOERR_DIR_CLOSE

SQLITE_IOERR_SHMOPEN

SQLITE_IOERR_SHMSIZE

SQLITE_IOERR_SHMLOCK

SQLITE_IOERR_SHMMAP

SQLITE_IOERR_SEEK

SQLITE_IOERR_DELETE_NOENT

SQLITE_IOERR_MMAP

SQLITE_IOERR_GETTEMPPATH

SQLITE_IOERR_CONVPATH

SQLITE_IOERR_VNODE

SQLITE_IOERR_AUTH

SQLITE_IOERR_BEGIN_ATOMIC

SQLITE_IOERR_COMMIT_ATOMIC

SQLITE_IOERR_ROLLBACK_ATOMIC

SQLITE_LOCKED_SHAREDCACHE

SQLITE_LOCKED_VTAB

SQLITE_BUSY_RECOVERY

SQLITE_BUSY_SNAPSHOT

SQLITE_CANTOPEN_NOTEMPDIR

SQLITE_CANTOPEN_ISDIR

SQLITE_CANTOPEN_FULLPATH

SQLITE_CANTOPEN_CONVPATH

SQLITE_CANTOPEN_DIRTYWAL

SQLITE_CANTOPEN_SYMLINK

SQLITE_CORRUPT_VTAB

SQLITE_CORRUPT_SEQUENCE

SQLITE_READONLY_RECOVERY

SQLITE_READONLY_CANTLOCK

SQLITE_READONLY_ROLLBACK

SQLITE_READONLY_DBMOVED

SQLITE_READONLY_CANTINIT

SQLITE_READONLY_DIRECTORY

SQLITE_ABORT_ROLLBACK

SQLITE_CONSTRAINT_CHECK

SQLITE_CONSTRAINT_COMMITHOOK

SQLITE_CONSTRAINT_FOREIGNKEY

SQLITE_CONSTRAINT_FUNCTION

SQLITE_CONSTRAINT_NOTNULL

SQLITE_CONSTRAINT_PRIMARYKEY

SQLITE_CONSTRAINT_TRIGGER

SQLITE_CONSTRAINT_UNIQUE

SQLITE_CONSTRAINT_VTAB

SQLITE_CONSTRAINT_ROWID

SQLITE_CONSTRAINT_PINNED

SQLITE_NOTICE_RECOVER_WAL

SQLITE_NOTICE_RECOVER_ROLLBACK

SQLITE_WARNING_AUTOINDEX

SQLITE_AUTH_USER

SQLITE_OK_LOAD_PERMANENTLY

SQLITE_OK_SYMLINK

Instances

Eq ErrorFlag

Defined in Squeather.Internal.Types

Methods

(==) :: ErrorFlag -> ErrorFlag -> Bool

(/=) :: ErrorFlag -> ErrorFlag -> Bool

Ord ErrorFlag

Defined in Squeather.Internal.Types

Methods

compare :: ErrorFlag -> ErrorFlag -> Ordering

(<) :: ErrorFlag -> ErrorFlag -> Bool

(<=) :: ErrorFlag -> ErrorFlag -> Bool

(>) :: ErrorFlag -> ErrorFlag -> Bool

(>=) :: ErrorFlag -> ErrorFlag -> Bool

max :: ErrorFlag -> ErrorFlag -> ErrorFlag

min :: ErrorFlag -> ErrorFlag -> ErrorFlag

Show ErrorFlag

Defined in Squeather.Internal.Types

Methods

showsPrec :: Int -> ErrorFlag -> ShowS

show :: ErrorFlag -> String

showList :: [ErrorFlag] -> ShowS

data SqueatherErrorFlag

Errors produced by the Squeather library (as opposed to being caused directly by the underlying SQLite3 C library.)

Constructors

ParameterNotFound

Named parameter for SQL statement not found

ExecFailed

The exec function found an error string

IntConversion

Failed to convert an Int to a CInt or vice-versa because the values were out of range.

UnknownColumnType CInt

sqlite3_column_type returned a type Squeather didn't identify.

UnknownSqliteError CInt

SQLite returned an error code that is uknown to Squeather.

IncompleteBackup

A backup was started, but it did not finish running.

Bug

These failures should never happen and indicate a bug in Squeather.

ColumnNameNull Int

The call to sqlite3_column_name returned a null pointer.

Instances

Eq SqueatherErrorFlag

Defined in Squeather.Internal

Methods

(==) :: SqueatherErrorFlag -> SqueatherErrorFlag -> Bool

(/=) :: SqueatherErrorFlag -> SqueatherErrorFlag -> Bool

Ord SqueatherErrorFlag

Defined in Squeather.Internal

Methods

compare :: SqueatherErrorFlag -> SqueatherErrorFlag -> Ordering

(<) :: SqueatherErrorFlag -> SqueatherErrorFlag -> Bool

(<=) :: SqueatherErrorFlag -> SqueatherErrorFlag -> Bool

(>) :: SqueatherErrorFlag -> SqueatherErrorFlag -> Bool

(>=) :: SqueatherErrorFlag -> SqueatherErrorFlag -> Bool

max :: SqueatherErrorFlag -> SqueatherErrorFlag -> SqueatherErrorFlag

min :: SqueatherErrorFlag -> SqueatherErrorFlag -> SqueatherErrorFlag

Show SqueatherErrorFlag

Defined in Squeather.Internal

Methods

showsPrec :: Int -> SqueatherErrorFlag -> ShowS

show :: SqueatherErrorFlag -> String

showList :: [SqueatherErrorFlag] -> ShowS

data Error

Exceptions. Squeather indicates all errors (even those arising from possible bugs) by throwing exceptions of this type.

Constructors

Error

Fields

• errorContext :: Text

  Gives a context where this error occured, such as a SELECT query or a filename passed to open.

• errorFlag :: Either ErrorFlag SqueatherErrorFlag

  Either the error flag returned by SQLite library, or the flag produced by this library.

• errorText :: Text

  The text description of the error, as returned by SQLite or as created by Squeather.

• errorFilename :: Text

  The filename of the database giving rise to the error.

Instances

Eq Error

Defined in Squeather.Internal

Methods

(==) :: Error -> Error -> Bool

(/=) :: Error -> Error -> Bool

Ord Error

Defined in Squeather.Internal

Methods

compare :: Error -> Error -> Ordering

(<) :: Error -> Error -> Bool

(<=) :: Error -> Error -> Bool

(>) :: Error -> Error -> Bool

(>=) :: Error -> Error -> Bool

max :: Error -> Error -> Error

min :: Error -> Error -> Error

Show Error

Defined in Squeather.Internal

Methods

showsPrec :: Int -> Error -> ShowS

show :: Error -> String

showList :: [Error] -> ShowS

Exception Error

Defined in Squeather.Internal

Methods

toException :: Error -> SomeException

fromException :: SomeException -> Maybe Error

displayException :: Error -> String

Version

sqliteVersion :: String

Returns a string which is the version number for SQLite used to build this library. SQLite is embedded into the library, so the only way to change the SQLite version is to recompile the library.