0 Replies Latest reply on Oct 31, 2008 8:05 AM by matteg.gerry.matte.shaw.ca

    xxxHome classes generate duplicate references for a mysql lookup table

    matteg.gerry.matte.shaw.ca
      When I reverse engineer mysql InnoDB tables that refer to other tables, each reference causes the xxxHome Entity to define the referenced table.  If two different fields refer to another secondary table multiple times then the secondary table is defined twice with the same name.  That causes compile errors.

      public class ContractsHome extends EntityHome<Contracts> {
           @In(create = true)

           OrganisationsHome organisationsHome;
           @In(create = true)

           OrganisationsHome organisationsHome;  // these statements cause fatal compile error
           @In(create = true)                    // these statements cause fatal compile error

           PersonsHome personsHome;

      is generated by seam when the mySql Contracts Table is defined as:

      CREATE  TABLE IF NOT EXISTS `hauler`.`contracts` (
        `id_contracts` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
        `hauler_org_id` INT UNSIGNED NOT NULL ,
        `client_org_id` INT UNSIGNED NOT NULL ,
        `contract_no` INT UNSIGNED NOT NULL ,
        `start_date_time` DATETIME NULL ,
        `stop_date_time` DATETIME NULL ,
        `contract_status` ENUM('Active','Suspended','Terminated','Negotiating') NOT NULL ,
        `client_contact_person` INT UNSIGNED NULL ,
        `contract_description` VARCHAR(45) NULL ,
        PRIMARY KEY (`id_contracts`) ,
        INDEX `client_contact_person` (`client_contact_person` ASC) ,
        INDEX `hauler_org` (`hauler_org_id` ASC) ,
        INDEX `hauling_for` (`client_org_id` ASC) ,
        CONSTRAINT `client_contact_person`
          FOREIGN KEY (`client_contact_person` )
          REFERENCES `hauler`.`persons` (`id_persons` )
          ON DELETE NO ACTION
          ON UPDATE NO ACTION,
        CONSTRAINT `hauler_org`
          FOREIGN KEY (`hauler_org_id` )
          REFERENCES `hauler`.`organisations` (`id_organisations` )
          ON DELETE NO ACTION
          ON UPDATE NO ACTION,
        CONSTRAINT `hauling_for`
          FOREIGN KEY (`client_org_id` )
          REFERENCES `hauler`.`organisations` (`id_organisations` )
          ON DELETE NO ACTION
          ON UPDATE NO ACTION)
      ENGINE = InnoDB;

      The Contracts table contains two constraints that refer to another table Organisations (one for each party of the Contract).  The same java variable name OrganisationsHome is generated for each constraint.  Could we instead use the constraint name so that both references can be accomodated ?

      BTW, this is not a problem new to version 2.1.0.SP1.  I experienced it with SEAM 1.2.1.GA, 2.0.1.GA, and 2.1.0.CR1.  I have used two different mysql drivers (version 3 and version 5.1.7) with no difference in behavior.  I also tried varying the hibernate dialect from MySql to MySqlInnoDb with no diference. The mySql version is 5.0.45.

      I suspect that this behavior is not unique to mysql ..... it's possible that most people do not define their master/detail tables so explicitly.  In fact my past practise has been to remove the second constraint and accept that the database architecture has to be dumbed down for hibernate/seam.

      I would much prefer to have hibernate handle the constraints (and foreign key references) correctly.