(This is a snapshot of my old weblog. New posts and selected republished essays can be found at raganwald.com.)
Wednesday, December 27, 2006
Relational Calculus is about Relations, not Rows
A post on Enfranchised Mind (fabulous blog name!) nails the ORM problem:
What strikes me about this quote is the real value in learning new programming paradigms—like functional programming—where stuff other than Plain Old Objects (“POO”) is raised to first class status. When functions can be passed functions as parameters and return functions from functions, we start to think about manipulating the functions themselves, rather than manipulating dumb data.
The same goes for continuations, environments, super-strong typing, and a host of other ideas that have been known to the programming community for decades. Even if they aren’t a formal part of your current language, they are a valuable part of your perspective on how to use your language to solve problems.
In the case of modeling SQL, there is no reason why standard OO cannot model joins, selections, and projections with ease. All it takes is the flash of insight that it’s important to think about a relational database that way.
Update: Here’s your homework: compare and contrast the Relational Algebra with the Relational Calculus. Does SQL implement the algebra or the calculus? If your answer is Algebra, propose an OO model of the Calculus; if your answer is Calculus, propose an OO model of the Algebra.
A post on Enfranchised Mind (fabulous blog name!) nails the ORM problem:
The core of relational calculus operates on relations,aka tables. There are three main operators, all of which work on relations/tables: join, which takes two relations and creates a third relation which consists of all possible pairings of the rows of the two original relations; selection, which takes a relation and creates a new relation with only a subset of rows of the original relation; and projection, which takes a relation and creates a new relation with a subset of the columns of the old relation
These three operations are the core, the heart and soul, of relational calculus—which is the core of SQL. To faithfully model SQL we must, on some level, faithfully model the relational calculus. And this is where I think the Object Oriented programmers go astray in trying to interface to SQL. In their hurry to make things into objects, they immediately (and without fail) declare the rows to be objects—and thus miss the fact that relational calculus and thus SQL is about relations, not rows.
They’re abstracting at the wrong level.
What strikes me about this quote is the real value in learning new programming paradigms—like functional programming—where stuff other than Plain Old Objects (“POO”) is raised to first class status. When functions can be passed functions as parameters and return functions from functions, we start to think about manipulating the functions themselves, rather than manipulating dumb data.
The same goes for continuations, environments, super-strong typing, and a host of other ideas that have been known to the programming community for decades. Even if they aren’t a formal part of your current language, they are a valuable part of your perspective on how to use your language to solve problems.
In the case of modeling SQL, there is no reason why standard OO cannot model joins, selections, and projections with ease. All it takes is the flash of insight that it’s important to think about a relational database that way.
Update: Here’s your homework: compare and contrast the Relational Algebra with the Relational Calculus. Does SQL implement the algebra or the calculus? If your answer is Algebra, propose an OO model of the Calculus; if your answer is Calculus, propose an OO model of the Algebra.
Comments on “Relational Calculus is about Relations, not Rows”:
<< Home
The Holy Grail of most ORM tool is avoidance of SQL. The definition of success is to never write a single SQL.
They're not trying to preserve relational algebra or SQL through Objects, they're trying to turn relational databases into object stores.
I realize it every time I look at code that has excellent usage of objects and ORM, and it all makes sense, until you look at the underlying SQL queries and shiver.
But there's nothing fundamentally wrong with objects that functions could fix.
The problem is not with the use of objects, or abstracting at the wrong level. The problem is not even about abstraction.
It's the idea that you can unify different models (SQL, XML, remote calls, functions, etc) with one model (e.g. Java) and life will be easier.
They're not trying to preserve relational algebra or SQL through Objects, they're trying to turn relational databases into object stores.
I realize it every time I look at code that has excellent usage of objects and ORM, and it all makes sense, until you look at the underlying SQL queries and shiver.
But there's nothing fundamentally wrong with objects that functions could fix.
The problem is not with the use of objects, or abstracting at the wrong level. The problem is not even about abstraction.
It's the idea that you can unify different models (SQL, XML, remote calls, functions, etc) with one model (e.g. Java) and life will be easier.
> The Holy Grail of most ORM tool is
> avoidance of SQL.
assaf - I don't see it this way. The reason I look to ORM is that it makes possible things that aren't practical with straight SQL like having floating object models in memory and doing staged commits back to one another into memory and only finally to the database.
For this reason I've had a low opinion of activerecord in the past because it's powerful enough to get people who don't know SQL to write code (which is a bad thing - if you don't know SQL or at least a form or relational logic you shouldn't be working with databases until you've learnt it) yet doesn't have enough power to be more useful than SQL to those of us who do. It might have changed in recent versions.
> It's the idea that you can unify
> different models (SQL, XML, remote
> calls, functions, etc) with one model
> (e.g. Java) and life will be easier.
I believe sqlalchemy (python) meets the requirements outlined in the essay we're replying to of being an effective ORM that doesn't choose bad abstractions (although it makes object-per-row available if you want to use it). I disagree with your statement that there's a problem with the idea of a unifying model. Can you give more to support the case that it's a bad idea?
> avoidance of SQL.
assaf - I don't see it this way. The reason I look to ORM is that it makes possible things that aren't practical with straight SQL like having floating object models in memory and doing staged commits back to one another into memory and only finally to the database.
For this reason I've had a low opinion of activerecord in the past because it's powerful enough to get people who don't know SQL to write code (which is a bad thing - if you don't know SQL or at least a form or relational logic you shouldn't be working with databases until you've learnt it) yet doesn't have enough power to be more useful than SQL to those of us who do. It might have changed in recent versions.
> It's the idea that you can unify
> different models (SQL, XML, remote
> calls, functions, etc) with one model
> (e.g. Java) and life will be easier.
I believe sqlalchemy (python) meets the requirements outlined in the essay we're replying to of being an effective ORM that doesn't choose bad abstractions (although it makes object-per-row available if you want to use it). I disagree with your statement that there's a problem with the idea of a unifying model. Can you give more to support the case that it's a bad idea?
Craig,
Caching updates to the end of the transaction is not strictly an ORM feature, but something any persistent layer can offer.
I don't like all-out ORM solutions for a simple reason:
Employee.find_by_dept(dept_id).each { |e| e.destroy }
I worked on an open source project for mapping Java to SQL. I intended it to make the simple cases even simpler, which it does. All too well, because it quickly turned into a gateway drug. What followed where inefficient SQL statements (like the one above) and inability to use the power of SQL by developers who never had to use SQL.
I worked on an open source project for mapping Java to XML. And likewise, I saw non-extensible, overly complicated XML documents by developers who chose to say no to DOM.
I also worked on mapping Java to remote calls (SOAP as it may be), and again, saw remote APIs that are anything but loosely coupled and reusable.
Granted that are cases for creating tightly coupled remote APIs. But if the framework shields you from knowing the difference, only chance will produce the right result.
Some people believe frameworks will just get better, so let's not bother learning how to write SQL (or XML, or remote calls, or anything else that's not OO code). I'm still waiting.
I believe we need to regress the frameworks so they only work most of the time, forcing people to learn, use and understand other models for what they are.
Object oriented is good, but object oriented is not atomic types, it's not functions, and likewise it's not relational, not a content model, not a service architecture.
All these exist for a reason.
Some people believe that because you *can* use SQL (or XML, or ...) in a framework, there's nothing wrong with it. The problem is, *can* is a theory, *do* is practice. And those who don't have to, just don't.
Then there's the ancedotal evidence that people like me can throw out to the world. I know what the SQL looks like, and I know what the framework generates, and I know how to use it with discretion. See?
But only because I've been writing SQL most of my life. If I just started programming last year, I would have no clue.
No framework will rob me of my existing knowledge of SQL, but no framework will teach, or motivate me to learn, SQL either.
PS I loved your "Why Rails gets traction" post. Are you going to write more about datamagi?
Caching updates to the end of the transaction is not strictly an ORM feature, but something any persistent layer can offer.
I don't like all-out ORM solutions for a simple reason:
Employee.find_by_dept(dept_id).each { |e| e.destroy }
I worked on an open source project for mapping Java to SQL. I intended it to make the simple cases even simpler, which it does. All too well, because it quickly turned into a gateway drug. What followed where inefficient SQL statements (like the one above) and inability to use the power of SQL by developers who never had to use SQL.
I worked on an open source project for mapping Java to XML. And likewise, I saw non-extensible, overly complicated XML documents by developers who chose to say no to DOM.
I also worked on mapping Java to remote calls (SOAP as it may be), and again, saw remote APIs that are anything but loosely coupled and reusable.
Granted that are cases for creating tightly coupled remote APIs. But if the framework shields you from knowing the difference, only chance will produce the right result.
Some people believe frameworks will just get better, so let's not bother learning how to write SQL (or XML, or remote calls, or anything else that's not OO code). I'm still waiting.
I believe we need to regress the frameworks so they only work most of the time, forcing people to learn, use and understand other models for what they are.
Object oriented is good, but object oriented is not atomic types, it's not functions, and likewise it's not relational, not a content model, not a service architecture.
All these exist for a reason.
Some people believe that because you *can* use SQL (or XML, or ...) in a framework, there's nothing wrong with it. The problem is, *can* is a theory, *do* is practice. And those who don't have to, just don't.
Then there's the ancedotal evidence that people like me can throw out to the world. I know what the SQL looks like, and I know what the framework generates, and I know how to use it with discretion. See?
But only because I've been writing SQL most of my life. If I just started programming last year, I would have no clue.
No framework will rob me of my existing knowledge of SQL, but no framework will teach, or motivate me to learn, SQL either.
PS I loved your "Why Rails gets traction" post. Are you going to write more about datamagi?
Things similar to this already exist. Avi Bryant has a package for Smalltalk called ROE, there is the community project SchemeQL, and this hack in Python.
I have written a DB abstraction layer for java that doesn't really try to hide the relational roots. Here's a query in it (Note that you must create or generate a java version of structure, like so:
public class DBDATA {
public static final Schema SCHEMA = Schema.make("myAppName");
public static final Table USERS = Table.make(SCHEMA, "Users");
public static final StringField USER_NAME = StringField.makeNonNull(USERS, "name");
//etcetera
}
then you build queries like so:
Query q = new Select().from(USERS).fetch(USER_NAME).
fetch(USER_JOINED).where(Equals.make(city, USER_CITY)).join(PICS).on(Equals.make(
USER_ID, PICS_MADEBY)).fetchCount(PICS_ID).closeJoin().
orderAsc(USER_JOINED).closeQuery();
Advantages:
- db independent.
- craft your own SQL but in a way that you can later add on to the query easily (e.g. throw another .where at it, which will be ANDed with whatever was already there)
- no sql injection possible.
- type safety (Can't equals an int and a string. Yay generics!)
I'll blog when its ready for release.
NB: After running query you fetch by way of:
String s = result.get(USER_NAME);
public class DBDATA {
public static final Schema SCHEMA = Schema.make("myAppName");
public static final Table USERS = Table.make(SCHEMA, "Users");
public static final StringField USER_NAME = StringField.makeNonNull(USERS, "name");
//etcetera
}
then you build queries like so:
Query q = new Select().from(USERS).fetch(USER_NAME).
fetch(USER_JOINED).where(Equals.make(city, USER_CITY)).join(PICS).on(Equals.make(
USER_ID, PICS_MADEBY)).fetchCount(PICS_ID).closeJoin().
orderAsc(USER_JOINED).closeQuery();
Advantages:
- db independent.
- craft your own SQL but in a way that you can later add on to the query easily (e.g. throw another .where at it, which will be ANDed with whatever was already there)
- no sql injection possible.
- type safety (Can't equals an int and a string. Yay generics!)
I'll blog when its ready for release.
NB: After running query you fetch by way of:
String s = result.get(USER_NAME);
This is blog admin over at EnfranchisedMind. This post was actually written by my friend, BHurt, not by me. How'd you stumble across it, anyway?
So... ORM wrapping SQL wrapping Storage+Indexes wrapping just an old dumb data.
May be solving problems with just an old dumb data alone is not so bad?
May be solving problems with just an old dumb data alone is not so bad?
<< Home
