private void createIndexDDL(Index index) throws IOException { String ddl = null; if (index.getType().startsWith("DOMAIN")) { ddl = Templates.CREATE_INDEX_DOMAIN_TEMPLATE.replace(Templates.INDEX_NAME, index.getName()); ddl = ddl.replace(Templates.INDEX_TYPE, index.getSpecialType()); ddl = ddl.replace(Templates.PARAMETERS, index.getParameters()); } else { ddl = Templates.CREATE_INDEX_TEMPLATE.replace(Templates.INDEX_NAME, index.getName()); if (null != index.getTableSpace()) ddl = ddl.replace( Templates.TABLE_SPACE, Templates.CREATE_INDEX_TABLE_SPACE_TEMPLATE.replace( Templates.TABLE_SPACE, index.getTableSpace())); else ddl = ddl.replace(Templates.TABLE_SPACE, ""); ddl = ddl.replace(Templates.NEXT_EXTENT, Utils.getAsString(index.getNextExtend())); } ddl = ddl.replace(Templates.TABLE_NAME, table); List<String> columns = index.getColumns(); StringBuilder builder = new StringBuilder(); int size = columns.size(); for (int i = 0; i < size; i++) { builder.append(columns.get(i)); if (i != size - 1) builder.append(','); builder.append("\r\n"); } ddl = ddl.replace(Templates.COLUMNS, builder.toString()); write(ddl); }
public void createDDL() throws SQLException, IOException { TableReader sourceReader = new TableReader(sourceConnection); TableReader destionationReader = new TableReader(destinationConnection); List<Index> sourceIndexes = sourceReader.getIndexes(schema, table); List<Index> destinationIndexes = destionationReader.getIndexes(schema, table); for (Index destIndex : destinationIndexes) { boolean found = false; boolean drop = false; if (destIndex.getName().startsWith("SYS")) { continue; } for (Index sourceIndex : sourceIndexes) { if (destIndex.getName().equalsIgnoreCase(sourceIndex.getName())) { if (!destIndex.getColumns().containsAll(sourceIndex.getColumns())) { drop = true; } found = true; break; } else if (destIndex.getColumns().size() == sourceIndex.getColumns().size() && destIndex.getColumns().containsAll(sourceIndex.getColumns())) { found = true; break; } } if (drop) { dropDDL(destIndex.getName()); createIndexDDL(destIndex); } else if (!found) { createIndexDDL(destIndex); } } }
@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); } } } }