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

Статья опубликована в журнале "Мир ПК" №7-2008 и послужила основой для одной из глав книги "СУБД для программиста. Базы данных изнутри".

Место модульного тестирования в системе испытаний

Важнейший этап разработки программной системы - ее испытание на соответствие требованиям. Требования к системе распределяются по уровням детализации, и, значит, соответствующие им тесты имеют различную форму и ответственных за их создание. На самом нижнем уровне располагаются модульные тесты, для которых также используется жаргонное словечко «юнит-тест», калька с английского термина unit test.

Уровень	Тип требований	Источник, документ	Вид теста	Ответственный
Система	Требования к системе	Техническое задание	Системный тест (system test)	Главный инженер/ конструктор
Подсистема (компонент, пакет)	Требования к подсистеме	Технический проект	Интеграционный тест (integration test)	Ведущий инженер-программист
Модуль	Требования к модулю	Спецификация модуля	Модульный тест (unit test)	Инженер-программист

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

Рекламируемые в последние годы методики разработки «от тестов» (TDD -- Test Driven Development) базируются на обязательном создании модульных тестов еще до написания собственно кода и, соответственно, преследовании цели в виде 100%-го покрытия ими этого самого кода приложения. В общем случае покрытие -- это отношение числа функций/методов модуля к числу тестируемых функций. Разумеется, чем больше модульных тестов и больше покрытие, тем выше степень надежности модулей. Однако подобная методика имеет и видимые невооруженным глазом недостатки:

Корректное выполнение модульных тестов не гарантирует соответствие системы требованиям на вышестоящих уровнях (см. таблицу выше).
Сложность разработки модульных тестов, как правило, не меньше сложности разработки собственно тестируемого основного кода. Таким образом, общее время программирования увеличивается в 2-3 раза.

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

Особенности разработки на SQL

В традиционных средах программирования, таких как С++/Delphi/Java/C# и др., модульное тестирование — обычная практика: имеются соответствующая инфраструктура, тесно интегрированная со средой разработки, библиотеки и стандартные методики создания и прогона тестов вручную или в автоматическом режиме. Программисты приложений баз данных, разрабатывающие много серверного кода на SQL и его процедурных расширениях, оказываются в худшем положении: поставщики СУБД, как правило, не включают в комплект не только инструменты для модульного тестирования, но и порой даже простые средства отладки и трассировки (здесь следует отметить, что отсутствие пошаговой отладки не является в данной области критичным фактором). Далеко не все СУБД поддерживают и подобие модульности для разрабатываемых хранимых процедур и функций. Иными словами, задача создания инфраструктуры, инструментов и методики для модульного тестирования ложится на плечи программиста. Один из вариантов разработки с использованием модульных тестов мы и рассмотрим.

Хотя пример реализован для MS SQL Server 2005, возможно использовать его для любой другой СУБД с небольшими изменениями.

Описание задачи

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

На схеме структура и связи максимально упрощены. В таблицах products и stores хранятся список товаров и данные о магазинах. В таблице sales ведется статистика продаж: количество и цена — в разрезе «продукт—магазин—день». А в таблицу sales_forecasts нужно внести данные прогноза продаж: количество и среднюю цену.

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

К статье прилагаются исходные тексты примеров, их можно загрузить по ссылке в конце статьи. В директории Sales расположены файлы, содержащие SQL-скрипты создания БД, таблиц и собственно хранимых процедур и функций. Командный файл install.cmd производит установку, необходимо только изменить название сервера (SET SERVER_NAME=) и, возможно, базы данных примера в командном файле общих параметров set_env.cmd. Каталог SalesTest содержит файлы проекта модульных тестов нашей маленькой подсистемы. Здесь также имеется командный файл установки, но технология разработки несколько отличается и основана на несложном макроязыке. Но об этом чуть позже.

Возможный вид хранимой процедуры (файл SalesForecast.sql), производящей вычисления и заполняющей таблицу прогноза, приведен в листинге 1.

CREATE PROCEDURE dbo.sales_forecast_init
   @first_week     datetime,
   @last_week      datetime,
   @first_week_ref datetime
AS
BEGIN
   SET NOCOUNT ON;

   SELECT @first_week     = dbo.utils_time_to_zero(@first_week),
          @last_week      = dbo.utils_time_to_zero(@last_week),
          @first_week_ref = dbo.utils_time_to_zero(@first_week_ref);

   DELETE FROM sales_forecast
      WHERE week BETWEEN @first_week AND @last_week;

   INSERT INTO sales_forecast
      (store_id,
       product_id,
       week,
       quantity,
       avg_price)
   SELECT s.store_id,
          s.product_id,
          w.start_date,
          sum(s.quantity),
          avg(s.price)
      FROM sales AS s
           LEFT OUTER JOIN dbo.utils_get_weeks_list(@first_week, @last_week) AS w
              ON s.day BETWEEN dateadd(ww, datediff(ww, @first_week, w.start_date), @first_week_ref)
                       AND dateadd(ww, datediff(ww, @first_week, w.start_date) + 1, @first_week_ref)
      GROUP BY s.store_id,
               s.product_id,
               w.start_date;
