To już niestety ostatni wpis z cyklu pod patronatem Objectivity. Tematyka testów SQL mnie bardzo zainteresowała, więc chcę się jeszcze tym aspektem mojej pracy z Wami podzielić 🙂

Testy SQL

Pisząc aplikację zwykle piszemy testy. Raczej nie muszę wyjaśniać po co 🙂 Czy tworząc różne procedury lub funkcje na bazie danych też sprawdzamy ich poprawność pisząc testy? Tu już niekoniecznie. A szkoda, bo pisanie testów w tSQLt nie różni się za bardzo od pisania zwykłych testów jednostkowych. Oczywiście jest to SQL, a nie C# – ale cały koncept pozostaje taki sam. Reguła 3A (Arrange, Act, Assert) dalej jest aktualna: przygotowujemy dane, wywołujemy interesującą na logikę i sprawdzamy, czy wynik jest zgodny z oczekiwanym.

Instalacja tSQLt

Na początek trzeba zainstalować sobie tSQLt – warto iść zgodnie z dokumentacją. Na tej samej stronie jest również przycisk do pobrania paczki. W pobranej paczce jest plik Example.sql, który pozwala utworzyć testową bazę danych wraz z testami tSQLt.

Test

Test jest zwykłą procedurą złożoną z kilku kroków opisanych poniżej. Co ważne: żeby zostać rozpoznany jako klasa testowa, musi zaczynać się od słowa test np. [schema].[test status message includes the number of particles].

Test Setup

Czasami zdarza się, że przygotowanie testu wymaga wielu operacji: zamockowania tabel, wstawienia wielu wierszy itp. Żeby nie tworzyć wielkich testów z milionem linii, warto przygotować plik Setup, w którym te wszystkie dane przygotujemy. Co ważne, taki plik nie może mieć nazwy zaczynającej się od słowa test (bo nie jest stricte klasą testową, tylko pomocniczą). Przykładowa klasa pomocnicza dla testu opisywanego powyżej może więc nazywać się [schema].[status message includes the number of particles setup]. Niestety z powodu sortowania alfabetycznego według nazw w Object Explorer w bazie danych, Test i Test Setup mogą być rozdzielone wieloma innymi testami.

Arrange

Na początek potrzebujemy zamockować sobie jakieś dane. Skoro to baza danych – zapewne potrzebujemy wpisów w konkretnej tabeli. Najpierw więc tworzymy sobie sztuczną tabelę (z pominięciem constraints – możecie o tym poczytać w dokumentacji):

EXEC tSQLt.FakeTable 'Accelerator.Particle';

A następnie wstawiamy do niej jakieś wartości:

INSERT INTO Accelerator.Particle (Id, X, Y, Value) VALUES (1, 0.5, 0.5, 'MyValue');

Dodatkowo przygotujemy sobie tabelę na wyniki:

CREATE TABLE #Actual (
  Id    DECIMAL(10,2),
  X     DECIMAL(10,2),
  Y     DECIMAL(10,2),
  Value DECIMAL(10,2))

Act

Wywołujemy odpowiednią funkcję, którą chcemy przetestować (poniżej jest przedstawiona jej logika):

CREATE FUNCTION [Accelerator].[GetParticlesInRectangle](
  @X1 DECIMAL(10,2),
  @Y1 DECIMAL(10,2),
  @X2 DECIMAL(10,2),
  @Y2 DECIMAL(10,2)
)
RETURNS TABLE
AS RETURN (
  SELECT Id, X, Y, Value 
    FROM Accelerator.Particle
   WHERE X > @X1 AND X < @X2
         AND
         Y > @Y1 AND Y < @Y2
);

i jej wynik wstawiamy do tabeli tymczasowej #Actual

  SELECT Id, X, Y, Value INTO #Actual
  FROM Accelerator.GetParticlesInRectangle(0.0, 0.0, 1.0, 1.0);

AssertEqualsTable

Przygotowujemy tymczasową tabelę #Expected o strukturze identycznej do tabeli #Actual

SELECT TOP(0) * INTO #Expected FROM #Actual;

Wstawiamy konkretne, oczekiwane wartości do tej tabeli

INSERT INTO #Expected (Id, X, Y, Value) VALUES (1, 0.5, 0.5, 'MyValue');

A na koniec wykonujemy porównanie tabel #Expected i #Actual

EXEC tSQLt.AssertEqualsTable '#Expected', '#Actual';

AssertEquals

Innym sposobem testowania jest pobranie wartości zwracanej przez testowaną funkcję IsExperimentReady() (jej logika jest przedstawiona poniżej):

CREATE FUNCTION [Accelerator].[IsExperimentReady]()
RETURNS BIT
AS
BEGIN 
  DECLARE @NumParticles INT;
  
  SELECT @NumParticles = COUNT(1) FROM Accelerator.Particle;
  
  IF @NumParticles > 2
    RETURN 1;

  RETURN 0;
END;

Wynik takiej funkcji możemy zapisać do zmiennej:

SELECT @Ready = Accelerator.IsExperimentReady();

i sprawdzić, czy jest odpowiedni:

EXEC tSQLt.AssertEquals 1, @Ready;

Uruchamianie testu

Konkretny test można uruchomić wywołując komendę

EXEC tSQLt. Run 'schema.test_name'

Można również uruchomić wszystkie testy w obrębie schema

EXEC tSQLt. Run 'schema'

Oraz uruchomić wszystkie testy, jakie mamy w bazie

EXEC tSQLt. RunAll

Output testów

Przykładowy output wywołania więcej niż jednego testu wygląda następująco:

Dostajemy tabelkę z listą testów. Od góry są wyświetlane testy, które zakończyły się sukcesem, a na dole te, które zakończyły się niepowodzeniem. Przy wielu testach taka tabelka może nie być zbyt czytelna, ale można zawsze spojrzeć na ostateczny komunikat ze statusem sukcesu/błędu z podsumowaniem z ostatniej linijki:

Output testu

Output przykładowego testu, który się sypie wygląda następująco:

Może to być niewłaściwa wartość konkretnej propercji:

Widać, że oczekiwaliśmy wartości 1, a mamy 0.

Mogą to być również różniące się wiersze w tabeli:

Znak < wskazuje otrzymany rekord z tabeli #Actual, a znak > rekord przewidywany z tabeli #Expected. Widać, że różnią się wartością w kolumnie X.

Funkcja vs Procedura

W powyższych przykładach korzystaliśmy z funkcji, które zwracały tabelę lub jakąś wartość. Można również korzystać z procedur, które wykonują jakąś logikę, ale nic nie zwracają.

Czyszczenie bazy

Warto wspomnieć, że każdy test tSQLt jest domyślnie opakowany w transakcję, która wykonuje rollback po jego wykonaniu.

Podsumowanie

Jak widać, przetestowanie logiki, którą mamy w bazie danych (w postaci funkcji czy procedur) nie jest wcale takie trudne. Oczywiście czasem może to wymagać drobnych zmian, ale po stronie backendu czy frontendu jest dokładnie tak samo. Tworzenie testów tSQLt jest podobne do pisania testów jednostkowych – tyle, że zamiast kodu np. w C# piszemy kod SQL. W związku z tym warto pisać testy tSQLt, które sprawdzą poprawność logiki – jeśli mamy takową po stronie bazy danych.

Post powstał pod patronatem firmy, w której aktualnie pracuję: Objectivity.

WWW

Linkedin

Facebook

Twitter

Grafikę tytułową zaprojektował niezastąpiony zespół designu!