Listing columns first Then table Then join Then filter Then group bys Then limit
The order of operations are out of whack and makes pipeline ing a little hard.
I found this to be closer to LINQ way
I hope in near future databases will come with better query languages...
1. Sql is non-modular
2. Non-orthogonal
3. Its parroting of the (beautiful) relational data model is... opinionated at best.
The standard is unreadable and not really implementable, one always has to resort to implementation docs.
Realistically there are no alternatives, but... sql is no good.
Table Out1 has translated versions of fields AA..AZ from table In1.
Table Out2 has translated versions of fields BA..BZ from table In1.
table Out3 has translated versions of fields X..Z from table In2, and the translation depends on some of the AA..BZ fields, in both translated and untranslated forms.
We'd end up with views that depended on other views and after expanding had like a dozen self-joins to add more fields that weren't included in the base view. And views that were selecting a big list of columns unchanged, plus adding a couple.
But somehow despite being an absolute ^%#@!^&$! it still worked better than the pointy-clicky ETL tooling we'd been using previously. Mostly because of the run->debug->modify cycle time being faster, and only a little because of avoiding pointy-clicky stuff.
2. What SQL functions are you talking about? There are builtins, e.g. avg, sum, and then there are User Defined Functions, which have nothing to do with SQL, mostly just a random external language or one of the non-portable PL/SQL flavours.
Practically speaking, one cannot really write portable SQL outside of a very limited intersection of various dialects. And within this limit users just cannot build a reasonably portable code library.
There is a reason why analysts working with SQL just copy massive query templates again and again and again...
What more natural could there be than to write this as (old 1992 correlated query style)
SELECT cityName FROM Cities C WHERE ((SELECT COUNT() FROM Inhabitants I WHERE I.cityName = C.cityName) > 4000)
Agreed ???
YOU CAN'T DO THIS IN SQL [standard version] !!! You must write
SELECT cityName FROM Cities C WHERE (4000 < (SELECT COUNT() FROM Inhabitants I WHERE I.cityName = C.cityName))
This makes SQL about the only language in the world that allows you to write "a<b" in certain places but forbids you to write "b>a" in those very same places. Some professional piece of language design.
(Disclaimer : I don't know how matters are in SQL:2016 wrt to this issue, but it drove a former IBM employee who was at the time a member of the SQL standards committee to (a) start work on TTM and (b) leave IBM as soon as he could.)
It makes it really hard to offer good autocompletions too.
https://docs.microsoft.com/en-us/azure/data-explorer/kusto/q...
Query q = db.from("foo").join("bar").using("id")
.select("id","foothing","barthing");
Also, I'd really like a SQL mode where no literal constants are allowed, as a mechanism to force the use of query parameters and prevent SQL injection.
(In fact, it might even be outright true. But that's not a guarantee that what the Micro$oft users do with the Micro$oft tools is necessarily also the *BEST* thing for the user of that software product that the Micro$oft users produce.)
The creator of LINQ was Erik Meijer (https://en.wikipedia.org/wiki/Erik_Meijer_(computer_scientis...), who was a leading researcher in programming languages and functional programming before shifting into industry. He's well known in the Haskell world for e.g. his work on bananas and lenses, and many other things.
LINQ (https://en.wikipedia.org/wiki/Language_Integrated_Query) is basically an embedding of a general monadic framework, augmented with a set of query-specific operators. It can process arbitrary data sources - quoting from the link: "arrays, enumerable classes, XML documents, relational databases, and third-party data sources."
In addition, because it's fundamentally monadic, it can be used to express all sorts of computations, by: "utilizing query expressions as a general framework for readably composing arbitrary computations, include the construction of event handlers or monadic parsers."
LINQ has been ported to PHP, JS & TS, and even Actionscript (although the ports aren't necessarily as capable as the version on .NET, which has language-level support for the features.)
If you're interested in this general subject, I guarantee you you have a lot to learn from LINQ and the research work behind it.
Erik Meijer is that, eurhm, person, I remember from publicly stating that his favourite resarch method is "throwing things at the wall and see what sticks". WOW. That's the way of the true academic. Of course it might have been the case that he was being really truly utterly facetious/cynical, but I certainly don't recall having any sense of that, not even remotely, upon reading that remark. (And if it means anything to you, I don't recall someone like Dijkstra ever writing anything like that anywhere. But of course Dijkstra was one of the last *TRUE* academics.)
And he is also that, eurhm, person, I remember from "All your databases are belong to us", which drove Chris Date to writing the response he did (published both in the ACM and in his own book "Stating the obvious") in which he publicly shamed the ACM itself for lending its pages to such sheer utter nonsense.
You might want to read the substance. If it shows anything at all, it's the absolute absence of value there is to be found in having a PhD these days.
And there is very, VERY, little in your reply that does not fall either into the category of "argumentum ad verecundiam" or else into that of superficial handwaving.
Date rather missed the point though - in that article, Meijer clearly identified the duality of the "data modeler" vs. "programmer" perspective - "duality" implying that these were two different perspectives on the same problem. He was saying that the programmer perspective had needs, created by the new industry landscape at the time, that weren't being served by existing relational databases. And he was correct on that point, as the rise of NoSQL solutions and databases such as BigQuery have demonstrated. If Meijer was wrong, then the entire industry, including Google, AWS, etc. have been wrong for a couple of decades now. But really, Date was partly indulging in a shooting of the messenger, and partly a nitpick at what he saw as a mischaracterizations. Date's responses were largely irrelevant to Meijer's main points, and he should have known better.
Anyway, I notice that none of your excuses to avoid learning are actually technical objections to what I was recommending. Why fight so hard to find reasons not to learn?
The only perspective should be "how can we get information from users (or any other form of information-capturing device in fact), make records of that information and keep those records as long as the information may be needed [and as long as it pays off to keep them], so that we can later get that information back to same [or other] users". Let's call that perspective "HG" for "Holy Grail".
Codd's intent has always been to achieve HG. Date's intent has always been to achieve HG. Both properly identified the core asset we work with : data. It's always ultimately about the data. Both consequentially properly identified the first step needed to make achieving HG possible : a model of data built on foundations of mathematics. (To quote Codd : "database design is not going to be possible if the only concepts available are bits and bytes".) The model of data they came up with is the relational one. And achieving HG is not going to be done by ditching the model of data. On the contrary. It might be achievable by replacing it with some other model of data, but that is going to require exhaustive demonstration of how and why the replacing model of data is better than the relational one, and delivering such exhaustive demonstration is going to require understanding the relational one in the first place. Of the entire mob that is into what you call the "programmer perspective", there is not a single individual that does.
And Date's point in his reply was : neither does Meijer. Backed by evidence, so it was neither "shooting the messenger" nor "a nitpick" but unfortunately it might require understanding the RM to understand the evidence.
To close, I have some hints for you : (1) there ARE NO "existing relational databases" (2) "BigQuery" is not a "database" but a "DBMS" (there's a Dijkstra quote somewhere about how mastery of mathematics and mastery of language fortuitously tend to always come hand in hand - and there's a corollary to that) and (3) yes, the entire industry has indeed been "wrong for decades" because the RM has never been properly understood. See the McGoveran quote on www.dbdebunk.com. Ironically, this grave mistake might be due exactly to "too much programmer perspective" at the time those pseudo-relational systems were built.
Plus (assume my .where() example was Java), the host language compiler is never going to find a way to cope with the declaration of the 'input' of the 'lambda', *precisely* because [the definitions of] those things are outside the scope of what the host language compiler knows about. So you need a way to do something like "import my-db-definition;" And then you need to put machinery in place to verify at runtime that the definitions as they are for the db, are still the same (or compatible) with the definitions that the program was compiled with. Etc. etc. etc.
Lambdas in the host language won't play well with remote RDBMSes -- you'd have to be able to serialize the lambda and make the serialized form reasonably efficient. I'm skeptical of lambdas for expressions in DB query APIs.
An expression like `foo + bar` has to become `.expr(plus("foo", "bar"))`, except, if you take this to its limit you'll want to use non-string objects to identify column names and other such things, and, again, it quickly becomes unwieldy.
You cover some of this in your comment, so I think we're in agreement:
> That "the API becomes unwieldy" is in fact inevitable.
And yet an API is kinda desirable.
Ultimately I think a query language with no constant literals, only query parameters, compiling to a standard AST that can also be constructed by APIs, may be the best way forward. One could then write in a QL to start with, compile to an AST, serialize into a host language if desired, modify and use that, or always use the QL, or even always use the API, unwieldy though it would be. Then lambdas could actually be in the QL and compiled on the fly as needed:
Query q = db.query();
q = q.from(...).join(...).using(...)
.select(...);
q = q.where(q.expr("foo + bar < baz"));
QueryScope qs;
Query q = db.query();
q = q.from(...).join(...).using(...)
.select(...);
/* Get a context of in-scope identifiers, types, ... */
qs = q.getWhereScope();
/* Now we can compile an expression string */
q = q.where(qc.expr("foo + bar < baz"));
QueryScope qs;
Query q = db.parse("SELECT ... FROM ... JOIN ... USING (...)");
/* Get a context of in-scope identifiers, types, ... */
qs = q.getWhereScope();
/* Now we can compile an expression string */
q = q.where(qc.expr("foo + bar < baz"));Yeah. however I may not live to see the day when that desire, which I agree is felt by 99.99% of the developer community (hell, even by 99.99% of the end user community because don't come and tell me that what that community is feeling isn't some sort of sense that "the developers just can't offer us any answers"), actually gets to be fulfilled.
"compiling to a standard AST"
I think that what you might be failing to appreciate is that achieving that requires a "standard algebra", and that no such thing exists at this present day.
Oh I appreciate that. What I've in mind is something like the SQL standard (which isn't exactly universally adhered to) of QL ASTs.
However, it's good to have options, I wonder if prql will get traction. It would be great if it did.
How's that the problem and not advantage?
And FWIW, what I wrote was in reply to "mental model". It was *you* who apparently equated that with "speaking English". The "mental model" I described as "exactly the problem" is the "mental model" *of the corresponding logic*, which, though mathematically consistent, is hopelessly unintuitive. Being English-speaking or not has nothing to do with understanding a particular logic underpinning the behaviour of a particular Data Manipulation Language.
> None of the people who "speak SQL" these days fit that bill.
You can't prove this. This is what you believe is true.
> And FWIW, what I wrote was in reply to "mental model". It was you who apparently equated that with "speaking English"
Isn't it odd how we have the gift of being able to communicate, yet all we do is think we can wield telepathy and not use words to express ourselves. Unless you accurately express what it is you're thinking about, I can't fill in the gaps since I'm not telepathically enabled.
> Being English-speaking or not has nothing to do with understanding a particular logic underpinning the behaviour of a particular Data Manipulation Language.
Of course it does.
I think it helps you focus on the outcome required, and makes you more efficient deciding how to implement it.
I use the same approach for methods in normal code: first decide on the signature of the method, and potentially a unit test to test it, and then think about the implementation.
db.Table.where(x=> x.name == 'abc').skip(10).take(20).groupBy()
This is really hard in sql especially with temporary tables and nesting you need to do as soon as you need to do things a little out of order...
Etc
Moreover, your very examples illustrate how "thinking SQL" is *NOT* the path to finding proper solutions. .skip() and .take() clearly originate from the world of ranking problems and it drips off of every word that you were thinking of finding a "modern" way to specify [things like] "TOP 10", but those solutions are themselves a fundamentally crippled way to attack the problem.