HaskellDB - Getting Started

This document explains how to install HaskellDB, set up a test table, generate a database description from it and run a simple HaskellDB program.

Installing prerequisites

Database engine

You will need at least one supported database engine:

PostgreSQL, any relatively recent version should work.
MySQL, version 4.1 or greater, since earlier versions do not support nested queries.
SQLite, any relatively recent version should work.
Some other database system with an ODBC interface.

Compiler

We will assume that you have GHC 6.2.1 installed. GHC 6.2.1 is required to build HaskellDB, even if you will be using Hugs to run your programs. This requirement will most likely be dropped in the future.

HSQL

Download HSQL 1.4 from the HToolkit download page.
Unpack it
Configure (enable only the drivers that you have the client headers and libaries for):
```
$ ./configure --enable-odbc --enable-mysql --enable-postgres
```
Compile:
```
$ make
```
Install:
```
$ make install
```

Installing HaskellDB

Download HaskellDB from the HaskellDB SourceForge files page.
Unpack it
Configure:
```
$ ./configure
```
Compile:
```
$ make
```
Install:
```
$ make install
```

Creating a database table for testing

The table, test_tb1, that we will use for this example has the following layout:

Field	Type
c11	INT NOT NULL
c12	INT

With the latest version of HaskellDB, the table can be created from a small HaskellDB program. An example of such a program is included in the release (test/dbspec.hs). However, you will need to edit it to create the table we need for this example.

The changes needed are the following:

test - This function should look like this:
```
 test = DBInfo {dbname = "ctest", opts = testopts, tbls = [testtbl1]} 
```
where ctest is the name of the database you use.

testtbl1 - This function should look like this:

 testtbl1 = TInfo {tname = "test_tb1", cols = [testcol11,testcol12]}

testcol11 - And this function like this:

testcol11 = CInfo {cname = "c11", descr = (IntT,False)}

testcol12 - And finally this function like this:

testcol12 = CInfo {cname = "c12", descr = (IntT,True)}

testtbl2, testcol21, testcol22 - These functions are not needed for this example and can be removed.

If you are using GHC, compile the program with:

$ ghc --make -package haskelldb -o dbspec dbspec.hs

and run it:

./dbspec PostgreSQL localhost test_db bob secret

where the arguments supplied are the same as the ones later used by DBDirect to generate the database description.

If you are using Hugs, run the program with:

$ runhugs -98 +o dbspec.hs PostgreSQL localhost test_db bob secret

If you for some reason don't want to use the method described above, create the database with your favorite database client program instead using this query:

CREATE TABLE test_tb1 (c11 INT NOT NULL, c12 INT NULL);

Database descriptions

In order for HaskellDB to be able to create queries against a database, the tables in the database must be described by Haskell modules which contain information about the names and types of the fields.

HaskellDB includes a program called DBDirect which generates such database description modules by connecting to the database and retrieving database meta-data.

Note: Currently, DBDirect is not installed when make install is run, but it is compiled and should be available in the build/ghc subdirectory after running make. So when DBDirect is used below, just replace that by <path to HaskellDB dir>/build/ghc/DBDirect.

DBDirect takes the following command line options:

DBDirect <driver> <driver options> <module name>

<driver> = "ODBC", "MySQL" or "PostgreSQL"

ODBC: <driver options> = dsn userid password
MySQL: <driver options> = server database userid password
PostgreSQL: <driver options> = server database userid password

<module name> = The base name of the database description modules. If this is "Test" and there are tables "test_tb1" and "test_tb2" in the database that you run DBDirect against, the following files will be created in the current directory:

Test.hs (just imports and re-exports all the other modules)
Test/Test_tb1.hs
Test/Test_tb2.hs

Generating a database description for the test table

Run DBDirect against the database where you created test_tb1 with a <module name> of "Dp037" (just happens to be the name that the test program below uses). For example, if you are using a PostgreSQL database on the local machine, you created test_tb1 in the database "test_db", your username is "bob" and your password is "secret", you would run:

$ DBDirect PostgreSQL localhost test_db bob secret Dp037

Check that you have the files:

Dp037.hs
Dp037/Test_tb1.hs

Setting up the test program

The test program that we will use is test/insert-update-delete.hs from the HaskellDB source distribution.

Compiling and running the test program

If you are using GHC, compile the program with:

$ ghc --make -package haskelldb -o insert-update-delete insert-update-delete.hs

and run it with

./insert-update-delete PostgreSQL localhost test_db bob secret

where the arguments are the same as used when running DBDirect and dbspec

If you are using Hugs, run the program with:

$ runhugs -98 +o insert-update-delete.hs PostgreSQL localhost test_db bob secret

If everything works, the program should produce this output:

Before insert:
After insert:
      -567      NULL
       157        56
After update:
      -567      NULL
       157        18
After delete:

Writing your own HaskellDB programs

The AvianWiki HaskellDB tutorial has some nice examples of HaskellDB queries. Contributed by Shae Erisson.