Введение в модульное тестирование T-SQL помощью tSQLt

tSQLt — это мощный фреймворк с открытым исходным кодом для модульного тестирования кода SQL Server. 

Модульное тестирование SQL-кода — полезная практика в разработке баз данных, которая позволяет обнаруживать ошибки до попадания их в продакшн. Хотя надо сказать, есть разные мнения, и некоторые разработчики все еще спорят о необходимости модульного тестирования SQL-кода. Модульное тестирование очень важно для контроля и проверки поведения отдельных частей базы данных и не стоит игнорировать написание модульных тестов кода базы данных. По сути, модульные тесты позволяют автоматически проверять поведение объектов базы данных (хранимых процедур, триггеров, функций и т. д.) при регрессионном тестировании. 

В части тестирования SQL-кода у tSQLt есть ряд преимуществ:

• Тесты пишутся на языке T-SQL. Это основное преимущество. Нет необходимости в изучении нового языка программирования или платформы для написания и выполнения SQL-тестов. Для теста нужна только база данных с установленным tSQLt и SQL Server Management Studio или любой другой редактор запросов.

• Каждый модульный тест автоматически запускается в рамках транзакции. После выполнения теста все операции изменения данных автоматически откатываются и нет необходимости в какой-либо очистке данных.

• tSQLt позволяет использовать заглушки (mock, fake) реальных объектов. Заглушки/фейки имитируют поведение реального объекта, что позволяет изолировать зависимости и писать изолированные тесты. Например, нам нужно протестировать хранимую процедуру, использующую внутри пользовательскую функцию. Мы можем изолировать тест хранимой процедуры, написав mock-функцию, используемую только в рамках данного теста.

• tSQLt полностью бесплатен и с открытым исходным кодом.

• tSQLt можно интегрировать в SSDT-проекты. Для интеграции с Visual Studio можно использовать tSQLt Test Adapter for Visual Studio 2017 или ApexSQL Unit Test.

Установка tSQLt

Установка tSQLt очень проста. Сначала нужно включить CLR (SQL Common Language Runtime) и свойство TRUSTWORTHY базы данных, в которую устанавливается tSQLt.

EXEC sp_configure 'clr enabled', 1; RECONFIGURE; GO EXEC sp_configure 'clr enabled'

В этой статье мы будем использовать демонстрационную базу данных WideWorldImporters.

USE WideWorldImporters  GO ALTER DATABASE WideWorldImporters SET TRUSTWORTHY ON;

Далее скачиваем архив tSQLt с tsqlt.org.

Выполняем содержимое файла tSQLt.class.sql в SQL Server Management Studio. Если установка прошла успешно, вы увидите сообщение «Thank you for using tSQLt» и версию tSQLt.

Примечание — в Azure SQL включать CLR и TRUSTWORTHY не нужно, достаточно выполнить файл tSQLt.class.sql. 

Проверить установку компонент tSQLt можно с помощью следующего запроса:

SELECT @@VERSION,name  FROM sys.objects sysobj where schema_id = ( select sch.schema_id from sys.schemas sch where name='tSQLt' ) order by sysobj.name

Пишем первый тест

Сначала нужно создать «тест-класс» (тестовый класс), который группирует тесты (test case). Приведенный ниже скрипт создает новый класс с именем DemoUnitTestClass.

USE WideWorldImporters GO EXEC tSQLt.NewTestClass 'DemoUnitTestClass';

Тестовый класс tSQLt, по сути, представляет собой схему БД с дополнительным расширенным свойством с информацией о том, что данная схема создана для тестового класса. В этом можно убедиться с помощью следующего запроса:

select SCHEMA_NAME,objtype,name,value from INFORMATION_SCHEMA.SCHEMATA SC CROSS APPLY fn_listextendedproperty (NULL, 'schema', NULL, NULL, NULL, NULL, NULL) OL WHERE OL.objname=sc.SCHEMA_NAME COLLATE Latin1_General_CI_AI and SCHEMA_NAME = 'DemoUnitTestClass'

Также это можно посмотреть через SSMS в расширенных свойствах (extended properties) схемы.

• Откройте в SSMS узел Security->Schemas для базы данных.

• Щелкните правой кнопкой мыши на схеме DemoUnitTestClass и откройте свойства (Properties).

• Выберите страницу Extended Properties.

Для удаления класса можно использовать хранимую процедуру tSQLt.DropClass, принимающую в параметре имя тестового класса.

EXEC tSQLt.DropClass 'DemoUnitTestClass'

Хранимая процедура DropClass удаляет не только сам класс (схему), но и все тестовые объекты, связанные с этим классом. Если создать класс с уже существующим именем, то поведение будет аналогично процедуре DropClass: удаляются все объекты, связанные с тестовым классом, и создается пустой тестовый класс с указанным именем.

Ассерты в tSQLt

Для проверки результата выполнения tSQLt предлагает несколько хранимых процедур:

• tSQLt.AssertEquals

• tSQLr.AssertEqualsTable

