OrmHate • Hacker News Board

Re: OrmHate

#111

Post by crazygringo » Tue, Jun 26, 2012, 7:10 PM UTC

ubernostrum wrote: post #108

crazygringo wrote: post #101

Earlier quoted context omitted.

No, ORM's provide object-relational mapping, and all sorts of functions for building up queries as part of that. They don't provide an API for common queries at all. I don't even know what "all the common queries" would be... beyond get-a-value-for-pkey, every app is totally different in the kinds of queries it needs. What I described isn't a homebrew data layer or building an ORM, it's just a few helpful shortcut fu…

I hate to break it to you, but there are about as many definitions of "ORM" as there are of things like "functional programming". What you have described would be considered, by many ORM critics, to be ORM.

Aw, MAAAAN... Oh well, OK...

Re: OrmHate

#112

Post by xi » Tue, Jun 26, 2012, 7:21 PM UTC

MattRogish wrote: post #110

xi wrote: post #106

Earlier quoted context omitted.

ORDER BY, LIMIT/OFFSET have to do with presentation of data. So, although it's highly desired that a language based on the relational model supports them, they have nothing to do with the model per se. By fit poorly I mean that you cannot express most real-world business inquiries using pure relational primitives without ORDER BY or LIMIT/OFFSET and that's why I think relational algebra is not usable per se. SQL fixe…

As long as you can represent your query with predicate logic and not violate set theory (or other relational tenants), it's perfectly "relational". It's important to note that folks have figured out how to extend Codd's original relational algebra with stuff like aggregation. As the OP mentioned, "Relational" doesn't mean "What Codd wrote in a single paper back in 1969". It has continued to evolve, both with Codd's d…

That means #1 is totally relational (as an aside, you don't need ORDER BY or LIMIT for it either).

I would love to see it. Yes, you can do it in SQL, but I'd say it's not easy at all without ORDER BY and LIMIT or windowing functions and I don't know if you can do it in Tutorial D. For the reference, #1 is:

Show the blog post with the largest number of comments. If more than one exist, pick the latest.

The schema is:

    post(id integer, created timestamp)
    comment(id integer, post_id integer)

See CJ Date's excellent discussion on ORDER(BY)

I read it and the book as well, but I wouldn't call it excellent. What I read there is a reluctant admission of failure to incorporate an important operation to his query model. I see no attempt to analyze why it doesn't work or adapt the query model to make ORDER a regular operation.

Re: OrmHate

#113

Post by mjburgess » Tue, Jun 26, 2012, 7:36 PM UTC

That is really bad ideological myopia there. He refused to consider alternatives (for which I would adovcate functional design or something like sqlalchemy).

"Actually I think this is an inevitable consequence of using a relational database - you either have to make your in-memory model more relational, or you complicate your mapping code."

No its an inevitable consequence of trying to think with an object-oriented type system to describe a set-based data collection! In other-words the problem is attempting ORM: he's begging the question!

