On Thu, Sep 12, 2019 at 12:36:54PM +0300, Lassi Kortela wrote:
> > > There's also Perl DBI, which is reimplemented(?) in Gauche as "dbi"
> > > module.
> >
> > <https://practical-scheme.net/wiliki/wiliki.cgi?Gauche%3ADBI%2FDBD>
>
> Here's the English manual: <http://practical-scheme.net/gauche/man/gauche-refe/Database-independent-access-layer.html#Database-independent-access-layer>
>
> Writing DBI drivers: <http://practical-scheme.net/gauche/man/gauche-refe/Database-independent-access-layer.html#Writing-drivers-for-DBI>
>
> Does DBI have transactions? Those are mandatory for any job where we can
> have more than one concurrent user.

I don't think that's something that necessarily needs to exist in dbi.
You can do "BEGIN;" and "ROLLBACK;" or "COMMIT;" manually.  For those
databases that support savepoints you can translate nested calls to them.

The postgres library in CHICKEN transparently does this, so it's a nice
thing to have, as you don't need to manually keep track of the
transaction depth in your own code.  On the other hand, you can't set the
isolation level of the transaction in a nested transaction (and if you
try with the postgres egg, it'll raise an exception).

The real question is: what level of convenience should this library offer?

On Thu, Sep 12, 2019 at 12:32:15PM +0300, Lassi Kortela wrote:
> > > I don't know anything about ODBC
> >
> > I dunno, from what I've seen it looks overengineered enterprise bullshit,
> > and old-fashioned as well
>
> You may be right. Here's the current/upcoming C API:
> <https://github.com/microsoft/ODBC-Specification/tree/master/Windows/inc>.
> It doesn't seem terrible by any means, but it's a C API and there's quite a
> lot of stuff.

It's huge and low-level (as a C API typically is).  Might be useful to
compare with to see if it's "complete".  But how complete do you need
to be?

> > > There's also Perl DBI, which is reimplemented(?) in Gauche as "dbi" module.
>
> <https://practical-scheme.net/wiliki/wiliki.cgi?Gauche%3ADBI%2FDBD>
>
> The interface looks reasonable. Not sure how extensible it is.

Via inheritance and multimethods it should be pretty much infinitely
extensible.

> > The DBI interface in Gauche seems useful, but it does sort of rely on
> > having an inheritance hierarchy.  At least, if you want to provide
> > non-standard extensions that would be done in the database-specific
> > subclass.
>
> I don't think inheritance is necessary; a vtable (virtual method table) is
> enough. That can be done using a hashtable, a record or even a vector.

That's effectively rolling our own multimethod implementation.  Works for
me, but it's also a bit kludgy if more SRFIs need the same thing.

> Though for many applications it's nice to be able to stick to ANSI SQL as
> much as possible for maximum portability and have some comforts for dealing
> with it. I still miss a DB interface that would offer a wrapper to do the
> usual upsert (update or insert) dance using only ANSI features.

Technically, ANSI has a standard way of upsert (at least, according to
the Wikipedia page I found, there's a MERGE keyword that I didn't know
about before), but many databases don't support it.

But I think that an SQL dialect abstraction might be too broad of a scope
for this SRFI.  We should stick to just the database interface for
sending statements and retrieving result sets.  Some sort of way to
obtain which database you're talking to would suffice; implementations
can then decide how to call it.

There are soooo many things that each database does slightly differently.
A very simple example is booleans: in databases that support them you
would definitely want to use them.  In others, you could do some sort of
convention like mapping all TINYINT(1) columns to booleans, or use JSON
with only true/false values, etc.  This isn't something a database
interface should concern itself with, IMO.

I might draw the line at getting the last inserted record ID, as some
databases offer that as the only way to get the ID of an autogenerated
column, AFAIK.  But for bulk inserts, that won't work either.  Some
databases support RETURNING ...., which allows you to fetch a result
set from the INSERT statement which contains data from the records
you just inserted.  It's a total mess :)

The transaction management you mentioned earlier is another such
questionable edge case that we might want to support.

> > Perhaps it's a better idea to define a common API that each module
> > exporting database stuff should implement.  Then for Schemes that support
> > functors (like CHICKEN), that could be one way of importing them in an
> > agnostic way, and a dbi-like functionality could be defined on top?
>
> This is a lot better than nothing, but I'd personally prefer the generic
> functions.

Yeah, that's certainly more flexible.

Cheers,
Peter