By Steven Feuerstein
OTN Member since 2001

Save time by setting up a test plan before you write a speck of code

Installment 2 of Feuerstein's "Building a Code-Analysis Utility" series

I furthermore identified a data source (the ALL_ARGUMENTS data dictionary view) and code (the DBMS_DESCRIBE package) to help me build my utility. What's next?

To be honest, my natural inclination is to open up my favorite integrated development environment (IDE) and start writing code fast and furiously, caught up in the excitement of the act of creating. I want to think things through as I go, tackle challenges incrementally, get something working, and then fine-tune it.

There are positive aspects to such an approach (you certainly don't run the danger of over- designing), but there are also many drawbacks. For one thing, I won't end up just fine-tuning my code if I build it through the Headlong Rush Construction non-Linear System, known affectionately as HRCLS or Hercules (a very lightweight methodology). No, I'll end up rewriting the whole thing—once, twice, three times, as I narrow in on the end goal, the target, the real thing. And although it is exciting to see my utility take shape, evolve, and transform under my very fingertips, I'll waste an awful lot of time.

For OverloadCheck, I'm going to resist my initial temptations. Rather than write code within the first 60 seconds of beginning my project, I'm going to make a simple declaration: I pledge to make my code accountable to a comprehensive test plan.

What does that have to do with avoiding the Hercules syndrome? Let's consider what such a pledge implies:

• I will produce a test plan, a thorough one with elaborations of dozens of (maybe even as many as 100) test cases, including the numbers of parameters, various datatype combinations, and the presence or absence of default values.
• I will design, build, and run tests that determine whether or not my code satisfies the test plan: tests that, in other words, cover all of my test cases.

Gosh, that sounds awfully reasonable, doesn't it? I mean, who wouldn't do all those things all the time? And yet, we all know the truth: Very few of us actually take the time or have the necessary tools at hand to do comprehensive testing. I believe, in fact, that following through on the above pledge would mean a radical departure for at least 90 percent of all software developers and development teams in the world (including myself).

In my case, I found that this firm commitment to testing, declared at the start of my development , improved my implementation of OverloadCheck.

Defining Scope, Making Assumptions

Before developing a test strategy and constructing tests, I need to decide on the scope of my project. The user community usually sets (and frequently changes) the scope of a project. In the case of OverloadCheck and many of the other utilities I produce, you, the developer, are my user—but it's still up to me to decide the scope, using your needs as my guide. My objective is to produce a body of code that immediately helps developers improve code quality, but also serves as a stable and extensible foundation that developers can add to for their own needs. It has to handle enough real- world complexity to be useful, but not so much that developing OverloadCheck turns into a full-time job.

When I take a close look at the contents of ALL_ARGUMENTS, I'm struck by just how complicated those parameter lists can get. In the world of Oracle9i, for example, an argument can have as its type an associative array (formerly known as an index by table) of records, in which a field of the record is another associative array, and so on. To be quite honest, I really don't want to have to write the code to handle the full range of possibilities of such arguments.

Instead, OverloadCheck will examine and perform analysis based only on the "level 0"arguments: those entries in the ALL_ARGUMENTS view that correspond to the parameter list as it appears in the program header. Actually, this may be all you ever need in order to analyze overloadings, but the full details will probably come in handy in some other kind of code check. (At the time of this writing, I have identified one situation that requires more information: If you define an argument as a record of the form <table_or_cursor>%ROWTYPE, then neither ALL_ARGUMENTS nor DBMS_DESCRIBE offer that type information. You would have to compare the datatypes of the individual fields of those row types—level 1 and perhaps more—to identify ambiguity. Yikes.)

Defining a Testing Strategy

The next step in developing OverloadCheck is to explore and define a testing strategy. As I mentioned , my commitment to writing code that will be accountable to a test plan affects development. For example, my first impulse when writing a utility such as OverloadCheck is to produce a program that accepts a package name and displays on screen the results of the code check. To determine if the utility works properly, I would need to look at the results of a check on screen and analyze them. How does that sound? Familiar? Scalable? Heck, that approach usually doesn't even work for very simple programs.

The OverloadCheck test plan will undoubtedly have many test cases, and the results won't be obvious. In other words, unless I memorize what success means for all these test cases, I'd have to rely on manual, visual verification (look at this window; now compare it with the contents of that window). It would take me an awfully long time to complete a test (a fairly crucial element of accountability), so I would run my tests infrequently—if ever. That constitutes reneging on my pledge.

I need to find a way to test more quickly and easily. With Java, many developers turn to Junit. PL/SQL developers take advantage of utPLSQL, an open source unit-testing framework for PL/SQL developers. (Disclosure: I am the creator of utPLSQL, although other people now also help with implementation and documentation.)

I'm not going to take much space in this article to describe in detail the genesis, theoretical underpinnings, or basic workings of utPLSQL. If you'd like more detail than this article provides, please visit http://utplsql.sourceforge.net/ or http://utplsql.oracledeveloper.nl.

A Quick Introduction to utPLSQL

utPLSQL is a testing framework (a collection of code and processes for using that code) for PL/SQL programs. With utPLSQL, you can construct a unit-test package that contains unit-test procedures and design this package in conformity with utPLSQL naming conventions and testing mechanisms. You then simply direct utPLSQL to test your program or package. It runs all of your tests and automatically detects whether the test succeeded or failed. It pinpoints the test case that failed, enabling you to more quickly confirm that the test case is right and identify the cause of failure in your application.

This framework is based on the unit-testing concepts of Extreme Programming ( www.xprogramming.com) and Junit, the Java unit-testing framework. Here are a few of the fundamental principles of this approach to unit testing:

• Write unit tests before you write your code.
• Write and change a little, test a lot.
• Automate execution and reporting on tests: the red light-green light approach.

Figure 1 offers a simplified view of the architecture of utPLSQL, a round-trip journey that automates your testing in powerful ways. Here's an explanation of the stops in this journey:

1. Call the utPLSQL.test procedure to execute your test package. utPLSQL relies on dynamic PL/SQL and naming conventions to run any setup code, locate and execute your unit-test programs, and perform necessary cleanup (the "teardown" step).
2. Your unit-test procedure makes calls to the "Assertion API," which compares test results against "control" conditions.
3. The assertion programs write the outcome (pass or fail) to the underlying results table.
4. utPLSQL.test reads the contents of the results table to determine the status of the test.
5. utPLSQL reports on the results, either to the screen via DBMS_OUTPUT or to a file with UTL_FILE.

Figure 1: Simplified view of utPLSQL architecture round trip.ppt.

Let's look at a very simple example that will give you a sense of the contents of a unit-test package. Suppose I've created an encapsulation over SUBSTR that lets me request a sub-string between start and end locations—a simple function (Listing 1).

Even a simple or seemingly trivial program such as betwnStr requires testing—and there are, in fact, numerous test cases I must consider (including NULL start value, NULL end value, and start greater than end). Listing 2 shows a portion of the test package (see ut_betwnstr.pkb for the full package contents). For an explanation of key sections, see Table 1.

Maybe you're thinking, "How tedious! Do I really have to write all that code just to test this simple function?" In fact, those who work in the testing arena acknowledge that the volume of code you need to write to test an application often exceeds that of the application itself. As for this particular package and the utPLSQL-style of testing code, you can in some instances generate all of your test code. In fact, the ut_betwnstr package was generated through a call to the utGen.testpkg_from_string procedure, as shown in ). Listing 3.

Even if you don't generate the test package, you can often find other ways to run lots of utPLSQL-based tests with a minimum of code—which is precisely what I did with OverloadCheck.

Applying utPLSQL to OverloadCheck

To take advantage of utPLSQL, I need to build a unit-test package and call utAssert programs to determine whether or not my code passes its tests. Before I do any of that, however, I need to elaborate my test cases. Remember: first the test cases, then the code.

Time to brainstorm, to sit back and think about the utility I'm providing. What do I want it to be able to verify? What are the specific examples of packages that compile but contain ambiguous overloadings and are situations I can detect? What are examples of valid overloadings? I need, after all, to test the positive and the negative. After a few moments, I come up with the following:

Valid Overloadings

• Two overloaded programs with a different number of non-defaulted parameters.
• Two overloaded programs with the same number of non-defaulted parameters and sufficient differences in datatypes (such as NUMBER versus DATE).
• A function and procedure with the same name and parameter list, including no parameters. There is never any problem in distinguishing between these two, because you use them differently in the code.

Invalid Overloadings

• Two overloaded programs with a different number of non-defaulted parameters, leading to ambiguous argument lists.
• Two programs with a single parameter, each of the same datatype but different parameter names.
• Two programs, each with the same name, with a single parameter but different datatypes in the same family.
• One program with no arguments, the second with one argument with a default value.
• One program with N arguments, the second with N+1 arguments, all with default values.
• One program with N arguments, with N-1 of them defaulted; a second program with N+1 arguments and the last two defaulted.
• One program with one argument, the second with two arguments, the second with a default value.

I'm sure I'll think of other test cases, but this is enough to proceed. How can I best go about testing the above scenarios? I need to define these various combinations in a package. Let's call it allargs_test. It would probably work pretty well to use a different overloaded program name for each of my test cases. That will keep things distinct and well-organized. In fact, I'll create a table (see Table 2).

This table is helpful but not yet complete. I also need to specify for each of these test cases what I expect the outcomes to be. Is it a valid overloading? If not, how will it fail? And how do I capture this information in a way that allows me to use utPLSQL to automatically run my tests? So many questions.

In this article, I don't specify the full set of outcomes for all the test cases; it will be enough, I think, to give you a sense of the overall process. Let's run through a couple of those cases in Table 2 so you get a feel for the kind of information I need to test. Consider the samefamily1 procedure. Here's the specification for this overloading:

CREATE OR REPLACE PACKAGE allargs_test
IS 
   PROCEDURE samefamily1 (arg IN NUMBER);
   PROCEDURE samefamily1 (arg IN INTEGER);

Clearly, the datatypes for the arg argument are too similar; they're both part of the numeric family. Thus, it's an ambiguous overloading that will cause problems. The outcome from running OverloadCheck against samefamily1 should be something like:

allargs_test.samefamily: invalid overloading 

Gee, that seems rather basic. We just say yes or no? Either it's invalid or valid? In this case, I suppose so. But let's look at another, more interesting scenario. Consider the headers for noparms2:

CREATE OR REPLACE PACKAGE allargs_test
IS 
   PROCEDURE noparms2 (
      arg1 IN VARCHAR2 := NULL, 
      arg2 IN VARCHAR2 := NULL);

   PROCEDURE noparms2 (
      arg1   IN   VARCHAR2 := NULL,
      arg2   IN   VARCHAR2 := NULL,
      arg3   IN   VARCHAR2 := NULL);

What should OverloadCheck tell me about these two programs? Let me count the invalid or ambiguous ways that allargs_test.noparms2 could be invoked:

1. allargs_test.noparms2;
2. allargs_test.noparms2 ('abc');
3. allargs_test.noparms2 ('abc', 'def');

OverloadCheck should identify a total of three different invalid formulations for noparms2. That's because all the trailing default values allow me to call noparms2 with varying numbers of parameters. That's a good bit more complicated than the first test case. And, of course, it can get arbitrarily more complex, depending on the number of overloaded programs, the number of parameters, and the presence of default values.

How can I use utPLSQL to check the kinds of results shown above—automatically? The testing framework offers a wide range of assertion programs. For example, I can check to see whether two scalar values are equal, which you already saw with betwnstr:

utassert.eq ('zero start', check_this, against_this);

I can also, however, perform far more interesting assertions. I can check to see if an outcome is NULL. I can check to see whether two tables, two queries, two files, two database pipes, or two collections are equal. I can check to see if a specific exception was raised. I can even, with the latest release (2.0.10.2) of utPLSQL (and thanks to the contribution of Rainer Medert), analyze the output from DBMS_OUTPUT to see if it meets my expectations.

Is it possible to use any of the above assertion checks to test OverloadCheck? I mentioned earlier that my first inclination when writing such a utility is to display the results out to the screen. (In fact, an early prototype of OverloadCheck, which you can find in the args_analysis.pkg script, employs this technique.) Perhaps I could then use the utAssert.eqoutput procedure.

After considering this for a while, I decide that, although it is theoretically possible that this assertion program would work, I really can't see building a test suite around DBMS_OUTPUT. Why not? What if I (or someone else who uses and enhances OverloadCheck) decide to tweak the format of the output? I'd have to change the code in my unit-test package. The bottom line is that testing the correctness of OverloadCheck via DBMS_OUTPUT blurs the line between data (the results of analysis) and presentation (the way I show those results on the screen). Because that's generally a bad idea, I look for other options.

It would certainly be easier to verify the correctness of OverloadCheck if the outcomes were more structured than a line of text showing up on the screen. Let's revisit the results from allargs_test.noparms2:

1. allargs_test.noparms2;
2. allargs_test.noparms2 ('abc');
3. allargs_test.noparms2 ('abc', 'def');

What this actually shows is that in any of the above invocations, PL/SQL cannot tell which of the two noparms2 procedures I want to run. You can see another way to represent this information in Table 3. I keep track of which pair of overloadings I'm comparing and then, within each overloading, which sequence of arguments is ambiguous. I could store this information (both the control data—what I expect—and the test data that comes from running OverloadCheck) in a database table or a collection. Then, I could use, say, utAssert.eqTable to check the results of the OverloadCheck analysis against a control table that I set up and populate with my expected results. I've found it's generally lots easier to populate and work with relational tables than collections, so let's pursue that route after I review my thinking so far:

• I don't want to rely on DBMS_OUTPUT for verification of correctness, but I do want to display results to the screen so that a OverloadCheck user can see those results easily. That's the whole point of the utility, after all.
• I'd like to have OverloadCheck populate a database table with those same results, so that I can comprehensively and accurately test the utility.

Now it's time to design a database table (or two or three), taking into account the requirements (and wish list) for my persistent data structures:

• Store control and test data. I don't have to keep these in the same table, and in fact, I'm inclined to separate them. It'll be easier to manage the data and perform the tests.
• Keep track of all the information utPLSQL needs in order to run its tests, and report on the results.
• Minimize the amount of code I need to write in my test package to integrate with utPLSQL.

I'm going to start with that last point first. As I mentioned earlier, it's not unusual for the volume of testing code to exceed that of the application. That's fine, but I sure wouldn't mind being able to achieve comprehensive testing without writing generating, or designing thousands of lines of code. Is this possible?

In fact, here's what I'd like my test package to look like (in pseudo-code) for OverloadCheck:

BEGIN

   FOR every testcase
   LOOP
      Run OverloadCheck for the testcase scenario
      Compare test results to control table
   END LOOP;
END;

In other words, I'd like to shift away from hard coding all of my test case logic directly in my test package (ut_betwnstr is one example of this approach) and instead pursue a soft coding approach in which I leverage information in database tables to drive my testing.