Like many members of the Zend Framework community, I've been integrating Doctrine 2 into my projects for quite some time, despite the popular ORM not yet being officially supported by the Zend Framework. The recently released Doctrine 2 presents significant advantages over its 1.X predecessor, advantages which are even more pronounced when compared to the Zend Framework's default database solution, namely the Zend_Db component. It really does make database integration fun, and I highly recommend checking it out if you haven't already (to get a head start on the initially confusing integration process, check out my z2d2 project, hosted on GitHub).
One of many cool Doctrine 2 features is the Doctrine Console, a command-line utility you can use to easily carry out a variety of administration tasks such as generating the database schema based on a set of map files, converting Doctrine 1.X schema's to 2.X schemas, and dropping a database. For instance, given a series of annotated classes, generating the associated table schemas is as easy as executing the following command:
$ doctrine orm:schema-tool:create
So what does all of this have to do with the purpose of this installment? When running unit tests which vet various aspects of your database you'll want to setup (create) and teardown (destroy) the test database, ensuring that a pristine and repeatable version of the database is available to each test. This logically involves repeatedly recreating the database, schema, and potentially data.
While there exist many programmatic approaches to setting up and tearing down a database, when using Doctrine 2 it's possible to use the SchemaTool class, which presents a programmatic interface to many of the features made available through the Doctrine Console. Using this class you can retrieve the mapping metadata, using it to repeatedly rebuild the database schema. The following setUp() method uses this class (complete with code comments) to perform these tasks:
<?php

class ModelTestCase extends PHPUnit_Framework_TestCase
{
  
  protected $em;
  
  public function setUp()
  {
    
    // Make sure we are using the test environment
    $application = new Zend_Application(
      'testing', 
      APPLICATION_PATH . '/configs/application.ini'
    );

    // Bootstrap the application
    $bootstrap = $application->bootstrap()->getBootstrap();

    // Retrieve the Doctrine 2 entity manager
    $this->em = $bootstrap->getResource('entityManager'); 
    
    // Instantiate the schema tool
    $tool = new DoctrineORMToolsSchemaTool($this->em);

    // Retrieve all of the mapping metadata
    $classes = $this->em->getMetadataFactory()->getAllMetadata();

    // Delete the existing test database schema
    $tool->dropSchema($classes);

    // Create the test database schema
    $tool->createSchema($classes);

    parent::setUp();
    
  }
  
  public function tearDown()
  {
    parent::tearDown();
  }
  
}
You might be wondering why nothing specific to the database destruction process is found in the tearDown() method. This is because it's easier to avoid additional work by essentially carrying out both steps in the setUp() method; you'll see that the dropSchema() method is used directly prior to executing the createSchema() method.
After implementing this revised setUp() method, go ahead and run your tests, and you'll see that the database will successfully regenerate with each test.

Using the PHPUnit Database Extension

While the above example works great for conveniently regenerating the database structure, chances are you're going to additionally want to initialize some test data. Unfortunately, the Doctrine 2 release is no longer packaged with the convenient YAML-related features available in the 1.X releases, meaning another solution must be devised. Fortunately, PHPUnit's DBUnit extension takes much of the pain out of devising your own custom solution.
The following code isn't ideal because in order to demonstrate the core feature of DBUnit I'll need to duplicate a bit of database connection credentials which would otherwise be retrieved directly from the application.ini file (see the article Introducing the Zend Framework's Application Configuration Component for more information about accessing configuration data), it should nonetheless provide you with a sound understanding of DBUnit's capabilities. In order to use DBUnit, you'll need to make a few minor but important changes to your project's base test class. The modified code is presented next. Notice in particular how the base class now extends PHPUnit_Extensions_Database_TestCase, and that two additional required methods have been defined, namely getConnection() and getDataSet(). The former method is responsible for establishing the database connection, while the latter is responsible for loading the data set into the database:
<?php

require_once "PHPUnit/Extensions/Database/DataSet/YamlDataSet.php";

class ModelTestCase extends PHPUnit_Extensions_Database_TestCase
{
  
  protected $em;

  public function getConnection()
  {
    $pdo = new PDO('mysql:dbname=test_gamenomad_com;host=127.0.0.1', 
                   'admin', 'secret');
    return $this->createDefaultDBConnection($pdo, 'test_gamenomad_com');
  }
  
  public function getDataSet()
  {
    return new PHPUnit_Extensions_Database_DataSet_YamlDataSet(
      dirname(__FILE__)."/fixtures.sql"
    );
  }
...
A simple such YAML file which might populate a table named platforms is presented next:
platforms:
  -
    id: 1
    name: "Xbox 360"
    abbreviation: "360"
  -
    id: 2
    name: "PlayStation 3"
    abbreviation: "ps3"
  -
    id: 3
    name: "Nintendo Wii"
    abbreviation: "wii"
Doctrine 2 developer Benjamin Eberlei released DoctrineExtensions, which includes a PHP Unit Extension for Doctrine. By the looks of it (see the documentation available via the previous link), it's possible to remove the redundant authentication credentials and instead refer to the Doctrine entity manager. Stay tuned as in an upcoming article I'll introduce the DoctrineExtensions project in some detail.

Where to From Here?

Of course, interacting with the database in the fashion described above is a pretty expensive process. In fact, when you're merely interested in testing the classes which interact with a data source, consider using test doubles (stubs and mocks), a feature I'll discuss in an upcoming installment.

About the Author

Jason Gilmore is founder of the publishing, training, and consulting firm WJGilmore.com. He is the author of several popular books "Easy PHP Websites with the Zend Framework", "Easy PayPal with PHP", and "Beginning PHP and MySQL, Fourth Edition". Follow him on Twitter at @wjgilmore.