More Bulletproofing with PHPUnit’s Database Extension

Share this article

In a previous article I discussed how you can create more robust code that queries your relational database through automated testing. But, as you know, querying is just one part of the picture. There are still other database operations, namely create, update, and delete. Just like retrieval, they too should be tested.

I’ll be exploring the other features of PHPUnit’s Database Extensions in this article while building on your knowledge of database testing in PHP. Specifically, the aim is:

  1. Learn how to test the result of an insert operation
  2. Use PHPUnit’s Database assertion API to test the operation

I’ve written this article to be self-contained so that you won’t have to read my first tutorial beforehand, but if you are new to Test Driven Development, I highly recommend you read it as well as the introduction to PHPUnit by Michelle Saver.

To follow along with this article, you’ll need PHPUnit and the PHPUnit Database extension installed. Companion code can be found on GitHub; feel free to clone the source and play with it.

Setting Up

Like in my first article, we’ll pretend that we’re the back-end guru of a fictional web-based magazine. The magazine is composed of many sections, and each section has feature articles which are set by the editor of that section.

The first articles app lacked an implementation for saving an article to the database and this is the functionality we are going to test. We’ll assume that there is already a web interface where our editors can write their articles and this will the call code that saves an article to the database. The only thing that is lacking is the implementation itself and, of course, the test. By making this assumption, we can isolate the behavior we want to test.

The first step is to use the database dump included in the source code to create the database in MySQL. Here’s a bird’s eye view:

The next step, since we are writing a database test, is to set up the data. After all, we need something to test the operations on! Create an XML file named seed.xml, with the root element dataset and some sample data. For example, here’s how the file would look:

<dataset>       
 <table name="sections">
  <column>id</column>
  <column>name</column>
  <row>
   <value>1</value>
   <value>PHP</value>
  </row>
 </table>
 <table name="articles">
  <column>id</columm>
  ...
 </table>
</dataset>

I recommend you to write your own data just for practice, although you can always use the sample dataset in the GitHub repo. All the tables there are filled already.

Writing the Test Class

Every time you run the test, you need to clear and insert the initial data into the database. This is done by overriding some methods in a class that extends PHPUnit_Extensions_Database_TestCase.

<?php
class ArticleDaoTest extends PHPUnit_Extensions_Database_TestCase
{
    public function getConnection() {
        $pdo = new PDO("mysql:host=localhost;dbname=bulletproof",
            "root", "password");
        return $this->createDefaultDBConnection($pdo,
            "bulletproof");
    }

    public function getSetUpOperation() {
        // whether you want cascading truncates
        // set false if unsure
        $cascadeTruncates = false;

        return new PHPUnit_Extensions_Database_Operation_Composite(array(
            new PHPUnit_Extensions_Database_Operation_MySQL55Truncate($cascadeTruncates),
            PHPUnit_Extensions_Database_Operation_Factory::INSERT()
        ));
    }

    public function getDataSet() {
        return $this->createXMLDataSet("seed.xml");
    }
}

In the code above we overrode the getConnection() method to tell the test class to connect to the database and then get the test data from the return of getDataSet() method which we also overrode.

Each test will truncate the tables and repopulate them using seed.xml to re-initialize the database. If you’re using MySQL 5.5 or above, you may encounter a problem, since the TRUNCATE command is not allowed for InnoDB tables that make use of foreign key constraints. To work around this restriction, we need to override its getSetUpOperation() method.

The PHPUnit_Extensions_Database_Operation_MySQL55Truncate class referenced in getSetUpOperation() is a custom subclass of PHPUnit_Extensions_Database_Operation_Truncate that overrides the execute() method:

<?php
class PHPUnit_Extensions_Database_Operation_MySQL55Truncate extends PHPUnit_Extensions_Database_Operation_Truncate
{
    public function execute(PHPUnit_Extensions_Database_DB_IDatabaseConnection $connection, PHPUnit_Extensions_Database_DataSet_IDataSet $dataSet) {
        $connection->getConnection()->query("SET @PHAKE_PREV_foreign_key_checks = @@foreign_key_checks");
        $connection->getConnection()->query("SET foreign_key_checks = 0");
        parent::execute($connection, $dataSet);
        $connection->getConnection()->query("SET foreign_key_checks = @PHAKE_PREV_foreign_key_checks");
    }
}

