Making PHPUnit, Doctrine & MySQL Play Nicely

I’m currently using Doctrine for the first time and really enjoying it so far. One of the pain points for me though has been in getting Doctrine setup with PHPUnit for testing. One of the main Doctrine contributors, Benjamin Beberlei, has written a package called DoctrineExtensions which amongst other things adds a class called DoctrineExtensions\PHPUnit\OrmTestCase which extends PHPUnit’s DbUnit database test case class. This all works well in principle but hits a major snag in reality: MySQL doesn’t allow InnoDb tables with foreign keys to be truncated. PHUnit’s database extension truncates the database tables before each test run and inserts a fresh set of data to work with. By default Doctrine uses the InnoDb table type and creates foreign key constraints to enforce referential integrity. With this combination the test run errors out every time if you have Doctrine entities that reference other entities through foreign keys. There is a solution to this however which I’ll explain below.

Solving this problem in PHPUnit

This problem isn’t exclusive to unit testing with Doctrine. Mike Lively, the author of PHPUnit’s DbUnit extension, has provided a solution in a Gist. Basically, this extends the PHPUnit_Extensions_Database_Operation_Truncate class to suspend MySQL foreign key checks for the length of the truncate operation. The getSetUpOperation() method then needs to be overridden in the test class to use the custom MySQL truncate class. All in all a neat and elegant solution.

Porting this Solution to Doctrine

Porting this solution turned out to be fairly simple, with the exception of one issue which needed to be addressed. I started with the custom MySQL truncate class, which is almost an exact copy of the one Mike Lively wrote:


<?php

namespace Tests\Models\Db;

use PHPUnit_Extensions_Database_Operation_Truncate,
 PHPUnit_Extensions_Database_DB_IDatabaseConnection,
 PHPUnit_Extensions_Database_DataSet_IDataSet;
/**
 * MySQL truncate class that allows foreign key checks to be suspended for the duration of the truncate operation.
 * @author Mike Lively
 * @author Jeremy Cook
 * @version 1.0
 * @package Veridis
 * @see https://gist.github.com/1319731
 */
class MySQLTruncate extends PHPUnit_Extensions_Database_Operation_Truncate
{
 /**
 * Executes the truncate.
 * Note: the $connection variable here is an instance of DoctrineExtensions\PHPUnit\TestConnection
 * (non-PHPdoc)
 * @see PHPUnit_Extensions_Database_Operation_Truncate::execute()
 */
 public function execute(PHPUnit_Extensions_Database_DB_IDatabaseConnection $connection, PHPUnit_Extensions_Database_DataSet_IDataSet $dataSet)
 {
 $connection->getConnection()->exec("SET @PREVIOUS_foreign_key_checks = @@foreign_key_checks");
 $connection->getConnection()->exec("SET @@foreign_key_checks = 0");
 parent::execute($connection, $dataSet);
 $connection->getConnection()->exec("SET @@foreign_key_checks = @PREVIOUS_foreign_key_checks");
 }

}

This is pretty self explanatory and simply disables MySQL’s foreign key checks while the database tables are truncated and populated with data. I then created an abstract base class which extends the OrmTestCase class to set up the entity manager. This can then be extended by any class that needs to test against the database with an entity manager. To this class I added the code to register the custom MySQL truncate class, and a simplified version of the class looks like this:


<?php

use DoctrineExtensions\PHPUnit\OrmTestCase,
 Doctrine\ORM\Configuration,
 Doctrine\Common\Cache\ArrayCache,
 Doctrine\ORM\EntityManager,
 PHPUnit_Extensions_Database_Operation_Composite,
 PHPUnit_Extensions_Database_Operation_Factory;

/**
 * Base class for test cases that need an entity manager to work with.
 * Defines an entity manager for the tests to work with.
 * @author Jeremy Cook
 * @version 1.0
 */
 abstract class Base extends OrmTestCase
 {
 /**
 * Method to create the entity manager, used by parent methods.
 * @return \Doctrine\ORM\EntityManager
 * @see OrmTestCase::createEntityManager()
 */
 protected function createEntityManager ()
 {
 $config = new Configuration;
 $cache = new ArrayCache();
 //Set up config here.
 $conn = array();
 return EntityManager::create($conn, $config);
 }
 /**
 * Overrides the parent method to add a custom MySQL truncate operation.
 * This suspends foreign key checks for the duration of the truncate command.
 * (non-PHPdoc)
 * @see DoctrineExtensions\PHPUnit\DatabaseTestCase::getSetUpOperation()
 */
 public function getSetUpOperation()
 {
 $truncate = new MySQLTruncate();
 return new PHPUnit_Extensions_Database_Operation_Composite(array(
 $truncate,
 PHPUnit_Extensions_Database_Operation_Factory::INSERT()
 ));
 }
 }

The createEntityManager method is defined as abstract in OrmTestCase and needs to return an instance of Doctrine\ORM\EntityManager. The getSetUpOperation method is defined in PHPUnit_Extension_Database_TestCase and is overriden here to add the custom MySQL truncate class. There’s one final abstract method that needs to be implemented in a test class and that’s getDataSet. This is defined in PHPUnit_Extension_Database_TestCase and needs to return a PHPUnit_Extensions_Database_DataSet_IDataSet instance to populate the database with data. With this I created a test case extending this class, added data to populate the database, ran it and got an exception. What was going on?

The Last Problem

I traced the exception down to DoctrineExtensions\PHPUnit\TestConnection::getTruncateCommand(). This method was hard coded to throw an exception when called. I spoke to Benjamin Beberlei on IRC to ask him about this and he couldn’t remember why this code was added here. I made an edit so that the getTruncateCommand method looks as follows:


<?php

/**

* Returns the command used to truncate a table.
*
* @return string
*/
public function getTruncateCommand()
{
 return 'TRUNCATE';
}

With this everything seems to work. Doctrine’s PHPUnit test extension is able to truncate tables in a MySQL database that have foreign key constraints.

3 thoughts on “Making PHPUnit, Doctrine & MySQL Play Nicely

  1. You will avoid The Last Problem if your MySQLTruncate extends from DoctrineExtensions\PHPUnit\Operations\Truncate instead of the PHPUnit’s base Truncate. This is because the former is built to use Doctrine’s internal DatabasePlatform to generate the Truncate SQL.

    1. Thanks for that Ezequiel. This article is quite out of date now. My personal preference these dates for this sort of functional testing would be to use Doctrine Fixtures to load the data. Along with this I would either drop and recreate the db with each test run or just delete from the db tables before inserting data.

Leave a Reply