Chapter 8. Database Testing

The most common dataset is called Flat XML. It is a very simple xml format where a tag inside the root node <dataset> represents exactly one row in the database. The tags name equals the table to insert the row into and an attribute represents the column. An example for a simple guestbook application could look like this:

<?xml version="1.0" ?>
<dataset>
    <guestbook id="1" content="Hello buddy!" user="joe" created="2010-04-24 17:15:23" />
    <guestbook id="2" content="I like it!" user="nancy" created="2010-04-26 12:14:20" />
</dataset>

This is obviously easy to write. Here <guestbook> is the table name where two rows are inserted into each with four columns “id”, “content”, “user” and “created” with their respective values.

However this simplicity comes at a cost.

From the previous example it isn't obvious how you would specify an empty table. You can insert a tag with no attributes with the name of the empty table. A flat xml file for an empty guestbook table would then look like:

<?xml version="1.0" ?>
<dataset>
    <guestbook />
</dataset>

The handling of NULL values with the flat xml dataset is tedious. A NULL value is different than an empty string value in almost any database (Oracle being an exception), something that is difficult to describe in the flat xml format. You can represent a NULL's value by omitting the attribute from the row specification. If our guestbook would allow anonymous entries represented by a NULL value in the user column, a hypothetical state of the guestbook table could look like:

<?xml version="1.0" ?>
<dataset>
    <guestbook id="1" content="Hello buddy!" user="joe" created="2010-04-24 17:15:23" />
    <guestbook id="2" content="I like it!" created="2010-04-26 12:14:20" />
</dataset>

In this case the second entry is posted anonymously. However this leads to a serious problem with column recognition. During dataset equality assertions each dataset has to specify what columns a table holds. If an attribute is NULL for all the rows of a data-table, how would the Database Extension know that the column should be part of the table?

The flat xml dataset makes a crucial assumption now, defining that the attributes on the first defined row of a table define the columns of this table. In the previous example this would mean “id”, “content”, “user” and “created” are columns of the guestbook table. For the second row where “user” is not defined a NULL would be inserted into the database.

When the first guestbook entry is deleted from the dataset only “id”, “content” and “created” would be columns of the guestbook table, since “user” is not specified.

To use the Flat XML dataset effectively when NULL values are relevant the first row of each table must not contain any NULL value and only successive rows are allowed to omit attributes. This can be awkward, since the order of the rows is a relevant factor for database assertions.

In turn, if you specify only a subset of the table columns in the Flat XML dataset all the omitted values are set to their default values. This will lead to errors if one of the omitted columns is defined as “NOT NULL DEFAULT NULL”.

In conclusion I can only advise using the Flat XML datasets if you do not need NULL values.

You can create a flat xml dataset instance from within your Database TestCase by calling the createFlatXmlDataSet($filename) method:

<?php
class MyTestCase extends PHPUnit_Extensions_Database_TestCase
{
    public function getDataSet()
    {
        return $this->createFlatXmlDataSet('myFlatXmlFixture.xml');
    }
}
?>

There is another more structured XML dataset, which is a bit more verbose to write but avoids the NULL problems of the Flat XML dataset. Inside the root node <dataset> you can specify <table>, <column>, <row>, <value> and <null /> tags. An equivalent dataset to the previously defined Guestbook Flat XML looks like:

<?xml version="1.0" ?>
<dataset>
    <table name="guestbook">
        <column>id</column>
        <column>content</column>
        <column>user</column>
        <column>created</column>
        <row>
            <value>1</value>
            <value>Hello buddy!</value>
            <value>joe</value>
            <value>2010-04-24 17:15:23</value>
        </row>
        <row>
            <value>2</value>
            <value>I like it!</value>
            <null />
            <value>2010-04-26 12:14:20</value>
        </row>
    </table>
</dataset>

Any defined <table> has a name and requires a definition of all the columns with their names. It can contain zero or any positive number of nested <row> elements. Defining no <row> element means the table is empty. The <value> and <null /> tags have to be specified in the order of the previously given <column> elements. The <null /> tag obviously means that the value is NULL.

You can create a xml dataset instance from within your Database TestCase by calling the createXmlDataSet($filename) method:

<?php
class MyTestCase extends PHPUnit_Extensions_Database_TestCase
{
    public function getDataSet()
    {
        return $this->createXMLDataSet('myXmlFixture.xml');
    }
}
?>

This new XML format is specific to the MySQL database server. Support for it was added in PHPUnit 3.5. Files in this format can be generated using the mysqldump utility. Unlike CSV datasets, which mysqldump also supports, a single file in this XML format can contain data for multiple tables. You can create a file in this format by invoking mysqldump like so:

mysqldump --xml -t -u [username] --password=[password] [database] > /path/to/file.xml

