3 ms·

At the very least you should know SQL. The purpose of having a query dsl in some other language isn't to avoid understanding sql, it's because sql isn't easily

At the very least you should know SQL. The purpose of having a query dsl in some other language isn't to avoid understanding sql, it's because sql isn't easily composable.

ttfkam 4y ago
CTEs. Views. Set-returning functions. "SQL isn't easily composable" is a pernicious lie. Does it compose like your favorite functional language? No. SQL is not nor has ever been a functional or object-oriented or generic or procedural language. It's a fourth-generation declarative set-oriented language, so the composability is going to be based on sets. Period. Until you think of SQL as a set-oriented declarative language, you will be perpetually trying to push water uphill, ORM or no.
- ludston 4y ago
  It's not a pernicious lie at all. You can't just say, it's composable so long as you only compose with the special mentality that you consider to be blessed. Also the fact that SQL acts on sets is completely irrelevant to the syntax being composable or not. JSON is programmatically composable. XML is programmatically composable. S-expressions are programmatically composable. Most programming language syntax's are not. If you build a UI or reporting mechanism that wraps around an SQL engine and has multiple configuration options that combine to determine the SQL expression that should be used, the fact of the matter is that you will either abstract the combination of the configuration into a DSL that then gets converted to SQL, or you will have a mess of string concatenation that isn't type-safe and isn't verifiably correct until runtime. Show me the code you'd write to generate an SQL expression with ten optional filters, where each filter is comparing a different column to a set, and if the set is empty the filter should be ignored. I'll show you a DSL that does the same thing, is readable and statically verifiable, and ensures that if anybody else ever writes a similar query guarantee's that parameters will be the same so that the execution plan can be cached.
  - ttfkam 4y ago
    S-Expressions are not a set-based construct. Neither is JSON nor XML. Those are all document-oriented and/or hierarchical tree structures. I did not "bless" SQL as a set-based language. It's literally a 4th generation, declarative DSL for set theory. That was its design goal. That's the lens through which every change to the language and the engines that implement it are discussed and adopted. If I said LISP sucked at object-oriented programming and OO design patterns, how would you react? How about if they said LISP shouldn't be solely through the lens of functional programming just because that's your "blessed mentality" for it. I don't even know how to respond to that assertion. "It's not even wrong."
    - ludston 4y ago
      I would probably say that you are miss-understanding the difference between syntax and the evaluator that evaluates that syntax. All three of these expressions contain the same data: (select (col1 col2) (table1) (= col1 "foo")) "select col1 col2 from table1 where col1 = 'foo'" <select><columns><column>col1</column><column>col2</column></columns><tables><table>table1</table></tables><filters><equalityfilter><column>col1</column><value>foo</value></equalityfilter></filters></select> All three of these are "4th generation, declarative DSLs for set theory" as you like to brandish around. > If I said LISP sucked at object-oriented programming and OO design patterns, how would you react? How about if they said LISP shouldn't be solely through the lens of functional programming just because that's your "blessed mentality" for it. S-Expressions are just a syntax. It's quite normal to see Lisps that are functional, declarative, object-oriented or procedural. The only thing that differentiates Lisps from other programming language is the simplicity of the syntax enabling meta-programming, which is specifically the problem with SQL syntax and it's composability.
      - ttfkam 4y ago
        JSON, and XML are data structures, not programming languages. Can you loop or iterate in JSON or does some other language do the iterating? Can XML execute instructions (without a script tag or processing instruction, which is just punting to an external 3rd-gen language)? S-Expressions blur the line a bit, but clearly not in the direction of 4GL. A 4th-gen language is specifically one that describes what you want, not how to get it. S-Expressions fail this metric in that they can describe the logic for retrieval in addition to the data (where the data portion is not conducive to it being a programming language). SELECT a.id , a.name , p.name FROM alien a JOIN planet p ON a.planet = p.id WHERE a.name = 'Kaminoan' ; Now here I'd completely agree that things might have been better with some non-implicit syntax bounds, eg.: SELECT [ a.id , a.name , p.name ] FROM [ alien a ] JOIN [ planet p, (a.planet = p.id) ] WHERE [ a.name = 'Kaminoan' ] ; As it stands today, SQL has some pretty gnarly EBNR to describe it. But that is still missing the point. Where are the datasets for "alien" and "planet"? Are they on the same disk? The same server? The same cluster? The same kind of database? Are there a lot of aliens? Would a btree index lookup be more appropriate, a sequential search, a bloom index, or some other algorithm? Is alien name enforced to be unique or not so that we can be sure to return either optional one, exactly one, zero or more, or one or more? Is "planet" a single table, a view into one or more tables, a materialized view that caches its results, or a foreign table accessing the data from another source that isn't even relational? A 3rd-gen language would need to know these things. It would require telling the system HOW to get the data rather than just describing WHAT data subsets are desired based on other known data sets. It doesn't just transform one or more sets into a new set without any consideration as to what serialization format those data sets are stored in—on disk or in memory. Here we have the need for separation between DDL and DML. Yet DDL still only describes the structure and makes access patterns available. Creating an index does not mandate use of that index. It merely puts that data access pattern on the menu. DML (as seen above) makes the query result set definition but has nothing to do with telling the engine how it should go about doing it. Ergo: 4th-gen declarative language This doesn't mean SQL is the best language or even better than all 3rd-gen languages simply because it's a 4th-gen language. But if you're going to engage in this topic, I strongly encourage you to learn what a 4th-gen language actually is and how laughable calling JSON, XML, or S-Expressions one is. I could give you HTML as a 4th-gen language in that it describes layout and general behavior without reference to how the layout engine renders it or implements its linking, form-handling, and embedding behavior. HTML+CSS make a compelling case for a composite 4th-gen language, describing what you want, not how to render it. Whether it be links, Firefox, or Mobile Safari, the description is the same and the desired behavior is well-defined despite wildly different implementations from the engines.

