Sample Two

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:

First of all create a few more unit test to cover our new business requirements:

Unit Test to check that all available denials events are loaded from source table -UT_ACCESSMONITORING_AllAvailableDenialsAreLoaded;

Unit Test to check that same denial event were loaded only once from the source table - UT_ACCESSMONITORING_DenialsAreNotDuplicated;

Unit Test to check that we can select data from denial table in less time than allowable threshold UT_ACCESSMONITORING_DenialSelectedUnderSpecifiedThreshold.

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.

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

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.

Let’s refactor our database code:

Change DENIED_USER_ACCESS to a table instead of view, with the new extra columns columns:

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

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;