PHPUnit Database Extension (DBUnit Port)

In this example I am going show you how to create a new test case to test the database functionality of a class providing functionality to edit the details of a bank account.

To use the database extension I will create a new test case extending the class PHPUnit_Extensions_Database_TestCase.

There are two methods that you must have in any database test case. Those methods are getConnection() and getDataSet(). The first method getConnection() must return an object that implements the PHPUnit_Extensions_Database_DB_IDatabaseConnection interface. In most cases you will return an instance of the PHPUnit_Extensions_Database_DB_DefaultDatabaseConnection. The constructor for this class takes two parameters. The first is a PDO object that is connected to the database you plan on using to test your functionality. The second parameter is the name of the database schema you will be testing against. I realize that PHPUnit_Extensions_Database_DB_DefaultDatabaseConnection is a handful to type so there is a helper method in the TestCase class that will instantiates a database connection with the same arguments: createDefaultDBConnection(PDO $connection, string $schema).

You can choose a variety of methods to get the PDO object to pass to this method. For this example I will create the connection in the test case constructor and add it as a protected class property. For large test suites you may want to use a static singleton method of a test utility class to help get rid of some of the overhead of opening and closing connections. Here is our test case with the getConnection() method implemented:

For my test I have simply chosen to use the memory database in sqlite. I have chosen this simply because it is faster than an sqlite database in the filesystem and this is just a tutorial so persistence of the file is simply not needed. I have called the BankAccount::createTable() method to create the schema that the BankAccount object uses. The downside to using the memory database is you have to create your schema every time. Normally you would have a persistant database for testing that would always have the necessary schema to test your code. Note that when using sqlite what you pass as the $schema parameter does not really matter since sqlite does not really have the concept of schemas.

This brings us to the next required method. The getDataSet() method is used to return the seed data that is used in setUp() and tearDown() operations for each test. There are currently two different ways to easily load datasets: the Flat XML DataSet and the XML DataSet. The flat XML data set is an extremely simple yet somewhat limited data set where the XML data set is slightly more complicated but much more robust when dealing with null values. For our example we will use the flat xml data set.

In the flat xml data set you create a root element named dataset and then create xml element for every row in every table of your database. The element name is equal to the table name the row resides in. The element will have one attribute for each column in the table. If there is an attribute missing for one element that exists in another element then that colum for the row with the missing attribute will be set to NULL. Please note that colname="" will not set that column to null. It will set it to an empty string (or whatever that translates to for that articular column's data type.

The flat xml data set we will be using is as follows:

The classname of the flat xml data set is PHPUnit_Extensions_Database_DataSet_FlatXMLDataSet and it's only argument is the path to xml file that you are loading. Once again, this is an astronomically long class name so there is a helper method to instantiate this class: createFlatXMLDataSet().

Here is our test case with the getDataSet() method implemented:

Now in a default implementation the database extension will perform a 'clean insert' operation on your database using the the dataset returned by getDataSet. The clean insert operation will truncate any tables in the data set (not all tabes in the database) and then insert the rows in the dataset. The default implementation will do nothing on tear down. There are several other operations that can be used by adding the getSetUpOperation() and getTearDownOperation() methods. These methods should return a PHPUnit_Extensions_Database_Operation_* class. These operations can be returned using the test case's getOperations() method. The default set up implementation is the equivelant of creating the following getSetUpOperation().

There is one more thing to take note of before we dive into our first test. The Database_Testcase is heavily dependant on it's implementation of setUp() and tearDown() for it's functionality to work. If you need to perform additional setup or teardown actions make sure you call the parent versions of these functions as well.

The functionality I would like to test in this example is testing new account creation. In the bank account class, whenever a new BankAccount class is instantiated it is supposed to create a new bank account with a zero balance in the database. To test this we will need to create another dataset representing our seed data with the expected changes.

Using this data set we can create a new test that instantiates a bank account object and then we will assert that the contents of the database are equal to the dataset we just created. In order to compare the database to your dataset you need to create a dataset containing the contents of your database. This is done by calling the createDataSet() method of the database connection object you created in the getConnection() method. You can then pass it to the assertDataSetsEqual() function. Also notice that you can just reuse the createFlatXMLDataSet() method to load the expected dataset.

If you have a large database data set you can also compare at the table level using the test case method assertTablesEqual(). You can pass it individual tables by calling the getTable($tableName) method. This method exists for any of the data set classes. You can also limit tables at the database level by passing an array of table names to the createDataSet() method. So if your database had multiple tables the above function could have been rewritten as below to only compare at the table level.

Or if you didn't even want to bother loading tables you won't be testing against:

Hopefully that is enough to pique your interest. As always, if you have any questions feel free to ask me.