DB Test Driven

 

Sample One

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
http://www.dbtestdriven.com/sample-01.aspx