[Haskell-cafe] Haskell DB bindings

[oleg at pobox.com]

[moved from the main Haskell-list: I'm afraid the HDB discussion on
the Haskell mailing list had already been long]

Tim Docker wrote on the Haskell mailing list

> The differences between HDBC and HSQL have been recently discussed.
> Where does Takusen fit into this picture? From the above, it sounds
> like it has quite a different API. Are all 3 of these actively
> maintained?

Takusen is built around an enumerator -- left fold enumerator, to be
precise. That is, a database query is considered to be `left-folding'
over the result table. Early termination is supported -- the iteration
can be finished at any time the iteratee decided it has had
enough. Cursors -- derivatives (pun intended) of the enumerator -- are
supported as well, and we have a withCursor function (although, due to
laziness, we haven't implemented the type-based region management for
cursors. But we have talked about it!)

The best supported databases are Oracle and Sqlite. For them, we
implement buffer pre-allocation, pre-fetching (useful for minimizing
the communication overhead if rows are small), parameter binding,
handling date-time datatypes.  Parameter binding is not yet supported
for PostgreSQL.

All interfaces support DML/DDL statements, transactions and isolation
modes (if the back-end supports that), transparent translation from
the DB type to a Haskell type. The user doesn't need to do anything
for the translation. Database NULLs are fully handled: if the Haskell
datatype is a Maybe datatype, NULL is permitted and translated into
Nothing. Otherwise, NULL raises an exception. Nulls are distinguished 
from empty strings, provided the database server does so.

Takusen has two layers. The low-level API (which is not actually meant
for the end user) reconciles the differences among various DB
interfaces. Currently supported are Oracle, Sqlite, PostgreSQL, Stub
(that is, no DB -- useful for testing), and the ntwdblib interface to
MSSqlServer (Microsoft encourages a newer interface though).

Krasimir Angelov wrote:
> Can you explain how do you manage the lifetime of handles in Takusen?
> It isn't clear to me where are the advantages of enumerators.

The handles are not visible to the user. The user cannot close the
handle (or forget to close the handle) because the user is not given
the handle in the first place. Just as we left-fold over a list, we
are not given the pointer to the ``current list''. We are merely given
the current element of the list. Perhaps the following links might be
of help:

 http://www.eros-os.org/pipermail/e-lang/2004-March/009643.html
 http://pobox.com/~oleg/ftp/Haskell/misc.html#fold-stream
 http://pobox.com/~oleg/ftp/papers/LL3-collections-enumerators.txt