Support real life SQL about red HOT 6 CLOSED

Having recently spent a week fighting hand and fist against SQLAlchemy to get it to just produce a union query with selects from subqueries with aggregate and window functions, that took me ten minutes to actually write by hand. You want to be able to go from SQL into language syntax thats like SQL, as well as language syntax thats idiomatic to whatever crazy rendered SQL that can get the job done. Personally I am a fan of the escape hatch abstraction. Something that's very thinly abstracting the SQL so you avoid vendor lock in, but close enough I can translate almost directly. Might not have to be related to the ORM really, just work relatively nicely with SQL generation in concert with the ORM. So perhaps a subquery is from an ORM expression, the other is some vendor specific directly optimised by hand query close to raw SQL, and the two are nicely combined to be rendered together with a parent query like object referencing either as if it was more ORM like.

from red.

WITH RECURSIVE included_parts(sub_part, part, quantity) AS (
    SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product'
  UNION ALL
    SELECT p.sub_part, p.part, p.quantity
    FROM included_parts pr, parts p
    WHERE p.part = pr.sub_part
  )
SELECT sub_part, SUM(quantity) as total_quantity
FROM included_parts
GROUP BY sub_part

I know, it’s not complete... and I don’t know if it is possible (yet). But the way I’m thinking of taking is something like:

(IncludedParts.where({ .part eq “our_product” }), { .^where: .part == IncludedParts.sub-part } ... *).classify: *.sub-part, :reduce-field{ .quantity.sum }

from red.

I think if we find a generic and composable way to represent each SQL feature it will be possible to create large and complex query.

So, I’ll put it here some thoughts about some of the “special” features you used on your example. Not all at once. And please comment every one you’d like to!

from red.

I don’t know if it is possible but the way I think to do something like this

WITH RECURSIVE t(n) AS (
    VALUES (1)
  UNION ALL
    SELECT n+1 FROM t WHERE n < 100
)
SELECT sum(n) FROM t;

Is with something like this

(Red.select({ 1 }), * + 1 ... * < 100).sum

from red.

I was thinking about that and Im starting to disagree...
The way Im thinking now is that the Red user should be able to do with Red any thing it could with local data, what SQL would it generate isn't a problem. What do you guys think?
I mean: we should map language syntax to sql, and not sql tho language syntax...

from red.

I think Red is tracking a way to transform perl6 code to SQL, something like:https://github.com/FCO/Red/wiki/Better-map-grep and https://gist.github.com/FCO/87831dd190e1bb12adb138ce866acb73

from red.