Learning ColdFusion 9: ORM Inheritance Mapping

Posted August 21, 2009 at 10:21 AM

Tags: ColdFusion

Yesterday, in the comments to my blog post on using EntityNew() vs. the NEW operator, Robert Rawlins asked me about persisting inherited objects with ColdFusion 9's new Object-Relational Mapping (ORM) functionality. This is something that I've looked at, but never really tried; as such, I figured there was no time like the present. When it comes to dealing with inheritance-based objects, there are three methods available, but to me, only two of them seem relevant; these are the two methods in which the sub-classes are stored in their own tables. Between these two methods of persistence, the only difference is that one uses a discriminator (type-differentiator in the base table) and one does not.

While using a discriminator value seems like a very natural thing from a database point of view, I really want to start thinking in terms of objects, not databases. As such, I think we should first explore the inheritance method that does not have a discriminator. For the following demos, I am going to use the domain of a rental store, such as Blockbuster, that can rent both DVDs and Games. And since both DVDs and Games can be rented, in this domain, each item can been seen as a special sub-class of rental:

In the above UML(ish) diagram, the GameRental and the DVDRental domain objects both extend the core Rental object. Each of the sub-classes contains a foreign key reference to the primary key (ID) of the Rental record as well as its own, specialized information. Keeping this design in mind, let's take a look at our ColdFusion components. First, we will look at the base class, Rental.cfc:

Rental.cfc

 Launch code in new window » Download code as text file »

The Rental.cfc ColdFusion component contains the core rental information, but does not have any information about its sub-classes. As far as the ORM system is concerned, the data contains in this class will be persisted in the "rental" table.

Now, let's take a look at the sub-classes. First, we'll look at the GameRental.cfc:

GameRental.cfc (Extends Rental.cfc)

 Launch code in new window » Download code as text file »

As you can see here, just as with any inheritance-based object, the GameRental.cfc class extends the base Rental.cfc class. Because this is being used by the ORM system, however, we have to provide a bit of additional information. The Table attribute tells the ORM system where to persist the data. The JoinColumn attribute tells the ORM system how to relate the sub-class table record back to the base class table record. So, going by the code above, our "game_rental" table will have a "rental_id" column which is a foreign key that references the primary key column, "id," of the "rental" table.

The DVDRental.cfc object works in exactly the same way as the GameRental.cfc:

DVDRental.cfc (Extends Rental.cfc)

 Launch code in new window » Download code as text file »

Again, this sub-class acts just like the previous one, extending the Rental.cfc class using a foreign key column.

When the ColdFusion ORM system rebuilds the database, using these domain models, we get the following database tables (this was done on a Derby Embedded database):

rental

• id (integer 10) required
• sku (varchar 30) required
• name (varchar 50) required

game_rental

• rental_id (integer 10) required
• game_system (varchar 50) required
• rating (varchar 5) required

dvd_rental

• rental_id (integer 10) required
• type (varchar 20) required
• rating (varchar 5) required

As you can see, each sub-class database table contains a foreign key (rental_id) that will be used to associate it back to the base table (rental).

Now that we have our domain model mapped out and our database built, let's finally put it to some use. In this demo, we will create and persist one instance of each sub-class:

 Launch code in new window » Download code as text file »

Notice that when we create the sub-classes, we are setting properties that were defined in both the sub-class as well as the base class. When we run this code, we get the following output:

When we create the sub-classes and use them, there's nothing that cool going on - they act like normal objects. But, look at what we get when we start loading objects based on entity names. Notice that we can load all objects using the base class entity name or, we can load collections of specific sub-classes uses the sub-class entity name. Even cooler than that, though, when we load objects based on the base class entity name, ColdFusion's ORM system knows how to load the sub-classes even though the base class does not contain any information about the sub-classes! Sweet-ass-sweet!

So that's inheritance without a discriminator. Now, let's look at using a discriminator. If you look at the database tables created above, you'll notice that the base class table, "rental," does not lend any insight into which sub-classes might be extending it. With a discriminator, we can add a "Type" column to the base class table that will tell us which sub-class (if any) is extending the record. In order to do this, all we have to do is add a DiscriminatorColumn attribute to our base class and a DiscriminatorValue attribute to our sub-classes.

