Module Database.CDBI.Connection

This module defines basis data types and functions for accessing database systems using SQL. Currently, only SQLite3 is supported, but this is easy to extend. It also provides execution of SQL-Queries with types. Allowed datatypes for these queries are defined and the conversion to standard SQL-Queries is provided.

Author: Mike Tallarek, Michael Hanus

Summary of exported operations:

fromSQLResult :: Either DBError a -> a  Deterministic 
Gets the value of an SQLResult.
printSQLResults :: Show a => Either DBError [a] -> IO ()  Deterministic 
Print an SQLResult list, i.e., print either the DBError or the list of result elements.
runDBAction :: DBAction a -> Connection -> IO (Either DBError a)  Deterministic 
Runs a DBAction on a connection.
runInTransaction :: DBAction a -> DBAction a  Non-deterministic 
Run a DBAction as a transaction.
(>+=) :: DBAction a -> (a -> DBAction b) -> DBAction b  Deterministic 
Connects two DBActions.
(>+) :: DBAction a -> DBAction b -> DBAction b  Deterministic 
Connects two DBActions but ignore the result of the first.
returnDB :: Either DBError a -> DBAction a  Deterministic 
Returns an SQLResult.
failDB :: DBError -> DBAction a  Deterministic 
A failed DBAction with a specific error.
select :: String -> [SQLValue] -> [SQLType] -> DBAction [[SQLValue]]  Non-deterministic 
Execute a query where the result of the execution is returned.
execute :: String -> [SQLValue] -> DBAction ()  Non-deterministic 
execute a query without a result
executeMultipleTimes :: String -> [[SQLValue]] -> DBAction ()  Non-deterministic 
Executes a query multiple times with different SQLValues without a result
connectSQLite :: String -> IO Connection  Deterministic 
Connect to a SQLite Database
disconnect :: Connection -> IO ()  Deterministic 
Disconnect from a database.
writeConnection :: String -> Connection -> IO ()  Deterministic 
Write a String to a Connection.
begin :: DBAction ()  Deterministic 
Begin a transaction.
commit :: DBAction ()  Deterministic 
Commit a transaction.
rollback :: DBAction ()  Deterministic 
Rollback a transaction.
setForeignKeyCheck :: Bool -> DBAction ()  Deterministic 
Turn on/off checking of foreign key constraints (SQLite3).
runWithDB :: String -> DBAction a -> IO (Either DBError a)  Deterministic 
Executes an action dependent on a connection on a database by connecting to the datebase.
executeRaw :: String -> [String] -> DBAction [[String]]  Non-deterministic 
Executes an SQL statement.
getColumnNames :: String -> DBAction [String]  Non-deterministic 
Returns a list with the names of every column in a table The parameter is the name of the table.
valueToString :: SQLValue -> String  Non-deterministic 

Exported datatypes:


SQLResult

The result of SQL-related actions. It is either a DBError or some value.

Type synonym: SQLResult a = Either DBError a


DBError

DBErrors are composed of an DBErrorKind and a String describing the error more explicitly.

Constructors:


DBErrorKind

The different kinds of errors.

Constructors:

  • TableDoesNotExist :: DBErrorKind
  • ParameterError :: DBErrorKind
  • ConstraintViolation :: DBErrorKind
  • SyntaxError :: DBErrorKind
  • NoLineError :: DBErrorKind
  • LockedDBError :: DBErrorKind
  • UnknownError :: DBErrorKind

SQLValue

Data type for SQL values, used during the communication with the database.

Constructors:

  • SQLString :: String -> SQLValue
  • SQLInt :: Int -> SQLValue
  • SQLFloat :: Float -> SQLValue
  • SQLChar :: Char -> SQLValue
  • SQLBool :: Bool -> SQLValue
  • SQLDate :: ClockTime -> SQLValue
  • SQLNull :: SQLValue

SQLType

Type identifiers for SQLValues, necessary to determine the type of the value a column should be converted to.

Constructors:

  • SQLTypeString :: SQLType
  • SQLTypeInt :: SQLType
  • SQLTypeFloat :: SQLType
  • SQLTypeChar :: SQLType
  • SQLTypeBool :: SQLType
  • SQLTypeDate :: SQLType

DBAction

A DBAction takes a connection and performs an IO action that returns a SQLResult a value.

Constructors:


Connection

Data type for database connections. Currently, only connections to a SQLite3 database are supported, but other types of connections could easily be added. The following functions might need to be re-implemented for other DBs: A function to connect to the database, disconnect, writeConnection readRawConnectionLine, parseLines, begin, commit, rollback, and getColumnNames

Constructors:

  • SQLiteConnection :: Handle -> Connection

Exported operations:

fromSQLResult :: Either DBError a -> a  Deterministic 

Gets the value of an SQLResult. If there is no result value but a database error, the error is raised.

