> Another way of handling this is defining a schema in your models. For example, Mongoid, a popular MongoDB ODM for Ruby, lets you do just that. However, when defining a schema using such tools one should wonder why they aren’t defining the schema in the database itself. Bah. It's like they didn't know that schema-free data stores mean "there is no schema; different objects may have different fields". This is the who…
> This is the whole point of MongoDB: you assume the responsibility of managing the schema
Who/what is the 'you' there? Don't 'you' have the responsibility of managing the schema either way? It's a question of whether you want to manage the schema through an rdbms, or... just in your application logic, I guess?
You've formulated the composibility reasonably in that sentence but it's important to remember that is not the only problem the world faces in data manipulation. I think the irony is that much of the problems of sql are related to a lack of tooling. The lack of tooling is result of large numbers of devs being distracted by ORM which moves that particular problem into the language ide/editor. Essentially we lack great…
I am rather fond of Sqitch and pgTAP as tools for database development.
Sqitch problem domain I handle a different way.
Pgtap is on my list of things to poke around with.
You've formulated the composibility reasonably in that sentence but it's important to remember that is not the only problem the world faces in data manipulation. I think the irony is that much of the problems of sql are related to a lack of tooling. The lack of tooling is result of large numbers of devs being distracted by ORM which moves that particular problem into the language ide/editor. Essentially we lack great…
You mean something like this? https://www.jetbrains.com/dbe/
Yes that was in my mind as I was typing it actually :)
We have a dataset much larger than RAM (600gb vs 60gb RAM). It's the working set that really matters. Accessing data outside the working set can be slow - unless you have SSDs :) Regarding consistent/durable: during the past four years, we've not had any problems on this front that weren't caused by us in. We've had an issue that was a misconfiguration on our part where we allowed writes to a server because we pulled…
Well, SSD's aren't nearly as fast as RAM, even today. I think we are in agreement about the working data vs all data. I am saying that in most applications your working dataset is much smaller than your total data set. So why pay for hardware capable of holding your entire dataset in RAM when you don't need it? I am surprised you are able to do this with Mongo. Last I checked, it simply did not handle this case, and…
Fair points - we don't allow failed servers back into replica sets and rely on writing to multiple nodes instead of the disk as source of truth.
This may not suit everyone and absolutely does not suit financial transactions. You can bend Mongo to do it using additional collections and money movement logs... but, why bother when it's simpler to use MySQL/Postgres?
His proposal is not string concatenation, and it is not vulnerable to injection.
"WHERE price <= #{price_range}" looks like raw interpolation to me. How do you make that not vulnerable to injection unless you're escaping all variables that might be used in a query?
How do you make that not vulnerable to injection unless you're escaping all variables that might be used in a query?
It was just a mockup. But you are right, in reality it would end up looking more like this (and use custom interpolation for escaping):
s.add "WHERE foo > $(bar)"
Likewise a smart syntax for clause combining (AND/OR) and some kind of nesting would probably be needed.
I believe both of these problems should be solvable without compromising the simplicity of the approach.
> Another way of handling this is defining a schema in your models. For example, Mongoid, a popular MongoDB ODM for Ruby, lets you do just that. However, when defining a schema using such tools one should wonder why they aren’t defining the schema in the database itself. Bah. It's like they didn't know that schema-free data stores mean "there is no schema; different objects may have different fields". This is the who…
> This is the whole point of MongoDB: you assume the responsibility of managing the schema Who/what is the 'you' there? Don't 'you' have the responsibility of managing the schema either way? It's a question of whether you want to manage the schema through an rdbms, or... just in your application logic, I guess?
Manage as in write all the code that ensures adherence to the schema. If you say that the field called "score" is an int in a schema-ful DB, then insert an array, the DB will throw an error. If you do that in a schema-less DB, it will not unless you add a check yourself. If you are not using some type of unified DB access layer, you must perform this check every time you write a value. You must also perform the check every time you read a value, since someone else might have written something different to the DB while you weren't looking. This you take on doing much more work, yet gain flexibility and possibly some space savings (as in, you are not storing empty cells if you don't want to).
Let's say you have a product search screen in your application. There's a text field for filtering on product title (WHERE title LIKE), one for filtering on UPC (WHERE upc LIKE), a couple range filters for min/max prices (WHERE price =), and then on the results screen the user sort on a few different columns (ORDER BY) as well as paginate and set number of results per-page (LIMIT + OFFSET). How exactly are you going…
How exactly are you going to "just write SQL" if the actual query statement needs to change based on the user input? How about something like this: s = Select.new s.add "WHERE title LIKE #{title}" if title s.add "WHERE price Note how I deliberately shuffled the order and didn't bother with escaping. Also note how anyone who knows SQL could immediately work with this, learning curve: 5 seconds. Why is there no ORM tha…
> Why is there no ORM that works like this?
Because no one wants to write a SQL fragment parser. You would have write a parser that:
a) Could be started at an arbitrary place in a query and figure out what it needed to do.
b) Would be SQL-dialect aware. (Maybe -- it might be possible to skip this.)
Were it not for (a), I'd imagine you could quickly build this by using some kind of debug feature of the database -- sending it a query to parse and getting back a parse tree.
Let's say you have a product search screen in your application. There's a text field for filtering on product title (WHERE title LIKE), one for filtering on UPC (WHERE upc LIKE), a couple range filters for min/max prices (WHERE price =), and then on the results screen the user sort on a few different columns (ORDER BY) as well as paginate and set number of results per-page (LIMIT + OFFSET). How exactly are you going…
How exactly are you going to "just write SQL" if the actual query statement needs to change based on the user input? How about something like this: s = Select.new s.add "WHERE title LIKE #{title}" if title s.add "WHERE price Note how I deliberately shuffled the order and didn't bother with escaping. Also note how anyone who knows SQL could immediately work with this, learning curve: 5 seconds. Why is there no ORM tha…
Micro ORMs usually works very much like this. There is a problem when you want sub records, they are handled differently in each library or not handled at all.
I once agreed with this, but now I don't. I just want to write SQL (dammit!). I can never, ever remember the intricacies of the Sequel API or any one of these query builder APIs. I am always looking up something that is rather trivial because I am thinking in SQL, the language, and always have to convert back to Ruby or whatever language I am working in. CTEs and SQL functions in PostgreSQL strike a good balance in t…
Let's say you have a product search screen in your application. There's a text field for filtering on product title (WHERE title LIKE), one for filtering on UPC (WHERE upc LIKE), a couple range filters for min/max prices (WHERE price =), and then on the results screen the user sort on a few different columns (ORDER BY) as well as paginate and set number of results per-page (LIMIT + OFFSET). How exactly are you going…
Even with PL/pgSQL, there is no way to build a SQL statement dynamically _and_ safely -- it's all just string concatenation there, too. However, using `CASE`, `WITH` and `LATERAL` you can have a root `SELECT` that returns one of a few different queryable code paths, and `WITH`/`LATERAL` allow you to reuse definitions.
They don't write if they looked at other databases. I think there are a lot of alternatives that might have worked better for them than MongoDb and even Mongo 3.0 might have worked better.
Regarding the schemalessness for simple new properties they should probably have fixed that in the ORM layer, it can add default values if needed. That something is null can actually happen in sql also.