Say that I am writing a big application with lots of string manipulation. I've got a "hangnail" calledSUBSTR—this function bothers me and I need to take care of it. What's the problem? SUBSTR is great when you know the starting location of a string and the number of characters you want. In many situations, though, I have only the start and end locations, and I need to figure out the number of characters. Is it:
mystring:= SUBSTR (full_string, 5, 17); -- start and end? Nah...mystring:= SUBSTR (full_string, 5, 12); -- end - start?mystring:= SUBSTR (full_string, 5, 13); -- end - start + 1?mystring:= SUBSTR (full_string, 5, 11); -- end - start - 1?Why should I have to remember stuff like this? I never do remember, in fact, and so I find myself time and again doing the "SUBSTR tango": take out a scrap of paper, write down "abcdefgh", put a mark over the "c" and another over the "g", count on my fingers, and then remember that the formula is "end - start + 1". Of course.
All right, so I do that a dozen times, I'm pretty sick of it, and now I'm determined to stop wasting my time. I decide to write a function of my own called "betwnstr" (return the STRing BETWeeN the start and end) that does the work and the remembering for me.
After just a few moments, I create the following function:
CREATE OR REPLACE FUNCTION betwnStr (string_in IN VARCHAR2, start_in IN INTEGER, end_in IN INTEGER) RETURN VARCHAR2ISBEGIN RETURN (SUBSTR (string_in, start_in, end_in - start_in + 1));END;That was easy—and I can tell with just a glance that it will work—right? It seems so obvious. Yet... OK, I really should run some tests and to do that, I put together a test script:
BEGIN DBMS_OUTPUT.PUT_LINE (betwnstr ('abcdefgh', 3, 5));END;And when I run this script (saved in the betwnstr.tst file), everything looks hunky-dory:
SQL> @betwnstr.tst cdeAm I done testing? Not really. There are lots of different conditions I ought to test before I say that betwnstr is ready for prime time. I can pass a starting location of 0 to SUBSTR, for example, and it acts just as though I passed it a value of 1. Will betwnstr work the same way? Best to check. So I go back into my test script, make the change, and run it:
BEGIN DBMS_OUTPUT.PUT_LINE (betwnstr ('abcdefgh', 5, 10));END;And when I run this script again, once more everything looks fine:
SQL> @betwnstr.tst efghAfter pondering for another thirty seconds or so, I come up with lots of other conditions/combinations:
| Start | End |
| NULL | NOT NULL |
| NOT NULL | NULL |
| NULL | NULL |
| 3 (positive number) | 1 (smaller positive number) |
| 3 (positive number) | 100 (larger than length of string) |
And so on. And the most likely path of testing these various conditions is to go back to betwnstr.tst, plug in the values, and run the test. After a while, I will be very satisfied that my code works correctly.
We could conclude that I have just performed a very thorough unit test of my program. There are, however, several drawbacks to this approach:
· It was necessary for me to visually ("manually") verify that the result of the betwnstr execution was correct. Is the correct answer really "def"? Perhaps it is "efg". I need to look at the original test and do the work in my head. This is both time-consuming and error-prone.
· Each time I set up a new test, I "lost" my previous test; I typed in new values over old values.
· There is a good chance that I will lose track of—or even immediately discard—my ad hoc test script when I am satisfied that the code works.
The consequences of this approach to testing are rather far-reaching. Suppose that I want to add functionality to this program, or I discover a problem after using it for a little while. There's one problem with betwnstr you might have already noticed: what if I pass it a negative starting position? If I do that with SUBSTR, it simply starts at the Nth position from the end of string and then scans forward to extract the substring, as in:
BEGIN DBMS_OUTPUT.PUT_LINE (SUBSTR ('abcdefgh', -3, 5));END;This returns "fgh". If I pass those same arguments to betwnstr, I get some eerily similar but questionable results:
SQL> BEGIN 2 DBMS_OUTPUT.PUT_LINE (SUBSTR ('abcdefgh', -3, 5)); 3 DBMS_OUTPUT.PUT_LINE (betwnstr ('abcdefgh', -3, 5)); 4 END; 5 / fghfghThis doesn't really make sense, does it? What is the string between positions -3 and 5? Seems to me that betwnstr should accept -3 as start and -5 as end and return the substring "def"—or is it "fed"? I will leave the revised implementation of the betwnstr function as an exercise for the reader, because it is not relevant to unit testing. For now, just assume that I have modified betwnstr in some fairly substantial ways to support negative start and end positions.
Consider the situation I now face from a testing perspective. To be certain that my code works, I should run all the previous tests, plus an additional set of tests based on the variations of negative and positive values. But I didn't keep all those test cases! I ran the tests, the code worked, I was done.
Ah, but it isn't that simple, is it? I can tell you with total confidence that I have never written a piece of code that required just one round of testing. Software, if it is used by human beings, will change over time. That is the nature of the reality software seeks to emulate.
Now I face the task of re-creating the same tests I ran earlier. This makes me feel harried, short of time (because I am clearly wasting my time doing something I did before), and pressured. I know now that I should have kept all those tests intact, but I feel that to do so now would take extra time. I need to test, test quickly no matter how ugly the process, and get this code into production.
Does that sense of desperation sound familiar? It is a sure sign that you should slow down, even STOP, and reevaluate your path. So I will do that with betwnstr.
Certainly, it would make more sense to construct a series of tests in my test script, something like this:
SET SERVEROUTPUT ON FORMAT WRAPPEDBEGIN DBMS_OUTPUT.PUT_LINE (betwnstr ('abcdefgh', 3, 5)); DBMS_OUTPUT.PUT_LINE (betwnstr ('abcdefgh', 0, 2)); DBMS_OUTPUT.PUT_LINE (betwnstr ('abcdefgh', NULL, 5)); DBMS_OUTPUT.PUT_LINE (betwnstr ('abcdefgh', 3, NULL)); DBMS_OUTPUT.PUT_LINE (betwnstr ('abcdefgh', 3, 100)); DBMS_OUTPUT.PUT_LINE (betwnstr ('abcdefgh', -3, -5)); DBMS_OUTPUT.PUT_LINE (betwnstr ('abcdefgh', -3, 0));END;When I run this script (which I can do repeatedly with little incremental effort), I get the following output:
This output is, unfortunately, very hard to analyze for correctness.
Doesn't it seem that there should be a better way to obtain a high level of confidence in one's code—to test comprehensively without taking lots of time to get the job done? I believe there is, and I spent a good part of June 2000 constructing a PL/SQL utility called utPLSQL to do just that.
If I were going to use utPLSQL to test betwnstr, I would open a SQL*Plus session and issue this statement:
SQL> exec utplsql.test ('betwnstr')If there were a problem, it would be revealed with this kind of message sent to the screen:
> FFFFFFF AA III L U U RRRRR EEEEEEE> F A A I L U U R R E> F A A I L U U R R E> F A A I L U U R R E> FFFF A A I L U U RRRRRR EEEE> F AAAAAAAA I L U U R R E> F A A I L U U R R E> F A A I L U U R R E> F A A III LLLLLLL UUU R R EEEEEEE. FAILURE: "betwnstr" FAILURE - EQ "normal" Expected "cde" and got "c"FAILURE - EQ "zero start" Expected "abc" and got "a"SUCCESS - ISNULL "null start" Expected "" and got ""SUCCESS - ISNULL "big start small end" Expected "" and got ""Assuming that I fixed the problem and the test turned out to be successful, I would simply see this:
SQL> exec utplsql.test ('betwnstr'). > SSSS U U CCC CCC EEEEEEE SSSS SSSS> S S U U C C C C E S S S S> S U U C C C C E S S> S U U C C E S S> SSSS U U C C EEEE SSSS SSSS> S U U C C E S S> S U U C C C C E S S> S S U U C C C C E S S S S> SSSS UUU CCC CCC EEEEEEE SSSS SSSS. SUCCESS: "betwnstr" SUCCESS - EQ "normal" Expected "cde" and got "cde"SUCCESS - EQ "zero start" Expected "abc" and got "abc"SUCCESS - ISNULL "null start" Expected "" and got ""SUCCESS - ISNULL "big start small end" Expected "" and got ""Well, that's kind of nice, isn't it? utPLSQL tells me whether or not my test succeeded—and even reports on individual test cases. Now how is this possible?
Unfortunately, it is not all automatic. You need to build a test package that conforms to certain rules (mainly, how you name procedures in the specification). For example, the test package specification for betwnstr looks like this:
CREATE OR REPLACE PACKAGE ut_betwnstrIS PROCEDURE ut_setup; PROCEDURE ut_teardown; -- For each program to test... PROCEDURE ut_betwnstr;END ut_betwnstr;In other words, I specify a test setup program that is run before my unit tests, and a teardown program that runs after the unit tests in order to perform cleanup operations. The ut_betwnstr procedure is the unit test program for betwnstr. Here is a portion of the implementation of this program:
PROCEDURE ut_BETWNSTR IS BEGIN utAssert.eq ('normal', BETWNSTR('abcdefg', 3, 5), 'def'); utAssert.isnull ('null start', BETWNSTR('abcdefg', NULL, 5),); END ut_BETWNSTR;Here I am making calls to utPLSQL assertion routines to check whether the outcome of a call to betwnstr matches what I would have expected. The result of this test is stored in a database table. After all unit tests are run, utPLSQL queries the contents of this table to display the results.
Figure 19-1 shows the "round trip" involved in utPLSQL's running the test programs, which in turn call assertion programs to populate the results table, which is then analyzed for test outcomes.
