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 :: Either DBError [a] -> IO ()   
Print an SQLResult list, i.e., print either the DBError or the list of result elements.
runInTransaction :: (Connection -> IO (Either DBError a)) -> Connection -> IO (Either DBError a)   
Run a DBAction as a transaction.
(>+=) :: (Connection -> IO (Either DBError a)) -> (a -> Connection -> IO (Either DBError b)) -> Connection -> IO (Either DBError b)   
Connect two DBActions.
(>+) :: (Connection -> IO (Either DBError a)) -> (Connection -> IO (Either DBError b)) -> Connection -> IO (Either DBError b)   
Connect two DBActions, but ignore the result of the first.
fail :: DBError -> Connection -> IO (Either DBError a)   
Failing action.
ok :: a -> Connection -> IO (Either DBError a)   
Successful action.
sequenceDBAction :: [Connection -> IO (Either DBError a)] -> Connection -> IO (Either DBError [a])   
Executes a list of DB actions sequentially and returns the list of all results.
sequenceDBAction_ :: [Connection -> IO (Either DBError a)] -> Connection -> IO (Either DBError ())   
Executes a list of DB actions sequentially, ignoring their results.
mapDBAction :: (a -> Connection -> IO (Either DBError b)) -> [a] -> Connection -> IO (Either DBError [b])   
Applies a function that yields DB actions to all elements of a list, executes the transaction sequentially, and collects their results.
mapDBAction_ :: (a -> Connection -> IO (Either DBError b)) -> [a] -> Connection -> IO (Either DBError ())   
Applies a function that yields DB actions to all elements of a list, executes the transactions sequentially, and ignores their results.
select :: String -> [SQLValue] -> [SQLType] -> Connection -> IO (Either DBError [[SQLValue]])   
Execute a query where the result of the execution is returned.
execute :: String -> [SQLValue] -> Connection -> IO (Either DBError ())   
execute a query without a result
executeMultipleTimes :: String -> [[SQLValue]] -> Connection -> IO (Either DBError ())   
execute 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.
begin :: Connection -> IO ()   
Begin a transaction.
commit :: Connection -> IO ()   
Commit a transaction.
rollback :: Connection -> IO ()   
Rollback a transaction.
runWithDB :: String -> (Connection -> IO a) -> IO a   
Executes an action dependent on a connection on a database by connecting to the datebase.
executeRaw :: String -> [String] -> Connection -> IO (Either DBError [[String]])   
Execute a SQL statement.
getColumnNames :: String -> Connection -> IO (Either DBError [String])   
Returns a list with the names of every column in a table The parameter is the name of the table and a connection
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 returns an IO (SQLResult a).

Type synonym: DBAction a = Connection -> IO (SQLResult a)


Connection

Data type for database connections. Currently, only connections to a SQLite3 database are supported, but other types of connections could easily be added. List of functions that would need to be implemented: 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 :: Either DBError [a] -> IO ()   

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

runInTransaction :: (Connection -> IO (Either DBError a)) -> Connection -> IO (Either DBError a)   

Run a DBAction as a transaction. In case of an Error it will rollback all changes, otherwise the changes are committed.

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

(>+=) :: (Connection -> IO (Either DBError a)) -> (a -> Connection -> IO (Either DBError b)) -> Connection -> IO (Either DBError b)   

Connect two DBActions. When executed this function will execute the first DBAction and then execute the second applied to the first result An 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.

(>+) :: (Connection -> IO (Either DBError a)) -> (Connection -> IO (Either DBError b)) -> Connection -> IO (Either DBError b)   

Connect two DBActions, but ignore the result of the first.

fail :: DBError -> Connection -> IO (Either DBError a)   

Failing action.

Further infos:
  • solution complete, i.e., able to compute all solutions

ok :: a -> Connection -> IO (Either DBError a)   

Successful action.

Further infos:
  • solution complete, i.e., able to compute all solutions

sequenceDBAction :: [Connection -> IO (Either DBError a)] -> Connection -> IO (Either DBError [a])   

Executes a list of DB actions sequentially and returns the list of all results.

sequenceDBAction_ :: [Connection -> IO (Either DBError a)] -> Connection -> IO (Either DBError ())   

Executes a list of DB actions sequentially, ignoring their results.

mapDBAction :: (a -> Connection -> IO (Either DBError b)) -> [a] -> Connection -> IO (Either DBError [b])   

Applies a function that yields DB actions to all elements of a list, executes the transaction sequentially, and collects their results.

mapDBAction_ :: (a -> Connection -> IO (Either DBError b)) -> [a] -> Connection -> IO (Either DBError ())   

Applies a function that yields DB actions to all elements of a list, executes the transactions sequentially, and ignores their results.

select :: String -> [SQLValue] -> [SQLType] -> Connection -> IO (Either DBError [[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] -> Connection -> IO (Either DBError ())   

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]] -> Connection -> IO (Either DBError ())   

execute a query multiple times with different SQLValues without a result

Example call:
(executeMultipleTimes query values conn)
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)
  • conn : A Connection to a database where the query will be executed
Returns:
A empty 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.

begin :: Connection -> IO ()   

Begin a transaction.

commit :: Connection -> IO ()   

Commit a transaction.

rollback :: Connection -> IO ()   

Rollback a transaction.

runWithDB :: String -> (Connection -> IO a) -> IO 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] -> Connection -> IO (Either DBError [[String]])   

Execute a 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 -> Connection -> IO (Either DBError [String])   

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

valueToString :: SQLValue -> String