Integration Testing with a Database

Owned by Oliver Jones
Last updated: Apr 08, 2018 by Ivo UlrichVersion comment
5 min read

Introduction

Many OpenMRS unit tests need to load their test data into the database as they initialize.  OpenMRS's unit tests use the DbUnit extension to JUnit to do this test-data loading. The kind of unit test that makes use of DBMS data really needs a way to load predefined data; if a developer creates a unit test that depends on test data in her own MySQL instance, the test will probably fail on the continuous integration server or in another developer's environment. Of course, that make the test a whole lot less useful.

The 2009 Implementers' Group Meeting worked out some best practices for unit testing. This page should serve to help apply those best practices.

The OpenMRS unit testing framework uses the H2 in-memory database management system.  This is helpful: it means you can run your unit tests without worrying about trashing your favorite mySQL instance.

The unit testing framework automatically loads standard datasets, including the ones at /openmrs/api/src/test/resources/org/openmrs/include.  The ones you load for a particular test augment the standard ones.

Loading a test dataset in your unit test.

The OpenMRS base class org.openmrs.test.BaseContextSensitiveTest offers the method executeDataSet. It takes as an argument the name of an XML file with a test dataset in it. You can call this method from your test's setUp method or the individual test method. It immediately loads your test data into the database, using DbUnit's REFRESH operation. Derive your unit test class from BaseContextSensitiveTest and this method is available to you.

Typical JUnit code for loading a test dataset might look like this (this example was originally created by bwolfe and jrwarren):

...
import org.junit.Before;
import org.junit.Test;
import org.openmrs.test.Verifies;
import org.openmrs.web.test.BaseWebContextSensitiveTest;
...
public class MyUnitTest extends BaseWebContextSensitiveTest {
...
protected static final String PROGRAM_TEST_OUTCOMES =
      "org/openmrs/web/dwr/include/DWRProgramWorkflowServiceTest-outcomes.xml";


  @Test
  @Verifies(
    value = "should get possible outcomes for a program",
    method = "getPossibleOutcomes()")
  public void getPossibleOutcomes_shouldReturnOutcomeConceptsFromProgram()
                                     throws Exception {
    ...
    executeDataSet(PROGRAM_TEST_OUTCOMES );
    ...
    /* run tests ... */
  }

This example XML file can be found at this path, in the openmrs-web project, in the OpenMRS source code:

openmrs/web/src/test/resources/org/openmrs/web/dwr/include  

Preparing your test dataset

DbUnit offers a simple and easy-to-use XML format for holding test datasets. It uses a single XML element per row.  The names of the elements are the table names, and the names of the attributes are the column names.  Here's a simplified example that creates a Concept and some associated dbms rows.

<?xml version='1.0' encoding='UTF-8'?>
<dataset>
  <!-- Marital outcome -->
  <concept concept_id="99" 
           retired="false" datatype_id="4" class_id="10" is_set="false" 
           creator="1" date_created="2008-08-18 12:38:58.0" version="" 
           uuid="cf25712e-f193-48d5-a85a-5fd8d0244c71"/>
  <concept_name concept_id="99" name="MARITAL OUTCOME" 
           locale="en" 
           creator="1" date_created="2008-08-18 12:38:58.0" 
           concept_name_id="5151" concept_name_type="FULLY_SPECIFIED" 
           locale_preferred="1" voided="false" 
           uuid="d1093cd6-3c89-45f0-8731-13c551c14b4f"/>
  <concept_description concept_description_id="151" concept_id="99" 
           description="The outcome of the program" 
           locale="en" creator="1" date_created="2008-08-18 12:38:58.0" 
           uuid="b2be1127-4ee8-4110-a3ed-9450ad097734"/>
</dataset>

Open up an xml file.  Create a <dataset>  </dataset> element, and then insert your test elements into it.  Notice the first element, the <concept/> element, in the example above. It has an ID number, a creator, a date_created, and UUID columns.  Almost all rows of all OpenMRS tables have those columns, so your test data needs to assign them.  It also has some table-specific items, like retired, datatype_id, class_id, and so on. This will depend on the columns of your table.

A good way to create test datasets is to follow these steps:

  • use the OpenMRS administrative interface to create the appropriate data in a local mySQL instance
  • use a mySQL client to look at the appropriate rows of the appropriate tables
  • create the xml, either manually or programatically (See here and here for programmatic approaches.)

It's not hard to keep trying until you get your test dataset right. Simply edit your XML file and re-run your test. If there's something wrong with your data, executeDataSet will throw descriptive exceptions, and JUnit will display them in the Failure Trace panel of your IDE when you run the test. For example, if two rows of your dataset in your xml file have the same UUID, you'll get a message like this.

org.h2.jdbc.JdbcSQLException: Unique index or primary key violation: "CONSTRAINT_INDEX_6 ON PUBLIC.CONCEPT(UUID)"; SQL statement:
insert into CONCEPT (CONCEPT_ID, RETIRED, DATATYPE_ID, CLASS_ID, IS_SET, CREATOR, DATE_CREATED, VERSION, UUID) values (?, ?, ?, ?, ?, ?, ?, ?, ?) [23001-135]

Usually, it's easy to figure out what went wrong from the SQLException. In this case the column CONCEPT.UUID's value caused a constraint violation.

When you're entering multiple rows for a particular table in your test dataset, take care that each of those rows has a distinct primary key (for example, in the concept table, concept_id is the primary key). Because the data is loaded using the REFRESH operation, DbUnit won't warn you if you have several rows of data with the same primary key. It will just overwrite the row.

Since OpenMRS 1.10 it is also required for rows to be entered in the correct order i.e. you are not allowed to insert a concept which refers to a datatype which does not exist yet.

Using Module-Defined Tables with dbUnit

Make sure the POMs in your project have been modified per the directions here.

When testing with tables defined by your module, you must have test-hibernate.cfg.xml in your [api/omod]:src/test/resources folder and it must specify all the tables your module defines or uses that are not core openmrs tables. (The mapping tables included in your module should also be specified in omod:src/main/resources:config.xml.) If you do not do this, you will get "table does not exist" errors for your module tables. Here is an example of the contents of the file:

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
	"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
	"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">

<hibernate-configuration>
	<session-factory>
		<mapping resource="LabInstrument.hbm.xml" />
		<mapping resource="LabSupplyItem.hbm.xml" />
		<mapping resource="RuleDefinition.hbm.xml" />
	</session-factory>
</hibernate-configuration>

In this case, the first two mapping files being referenced belong to the module being set up for unit testing. If you have followed the Maven archetype, they should already be in [api/omod]:src/main/resources defining the corresponding tables. The third mapping file belongs to a module on which the module being tested depends. Such files must be copied from the projects where they are defined to the [api/omod]:src/test/resources folder.