Rental.cfc

 Launch code in new window » Download code as text file »

Notice now that our Rental.cfc CFComponent tag has a DiscriminatorColumn attribute with value, "rental_type." This will tell ColdFusion 9's ORM system to add the column, "rental_type" to the base table, "rental." And, in fact, when we have the ORM system rebuild our database, we get the following rental table:

rental

• id (integer 10) required
• rental_type (varchar 255) required
• sku (varcahr 30) required
• name (varchar 50) required

That new "rental_type" column gets populated based on the type of sub-class that extends the Rental.cfc object. And, the value that gets put in that column is defined by the DiscriminatorValue attribute in the sub-class CFComponent tags:

GameRental.cfc (Extends Rental.cfc)

 Launch code in new window » Download code as text file »

Here, our CFComponent tag has the DiscriminatorValue, "game."

DVDRental.cfc (Extends Rental.cfc)

 Launch code in new window » Download code as text file »

Here, our CFComponent tag has the DiscriminatorValue, "dvd."

Now that we have our discriminator column and values in place, let's see how they can be used. Assuming that our previous demo has already run and populated the database, we are now going to re-query the ORM system for all of our rentals:

 Launch code in new window » Download code as text file »

When we run this code, we get the following output:

What you might notice is that this output looks exactly like the all-rentals output we got in our first demo. All of the discriminator stuff that we just added is transparent to us from an object-based view point. But, when we look at the database, you will see that our DiscriminatorColumn, "rental_type," was properly populated:

At this point, you might be asking yourself what the point of the discriminator is? After all, if we can load objects based on sub-class entity names and the discriminator type is transparent to us when using the base class entity name... well, what purpose does it serve? To be honest, I don't know enough to fully answer that question. What I can tell you is that just because we are using the ORM system in this set of examples doesn't mean that we are tied to the ORM system or all data retrieval. We could easily run a standard CFQuery tag and use the discriminator column to gather information:

 Launch code in new window » Download code as text file »

Here, we are using the DiscriminatorColumn to optimize our raw SQL statement. And, when we run this, we get the following CFOutput:

Records: 1
Name: Street Fighter

So, while it seems that the discriminator value is not readily available to us when dealing with ORM-enabled objects, it is still a good value to have in place when we need to go in and write some highly optimized SQL.

Using inheritance in ColdFusion components is a very natural and powerful thing to do; and, seeing that ColdFusion 9's new ORM functionality can map that to proper database persistence is very exciting. I am curious to know how it writes the queries to return all the base records since it would have to do a number of joins, perhaps UNION ALL'd together? I don't know enough about logging to figure that out (can someone lend some easy insight?). But, I will just assume that it is doing it very efficiently. Anyway, this is cool stuff.

One final note regarding this inheritance stuff is that the base record does not have to be sub-classed at all. Even in this context, you could create a new base entity, "Rental," populate it, and save it. If you do so, the "rental_type" discriminator column is populated with the base class entity name, "Rental," and the loaded object will not have any sub-class properties - only the core, base class properties.

Download Code Snippet ZIP File

Post Comment  |  Ask Ben  |  Other Searches  |  Print Page

• Add To Del.icio.us
• Add To Digg
• Add to Spurl.net
• Add to Wists
• Add to Simpy
• Add to Newsvine
• Add to BlinkList
• Add to Furl
• Add to reddit
• Add to FARK
• Add to BlogMarks
• Add to Yahoo! My Web
• Add to smarking
• Add to Ma.gnolia
• Add to Segnalo

Reader Comments

Nice article Ben!

Rupesh Kumar posted a great article on how to set up logging:
http://coldfused.blogspot.com/2009/07/coldfusion-orm-how-to-log-sql.html

Ben,

for logging you can follow Rupesh's blog post http://www.rupeshk.org/blog/index.php/2009/07/coldfusion-orm-how-to-log-sql/

When doing my ORM testing, I have CFBuilder tail view with my cf log open and I can see right away what is being invoked.

@John,

Thank my man. I saw Rupesh's article, but I am not sure how to use the console. Don't I have to boot up CF from the console in order to see the output there? I will re-read his article.

