Saturday, May 16, 2015

Unit Testing Database with tSQLt

Unit Testing Database with tSQLt 


Unit testing is integral part of application development. Developer can test their code blocks using Unit Test Frameworks in context to get expected output.


The most of application developer use test project to develop unit test for application. Almost all IDE provide unit test project template.


H'v you tried unit test case in database. tSQLt (Database Unit Testing Framework for SQL Server Database) is popular one. tSQLt is written in T-SQL, Its very easy for a database developer to install/configure for a database and use it. you can easily define your own test case and test condition with tSQLt Framework.

Configuration/ Installation of tSQLt


Download & Enable CLR
  • Download tSQLt (tSQlt.zip) from
http://sourceforge.net/projects/tsqlt/files/latest/download?source=filesor from https://docs.google.com/uc?authuser=0&id=0B9vkinxiudBGVWtRNDcyRWthQ1k&export=download
  • Extract tSQLt.zip
  • Enable CLR for SQL Server 
    By default the clr is not enabled for database server . So require to enable.
    you can SetClrEnabled.sql script from extracted files. Sql Server is now prepared for tSQLt Framework

Installation of tSQLt in a database for which we wants to create unit tests.


  • Create a database or use existing database. My database to apply unit test is TryTestCaseApproach.


Script:

USE master;
GO

IF DB_ID('TryTestCaseApproach') IS NOT NULL
DROP DATABASE TryTestCaseApproach
GO

CREATE DATABASE TryTestCaseApproach
Go
USE TryTestCaseApproach
GO

CREATE TABLE dbo.MYTestRecord
(
RecordID INT PRIMARY KEY,
Price MONEY NOT NULL,
Point SMALLINT NOT NULL
)
GO

ALTER TABLE MYTestRecord ADD CONSTRAINT check_Point_min CHECK (Point > 5)
GO
INSERT INTO MYTestRecord VALUES (101, 695.95, 35),(102, 735.95, 25),(103, 694.95, 45),(104, 737.95, 15)


GO




  • Install tSQLt in database TryTestCaseApproach

Make database trustworthy.
ALTER DATABASE TryTestCaseApproach SET TRUSTWORTHY ON

  • Run (tSQLt.class.sql) in database  (TryTestCaseApproach).



Now Test Framework is configured for the database. Open object explorer of SSMS(SQL Server Management Studio). You will see there are number of database objects in your database having schema tSQLt . they are tables,views,stored procedure and functions (both tabular & scalar). These objects will helps you to create unit test and run your unit test.

All tSQLt objects are visible to you , so you can add your own case in defaults case type of tSQLt.


Create Unit Test

Before your start to create  a test case , you need to create class. NewTestClass stored procedure used for creating test class. this stored procedure is in tSQLt Test Framework.

My Test Class is TestMyExp ,The following command will create Test class for you.

EXEC tSQLt.NewTestClass 'TestMyExp';

after executing this command TestMyExp Test Class ready to create test cases.


Create a simple scalar function (say actual function that is being used in application).
 IF OBJECT_ID('AddSalesTax', 'FN') IS NOT NULL
DROP FUNCTION AddSalesTax;
GO CREATE FUNCTION AddSalesTax(@amt MONEY)
RETURNS MONEY
AS BEGIN
RETURN (@amt * .095) + @amt
END;
GO

  I wants to call this function inside stored procedure (Test Case stored procedure) , so I am creating a stored procedure.

IF OBJECT_ID('TestMyExp.TestAddSalesTax', 'P') IS NOT NULL
DROP PROCEDURE TestMyExp.TestAddSalesTax;
GO CREATE PROCEDURE TestMyExp.TestAddSalesTax
ASBEGIN  DECLARE @total MONEY
  SELECT @total = dbo.AddSalesTax(10);
   EXEC tSQLt.AssertEquals 10.95, @total;
END;
GO


Run Test Case.

Execute this stored procedure using a Test case run in order to test function AddSalesTax.
 tSQLt.Run stored procedure of the Test Framework take stored procedure name as parameter

EXEC tSQLt.Run 'TestMyExp.TestAddSalesTax';

Test Result.

  Test Successed

If  stored procedure altered .

Alter PROCEDURE TestMyExp.TestAddSalesTax
ASBEGIN  DECLARE @total MONEY
  SELECT @total = dbo.AddSalesTax(10);
   EXEC tSQLt.AssertEquals 10.01, @total;
END;
GO


  Run Test :-

    Test failed


Same way as Stored procedure TestAddSalesTax, can added numbers of unit test. & can test each & every input parameter / expected value for a stored procedure/function.



Unit Testing is not suitable for every situation . We can not test each & every data type , input parameter , output result set and expected value. tSQLt is very useful tool to Unit Testing for SQL Server database. As per need of project , you can use this tool.

~
Sunit Kanyan
technocrats@sunitkanyan.in