Содержание

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

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