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

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   

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

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

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

Runs a DBAction on a connection.

runInTransaction :: DBAction a -> DBAction a   

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   

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   

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   

Returns an SQLResult.

failDB :: DBError -> DBAction a   

A failed DBAction with a specific error.

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

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

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

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   

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

Disconnect from a database.

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

Write a String to a Connection.

begin :: DBAction ()   

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

commit :: DBAction ()   

Commit a transaction.

rollback :: DBAction ()   

Rollback a transaction.

setForeignKeyCheck :: Bool -> DBAction ()   

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

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

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

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]   

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

valueToString :: SQLValue -> String