[hibernate-issues] [Hibernate-JIRA] Commented: (HBX-1193) reverse engineering of UniqueConstraint annotation for function-based indexes

[Steve Maring (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HBX-1193?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=44040#comment-44040 ] 

Steve Maring commented on HBX-1193:
-----------------------------------

added

hibernatetool.metadatadialect = org.hibernate.cfg.reveng.dialect.OracleMetaDataDialect

to the properties, and that forced the usage of the OracleMetaDataDialect

However, the problem of the "FUNCTION-BASED" indexes persists.

I find it odd that the SQL for the index does not even request the TYPE:

private static final String SQL_INDEX_BASE = "select a.column_name, "
			+ "decode(b.uniqueness,'UNIQUE','false','true'), "
			+ "a.index_owner, a.index_name, a.table_name "
			+ "from all_ind_columns a left join all_indexes b on "
			+ "(a.table_name = b.table_name "
			+ " AND a.table_owner = b.table_owner "
			+ " AND a.index_name  = b.index_name) ";

but then it seems to hard code an arbitrary type instead:

	public Iterator getIndexInfo(final String catalog, final String schema,
			final String table) {
		try {
			log.debug("getIndexInfo(" + catalog + "." + schema + "." + table + ")");

			ResultSet indexRs;
			indexRs = getIndexInfoResultSet( schema, table );

			return new ResultSetIterator(null, indexRs,
					getSQLExceptionConverter()) {

				Map element = new HashMap();

				
				protected Object convertRow(ResultSet rs) throws SQLException {
					element.clear();
					element.put("COLUMN_NAME", rs.getString(1));
					element.put("TYPE", new Short((short) 1)); // CLUSTERED
																// INDEX
					element.put("NON_UNIQUE", Boolean.valueOf(rs.getString(2)));
					element.put("TABLE_SCHEM", rs.getString(3));
					element.put("INDEX_NAME", rs.getString(4));
					element.put("TABLE_CAT", null);
					element.put("TABLE_NAME", rs.getString(5));


I'm going to take ownership of this and try out a modification of the constants to exclude the "FUNCTION-BASED" indexes.

If it works, I will try to find a Hibernate developer with Github write access, that cares, and send them a diff of my local git

> reverse engineering of UniqueConstraint annotation for function-based indexes
> -----------------------------------------------------------------------------
>
>                 Key: HBX-1193
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HBX-1193
>             Project: Hibernate Tools
>          Issue Type: Bug
>          Components: hbm2java
>    Affects Versions: 3.2.4.GA
>            Reporter: Steve Maring
>              Labels: engineering, function-based, hbm2java, index, reverse, uniqueconstraint
>
> getting a runtime error ...
> [code]org.hibernate.AnnotationException: Unable to create unique key constraint (SYS_NC00048$) on table WRKR: SYS_NC00048$ not found[/code]
> the generated entity looks like this:
> [code]@Entity
> @Table(name="WRKR"
>     , uniqueConstraints = {  @UniqueConstraint(columnNames="SYS_NC00048$"),
>                                 @UniqueConstraint(columnNames="SYS_NC00047$"), 
>                                 @UniqueConstraint(columnNames="LOGIN_VAL"), 
>                                 @UniqueConstraint(columnNames="SOCL_SECUR_NUM_VAL"), 
>                                 @UniqueConstraint(columnNames="AD_LOGIN_VAL")} 
> )
> public class Wrkr  implements java.io.Serializable {[/code]
> Interesting thing is that this data seems to be coming from the table Indexes.  They read ...
> [code]
> Index Name		        Uniqueness	        Index Type		         Columns     	Column Expression
> IX_WRKR_01		        UNIQUE		NORMAL			        WRKR_SEQ	
> IX_WRKR_12		        UNIQUE		NORMAL			        SOCL_SECUR_NUM_VAL	
> IX_WRKR_16		        UNIQUE		FUNCTION-BASED NORMAL	SYS_NC00047$		UPPER("AD_LOGIN_VAL")
> IX_WRKR_17		        UNIQUE		FUNCTION-BASED NORMAL	SYS_NC00048$		UPPER("LOGIN_VAL")
> IX_WRKR_UQ_LOGIN_VAL	  UNIQUE		NORMAL			        LOGIN_VAL	
> IX_WRKR_UQ_AD_LOGIN_VAL	UNIQUE		NORMAL			        AD_LOGIN_VAL
> [/code]
> WRKR_SEQ is the primary key, and SOCL_SECUR_NUM_VAL, LOGIN_VAL, and AD_LOGIN_VAL are "real" columns.  The other two SYS_NC0004#$ are not actual columns in the table.  They seem to be some sort of "function-based" Oracle indexes/columns that you only see in the list of indexes.
> tried:
> <table name="WRKR">
> 		<column name="SYS_NC00047$" exclude="true"/>
> 		<column name="SYS_NC00048$" exclude="true"/>
> </table>
> but it didn't help.
> The problem seems to be isolated to the annotation generation.  I would venture to say that it should NOT be generating @UniqueConstraint entries for columns that don't actually exist in the table.

--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira