Going the Distance

The customer encountered a problem while testing our application suite. We had just migrated to a new set of tools, and a new version of the database on the back end. It took a while for me to get around to researching the problem. Luckily I was able to duplicate the problem in a development environment. I tracked the source of the problem down to some bad test data. Then I checked the database where the customer performed their tests. They had the same problem there. The fix was to write a script to correct the data.

At first when I ran the script to correct the problem, it just bombed. It logged the problems it encountered. So I manually executed each of the statements in the script until I found what the problem was. That’s when I was done with the coding. It was time to test it out. Luckily I had a lot of data that was corrupt in my development environment. I ran the script and it fixed about 5000 records. I used a tool to view the corrected data. In general it looked pretty good. I was tempted to just ship the fix at this point since I had 9 other problem to research and resolve.

Instead I decided to take my unit tests a step further. I figured it would be easiest to write another script to verify all the records were updated correctly. It took about an hour to write this unit test script. That was not too much overhead considering the time it take to investigate and code a solution to the problem. I did find some exceptional scenarios in the script results when I churned the data with a script. I just checked all 5000 outputs records for correctness. At first I just output the number of problems the test script found. Then I actually wrote the problems out to a log for further analysis.

Here in development we always seem to be in a rush because we are understaffed and overworked. But here is the funny thing. If I sent out a script that did not fully work, I would waste a whole lot more time dealing with the repercussions. Why not spend a little more time and get it right the first time? I love to code. Writing a script to verify the unit test results is just a little more coding. So it is in fact fun work. Now just don’t tell the management staff that. Otherwise they will get the wrong idea.

Testing Database Code

Last night I read a blog entry entitled “How to Unit-Test Code That Interacts With a Database”. It stated that such testing is hard. But you still want to fully test the database interaction. The blog recommended that you connect to a separate database to do your unit testing. Each developer should ideally have their own database. In addition, the database should be reset to the state it started in prior to the unit test. The best database for testing is a small focused data set. Above all the unit test activity should not connect to the production database.

On our project we conduct unit testing on new code that we write, as well as on fixes that we produce. Our main test data set is a small but interesting combination of test cases for verification. In general most of our developers have their own schema for testing. This allows you to do whatever you want without impacting other developers. It also helps in that nobody else makes changes to the data in your database. In the past I have written elaborate unit test scripts which generate delete old test data, generate new test data, execute the tests, and report on the results. This is normally done when I write code that relies heavily on database interaction.

One of the best parts of the blog post I read was the large number of comments from readers. I will share some of them here for your benefit. Multiple people pointed out that the type of testing discussed here is integration testing rather than unit testing. Unit tests are concerned with logic or combination checks. Oracle users recommended the utPLSQL framework for Oracle. You can use the ut_setup and ut_teardown functions to take care of unit test data.

Some readers commented that you could treat the database changes as one big transaction, then roll it back after testing is done. This would definitely not work for my system. We commit transactions all over the place. We even have autonomous transactions which commit themselves without interacting with any outer transactions. You can do better than getting your own schema for testing. You can get a whole database installation of your own to conduct unit tests in. Express versions of Oracle and Microsoft SQL Server are free.

It appears that unit testing is hot in the blogosphere. This seems strange to me. But I am happy about it. Nobody seems to like unit testing where I work. Most developers seem to want to code, check it in, and ship it. I know I myself could use a more rigorous unit test habit.