[MLton] SQLite3 MLton binding

[Wesley W. Terpstra]

The reason I've been mucking around with Fold and friends is for an  
SQL interface for MLton. You can find it here:
	svn+ssh://mlton.org/svnroot/mltonlib/trunk/ca/terpstra/sqlite3

In case you're not familiar with it, SQLite3 is a serverless SQL  
engine. It weighs about 170kB (can be statically linked) and stores  
all of the database into a single file. It's relatively fast, and (I  
think) it's ideal for end-user applications. You don't need to  
configure it, it doesn't need a server, and it doesn't have external  
dependencies. Find out more here:
	http://www.sqlite.org/

At any rate, I've set out to bind it to SML. At the moment, it works  
like this:

local
   open SQL.Template
in
   (* Templates that can be instantiated to create a query *)
   val T1: SQL.db -> string -> int -> (string -> int -> real -> XX,  
XX) SQL.query
             = query "select x, y, z from mytable where x="iS" and  
y="iI";" oS oI oR $
end

val db = SQL.openDB "mydatabase"

val Q1 = T1 db "hiho" 6

fun f s i r = XX
val x = SQL.map f Q1
fun g s i r = XX
val y = SQL.map f Q1
val () = SQL.close Q1

val () = SQL.closeDB db

As you can see from the example code, in the program prelude you list  
out all the templates for queries you might want to execute. Later,  
you pass them a database and the arguments required, to get a query.  
You can then iterate over the query (and cancel it if you found  
enough data) and map the query to create a vector.

When creating a template, you put i{B,R,I,Z,S,X} in the string to  
accept a Word8Vector.vector, real, int, Int64.int, string, or  
"storage" in the function created. These are then passed to SQLite3  
via the C interface methods. "storage" is simply a "type-less" entry  
I expose as a datatype. These correspond to blobs, doubles, ints,  
int64, and text in SQLite3 terminology.

At the end of a template, you put the output column types. o 
{B,R,I,Z,S,X} as before. These are converted by SQLite3 before being  
imported into the GC'd memory of MLton. If you don't know the number  
of columns to expect---for example you executed a user's query---then  
use oA{B,R,I,Z,S,X} which will bind a parameter vector of the  
appropriate type. eg:
   val T1 : SQL.db -> (string vector -> XX, XX) SQL.query
               = query user_query_string oAS $

Where I'd like to go from here:

I don't like the XX return type. If Fold and friends could generate  
polymorphic methods, that would be great. As it is, you have to  
specify the XX in the type binding. This makes the 'map' function  
something of a lie. I've been considering Stephan's Fold01N to create  
a tuple as a return value instead of calling a function. Then it  
would be possible to support map properly (and also app). It would  
also make iteration more convenient. However,

1. SQLite3 is very fast, and I'd rather not make it slower. If I use  
the (x & y & z) idiom, how much will it cost me in speed? The old  
Printf page mentioned that MLton optimizes all of this trickery away;  
which intermediate language do I look at to confirm this?

2. The type signature for (x & y & z & ...) quickly gets hideous. I'd  
rather spare users from seeing this.

The other thing I can't decide is whether or not it makes sense to  
reuse a query. The original SQLite3 interface allows you to 'reset' a  
query after it completes, so that you can use it again. This is what  
I currently expose in the SML binding. However, I think that an  
'autoclose' behaviour might be more convenient. According to the  
manual, reusing queries can be much faster than reparsing the SQL.