> From: Alaric Snell-Pym <xxxxxx@snell-pym.org.uk>
> Date: Wednesday, September 18, 2019 8:23 AM
>
> On 18/09/2019 13:48, xxxxxx@ancell-ent.com wrote:

>> What I haven't yet figured out is how we should direct the DBD to do
>> the same sorts of things with results.  Can it all be done at the DBI
>> level when we get them back, perhaps with what are logically
>> callbacks to the DBI level, or do queries and other statement types
>> that return data need to capture and send down to the DBD output data
>> and quirk directives.
>
> Hrm. I suppose the general problem here is that we want to be able to
> tweak the mapping from DB-level types back to values in the result set
> in a usable but extensible way.

Exactly.

> *thinks*
>
>> Maybe something like an optional end parameter, a list with #f for
>> each part of the tuple that doesn't need special treatment.  I.e.
>> something like:
>>
>> (query "SELECT address, last_login FROM people WHERE name = ?"
>>        user-name
>>        '(#f [do-some-magic-with-timestamps]))
>
> That works, but feels mildly messy. Can we do it as an alist?

Absolutely it's messy.

> (query "SELECT address, last_login FROM ..."
>        ...
>        '((last_login . [do-some-magic-with-timestamps])))

I like that a lot better, along with your implementation suggestion:

> In fact, I suspect the values of the alist should probably be a list of
> values obtained from bindings exported from the DBI or DBD modules, or
> the results of calling procedures exported from same:

My ideal is that the DBI level won't be parsing any part of queries or
other statements, it should be query language agnostic, the thin wrapper
you mention below, and let the levels above and below deal with this.

> dbi:timestamp-to-srfi19  (convert timestamps to SRFI-19 time objects,
> rather than the default of some struct type with the timestamp and a
> time zone in)
>
> dbi:string-to-boolean (convert 't'/'y'/'true'/'yes' to #t,
> 'f','n','false','no' to #f, all else to errors)
>
> (mysql:result-string-encoding 'latin-1)   (interpret strings received
> from MySQL as being encoded in latin-1 rather than the database default)
>
> etc?

Yes, except would the last example be MySQL specific?  And SQLite for
example doesn't have a timestamp datatype, or "storage type", only NULL,
INTEGER, REAL (floating point), TEXT, and BLOB.  Time and dates are done
by convention, with SQLite providing encoding and decoding functions.

> As for how to implement this, might I suggest that the structure of the
> DBI be:
>
> 1) Thin wrapper functions that pass stuff down to the appropriate DBD
> driver, with very little processing of their own, for use by users.

Exactly what I was thinking.  Thin and generic as possible, query
language agnostic as possible, even to the point of not being SQL
specific.  Passes through data without knowledge of typing, because
Scheme is a Lisp.

> 2) A library of convenience functions for users based on top of (1),
> providing all the query-map and query-for-each and whether to present
> rows as lists or alists or directly (apply) the callback lambda to the
> result list so its arity has to match the width of the result set and
> all that stuff.

Yes, this should be logically at its own level above, even if in the
same SRFI as (1).  And I suppose this level should (mostly) hide whether
level (1) returns the whole result set at once or not.

> 2) A rich host of functions for use by DBD drivers to handle common
> cases of type translation and so on, so that they can easily support all
> the standard magic without needing complicated extension mechanisms to
> be able to also support their own, darker, magic.
>
> For instance, the mysql:result-string-encoding flag I made an example
> of would need to be applied at the point where the byte array coming
> up from the C array is turned into a Scheme string, while
> dbi:string-to-boolean needs to come later in the chain - and if the
> DBI is "in charge" of that process then it'll need complicated
> extension hooks for the DBD to be able to interject at the right
> points.

Indeed, the DBD level should be told what to do at the DBI level or
higher, without those levels really know what that is, only the user,
nor those levels or the user really knowing where they're done.

> Whereas the mysql DBD can just scan the list for string encoding
> directives at the point where it's creating the scheme string, then
> pass any directives it's not interpreted itself off to a DBI "standard
> sql to scheme mappings" procedure (which will complain if there are
> any directives it can't understand, if somebody accidentally used a
> sqlite directive in a mysql query...)

Indeed, except I'm not sure I'd place the "standard sql to scheme
mappings" procedures at the DBI level, e.g. it shouldn't even know SQL.
Or the DBI level needs to be clearly partitioned into the (1) thin
wrapper, and distinct library section(s).

Thanks for your analysis!

- Harold