-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Projects » Tools

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 2 posts ] 
  Previous topic | Next topic 
Author Message
ukroener
 Post subject: reverse engineering for table without primary key
PostPosted: Wed Oct 29, 2008 2:12 pm 
Newbie

Joined: Mon Jan 09, 2006 10:43 am
Posts: 3
Location: Brussels, Belgium
Hello,

I have to reverse engineer a table without a primary key (juck!), and create a table from it in a different DB environment that has the same properties. The reason is that this table is imposed on me and "historically grown".

I've read http://www.jboss.com/index.html?module= ... c&t=101902 and http://opensource.atlassian.com/project ... se/HBX-883 but how that squares up with the hibernate paradigm "the database is always right" is not clear to me.

When one does reverse engineer such a table, then one ends up with a composite key which encompasses ALL of the columns. In my environment, that is not right either, because a composite key always creates a DB replicate table with non-null constraints, but in the original table, some of the rows of the table have null values! Hence, my replicate will never be able to receive the rows where null values exist (e.g. in the example below, insert into public.no_pk values ('Fred', null)) will not be replicated. This is a problem, because I happen to need the data afterward.

First, here is the original problem, reduced to its smallest denominator, in a postgres 8.3.x environment:

------

1. Versions
- Hibernate Tools 3.2.4.CR1-N200810280059
- Hibernate version 3.2.6, Feb 7, 2008
- java version "1.6.0_0", OpenJDK Runtime Environment (build 1.6.0_0-b11), OpenJDK 64-Bit Server VM (build 1.6.0_0-b11, mixed mode)


2. postgres create table

CREATE TABLE public.no_pk
(
firstname character varying(10) DEFAULT 'John'::character varying,
lastname character varying(10) DEFAULT 'Doe'::character varying
)
WITH (OIDS=FALSE);
ALTER TABLE public.no_pk OWNER TO me;


-- aint no fun without any values

insert into public.no_pk values ('Barack', 'Obama');
insert into public.no_pk values ('John', 'McCain');
insert into public.no_pk values ('Ralph', 'Nader');
insert into public.no_pk values ('Fred', null);

--

3. excerpts from build.xml

<target name="reveng" depends="clean,setup">
<mkdir dir="${build}/hibernate" />
<hibernatetool>
<jdbcconfiguration propertyfile="source-hibernate.properties" revengfile="reveng.xml" packagename="org.ourorg.dao" />
<hbm2hbmxml destdir="${build}/classes" />
<hbm2java destdir="${build}/hibernate" />
<hbm2cfgxml destdir="${build}/classes" />
</hibernatetool>
<javac debug="true" debuglevel="${debuglevel}" srcdir="${build}/hibernate" destdir="${build}/classes" />
</target>

4. reveng.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-reverse-engineering
SYSTEM "http://hibernate.sourceforge.net/hibernate-reverse-engineering-3.0.dtd" >
<hibernate-reverse-engineering>
<schema-selection match-table="no_pk"/>
</hibernate-reverse-engineering>


5. ant run:


Buildfile: build.xml

clean:
[delete] Deleting directory /home/path/to/disaster/build
[delete] Deleting directory /home/path/to/disaster/dist

init:
[mkdir] Created dir: /home/path/to/disaster/build
[mkdir] Created dir: /home/path/to/disaster/build/classes
[mkdir] Created dir: /home/path/to/disaster/dist
[mkdir] Created dir: /home/path/to/disaster/dist/lib

setup:

