Database Unit Test Tool-sqlunit

Original information

Database Unit Test Tool-sqlunit

Objective

As I said before, I've changed the way I write code. Instead of writing "test-driven development" of test cases immediately (I know this development theory is hard to hold). But this is a good start when I anticipate that there must be a problem with this programming approach. Under an INSERT statement with a primary key with an automatic serial number, should I convert the error code for my system that is abnormal in my program? Or just let RA-00001 spread? The way I do this is simply to eject the error code of Oracle's own prompts, There is no need to recreate a set of error code mechanisms for errors. If you get an error code in some cases, it's very serious to tell the user that something is wrong at the moment, and that people all over the world should know that the system went wrong.

Talk about exceptions, use them, throw them, don't try to catch them (in most cases) and do something else. Record the exceptions and throw them up one level. I think it's a good thing for every developer to know that the current situation is starting to go bad. I've spent too much time trying to figure out the system's errors by debugging code, but most of the anomalies have been captured and the process continues to run, causing me to waste a lot of time. At least in some cases, I was fortunate enough to solve the problem by logging the wrong log.

What is Sqlunit

Sqlunit is a unit test tool for regression test database stored procedures. A sqlunit test case should be written with an XML file. The Sqlunit tool is implemented in Java, using the JUnit Unit to test the mining transformation XML test information to the JDBC database connection, comparing the results obtained from the database and the expected results in the test case. Unfortunately, it only lasted three years of the development process, but what I want to say is that this is a fairly good test model. Supports stored procedures, functions, cursors, and user-defined types (although I haven't tried them yet). The current latest version is 5.0. I've been using the 1.3 version. A college classmate (now working in Oracle), and when I first started using the Sqlunit test tool, he even contributed code to the test part of Oracle database in Sqlunit.

I use it to build a database for Cabeze, this is the first project I've done for my career, even though it didn't work out in the end, but it was good because I was just building projects on some bits and pieces of less important code, so I could build the entire test data through Sqlunit ( Not testing the actual product data ... Although there are no actual devices, the database is set up (create test data), run test cases, and finally roll back to the original state (empty) in the destroy phase. Unfortunately, the system I built in my work was not empty and tested with actual products, or half production (cleaning) data was a viable alternative.

Back to my state now. I'm trying to get to know myself and write test cases for a wide variety of stored procedures with the help of this tool. I followed up with an error-reporting test case because all of our credit card numbers were cluttered. Each time fails under the incorrect card number. The thing that clings.

Why not create a routine that can produce "actual" credit card numbers, more precisely: a sequence of suitable lengths that have detected numbers? So the credit card uses the Luhn formula to block these obvious swap errors.

This Luhn algorithm will detect any odd-numbered errors, most of which are swapped near the array. It will not, however, detect the replacement position two digit sequence 09-90 (or vice verb). It detects 7 detected in 10 possible duplicates (it cannot detect the number of or 44? 77).

Under Cabeze, I have rewritten my own pl/sql.

Card number generator (and validated), 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.

After I started using sqlunit, I experienced what a powerful feature this tool demonstrates! It has some complex formulas (for me), so you can help me figure out the results when I write these test cases. This is the number sequence I created to persist in testing 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 27 28 29 30-31 This is the same as the same as the

CREATE OR REPLACE FUNCTION create_check_digit (p_card_number in number) return number are   type t_digits is TA BLE of integers;   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))-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 large stack of Oracle stored procedure functions)

I didn't realize it would be simpler, especially with some regular expressions. This is just the first time I've tried ... So don't embarrass me ... If there is a better solution, please leave a message. Thank you. ;)

This is the output of my final test:

?

1 2 3 4 5 6 7 8 9 a

[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 t EST[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 (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 tes T[11]: RANDOM (12345678965579) (16MS) [Sqlunit] Running test[12]: RANDOM number (5498975) (0MS) [Sqlunit] tearing down test ...

I was able to run various test cases in a very short time. The previous test ran to look like this:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 This is the

A useful word.

Anticipat Advance Letter of credit

Certain inevitable

Propagation and multiplication of propagated

Regression recession returns

Harness Tool Horse

Semi-production Semi-production

Cleansed, clean the cleaning.

Viable alternative a viable alternative

Reacquaint New Acquaintance

Scrambled messy.

Barnacles cling to the things

Transposition Exchange Transform

Adjacent adjacent to the neighboring

Impetus Momentum Power