How Would One Find a Dependency-Order list of Tables to Delete for Test Data?

Question

I have a test suite that needs to delete any existing data in my local MySQL test instance for a large number (but not all) of the tables in the system.

 delete from customer;

Of course, customer has quite a few foreign keys to it, so I also have to delete a few more tables...

 delete from customer_email;
 delete from customer_phone;

And again, these tables have more foreign keys...

 delete from customer_email_notification;
 delete from customer_phone_call;

etc etc ad nauseum. It's frustrating to have to mentally navigate the entire data model in order to write the test setup.

I am using Hibernate so I was wondering if there was some way to automatically get a priority-order list of tables such that it can be passed directly to a deletion handler. Ideally:

 List<String> dependentTables = foreignKeyGraph.findAll("customer");
 deletionHandler.clean(dependentTables);

Is there a Hibernate utility out there that can do this? I was going to write something that traversed our domain's annotations and derived table names that way, but it seems like a lot of work for a fairly common problem. Thanks!

Answer 1

A good way to handle this is to use DELETE triggers on each table. On the CUSTOMER table there would be a trigger to delete all the related data found by following each foreign key that relates back to CUSTOMER for the given CUSTOMER_ID - likewise for each table related to CUSTOMER - and each table related to those tables, etc. So for example, if the CUSTOMER_EMAIL table relates back to CUSTOMER via CUSTOMER_ID, and likewise CUSTOMER_PHONE, then the delete trigger on CUSTOMER might look like

CREATE TRIGGER CUSTOMER_AD  -- AD = After Delete
  AFTER DELETE ON CUSTOMER
  FOR EACH ROW
BEGIN
  DELETE FROM CUSTOMER_EMAIL E WHERE E.CUSTOMER_ID = :NEW.CUSTOMER_ID;
  DELETE FROM CUSTOMER_PHONE P WHERE P.CUSTOMER_ID = :NEW.CUSTOMER_ID;
END CUSTOMER_AD;

Share and enjoy.