Jump to Start Test-Driven Database Development (TDDD) – Part 3

Total: 7 Average: 4.6

This article is a walk-through of creating a report base on a database object developed and tested by using test-driven development (TDDD). Furthermore, some tips for improving database unit testing via TDDD will be discussed in this article too.

Test-driven database development (TDDD) Recap

In simple words, TDDD is all about writing unit-test before the database object is even created. So, if we want to create a database object to satisfy some business requirement, it should be processed by creating a unit-test that ensures the object exists. Moreover, it has to be followed by another unit-test that will ensure the proper functioning of the database object (at the minimum) though that functioning check should only be limited to meet only the desired requirement.

Jump to Start TDDD Part-1 Recap

In the first part of the article, traditional database development was compared with test-driven database development (TDDD) and provided with coding examples. TDDD began with the unit-test aimed at checking of the object exists and followed by the unit test that checked if the object meets the requirement.

For a detailed walk-through, please, refer to the first part of this article.

Jump to Start TDDD Part-2 Recap

The second part of the article was focused on meeting a reporting requirement to view articles per author. This requirement was finally met by the unit test aimed at checking the object creation and followed by the unit test aimed at checking if the object sends the correct number of articles per author or not.

We ran the database unit tests that passed in order to satisfy the reporting requirement.

Please, refer to the second part of this article to the see details of all the steps for meeting the report requirements.

Anatomy of Unit Test to Check Object Outputs Correct Data

One of the main unit tests in TDDD is aimed at checking whether database object outputs correct data or not. It is achieved as follows:

  1. Creating of mocked up tables (blank copies of original tables without constraints and data).
  2. Adding rows to the mocked-up tables.
  3. Creating the expected table.
  4. Adding expected results to the expected table.
  5. Running database object (such as view) and putting results into an actual table.

Comparing the expected table with the actual table.TDDD part3 pic1

Implementing Reporting Requirement (ArticlesPerAuthorReport)

In this article, we are going to implement the reporting requirement that was deeply discussed in the previous part of the article.

Reset SQLDevBlogReportTDD Sample Database

We are going to reset SQLDevBlogReportTDD database which was created by us in the previous part of this article and add more data to it.

Sample Database Already Present

If you have already created SQLDevBlogReportTDD database by following walk-through in the second part, then, please, don’t run the scripts in steps 9, 10 and 11.

Creating Sample Database for the first time

If you are creating the database for the first time, then, please, create a blank database SQLDevBlogReportTDD first and then follow all the steps from 1 through 11.

Code
-- -- ================================================================
-- Create date: 2018-07-03 22:52:40
-- Reset SQLDevBlogReportTDD database to add more data
-- ================================================================
/*
-- This step is only required if you are creating the database for the first time
-- Please skip (comment out) this step if you have already created SQLDevBlogReportTDD database
-- Create sample database (SQLDevBlogReportTDD) 
CREATE DATABASE SQLDevBlogReportTDD;
GO
*/
-- Use already created SQLDevBlogReportTDD
USE SQLDevBlogReportTDD;

-- (1) Create Article table in the sample database
IF EXISTS (SELECT
      *
    FROM INFORMATION_SCHEMA.TABLES T
    WHERE T.TABLE_NAME = 'Article')
  DROP TABLE dbo.Article; -- drop table if already exists

CREATE TABLE Article (
  ArticleId INT PRIMARY KEY IDENTITY (1, 1)
 ,CategoryId INT
 ,AuthorId INT
 ,Title VARCHAR(150)
 ,Published DATETIME2
 ,Notes VARCHAR(400)  
);

-- (2) Create Author table in the sample database
IF EXISTS (SELECT
      *
    FROM INFORMATION_SCHEMA.TABLES T
    WHERE T.TABLE_NAME = 'Author')
  DROP TABLE dbo.Author; -- drop table if already exists

CREATE TABLE Author (
  AuthorId INT PRIMARY KEY IDENTITY (1, 1)
 ,Name VARCHAR(40)
 ,RegistrationDate DATETIME2
 ,Notes VARCHAR(400)
);

