Schema Translation For Quick InMemory Integration Tests

In a previous post, I highlighted the time wasted waiting for integration tests to run against a full database. I proposed the idea of running integration tests against an InMemory database locally and a full database in CI. These are the steps I did to achieve this goal.

• Creation of unitils.dbmaintainer.script.ScriptSource classes
• Transform schema files using regular expression
• Configured Unitils.properties
• Reconfigure the JpaVendorAdapter
• Use Number for queries that use count

ScriptSource Classes

Unitils' default ScriptSource implementation uses the file system to load schema files. I found this annoying because I would have to configure the location of the schema files with a full path. As this project is maven based and shared between a team, full paths are pointless unless you force the team to check out in exactly the same place. Therefore I created my own version of DefaultScriptSource, where the schema files are loaded using the Spring DefaultResourceLoaded. That way I can use the classpath prefix in the unitils.properties file. Another addition to the ScriptSource implementation was preprocess method to translate schema from one database type to another. This method is called in the createScript method, just before the Script class is created.

protected Script createScript(List parentIndexes, File scriptFile) {
...
String preprocessedScriptContent = preprocess(scriptContent);
return new Script(scriptFile.getName(), preprocessedScriptContent, version);
}

Next I extended my version of the DefaultScriptSource class, overriding the preprocessor method. I called this new class, MySqlToHyperSonicAdaptingClasspathScriptSource, for want of a better name! The preprocess method uses regular expressions to translate the schema from MySQL into HSQLDB statements.

Transform schema files

There are many differences between MySQL and HSQLDB statements and I didn't do a complete transformation. I only did the following.

• int sizes removed
• bit sizes removed
• unsigned removed
• long blob replaced with object
• modify to alter column
• engine statement removed
• foreign key checks removed
• reordering of default values
• removed quoted reserved words
• change column to rename column

Unitils.properties

This is the checked in unitils.properties file, the version the local integration tests use.

org.unitils.dbmaintainer.script.ScriptSource.implClassName=domfarr.unitils.script.MySqlToHyperSonicAdaptingClasspathScriptSource
dbMaintainer.script.locations=classpath:/database/schema
dbMaintainer.generateDataSetStructure.enabled=false
dbMaintainer.fromScratch.enabled=true
dbMaintainer.dbVersionSource.autoCreateVersionTable=true
dbMaintainer.autoCreateExecutedScriptsTable=true
DatabaseModule.Transactional.value.default=commit
updateDataBaseSchema.enabled=true
jpa.persistenceProvider=hibernate

database.dialect=hsqldb
database.driverClassName=org.hsqldb.jdbcDriver
database.url=jdbc:hsqldb:mem:aname
database.userName=sa
database.password=
database.schemaNames=PUBLIC

# Add this file in your home directory for custom db settings
unitils.configuration.localFileName=unitils.properties

The last line is important. This line loads another unitils.properties file located in the users.home directory. Any values in this file override any previous values configured. Locally you do not have this file, it is not checked in, and is outside of the project. However, the CI machine does have this file and it overrides to configure a MySQL database. It also uses my own version of DefaultScriptSource which does not preprocess the schema files.

database.driverClassName=com.mysql.jdbc.Driver
database.dialect=mysql
database.url=jdbc:mysql://123.123.123.123/bamboo
database.userName=bamboo
database.password=bamboo
database.schemaNames=bamboo
org.unitils.dbmaintainer.script.ScriptSource.implClassName=domfarr.unitils.script.ClasspathScriptSourceocessedScriptContent, version);

JpaVendorAdapter

When configuring the JpaVendorAdapter, I use spring, it asks for the dialect. Remove this entry, this basically works without it and you don't have to worry about filtering this file using maven.

Queries that use count

If you have a createNativeQuery that uses count, for example:

select count(distinct id) from companies inner join companies_relations on companies.id = companies_relations.parent_id"

Don't return the database specific class. MySQL returns BigInteger, HSQLDB returns Integer, return Number, the super class.

  public int size() {

int count = (Integer) getJpaTemplate().execute( new JpaCallback() {
  public Object doInJpa(EntityManager em) throws PersistenceException {

    Number count = (Number) em.createNativeQuery(
        "select count(distinct id) from companies inner join companies_relations " +
        "on companies.id = companies_relations.parent_id").getSingleResult();

    return count.intValue();

  }
} );

return count;
}

Okay...did you make it this far? If you did, nice work. This post is way longer than I wanted, but I hope it works for you. If not let me know and I'll do my best to help out.