On 13/09/2019 13:23, Peter Bex wrote:
> 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".
Agreed. For a start, database-specific query syntax is sometimes useful,
and trying to come up with a way to handle all of that from a truly
portable front-end is a lesson in pain!

I think abstractions over SQL can fall into three camps:

1) Nicer syntax for SQL than strings - I count SSQL in that camp and use
it as such myself! The transformation here is purely syntactic, but even
then it's complicated, as Peter's blog post illustrates.

2) Implementing some particular existing data model in SQL, using a
subset of SQL's features. Eg, an implementation of srfi-69 or something
that happens to use sqlite at the backend. You won't use this to try and
access arbitrary SQL databases. At the most general extreme of this area
might be a relational algebra DSL that gives you
join/select/project/group/etc operators over relations, implemented in
such a way that you can use arbitrary SQL tables as base relation
constants - but even then you're still only trying to implement a
particular set of semantics defined independently of SQL, using your own
chosen subset of the SQL language your backend gives you.

3) Terrible mistakes that work for some use cases but not for other use
cases that your application inevitably ends up needing, leading to
massive regrets and heartache and incidental complexity. ORMs fall into
this camp :-)

> 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.

Did I ever tell you my anecdote about the YEAR(2) type that won't let
you INSERT a value above 99 but actually stores an unsigned byte and you
can work around that with something as simple as INSERTing 99+1 or
something (I can't remember exactly) - which then truncates the
three-digit value to two digits *on output to the user* so you can do
"SELECT year_2_column FROM table ORDER BY year_2_column ASC" and get the
result "00, 01, 02, 10, 00, 01, 02", where the latter three values are
actually 100, 101 and 102? Classic :-D

>
> Cheers,
> Peter
>

ABS

--
Alaric Snell-Pym   (M7KIT)
http://www.snell-pym.org.uk/alaric/