Adding Database Tests to Existing PHPUnit Test Cases

When I was first creating the Database Extension for PHPUnit I realized that there was a very high likelihood that several people would have tests that were already written that they would like to add additional database tests too. To accomplish this I actually wrote the PHPUnit_Extensions_Database_DefaultTester class. In fact, if you were to look at the source of the database test case you will see that all of it’s operations are actually forwarded to this class which does all of the work.

Please continue reading to see how you can use composition to add database tests to your existing test cases.

So, now you might be wondering how can I use this class to add data-centric tests to my existing test case. I am going to use the BankAccount example that exists in PHPUnit to show you. Below you can find the existing bank account test case (without comments.)

require_once 'PHPUnit/Framework/TestCase.php';
require_once 'BankAccount.php';

class BankAccountTest extends PHPUnit_Framework_TestCase
{
    protected $ba;

    protected function setUp()
    {
        $this->ba = new BankAccount;
    }

    public function testBalanceIsInitiallyZero()
    {
        $this->assertEquals(0, $this->ba->getBalance());
    }

    public function testBalanceCannotBecomeNegative()
    {
        try {
            $this->ba->withdrawMoney(1);
        }

        catch (BankAccountException $e) {
            $this->assertEquals(0, $this->ba->getBalance());

            return;
        }

        $this->fail();
    }

    public function testBalanceCannotBecomeNegative2()
    {
        try {
            $this->ba->depositMoney(-1);
        }

        catch (BankAccountException $e) {
            $this->assertEquals(0, $this->ba->getBalance());

            return;
        }

        $this->fail();
    }
}

I would like to add a test to see if a bank account is created in the database when the bank account’s constructor is called. This is the same test I showed you in my first example of the database extension. However, I do not want to add the overhead of creating and seeding the database for my existing tests that obviously doesn’t need them. I should tell you right away that I would actually prefer in these cases to just create a new test case to handle just the data-centric tests. However, one thing using composition will assist greatly with is if you want to test database code that should throw exceptions as well as perform data operations. I am sure there are many other valid uses that I haven’t even thought of as well.

First thing I need to do is add an instance of PHPUnit_Extensions_Database_DefaultTester to the test case. To create a tester instance I will need to have a pdo connection. For this example I am going to use a database I created on my local machine in mysql.

class BankAccountTest extends PHPUnit_Framework_TestCase
{
    // ...
    protected function getDatabaseTester()
    {
        $pdo = new PDO('mysql:host=localhost;dbname=test', 'root', '');
        $connection = new PHPUnit_Extensions_Database_DB_DefaultDatabaseConnection($pdo, 'test');
        return new PHPUnit_Extensions_Database_DefaultTester($connection);
    }
    // ...
}

I will now need to specify the different events that will occur on startUp and tearDown of the new test and the data that will be used. To do this I am going to add a few calls to the tester in my getDatabaseTester() method. The first method, setSetUpOperation(), will tell the tester which operation I would like to perform on the database at the start of each test. Likewise, the second method setTearDownOperation() will tell the tester which operation to perform on the database at the end of each test. Both of these methods will accept an object implementing PHPUnit_Extensions_Database_Operation_IDatabaseOperation. The most common one you will probably want to use is the ‘clean insert’ operation. This operation will perform a truncate on each table in your data set and then insert all rows in the dataset. I am going to use a factory class to generate the operation. If you want to view all of the current operations available I recommend browsing the following src directory: http://www.phpunit.de/browser/phpunit/branches/3.2/PHPUnit/Extensions/Database/Operation. The final method we will need to call is setDataSet(). This will
accept any of the data set objects. I am just going to use a flat xml data set with a few rows.

<dataset>
    <bank_account account_number="15934903649620486" balance="100.00">
    <bank_account account_number="15936487230215067" balance="1216.00">
    <bank_account account_number="12348612357236185" balance="89.00">
</bank_account>

So with these new changes our getDatabaseTester() method will look like this:

require_once 'PHPUnit/Framework/TestCase.php';
require_once 'BankAccount.php';

class BankAccountTest extends PHPUnit_Framework_TestCase
{
    // ...
    protected function getDatabaseTester()
    {
        $pdo = new PDO('mysql:host=localhost;dbname=test', 'root', '');
        $connection = new PHPUnit_Extensions_Database_DB_DefaultDatabaseConnection($pdo, 'test');
        $tester = new PHPUnit_Extensions_Database_DefaultTester($connection);
        $tester->setSetUpOperation(PHPUnit_Extensions_Database_Operation_Factory::CLEAN_INSERT());
        $tester->setTearDownOperation(PHPUnit_Extensions_Database_Operation_Factory::NONE());
        $tester->setDataSet(new PHPUnit_Extensions_Database_DataSet_FlatXMLDataSet(dirname(__FILE__).'/_files/bank-account-seed.xml'));
       
        return $tester;
    }
    // ...
}

You will notice that I used an operation called NONE() on teardown. This, as you may have guessed, is a NULL operation. It does absolutely nothing which in this case is perfectly alright since we are truncating at the the beginning of each test.

