На одном из наших недавних проектов мы столкнулись с серьёзной проблемой. Веб-приложение, которое мы разрабатывали, должно было использовать внутренюю базу данных финансовой организации. Из соображений безопасности, доступ был очень сильно ограничен: любые изменения необходимо было делать при помощи хранимых процедур, а читать данные — только при помощи представлений. Таким образом, приложение должно было выполнять сложные манипуляции данными, не имея никакого представления об их структуре. Основной загвоздкой для нас было то, что наше приложение попадало в зависимость от больших и сложных процедур, для которых не существовало автоматизированных тестов.

Погуглив немного, мы обнаружили, что в штатном инструментарии Oracle SQL Developer [1] есть функционал для создания автоматизированных тестов. Мы тут же приступили к его изучению. И хотя тесты для самой сложной процедуры пришлось создавать уже после её написания, этот инструментарий всё же помог нам устранить несколько ошибок, а также существенно облегчил процесс расширения функционала и рефакторинга. Ниже я приведу пример использования TDD для построения хранимых процедур, а также поделюсь опытом в работе с инструментарием.

Пример использования

Допустим, у заказчика имеется существующее приложение, которое позволяет его клиентам выполнять отправку СМС-сообщений. Ещё одна команда разрабатывает новое приложение, которое должно будет работать параллельно с существующим, поэтому было бы хорошо иметь общее место для бизнес-логики.

Структура данных

Приложение использует следующую структуру данных:

CREATE TABLE CLIENTS(
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
    NAME NVARCHAR2(255) NOT NULL,
    BALANCE NUMBER(*,2) DEFAULT 0 NOT NULL,
    IS_ACTIVE NUMBER(1) DEFAULT 0 NOT NULL,
    IS_PREPAY NUMBER(1) DEFAULT 0 NOT NULL
);

CREATE TABLE MESSAGE_QUEUE(
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
    ID_CLIENT NUMBER NOT NULL,
    SENDER VARCHAR2(20),
    RECIPIENT VARCHAR(20),
    MESSAGE NVARCHAR2(255) NOT NULL,
    QUEUED_ON TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    SEND_ON TIMESTAMP WITH TIME ZONE NULL,
    SENT_ON TIMESTAMP WITH TIME ZONE NULL
);

