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();
}
}
}
