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. :)
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.
Testing against a database usually indicates bad tests, probably due to lack of encapsulation in the code under test. You should try to isolate the code that interacts with the database from the rest of your code as much as possible, keeping this interaction layer so simple that you can get away with a few, very basic tests.
In other words; The code that deals with comments, shouldn't be the same code that deals with database interaction. You could - for example - write a generic table module, that your comment model uses to access the database. You would still have to test the table module, but that should be done in isolation from the comment code.
When testing database code, it's good to always have the same database as the starting point. Especially if you do unit-testing (which I assume is the case here). One of the ways is to truncate all tables as Jason suggested, but I prefer to have some starting data in it. You know, you always need to have some 'default' data that is present in each database.
Also, some tests only make sense with full database. So, create a special instance of database for those tests. I have about 3 or 4 different databases that I plug-in (just copy the files in) before running some tests. Having the same starting point each time ensures repeatability.
So, just prepare a few database states that are good 'starting points' and back them up. Before running each set of tests, restore appropriate database, and then run it.
You might want to allow PHP to create and supply data to a temporary table/database and test on that table/database. Then you don't 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.
I'd encourage you to not try to test the database access code using SimpleTest.
Instead, create a functional test for your app using, for example, Selenium: record a test case when you start from a known state of a database; then add a comment and check (using Selenium's asserts) that the content indeed is there.
This way it is: - easier to set up and maintain - you verify not just the DB layer, but the presentation layer, too
That said, if you have stored procedures in your DB, do use SimpleTest - I've done it myself successfully. Basically, create SimpleTests that start from a known DB state, then perform a few INSERTS/UPDATES, then run the stored proc and make sure the state of the DB is what you'd expect.
If you really want to test against a database, I would recommend to import data/create tables before each test. That way, your database starts from a known state on each test. Since this is rather performance-expensive, you can start a transaction (provided that your rdms supports it) in setUp and rollback in tearDown. MySql (Which is likely the RDBMS you're using), doesn't support nested transactions, so if the code under test uses transactions, you can run into trouble. You can get around this, using savepoints. Set up a savepoint before testing, and rollback to savepoint after test.
I'll still maintain that if you need much of this, you should consider the possibility that your tests are trying to tell you something ..
This is similar to a question I asked a while back:
and I got some good answers.