How do you set up database testing using the PHP SimpleTest framework

Question

I am using SimpleTest, a PHP-based unit testing framework. I am testing new code that will handle storing and retrieving website comments from a database. I am at a loss for how to structure the project to test the database access code.

I am looking for any suggestions as to best practices for testing db code in a PHP application. Examples are really great. Sites for further reading are great.

Thank you kindly. :)

Answer 1

This is an old question but I thought I'd add some specific experience we've had with this.

Other posters are technically correct that this is a form of integration test but from where I sit there is often too much logic in MySQL to be stubbed out in unit testing. If you are like us and have large, complex services that are heavily dependent on MySQL (and often several tables per service), having a robust testing framework that includes testing query logic is really handy. We mock out a good number of our dependencies in our unit tests but not MySQL.

We have a set of classes that wrap simpletest to provide this functionality. It works something like this:

• Instructions to create each database table are stored in a file at tests/etc/schemas/table.sql. It contains the schema data as well as inserts for all the canned data the test will expect to find.
• Each test that requires the database extends a Test_DbCase class which provides functionality to build the tables.
• A bootstrap class takes care of creating and dropping the database on construct and destruct.
• At runtime, the test calls loadTables('foo', 'bar') in the setUp method to execute the sql commands in foo.sql and bar.sql.
• Tests are run against the canned data..the rest is obvious.

One other tool we have is a bash script that makes it easier to create the table.sql files. This is really handy because otherwise we'd be writing the SQL by hand - you can take an existing set of tables, set up all your data in MySQL, and then export it to create the test files basically.

This works really well for us, though we ended up having to roll a lot of it ourselves.

Answer 2

I had a local database dedicated to unit testing with a known name and database username/password. The unit tests were hard-coded to that location but different developers could override those variables if they wanted.

Then before each test you TRUNCATE each table. This is much faster than dropping/creating tables or the database itself.

Note: Do not truncate after the tests! That way if a test fails you have the current state of the database which often helps diagnose the problem.