Testy jednostkowe zapytań SQL z użyciem tSQLt

Testowanie, to w świecie programistycznym obecnie standard. Dla wielu języków programowania istnieją biblioteki i frameworki wspierające pisanie testów jednostkowych. Jak się okazuje, SQL nie jest również odosobniony. Dzisiaj chciałem zaprezentować Wam testy jednostkowe zapytań SQL z użyciem tSQLt.

Zanim przejdziesz dalej, to polecam zacząć od wprowadzenia, które dostępne jest w w tym miejscu

Czym jest tSQLt?

tSQLt to open source’owy framework dla środowiska Microsoft SQL Server. Dzięki temu jest ogólnodostępny i tylko Twoje chęci (albo ich brak) mogą powstrzymać Cię przed jego używaniem. Co go wyróżnia?

Dbałość o izolację testów od danych – wykonanie testu nie generuje więc żadnych zmian w strukturze lub danych, ponieważ działa na transakcji – każde wykonanie testu kończy rollback. Dodatkowo można utworzyć „wirtualne” tabele, widoki czy procedury na podstawie istniejących obiektów w bazie.
Testy tworzone są w ramach schematów bazy – pozwala to zgrupować i zorganizować pisane testy.

W jaki sposób korzysta się z tSQLt? To co nam potrzebne to edytor kodu z którego odpalimy zapytania stworzone w SQLu. Możesz korzystać z najpopularniejszego Microsoft SQL Server Management Studio, ale ja osobiście preferuje Viusal Studio Code. Główne powody są dwa:

Możliwość dowolnego budowania struktury katalogów projektów w ramach workspace’a.
Współpraca z Gitem, czyli wsparcie kontroli wersji.

Instalacja tSQLt polega na pobraniu paczki skryptów i odpalenie najpierw PrepareServer.sql, a następnie tSQLt.class.sql. Co ważne, framework instaluje się w konkretnej bazie danych, dlatego koniecznie trzeba przygotować sobie bazę, będącą środowiskiem testowym.

Pisanie testów z tSQLt

Po przygotowaniu do pracy z tSQLt w pierwszej kolejności tworzymy klasę testów, czyli „katalog” dla naszej grupy testów.

EXEC tSQLt.NewTestClass 'FirstLearningUnitTests';

Najpierw omówię podstawową strukturę i sposób pisania testu na prostym przykładzie. Załóżmy, że chcemy testować funkcję, która zwróci nam kwotę brutto do podanej w parametrze wartości netto oraz stawce VAT.

Co ważne, spróbujemy zadziałać zgodnie z TDD – najpierw napiszemy test, a potem zaimplementujemy funkcję, która jest sprawdzania. Powiedzmy, że refaktoryzację w tym przypadku sobie wyjątkowo odpuścimy.

Test jednostkowy z użyciem tSQLt to nic innego jak procedura, taka jak poniżej:

CREATE PROCEDURE [Klasa Testów].[test Nazwa Testu]
AS
BEGIN
-- ARRANGE --

-- ACT --

-- ASSERT --
END

Jako schemat dla procedury podajemy utworzoną wcześniej klasę testów. Przypisujemy w ten sposób test do konkretnej grupy. Następnie nazywamy ją tak jak chcemy nazwać nasz test. Ważne jest to, że każda procedura będąca testem musi zaczynać się od „test”, bo tylko takie procedury mechanizm frameworka bierze pod uwagę.

Co do nazewnictwa to proponuje zastosować schemat:

[test] [Nazwa funkcji] When [Parametry wejściowe/testowane] Then [Oczekiwany wynik]

W naszym przykładzie będzie to:

CREATE PROCEDURE [FirstLearningUnitTests].[test ReturnGrossValue When Net Value 100 And Tax Rate 23 Then Gross Value 123]
AS
BEGIN
-- ARRANGE --

-- ACT --

-- ASSERT --
END

Procedury w SQL Serverze mają ograniczenie długości nazwy do 128 znaków. Jest to całkiem sporo, ale należy pamiętać, by nie tworzyć elaboratów.

Następnie zawartość testu podzielona jest na 3 etapy:

Arrange, czyli przygotowanie testu. Deklarujemy tutaj dane wejściowe.
Act – część, w której wykonujemy testowany fragment kodu. W naszym wypadku będzie to funkcja, z której pobierzemy zwracany wynik.
Assert – Tutaj deklarujemy wynik oraz porównujemy go z tym co zwrócił testowany kod. Wynik assercji definiuje to, czy test zakończy się sukcesem.

Piszemy pierwszy test

Nasz test wygląda tak.

CREATE PROCEDURE [FirstLearningUnitTests].[test ReturnGrossValue When Net Value 100 And Tax Rate 23 Then Gross Value 123]
AS
BEGIN

-- ! ARRANGE --

DECLARE @NetValue DECIMAL(18,2) = 100
DECLARE @TaxRate SMALLINT = 23

-- ! ACT --