CREATE TABLE TRANSACTIONS(
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
    ID_CLIENT NUMBER NOT NULL,
    VALUE NUMBER(*,2) NOT NULL,
    TRANSACTION_TIME TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

Для краткости, определения первичных и внешних ключей опущены.

Настройка окружения

Юнит-тестирование в SQL Developer использует базу данных для хранения тестов, их настроек, библиотеки, и результатов выполнения. В этих целях настоятельно рекомендуется создать пользователя для тестирования, затем создать в его базе данных репозиторий. Этот процесс более подробно описан в документации по юнит-тестированию [2].

Терминология тестирования Oracle

Терминология тестирования, которую использует Oracle несколько отличается от общепринятой терминоголии xUnit [3]:

Далее в тексте я буду использовать русский вариант терминологии xUnit.

Неожиданности

Работая с приложением, мы обнаружили, что оно не всегда работает так, как мы ожидали:

• Иногда, все пункты меню юнит-тестирования оказывались отключенными. В таких случаях необходимо щёлкнуть пункт меню View>Unit Test
• Все тесты внутри сценария используют общий набор настройки и сброса контекста, что вполне логично. Но из-за того, что редактируются они через вкладку теста, создаётся ощущение, что их можно персонализировать для каждого теста отдельно.

Разработка с помощью тестирования

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

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

CREATE OR REPLACE PROCEDURE QUEUE_MESSAGE(
  V_ID_CLIENT IN NUMBER,
  V_SENDER IN VARCHAR2,
  V_RECIPIENT IN VARCHAR2,
  V_MESSAGE IN NVARCHAR2,
  V_IS_QUEUED OUT NUMBER)
AS BEGIN
  NULL;
END QUEUE_MESSAGE;

В случае с Oracle, имеет смысл задавать префикс для переменных, имя которых может совпасть с названием поля, так как в случае неясности, знаменитая СУБД решит спор в пользу поля. А во избежании беспорядка, проще давать префикс всем переменным без исключения.

Примечание

Если параметры процедуры изменились, то каждый её тестовый сценарий необходимо обновить вручную, щёлкнув пункт контекстного меню Synchronize Test...

Первый сценарий

Для упрощения нашего примера, допустим, что стоимость одного сообщения — 0.03 каких-то денег. И, как это ни странно, для описания сценария вполне подходит Gherkin:

Дано:
    Активный пост-оплатный клиент

Когда:
    Он отправляет сообщение

То:
    Возвращается положительный результат,
    И стоимость сообщения фиксируется в журнале транзакций,
    К тому же, сообщение добавляется в очередь.

Самый быстрый способ создать тест — щёлкнуть правой кнопкой мыши на процедуре в дереве объектов, затем выбрать пункт меню Create Unit Test.... В появившемся окне можно сразу нажать кнопку Finish. Сценарий QUEUE_MESSAGE с единственным тестом должен появиться в панели Unit Test.

Настройка контекста

Сначала нам необходимо будет заполнить базу необходмыми данными. Для нас самым удобным оказалось использование режима PL/SQL для настройки и сброса контекста. Тем не менее, любой из вариантов легко использовать повторно при помощи публикации в библиотеку. Чтобы скопировать существующий шаг из библиотеки, достаточно выбрать его из выпадающего списка, затем нажать кнопку Copy. А если нужно использовать его без изменений, но вместо кнопки Copy необходимо нажать чекбокс Subscribe.

Осторожно!

Идея использовать существующую БД для тестирования может показаться привлекательной. Казалось бы, сохранил данные в настройке, и восстановил при сбросе контекста… Однако следует иметь в виду, что если в процессе выполнения тестов на любом этапе произошла непредвиденная ошибка, то база данных окажется в том виде, в каком она была во время ошибки, и сброс контекста выполнен не будет. Поэтому лучше всего использовать чистую базу данных, которую не страшно и несложно полностью пересоздать в случае порчи структуры или данных.

Предполагая, что мы работаем с пустой базой данных, для настойки контекста, нам понадобится всего лишь одна вставка записи пост-оплатного клиента. Её можно тут же сохранить в библиотеке, назвав Пост-оплатный клиент.

Сброс контекста

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

Вызов

Непосредственно выполнение теста определяется при помощи задания параметров хранимой процедуры. Здесь же задаются и значения выходных параметров для проверки. Проверку выходных параметров можно отключить при помощи чекбокса Test Result. Он относится к параметрам, заданным как в таблице, так и динамически.

Осторожно!

С виду может показаться, что задавать параметры мышкой в таблице очень удобно, однако необходимо иметь в виду, что эта таблица копированию не подлежит. Это особенно важно для процедур с большим количеством аргументов, так как для создания очередного теста их все придётся заново задавать вручную, особенно когда новый тест отличается от текущего всего лишь на одно значение. Динамический запрос (Dynamic Value Query), в отличие от таблицы, можно сохранять в библиотеке, а затем можно либо повторно использовать, либо копировать.

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

select 1 as V_ID_CLIENT, '79052222222' as V_SENDER, '79161111111' as V_RECIPIENT,
   'Айда гулять!' AS V_MESSAGE, 1 as V_IS_QUEUED$ from DUAL

Примечание

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

Так как мы указали проверку выходного параметра, то уже можно запустить сценарий, и увидеть сбой. Если всё сделано правильно, система должно сообщить об ошибке. Любой другой сбой на этом этапе означает некорректную настройку.

Самый простой способ успокоить тест — внаглую вписать 1 в выходной параметр в теле процедуры: SELECT 1 INTO IS_QUEUED FROM DUAL;

Утверждения

Тест снова зелёный, но мы ещё не проверили все необходимые условия. Их можно проверить в других тестах того же сценария. Перед тем как создавать новый тест, стоит переименовать существующий из дефолтного "Test Implementation 1" в "Положительный результат", а весь сценарий — в "Активный пост-оплатный клиент отправляет сообщение".

Важно

Легко предположить, что каждый тест выполняется внутри транзакции. Однако на поверку это оказалось не так. В случае возникновения непредвиденной ошибки, база данных может оказаться в непределённом состоянии. Ожидаемых ошибок такое поведение не касается.

Наша следующая проверка будет помещена в отдельный тест для получения более тонкой обратной связи, однако, стоит помнить, что каждый новый тест будет затрачивать время на настройку и сброс контекста, а каждый сбой проверки снабжается чётким сообщением о его причине. Мы разделим проверки по разным тестам в этом сценарии, а потом объединим все проверки в один тест в следующем сценарии.

Примечание

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

Следующий тест должен проверить, что сообщение помещено в очередь. Так как настройка и сброс контекста уже указаны, нам необходимо использовать динамический запрос из библиотеки, и задать проверку утверждения. После того как мы скопировали динамический запрос, может показаться, что проверять уже проверенный выходной параметр ни к чему, и можно сбросить чекбокс Test Result. Однако, если прогнать тесты в таком состоянии, то будет видно, что один из тестов проигнорирован. Лично для меня проигнорированный тест — символ незаконченной работы, поэтому флажок придётся поставить на место.

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

-- Please replace this code with either a boolean
-- expression like this:
--     RETURN FALSE;
-- or else a code block which returns a boolean value
-- similar to the following:
DECLARE
    l_count NUMBER;
BEGIN
    SELECT count(*) INTO l_count FROM dual;
    IF l_count <> 0
    THEN
        RETURN TRUE;
    ELSE
        RETURN FALSE;
    END IF;
END;

Для нашей проверки мы можем использовать этот шаблон, заменив dual на MESSAGE_QUEUE, затем применив необходимые фильтры. Условие также придётся сменить с l_count <> 0 на l_count = 1 для большей точности. После этого можно смело сохранять функцию в библиотеку для дальнейшего использования.

Примечание

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

При прогоне тестов мы должны увидеть ошибку. Её очень легко исправить:

  INSERT INTO MESSAGE_QUEUE(ID_CLIENT, SENDER, RECIPIENT, MESSAGE)
    VALUES(V_ID_CLIENT, V_SENDER, V_RECIPIENT, V_MESSAGE);

Теперь можно убедиться, что все тесты проходят с успехом.

Примечание

При работе с тестами репозиторий блокируется, поэтому по окончании работы необходимо либо закрыть SQL Developer, либо закрыть репозиторий (Deselect Repository).

И, напоследок, проверим запись транзакции. Для этого выберем следующий тип валидации — сравнение результатов запросов (Compare Query Results). Как и следует из названия, он работает очень просто: нужно указать два запроса, результаты которых совпадут. Так как точную дату и время узнать невозможно, можно довольствоваться люб значение в пределах 10 секунд:

-- Source query
SELECT 1 AS ID_CLIENT, 0.03 AS SUM_VALUE FROM DUAL

-- Target query
SELECT ID_CLIENT, SUM(VALUE) FROM TRANSACTIONS
WHERE TRANSACTION_TIME BETWEEN CURRENT_TIMESTAMP AND (CURRENT_TIMESTAMP - 1/24/6)
GROUP BY ID_CLIENT;

После прогона тестов мы видим расплывчатую ошибку Validation одна недавняя транзакция: Compare query results check found differences. Где "одна недавняя транзакция" — название нашей последней проверки в библиотеке. И хотя этот вариант уже является ценным инструментом, было бы замечательно, если бы он мог показать чем же именно результаты отличаются.

Добавим нужный функционал в нашу процедуру:

  INSERT INTO TRANSACTIONS(ID_CLIENT, VALUE) VALUES(V_ID_CLIENT, 0.03);

Отладка

После очередного прогона тестов вдруг выясняется, что ошибка никуда не делась. Вы, наверное, уже заметили ошибку в коде выше, однако в реальных условиях ситуации бывают куда более сложными. Так как разницы инструмент на показывает, придётся выяснять причину вручную. К сожалению, отладочный функционал SQL Developer здесь ничем помочь не в состоянии. Это значит, что нам придётся прогнать тест без выполнения сброса. Для этого можно создать ещё один сценарий — отладочный. А точнее два: один — без сброса, но с тем же динамическим запросом, что и в нерабочем тесте — для того чтобы разобраться в чём дело; а второй — без настройки контекста, но со сбросом — для того чтобы убрать за первым.

После запуска первого сценария можно посмотреть содержимое таблицы, и свериться с проверочным запросом. Теперь чётко видно, что проблема заключалась именно в проверочном запросе. Не забыв запустить второй сценарий для очистки данных, поправляем условия теста, и устраиваем повторный прогон. Теперь всё в порядке. Отладочные сценарии можно оставить на будущее, а первый законченный сценарий можно поместить в новый набор тестов.

Второй сценарий

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

Дано:
    Неактивный пост-оплатный клиент

Когда:
    Он отправляет сообщение

То:
    Возвращается отрицательный результат,
    и транзакция не фиксируется,
    и очередь сообщений остаётся неизменной.

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

Для настройки контекста копируем PL/SQL шаг "Активный пост-оплатный клиент", в котором заменяем 1 на 0 и публикуем в библиотеке под названием "Неактивный пост-оплатный клиент". Повторяем то же для динамического запроса, назвав новый запрос "Неотправленное сообщение". Для сброса контекста используем существующий шаг.

После прогона тест должен показать ошибку. Её очень легко исправить. Заменяем SELECT 1 INTO V_IS_QUEUED FROM DUAL на SELECT IS_ACTIVE INTO V_IS_QUEUED FROM CLIENTS WHERE ID=V_ID_CLIENT — и всё снова работает.

Затем необходимо проверить, чтобы транзакция не сохранялась. Для этого используем следующий тип проверки — сравнение таблиц (Compare tables). Поначалу может показаться, что сравнивать не с чем, однако, в настройке контекста есть возможность скопировать существующую таблицу во временную. Нам это прекрасно подходит — можно скопировать транзакции во временную таблицу, а после вызова процедуры сравнить результаты. Главное — не забыть эту таблицу удалить при сбросе контекста. Есть два варианта — восстановить, затем удаилить, и просто удалить. Так как восстанавливать нам нечего — выберем второй вариант. Обратите внимание, что как и в случае со сравнением запросов, единственный вариант обратной связи — есть совпадение или нет.

Полюбовавшись на ошибку после прогона тестов, можно подумать над решением. Например, можно обернуть вставку в условие, исользуя свеже-обновлённый V_IS_QUEUED:

IF V_IS_QUEUED = 1 THEN
  INSERT INTO TRANSACTIONS (ID_CLIENT, VALUE) VALUES (V_ID_CLIENT, 0.03);
END IF;

Компилируем процедуру, прогоняем тесты — всё работает.

В заключение, нам надо проверить, что очередь сообщений осталась без изменений. И хотя руки чесались сразу же поместить вставку сообщения внутрь условия рядом со вставкой транзакции, это было бы поощрением нарушения дисциплины. Поэтому сначала создадим дополнительную проверку для этого утверждения. Следующий тип проверки — Запрос, не возвращающий записей (Query returning no rows). Так как мы полностью очищаем все данные после каждого теста, достаточно будет указать SELECT * FROM MESSAGE_QUEUE в качестве такого запроса.

Прогон тестов показывает ошибку, которую мы с лёгкостью устраняем, помещая вставку внутрь условия. И на этом заканчивается наш второй сценарий.

Выводы

SQL Developer можно использовать для разработки хранимых процедур методом TDD. Невзирая на многочисленные недостатки, этот пакет предоставляет платформу для разработки хранимых процедур, позволяя разработчикам с лёгкостью и уверенностью менять и расширять функционал существующих процедур.

К сожалению, тестовый репозиторий можно создать только в СУБД Oracle. Кроме того, попытки использовать сторонние СУБД типа PostgreSQL или даже MySQL в качестве базы данных для тестирования, заканчиваются крахом подсистемы теститования. Также выяснилось, что использование SQL Developer в системах непрерывной интеграции вызывает массу проблем, но это уже отдельная история.

[1] Oracle SQL Developer (англ.) — http://www.oracle.com/technetwork/developer-tools/sql-developer/overview/index.html
[2] Oracle SQL Developer Help: Unit Testing Repository (англ.) — https://docs.oracle.com/cd/E15846_01/doc.21/e15222/unit_testing.htm#RPTUG45067
[3] xUnit — https://ru.wikipedia.org/wiki/XUnit