dbre mysql 'Foreign key table for foreign key' 'must not be null'

[rbon]

Given the following mysql database definition for user 'sa' and no password:

-- DROP SCHEMA IF EXISTS rootest;

-- CREATE SCHEMA rootest DEFAULT CHARACTER SET utf8;

-- USE rootest;

DROP TABLE IF EXISTS TeamPlayer;
DROP TABLE IF EXISTS Player;
DROP TABLE IF EXISTS Team;
DROP TABLE IF EXISTS PlayerStatusType;
DROP TABLE IF EXISTS User;

-- =================================

CREATE TABLE User
  (
    id 	bigint auto_increment PRIMARY KEY
  )
 ENGINE = InnoDB;

-- ================================

CREATE TABLE PlayerStatusType
  (
    type  varchar(63) PRIMARY KEY
  )
 ENGINE = InnoDB;

-- ===============================

CREATE TABLE Team
  (
    id       bigint auto_increment,
    city     varchar(127)   not null,
    name     varchar(127)   not null,
    version  int,

    primary key(id),
    unique(city, name)
  )
 ENGINE = InnoDB;

-- ================================

CREATE TABLE Player
  (
    id         bigint auto_increment,
    firstName  varchar(63),
    lastName   varchar(63)   not null,
    birthdate  date          not null,
    statusType varchar(63)   not null,
    version    int,

    primary key(id),
    
    foreign key(statusType) references PlayerStatusType(type)
  )
 ENGINE = InnoDB;

-- ===================================

CREATE TABLE TeamPlayer
  (
    teamId    bigint,
    playerId  bigint,
    fromDate  date,
    toDate    date,
    version   int,

    primary key(teamId, playerId, fromDate),

    foreign key(teamId) references Team(id),
    foreign key(playerId) references Player(id)
  )
 ENGINE = InnoDB;

-- ===================================

and the following roo project scripts:

// Create project

project --topLevelPackage com.roodbtest

// Setup database

persistence setup --provider HIBERNATE --database MYSQL
database properties set --key database.url --value jdbc:mysql://localhost:3306/rootest
database properties set --key database.username --value sa

database properties list

edit persistence.xml changing hibernate.hbm2ddl.auto value to 'validate'

// database reverse engineer

///////////

database reverse engineer --schema rootest --package ~.domain --testAutomatically

the following output results:

Created SRC_MAIN_RESOURCES/dbre.xml
Updated ROOT/pom.xml
Updated SRC_MAIN_RESOURCES/META-INF/persistence.xml
Created SRC_MAIN_JAVA/com/roodbtest/domain
Created SRC_MAIN_JAVA/com/roodbtest/domain/Player.java
Created SRC_MAIN_JAVA/com/roodbtest/domain/PlayerStatusType.java
Created SRC_MAIN_JAVA/com/roodbtest/domain/Team.java
Created SRC_MAIN_JAVA/com/roodbtest/domain/TeamPlayerPK.java
Created SRC_MAIN_JAVA/com/roodbtest/domain/TeamPlayer.java
Created SRC_MAIN_JAVA/com/roodbtest/domain/User.java
Undo create SRC_MAIN_JAVA/com/roodbtest/domain/User.java
Undo create SRC_MAIN_JAVA/com/roodbtest/domain/TeamPlayer.java
Undo create SRC_MAIN_JAVA/com/roodbtest/domain/TeamPlayerPK.java
Undo create SRC_MAIN_JAVA/com/roodbtest/domain/Team.java
Undo create SRC_MAIN_JAVA/com/roodbtest/domain/PlayerStatusType.java
Undo create SRC_MAIN_JAVA/com/roodbtest/domain/Player.java
Undo create SRC_MAIN_JAVA/com/roodbtest/domain
Undo manage SRC_MAIN_RESOURCES/META-INF/persistence.xml
Undo manage ROOT/pom.xml
Undo create SRC_MAIN_RESOURCES/dbre.xml
Foreign key table for foreign key 'player_ibfk_1' in table 'Player' must not be null in determining a one-to-one relationship
Script required 2 second(s) to execute
Script execution aborted
Created SRC_MAIN_JAVA/com/roodbtest/domain
Created SRC_MAIN_JAVA/com/roodbtest/domain/Player_Roo_Entity.aj
Deleted SRC_MAIN_JAVA/com/roodbtest/domain/Player_Roo_Entity.aj

