Database Unit Test Tool-SQLUnit

Original information

Database Unit Test Tool-SQLUnit

Preface

As mentioned before, I have changed the original code writing method. Instead of writing test cases immediately (I know this development theory is hard to stand ). But when I predict that this programming method will have problems, this is a good start. In an INSERT statement with a primary key with an automatic serial number, should I generate an error code with an exception of my own system in the conversion program? Or just let the RA-00001 spread? My solution is to only display the error code prompted by Oracle itself, and there is no need to re-create an error code Mechanism for the error itself. If you get an error code under certain circumstances, you have already told the user very seriously that something has gone wrong, and people all over the world should know that the system has gone wrong.

Let's talk about exceptions, use them, throw them, and don't try to catch them (in most cases) and do something else. Record the exceptions and throw them to the next layer. I think: it is a good thing to let every developer know that the current situation is getting worse. I spent too much time debugging code to find out system errors, but most of the exceptions have been caught and the process continues to run, causing me to waste a lot of time. At least in some cases, I was lucky enough to solve the problem by logging error logs.

What is SQLUnit

SQLUnit is a unit test tool for regression testing database stored procedures. A SQLUnit test case should be written in an XML file. The SQLUnit tool is implemented in Java. The JUnit unit is used to test the pipeline construction and conversion XML test information to connect to the JDBC database, and the results obtained from the database and the expected results in the test case are compared. Unfortunately, it only lasted three years of development, but I would like to say that this is a pretty good test model. Supports stored procedures, functions, cursors, and user-defined types (although I have not tried it ). The latest version is 5.0. I have been using version 1.3. When I first started using the SQLUnit test tool, a college student (now working in Oracle) Even contributed code to the test part of the Oracle database in SQLUnit.

I used it to build a database for CABEZE. This is my first project for my own business. Although it was not successful, it was also good, because I only set up a project in some fragmented and unimportant code, I can use SQLUnit to build the entire test data (not to test the actual product data... even if there are no actual devices, create a database (create test data), run the test case, and finally roll back to the original state (empty) in the destruction phase ). Unfortunately, the system I built at work is not empty and tested with actual products, or semi-production (cleaning) data is just a viable alternative.

Return to my current status. I'm trying to get to know myself again and write test cases for a variety of stored procedures with the help of this tool. I followed up on a test case that reported an error because all of our credit card numbers were messy. Each time it fails under an incorrect card number. The things to attach.

Why don't I create a routine that can produce "actual" credit card numbers? More precisely, it is: a series of numbers that have been detected with proper length? Therefore, credit cards use the Luhn formula to prevent these obvious exchange errors.

This Luhn algorithm will detect any odd number errors, and most of them swap the adjacent array. It will not, however, detect the transpose position two digit sequence 09-90 (or vice verb ). It will detect 7 in 10 possible repeated numbers (it cannot detect 22? 55, 33? 66 or 44? 77 ).

Under CABEZE, I have rewritten my own PL/SQL

The card number generator (which passes verification), but now I don't publish it, and I seem to have lost that part of the code. So I try to rewrite it again.

Since I started using SQLUnit, I have experienced how powerful this tool has demonstrated! It has some complex (for me) formulas, so it can help me calculate the results when writing these test cases. This is the sequence of numbers I created to stick to the test based on the Luhn formula.

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 28 29 30 31 32 33 34 35 36 38 39 40 41 42 43 44 45 46 47 48 49

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 ('oss: '| 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;

A lot of Oracle stored procedure functions)

I don't realize that this can be simpler, especially some regular expressions. This is just my first attempt... so don't embarrass me... leave a message if there is a better solution. Thank you. ;)

This is my final test output:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

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

I can run various test cases in a short time. The previous test looks like this:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 28 29 30 31 32 33 34 35 36 38 39 40

[Sqlunit] *** Running SQLUnit file: p_cc.xml [sqlunit] Getting connection (DEFAULT) [sqlunit] Setting up test... [sqlunit] Running test [1]: passing null (109 ms) [sqlunit] Running test [2]: valid card number (4992739871) (109 ms) [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) (0 ms) [sqlunit] Running test [4]: valid card number (4111111111111111) (0 ms) [sqlunit] Running test [5]: valid card number (4222222222222) (0 ms) [sqlunit] Assertion "outparams-equal" faile D (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) (0 ms) [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, fai Lures: 2, errors = 0 I can detect these problems, fix it, and run these tests within a few seconds. Does this look cool? This test helped me realize that if a string is an odd number, it will prompt me with an error. For example, if your Visa card is a typical 16-digit long number, and the last digit is a verification of all numbers. You start with the rightmost number except the checksum and return the number. This is the trend of placing numbers in the preceding PL/SQL integer table. In future tests, I will point out more credit card numbers in incorrect format. I just want to use this as an example program of SQLUnit. Try.. Let me know how it works. Maybe we are lucky to have some Java programmers re-run this project.

Useful Words

Anticipat advance letter of credit

Certain

Propagated reproduction and Proliferation

Regression recession regression

Harness Tool

Semi-production

Cleansed clean cleaning

A viable alternative to viable alternative

Reacquaint

Scrambled

Barnacles

Transposition Interchange

Adjacent

Impetus momentum