>     (sql-prepare    sql-string . params) -> statement
>
> I'd rather let the implementation do the prepared-statement caching, and
> put string . params into the below examples, so (sql-do database string
> . params).  It bumps up the ease of use a lot, and you have only one
> opaque object to keep track of, the database connection.

Caching is not really an issue since the implementation hopefully should
not build an escaped SQL string out of sql-string and params. The
wrapper object would just keep sql-string and params in one object, to
be sent to DB engine later as separate objects.

Maybe we should just use (cons sql-string params) in which case we don't
need sql-prepare.

Using (sql-do-something database sql-string . params) is nice until you
start adding more arguments to sql-do-something. It's nice for the
mapfun and accumulator arguments to be optional, which means they have
to be at the end. But it's also nice for params to be optional. The
arglist gets a bit hard to read.

You'd have to pass params as one argument (which we'd probably have as
an alist) instead of a rest argument which is a plist. And you'd have to
pass '() every time you want to apply a custom mapfun or accumulator
with no params:

(sql-get-all database "select a,b,c from foo" '() list)

>     (sql-do         database statement) -> #f
>
> Unspecified value, I think.

Yes.

>     (sql-do/row-id  database statement) -> integer
>
>     (sql-get-one  database statement [mapfun]) -> result
>     (sql-get-all  database statement [mapfun accumulator]) -> state
>
>     ;; sql-query-one expects exactly one row back and does (apply mapfun
>     ;; row). The mapfun may return multiple values, which are returned
>     ;; from sql-query-one. It raises an exception if it gets no rows, or if
>     ;; it gets more than one row.
>
> The only thing I don't like about this is that it's column-positional,
> but I suppose we are stuck with that, since some queries result in
> columns named things like "(a+b)/c".

You can name those columns with "select (a+b)/c as foo", but there may
be some situations where that syntax doesn't work.

> Is it worth having a special case for "one row, one column" that gives
> you a scalar directly?

If we use (sql-get-one db "select count(*) from foo" values) it gives us
a scalar because we wrote a select that only gives one column. And
values is the default, so that can be shortened to (sql-get-one db
"select count(*) from foo").

The idea of feeding each row to a mapfun is straight from Bigloo's
sqlite module which you linked. I think it's brilliant because it makes
the one-column case and the multi-column case have the same calling
convention. Just (lambda (a) ...) vs (lambda (a b c) ...). If they were
passed to a list or vector instead of a lambda, it would be awkward for
users to unpack the one-column case manually.

And thanks to `values`, the one-row and multi-row cases can also have
the same calling convention. Multi-row just adds the accumulator and
uses `vector` instead of `values` as the default mapfun. It would be
nice if Scheme came with a standard `identity` function for (lambda (x)
x) which we could use to accumulate single columns from multiple rows.

All of this relies on either:

1) real multiple values in the Scheme implementation
2) a `values` implementation where the single-value case (values a)
    returns `a` with no wrapper object.

>     ;; Each return value
>     ;; is fed to the accumulator. Finally the accumulator is fed
>     ;; (eof-object) and its final state is returned from sql-query-all.
>
> I love this use of an accumulator!

Me too! It's basically your idea. Thanks for advocating for generators
and accumulators on the lists.