CODEWITHSUNDEEP
phpdoctrineforeign-keysdoctrine-1.2
user212218
user212218

Reputation:

How to delete all data without breaking referential integrity using Doctrine?

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)?

Upvotes: 5

Views: 1191

Answers (3)

user212218
user212218

Reputation:

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.
    """

Upvotes: 0

user212218
user212218

Reputation:

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

Upvotes: 0

kgilden
kgilden

Reputation: 10356

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.

Upvotes: 2

Related Questions