On Thu, Sep 12, 2019 at 02:18:55PM -0500, xxxxxx@ancell-ent.com wrote:
> To keep our middle of the stack efforts focused and sane, we should decide on a set of at least two Scheme user level libraries that they will have to adequately support. Or perhaps substitute one or more applications, see the following comments on previous DSL efforts.
>
> In the running so far are:
>
> John Cowan's Simple SQL, see previous postings to this list: https://srfi-email.schemers.org/schemepersist/msg/12155731/

I've given my comments on this one already.

> John also has a more advanced, back to basics idea: https://bitbucket.org/cowan/r7rs-wg1-infra/src/default/RelationsCowan.md

This is a bit too abstract for me to make any useful comments on.

> My favorite in Clojure land is the Korma DSL, but Peter Bex has reservations about this general type of DSL, see this blog posting which concludes his SSQL DSL was a failed experiment, except in revealing that sort of DSL is a bad idea: https://www.more-magic.net/posts/lispy-dsl-ssql.html

I personally have given up on trying to make a "grand unifying DSL" which
maps to SQL.  This includes basically all query builders; I see the value
in having a nice way to write common queries in a simple way, but it's too
tempting to go overboard and try to do it all.  And there's always
"something missing".

However, if there was a way to map this to the underlying AST like Lassi
proposed in his mail "Playing the devil's advocate: why write SQL at all",
it would be a lot more tractable.

> Peter, would I be making a *terrible* mistake to try to create a Scheme version of Korma? I don't want any more features than it provided, but I'm a fairly basic user of SQL, not ORM oriented, but basic natural normalized OLTP, and OLAP (star schema) designer and user, and contra John's desire to use SQLite's dynamic typing as a natural fit for Scheme data, I prefer to define constraints to keep my data and programming honest. I doubt I'm a typical user....

I don't think we differ that much in how we use databases, actually :)

But regarding Korma, I wouldn't say it's a huge mistake, as long as you
know what you're getting yourself into and keep a clear vision of what
should be in scope and what falls outside.

> About data integrity, an ancedotal factoid I came across recently was several people attesting that more than half the effort required in moving an application with a lot of data from MySQL to PostgreSQL was in cleaning up all the bad data the former had allowed to creep in.

Yeah, MySQL is terrible at data integrity.  I've run into so many quirks
in MySQL that are so outrageously ridiculous that I simply cannot trust
it with my data.  When I was going to a new employer, the first thing I
would do was replace MySQL with PostgreSQL.

> How about a SQL DDL, Data Definition Language library or two? This is what you use to define your tables, columns, relationships etc.? I liked Lobos back when I was doing Clojure in the middle of the decade, but that lost support from its single developer even faster than Korma, I'm not sure it was even widely used.

I personally think a DSL for DDL is much less important.  DDL statements
are pretty static, whereas queries are often built up dynamically with
various filters and what have you.  That's where the real pain of SQL
comes from, for me at least.  Dynamically building SQL really shows you
where all the warts are and how noncomposable it really is.  A table
definition is a pretty static "write once" thing for me, and I don't
see any particular value in having a DSL for that unless you want to
support multiple databases/SQL dialects.  That's just something I'm
currently not all that interested in.  I think if you want to tackle
this, a lot of the same problems crop up as in a generic SQL DSL.
If you want to attempt it, I think the same DSL/project could tackle
query generation and DDL generation.

Cheers,
Peter