Zend_Test_PHPUnit_Db

Coupling of data-access and the domain model often requires the use of a database for testing purposes. But the database is persistent across different tests which leads to test results that can affect each other. Furthermore setting up the database to be able to run a test is quite some work. PHPUnit's Database extension simplifies testing with a database by offering a very simple mechanism to set up and teardown the database between different tests. This component extends the PHPUnit Database extension with Zend Framework specific code, such that writing database tests against a Zend Framework application is simplified.

Database Testing can be explained with two conceptual entities, DataSets and DataTables. Internally the PHPUnit Database extension can build up an object structure of a database, its tables and containing rows from configuration files or the real database content. This abstract object graph can then be compared using assertions. A common use-case in database testing is setting up some tables with seed data, then performing some operations, and finally asserting that the operated on database-state is equal to some predefined expected state. Zend_Test_PHPUnit_Db simplifies this task by allowing to generate DataSets and DataTables from existing Zend_Db_Table_Abstract or Zend_Db_Table_Rowset_Abstract instances.

Furthermore this component allows to integrate any Zend_Db_Adapter_Abstract for testing whereas the original extension only works with PDO. A Test Adapter implementation for Zend_Db_Adapter_Abstract is also included in this component. It allows to instantiate a Db Adapter that requires no database at all and acts as an SQL and result stack which is used by the API methods.

Quickstart

Setup a Database TestCase

We are now writting some database tests for the Bug Database example in the Zend_Db_Table documentation. First we begin to test that inserting a new bug is actually saved in the database correctly. First we have to setup a test-class that extends Zend_Test_PHPUnit_DatabaseTestCase. This class extends the PHPUnit Database Extension, which in turn extends the basic PHPUnit_Framework_TestCase. A database testcase contains two abstract methods that have to be implemented, one for the database connection and one for the initial dataset that should be used as seed or fixture.

Note: You should be familiar with the PHPUnit Database extension to follow this quickstart easily. Although all the concepts are explained in this documentation it may be helpful to read the PHPUnit documentation first.

class BugsTest extends Zend_Test_PHPUnit_DatabaseTestCase
{
    private $_connectionMock;
 
    /**
     * Returns the test database connection.
     *
     * @return PHPUnit_Extensions_Database_DB_IDatabaseConnection
     */
    protected function getConnection()
    {
        if($this->_connectionMock == null) {
            $connection = Zend_Db::factory(...);
            $this->_connectionMock = $this->createZendDbConnection(
                $connection, 'zfunittests'
            );
            Zend_Db_Table_Abstract::setDefaultAdapter($connection);
        }
        return $this->_connectionMock;
    }
 
    /**
     * @return PHPUnit_Extensions_Database_DataSet_IDataSet
     */
    protected function getDataSet()
    {
        return $this->createFlatXmlDataSet(
            dirname(__FILE__) . '/_files/bugsSeed.xml'
        );
    }
}

Here we create the database connection and seed some data into the database. Some important details should be noted on this code:

You cannot directly return a Zend_Db_Adapter_Abstract from the getConnection() method, but a PHPUnit specific wrapper which is generated with the createZendDbConnection() method.
The database schema (tables and database) is not re-created on every testrun. The database and tables have to be created manually before running the tests.
Database tests by default truncate the data during setUp() and then insert the seed data which is returned from the getDataSet() method.
DataSets have to implement the interface PHPUnit_Extensions_Database_DataSet_IDataSet. There is a wide range of XML and YAML configuration file types included in PHPUnit which allows to specifiy how the tables and datasets should look like and you should look into the PHPUnit documentation to get the latest information on these dataset specifications.

Specify a seed dataset

In the previous setup for the database testcase we have specified a seed file for the database fixture. We now create this file specified in the Flat XML format:

<?xml version="1.0" encoding="UTF-8" ?>
<dataset>
    <zfbugs bug_id="1" bug_description="system needs electricity to run"
        bug_status="NEW" created_on="2007-04-01 00:00:00"
        updated_on="2007-04-01 00:00:00" reported_by="goofy"
        assigned_to="mmouse" verified_by="dduck" />
    <zfbugs bug_id="2" bug_description="Implement Do What I Mean function"
        bug_status="VERIFIED" created_on="2007-04-02 00:00:00"
        updated_on="2007-04-02 00:00:00" reported_by="goofy"
        assigned_to="mmouse" verified_by="dduck" />
    <zfbugs bug_id="3" bug_description="Where are my keys?" bug_status="FIXED"
        created_on="2007-04-03 00:00:00" updated_on="2007-04-03 00:00:00"
        reported_by="dduck" assigned_to="mmouse" verified_by="dduck" />
    <zfbugs bug_id="4" bug_description="Bug no product" bug_status="INCOMPLETE"
        created_on="2007-04-04 00:00:00" updated_on="2007-04-04 00:00:00"
        reported_by="mmouse" assigned_to="goofy" verified_by="dduck" />
