More ColdFusion 9 and working with Legacy Databases Part II

Tags: Coldfusion, CFORM

As with my previous blog, there are some more things that we as a developer need to be very weary off. One of these is the relationship between tables where one table can have a many relationship with another.

While trying to convert some code over to ColdFusion 9, I came across this interesting problem that at the moment I can't solve. It is maybe something trivial, but the fact that it doesn't behave the way it should is not as trivial. This is something to take into consideration when developing with ColdFusion 9 and the CF-ORM feature.

The first thing that I am going to look at is these two tables, one is page and one is space. A space has a one-to-many relationship with page, and the database is a mysql database that is not able to use foreign keys. So if you are not familiar with this, then this is what we call a MyISAM database, and ColdFusion has problems with relationships with databases, and tables that are not capable of handling foreign keys.

So lets look at the first table, Space.

space

The second table is just as simple, and this is our page table.

page

Ok, so lets look at writing the ColdFusion code to get this mapped into our persistent components. The page component should look like this.

component persistent="true" output="false" {
   property name="id" ormtype="int" unique="true" fieldtype="id";
   property name="name" ormtype="string";
   property name="space" ormtype="string";
}

And the space component will look like this, however there is a problem with and I'll discuss that a bit further on.

component persistent="true" {
   property name="id" ormtype="int" unique="true" fieldtype="id";
   property name="unique_url" ormtype="string";
   property name="name" ormtype="string";
   property name="page" fieldtype="one-to-many" cfc="page" fkcolumn="space_id";
}

Now as I explained earlier ColdFusion 9 ORM has problems with one-to-many relationships when the table is doesn't handle foreign keys, in an ideal situation or a table that does handle the foreign keys we would not need to use the fkcolumn. In another language that does use ORM and hibernate, it doesn't care or is ultra smart when mapping this out for us. But ColdFusion must have this attribute in this scenario, which is wrong as far as I am concerned.

page2Ok so now that we now what we need to do with ColdFusion, there is another problem as I stated earlier. And that is with the fkcolumn attribute itself, now as I write this I am still not very clear how to fix this as yet, but from the image you will see what ColdFusion does. And this is also a major problem with ColdFusion.

Now the question I would be asking is where is the extra field space coming from? And why can't this be mapped correctly!

Now ideally if ColdFusion could handle mapping of tables correctly, we should only need to have code that looks like the below code. And ColdFusion would be smart enough to know what type of database it is, and how to act accordingly.

component persistent="true" {
   property name="id" ormtype="int" unique="true" fieldtype="id";
   property name="unique_url" ormtype="string";
   property name="name" ormtype="string";
   property name="page" fieldtype="one-to-many" cfc="page";
}

So even though we can still have fun with the ORM feature of ColdFusion 9, please be aware that there are still some areas that need attention to detail. Especially when you want to work with legacy databases, so be warned and hopefully lets all push for Adobe to fix this problem as quickly as they can.


TweetBacks


Comments (Moderated) | Add Comment

  • Rupesh Kumar's Gravatar ColdFusion ORM *does* generate the fkcolumn name but only if the table does not exist. And that is completely fair. When the tables are getting generated by ColdFusion/Hibernate, it can freely decide on the column name and the tables will get generated fine with a correct relationship established.

    However, if the table exists, the relationship has to be established using foreign key columns that are there in the table. If the foreign key constraint is not defined in the table, you *have* to tell ColdFusion which column is the fkcolumn. How else can ColdFusion know which column to use as the fkcolumn? 

    What you are asking Adobe to do is to follow some convention to look for the fkcolumn name with a certain pattern. How do you decide what is that convention? Grails follows "Programming by Convention" but ColdFusion does not. So let's not compare it that way.

    Since you are talking about existing tables without any constraints, you need to specify fkcolumn. I don't see that as a BUG at all.
    # Posted By Rupesh Kumar | 3/3/10 6:28 AM
  • Andrew Scott's Gravatar Rupesh,

    You are missing the point. In this example the database exists, it has FK's and it has already got data in it.

    No matter what I do in ColdFusion, I can't get the relationship fields to map correctly. ColdFusion will always generate another field.

    I am sorry this is a bug, if I can't get it to map to an existing database. Then it is a bug pure and simple.
    # Posted By Andrew Scott | 3/3/10 4:29 PM
  • Rupesh Kumar's Gravatar I am not missing any point Andrew.

    There are two things you are talking about.

    1. You say there is an extra column 'space' getting added in the page table and you are wondering where this column is coming from. Seeing the page component, one can clearly see that you are adding a property called 'space' whose ormtype is 'string'. Since you have asked ColdFusion to update the schema, ColdFusion adds the 'space' column. So in a way you are asking ColdFusion to add a space column in the page table and when it gets added, you claim that it is a bug.

    2. You say that property definition

      property name="page" fieldtype="one-to-many" cfc="page";

    should have been sufficient. It would have been sufficient if the table did not exist or there was a fk constraint defined in the table. Since you say that your tables exist but are not capable of handling fk constraint, coldfusion can no way figure out which column should be used for relationship. You consider this as a bug but all you need to do in this case, is to specify the fkcolumn attribute as 'space_id'.

    # Posted By Rupesh Kumar | 3/5/10 1:59 AM
  • Andrew Scott's Gravatar Rupesh,

    I am tired of you not reading these things correctly, you point blank have claimed that all I need to do is set the fk to space_id. Setting the Fk to this does nothing but throw an error.

    What I should have done, is set the FK to ID as ID is the primary key to the Space entity. And named the name attribute as Space_ID.

    But thats not the problem, and I have been able to duplciate this again. Where the following

    property name="space_id" fieldtype="many-to-one" cfc="Space" fkcolumn="space_id" cascade="all";

    Created in the page a field called space. How do you explain this Rupesh?

    If I am able to duplicate this again, tells me that there is a problem and I stand by the bug I raised on this.
    # Posted By Andrew Scott | 3/5/10 5:37 AM

Comments (Moderated) | Add Comment