Практическое занятие №10: управление проектом, этап 2

1. Проблематика: почему схему БД нельзя менять вручную?

Опасность ClickOps & Ad-hoc DDL

  • Рассинхронизация стендов: колонка добавлена на тесте, забыта на стейдже и упала на проде.
  • Блокировки таблиц: неконтролируемый ALTER TABLE захватывает Exclusive Lock и вешает всю СУБД.
  • Невозможность отката: при сбое релиза никто точно не помнит порядок ручных команд.

Решение: Database as Code

  • Контроль версий: все изменения хранятся в Git в виде версионированных скриптов.
  • Автоматизация CI/CD: миграции накатываются сервером сборки автоматически.
  • Полный аудит: СУБД знает автора, время, длительность и контрольную сумму каждого DDL.

2. Инструменты управления миграциями: Flyway vs Liquibase

Flyway (SQL-First подход)

  • Миграции пишутся на чистом, нативном SQL.
  • Идеален для специфических возможностей конкретной СУБД (PostgreSQL, Oracle).
  • Минимальный порог входа, простота чтения и ревью в Pull Request.
  • Строгий формат имен: V1__init.sql, U1__undo.sql.

Liquibase (СУБД-агностичный подход)

  • Поддержка форматов XML, YAML, JSON и SQL (Changesets).
  • Автоматическая трансляция абстрактных DDL под разные СУБД.
  • Встроенные механизмы генерации предварительных условий (Preconditions).
  • Более тяжеловесный и многословный синтаксис файлов.

3. Соглашение об именовании миграций Flyway

Flyway определяет тип и порядок выполнения миграций строго по соглашению об именовании файлов:

V<Версия>__<Описание>.sql   -- Версионированная миграция (Versioned)
U<Версия>__<Описание>.sql   -- Undo-миграция отката (Rollback)
R__<Описание>.sql           -- Повторяемая миграция (Repeatable)
  • Префикс: V — накат, U — откат, R — повторное выполнение при изменении хэша.
  • Версия: число с точками или подчеркиваниями (1, 2.1, 20261001_1200).
  • Разделитель: СТРОГО два символа подчеркивания (__).
  • Описание: читаемый текст, где одиночные подчеркивания превратятся в пробелы.

4. Сердце системы: таблица flyway_schema_history

Перед запуском миграций Flyway проверяет служебную мета-таблицу в целевой базе данных:

CREATE TABLE flyway_schema_history (
    installed_rank INT NOT NULL PRIMARY KEY,   -- Хронологический номер наката
    version VARCHAR(50) NULL,                  -- Номер версии (напр. '1', '2')
    description VARCHAR(200) NOT NULL,         -- Название операции
    type VARCHAR(20) NOT NULL,                 -- Тип: SQL, UNDO, BASELINE
    script VARCHAR(1000) NOT NULL,             -- Имя файла миграции
    checksum INT NULL,                         -- Контрольная сумма файла (CRC32)
    installed_by VARCHAR(100) NOT NULL,        -- Пользователь СУБД
    installed_on TIMESTAMP DEFAULT NOW(),      -- Временная метка применения
    execution_time INT NOT NULL,               -- Длительность в миллисекундах
    success BOOLEAN NOT NULL                   -- Результат: TRUE или FALSE
);

5. Алгоритм наката миграций (Flyway Migrate)

  1. Подключение к БД: установка блокировки (Locking) схемы для исключения одновременного параллельного наката двумя нодами.
  2. Чтение истории: извлечение списка уже примененных версий и их контрольных сумм из flyway_schema_history.
  3. Валидация (Validate): проверка, не был ли изменен текст уже примененных файлов (сверка checksum). Если файл изменили → немедленная остановка с ошибкой!
  4. Сортировка новых скриптов: выстраивание не примененных файлов по номерам версий.
  5. Транзакционное исполнение: выполнение каждого скрипта в транзакции с добавлением записи в историю.

6. Механика безопасного отката (Undo & Rollback)

Откат через Undo-скрипт (U-миграция)

  • Создается файл U2__drop_barcode_from_goods.sql.
  • Выполняет строго обратные действия к V2 (удаление добавленной таблицы/колонки).
  • Удаляет запись о версии 2 из flyway_schema_history.
  • Риск: потеря данных, если в новой колонке уже накопились боевые записи!

Откат вперед (Forward-Only Rollback)

  • В промышленном проде откат чаще делают через новую прямую миграцию V3__revert_barcode_change.sql.
  • История миграций остается непрерывной и монотонно возрастающей.
  • Исключаются конфликты в распределенных командах и кластерах репликации.

7. Непрерывное развертывание: Паттерн Expand / Contract

Как обновить базу без остановки сервиса (Zero-Downtime Deployment)?

1. Фаза Expand (Расширение)

В базу добавляется новая структура параллельно со старой. Старые колонки не удаляются, новые поля объявляются как NULLable.