END;

За рамками листинга остались использованные в тексте процедуры функции utils_time_to_zero и utils_get_weeks_list -- их код приведен в файле Utils.sql. Первая обнуляет время в значении типа datetime. Например, вызов utils_time_to_zero('2008-06-01 01:02:03') возвратит значение '2008-06-01 00:00:00'. Данная функция необходима, так как MS SQL Server 2005 не имел типа «дата» (доступна с версии 2008), хранящего только дату без времени. В других СУБД можно обойтись встроенным типом date при его наличии.

Функция utils_get_weeks_list возвращает таблицу-список недель между двумя заданными датами. Неделя задается датой первого дня (понедельника). Например, вызов функции utils_get_weeks_list('2008-02-04 00:00:00', '2008-02-18 00:00:00') возвратит таблицу из трех строк:

week_num	start_date
1	2008-02-04 00:00:00
2	2008-02-11 00:00:00
3	2008-02-18 00:00:00

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

Создаем специализированный макроязык

Хотя в столь простом случае можно было бы обойтись исключительно средствами самого Transact SQL, решение получилось бы достаточно громоздким. Например, нам понадобятся стандартные процедуры проверок типа «генерировать ошибку, если величина не равна/равна/больше/меньше заданной». Но в Transact SQL при вызове процедур нельзя напрямую передавать им значения, полученные из SQL-запросов или других функций. Следовательно, придется всякий раз объявлять и инициализировать локальные переменные, а затем передавать их в процедуру проверки. Избежать этих и многих других неудобств поможет макропрограммирование.

Определим несколько макросов, которые будем использовать в тексте процедур на Transact SQL. Перед трансляцией процедуры в СУБД исходный текст проходит предварительную обработку макропроцессором, макросы раскрываются, получается чистый SQL. Подобный принцип активно применяется в языках семейства Си/С++. По сути же макропрограммирование позволяет обойти ограничения любого языка и создать на нем собственную предметную надстройку для эффективного решения частных задач.

Утилита, управляющая трансляцией файлов исходных текстов (они имеют расширение *.sqm вместо обычного *.sql), носит название SPM - Stored Procedures Macroprocessor, соответствующий файл spm2.exe находится в каталоге Bin. Для корректной трансляции процедур на сервер необходимо изменить параметры соединения (переменные среды SERVER_NAME и DATABASE_NAME) в командном файле set_env.cmd, которые будут затем использованы в секции вызова isql файла проекта SalesTest\SalesTest.spm2.

Теперь текст процедуры модульного теста нашей функции utils_time_to_zero с использованием макросов будет выглядеть более понятно (листинг 2).

include(Common.sqh)
include(MSSQL.sqh)
include(SqlUnit.sqh)