printSQLResults :: Show a => Either DBError [a] -> IO ()  Deterministic 

Print an SQLResult list, i.e., print either the DBError or the list of result elements.

runDBAction :: DBAction a -> Connection -> IO (Either DBError a)  Deterministic 

Runs a DBAction on a connection.

runInTransaction :: DBAction a -> DBAction a  Non-deterministic 

Run a DBAction as a transaction. In case of an error, it will rollback all changes, otherwise, the changes are committed. The transaction is also checked whether foreign key errors have been introduced so that a transaction which introduces foreign key errors will never be committed.

Example call:
(runInTransaction act conn)
Parameters:
  • act : The DBAction
  • conn : The Connection to the database on which the transaction shall be executed.

(>+=) :: DBAction a -> (a -> DBAction b) -> DBAction b  Deterministic 

Connects two DBActions. When executed this function will execute the first DBAction and then execute the second applied to the result of the first action. A database error will stop either action.

Example call:
(x >+= y)
Parameters:
  • x : The DBAction that will be executed first
  • y : The DBAction hat will be executed afterwards
Returns:
A DBAction that wille execute both DBActions. The result is the result of the second DBAction.
Further infos:
  • defined as left-associative infix operator with precedence 1

(>+) :: DBAction a -> DBAction b -> DBAction b  Deterministic 

Connects two DBActions but ignore the result of the first.

Further infos:
  • defined as left-associative infix operator with precedence 1

returnDB :: Either DBError a -> DBAction a  Deterministic 

Returns an SQLResult.

failDB :: DBError -> DBAction a  Deterministic 

A failed DBAction with a specific error.

select :: String -> [SQLValue] -> [SQLType] -> DBAction [[SQLValue]]  Non-deterministic 

Execute a query where the result of the execution is returned.

Example call:
(select query values types conn)
Parameters:
  • query : The SQL Query as a String, might have ? as placeholder
  • values : A list of SQLValues that replace the ? placeholder
  • types : A list of SQLTypes that describe the types of the result-tables (e.g. "select * from exampletable" and [SQLTypeInt, SQLTypeFloat, SQLTypeString] when the table exampletable has three columns of type Int, Float and String.) The order of the list has to be the same as the order of the columns in the table
  • conn : A Connection to a database where the query will be executed
Returns:
A Result with a list of SQLValues which types correspond to the SQLType-List that was given as a parameter if the execution was successful, otherwise an Error

execute :: String -> [SQLValue] -> DBAction ()  Non-deterministic 

execute a query without a result

Example call:
(execute query values conn)
Parameters:
  • query : The SQL Query as a String, might have ? as placeholder
  • values : A list of SQLValues that replace the ? placeholder
  • conn : A Connection to a database where the query will be executed
Returns:
An empty if the execution was successful, otherwise an error

executeMultipleTimes :: String -> [[SQLValue]] -> DBAction ()  Non-deterministic 

Executes a query multiple times with different SQLValues without a result

Example call:
(executeMultipleTimes query values)
Parameters:
  • query : The SQL Query as a String, might have ? as placeholder
  • values : A list of lists of SQLValues that replace the ? placeholder (one list for every execution)
Returns:
A void result if every execution was successful, otherwise an Error (meaning at least one execution failed). As soon as one execution fails, the rest wont be executed.

connectSQLite :: String -> IO Connection  Deterministic 

Connect to a SQLite Database

Example call:
(connectSQLite str)
Parameters:
  • str : name of the database (e.g. "database.db")
Returns:
A connection to a SQLite Database

disconnect :: Connection -> IO ()  Deterministic 

Disconnect from a database.

writeConnection :: String -> Connection -> IO ()  Deterministic 

Write a String to a Connection.

begin :: DBAction ()  Deterministic 

Begin a transaction. Inside a transaction, foreign key constraints are checked.

commit :: DBAction ()  Deterministic 

Commit a transaction.

rollback :: DBAction ()  Deterministic 

Rollback a transaction.

setForeignKeyCheck :: Bool -> DBAction ()  Deterministic 

Turn on/off checking of foreign key constraints (SQLite3).

runWithDB :: String -> DBAction a -> IO (Either DBError a)  Deterministic 

Executes an action dependent on a connection on a database by connecting to the datebase. The connection will be kept open and re-used for the next action to this database.

Example call:
(runWithDB str action)
Parameters:
  • str : name of the database (e.g. "database.db")
  • action : an action parameterized over a database connection
Returns:
the result of the action

executeRaw :: String -> [String] -> DBAction [[String]]  Non-deterministic 

Executes an SQL statement. The statement may contain ? placeholders and a list of parameters which should be inserted at the respective positions. The result is a list of list of strings where every single list represents a row of the result.

getColumnNames :: String -> DBAction [String]  Non-deterministic 

Returns a list with the names of every column in a table The parameter is the name of the table.

valueToString :: SQLValue -> String  Non-deterministic