Haskell snippet; CRUD operations with haskell hsql and hsql-sqlite3

The source listing below is not complicated, showing a basic create, read unit test (minus the update/delete) against a simple sqlite3 database. You may have some trouble setting up hsql, especially because it seems that the module is not being maintained. The code is still useful and viable, but for now, you are going to have issues building the module. The build failure will probably be resolved pretty soon as I see updates to that particular code.

Ensure that you are running the latest ghc. Tested with ghc 6.8.2

Download hsql-1.7 (or greater)
http://hackage.haskell.org/packages/archive/hsql/1.7/hsql-1.7.tar.gz

Change the hsql.cabal to what is shown in the listing; Rank2Types, DeriveDataTypeable extensions were added. This will not work with previous versions of ghc (at least I had to build it on 6.8.2).

name:  hsql
version: 1.7
license: BSD3
author:  Krasimir Angelov 
category: Database
description: Simple library for database access from Haskell.
exposed-modules:
 Database.HSQL,
 Database.HSQL.Types
build-depends: base, old-time
extensions:     ForeignFunctionInterface, 
TypeSynonymInstances, CPP, Rank2Types, DeriveDataTypeable

Do the runhaskell Setup.lhs configure (then build and then install).

Get the latest version of hsql-sqlite3:

http://hackage.haskell.org/cgi-bin/hackage-scripts/package/hsql-sqlite3-1.7

hsql-sqlite3 also has a cabal script that won't run with the current version of ghc and cabal; I don't know if this is the definite solution to resolve the build errors, but making these changes resolved the build failures.

Add the following to hsql-sqlite3.cabal
extra-libraries: sqlite3

There was a lot of script config code in Setup.lhs, I removed all of it and just added these five lines

Setup.lhs:

#!/usr/bin/runghc

\begin{code}
import Distribution.Simple
main = defaultMain
\end{code}

I then ran runhaskell configure (build, install).

The example

The haskell source listing shows the code for creating the filesystem sqlite3 database called simple.db and the other create/read hsql operations. (As a prerequisite, create the tmp directory in the current working path)

module Tests.Data.TestBasicHSQL where

import IO
import Database.HSQL as Hsql
import Database.HSQL.SQLite3 as Hsql

simpleDB = "tmp/simple.db"

sqlCreate = "create table if not exists simpletable(mydata)"
sqlInsert = "insert into simpletable values('dogs and cats')"
sqlSelect = "select mydata from simpletable"

--
-- Get Rows routine from David at davblog48
getRows :: Statement -> IO [[String]]
getRows stmt = do
  let fieldtypes = map (\(a,b,c) -> a) $ getFieldsTypes stmt
  rowdata <- collectRows (\s -> mapM (getFieldValue s) fieldtypes ) stmt
  return rowdata

runTestBasicHSQL = do
  putStrLn "Test HSQL"
  tryconn <- try $ Hsql.connect simpleDB ReadWriteMode
  conn <- case tryconn of
            Left _ -> error "Invalid Database Path"
            Right conn -> return conn
  
  -- Run a simple create query
  stmt <- Hsql.query conn sqlCreate
  Hsql.closeStatement stmt
  stmt <- Hsql.query conn sqlInsert
  Hsql.closeStatement stmt
  stmt <- Hsql.query conn sqlSelect
  rows <- getRows stmt
  putStrLn $ "Length rows=" ++ show (length rows)
  mapM_ (\val -> putStrLn $ show val) rows
  Hsql.closeStatement stmt
  Hsql.disconnect conn
-- End of File