SQL и модульное тестирование

В статье рассказывается о применении технологии модульных тестов при разработке приложений на 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 не имеет типа «дата», хранящего только дату без времени. В других СУБД можно обойтись встроенным типом 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, '2008-01-02 11:22:33', 121),
          @date_without_time = convert(datetime, '2008-01-02 00:00:00', 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. А подобных проверок в теле рядовой процедуры теста используется в среднем около десятка.

Теперь взглянем на файл модульного теста SalesForecast Test.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 'TEST#%' AND 
                products.reference LIKE 'TEST#%' 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 = 'TEST#store1';
   SELECT @product_id = id FROM products WHERE reference = 'TEST#prod2';
   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 это isql.exe или osql.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)

AttachmentSize
Package icon SQLUnit_Sources2.zip378.9 KB