411–420 of 466 posts
Earlier quoted context omitted.
That's what frameworks like Hibernate or Doctrine do, they have their own object oriented query language that compiles to SQL.
Well, that's an object oriented design, and it's not really a full language in that it has its own syntax. I'm talking about a relational language with its own syntax.
Both DQL and HQL are complete query languages.
Earlier quoted context omitted.
> SQL is messy because describing the underlying data relationships are messy. No, the relational model is beautiful and consistent! SQL is messy because the syntax is not consistent and elegantly composable. It could have those properties and still present the same underlying data relationships. See Linq in C# as an example for how a more composable query syntax can expose the same data model. For example in Linq yo…
I disagree, the relational model is very incongruous with the object model that nearly all LOB applications use. Which is where 99% of the usage issues lie.
Earlier quoted context omitted.
> SQL is messy because describing the underlying data relationships are messy. No, the relational model is beautiful and consistent! SQL is messy because the syntax is not consistent and elegantly composable. It could have those properties and still present the same underlying data relationships. See Linq in C# as an example for how a more composable query syntax can expose the same data model. For example in Linq yo…
Slightly tangential but I really like Linq syntax. The only problem I have with it is that it is really hard to debug if there is a logic error. I often see developers write linq find out the record set they get back is incorrect and then break up the linq query to a nested if clause to get what they want.
table = (SELECT column | scalar expression FROM graph | table WHERE ..GROUP BY ... HAVING... ORDER BY...)
So in SQL, each scope is a table and that is the main primitive.
More metrics would be needed to criticize SQL orthogonality, instead of providing only one example of subqueries as scalar expressions, when they more generally produce tables.
Actually, SQL use the same query syntax for scalars and for tables and that could be seen as good orthogonality.
Earlier quoted context omitted.
I only had a quick look, is it a computer-friendly offshoot of relational algebra[1], the actual mathematical model for relational databases, as an actual query language? [1] https://en.wikipedia.org/wiki/Relational_algebra
Relational calculus is another mathematical model that is dual to relational algebra: if you have one representation you can always get the other. Relational algebra describes a fairly direct set of manipulations of database rows that can be implemented efficiently. Relational calculus operations are more abstract, but have useful identity transformations you can use to optimize query plans. So, when you ask a databa…
Earlier quoted context omitted.
I only had a quick look, is it a computer-friendly offshoot of relational algebra[1], the actual mathematical model for relational databases, as an actual query language? [1] https://en.wikipedia.org/wiki/Relational_algebra
Relational algebra is functional programming: you map-reduce your way to a solution set. Relational calculus is logical programming: you specify what the solution looks like and the query engine figures out the sequence of operations to find a solution.
Earlier quoted context omitted.
At work we almost exclusively use pure stored procedures[1] and everything is normalized very well. It is an absolute joy to write SQL, because of how terse it is while still being very readable. Trying to implement business rules about data relations outside of the DB is a nightmare. [1] We use dynamic SQL within stored procedures for pivots.
You lose source control on your procedures. How you deal with that?
Earlier quoted context omitted.
Not for the reason you're implying, actually for the opposite reason. I'm from the days of yore, just before ORMs became popular and it basically replaced a lot of boilerplate code, but it wasn't the SQL that was the bulk of it. It was mainly to save time in writing code to map columns to object properties really, the sql statements themselves were trivial even if you weren't lazy and just used select *. Also, here's…
> It was mainly to save time in writing code to map columns to object properties really, the sql statements themselves were trivial even if you weren't lazy and just used select *. I am running a side project and yes, writing the native SQL statement to take your object and put it in the database is not a problem, put in the parameterised values and off you go. But getting the data back from the database? Oh the horr…
It's perfectly fine to just 'know' that the data is going to be an int and just do `var id = rs.getInt(id")`.
I'm on my phone so it's too fiddly to write my own brief example, but if you get rid of the silly comments in this you'll see you can do it all in a few lines, just very boilerplate lines:
Earlier quoted context omitted.
you're comparing $lookup, an operator that nosql isn't designed for to a join, an operator sql was designed for
Lookups are very common in MongoDB; Starting with SQL, lifting the data as-is into Mongo, and translating the queries 1:1 will just result in garbage queries, like that one. An "idealized" NoSQL schema is far, far more complex than anything anyone used to SQL would arrive at ([1]), but most of that is because in a "pure" NoSQL/Document-oriented database, the query engine simply isn't that powerful (think Dynamo). Mon…
Earlier quoted context omitted.
Lookups are very common in MongoDB; Starting with SQL, lifting the data as-is into Mongo, and translating the queries 1:1 will just result in garbage queries, like that one. An "idealized" NoSQL schema is far, far more complex than anything anyone used to SQL would arrive at ([1]), but most of that is because in a "pure" NoSQL/Document-oriented database, the query engine simply isn't that powerful (think Dynamo). Mon…
I wouldn't say arrays are bad, and the entire paradigm of data modeling in mongodb is to store your data based on your application usage patterns. if you have to query across multiple collections via a $lookup, then maybe you'd benefit from embedding the smaller of those collections into the former.
One example: We used mongodb to track the state of a general CSV import system. So, we'd have a document for each csv file a user imported, and on that document, we were storing errors which occurred during the import to later display to the user. Of course, in an array. Worked great for years, until one day, a user uploaded a very bad CSV, non-maliciously, with hundreds of thousands of lines, with dozens of errors on each line, generating an array millions of items large. The failure condition here was wild: the import just got slower, and slower, and slower, until eventually the (modestly provisioned) db cluster started failing. We immediately normalized that array into its own collection, re-ran the import, still generated millions of errors, but with no problem.
As always, a general rule doesn't apply in every situation, but in my experience, unless you have a really strong grasp on how a system's use will scale, years into the future, unbounded arrays are icky. Lookups across two collections are only a modest performance loss over a direct query, and if the arrays get up there in size, they can actually be faster.