-- (3) Create Article Category table in the sample database
IF EXISTS (SELECT
      *
    FROM INFORMATION_SCHEMA.TABLES T
    WHERE T.TABLE_NAME = 'Category')
  DROP TABLE dbo.Category; -- drop table if already exists

CREATE TABLE Category (
  CategoryId INT PRIMARY KEY IDENTITY (1, 1)
 ,Name VARCHAR(50)
 ,Notes VARCHAR(400)
);

-- (3) Create Article table in the sample database
IF EXISTS (SELECT
      *
    FROM INFORMATION_SCHEMA.TABLES T
    WHERE T.TABLE_NAME = 'Article')
  DROP TABLE dbo.Article; -- drop table if already exists

CREATE TABLE Article (
  ArticleId INT PRIMARY KEY IDENTITY (1, 1)
 ,CategoryId INT
 ,AuthorId INT
 ,Title VARCHAR(150)
 ,Published DATETIME2
 ,Notes VARCHAR(400)  
);

-- Adding foreign keys for author and article category
ALTER TABLE Article ADD CONSTRAINT FK_Category_CategoryId FOREIGN KEY (CategoryId) REFERENCES Category (CategoryId);
ALTER TABLE Article ADD CONSTRAINT FK_Author_AuthorId FOREIGN KEY (AuthorId) REFERENCES Author (AuthorId);

GO

---- (4) Populating Author table
INSERT INTO Author (Name, RegistrationDate, Notes)
  VALUES ('Peter', '2017-01-01', 'Database Analyst'), -- 1 (Peter)
  ('Adil', '2017-01-02', 'Database and Business Intelligence Developer'), -- 2 (Adil)
  ('Sarah', '2018-01-01', 'Database Analyst Programmer'), -- 3 (Sarah)
  ('Asim', '2018-01-01', 'Database Analyst'), -- 4 (Asim)
  ('Sam', '2017-03-01', 'Database Developer'), -- 5 (Sam)
  ('Akeel', '2017-04-04', 'Database Developer/Tester'), -- 6 (Akeel)
  ('Mark', '2017-05-05', 'Database Analyst Programmer'),-- 7 (Mark)
  ('Zara', '2017-06-01', 'Business Intelligence Consultant'); --8 (Zara)
GO
---- (5) Populating Category table
INSERT INTO Category (Name, Notes)
  VALUES ('Analysis', 'Database Analysis'), -- 1 (Aanlysis)
  ('Development', 'Articles about database development'), -- 2 (Development)
  ('Testing', 'Database testing related articles'), -- 3 (Testing)
  ('DLM', 'Database lifecycle management'); -- 4 (DLM)

-- (6) Populating Article 
INSERT INTO Article (CategoryId, AuthorId, Title, Published, Notes)
  VALUES (1, 1, 'Replicating a problem in SQL', '02-01-2018', ''), -- 1
  (1, 2, 'Modern Database Development Tools', '02-01-2018', ''), -- 2
  (3, 3, 'Test Driven Database Development (TDDD)', '03-01-2018', ''), -- 3
  (3, 1, 'Database Unit Testing Fundamentals', '10-01-2018', ''), -- 4
  (3, 3, 'Unit Testing with tSQLt', '10-01-2018', ''), -- 5
  (2, 5, 'Developing Databases From Scratch', '2017-03-10', ''), -- 6
  (3, 5, 'Advanced Database Unit Testing Frameworks', '2017-03-20', ''), -- 7
  (3, 6, 'Automated Database Unit Testing', '2017-05-01', ''), -- 8
  (3, 6, 'Database Unit Test using MSSQL Tests', '2017-05-06', ''), -- 9
  (1, 6, 'Database Analysis Fundamentals', '2017-05-20', ''), -- 10
  (1, 6, 'In-Depth Database Analysis', '2018-01-01', ''), -- 11
  (2, 7, 'Automating Database Development Tasks', '2017-06-01', ''), -- 12
  (2, 7, 'Database Development with SSDT (SQL Server Data Tools)', '2018-01-01', ''), -- 13
  (2, 7, 'Database Development with DbForge Studio for SQL Server', '2018-02-01', ''), -- 14
  (3, 7, 'Writing Test Cases for Databases', '2018-02-10', ''), -- 15
  (4, 8, 'Understanding DLM (Database Lifecycle Management)', '2017-07-01', ''), -- 16
  (4, 8, 'DLM (Database Lifecycle Management) Tips and Tricks', '2017-07-15', ''), -- 16
  (1, 8, 'Database Analysis in T-SQL', '2018-01-10', ''), -- 17
  (1, 8, 'Advanced Database Analysis Methods', '2018-02-10', ''), -- 18
  (3, 8, 'Version Controlling Database Unit Tests', '2018-03-10', ''), -- 19
  (3, 8, 'Beyond Database Unit Testing', '2018-06-10', ''); -- 20

