Database interface

oleg@pobox.com oleg@pobox.com
Wed, 13 Aug 2003 19:54:36 -0700 (PDT)


> I'll probably generalise the query function to do a fold, rathen than
> always accumulate a list:
>         doquery :: Process -> String -> (a -> b  -> IO b) -> b -> IO b

If I may interject, that's precisely how a Scheme DB interface is
designed. The main function is a left-fold. Not quite though: it
provides for a premature termination:

A major procedure: DB1:fold-left PROC INITIAL-SEED QUERY-OBJECT

A QUERY-OBJECT (which in this implementation is a list of fragments
that make a SQL statement, in the reverse order -- without the
terminating semi-colon) is submitted to the database, using the
default database connection.

PROC is a procedure: SEED COL COL ...
The procedure PROC takes 1+n arguments where n is the number of
columns in the the table returned by the query.  The procedure PROC
must return two values: CONTINUE? NEW-SEED

The query is executed, and the PROC is applied to each returned row in
order. The first invocation of PROC receives INITIAL-SEED as its first
argument. Each following invocation of PROC receives as the first
argument the NEW-SEED result of the previous invocation of PROC.  The
CONTINUE? result of PROC is an early termination flag. If that flag is
returned as #f, any further applications of PROC are skipped and
DB1:fold-left finishes.  The function DB1:fold-left returns NEW-SEED
produced by the last invocation of PROC. If the query yielded no rows,
DB1:fold-left returns the INITIAL-SEED.

Thus DB1:fold-left is identical to the left fold over a sequence,
modulo the early termination.

There are a few minor variants of the above procedure, optimized for
common particular cases: a query that is expected to return at most
one row, and a query that expects to return at most one row with
exactly one column. The latter query has the same interface as a
lookup in a finite map.

The QUERY-OBJECT is of coursed not built by hand. There is a
domain-specific language (which greatly resembles the source language
and makes some use of quasi-quotation) whose result is a query
object. Therefore, I can write both the query and the handlers of the
query result using the same syntax.

The premature termination is important. Database connections and
cursors are too precious resources to leave them for the garbage
collector to finalize. More discussion and pointers can be found at

	http://srfi.schemers.org/srfi-44/mail-archive/msg00023.html

The interface has been used in an industrial application.