Unit testing in database - Test patterns

T

This part will be about test patterns and it is a follow up of previous part about Challenges. You can also use unit_tests tag/label to explore all related articles.

Prerequisites

You need to install utPLSQL package. It is a great project which will make all of this much easier. There is an Install guide, a lot of Examples and of course a Documentation on projects GitHub. On my DEV environment I use headless installation to "UT3" schema and I suggest you to start with that.

Test patterns

There is just few patterns you need.

1. test single value (including NULLs)
2. test multiple values (whole data sets - multiple rows and columns)
3. test if exception was raised

Single values

Let's go thru first example:

CREATE OR REPLACE PACKAGE BODY player_ut AS

    test_player_name    players.player_name%TYPE := 'PLAYER_NAME';



    PROCEDURE test_create_player AS
        curr_player_name    players.player_name%TYPE;
    BEGIN
        -- delete player just to be sure
        player.delete_player (
            in_player_id    => player.get_id(test_player_name),
            in_silent       => TRUE
        );
        --
        player.create_player (
            in_player_name  => test_player_name
        );
    
        -- player is deleted in created during before_each procedure
        SELECT MIN(player_name) INTO curr_player_name
        FROM players
        WHERE player_id     = test_player_name
            AND is_active   = 'Y';
        --
        ut.expect(curr_player_name).to_equal(test_player_name);
    END;

END;
/

So you make sure player don't exist, then run the procedure you want to test (create_player) and then check the result, specifically if correct value exist in Player table. That was not that hard, wasn't it?

Here are some of the checks you can do instead of to_equal(...):

• to_be_null()
• to_be_not_null()
• to_be_greater_or_equal(...)
• to_be_less_than(...)
• to_be_like(...)
• to_be_true()
• to_contain(...)
• to_have_count(...)
• to_match(...)

As you see, checking single value is easy. There are plenty options/checks available and you should explore Expectation concepts for the exhaustive list and details.

Multiple values (data sets, cursors)

Let's go thru another example:

CREATE OR REPLACE PACKAGE BODY player_ut AS

    test_player_name    players.player_name%TYPE := 'PLAYER_NAME';



    PROCEDURE test_notifications AS
        r_current       SYS_REFCURSOR;
        r_expected      SYS_REFCURSOR;
    BEGIN
        -- cleanup first
        DELETE FROM notifications
        WHERE player_id             = test_player_name
            AND notification_id     LIKE 'TEST%';

        -- issue 2 notifications
        player.trigger_notification('TEST 1');
        player.trigger_notification('TEST 2');

        -- create cursors to compare before and after data
        OPEN r_current FOR
            SELECT notification_id, counter
            FROM notifications
            WHERE player_id             = test_player_name
                AND notification_id     LIKE 'TEST%'
                AND delivered_at        IS NULL;
        --
        OPEN r_expected FOR
            SELECT 'TEST 1' AS notification_id, 1 AS counter FROM DUAL UNION ALL
            SELECT 'TEST 2' AS notification_id, 2 AS counter FROM DUAL;
        --
        ut.expect(r_current).to_equal(r_expected);
    END;

END;
/

Goal here is to check if user notifications works. We delete existing test notifications first, then issue two new ones. Then we compare current data set with the expected results as two cursors. Is this complicated? You can check pretty much anything with this concept.

There are more comparing options available:

• to_be_empty()
• to_have_count(...)
• to_contain(...)

Catching exceptions

To catch exceptions you must mark your test procedure:

CREATE OR REPLACE PACKAGE player_ut AS

    -- %test
    -- %throws(PROGRAM_ERROR)
    PROCEDURE test_create_player#duplicite_name;

END;
/

Let's try to raise exception and catch it in test:

CREATE OR REPLACE PACKAGE BODY player_ut AS

    test_player_name    players.player_name%TYPE := 'PLAYER_NAME';



    PROCEDURE test_create_player#duplicite_name AS
    BEGIN
        -- create player to be sure it exists
        player.create_player (
            in_player_name  => test_player_name
        );

        -- create player with existing name must fail
        player.create_player (
            in_player_name  => test_player_name
        );
    END;

END;
/

Thats all. If it raises PROGRAM_ERROR exception (specified in test package specification, the throws(...) line) then test will pass. Otherwise the test will fail.

Now it is your turn. You should play with these on your own.

See you on the next part.