n00b need help reverse engineering a legacy db .. bad FKs

larvelljones · **Joined:** Tue Sep 18, 2007 10:04 am **Posts:** 3

Ok. I'm trying to reverse engineer a legacy db and the problem seems to be that it's failing trying to create an association between two tables

crfac (with composite primary key: clientId, invno)
crinvfee (with foreign key invno)

Now the export fails due to the "must have same number of columns as the referenced primary key" error. Ok. Fair enough.

But crinvfee does have a column called clienttk, which is a fk to another table.

aacli (the client table with primary key clienttk)

And this table contains the clientId association. So the question is how do I make the reverse engineering recognize this indirect association and make use of it. Also, it appears that this weak association to crfac exists for multiple tables, so if it can be configured for multiple tables that would be ideal. Otherwise, I'll just have to deal with them as they come up. It's a very large database with 1000s of tables, so I'd like to avoid running the hbm2hbmxml tool and keep finding different problematic tables.

Thanks!

Hibernate version:
3.3.1.GA

Mapping documents:

Code:

<hibernate-reverse-engineering>

<type-mapping>
 <!-- jdbc-type is name fom java.sql.Types -->
 <sql-type jdbc-type="VARCHAR" length='20' hibernate-type="SomeUserType" /> 
 <sql-type jdbc-type="VARCHAR" length='1' hibernate-type="yes_no" />
 <!-- length, scale and precision can be used to specify the mapping precisly -->
 <sql-type jdbc-type="NUMERIC"  precision='1' hibernate-type="boolean" /> 
 <!-- the type-mappings are ordered. This mapping will be consulted last, 
  thus overriden by the previous one if precision=1 for the column -->
 <sql-type jdbc-type="NUMERIC"  hibernate-type="long" /> 
</type-mapping>

</hibernate-reverse-engineering>

Code:

<target name="gen_hibernate" >
 <delete dir="hibernate"/>
 <mkdir dir="hibernate"/>
 <hibernatetool>
   <jdbcconfiguration
     configurationfile="src/main/resources/hibernate.cfg.xml"
     revengfile="hibernate.reveng.xml"
     packagename="blah"
     detectmanytomany="true"
     detectoptimisticlock="true"/>
   <hbm2hbmxml destdir="hibernate"/>
   <hbm2java destdir="hibernate">
      <property key="jdk5" value="true"/>
      <property key="ejb3" value="true"/>
   </hbm2java>
 </hibernatetool>
</target>

Full stack trace of any exception that occurs:

Code:

