ColdFusion 9 ORM bug with composite keys how did this get missed

Tags: Coldfusion, ColdFusion 9, CFORM

Well I have spent a good 2 days trying to work this problem out, to only find that this is not a problem with code that I am writing. It turns out that this applies to both legacy and new databases, and for the life of me I can not understand why this is a problem in ColdFusion itself.

Now before I go into the actual problem, let me first say that I came across a multitude of bugs here, some have been around since ColdFusion 8 days or even longer. I would really like to know how to get around this one because it brings the development of this project on a legacy database to a complete no more coding in ORM, never to go there again until fixed scenario.

So let us look at the entity in question, then I will go into a bit more detail as to the problems one by one.

The following table is to a single sign on for members to a project I have been working on.

Member.cfc
property name="MemberId" generator="identity" fieldtype="id";
property name="Space" ormtype="string" length="10" fieldtype="id";

The first thing that you will notice here is that I have composite keys, this is because the project can have multiple Spaces and a member can create themselves to anyone of these spaces, so a composite key works well here. The problem is that CFORM in its wisdom decides that you can not have an identity field with a composite key, and refuses to create this in the database as such.

So I thought lets just check that I am not going crazy, and lets remove the composite key to a single primary key.

Member.cfc
property name="MemberId" generator="identity" fieldtype="id";
property name="Space" ormtype="string" length="10";

In this example I am creating the database from scratch so I am going to delete this table from the database, and rerun the application with a new application name. Now after we have run the code we can now see that in the database if we introspect the properties of the primary key, we can indeed see that the primary key is setup to have an identity and incremental.

Ok so that is a problem, so I then deleted the table again and went back to the original code. This time I wrote some SQL to modify the table to apply the changes that CFORM should have applied in the first place.

Now when we try to run the code to insert some data we get a message that kind of throws us a bit.

[Macromedia][SQLServer JDBC Driver][SQLServer]Cannot insert explicit value for identity column in table 'Member' when IDENTITY_INSERT is set to OFF.

Now this is what made it very clear that this is a bug within CFORM, because when we go back and look at the database the table is indeed still set to have identity and all the other properties that go along with it are good. So this actually means that CFORM or hibernate is explicitly turning this off.

To this date I have not found a work around for this, and I was actually working on an existing database which means that the redevelopment of this with ORM is not going to happen.

I would also like to mention that during this development I also came across another bug that seems very strange, and I had reported this some time ago and had completely forgotten about it until I tried to Google for a solution, but the following code will never work either in script of tags.

catch(Any e) {
   if(e.message contains 'Violation') {
      writeDump(e);
      abort;
   }
}

Now I know that many don't tend to use this method of capturing an exception, but I rely on this method heavily and in this case I wanted to know if there was a violation of the key so that I could notify and report this back to the user. And if we modify this code slightly so that it looks like this instead.

catch(Any e) {
      writeDump(e);
      abort;
}

You can see that it does indeed have the word Violation in it, and again another work around that seems to be impossible to find that will be sufficient for this applications workflow.


TweetBacks

ColdFusion 9 ORM bug with composite keys how did this get missed - http://t.co/5liSALq Aug 21, 2011
ColdFusion 9 ORM bug with composite keys how did this get missed http://t.co/U4CcHSU Aug 21, 2011