I would expect better from Fowler, but it does go to show the futher into an ideology you get (heavily design-patterned OOP in fowler's case) the more you see design and programming problems as problem with OOP rather than anything more fundamental.

Re: OrmHate

#114

Post by y0ghur7_xxx » Tue, Jun 26, 2012, 7:48 PM UTC

gnaritas wrote: post #94

maratd wrote: post #23

Earlier quoted context omitted.

> In my admittedly anecdotal experience, I have found that ORMs are the most useful for the most trivial queries. Bingo. In other words, non-relational queries work well and relational queries don't. When I'm joining multiple tables and looking for specific rows/objects that match multiple restrictions, ORM turns into a migrane of epic proportions. SQL was specifically designed to solve that exact problem. Abstractin…

> When I'm joining multiple tables and looking for specific rows/objects that match multiple restrictions, ORM turns into a migrane of epic proportions. Perhaps you're doing it wrong. Put the complex query in a proc or view, and use the ORM to map the result set into memory. Using an ORM doesn't mean not using SQL when it works better.

>> When I'm joining multiple tables and looking for specific

>> rows/objects that match multiple restrictions, ORM turns into a

>> migrane of epic proportions.

>

> Perhaps you're doing it wrong. Put the complex query in a proc or

> view, and use the ORM to map the result set into memory. Using an ORM

> doesn't mean not using SQL when it works better.

that's what he sad: use sql when it works better. if you put it in a proc, a view or your application makes no difference.

Re: OrmHate

#115

Post by MattRogish » Tue, Jun 26, 2012, 7:57 PM UTC

xi wrote: post #112

MattRogish wrote: post #110

Earlier quoted context omitted.

As long as you can represent your query with predicate logic and not violate set theory (or other relational tenants), it's perfectly "relational". It's important to note that folks have figured out how to extend Codd's original relational algebra with stuff like aggregation. As the OP mentioned, "Relational" doesn't mean "What Codd wrote in a single paper back in 1969". It has continued to evolve, both with Codd's d…

That means #1 is totally relational (as an aside, you don't need ORDER BY or LIMIT for it either). I would love to see it. Yes, you can do it in SQL, but I'd say it's not easy at all without ORDER BY and LIMIT or windowing functions and I don't know if you can do it in Tutorial D. For the reference, #1 is: Show the blog post with the largest number of comments. If more than one exist, pick the latest. The schema is:…

It's been a loooong time since I wrote any longhand Relational Algebra, so I'll cheat and use SQL. All of this can be done pretty easily with relational algebra primitives.

And we can make up any operator we want as long as it uses a primitive, so (to save typing I created a view, but you could copy-pasta). I added a "Title" to post because otherwise you could skip it entirely and just use the comment table twice, but where's the fun in that?

CREATE VIEW counts AS
( select count() AS comment_count, post_id
               from comment
           group by post_id )SELECT id, title, comment_count
  FROM post p
 INNER JOIN counts AS c1
    ON p.id = c1.post_id
 WHERE NOT EXISTS( SELECT 
                     FROM counts AS c2
                    WHERE c2.post_id != p.id
                      AND c2.comment_count  > c1.comment_count )

Re: OrmHate

#116

Post by xi » Tue, Jun 26, 2012, 8:21 PM UTC

MattRogish wrote: post #115

xi wrote: post #112

Earlier quoted context omitted.

That means #1 is totally relational (as an aside, you don't need ORDER BY or LIMIT for it either). I would love to see it. Yes, you can do it in SQL, but I'd say it's not easy at all without ORDER BY and LIMIT or windowing functions and I don't know if you can do it in Tutorial D. For the reference, #1 is: Show the blog post with the largest number of comments. If more than one exist, pick the latest. The schema is:…

It's been a loooong time since I wrote any longhand Relational Algebra, so I'll cheat and use SQL. All of this can be done pretty easily with relational algebra primitives. And we can make up any operator we want as long as it uses a primitive, so (to save typing I created a view, but you could copy-pasta). I added a "Title" to post because otherwise you could skip it entirely and just use the comment table twice, bu…

Thank you. I accept your answer with the note that you ignored my request to return only the latest post when there are more then one posts with the same number of comments, but it's not hard to adapt you query to satisfy this requirement.

However you can't do the same trick if I ask you to return the top 3 posts with the largest number of comments; or, to make the query more realistic, ask you to return the percentage of comments generated by the top 10% popular (by the number of comments) posts. Which is my point: pure relational algebra as advocated by Date et al in Tutorial D is less expressive than SQL, which probably explains the cold reception it got from the industry.

Edit: now that I think about it, you could do it without ORDER BY/LIMIT, but still it's harder than necessary.

Re: OrmHate

#117

Post by brazzy » Tue, Jun 26, 2012, 10:06 PM UTC

krig wrote: post #67

brazzy wrote: post #11

Earlier quoted context omitted.

Have you actually read TFA? Fowler's point is that yes, ORMs don't solve all of the really hard mapping problems, but save you a lot of boilerplate on the other 80-90%, and the hallmark of a good ORM is that it allows itself to be bypassed with relatively little hassle for those hard problems (like performance tuning). Also from TFA: what do you suggest using instead?

I'll try to clarify what I mean, since my reply to the other post is somewhat confused. I should read more carefully before posting. Anyway. First of all, is your data suitable for storage in a relational database? If not, if you end up having to do tons of joins and every table has references to other tables, something like Redis is probably a better fit - I'd move away from ORM/relational at that point. If it is, t…

Data that's not a good fit to be represented as relations is a completely different issue, and I agree with you on that.

Simple queries maybe simple to write, but they're still boilerplate you're better off without. An ORM lets you work at a higher level of abstraction.

> A lot of the nitty gritty of writing relational queries by hand (as noted, escaping, listing the fields to be queried etc) can be abstracted by a set of helper functions, and maintaining these is much simpler than fixing problems with the relational mapping.

Except that you just started to roll your own ORM, and I can guarantee that maintaining it will not stay simple for very long.

> If your join is directly expressed in SQL, you solve the problem by modifying the join.

Yep, that's what you do with a good ORM.

>There's no additional headache involving figuring out how the object model turns into tables and queries.

Wrong - as long as you have a relational DB at one end and an object model at the other, this additional headache is unavoidable.

Re: OrmHate

#118

Post by cico71 » Tue, Jun 26, 2012, 10:34 PM UTC

xi wrote: post #116

MattRogish wrote: post #115

Earlier quoted context omitted.

It's been a loooong time since I wrote any longhand Relational Algebra, so I'll cheat and use SQL. All of this can be done pretty easily with relational algebra primitives. And we can make up any operator we want as long as it uses a primitive, so (to save typing I created a view, but you could copy-pasta). I added a "Title" to post because otherwise you could skip it entirely and just use the comment table twice, bu…

Thank you. I accept your answer with the note that you ignored my request to return only the latest post when there are more then one posts with the same number of comments, but it's not hard to adapt you query to satisfy this requirement. However you can't do the same trick if I ask you to return the top 3 posts with the largest number of comments; or, to make the query more realistic, ask you to return the percenta…

I was about to prepare the query without ties and yes it is a lot harder than necessary and, more important IMO, much less readable/intuitive for people than have to maintain the code and that is exactly why different SQL dialects introduced ORDER BY/LIMIT/OFFSET/TOP/RANK etc.

But let me be a little bit picky about this and Date's view on ORDER BY.

The relational model deals just with the algebra/calculus without getting into the details of a language based on the model.

In the book pointed out by Matt, Date explicitly states he's not saying ORDER BY is not useful, just that it doesn't return a relation and thus it's not included in the algebra.

However a language based on the relational model, like Tutorial D, can include such an operator. To be double sure, I checked on The Third Manifesto V2 and a LOAD operator with an ORDER specification is defined in the context of the "special cased" support for arrays.

You can see a couple of paragraphs if you go here http://books.google.it/books?id=X85QAAAAMAAJ&dq=editions... and search for ORDER and LOAD (page 118).

TBH I'm not even sure it's a good idea to introduce arrays for ordering, but, anyway, back to the quota queries: we agree that regular aggregation operators are enough although the query becomes very complex.

In the same book referenced by Matt there's an exercise (7.14) showing how to do a quota query and you can see that even in Tutorial D it's complex.

However, in the solution, Date & Darwen also propose something else: to add a more specific RANK operator which is really just syntactic sugar to simplify this kind of queries. With the important difference, compared to ORDER, that it still returns a relation and not an ordered sequence of tuples.

Unfortunately the whole solution to the excercise is not available through Google Books preview, and the operator is formally defined elsewhere, but you can see how such RANK operator would work here http://books.google.it/books?id=WuZGD5tBfMwC&lpg=PA163&#...

Just a final comment about Date being reluctant to analyze the matter, unfortunately his work is disseminated in a lot of books (and he changed his position on quite several matters throughout the years).

I love the "SQL and Relational Theory" one but, having red all of his books, I would be hesitant to suggest it unless one already knows Date. I think the latest edition (8th) of "An Introduction to Database Systems" is still the best book to start with.

Re: OrmHate

#119

Post by MattRogish » Tue, Jun 26, 2012, 10:56 PM UTC

xi wrote: post #116

MattRogish wrote: post #115

Earlier quoted context omitted.

It's been a loooong time since I wrote any longhand Relational Algebra, so I'll cheat and use SQL. All of this can be done pretty easily with relational algebra primitives. And we can make up any operator we want as long as it uses a primitive, so (to save typing I created a view, but you could copy-pasta). I added a "Title" to post because otherwise you could skip it entirely and just use the comment table twice, bu…

Thank you. I accept your answer with the note that you ignored my request to return only the latest post when there are more then one posts with the same number of comments, but it's not hard to adapt you query to satisfy this requirement. However you can't do the same trick if I ask you to return the top 3 posts with the largest number of comments; or, to make the query more realistic, ask you to return the percenta…

(Someone figured out LIMIT for relational algebra, and ORDER can be coerced to relational if you define a bunch of edge cases. I don't think it's fair to suggest that a RDBMS can't/shouldn't/wouldn't do LIMIT or ORDER).

I agree, relational algebra sucks to code in. That wasn't the point of it, of course. Codd's goal was to prove as long as your language is reducible to relational algebra, you're relational. And with that, you get all the side benefits.

I'm not so sure Date et al are advocating coding purely in RA. At least, I've never heard them say that. Their "Tutorial D", as the name implies, is for educational and specification purposes more than a useable implementation. Again, if you can make your awesome language reducible to "Tutorial D", you get all the nice benefits of the True RDBMS, which is pretty awesome.

Re: OrmHate

#120

Post by xi » Tue, Jun 26, 2012, 11:50 PM UTC

cico71 wrote: post #118

xi wrote: post #116

Earlier quoted context omitted.

Thank you. I accept your answer with the note that you ignored my request to return only the latest post when there are more then one posts with the same number of comments, but it's not hard to adapt you query to satisfy this requirement. However you can't do the same trick if I ask you to return the top 3 posts with the largest number of comments; or, to make the query more realistic, ask you to return the percenta…

I was about to prepare the query without ties and yes it is a lot harder than necessary and, more important IMO, much less readable/intuitive for people than have to maintain the code and that is exactly why different SQL dialects introduced ORDER BY/LIMIT/OFFSET/TOP/RANK etc. But let me be a little bit picky about this and Date's view on ORDER BY. The relational model deals just with the algebra/calculus without get…

I was about to prepare the query without ties and yes it is a lot harder than necessary and, more important IMO, much less readable/intuitive for people than have to maintain the code and that is exactly why different SQL dialects introduced ORDER BY/LIMIT/OFFSET/TOP/RANK etc.

Very good point.

In the book pointed out by Matt, Date explicitly states he's not saying ORDER BY is not useful, just that it doesn't return a relation and thus it's not included in the algebra.

My biggest gripe about ORDER BY, LIMIT and relational model is the fact that while Date and others made some attempts to express these operations in terms of relational algebra, they never (AFAIK) tried to do the opposite: alter the relational query model to naturally support them. It's not hard: just replace sets with sequences or arrays. It will gives you natural ORDER and SLICE operators as well as new aggregates FIRST, LAST, NTH. It solves duplicates without having to introduce bags, gives windowing functions for free and probably better represents how modern RDBMS interpret a query. Another hint why sequences may work better than sets is the fact that regular set operations such as INTERSECT and UNION (as opposed to UNION ALL, which becomes concatenation) are so rarely used in real-world queries.

I'm not even arguing that this is a good approach, but I think it deserves some discussion and it appears they never even thought of a possibility of changing the model treating it not as an instrument, but as a sacred scripture.