haskellDB

Headline

Schema-aware database programming with Technology:HaskellDB

The implementation demonstrates schema-aware database programming in Language:Haskell with Technology:HaskellDB. That is, the database schema of a database for companies is mapped to Haskell types. Database queries are expressed as Haskell functions on top of Haskell types that are derived by Technology:DBDirect, which is part of Technology:HaskellDB. The functions leverage HaskellDB's library for relational algebra and other CRUD expressiveness. For comparison, see Contribution:hdbc for a demonstration of schema-unaware database programming in Haskell where database queries are expressed in Language:SQL whose execution is embedded into Haskell. Technology:DBDirect derives one module per database table. These modules are the basis for accessing the database within the Haskell program in a schema-aware manner. For instance, operations for totaling and cutting salaries are implemented with the module for employees and while leveraging combinators for projection, selection, aggregation, and renaming. With the use of relational algebra and library functions for other CRUD expressiveness, all operations are effectively independent of the underlying DBMS <cite>bringert2004student</cite>. The connection to the underlining Technology:MySQL database is achieved by means of a "HaskellDB-Technology:HDBC-Technology:ODBC" back-end.

Illustration

Connecting to the database

We provide a function to connect to a database and execute an action:

execute :: (Database -> IO a) -> IO a
execute = connect driver conf
  where           
    conf = [ ("Driver","MySQL ODBC 5.1 Driver")
           , ("Port","3306")
           , ("Server", "localhost")
           , ("User", "root")
           , ("Database", "101companies") ]

We use

connect

and

driver

, which are both provided by the HDBC-ODBC back-end. By looking at the return type of the function one can see that any database function of type

Database -> IO a

can be applied to

execute

resulting in the specified IO action and possibly a result of type

. By encapsulating the connection process like this we achieve complete independence from the underling database implementation for all queries and statements.

DBDirect

We use the DBDirect command

Invalid Language supplied

(see the usage section for the complete command). This command generates a module describing the database by naming tables and fields. Compiling this module with GHC creates one module per table, each module holding actual variables for tables and fields. These variables are the basis for the following totaling query.

Totaling

We import the description modules for the companies and employees tables:

import qualified DBDesc.Employee as E
import qualified DBDesc.Company as C

We define a special field for storing the sum of all salaries:

data Ttl = Ttl

instance FieldTag Ttl where fieldName   = "ttl"
  
ttl :: Attr Ttl Double
ttl = mkAttr Ttl

We declare

Ttl

to be an instance of the

FieldTag

class by specifying what the name of the field should be. We use this field and HaskellDB's

mkAttr

to define an attribute

ttl

for holding a

Double

value. The actual total query is defined as follows:

total :: String -> Query (Rel (RecCons Ttl (Expr Double) RecNil))
total cname = do 
  es <- table E.employee
  cs <- table C.company

  restrict $
    ( fromNull (constant 0) (cs!C.xid) .==. es!E.cid 
      .&&. 
      cs!C.name .==. constant cname )

  project (ttl <<  sum (es!E.salary))

We are working in the

Query

monad. The

table

functions return all records in the given table. Using two tables gives us the relational cross product of those tables lines 3 and 4. We use HaskellDB's selection function

restrict

in line 6-9 to select only those rows in which the company-id of the employee is equal to the company which has the given name

cname

. By making use of

project

in line 11 we only select the salary column and then use the aggregation function

sum

to total all salaries. After that we put the total value in

ttl

Executing the query

We use

query

and

execute

to execute the totaling query:

let cname = "meganalysis"
[res] <- execute $ (flip $ query) $ total cname

This gives us a list (which we expect to be a singleton list) of records. We can now access the

ttl

attribute of the record

res

by using the

(!)

-operator and print the total value:

print $ res!ttl

Architecture

We provide MySQL-scripts to create (see this!!Company.sql) company tables and populate (see this!!Meganalysis.sql) these tables. this!!Total.hs and this!!Cut.hs provide totaling and cutting functionality using HaskellDB's relational algebra library. this!!MyConnection.hs encapsulates the process of connecting to the MySQL database. this!!Main.hs collects test scenarios for totaling and cutting.