Support table truncation teardown in the TestContext framework

[spring-projects-issues]

Adib Saikali opened SPR-9916 and commented

Overview

Currently the Spring TestContext Framework implements the Transaction rollback tear down for integration tests that touch the database. Another standard pattern for database tear down is the Table truncation tear down, discussed on page 661 in the xUnit Test Patterns Book.

There are many situations where the test needs to span multiple @Transactional service methods with many different propagation settings. Therefore running the whole integration test within a single @Transactional test method with transaction rollback is not enough. Right now this means doing some sort of cleanup of the work that the transactions did. The transaction cleanup code can be problematic to write and keep in sync with how the tests modify the database making test code brittle. Dropping and recreating the schema can be overkill because you have to rebuild some tables which are essentially immutable for the duration of the test, for example we might have tables with application usernames, passwords, preferences ... etc, that are essentially immutable for a vast majority of integration tests.

Proposal

The @Truncate annotation in the example below should instruct the TestContext framework to truncate all tables in the test schema other than test.users and test.roles.

Spring can scan the schema using JDBC metadata to find out all the table names and then can use the configuration information in the annotation to figure what tables to truncate or remove.

A class level @Truncate should truncate the tables after every test method, but placing @Truncate on a method can give fine-grained control over which tables to truncate after every test method.

One can play around with whether a single regex should be used to identify tables and schema or whether those should be separate settings.

@SpringJUnitConfig
@Truncate(schema = "test", tables = "*", keep = {"users", "roles"})
public class Test {
    // ...
}

An alternative to @Truncate could be a truncate() method in the abstract base transactional test classes for JUnit 4 and TestNG. The method could have a signature along the lines of the following.

public void truncate(String schema, String truncateTablesRegex, String keepTablesRegex)

[sbrannen]

Closing in favor of @Sql support in general as well as the recently added support for class-level execution phases for @Sql.

• Support declarative SQL script execution in the TestContext framework [SPR-7655] #12311
• Introduce class-level execution phases for @Sql #27285