This file can be used in your Database TestCase by calling the createMySQLXMLDataSet($filename) method:

<?php
class MyTestCase extends PHPUnit_Extensions_Database_TestCase
{
    public function getDataSet()
    {
        return $this->createMySQLXMLDataSet('/path/to/file.xml');
    }
}
?>

Alternatively, you can use YAML dataset for the guestbook example:

guestbook:
  -
    id: 1
    content: "Hello buddy!"
    user: "joe"
    created: 2010-04-24 17:15:23
  -
    id: 2
    content: "I like it!"
    user:
    created: 2010-04-26 12:14:20

This is simple, convient AND it solves the NULL issue that the similar Flat XML dataset has. A NULL in YAML is just the column name without no value specified. An empty string is specified as column1: "".

The YAML Dataset has no factory method on the Database TestCase currently, so you have to instantiate it manually:

<?php
class YamlGuestbookTest extends PHPUnit_Extensions_Database_TestCase
{
    protected function getDataSet()
    {
        return new PHPUnit_Extensions_Database_DataSet_YamlDataSet(
            dirname(__FILE__)."/_files/guestbook.yml"
        );
    }
}
?>

Another file-based dataset is based on CSV files. Each table of the dataset is represented as a single CSV file. For our guestbook example we would define a guestbook-table.csv file:

id,content,user,created
1,"Hello buddy!","joe","2010-04-24 17:15:23"
2,"I like it!""nancy","2010-04-26 12:14:20"

While this is very convenient for editing with Excel or OpenOffice, you cannot specify NULL values with the CSV dataset. An empty column will lead to the database default empty value being inserted into the column.

You can create a CSV DataSet by calling:

<?php
class CsvGuestbookTest extends PHPUnit_Extensions_Database_TestCase
{
    protected function getDataSet()
    {
        $dataSet = new PHPUnit_Extensions_Database_DataSet_CsvDataSet();
        $dataSet->addTable('guestbook', dirname(__FILE__)."/_files/guestbook.csv");
        return $dataSet;
    }
}
?>

There is no Array based DataSet in PHPUnit's Database Extension (yet), but we can implement our own easily. Our guestbook example should look like:

<?php
class ArrayGuestbookTest extends PHPUnit_Extensions_Database_TestCase
{
    protected function getDataSet()
    {
        return new MyApp_DbUnit_ArrayDataSet(array(
            'guestbook' => array(
                array('id' => 1, 'content' => 'Hello buddy!', 'user' => 'joe', 'created' => '2010-04-24 17:15:23'),
                array('id' => 2, 'content' => 'I like it!',   'user' => null,  'created' => '2010-04-26 12:14:20'),
            ),
        ));
    }
}
?>

A PHP DataSet has obvious advantages over all the other file-based datasets:

For this dataset like the Flat XML, CSV and YAML DataSets the keys of the first specified row define the table's column names, in the previous case this would be “id”, “content”, “user” and “created”.

The implementation for this Array DataSet is simple and straightforward:

<?php
class MyApp_DbUnit_ArrayDataSet extends PHPUnit_Extensions_Database_DataSet_AbstractDataSet
{
    /**
     * @var array
     */
    protected $tables = array();

    /**
     * @param array $data
     */
    public function __construct(array $data)
    {
        foreach ($data AS $tableName => $rows) {
            $columns = array();
            if (isset($rows[0])) {
                $columns = array_keys($rows[0]);
            }

            $metaData = new PHPUnit_Extensions_Database_DataSet_DefaultTableMetaData($tableName, $columns);
            $table = new PHPUnit_Extensions_Database_DataSet_DefaultTable($metaData);

            foreach ($rows AS $row) {
                $table->addRow($row);
            }
            $this->tables[$tableName] = $table;
        }
    }

    protected function createIterator($reverse = FALSE)
    {
        return new PHPUnit_Extensions_Database_DataSet_DefaultTableIterator($this->tables, $reverse);
    }

    public function getTable($tableName)
    {
        if (!isset($this->tables[$tableName])) {
            throw new InvalidArgumentException("$tableName is not a table in the current database.");
        }

        return $this->tables[$tableName];
    }
}
?>

For database assertions you do not only need the file-based datasets but also a Query/SQL based Dataset that contains the actual contents of the database. This is where the Query DataSet shines:

<?php
$ds = new PHPUnit_Extensions_Database_DataSet_QueryDataSet($this->getConnection());
$ds->addTable('guestbook');
?>

Adding a table just by name is an implicit way to define the data-table with the following query:

<?php
$ds = new PHPUnit_Extensions_Database_DataSet_QueryDataSet($this->getConnection());
$ds->addTable('guestbook', 'SELECT * FROM guestbook');
?>

