Database interface - would like advice on oracle library binding

oleg at oleg at
Tue Sep 23 22:51:08 EDT 2003

> 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?

I'd like to remark first that many real databases let us avoid opening
many cursors at once. It seems to be more efficient to do as much as
possible within the engine -- using the full power of SQL or Stored
Procedures -- and relay only the final results through the
boundary. We can use SELECT ... UNION, correlated subqueries, SELECT
rowid, self (outer) joins, etc. There are cases where SQL is powerless
-- a transitive closure comes to mind. Stored procedures may sometimes
help. A stored procedure may return a 'row' and thus can be used as a
"generator" in Icon or Ruby's sense of the word.

Still, opening several cursors may be unavoidable. The left fold
approach may still help -- we _can_ mechanically invert a left fold
combinator (to be precise, a half-baked version of it) into a lazy
list. Please see a separate message "how to invert the left fold"

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

I believe the extract functions can be constructed automatically --
similar to the way Quickcheck constructs test cases. I believe that
instead of

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

we can do

    results <- doQuery dbconn sqltext [] process
        process:: String -> String -> [(String,String)] -> [(String,String)]
	process name address results = (name,address):results

doQuery should be able to figure our that the function 'process' takes
two arguments of type string. Therefore, doQuery needs to extract two
columns from the current row, both of type string. Note, 'process' is
a pure function. There is no reason for it to be monadic (if it
doesn't have to be). Because doQuery is a left-fold iterator, it can
keep reusing buffers until the iteration finishes. We can keep the
pointers to column buffers in a polytipic list, and we can keep the
corresponding deallocation actions in a list of IO (). When we prepare
a statement, we create both lists. When we finish doQuery, we scan the
list of destructor actions and execute them to free all the
buffers. It's hard for me to write the corresponding code because I
don't have Oracle handy (besides, I like Informix better). Is it
possible to come up with a "stub" that uses flat files? We are only
interested in fetching rows. It doesn't matter if these rows come from
a file or from a database. That would make prototyping the interface 
quite easier.

More information about the Haskell-Cafe mailing list