DECLARE @Actual DECIMAL(18,2) = (SELECT [dbo].[ReturnGrossValue](@NetValue,@TaxRate))

-- ! ASSERT --

DECLARE @Expected DECIMAL(18,2) = 123
                                               
EXEC tSQLt.AssertEquals @Expected, @Actual;

END

W pierwszej kolejności deklarujemy dane wejściowe – wartość netto oraz stawkę VAT. Następnie do zmiennej Actual zapisujemy wynik funkcji. Na koniec deklarujemy wynik oraz uruchamiamy porównanie za pomocą:

EXEC tSQLt.AssertEquals @Expected, @Actual;

Teraz możemy uruchomić nasz test przy użyciu:

EXEC tSQLt.Run '[FirstLearningUnitTests].[test ReturnGrossValue When Net Value 100 And Tax Rate 23 Then Gross Value 123]'

Jednak póki co, dostaniemy takie komunikaty:

Przecież jeszcze nie zaimplementowaliśmy naszej testowanej funkcji więc siłą rzeczy mamy błąd. Zmieńmy ten stan rzeczy pisząc funkcję, która wykona stosownych obliczeń.

CREATE FUNCTION [dbo].[ReturnGrossValue]
(
	@NetValue DECIMAL(18,2), @TaxRate int
)
RETURNS DECIMAL(18,2)
AS
BEGIN

DECLARE @Result DECIMAL(18,2)

SET @Result = @NetValue + @NetValue * (@TaxRate/100)

RETURN @Result

END

Teraz gdy uruchomimy test jest lepiej. Ale…

…nie zakończył się on powodzeniem. Powinniśmy otrzymać 123 a z jakiegoś powodu jako rezultat dostaliśmy 100. To jest właśnie przykład pokazujący istotę pisania testów. Pozornie poprawny kod zwraca nam bzdury, a my to wykrywamy w momencie doprowadzania do pomyślnego wykonania testu. Funkcja jest wręcz trywialna, ale zapomnieliśmy o jednej rzeczy. Dzielenie dwóch integerów w SQL Serverze zwraca zawsze liczbę całkowitą, nawet jak powinna wyjść wartość po przecinku. Dlatego po takiej drobnej korekcie:

CREATE FUNCTION [dbo].[ReturnGrossValue]
(
	@NetValue DECIMAL(18,2), @TaxRate int
)
RETURNS DECIMAL(18,2)
AS
BEGIN

DECLARE @Result DECIMAL(18,2)

SET @Result = @NetValue + @NetValue * (CAST(@TaxRate AS FLOAT)/100)

RETURN @Result

END

Wynik naszego testu jest już prawidłowy:

Test z izolacją danych

Spróbujmy teraz bardziej praktycznego rozwiązania. Napiszemy test do procedury, która używa naszej funkcji do wyliczenia kwoty brutto oraz zapisuje ten wynik w tabeli. Zacznijmy ponownie od testu.

CREATE PROCEDURE [FirstLearningUnitTests].[test SaveGrossValueForOrderItem When Order No 20200101 And Item No 1 Then Gross Value 736.77]
AS
BEGIN

-- ! ARRANGE --

DECLARE @OrderNo int = 20200101
DECLARE @ItemNo int = 1

EXEC tSQLt.FakeTable 'dbo.OrderItems'

INSERT INTO [dbo].[OrderItems] ([ORDER_NO], [ORDER_ITEM], [PRODUCT_CODE], [PRODUCT_NAME],[NET_VALUE], [TAX_RATE], [GROSS_VALUE])
VALUES (@OrderNo, @ItemNo, 'EX2020-T1', 'Example Product Model 2020-T1', 599.00, 23, NULL)

-- ! ACT --

EXEC [dbo].[SaveGrossValueForOrderItem] @OrderNo, @ItemNo

IF OBJECT_ID('actual') IS NOT NULL DROP TABLE actual;

SELECT [ORDER_NO], [ORDER_ITEM], [PRODUCT_CODE], [PRODUCT_NAME],[NET_VALUE], [TAX_RATE], [GROSS_VALUE]
INTO actual
FROM
[dbo].[OrderItems] WHERE [ORDER_NO] = @OrderNo AND [ORDER_ITEM] = @ItemNo

-- ! ASSERT --

IF OBJECT_ID('expected') IS NOT NULL DROP TABLE expected;

CREATE TABLE expected
(ORDER_NO int,
ORDER_ITEM int,
PRODUCT_CODE NVARCHAR(20),
PRODUCT_NAME NVARCHAR(40),
NET_VALUE DECIMAL(18,2),
TAX_RATE int,
GROSS_VALUE DECIMAL(18,2) NULL)

INSERT INTO expected ([ORDER_NO], [ORDER_ITEM], [PRODUCT_CODE], [PRODUCT_NAME],[NET_VALUE], [TAX_RATE], [GROSS_VALUE])
VALUES
(
    20200101, 1, 'EX2020-T1', 'Example Product Model 2020-T1', 599.00, 23, 736.77
)

EXEC tSQLt.AssertEqualsTable expected, actual;

