OrmHate

A relational DBMS is simply predicate logic applied to set theory[0]. The DBMS part offers logical/physical data independence, which is amazing, and no one has actually implemented that (NoSQL, neither!).

SQL has never been particularly awesome way to interact with relational database management systems; SQL isn't even strictly relational: SQL tables are bags (multisets), not sets[1].

The Rel project (http://dbappbuilder.sourceforge.net/) is probably the closest thing to a "real" RDBMS. It also implements CJ Date & Hugh Darwin's "Tutorial D" language, a truly relational language used to work with a RDBMS.

What I find most ironic with the whole "Relational / Object" mismatch is nothing in Relational Theory prohibits objects. As a matter of fact, a RELATION[2] consists of NAMED ATTRIBUTES and DOMAINS.

Domains are basically DATATYPES and OPERATIONS that can be performed on them[3]. Although the notion of CLASSES (in OO land) wouldn't map 100%, much of what you're interested in doing with Objects can and should be directly represented in a truly relational DBMS[4].

In a traditional MVC style framework the RDBMS would consist of the entire Model layer; thus the "Object/Relational Mismatch" disappears!

-- Matt

Refs:

0. http://www.seas.upenn.edu/~zives/03f/cis550/codd.pdf

1. A bag can have duplicate rows SQL does not require that a table have keys NULL introduces three-valued logic

2. http://en.wikipedia.org/wiki/Relation_(database)

3. http://www.dbdebunk.com/page/page/627014.htm

4. http://www.dbdebunk.com/page/page/1717959.htm

Why? Stored procedures are nothing more than a bolted-on way to store SQL queries on the database server. Yes, you've create an "API" and an "abstraction" but you've done nothing you couldn't have done in any other language.

> The stored procedures thus create an API

I consider stored procedures to be little more than a shitty "API builder" thrown in for free with your database.

* The programming language generally sucks

* Passing in complex data always sucks

* Source control is non-existent (or sucks)

You've probably had to write an API layer within your code anyway, to deal with the remaining logic requirements, why not store those queries within that layer? Or put them in a further API layer within your own source code?

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 fixed this problem by adding many non-relational constructs, but but without any sense of consistency or direction.

I also strongly disagree that ORDER BY and LIMIT/OFFSET are presentational operations since I often use them not only for wrapping the outer SELECT, but also within correlated subqueries.

To show some proof, here are a few queries which are hard or impossible to express in relational algebra:

1. Show the blog post with the largest number of comments [^].

2. Show the tags associated with the blog post with the largest number of comments.

3. For each blog category, show the 3 top blog posts by the number of comments.

[^] If more than one exist, pick the latest.

NULL is a completely different beast and this is the only real thing one can consider problematic.

I think NULL is only hard because relational model is a wrong way to look at the data. If you see an entity attribute not as a column of a tuple, but as a function from an entity set to some value domain, the fact that the attribute is nullable just means that the function is not total. There is a well developed mathematical apparatus for partial functions, in which NULL becomes a bottom value injected to the value domain, and tri-valued logic is simply a monotonic extension of regular Boolean operators.

What you have described would be considered, by many ORM critics, to be ORM.

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 direct involvement and from successors like Date, Darwin, and Pascal. Codd wasn't an all-seeing, all-knowing data-management demi-god - but his general theory of relational database management and the core tenants are still super awesome. Extensions to it, as long as they don't violate the RM, are just as valid as Codd's original work.

That means #1 is totally relational (as an aside, you don't need ORDER BY or LIMIT for it either). Indeed, relational algebra supports aggregations (http://en.wikipedia.org/wiki/Relational_algebra#Aggregation).

It's important to separate the query language from core RDBMS theory, as the two are orthogonal. Codd suggested Relational Algebra as a reference language but never intended for it to be the only way to communicate with a RDBMS.

See CJ Date's excellent discussion on ORDER(BY): http://books.google.com/books?id=WuZGD5tBfMwC&lpg=PA163&...

EDIT: It even seems folks have figured out how to make "LIMIT" relational operator: http://stackoverflow.com/questions/10229535/relational-algeb...

I have not read the paper, so I cannot discuss the validity of the approach.