@João,

Ok cool. I wasn't sure if it also went to the log files. I will do some investigating after work.

Excellent stuff Ben!

Thanks a great deal for taking the time to look at this, isn't it a simple implementation!?!? All of this ORM stuff really couldn't be any simpler to implement, really VERY exciting stuff.

Your article doesn't really leave me with any unanswered questions, however, I do wonder if this works deeper than just a single level, so imagine taking your example further, whereby your dvd rentals have different types 'DVD', 'HD-DVD' and 'Blueray' could you create objects for each of those, creating a final comprised object 3 levels deep opposed to the two that you currently have? just something which crossed my mind.

HD-DVDRental -> DVDRental -> Rental

I know that's a horribly poor use case example but just wanted to explore going deeper than the single level of inheritance.

Thanks again mate, really do appreciate your thorough explanation.

Rob

Hi Ben, I log the SQL to a file. I can send you my config if you want?

@Robert,

I assume that would work. If they can extend 1 level deep, I assume they can extend N levels deep. But, that's just a guess. I am sure the SQL would get much more complicated!

@John,

That would be awesome my man!

Ben, the main reason for single-table inheritance being the generally preferred option is performance. By using a single table to hold all of the subtypes, you remove a lot of joins that have to be added when using multiple tables. When you start getting into larger sets of objects or deeper inheritance, the joins will start affecting the speed of the query.

Technically, the single-table option isn't the "best" from a database normalization point of view, since you will end up with null columns for some of your objects (for the properties that don't apply to that subtype). When disk space and database size were bigger issues, this concern was valid. Now that gigantic hard drives are available for pennies per gigabyte, the benefit of faster queries outweighs any obsession with rigid normalization rules.

So, you can do it either way. But most people go the single-table route for the speed.

@Brian,

That makes a lot of sense. And, I think I've seen things like that in databases that were more designed for reporting. But, as you are saying - in times where harddrive space is very cheap, it is becoming less and less of a concern.

One of the concerns I had about the distributed table inheritance is that it has to a bunch of joins. When getting a particular type, using one INNER JOIN is probably quite efficient; but, what about when it gets all the base records and has to join to all sub-class tables. That is the kind of thing that I "hope" is done efficiently, but would be nervous about running those queries too often.

Well, it's done as "effectively" as one can do joins on indexed foreign keys. Which is pretty efficiently, but it will never be as fast as not having to do the joins at all. ;-)

@Brian,

Right, but my curiosity is in that when you have several sub-classes, I wonder how it rounds up all the tables. In my mind I see something like this:

SELECT * FROM base b INNER JOIN subA sa
ON b.id = sa.base_id

UNION ALL

SELECT * FROM base b INNER JOIN subB sb
ON b.id = sb.base_id

UNION ALL

SELECT * FROM base b INNER JOIN subB sc
ON b.id = sc.base_id

One day, I'll figure out how to turn on my logging and actually look at the SQL that Hibernate is generating.

@Ben,

The discriminator column is available in case you are:

Working with a "legacy" database (that is, a database not designed from the standpoint of Hibernate's conventions).

Working with a "standard" database (that is, a database designed from the standpoint of Hibernate's conventions) but are extra-paranoid about data integrity and wish to impose extra elements into the schema that will help enforce data integrity from misbehaved applications and from unwary SQL-happy programmers.

If your database is being designed for a Hibernate-based project and will only be used by that Hibernate-based project, then Hibernate is perfectly happy not to use the discriminator column.

Justice

@Justice,

Ah gotcha. I also like the idea of being able to gather optimized queries that do not require a join if all you need to know is the type of JOIN that would be created.

@Brian,

Even though hard drive space is cheap and querying a single table is faster, when using a single table for sub-classes you have to be careful of modification anomalies.

Wikipedia has a good explanation of modification anomalies at http://en.wikipedia.org/wiki/Database_normalization#Free_the_database_of_modification_anomalies

I am not saying don't denormalize your database, just make sure to take all aspects into consideration.

Also from my understanding database normalization didn't have to do with disk space; but rather to do with data integrity and countering modification anomalies, while lower disk costs were a positive by-product of it.

@Ben,

Awesome article, I rather like the dumping of all base objects giving the relevant sub-class information too.

Good things to keep in mind when manually managing denormalized tables, Andrew. But Hibernate makes it very difficult (virtually impossible) to encounter modification anomalies, so I wouldn't worry about that in a Hibernate-managed setup.

@Brian,

You may be correct in saying that Hibernate handles the modification anomalies, however saying not to worry about it may not be a good idea for every situation.

In a single entry point system, letting Coldfusion and Hibernate handle the database setup maybe a valid option. However if that system has the chance of growing (which a lot of projects do) into a system that has multiple entry-points (ie: other systems, merging of databases); putting a little fore-thought in and using some database normalization (if not at least third normal form) will save you future headaches and days/weeks of re-design and data-porting.

I have had to do this on multiple occasions to someone else's database (usually in Access) that has grown too large for their design, and it costs a lot more in time and money the second time through (especially considering systems also have to be modified to handle the new database design).

This is only advice based from my personal experience, so please feel free to ignore it if you feel it is not relevant to your situation.

Thanks a lot for posting this Ben. I haven't done much playing with the CF9 ORM stuff, but for some reason I was curious about how it handles persistence of subclassed objects. Thanks (again) for doing the testing for me!

Just an FYI, Rob Kolosky logged the SQL created from using inheritance mapping and this is what he got:

SELECT
. . . . rental0_.id as id44_,
. . . . rental0_.sku as sku44_,
. . . . rental0_.name as name44_,
. . . . rental0_1_.type as type45_,
. . . . rental0_1_.rating as rating45_,
. . . . rental0_2_.game_system as game2_46_,
. . . . rental0_2_.rating as rating46_,
. . . . rental0_.rentalType as rentalType44_
from
. . . . tblRental rental0_
left outer join
. . . . tblDVDRental rental0_1_
on
. . . . rental0_.id=rental0_1_.rentalID
left outer join
. . . . tblGameRental rental0_2_
on
. . . . rental0_.id=rental0_2_.rentalID

So, it looks like the underlying SQL does not use any kind of UNION ALL'ing. Since Hibernate is such a tried and true product, I have to assume that this it the optimal way to go and that it performs much better than several UNION ALL statements???

@Ben,

I don't think this approach is more optimal on a database level, however it is probably the easiest approach to code up while giving similar results.

By this I mean: when coding any type of UNION, you have to make sure the column structure of the multiple select statements are exactly the same. This approach means you have to compare structures and align them correctly (adding nulls for columns that are not available in a statement).

However, by doing OUTER JOINs you do not really need to compare columns, the RDBMS will just add nulls if the column does not relate to that table or its JOINs. The short comings of having your data spread-out over multiple columns instead of aligned up nicely, is easily side-stepped by expecting it and adding code to the framework to handle it.

@Andrew,

Yeah, I hear what you're saying. That makes sense. I guess, there's just something in my gut that feels odd about trying to join several different tables to the same row of the root data table. But, I guess I shouldn't even concentrate on that - rather, on the niceness that ORM abstracts this whole concept away from us.

@Ben,

I know what you mean by it not feeling right and I am rather against it myself (especially for intermediate to advanced systems, like enterprise level applications).

In saying that, I can understand the purpose of ORM to simplify the processes of less data intensive applications.

@Andrew,

The purpose of an ORM like Hibernate is to abstract away the procedural database from an object-oriented application, as well as take the place of a full-fledged object-oriented data-access layer.

An ORM like Hibernate is fully capable of supporting, and fully intended for, enterprise applications backed by a normalized OLTP database. Its purpose is not to simplify data access (that bit comes for free as a side-effect). Its purpose is to abstract away the procedural database from an object-oriented application, as well as take the place of a full-fledged object-oriented data-access layer developed in-house.

Justice

@Justice,

Thanks for the insight.

Post Comment  |  Ask Ben

Post A Comment

Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.

Please review the following issues:

Author Name:
Author Email:
Author Website:
Comment:	Formatting: <strong>bold</strong> <em>italic<em>
	Remember my information
	Subscribe to entry *
	Send me a copy of this comment (as part of the comment thread)

 

* If you subscribe, any new posts to this thread will be sent to your email address.