• tSQLt.AssertEmptyTable

• tSQLt.AssertEqualsString

• tSQLt.AssertEqualsTableSchema

• tSQLt.AssertLike

• tSQLt.AssertNotEquals

• tSQLt.AssertObjectDoesNotExist

• tSQLt.AssertObjectExists

• tSQLt.AssertResultSetsHaveSameMetaData

• tSQLt.Fail

Рассмотрим подробнее tSQLt.AssertEquals.

Процедура tSQLt.AssertEquals сравнивает ожидаемое и фактическое значение и принимает три параметра:

• @expected — ожидаемое значение, которое будет сравниваться с проверяемым значением.

• @actual — полученный результат, который и тестируем.

• @message — сообщение, которое выводится при падении теста.

Давайте напишем простой модульный тест функции, вычисляющей налог для суммы, переданной в параметре.

Сама функция CalculateTaxAmount:

CREATE OR ALTER FUNCTION CalculateTaxAmount(@amt MONEY) RETURNS MONEY AS BEGIN RETURN (@amt /100)*18  END; GO   select dbo.CalculateTaxAmount(100) AS TaxAmount

В этом примере мы передаем в параметре 100 и получаем в результате 18. Теперь напишем тест для этой функции.

EXEC tSQLt.NewTestClass 'DemoUnitTestClass'; GO     CREATE OR ALTER PROC DemoUnitTestClass.[test tax amount] AS BEGIN DECLARE @TestedAmount as money = 100 DECLARE @expected as money  = 18 DECLARE @actual AS money  SET @actual = dbo.CalculateTaxAmount(100)   EXEC tSQLt.AssertEquals @expected , @actual   END

Для выполнения теста используется хранимая процедура tSQLt.Run. В параметре можно передать имя тестового класса, чтобы запустить все тесты, связанные с указанным классом, или указать конкретный тест.

tSQLt.Run 'DemoUnitTestClass.[test tax amount]'

tSQLt.Run 'DemoUnitTestClass'

Как видно, наш тест выполнился успешно. Давайте изменим ожидаемый результат и повторно запустим тест.

CREATE OR ALTER PROC DemoUnitTestClass.[test tax amount] AS BEGIN DECLARE @TestedAmount as money = 100 DECLARE @expected as money  = 20 DECLARE @actual AS money  SET @actual = dbo.CalculateTaxAmount(100)   EXEC tSQLt.AssertEquals @expected , @actual   END GO   tSQLt.Run 'DemoUnitTestClass.[test tax amount]'

Тест упал. Сообщения о результатах выполнения теста вполне понятные. В первой строке мы можем увидеть причины падения теста: ожидаемые и фактические значения различны.

Важный момент — именование тестов. Имена тестов должны начинаться с «test». Если назвать по-другому, то tSQLt не сможет найти и выполнить тест. 

CREATE OR ALTER PROC DemoUnitTestClass.[test tax amount] AS BEGIN DECLARE @TestedAmount as money = 100 DECLARE @expected as money  = 20 DECLARE @actual AS money  DECLARE @Message AS VARCHAR(500)='Wrong tax amount' SET @actual = dbo.CalculateTaxAmount(100)   EXEC tSQLt.AssertEquals @expected , @actual ,@Message   END GO   tSQLt.Run 'DemoUnitTestClass.[test tax amount]'

Мы рассмотрели только самый базовый пример, в реальном мире, конечно, тесты могут быть намного сложнее. Поэтому необходимо хорошо понимать паттерн Arrange-Act-Assert относительно SQL-тестов. Этот паттерн предлагает разделять тест на три части:

• Arrange — объявление переменных, определение предварительных условий и входных данных.

• Act — выполнение проверяемого кода и фиксация результата выполнения.

• Assert — сравнение ожидаемого и фактического значения.

Укажем в нашем тесте эти части в комментариях:

CREATE OR ALTER PROC DemoUnitTestClass.[test tax amount] AS BEGIN ----------------------Arrange----------------------------- DECLARE @TestedAmount as money = 100                   --- DECLARE @expected as money  = 20                       ---  DECLARE @actual AS money          ---  DECLARE @Message AS VARCHAR(500)='Wrong tax amount'    --- ---------------------------------------------------------- ----------------------Act--------------------------------- SET @actual = dbo.CalculateTaxAmount(100)              --- ---------------------------------------------------------- ----------------------Assert------------------------------ EXEC tSQLt.AssertEquals @expected , @actual ,@Message  --- ---------------------------------------------------------- END

Заключение

В этой статье мы поговорили о важности модульного тестирования SQL-кода и о том, как реализовать этот подход с помощью фреймворка tSQLt.

---

Модульное тестирование кода бэкенда прочно вошло в нашу жизнь, но код базы данных по-прежнему мало кто тестирует. Приглашаем всех заинтересованных на открытое занятие «Тестирование T-SQL кода с помощью tSqlt». На этом уроке поговорим о модульном тестировании кода SQL Server и использовании для этого tSqlt. Регистрация открыта по ссылке.