As scary as it might look, the execute() method just remembers MySQL’s foreign keys setting, turns it off temporarily so the TRUNCATE command can proceed, and then restores the setting.

Writing Your Test

Now it’s time to get our hands dirty writing the test itself. Suppose we agree on the following update for the IArticleDao interface:

<?php
interface IArticleDao
{
    // here is the new method for saving
    public function save($article);

    // already implemented in the first article
    public function getArticles($sectionId);
}

The first test to write is the test for saving an article. It should ensure that the data of the article goes to the correct columns in the articles table.

<?php
class ArticleDaoTest extends PHPUnit_Extension_Database_TestCase
{
...
    public function testSaveArticle() {
        $article = new ArticleDAO();
        $article->save(array(
            "title" => "PHP is Great!",
            ...
            ));

        $resultingTable = $this->getConnection()
            ->createQueryTable("articles",
            "SELECT * FROM articles");
        
        $expectedTable = $this->createXmlDataSet(
            "expectedArticles.xml")
            ->getTable("articles");
        $this->assertTablesEqual($expectedTable,
            $resultingTable);   
    }
}

First we instantiate an instance of ArticleDAO and then call the new save() method we are testing and pass in the associative array representing the article. After we have saved the article, we want to check if the data has been saved correctly. We query for the contents of the table and place them in instance of a QueryTable.

With the results of our save() operation in $resultingTable, we have to compare it with an expected data set. But where do we get this expected data set? From the method createXmlDataSet() and the XML file expectedArticles.xml.

The getTable() method indicates we only want to get the contents of the articles table since this is the one we want to compare against our $resultingTable.

The final step would then be to compare the $resultingTable and $expectedTable which is done by feeding both variables to the assertTablesEqual() method.

The setup of the data set in expectedArticles.xml is the same as our seed.xml; here’s an example:

<dataset>
 <table name="articles">
  <column>id</column>
  <column>title</column>
...
  <row>
   <value>1</value>
   <value>PHP is Great!</value>
...
  </row>
 </table>
</dataset>

Passing the Tests

In Test Driven Development, failure is the first step. If you run the test now it will fail because we haven’t written the implementation yet.

jeune@Miakka:~/bulletproofing$ phpunit ArticleTest.php
PHPUnit 3.6.7 by Sebastian Bergman.

PHP Fatal error:  Class ArticleDAO contains 1 abstract method and must therefore be declared abstract or implement the remaining methods (IArticleDAO::save) in /bulletproofing/ArticleDao.php on line 12

The next step is to write the code that will let us pass the test (that is, save an article to the database). Let’s implement the method that was added to our IArticleDAO interface.

<php
class ArticleDAO implements IArticleDAO
{
...
    public function save($article) {
        $db = new PDO(
            "mysql:host=localhost;dbname=bulletproof", 
            "root", "password");

        $stmt = $db->prepare("INSERT INTO articles (title, description, content, preview_image, section_id) value (:title, :description, :content, :preview_image, :section_id)");
        $stmt->execute($article);                 
        return true;
    }
}

Now if we run things again, we should see a passing test.

jeune@Miakka:~/Bulletproofing$ phpunit ArticleTest.php
PHPUnit 3.6.7 by Sebastian Bergman.

.

Time: 0 seconds, Memory: 3.00Mb

OK (1 test, 1 assertion)

Summary

In this tutorial we’ve seen how to write a database test of an insert operation in PHP using PHPUnit’s Database Extension. The technique presented in this tutorial should also work for delete and update operations.

Image via Fotolia

Jeune AsuncionJeune Asuncion
View Author

Jeune has a fascination for building and solving things. Right now, he is interested in data mining, algorithms, performance, and software design. When not in front of his laptop, he cooks mean Asian dishes or is out kicking ass on the football (soccer) field.

Advanced
Share this article
Read Next
Get the freshest news and resources for developers, designers and digital creators in your inbox each week