Databases Programming Assignment

Purpose

In the main assignment text, task 4 is about writing a front-end application, the Student Portal, in Java using JDBC. Here we describe how to implement the Student Portal in Haskell using HDBC, which you might prefer if you already know Haskell. You also get two user interfaces for the price of one :-)

Requirements and deliverables

If you implement your Student Portal in Haskell instead of Java, the requirements are the same, i.e. the expected functionality is still the same, and the same tests will be performed when you demonstrate your solution in one of supervised lab session. The only difference is that you submit StudentPortal.hs instead of StudentPortal.java. See task 4 in the main assignment text for details.

Task 4: Front-end application in Haskell

We provide the Haskell modules described below as a starting point for your work. You can download them all at once from task4haskell.zip.

StudentPortal.hs

This is the main module for the Student Portal application. It contains three incomplete function definitions where you should add your own code to access the database. You can of course also add additional helper functions as you see fit.

We also provide the following modules, which you can use unchanged:

StudentPortalCommon.hs

Common type defintions.

SqlRows.hs

Convenience functions for converting SQL rows (query result) to tuples, like the types defined in StudentPortalCommon.

StudentPortalCLI.hs

User interface module, option 1. This module implements the same type of command-line interface that is implemented in Java in StudentPortal.java.

StudentPortal3p.hs

User interface module, option 2. This module implements a simple web interface, using threepenny-gui, which you need to install, e.g. by running cabal install threepenny-gui.

The two user interfaces modules both export a function called studentPortal, which is called from the main function, so you switch between the two user interfaces simply by changing which user interface module you import. No other changes in the code should be needed.

HDBC

An introduction to HDBC can be found in the book Real World Haskell, Chapter 21. Using Databases. Here we just give a brief summary of what you need to know.

As you can see in the main module StudentPortal.hs, to access your PostgreSQL database from Haskell using HDBC, you need to import two modules that come from two packages:

• Database.HDBC that comes from the package HDBC and contains functions that work with several different SQL databases. You probably only need to use a few functions from this module: run, quickQuery', withTransaction, fromSql, toSql, handleSqlError.
• Database.HDBC.PostgreSQL that comes from the package HDBC-postgresql and contains functions to connect to a PostgreSQL database. You probably only need to use one function form this module: connectPostgreSQL.

You can install both packages at once e.g. by running cabal install HDBC-postgresql.

SQL values and rows

With HDBC, all the values that you retrieve from or send to the database have the Haskell type SqlValue. For example, the function quickQuery' that sends an SQL query to the database and retrieves the result has type:

  quickQuery' :: Connection -> String -> [SqlValue] -> IO [[SqlValue]]

HDBC provides the overloaded functions toSql and fromSql that convert between SqlValue and normal Haskell types, like String and Int. You can also convert from SqlValue to e.g. Maybe String, if you are retrieving a string that might be NULL.

The module SqlRows mentioned above provides functions to convert whole rows of SQL values at once, which allows you to write functions like the following:

  getContinent :: Connection -> String -> IO [(String,Int)]
  getContinent conn continent =
      fromRowsM =<< quickQuery' query [toSql continent]
    where
      query = "SELECT name,population FROM Countries WHERE continent=?"

Here, the rows returned by quickQuery' are converted from [[SqlValue]] to the type [(String,Int)]. For correctly constructed queries the conversion should succeed, but in general it might fail, e.g. if the rows in the query result don't contain the expected number of values (two in this example) or if the individual values in the rows can not be converted to the expected Haskell types (String and Int in this example).

The function fromRowsM and fromRowM are monadic and raise an error in the IO monad if the conversion fails. Errors in the IO monad can be caught using functions from the library module System.IO.Error. There are also pure variants fromRows and fromRow that call error when conversion fails.

  fromRowM  :: (FromRow a,Monad m) => Row -> m a
  fromRowsM :: (FromRow a,Monad m) => Rows -> m [a]

  fromRow   :: FromRow a => Row -> a
  fromRows  :: FromRow a => Rows -> [a]