In this tutorial you will refactor existing code,
learn new assert procedures,
will add extra unit tests.


After some time business folks come back with extra set of requirements:

  • Each denial record need to have unique denial identification number for each individual event;
  • Improve performance because statistically we pulling only around 100 denial records per millions of events, and the old view works too slow. We should pull all the records from the new object in under 10 seconds;
  • Denial information should be loaded at list daily;

Base on a new feedback from business we want to change DENIED_USER_ACCESS view in to a table, add DENIAL_ID, and create some script that will load data from ACCESS_LOG table.

Let’s move with development:

  1. First of all create a few more unit test to cover our new business requirements:
    1. Unit Test to check that all available denials events are loaded from source table -UT_ACCESSMONITORING_AllAvailableDenialsAreLoaded;
    2. Unit Test to check that same denial event were loaded only once from the source table - UT_ACCESSMONITORING_DenialsAreNotDuplicated;
    3. Unit Test to check that denial IDs are unique - UT_ACCESSMONITORING_DenialIDsAresUnique;
    4. Unit Test to check that we can select data from denial table in less time than allowable threshold UT_ACCESSMONITORING_DenialSelectedUnderSpecifiedThreshold.
       
  2. Please note that the ACCESS_LOG source table has changed since our last tutorial, now we have an extra ACCESS_LOG_ID field, which is needed to identify each individual log record.
    Run following query to double check that our source table has all fields we need:

         SELECT ACCESS_LOG_ID, USER_NM, OBJECT_NM, ACCESS_ST FROM ACCESS_LOG;

    and that it is populated with test data. You will find the set testing data in the load_test_data.sql file.
     
  3. When done, let’s run all unit tests from the ACCESSMONITORING Suite:

         EXEC DBTD_RUNTESTSUITE 'ACCESSMONITORING','', 1;

    check what tests are failing:

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

    check what error were returned by failed unit tests:

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

     
  4. There should be quite a few tests that have failed because we have not made any changes to the code yet. DENIED_USER_ACCESS view is still there.
     
  5. Let’s refactor our database code:
    1. Change DENIED_USER_ACCESS to a table instead of view, with the new extra columns columns:

           DROP VIEW DENIED_USER_ACCESS;

           CREATE TABLE DENIED_USER_ACCESS
           (
           DENIAL_ID  INT IDENTITY (1, 1) NOT NULL, --Unique denial record ID
           ACCESS_LOG_ID INT,       --Unique access log ID
           USER_NM   VARCHAR(50),     --User name
           OBJECT_NM  VARCHAR(250),     --Name of the object that user attempts to access
           ACCESS_ST  VARCHAR(50)      --Access status
           );

       
    2. Add new stored procedure to load denial data:

           CREATE PROCEDURE SP_LOAD_DENIAL_EVENTS
           AS
           BEGIN
           INSERT INTO DENIED_USER_ACCESS( ACCESS_LOG_ID, USER_NM, OBJECT_NM, ACCESS_ST)
           SELECT ACCESS_LOG_ID, USER_NM, OBJECT_NM, ACCESS_ST
           FROM ACCESS_LOG
           WHERE ACCESS_ST IN ('DENIED');
           END;
          
  6. Run data loading stored procedure a few times, to load denial records.

         EXEC SP_LOAD_DENIAL_EVENTS; -- first run
         EXEC SP_LOAD_DENIAL_EVENTS; -- second run  
         EXEC SP_LOAD_DENIAL_EVENTS; -- third run

     
  7. Re-run unit tests and check results the same way as in the steps 3. Some tests will fail because data loading procedure will incorrectly load all data again and again, instead of loading only new records;
     
  8. Refactor SP_LOAD_DENIAL_EVENTS stored procedure in the following way:

         CREATE PROCEDURE SP_LOAD_DENIAL_EVENTS
         AS
         BEGIN
         INSERT INTO DENIED_USER_ACCESS( ACCESS_LOG_ID, USER_NM, OBJECT_NM, ACCESS_ST)
         SELECT
           L.ACCESS_LOG_ID,
           L.USER_NM,
           L.OBJECT_NM,
           L.ACCESS_ST
         FROM
           ACCESS_LOG AS L
           LEFT OUTER JOIN
           DENIED_USER_ACCESS AS D
           ON
            L.ACCESS_LOG_ID = D.ACCESS_LOG_ID
         WHERE
           L.ACCESS_ST IN ('DENIED')
           AND D.ACCESS_LOG_ID IS NULL;
         END;

     
  9. Clear DENIED_USER_ACCESS table

         TRUNCATE TABLE DBTD_TBL_TESTRESULT;
     
  10. Re run data loading procedure a few times.

         EXEC SP_LOAD_DENIAL_EVENTS; -- first run
         EXEC SP_LOAD_DENIAL_EVENTS; -- second run  
         EXEC SP_LOAD_DENIAL_EVENTS; -- third run

     
  11. Re-run unit tests and verify functionality the same way as described in in the steps 3.
     
  12. By now there should be no failing Unit Tests.

We will improve out project in the next series of samples.
In this tutorial we went over: code refactoring; new unit tests and assertion procedures; running unit tests and checking results;
 

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;
  • remove_app.sql – removes application objects;
  • load_test_data.sql – loads test data;
  • install_unit_tests.sql – installs unit tests;
  • remove_unit_tests.sql – removes unit tests;

 

Useful Queries
  1. Run all tests for the Unit Tests Suite ACCESSMONITORING:

         EXEC DBTD_RUNTESTSUITE 'ACCESSMONITORING','', 1;
     
  2. Verify unit test results:

         SELECT * FROM DBTD_TBL_TESTRESULT WHERE Status != 'Success';
     
  3. Check what error were returned by failed unit tests:

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

See Also