Содержание

Подготовка проекта
Изоляция тестирования
Сбор результатов
Доработка инструментов
Подводные камни
Ссылки

TLDR

Добавляем дочерний sqlproj с тестами на tSQLt.
В сборке PR билдим оба проекта, получаем дакпаки с учётом изменений из текущего бранча.
Создаём клон БД налету и деплоим в этот клон дакпак проекта с тестами.
Выполняем тесты, собираем покрытие через Extended Events с помощью утилиты SqlCover.
Конвертируем полученные данные в форматы для TeamCity и SonarQube.

Построение CI/CD для баз данных задача в любом случае нетривиальная. Если в проекте БД ничего, кроме таблиц, нет, то, в принципе, на внедрении sqlpackage можно бы и закончить. Но если в базе пишется код хранимок, триггеров, функций, то этот код хотелось бы тестировать перед публикацией на прод. В статье хотел бы поделиться некоторыми деталями внедрения юнит-тестов в пайплайн для баз данных с кодом на T-SQL. Мы действительно гоняем тесты при сборке каждого пулл-реквеста в sqlproj-репозиториях, но не всё так просто.

Подготовка проекта

В мире T-SQL вариантов нет, живой фреймворк один — tSQLt. Даже ребята из Microsoft уже открыто на него ссылаются в разговорах о фреймворке, сделанном под SSDT. Поэтому на tSQLt и ориентируемся.

Удачная организация внутри солюшна очень похожа на то, как это делается в .NET и других языках, фреймворках: есть основной проект и дополнительный, дочерний, который с тестами и смотрит на основной проект.

Таким образом, создаём в солюшне два sqlproj. Чтобы код с тестами не превратился в бессмысленную самостоятельную БД, взаимосвязь с основным проектом устанавливаем как Same Database. Этим решением достигаются две цели:

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

Окно добавления новой связи между БД в проекте SSDT. Обратите внимание на выбор значения в поле Database location.

В этот же проект с тестами нужно подключить фреймворк tSQLt. Фреймворк нужен будет во всех проектах с тестами. И, скорее всего, вы будете вносить в код фреймворка доработки. Поэтому рекомендую исходники форкнуть, оформить в виде такого же локального SSDT-проекта, как и все остальные БД, сбилдить в dacpac и расшарить его на все ваши TSQL-проекты. Код фреймворка, очевидно, должен оказаться в той же БД, что и тесты. Подключаем его аналогично основному проекту, с опцией Same Database, но не напрямую, как проект с исходниками, а в виде dacpac-файла.

Примерно так должны выглядеть зависимости в проекте с тестами. Справа приведён фрагмент файла AdventureWorsTest.sqlproj

После сборки проекта с тестами получится дакпак, содержащий вообще всё:

код основного проекта;
фреймворк тестирования;
сами тесты.

В контуры, где предполагается выполнять эти тесты, будет деплоиться вот этот насыщенный дакпак.

Чтобы понимать, находится ли основной проект в состоянии, способном задеплоиться, лучше сначала деплоить его собственный дакпак. И только потом, поверх первого деплоя, деплоить дакпак с тестами. Иначе получится, что к проду подъедет дакпак, который никто ни разу деплоить не пытался — такие сюрпризы никому не нужны.

Изоляция тестирования

Код на TSQL заметно отличается от кода на JS, .NET, Python и многих других языков тем, что выполниться может только на стороне СУБД. То есть сначала код нужно куда-то задеплоить.И тестирование, очевидно, возможно исключительно в рабочем инстансе БД. Можно было бы порассуждать о поднятии инстанса налету в условном Docker-контейнере, если бы в традициях кодинга на TSQL не было заложено распиливание системы на множество БД, а потом работа со всеми этими БД подряд, в обратную сторону и по диагонали из одной хранимки. БД с подобной лапшой в изоляции, на пустой инстанс задеплоить не получится. Ей нужно всё привычное окружение. Все связи должны зарезолвиться.

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

В поисках решения описанной проблемы пришли к клонированию БД. Речь о команде DBCC CLONEDATABASE. Клонирование выполняется очень быстро даже для развесистых БД. У этого механизма есть ряд нюансов, но в целом он показал себя хорошо. Создание БД налету с нуля тоже рассматривалось, но в sqlproj-проектах нет множества свойств, настроек, характерных для приличной болванки БД, созданной под контролем ДБА, и с нуля из дакпака раскатать что-то совсем похожее на такую же БД из прода сложновато. Но кто знает, может ещё вернемся к обсуждению этого варианта.

