Designing a Haskell database interface

[Bayley, Alistair]

I'm making slow progress on an Oracle OCI binding. I've got the noddy
session setup and database connection stuff working, so now I'm looking at
how results should be returned from SQL queries. In Haskell, there doesn't
seem to be any consistent way of returning results from SQL queries, unlike
(say) Java's JDBC interface. It may be a bit early to propose a standard SQL
dbms interface design, but... does anyone have any idea(s) about how one
ought to look?

Here's a simple survey of the sql dbms interfaces I've come across so far (I
found HaskellDB the most complex, and difficult to understand). My
assumptions about how these various libraries work might be quite wrong:


HaskellDB:
  dbQuery returns IO [row r],  where
   - dbQuery is implemented by adoQuery (this library uses Odbc):
       adoQuery :: IConnection () -> PrimQuery -> Rel r -> IO [AdoRow r]
   - "AdoRow a" is an instance of class Row
   - "class Row row a" declares one function: rowSelect
       rowSelect :: Attr r a -> row r -> a
   - type AdoRow implements rowSelect with adoRowSelect:
       adoRowSelect :: Variant a => Attr r a -> AdoRow r -> a
   - Attr has one constructor: Attr Attribute, and Attribute is just a
synonym for String.
  So (I think) the return types are determined by the types of the phantom
types in "Attr r a" and "AdoRow r". And this is where my head explodes...


LibPQ:
  fetchAllRows returns tuple pair of the connection and row :  (DBI a,
[[String]])
   - so, a row is a list of list of Strings.


HaSql:
  haSQLObtainQueryResults returns SQL [a], where 
   - SQL is some custom IO+State Monad
   - the type of a is the return type of a function you pass to
haSQLObtainQueryResults to (I think) convert an Odbc pointer into a Haskell
type.


MySql-hs:
  mysqlQuery returns a tuple of (Integral, [[Maybe String]], [MysqlField]),
where
   - the first element (Integral type) is the number of rows
   - the second element is a list of list of Strings - the result set.
   - the third element is metadata. MysqlField is a record describing a
database column.


The most sophisticated implementation wrt type information seems to be
HaskellDB. My initial goal was to use this library and provide an Oracle
database driver for it, but the HaskellDB seems to be quite dependent on
Trex, which AFAICT is a Hugs library.

I was thinking of splitting it into two parts: a library that submits SQL
queries and returns the results, and a library that constructs queries with
the relational calculus and generates SQL from them. The relational calculus
bit was what I was interested in, but for now I want to work on getting data
out of my Oracle database.


Also...

I was wondering how you might go about mapping arbitrary dbms types to a
Haskell result set.

In an ideal world, you can store any values you like in a "relational"
database. However, most SQL dbms products give you just numbers, text, and
dates. Some dbms's (PostgreSql and Oracle) allow users/programmers to create
new types and let the dbms treat them in the same manner as the built-in
ones i.e. with equality and ordering predicates, and indexing. The built-in
support for the three basic types (numbers, text, dates) is reflected in the
JDBC API; it has methods like: getByte, getShort, getFloat, getDouble,
getBigDecimal, getInt, getString, getDate, getTime, getBoolean, while other
types are handled by methods like getBinaryStream and getObject.

So how would you convert a (say) PostgreSql Point or Box value to a Haskell
type? Would the approach taken by HaSql be the best (the user provides a
function that converts binary data into a Haskell value)?


*****************************************************************
The information in this email and in any attachments is 
confidential and intended solely for the attention and use 
of the named addressee(s). This information may be 
subject to legal professional or other privilege or may 
otherwise be protected by work product immunity or other 
legal rules.  It must not be disclosed to any person without 
our authority.

If you are not the intended recipient, or a person 
responsible for delivering it to the intended recipient, you 
are not authorised to and must not disclose, copy, 
distribute, or retain this message or any part of it.
*****************************************************************