Tuesday, November 25, 2008

dbUnit and HSQLDB Databases with Schema Names

There are several ways to manage unit tests that involve a data layer. Someday I'll enumerate them in another post. But when I'm doing Java, one of my favorites is using HSQLDB as a fast, in-memory stand-in for a more heavyweight database. I also like dbUnit to manage setting and resetting the data in the in-memory database. I ran into an interesting problem when trying to use dbUnit with an HSQLDB database that employed named schemas (like, say, Oracle). In case you've never had to create your own schemas in DDL before, you can create the schema "SCOTTSCHEMA" like this:
Statement ddl = conn.createStatement(); ddl.executeUpdate("CREATE SCHEMA SCOTTSCHEMA AUTHORIZATION DBA");

Now, when you get ready to use dbUnit and create your IDatabaseConnection, you need to pass the schema name:

IDatabaseConnection dbUnitConn = new DatabaseConnection(conn, "SCOTTSCHEMA");

Now I should be able to load up XML files using dbUnit that look like this for a hypothetical "CONTACTS" table:

<?xml version="1.0" encoding="UTF-8"?> <dataset> <SCOTTSCHEMA.CONTACTS FIRST_NAME="Scott" LAST_NAME="McMaster"/> <SCOTTSCHEMA.CONTACTS FIRST_NAME="Tracy" LAST_NAME="McMaster"/> </dataset>

It turns out that passing the schema name in the second parameter is necessary, but not sufficient: If you try to run a database operation against dbUnitConn at this point, you'll get the following exception:

org.dbunit.dataset.NoSuchTableException: SCOTTSCHEMA.CONTACTS at org.dbunit.database.DatabaseDataSet.getTableMetaData(DatabaseDataSet.java:222) at org.dbunit.operation.DeleteAllOperation.execute(DeleteAllOperation.java:109) at org.dbunit.operation.CompositeOperation.execute(CompositeOperation.java:79) at ...

When I first hit this, it was easier to figure out what was going on from the dbUnit code rather than the documentation. I discovered rather quickly that you also need to set FEATURE_QUALIFIED_TABLE_NAMES in the IDatabaseConnection's config:

dbUnitConn.getConfig().setFeature(DatabaseConfig.FEATURE_QUALIFIED_TABLE_NAMES, true);

Now dbUnit will build its internal map of database tables prefixed with the schema name and will be able to find the table metadata when you execute an operation. It seems like it would make sense to default this feature to "true" when you create a database connection with a schema name...

3 comments:

Mats said...

Hi Scott!
Would be nice to know if you've come across any tool that could do the DDL part for several databases. We have the following problem: We would like to use DbUnit to run unit tests. The production database is MySQL. However we would like to be able to run these unit tests on a build and test server. We would also like the unit test to be totally "self contained" in the sense that it does all its setup, without requiring an manual pre-test DB setup. Currently we are running our DB unit tests on MySQL, HSQLDB and H2. We run HSQLDB and H2 in-process and in-memory. We run HSQLDB and H2 on the build server. The developer also has the option to run the tests using a local MySQL server. The prerequisite for this is having creating the databases used by the tests. Before each test is run, the tables involved in the test are dropped. Here we have been forced to write some own code which handles the small SQL DDL differences between the databases - basically the handling of auto_increment.
Anyway, we're now adding some requirements and maintaining the DDL to DbUnit glue isn't really our thing. So - do you know of any such open source glue?

Scott McMaster said...

Hi Mats. I lean toward using ORM -- preferably Hibernate -- in the data access layer. And in that case, it's very easy to generate the DDL for different database engines on the fly during unit testing by setting the hibernate.hbm2ddl.auto property to "create" or "create-drop" (depending on how often you want the database rebuilt while testing). More to your point, if you can get a satisfactory database schema driven completely off of Hibernate mappings, you can also use the hbm2ddl Ant task to generate scripts for whatever database(s) for which you have Hibernate session configurations. In my experience, that DDL will rarely pass muster with a DBA for production use, but depending on your exact situation, it might get you where you want to go.

Mahesh.G said...

I am using dbunit for our SQL Server database unit testing. As i am facing the following issue.

Dbunit is not considering the SchemaName.TableName, it is not able to identify the table.

I added the following code to my connection .

config.setProperty("http://www.dbunit.org/features/qualifiedTableNames", true );
config.setProperty("http://www.dbunit.org/properties/escapePattern", "\"?\"");

Still i am not able to get it.

Can you please look into it?