tSqlt — модульное тестирование в Sql Server

Если значительная часть бизнес логики Вашего приложения располагается в базе данных, вас наверняка посещала мысль о модульном тестировании хранимых процедур и функций. Опустим обсуждение вопроса о том, хорошо это или плохо — выносить логику в хранимые процедуры, и разберемся — как тестировать хранимый код. В этой статье я расскажу о tSqlt — замечательном бесплатном фреймворке unit-тестов с открытым исходным кодом для Sql Server.

Установка

tSqlt распространяется бесплатно под лицензией Apache 2.0 с открытым исходным кодом. Дистрибутив в виде архива можно загрузить с официального сайта.

Прежде чем начинать установку фреймворка, необходимо настроить экземпляр Sql server для работы с CLR:

EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;

И объявить целевую базу данных как доверенную (свойство TRUSTWORTHY).

DECLARE @cmd NVARCHAR(MAX);
SET @cmd = 'ALTER DATABASE ' + 
           QUOTENAME(DB_NAME()) + 
           ' SET TRUSTWORTHY ON;';
EXEC(@cmd);

В архиве Вы найдете sql-скрипт, который необходимо выполнить в целевой базе данных. Скрипт создаст собственную схему tSqlt, сборку CLR и множество процедур и функций. Часть процедур будут содержать префикс Private_ и предназначены для внутреннего использования самим фреймворком.

Работа с тестами

Тест представляет собой хранимую процедуру, название которой начинается со слова «test». Для удобства тесты объединяются в «классы», представляющие собой схемы Sql Server. Каждый класс может иметь свою процедуру SetUp, которая будет вызываться перед запуском каждого теста.
Создать новый класс можно процедурой NewTestClass

EXEC tSQLt.NewTestClass 'MyTestClass'

Запускать тесты можно все разом, по классам и по одному. Для этого служат процедуры Run и RunAll:

-- Запуск всех тестов
EXEC tSQLt.RunAll;

-- Запуск всех тестов класса MyTestClass
EXEC tSQLt.Run 'MyTestClass';

-- Запуск теста FisrtTest класса MyTestClass
EXEC tSQLt.Run 'MyTestClass.FisrtTest';

-- Повторный запуск последнего теста.
-- будет запущен тест FisrtTest из класса MyTestClass
EXEC tSQLt.Run;

Возможности

Если Вы когда-нибудь использовали какой-либо фреймворк для unit-тестов, вы будете приятно удивлены, не найдя серьезных отличий в tSqlt.
Замечательной особенностью tSqlt является изоляция тестов друг от друга, реализуемая с помощью механизма транзакций.
Помимо этого tSqlt содержит ряд полезных процедур для тестового вывода, помогающих определить — что же в тесте пошло не так.

Типичный тест состоит из трех частей:

1. Подготовка окружения / тестовых данных
2. Выполнение тестируемого кода
3. Проверка результатов

Расскажу о них по порядку:

Подготовка окружения / тестовых данных

На этом этапе нам нужно подготовить объекты базы данных, которые будут использованы тестируемым кодом — заменить их заглушками, Stub и Mock объектами.

Что можно подменить:

Тип объекта	Процедура	Результат
Таблица	FakeTable	Stub
Процедура	SpyProcedure	Mock
Функция	FakeFunction	Stub

Для подмены таблиц фреймворк предоставляет процедуру FakeTable, создающую копию целевой таблицы без данных.