reveng:
[mkdir] Created dir: /home/path/to/disaster/build/hibernate
[hibernatetool] Executing Hibernate Tool with a JDBC Configuration (for reverse engineering)
[hibernatetool] 1. task: hbm2hbmxml (Generates a set of hbm.xml files)
[hibernatetool] Oct 29, 2008 5:59:16 PM org.hibernate.cfg.Environment <clinit>
[hibernatetool] INFO: Hibernate 3.2.6
[hibernatetool] Oct 29, 2008 5:59:16 PM org.hibernate.cfg.Environment <clinit>
[hibernatetool] INFO: hibernate.properties not found
[hibernatetool] Oct 29, 2008 5:59:16 PM org.hibernate.cfg.Environment buildBytecodeProvider
[hibernatetool] INFO: Bytecode provider name : cglib
[hibernatetool] Oct 29, 2008 5:59:16 PM org.hibernate.cfg.Environment <clinit>
[hibernatetool] INFO: using JDK 1.4 java.sql.Timestamp handling
[hibernatetool] Oct 29, 2008 5:59:16 PM org.hibernate.cfg.reveng.OverrideRepository addFile
[hibernatetool] INFO: Override file: /home/path/to/disaster/reveng.xml
[hibernatetool] Oct 29, 2008 5:59:16 PM org.hibernate.connection.C3P0ConnectionProvider configure
[hibernatetool] INFO: C3P0 using driver: org.postgresql.Driver at URL: jdbc:postgresql://server:5433/myschema
[hibernatetool] Oct 29, 2008 5:59:16 PM org.hibernate.connection.C3P0ConnectionProvider configure
[hibernatetool] INFO: Connection properties: {user=me, password=****}
[hibernatetool] Oct 29, 2008 5:59:16 PM org.hibernate.connection.C3P0ConnectionProvider configure
[hibernatetool] INFO: autocommit mode: false
[hibernatetool] Oct 29, 2008 5:59:17 PM com.mchange.v2.log.MLog <clinit>
[hibernatetool] INFO: MLog clients using java 1.4+ standard logging.
[hibernatetool] Oct 29, 2008 5:59:17 PM com.mchange.v2.c3p0.C3P0Registry banner
[hibernatetool] INFO: Initializing c3p0-0.9.1.2 [built 21-May-2007 15:04:56; debug? true; trace: 10]
[hibernatetool] Oct 29, 2008 5:59:17 PM com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource getPoolManager
[hibernatetool] INFO: Initializing c3p0 pool... com.mchange.v2.c3p0.PoolBackedDataSource@ea846fb8 [ connectionPoolDataSource -> com.mchange.v2.c3p0.WrapperConnectionPoolDataSource@41a31e29 [ acquireIncrement -> 2, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, debugUnreturnedConnectionStackTraces -> false, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityToken -> z8kflt7xkema9630mxjv|19202d69, idleConnectionTestPeriod -> 60, initialPoolSize -> 2, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 1000, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 6, maxStatements -> 100, maxStatementsPerConnection -> 0, minPoolSize -> 2, nestedDataSource -> com.mchange.v2.c3p0.DriverManagerDataSource@d7fe5534 [ description -> null, driverClass -> null, factoryClassLocation -> null, identityToken -> z8kflt7xkema9630mxjv|1eb3319f, jdbcUrl -> jdbc:postgresql://server:5433/myschema, properties -> {user=******, password=******} ], preferredTestQuery -> null, propertyCycle -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, usesTraditionalReflectiveProxies -> false; userOverrides: {} ], dataSourceName -> null, factoryClassLocation -> null, identityToken -> z8kflt7xkema9630mxjv|5c6ed322, numHelperThreads -> 3 ]
[hibernatetool] Oct 29, 2008 5:59:17 PM org.hibernate.cfg.SettingsFactory buildSettings
[hibernatetool] INFO: RDBMS: PostgreSQL, version: 8.3.4
[hibernatetool] Oct 29, 2008 5:59:17 PM org.hibernate.cfg.SettingsFactory buildSettings
[hibernatetool] INFO: JDBC driver: PostgreSQL Native Driver, version: PostgreSQL 8.2 JDBC3 with SSL (build 506)
[hibernatetool] Oct 29, 2008 5:59:17 PM org.hibernate.dialect.Dialect <init>
[hibernatetool] INFO: Using dialect: org.hibernate.dialect.PostgreSQLDialect
[hibernatetool] Oct 29, 2008 5:59:17 PM org.hibernate.transaction.TransactionFactoryFactory buildTransactionFactory
[hibernatetool] INFO: Using default transaction strategy (direct JDBC transactions)
[hibernatetool] Oct 29, 2008 5:59:17 PM org.hibernate.transaction.TransactionManagerLookupFactory getTransactionManagerLookup
[hibernatetool] INFO: No TransactionManagerLookup configured (in JTA environment, use of read-write or transactional second-level cache is not recommended)
[hibernatetool] Oct 29, 2008 5:59:17 PM org.hibernate.cfg.SettingsFactory buildSettings
[hibernatetool] INFO: Automatic flush during beforeCompletion(): disabled
[hibernatetool] Oct 29, 2008 5:59:17 PM org.hibernate.cfg.SettingsFactory buildSettings
[hibernatetool] INFO: Automatic session close at end of transaction: disabled
[hibernatetool] Oct 29, 2008 5:59:17 PM org.hibernate.cfg.SettingsFactory buildSettings
[hibernatetool] INFO: JDBC batch size: 15
[hibernatetool] Oct 29, 2008 5:59:17 PM org.hibernate.cfg.SettingsFactory buildSettings
[hibernatetool] INFO: JDBC batch updates for versioned data: enabled
[hibernatetool] Oct 29, 2008 5:59:17 PM org.hibernate.cfg.SettingsFactory buildSettings
[hibernatetool] INFO: Scrollable result sets: enabled
[hibernatetool] Oct 29, 2008 5:59:17 PM org.hibernate.cfg.SettingsFactory buildSettings
[hibernatetool] INFO: JDBC3 getGeneratedKeys(): disabled
[hibernatetool] Oct 29, 2008 5:59:17 PM org.hibernate.cfg.SettingsFactory buildSettings
[hibernatetool] INFO: Connection release mode: auto
[hibernatetool] Oct 29, 2008 5:59:17 PM org.hibernate.cfg.SettingsFactory buildSettings
[hibernatetool] INFO: Maximum outer join fetch depth: 1
[hibernatetool] Oct 29, 2008 5:59:17 PM org.hibernate.cfg.SettingsFactory buildSettings
[hibernatetool] INFO: Default batch fetch size: 50
[hibernatetool] Oct 29, 2008 5:59:17 PM org.hibernate.cfg.SettingsFactory buildSettings
[hibernatetool] INFO: Generate SQL with comments: disabled
[hibernatetool] Oct 29, 2008 5:59:17 PM org.hibernate.cfg.SettingsFactory buildSettings
[hibernatetool] INFO: Order SQL updates by primary key: disabled
[hibernatetool] Oct 29, 2008 5:59:17 PM org.hibernate.cfg.SettingsFactory buildSettings
[hibernatetool] INFO: Order SQL inserts for batching: disabled
[hibernatetool] Oct 29, 2008 5:59:17 PM org.hibernate.cfg.SettingsFactory createQueryTranslatorFactory
[hibernatetool] INFO: Query translator: org.hibernate.hql.ast.ASTQueryTranslatorFactory
[hibernatetool] Oct 29, 2008 5:59:17 PM org.hibernate.hql.ast.ASTQueryTranslatorFactory <init>
[hibernatetool] INFO: Using ASTQueryTranslatorFactory
[hibernatetool] Oct 29, 2008 5:59:17 PM org.hibernate.cfg.SettingsFactory buildSettings
[hibernatetool] INFO: Query language substitutions: {yes='Y', no='N', false=0, true=1}
[hibernatetool] Oct 29, 2008 5:59:17 PM org.hibernate.cfg.SettingsFactory buildSettings
[hibernatetool] INFO: JPA-QL strict compliance: disabled
[hibernatetool] Oct 29, 2008 5:59:17 PM org.hibernate.cfg.SettingsFactory buildSettings
[hibernatetool] INFO: Second-level cache: disabled
[hibernatetool] Oct 29, 2008 5:59:17 PM org.hibernate.cfg.SettingsFactory buildSettings
[hibernatetool] INFO: Query cache: disabled
[hibernatetool] Oct 29, 2008 5:59:17 PM org.hibernate.cfg.SettingsFactory buildSettings
[hibernatetool] INFO: Optimize cache for minimal puts: disabled
[hibernatetool] Oct 29, 2008 5:59:17 PM org.hibernate.cfg.SettingsFactory buildSettings
[hibernatetool] INFO: Cache region prefix: hibernate.test
[hibernatetool] Oct 29, 2008 5:59:17 PM org.hibernate.cfg.SettingsFactory buildSettings
[hibernatetool] INFO: Structured second-level cache entries: disabled
[hibernatetool] Oct 29, 2008 5:59:17 PM org.hibernate.cfg.SettingsFactory buildSettings
[hibernatetool] INFO: Statistics: disabled
[hibernatetool] Oct 29, 2008 5:59:17 PM org.hibernate.cfg.SettingsFactory buildSettings
[hibernatetool] INFO: Deleted entity synthetic identifier rollback: disabled
[hibernatetool] Oct 29, 2008 5:59:17 PM org.hibernate.cfg.SettingsFactory buildSettings
[hibernatetool] INFO: Default entity-mode: pojo
[hibernatetool] Oct 29, 2008 5:59:17 PM org.hibernate.cfg.SettingsFactory buildSettings
[hibernatetool] INFO: Named query checking : enabled
[hibernatetool] Oct 29, 2008 5:59:17 PM org.hibernate.cfg.reveng.JDBCReader processPrimaryKey
[hibernatetool] WARNING: The JDBC driver didn't report any primary key columns in no_pk. Asking rev.eng. strategy
[hibernatetool] Oct 29, 2008 5:59:18 PM org.hibernate.cfg.reveng.JDBCReader processPrimaryKey
[hibernatetool] WARNING: Rev.eng. strategy did not report any primary key columns for no_pk
[hibernatetool] Oct 29, 2008 5:59:18 PM org.hibernate.tool.Version <clinit>
[hibernatetool] INFO: Hibernate Tools 3.2.2.GA
[hibernatetool] 2. task: hbm2java (Generates a set of .java files)
[hibernatetool] 3. task: hbm2cfgxml (Generates hibernate.cfg.xml)
[javac] Compiling 2 source files to /home/path/to/disaster/build/classes

dist:
[copy] Copying 1 file to /home/path/to/disaster/build/classes
[copy] Copying 1 file to /home/path/to/disaster/build/classes

BUILD SUCCESSFUL

-- Resulting hbm.xml:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated Oct 29, 2008 5:55:35 PM by Hibernate Tools 3.2.2.GA -->
<hibernate-mapping>
<class name="org.ourorg.dao.NoPk" table="no_pk" schema="public">
<composite-id name="id" class="org.ourorg.dao.NoPkId">
<key-property name="firstname" type="string">
<column name="firstname" length="10" />
</key-property>
<key-property name="lastname" type="string">
<column name="lastname" length="10" />
</key-property>
</composite-id>
</class>
</hibernate-mapping>


-----

Groovy, but as I stated earlier, this is not what I wanted. Instead, I wanted a mapping with just the data rows, and if necessary, some sort of auto-generated PK.

The original problem comes from an Oracle DB. There we would have the ROWID as a replacement for the absent PK. Therefore, back in the original problem under Oracle, I edited the reveng.xml

<table schema="DWH" name="DW_TABLE">
<primary-key>
<key-column name="ROWID"/>
</primary-key>
</table>

That looked like a solution, until I tried it out. If I then run this I get "sqltype is null for Table".

...
[hibernatetool] Oct 29, 2008 6:39:35 PM org.hibernate.cfg.reveng.dialect.OracleMetaDataDialect$1 convertRow
[hibernatetool] INFO: {TABLE_NAME=DW_TABLE, REMARKS=null, TABLE_TYPE=TABLE, TABLE_SCHEM=DWH, TABLE_CAT=null}
....
[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.cfg.JDBCBinderException: sqltype is null for Table: CR2006_DWH.DW_CATCHES_FACTS column: ROWID

BUILD FAILED
org.hibernate.cfg.JDBCBinderException: sqltype is null for Table: CR2006_DWH.DW_CATCHES_FACTS column: ROWID
at org.hibernate.cfg.JDBCBinder.guessAndAlignType(JDBCBinder.java:638)
at org.hibernate.cfg.JDBCBinder.bindColumnToSimpleValue(JDBCBinder.java:602)
at org.hibernate.cfg.JDBCBinder.bindPrimaryKeyToProperties(JDBCBinder.java:454)
at org.hibernate.cfg.JDBCBinder.createPersistentClasses(JDBCBinder.java:172)
at org.hibernate.cfg.JDBCBinder.readFromDatabase(JDBCBinder.java:89)
at org.hibernate.cfg.JDBCMetaDataConfiguration.readFromJDBC(JDBCMetaDataConfiguration.java:42)
at org.hibernate.tool.ant.JDBCConfigurationTask.doConfiguration(JDBCConfigurationTask.java:81)
at org.hibernate.tool.ant.ConfigurationTask.getConfiguration(ConfigurationTask.java:55)
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:288)
at sun.reflect.GeneratedMethodAccessor3.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:616)
at org.apache.tools.ant.dispatch.DispatchUtils.execute(DispatchUtils.java:106)
at org.apache.tools.ant.Task.perform(Task.java:348)
at org.apache.tools.ant.Target.execute(Target.java:357)
at org.apache.tools.ant.Target.performTasks(Target.java:385)
at org.apache.tools.ant.Project.executeSortedTargets(Project.java:1337)
at org.apache.tools.ant.Project.executeTarget(Project.java:1306)
at org.apache.tools.ant.helper.DefaultExecutor.executeTargets(DefaultExecutor.java:41)
at org.apache.tools.ant.Project.executeTargets(Project.java:1189)
at org.apache.tools.ant.Main.runBuild(Main.java:758)
at org.apache.tools.ant.Main.startAnt(Main.java:217)
at org.apache.tools.ant.launch.Launcher.run(Launcher.java:257)
at org.apache.tools.ant.launch.Launcher.main(Launcher.java:104)

I saw http://forum.hibernate.org/viewtopic.php?p=2397022 so I tried to set the hibernate.default_schema to SYS, then to SYSTEM, same results.

When I set hibernate.default_schema to DWH, the rev.eng strategy is ignored, meaning I again end up with a composite ID with all of the rows.

I also tried jdbc-type and type inside of the <key-column>, the behaviour does not change, because the java code wants the sqltype. Specifying sqltype in the <key-column> is against the DTD.

Lest you ask, I have the latest and greatest Oracle JDBC drivers, 11.1.0.7.0, and but also tried the ones that match my DB version, 9.2.0.8.

Any hope and help on this one?

TIA,


Ulrich Kroener
Top
 Profile  
 
max
 Post subject:
PostPosted: Fri Oct 31, 2008 8:29 am 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 6:10 am
Posts: 8615
Location: Neuchatel, Switzerland (Danish)
since hibernate won't be able to map a table with nulls in the primary key not much we can do at the tooling level.

About you saying if you set default_Schema to DWH then the reveng is ignored well that is because when you set the default schema to DWH then the tables found in that schema will not have an schema associated with them.

Hence just remove the schema attribute from your <table> in reveng.xml and it will be picked up.

_________________
Max
Don't forget to rate

Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 2 posts ] 

Board index » Projects » Tools

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.