Coverage Report

Coverage Report - liquibase.snapshot.jvm.OracleDatabaseSnapshotGenerator

Classes in this File

Line Coverage

Branch Coverage

Complexity

OracleDatabaseSnapshotGenerator

0/247

0/112

5.769

 package liquibase.snapshot.jvm;
 
 import java.sql.Connection;
 import java.sql.DatabaseMetaData;
 import java.sql.PreparedStatement;
 import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.sql.Statement;
 import java.sql.Types;
 import java.util.ArrayList;
 import java.util.HashMap;
 import java.util.List;
 import java.util.Map;
 
 import liquibase.database.Database;
 import liquibase.database.core.OracleDatabase;
 import liquibase.database.jvm.JdbcConnection;
 import liquibase.database.structure.Column;
 import liquibase.database.structure.ForeignKey;
 import liquibase.database.structure.ForeignKeyConstraintType;
 import liquibase.database.structure.ForeignKeyInfo;
 import liquibase.database.structure.Index;
 import liquibase.database.structure.PrimaryKey;
 import liquibase.database.structure.Table;
 import liquibase.database.structure.UniqueConstraint;
 import liquibase.exception.DatabaseException;
 import liquibase.snapshot.DatabaseSnapshot;
 import liquibase.util.JdbcUtils;
 
 public class OracleDatabaseSnapshotGenerator extends JdbcDatabaseSnapshotGenerator {
 
     private List<String> integerList = new ArrayList<String>();
 
     @Override
     public boolean supports(Database database) {
         return database instanceof OracleDatabase;
     }
 
     @Override
     public int getPriority(Database database) {
         return PRIORITY_DATABASE;
     }
 
     @Override
     protected String convertTableNameToDatabaseTableName(String tableName) {
         return tableName.toUpperCase();
     }
 
     @Override
     protected String convertColumnNameToDatabaseTableName(String columnName) {
         return columnName.toUpperCase();
     }
 
     /**
      * Oracle specific implementation
      */
     @Override
     protected void getColumnTypeAndDefValue(Column columnInfo, ResultSet rs, Database database) throws SQLException,
             DatabaseException {
         super.getColumnTypeAndDefValue(columnInfo, rs, database);
 
         // Exclusive setting for oracle INTEGER type
         // Details:
         // INTEGER means NUMBER type with 'data_precision IS NULL and scale = 0'
         if (columnInfo.getDataType() == Types.INTEGER) {
             columnInfo.setTypeName("INTEGER");
         }
 
         String columnTypeName = rs.getString("TYPE_NAME");
         if ("VARCHAR2".equals(columnTypeName)) {
             int charOctetLength = rs.getInt("CHAR_OCTET_LENGTH");
             int columnSize = rs.getInt("COLUMN_SIZE");
             if (columnSize == charOctetLength) {
                 columnInfo.setLengthSemantics(Column.LengthSemantics.BYTE);
             } else {
                 columnInfo.setLengthSemantics(Column.LengthSemantics.CHAR);
             }
         }
     }
 
     @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();
             }
 
         }
     }
 
     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 configureColumnType(Column column, ResultSet rs) throws SQLException {
         if (integerList.contains(column.getTable().getName() + "." + column.getName())) {
             column.setDataType(Types.INTEGER);
         } else {
             column.setDataType(rs.getInt("DATA_TYPE"));
         }
         column.setColumnSize(rs.getInt("COLUMN_SIZE"));
         column.setDecimalDigits(rs.getInt("DECIMAL_DIGITS"));
 
         // Set true, if precision should be initialize
         column.setInitPrecision(!((column.getDataType() == Types.DECIMAL || column.getDataType() == Types.NUMERIC || column
                 .getDataType() == Types.REAL) && rs.getString("DECIMAL_DIGITS") == null));
     }
 
     @Override
     public List<ForeignKey> getAdditionalForeignKeys(String schemaName, Database database) throws DatabaseException {
         List<ForeignKey> foreignKeys = super.getAdditionalForeignKeys(schemaName, database);
 
         // Setting default schema name. Needed for correct statement generation
         if (schemaName == null) {
             schemaName = database.convertRequestedSchemaToSchema(schemaName);
         }
 
         // Create SQL statement to select all FKs in database which referenced to unique columns
         String query = "select uc_fk.constraint_name FK_NAME,uc_fk.owner FKTABLE_SCHEM,ucc_fk.table_name FKTABLE_NAME,ucc_fk.column_name FKCOLUMN_NAME,decode(uc_fk.deferrable, 'DEFERRABLE', 5 ,'NOT DEFERRABLE', 7 , 'DEFERRED', 6 ) DEFERRABILITY, decode(uc_fk.delete_rule, 'CASCADE', 0,'NO ACTION', 3) DELETE_RULE,ucc_rf.table_name PKTABLE_NAME,ucc_rf.column_name PKCOLUMN_NAME from all_cons_columns ucc_fk,all_constraints uc_fk,all_cons_columns ucc_rf,all_constraints uc_rf where uc_fk.CONSTRAINT_NAME = ucc_fk.CONSTRAINT_NAME and uc_fk.constraint_type='R' and uc_fk.r_constraint_name=ucc_rf.CONSTRAINT_NAME and uc_rf.constraint_name = ucc_rf.constraint_name and uc_rf.constraint_type = 'U' and uc_fk.owner = '"
                 + schemaName
                 + "' and ucc_fk.owner = '"
                 + schemaName
                 + "' and uc_rf.owner = '"
                 + schemaName
                 + "' and ucc_rf.owner = '" + schemaName + "'";
         Statement statement = null;
         ResultSet rs = null;
         try {
             statement = ((JdbcConnection) database.getConnection()).getUnderlyingConnection().createStatement();
             rs = statement.executeQuery(query);
             while (rs.next()) {
                 ForeignKeyInfo fkInfo = new ForeignKeyInfo();
                 fkInfo.setReferencesUniqueColumn(true);
                 fkInfo.setFkName(convertFromDatabaseName(rs.getString("FK_NAME")));
                 fkInfo.setFkSchema(convertFromDatabaseName(rs.getString("FKTABLE_SCHEM")));
                 fkInfo.setFkTableName(convertFromDatabaseName(rs.getString("FKTABLE_NAME")));
                 fkInfo.setFkColumn(convertFromDatabaseName(rs.getString("FKCOLUMN_NAME")));
 
                 fkInfo.setPkTableName(convertFromDatabaseName(rs.getString("PKTABLE_NAME")));
                 fkInfo.setPkColumn(convertFromDatabaseName(rs.getString("PKCOLUMN_NAME")));
 
                 fkInfo.setDeferrablility(rs.getShort("DEFERRABILITY"));
                 ForeignKeyConstraintType deleteRule = convertToForeignKeyConstraintType(rs.getInt("DELETE_RULE"));
                 if (rs.wasNull()) {
                     deleteRule = null;
                 }
                 fkInfo.setDeleteRule(deleteRule);
                 foreignKeys.add(generateForeignKey(fkInfo, database, foreignKeys));
             }
         } catch (SQLException e) {
             throw new DatabaseException("Can't execute selection query to generate list of foreign keys", e);
         } finally {
             JdbcUtils.closeResultSet(rs);
             JdbcUtils.closeStatement(statement);
         }
         return foreignKeys;
     }
 
     @Override
     protected void readIndexes(DatabaseSnapshot snapshot, String schema, DatabaseMetaData databaseMetaData)
             throws DatabaseException, SQLException {
         Database database = snapshot.getDatabase();
         updateListeners("Reading indexes for " + database.toString() + " ...");
 
         String query = "select aic.index_name, 3 AS TYPE, aic.table_name, aic.column_name, aic.column_position AS ORDINAL_POSITION, null AS FILTER_CONDITION, ai.tablespace_name AS TABLESPACE, ai.uniqueness FROM all_ind_columns aic, all_indexes ai WHERE aic.table_owner='"
                 + database.convertRequestedSchemaToSchema(schema)
                 + "' and aic.index_name = ai.index_name ORDER BY INDEX_NAME, ORDINAL_POSITION";
         Statement statement = null;
         ResultSet rs = null;
         Map<String, Index> indexMap = null;
         try {
             statement = ((JdbcConnection) database.getConnection()).getUnderlyingConnection().createStatement();
             rs = statement.executeQuery(query);
 
             indexMap = new HashMap<String, Index>();
             while (rs.next()) {
                 String indexName = convertFromDatabaseName(rs.getString("INDEX_NAME"));
                 String tableName = rs.getString("TABLE_NAME");
                 String tableSpace = rs.getString("TABLESPACE");
                 String columnName = convertFromDatabaseName(rs.getString("COLUMN_NAME"));
                 if (columnName == null) {
                     // nothing to index, not sure why these come through sometimes
                     continue;
                 }
                 short type = rs.getShort("TYPE");
 
                 boolean nonUnique;
 
                 String uniqueness = rs.getString("UNIQUENESS");
 
                 if ("UNIQUE".equals(uniqueness)) {
                     nonUnique = false;
                 } else {
                     nonUnique = true;
                 }
 
                 short position = rs.getShort("ORDINAL_POSITION");
                 String filterCondition = rs.getString("FILTER_CONDITION");
 
                 if (type == DatabaseMetaData.tableIndexStatistic) {
                     continue;
                 }
 
                 Index index;
                 if (indexMap.containsKey(indexName)) {
                     index = indexMap.get(indexName);
                 } else {
                     index = new Index();
                     Table table = snapshot.getTable(tableName);
                     if (table == null) {
                         continue; // probably different schema
                     }
                     index.setTable(table);
                     index.setTablespace(tableSpace);
                     index.setName(indexName);
                     index.setUnique(!nonUnique);
                     index.setFilterCondition(filterCondition);
                     indexMap.put(indexName, index);
                 }
 
                 for (int i = index.getColumns().size(); i < position; i++) {
                     index.getColumns().add(null);
                 }
                 index.getColumns().set(position - 1, columnName);
             }
         } finally {
             JdbcUtils.closeResultSet(rs);
             JdbcUtils.closeStatement(statement);
         }
 
         for (Map.Entry<String, Index> entry : indexMap.entrySet()) {
             snapshot.getIndexes().add(entry.getValue());
         }
 
         /*
          * marks indexes as "associated with" instead of "remove it" Index should have associations with: foreignKey,
          * primaryKey or uniqueConstraint
          */
         for (Index index : snapshot.getIndexes()) {
             for (PrimaryKey pk : snapshot.getPrimaryKeys()) {
                 if (index.getTable().getName().equalsIgnoreCase(pk.getTable().getName())
                         && index.getColumnNames().equals(pk.getColumnNames())) {
                     index.addAssociatedWith(Index.MARK_PRIMARY_KEY);
                 }
             }
             for (ForeignKey fk : snapshot.getForeignKeys()) {
                 if (index.getTable().getName().equalsIgnoreCase(fk.getForeignKeyTable().getName())
                         && index.getColumnNames().equals(fk.getForeignKeyColumns())) {
                     index.addAssociatedWith(Index.MARK_FOREIGN_KEY);
                 }
             }
             for (UniqueConstraint uc : snapshot.getUniqueConstraints()) {
                 if (index.getTable().getName().equalsIgnoreCase(uc.getTable().getName())
                         && index.getColumnNames().equals(uc.getColumnNames())) {
                     index.addAssociatedWith(Index.MARK_UNIQUE_CONSTRAINT);
                 }
             }
         }
     }
 
     @Override
     protected void readPrimaryKeys(DatabaseSnapshot snapshot, String schema, DatabaseMetaData databaseMetaData)
             throws DatabaseException, SQLException {
         Database database = snapshot.getDatabase();
         updateListeners("Reading primary keys for " + database.toString() + " ...");
 
         // we can't add directly to the this.primaryKeys hashSet because adding columns to an exising PK changes the
         // hashCode and .contains() fails
         List<PrimaryKey> foundPKs = new ArrayList<PrimaryKey>();
         // Setting default schema name. Needed for correct statement generation
         if (schema == null)
             schema = database.convertRequestedSchemaToSchema(schema);
 
         String query = "select uc.table_name TABLE_NAME,ucc.column_name COLUMN_NAME,ucc.position KEY_SEQ,uc.constraint_name PK_NAME,ui.tablespace_name TABLESPACE from all_constraints uc,all_indexes ui,all_cons_columns ucc where uc.constraint_type = 'P' 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 statement = null;
         ResultSet rs = null;
         try {
             statement = ((JdbcConnection) database.getConnection()).getUnderlyingConnection().createStatement();
             rs = statement.executeQuery(query);
 
             while (rs.next()) {
                 String tableName = convertFromDatabaseName(rs.getString("TABLE_NAME"));
                 String tablespace = convertFromDatabaseName(rs.getString("TABLESPACE"));
                 String columnName = convertFromDatabaseName(rs.getString("COLUMN_NAME"));
                 short position = rs.getShort("KEY_SEQ");
 
                 boolean foundExistingPK = false;
                 for (PrimaryKey pk : foundPKs) {
                     if (pk.getTable().getName().equals(tableName)) {
                         pk.addColumnName(position - 1, columnName);
 
                         foundExistingPK = true;
                     }
                 }
 
                 if (!foundExistingPK && !database.isLiquibaseTable(tableName)) {
                     PrimaryKey primaryKey = new PrimaryKey();
                     primaryKey.setTablespace(tablespace);
                     Table table = snapshot.getTable(tableName);
                     if (table == null) {
                         continue; // probably a different schema
                     }
                     primaryKey.setTable(table);
                     primaryKey.addColumnName(position - 1, columnName);
                     primaryKey.setName(convertPrimaryKeyName(rs.getString("PK_NAME")));
 
                     foundPKs.add(primaryKey);
                 }
             }
         } finally {
             JdbcUtils.closeResultSet(rs);
             JdbcUtils.closeStatement(statement);
         }
 
         snapshot.getPrimaryKeys().addAll(foundPKs);
     }
 }

