Tsqlt

De Banane Atomic
Aller à la navigationAller à la recherche

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

Tsql.svg
-- mock table: duplicate an existing table with no data and no constraint
EXEC tSQLt.FakeTable 'dbo.MyTable';
-- 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.

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

Spy procedure

Tsql.svg
-- 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

Tsql.svg
-- 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

Tsql.svg
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
Tsql.svg
-- 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

Tsql.svg
-- 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

Tsql.svg
EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;
GO

2. Set TRUSTWORTHY on the database

Tsql.svg
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

Tsql.svg
-- 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.
Tsql.svg
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.
Tsql.svg
-- changer le propriétaire de la bdd
EXEC dbo.sp_changedbowner @loginame = N'sa'