Sunday, February 3, 2008

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

1 comment:

Michael Roger said...

This is great. I added a link from http://haskell.org/haskellwiki/Applications_and_libraries/Database_interfaces , which I cleaned up today to separate the dead libraries form the live ones.

If you wouldn't mind, please copy your advice there (or give permission for someone like me to copy it).