In this tutorial you will learn how to create unit tests,
run them, check results, and will try conceptual high level
approach to test driven database development.
The data warehouse have a log table that store all user access attempts to many available corporate resources. Along many other fields this table has the ACCESS_ST column with, which stores the access status to a requested resource, for instance GRANTED or DENIED.
Web development team have requested to build a new access monitoring database object (a view or a table) that will include all the events where user was denied access to a given resource (ACCESS_ST = ‘DENIED’).
-
Let’s create Unit Test stored procedure UT_ACCESSMONITORING_HasDeniedOnly which checks that only events available in the object are denial of access events (ACCESS_ST = ‘DENIED’ and ACCESS_ST IS NOT NULL).
As a starting point we are thinking to eventually create a view with the "DENIED_USER_ACCESS" name;
CREATE PROCEDURE UT_ACCESSMONITORING_HasDeniedOnly
AS
BEGIN
DECLARE @v_Message VARCHAR(255);
DECLARE @v_Expected_Count INT;
DECLARE @v_Not_Expected_Count INT;
DECLARE @v_Actual_Count INT;
SET @v_Expected_Count = 0;
SELECT @v_Actual_Count = count(*)
FROM DENIED_USER_ACCESS
WHERE ACCESS_ST != 'DENIED';
EXEC DBTD_ASSERT_ARE_EQUAL @v_Actual_Count, @v_Expected_Count, 'DENIED_USER_ACCESS inclure records that have status other than "DENIED"';
END;
GO
-
In addition let’s create extra unit test UT_ACCESSMONITORING_ShouldHaveRecords to check that there are at list some DENIED records returned by the DENIED_USER_ACCESS view;
CREATE PROCEDURE UT_ACCESSMONITORING_ShouldHaveRecords
AS
BEGIN
DECLARE @v_Message VARCHAR(255);
DECLARE @v_Expected_Count INT;
DECLARE @v_Not_Expected_Count INT;
DECLARE @v_Actual_Count INT;
SET @v_Not_Expected_Count = 0;
SELECT @v_Actual_Count = count(*)
FROM DENIED_USER_ACCESS;
EXEC DBTD_ASSERT_ARE_NOT_EQUAL @v_Actual_Count, @v_Not_Expected_Count, 'DENIED_USER_ACCESS does not have any records';
END;
GO
-
Run Unit Test stored procedure UT_ACCESSMONITORING_HasDeniedOnly and it should fail with the “UNIT TEST ERROR: 208 : Invalid object name 'DENIED_USER_ACCESS'.” because DENIED_USER_ACCESS object does not even exist in database;
-
Create the new view DENIED_USER_ACCESS with ACCESS_ST = ‘DENIED’ where condition;
CREATE VIEW DENIED_USER_ACCESS
AS
SELECT
USER_NM,
OBJECT_NM,
ACCESS_ST
FROM ACCESS_LOG
WHERE ACCESS_ST IN ('DENIED');
GO
-
Populate source table with some test data;
-
Run Unit Test UT_ACCESSMONITORING_HasDeniedOnly again and watch it succeed;
Now we have a few equally important artifacts:
-
The DENIED_USER_ACCESS view - that will be used across our system;
-
The automated unit tests UT_ACCESSMONITORING_HasDeniedOnly and UT_ACCESSMONITORING_ShouldHaveRecords which can be repeatedly run to verify business logic every time there is a change in the database.
Note:
Sample One does not cover all the case scenarios it just provides a conceptual high level approach to test driven database development.
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
-
Check that there is data in the ACCESS_LOG table:
SELECT TOP 10 * FROM ACCESS_LOG;
-
Get data from the DENIED_USER_ACCESS table:
SELECT * FROM DENIED_USER_ACCESS;
-
Run each test individually:
exec UT_ACCESSMONITORING_HasDeniedOnly;
exec UT_ACCESSMONITORING_ShouldHaveRecords;
exec UT_ACCESSMONITORING_CheckSourceData;
-
Run all tests for the Unit Tests Suite ACCESSMONITORING:
EXEC DBTD_RUNTESTSUITE 'ACCESSMONITORING','', 1;
-
Verify unit test results:
SELECT * FROM DBTD_TBL_TESTRESULT;
SELECT * FROM DBTD_TBL_LOG;
SELECT * FROM DBTD_TBL_TESTSLIST;
See Also