Тестируем SQL Server код с tSQLt

FYI: эта статья представляет собой дополненную версию моего доклада на SQA Days #25.

Опираясь на свой опыт общения с коллегами, могу утверждать: тестирование кода в БД не является распространённой практикой. Это может нести в себе потенциальную опасность. Логику в БД пишут такие же люди, какие пишут «обычный» код. Следовательно, там так же могут присутствовать ошибки, и они так же могут повлечь за собой негативные последствия для продукта, бизнеса и потребителей. Неважно, идёт ли речь о хранимых процедурах, помогающих бэкенду, или о ETL, преобразующих данные в хранилище — риск есть, и тестирование может его существенно снизить. О том, что такое tSQLt и как оно помогает нам в тестировании кода в SQL Server, я и хочу вам рассказать.

Контекст

Есть большой warehouse на SQL Server, содержащий различные данные по клиническим исследованиям. Он наполняется из различных источников (главным образом это документ-ориентированные базы). Внутри самого сервера данные многократно преобразуются с помощью ETL. В дальнейшем эти данные могут быть выгружены в базы поменьше для использования веб-приложениями, решающими какие-то небольшие конкретные задачи. Некоторые из заказчиков заказчика также просят предоставить им API для своих внутренних нужд. В реализации таких API нередко используются хранимые процедуры и запросы.

В общем, кода на стороне СУБД порядком.

Зачем всё это нужно

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

Думаю, многие в курсе зависимости цены бага от времени его обнаружения, открытие которой обычно приписывают Барри Боэму. Ошибка, занесённая на раннем этапе и обнаруженная на позднем, может стоить дороже в связи с необходимостью прохождения множества этапов (кодирование, юнит, интеграционное, системное тестирование и т. д.) повторно как для локализации ошибки, так и для доведения исправленного кода обратно до этапа, на котором проблема была выявлена. Этот эффект также актуален и для случая warehouse’а. Если в какую-то ETL закралась ошибка, и данные проходят многократные преобразования, то при обнаружении ошибки в данных придётся:

1. Пройти все шаги преобразования назад до локализации проблемы.
2. Исправить проблему.
3. Повторно получить исправленные данные (не исключена необходимость ручных правок).
4. Убедиться, что некорректные данные, вызванные ошибкой, не появились где-либо ещё.

Не cтоит также забывать, что мы не мягкими игрушками торгуем. Ошибка в такой сфере, как клинические исследования, может повлечь вред не только бизнесу, но и здоровью людей.

Как тестировать?

Раз речь идёт о тестировании кода, то мы подразумеваем юнит- и интеграционное тестирование. Вещи эти сильно репетативные и подразумевают постоянный регресс. Строго говоря, вручную такое тестирование никто не проводит (ну может быть за исключением каких-то совсем вырожденных случаев).

Приятный бонус: тесты могут быть вспомогательным материалом при документировании кода. К слову, требования заказчика могут выглядеть так (кликабельно):

Excel, две колонки с требованиями + разрозненная вспомогательная информация в других колонках + невнятная разметка, которая больше сбивает с толку, чем помогает. При необходимости восстановить изначальные пожелания могут возникнуть трудности. Тесты могут помочь более точно зафиксировать нюансы реализации (само собой, рассматривать их как эквивалент документации не стоит).

К сожалению, с ростом сложности кода растёт сложность тестов, и этот эффект может нивелироваться.

Тесты могут послужить дополнительной прослойкой безопасности против спонтанных мёржей. Автотесты в CI ввиду формализма помогают справиться с этой проблемой.

Если наш выбор пал на путь автоматизации, то нам необходимо определиться с инструментарием для её осуществления.

Чем тестировать?

В случае тестирования кода в БД я выделяю два подхода: SQL-powered, т. е. функционирование непосредственно в СУБД, и Non-SQL-powered. Я смог выделить следующие нюансы:

SQL-powered	Non-SQL-powered
Требуется установка объектов в БД	Требуется установка дополнительных внешних к БД инструментов
Тесты всегда независимы от технологий, применяемых в приложении вне БД	Тесты могут быть зависимы от технологий, применяемых вне БД
Фреймворк всегда привязан к конкретной СУБД	Фреймворк зачастую поддерживает несколько СУБД
Для написания тестов требуются только знания СУБД; для разработки можно привлечь ручных тестировщиков или DBA	Для написания тестов обычно требуется дополнительное знание каких-либо языков программирования и/или технологий; зачастую нужна помощь программистов
Выполнение на уровне СУБД позволяет использовать более продвинутые fake’и и assertion’ы	Выполнение извне может ограничивать возможности инструментов

В SQL Server’е у нас есть некоторый выбор:

Общие сведения
Название	Подход	Архитектура	Написан на	Тесты на
tSQLt	SQL-powered	xUnit	T-SQL + CLR	T-SQL
TSQLUnit	SQL-powered	xUnit	T-SQL	T-SQL
utTSQL	SQL-powered	xUnit	T-SQL	T-SQL
T.S.T.	SQL-powered	xUnit	T-SQL	T-SQL
DbFit	Non-SQL-powered	FitNesse	C#/Java	Wiki markdown
Slacker	Non-SQL-powered	RSpec (BDD-oriented)	Ruby	Ruby
NUnit и т.п.	Non-SQL-powered	xUnit	N/A	N/A

Даты
Название	Первое появление	Последний коммит	Последний релиз
tSQLt	27-07-2008	01-07-2019	31-01-2016
TSQLUnit	16-12-2002 (0.9) 21-07-2009 (0.91 rc1)	26-04-2018 (GitHub)	09-04-2011 (SourceForge)
utTSQL	12-03-2008	12-03-2008	12-03-2008
T.S.T.	02-03-2009 (v1.0)	N/A	30-03-2012
DbFit	12-01-2009	10-09-2018	15-08-2015
Slacker	23-06-2011	10-12-2018	10-12-2018
NUnit и т.п.	N/A	N/A	N/A

Возможности
Название	CLR не требуется	XML вывод	Тесты обёрнуты в транзакции	Fake’и	Обработчики ошибок	Assertion’ы
tSQLt	–	+	+	+	+	Отлично
TSQLUnit	+	–	+	–	–	Очень плохо
utTSQL	+	–	–	–	–	Плохо
T.S.T.	+	+	+ (опц.)	–	+	Отлично
DbFit	+	–	+ (опц.)	–	+	Хорошо; есть нюансы
Slacker	+	–	+ (опц.)	–	–	Хорошо; есть нюансы
NUnit и т.п.	+	+	N/A	N/A	N/A	Отлично; есть нюансы

Прочее
Название	Документация	Коммьюнити
tSQLt	Отлично; есть нюансы	Отлично
TSQLUnit	Плохо	Плохо
utTSQL	Отлично	Плохо
T.S.T.	Отлично	Плохо
DbFit	Отлично	Нормально
Slacker	Отлично	Нормально
NUnit и т.п.	Отлично	Отлично

Оценки «хорошо-плохо» субъективны, извините, без этого никуда.

Пояснение: «Первое появление» — это самая ранняя дата в жизненном пути фреймворка, которую мне удалось найти, т. е. самый ранний релиз или коммит.

Можно заметить, что SQL-powered альтернативы достаточно давно заброшены, и tSQLt единственный поддерживаемый вариант. Функционально тоже tSQLt выигрывает. Единственное — в плане assertion’ов T.S.T. может похвастаться немного более богатым выбором, нежели tSQLt, что, впрочем, вряд ли перевесит его минусы.

В документации tSQLt имеются нюансы, но об этом я расскажу позже.

В мире non-SQL-powered всё не так однозначно. Альтернативы, пусть и не супер активно, но разрабатываются. DbFit — интересный инструмент, основанный на фреймворке FitNesse. Он предлагает написание тестов на wiki-разметке. Slacker тоже вещь любопытная — BDD-подход при написании тестов к БД.

Оговорюсь об Assertion’ах в non-SQL-powered. Чисто внешне их меньше, и можно было бы сказать, что они из-за этого хуже. Но тут стоит учитывать то, что они принципиально отличаются от tSQLt. Не всё так однозначно.

Последняя строчка — «NUnit и т. п.» — это, скорее, напоминание. Многие из привычных в повседневной работе фреймворков для unit-тестирования могут с помощью вспомогательных библиотек быть применены на БД. В таблице много N/A, т. к. эта строка, по сути, включает в себя множество инструментов. Оттуда же и «нюансы» в assertion-колонке — в разных инструментах их набор может варьироваться, да и вопрос применимости к БД открыт.

Как ещё одну интересную метрику мы можем рассмотреть Google trends.

Нюансы:

1. Не стал включать Slacker, т. к. это название может обозначать много чего (а запросы вроде «Slacker framework» на графиках не особо видны).
2. Тренд T.S.T. любопытства ради добавил, но он тоже не то чтобы сильно отражает положение дел, т. к. это аббревиатура, которая обозначает множество разных вещей.
3. Не стал включать NUnit и аналоги, т. к. это изначально фреймворки для тестирования кода непосредственно приложений, и их тренды не показательны для нашего контекста.

В общем и целом, можно сказать, что tSQLt выгодно выглядит на фоне аналогов.

Что за tSQLt?

tSQLt, как несложно догадаться, это SQL-powered фреймворк для unit-тестирования.

→ Официальный сайт

Заявляется поддержка SQL Server начиная с 2005 SP2. Так далеко в прошлое заглядывать мне не доводилось, но у нас на дев-сервере стоит 2012, у меня локально 2017 — каких-либо проблем замечено не было.

Open source, лицензия Apache 2.0, доступен на GitHub. Можно форкаться, контрибьютить, пользоваться бесплатно в коммерческих проектах и, самое главное, не бояться закладок в CLR.

Механика работы

Тест-кейсы — это хранимые процедуры. Они объединяются в тест-классы (test suite в терминах xUnit).

Тест-классы — это не что иное, как обычные БД схемы. От прочих схем они отличаются регистрацией в таблицах фреймворка. Сделать такую регистрацию можно вызовом одной процедуры — tSQLt.NewTestClass.

Внутри тест-класса также возможно определение SetUp-процедуры, которая будет запускаться перед исполнением каждого отдельного тест-кейса.

Teardown-процедура для восстановления системы по завершению тест-кейса не требуется. Каждый тест-кейс вместе с SetUp-процедурой выполняется в отдельной транзакции, которая после сбора результатов откатывается. Это очень удобно, но имеет некоторые негативные эффекты, о чём расскажу несколько ниже.

Фреймворк позволяет запускать отдельные тест-кейсы, тест-классы целиком или все зарегистрированные тест-классы разом.

Возможности на примерах

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

Дисклеймер:

• примеры упрощены;
• в оригинале не весь код тестов написан мной, это, скорее, плоды коллективного творчества;
• пример 2 выдуман с целью более полной демонстрации возможностей tSQLt.

Пример №1: CsvSql

По просьбе одного из заказчиков заказчика реализовано следующее. В БД в Nvarchar(MAX) полях хранятся SQL-запросы. Для просмотра этих запросов прикручен минимальный фронтенд. Result sets, возвращаемые этими запросами, используются бэкендом для генерации CSV-файла для возвращения по API-вызову.

Result set'ы достаточно увесистые и содержат множество колонок. Условный пример такого result set:

Данный result set представляет собой некоторые данные о клинических испытаниях. Давайте поближе посмотрим, как считается ClinicsNum — количество клиник, задействованных в исследовании. У нас есть две таблицы: [Trial] и [Clinic]:

Имеет место FK: [Clinic].[TrialID] -> [Trial].[TrialID]. Очевидно, что для подсчёта количества клиник нам всего-навсего потребуется обычный COUNT(*).

SELECT COUNT(*), ...
  FROM dbo.Trial
  LEFT JOIN dbo.Clinic
    ON Trial.ID = Clinic.TrialID
  WHERE Trial.Name = @trialName
  GROUP BY
...

Как нам протестировать такой запрос? Для начала мы можем использовать удобный stub — FakeTable, который значительно упростит дальнейшую работу.

EXEC tSQLt.FakeTable 'dbo.Trial';
EXEC tSQLt.FakeTable 'dbo.Clinic';

FakeTable делает простую вещь — переименовывает старые таблицы и создаёт на их месте новые. Те же имена, те же колонки, но без constraint’ов и trigger’ов.

Зачем нам это нужно:

1. В тестовой базе могут быть какие-то данные, которые могут помешать тестам. Благодаря FakeTable мы не зависим от них.
2. Для теста, как правило, нужно заполнить лишь некоторые колонки. В таблице же их может быть множество, и какие-то из них будут иметь constraint’ы. Мы упрощаем таким образом дальнейшую установку тестовых данных — будем вставлять только те, которые действительно необходимы для тест-кейса.
3. Мы точно не затронем какой-либо триггер при вставке тестовых данных и можем не беспокоиться о нежелательных пост-эффектах.

Далее, вставляем нужные нам тестовые данные:

INSERT INTO dbo.Trial
([ID], [Name])
VALUES
(1,    'Valerian');

INSERT INTO dbo.Clinic
([ID], [TrialID], [Name])
VALUES
(1,    1,         'Clinic1'),
(2,    1,         'Clinic2');

Достаём из базы наш запрос, создаём таблицу Actual и заполняем её result set’ом из нашего запроса:

DECLARE @sqlStatement NVARCHAR(MAX) = (SELECT…
CREATE TABLE actual ([TrialID], ...);
INSERT INTO actual
EXEC sp_executesql @sqlStatement, ...

Заполняем Expected — ожидаемые значения:

CREATE TABLE expected (
    ClinicsNum INT
);
INSERT INTO expected SELECT 2

Хочу обратить ваше внимание, что в Expected таблице у нас всего лишь одна колонка, в то время как в Actual мы имеем полный набор.

Это связано с особенностью процедуры AssertEqualsTable, которую мы будем использовать для проверки значений.

EXEC tSQLt.AssertEqualsTable
    'expected',
    'actual',
    'incorrect number of clinics';

Она сравнивает только те колонки, которые присутствуют в обеих сравниваемых таблицах. Это весьма удобно в нашем случае, т. к. тестируемый запрос возвращает массу колонок, на каждой из которых «висит» своя логика, порой весьма запутанная. Мы не хотим раздувать тест-кейсы, так что данная возможность нам весьма кстати. Понятное дело, это палка о двух концах. Если в Actual набор колонок заполняется автоматически через SELECT TOP 0 и в какой-то момент внезапно вылезает лишняя колонка, то такой тест-кейс этот момент не отловит. Для обработки таких ситуаций необходимо делать дополнительные проверки.

Процедуры-побратимы AssertEqualsTable

Стоит упомянуть, что tSQLt содержит две процедуры, похожие на AssertEqualsTable. Это AssertEqualsTableSchema и AssertResultSetsHaveSameMetaData. Первая делает то же самое, что AssertEqualsTable, но на метаданных таблицы. Вторая же проводит подобное сравнение, но на метаданных result set'ов.

Пример №2: Constraints

В предыдущем примере мы увидели, как можно снимать constraint’ы. А что, если нам нужно их проверять? Технически, это тоже часть логики, и она также может быть рассмотрена как кандидат на покрытие тестами.

Рассмотрим ситуацию из предыдущего примера. Две таблицы — [Trial] и [Clinic]; [TrialID] FK:

Давайте попробуем написать тест-кейс для проверки этого constraint’а. Сначала, как и в прошлый раз, мы фейкаем таблицы.

EXEC tSQLt.FakeTable '[dbo].[Trial]'
EXEC tSQLt.FakeTable '[dbo].[Clinic]'

Цель та же — избавиться от лишних ограничений. Мы хотим изолированных проверок без лишних телодвижений.

Далее, возвращаем необходимый нам constraint на место с помощью процедуры ApplyConstraint:

EXEC tSQLt.ApplyConstraint
    '[dbo].[Clinic]',
    'Trial_FK';

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

EXEC tSQLt.ExpectException
    @ExpectedMessage = 'The INSERT statement conflicted...',
    @ExpectedSeverity = 16,
    @ExpectedState = 0;

После установки обработчика можно совершать попытку вставки невставляемого.

INSERT INTO [dbo].[Clinic] ([TrialID])
    VALUES (1)

Исключение поймано. Test pass.

Процедуры-побратимы ApplyConstraint

Для тестирования триггеров разработчики tSQLt предлагают нам схожий подход. Для возвращения триггера в fake-таблицу можно использовать процедуру ApplyTrigger. Далее всё как в примере выше — вызываем срабатывание триггера, проверяем результат.

ExpectNoException — антоним ExpectException

Для случаев, когда исключение точно не должно возникать, есть процедура ExpectNoException. Работает аналогично ExpectException, за исключением того, что тест в случае возникновения исключения считается заваленным.

Пример №3: семафор

Ситуация следующая. Есть некоторое количество хранимых процедур и windows-сервисов. Начало их выполнения может быть вызвано разными внешними к ним событиями. При этом допустимый порядок их выполнения фиксирован. Для разграничения доступа к таблицам БД используется семафор. Он представляет собой группу хранимых процедур.

Для примера рассмотрим одну из этих процедур. Имеем две таблицы:

Таблица [Process] содержит в себе список процессов, допустимых к исполнению, [ProcStatus] — список статусов этих процессов.

Что же делает наша процедура? При вызове сначала происходит ряд проверок:

1. Имя процесса для запуска, переданное в параметре процедуры, ищется в поле [Name] таблицы [Process].
2. Если имя процесса было найдено, то проверяется, допустим ли его запуск в данный момент — флаг [IsRunable] таблицы [Process].
3. Если оказалось, что процесс допустим к выполнению, то остаётся убедиться, что он ещё не запущен. В таблице [ProcStatus] проверяется отсутствие записей о данном процессе со статусом = ‘InProg’.

Если всё ОК, то в ProcStatus добавляется новая запись об этом процессе со статусом ‘InProg’ (это и считается запуском), ID этой записи возвращается с параметром ProcStatusId. Если какая-либо проверка провалена, то мы ожидаем следующее:

1. Отправляется письмо администратору системы.
2. Возвращается ProcStatusId = -1.
3. Новая запись в [ProcStatus] не добавляется.

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

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

1. Мы гарантированно избавляемся от каких-либо данных, способных помешать корректному выполнению тест-кейса.
2. Мы упростим дальнейшую проверку отсутствия записей в ProcStatus.

EXEC tSQLt.FakeTable 'dbo.Process';
EXEC tSQLt.FakeTable 'dbo.ProcStatus';

Для отправки сообщения используется написанная нашими программистами процедура [SendEmail]. Для проверки отправки письма администраторам нам нужно отловить её вызов. Для этого случая tSQLt предлагает нам использовать SpyProcedure.

EXEC tSQLt.SpyProcedure 'dbo.SendEmail'

SpyProcedure делает следующее:

1. Создаёт таблицу вида [dbo].[SendEmail_SpyProcedureLog].
2. Подобно FakeTable, заменяет оригинальную процедуру на свою, с тем же именем, но содержащую логику логирования. При желании можно добавить какую-либо свою логику.

Как несложно догадаться, логирование происходит в таблицу [dbo].[SendEmail_SpyProcedureLog]. Данная таблица содержит колонку [_ID_] — порядковый номер вызова процедуры. Последующие колонки носят имена параметров, передаваемых в процедуру, и в них собираются значения, передаваемые при вызовах. При необходимости их также можно проверить.

Последняя вещь, которую нам нужно сделать перед вызовом семафора и проверками, это создать переменную, в которую мы будем помещать ID записи из [ProcStatus] таблицы (точнее, -1, ведь запись добавлена не будет).

DECLARE @ProcStatusId BIGINT;

Вызываем семафор:

EXEC dbo.[Semaphore_JobStarter]
    'SomeProcess',
    @ProcStatusId OUTPUT; -- вот мы получили -1

Всё, теперь у нас есть все необходимые данные для проверки. Начнём с проверки отправки
письма:

IF NOT EXISTS (
    SELECT *
    FROM
dbo.SendEmail_SpyProcedureLog)
EXEC tSQLt.Fail 'SendEmail has not been run.';

В данном случае мы решили не проверять параметры, переданные при отправке, а просто проверить сам факт. Обращаю ваше внимание на процедуру tSQLt.Fail. Она позволяет «официально» завалить тест-кейс. Если вам нужно выстроить какую-то специфическую конструкцию, то tSQLt.Fail позволит вам это сделать.

Далее, проверим отсутствие записей в [dbo].[ProcStatus]:

EXEC tSQLt.AssertEmptyTable 'dbo.ProcStatus';

Вот где нам помог FakeTable, который мы применили в самом начале. Благодаря ему мы можем ожидать пустоту. Без него для точной проверки нам бы, по-хорошему, следовало бы сверить количество записей до и после выполнения семафора.

Равенство ProcStatusId = -1 мы можем с лёгкостью проверить с помощью AssertEquals:

EXEC tSQLt.AssertEquals
    -1,
        @ProcStatusId,
        'Wrong ProcStatusId.';

AssertEquals минималистичен — просто сравнивает два значения, ничего сверхъестественного.

Процедуры-побратимы AssertEquals

Для сравнения значений нам предоставлен ряд процедур:

• AssertEquals
• AssertNotEquals
• AssertEqualsString
• AssertLike

Думаю, их названия говорят сами за себя. Единственный момент, который стоит отметить — наличие отдельной процедуры AssertEqualsString. Всё дело в том, что AssertEquals/AssertNotEquals/AssertLike работают с SQL_VARIANT, а NVARCHAR(MAX) к нему не относится, в связи с чем разработчикам tSQLt пришлось выделить для проверки NVARCHAR(MAX) отдельную процедуру.

FakeFunction

FakeFunction с некоторой натяжкой можно назвать процедурой, похожей на SpyProcedure. Этот fake позволяет заменить какую-либо функцию на необходимую более простую. Т. к. функции в SQL Server работают по принципу тюбика с зубной пастой — выдают результат через «единственное технологическое отверстие», — то никакого функционала логирования, увы, не предоставляется. Только замена логики.

Подводные камни

Стоит обозначить некоторые подводные камни, с которыми вы, возможно, столкнётесь в процессе работы с tSQLt. В данном случае под подводными камнями я подразумеваю некоторые проблемные моменты, которые родились ввиду ограничений SQL Server и/или которые невозможно разрешить разработчикам фреймворка.

Отмена/порча транзакций

Первая и самая главная проблема, с которой столкнулась наша команда, — это отмена транзакций. SQL Server не умеет откатывать вложенные транзакции отдельно — только все целиком, вплоть до самой внешней. Учитывая тот факт, что tSQLt оборачивает каждый тест-кейс в отдельную транзакцию, это становится проблемой. Ведь откат транзакции внутри тестируемой процедуры может сломать выполнение теста, вызывая недескриптивную ошибку выполнения.

Для обхода этой проблемы у нас используются savepoint’ы. Идея проста. Перед тем, как стартовать в тестируемой процедуре транзакцию, производим проверку — а не находимся ли мы внутри транзакции уже. Если оказывается, что да, находимся, то, предполагая, что это транзакция tSQLt, ставим savepoint вместо старта. Тогда, при необходимости, мы будем откатываться к этому savepoint’у, а не к началу транзакции. Вложенности как таковой нет.

Проблема усложняется порчей транзакций. Если вдруг что-то пошло не так и сработало исключение, то транзакция может стать doomed. Такую транзакцию нельзя не только закоммитить, но и откатить до savepoint’а — только откатывать всё целиком.

Учитывая всё вышеописанное, приходится применять следующую конструкцию:

DECLARE @isNestedTransaction BIT = 
    CASE
WHEN @@trancount > 0 
        THEN 'true'
        ELSE 'false'
END;
BEGIN TRY
    IF @isNestedTransaction = 'false' 
        BEGIN TRANSACTION
    ELSE
        SAVE TRANSACTION SavepointName;
        -- something useful
    IF @isNestedTransaction = 'false'
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    DECLARE @isCommitable BIT = 
        CASE WHEN XACT_STATE() = 1
            THEN 'true'
            ELSE 'false'
    END;
    IF @isCommitable = 'true' AND @isNestedTransaction = 'true'
        ROLLBACK TRANSACTION SavepointName;
    ELSE
        ROLLBACK;
    THROW;
END CATCH;

Рассмотрим код по частям. Сначала нам необходимо определить, находимся ли мы внутри транзакции:

DECLARE @isNestedTransaction BIT = 
    CASE WHEN @@trancount > 0 
        THEN 'true'
        ELSE 'false'
END;

После получения флага @isNestedTransaction запускаем TRY-блок и ставим, в зависимости от ситуации, savepoint или начало транзакции.

BEGIN TRY
    IF @isNestedTransaction = 'false' 
        BEGIN TRANSACTION
    ELSE
        SAVE TRANSACTION SavepointName;
        -- something useful

После того, как мы сделали что-то полезное, коммитимся, если это «настоящий» запуск процедуры.

        -- something useful
    IF @isNestedTransaction = 'false'
    COMMIT TRANSACTION;
END TRY

Само собой, если это запуск из тест-кейса, нам коммитить ничего не надо. По окончании выполнения tSQLt просто откатит все изменения. Если вдруг что-то пошло не так и мы попали в блок CATCH, то первым делом надо узнать, может ли вообще наша транзакция быть закоммичена.

BEGIN CATCH
    DECLARE @isCommitable BIT = 
        CASE WHEN XACT_STATE() = 1
            THEN 'true'
            ELSE 'false'
    END;

Откатываться до savepoint’а мы можем только если

1. транзакция commitable,
2. имеет место тестовый запуск, т.е. savepoint существует.

В прочих случаях нам нужно откатывать всю транзакцию целиком.

    IF @isCommitable = 'true' AND @isNestedTransaction = 'true'
        ROLLBACK TRANSACTION SavepointName;
    ELSE
        ROLLBACK;
    THROW;
END CATCH;

Да, к сожалению, если при тестовом запуске получилась uncommittable-транзакция, то мы всё равно получим ошибку выполнения тест-кейса.

FakeTable и проблема с Foreign key

Рассмотрим уже знакомые нам таблицы [Trial] и [Clinic]:

Мы помним про [TrialID] FK. Какие же проблемы это может вызвать? В примерах, приводимых ранее, мы применяли FakeTable на обе таблицы сразу. Если же мы применим его только на [Trial], то получим следующую ситуацию:

Попытка вставить запись в [Clinic], таким образом, может обернуться неудачей (даже если мы подготовили все необходимые данные в fake-версии таблицы [Trial]).

[dbo].[Test_FK_Problem] failed: (Error) The INSERT statement conflicted with the FOREIGN KEY constraint "Trial_Fk". The conflict occurred in database "HabrDemo", table "dbo.tSQLt_tempobject_ba8f36353f7a44f6a9176a7d1db02493", column 'TrialID'.[16,0]{Test_FK_Problem,14}

Вывод: нужно или фейкать всё, или не фейкать ничего. Во втором случае очевидно, что база должна быть заранее подготовлена для проведения тестов.

SpyProcedure на системных процедурах

Увы, но шпионить за вызовами системных процедур не получится.

[HabrDemo].[test_test] failed: (Error) Cannot use SpyProcedure on sys.sp_help because the procedure does not exist[16,10] {tSQLt.Private_ValidateProcedureCanBeUsedWithSpyProcedure,7}

В примере с семафором мы отслеживали вызовы процедуры [SendEmail], написанной нашими разработчиками. В данном случае написание отдельной процедуры обусловлено необходимостью сбора и обработки некоторой дополнительной информации перед непосредственной отправкой сообщений. В целом же надо быть морально готовым к тому, что, возможно, придётся писать процедуры-прослойки для некоторых системных процедур исключительно для удовлетворения целей тестирования.

Достоинства

Быстрая установка

Установка проходит в 2 этапа и занимает около 2 минут. Вам нужно всего лишь активировать CLR на сервере, если это ещё не сделано, и выполнить один-единственный скрипт. Всё, можно добавлять первый тест-класс и писать тест-кейсы.

Быстрое освоение

tSQLt — инструмент простой в освоении. У меня на его освоение ушёл рабочий день с небольшим. Я спрашивал у коллег, кто работал с фреймворком, и получилось, что примерно один день уходит у всех.

Быстрое внедрение в CI

На настройку интеграции в CI на нашем проекте ушло приблизительно 2 часа. Время, понятное дело, может варьироваться, но в целом это не проблема, и интеграция может быть осуществлена весьма быстро.

Широкий набор инструментов

Это субъективная оценка, но, на мой взгляд, функционал, предоставляемый tSQLt, достаточно богат и покрывает львиную долю потребностей на практике. Для редких случаев, когда встроенных fake’ов и assertion’ов не хватает, есть, конечно, tSQLt.Fail.

Удобная документация

Официальная документация удобна и последовательна. С её помощью можно без проблем вникнуть в суть использования tSQLt в короткие сроки, даже если это ваш первый инструмент для unit-тестирования.

Удобный вывод результатов

Данные можно получить в весьма наглядном текстовом виде:

[tSQLtDemo].[test_error_messages] failed: (Failure) Expected an error to be raised.
[tSQLtDemo].[test_tables_comparison] failed: (Failure) useful and descriptive error message
Unexpected/missing resultset rows!
|_m_|Column1|Column2|
+---+-------+-------+
|<  |2      |Value2 |
|=  |1      |Value1 |
|=  |3      |Value3 |
|>  |2      |Value3 |
 
+----------------------+
|Test Execution Summary|
+----------------------+
 
|No|Test Case Name                      |Dur(ms)|Result |
+--+------------------------------------+-------+-------+
|1 |[tSQLtDemo].[test_constraint]       |     83|Success|
|2 |[tSQLtDemo].[test_trial_view]       |     83|Success|
|3 |[tSQLtDemo].[test_error_messages]   |    127|Failure|
|4 |[tSQLtDemo].[test_tables_comparison]|    147|Failure|
-----------------------------------------------------------------------------
Msg 50000, Level 16, State 10, Line 1
Test Case Summary: 4 test case(s) executed, 2 succeeded, 2 failed, 0 errored.
----------------------------------------------------------------------------- 

Можно также извлечь из базы данных (кликабельно)…

…или получить в XML формате.

<?xml version="1.0" encoding="UTF-8"?>
<testsuites>
   <testsuite id="1" name="tSQLtDemo" tests="3" errors="0" failures="1" timestamp="2019-06-22T16:46:06" time="0.433" hostname="BLAHBLAHBLAH\SQL2017" package="tSQLt">
      <properties />
      <testcase classname="tSQLtDemo" name="test_constraint" time="0.097" />
      <testcase classname="tSQLtDemo" name="test_error_messages" time="0.153">
         <failure message="Expected an error to be raised." type="tSQLt.Fail" />
      </testcase>
      <testcase classname="tSQLtDemo" name="test_trial_view" time="0.156" />
      <system-out />
      <system-err />
   </testsuite>
</testsuites>

Последний вариант позволяет без проблем интегрировать тесты в CI. В частности, у нас всё работает под Atlassian Bamboo.

Поддержка Redgate

К плюсам также можно отнести поддержку такого крупного поставщика DBA-инструментов, как RedGate. SQL Test — их плагин к SQL Server Management Studio — сразу из коробки работает с tSQLt. Помимо этого, RedGate осуществляет помощь главному разработчику tSQLt с dev-окружением, как утверждает сам этот разработчик в Google groups.

Недостатки

Нет fake’ов временных таблиц

tSQLt не позволяет фейкать временные таблицы. В случае необходимости вы можете воспользоваться неофициальным дополнением. К сожалению, это дополнение поддерживается только SQL Server 2016+.

Нет доступа ко внешним базам

Не получится держать отдельную базу только для хранения фреймворка. tSQLt спроектирован из расчёта на тестирование того, что лежит с ним в одной базе. Fake’и, увы, работать не будут.

CREATE PROCEDURE [tSQLtDemo].[test_outer_db]
AS
BEGIN
    SELECT TOP 10 * FROM [AdventureWorks2017].[Person].[Password]
    EXEC tSQLt.FakeTable '[AdventureWorks2017].[Person].[Password]'
    SELECT TOP 10 * FROM [AdventureWorks2017].[Person].[Password]
END

Assertion’ы вроде как функционируют, но их работоспособность, понятное дело, никто не гарантирует.

CREATE PROCEDURE [tSQLtDemo].[test_outer_db_assertions]
AS
BEGIN
    SELECT TOP 1 *
    INTO #Actual
    FROM [AdventureWorks2017].[Person].[Password]

    SELECT *
    INTO #Expected
    FROM (
            SELECT 'bE3XiWw=' AS [PasswordSalt]
    ) expectedresult;

    EXEC tSQLt.AssertEqualsTable '#Expected', '#Actual', 'The salt is not salty';
END

Баги документации

Несмотря на то, что выше я писал о последовательности и доступности документации, она также содержит проблемы. В ней встречаются некоторые устаревшие моменты.

Пример 1. «Quick start guide» предлагает скачивать фреймворк с SourceForge. С SourceForge они распрощались аж в 2015.

Пример 2. Гайд по ApplyConstraint в примере для отлавливания исключения использует тяжеловесную конструкцию с процедурой Fail, которую было бы проще и нагляднее заменить на ExpectException.

CREATE PROCEDURE ConstraintTests.[test ReferencingTable_ReferencedTable_FK prevents insert of orphaned rows]
AS
BEGIN
     EXEC tSQLt.FakeTable 'dbo.ReferencedTable';
     EXEC tSQLt.FakeTable 'dbo.ReferencingTable';
     
     EXEC tSQLt.ApplyConstraint 'dbo.ReferencingTable','ReferencingTable_ReferencedTable_FK';
     
     DECLARE @ErrorMessage NVARCHAR(MAX); SET @ErrorMessage = '';
     
     /* [НБ] Почему мы не используем ExceptException ниже? */
     BEGIN TRY
    INSERT  INTO dbo.ReferencingTable
            ( id, ReferencedTableId )
    VALUES  ( 1, 11 ) ;
     END TRY
     BEGIN CATCH
    SET @ErrorMessage = ERROR_MESSAGE();     
     END CATCH
     
     IF @ErrorMessage NOT LIKE '%ReferencingTable_ReferencedTable_FK%'
     BEGIN
       EXEC tSQLt.Fail 'Expected error message containing ''ReferencingTable_ReferencedTable_FK'' but got: ''',@ErrorMessage,'''!';
     END
     
END
GO

И это закономерно, потому что имеет место…

Частичная заброшенность

В разработке tSQLt наблюдается долгий перерыв с начала 2016 и до июня 2019. Да, к сожалению, данный инструмент частично заброшен. В 2019 понемногу, судя по GitHub, разработка всё-таки сдвинулась. Хотя в официальном Google Groups есть тред, в котором Себастьяна, главного разработчика tSQLt, напрямую спрашивали о дальнейшей судьбе разработки. Последний вопрос задан 2 марта 2019, ответ до сих пор не получен.

Проблема с SQL Server 2017

Если вы используете SQL Server 2017, то для вас, возможно, установка tSQLt потребует некоторых дополнительных манипуляций. Всё дело в том, что впервые с 2012 года в SQL Server сделали security-изменения. На уровне сервера был добавлен флаг «CLR strict security», который запрещает создание неподписанных сборок (даже SAFE). Подробное описание проблемы заслуживает отдельной статьи (и, к счастью, всё уже отлично описано и последующие статьи в цикле). Просто будьте морально готовы к этому.

Можно было бы, конечно, отнести этот недостаток к «подводным камням», решение которых не зависит от разработчиков tSQLt, но разрешить эту проблему на уровне фреймворка можно, хоть и несколько трудоёмко. В GitHub уже имеется issue, правда, с его разрешением тянут с октября 2017 (см. предыдущий подпункт).

Альтернативы (±) для других СУБД

Стоит также упомянуть альтернативы для случая других СУБД. tSQLt не единственный в своём роде инструмент. Хотя, ввиду особенностей реализации (CLR, да и T-SQL ощутимо отличается от прочих SQL диалектов), вы не сможете им воспользоваться в других СУБД, вы всё ещё можете найти схожие инструменты. Отмечу, что эти альтернативы от tSQLt отличаются ощутимо, так что речь прежде всего о SQL-powered подходе в целом.

Так, под PostgreSQL есть достаточно развитый и активно разрабатываемый ptTAP. Он подразумевает написание тестов на «родном» PL/pgSQL и вывод результатов в формате TAP. Под MySQL есть схожий, хоть и несколько менее функциональный инструмент — MyTAP. Если вдруг вам повезло работать с Oracle, то вы имеете возможность применить utPLSQL — очень мощный и активно (я бы даже сказал, более чем) развивающийся инструмент. 

Заключение

Пожалуй, всей вышеизложенной информацией я хотел донести две основные мысли.

Первая — полезность тестирования кода в БД. Сидите ли вы под SQL Server, Oracle или MySQL — неважно. Если у вас в БД хранится некоторое количество непротестированной логики, то вы берёте на себя дополнительные риски. Баги в коде БД способны так же, как и баги во всём остальном коде, нанести ущерб продукту и, как следствие, компании, его поставляющей.

Вторая идея — это выбор инструмента. Если вы, так же как и я, работаете с SQL Server, то tSQLt является если не 100% победителем, то точно стоит того, чтобы вы на него обратили внимание. Даже несмотря на вялую разработку в последнее время, это всё ещё актуальный инструмент, значительно облегчающий тестирование.

Источники, которые мне помогли (неполный список)

DbFit — Automated Open Source Database Testing: http://www.methodsandtools.com/tools/dbfit.php

DbFit Documentation: https://dbfit.github.io/dbfit/docs/

Slacker wiki: https://github.com/vassilvk/slacker/wiki

T.S.T. documentation: https://archive.codeplex.com/projects/TST/4e04e281-9f35-4891-809a-15f09d304f4e 

NUnit Assertions: https://github.com/nunit/docs/wiki/Assertions

utTSQL code: https://sourceforge.net/p/uttsql/code/HEAD/tree/

Junit Class Assert: https://junit.org/junit4/javadoc/latest/org/junit/Assert.html

pgTap: https://pgtap.org/

utPLSQL: http://utplsql.org/

MyTap: https://github.com/hepabolu/mytap

tSQLt Google groups: https://groups.google.com/forum/#!forum/tsqlt

tSQLt official website: https://tsqlt.org/

tSQLt GitHub: https://github.com/tSQLt-org/tSQLt

Google trends: https://bit.ly/2x7BQL6

How to ROLLBACK a transaction when testing using tSQLt: https://stackoverflow.com/questions/8973138/how-to-rollback-a-transaction-when-testing-using-tsqlt
 
What are the Pros and Cons of Manual Unit Testing against the Automated Unit Testing?: https://stackoverflow.com/questions/2948337/what-are-the-pros-and-cons-of-manual-unit-testing-against-the-automated-unit-tes#2948354

The Good, the Bad, and the Ugle̅e̅: https://sqlquantumleap.com/2017/08/07/sqlclr-vs-sql-server-2017-part-1-clr-strict-security/

Rex Black, Erik Van Veenendal, Dorothy Graham, Foundations of Software Testing, Third edition, 2012 Cengage Learning EMEA