SQLUnit: Database Unit Testing

What feels like a thousand years ago I used SQLUnit to do create and perform unit tests on database stored procedures. Lots of them. To the point where that's almost all I was doing. I both hated it and loved it. Hated it because it was boring, tedious, mind-numbing work. Loved it because it gave me confidence in the code I was writing and, once all the tedium was done, could be done quickly. A small change could be immediately tested to see it's effect.

Something else happened too, I began to write code thinking about the tests. Every single line of code was a potential test. If I do this, how will I test it? If I do that, how will I test it? Where do the tests end? You could theoretically test forever...and then I would inevitably fall down that rabbit hole.

Like I was saying, it changed the way I wrote code. Test Driven Development without writing the tests first (which I can hardly stand). That was a good thing because I began to anticipate certain errors. On an INSERT statement with a sequence generated Primary Key, should I trap the exception in code and give it my own error number? Or just let the ORA-00001 propagate up? I just let the Oracle error come up, no need to create my own for that. There's something seriously wrong if you get an error in that case and the whole world should know about it.

Speaking of exceptions, use them, throw them, don't trap them (mostly) and do something else. Log it and then RAISE it. I think it's good for every single developer to know when something is messed up. I've spent far too much time trying to debug something because all the errors are being trapped and processing just continues. I've been lucky on some occasions to have error logging, at least...

What is SQLUnit?

SQLUnit is a regression and unit testing harness for testing database stored procedures. An SQLUnit test suite would be written as an XML file. The SQLUnit harness, which is written in Java, uses the JUnit unit testing framework to convert the XML test specifications to JDBC calls and compare the results generated from the calls with the specified results.

Unfortunately it has not been worked on in almost 3 years, but I'd say it's in pretty good shape. There's support for Procedures, Functions, Ref Cursors and User Defined Types (though I haven't tried this out yet). The current version is 5.0. The last version I worked on consistently was 1.3 I believe. A colleague (who now works for Oracle) from my early days of SQLUnit testing even contributed some Oracle specific items.

