Database testing – Unit Testing
July 2, 2007 5 Comments
One of my unresolved concerns about unit testing is that there is a gulf between where unit testing has come from and the best way to write performant database code. How well does the tradition unit testing and TDD approach sit with set-based operations in code.
What I am going on about?
Well, it seems to be that unit testing sits very nicely with refactored code, where little routines can be packaged into a function with a defined responsibility that can be reused by many other components.
Where this gives me a problem, is that this encourages you to write SQL with lots of row-by-row functional lookups which are tried and tested, trusted and known to work. But this can cripple your SQL performance.
The flipside can require you to repeatedly write the same lookups inline in various bits of SQL. Better performing but you’re having to test many of the same things for each piece of SQL. Needs some thought that one.
For now, for me, performance wins every time.
There are a number of tools out there to help database testing of code modules, many of which have benefited from Steven Feuerstein’s involvement. Some of the options are GUI led, some require knowledge of Java or some other language.
The core technology is utPLSQL – “developed by Steven Feuerstein … it allows the automated testing of packages, functions and procedures.”
Extending utPLSQL, we have Ounit. “Ounit offers a basic graphical interface to utPLSQL”. Ounit was designed by Steven Feuerstein who has gone on to move the concept on further with what is now known as Quest Code Tester for Oracle, which is now a commercial product from Quest but which also offers trial and freeware versions.
Another tool out there is DBunit– ” JUnit extension targeted for database-driven projects that, among other things, puts your database into a known state between test runs”.
Also, another option that has been highlighted to me recently is DBFit, a version 0.1 utility.
In these early stages of my understanding, I’m not convinced that any one of these options ticks all the boxes. It sounds like utPLSQL is going to be involved. A GUI front end might be involved but it needs to be integrated with other unit testing and build results – so it might be as simple as collating the results and sending them in some format to a web page somewhere.
Definitely, a significant feature of any solution should be some sort of integration with build technologies such as Ant, Maven and Cruise Control – we want to automate testing as much as possible, after all. But if the underlying technology for the tests is PLSQL then calling that is not going to be much of a problem.
Also, not having played with these yet, I have questions over whether these tend to best suited to just testing procedures which do calculations, etc on inputs rather than results sets from complex SQL which may or may not be joining the right tables and returning the right values. And how well can we adapt them to test batch jobs.
I expect that we can adapt some of them pretty well to do most things that we want. I’m going to have a decent play with all of them and I wouldn’t be surprised if out of the big mix came a little bit of utPLSQL along with some DBUNIT to put up and tear down the data required.