DeclareProcedure(dbo.test_utils_time_to_zero)
AS
BEGIN
   SET NOCOUNT ON;

   DECLARE @date_with_time    datetime,
           @date_without_time datetime;

   SELECT @date_with_time    = convert(datetime, &#39;2008-01-02 11:22:33&#39;, 121),
          @date_without_time = convert(datetime, &#39;2008-01-02 00:00:00&#39;, 121);

   SQLUnit_AreEquals(
      datetime,
      {dbo.utils_time_to_zero(@date_with_time)},
      {@date_without_time},
      {Error truncate time})
END;
GO

Если изучить макрос SQLUnit_AreEquals, например посмотрев на сгенерированный файл UtilsTest.sql, то можно увидеть кусок рутинного кода, не подлежащего выносу в хранимую процедуру или функцию. Четыре строки макроса раскрываются в 14 строк чистого Transact SQL. А подобных проверок в теле рядовой процедуры теста используется в среднем около десятка.

Теперь взглянем на файл модульного теста SalesForecastTest.sqm. В процедуре test_sales_forecast_setup производится заполнение таблиц временными данными для теста. Следовательно, в test_sales_forecast_teardown мы их удаляем. Термины setup (инициализация) и teardown (очистка) -- стандарты де-факто в модульном тестировании, поэтому мы их не изменяем. Сам тест проводится процедурой test_sales_forecast_init, использующей макросы (листинг 3).

DeclareProcedure(dbo.test_sales_forecast_init)
AS
BEGIN
   SET NOCOUNT ON;

   DECLARE @first_week     datetime,
           @last_week      datetime,
           @first_week_ref datetime;
   SELECT
      @first_week     = TestWeek21,
      @last_week      = TestWeek22,
      @first_week_ref = TestWeek11;


   EXEC dbo.sales_forecast_init
      @first_week     = @first_week,
      @last_week      = @last_week,
      @first_week_ref = @first_week_ref;


   SQLUnit_MoreThan(
      int,
      {SELECT count(*)
          FROM sales_forecast
               INNER JOIN stores ON sales_forecast.store_id = stores.id
               INNER JOIN products ON sales_forecast.product_id = products.id
          WHERE stores.reference LIKE &#39;TEST#%&#39; AND
                products.reference LIKE &#39;TEST#%&#39; AND
                week BETWEEN @first_week AND @last_week
      },
      {0},
      {Sales forecasts has no data});

   /* Check calculated data on random week */
   DECLARE @store_id      uniqueidentifier,
           @product_id    uniqueidentifier,
           @week_to_check datetime,
           @ref_week      datetime;

   SELECT @store_id = id FROM stores WHERE reference = &#39;TEST#store1&#39;;
   SELECT @product_id = id FROM products WHERE reference = &#39;TEST#prod2&#39;;
   SELECT @week_to_check =
      dateadd(ww,
              datediff(ww, @first_week, @last_week) * (SELECT rand_value FROM rand2),
              @first_week);
   SELECT @ref_week = dateadd(ww, datediff(ww, @first_week, @week_to_check), @first_week_ref);

   SQLUnit_AreEquals(
      int,
      {SELECT quantity
          FROM sales_forecast
          WHERE store_id = @store_id AND
                product_id = @product_id AND
                week = @week_to_check
      },
      {SELECT SUM(quantity)
          FROM sales
          WHERE store_id = @store_id AND
                product_id = @product_id AND
                [day] BETWEEN @ref_week AND dateadd(ww, 1, @ref_week)
      },
      {Invalid quantity});

   SQLUnit_AreEquals(
      int,
      {SELECT avg_price
          FROM sales_forecast
          WHERE store_id = @store_id AND
                product_id = @product_id AND
                week = @week_to_check
      },
      {SELECT AVG(price)
          FROM sales
          WHERE store_id = @store_id AND
                product_id = @product_id AND
                [day] BETWEEN @ref_week AND dateadd(ww, 1, @ref_week)
      },
      {Invalid average price});

END;

Запуск теста выполняет процедура test_sales_forecast_all (листинг 4).

DeclareProcedure(dbo.test_sales_forecast_all)
AS
BEGIN
   SET NOCOUNT ON;
   EXEC dbo.test_sales_forecast_setup;
   EXEC dbo.test_sales_forecast_init;
   EXEC dbo.test_sales_forecast_teardown;
END;
GO

Теперь, имея одну точку входа для запуска тестов, мы можем легко автоматизировать процесс с помощью командного файла run_tests.cmd и утилиты выполнения SQL командной строки, поставляемой с любой СУБД. Для MS SQL Server это osql.exe или sqlcmd.exe, использующие ODBC-соединение.

Запускаем командный файл и видим непосредственный итог наших тестов (листинг 5).

@echo off

call "%~d0%~p0..\set_env.cmd"

echo Testing utils module...
osql -b -r 0 -E -S %SERVER_NAME% -d %DATABASE_NAME% -Q "EXEC dbo.test_utils_all" -o %OUTPUT_FILE%
if errorlevel 1 goto batch_failed

echo Testing sales forecast module...
osql -b -r 0 -E -S %SERVER_NAME% -d %DATABASE_NAME% -Q "EXEC dbo.test_sales_forecast_all"  -o %OUTPUT_FILE%
if errorlevel 1 goto batch_failed

goto all_done

:batch_failed
echo Test FAILED
exit /b 1

:all_done
echo Test OK
exit /b 0

Остановиться и оглянуться

Обратите внимание, наша процедура test_sales_forecast_init проводит весьма простую проверку: мы сверяем цифры по одному товару и одному магазину за единственную неделю, выбранную случайным образом из заданного диапазона. При этом ее текст даже с применением лаконичных макросов растягивается на 80 строк. А без использования — на 110 строк (см. сгенерированный SQL). Если еще учесть примерно 80 строк предварительной инициализации и очистки, то получается очень много. Ведь текст собственно тестируемой процедуры sales_forecast_init занимает всего 33 строки!

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

Сергей Тарасов, май 2008 (с изменениями: декабрь 2008)

Исходные тексты: SQLUnit_Sources2.zip