По поводу данных: писать тесты, ориентируясь на то, что хранится здесь и сейчас в БД, взятой с прода или даже в дев-песочнице, наивно и бесперспективно. Нашли подходящего клиента (пусть и обезличенного), с нужным тарифом, балансом, заказами, подстроили под него тест с захардкоженным client_id. Завтра клиент уже не клиент или перешёл на другой тариф, или товар из заказа сменил категорию — и тест развалился. Надёжный тест от подобных обстоятельств не зависит. Приличный тест на tSQLt сначала фейкает зависимости, подкладывает минимально необходимый набор данных в фейк-таблицы, превращает «лишние» хранимки и функции в фейк-пустышки и проверяет только то, для чего задуман. Здесь клоны снова оказываются удачным решением: таблицы в клоне девственно пусты и наивному варианту теста работать будет просто не на чем.

Однако подготовка изолированной болванки, в которой можно будет в параллели запускать тесты для множества пулл-реквестов, на клонировании не заканчивается. Сценарий в общих чертах такой:

- Создать клон
    - Задеплоить проект с тестами
        - Разрешить доступ сборке tSQLt в клон
            - Стартовать сбор покрытия
                - Обнаружить список тест-классов
                    - Выполнить отдельный тест или тест-класс
                        - Повторно выполнить упавшие тесты

Сбор результатов

Покрытие может собрать утилита SqlCover от Ed Elliott. Механика заложена такая: перед выполнением тестов создаётся сессия сбора Extended Events, которая отслеживает хорошо всем знакомый SP:StmtStarting. В деталях каждого такого события есть object_id и номер строки. Если распарсить исходники хранимок и других programmabilities, можно будет сопоставить события с исходниками и понять, какие выражение были выполнены при работе тестов.

Уровень подробностей до statement — так себе. Представьте километровый запрос с кучей CTE, которые заходят в MERGE с несколькими Action, оттуда, допустим, OUTPUT DELETED.* INTO @tbl. Ведь это одно выражение. И SP:StmtStarting сообщит нам только, что мы в это выражение зашли. А работал конкретный CTE, не работал, под который Action мёржа мы провалились, было ли хоть что-то в OUTPUT DELETED — этих подробностей не узнаем. Не говоря уже о том, по какой ветке CASE WHEN THEN ELSE код ходил, а по какой нет. Это обидно. Столько усилий и настолько топором рубленая статистика. Но всё же, наличие тестов и даже подобного понимания уровня покрытия кода тестами, гораздо лучше, чем их отсутствие.

Результат выполнения тестов из одного пулл-реквеста в интерфейсе TeamCity. Много тестов отработали, один завершился ошибкой.

Кроме покрытия, нужно понять, какой тест отвалился, какой отработал. Очень желательно знать конкретную возникшую ошибку; неплохо бы и время, за которое каждый тест выполнился. Так что дёрнуть tSQLt.Run и расслабиться не получится. Информацию от фреймворка следует выгрузить и преобразовать в форматы для CI-раннера (в нашем случае TeamCity) и для сонара.

SonarQube следит за покрытием изменений в коде тестами и за копи-пастой

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

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

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

SqlCover

#1 Вот в этом месте заложены жуткие тормоза:

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

Мы лупасим по выражениям, которые распарсили из кода и в так называемых батчах, извлечённых из собранных Extended Events ищем, был ли там наш объект. Nested Loops x Table Scan. Поискать это место пришлось, когда время выполнения тестов на относительно большом проекте перев��лило за час. Беглый осмотр выявил, что без сбора Coverage те же тесты выполняются за пятнадцать минут. Из подозрительного нашлась вот такая бяка, и дело оказалось именно в ней. Большой проект и много тестов — большой размер трассы и, соответственно, коллекции _batches. Лечится тупым перекладыванием _batches в Dictionary с ключом по ObjectId.

#2 Трейсфайл в любом случае будет большой и нужно выделить под него место, указав в определении EVENT SESSION явно сколько-то гигабайт. Если явно не указывать, то трасса порежется до размера по умолчанию в 1ГБ. Мы долго не могли понять, почему coverage не растёт, хотя новые тесты активно пишутся. Выяснилось, что 60ГБ+ из трассы просто никуда не сохранялись, поскольку мы для них место не выделили. Обнаруженный фактический объём трассировки заодно стал поводом поискать варианты его уменьшения. Сессию трассировки стартует SqlCover и определение сессии находится в исходниках этой утилиты, так что это всё в тот же огород. Самое очевидное, за счёт чего можно уменьшить трассу — в определении сразу исключить объекты фреймворка tSQLt. Попытки улучшить качество определения сессии трассировки окупятся: эти объёмы ложатся на диск вашего же сервера, время сбора данных и подсчёта покрытия тестами включено во время ваших же билдов.

