Description

tSQLt est un framework de tests unitaire pour T-SQL.
Les tests sont des stored procedures regroupé par schema (test class). Un schema correspond à une FN ou une USP.
Chaque test est exécuté dans la cadre d'une transaction, ainsi les tests ne modifient pas la bdd.

Liens

Assemble

-- mock table: duplicate an existing table with no data and no constraint
EXEC tSQLt.FakeTable 'dbo.MyTable', @ComputedColumns = 1;
-- insert data in the mocked table
INSERT INTO dbo.MyTable (Column1, Column2) VALUES (1, 'Text');

-- mock FN
EXEC tSQLt.RemoveObject 'dbo.FN_MyFunc';
EXEC ('CREATE FUNCTION dbo.FN_MyFunc (@Date DATE) RETURNS datetime AS 
        BEGIN RETURN ''2019-01-01 00:00:00''; END;');

Setup routine

Créé un SP qui permet de centraliser la préparation des données.
Cette SP est appelée automatiquement au début de tous les tests de MyTestClass.

CREATE PROCEDURE MyTestClass.SetUp
AS
BEGIN
    -- assemble
END;
GO

Spy procedure

-- mock SP
EXEC tSQLt.SpyProcedure @ProcedureName = 'dbo.USP_MyProc'
                       ,@CommandToExecute = 'set @MyVar = ''value'' '
-- ensure that the format of the expected table is consistent with what the SpyProcedure will return
SELECT TOP 0 * INTO MyTable.Expected FROM dbo.USP_MyProc_SpyProcedureLog

Fake function

-- create a fake FN
CREATE FUNCTION dbo.FN_Fake_MyFunction (
    @Param1 INT
)
RETURNS DECIMAL(10,4)
AS
BEGIN
  RETURN 1234.5678;
END;
GO

-- mock FN
EXEC tSQLt.FakeFunction 'dbo.FN_MyFunction', 'dbo.FN_Fake_MyFunction';

Act

CREATE TABLE #actual (Col1 INT NOT NULL, Col2 INT NOT NULL);

INSERT INTO #actual
EXEC dbo.MyStoredProcedure @P_Arg1 = 1,
                           @P_Arg2 = '2019-01-01'

-- store result of the SP into the @result table-valued variable (need to be created)
DECLARE @result AS test.T_MyStoredProcedureTable;

INSERT INTO @result
EXEC dbo.MyStoredProcedure @P_Arg1 = 1,
                           @P_Arg2 = '2019-01-01'

-- then create the #actual from @result
SELECT col1, col2
INTO #actual
FROM @result;

Assert

By default, the test passes.
If there are multiple assertions:

the test stops when the first assertion failed
the test is considered as valid if all the assertion are valid

-- test if a table exists
EXEC tSQLt.AssertObjectExists @ObjectName = N'dbo.MyTable',
                              @Message = N'The object dbo.MyTable does not exist.';

-- compare 2 tables
EXEC tsqlt.AssertEqualsTable @Expected = '#expected', @Actual = '#actual';

-- test if a table is empty
EXEC tsqlt.AssertEmptyTable @TableName = '#actual';

-- test if the tables have the same values and the same value types
EXEC tSQLt.AssertResultSetsHaveSameMetaData 
    @expectedCommand = N'Select * from [MyTable].Expected',
    @actualCommand = N'Select * from [MyTable].Actual'

Create new unit test

SQL Test → +

Test name: With input parameters, returns result
Database: select the database on which you want to do the unit test
Test Class: use the name of the SP or the FN

SQL Commands

-- list the test classes (schemas)
select * from tsqlt.TestClasses

-- run all the unit tests
exec tsqlt.runall
-- run all the unit tests of a specific test class
exec tsqlt.run 'MyTestClass'
-- run a specific unit test
exec tsqlt.run '[MyTestClass].[MyUnitTest]'

-- create a test class
EXEC tSQLt.NewTestClass @ClassName = N'MyTestClass' 
GO

-- create a unit test
CREATE PROC MyTestClass.[test MyFunction is called correctly]
AS
-- Assemble
-- Act
--Assert
GO

Installation

1. Activer l'intégration CLR

EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;
GO

2. Set TRUSTWORTHY on the database

DECLARE @cmd NVARCHAR(MAX);
SET @cmd='ALTER DATABASE ' + QUOTENAME(DB_NAME()) + ' SET TRUSTWORTHY ON;';
EXEC(@cmd);
GO

3. Install tSQLt framework on the database
RedGate SQL Test → Add database

-- display version of tSQLt
SELECT * FROM tSQLt.Info()

Erreurs

CREATE ASSEMBLY for assembly 'tSQLtCLR' failed

CREATE ASSEMBLY for assembly 'tSQLtCLR' failed because assembly 'tSQLtCLR' is not authorized for PERMISSION_SET = EXTERNAL_ACCESS. The assembly is authorized when either of the following is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission.

ALTER DATABASE Pricing SET TRUSTWORTHY ON;

Could not obtain information about Windows NT group/user

Could not obtain information about Windows NT group/user 'DOMAIN\username', error code 0x54b.

-- changer le propriétaire de la bdd
EXEC dbo.sp_changedbowner @loginame = N'sa'

Tsqlt

Sommaire