You can make use of this by specifying arbitrary queries for your tables, for example restricting rows, column or adding ORDER BY clauses:

<?php
$ds = new PHPUnit_Extensions_Database_DataSet_QueryDataSet($this->getConnection());
$ds->addTable('guestbook', 'SELECT id, content FROM guestbook ORDER BY created DESC');
?>

The section on Database Assertions will show some more details on how to make use of the Query DataSet.

Accessing the Test Connection you can automatically create a DataSet that consists of all the tables with their content in the database specified as second parameter to the Connections Factory method.

You can either create a dataset for the complete database as shown in testGuestbook(), or restrict it to a set of specified table names with a whitelist as shown in testFilteredGuestbook() method.

<?php
class MySqlGuestbookTest extends PHPUnit_Extensions_Database_TestCase
{
    /**
     * @return PHPUnit_Extensions_Database_DB_IDatabaseConnection
     */
    public function getConnection()
    {
        $database = 'my_database';
        $pdo = new PDO('mysql:...', $user, $password);
        return $this->createDefaultDBConnection($pdo, $database);
    }

    public function testGuestbook()
    {
        $dataSet = $this->getConnection()->createDataSet();
        // ...
    }

    public function testFilteredGuestbook()
    {
        $tableNames = array('guestbook');
        $dataSet = $this->getConnection()->createDataSet($tableNames);
        // ...
    }
}
?>

I have been talking about NULL problems with the Flat XML and CSV DataSet, but there is a slightly complicated workaround to get both types of datasets working with NULLs.

The Replacement DataSet is a decorator for an existing dataset and allows you to replace values in any column of the dataset by another replacement value. To get our guestbook example working with NULL values we specify the file like:

<?xml version="1.0" ?>
<dataset>
    <guestbook id="1" content="Hello buddy!" user="joe" created="2010-04-24 17:15:23" />
    <guestbook id="2" content="I like it!" user="##NULL##" created="2010-04-26 12:14:20" />
</dataset>

We then wrap the Flat XML DataSet into a Replacement DataSet:

<?php
class ReplacementTest extends PHPUnit_Extensions_Database_TestCase
{
    public function getDataSet()
    {
        $ds = $this->createFlatXmlDataSet('myFlatXmlFixture.xml');
        $rds = new PHPUnit_Extensions_Database_DataSet_ReplacementDataSet($ds);
        $rds->addFullReplacement('##NULL##', null);
        return $rds;
    }
}
?>

If you have a large fixture file you can use the DataSet Filter for white- and blacklisting of tables and columns that should be contained in a sub-dataset. This is especially handy in combination with the DB DataSet to filter the columns of the datasets.

<?php
class DataSetFilterTest extends PHPUnit_Extensions_Database_TestCase
{
    public function testIncludeFilteredGuestbook()
    {
        $tableNames = array('guestbook');
        $dataSet = $this->getConnection()->createDataSet();

        $filterDataSet = new PHPUnit_Extensions_Database_DataSet_DataSetFilter($dataSet);
        $filterDataSet->addIncludeTables(array('guestbook'));
        $filterDataSet->setIncludeColumnsForTable('guestbook', array('id', 'content'));
        // ..
    }

    public function testExcludeFilteredGuestbook()
    {
        $tableNames = array('guestbook');
        $dataSet = $this->getConnection()->createDataSet();

        $filterDataSet = new PHPUnit_Extensions_Database_DataSet_DataSetFilter($dataSet);
        $filterDataSet->addExcludeTables(array('foo', 'bar', 'baz')); // only keep the guestbook table!
        $filterDataSet->setExcludeColumnsForTable('guestbook', array('user', 'created'));
        // ..
    }
}
?>

The composite DataSet is very useful for aggregating several already existing datasets into a single dataset. When several datasets contain the same table the rows are appended in the specified order. For example if we have two datasets fixture1.xml:

<?xml version="1.0" ?>
<dataset>
    <guestbook id="1" content="Hello buddy!" user="joe" created="2010-04-24 17:15:23" />
</dataset>

and fixture2.xml:

<?xml version="1.0" ?>
<dataset>
    <guestbook id="2" content="I like it!" user="##NULL##" created="2010-04-26 12:14:20" />
</dataset>

Using the Composite DataSet we can aggregate both fixture files:

<?php
class CompositeTest extends PHPUnit_Extensions_Database_TestCase
{
    public function getDataSet()
    {
        $ds1 = $this->createFlatXmlDataSet('fixture1.xml');
        $ds2 = $this->createFlatXmlDataSet('fixture2.xml');

        $compositeDs = new PHPUnit_Extensions_Database_DataSet_CompositeDataSet();
        $compositeDs->addDataSet($ds1);
        $compositeDs->addDataSet($ds2);

        return $compositeDs;
    }
}
?>