#3 Если в базах есть триггеры и хочется измерять их покрытие, то придётся самостоятельно закостылить отслеживание триггеров. У таблиц же после фейканья object_id меняется и tSQLt.ApplyTrigger приклеивает триггеры уже к таблице, которой не было до начала выполнения тестов и не будет после. Да и триггеры ведь альтером с одной таблицы на другую не перевешиваются, то есть будет создан новый временный объект. SqlCover такой идентификатор в sys.objects не найдёт. Нужно будет сопоставить события в трассе по временному триггеру на фейковой таблице с реальным триггером на исходной таблицей.

tSQLt

#1 Если базы активно смотрят друг на друга, то нужно дописывать вручную хранимки фейканья синонимов. Превращать удалённые таблицы, функции, хранимки из местных синонимов в местные же полноценные фейк-объекты.

#2 Если версия сиквела позволяет, то желательно переписать всю агрегацию строк с FOR XML на STRING_CONCAT. Работа с XML быстротой не отличается, на объёме это будет заметно. Фреймворк собирает динамические запросы из метаданных, поэтому агрегации строк в коде много.

#3 Если автор теста забыл зафейкать крупную таблицу и выполнил tSQLt.AssertEqualsTable для сравнения результата с маленьким тестовым набором строк, то хранимка начнёт выводить дельту, которая составит столько миллионов строк, сколько оказалось в исходной, незафейканной вовремя таблице. Эти несколько часов бессмысленного спама никому не нужны — просто добавьте ограничение. Методом тыка выбрали для этого tSQLt.Private_Print. Ни от какого функционала фреймворка не ожидаются тысячи принтов, так что закостылили на все варианты проявления описанного сценария сразу.

Я, говорит, человек простой: сколько дали, столько и напечатаю

#4 Подготовка приличного теста с фейканьем зависимостей отнимает много сил. Желательно реализовать хранимку, генерирующую болванку теста с фейканьем зависимостей. Зависимости тестируемой хранимки можно извлечь из sys.sql_expression_dependencies.

#5 В нескольких местах фреймворка тесты что-то складывают в перманентные таблицы, причём часть этой информации после отката транзакции с тестовой сессией теряется. Эта информация по большей части за пределами конкретного запуска тестов не имеет смысла. А при активной параллельной работе нескольких сессий тесты и вовсе начинают пересекаться по таблицам, подчищать или считывать не своё. Примерно все такие места нужно выпилить, персистентные таблицы заменить времянками. Хоть пайплайн и гоняет тесты внутри клонов, в исходной версии БД на сервере-песочнице программисты сами занимаются отладкой. Поэтому устранить неуместное пересечение тестов по общим ресурсам конечно сто́ит.

Авторы этих инструментов, безусловно, огромные молодцы, а продукты создали уникальные и, бесспорно, полезные. Но код сырой и непричёсанный. В SqlCover есть куда применить и мелкую оптимизацию, и просто до ума довести некоторые фрагменты. Прикрутить StyleCop, добавить тестов. В tSQLt странно получилось, что ребята в курсе за тесты, но не слышали про форматтеры и линтинг. По исходникам очень желательно пройтись опытным взглядом и уверенной рукой.

«Напильником» поработать придётся довольно активно.

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

С клонами баз связана масса нюансов, в том числе баги вроде вот этого. Баг DacFx, который упирается в баг SqlSever.
Прежде чем плодить толпы клонов, нужно договориться с DBA. И запрограммировать джоб, который подчистит брошенные ошмётки от прерванных сессий.
Программистам нужна возможность гонять тесты не только на стороне CI, но и во время разработки. Для этого может понадобиться отдельный сервер. И период привыкания к тому, что иногда разработчики пересекаются друг с другом по хранимкам и таблицам.
Coverage через Extended Events не сможет дать тех же подробностей, что в «нормальных» языках программирования. Построчной детализации не будет, только уровень отдельных выражений.
Много времени придётся потратить на доработку SqlCover и tSQLt.
Вначале нужно построить CI/CD для БД хотя бы без тестов.
Всё, что вокруг вызовов SqlCover, tSQLt, SqlPackage — кастомные решения. Это нужно будет писать руками. Километры скриптов.

Игра в любом случае стоит свеч. Процесс разработки на TSQL с тестами в пайплайнах и сонаром — гораздо более взрослый и приличный, чем без них. Совсем явно это станет понятно даже скептикам, когда впервые тест отвалится по делу.

Ссылки

Контроль покрытия T-SQL