Tsqlt

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

• Quick start
• Tutorial

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.'; -- test if a table is empty EXEC tsqlt.AssertEmptyTable @TableName = '#actual';

Compare 2 tables

-- create the #expected table from the #actual table SELECT TOP (0) * INTO #expected FROM #actual; -- insert the expected data INSERT INTO #expected (Col1, Col2) VALUES (1, '1'), (2, '2'), (3, '3'); -- compare the tables EXEC tsqlt.AssertEqualsTable @Expected = '#expected', @Actual = '#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'