4 ms·

I don't mind SQL, really, but I would prefer a query construction API that has the full power of SQL (and more even). At the very least such a thing would not

I don't mind SQL, really, but I would prefer a query construction API that has the full power of SQL (and more even). At the very least such a thing would not have a SQL injection problem, but also could be used to generate ASTs. Query q = db.from("foo").join("bar").using("id") .select("id","foothing","barthing"); However, the moment you want any non-trivial SQL expression, such an API becomes unwieldy. And yet the need for non-trivial SQL expressions in queries can't be avoided, nor can it be left to the host language. 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.

tehlike 5y ago
Linq in c# fixes that.
- ErwinSmout 5y ago
  I doubt that it does. There is way more to "integrating [host] language and queries" than the MIN() of what Micro$oft engineers are (a) capable of understanding and (b) allowed by their own management to put in the products they come up with.
  - antonvs 5y ago
    Your comment may be true of many things that Microsoft does, but not LINQ. The creator of LINQ was Erik Meijer (https://en.wikipedia.org/wiki/Erik_Meijer_(computer_scientist) 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 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.
    - ErwinSmout 5y ago
      Dude, please, give me a break. 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.
      - antonvs 5y ago
        You can't really complain about argument from authority when using another authority to rebut the first one. 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?
  - cryptonector 5y ago
    I also doubt it, though I know nothing about Linkq so I'm loathe comment except in so far as what makes the problem hard (see comments above).
  - tehlike 5y ago
    Microsoft is surprisingly good for creating good developer tools and infrastructure.
    - ErwinSmout 5y ago
      In the perception of the Micro$oft users who are brainwashed with the idea that what Micro$oft does is good for the developers. (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.)
      - tehlike 5y ago
        You are being unnecessarily cynical. I left microsoft ecosystem about 10 years ago, but still miss the developer environment they had in 2012 today. Nothing still comes close.
        ErwinSmout 5y ago
        I invite you to inspect, even if only cursorily, my above exchange with mr. antonvs. My point is that Micro$oft was created by programmers (in fact in its earliest days it was mostly a compilers company), that to this very day the vast majority of what it does falls into the category "by programmers, for programmers", and it may indeed be the case that they are extremely good at that. And if you are yourself a programmer, then it is obviously only self-evident that you are then going to praise/applaud Micro$oft for what they do. It's just that this "programmer perspective" may yield a hopelessly narrowed down tunnel vision on what the real goal should be. That real goal being : making complete sense to the final end-users. And as an example of "not making complete sense" : I open an xls file with multiple sheets in it. I browse through the contents, paging up and down through the sheets. I'm done browsing and I close the file. Microsoft asks me if I want to save my changes. The reason of course (or presumably) being that somewhere hidden inside, there's a "current sheet" indicator and it is considered part of the data because it gets saved alongside the actual sheet contents, so the file can open up with the last sheet displayed already active. But you show me one single user (and especially one not coming from an IT background) who never once in his life reacted to that with "WHAT FRIKKING CHANGES ARE YOU TALKING ABOUT ? I CHANGED NOTHING.".
ErwinSmout 5y ago
That "the API becomes unwieldy" is in fact inevitable. In order to support .where(<boolean expression>) as well as .select(<any expression>, ascolname) you need a way to pass a parameter of type something-like-expression to the .where() and .select() methods, and lambdas or at least something like them appear to be the right way to do that (e.g. .where((foothing) -> (foothing<0)), but languages offer no means to "introspect into the lambda" which is what is needed to translate it into SQL. 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.
- cryptonector 5y ago
  > lambdas 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")); Ok, that snippet has a problem if we want `q` to be a unique pointer. Let's fix it up a bit: 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")); And a hybrid QL + API might look like: 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"));

tehlike 5y ago

Linq in c# fixes that.

ErwinSmout 5y ago
I doubt that it does. There is way more to "integrating [host] language and queries" than the MIN() of what Micro$oft engineers are (a) capable of understanding and (b) allowed by their own management to put in the products they come up with.
- antonvs 5y ago
  Your comment may be true of many things that Microsoft does, but not LINQ. The creator of LINQ was Erik Meijer (https://en.wikipedia.org/wiki/Erik_Meijer_(computer_scientist) 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 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.
  - ErwinSmout 5y ago
    Dude, please, give me a break. 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.
    - antonvs 5y ago
      You can't really complain about argument from authority when using another authority to rebut the first one. 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?
- cryptonector 5y ago
  I also doubt it, though I know nothing about Linkq so I'm loathe comment except in so far as what makes the problem hard (see comments above).
- tehlike 5y ago
  Microsoft is surprisingly good for creating good developer tools and infrastructure.
  - ErwinSmout 5y ago
    In the perception of the Micro$oft users who are brainwashed with the idea that what Micro$oft does is good for the developers. (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.)
    - tehlike 5y ago
      You are being unnecessarily cynical. I left microsoft ecosystem about 10 years ago, but still miss the developer environment they had in 2012 today. Nothing still comes close.
      - ErwinSmout 5y ago
        I invite you to inspect, even if only cursorily, my above exchange with mr. antonvs. My point is that Micro$oft was created by programmers (in fact in its earliest days it was mostly a compilers company), that to this very day the vast majority of what it does falls into the category "by programmers, for programmers", and it may indeed be the case that they are extremely good at that. And if you are yourself a programmer, then it is obviously only self-evident that you are then going to praise/applaud Micro$oft for what they do. It's just that this "programmer perspective" may yield a hopelessly narrowed down tunnel vision on what the real goal should be. That real goal being : making complete sense to the final end-users. And as an example of "not making complete sense" : I open an xls file with multiple sheets in it. I browse through the contents, paging up and down through the sheets. I'm done browsing and I close the file. Microsoft asks me if I want to save my changes. The reason of course (or presumably) being that somewhere hidden inside, there's a "current sheet" indicator and it is considered part of the data because it gets saved alongside the actual sheet contents, so the file can open up with the last sheet displayed already active. But you show me one single user (and especially one not coming from an IT background) who never once in his life reacted to that with "WHAT FRIKKING CHANGES ARE YOU TALKING ABOUT ? I CHANGED NOTHING.".

ErwinSmout 5y ago

That "the API becomes unwieldy" is in fact inevitable. In order to support .where(<boolean expression>) as well as .select(<any expression>, ascolname) you need a way to pass a parameter of type something-like-expression to the .where() and .select() methods, and lambdas or at least something like them appear to be the right way to do that (e.g. .where((foothing) -> (foothing<0)), but languages offer no means to "introspect into the lambda" which is what is needed to translate it into SQL. 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.

cryptonector 5y ago
> lambdas 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")); Ok, that snippet has a problem if we want `q` to be a unique pointer. Let's fix it up a bit: 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")); And a hybrid QL + API might look like: 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"));