1		package liquibase.snapshot.jvm;
2
3		import java.sql.Connection;
4		import java.sql.DatabaseMetaData;
5		import java.sql.PreparedStatement;
6		import java.sql.ResultSet;
7		import java.sql.SQLException;
8		import java.sql.Statement;
9		import java.sql.Types;
10		import java.util.ArrayList;
11		import java.util.HashMap;
12		import java.util.List;
13		import java.util.Map;
14
15		import liquibase.database.Database;
16		import liquibase.database.core.OracleDatabase;
17		import liquibase.database.jvm.JdbcConnection;
18		import liquibase.database.structure.Column;
19		import liquibase.database.structure.ForeignKey;
20		import liquibase.database.structure.ForeignKeyConstraintType;
21		import liquibase.database.structure.ForeignKeyInfo;
22		import liquibase.database.structure.Index;
23		import liquibase.database.structure.PrimaryKey;
24		import liquibase.database.structure.Table;
25		import liquibase.database.structure.UniqueConstraint;
26		import liquibase.exception.DatabaseException;
27		import liquibase.snapshot.DatabaseSnapshot;
28		import liquibase.util.JdbcUtils;
29
30	0	public class OracleDatabaseSnapshotGenerator extends JdbcDatabaseSnapshotGenerator {
31
32	0	private List<String> integerList = new ArrayList<String>();
33
34		@Override
35		public boolean supports(Database database) {
36	0	return database instanceof OracleDatabase;
37		}
38
39		@Override
40		public int getPriority(Database database) {
41	0	return PRIORITY_DATABASE;
42		}
43
44		@Override
45		protected String convertTableNameToDatabaseTableName(String tableName) {
46	0	return tableName.toUpperCase();
47		}
48
49		@Override
50		protected String convertColumnNameToDatabaseTableName(String columnName) {
51	0	return columnName.toUpperCase();
52		}
53
54		/**
55		* Oracle specific implementation
56		*/
57		@Override
58		protected void getColumnTypeAndDefValue(Column columnInfo, ResultSet rs, Database database) throws SQLException,
59		DatabaseException {
60	0	super.getColumnTypeAndDefValue(columnInfo, rs, database);
61
62		// Exclusive setting for oracle INTEGER type
63		// Details:
64		// INTEGER means NUMBER type with 'data_precision IS NULL and scale = 0'
65	0	if (columnInfo.getDataType() == Types.INTEGER) {
66	0	columnInfo.setTypeName("INTEGER");
67		}
68
69	0	String columnTypeName = rs.getString("TYPE_NAME");
70	0	if ("VARCHAR2".equals(columnTypeName)) {
71	0	int charOctetLength = rs.getInt("CHAR_OCTET_LENGTH");
72	0	int columnSize = rs.getInt("COLUMN_SIZE");
73	0	if (columnSize == charOctetLength) {
74	0	columnInfo.setLengthSemantics(Column.LengthSemantics.BYTE);
75		} else {
76	0	columnInfo.setLengthSemantics(Column.LengthSemantics.CHAR);
77		}
78		}
79	0	}
80
81		@Override
82		protected void readUniqueConstraints(DatabaseSnapshot snapshot, String schema, DatabaseMetaData databaseMetaData)
83		throws DatabaseException, SQLException {
84	0	Database database = snapshot.getDatabase();
85	0	updateListeners("Reading unique constraints for " + database.toString() + " ...");
86	0	List<UniqueConstraint> foundUC = new ArrayList<UniqueConstraint>();
87
88	0	Connection jdbcConnection = ((JdbcConnection) database.getConnection()).getUnderlyingConnection();
89
90	0	PreparedStatement statement = null;
91	0	ResultSet rs = null;
92
93		// Setting default schema name. Needed for correct statement generation
94	0	if (schema == null)
95	0	schema = database.convertRequestedSchemaToSchema(schema);
96
97		try {
98	0	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 = '"
99		+ schema + "' and ui.table_owner = '" + schema + "' and ucc.owner = '" + schema + "'";
100	0	statement = jdbcConnection.prepareStatement(query);
101	0	rs = statement.executeQuery();
102	0	while (rs.next()) {
103	0	String constraintName = rs.getString("constraint_name");
104	0	String tableName = rs.getString("table_name");
105	0	String status = rs.getString("status");
106	0	String deferrable = rs.getString("deferrable");
107	0	String deferred = rs.getString("deferred");
108	0	String tablespace = rs.getString("tablespace_name");
109	0	UniqueConstraint constraintInformation = new UniqueConstraint();
110	0	constraintInformation.setName(constraintName);
111	0	constraintInformation.setTablespace(tablespace);
112	0	if (!database.isSystemTable(null, schema, tableName) && !database.isLiquibaseTable(tableName)) {
113	0	Table table = snapshot.getTable(tableName);
114	0	if (table == null) {
115	0	continue; // probably different schema
116		}
117	0	constraintInformation.setTable(table);
118	0	constraintInformation.setDisabled("DISABLED".equals(status));
119	0	if ("DEFERRABLE".equals(deferrable)) {
120	0	constraintInformation.setDeferrable(true);
121	0	constraintInformation.setInitiallyDeferred("DEFERRED".equals(deferred));
122		}
123	0	getColumnsForUniqueConstraint(jdbcConnection, constraintInformation, schema);
124	0	foundUC.add(constraintInformation);
125		}
126	0	}
127	0	snapshot.getUniqueConstraints().addAll(foundUC);
128		} finally {
129	0	try {
130	0	rs.close();
131	0	} catch (SQLException ignored) {
132	0	}
133	0	if (statement != null) {
134	0	statement.close();
135		}
136
137		}
138	0	}
139
140		protected void getColumnsForUniqueConstraint(Connection jdbcConnection, UniqueConstraint constraint, String schema)
141		throws SQLException {
142	0	PreparedStatement stmt = null;
143	0	ResultSet rs = null;
144		try {
145	0	stmt = jdbcConnection
146		.prepareStatement("select ucc.column_name from all_cons_columns ucc where ucc.constraint_name=? and ucc.owner=? order by ucc.position");
147	0	stmt.setString(1, constraint.getName());
148	0	stmt.setString(2, schema);
149	0	rs = stmt.executeQuery();
150	0	while (rs.next()) {
151	0	String columnName = rs.getString("column_name");
152	0	constraint.getColumns().add(columnName);
153	0	}
154		} finally {
155	0	if (rs != null) {
156		try {
157	0	rs.close();
158	0	} catch (SQLException ignored) {
159	0	}
160		}
161	0	if (stmt != null)
162	0	stmt.close();
163		}
164	0	}
165
166		@Override
167		protected void readColumns(DatabaseSnapshot snapshot, String schema, DatabaseMetaData databaseMetaData)
168		throws SQLException, DatabaseException {
169	0	findIntegerColumns(snapshot, schema);
170	0	super.readColumns(snapshot, schema, databaseMetaData);
171
172		/*
173		* Code Description: Finding all 'tablespace' attributes of column's PKs
174		*/
175	0	Database database = snapshot.getDatabase();
176	0	Statement statement = null;
177	0	ResultSet rs = null;
178		try {
179	0	statement = ((JdbcConnection) database.getConnection()).getUnderlyingConnection().createStatement();
180
181		// Setting default schema name. Needed for correct statement generation
182	0	if (schema == null)
183	0	schema = database.convertRequestedSchemaToSchema(schema);
184
185	0	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 = '"
186		+ schema + "' and ui.table_owner = '" + schema + "' and ucc.owner = '" + schema + "'";
187	0	rs = statement.executeQuery(query);
188
189	0	while (rs.next()) {
190	0	Column column = snapshot.getColumn(rs.getString("TABLE_NAME"), rs.getString("COLUMN_NAME"));
191		// setting up tablespace property to column, to configure it's PK-index
192	0	if (column == null) {
193	0	continue; // probably a different schema
194		}
195	0	column.setTablespace(rs.getString("TABLESPACE"));
196	0	}
197		} finally {
198	0	if (rs != null) {
199		try {
200	0	rs.close();
201	0	} catch (SQLException ignore) {
202	0	}
203		}
204	0	if (statement != null) {
205		try {
206	0	statement.close();
207	0	} catch (SQLException ignore) {
208	0	}
209		}
210		}
211
212	0	}
213
214		/**
215		* Method finds all INTEGER columns in snapshot's database
216		*
217		* @param snapshot
218		* current database snapshot
219		* @return String list with names of all INTEGER columns
220		* @throws java.sql.SQLException
221		* execute statement error
222		*/
223		private List<String> findIntegerColumns(DatabaseSnapshot snapshot, String schema) throws SQLException,
224		DatabaseException {
225
226	0	Database database = snapshot.getDatabase();
227		// Setting default schema name. Needed for correct statement generation
228	0	if (schema == null) {
229	0	schema = database.convertRequestedSchemaToSchema(schema);
230		}
231	0	Statement statement = ((JdbcConnection) database.getConnection()).getUnderlyingConnection().createStatement();
232	0	ResultSet integerListRS = null;
233		// Finding all columns created as 'INTEGER'
234		try {
235	0	integerListRS = statement
236		.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 = '"
237		+ schema + "'");
238	0	while (integerListRS.next()) {
239	0	integerList.add(integerListRS.getString("TABLE_NAME") + "." + integerListRS.getString("COLUMN_NAME"));
240		}
241		} finally {
242	0	if (integerListRS != null) {
243		try {
244	0	integerListRS.close();
245	0	} catch (SQLException ignore) {
246	0	}
247		}
248
249	0	if (statement != null) {
250		try {
251	0	statement.close();
252	0	} catch (SQLException ignore) {
253	0	}
254		}
255		}
256
257	0	return integerList;
258		}
259
260		@Override
261		protected void configureColumnType(Column column, ResultSet rs) throws SQLException {
262	0	if (integerList.contains(column.getTable().getName() + "." + column.getName())) {
263	0	column.setDataType(Types.INTEGER);
264		} else {
265	0	column.setDataType(rs.getInt("DATA_TYPE"));
266		}
267	0	column.setColumnSize(rs.getInt("COLUMN_SIZE"));
268	0	column.setDecimalDigits(rs.getInt("DECIMAL_DIGITS"));
269
270		// Set true, if precision should be initialize
271	0	column.setInitPrecision(!((column.getDataType() == Types.DECIMAL \|\| column.getDataType() == Types.NUMERIC \|\| column
272		.getDataType() == Types.REAL) && rs.getString("DECIMAL_DIGITS") == null));
273	0	}
274
275		@Override
276		public List<ForeignKey> getAdditionalForeignKeys(String schemaName, Database database) throws DatabaseException {
277	0	List<ForeignKey> foreignKeys = super.getAdditionalForeignKeys(schemaName, database);
278
279		// Setting default schema name. Needed for correct statement generation
280	0	if (schemaName == null) {
281	0	schemaName = database.convertRequestedSchemaToSchema(schemaName);
282		}
283
284		// Create SQL statement to select all FKs in database which referenced to unique columns
285	0	String query = "select uc_fk.constraint_name FK_NAME,uc_fk.owner FKTABLE_SCHEM,ucc_fk.table_name FKTABLE_NAME,ucc_fk.column_name FKCOLUMN_NAME,decode(uc_fk.deferrable, 'DEFERRABLE', 5 ,'NOT DEFERRABLE', 7 , 'DEFERRED', 6 ) DEFERRABILITY, decode(uc_fk.delete_rule, 'CASCADE', 0,'NO ACTION', 3) DELETE_RULE,ucc_rf.table_name PKTABLE_NAME,ucc_rf.column_name PKCOLUMN_NAME from all_cons_columns ucc_fk,all_constraints uc_fk,all_cons_columns ucc_rf,all_constraints uc_rf where uc_fk.CONSTRAINT_NAME = ucc_fk.CONSTRAINT_NAME and uc_fk.constraint_type='R' and uc_fk.r_constraint_name=ucc_rf.CONSTRAINT_NAME and uc_rf.constraint_name = ucc_rf.constraint_name and uc_rf.constraint_type = 'U' and uc_fk.owner = '"
286		+ schemaName
287		+ "' and ucc_fk.owner = '"
288		+ schemaName
289		+ "' and uc_rf.owner = '"
290		+ schemaName
291		+ "' and ucc_rf.owner = '" + schemaName + "'";
292	0	Statement statement = null;
293	0	ResultSet rs = null;
294		try {
295	0	statement = ((JdbcConnection) database.getConnection()).getUnderlyingConnection().createStatement();
296	0	rs = statement.executeQuery(query);
297	0	while (rs.next()) {
298	0	ForeignKeyInfo fkInfo = new ForeignKeyInfo();
299	0	fkInfo.setReferencesUniqueColumn(true);
300	0	fkInfo.setFkName(convertFromDatabaseName(rs.getString("FK_NAME")));
301	0	fkInfo.setFkSchema(convertFromDatabaseName(rs.getString("FKTABLE_SCHEM")));
302	0	fkInfo.setFkTableName(convertFromDatabaseName(rs.getString("FKTABLE_NAME")));
303	0	fkInfo.setFkColumn(convertFromDatabaseName(rs.getString("FKCOLUMN_NAME")));
304
305	0	fkInfo.setPkTableName(convertFromDatabaseName(rs.getString("PKTABLE_NAME")));
306	0	fkInfo.setPkColumn(convertFromDatabaseName(rs.getString("PKCOLUMN_NAME")));
307
308	0	fkInfo.setDeferrablility(rs.getShort("DEFERRABILITY"));
309	0	ForeignKeyConstraintType deleteRule = convertToForeignKeyConstraintType(rs.getInt("DELETE_RULE"));
310	0	if (rs.wasNull()) {
311	0	deleteRule = null;
312		}
313	0	fkInfo.setDeleteRule(deleteRule);
314	0	foreignKeys.add(generateForeignKey(fkInfo, database, foreignKeys));
315	0	}
316	0	} catch (SQLException e) {
317	0	throw new DatabaseException("Can't execute selection query to generate list of foreign keys", e);
318		} finally {
319	0	JdbcUtils.closeResultSet(rs);
320	0	JdbcUtils.closeStatement(statement);
321	0	}
322	0	return foreignKeys;
323		}
324
325		@Override
326		protected void readIndexes(DatabaseSnapshot snapshot, String schema, DatabaseMetaData databaseMetaData)
327		throws DatabaseException, SQLException {
328	0	Database database = snapshot.getDatabase();
329	0	updateListeners("Reading indexes for " + database.toString() + " ...");
330
331	0	String query = "select aic.index_name, 3 AS TYPE, aic.table_name, aic.column_name, aic.column_position AS ORDINAL_POSITION, null AS FILTER_CONDITION, ai.tablespace_name AS TABLESPACE, ai.uniqueness FROM all_ind_columns aic, all_indexes ai WHERE aic.table_owner='"
332		+ database.convertRequestedSchemaToSchema(schema)
333		+ "' and aic.index_name = ai.index_name ORDER BY INDEX_NAME, ORDINAL_POSITION";
334	0	Statement statement = null;
335	0	ResultSet rs = null;
336	0	Map<String, Index> indexMap = null;
337		try {
338	0	statement = ((JdbcConnection) database.getConnection()).getUnderlyingConnection().createStatement();
339	0	rs = statement.executeQuery(query);
340
341	0	indexMap = new HashMap<String, Index>();
342	0	while (rs.next()) {
343	0	String indexName = convertFromDatabaseName(rs.getString("INDEX_NAME"));
344	0	String tableName = rs.getString("TABLE_NAME");
345	0	String tableSpace = rs.getString("TABLESPACE");
346	0	String columnName = convertFromDatabaseName(rs.getString("COLUMN_NAME"));
347	0	if (columnName == null) {
348		// nothing to index, not sure why these come through sometimes
349	0	continue;
350		}
351	0	short type = rs.getShort("TYPE");
352
353		boolean nonUnique;
354
355	0	String uniqueness = rs.getString("UNIQUENESS");
356
357	0	if ("UNIQUE".equals(uniqueness)) {
358	0	nonUnique = false;
359		} else {
360	0	nonUnique = true;
361		}
362
363	0	short position = rs.getShort("ORDINAL_POSITION");
364	0	String filterCondition = rs.getString("FILTER_CONDITION");
365
366	0	if (type == DatabaseMetaData.tableIndexStatistic) {
367	0	continue;
368		}
369
370		Index index;
371	0	if (indexMap.containsKey(indexName)) {
372	0	index = indexMap.get(indexName);
373		} else {
374	0	index = new Index();
375	0	Table table = snapshot.getTable(tableName);
376	0	if (table == null) {
377	0	continue; // probably different schema
378		}
379	0	index.setTable(table);
380	0	index.setTablespace(tableSpace);
381	0	index.setName(indexName);
382	0	index.setUnique(!nonUnique);
383	0	index.setFilterCondition(filterCondition);
384	0	indexMap.put(indexName, index);
385		}
386
387	0	for (int i = index.getColumns().size(); i < position; i++) {
388	0	index.getColumns().add(null);
389		}
390	0	index.getColumns().set(position - 1, columnName);
391	0	}
392		} finally {
393	0	JdbcUtils.closeResultSet(rs);
394	0	JdbcUtils.closeStatement(statement);
395	0	}
396
397	0	for (Map.Entry<String, Index> entry : indexMap.entrySet()) {
398	0	snapshot.getIndexes().add(entry.getValue());
399		}
400
401		/*
402		* marks indexes as "associated with" instead of "remove it" Index should have associations with: foreignKey,
403		* primaryKey or uniqueConstraint
404		*/
405	0	for (Index index : snapshot.getIndexes()) {
406	0	for (PrimaryKey pk : snapshot.getPrimaryKeys()) {
407	0	if (index.getTable().getName().equalsIgnoreCase(pk.getTable().getName())
408		&& index.getColumnNames().equals(pk.getColumnNames())) {
409	0	index.addAssociatedWith(Index.MARK_PRIMARY_KEY);
410		}
411		}
412	0	for (ForeignKey fk : snapshot.getForeignKeys()) {
413	0	if (index.getTable().getName().equalsIgnoreCase(fk.getForeignKeyTable().getName())
414		&& index.getColumnNames().equals(fk.getForeignKeyColumns())) {
415	0	index.addAssociatedWith(Index.MARK_FOREIGN_KEY);
416		}
417		}
418	0	for (UniqueConstraint uc : snapshot.getUniqueConstraints()) {
419	0	if (index.getTable().getName().equalsIgnoreCase(uc.getTable().getName())
420		&& index.getColumnNames().equals(uc.getColumnNames())) {
421	0	index.addAssociatedWith(Index.MARK_UNIQUE_CONSTRAINT);
422		}
423		}
424		}
425	0	}
426
427		@Override
428		protected void readPrimaryKeys(DatabaseSnapshot snapshot, String schema, DatabaseMetaData databaseMetaData)
429		throws DatabaseException, SQLException {
430	0	Database database = snapshot.getDatabase();
431	0	updateListeners("Reading primary keys for " + database.toString() + " ...");
432
433		// we can't add directly to the this.primaryKeys hashSet because adding columns to an exising PK changes the
434		// hashCode and .contains() fails
435	0	List<PrimaryKey> foundPKs = new ArrayList<PrimaryKey>();
436		// Setting default schema name. Needed for correct statement generation
437	0	if (schema == null)
438	0	schema = database.convertRequestedSchemaToSchema(schema);
439
440	0	String query = "select uc.table_name TABLE_NAME,ucc.column_name COLUMN_NAME,ucc.position KEY_SEQ,uc.constraint_name PK_NAME,ui.tablespace_name TABLESPACE from all_constraints uc,all_indexes ui,all_cons_columns ucc where uc.constraint_type = 'P' and uc.index_name = ui.index_name and uc.constraint_name = ucc.constraint_name and uc.owner = '"
441		+ schema + "' and ui.table_owner = '" + schema + "' and ucc.owner = '" + schema + "'";
442	0	Statement statement = null;
443	0	ResultSet rs = null;
444		try {
445	0	statement = ((JdbcConnection) database.getConnection()).getUnderlyingConnection().createStatement();
446	0	rs = statement.executeQuery(query);
447
448	0	while (rs.next()) {
449	0	String tableName = convertFromDatabaseName(rs.getString("TABLE_NAME"));
450	0	String tablespace = convertFromDatabaseName(rs.getString("TABLESPACE"));
451	0	String columnName = convertFromDatabaseName(rs.getString("COLUMN_NAME"));
452	0	short position = rs.getShort("KEY_SEQ");
453
454	0	boolean foundExistingPK = false;
455	0	for (PrimaryKey pk : foundPKs) {
456	0	if (pk.getTable().getName().equals(tableName)) {
457	0	pk.addColumnName(position - 1, columnName);
458
459	0	foundExistingPK = true;
460		}
461		}
462
463	0	if (!foundExistingPK && !database.isLiquibaseTable(tableName)) {
464	0	PrimaryKey primaryKey = new PrimaryKey();
465	0	primaryKey.setTablespace(tablespace);
466	0	Table table = snapshot.getTable(tableName);
467	0	if (table == null) {
468	0	continue; // probably a different schema
469		}
470	0	primaryKey.setTable(table);
471	0	primaryKey.addColumnName(position - 1, columnName);
472	0	primaryKey.setName(convertPrimaryKeyName(rs.getString("PK_NAME")));
473
474	0	foundPKs.add(primaryKey);
475		}
476	0	}
477		} finally {
478	0	JdbcUtils.closeResultSet(rs);
479	0	JdbcUtils.closeStatement(statement);
480	0	}
481
482	0	snapshot.getPrimaryKeys().addAll(foundPKs);
483	0	}
484		}