Slashdot Mirror
Strategies for Test Databases?
youngcfan asks: "I've been tasked with finding strategies for a test database that can be used effectively by both software developers and the QA team. We're a J2EE shop with most of the interesting pieces of the application interacting heavily with the database -- so we need to test it. We're ramping up on JUnit, but are looking for ways to test the database-driven pieces of code. Since QA needs the same database for functional testing separate from developers' unit tests, DBUnit doesn't seem to suffice. We also have the challenge of working on multiple releases at the same time, which only complicates how and when to add new data to the test database in a way that's useful and valid for everyone. We're looking for strategies for using a test database in a way that meets both the QA's and the developers' needs, works for multiple releases, and isn't a heavy burden to maintain given that the schema and code can change anytime before any of the multiple upcoming releases. Any suggestions?"
Oracle, Sybase and MySQL can all be used as test databases.
Perhaps you really want to know how to test code that uses databases, which is a different question
There are many refactorings that can be done to reduce your dependency on a particular database install...but thats a rather large topic. I'm available for consultancy, post here and I can get in touch...
Some things you might like to consider
Test data sucks: there are too many real-world situations the developers fail to think of.
We're a pretty small shop, but here's what we do: The production server backup is loaded to the test server daily. Every developer maintains a set of scripts which make any needed databae structure modifications after the backup has loaded. All development and QA testing is done against this test database. Where the production data isn't stable enough for unit testing we force-feed a few specific rows (as few as possible). This gives us fresh, real-world data for development and testing, and when an application rolls out, the exact same set of modification scripts are usually run on the production server (i.e. the modification scripts have been indirectly but repeatedly tested themselves).
SMQ 90AE4B2BC4F6BEAF7340F0B40BA2DEF7340F6BC2D0392
I also second the idea that developers and QA's normally should all have their own database running on separate servers.
Ideally, the developers and QA run against a smaller database that is (ideally) populated from scratch with a small dataset to speed development; and then for release testing use a much larger populated database or (if that's too difficult) a copy of the production database that has been appropriately scrubbed to get rid of confidential data.
The database offerings from the various major vendors allow you to "quiesce" the database which suspends new transactions, completes all pending transactions, and then ensure that all data and log are flushed to disk. Then, with the production system paused, take a hot point-in-time snapshot of the filesystem, effectively giving you a compelte database dump in a few seconds. (This requires a storage system that allows you to make snapshots -- NetApp's do this, for example.) Resume the database to let the production system continue, and then copy the snapshot of database files to another server and reconstruct a clone of the database.
Run the appropriate trimming/cleansing/schema update on the clone database, and then make a snapshot of THAT. You can then revert the database to a knowing starting point as you like. If your development requires schema changes, don't let developers make the schema changes directly -- insteead, insist on schema change DDL's to be scripted, and reapply the script to the snapshot at each refresh.
When doing the final release testing, get the latest snapshot of the production database, run the update scripts, and run the tests. If everything looks good, make another snapshot of the production database, and apply the updates to the production database.
Done right, you can always roll back the test
http://sqlunit.sourceforge.net/ is based on JUnit and it specifically designed to test databases and result sets. It is what I use when building automated test streams. Supports many databases on fresh download and can be extended easily if required.