Unit Testing a Thick Database

Why Unit Test a Database?

In the Java (OO) world, unit testing specifically avoids testing persistence.  The persistence engine (database) is mocked so it doesn't interfere with the purity of the testing.  Also, Java (OO) purists attempt to remove all logic from the database.  However, we typically don't find such purity in practice.

What is a Thick Database?

A Thick Database is a database with lots of logic built-in.  Dulcian.com does a great job of explaining what it is and why it's useful.  In practice, we tend to see some logic implemented in the database for a variety of reasons.

What is a Unit in the Database?

Most references define a "unit" as something like the smallest bit of logic in a system.  For this discussion, "unit" will refer to an interface.  Interfaces in an Oracle database include the following:

• Packages
• Procedures (Public and Internal)
• Functions (Public and Internal)
• Procedures
• Functions
• Types with Methods
• Tables
• Check Constraints
• DML Triggers
• View Triggers
• Session Triggers
• Database Triggers

Is a Unit Test Framework Required?

No.  However, a unit test framework does allow easier integration with CI/CD.  Examples of database unit test frameworks include:

• utPLSQL
• wtPLSQL
• SQL*Developer Unit Testing

Alternatively, here is a bare-bones package that is useful when a framework is not available.

create package simple_ut

as

    C_FALSE           constant number := -1;

    C_TRUE            constant number := 0;

    C_BRIEF_OUTPUT    constant number := 0;

    C_NORMAL_OUTPUT   constant number := 1;

    C_VERBOSE_OUTPUT  constant number := 2;

    g_output_mode              number := C_NORMAL_OUTPUT;

    procedure ut_announce

            (in_str         in varchar2);

    function ut_assert

            (in_test_name   in varchar2

            ,in_test_string in varchar2)

        return number;

    procedure ut_assert

            (in_test_name   in varchar2

            ,in_test_string in varchar2);

    procedure demo;

end simple_ut;

/

create package body simple_ut

as

    procedure ut_announce

            (in_str         in varchar2)

    is

    begin

        dbms_output.put_line('');

        dbms_output.put_line('========================================');

        dbms_output.put_line('=== ' || $$PLSQL_UNIT || ' ' || in_str);

    end ut_announce;

    function ut_assert

            (in_test_name   in varchar2

            ,in_test_string in varchar2)

        return number
    is

        l_result   number;
    begin

        execute immediate 'BEGIN'                        || CHR(10) ||

                          'if ' || in_test_string        || CHR(10) ||

                          'then'                         || CHR(10) ||

                             ':n1 := ' || C_TRUE  || ';' || CHR(10) ||

                          'else'                         || CHR(10) ||

                             ':n1 := ' || C_FALSE || ';' || CHR(10) ||

                          'end if;'                      || CHR(10) ||

                          'END;' using out l_result;

        return l_result;
    end ut_assert;

    procedure ut_assert

            (in_test_name   in varchar2

            ,in_test_string in varchar2)

    is

    begin

        if ut_assert(in_test_name, in_test_string) = C_TRUE

        then

            if g_output_mode != C_BRIEF_OUTPUT

            then

                DBMS_OUTPUT.PUT_LINE(' -) PASSED: ' || in_test_name);

                if g_output_mode = C_VERBOSE_OUTPUT

                then

                    DBMS_OUTPUT.PUT_LINE('             Details: "' ||

                        replace(in_test_string,CHR(10),';') || '"' );

                end if;

            end if;

        else

            DBMS_OUTPUT.PUT_LINE('*** FAILED: ' || in_test_name);

            DBMS_OUTPUT.PUT_LINE('             Details: "' ||

                replace(in_test_string,CHR(10),';') || '"' );

        end if;

    end ut_assert;

    procedure demo

    is

    begin

        ut_assert('This test passes', '1 = 1');

        ut_assert('This test fails', '0 = 1');

    end demo;

end simple_ut;

/

Where to start?

1. Pick something to test, like a procedure in a package.
2. Define a "No Data" Test Case for that procedure (nothing for the procedure to do).
3. Create a Unit Test Package to contain you Unit Test code.
4. Create a procedure in the Unit Test Package called "no_data_to_process".
5. Write the "no_data_to_process" procedure to call the procedure (from step 1) with without any test data setup.
6. Add "ut_assert" procedure calls from the "simple_ut" package to document results.
7. Run the "no_data_to_process" procedure and check results in DBMS_OUTPUT.
8. Add more tests.

Are there any Tips or Tricks?

1. As part of the Test Case, test the data setup (if any) before the "actual" unit test.
2. Run the "actual" unit test in a separate procedure with a dedicated Exception Handler.
3. Create Unit Test Procedures/Functions to load Test Data into a single record variable.
4. Use the single record variable to INSERT Test Data into a table.
5. Setup Date/Time sensitive data for each run of a Unit Test.
6. Create additional test customers/locations/actors as needed to re-run unit tests.
7. Capture Sequence Generator values after the "actual" unit test.
8. Include testing of logs after the "actual" unit test.
9. Cleanup Test Data only when necessary to re-run unit tests.
10. Develop a complete Test Data Set with a variety of Test Cases setup and ready for testing.

What is the downside?

If the database logic is unstable (constantly changing), it is very difficult to maintain the unit tests.  One strategy is to Unit Test only the functionality that is stable.

What is the Upside?

Several obvious, but sometimes unexpected outcomes of unit testing:

• Testing code that serves no purpose results in removal of useless code.
• Fault insertion testing results in much better error messages and error recovery.
• Thinking through unit test cases results in simplification of overall implementation.