In this tutorial you will learn about suite setup
and teardown procedures, message logging
and hint procedures


In our prior samples we were doing a few manual steps to prepare environment for unit tests to run. This might be ok to do a few times or do it for a few days, but in a long run all those little steps will take a lot of our time.

DB Test Driven framework allows you to automate the steps of setting up environment and cleaning after the run. Moreover you have a choice to prepare environment each time before individual unit test execution, or once at the beginning of Test Suite. The cleanup steps are configurable in the same way. 

Let’s start:

  1. Please note that we have converter our data loading script “load_test_data.sql” from prior samples in to following stored procedures: UTD_LOAD_TEST_DATA_INTO_ACCESS_LOG and UTD_REMOVE_TEST_DATA_FROM_ACCESS_LOG. We are using “UTD_” prefix as abbreviation for “Unit Test Data”. You will find procedures in the “test_data.sql” file;
     
  2. Let’s create Suite Setup stored procedure UT_ACCESSMONITORING_SETUP which will load the test data into the source tables and will run application logic:

         CREATE PROCEDURE UT_ACCESSMONITORING_SETUP
         AS
         BEGIN
             EXEC DBTD_LOG_MESSAGE 'INFO', 'RUNNING UT_ACCESSMONITORING_SETUP';
             --load test data
             EXEC UTD_LOAD_TEST_DATA_INTO_ACCESS_LOG;
             --run app logic
             EXEC SP_LOAD_DENIAL_EVENTS;
         END;

     
  3. Then create clean-up procedure UT_ACCESSMONITORING_TEARDOWN  that will clean-up and remove test data from the system:

         CREATE PROCEDURE UT_ACCESSMONITORING_TEARDOWN
         AS
         BEGIN
             EXEC DBTD_LOG_MESSAGE 'INFO', 'RUNNING UT_ACCESSMONITORING_TEARDOWN';
             --clean denial table
             DELETE FROM DENIED_USER_ACCESS
             WHERE
               ACCESS_LOG_ID IN
                (
                SELECT ACCESS_LOG_ID
                FROM ACCESS_LOG
                WHERE DATA_SOURCE_NM = 'TESTING DATA'
                );
             --remove test data
             EXEC UTD_REMOVE_TEST_DATA_FROM_ACCESS_LOG;
         END;

     
  4. It will be good idea to check if there is any data in the source table, let’s create following unit test:
         CREATE PROCEDURE UT_ACCESSMONITORING_CheckSourceData
         AS
         BEGIN
             EXEC DBTD_ASSERT_IS_NOT_EXPECTED_COUNT 
                0, 
                'ACCESS_LOG', 
                '', 
                'Source table ACCESS_LOG has no data';
         END;
        
  5. Run unit tests in the ACCESSMONITORING Suite:

         EXEC DBTD_RUNTESTSUITE 'ACCESSMONITORING','', 1;

    check if there are any tests which failing:

         SELECT * FROM DBTD_TBL_TESTRESULT WHERE Status != 'Success';

    if something fails, check what errors were returned by failed unit tests:

         SELECT * FROM DBTD_TBL_LOG
         WHERE EventType IN ('ERROR', 'FAILURE')
         ORDER BY EventTime desc


    Note: “EventTime” column in the DBTD_TBL_LOG table will help you to find latest related errors.
     
  6. In our case everything went fine and there was no errors or failures.
  7. Run following query to identify how many times setup and teardown procedures have run:

         SELECT *
         FROM DBTD_TBL_LOG
         WHERE
             [Message] like'%RUNNING UT_ACCESSMONITORING_SETUP%'
             OR [Message] like '%RUNNING UT_ACCESSMONITORING_TEARDOWN%'
         ORDER BY EventTime desc


    you will find out that they have run as many times as there unit tests. In many cases this behavior is desirable, but when you start to load millions of testing records it might greatly decrease performance and increase server load.
     
  8. Looking on each of our existing unit tests we might say that there no need to load and clean-up processes to run before the each individual test. Our unit tests will function equally with only one data load.
    This might not be a case for many other projects, but we will touch base on that in our future tutorials.
  9. For now let’s change our set up and teardown procedures to run only once, by adding following line into our setup and teardown procedures:

         EXEC DBTD_RUN_ONCE 'Procedure will run only once for all the unit tests in the ACCESSMONITORING suite'; 
     
  10. Re-run unit tests the same way as it described in the step 5. In our case we got no issues.
  11. Double check how many times setup and teardown procedures have run as described in the step 7. In our case we have found that setup and teardown procedures ran only once, as expected.
  12. Before we call it an End, let’s make sure that our unit tests do what they intended to do, and check if our setup or teardown process caused any impact:
    1. UT_ACCESSMONITORING_CheckSourceData – no impact;
    2. UT_ACCESSMONITORING_AllAvailableDenialsAreLoaded – no impact;
    3. UT_ACCESSMONITORING_DenialsAreNotDuplicated – impacted. Code assumes that UTD_LOAD_TEST_DATA_INTO_ACCESS_LOG procedure will run multiple times before this test;
    4. UT_ACCESSMONITORING_DenialIDsAresUnique – impacted. Code assumes that UTD_LOAD_TEST_DATA_INTO_ACCESS_LOG procedure will run multiple times before this test;
    5. UT_ACCESSMONITORING_DenialSelectedUnderSpecifiedThreshold – no impact;
    6. UT_ACCESSMONITORING_HasDeniedOnly – no impact;
    7. UT_ACCESSMONITORING_ShouldHaveRecords – no impact;
  13. After analysis we can see that two unit tests need to run SP_LOAD_DENIAL_EVENTS logic more than once to verify application functionality. To mitigate this impact we a few choices:
    1. Update unit tests suite setup procedure to run logic multiple times;
    2. Update unit tests to run business logic a few times internally in the test;
    3. Move two affected tests into separate unit test suite with its own setup;
  14. In our case we have chosen to update unit tests and call extra loads from the tests. It allows us to keep each test complete and independent from external factors.
    To do it we have added following two lines into UT_ACCESSMONITORING_DenialsAreNotDuplicated and UT_ACCESSMONITORING_DenialIDsAresUnique unit tests:

         EXEC SP_LOAD_DENIAL_EVENTS; -- extra logic run
         EXEC SP_LOAD_DENIAL_EVENTS; -- extra logic run

     
  15. Re-run unit tests the same way as it described in the step 5.
    No issues found on our side. 

In this tutorial we have moved quite a few steps towards automation and on the way to free ourselves from tedious daily activities while gettinh that extra time for development and fun. 
 

Sample Source Code

you can find source code for this sample on our download page, code should include following files:

  • install_app.sql – installs application objects;
  • install_source_objects.sql – installs source objects;
  • install_unit_tests.sql – installs unit tests;
  • remove_app.sql – removes application objects;
  • remove_source_objects.sql – installs source objects;
  • remove_unit_tests.sql – removes unit tests;
  • test_data.sql – loads test data;

 

See Also