protected void getColumnsForUniqueConstraint( Connection jdbcConnection, UniqueConstraint constraint, String schema) throws SQLException { PreparedStatement stmt = null; ResultSet rs = null; try { stmt = jdbcConnection.prepareStatement( "select ucc.column_name from all_cons_columns ucc where ucc.constraint_name=? and ucc.owner=? order by ucc.position"); stmt.setString(1, constraint.getName()); stmt.setString(2, schema); rs = stmt.executeQuery(); while (rs.next()) { String columnName = rs.getString("column_name"); constraint.getColumns().add(columnName); } } finally { if (rs != null) { try { rs.close(); } catch (SQLException ignored) { } } if (stmt != null) stmt.close(); } }
@Override protected void readColumns( DatabaseSnapshot snapshot, String schema, DatabaseMetaData databaseMetaData) throws SQLException, DatabaseException { findIntegerColumns(snapshot, schema); super.readColumns(snapshot, schema, databaseMetaData); /* * Code Description: * Finding all 'tablespace' attributes of column's PKs * */ Database database = snapshot.getDatabase(); Statement statement = null; ResultSet rs = null; try { statement = ((JdbcConnection) database.getConnection()).getUnderlyingConnection().createStatement(); // Setting default schema name. Needed for correct statement generation if (schema == null) schema = database.convertRequestedSchemaToSchema(schema); String query = "select ui.tablespace_name TABLESPACE, ucc.table_name TABLE_NAME, ucc.column_name COLUMN_NAME FROM all_indexes ui , all_constraints uc , all_cons_columns ucc where uc.constraint_type = 'P' and ucc.constraint_name = uc.constraint_name and uc.index_name = ui.index_name and uc.owner = '" + schema + "' and ui.table_owner = '" + schema + "' and ucc.owner = '" + schema + "'"; rs = statement.executeQuery(query); while (rs.next()) { Column column = snapshot.getColumn(rs.getString("TABLE_NAME"), rs.getString("COLUMN_NAME")); // setting up tablespace property to column, to configure it's PK-index if (column == null) { continue; // probably a different schema } column.setTablespace(rs.getString("TABLESPACE")); } } finally { if (rs != null) { try { rs.close(); } catch (SQLException ignore) { } } if (statement != null) { try { statement.close(); } catch (SQLException ignore) { } } } }
/** * Method finds all INTEGER columns in snapshot's database * * @param snapshot current database snapshot * @return String list with names of all INTEGER columns * @throws java.sql.SQLException execute statement error */ private List<String> findIntegerColumns(DatabaseSnapshot snapshot, String schema) throws SQLException, DatabaseException { Database database = snapshot.getDatabase(); // Setting default schema name. Needed for correct statement generation if (schema == null) { schema = database.convertRequestedSchemaToSchema(schema); } Statement statement = ((JdbcConnection) database.getConnection()).getUnderlyingConnection().createStatement(); ResultSet integerListRS = null; // Finding all columns created as 'INTEGER' try { integerListRS = statement.executeQuery( "select TABLE_NAME, COLUMN_NAME from all_tab_columns where data_precision is null and data_scale = 0 and data_type = 'NUMBER' and owner = '" + schema + "'"); while (integerListRS.next()) { integerList.add( integerListRS.getString("TABLE_NAME") + "." + integerListRS.getString("COLUMN_NAME")); } } finally { if (integerListRS != null) { try { integerListRS.close(); } catch (SQLException ignore) { } } if (statement != null) { try { statement.close(); } catch (SQLException ignore) { } } } return integerList; }
@Override protected void readUniqueConstraints( DatabaseSnapshot snapshot, String schema, DatabaseMetaData databaseMetaData) throws DatabaseException, SQLException { Database database = snapshot.getDatabase(); updateListeners("Reading unique constraints for " + database.toString() + " ..."); List<UniqueConstraint> foundUC = new ArrayList<UniqueConstraint>(); Connection jdbcConnection = ((JdbcConnection) database.getConnection()).getUnderlyingConnection(); PreparedStatement statement = null; ResultSet rs = null; // Setting default schema name. Needed for correct statement generation if (schema == null) schema = database.convertRequestedSchemaToSchema(schema); try { String query = "select uc.constraint_name,uc.table_name,uc.status,uc.deferrable,uc.deferred,ui.tablespace_name from all_constraints uc, all_cons_columns ucc, all_indexes ui where uc.constraint_type='U' and uc.index_name = ui.index_name and uc.constraint_name = ucc.constraint_name and uc.owner = '" + schema + "' and ui.table_owner = '" + schema + "' and ucc.owner = '" + schema + "'"; statement = jdbcConnection.prepareStatement(query); rs = statement.executeQuery(); while (rs.next()) { String constraintName = rs.getString("constraint_name"); String tableName = rs.getString("table_name"); String status = rs.getString("status"); String deferrable = rs.getString("deferrable"); String deferred = rs.getString("deferred"); String tablespace = rs.getString("tablespace_name"); UniqueConstraint constraintInformation = new UniqueConstraint(); constraintInformation.setName(constraintName); constraintInformation.setTablespace(tablespace); if (!database.isSystemTable(null, schema, tableName) && !database.isLiquibaseTable(tableName)) { Table table = snapshot.getTable(tableName); if (table == null) { continue; // probably different schema } constraintInformation.setTable(table); constraintInformation.setDisabled("DISABLED".equals(status)); if ("DEFERRABLE".equals(deferrable)) { constraintInformation.setDeferrable(true); constraintInformation.setInitiallyDeferred("DEFERRED".equals(deferred)); } getColumnsForUniqueConstraint(jdbcConnection, constraintInformation, schema); foundUC.add(constraintInformation); } } snapshot.getUniqueConstraints().addAll(foundUC); } finally { try { rs.close(); } catch (SQLException ignored) { } if (statement != null) { statement.close(); } } }