Writing Database Tests
There's no magic when it comes to writing database tests, you write them just like you would any other type of test. A database test is typically a three-step process:
- Setup the test. Each individual unit test should be run against predefined test data. Furthermore, your database should be in a known state before running your database test suite. There are three common strategies for doing this. First, start fresh by dropping the existing schema then rebuilding it from scratch reloading the initial test data. This is a brute-force approach that is straightforward, safe, but a bit slow. Second, keep the existing schema but replace initial test data. This is a bit faster than the first approach because it avoids recreation of the schema, but runs the risk of data corruption if you don't ensure that all data is replaced. Third, restore the database from a backed up image.
- Run the test. You want to automate your tests so that you can run them whenever you need to. Modern approaches to development such as Extreme Programming (XP) and the Rational Unified Process (RUP) are predicated upon the concept that you will evolve your system over time to meet the changing needs of your stakeholders. If you want to ensure that your system works, the implication is that you need good regression testing tools. Unfortunately, database testing tools are few and far between, although some tools such as Quest Unit Tester for Oracle (www.unit-test.com), TSQLUnit for MS SQL Server (tsqlunit.sourceforge .net), and IBM Rational Functional Tester (www-306.ibm.com/software/awdtools/ tester/ functional/) are available.
- Check the results. Each individual test should determine whether it was successful and report the result back to you. When an error occurs, you need to be able to determine what happened by browsing the database or by doing a table dump from within your program.
A common debate amongst developers is where to obtain test data: Should you use production data or create your own test data? The answer is that you need both. For unit testing, I prefer to create sample data to ensure that I can predict the actual results for each test. For other forms of testing, particularly system integration testing and function testing, I will use production data so as to better simulate real-world conditions. For load/stress testing, I will use production data if it is available; otherwise, I will create the requisite test data. Tools such as DBUnit (www.dbunit.org) and DTM Data Generator (www.sqledit.com/dg/) are good options for creating test data.
There are several strategies for managing test data, each of which can be used alone or in combination:
- First, you can maintain an external definition of the test data, perhaps in flat files, XML files, or a secondary set of tables. This data would be loaded in from the external source as needed.
- Second, you could have test data creation scripts, written using data manipulation language (DML) SQL code or application source code (Java or C#, for instance), which does the necessary deletions, insertions, and/or updates required to create the test data.
- Third, you could have self-contained test cases that put the database into the known state required for the specific test.
A significant advantage of the second and third strategies is that it is much more likely that the developers of that code will place it under configuration management (CM) control. Although it is possible to put test data itself under CM control, worst case you generate an export file that you check in. This isn't a common practice and therefore may not occur as frequently as required. My advice is to choose an approach that reflects the culture of your organization.
