PHPUnit DBUnit for integration testing a class that queries a MySQL stored procedure

Question

I have created a YAML dump of my database, which has test data in it. I want to be able to load that data into an empty test database, at the start of each test using the getDataSet() and getConnection() methods.

The database has quite a few views, which are exported as tables into YAML. This is not too much of a problem. The problem is that one of the classes being tested queries a MySQL stored procedure, which is obviously not dumped into the YAML fixture.

Ideally, I would like to specify an empty test database, and have the database truncated and populated at the start of each test, using the YAML file.

This does not seem possible.

Is there an alternative way, perhaps using an SQL dump to create the fixture?

Answer 1

There's really only two ways to effectively test code that uses stored procedures and triggers.

The Lesser Preferred: Create and use a test database.

The More Preferred: Put all your queries that rely on sprocs and triggers into standalone functions. Then mock those functions out in your unit tests.