Thursday, June 11, 2009

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.

8 comments:

Marc Thompson said...

Hi There,
I'm evaluating SQLUnit at the moment and am wrestling with it to try and get User Defined Types to behave nicely.
Did you get around to trying them out? And if so, did you get it to work? All other transaction types operate nicely, but I haven't yet found out how to get UDT's to work, nor is there any documented examples anywhere on the net.

Cheers,
Marc.

oraclenerd said...

@marc

I haven't had a chance to get into user defined types yet unfortunately. All the docs say it works, but alas, they're sparse.

I'm guessing you tried the typemap and typedef tags right?

Beyond that, I won't be able to help you just yet. If those don't help (or don't work), I'll see if I can find time this week to write one up.

chet

Marc Thompson said...

The documentation on typemap and typedef mentions that the tags should be placed within the 'connection' tag, but trying to put it in there gives an error:

The content of element type "connection" must match "((driver,url,user,password,jarfile-url?)|(datasource,jndi))?".

Let me know if you find anything; meanwhile I'll keep experimenting.

Cheers,
Marc.

oraclenerd said...

@marc

I just tried and got the same thing. I'm fiddling around with it too.

Had you already created the Java to map to your UDT?

Marc Thompson said...

I figured out that the sqlunit.dtd inside the sqlunit-5.0.jar in the lib directory was old, so I removed the entire jar and referenced the classes and property files directly. Once the latest version of sqlunit.dtd is used, then the typemap tag can be used.
I'm not entirely sure how to construct the Java class to map the UDT - whether it should be created in a similar fashion to the existing Type classes, or whether I just need to create a class that contains similar variables to the structure of the UDT.
On tracing through a call to a function that returns a UDT, I now get a 'java.sql.SQLException: Inconsistent java and sql object types: MYUSER.MYUDT' error when reaching the setOutputParameters method. So it seems I have to work on the map class.

By the way, I am using java 1.6.0 and Oracle's ojdbc6_g (debug) drivers to connect to an 11g database. I had access violation errors when using the ojdbc14 and ojdbc5_g drivers. eek.

oraclenerd said...

@marc

I think I got one step further than you because I seemed to have ignored (originally anyway) the connection issue.

What you need to do, I think, is create a class that maps to your UDT. If you are using JDeveloper it's easy (if not, download it, for free, and use it).

Once you've installed it, create a connection to your database, open the connection, browse to your UDT. Right click and you should see an option to "Generate Java." That's as far as I got, but here's what I think you need to do after that.

Compile the code (use JDev) and find the location of the java classes. Put those directly in your CLASSPATH environment variable (not the final solution, but a step forward), then you should be able to go.

Alternately, you can create the Java code yourself (much less fun unless you enjoy torture). Check out this article on AskTom (naturally).

I'm going to try out your "trick" as well and reference the classes and properties files directly.

Marc Thompson said...

Brilliant!
I used the Generate Java technique to create the Java class for the UDT, and it is now working fine.

I just need to verify that it will work for nested UDT's, but by all indications it would seem that I shouldn't have any problems with that.

Thank you very much for your help!
Cheers,
Marc.

oraclenerd said...

Good news!

Now share! ;)

If you want, I can post (giving you credit) the details as well. We can't be the only 2 people out there trying to use it. Email: chet at oraclenerd . com