[Larceny-users] sqlite3 FFI

[David Rush]

On Sun, Jun 1, 2008 at 12:07 AM, Peter Keller <psilord at cs.wisc.edu> wrote:
> 2. While I have a usable chunk of the sqlite3 interface done, should I
> bother making statement level or higher abstractions, or just let that
> be made by the people using the library?

If you are programming a lot in Scheme, you will end up making those
higher-level abstractions almost as a matter of breathing. The system
I posted a few days ago for MySQL has several of them which are based
around my own analysis of the question of how a DB engine should be
related to fairly flat, transparent data structures such as Scheme
provides. I realize that given the lack of documentation, that they
would have been entirely invisible except to extremely interested
users.

The main interface is implemented from the section titled
'Query-Macros', however, there are a few important functions that
haven't yet been fully abstracted from the MySQL layer. The MySQL
leftovers get used in the 15% of my code that doesn't fit the usage
patterns covered by the main macros. I also have a deep and enduring
attraction to CPS programming, especially when it comes to dealing
with external systems (such as databases). All that said, I'm sure
that there have been several DB encapsulations built in the Scheme
world, but I've never really heard of any outside the PLT
implementation (I' have to bet that Chicken has one as well, though).

My API (a summary):

(define-cached-query name ...)

A macro that wraps up the machinery for grabbing the rows from a DB
based and keeping them in a keyed hashtable.

(define-cached-value (accessor-name . args) ...)

A macro that grabs values from a DB and stores them in a
user-specified data structure (mostly used for computing dependent
values in otherwise-persisted data structures)

(mysql-query-k cx query k-success k-failure)

This is the main 'workhorse' function which executes an arbitrary
query and returns the relevant information to the program to carry on
processing. <k-success> and <k-failure> are both called in tail
position to make it easy to use this as both a conditional and as a
binding form. There are a few pre-packaged kontinuations which can be
used to fit this into non-CPS code, as well.

(mysql-insert-k cx table-name field-alist obj k-success k-failure)

A relatively high-level function abstraction which generates a SQL
insert statement from the field conversions specified in <field-alist>
and the given object <obj>. There should probably be a macrotic
wrapping for this function, but the textual overhead associated with
it is not all that large.

(mysql-update-k cx table-name key-alist key field-alist obj k-success k-failure)

This is the same as MYSQL-INSERT-K except for SQL updates. It's a
really ugly interface and the comments in the source code even say
that, but then again the fundamental semantics of a DB update are a
little bit icky as well. I imagine that I will eventually come up with
a better abstraction here, but I also think it will involve a deeper
binding of Scheme data values and the notion of persistent data.

(mysql-row->alist row)

Builds a Scheme alist out of a returned DB row structure. Very useful
when you don't want to build a data structure around an ad-hoc query.

(mysql-convert-field row name string->datum . default)

Converts a column from MySQL's internal format into a Scheme datum.
The default value is used when MySQL indicates that the column had a
NULL value.

(mysql-construct row kons fields)

Converts a MySQL <row> into whatever object type <kons> returns when
it is applied to the list resulting from converting all the <fields>.
The <fields> list is a list of lists which contain the final 3
arguments to MYSQL-CONVERT-FIELD.

And that's the highlights of the interface folks. Eventually, I would
hope that everyone's interfaces to relational DBs might all settle
down enough to write a SRFI for it, but somehow I doubt that anything
resembling a community consensus even exists. For my own work, I do
plan to eventually abstract away from MySQL, but so far I haven't had
the need to use anything else (all the client-server systems I've been
working with lately have been built around MySQL), although I suspect
that I may wrap up an ODBC binding fairly soon. When that happens, I
fully expect to finish abstracting away from the MYSQL-* functions to
something a little less dependent on the specific patterns inflicted
by the MySQL API.

david rush
-- 
GPG Public key at http://cyber-rush.org/drr/gpg-public-key.txt