ColdFusion ORM creates wrong data type

Question

Database: MySql 5.1.47 on OS X

ORM Settings in Application.cfc:

this.ormEnabled = true; this.ormsettings = { autogenmap = true, dbCreate = application.dbCreate, automanageSession = true, datasource = application.dsn, logSQL = application.logSQL, sqlScript = application.sqlScript };

News.cfc

/**
* These are the news items
* @persistent true
* @accessors true
* @output false
* @entityname "News"
* @table news
*/

component
{
 property name="NewsId" type="string" fieldtype="id" ormtype="integer" generator="native" generated="insert";
 property name="Teaser" type="string" sqltype="varchar(200)";
 property name="Story" type="string" sqltype="varchar(500)";
 property name="ProductLineId" type="numeric" sqltype="int" ormtype="int" fieldtype="many-to-one" cfc="ProductLine" fkcolumn="ProductLineId" foreignkeyname="fk_productline_news";

}

ProductLine.cfc

/**
* @persistent true
* @accessors true
* @output false
* @table productline
*/

component
{
 property name="ProductLineId" sqltype="int" fieldtype="id" ;
 property name="Label" type="string" sqltype="varchar(50)";
}

Debug output from ORMReload()

[localhost]:10/05 21:32:00 [jrpp-70] HIBERNATE DEBUG - 
[localhost]:    create table news (
[localhost]:        NewsId integer not null auto_increment,
[localhost]:        Teaser varchar(200),
[localhost]:        Story varchar(500),
[localhost]:        **ProductLineId varchar(255)**,
[localhost]:        primary key (NewsId)
[localhost]:    )
[localhost]:10/05 21:32:00 [jrpp-70] HIBERNATE DEBUG - 
[localhost]:    create table productline (
[localhost]:        ProductLineId int not null,
[localhost]:        Label varchar(50),
[localhost]:        primary key (ProductLineId)
[localhost]:    )
[localhost]:10/05 21:32:01 [jrpp-70] HIBERNATE DEBUG - 
[localhost]:    alter table news 
[localhost]:        add index fk_productline_news (ProductLineId), 
[localhost]:        add constraint fk_productline_news 
[localhost]:        foreign key (ProductLineId) 
[localhost]:        references productline (ProductLineId)

The db creation fails when it attempts to create the foreign key relationship. Notice that the field in news is a varchar(255). Where did that come from? I tried to set it up as an integer in every place I could find but it always gets generated as a varchar. I think that is why the relationship fails since the two fields are different data types.

What am I doing wrong?

Answer 1

try this in News.cfc instead:

property name="productLine" fieldtype="many-to-one" cfc="ProductLine" fkcolumn="ProductLineID" foreignkeyname="fk_productline_news"

Answer 2

Don't you need an ormtype on your ProductLineID property in ProductLine.cfc? And I noticed you have "ormtype=int" in one place, and ormtype=integer in another.

Answer 3

Here are the steps I took to finally get this working correctly.

1. Be careful of tablename case with ColdFusion/MySql/OS X. It can really kill orm. What happens is that if the wrong case treatment is used in configuring MySql, Orm will drop a table but then be unable to recreate it because MySql still thinks the table exists but with a different case. This can be particularly frustrating as the MySql tools will not actually display the table with the wrong case, but you can still query it. This is a known issue with the MySql team. My advice is to just supply all lowercase names for your tables and to set your MySql table case configuration option to 1 (Store in lowercase, case insensitive). That seemed to work for me.

2. Make sure you set your dialect orm option to "MySQLwithInnoDb"

3. Set ormtype on all keys.

4. Stick with int or integer and use it consistently. I went with integer.

5. Occasionally restart everything/reboot.

6. I took the sqltype off of the foreign key reference in News.cfc

After I did all that, it finally started working as expected.

Here is my final News.cfc

/**
* Theser are the news items on the home page.
* @persistent true
* @accessors true
* @output false
* @entityname "News"
* @table news
*/

component
{
    property name="NewsId" type="string" fieldtype="id" ormtype="integer" generator="native" generated="insert";
    property name="Teaser" type="string" sqltype="varchar(200)";
    property name="Story" type="string" sqltype="varchar(500)";
    property name="ProductLine" fieldtype="many-to-one" cfc="ProductLine" ormtype="integer" fkcolumn="ProductLineId" foreignkeyname="fk_productline_news";

}