Postări
Installing tSQLT
Installing tSQLT is pretty simple. You can find the .zip file containing two SQL script files here http://sourceforge.net/projects/tsqlt/ . After downloading the zip and extracting it, run the two .sql files on your desired database in this order: SetClrEnabled.sql, tSQLt.class.sql. That’s it, tSQLT has been installed.
There is also a tutorial here: http://www2.sqlity.net/articles/tSQLtTutorial and an user guide here: http://www2.sqlity.net/tsqlt/tsqlt-user-guide
After running tSQLt.class.sql, you may get the following warnings:
Compiled at 2011-02-27 12:57:27.803
The module 'DefaultResultFormatter' depends on the missing object 'tSQLt.TableToText'. The module will still be created; however, it cannot run successfully until the object exists.
The module 'RunTestClass' depends on the missing object 'tSQLt.Run'. The module will still be created; however, it cannot run successfully until the object exists.
The module 'Run' depends on the missing object 'tSQLt.private_RunTestClass'. The module will still be created; however, it cannot run successfully until the object exists.
The module 'SetFakeViewOff' depends on the missing object 'tSQLt.private_SetFakeViewOff_SingleView'. The module will still be created; however, it cannot run successfully until the object exists.
You can ignore them for now and take a look at your database, and under stored procedures you should see a whole bunch using the tsql schema:
tSQLt created those, along with a view, some tables, table-valued functions, scalar-valued functions and other objects, including a CLR assembly.
Testing with tSQLt
Let’s start with a simple test. Testing a scalar-valued function that returns the last working day of previous month.
Here is the function we want to test:
CREATE FUNCTION [dbo].[fnGetLastWorkingDayOfPreviousMonth]
(
-- Add the parameters for the function here
)
RETURNS datetime
AS
BEGIN
declare @lastdayofpreviousmonth datetime
declare @lastworkingdayofpreviousmonth datetime
if(@@DATEFIRST=7)
begin
select @lastdayofpreviousmonth = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
if(DATEPART(dw, @lastdayofpreviousmonth) between 2 and 6)
set @lastworkingdayofpreviousmonth= @lastdayofpreviousmonth
else
set @lastworkingdayofpreviousmonth = DATEADD(day, -(1+DATEPART(dw, @lastdayofpreviousmonth)%@@DATEFIRST), @lastdayofpreviousmonth)
end
return @lastworkingdayofpreviousmonth
END
First thing to do is set up a test class. This is essentially a schema we assign to all our tests. Using this class, we can run all tests inside it, or choose to run them selectively.
EXEC tSQLt.NewTestClass 'DemoTSQLT'; GO
Creating a test for our function is as easy as creating a stored procedure having the schema 'DemoTSQLT'. tSQLt provides a nice way to test if two values are equal : tSQLt.assertEquals. The test for our function would look like this:
Create procedure [DemoTSQLT].[test last working day in previous month] AS BEGIN declare @expectedResult datetime set @expectedResult = cast('01-31-2011' as datetime) declare @actualResult datetime select @actualResult = dbo.fnGetLastWorkingDayOfPreviousMonth() exec tSQLt.assertEquals @expectedResult, @actualResult END GO
Because the function to test was designed to include the time portion of the date, effectively making ‘2011-01-31 23:59:59.000’ the last working day of previous month, this test will fail spectacularly. To run the test and see it fail, we can simply do the following:
tsqlt.Run 'DemoTSQLT'
GO
The output of this test can be seen bellow:
Here, we ran all tests in ‘DemoTSQLT ‘ class. To run a specific test, change tsqlt.Run to tsqlt.RunTest like this:
tsqlt.RunTest '[DemoTSQLT].[test last working day in previous month]'
GO
Let’s change the test a bit: Where we cast ‘01-31-2011’ as datetime, let’s change that to ‘2011-01-31 23:59:59.000'. Running the test (tsqlt.Run ‘DemoTSQLT’) now will have this result:
+---------------------+ |Test Execution Sumary| +---------------------+ |No|Test Case Name |Result | +--+-----------------------------------------------------+-------+ |1 |[DemoTSQLT].[test last working day in previous month]|Success| ----------------------------------------------------------------------------- Test Case Summary: 1 test case(s) executed, 1 succeeded, 0 failed, 0 errored. -----------------------------------------------------------------------------
Test succeeded, developer happy, function is ready to be used.
There you have it. There is a lot more to tSQLt to be sure and I will try to cover it as much as possible in the next parts of the series. Have fun and happy testing.
Cheers,
Radu
