Why I designed Quest Code Tester
I designed and (with a massive infusion of resources from Quest Software) built Quest Code Tester to solve a big problem: I wasn't testing my PL/SQL code enough. I would do some ad hoc testing, sure, but never enough and never thoroughly enough. I didn't really have any idea if my programs (whether small demonstration utilities or "real" application code) actually worked according to specs.
And from interaction with thousands of other PL/SQL developers, through my presentations and trainings, I had the distinct impression that most everyone else was in the same situation. Sure, we all wanted to test our code better. We didn't want bugs to pop up in our code, causing embarrassment, as well as additional expense and delays in our projects.
Yet it was (and is) very difficult for me to do the testing that I knew should be done for at least two main reasons:
1. I wasn't sure how to write the test code. How should I, could I, generally write a program to test another program? How can I verify the results? Just write text to the screen with DBMS_OUTPUT and then sit there staring at it, until I was convinced I knew that the test succeeded or failed? And how do I test things like "Did the program insert the right data? Did it update the correct rows, correctly?"
2. Even if I knew what to write, I just didn't have the time to write it. Check out the literature on unit testing (the testing of individual units or programs). It is commonly accepted that for every 10 lines of code that I write, I will need to write ten times that amount (100 lines) or more of testing code. Well, that's just plain crazy! I barely have time to write the actual program. How I am going to find to write (and maintain) an order of magnitude more logic?
So, big challenges – and not just for me, and not just in the world of PL/SQL. The above concerns generally apply to any programming language (through database programming does have that extra complexity of testing changes to underlying tables).
Not surprisingly, then, people with bigger brains than me have been tackling this problem over the years. One major result of this hard thinking is known as the "XUnit" family of unit testing frameworks. The "X" comes from Extreme Programming, a lightweight methodology that takes widely accepted and agreed upon ideas in programming (such as "It's good to test your code.") to extremes ("Everyone tests all their code, all the time.").
These frameworks offer a "cooperative paradigm". This means that if you cooperate with the framework by writing your test code using the defined API to check your results, then the framework will automatically run your tests and report the results to you. That's nice stuff, and a big advance over writing everything yourself and manually verifying results (very slow and error prone).
JUnit is probably the best known "XUnit" framework, used by multitudes of Java programmers. .Net has NUnit. And PL/SQL? It has utPLSQL.
I wrote the original utPLSQL framework back in 2000 (I somehow couldn't bring myself to called it PUnit or PSUnit – but another developer has done something like that, creating another "XUnit" framework for PL/SQL called PL/Unit). utPLSQL is used by at least hundreds of developers around the world. For more information about utPLSQL, check out the SourceForge project page at http://utplsql.sourceforge.net/.
utPLSQL is a major step above writing your own test code. The main drawback with utPLSQL, however, is that you do have to write (and maintain) the test code yourself, and that can be a mighty big task. I have talked to several utPLSQL users who have assembled literally tens of thousands of lines of utPLSQL test package code.
Yet I didn't use utPLSQL myself. I didn't (and don't) have the discipline to make the time to write all that test code. So while I am very happy (and, yes I admit it, proud) that I wrote utPLSQL and that is been of assistance to many PL/SQL developers, it wasn't doing much for me. It also wasn't having broad impact within the PL/SQL world. Relatively few developers know about it, much less use it.
I could have let things go at that, say that I had done my best. But I was pretty sure I hadn't. Plus, I have had this growing feeling over the years that the lack of testing in the world of PL/SQL is one of our biggest and most critical weaknesses. I believe this is particularly true with PL/SQL, because PL/SQL is a lot like Cobol: tens of thousands (maybe even millions?) of mission critical applications are built in PL/SQL and are running companies around the world. They will still be doing that ten and twenty years from now. If these applications don't have strong regression tests in place, it is almost impossible to maintain them with any degree of confidence.
So I found that I couldn't rest at utPLSQL. I wondered if it was possible to do something better, something that would be easier to use and require less time. I dreamed of a tool that would let me just "tell" it the tests that I want to run, and have it do all the work for me.
Well, after dreaming about that for a while, I decided to try to build such a tool. And the result is Quest Code Tester for Oracle.
A Quick Overview of Quest Code Tester
The concept behind Quest Code Tester is simple:
Rather than write test code, you describe the tests that you need to have run on your program. Then anytime you need to run your tests, you simply press a button. Quest Code Tester then runs the tests, records the results in its repository and displays the results visually to you.
Monitor the status of your code and test it as necessary.
The fundamental concept of Quest Code Tester is similar to that of SQL. I started with Oracle in 1987 (in presales!) and back then what we demoed was the SQL language via SQL*Plus scripts (my, how far we've come). The message and power of SQL was simply:
Don't write a program to retrieve or change your data. Just describe through SQL syntax the set of data you want (and/or how you want to change it) and the underlying SQL engine will do the work for you.
SQL in and of itself wasn't enough; that's why we have PL/SQL. Yet that switch from writing programs to describing needs lies at the core of the relational database revolution.
That same power comes to the fore in Quest Code Tester for Oracle. With the usual "hand-coded" test programs, you have to get your hands very "dirty," implementing every detail of your tests. And while it is true that utPLSQL takes some of the burden off your shoulders, you are still left to writing the test package, and embedding all your test case definitions directly in code. This makes it difficult to document the tests you have written, and it also means that you have to spend lots of time maintaining that test code.
With Quest Code Tester we take a completely different approach. You point to the program that you want to test (either a packaged subprogram or schema level procedure/function). We then parse the program header and present you with Test Builder, an intuitive graphical interface through which you define your test cases, all the way down to the expected results once your program has run.
Describe a test – provide inputs and the outcomes you want to test.
Quest Code Tester will then generate the necessary code to run your tests. Our test code is nothing more nor less than a PL/SQL package, so you can look at the code, see what we are doing, make sure you are comfortable with the test logic.
A few point and clicks, and voila – 690 lines of fully functional test code.
You can now run your tests at any time by pressing the Run Test button, shortcut key or choosing Run Test from the right click menu.
Of course, you will almost certainly need to change your test definition, adding new test cases, fixing mistakes in your original test specification. No problem! Now, instead of diving into the code, you can simply bring up the Test Editor for that program and make the changes you need at a logical level (test description) rather than the "physical level" (actual test code).
Of course, there is much, much more to Quest Code Tester than I can share with you within my blog. To help you get up to speed on the tool, we offer an on-line community at http://unittest.inside.quest.com/index.jspa that includes discussion forums and a video library.
One thing to keep in mind
The ultimate objective of Quest Code Tester is that you, the programmer, will not write any test code. You stay at the "logical level" and leave it to the tool to do all the "heavy lifting" of generating (and thus also maintaining) the test code itself.
Now, that's the ultimate objective. In the 1.5 release, we will generate 100% of your test code for many common scenarios, but you may still on occasion (for example, when testing the contents of collections) need to write some setup logic or even your own custom assertion code. The tool offers many customization points within the graphical interface, so that you can supply your own code and have it generated into the test package.
OK, in my next blog I will like to share with you some of the most important enhancements to Quest Code Tester 1.2.5.5, which will be in the 1.5 release.