-- (7) Create view to see list of authors, articles and categories
IF EXISTS (SELECT
      *
    FROM INFORMATION_SCHEMA.VIEWS v
    WHERE V.TABLE_NAME = 'vwAuthors')
  DROP VIEW vwAuthors; -- drop view if already exists
GO

CREATE VIEW dbo.vwAuthors
AS
SELECT
  a1.Title AS ArticleTitle
 ,c.Name AS CategoryName
 ,a.Name AS AuthorName
FROM Author a
INNER JOIN Article a1
  ON a.AuthorId = a1.AuthorId
INNER JOIN Category c
  ON a1.CategoryId = c.CategoryId;
GO

-- (8) Create view ArticlesPerAuthor to see total number of articles per author
IF EXISTS (SELECT
      *
    FROM INFORMATION_SCHEMA.VIEWS v
    WHERE V.TABLE_NAME = 'ArticlesPerAuthorReport')
  DROP VIEW ArticlesPerAuthorReport;  -- drop view if already exists
GO

CREATE VIEW dbo.ArticlesPerAuthorReport
AS
SELECT
  a.Name AS [Author]
 ,COUNT(a1.ArticleId) AS [Total Articles]
FROM Author a
INNER JOIN Article a1
  ON a.AuthorId = a1.AuthorId
GROUP BY a.Name;
GO

-- NOTE: Please uncomment and run Steps 9, 10 and 11 only if tSQLt is not installed and unit tests are not written yet
/*
-- (9) Install tSQLt unit testing framework on SQLDevBlogReportTDD (if not installed)

-- (10) Create unit test to check ArticlesPerAuthorReport exists
CREATE PROCEDURE ArticlesPerAuthorReport.[test to check ArticlesPerAuthorReport exists]
AS
BEGIN
  --Assemble

  --Act

  --Assert
  EXEC tSQLt.AssertObjectExists @ObjectName = N'ArticlesPerAuthorReport'
                               ,@Message = N''

END;
GO

-- (11) Create unit test check ArticlesPerAuthorReport outputs correct
CREATE PROCEDURE ArticlesPerAuthorReport.[test to check ArticlesPerAuthorReport outputs correct]
AS
BEGIN
  --Assemble
  --  Create mocked up tables (blank copies of original tables without constraints and data)
  EXEC tSQLt.FakeTable @TableName = N'Author'
                      ,@SchemaName = N'dbo'

  EXEC tSQLt.FakeTable @TableName = N'Article'
                      ,@SchemaName = N'dbo'                      

  EXEC tSQLt.FakeTable @TableName = N'Category'
                      ,@SchemaName = N'dbo'                      

  -- Add rows to the mocked up tables
  INSERT INTO Author (AuthorId,Name, RegistrationDate, Notes)
  VALUES (1,'Zak', DATEFROMPARTS(2017,01,01), 'Database Author'),
    (2,'Akeel',DATEFROMPARTS(2018,01,01),'Business Intelligence Author')
  
  INSERT INTO Category (CategoryID,Name, Notes)
  VALUES (1,'Database Development', '-'),
  (2,'Business Intelligene','-');

  INSERT INTO Article (ArticleId,CategoryId, AuthorId, Title, Published, Notes)
  VALUES (1,1, 1, 'Advanced Database Development', DATEFROMPARTS(2017,02,01),'10K Views'),
  (1,1, 1, 'Database Development with Cloud Technologies', DATEFROMPARTS(2017,02,01),'5K Views'),
  (1,1, 1, 'Developing Databases with Modern Tools', DATEFROMPARTS(2017,03,01),'20K Views'),
  (1,2, 2, 'Business Intelligence Fundamentals', DATEFROMPARTS(2017,02,01),'10K Views'),
  (1,2, 2, 'Tabular Models', DATEFROMPARTS(2017,02,01),'50K Views')

  -- Create expected table
  CREATE TABLE ArticlesPerAuthorReport.Expected
  (Author VARCHAR(40),[Total Articles] int)  

  -- Add expected results into expected table
  INSERT INTO ArticlesPerAuthorReport.Expected (Author, [Total Articles])
  VALUES ('Zak', 3), ('Akeel',2);


  --Act
  --  Run ArticlesPerAuthorReport object (view) and put results into an actual table
  SELECT * INTO ArticlesPerAuthorReport.Actual FROM ArticlesPerAuthorReport apar
  

  --Assert
  --  Compare the expected and actual tables
  EXEC TSQLT.AssertEqualsTable @Expected = N'ArticlesPerAuthorReport.Expected'
                              ,@Actual = N'ArticlesPerAuthorReport.Actual'
                              

END;
GO
*/