Now I am ready to add my new test method. In this test I am going to call my getDatabaseTester(). I will the call the onSetUp() method of the tester. This will perform the setup operation that we specified previously. Then I will create a new bank account, retrieve a dataset from the connection I created in getDatabaseTester() (utilizing the getConnection() method,) and compare the results using the assertDataSetsEqual() static method of the Database TestCase class. Finally I will clean up my mess (which in this case means nothing) using the onTearDown() method of the tester object.

You will notice that I have access to the two asserts of the database test case. They are static, public methods and can be accessed from anywhere.

require_once 'PHPUnit/Framework/TestCase.php';
require_once 'BankAccount.php';

class BankAccountTest extends PHPUnit_Framework_TestCase
{
    // ...
    protected function getDatabaseTester()
    {
        $pdo = new PDO('mysql:host=localhost;dbname=test', 'root', '');
        $connection = new PHPUnit_Extensions_Database_DB_DefaultDatabaseConnection($pdo, 'test');
        $tester = new PHPUnit_Extensions_Database_DefaultTester($connection);
        $tester = new PHPUnit_Extensions_Database_DefaultTester($pdo);
        $tester->setSetUpOperation(PHPUnit_Extensions_Database_Operation_Factory::CLEAN_INSERT());
        $tester->setTearDownOperation(PHPUnit_Extensions_Database_Operation_Factory::NONE());
        $tester->setDataSet(new PHPUnit_Extensions_Database_DataSet_FlatXMLDataSet(dirname(__FILE__).'/_files/bank-account-seed.xml'));
       
        return $tester;
    }

    public function testNewAccountCreation()
    {
        $tester = $this->getDatabaseTester();
        $tester->onSetUp();

        $bank_account = new BankAccount('12345678912345678', new PDO('mysql:host=localhost;dbname=test', 'root', ''));
       
        $xml_dataset = $this->createFlatXMLDataSet(dirname(__FILE__).'/_files/bank-account-after-new-account.xml');
        PHPUnit_Extensions_Database_TestCase::assertDataSetsEqual($xml_dataset, $tester->getConnection()->createDataSet());
       
        $tester->onTearDown();
    }
    // ...
}

That’s all there is to it. Now you can just go nuts adding new database tests or plain old php tests only incurring the database overhead when needed.

Share
This entry was posted in PHP Testing. Bookmark the permalink.

9 Responses to Adding Database Tests to Existing PHPUnit Test Cases

  1. sas171 says:

    Hi, I was playing with the extension from the svn and wondered if there is a possibility to limit the columns in a table I want to compare. For example in many cases I’m not interested to check the id column.

  2. Mike Lively says:

    Yes, this is in fact possible utilizing the PHPUnit_Extensions_DBUnit_DataSet_DataSetFilter decorator.

    You basically pass an instantiated dataset object to this class’ constructor as the first parameter and then you pass an associative array keyed on table names and the values are an array of columns to ignore. In otherwords, something like this:

    $databaseDataSet = $this->getConnection->createDataSet();

    $filteredDataSet = new PHPUnit_Extensions_DBUnit_DataSet_DataSetFilter($databaseDataSet, array(
    ‘messages’ => array(‘created’, ‘last_modified’)
    ));

    You can also do it at the table level.

    $filteredTable = new PHPUnit_Extensons_DBUnit_DataSet_TableFilter($messagesTable, array(‘created’, ‘last_modified’));

    Hope that helps

  3. sas171 says:

    Thank you for the fast response, Mike. The extension works very nice. I love it! Can’t wait for PHPUnit 3.2 stable to be able to use it in production environment.

  4. Dean Farrell says:

    Has this functionality been moved, removed, renamed?

    I keep getting undefined method errors. I poked around in Framework/TestCase.php and saw no mention of the PHPUnit_Extensions_Database_DefaultTester method.

    Thanks.

  5. Adrian says:

    Hi Mark,

    Great extension! I’ve noticed that you used to work on the official documentation of this feature (http://www.phpunit.de/pocket_guide/3.2/en/database.html). However, it hasn’t been updated in last 6 months. Any chance you will find some time to finish it? :)

    Cheers.

  6. Adrian Schoenig says:

    I had the problem that loading the database with contents from an XML file was horribly slow (took more than a minute per test case on my setup!) even though there are just a dozen tables in the file with a handful of rows in each.

    PHPUnit_Extensions_Database_Operation_RowBased is the bottle neck. I got it to speed up by a factor of almost 40 by changing to transactions and most importantly by replacing

    $databaseTableMetaData = $databaseDataSet->getTableMetaData($table->getTableMetaData()->getTableName());

    with:

    $databaseTableMetaData = $table->getTableMetaData();

  7. Adrian Schoenig says:

    PHPUnit_Extensions_Database_Operation_RowBased::execute() is the bottle neck to be precise.

  8. mjimenez says:

    You said
    “Yes, this is in fact possible utilizing the PHPUnit_Extensions_DBUnit_DataSet_DataSetFilter decorator”

    In my 3.2.21 setting I have to use PHPUnit_Extensions_Database_DataSet_DataSetFilter class instead

  9. SM PHP says:

    I am getting below error when i execute ur code

    Fatal error: Call to undefined method PHPUnit_Extensions_Database_DefaultTester::createFlatXMLDataSet() in BankAccountTest.php on line 98

    Can u tell me why ?

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>