Author: dchannon Date: 2006-09-17 11:25:26 -0400 (Sun, 17 Sep 2006) New Revision: 10490 Added: trunk/HibernateExt/tools/src/java/org/hibernate/cfg/reveng/dialect/OracleMetaDataDialect.java Log: Oracle specialist rev-eng database dialect. This should avoid a few Oracle JDBC driver bugs and provide performance improvements on large schemas. Added: trunk/HibernateExt/tools/src/java/org/hibernate/cfg/reveng/dialect/OracleMetaDataDialect.java =================================================================== --- trunk/HibernateExt/tools/src/java/org/hibernate/cfg/reveng/dialect/OracleMetaDataDialect.java 2006-09-16 21:19:50 UTC (rev 10489) +++ trunk/HibernateExt/tools/src/java/org/hibernate/cfg/reveng/dialect/OracleMetaDataDialect.java 2006-09-17 15:25:26 UTC (rev 10490) @@ -0,0 +1,419 @@ + +package org.hibernate.cfg.reveng.dialect; + +import java.sql.Connection; +import java.sql.DatabaseMetaData; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.sql.Statement; +import java.util.HashMap; +import java.util.Iterator; +import java.util.Map; + +import org.apache.commons.logging.Log; +import org.apache.commons.logging.LogFactory; +import org.hibernate.cfg.JDBCBinderException; +import org.hibernate.connection.ConnectionProvider; +import org.hibernate.exception.SQLExceptionConverter; +import org.hibernate.mapping.Table; + +/** + * Oracle Specialised MetaData dialect that uses standard JDBC + * and querys on the Data Dictionary for reading metadata. + * + * @author David Channon + */ +public class OracleMetaDataDialect implements MetaDataDialect { + + private static final Log log = LogFactory.getLog(OracleMetaDataDialect.class); + + private SQLExceptionConverter sec; + private ConnectionProvider provider; + + private Connection connection; + private DatabaseMetaData metaData; + + + public void configure(ConnectionProvider provider, SQLExceptionConverter sec) { + this.provider = provider; + this.sec = sec; + } + + public void close() { + metaData = null; + if(connection != null) { + try { + provider.closeConnection(connection); + } + catch (SQLException e) { + sec.convert(e, "Problem while closing connection", null); + } + } + provider = null; + sec = null; + } + + protected DatabaseMetaData getMetaData() throws JDBCBinderException { + if (metaData == null) { + try { + metaData = getConnection().getMetaData(); + } + catch (SQLException e) { + throw sec.convert(e, "Getting database metadata", null); + } + } + return metaData; + } + + public Iterator getTables(final String catalog, final String schema, String table) { + try { + log.debug("getTables(" + catalog + "." + schema + "." + table + ")"); + // Collect both Tables and Views from the 'ALL' data dicitonary tables. + // Note: This will potentally collect more tables that the jdbc meta data + Statement stmt = this.getConnection().createStatement(); + StringBuffer query = new StringBuffer(); + query.append("select table_name, owner, 'TABLE' from all_tables "); + if (schema != null || table != null) + query.append("where "); + if (schema != null) { + query.append("owner='" + schema + "' "); + } + if (table != null) { + if (schema != null) + query.append("and "); + query.append("table_name = '" + table + "' "); + } + query.append("union all "); + query.append("select view_name, owner, 'VIEW' from all_views "); + if (schema != null || table != null) + query.append("where "); + if (schema != null) { + query.append("owner='" + schema + "' "); + } + if (table != null) { + if (schema != null) + query.append("and "); + query.append("view_name = '" + table + "' "); + } + if (log.isDebugEnabled()) + log.debug("getTables Query:" + query.toString()); + + ResultSet tableRs = stmt.executeQuery(query.toString()); + + return new ResultSetIterator(tableRs, sec) { + + Map element = new HashMap(); + protected Object convertRow(ResultSet tableRs) throws SQLException { + element.clear(); + element.put("TABLE_NAME", tableRs.getString(1)); + element.put("TABLE_SCHEM", tableRs.getString(2)); + element.put("TABLE_CAT", null); + element.put("TABLE_TYPE", tableRs.getString(3)); + element.put("REMARKS", null); + return element; + } + protected Throwable handleSQLException(SQLException e) { + // schemaRs and catalogRs are only used for error reporting if + // we get an exception + String databaseStructure = getDatabaseStructure( catalog, schema ); + throw sec.convert( e, + "Could not get list of tables from database. Probably a JDBC driver problem. " + + databaseStructure, null ); + } + }; + } catch (SQLException e) { + // schemaRs and catalogRs are only used for error reporting if we get an exception + String databaseStructure = getDatabaseStructure(catalog,schema); + throw sec.convert(e, "Could not get list of tables from database. Probably a JDBC driver problem. " + databaseStructure, null); + } + } + + private String getDatabaseStructure(String catalog, String schema) { + ResultSet schemaRs = null; + ResultSet catalogRs = null; + String nl = System.getProperty("line.separator"); + StringBuffer sb = new StringBuffer(nl); + // Let's give the user some feedback. The exception + // is probably related to incorrect schema configuration. + sb.append("Configured schema:").append(schema).append(nl); + sb.append("Configured catalog:").append(catalog ).append(nl); + + try { + schemaRs = getMetaData().getSchemas(); + sb.append("Available schemas:").append(nl); + while (schemaRs.next() ) { + sb.append(" ").append(schemaRs.getString("TABLE_SCHEM") ).append(nl); + } + } + catch (SQLException e2) { + log.warn("Could not get schemas", e2); + sb.append(" <SQLException while getting schemas>").append(nl); + } + finally { + try { + schemaRs.close(); + } + catch (Exception ignore) { + } + } + + try { + catalogRs = getMetaData().getCatalogs(); + sb.append("Available catalogs:").append(nl); + while (catalogRs.next() ) { + sb.append(" ").append(catalogRs.getString("TABLE_CAT") ).append(nl); + } + } + catch (SQLException e2) { + log.warn("Could not get catalogs", e2); + sb.append(" <SQLException while getting catalogs>").append(nl); + } + finally { + try { + catalogRs.close(); + } + catch (Exception ignore) { + } + } + return sb.toString(); + } + + public void close(Iterator iterator) { + if(iterator instanceof ResultSetIterator) { + ((ResultSetIterator)iterator).close(); + } + } + + public Iterator getIndexInfo(final String catalog, final String schema, final String table) { + try { + log.debug("getIndexInfo(" + catalog + "." + schema + "." + table + ")"); + // Collect both Indexes from the 'ALL' data dicitonary table. + // It is assumed that atleast the TABLE name is supplied. + Statement stmt = this.getConnection().createStatement(); + StringBuffer query = new StringBuffer(); + + query.append("select a.column_name, decode(b.uniqueness,'UNIQUE','false','true'), a.index_owner, a.index_name, a.table_name "); + query.append("from all_ind_columns a, all_indexes b "); + query.append("where a.table_name = b.table_name "); + query.append("AND a.table_owner = b.table_owner "); + query.append("AND a.index_name = b.index_name "); + if (schema != null) { + query.append("AND a.table_owner='" + schema + "' "); + } + query.append("AND a.table_name = '" + table + "' "); + query.append("order by a.table_name, a.column_position "); + + if (log.isDebugEnabled()) + log.debug("getIndexInfo Query:" + query.toString()); + + ResultSet indexRs = stmt.executeQuery(query.toString()); + + return new ResultSetIterator(indexRs, sec) { + + 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", 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)); + + return element; + } + protected Throwable handleSQLException(SQLException e) { + throw sec.convert(e, "Exception while getting index info for " + Table.qualify(catalog, schema, table), null); + } + }; + } catch (SQLException e) { + throw sec.convert(e, "Exception while getting index info for " + Table.qualify(catalog, schema, table), null); + } + } + + public Iterator getColumns(final String catalog, final String schema, final String table, String column) { + try { + log.debug("getColumns(" + catalog + "." + schema + "." + table + "." + column + ")"); + // Collect Columns from the 'ALL' data dicitonary table. + // A decode is used to map the type name to the JDBC Type ID + Statement stmt = this.getConnection().createStatement(); + StringBuffer query = new StringBuffer(); + + query.append("select column_name, owner, decode(nullable,'N',0,1), "); + query.append("decode(data_type, 'FLOAT',decode(data_precision,null, data_length, data_precision), 'NUMBER', decode(data_precision,null, data_length, data_precision), data_length), "); + query.append("decode(data_type,'CHAR',1, 'DATE',91, 'FLOAT',6, 'LONG',-1, 'NUMBER',2, 'VARCHAR2',12, 'BFILE',-13, "); + query.append("'BLOB',2004, 'CLOB',2005, 'MLSLABEL',1111, 'NCHAR',1, 'NCLOB',2005, 'NVARCHAR2',12, "); + query.append("'RAW',-3, 'ROWID',1111, 'UROWID',1111, 'LONG RAW', -4, 'TIMESTAMP', 93, 'XMLTYPE',2005, 1111), "); + query.append("table_name, data_type, decode(data_scale, null, 0 ,data_scale) "); + query.append("from all_tab_columns "); + if (schema != null || table != null || column != null) + query.append("where "); + if (schema != null) { + query.append("owner='" + schema + "' "); + } + if (table != null) { + if (schema != null) + query.append("and "); + query.append("table_name = '" + table + "' "); + } + if (column != null) { + if (schema != null || table != null) + query.append("and "); + query.append("column_name = '" + column + "' "); + } + query.append("order by column_id "); + + if (log.isDebugEnabled()) + log.debug("getIndexInfo Query:" + query.toString()); + + ResultSet columnRs = stmt.executeQuery(query.toString()); + + return new ResultSetIterator(columnRs, sec) { + + Map element = new HashMap(); + protected Object convertRow(ResultSet rs) throws SQLException { + element.clear(); + element.put("COLUMN_NAME", rs.getString(1)); + element.put("TABLE_SCHEM", rs.getString(2)); + element.put("NULLABLE", new Integer(rs.getInt(3))); + element.put("COLUMN_SIZE", new Integer(rs.getInt(4))); + element.put("DATA_TYPE", new Integer(rs.getInt(5))); + element.put("TABLE_NAME", rs.getString(6)); + element.put("TYPE_NAME", rs.getString(7)); + element.put("DECIMAL_DIGITS", new Integer(rs.getInt(8))); + element.put("TABLE_CAT", null); + element.put("REMARKS", null); + return element; + } + protected Throwable handleSQLException(SQLException e) { + throw sec.convert(e, "Error while reading column meta data for " + Table.qualify(catalog, schema, table), null); + } + }; + } catch (SQLException e) { + throw sec.convert(e, "Error while reading column meta data for " + Table.qualify(catalog, schema, table), null); + } + } + + public Iterator getPrimaryKeys(final String catalog, final String schema, final String table) { + try { + log.debug("getPrimaryKeys(" + catalog + "." + schema + "." + table + ")"); + // Collect PrimaryKeys from the 'ALL' data dicitonary tables. + Statement stmt = this.getConnection().createStatement(); + StringBuffer query = new StringBuffer(); + + query.append("select c.table_name, c.column_name, c.position, c.constraint_name, c.owner "); + query.append("from all_cons_columns c, all_constraints k "); + query.append("where k.constraint_type = 'P' "); + query.append("AND k.constraint_name = c.constraint_name "); + query.append("AND k.table_name = c.table_name "); + query.append("AND k.owner = c.owner "); + if (schema != null) { + query.append("AND k.owner='" + schema + "' "); + } + if (table != null) { + query.append("AND k.table_name = '" + table + "' "); + } + query.append("order by c.table_name, c.constraint_name, c.position desc "); + + if (log.isDebugEnabled()) + log.debug("getPrimaryKeys Query:" + query.toString()); + + ResultSet pkeyRs = stmt.executeQuery(query.toString()); + + return new ResultSetIterator(pkeyRs, sec) { + + Map element = new HashMap(); + protected Object convertRow(ResultSet rs) throws SQLException { + element.clear(); + element.put("TABLE_NAME", rs.getString(1)); + element.put("COLUMN_NAME", rs.getString(2)); + element.put("KEY_SEQ", new Short(rs.getShort(3))); + element.put("PK_NAME", rs.getString(4)); + element.put("TABLE_SCHEM", rs.getString(5)); + element.put("TABLE_CAT", null); + return element; + } + protected Throwable handleSQLException(SQLException e) { + throw sec.convert(e, "Error while reading primary key meta data for " + Table.qualify(catalog, schema, table), null); + } + }; + } catch (SQLException e) { + throw sec.convert(e, "Error while reading primary key meta data for " + Table.qualify(catalog, schema, table), null); + } + } + + public Iterator getExportedKeys(final String catalog, final String schema, final String table) { + try { + log.debug("getExportedKeys(" + catalog + "." + schema + "." + table + ")"); + // Collect ExportedKeys from the 'ALL' data dicitonary tables. + + Statement stmt = this.getConnection().createStatement(); + StringBuffer query = new StringBuffer(); + + query.append("select p.table_name, p.owner, f.owner, f.table_name, fc.column_name, pc.column_name, f.constraint_name, fc.position "); + query.append("from all_cons_columns pc, all_constraints p, all_cons_columns fc, all_constraints f "); + query.append("where f.constraint_type = 'R' "); + query.append("AND p.owner = f.r_owner "); + query.append("AND p.constraint_name = f.r_constraint_name "); + query.append("AND p.constraint_type = 'P' "); + query.append("AND pc.owner = p.owner "); + query.append("AND pc.constraint_name = p.constraint_name "); + query.append("AND pc.table_name = p.table_name "); + query.append("AND fc.owner = f.owner "); + query.append("AND fc.constraint_name = f.constraint_name "); + query.append("AND fc.table_name = f.table_name "); + query.append("AND fc.position = pc.position "); + if (schema != null) { + query.append("AND p.owner='" + schema + "' "); + } + if (table != null) { + query.append("AND p.table_name = '" + table + "' "); + } + query.append("order by f.table_name, f.constraint_name, fc.position "); + + if (log.isDebugEnabled()) + log.debug("getExportedKeys Query:" + query.toString()); + + ResultSet pExportRs = stmt.executeQuery(query.toString()); + + return new ResultSetIterator(pExportRs, sec) { + + Map element = new HashMap(); + protected Object convertRow(ResultSet rs) throws SQLException { + element.clear(); + element.put( "PKTABLE_NAME", rs.getString(1)); + element.put( "PKTABLE_SCHEM", rs.getString(2)); + element.put( "PKTABLE_CAT", null); + element.put( "FKTABLE_CAT", null); + element.put( "FKTABLE_SCHEM",rs.getString(3)); + element.put( "FKTABLE_NAME", rs.getString(4)); + element.put( "FKCOLUMN_NAME", rs.getString(5)); + element.put( "PKCOLUMN_NAME", rs.getString(6)); + element.put( "FK_NAME", rs.getString(7)); + element.put( "KEY_SEQ", new Short(rs.getShort(8))); + return element; + } + protected Throwable handleSQLException(SQLException e) { + throw sec.convert(e, "Error while reading exported keys meta data for " + Table.qualify(catalog, schema, table), null); + } + }; + } catch (SQLException e) { + throw sec.convert(e, "Error while reading exported keys meta data for " + Table.qualify(catalog, schema, table), null); + } + } + + protected Connection getConnection() throws SQLException { + if(connection==null) { + connection = provider.getConnection(); + } + return connection; + } + + public boolean needQuote(String name) { + // TODO: use jdbc metadata to decide on this. but for now we just handle the most typical cases. + if(name.indexOf('-')>0) return true; + if(name.indexOf(' ')>0) return true; + return false; + } +}