Тестируем 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
    • +16
    • 4,2k
    • 3
    Аркадия
    66,95
    Заказная разработка, IT-консалтинг
    Поделиться публикацией

    Комментарии 3

      +1
      Спасибо, интересно расписано на примерах.
      Про фреймворк слышал, идеи использовать его были, но до реального применения дело пока не дошло. Надеюсь, удастся как-нибудь позаниматься им.
        0
        Постоянно использую в парадигме TDD, очень облегчает жизнь и дальнейшую поддержку. Рекомендую к приему внутрь.
          0
          В Visual Studio, проект SSDT имеется SQL Server Unit Test template, библиотека Microsoft.Data.Tools.Schema.Sql.UnitTesting
          За исключением fake tables, описанная Вами функциональность там имеется. Для заполнения и откатки данных используются пре- и пост- скрипты. GUI по аналогии с WinForms — код на TSQL как «визуализация» и код на C# как возможность расширения. Пользуюсь больше 10и лет, очень доволен.

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

          Самое читаемое