PHPUnit: how can I remove test data from my database after the tests are complete?

Question

I have a Zend Framework application (version 1.11) that uses Doctrine 2. I've got PHPUnit set up to run tests on my models and forms and whatnot. The tests work great, but there's one problem: they leave the test data in the database once they are done. Here's a basic sample of one of my tests:

class VisaTypeEntityTest extends ModelTestCase
{
    public function testCanSaveAndRetrieveVisaType()
    {
        $addVisaType = new \Entities\VisaTypes();
        $addVisaType->setEnglishName('Test Visa Type')
            ->setJapaneseName('試し')
            ->setKanaName('タメシ')
            ->setDescription('Description of the test visa type');

        $this->em->persist($addVisaType);
        $this->em->flush();

        $getVisaType = $this->em->getRepository('\Entities\VisaTypes')
            ->findOneByEnglishName('Test Visa Type');

        $this->assertEquals('Test Visa Type', $getVisaType->getEnglishName());
    }
}

Obviously the data has to actually be entered into the database in order to make sure everything is kosher. But I don't want all the test data gumming up the database every time I run a test, nor do I want to go and manually remove it.

Is there something I can do, such as using the tearDown() method to get rid of the test data once the test is complete? And if so, is it possible to "roll back" the auto increments in the tables' id fields to what they were beforehand? I know it really shouldn't matter if there are gaps between ids, but if there is some way to get Doctrine to reset the auto increment value that would be great.

Answer 1

1. Regarding "cleaning up":

Because you are using InnoDB you can use transactions to restore the DB to the same state as it was before the test started:

So in setUp() you would add $this->em->getConnection()->beginTransaction(); and in tearDown() $this->em->getConnection()->rollback(); This restores the database to the previous state. Also have a look at MySQL handbook on "The InnoDB Transaction Model and Locking" to make sure that this does not interfere with any other data in your application (keyword: isolation level).

2. Regarding rolling back auto increment ids:

As far as I know this is not (easily) possible. There is a thread about it on SO in which it is stated that your application should not care if there are gaps between the auto increment ids. If you are using a test database (which is highly recommended) this should be even a lesser concern.