> From: Lassi Kortela <xxxxxx@lassi.io>
> Date: Thursday, September 19, 2019 10:35 AM
>
>>> I've updated it now; it's at
>>> <https://bitbucket.org/cowan/r7rs-wg1-infra/src/default/SimpleSqlCowan.md>....
>
> [...]
>
>> [ Me: ]
>>
>> I've been thinking a more general open should return a "connection",
>> since that's what it generally is, and you may find it useful to open
>> more than one connection to a non-embedded database.
>
> Racket has some kind of connection pool:
> <https://docs.racket-lang.org/db/connect.html?q=pool#%28tech._connection._pool%29>.

I think that a connection pooling facility should be a part of my DBI,
perhaps as a plugin, but I also think that you should be able to control
if and how you use it.

>>> (sql-commit db) -> unspecified
>
>> Rollback is definitely needed, and if you make commit the default,
>> you can reduce the API surface by remove this manual commit.
>
> I agree that having an explicit commit procedure can be confusing. It
> makes you worry about whether there is ever any auto-commit, or whether
> you need to commit everything manually.

That's another good point.  With MySQL seeing the light ... actually,
I'd be comfortable limiting the remit of this DBI to databases that
(can) really do transactions, see below:

> Are there situations where you want neither auto-commit nor
> transactions? I know nothing about this topic.

In theory yes, although in practice this has turned out to be a
nightmare.  There are a number of problem domains where it's not the end
of the world if you lose an insert, update, or delete.  Currently spying
on people is the big domain for this, you don't need every location at a
point in time data point to figure out where someone spends their time.
MongoDB has been big in this.

In the bad old days, when MySQL was a thin SQL layer over an ISAM
library, using it for something like a BBS inspired forum was also
mostly harmless, if a change didn't take, it could be made again,
although dropping someone's posting on the floor is uncool, but
browsers can sometimes let you try again.

In practice, systems that eschew transactions, MySQL to the point of
officially disrespecting them, also are reported to have terrible
overall durability, all too easily producing completely unrecoverable
databases.  To this date, MongoDB ships with a 30 or 60 second save
interval with its new and probably very good WiredTiger OKVS they
bought, that from the BerkelyDB and Sleepycat people.

Do we care about this domain of databases??  I doubt it.

> [...]
>
>>> Statements and result sets
>
>>> (sql-statement db code bindings) -> statement
>
> [...]
>
>>> (sql-exec db statement) -> implementation-dependent
>>
>>> Use this procedure when statement does not return a result set.
>>
>>> (sql-result-set db statement thunk) -> whatever thunk returns
>>
>>> Executes statement and calls thunk, passing a result-set object.
>
> I would have sql-exec be the only procedure that executes SQL. This is a
> common point of confusion in DB APIs in other languages: there are
> separate "execute" and "query" procedures, which are basically
> equivalent, yet not really, so you're left scratching your head. Those
> kinds of details don't stick to memory because they are illogical.
>
> IMHO it's easier to understand that a query is run though sql-exec like
> other statements. But the other statements return zero rows, whereas a
> query returns zero or more rows.
>
> In many (but not all!) databases, a SQL INSERT or UPDATE returns the
> count of rows affected, without actually returning the contents of those
> rows. We need to think about how to bake this into the API. There are
> probably situations where people know what database they are using and
> want to avoid an extra SELECT to find out the row count.

This is such a fundamental thing that I think it should be made
"convenient", somehow, to use API to get back that number of affected
rows, whether or not it's supplied by default.  No ideas how to.

In general, I agree with what you're saying.  With apologies to
Tolkien, one execute to rule them all, one execute to find them, one
execute to bring them all and in the darkness bind them.  Given that I
question the API level separation of statement creation and execution.

> One thing that I think is mandatory for basic programming sanity is
> special procedures for when you expect a query to return:
>
> * Either no rows or one row, never more than one.
> * Always exactly one row.
>
> Not sure whether or not these special cases should be baked in at such a
> low level, but for application programming they are great.

Since they have to find that out from the result-object, it makes sense
to put them at this level.

> [...]

- Harold