If my database already has a schema in it which has a table with an index on column A and also a unique constraint on column A; then I use Liquibase to diff it against an empty database, it will tell me to drop everything. The problem is, it tries to run: DROP INDEX SCHEMANAME.INDEXNAME; but DB2 gives me this error:
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0669N A system required index cannot be dropped explicitly.
SQLSTATE=42917
I need to drop the unique constraint before it will let me drop the index, but I don’t see any drop constraint statements in the SQL code that Liquibase produces.
I think the problem might be on this line in DiffResult.addUnexpectedUniqueConstraintChanges():
if (!getUnexpectedTables().contains(uc.getTable())) {
Is there a specific reason why unexpected Unique Constraints aren’t dropped if the table they belong to is also unexpected?
Would it break anything if that if condition was removed?
I think we can remove the if statement. I committed a change to trunk that removed it.
I think the idea was that if the table is not there, we shouldn’t have to worry about the unique constraint not being there either, but that is obviously not the case in all databases.
When I just commented out that if statement it broke some of my other tests. So in addition to removing the if statement, I also had to move this line:
addUnexpectedForeignKeyChanges(changeSets);
up above this line:
addMissingUniqueConstraintChanges(changeSets);
in DiffResult.printChangeLog(PrintStream out, Database targetDatabase, XmlWriter xmlWriter)
but make sure to leave this other line where it was, otherwise you'll break other things:
addMissingForeignKeyChanges(changeSets);
(I've been working on this for 3 days, so I can't really remember what changes caused what to break or why...)
I also had to add these new functions to the Diff class:
/**
* Removes duplicate Indexes from the DiffResult object.
*
* @param indexes [IN/OUT] - A set of Indexes to be updated.
*/
private void removeDuplicateIndexes( SortedSet indexes )
{
SortedSet combinedIndexes = new TreeSet();
SortedSet indexesToRemove = new TreeSet();
// Find Indexes with the same name, copy their columns into the first one,
// then remove the duplicate Indexes.
for ( Index idx1 : indexes )
{
if ( !combinedIndexes.contains( idx1 ) )
{
for ( Index idx2 : indexes.tailSet( idx1 ) )
{
if ( idx1 == idx2 ) {
continue;
}
if ( idx1.getName().equalsIgnoreCase( idx2.getName() )
&& idx1.getTable().getName().equalsIgnoreCase( idx2.getTable().getName() ) )
{
for ( String column : idx2.getColumns() )
{
if ( !idx1.getColumns().contains( column ) ) {
idx1.getColumns().add( column );
}
}
indexesToRemove.add( idx2 );
}
}
combinedIndexes.add( idx1 );
}
}
// Sort the columns.
Set<String> sortedColumns = new TreeSet<String>();
for ( Index idx : combinedIndexes )
{
List<String> columns = idx.getColumns();
sortedColumns.clear();
sortedColumns.addAll( columns );
columns.clear();
columns.addAll( sortedColumns );
}
indexes.removeAll( indexesToRemove );
}
/**
* Removes duplicate Unique Constraints from the DiffResult object.
*
* @param uniqueConstraints [IN/OUT] - A set of Unique Constraints to be updated.
*/
private void removeDuplicateUniqueConstraints( SortedSet<UniqueConstraint> uniqueConstraints )
{
SortedSet<UniqueConstraint> combinedConstraints = new TreeSet<UniqueConstraint>();
SortedSet<UniqueConstraint> constraintsToRemove = new TreeSet<UniqueConstraint>();
// Find UniqueConstraints with the same name, copy their columns into the first one,
// then remove the duplicate UniqueConstraints.
for ( UniqueConstraint uc1 : uniqueConstraints )
{
if ( !combinedConstraints.contains( uc1 ) )
{
for ( UniqueConstraint uc2 : uniqueConstraints.tailSet( uc1 ) )
{
if ( uc1 == uc2 ) {
continue;
}
if ( uc1.getName().equalsIgnoreCase( uc2.getName() )
&& uc1.getTable().getName().equalsIgnoreCase( uc2.getTable().getName() ) )
{
for ( String column : uc2.getColumns() )
{
if ( !uc1.getColumns().contains( column ) ) {
uc1.getColumns().add( column );
}
}
constraintsToRemove.add( uc2 );
}
}
combinedConstraints.add( uc1 );
}
}
// Sort the columns.
Set<String> sortedColumns = new TreeSet<String>();
for ( UniqueConstraint uc : combinedConstraints )
{
List<String> columns = uc.getColumns();
sortedColumns.clear();
sortedColumns.addAll( columns );
columns.clear();
columns.addAll( sortedColumns );
}
uniqueConstraints.removeAll( constraintsToRemove );
}</ol>
and then call them like this at the end of the Diff.compare() function:
// Hack: Sometimes Indexes or Unique Constraints with multiple columns get added twice (1 for each column),
// so we're combining them back to a single Index or Unique Constraint here.
removeDuplicateIndexes( diffResult.getMissingIndexes() );
removeDuplicateIndexes( diffResult.getUnexpectedIndexes() );
removeDuplicateUniqueConstraints( diffResult.getMissingUniqueConstraints() );
removeDuplicateUniqueConstraints( diffResult.getUnexpectedUniqueConstraints() );
Oh, I just noticed one more change that you need to make.
This line:
addMissingIndexChanges(changeSets);
needs to be moved up above this line:
addMissingUniqueConstraintChanges(changeSets);
Otherwise Unique Constraints will be created first and that would also create a system generated Index; so if you try to add your own Index afterwards it will complain because the system generated Index already exists for the same table...