2. Фаза Transition (Переход)

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

3. Фаза Contract (Сжатие)

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

8. Пример Expand/Contract: Перенос паспортных данных

Сценарий: выделение отдельной таблицы паспортов из таблицы клиентов

-- 1. ЭТАП EXPAND (Миграция V4): создаем новую таблицу
CREATE TABLE client_passports (
    client_id INT PRIMARY KEY REFERENCES clients(client_id),
    series_number VARCHAR(20) NOT NULL,
    issued_by TEXT NOT NULL
);

-- 2. ЭТАП TRANSITION: бэкфилл данных из старой колонки
INSERT INTO client_passports (client_id, series_number, issued_by)
SELECT client_id, passport_data, 'MVD' FROM clients;

-- 3. ЭТАП CONTRACT (Миграция V6, через месяц после обновления клиентов):
ALTER TABLE clients DROP COLUMN passport_data;

9. Регрессионное тестирование клиентского приложения

После выполнения DDL-миграции необходимо удостовериться, что прикладные компоненты сохранили работоспособность:

Тип проверки Что проверяем? Критерий успеха
Совместимость INSERT Старые экранные формы вставки записей Новые поля имеют DEFAULT, вставка без них не вызывает ошибок.
Стабильность VIEW/PROC Хранимые процедуры и отчетные представления Объекты не инвалидированы, возвращают валидные типы данных.
Производительность SELECT Тяжелые поисковые запросы Новые индексы работают (Index Scan), время отклика в пределах SLA.

10. Практические сценарии занятия

Вариант 1. Складской комплекс

Миграция V2: добавление колонки Штрихкод_EAN13 (UNIQUE) в Товары. Регрессионный тест старых форм поиска.

Вариант 2. Гостиничный сервис

Миграция V3: таблица Источники_бронирования и внешний ключ в Бронирования. Проверка стабильности регистрации.

Вариант 3. Банковский портал

Миграция V4: выделение Паспортные_данные из Клиентов с переносом данных и безопасным Undo-скриптом.

Вариант 4. Образовательный портал

Миграция V5: составной индекс idx_results_date_student. Тестирование ускорения формирования сводной ведомости.

11. Критические ошибки проектирования миграций

Топ-3 фатальных ошибок

  • Добавление NOT NULL без DEFAULT: ломает все существующие строки и старые клиенты при вставке.
  • Правка уже примененного файла: ломает валидацию чек-суммы Flyway и останавливает конвейер CI/CD.
  • Асимметричный откат: скрипт отката удаляет таблицу, но забывает удалить созданные ею типы и триггеры.

Инженерный регламент качества

  • Каждая миграция проверяется связкой: Migrate → Test → Undo → Migrate.
  • Все деструктивные операции разделяются на этапы Expand/Contract.
  • Индексы на больших таблицах создаются в фоновом режиме (CONCURRENTLY).

12. Регламент практической работы

  1. Открыть интерактивный рабочий лист zanyatie.html в браузере.
  2. Ввести реальные ФИО и группу (чистые поля без автоплейсхолдеров).
  3. Переключиться на назначенный преподавателем вариант практического сценария (1–4).
  4. Разработать DDL прямой миграции (V_N) и скрипт обратного отката (U_N).
  5. Рассчитать метаданные для записи в flyway_schema_history.
  6. Заполнить протокол регрессионного теста и ответить на вопросы самопроверки.
  7. Сохранить результат и экспортировать протокол в PDF.

13. Критерии оценивания (4-балльная шкала)

2 балла

Скрипт прямой миграции:

Синтаксическая чистота DDL, корректные типы, обратная совместимость (DEFAULT для NOT NULL), уникальные индексы и внешние ключи.

1 балл

Протокол отката и теста:

Зеркальный Undo-скрипт без утечки мусорных объектов и оформленный регрессионный тест совместимости клиентских запросов.

1 балл

Аудит и самопроверка:

Корректный расчет метаданных flyway_schema_history и глубокие аргументированные ответы на блок самопроверки.

14. Классика веб-безопасности: Little Bobby Tables

xkcd Exploits of a Mom
Тематический интернет-мем: «Exploits of a Mom» (культовый веб-комикс про «Little Bobby Tables», xkcd №327).
Автор: Randall Munroe. Первоисточник: xkcd.com/327. Лицензия: Creative Commons Attribution-NonCommercial 2.5 Generic (CC BY-NC 2.5).
Связь с темой занятия: Мама назвала сына Robert'); DROP TABLE Students; --. Когда школа вставила имя ученика через конкатенацию SQL-строки, база данных стерла всех студентов года. Занятие 28 напоминает: любая модификация схемы данных и обработка пользовательских структур должна быть строго контролируемой, версионированной и защищенной от деструктивных изменений!
Слайд 1 из 15