It seems people here are really interested in alternatives to SQL. So perhaps you'd also like to have a look at https://github.com/erezsh/Preql
(Same name, same goal, different approach, and already working)
PRQL – A proposal for a better SQL
181–190 of 302 posts
Re: PRQL – A proposal for a better SQL
#182using COMMON TABLE EXPRESSIONS (cte) can greatly improve readability of complex sql queries. adding "flow" just feels like a variation on sequential programming.
i am against "improving" sql. instead, i thing a whole rethink of the engineering behind relational engines needs to occur. for example, why can't a relational database support both SQL and other languages simultaneously, instead of being so black-boxish?
Re: PRQL – A proposal for a better SQL
#183I'm kinda surprised that the list of influences doesn't mention XQuery. Yes, it's not a relational query language... but it covers much of the same ground in practice, especially the part that they call "FLWOR expressions" (for/let/where/order/return) that operate on "tuple streams":
https://www.w3.org/TR/xquery-31/#id-flwor-expressions
And it has grouping, windowing functions etc. I bet you could define a subset that is specifically tailored to the same use cases as SQL - basically, get rid of everything to do with elements and attributes, and only allow scalars and sequences (and maybe maps?). But otherwise keep XDM data types and their semantics.
Re: PRQL – A proposal for a better SQL
#184using COMMON TABLE EXPRESSIONS (cte) can greatly improve readability of complex sql queries. adding "flow" just feels like a variation on sequential programming. i am against "improving" sql. instead, i thing a whole rethink of the engineering behind relational engines needs to occur. for example, why can't a relational database support both SQL and other languages simultaneously, instead of being so black-boxish?
No post body was provided.
Re: PRQL – A proposal for a better SQL
#185No graph query support and no possibility to directly output nested structures = no donut.
Re: PRQL – A proposal for a better SQL
#186Earlier quoted context omitted.
Thanks! > Did you consider using a keyword like `let` for column declarations Yeah, the current design for that is not nice. Good point re the keyword scanning. I actually listed `let` as an option in the notes section. Kusto uses `extend`; dplyr uses `mutate`; pandas uses `assign`. I opened an issue here: https://github.com/max-sixty/prql/issues/2
I've added the `let` keyword given a few people commented on this.
Awesome that you're responding to feedback like this!
Another suggestion around `let`: consider splitting it into two operations, for creating a new column and for modifying an existing one. E.g. called `let` and `set`. Those are in effect pretty different operations: you need to know which one is happening to know how many columns the table will have, and renaming a table column can with your current system change which operation is happening.
Splitting them into separate operations would make things easier on the reader: they can tell what's happening without having to know all the column names of the table. And it shouldn't really be harder for the writer, who ought to already know which they're doing.
I encountered something like this at my previous job. We had a DSL with an operation that could either create or modify a value. This made the code harder to read, because you had to have extra state in your head to know what the code was doing. When I rewrote the DSL (the rewrite was sorely needed for other reasons), I split the operation in two. I was worried people would have been too used to the old language, but in practice everyone was happy with it.
Re: PRQL – A proposal for a better SQL
#187[deleted]
Re: PRQL – A proposal for a better SQL
#188I'm quite opposed to the idea "from should be first". I want to understand what exactly the query returns, not the implementation detail of the source of this data (that can later be changed). Literally first example from page - I have no idea what is being returned: from employees filter country = "USA" # Each line transforms the previous result. let gross_salary = salary + payroll_tax # This _adds_ a column / varia…
It's a fair sentiment, but it can be handled without losing directional flow and composability, some of the bigger advantages of reworking SQL.
One idea would be along the lines of a function prototype: a declaration, up front, about the columns and types that a query is expected to return. It's a good place to put documentation, it's redundant information which should protect against mistakes but not so redundant that it would be too taxing - the author should know what the query returns. The prototype would only be used for validation of column names and types.
Another idea would be requiring the last element in a query to be a projection, a bit like the return statement in a function body: here's what I'm returning out of the grand set of symbols available (e.g. via various joins) in scope from previous operations in the flow.
Re: PRQL – A proposal for a better SQL
#189i really like this, going to follow it for a bit
Re: PRQL – A proposal for a better SQL
#190Just wanna say, I absolutely love this.
One piece of feedback: "sort sum_gross_cost # Uses the auto-generated column name." ... seems like a huge landmine. Languages really should not have any implicit way of constructing identifiers (among other reasons it is not easily greppable). You might consider using a syntax like `sum:gross_cost` which can function as a sort parameter and an aggregation, but is actually recognizable as an object instead of having a…
Yes, I agree with the downsides of the existing approach, and you're absolutely right re "Languages really should not have any implicit way of constructing identifiers".
I'd still say it's fine if it implicitly names output columns, for convenience.
I referenced your comment in an issue and listed some options: https://github.com/max-sixty/prql/issues/5