tSQLt.FakeTable [@TableName = ] 'Имя заменяемой таблицы'
                , [[@SchemaName = ] 'Имя схемы']
                , [[@Identity = ] 'Сохранять идентификаторы']
                , [[@ComputedColumns = ] 'Сохранять вычисляемые поля]
                , [[@Defaults = ] 'Сохранять значения по умолчанию']

По умолчанию вычисляемые поля, значения по умолчанию и столбцы identity не сохранятся, однако, это можно изменить необязательными параметрами @identity, @ComputedColumns и @Defaults.
Функция НЕ может подменять временные таблицы, объекты других баз данных, а так же не сохраняет внешние ключи. Процедура FakeTable создаст заглушку (Stub) которую Вы сможете заполнить тестовыми данными, без необходимости изменять настоящий объект. Это даст Вам возможность запускать тесты независимо друг от друга нескольким пользователям одновременно на одном экземпляре Sql Server.

Процедура FakeFunction заменяет реальную функцию на заглушку (Stub).

tSQLt.FakeFunction [@FunctionName = ] 'Имя заменяемой функции'
                 , [@FakeFunctionName = ] 'Имя заглушки'

Процедура SpyProcedure создает Mock объект, подменяя реальную процедуру и сохраняя значения параметров, с которыми процедура будет вызвана. tSqlt создать специальную таблицу с параметрами вызова подмененной процедуры, прибавляя к имени процедуры постфикс "_SpyProcedureLog". Если Ваша процедура называлась, к примеру, CalcSales, то ее параметры будут сохранены в таблице CalcSales_SpyProcedureLog.
Если Вам помимо сохранения аргументов требуется, чтобы Mock объект выполнил какую-либо операцию или вернул значение, Вы можете передать Sql-скрипт в параметре @CommandToExecute.

tSQLt.SpyProcedure [@ProcedureName = ] 'Имя процедуры'
                [, [@CommandToExecute = ] 'Исполняемый скрипт' ]

Выполнение тестируемого кода

Самая простая часть — здесь Вы запускаете код, который хотите протестировать.
Стоит упомянуть, что если Вы ожидаете, что тестируемый код создаст исключение, необходимо заранее предупредить об этом tSqlt, вызвав процедуру ExpectException

tSQLt.ExpectException 
                [  [@ExpectedMessage= ] 'Ожидаемый текст ошибки']
                [, [@ExpectedSeverity= ] 'Ожидаемый уровень ошибки']
                [, [@ExpectedState= ] 'Ожидаемое состояние ошибки']
                [, [@Message= ] 'Сообщение об ошибке']
                [, [@ExpectedMessagePattern= ] 'Шаблон текста ошибки']
                [, [@ExpectedErrorNumber= ] 'Ожидаемый номер ошибки']

К этой процедуре так же прилагается процедура ExpectNoException, проверяющая, что исключение не было создано.

tSQLt.ExpectNoException [ [@Message= ] 'Сообщение об ошибке']

Проверка результатов

Для сравнения результатов работы тестируемого кода, с нашими ожиданиями используется набор процедур, ожидаемо названных Assert*. Естественно Вы можете использовать свой собственный код для сравнения результатов и ожиданий, вызывая процедуру tSQLt.Fail с описанием ошибки если тест не пройден. Однако, использование процедур Assert* делает тест более читабельным и похожим на привычные unit-тесты. К тому же, добавление логики в тест (пусть даже элементарной) не самая хорошая идея.

Assert* процедуры, предоставляемые фреймворком:

Процедура	Описание
AssertNotEquals	Проверяет, что два значения НЕ равны. ВНИМАНИЕ: NULL в ожидаемом значение приведет к ошибке -- Тест будет пройден успешно EXEC tSQLt.AssertEquals NULL, NULL;
AssertEmptyTable	Проверяет, что процедура пуста
AssertEquals	Проверяет, что два значения равны. ВНИМАНИЕ: В данном случае NULL равен NULL -- Тест будет пройден успешно EXEC tSQLt.AssertEquals NULL, NULL;
AssertEqualsString	Проверяет, что две строки равны. ВНИМАНИЕ: В данном случае NULL равен NULL -- Тест будет пройден успешно EXEC tSQLt.AssertEqualsString NULL, NULL;
AssertObjectExists	Проверяет существование объекта.
Fail	Завершает тест с заданной ошибкой
AssertObjectDoesNotExist	Проверяет что объект НЕ существует.
AssertLike	Проверяет что между ожидаемым и фактическим значением выполняется оператор LIKE ВНИМАНИЕ: В данном случае NULL равен NULL -- Тест будет пройден успешно EXEC tSQLt.AssertLike NULL, NULL;

Еще одну особую процедуру AssertEqualsTable я опишу отдельно.
Эта процедура сравнивает содержимое двух таблиц. Для успешного прохождения теста результирующая таблица должна иметь те же столбцы и те же значения в них, что и таблица с ожидаемыми значениями. Однако, если две эти таблицы, по мнению AssertEqualsTable, абсолютно равны:

    CREATE TABLE expected( 
		A INT
    )
    
    CREATE TABLE actual( 
		A BIGINT,
		B INT
    ) 
   
    -- Тест будет пройден
    EXEC tSQLt.AssertEqualsTable 'expected', 'actual';

Если Вам необходимо более жесткое сравнение метаданных таблиц, дополнительно используйте процедуру AssertResultSetsHaveSameMetaData

    CREATE TABLE expected( 
		A INT
    )
    
    CREATE TABLE actual( 
		A BIGINT,
		B INT
    ) 
   
    -- Тест будет пройден
    EXEC tSQLt.AssertEqualsTable 'expected', 'actual';
    -- Тест будет завершен с ошибкой
    EXEC tSQLt.AssertResultSetsHaveSameMetaData 
                                       'SELECT * FROM expected', 
                                       'SELECT * FROM actual';

ВНИМАНИЕ: Если таблицы содержат поля типов text, ntext, image, xml, geography, geometry, rowversion или любых CLR-типов не отмеченных как «comparable» или «byte ordered» будет выдано исключение

Пример

Рассмотрим простой пример: процедура CalcAvgTemperature высчитывает среднее значение температуры за диапазон дат, основываясь на данных в таблице temperature.
Процедура PrintAvgTemperatureLastFourDays использует процедуру CalcAvgTemperature для вычисления средней температуры за последние четыре дня.

Процедуры для тестирования

-- Таблица со значениями температур
CREATE TABLE temperature 
(
	DateMeasure DATE,
	Value numeric (18,2)
)

GO

-- Вычисление средней температуры за диапазон
CREATE PROC CalcAvgTemperature
	@StartDate DATE,
	@EndDate DATE,

	@AvgTemperature numeric (18,2) OUT
AS
BEGIN

	SELECT @AvgTemperature = AVG(Value)
	FROM temperature
	WHERE DateMeasure BETWEEN @StartDate AND @EndDate

END

GO
 
-- Вывод средней температуры за 4 дня
CREATE PROC PrintAvgTemperatureLastFourDays
	@Date DATE,

	@TemperatureString VARCHAR(255) OUT
AS
BEGIN
	
	DECLARE 	
		@StartDate DATE = DATEADD(D, -3, @Date),
		@EndDate DATE = @Date,
		@Result numeric (18,2)

	EXEC CalcAvgTemperature @StartDate, @EndDate, @Result OUT

	SET @TemperatureString	= 
		'Средняя температура с ' + 
		CONVERT(VARCHAR,@StartDate,104) +
		' по ' +
		CONVERT(VARCHAR,@EndDate,104) +
		' равна ' +
		CONVERT(VARCHAR,@Result)
END

Создадим новый тестовый класс TemperatureTests

EXEC tSQLt.NewTestClass 'TemperatureTests'

Добавим в него по одному тесту для каждой из наших процедур.

Тесты

-- Тест процедуры PrintAvgTemperatureLastFourDays
CREATE PROC TemperatureTests.Test_PrintAvgTemperatureLastFourDays
AS
BEGIN

	-- Подготовка окружения
	
	-- Подменяем процедуру CalcAvgTemperature на заглушку,
	-- которая будет всегда возвращать 100.00
	EXEC tSQLt.SpyProcedure 
			'CalcAvgTemperature', 
			'SET @AvgTemperature = 100.00'

	-- Запуск процедуры

	DECLARE @TemperatureString VARCHAR(255)
	EXEC PrintAvgTemperatureLastFourDays
			'2014-08-04', 
			@TemperatureString OUT

	-- Проверка результата

	-- Получаем аргументы, с которыми была запущена 
        -- процедура CalcAvgTemperature
	SELECT StartDate, EndDate
	INTO actual
	FROM CalcAvgTemperature_SpyProcedureLog

	-- таблица с ожидаемыми результатами
	CREATE TABLE expected
	(
		StartDate DATE, 
		EndDate DATE
	)

	INSERT expected (StartDate, EndDate)
	VALUES ('2014-08-01', '2014-08-04')

	-- Сравниваем ожидаемые аргументы и фактические
	EXEC tSQLt.AssertEqualsTable 
		'expected', 
		'actual', 
		'Процедура CalcAvgTemperature вызвана с неверными аргументами'

	-- Сравниваем ожидаемую и фактическую строку вывода температуры
	EXEC tSQLt.AssertEqualsString 
		'Средняя температура с 01.08.2014 по 04.08.2014 равна 100.00', 
		@TemperatureString, 
		'Строка имеет неверный формат'

END

GO

-- Тест процедуры CalcAvgTemperature
ALTER PROC TemperatureTests.Test_CalcAvgTemperature
AS
BEGIN

	-- Подготовка окружения
	
	-- Подменяем таблицу temperature
	EXEC tSQLt.FakeTable 'temperature'

	-- Заполняем ее тестовыми данными
	INSERT temperature (DateMeasure, Value) 
	VALUES 
	('2014-08-04', 26.13),
	('2014-08-03', 25.12),
	('2014-08-02', 26.43),
	('2014-08-01', 20.95)

	-- Запуск процедуры

	DECLARE @AvgTemperature numeric(18,2)
	EXEC CalcAvgTemperature 
			'2014-08-01', 
			'2014-08-04', 
			@AvgTemperature OUT

	-- Проверка результата

	-- Сравниваем результат с ожиданиями
	EXEC tSQLt.AssertEquals
		24.66, 
		@AvgTemperature, 
		'Неверно вычислено среднее значение температуры'

END

Чтобы запустить оба теста можно воспользоваться процедурой Run и передать ей имя нашего тестового класса TemperatureTests.

EXEC tSqlt.Run 'TemperatureTests'

Как и ожидалось, тесты прошли успешно и в выводе мы увидим:

+----------------------+
|Test Execution Summary|
+----------------------+
 
|No|Test Case Name                                            |Result |
+--+----------------------------------------------------------+-------+
|1 |[TemperatureTests].[Test_CalcAvgTemperature]              |Success|
|2 |[TemperatureTests].[Test_PrintAvgTemperatureLastFourDays]|Success|
-----------------------------------------------------------------------------
Test Case Summary: 2 test case(s) executed, 2 succeeded, 0 failed, 0 errored.
-----------------------------------------------------------------------------

Особенности

Не стоит забывать, что каждый запуск теста tSQLt оборачивает в транзакцию. Поэтому, если в своей хранимой процедуре вы используете свои собственные транзакции — делать это надо аккуратно. Так, например, тест такой процедуры завершится с ошибкой:

Скрытый текст

CREATE PROC [IncorrectTran]
AS
BEGIN

	BEGIN TRAN TestTran

	BEGIN TRY

		SELECT 1 / 0

		COMMIT TRAN TestTran

	END TRY
	BEGIN CATCH

		IF @@TRANCOUNT > 0
			ROLLBACK TRAN TestTran

	END CATCH

END

Хотя вне теста процедура отработает без ошибки. Причина проблемы связана с тем, что ROLLBACK в процедуре откатит не только Вашу транзакцию, но и транзакцию tSqlt и на выходе из процедуры изменится количество активных транзакций. Эта проблема описана здесь, а ее решение можно посмотреть здесь.

На десерт

Для тех, кто любит графический интерфейс, зеленые и красные галочки напротив тестов и тому подобное компания Redgate разработала SQL Test — очень мощный плагин для Sql Managment Studio, основанный на tSqlt и позволяющий выполнять всю работу с тестами из меню.