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

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

This is an old question but I thought Id add some specific experience weve 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 wed 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.

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.

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

You might want to allow PHP to create and supply data to a temporary table/database and test on that table/database. Then you dont have to reset your database manually. Most frameworks have database manipulation libraries to make it easier. It might take time in the front end but will let you test much faster later when you make changes later.

Leave a Reply

Your email address will not be published.