At the very least you should know SQL. The purpose of having a query dsl in some other language isn't to avoid understanding sql, it's because sql isn't easily composable.

ttfkam 4y ago

CTEs. Views. Set-returning functions. "SQL isn't easily composable" is a pernicious lie. Does it compose like your favorite functional language? No. SQL is not nor has ever been a functional or object-oriented or generic or procedural language. It's a fourth-generation declarative set-oriented language, so the composability is going to be based on sets. Period. Until you think of SQL as a set-oriented declarative language, you will be perpetually trying to push water uphill, ORM or no.

ludston 4y ago
It's not a pernicious lie at all. You can't just say, it's composable so long as you only compose with the special mentality that you consider to be blessed. Also the fact that SQL acts on sets is completely irrelevant to the syntax being composable or not. JSON is programmatically composable. XML is programmatically composable. S-expressions are programmatically composable. Most programming language syntax's are not. If you build a UI or reporting mechanism that wraps around an SQL engine and has multiple configuration options that combine to determine the SQL expression that should be used, the fact of the matter is that you will either abstract the combination of the configuration into a DSL that then gets converted to SQL, or you will have a mess of string concatenation that isn't type-safe and isn't verifiably correct until runtime. Show me the code you'd write to generate an SQL expression with ten optional filters, where each filter is comparing a different column to a set, and if the set is empty the filter should be ignored. I'll show you a DSL that does the same thing, is readable and statically verifiable, and ensures that if anybody else ever writes a similar query guarantee's that parameters will be the same so that the execution plan can be cached.
- ttfkam 4y ago
  S-Expressions are not a set-based construct. Neither is JSON nor XML. Those are all document-oriented and/or hierarchical tree structures. I did not "bless" SQL as a set-based language. It's literally a 4th generation, declarative DSL for set theory. That was its design goal. That's the lens through which every change to the language and the engines that implement it are discussed and adopted. If I said LISP sucked at object-oriented programming and OO design patterns, how would you react? How about if they said LISP shouldn't be solely through the lens of functional programming just because that's your "blessed mentality" for it. I don't even know how to respond to that assertion. "It's not even wrong."
  - ludston 4y ago
    I would probably say that you are miss-understanding the difference between syntax and the evaluator that evaluates that syntax. All three of these expressions contain the same data: (select (col1 col2) (table1) (= col1 "foo")) "select col1 col2 from table1 where col1 = 'foo'" <select><columns><column>col1</column><column>col2</column></columns><tables><table>table1</table></tables><filters><equalityfilter><column>col1</column><value>foo</value></equalityfilter></filters></select> All three of these are "4th generation, declarative DSLs for set theory" as you like to brandish around. > If I said LISP sucked at object-oriented programming and OO design patterns, how would you react? How about if they said LISP shouldn't be solely through the lens of functional programming just because that's your "blessed mentality" for it. S-Expressions are just a syntax. It's quite normal to see Lisps that are functional, declarative, object-oriented or procedural. The only thing that differentiates Lisps from other programming language is the simplicity of the syntax enabling meta-programming, which is specifically the problem with SQL syntax and it's composability.
    - ttfkam 4y ago
      JSON, and XML are data structures, not programming languages. Can you loop or iterate in JSON or does some other language do the iterating? Can XML execute instructions (without a script tag or processing instruction, which is just punting to an external 3rd-gen language)? S-Expressions blur the line a bit, but clearly not in the direction of 4GL. A 4th-gen language is specifically one that describes what you want, not how to get it. S-Expressions fail this metric in that they can describe the logic for retrieval in addition to the data (where the data portion is not conducive to it being a programming language). SELECT a.id , a.name , p.name FROM alien a JOIN planet p ON a.planet = p.id WHERE a.name = 'Kaminoan' ; Now here I'd completely agree that things might have been better with some non-implicit syntax bounds, eg.: SELECT [ a.id , a.name , p.name ] FROM [ alien a ] JOIN [ planet p, (a.planet = p.id) ] WHERE [ a.name = 'Kaminoan' ] ; As it stands today, SQL has some pretty gnarly EBNR to describe it. But that is still missing the point. Where are the datasets for "alien" and "planet"? Are they on the same disk? The same server? The same cluster? The same kind of database? Are there a lot of aliens? Would a btree index lookup be more appropriate, a sequential search, a bloom index, or some other algorithm? Is alien name enforced to be unique or not so that we can be sure to return either optional one, exactly one, zero or more, or one or more? Is "planet" a single table, a view into one or more tables, a materialized view that caches its results, or a foreign table accessing the data from another source that isn't even relational? A 3rd-gen language would need to know these things. It would require telling the system HOW to get the data rather than just describing WHAT data subsets are desired based on other known data sets. It doesn't just transform one or more sets into a new set without any consideration as to what serialization format those data sets are stored in—on disk or in memory. Here we have the need for separation between DDL and DML. Yet DDL still only describes the structure and makes access patterns available. Creating an index does not mandate use of that index. It merely puts that data access pattern on the menu. DML (as seen above) makes the query result set definition but has nothing to do with telling the engine how it should go about doing it. Ergo: 4th-gen declarative language This doesn't mean SQL is the best language or even better than all 3rd-gen languages simply because it's a 4th-gen language. But if you're going to engage in this topic, I strongly encourage you to learn what a 4th-gen language actually is and how laughable calling JSON, XML, or S-Expressions one is. I could give you HTML as a 4th-gen language in that it describes layout and general behavior without reference to how the layout engine renders it or implements its linking, form-handling, and embedding behavior. HTML+CSS make a compelling case for a composite 4th-gen language, describing what you want, not how to render it. Whether it be links, Firefox, or Mobile Safari, the description is the same and the desired behavior is well-defined despite wildly different implementations from the engines.