Unit Testing Dababase Applications

Question

I am trying to get in to the TDD realm, and I'm having a hard time unit testing a few user models I have. I'm trying to test the validation of my models, and the business requirements are as follows:

1. >= 6 character username required
2. >= 5 character password, with at least 1 letter and number
3. valid email format required
4. ... blah blah blah
5. username and email cannot already exist in the DB

All requirements are easily testable, except 5, which requires the database to be in a known state. I know that with PHPUnit I can set up my database to be in a known state using XML files, but is there a better way?

What I want is for my database to revert back to the state it was in prior to running tests (i.e., during development). I suppose I could use MySQL transactions to rollback the changes, or I suppose I could also use two separate databases, one for development and one for testing.

I also read somewhere to not use actual DB connections in unit tests, and instead use mock data. Not quite sure how that works.

Can someone explain to me the different options I have, and which are the best routes?

Thanks

Edit:

I think I'm going to go with the Ruby on Rails approach and just set up 3 separate databases, production, development, and testing. Unless someone has a strong objection.

Answer 1

Some people say don't test on a database, others like me say its the only way you'll know if your DAO code is good.

What you need is two database. One for testing and one for development. Best practice is to have a common superclass for your unit tests that sets up the database (perhaps inits a fresh schema) and then all your DB unit tests run in that database. You can clean it up after.

For the test you mentioned (username and email cannot already exist in the DB) then do something like this (psuedocode).

Create user with username that you know doesn't exist (say username-largerandomuuid)
Insert user.
Assert user was created

Insert same user(name)
Assert error is thrown.