Comments (Moderated) | Add Comment

  • Big Mad Kev's Gravatar Reading the Documentation:
    http://help.adobe.com/en_US/ColdFusion/9.0/Develop...

    "For a composite key, the generator should always be "assigned"."

    Maybe that is the problem?
    # Posted By Big Mad Kev | 8/21/11 8:08 AM
  • Andrew Scott's Gravatar If a database can accept it then the ORM should be capable of it too.
    # Posted By Andrew Scott | 8/21/11 8:16 AM
  • Stephen Moretti's Gravatar I realise that you are dealing with a legacy database, but can't you just change the database to the way it should be? Primary key identity and foreign key.

    If one part of your composite key is an identity you can never get a key violation, so the composition becomes redundant. All that the composition is providing is an index comprising the primary identifier and the foreign key.

    Splitting your composition isn't changing the structure, only the way the indexes are created. This would also make it possible to use native ORM techniques. Otherwise you're looking at having to create custom interceptors, generators and classes for the key at the java level in hibernate (identity keys as part of a composite key is not natively supported).
    # Posted By Stephen Moretti | 8/21/11 9:40 AM
  • Andrew Scott's Gravatar Stephen, I am not sure I follow what you are saying there, They are composite keys, not foreign keys. Why would I change a legacy database structure that is already working, and add a foreign key to something that is not a foreign key?
    # Posted By Andrew Scott | 8/21/11 9:51 AM
  • Big Mad Kev's Gravatar So there isn't a bug in the CF9 Orm as it's document that you should have generator set to "assigned".

    Does changing your code fix the issue DB wise?
    # Posted By Big Mad Kev | 8/21/11 12:47 PM
  • Andrew Scott's Gravatar One could argue that it is by definition not a bug, but an oversight perhaps.

    No it doesn't, the database is set so that MemberId is an identity. Which means that I will not be able to write any ORM for this, and it will have to remain as normal SQL.
    # Posted By Andrew Scott | 8/21/11 1:52 PM
  • Stephen Moretti's Gravatar Ah right slight misunderstanding on my part. I assumed that "space" would be a reference to some other data, but I guess you're saying that. the data in "space" could equally be "asdasd" or "zebra". Both are meaningless to the application, but mean something to the user.

    If that is the case, then ignore that I called "space"a foreign key, but everything else still stands. You can never get a key violation from this composition because every memberid is unique. "space" really has no business being part of the record key. Indexed maybe, but not part of the composition. The composite key is slowing things down and any relationships only need reference the memberid and relationships using this composition will be slow and majorly over complicated.

    If you're refactoring your application, then you should also look at the database. You gain nothing but pain from keeping this composite key and loose nothing by changing the key to only use the memberid identity field as the sole primary key and "space" as a simple indexed column. Fix the problem with the database design rather than complain that hibernate/cform has a bug when it doesn't support a broken database design.

    I hope that makes more sense.

    The catch/try bug is annoying though.
    # Posted By Stephen Moretti | 8/21/11 7:19 PM
  • Andrew Scott's Gravatar Stephen, I agree the database can seriously use an overhaul, no question or doubt about it. Still the database allows for this strange composite key, and from what I can tell something is specifically switching the identity of.

    So whether the database can use this overhaul or not, the question still remains why is CFORM not allowing this?
    # Posted By Andrew Scott | 8/21/11 7:50 PM
  • Big Mad Kev's Gravatar I think issue here isn't Hibernate or CFORM it's the database is incorrectly designed.

    I'd have to agree with this:

    http://www.coderanch.com/t/217156/ORM/java/hiberna...

    "With composite keys your only key generation strategy is "assigned". Neither Hibernate nor the database can know anything about the business logic you require to generate your composite key.

    That aside, if your composite key contains an identity field (which will have all the properties of a surrogate key) why are you using a composite key at all?"

    So you can't blame Hibernate or CFORM for the bad architecture of your database.
    # Posted By Big Mad Kev | 8/22/11 2:02 AM
  • Andrew Scott's Gravatar I agree with that design, but it leaves the question as to why CFORM can't map to how the database is setup.

    Let us forget about whether we consider it a good design practice or not, the point is that databases can be setup in this manner. This means that CFORM if such a database does exist and they do, then CFORM shouldn't take it upon itself to not allow it.

    That is the issue at hand and nothing more or less.
    # Posted By Andrew Scott | 8/22/11 2:34 AM
  • Big Mad Kev's Gravatar But the issue here isn't CFORM it's hibernate so bashing CFORM is incorrect!

    You have to take Good Design in place because something like Hibernate has to be trusted by DBA's when you're in a locked down environment and they have to be happy that ORM isn't going to cause issues.

    The fact is ORM with a legacy database will always be difficult, it's not the fault of the ORM implementation its the database.
    # Posted By Big Mad Kev | 8/22/11 3:00 AM
  • Andrew Scott's Gravatar Someone can correct me if I am wrong here, but the error message is a Macromedia JDBC error, that means this is done from CFORM. From what I can tell it is up to the provider who implements hibernate and creates the classes to handle this stuff.

    I will be happy to be proven wrong, but a CFORM error message is not a hibernate error message.
    # Posted By Andrew Scott | 8/22/11 3:12 AM
  • Big Mad Kev's Gravatar Hibernate will generate the SQL and then CF will submit this and thus why you get the Macromedia Driver issue. The SQL created is the IDEAL SQL that you should be using but because you're database is badly designed the error occurs.

    So no it's not a CFORM issue. Fundamentally it comes down to bad DB design and you can't blame any ORM implementation be it CFORM or Hibernate itself.
    # Posted By Big Mad Kev | 8/22/11 4:14 AM
  • Andrew Scott's Gravatar Kev, I am going to agree to disagree with you. Bad design or not on the database level, doesn't mean ORM should not support it. You can keep telling me it is a bad DB design, and I will continue to agree with you.

    However I will not change my mind, the DB does allow for it and so should ORM, regardless of whether you agree with that or not.
    # Posted By Andrew Scott | 8/22/11 4:25 AM
  • Big Mad Kev's Gravatar The fact is ORM should only generate good design SQL and in this case it's not. So the bottom line is for your use case ORM is not the correct tool for the job.

    So again you can't blame CFORM or Hibernate or any ORM none will let you do it this way.

    The DB may allow you to have bad design, but ORM won't end off.
    # Posted By Big Mad Kev | 8/22/11 4:29 AM
  • Andrew Scott's Gravatar Fair enough
    # Posted By Andrew Scott | 8/22/11 4:58 AM

Comments (Moderated) | Add Comment