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 denial IDs are unique - UT_ACCESSMONITORING_DenialIDsAresUnique;
-
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:
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
);
-
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;
-
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;
-
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;
-
Clear DENIED_USER_ACCESS table
TRUNCATE TABLE DBTD_TBL_TESTRESULT;
-
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
-
Re-run unit tests and verify functionality the same way as described in in the steps 3.
-
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
-
Run all tests for the Unit Tests Suite ACCESSMONITORING:
EXEC DBTD_RUNTESTSUITE 'ACCESSMONITORING','', 1;
-
Verify unit test results:
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
See Also