5

I've put together a unit testing plugin for Symfony 1.4 that attempts to manage the database automatically, similarly to the way Django's test framework does it (destroy and rebuild the database between tests).

It makes sense to destroy and rebuild the database before the first test runs, as the schema could have changed during development, and it would be nothing short of horribly sadistic to make the developer keep his test database in sync with his models manually.

However, after the first test runs, I've found that it is usually faster just to delete all data, which would be a fairly simple task, except for the problem of foreign keys, which requires that the data be deleted in the correct order.

For MySQL, this isn't a problem; simply SET FOREIGN_KEY_CHECKS = 0, and you can destroy referential integrity to your heart's content (until it comes time to SET FOREIGN_KEY_CHECKS = 1, of course, but in this case, there would be no data left, so there's nothing for MySQL to complain about).

But this only works so long as the test database uses MySQL. For any other DBMS (esp. Sqlite), this will fail miserably.

Does Doctrine 1.2 provide a means for deleting all data in every table, or is there a way to "follow" relations (i.e., determine which tables have foreign keys and delete from them first)?

3 Answers3

2

I would use Doctrine DBAL and find the foreign keys first. Take a look into Doctrine's DBAL documentation about the Schema-Manager, its related API documentation as well as the methods provided in ForeignKeyConstraint.

EDIT: For doctrine 1.2 there seems to be a similar way to retrieve foreign keys documented here.

Find all foreign key constraints and based on the constraints let the code decide, in which order it's possible to delete the data.

kgilden
  • 10,336
  • 3
  • 50
  • 48
  • Thanks for the suggestion. This looks great, except that the plugin is for Symfony 1.4, which means Doctrine 1.2, not 2.0. Is there a method that's compatible with the previous release of Doctrine? –  Aug 14 '11 at 14:08
  • I figured you'd be able to just use the DBAL 2.0 component separately, but as I just read, it seems doctrine-1.2 has the dbal component inside it (which has the same feature btw). – kgilden Aug 14 '11 at 14:19
  • Unfortunately, it looks like `Doctrine_Import->listTableConstraints()` does not consider a foreign key to be a constraint; I tested it out on a couple of tables, and it correctly reported unique key constraints, but not foreign keys. –  Aug 14 '11 at 14:31
  • I looked in the docs one more time and it seems the correct method to call should be [listTableRelations()](http://www.doctrine-project.org/api/orm/1.2/doctrine/doctrine_import.html#listTableRelations%28%29). Fixed my answer as well, my bad. – kgilden Aug 14 '11 at 14:43
0

An alternative (although not exactly trivial) approach to take is to use transactions.

Start a transaction at the beginning of the test, and then issue a rollback after the test completes. The database will remove all the data created during the test.

Some care would have to be taken to make sure that tested code that actually relies on transactions continues to work, however.

This is the way that Django's test framework works:

class TestCase(TransactionTestCase):
    """
    Does basically the same as TransactionTestCase, but surrounds every test
    with a transaction, monkey-patches the real transaction management routines to
    do nothing, and rollsback the test transaction at the end of the test. You have
    to use TransactionTestCase, if you need transaction management inside a test.
    """
0

Doctrine_Data->purge() leverages Doctrine_Connection_UnitOfWork->buildFlushTree() to do exactly what I'm trying to accomplish.

Kind of makes sense, seeing as how Doctrine_Data_Import->doImport() (i.e., symfony doctrine:data-load) already provides the functionality I'm trying to reinvent... how did I miss that one? :P

  • Turns out, even this approach is not bullet-proof. I eventually ended up creating a custom adapter just for MySQL that disables foreign key checks before each purge operation. –  Aug 02 '12 at 18:11