On Thu, Sep 12, 2019 at 06:35:40AM -0500, xxxxxx@ancell-ent.com wrote:
> A very preliminary set of notes on the API is at <https://bitbucket.org/cowan/r7rs-wg1-infra/src/default/SimpleSqlCowan.md>.

Cool, thanks for the link.

My first thoughts:

- It seems unnecessarily complicated to me to have a special
   "transaction" object.  All SQL databases I know of have a
   transaction be a state of the connection.  So you can't
   create multiple transactions on the same connection.
   Dropping this and having transaction be a property of the
   "db" object would be simpler and reduce the API surface.

- I'm not sure giving blobs such prominence is needed.  I still
   haven't gotten around to implementing blob support in the
   postgresql egg, and I think I've never used blobs in my professional
   career, ever.  It's a bit of an antipattern to store file data in
   databases.  However, I do agree that if a DB supports blobs it is
   important for a driver to support them, so standardising then might
   be wise.

- I would extend transaction support to include a "level".  Like I
  mentioned in my other mail, you can nest transactions via savepoints
  which can be individually rolled back or "committed" (which is a no-op).

- The "meta" interface does not look very well-thought out.  This stuff
  is super complicated and the API seems too limited for proper
  introspection.

- It would be nice to have a way to execute statements with bound
   arguments without having to go through explicit prepared statement
   creation.  In Postgres at least, it's possible to send parameterised
   SQL queries directly, so that's less overhead.  And more convenient
   for the user when doing one-off queries (can be implemented in other
   DBs by making and immediately releasing a prepared statement).

- Drop the difference between statements and queries; in Postgres at
   least you can get a result set with INSERT...RETURNING, so the
   difference is moot.  Everything can return a result set.
   I don't know if this can be maintained always though; needs some
   more investigation.

- Perhaps represent result sets as opaque random-access objects?  Those
   can be converted to Scheme lists as needed.  This could provide some
   efficiency and makes it easier to access meta data like data types of
   the columns in the result set etc.

Cheers,
Peter