News for hackers. Stuff that matters.
Re:What MongoDB got right by jerf on Friday November 13, 2015 @08:55AM (#10559823)
SQL's fine on its own. The problem arises when you try to manipulate it. A query to get messages that were sent between now and a week ago, joining in the user table for both the sender and the receiver to get their full names, is sensible enough when written out as SQL. I can quibble about some of the affordances of SQL, but it's good enough for most queries. The problem is if you try to create some sort of data that represents "get the message body", "messages sent since last week", "get the sender's name", "get the receiver's name", and somehow programmatically assemble a query out of the pieces. (Slight quibble, this isn't actually about OO, this is about "anything that isn't SQL". A Haskell library will have the exact same problem.) An AST-based approach doesn't guarantee that this will be easy, but it's still a step above trying to assemble an SQL query from those pieces.

It's totally possible. There's a ton of libraries that do it in various ways. It's just not even remotely easy, like it should be. If you look into the inside of those libraries you'll find a C'thulian monstronsity of special cases interacting with special cases until the whole thing just explodes into a brain-consuming mess, because SQL was very, very clearly not written for this use case.

In another 10 or 20 years I look forward to data-based analysis that tries to determine how much of the "NoSQL" movement was because the relational data model doesn't work for all use cases, and how much of it was the entirely accidental (in the Brooks sense) problems with A: SQL, the language itself, not its capabilities and B: schema migrations with no essential reason to be as painful as they are. (And something something column stores, but I'm not sure where they fit into this story exactly.) And to be clear on tone, I really am interested. I'm pretty sure the answer won't be either extreme but I'm pretty uncertain about where in the middle we'll fall.

Re:What MongoDB got right by virtualwhys on Friday November 13, 2015 @09:34AM (#10560007)
> and somehow programmatically assemble a query out of the pieces.

You can do this today, in various statically typed languages[1] (perhaps dynamic languages as well, though the composition will likely be more ad hoc).

Agreed re: under the hood complexity, but that's more in supporting multiple database engines (and related corner cases) than the transformation of Query > AST > SQL.

[1] https://news.ycombinator.com/item?id=10525040

Re:What MongoDB got right by jerf on Friday November 13, 2015 @11:53AM (#10560807)
Having written some of this code myself, I have to disagree; transforming SQL fragments is legitimately frustratingly challenging. I have to admit I don't know how to show a small example that captures the problem, though, because this is one of those cases where the small examples always look easy. It isn't until you're trying to support all of them at once that it is a problem. Combining two fragments that each specify tables, joins, where clause filters, and potentially subqueries with each of those recursively is nontrivial when you get down to it.

It also really doesn't help that "SQL is declarative" is basically a lie, and it very frequently totally matters which "synonymous" query you actually throw at the database, thus eliminating a lot of the obvious clean answers in any practically-useful library.

Re:What MongoDB got right by virtualwhys on Friday November 13, 2015 @02:07PM (#10561745)
Definitely not an easy task but the degree of difficulty depends on the language.

Both Haskell and Scala, for example, have sufficiently powerful type systems to allow for building up a typed query expression of arbitrary complexity, which can then be deconstructed via pattern matching to assemble the sql statement. Easy? Not at all, but very much doable, and incredibly elegant...until you need to support various database engines and their limitations/extended features; then the implementation hacks begin :\

Personally I think the work of Stephan Zeiger on the Slick library in Scala is ground breaking. Also, Wadler et al's recent-ish paper on a composable query DSL in F# is worth checking out[1]

[1] http://homepages.inf.ed.ac.uk/wadler/papers/yow/dsl-long.pdf

Re:What MongoDB got right by jerf on Friday November 13, 2015 @02:56PM (#10562050)
You seem to persist in believing the problem is on the input side. It's not; it's on the output side. My entire point is that the resulting SQL generation code is what ends up quite hairy, because the way we have found to separate concerns in 2015 and SQL are very, very different.

Let me put it this way... it is precisely because a fluent, Haskell-native SQL querying interface little resembles SQL in either syntax or usage that there is the problem. It is precisely that there have to be these library at all that is the problem. If it were easy, these library wouldn't even exist, or would be little more than drivers, but they're not just drivers... they do a lot of real work.

If SQL didn't stink by the standards of modern separation of concerns, we wouldn't need "ground breaking" work!

Or, to put it another another way:

    $ git clone [email protected]:slick/slick.git
    $ cd slick
    $ cloc .
    ---------------------------------------
    Language   files  blank  comment  code
    ---------------------------------------
    Scala      261    3589   3844     23129
Cut down to just the Scala. 23kilolines of Scala is a lot of Scala! This is not an "easy" task. "Easy" would be something that just wrapped up the existing syntax in a slightly more native form and would clock in somewhere in the several hundred range.
Re:What MongoDB got right by virtualwhys on Friday November 13, 2015 @03:30PM (#10562216)
No, I'm saying input is easy, type system does virtually all the work. Output is where the effort is spent (i.e. pattern match on query expression to assemble the statement).

Why these libraries exist is because of string-ly typed programming; in the case of sql: 1) it doesn't compose; 2) is not safe (sql injection attacks); 3) difficult to refactor; 4) untyped, therefore whole class of bugs arise.

And yes, these libraries do a ton of work, well beyond just generating sql statements, which, in the case of Slick pushes the LOC count way higher (non-blocking IO, supports basically every database engine, native function support, jdbc modeled in scala, etc., etc., it's a huge engineering effort, somehow by one person).

Anyway, I'd like to see a much smaller composable query dsl with fewer features and opt-in database support. Compile that to javascript and run in the browser against local database would be very interesting. I think this can be done, but would probably be pretty restrictive in terms of features supported.

Re:What MongoDB got right by tracker1 on Friday November 13, 2015 @12:13PM (#10560936)
I find that SQL is pretty nice in a dynamic language with string templating available... I wrote a simple wrapper for MS-SQL in node using ES6's string templating to convert to parameterized queries[1]. The irony I wrote it while migrating a lot of data out of MS-SQL.. but the interface is probably the nicest SQL client I've worked with (despite being the author).

[1] https://www.npmjs.com/package/mssql-ng