Fixture dependences for foreign key constraints

Foreign key constraints often mean that inserting a fixture record into a test database for one model is not possible until records exist in other tables, which themselves may each have more dependencies, and so on. Is there any way to specify these dependencies in Cake’s TestFixtures?

Suppose I have model A that has foreign keys to models B, C and D. Model C has a foreign key to E, as does D to F and G. It would be great if I could specify those requirements once in the fixtures themselves. That way in any test in which I explicitly wanted to add a fixture for A, I could just add that fixture, and all of its dependencies would come along for the ride. Otherwise for every test case that involves A (either directly, or because I’m testing something else that has A as a dependency), I have to add the full properly ordered sequence [F, G, D, E, C, B, A] to whatever other fixtures are also needed for that test.

Is there any way in Cake to do what I’m talking about? Does this idea make as much sense as I think it does, or have I overlooked something about how I should be integration testing my controllers?

1 Like

If you have foreign key references, you have to load all tables involved, there is no way around this. Cake does not manage the fixture dependencies for you, but it loads the fixtures in a way that it is not required to load them in the right order.

Thank you for the response. I’m not sure I follow your second statement though. Since Cake has no knowledge of the fixtures’ dependencies, how can it know the correct order to load them in?

I tried a simple example on a test that only required two fixtures. If I specify them in the correct order in the $fixtures array of the test, then it passes. But if I switch the order, then PHPUnit just crashes with the message

Exception: Unable to insert fixtures for "App\Test\TestCase\Controller\MyControllerTest" test case. SQLSTATE[25P02]: In failed sql transaction: 7 ERROR: current transaction is aborted, commands ignored until end of transaction block in [vendor/cakephp/cakephp/src/TestSuite/Fixture/FixtureManager.php, line 267]

I haven’t found a way to get any more useful information about the SQL error out of Cake, since it wraps all the fixture operations in a transaction and I’m using Postgres. But it seems like a good bet that the problem here is that it tries to insert a record into the second table, before the foreign key entry it references has been inserted into the first table, because the order of the $fixtures array is incorrect.

I’m facing the same message/problem in a regular basis, as every time a test involving fixtures throws a fatal error that exception is thrown.

I think that if Cake truncates the tables in reverse order as the are in the $fixtures property, and then start to insert them from first to last, the issue will be solved. This would involve a small overhead, as all fixture management is done in a single loop and this change will need two.

I’m working on a small plugin to ease fixture management. If I manage to finish it, I will leave here a link or something.

As far as I can remember, cake creates all the tables first and then creates the constraints, this is is what in theory allows you to load the fixtures in any order. If it is not working for you, then it may be worth opening a ticket about it.

I work with a schema with all the tables and constraints already created, this may be causing the fixture error as dropping the constraints has no effect. Or maybe is something else, I’ll check it.

Also I’m editing my previous post, the first phrase was incomplete :sweat:

If you are not using the schema management features provided by the fixtures, then you’ll have to be very careful with how you order your tables in the $fixtures property. The fixture management system does not have a way to do dependency solving.

Can you elaborate on “using the schema management features provided by the fixtures”? Maybe just a link to docs relevant to what you’re describing. I want to make sure I am using those features but I’m not sure what they are. Thanks in advance!

The $fields property of the Fixture files is what I was referring to.