Эволюция системы разработки на SQL
Мы — SQL команда Срочного рынка Московской Биржи, занимаемся разработкой и сопровождением бэкофиса торгово-клиринговой системы Spectra с момента ее возникновения. Срочный рынок Московской Биржи — это более 500 фьючерсных и 30000 опционных инструментов, несколько миллионов сделок в день.
Торгово-клиринговая система Срочного рынка (ТКС Spectra) изначально строилась на основе MS SQL, и за пару десятков лет прошла сложный путь от нескольких серверов БД до огромной системы с сервис-ориентированной архитектурой. Долгое время вся бизнес-логика системы разрабатывалась в программном слое на серверах MS SQL: и матчинг заявок, и расчет обеспечения, и управление клиентами были реализованы на T-SQL.
На сегодняшний день весь высоконагруженный функционал вынесен в отдельные сервисы, но в базах данных остаются сотни таблиц и тысячи программных объектов. Особенностью кода является высокая когнитивная и цикломатическая сложность. Управлять этим кодом с учетом всех требований по надежности и быстродействию — очень интересная задача.
В этой статье мы хотим рассказать об эволюции нашей системы разработки на SQL.
Как все начиналось
Пару десятков лет назад, когда не было ни спринтов, ни сборочных конвейеров, ни модульных тестов, эталон программного кода находился в боевых базах, а для разработки использовались копии боевых БД с обезличенными данными. Каждый разработчик снимал скрипты необходимых объектов с сервера, вносил в них изменения, собирал набор скриптов по своим задачам. Согласование итогового комплекта для заливки выполнялось в ручном режиме. Набор скриптов модификации объектов и данных передавали в тестирование, в этот же набор вносили исправления обнаруженных багов и в релиз заливали в продакшен БД. С ростом числа задач и разработчиков сложность этого процесса многократно возросла, а с ней возросла и вероятность ошибки.
Первый подход к версионированию, Jira, спринты
Так дальше жить было нельзя, и мы перешли к версионированию SQL скриптов на основе SVN. Для мастер-ветки с продакшена были сняты скрипты всех баз, под каждую новую версию заводился бранч копированием мастер ветки. Со временем мастером стала текущая ветка в продакшене и уже от нее бранчевались версии для разработки.
Теперь эталонный код хранился не в БД, а в SVN. Для каждого объекта появилась история изменений, упорядочилась совместная разработка, но сам по себе переход на версионирование не решал проблему создания набора релизных скриптов. Распространенный в то время подход автоматизированного сравнения структур старой и новой версий БД и генерации скрипта заливки всех различающихся объектов нам не подходил, требовалось сохранить определенный разработчиком порядок внесения изменений в данные, схему и программный код. Кроме того, у нас было (и остается по сей день) довольно много кросс-серверного кода, который необходимо заливать поочередно на разные сервера. Решением стал переход к инкрементальной заливке изменений: выкладывать пронумерованные скрипты модификации структуры и данных в каталоги по датам.
Новый процесс разработки выглядел так:
Взять объекты для модификации из SVN, внести изменения, залить на свой рабочий сервер, проверить работоспособность.
Закоммитить изменения в бранч.
Выложить скрипты модификации данных, схемы и манифест со списком скриптов для одного или нескольких серверов БД и описанием задачи в каталог ToTest с текущей датой. Перевести задачу в тестирование.
Тестировщики заливают к себе скрипты согласно манифесту и проверяют задачу.
Ошибки при заливке исправляются в выложенном скрипте, замечания и баги — в новых файлах.В релиз последовательно накатываются все инкрементальные скрипты из бранча версии, предварительно заливаемость комплекта скриптов проверяется на интеграционных полигонах.
После релиза текущая ветка в SVN становилась эталонной, с боевых серверов снимались скрипты для сравнения с репозиторием.
Через некоторое время к системе был добавлен Atlassian Crucible, а к практикам — требование обязательного ревью кода при коммитах в релизную ветку. Вскоре после введения код ревью пришлось зафиксировать и стандарты написания кода, для предотвращения многодневных холиваров внутри команды.
Новый подход позволил перейти к разработке по спринтам, повысить качество кода и снизить число багов. Разумеется, у него были недостатки: необходимость выкладывания одного и того же кода в версионированные и в инкрементальные скрипты, длительная заливка в релиз (один и тот же объект мог перезаливаться десятки раз), непрозрачность изменений в данных, которые можно было отследить только по инкрементальным скриптам.
Но самое главное — пришла эра автоматизации.
Автоматизируй всё
DevOps подход за короткое время изменил мир разработки ПО. Трансформация практик захлестнула и Московскую Биржу: одна за другой команды переходили на git, автоматизированную сборку, отчитывались о покрытии юнит-тестами и собирали билды в конвейере. Чтобы сделать шаг в прекрасное будущее, нам, прежде всего, надо было научиться разворачивать все базы с нуля к любой заданной версии и наполнять их консистентными данными, но инкрементальные скрипты для этой задачи категорически не подходили. И тогда мы снова всё переделали.
Требовалось решить много интересных задач:
Заменить SVN на GIT. Разделить хранящийся в SVN sql-ный монолит на подпроекты и вести каждый в отдельном репозитории.
Добиться четкого версионирования как всей системы в целом, так и отдельных объектов.
Интегрироваться с системой общей сборки. Исключить необходимость ручной работы при генерации скриптов для заливки.
Упростить параллельную разработку нескольких версий и перенос кода между ними.
Получить возможность создания БД с нуля и заполнения набора справочников, необходимого для функционирования системы.
Реализовать систему юнит-тестов.
Рассчитывать метрику покрытия кода.
Использовать статический анализатор.
Автоматически генерировать документацию.
DDL, DML, DCL — версионируй всё
Версия БД — это определенное состояние схемы, данных и программных объектов. Для перехода от версии N к версии N+i необходимо однократно применить все изменения схемы и однократно модифицировать данные, а программный код можно перезаливать без ограничений. Например, тривиальная задача «добавить поле A в таблицу B, заполнить его в соответствии с некоторым правилом и выдать это поле в API-вызов C» реализуется тремя скриптами: alter table A add B. ; update A set B=…; create or alter procedure C…;. Изменения любой сложности в итоге можно разделить на эти три потока: схема, данные, объекты.
Эта несложная идея и стала основой новой системы хранения и версионирования.
Тип объектов | Принятое обозначение | Типы объектов БД | Пересоздание или инкрементальный подход. Способ хранения. |
Схема | DDL (Data Definition) | Roles | Нельзя пересоздавать, необходимы инкрементальные изменения. Каждый объект хранится как каталог с набором инкрементальных скриптов модификации структуры. |
Данные | DCL (1) (Data change) | Инкрементальные скрипты модификации данных, сгруппированные по соответствующим таблицам. | Можно пересоздавать некоторые справочники, в остальном необходимы итеративные изменения. Можно перезаливать только статичные данные |
Программные объекты | DML (Data manipulation) | CheckConstraints | Можно пересоздавать без ограничений. Хранятся в отдельных файлах. |
Сложные случаи | AFT | Любые изменения, которые невозможно декомпозировать до основных типов. | Должны выполняться однократно. |
При заливке версия и хэш каждого объекта, в том числе и версия модификации данных, сохраняется в служебной таблице. Дерево проекта выглядит примерно следующим образом:
Для автоматизации сборки и развертывания был написан комплект утилит на Python. Сборщик проекта запускается с указанием версии, обходит дерево проекта и генерирует пронумерованные заливочные скрипты:
Удаление всех программных объектов.
Каждому объекту добавляется обвязка: сверка номера версии с сохраненным в заливочной таблице, расчет и сравнение хэша. Эта сверка обеспечивает строго однократное применение изменений схемы и данных: после заливки счетчик версий DDL/DCL объекта в заливочной таблице обновляется и при повторном накате скрипт той же версии будет пропущен.
Для DDL и DCL последовательно применяются все версии, т. е. изменение схемы таблицы на версию 1.1 — изменение данных на версию 1.1 — изменение схемы на версию 1.2, и т. д.
DML собирается в один файл для каждой базы в порядке view — function — stored procedure, с учетом списка исключений: иногда требуется залить функцию раньше, чем представление, или внутреннюю функцию раньше внешней.
Модификация данных, для которой требуется полная заливка программного кода. Например, обновление данных на значение, рассчитываемое функцией, или вставка данных в таблицу с триггером.
Скрипты создания джобов SQL Agent-а.
Служебные операции.
Для самого крупного проекта сейчас генерируется почти 800 файлов.
Теперь полученный набор необходимо проверить, и в дело вступает утилита заливки. Скрипты накатываются на указанный в конфигурации сервер БД, это может быть машина разработчика или сборочная машина. Один и тот же комплект скриптов может быть залит на любую версию, от «нулевой» без БД до полной версии с данными, и после заливки мы получаем состояние, совпадающее с эталоном.
Хеши кода объектов сохраняются не для красоты. В strict режиме заливка первым делом проверяет соответствие существующих объектов их хэшам. Если обнаружено расхождение, это признак случайного или преднамеренного изменения кода, с которым необходимо разбираться.
Сборка
После решения проблем версионирования интегрироваться в общесистемный сборочный конвейер оказалось достаточно просто. Про сборки подробно рассказывается в одной из предыдущих статей (MOEX DevOps, вы крутые!). Пайплайн, собирающий определенную версию системы, забирает код sql-проектов, генерирует скрипты, заливает их на сборочные инстансы MS SQL, прогоняет unit-тесты, сравнение интерфейсов, автогенерацию кода взаимодействия с другими сервисами, и, если всё прошло успешно, пакует весь сгенерированный код и публикует артефакты в Nexus.
Да, кстати, мы покрыли unit-тестами большую часть огромного легаси монолита.
Unit-тесты
Выбирая решение для модульных тестов, мы с легкой завистью смотрели в сторону Java- и С-команд. У коллег имелся широкий выбор прекрасных систем, интегрируемых в IDE и в автоматизацию, а для MS SQL ничего удобного и подходящего под наши задачи подобрать не получалось. Да, было тяжеловесное решение от Microsoft, был tSqlt, но для сложных процедур с десятками параметров создание тестов в этих фреймворках грозило превратиться в бесконечную монотонную ручную работу. Необходимо было охватить тестами сотни объектов, зачастую содержащих сложную логику, покрываемую несколькими наборами входных параметров, при этом Arrange для теста порой требовал вставки консистентных данных в десяток таблиц.
Хочешь сделать хорошо — сделай сам, и мы написали свой фреймворк на Python.
Сильные стороны нашего фреймворка — автоматизация, унификация тестов и сокращение объема кода тестового модуля. Вся логика Arrange/Act/Assert пишется на SQL.
Перед прогоном модульных тестов на базу накатывается DCL с общими данными, создаются несколько клиентов, торговых инструментов, заполняются сессионные справочники. Записи хранятся в формате JSON, для добавления новых строк нетрудно снять данные с любого полигона разработки.
При разворачивании модуля тестирования в базе создаются служебные функции получения часто используемых эталонных данных. При написании теста не требуется изобретать велосипед, чтобы получить «образцового клиента».
У tSqlt был позаимствован подход FakeTable, в котором выбранная таблица на время прогона теста пересоздается без констрейнтов. Это позволяет добавить только необходимые для теста данные, не беспокоясь об остальных полях.
Все тесты параметризованы. Перед прогоном тестов передаваемые параметры в виде JSON строк заливаются в таблицу, по которой строятся вызовы тестовых модулей. Проверка одной процедуры с десятью наборами параметров — это написание одного модуля и добавление 10 строк в файле параметров.
Для проверки скалярной функции достаточно добавить одну строку в DCL обобщенного теста: параметры, выходное значение, код, выполняемый перед запуском.
Табличные функции и процедуры, возвращающие датасет, также могут (но не обязаны) проверяться единым тестом. Для созданной функции добавляем одну запись с json-ами входных и выходных наборов параметр/поле — значение.
Сложные случаи, не подпадающие под паттерн сверки входных и выходных значений, оформляются в виде отдельных параметризованных модулей.
Прогон тестов осуществляется в два этапа. На первом генератор скриптов соединяет модули и параметры, и для каждой пары создает скрипт теста, добавляя заголовок и обработку ошибок. На втором этапе заливщик накатывает на базу полученные скрипты (сейчас их почти 1800 только в одном из проектов) и передает лог выполнения наружу. Если хотя бы один тест зафейлен, поднимается красный флажок, и сборка останавливается.
Покрытие
Тесты пишутся и выполняются, пайплайн зеленеет, а сколько кода у нас покрыто? Для оценки метрики покрытия взяли опенсорсный SQL Cover, доступный под лицензией Apache 2.0. Перед запуском тестов он подключается к серверу, считывает выполняемый код через Extended Events, сравнивает с объектами в базе и выдает итоговый процент покрытия.
Статический анализ
Для статического анализа SQL кода нашлось прекрасное коммерческое решение — SQL Enlight. Он поставляется с обширным набором правил, которые можно настраивать и кастомизировать. У нас он установлен на машине каждого разработчика для проверки кода на лету и в сборке для проверки диффов.
SQL Enlight сигнализирует о нарушении правил
Автодокументирование
«Вот теперь всё красиво», — сказали нам на планерке, — «а автогенерации документации не хватает». В очередной раз с завистью посмотрев на джавистов, взяли Python и сделали утилиту, которая пробегает по хранимым процедурам, генерирует описание интерфейсов по заголовкам и комментариям и выкладывает артефакты в Nexus.
Интеграция и сравнение интерфейсов
По мере усложнения архитектуры ТКС возросло число модулей, с которыми надо взаимодействовать, и не всегда получалось синхронизировать изменения в одном цикле разработки. Расхождение обнаруживалось только на этапе интеграции, отнимало время и портило метрики. После того, как в ежедневную сборку добавили выкачку артефактов других модулей и сверку сигнатур/схем, несовпадение интерфейсов стало обнаруживаться и исправляться в течение нескольких часов.
А что дальше?
А дальше нас ждет много увлекательных и интересных задач, связанных с переходом на другую СУБД и продолжение декомпозиции монолита на модули. Усилия, вложенные в собственный тестовый фреймворк, уже оправдались: он с легкостью мигрирует на альтернативную СУБД и переносимый код с самого начала будет покрыт тестами. Одни кирпичики нашего процесса будут постепенно заменяться другими, но сам по себе стабильный, предсказуемый и удобный процесс разработки, надеемся, останется с нами надолго и упростит переход на новую систему.