I used it to build the database for CABEZE, my first, unsuccessful, try at my own business. It was nice because I was building it from scratch, so I could build all the test data along with it (no testing with production data...though there wasn't really much of that anyway), set up the database (create test data), run the tests and then tear it down back to it's original state (empty). Unfortunately the systems I've worked on since weren't empty and testing with production, or semi-production (cleansed) data was the only viable alternative.

Which brings me to now. I'm trying to reacquaint myself with the tool and writing test cases for various procedures. I ran into an issue create test cases because all of our credit card numbers were scrambled. Everything failed with an Invalid Card Number. Barnacles.

Why not create a routine that would generate "real" credit card numbers, more specifically, numbers of the appropriate length that had a check digit? So credit cards use the Luhn formula which is supposed to prevent certain transposition errors.

The Luhn algorithm will detect any single-digit error, as well as almost all transpositions of adjacent digits. It will not, however, detect transposition of the two-digit sequence 09 to 90 (or vice versa). It will detect 7 of the 10 possible twin errors (it will not detect 22 ↔ 55, 33 ↔ 66 or 44 ↔ 77).

With CABEZE, I had written my own PL/SQL card number generator (and validator), but I didn't blog at the time and seem to have lost that code. So I tried writing it again.

Since I'm using SQLUnit, what a great way to demonstrate the power of the tool. It's a somewhat complex (to me anyway) formula, so writing up tests for it should help me to figure it out. Here's the code to create a check digit based on the Luhn formula.

CREATE OR REPLACE
FUNCTION create_check_digit( p_card_number IN NUMBER ) RETURN NUMBER
IS
  TYPE t_digits IS TABLE OF INTEGER;
  l_table T_DIGITS := T_DIGITS();
  l_count INTEGER := 0;
  l_num INTEGER;
  l_digit INTEGER;
  l_odd INTEGER := 0;
  l_even INTEGER := 0;
  l_sum INTEGER := 0;
  l_check_digit INTEGER;
BEGIN
  IF p_card_number IS NULL THEN
    raise_application_error( -20001, 'you must provide a card number' );
  END IF;

  FOR i IN REVERSE 1..LENGTH( p_card_number ) LOOP
    l_count := l_count + 1;
    l_table.EXTEND(1);
    l_table( l_count ) := SUBSTR( p_card_number, i, 1 );
  END LOOP;

  FOR i IN 1..l_table.COUNT LOOP
    l_digit := l_table(i);
    IF MOD( i, 2 ) > 0 THEN
      l_num := l_digit * 2;
      IF l_num > 9 THEN
        FOR i IN 1..LENGTH( l_num ) LOOP
          l_odd := l_odd + SUBSTR( l_num, i, 1 );
        END LOOP;
      ELSE
        l_odd := l_num;
      END IF;
      p( 'odd:  ' || l_odd );
    ELSE
      l_even := l_digit;
    END IF;
    l_sum := l_sum + l_odd + l_even;
    p( 'l_sum:  ' || l_sum );
    l_odd := 0;
    l_even := 0;
  END LOOP;
  l_check_digit := ABS( ( CEIL( MOD( l_sum / 10, 10 ) ) * 10 ) - l_sum );
  p( 'check digit:  ' || l_check_digit );
  p( 'l_sum:  ' || l_sum );
  p( p_card_number || l_check_digit );
  RETURN l_check_digit;
END create_check_digit;
/

I have no doubt it could be done easier, specifically using regular expressions. This is just my first go at it...so don't give me a hard time...any other solutions are welcome in the comments. ;)

Here's the output of my final test run:

[sqlunit] *** Running SQLUnit file: p_cc.xml
[sqlunit] Getting connection(DEFAULT)
[sqlunit] Setting up test...
[sqlunit] Running test[1]: PASSING NULL (125ms)
[sqlunit] Running test[2]: VALID CARD NUMBER (4992739871) (15ms)
[sqlunit] Running test[3]: VALID CARD NUMBER (4012888888881881) (16ms)
[sqlunit] Running test[4]: VALID CARD NUMBER (4111111111111111) (0ms)
[sqlunit] Running test[5]: VALID CARD NUMBER (4222222222222) (15ms)
[sqlunit] Running test[6]: RANDOM (1) NUMBER (5) (0ms)
[sqlunit] Running test[7]: RANDOM (2) NUMBER (55) (0ms)
[sqlunit] Running test[8]: RANDOM (3) NUMBER (557) (16ms)
[sqlunit] Running test[9]: RANDOM (4) NUMBER (5579) (0ms)
[sqlunit] Running test[10]: RANDOM (5) NUMBER (65579) (0ms)
[sqlunit] Running test[11]: RANDOM (14) NUMBER (12345678965579) (16ms)
[sqlunit] Running test[12]: RANDOM NUMBER (5498975) (0ms)
[sqlunit] Tearing down test...

I was able to run a variety of different tests in seconds. A previous test run looked like this:

[sqlunit] *** Running SQLUnit file: p_cc.xml
[sqlunit] Getting connection(DEFAULT)
[sqlunit] Setting up test...
[sqlunit] Running test[1]: PASSING NULL (109ms)
[sqlunit] Running test[2]: VALID CARD NUMBER (4992739871) (109ms)
[sqlunit] Assertion "outparams-equal" failed (6(NUMERIC) != 1(NUMERIC) at outparams[0])
[sqlunit] *** expected:
[sqlunit] 
[sqlunit]   6
[sqlunit] 
[sqlunit] *** but got:
[sqlunit] 
[sqlunit]   1
[sqlunit] 
[sqlunit]
[sqlunit] Running test[3]: VALID CARD NUMBER (4012888888881881) (0ms)
[sqlunit] Running test[4]: VALID CARD NUMBER (4111111111111111) (0ms)
[sqlunit] Running test[5]: VALID CARD NUMBER (4222222222222) (0ms)
[sqlunit] Assertion "outparams-equal" failed (2(NUMERIC) != 0(NUMERIC) at outparams[0])
[sqlunit] *** expected:
[sqlunit] 
[sqlunit]   2
[sqlunit] 
[sqlunit] *** but got:
[sqlunit] 
[sqlunit]   0
[sqlunit] 
[sqlunit]
[sqlunit] Running test[6]: RANDOM NUMBER (5498975) (0ms)
[sqlunit] Tearing down test...
[sqlunit] sqlunit-ant: SQLUnit Tests Failed: In file: p_cc.xml, tests: 6, failures: 2, errors = 0
[sqlunit] SQLUnit Tests Failed: In file: p_cc.xml, tests: 6, failures: 2, errors = 0

I could then identify the problem, fix it, and run the tests within seconds. How cool is that?

That particular run helped me realize that if I passed in a string of numbers that was even, it would give me the wrong result. If you look at Visa for instance, it's typically 16 digits long with the final digit being the check digit. You start at the rightmost digit before the check digit and go backwards. That was the impetus behind putting the digits into the PL/SQL TABLE OF INTEGERS up above.

I'll post more on creating validly formatted credit card numbers later, I just wanted to use it as an example for SQLUnit.

Try it. Use it. Let me know how it goes. Maybe we can get some Java guy to get it started up again.