ant -verbose
Apache Ant version 1.6.5 compiled on June 2 2005
Buildfile: build.xml
Detected Java version: 1.5 in: /System/Library/Frameworks/JavaVM.framework/Versions/1.5.0/Home
Detected OS: Mac OS X
parsing buildfile /Users/boltonn/dev/src/vvm/intranet/src/national-ads/build.xml with URI = file:///Users/boltonn/dev/src/vvm/intranet/src/national-ads/build.xml
Project base dir set to: /Users/boltonn/dev/src/vvm/intranet/src/national-ads
Build sequence for target(s) `gen_hibernate' is [gen_hibernate]
Complete build sequence is [gen_hibernate, ]

gen_hibernate:
   [delete] Deleting directory /Users/boltonn/dev/src/vvm/intranet/src/national-ads/hibernate
   [delete] Deleting directory /Users/boltonn/dev/src/vvm/intranet/src/national-ads/hibernate
    [mkdir] Created dir: /Users/boltonn/dev/src/vvm/intranet/src/national-ads/hibernate
[hibernatetool] Executing Hibernate Tool with a JDBC Configuration (for reverse engineering)
[hibernatetool] 1. task: hbm2hbmxml (Generates a set of hbm.xml files)
[hibernatetool] 24 [main] INFO org.hibernate.cfg.Environment - Hibernate 3.3.1.GA
[hibernatetool] 30 [main] INFO org.hibernate.cfg.Environment - hibernate.properties not found
[hibernatetool] 34 [main] INFO org.hibernate.cfg.Environment - Bytecode provider name : javassist
[hibernatetool] 47 [main] INFO org.hibernate.cfg.Environment - using JDK 1.4 java.sql.Timestamp handling
[hibernatetool] 147 [main] INFO org.hibernate.cfg.Configuration - configuring from file: hibernate.cfg.xml
[hibernatetool] 218 [main] INFO org.hibernate.cfg.Configuration - Configured SessionFactory: null
[hibernatetool] log4j:WARN No appenders could be found for logger (org.hibernate.cfg.reveng.OverrideRepository).
[hibernatetool] log4j:WARN Please initialize the log4j system properly.
[hibernatetool] 402 [main] INFO org.hibernate.connection.DriverManagerConnectionProvider - Using Hibernate built-in connection pool (not for production use!)
[hibernatetool] 402 [main] INFO org.hibernate.connection.DriverManagerConnectionProvider - Hibernate connection pool size: 10
[hibernatetool] 402 [main] INFO org.hibernate.connection.DriverManagerConnectionProvider - autocommit mode: false
[hibernatetool] 405 [main] INFO org.hibernate.connection.DriverManagerConnectionProvider - using driver: net.sourceforge.jtds.jdbc.Driver at URL: jdbc:jtds:sqlserver://192.168.41.109:1433;DatabaseName=LAWTRN
[hibernatetool] 406 [main] INFO org.hibernate.connection.DriverManagerConnectionProvider - connection properties: {user=sa, password=****}
[hibernatetool] 771 [main] INFO org.hibernate.cfg.SettingsFactory - RDBMS: Microsoft SQL Server, version: 08.00.2039
[hibernatetool] 772 [main] INFO org.hibernate.cfg.SettingsFactory - JDBC driver: jTDS Type 4 JDBC Driver for MS SQL Server and Sybase, version: 1.2.2
[hibernatetool] 811 [main] INFO org.hibernate.dialect.Dialect - Using dialect: org.hibernate.dialect.SQLServerDialect
[hibernatetool] 879 [main] INFO org.hibernate.transaction.TransactionFactoryFactory - Using default transaction strategy (direct JDBC transactions)
[hibernatetool] 882 [main] INFO org.hibernate.transaction.TransactionManagerLookupFactory - No TransactionManagerLookup configured (in JTA environment, use of read-write or transactional second-level cache is not recommended)
[hibernatetool] 882 [main] INFO org.hibernate.cfg.SettingsFactory - Automatic flush during beforeCompletion(): disabled
[hibernatetool] 882 [main] INFO org.hibernate.cfg.SettingsFactory - Automatic session close at end of transaction: disabled
[hibernatetool] 883 [main] INFO org.hibernate.cfg.SettingsFactory - Scrollable result sets: enabled
[hibernatetool] 884 [main] INFO org.hibernate.cfg.SettingsFactory - JDBC3 getGeneratedKeys(): enabled
[hibernatetool] 884 [main] INFO org.hibernate.cfg.SettingsFactory - Connection release mode: auto
[hibernatetool] 885 [main] INFO org.hibernate.cfg.SettingsFactory - Default batch fetch size: 1
[hibernatetool] 885 [main] INFO org.hibernate.cfg.SettingsFactory - Generate SQL with comments: disabled
[hibernatetool] 885 [main] INFO org.hibernate.cfg.SettingsFactory - Order SQL updates by primary key: disabled
[hibernatetool] 885 [main] INFO org.hibernate.cfg.SettingsFactory - Order SQL inserts for batching: disabled
[hibernatetool] 885 [main] INFO org.hibernate.cfg.SettingsFactory - Query translator: org.hibernate.hql.ast.ASTQueryTranslatorFactory
[hibernatetool] 890 [main] INFO org.hibernate.hql.ast.ASTQueryTranslatorFactory - Using ASTQueryTranslatorFactory
[hibernatetool] 890 [main] INFO org.hibernate.cfg.SettingsFactory - Query language substitutions: {}
[hibernatetool] 890 [main] INFO org.hibernate.cfg.SettingsFactory - JPA-QL strict compliance: disabled
[hibernatetool] 890 [main] INFO org.hibernate.cfg.SettingsFactory - Second-level cache: enabled
[hibernatetool] 890 [main] INFO org.hibernate.cfg.SettingsFactory - Query cache: disabled
[hibernatetool] 890 [main] INFO org.hibernate.cfg.SettingsFactory - Cache region factory : org.hibernate.cache.impl.NoCachingRegionFactory
[hibernatetool] 891 [main] INFO org.hibernate.cfg.SettingsFactory - Optimize cache for minimal puts: disabled
[hibernatetool] 891 [main] INFO org.hibernate.cfg.SettingsFactory - Structured second-level cache entries: disabled
[hibernatetool] 900 [main] INFO org.hibernate.cfg.SettingsFactory - Echoing all SQL to stdout
[hibernatetool] 901 [main] INFO org.hibernate.cfg.SettingsFactory - Statistics: disabled
[hibernatetool] 901 [main] INFO org.hibernate.cfg.SettingsFactory - Deleted entity synthetic identifier rollback: disabled
[hibernatetool] 903 [main] INFO org.hibernate.cfg.SettingsFactory - Default entity-mode: pojo
[hibernatetool] 903 [main] INFO org.hibernate.cfg.SettingsFactory - Named query checking : enabled
[hibernatetool] 191209 [main] INFO org.hibernate.connection.DriverManagerConnectionProvider - cleaning up connection pool: jdbc:jtds:sqlserver://192.168.41.109:1433;DatabaseName=LAWTRN
[hibernatetool] An exception occurred while running exporter #2:hbm2hbmxml (Generates a set of hbm.xml files)
[hibernatetool] To get the full stack trace run ant with -verbose
[hibernatetool] org.hibernate.MappingException: Foreign key (FKA2B2CF246301D1BF:CRINVFEE [InvNo])) must have same number of columns as the referenced primary key (CRFAC [ClientId,InvNo])

BUILD FAILED
/Users/boltonn/dev/src/vvm/intranet/src/national-ads/build.xml:96: org.hibernate.MappingException: Foreign key (FKA2B2CF246301D1BF:CRINVFEE [InvNo])) must have same number of columns as the referenced primary key (CRFAC [ClientId,InvNo])
        at org.hibernate.tool.ant.HibernateToolTask.reportException(HibernateToolTask.java:226)
        at org.hibernate.tool.ant.HibernateToolTask.execute(HibernateToolTask.java:189)
        at org.apache.tools.ant.UnknownElement.execute(UnknownElement.java:275)
        at org.apache.tools.ant.Task.perform(Task.java:364)
        at org.apache.tools.ant.Target.execute(Target.java:341)
        at org.apache.tools.ant.Target.performTasks(Target.java:369)
        at org.apache.tools.ant.Project.executeSortedTargets(Project.java:1216)
        at org.apache.tools.ant.Project.executeTarget(Project.java:1185)
        at org.apache.tools.ant.helper.DefaultExecutor.executeTargets(DefaultExecutor.java:40)
        at org.apache.tools.ant.Project.executeTargets(Project.java:1068)
        at org.apache.tools.ant.Main.runBuild(Main.java:668)
        at org.apache.tools.ant.Main.startAnt(Main.java:187)
        at org.apache.tools.ant.launch.Launcher.run(Launcher.java:246)
        at org.apache.tools.ant.launch.Launcher.main(Launcher.java:67)
Caused by: org.hibernate.MappingException: Foreign key (FKA2B2CF246301D1BF:CRINVFEE [InvNo])) must have same number of columns as the referenced primary key (CRFAC [ClientId,InvNo])
        at org.hibernate.mapping.ForeignKey.alignColumns(ForeignKey.java:113)
        at org.hibernate.mapping.ForeignKey.alignColumns(ForeignKey.java:96)
        at org.hibernate.cfg.Configuration.secondPassCompileForeignKeys(Configuration.java:1296)
        at org.hibernate.cfg.JDBCMetaDataConfiguration.secondPassCompileForeignKeys(JDBCMetaDataConfiguration.java:33)
        at org.hibernate.cfg.Configuration.secondPassCompile(Configuration.java:1203)
        at org.hibernate.cfg.Configuration.buildMappings(Configuration.java:1148)
        at org.hibernate.tool.ant.ConfigurationTask.getConfiguration(ConfigurationTask.java:56)
        at org.hibernate.tool.ant.HibernateToolTask.getConfiguration(HibernateToolTask.java:302)
        at org.hibernate.tool.ant.HibernateToolTask.getProperties(HibernateToolTask.java:318)
        at org.hibernate.tool.ant.ExporterTask.configureExporter(ExporterTask.java:94)
        at org.hibernate.tool.ant.ExporterTask.execute(ExporterTask.java:39)
        at org.hibernate.tool.ant.HibernateToolTask.execute(HibernateToolTask.java:186)
        ... 12 more
--- Nested Exception ---
org.hibernate.MappingException: Foreign key (FKA2B2CF246301D1BF:CRINVFEE [InvNo])) must have same number of columns as the referenced primary key (CRFAC [ClientId,InvNo])
        at org.hibernate.mapping.ForeignKey.alignColumns(ForeignKey.java:113)
        at org.hibernate.mapping.ForeignKey.alignColumns(ForeignKey.java:96)
        at org.hibernate.cfg.Configuration.secondPassCompileForeignKeys(Configuration.java:1296)
        at org.hibernate.cfg.JDBCMetaDataConfiguration.secondPassCompileForeignKeys(JDBCMetaDataConfiguration.java:33)
        at org.hibernate.cfg.Configuration.secondPassCompile(Configuration.java:1203)
        at org.hibernate.cfg.Configuration.buildMappings(Configuration.java:1148)
        at org.hibernate.tool.ant.ConfigurationTask.getConfiguration(ConfigurationTask.java:56)
        at org.hibernate.tool.ant.HibernateToolTask.getConfiguration(HibernateToolTask.java:302)
        at org.hibernate.tool.ant.HibernateToolTask.getProperties(HibernateToolTask.java:318)
        at org.hibernate.tool.ant.ExporterTask.configureExporter(ExporterTask.java:94)
        at org.hibernate.tool.ant.ExporterTask.execute(ExporterTask.java:39)
        at org.hibernate.tool.ant.HibernateToolTask.execute(HibernateToolTask.java:186)
        at org.apache.tools.ant.UnknownElement.execute(UnknownElement.java:275)
        at org.apache.tools.ant.Task.perform(Task.java:364)
        at org.apache.tools.ant.Target.execute(Target.java:341)
        at org.apache.tools.ant.Target.performTasks(Target.java:369)
        at org.apache.tools.ant.Project.executeSortedTargets(Project.java:1216)
        at org.apache.tools.ant.Project.executeTarget(Project.java:1185)
        at org.apache.tools.ant.helper.DefaultExecutor.executeTargets(DefaultExecutor.java:40)
        at org.apache.tools.ant.Project.executeTargets(Project.java:1068)
        at org.apache.tools.ant.Main.runBuild(Main.java:668)
        at org.apache.tools.ant.Main.startAnt(Main.java:187)
        at org.apache.tools.ant.launch.Launcher.run(Launcher.java:246)
        at org.apache.tools.ant.launch.Launcher.main(Launcher.java:67)

Name and version of the database you are using:
sqlserver 8.0

larvelljones · **Joined:** Tue Sep 18, 2007 10:04 am **Posts:** 3

Can someone please help me out with this? Do I need to provide more info? Is this answered elsewhere? I couldn't find anything.

Please I need help. :-/

max · **Posted:** Wed Dec 10, 2008 7:54 am

i don't think we got a good way of reverse engineering overlapping foreign keys; so the best workaround i would suggest is to use a reveng.xml to exclude the overlapping foreignkey.