This is a very simple database structure.
Why does roo have a problem?
Is this a roo defect?
Am I missing a step or doing something incorrect?

I am using roo 1.1.4.

[Alan Stewart]

Your schema worked OK for me. No errors in the generation:

roo> database reverse engineer --schema no-schema-required --package ~.domain --testAutomatically 
Created SRC_MAIN_RESOURCES/dbre.xml
Created SRC_MAIN_JAVA/com/foo/empty/domain
Created SRC_MAIN_JAVA/com/foo/empty/domain/Player.java                                                                         
Created SRC_MAIN_JAVA/com/foo/empty/domain/PlayerStatusType.java
Created SRC_MAIN_JAVA/com/foo/empty/domain/Team.java
Created SRC_MAIN_JAVA/com/foo/empty/domain/TeamPlayerPK.java
Created SRC_MAIN_JAVA/com/foo/empty/domain/TeamPlayer.java
Created SRC_MAIN_JAVA/com/foo/empty/domain/User.java
Created SRC_TEST_JAVA/com/foo/empty/domain
Created SRC_TEST_JAVA/com/foo/empty/domain/PlayerDataOnDemand.java
Created SRC_TEST_JAVA/com/foo/empty/domain/PlayerIntegrationTest.java
Created SRC_TEST_JAVA/com/foo/empty/domain/PlayerStatusTypeDataOnDemand.java
Created SRC_TEST_JAVA/com/foo/empty/domain/PlayerStatusTypeIntegrationTest.java
Created SRC_TEST_JAVA/com/foo/empty/domain/TeamDataOnDemand.java
Created SRC_TEST_JAVA/com/foo/empty/domain/TeamIntegrationTest.java
Created SRC_TEST_JAVA/com/foo/empty/domain/TeamPlayerDataOnDemand.java
Created SRC_TEST_JAVA/com/foo/empty/domain/TeamPlayerIntegrationTest.java
Created SRC_TEST_JAVA/com/foo/empty/domain/UserDataOnDemand.java
Created SRC_TEST_JAVA/com/foo/empty/domain/UserIntegrationTest.java
Created SRC_MAIN_JAVA/com/foo/empty/domain/Player_Roo_Configurable.aj
Created SRC_MAIN_JAVA/com/foo/empty/domain/Player_Roo_Entity.aj
Created SRC_MAIN_JAVA/com/foo/empty/domain/Player_Roo_DbManaged.aj
Created SRC_MAIN_JAVA/com/foo/empty/domain/Player_Roo_ToString.aj
Created SRC_MAIN_JAVA/com/foo/empty/domain/PlayerStatusType_Roo_Configurable.aj
Created SRC_MAIN_JAVA/com/foo/empty/domain/PlayerStatusType_Roo_Entity.aj
Created SRC_MAIN_JAVA/com/foo/empty/domain/PlayerStatusType_Roo_ToString.aj
Created SRC_MAIN_JAVA/com/foo/empty/domain/Team_Roo_Configurable.aj
Created SRC_MAIN_JAVA/com/foo/empty/domain/Team_Roo_Entity.aj
Created SRC_MAIN_JAVA/com/foo/empty/domain/Team_Roo_DbManaged.aj
Created SRC_MAIN_JAVA/com/foo/empty/domain/Team_Roo_ToString.aj
Created SRC_MAIN_JAVA/com/foo/empty/domain/TeamPlayer_Roo_Configurable.aj
Created SRC_MAIN_JAVA/com/foo/empty/domain/TeamPlayer_Roo_Entity.aj
Created SRC_MAIN_JAVA/com/foo/empty/domain/TeamPlayerPK_Roo_Configurable.aj
Created SRC_MAIN_JAVA/com/foo/empty/domain/TeamPlayerPK_Roo_Identifier.aj
Created SRC_MAIN_JAVA/com/foo/empty/domain/TeamPlayerPK_Roo_Json.aj
Created SRC_MAIN_JAVA/com/foo/empty/domain/TeamPlayerPK_Roo_Serializable.aj
Created SRC_MAIN_JAVA/com/foo/empty/domain/TeamPlayer_Roo_DbManaged.aj
Created SRC_MAIN_JAVA/com/foo/empty/domain/TeamPlayer_Roo_ToString.aj
Created SRC_MAIN_JAVA/com/foo/empty/domain/User_Roo_Configurable.aj
Created SRC_MAIN_JAVA/com/foo/empty/domain/User_Roo_Entity.aj
Created SRC_MAIN_JAVA/com/foo/empty/domain/User_Roo_ToString.aj
Created SRC_TEST_JAVA/com/foo/empty/domain/UserDataOnDemand_Roo_Configurable.aj
Created SRC_TEST_JAVA/com/foo/empty/domain/UserDataOnDemand_Roo_DataOnDemand.aj
Created SRC_TEST_JAVA/com/foo/empty/domain/TeamPlayerIntegrationTest_Roo_Configurable.aj
Created SRC_TEST_JAVA/com/foo/empty/domain/TeamDataOnDemand_Roo_DataOnDemand.aj
Created SRC_TEST_JAVA/com/foo/empty/domain/PlayerStatusTypeDataOnDemand_Roo_DataOnDemand.aj
Created SRC_TEST_JAVA/com/foo/empty/domain/PlayerDataOnDemand_Roo_DataOnDemand.aj
Created SRC_TEST_JAVA/com/foo/empty/domain/TeamPlayerDataOnDemand_Roo_DataOnDemand.aj
Created SRC_TEST_JAVA/com/foo/empty/domain/TeamPlayerIntegrationTest_Roo_IntegrationTest.aj
Created SRC_TEST_JAVA/com/foo/empty/domain/PlayerStatusTypeDataOnDemand_Roo_Configurable.aj
Created SRC_TEST_JAVA/com/foo/empty/domain/TeamPlayerDataOnDemand_Roo_Configurable.aj
Created SRC_TEST_JAVA/com/foo/empty/domain/TeamIntegrationTest_Roo_Configurable.aj
Created SRC_TEST_JAVA/com/foo/empty/domain/TeamIntegrationTest_Roo_IntegrationTest.aj
Created SRC_TEST_JAVA/com/foo/empty/domain/TeamDataOnDemand_Roo_Configurable.aj
Created SRC_TEST_JAVA/com/foo/empty/domain/PlayerIntegrationTest_Roo_Configurable.aj
Created SRC_TEST_JAVA/com/foo/empty/domain/PlayerIntegrationTest_Roo_IntegrationTest.aj
Created SRC_TEST_JAVA/com/foo/empty/domain/PlayerDataOnDemand_Roo_Configurable.aj
Created SRC_TEST_JAVA/com/foo/empty/domain/PlayerStatusTypeIntegrationTest_Roo_Configurable.aj
Created SRC_TEST_JAVA/com/foo/empty/domain/PlayerStatusTypeIntegrationTest_Roo_IntegrationTest.aj
Created SRC_TEST_JAVA/com/foo/empty/domain/UserIntegrationTest_Roo_Configurable.aj
Created SRC_TEST_JAVA/com/foo/empty/domain/UserIntegrationTest_Roo_IntegrationTest.aj

