Issues with MicrosoftSqlDatabaseOperationLookup

[wabrit]

Hi - apologies if this is not the correct place to raise this.

I'm using 1.0.1 of spring-test-dbunit and trying to figure out how MicrosoftSqlDatabaseOperationLookup can help me bypass the normal restrictions on IDENTITY columns in MSSQL and allow me to supply PK values in INSERT operations.

Having switched to using the above class as my @DbUnitConfiguration(databaseOperationLookup) implementation I've hit a number of snags:

• I want one set of data and test classes to work across a mixture of database types (MSSQL, Postgres, Oracle), so hardcoding databaseOperationLookup to MicrosoftSqlDatabaseOperationLookup seems the wrong way to go. On the other hand I can't see a way for my own implementation of DatabaseOperationLookup to adjust behaviour depending on which target database is in use. Is this possible?
• When I start to use MicrosoftSqlDatabaseOperationLookup, my @DatabaseSetup annotations are causing failures due to Primary Key constraints being violated during INSERTS. Although I am using @DatabaseSetup(type=DatabaseOperation.CLEAN_INSERT), it appears that PK values from test data for different tests are colliding with each other. If I remove the use of MicrosoftSqlDatabaseOperationLookup, the problem goes away.

Any help/advice much appreciated - thanks.

[wabrit]

Some more information on this; I think there are two underlying problems.

The first is that the transaction doesn't seem to be getting rolled back even though defaultRollback=true is specified. This only happens when the MicrosoftSqlDatabaseOperationLookup is specified (if no lookup specified, the transaction is rolled back).

This wouldn't normally cause problems, because I have the following annotations on all my tests:

@DatabaseSetup(
        value={"/file1.setup.xml","/file2.setup.xml"},
        type=DatabaseOperation.CLEAN_INSERT
        )

So the CLEAN_INSERT should tidy things up for me. However this doesn't work because in the DbUnitRunner.getDbUnitDatabaseOperation() method there is the following logic:

if ((operation == DatabaseOperation.CLEAN_INSERT) && (lastOperation == DatabaseOperation.CLEAN_INSERT)) {
            operation = DatabaseOperation.INSERT;
        }

The DbUnitRunnerSetUpOrTearDown method has the following logic:

private void setupOrTeardown(DbUnitTestContext testContext, boolean isSetup,
            Collection<AnnotationAttributes> annotations) throws Exception {
        IDatabaseConnection connection = testContext.getConnection();
        DatabaseOperation lastOperation = null;
        for (AnnotationAttributes annotation : annotations) {
            for (String dataSetLocation : annotation.getValue()) {
                DatabaseOperation operation = annotation.getType();
                org.dbunit.operation.DatabaseOperation dbUnitDatabaseOperation = getDbUnitDatabaseOperation(
                        testContext, operation, lastOperation);
                IDataSet dataSet = loadDataset(testContext, dataSetLocation);
                if (dataSet != null) {
                    if (logger.isDebugEnabled()) {
                        logger.debug("Executing " + (isSetup ? "Setup" : "Teardown") + " of @DatabaseTest using "
                                + operation + " on " + dataSetLocation);
                    }
                    dbUnitDatabaseOperation.execute(connection, dataSet);
                    lastOperation = operation;
                }
            }
        }
    }

As you can see the lastOperation variable is remembered outside of the loop round the dataSetLocations, so the DELETE_ALL operation is only applied to the first file in the @DatabaseSetup(value={}) list. So if there are tables that are not in the first file but are in the second file, they will never get cleaned up.

I think the above logic needs to change so that if there is a DELETE_ALL then it needs to be processed against the tables found in all of the dataSetLocations first.

However, this wouldn't be such a problem if the transaction rollback were working with MicrosoftSqlDatabaseOperationLookup .