Data Check by Running View vwAuthors

In order to make sure of adding enough data to our sample database, retrieve data from the view vwAuthors to see all the records:

TDDD part3 pic2

Please, note that I am using dbForge Studio for SQL Server in this article so only output look may differ in case you run the same code in SSMS (SQL Server Management Studio). Nevertheless, there is no difference because scripts and their results are similar.

Running the unit tests

Run the database unit tests that we created originally (in previous part or just now) through test-driven database development:

TDDD part3 pic3

Congratulations, both tests have passed that means the desired database object is capable of meeting the reporting requirement to view articles per author.

Create Database Report Based on ArticlesPerAuthorsReport Object

You can create a database report in a number of ways (such as using Report Builder, creating Report Server Project in Visual Studio Data Tools or using dbForge Studio for SQL Server).

In this article, we are using dbForge Studio for SQL Server for report creating.

To proceed further, please, click on “New” from File Menu and then select “Data Report”:

Click “Standard Report” as shown below:

TDDD part3 pic5

Select “Simple Table\View” as “Data Type”:

TDDD part3 pic6

Add the base object (ArticlesPerAuthorReport) which is view in our case:

TDDD part3 pic7

Add the required fields:TDDD part3 pic8

No grouping is required at this point so proceed further by clicking “Next”:

TDDD part3 pic9

Select “Layout” and “Orientation” of the data report:

TDDD part3 pic10

Finally, add title “Articles per Author Report” to the report and click “Finish”:

TDDD part3 pic11

Next, adjust the formatting of the report as per requirement:

TDDD part3 pic12

Click “Preview” to see the database report:

TDDD part3 pic13

Save the report as “ArticlesPerAuthorReport”. Finally, database report has been created due to the business requirements.

Use of Setup Procedure

While writing database unit tests via using tSQLt, some test code is repeated quite often. It can be defined in a setup procedure and reused afterward in all the unit tests of that particular test class.

According to Dave Green, each test class can have only one setup procedure that runs automatically before the unit tests of that class processes.

Almost all the test code, which is written under Assemble (section), can be put under setup procedure to avoid code duplication.

For instance, creating mocked tables along with expected table and adding data to mocked tables and then to the expected table can be easily defined under the setup procedure and that it can be reused afterward.

Creating Setup Procedure to avoid test code duplication

Create a stored procedure in SQLDevBlogTDD as follows:

-- (12) Use of Setup Procedure to avoid repeating common test code
CREATE PROCEDURE ArticlesPerAuthorReport.Setup 
AS 
BEGIN
  --Assemble
  --  Create mocked up tables (blank copies of original tables without constraints and data)
  EXEC tSQLt.FakeTable @TableName = N'Author'
                      ,@SchemaName = N'dbo'

  EXEC tSQLt.FakeTable @TableName = N'Article'
                      ,@SchemaName = N'dbo'                      

  EXEC tSQLt.FakeTable @TableName = N'Category'
                      ,@SchemaName = N'dbo'                      

  -- Add rows to the mocked up tables
  INSERT INTO Author (AuthorId,Name, RegistrationDate, Notes)
  VALUES (1,'Zak', DATEFROMPARTS(2017,01,01), 'Database Author'),
    (2,'Akeel',DATEFROMPARTS(2018,01,01),'Business Intelligence Author')
  
  INSERT INTO Category (CategoryID,Name, Notes)
  VALUES (1,'Database Development', '-'),
  (2,'Business Intelligene','-');

  INSERT INTO Article (ArticleId,CategoryId, AuthorId, Title, Published, Notes)
  VALUES (1,1, 1, 'Advanced Database Development', DATEFROMPARTS(2017,02,01),'10K Views'),
  (1,1, 1, 'Database Development with Cloud Technologies', DATEFROMPARTS(2017,02,01),'5K Views'),
  (1,1, 1, 'Developing Databases with Modern Tools', DATEFROMPARTS(2017,03,01),'20K Views'),
  (1,2, 2, 'Business Intelligence Fundamentals', DATEFROMPARTS(2017,02,01),'10K Views'),
  (1,2, 2, 'Tabular Models', DATEFROMPARTS(2017,02,01),'50K Views')

  -- Create expected table
  CREATE TABLE ArticlesPerAuthorReport.Expected
  (Author VARCHAR(40),[Total Articles] int)  

  -- Add expected results into expected table
  INSERT INTO ArticlesPerAuthorReport.Expected (Author, [Total Articles])
  VALUES ('Zak', 3), ('Akeel',2)
END;
GO

TDDD part3 pic14

Now remove the test code, which we have already written in setup procedure, from the unit test to check ArticlesPerAuthorReport outputs correct as follows:

-- (11) Create unit test check ArticlesPerAuthorReport outputs correct
ALTER PROCEDURE ArticlesPerAuthorReport.[test to check ArticlesPerAuthorReport outputs correct]
AS
BEGIN
  --Assemble (Test Code written in Setup Procedure)
  -- Create mocked up tables (blank copies of original tables without constraints and data)
  -- Add rows to the mocked up tables
  -- Create expected table
  -- Add expected results into expected table
  
  --Act
  --  Run ArticlesPerAuthorReport object (view) and put results into an actual table
  SELECT * INTO ArticlesPerAuthorReport.Actual FROM ArticlesPerAuthorReport apar
  

  --Assert
  --  Compare the expected and actual tables
  EXEC TSQLT.AssertEqualsTable @Expected = N'ArticlesPerAuthorReport.Expected'
                              ,@Actual = N'ArticlesPerAuthorReport.Actual'
                              
END;
GO

Running All Unit Tests to Check Setup Procedure Working

Run the unit tests and see the results:

TDDD part3 pic15

Congratulations, the unit tests have run successfully despite the fact we are using setup procedure to run some part of test code before these unit tests are running.

Please, stay in touch because more about test-driven database development will come in Part-4 (soon).

Things to Do

You can now easily create a database report based on the object which was created through test-driven development in order to meet the business requirement.

  1. Please, try to meet another reporting requirement to show articles per year while using TDDD.
  2. Please, try to do the same to show articles by category while following test-driven database development.
  3. Please, while using TDDD, try to meet a reporting requirement to view, which authors have not written any articles.
  4. Please, try to meet a business requirement to search article by title via using TDDD.

Useful tool:

dbForge Unit Test – an intuitive and convenient GUI for implementing automated unit testing in SQL Server Management Studio.

Haroon Ashraf