Can you reproduce this every time? Can you start again with a clean empty project?

[rbon]

I have tried numerous times with no success.
Each time I exit roo and clean the directory of all files.
I have run the commands from within a script as well as individually on the command line.
I even changed the primary keys to be all BigInt. Same result every time.

I am using OS X 10.5.8, java 1.6.0_24.

[Alan Stewart]

What happens when you run the database introspect command? Do you see the XML of the schema? If so, use the --file option on this command and attach the output here

[rbon]

roo> database introspect --schema rootest

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!--WARNING: DO NOT EDIT THIS FILE. THIS FILE IS MANAGED BY SPRING ROO.-->
<database name="rootest">
    <option key="testAutomatically" value="false"/>
    <option key="includeNonPortableAttributes" value="true"/>
    <table name="Particpant">
        <column name="id" primaryKey="true" required="true" scale="0" size="19" type="-5,BIGINT"/>
        <unique name="PRIMARY">
            <unique-column name="id"/>
        </unique>
    </table>
    <table name="Player">
        <column name="id" primaryKey="true" required="true" scale="0" size="19" type="-5,BIGINT"/>
        <column name="firstName" primaryKey="false" required="false" scale="0" size="63" type="12,VARCHAR"/>
        <column name="lastName" primaryKey="false" required="true" scale="0" size="63" type="12,VARCHAR"/>
        <column name="birthdate" primaryKey="false" required="true" scale="0" size="10" type="91,DATE"/>
        <column name="statusType" primaryKey="false" required="true" scale="0" size="63" type="12,VARCHAR"/>
        <column name="version" primaryKey="false" required="false" scale="0" size="10" type="4,INT"/>
        <foreign-key foreignTable="playerstatustype" name="player_ibfk_1" onDelete="none" onUpdate="none">
            <option key="exported" value="false"/>
            <reference foreign="type" local="statusType"/>
        </foreign-key>
        <foreign-key foreignTable="TeamPlayer" name="teamplayer_ibfk_2" onDelete="none" onUpdate="none">
            <option key="exported" value="true"/>
            <reference foreign="playerId" local="id"/>
        </foreign-key>
        <unique name="PRIMARY">
            <unique-column name="id"/>
        </unique>
        <index name="statusType">
            <index-column name="statusType"/>
        </index>
    </table>
    <table name="PlayerStatusType">
        <column name="type" primaryKey="true" required="true" scale="0" size="63" type="12,VARCHAR"/>
        <foreign-key foreignTable="Player" name="player_ibfk_1" onDelete="none" onUpdate="none">
            <option key="exported" value="true"/>
            <reference foreign="statusType" local="type"/>
        </foreign-key>
        <unique name="PRIMARY">
            <unique-column name="type"/>
        </unique>
    </table>
    <table name="Team">
        <column name="id" primaryKey="true" required="true" scale="0" size="19" type="-5,BIGINT"/>
        <column name="city" primaryKey="false" required="true" scale="0" size="127" type="12,VARCHAR"/>
        <column name="name" primaryKey="false" required="true" scale="0" size="127" type="12,VARCHAR"/>
        <column name="version" primaryKey="false" required="false" scale="0" size="10" type="4,INT"/>
        <foreign-key foreignTable="TeamPlayer" name="teamplayer_ibfk_1" onDelete="none" onUpdate="none">
            <option key="exported" value="true"/>
            <reference foreign="teamId" local="id"/>
        </foreign-key>
        <unique name="PRIMARY">
            <unique-column name="id"/>
        </unique>
        <unique name="city">
            <unique-column name="city"/>
            <unique-column name="name"/>
        </unique>
    </table>
    <table name="TeamPlayer">
        <column name="teamId" primaryKey="true" required="true" scale="0" size="19" type="-5,BIGINT"/>
        <column name="playerId" primaryKey="true" required="true" scale="0" size="19" type="-5,BIGINT"/>
        <column name="fromDate" primaryKey="true" required="true" scale="0" size="10" type="91,DATE"/>
        <column name="toDate" primaryKey="false" required="false" scale="0" size="10" type="91,DATE"/>
        <column name="version" primaryKey="false" required="false" scale="0" size="10" type="4,INT"/>
        <foreign-key foreignTable="team" name="teamplayer_ibfk_1" onDelete="none" onUpdate="none">
            <option key="exported" value="false"/>
            <reference foreign="id" local="teamId"/>
        </foreign-key>
        <foreign-key foreignTable="player" name="teamplayer_ibfk_2" onDelete="none" onUpdate="none">
            <option key="exported" value="false"/>
            <reference foreign="id" local="playerId"/>
        </foreign-key>
        <unique name="PRIMARY">
            <unique-column name="teamId"/>
            <unique-column name="playerId"/>
            <unique-column name="fromDate"/>
        </unique>
        <index name="playerId">
            <index-column name="playerId"/>
        </index>
    </table>
</database>

I should have looked at this output earlier. The status table is 'PlayerStatusType' and the foreign key table in player is pointing to 'playerstatustype'. According to MySQL, on os x, tables are stored with case sensitivity but compared as lower case. However, one must not mix case within a single statement.
From mysql command line, I can query a table in upper or lower case.

I will convert all table names to lower case and try again.
If that does not work, I will convert all field names to lower case.

[rbon]

Conversion of table names to lower case results in correct output.

However, I should be able to have my table names in the case of my choosing.
Another downside to lower case table names is the resulting class names. For example, table 'teamplayer' becomes Teamplayer.java whereas table 'TeamPlayer' becomes TeamPlayer.java.

For reasons that may be shallow and superficial, I would like to have my table names the same as my pojo names. Maybe this is just aesthetics, but I prefer to avoid the underscore separator, 'team_player' in table names. It makes searching through documentation simpler as one has a single name for all aspects of an entity.

Should this roo shortcoming be entered as bug?