Objects, Tables and ORMs – part deux
The other day I looked at inheritance strategies for ORMs. Today I want to look at some of the other times where there isn't necessarily a 1:1 relationship between objects and database tables . . .
Objects, Tables and ORMs
One of the quickest tests of an ORM is to look at how flexible it is when mapping objects to tables. Here are some of the possible requirements to consider . . .
What I Need From My ORM/Data Access Layer
The ideal ORM is dependent on your exact use case. From Transfer and Reactor to SQL Alchemy and Hibernate, there are many ORM systems available of varying complexity. Here's what I need from my ORM system. I'd appreciate any input on features I haven't mentioned along with a sense of the problem they solve. This spec works for me today, but I'm sure there are plenty of new use cases just lurking out of site that I haven't thought through yet :-> . . .
Approaches to ORM . . .
Phillip Senn posted a link to Paul Nielsons Nordic ORM for .NET, and I just came across a nice overview of SQL Alchemy - a Python ORM. Always interesting to see different approaches to these common problems . . .
Microsoft Gets Into ORM
It appears that with Project Jasper, Microsoft is getting into ORM. Right now this will only work with VB9 and IronPython (and possibly Managed Javascript and Iron Ruby) due to the dependence on late binding so it isn't exactly going to take the commercial world by storm just yet, but it'll be interesting to check out their API and to see how this ends up working down the line with the Dynamic Language Runtime.
Interesting times!
Rethinking the Data Access Layer
There are a number of approaches to implementing a data access layer. You're got procedural tools like Steve Bryants excellent DataMgr and ORM systems like the extremely popular Transfer from Mark Mandell and Reactor from Doug Hughes.
In this short series I want to examine exactly what I need out of a DAL and how I plan to approach solving this problem in a somewhat different way that Steve, Mark or Doug have taken . . .
A DSL for SQL
Written in Ruby, but looks interesting. Will have to compare to Mark Mandells TQL (part of Transfer) and Doug Hughes' query object API and see what I can learn . . .
Bunch of great ORM Links
I was digging around and found a great set of ORM links. Mostly for the .NET crowd so some of the articles are covering basics like the benefits of ORM over SPs, but there were a couple of nice articles.
In particular, one pointed out that you can't escape the complexity. I've programmed basic SQL for years without ever understanding how queries are executed and how to optimize them, but you'll be lucky to use an ORM for a week without getting into trouble unless you understand how the ORM operates and the implications of your choices in terms of the queries generated. This goes even for low traffic sites. It only takes a few users calling a page where you have an n+1 query problem with 500 records to completely ruin your day!
It includes posts on the downside of ORM (along with a good rebuttal and an example).
There is also a good post giving a sense of how much work it is to create a comprehensive ORM a la Hibernate (or in this case nHibernate), but I think Doug and Mark have shown that a modest subset of Hibernates functionality can still have value.
Also an interesting look at how you handle n+1 query problems in nHibernate.
ORM: Filtering Calculated Attributes from Filter and Order By Clauses
So, I have a Filter clause of 'Age > 35' or 'Price < 20 AND Price > 35' where Age and Price are calculated attributes that the db knows nothing about. Or I want to Order by Age or Price. How do I pull out the invalid parts of the filter or order by clause while still leaving valid SQL?
ORM: What about "WHERE" and "ORDER BY"?
The purpose of a "where" clause is to filter the returned recordset based on field values. The purpose of an "order by" clause is to affect the order in which they are returned. When you want to be able to describe your DAO interface “properly” (i.e. without regard to whether the filter or order by clauses relate to persisted fields), a little extra work is required.
If I want to order by FirstName, all I need to do is pick up the table name and alias. In the database the FirstName attribute might be persisted as tbl_AdminUser.FirstName or tbl_User.Horribly_long_and_bizarre_Name_for_firstname_here. To do this I need to replace |space|Attribute_Name|space| with |space|Attribute_TableSQLName.Attribute_FieldName |space|. That is pretty straightforward with just the minor inconvenience that operators such as LIKE become reserved words so you can’t have an attribute name called like. Annoying, but acceptable to me.