END
GO

To co jest bardzo istotne podczas pisania testów jednostkowych tSQLt, to izolacja od danych w bazie. Testy muszą być napisane tak, by sprawdzały logikę biznesową bez wykorzystywania istniejących w tabelach informacji. Te przecież mogą się zmieniać i wpływać w ten sposób na wynik testu. By to osiągnąć z pomocą przychodzą nam tzw. FakeTables. Wykonując na etapie przygotowania testu kod:

EXEC tSQLt.FakeTable '[dbo].[OrderItems]'

Tworzymy pustą kopię wskazanej tabeli bez ustawionych na niej ograniczeń (constraints), która w momencie wykonywania testu nadpisuje stan danej tabeli. Ze względu na to, iż testy w tSQLt działają na transakcjach, po wykonaniu tabela wraca do swojej oryginalnej formy bez śladu testowania.

Po utworzeniu FakeTable uzupełniamy ją danymi potrzebnymi do naszego testu.

INSERT INTO [dbo].[OrderItems] ([ORDER_NO], [ORDER_ITEM], [PRODUCT_CODE], [PRODUCT_NAME],[NET_VALUE], [TAX_RATE], [GROSS_VALUE])
VALUES (@OrderNo, @ItemNo, 'EX2020-T1', 'Example Product Model 2020-T1', 599.00, 23, NULL)

W tabeli OrderItems brakuje nam wartości brutto i będziemy ją uzupełniać za pomocą procedury. Wykonujemy ją, oraz sprawdzamy jaki wyniki zapisała w tabeli. Wykorzystamy tutaj taką strukturę jak tabelę actual, którą możemy załadować rezultatem uruchomienia procedury.

EXEC [dbo].[SaveGrossValueForOrderItem] @OrderNo, @ItemNo

IF OBJECT_ID('actual') IS NOT NULL DROP TABLE actual;

SELECT [ORDER_NO], [ORDER_ITEM], [PRODUCT_CODE], [PRODUCT_NAME],[NET_VALUE], [TAX_RATE], [GROSS_VALUE]
INTO actual
FROM
[dbo].[OrderItems] WHERE [ORDER_NO] = @OrderNo AND [ORDER_ITEM] = @ItemNo

Na koniec w assercji przygotowywany jest oczekiwany zestaw danych. Utworzyłem na potrzeby testu tabelę expected, która zawierać będzie pożądany efekt.

IF OBJECT_ID('expected') IS NOT NULL DROP TABLE expected;

CREATE TABLE expected
(ORDER_NO int,
ORDER_ITEM int,
PRODUCT_CODE NVARCHAR(20),
PRODUCT_NAME NVARCHAR(40),
NET_VALUE DECIMAL(18,2),
TAX_RATE int,
GROSS_VALUE DECIMAL(18,2) NULL)

INSERT INTO expected ([ORDER_NO], [ORDER_ITEM], [PRODUCT_CODE], [PRODUCT_NAME],[NET_VALUE], [TAX_RATE], [GROSS_VALUE])
VALUES
(
    20200101, 1, 'EX2020-T1', 'Example Product Model 2020-T1', 599.00, 23, 736.77
)

Na koniec wykorzystujemy AssertEqualsTable, która porównuje oczekiwane oraz otrzymane zestawy danych.

EXEC tSQLt.AssertEqualsTable expected, actual;

Skupmy się teraz na procedurze.

CREATE PROCEDURE dbo.SaveGrossValueForOrderItem
    @OrdenNo int, @ItemNo int 

AS

    DECLARE @NetValue DECIMAL(18,2)
    DECLARE @TaxRate INT

    SELECT @NetValue = [NET_VALUE], @TaxRate = [TAX_RATE]
    FROM [dbo].[OrderItems] WHERE [ORDER_NO] = @OrdenNo AND [ORDER_ITEM] = @ItemNo

    DECLARE @GrossValue DECIMAL(18,2) = (SELECT dbo.ReturnGrossValue(@NetValue, @TaxRate))

    UPDATE [dbo].[OrderItems]
    SET [GROSS_VALUE] = @GrossValue
    WHERE [ORDER_NO] = @OrdenNo AND [ORDER_ITEM] = @ItemNo

GO

Wykorzystujemy w niej naszą funkcję z poprzedniego testu by obliczyć wartość brutto oraz zapisać ją w tabeli.

Uruchamiamy test. Tym razem odpalimy wszystkie testy z klasy [FirstLearningUnitTests]

EXEC tSQLt.Run '[FirstLearningUnitTests]'

Jak widać oba nasze testy zakończyły się sukcesem.

Podobał Ci się ten wpis? Zapraszam do mojej listy mailingowej by otrzymać więcej dodatkowych materiałów oraz być na bieżąco z nowościami!

Czym jest tSQLt?

Pisanie testów z tSQLt

Piszemy pierwszy test

Test z izolacją danych

Testy jednostkowe zapytań SQL – Wprowadzenie

Cechy dobrego konsultanta