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’).

  1. 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

 

  1. 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

  1. 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;
  2. 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 

  1. Populate source table with some test data;
  2. 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
  1. Check that there is data in the ACCESS_LOG table:
    SELECT TOP 10 * FROM ACCESS_LOG;
     
  2.  Get data from the DENIED_USER_ACCESS table:
    SELECT * FROM DENIED_USER_ACCESS;
     
  3. Run each test individually:
    exec UT_ACCESSMONITORING_HasDeniedOnly;
    exec UT_ACCESSMONITORING_ShouldHaveRecords;
    exec UT_ACCESSMONITORING_CheckSourceData;

     
  4. Run all tests for the Unit Tests Suite ACCESSMONITORING:
    EXEC DBTD_RUNTESTSUITE 'ACCESSMONITORING','', 1;
     
  5. Verify unit test results:
    SELECT * FROM DBTD_TBL_TESTRESULT;
    SELECT * FROM DBTD_TBL_LOG;
    SELECT * FROM DBTD_TBL_TESTSLIST
    ;

See Also