Database Unit Test Tool-SQLUnit

As mentioned earlier, I have changed the original code writing method. Instead of writing test cases to test-driven development 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 a master with an automatic serial number

As mentioned earlier, I have changed the original code writing method. Instead of writing test cases to test-driven development 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 a master with an automatic serial number

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

27

28

29

30

31

32

33

34

35

36

37

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;

Rochelle table T_DIGITS: = T_DIGITS ();

Rochelle count INTEGER: = 0;

Rochelle num INTEGER;

Rochelle digit INTEGER;

L_odd INTEGER: = 0;

Rochelle even INTEGER: = 0;

Rochelle sum INTEGER: = 0;

Rochelle 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

Rochelle count: = Rochelle count + 1;

Rochelle table.extend (1 );

L_table (l_count): = SUBSTR (p_card_number, I, 1 );

End loop;

FOR I IN 1 .. l_table.COUNT LOOP

Rochelle digit: = Rochelle table (I );

If mod (I, 2)> 0 THEN

Rochelle num: = Rochelle digit * 2;

IF l_num> 9 THEN

FOR I IN 1 .. LENGTH (l_num) LOOP

L_odd: = maid + SUBSTR (l_num, I, 1 );

End loop;

ELSE

L_odd: = l_num;

End if;

P ('odd: '| l_odd );

ELSE

Rochelle even: = Rochelle digit;

End if;

Rochelle sum: = Rochelle sum + Rochelle odd + Rochelle even;

P ('L _ sum: '| l_sum );

L_odd: = 0;

Rochelle 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

27

28

29

30

31

32

33

34

35

36

37

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" 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) (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, failures: 2, errors = 0

I can discover these problems, fix them, and run these tests within seconds. Does this look cool?