In this sample we will: refactor the code and introduce multiple
test suites; learn about new Assert Procedures that verify DDL.

After accumulating log records from different sources for some time business decided to learn all about available assets. They would like to assign each asset the unique identifier. The new routine will run manually for some time after log files are loaded into database, we also know that some of the log records can come in late, so we would need to sweep data over and over again to fetch available information.

Part 1: Doing DB Test-driven Development

Note: we have moved the business logic code and object creation steps to the second part of our tutorial, if you feel like starting from the object creation please look at Step 11, otherwise it is perfectly fine to start design process using TDD. 

  1. We will use new unit test suite ASSETINFO to test asset loading functionality and store it in the separate file “unit_tests_AssetInfo_install.sql”
  2. In the new ASSETINFO test suite we will create setup and teardown procedures to load testing data, run asset loading logic and eventually clean up:

CREATE PROCEDURE UT_ASSETINFO_SETUP
AS
BEGIN
    EXEC
DBTD_RUN_ONCE 'Setup will run only once for all the unit tests in the ASSETINFO suite';
   
EXEC DBTD_LOG_MESSAGE 'INFO', 'RUNNING UT_ASSETINFO_SETUP';
   
--load test data
   
EXEC UTD_LOAD_TEST_DATA_INTO_ACCESS_LOG;
   
--run app logic
   
EXEC SP_LOAD_ASSET_INFO;
END;

CREATE PROCEDURE UT_ASSETINFO_TEARDOWN
AS
BEGIN
   
EXEC DBTD_RUN_ONCE 'Teardown will run only once for all the unit tests in the ASSETINFO suite';
   
EXEC DBTD_LOG_MESSAGE 'INFO', 'RUNNING UT_ASSETINFO_TEARDOWN';
   
--remove test data
    EXEC
UTD_REMOVE_TEST_DATA_FROM_ACCESS_LOG;
END;

  1. Create unit test which checks that after we run application logic we will have at list some data in the ASSET table:

CREATE PROCEDURE UT_ASSETINFO_CheckData
AS
BEGIN
    
EXEC DBTD_ASSERT_IS_NOT_EXPECTED_COUNT 0, 'ASSET', '', 'ASSET table has no data';
END;

  1. Since we know name of our new table, lets create test to verify that table exist and that it have columns that we need:

CREATE PROCEDURE UT_ASSETINFO_CheckAssertTable
AS
BEGIN
   
--chec that table exist
    EXEC
DBTD_ASSERT_TABLE_EXISTS 'ASSET', 'Could not find ASSET table';
   
--check that columns we need exist and correct
    EXEC
DBTD_ASSERT_COLUMN_TYPE_AND_PRECISION 'ASSET', 'ASSET_ID', 
        
'INT', '', ' Unique asset ID column not found';
   
EXEC DBTD_ASSERT_COLUMN_TYPE_AND_PRECISION 'ASSET', 'OBJECT_NM', 
        
'VARCHAR', '(250)', 'Name column not found';
   
EXEC DBTD_ASSERT_COLUMN_TYPE_AND_PRECISION 'ASSET', 'DATA_SOURCE_NM', 
        
'VARCHAR', '(50)', 
        
'Name of the Data Source where data came from column not found';
   
EXEC DBTD_ASSERT_COLUMN_TYPE_AND_PRECISION 'ASSET', 'RECORDED_DT', 'DATETIME', '', 
        
'Time stamp when record were added column not found';
END;

  1.  Based on our requirement create unit test that checks newly created assets IDs:

 

CREATE PROCEDURE UT_ASSETINFO_AssetIdentifierIsUnique
AS
BEGIN
    EXEC
DBTD_ASSERT_IS_COLUMN_VALUE_UNIQUE 'ASSET', 'ASSET_ID', 
        
'Duplicate ASSET_IDs are found in the ASSET table';
END;

CREATE PROCEDURE UT_ASSETINFO_AssetIdentifierIsNotNull
AS
BEGIN
    EXEC
DBTD_ASSERT_IS_EXPECTED_COUNT 0, 'ASSET', 'ASSET_ID IS NULL', 
         'ASSET_IDs could not be NULL';
END;

 

  1. Create unit test which checks that asset is not loaded multiple times under different ASSET_ID, we will assume that assets with similar names that came from  different data sources are different assets

CREATE PROCEDURE UT_ASSETINFO_NoDuplicateAssets
AS
BEGIN
    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
       
(
      
SELECT OBJECT_NM, DATA_SOURCE_NM, COUNT(1) AS CT
       FROM
ASSET
       GROUP
BY OBJECT_NM, DATA_SOURCE_NM
      
) AS T
    WHERE 
       
CT > 1;

    EXEC DBTD_ASSERT_ARE_EQUAL @v_Actual_Count, @v_Expected_Count, 
        
'Same Asset were loaded multiple times under different IDs';

END;

  1.  Create unit test stab which checks that we have loaded all the assets available in the log table:

CREATE PROCEDURE UT_ASSETINFO_AllAssetsLoaded
AS
BEGIN
    
EXEC DBTD_FAILURE 'Not Implemented';
END;

 

  1.  Since now we have two unit test suites  ASSETINFO and ACCESSMONITORING we can run them independently or all together, let’s try it:
    1. First run:

EXEC DBTD_RUNTESTSUITE 'ACCESSMONITORING','', 1;

           and then

EXEC DBTD_RUNTESTSUITE 'ASSETINFO','', 1;

  1. As an alternative you can run all available tests in the database by calling following procedure:

