PHPUnit - test the validity of an SQL Query

Question

I'm in the process of testing a factory class. One of the methods must load the data in an array for an object that another method will instantiate.

That method contains the SQL query that holds a critical condition that must be tested. ( in this case only ask for the records that are "published". Ex.: WHERE published=1 ). That distinction in the SQL Query is the only detail that makes that method differ from another one, and I want to test the query execution behaviour.

Now, I can't really mock my PDO object and ask it to return a fixed result as I would not test the execution of the query by mySQL. That would make a useless test.

That leads me to think that I'll need to set up a static database with fixed test data inside it. Am I right on this or have I missed something?

Should I separate the test requiring the "test database" from the tests that are autonomous ?

Answer 1

Yes, it is the common practice.

It is also the special methods for loading fixtures: http://phpunit.de/manual/current/en/database.html

What about second question: no, you should not separate them. Your tests should test the behaviour, not the details of implementation. Join test cases into test classes by logic.

Answer 2

I strongly agree on the part of not mocking out the PDO. At some point i want to make sure my queries work against a real database. While this might not be a unit test anymore, technically. For me it offers so much more to know that my code that handles data storage really works against the db.

What i tend to do is creating a sort of Data Access class for each Class that needs to talk to the DB and so separating out most of the business logic from the db access code.

That way i can mock out the data access when testing the classes and after that set up a "Test Database" for each "data access class" and see if those work.

@zerkms Answer (+1) already linked http://phpunit.de/manual/current/en/database.html and the only other resource i have found to be of value when it comes to DB-Testing is the Book Real-World Solutions for Developing High-Quality PHP Frameworks and Applications that has a big chapter covering this subject.

---

Should I separate test requiring the "test database" from the tests that are autonomous ?

Only if your testsuite gets really really big and you have runtime issues that force you to say "hitting even a testing database just takes to long for all my tests so i run those only on a continuous integration server and not while developing.