Headline

Schema-aware database programming with Technology:HaskellDB

Motivation

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
a
. 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.

Usage

Setup

  • Follow the steps of setting up the database as described in the usage section for the hdbc implementation.

Generating the database description

  • Execute the following command in the implementation folder:
Invalid Language supplied
  • Compile the DBDesc module using GHC:
    Invalid Language supplied

Testing

function has to be applied. One can also use the this!!Makefile with a target test covering both database descriptions generation and testing.

Metadata


There are no revisions for this page.

User contributions

    This user never has never made submissions.

    User edits

    Syntax for editing wiki

    For you are available next options:

    will make text bold.

    will make text italic.

    will make text underlined.

    will make text striked.

    will allow you to paste code headline into the page.

    will allow you to link into the page.

    will allow you to paste code with syntax highlight into the page. You will need to define used programming language.

    will allow you to paste image into the page.

    is list with bullets.

    is list with numbers.

    will allow your to insert slideshare presentation into the page. You need to copy link to presentation and insert it as parameter in this tag.

    will allow your to insert youtube video into the page. You need to copy link to youtube page with video and insert it as parameter in this tag.

    will allow your to insert code snippets from @worker.

    Syntax for editing wiki

    For you are available next options:

    will make text bold.

    will make text italic.

    will make text underlined.

    will make text striked.

    will allow you to paste code headline into the page.

    will allow you to link into the page.

    will allow you to paste code with syntax highlight into the page. You will need to define used programming language.

    will allow you to paste image into the page.

    is list with bullets.

    is list with numbers.

    will allow your to insert slideshare presentation into the page. You need to copy link to presentation and insert it as parameter in this tag.

    will allow your to insert youtube video into the page. You need to copy link to youtube page with video and insert it as parameter in this tag.

    will allow your to insert code snippets from @worker.