</dataset>

We will work with this four entries in the database table "zfbugs" in the next examples. The required MySQL schema for this example is:

CREATE TABLE IF NOT EXISTS `zfbugs` (
    `bug_id` int(11) NOT NULL AUTO_INCREMENT,
    `bug_description` varchar(100) DEFAULT NULL,
    `bug_status` varchar(20) DEFAULT NULL,
    `created_on` datetime DEFAULT NULL,
    `updated_on` datetime DEFAULT NULL,
    `reported_by` varchar(100) DEFAULT NULL,
    `assigned_to` varchar(100) DEFAULT NULL,
    `verified_by` varchar(100) DEFAULT NULL,
PRIMARY KEY  (`bug_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 ;

A few initial database tests

Now that we have implemented the two required abstract methods of the Zend_Test_PHPUnit_DatabaseTestCase and specified the seed database content, which will be re-created for each new test, we can go about to make our first assertion. This will be a test to insert a new bug.

class BugsTest extends Zend_Test_PHPUnit_DatabaseTestCase
{
    public function testBugInsertedIntoDatabase()
    {
        $bugsTable = new Bugs();
 
        $data = array(
            'created_on'      => '2007-03-22 00:00:00',
            'updated_on'      => '2007-03-22 00:00:00',
            'bug_description' => 'Something wrong',
            'bug_status'      => 'NEW',
            'reported_by'     => 'garfield',
            'verified_by'     => 'garfield',
            'assigned_to'     => 'mmouse',
        );
 
        $bugsTable->insert($data);
 
        $ds = new Zend_Test_PHPUnit_Db_DataSet_QueryDataSet(
            $this->getConnection()
        );
        $ds->addTable('zfbugs', 'SELECT * FROM zfbugs');
 
        $this->assertDataSetsEqual(
            $this->createFlatXmlDataSet(dirname(__FILE__)
                                      . "/_files/bugsInsertIntoAssertion.xml"),
            $ds
        );
    }
}

Now up to the $bugsTable->insert($data); everything looks familiar. The lines after that contain the assertion methodname. We want to verify that after inserting the new bug the database has been updated correctly with the given data. For this we create a Zend_Test_PHPUnit_Db_DataSet_QueryDataSet instance and give it a database connection. We will then tell this dataset that it contains a table "zfbugs" which is given by an SQL statement. This current/actual state of the database is compared to the expected database state which is contained in another XML file "bugsInsertIntoAssertions.xml". This XML file is a slight deviation from the one given above and contains another row with the expected data:

<?xml version="1.0" encoding="UTF-8" ?>
<dataset>
    <!-- previous 4 rows -->
    <zfbugs bug_id="5" bug_description="Something wrong" bug_status="NEW"
        created_on="2007-03-22 00:00:00" updated_on="2007-03-22 00:00:00"
        reported_by="garfield" assigned_to="mmouse" verified_by="garfield" />
</dataset>

There are other ways to assert that the current database state equals an expected state. The "Bugs" table in the example already knows a lot about its inner state, so why not use this to our advantage? The next example will assert that deleting from the database is possible:

class BugsTest extends Zend_Test_PHPUnit_DatabaseTestCase
{
    public function testBugDelete()
    {
        $bugsTable = new Bugs();
 
        $bugsTable->delete(
            $bugsTable->getAdapter()->quoteInto("bug_id = ?", 4)
        );
 
        $ds = new Zend_Test_PHPUnit_Db_DataSet_DbTableDataSet();
        $ds->addTable($bugsTable);
 
        $this->assertDataSetsEqual(
            $this->createFlatXmlDataSet(dirname(__FILE__)
                                      . "/_files/bugsDeleteAssertion.xml"),
            $ds
        );
    }
}

We have created a Zend_Test_PHPUnit_Db_DataSet_DbTableDataSet dataset here, which takes any Zend_Db_Table_Abstract instance and adds it to the dataset with its table name, in this example "zfbugs". You could add several tables more if you wanted using the method addTable() if you want to check for expected database state in more than one table.

Here we only have one table and check against an expected database state in "bugsDeleteAssertion.xml" which is the original seed dataset without the row with id 4.

Since we have only checked that two specific tables (not datasets) are equal in the previous examples we should also look at how to assert that two tables are equal. Therefore we will add another test to our TestCase which verifies updating behaviour of a dataset.

class BugsTest extends Zend_Test_PHPUnit_DatabaseTestCase
{
    public function testBugUpdate()
    {
        $bugsTable = new Bugs();
 
        $data = array(
            'updated_on'      => '2007-05-23',
            'bug_status'      => 'FIXED'
        );
 
        $where = $bugsTable->getAdapter()->quoteInto('bug_id = ?', 1);
 
        $bugsTable->update($data, $where);
 
        $rowset = $bugsTable->fetchAll();
 
        $ds        = new Zend_Test_PHPUnit_Db_DataSet_DbRowset($rowset);
        $assertion = $this->createFlatXmlDataSet(
            dirname(__FILE__) . '/_files/bugsUpdateAssertion.xml'
        );
        $expectedRowsets = $assertion->getTable('zfbugs');
 
        $this->assertTablesEqual(
            $expectedRowsets, $ds
        );
    }
}

Here we create the current database state from a Zend_Db_Table_Rowset_Abstract instance in conjunction with the Zend_Test_PHPUnit_Db_DataSet_DbRowset($rowset) instance which creates an internal data-representation of the rowset. This can again be compared against another data-table by using the $this->assertTablesEqual() assertion.

Usage, API and Extensions Points

The Quickstart already gave a good introduction on how database testing can be done using PHPUnit and the Zend Framework. This section gives an overview over the API that the Zend_Test_PHPUnit_Db component comes with and how it works internally.

Note: Some Remarks on Database Testing
Just as the Controller TestCase is testing an application at an integration level, the Database TestCase is an integration testing method. Its using several different application layers for testing purposes and therefore should be consumed with caution.
It should be noted that testing domain and business logic with integration tests such as Zend Framework's Controller and Database TestCases is a bad practice. The purpose of an Integration test is to check that several parts of an application work smoothly when wired together. These integration tests do not replace the need for a set of unit tests that test the domain and business logic at a much smaller level, the isolated class.

The Zend_Test_PHPUnit_DatabaseTestCase class

The Zend_Test_PHPUnit_DatabaseTestCase class derives from the PHPUnit_Extensions_Database_TestCase which allows to setup tests with a fresh database fixture on each run easily. The Zend implementation offers some additional convenience features over the PHPUnit Database extension when it comes to using Zend_Db resources inside your tests. The workflow of a database test-case can be described as follows.

For each test PHPUnit creates a new instance of the TestCase and calls the setUp() method.
The Database TestCase creates an instance of a Database Tester which handles the setting up and tearing down of the database.
The database tester collects the information on the database connection and initial dataset from getConnection() and getDataSet() which are both abstract methods and have to be implemented by any Database Testcase.
By default the database tester truncates the tables specified in the given dataset, and then inserts the data given as initial fixture.
When the database tester has finished setting up the database, PHPUnit runs the test.
After running the test, tearDown() is called. Because the database is wiped in setUp() before inserting the required initial fixture, no actions are executed by the database tester at this stage.

Note: The Database TestCase expects the database schema and tables to be setup correctly to run the tests. There is no mechanism to create and tear down database tables.

The Zend_Test_PHPUnit_DatabaseTestCase class has some convenience functions that can help writing tests that interact with the database and the database testing extension.

The next table lists only the new methods compared to the PHPUnit_Extensions_Database_TestCase, whose » API is documented in the PHPUnit Documentation.

**Zend_Test_PHPUnit_DatabaseTestCase API Methods**
Method	Description
createZendDbConnection(Zend_Db_Adapter_Abstract $connection, $schema)	Create a PHPUnit Database Extension compatible Connection instance from a Zend_Db_Adapter_Abstract instance. This method should be used in for testcase setup when implementing the abstract getConnection() method of the database testcase.
getAdapter()	Convenience method to access the underlying Zend_Db_Adapter_Abstract instance which is nested inside the PHPUnit database connection created with getConnection().
createDbRowset(Zend_Db_Table_Rowset_Abstract $rowset, $tableName = null)	Create a DataTable Object that is filled with the data from a given Zend_Db_Table_Rowset_Abstract instance. The table the rowset is connected to is chosen when `$tableName` is `NULL`.
createDbTable(Zend_Db_Table_Abstract $table, $where = null, $order = null, $count = null, $offset = null)	Create a DataTable object that represents the data contained in a Zend_Db_Table_Abstract instance. For retrieving the data fetchAll() is used, where the optional parameters can be used to restrict the data table to a certain subset.
createDbTableDataSet(array $tables=array())	Create a DataSet containing the given `$tables`, an array of Zend_Db_Table_Abstract instances.

Integrating Database Testing with the ControllerTestCase

Because PHP does not support multiple inheritance it is not possible to use the Controller and Database testcases in conjunction. However you can use the Zend_Test_PHPUnit_Db_SimpleTester database tester in your controller test-case to setup a database enviroment fixture for each new controller test. The Database TestCase in general is only a set of convenience functions which can also be accessed and used without the test case.

Example #1 Database integration example

This example extends the User Controller Test from the Zend_Test_PHPUnit_ControllerTestCase documentation to include a database setup.

class UserControllerTest extends Zend_Test_PHPUnit_ControllerTestCase
{
    public function setUp()
    {
        $this->setupDatabase();
        $this->bootstrap = array($this, 'appBootstrap');
        parent::setUp();
    }
 
    public function setupDatabase()
    {
        $db = Zend_Db::factory(...);
        $connection = new Zend_Test_PHPUnit_Db_Connection($db,
                                                      'database_schema_name');
        $databaseTester = new Zend_Test_PHPUnit_Db_SimpleTester($connection);
 
        $databaseFixture =
                    new PHPUnit_Extensions_Database_DataSet_FlatXmlDataSet(
                        dirname(__FILE__) . '/_files/initialUserFixture.xml'
                    );
 
        $databaseTester->setupDatabase($databaseFixture);
    }
}

Now the Flat XML dataset "initialUserFixture.xml" is used to set the database into an initial state before each test, exactly as the DatabaseTestCase works internally.

Using the Database Testing Adapter

There are times when you don't want to test parts of your application with a real database, but are forced to because of coupling. The Zend_Test_DbAdapter offers a convenient way to use a implementation of Zend_Db_Adapter_Abstract without having to open a database connection. Furthermore this Adapter is very easy to mock from within your PHPUnit testsuite, since it requires no constructor arguments.

The Test Adapter acts as a stack for various database results. Its order of results have to be userland implemented, which might be a tedious task for tests that call many different database queries, but its just the right helper for tests where only a handful of queries are executed and you know the exact order of the results that have to be returned to your userland code.

$adapter   = new Zend_Test_DbAdapter();
$stmt1Rows = array(array('foo' => 'bar'), array('foo' => 'baz'));
$stmt1     = Zend_Test_DbStatement::createSelectStatement($stmt1Rows);
$adapter->appendStatementToStack($stmt1);
 
$stmt2Rows = array(array('foo' => 'bar'), array('foo' => 'baz'));
$stmt2     = Zend_Test_DbStatement::createSelectStatement($stmt2Rows);
$adapter->appendStatementToStack($stmt2);
 
$rs = $adapter->query('SELECT ...'); // Returns Statement 2
while ($row = $rs->fetch()) {
    echo $rs['foo']; // Prints "Bar", "Baz"
}
$rs = $adapter->query('SELECT ...'); // Returns Statement 1

Behaviour of any real database adapter is simulated as much as possible such that methods like fetchAll(), fetchObject(), fetchColumn and more are working for the test adapter.

You can also put INSERT, UPDATE and DELETE statement onto the result stack, these however only return a statement which allows to specifiy the result of $stmt->rowCount().

$adapter = new Zend_Test_DbAdapter();
$adapter->appendStatementToStack(
    Zend_Test_DbStatement::createInsertStatement(1)
);
$adapter->appendStatementToStack(
    Zend_Test_DbStatement::createUpdateStatement(2)
);
$adapter->appendStatementToStack(
    Zend_Test_DbStatement::createDeleteStatement(10
));

By default the query profiler is enabled, so that you can retrieve the executed SQL statements and their bound parameters to check for the correctness of the execution.

$adapter = new Zend_Test_DbAdapter();
$stmt = $adapter->query("SELECT * FROM bugs");
 
$qp = $adapter->getProfiler()->getLastQueryProfile();
 
echo $qp->getQuerY(); // SELECT * FROM bugs

The test adapter never checks if the query specified is really of the type SELECT, DELETE, INSERT or UPDATE which is returned next from the stack. The correct order of returning the data has to be implemented by the user of the test adapter.

The Test adapter also specifies methods to simulate the use of the methods listTables(), describeTables() and lastInsertId(). Additionally using the setQuoteIdentifierSymbol() you can specify which symbol should be used for quoting, by default none is used.