EXEC DBTD_RUNTESTS;

  1. Let’s check if there are any failed test (we know there should be):

SELECT * FROM DBTD_TBL_TESTRESULT WHERE Status != 'Success';

and what errors were returned by failed unit tests:

SELECT * FROM DBTD_TBL_LOG
WHERE EventType IN ('ERROR','WARNING')
ORDER BY EventTime DESC;

  1. There will be many unit tests that failed or were ignored due to the problems with suite setup or teardown procedures, and that is the good sign,  because there are no application objects created in the database (of course if you have not created them yet :-) ).
    It is time to start codding.

 

Part 2: Codding Business logic

Note: This tutorial based on the prior tutorials, because of that you might already have some of the objects created, but if you are starting afresh you can use code from following files:

  • app_install.sql – installs application objects;
  • source_objects_install.sql – installs data source objects;
  • test_data_install.sql – created procedures that loads test data;

 

  1. Create ASSET table

CREATE TABLE ASSET
(
  
ASSET_ID INT IDENTITY (1, 1) NOT NULL,--Unique asset ID
 
 OBJECT_NM VARCHAR(250), --Name of the object
   DATA_SOURCE_NM VARCHAR(50),--Name of the Data Source where data came from
   RECORDED_DT DATETIME --Time stamp when record were added
);

  1. For purpose of this sample please remove all of the logic from SP_LOAD_ASSET_INFO  procedure, and keep only following:

CREATE PROCEDURE SP_LOAD_ASSET_INFO
AS
BEGIN
    
EXEC DBTD_LOG_MESSAGE 'WARNING','todo: create logic';
END;

  1. Run all unit tests and check if there is anyone that failed.
  2. There will be at list two unit tests that failed UT_ASSETINFO_CheckData and UT_ASSETINFO_AllAssetsLoaded. Both unit tests failed because SP_LOAD_ASSET_INFO  procedure does not load any information into asset table.
  3. Create our first draft of SP_LOAD_ASSET_INFO, in this version we will load all available assets from the log file. This is not final iteration of procedure script, because this code will create a lot of duplicates if run again:

CREATE PROCEDURE SP_LOAD_ASSET_INFO
AS
BEGIN
    INSERT INTO
ASSET(OBJECT_NM, DATA_SOURCE_NM, RECORDED_DT)
   
SELECT
        DISTINCT
       
OBJECT_NM,
       
DATA_SOURCE_NM,
        GETDATE() AS RECORDED_DT
   
FROM
       
ACCESS_LOG;
END;

  1. Re-run all unit tests as we did it in the step 13 and you will find that all of them succeed?! 
    This is quite a surprise because, as you know, logic will deliberately create duplicate records and  UT_ASSETINFO_NoDuplicateAssets unit test missing to catch them.
    The reason unit test succeeds is because business logic run only once when it needs to run multiple times. Here is the fix:

CREATE PROCEDURE UT_ASSETINFO_SETUP
AS
BEGIN
    EXEC
DBTD_RUN_ONCE 'Setup will run only once for all the unit tests in the ASSETINFO suite';
   
EXEC DBTD_LOG_MESSAGE 'INFO', 'RUNNING UT_ASSETINFO_SETUP';
   
--load test data
    EXEC UTD_LOAD_TEST_DATA_INTO_ACCESS_LOG;
   
--run app logic first time
    EXEC SP_LOAD_ASSET_INFO;
   
--run app logic second and third time in attempt to create duplicates
    EXEC SP_LOAD_ASSET_INFO;
   
EXEC SP_LOAD_ASSET_INFO;
END;

 To be on the safe side we can also add extra business logic call in to UT_ASSETINFO_NoDuplicateAssets unit test, in case if we forget and change suite setup procedure.

  1. Re run unit test and you will find that UT_ASSETINFO_NoDuplicateAssets test failed as expected.
  2. Make changes to SP_LOAD_ASSET_INFO procedure in a way that it will load only new records:

CREATE PROCEDURE SP_LOAD_ASSET_INFO
AS
BEGIN
    INSERT INTO
ASSET(OBJECT_NM, DATA_SOURCE_NM, RECORDED_DT)
   
SELECT
        DISTINCT
        L
.OBJECT_NM,
       
L.DATA_SOURCE_NM,
       
GETDATE() AS RECORDED_DT
   
FROM
       
ACCESS_LOG AS L
        LEFT
OUTER JOIN
       
ASSET AS A
       
ON A.OBJECT_NM = L.OBJECT_NM
          
AND A.DATA_SOURCE_NM = L.DATA_SOURCE_NM
    
WHERE
       
A.RECORDED_DT IS NULL;
END;

  1. Re run unit test again and finally all test succeed.

In this tutorial we have introduces assert procedures that check DDL. And learned that successful run of all unit tests might unveil need for additional unit test coverage, as we have noticed with catching the duplicate records.

Sample Source Code

You can find source code for this sample on our download page, code should include following files:

  • app_install.sql – installs application objects;
  • app_remove.sql – removes application objects;
  • source_objects_install.sql – installs source objects;
  • source_objects_remove.sql – installs source objects;
  • test_data_install.sql – loads test data;
  • test_data_remove.sql – drops objects that create test data;
  • unit_tests_AccessMonitoring_install.sql – Assert monitoring test suite install
  • unit_tests_AccessMonitoring_remove.sql – Assert monitoring test suite removal
  • unit_tests_AssetInfo_install.sql –Asset Information test suite install package
  • unit_tests_AssetInfo_install.sql –Asset Information test suite removal package

 

See Also