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

Assemble

-- create expected table IF object_id('MyTable.Expected') IS NOT NULL DROP TABLE MyTable.Expected CREATE TABLE MyTable.Expected (Column1 INT, Column2 VARCHAR(100); INSERT MyTable.Expected VALUES (1, 'Text'); -- mock table: duplicate an existing table with no data and no constraint EXEC tSQLt.FakeTable @tablename = N'dbo.MyTable'; INSERT 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

Act

INSERT MyTable.Actual EXEC dbo.MyProc @Arg1 = 1, @Arg2 = '2019-01-01'

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 = N'MyTable.Expected', @Actual = N'MyTable.Actual', @FailMsg = N'The expected data was not returned.'; -- test if a table is empty EXEC tsqlt.AssertEmptyTable @TableName = N'MyTable.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' -- 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'