Database interface - would like advice on oracle library binding

[Bayley, Alistair]

(2nd attempt; mailman thinks I'm not a list member, but it still keeps
sending me mail.)

Still making slow progress on an Oracle database binding... now I'm trying
to fit the API I have into some sort of abstract interface (like the one(s)
discussed previously:
 http://haskell.org/pipermail/haskell-cafe/2003-August/004957.html ).


1. Is the left-fold the best/only interface to expose? I think yes, but that
doesn't allow fine-grained control over cursors i.e. being able to open many
cursors at once and interleave fetches from them. Or does it?


2. I'm finding it hard to write a doQuery function that takes an extraction
function that isn't a pig to write. Some advice would be useful here... (and
a long-ish explanation follows):

The Oracle Call Interface (OCI) requires that I allocate buffers for the
result of a single row fetch, before the first row is fetched. So a query
involves:
 - prepare statement etc
 - allocate buffers (one for each column - call OCI C function
"DefineByPos")
 - fetch row
 - extract/marshal data from buffer into Haskell types (which are then
processed by fold function)
 - fetch, marshal (repeat until no more rows)
 - free buffers

i.e. the same buffers are re-used for each row.

The problem for me is how to specify the left-fold function in terms of the
low-level API. If I want to supply extraction functions (to extract Haskell
values from result buffer), how do I manage the buffer allocation in the
doQuery function? The buffer allocate/free code also needs to know the
column positions and types, in the same manner as the extract functions.

I want to be able to write code like this:

    results <- doQuery dbconn sqltext [] \row results -> do
        name    <- stringv row 1
        address <- stringv row 2
        return (name,address):results

.. where the stringv function extracts/marshals a Haskell String from the
result buffer.

The intermediate approach I currently have means I have to pass an IO action
into the doQuery function that, when evaluated, allocates the buffer and
returns two more actions:
 - an action that extracts the row as a tuple
 - another action that frees the buffer

The doQuery function evaluates the initial action (to allocate the buffer),
uses the extract action to build the result (at present a list), and when
there are no more rows, uses the free action to free the buffer.

This approach is quite awkward (especially w.r.t. writing extract
functions), and it's hard for me to see how to build a better interface.
Hard, because of the memory management requirements.


--------------------
Here's a chunk of the code. A lot of it is OCI plumbing, but I hope you can
see how awkward it is to create an extract function (see ex3 at the bottom).

Given pointers to the buffer, extract a string of variable length (you have
to terminate it yourself).

> fetchStringVal :: OCIColInfo -> IO String
> fetchStringVal (_, bufptr, nullindptr, retsizeptr) = do
>   retsize <- liftM cShort2Int (peek retsizeptr)
>   nullind <- liftM cShort2Int (peek nullindptr) -- unused
>   pokeByteOff (castPtr bufptr) retsize nullByte
>   val <- peekCString (castPtr bufptr)
>   return val

Free a single column's buffer.

> freeColBuffer :: OCIColInfo -> IO ()
> freeColBuffer (_, bufptr, nullindptr, retsizeptr) = do
>   free bufptr
>   free retsizeptr
>   free nullindptr

Create a buffer for a string column, and return the extract and free IO
actions.

> getExtractFnString :: Int -> ErrorHandle -> StmtHandle -> IO (IO String,
IO ())
> getExtractFnString posn err stmt = do
>   c <- defineCol err stmt posn 2000 oci_SQLT_CHR
>   return ((fetchStringVal c), (freeColBuffer c))


doQuery uses the extractFns action to create the result buffer,
and the two actions (extract and free) which are passed to doQuery2.

> doQuery2 :: ErrorHandle -> StmtHandle -> IO a -> IO () -> [a] -> IO [a]
> doQuery2 err stmt extractData freeMem results = do
>   rc <- fetch err stmt
>   if rc == oci_NO_DATA
>     then do
>       freeMem
>       return results
>     else do
>       v <- extractData
>       doQuery2 err stmt extractData freeMem (v:results)

> doQuery :: Session -> String -> (ErrorHandle -> StmtHandle -> IO (IO a, IO
())) -> IO [a]
> doQuery (Sess env err con) qry extractFns = do
>   stmt <- getStmt env err
>   prepare err stmt qry
>   execute err con stmt
>   (extractData, freeMem) <- extractFns err stmt
>   doQuery2 err stmt extractData freeMem []


The interface provided by doQuery means I have to write extract functions
like this.
Here's one for a select that returns three String columns.
It's quite awkward...

> ex3 :: ErrorHandle -> StmtHandle -> IO (IO (String, String, String), IO
())
> ex3 err stmt = do
>   (fetchcol1, freecol1) <- getExtractFnString 1 err stmt
>   (fetchcol2, freecol2) <- getExtractFnString 2 err stmt
>   (fetchcol3, freecol3) <- getExtractFnString 3 err stmt
>   return
>     ( do { s1 <- fetchcol1; s2 <- fetchcol2; s3 <- fetchcol3; return (s1,
s2, s3) }
>     , do { freecol1; freecol2; freecol3 }
>     )


*****************************************************************
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.
*****************************************************************