Форсированное тестирование процедур T-SQL

Использование динамических запросов и web-отчетов для автоматизации тестов

Раньше тестирование процедур, как правило, являлось заключительной «санитарной» проверкой перед выпуском новой процедуры: четко ли работает процедура и выполняет ли все возложенные на нее задачи? Если ответ положительный, то можно выпускать процедуру и двигаться дальше. Только у некоторых администраторов баз данных находилось время заняться нетипичными сценариями использования, такими как неверные значения параметров, отсутствие данных или недоступные объекты, например рабочие таблицы. Но этот линейный подход больше не работает. Теперь известно, что хранимые процедуры, как и другое программное обеспечение, перед выпуском необходимо тестировать и перепроверять до тех пор, пока они не будут соответствовать установленным стандартам. И здесь нас ожидает препятствие, потому что тестирование модуля - процесс непростой. Оно требует трудоемкого ввода тестовых значений, отслеживания результатов теста в попытке определить, выполняет ли процедура все то, что предполагалось - и ничего более.

Когда задача не так проста, программист, естественно, начинает искать средства автоматизировать рутинную работу. Разве не было бы замечательно сократить время на тестирование модуля хранимых процедур T-SQL? Теперь это возможно. Мы можем автоматизировать тестирование процедуры путем использования только Query Analyzer и небольшого кода на T-SQL. Такое решение имеет четыре легко реализуемых компонента, и этот список можно по необходимости дополнить: таблица, хранящая тестовые варианты и идентифицирующая процедуры, которые вы собираетесь выполнять; динамически генерируемый код на T-SQL, выполняющий тестовые примеры в соответствии с процедурами; таблица, хранящая результаты тестов и системную хранимую процедуру сервера SQL Server sp_makewebtask для создания отчета по результатам тестов.

Проверка модулей и автоматизация тестирования

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

Иногда дело осложняется выбором верных тестовых значений и определением системы взаимодействия этих значений с состоянием базы данных, в то время как вы выполняете свои тесты. Допустим, что вы уже знаете, какие тестовые значения хотите использовать, не роясь в стратегиях выбора значений, таких как равнозначное деление (например, выбор входных значений, которые осуществляют действительные и недействительные состояния ресурса, такие как вставка в существующую таблицу - действительное - и, наоборот, попытка вставки в несуществующую таблицу - недействительное) и полузначимый анализ (например, выбор входных значений, исследующих крайние условия, такие как месяцы 1 и 12 при проверке дат). Под состоянием базы данных подразумеваются ее предварительные условия (т.е. требования или зависимости), такие как необходимые процедуре объекты и сведения о том, имеет ли пользователь доступ к этим объектам.

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

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

Запись тестовых вариантов

Как происходит автоматизация исполнения тестовых вариантов? На практике тестовый вариант включает в себя название процедуры и любые ассоциативные входные значения, параметры выхода или возвращаемые значения. Начнем с создания таблицы для хранения имен и параметров некоторых показательных процедур для последующего тестирования, что показано в Листинге 1. Чтобы не усложнять код, в одну и ту же таблицу тестовых значений tabTestValues включены и имена процедур, и данные параметра. В этом случае все тестовые данные расположены централизованно, так, что можно легко добавить новые тестовые варианты и запросить таблицу отчетов о подтверждении качества. Если необходима нормализация, можно создать отдельные таблицы для процедур и данных параметра. Таблица 1 иллюстрирует описание каждого столбца таблицы тестовых значений tabTestValues. При желании можно легко расширить tabTestValues для включения дополнительных сведений, таких как автор тестового варианта; указание даты и времени; номер версии процедуры (в целях контроля сохранности и управления созданием); собственник процедуры (для уведомления, особенно в случае неудачи теста); и требования к использованию или особенности внедрения процедуры (для обеспечения возможности восстановления).

Теперь, когда появилось место для хранения тестов, заполним таблицу tabTestValues несколькими образцами тестовых вариантов. Первые три команды INSERT в Листинге 2 создают тестовый случай для хранимой процедуры под названием Sproc_1, которая занимает три строки в таблице tabTestValues - одну строку для каждого из его входных параметров. В Листинге 3 показан процесс создания кода для хранимых процедур, ссылающихся на таблицу tabTestValues. Sproc_1 не имеет параметров выхода и только выводит свои входные значения.

Следующие четыре команды INSERT в Листинге 2 создают тестовый случай для Sproc_2, процедуры, содержащей три входных параметра и выходной параметр - совокупная стоимость - это тип данных «деньги». Структура Sproc_2 более сложная. При получении данных о названии товара, количестве и цене Sproc_2 подсчитывает общую стоимость заказа (quantity * price - количество * цена) и затем использует параметр выхода @total, чтобы перейти в процесс вызова (в данном случае Query Analyzer). Две последующие команды INSERT в Листинге 2 создают тестовый случай для Sproc_3. Эта процедура требует last name, затем использует выходной параметр для возврата сообщения об ошибке типа varchar(30). Эта логическая схема показывает, как автоматизированный тест-драйвер может управлять разными типами данных. Наконец, процедура up_lookupPrice запрашивает таблицу под названием Products - Товары (которая была намеренно пропущена в этом примере, чтобы вызвать ошибку "missing resourse") - о цене, затем возвращает эту цену в вызывающую программу.Up_lookupPrice представляет дополнительную реализацию путем дополнительных проверок на ошибки в реальном времени и возвращения кодов, используемых обычно на уровне создания хранимых процедур. Эта процедура также воздействует на тест-драйвер для взаимодействия с общим нарушением зависимости в прохождении таблицы Products.

Автоматическое тестирование с Up_autoTest

Теперь, когда таблица Тестовых Значений - tabTestValues - загружена тестовыми вариантами, рассмотрим внимательнее тест-драйвер, хранимую процедуру up_АutoTest, что показано в Листинге 4. up_AutoTest отражает текущее состояние тестовых окружений. Эта хранимая процедура имеет множество достоинств. Она самодостаточна (в данном случае выполняется непосредственно из Query Analyzer), тестирует любое количество процедур за один прием и выполняет любое количество тестов на одной и той же процедуре. Кроме того, данная хранимая процедура работает со всеми процедурами общего пользования (процедурами без определенных параметров, процедурами только с входными параметрами или выходными параметрами), поддерживает любое количество входных параметров (вплоть до разрешенного предела T-SQL), поддерживает все стандартные типы данных T-SQL (например, int, money,char). Up_autoTest обеспечивает стандартный формат для всех тестов, получает возвращаемые значения, как и выходные значения, когда они доступны; позволяет тестерам и разработчикам процедур по необходимости работать независимо друг от друга. Она проста в применении, поддержке и модификации.

Чтобы понять, как работает Up-autoTest, рассмотрим механизмы использования Query Analyzer для проверки вручную хранимой процедуры Up_lookupPrice, что показано в Листинге 3. Для начала следует объявить локальную переменную - @price - чтобы получить выходное значение процедуры up_lookupPrice. Затем, для проверки условий возможной ошибки, следует объявить еще одну локальную переменную - @return value - для получения возвращаемого значения процедуры up_lookupPrice. Далее следует выбрать тестовое значение - такое как 4 - для входного параметра @product_id процедуры up_lookupPrice, @product_id. И, наконец, необходимо включить команду EXEC, за которой следуют запросы SELECT, демонстрирующие значения @price и @return_value. Приведенный ниже код показывает процесс такого тестирования:

DECLARE @price money, @return_value int 
EXEC @return_value = up_lookupPrice 4, @price OUTPUT 
SELECT @price "Unit price" 
SELECT @return_value "Return Value"

Поскольку требуемой таблицы Products не существует, выполнение процедуры up_lookupPrice приводит к значению null Unit Price и возвращаемому значению 10 (пользовательский код, который означает отсутствие таблицы в Листинге 3). Из-за этой ошибки получаем заключение о том, что процедура up_lookupPrice данный тест не прошла.

Возникает вопрос, как автоматизировать шаги, выполняемые вручную для проверки процедуры up_lookupPrice. Таблица TabTestValues уже содержит всю необходимую информацию: название процедуры, имена и тестовые значения всех входных параметров, имена и ожидаемые значения для всех выходных параметров. Поэтому для автоматизации всех этапов тестирования нужно просто написать код на T-SQL, который объединит все эти данные в тестовые сценарии, такие как тот, что уже составлен вручную, а затем выполнит эти сценарии. Далее код на T-SQL сравнит ожидаемые и полученные результаты, перехватит любой указанный код ошибки, определит результаты теста, запишет их для дальнейшего использования и выпустит Web-отчет, демонстрирующий результаты теста.

Создание кода на T-SQL - главное в этом процессе, но только способность выполнять хранимые процедуры (или другой код на T-SQL) с ходу делает возможным автоматизированное тестирование. Задерживая конструкцию запроса до времени выполнения, создатели теста могут хранить имена процедур и детали теста в таблице метаданных (такой как таблица tabTestValues), не беспокоясь о том, когда, кем или как часто эти тесты в конечном счете будут выполняться.

Для наглядности рассмотрим на примере Листинга 4, как хранимая процедура up_AutoTest динамически тестирует up_lookupPrice. Сначала процедура up_AutoTest идентифицирует up_lookupPrice как тестируемую процедуру путем выбора ее proc_name и ассоциативных параметров, внесенных в таблицу tabTestValues. Далее процедура up_AutoTest использует курсор для динамичного построения тестового сценария, связывающего нужный синтаксис исполнения (имя процедуры наряду с любыми параметрами и шаблонными переменными, необходимыми для получения возвращаемых значений или выходных данных параметров) с локальной переменной, @query. Как только скрипт готов, процедура up_AutoTest выполняет @query.

Как предполагалось, процедура up_AutoTest выполняет любое количество последовательных единичных тестов. Если необходимо выполнить отдельные процедуры, можно переработать up_AutoTest, включив один входящий параметр со ссылкой на имя процедуры. Схема 1 демонстрирует последовательность этапов процедуры up_AutoTest.

Отчет по результатам тестов

Согласно требованиям, результаты тестов должны быть оформлены в отчет. Для создания отчета понадобится еще одна таблица, tabTestResults, которая создается кодом в Листинге 5. Теперь, после выполнения всех тестов, хранимая процедура up_AutoTest запрашивает в таблице tabTestResults возвращаемые значения, выходные параметры и ожидаемые тестовые результаты. Затем данная процедура оценивает эти три вида значений и определяет результат теста. Возможны и другие пути определения тестовых результатов. В заключение, эта процедура устанавливает графу test_result на Pass или Fail (Выполнен - Не выполнен) и модернизирует tabTestResults для включения результатов теста.

Чтобы работа по тестированию была доступна другим пользователям, процедура up_AutoTest также включает автоматический web-отчет. Следует только определить действующий маршрут файла в заключительном блоке кода Листинга 4, и процедура up_AutoTest запросит в таблице tabTestResults необходимые данные, а затем выполнит системную процедуру sp_makewebtask, исполнительный механизм Web Assistant в SQL-Server, выпустив web-отчет о результатах теста. В случае если эти результаты могут потребоваться, sp_makewebtask предлагает параметры для генерации web-отчетов по требованию пользователя, включая варианты планирования работы через планировщик задач службы SQL Server’s Agent service.

В процессе выполнения процедуры up_AutoTest в Web-отчете по результатам теста (Test Results Web) будет видно, что процедура up_lookupPrice не выполнила созданный тест с той же самой ошибкой в пользовательском коде (10), как и ранее - и, таким образом, предупреждает об отсутствии таблицы Products table. Три другие процедуры выполнили свои тесты, возвращая код выполнения (0) (без ошибок) программе вызова. Sproc_1 не содержит выходных параметров (отсюда отсутствие флажка - None flag - в столбце реальных выходных значений - the Actual Output Value column, тогда как Sproc_2 и Sproc_3 передают обратно ожидаемые выходные значения.

Использование Up_AutoTest

В процессе использования автоматизированного тестирования нужно помнить, что эффективность теста зависит как от созданных тестовых случаев, так и от выполняемых тестовых процедур. Вызывают ли тестовые случаи ожидаемую линию поведения (например, корректировку поля) процедуры? Если нет, то необходимо пересмотреть используемые входные значения или провести предварительное тестирование состояния базы данных (например, значений в столбцах, разрешений доступа). Получаются ли ожидаемые данные (возвращаемые значения, выходные параметры, изменения в таблице)? Пока процедура не осуществляет автоматическую проверку результата работы оператора выбора SELECT или изменений в таблице, произошедших в результате выполнения установок INSERT, UPDATE или DELETE. Если эти проверки важны для достижения цели теста, можно выполнить их вручную или расширить процедуру up_AutoTest для выполнения этой работы.

Применяйте на практике

Теперь вы можете автоматизировать модульные тесты для хранения процедур. Эта структура и ассоциативный тест-драйвер up_AutoTest, который используется в примерах этой статьи, поддерживает многие повторяющиеся тестовые сценарии. Использование up_AutoTest поможет сэкономить время модульного тестирования новой процедуры, перепроверки процедуры, созданной ранее, отладки процедур и выполнения обратных тестов для выявления нежелательных побочных эффектов или процедур модульного тестирования через базу данных в целом или прикладную систему. Если есть желание увидеть на практике, насколько эффективным будет автоматизированное тестирование, скопируйте и разместите листинги этой статьи в анализаторе Query Analyzer и проведите тест-драйв для up_AutoTest. Результат вас не разочарует.

Схема № 1. Рабочие шаги автоматизированной тестовой процедуры up_AutoTest Листинга 4.

1. Восстановить тестовый вариант из таблицы tabTestValues;
2. Объявить и инициализировать тестовые переменные в таблице tabTestValues;
3. Создать курсор, чтобы восстановить tabTestValues для каждого параметра;
4. Динамично создать список параметров для тестируемой процедуры;
5. Динамично построить запрос об исполнении для данной процедуры;
6. Динамично выполнить процедуру;
7. Поместить выходные результаты теста в таблицу tabTestResults;
8. Возвращаться к шагу 1 до тех пор, пока все тестовые случаи не будут обработаны;
9. Обновить таблицу tabTestResults в соответствии с результатами теста;
10. Создать web-отчет по таблице tabTestResults.

Таблица 1. Столбцы значений и функций в таблице tabTestValues.

Листинг 1. Код, создающий таблицу tabTestValues.

CREATE TABLE tabTestValues (
  proc_name varchar(24), proc_type char(1), test_number int,
  parm_name varchar(32), data_type varchar(15), parm_type
  varchar(12), test_value varchar(24)
)

Листинг 2. Код для заполнения таблицы tabTestValues.

INSERT tabTestValues  VALUES ('sproc_1', 'I', 1,

 'first_name', 'varchar(24)', 'input', 'jerry')

INSERT tabTestValues  VALUES ('sproc_1', 'I', 1,

 'last_name', 'varchar(24)', 'input', 'feldsein')

INSERT tabTestValues  VALUES ('sproc_1', 'I', 1,

 'age', 'int', 'input', 54)GO

INSERT tabTestValues  VALUES ('sproc_2', 'O', 2,

 'product', 'varchar(24)', 'input', 'mink')

INSERT tabTestValues  VALUES ('sproc_2', 'O', 2,

 'quantity', 'int', 'input', 2)

INSERT tabTestValues  VALUES ('sproc_2', 'O', 2,

 'unit_price', 'money', 'input', 500.30)

INSERT tabTestValues  VALUES ('sproc_2', 'O', 2,

 'total_cost', 'money', 'output', 1000.60)GO

INSERT tabTestValues  VALUES ('sproc_3', 'O', 3,

 'last_name', 'varchar(15)', 'input', 'jones')

INSERT tabTestValues  VALUES ('sproc_3', 'O', 3,

 'error_message', 'varchar(30)', 'output', 'Logic Error: 100 ')GO

INSERT tabTestValues  VALUES ('up_lookupPrice', 'O', 4,

 '@product_id', 'int', 'input', 7)

INSERT tabTestValues  VALUES ('up_lookupPrice', 'O', 4,

 '@product_price', 'money', 'output', 29.95)

Листинг 3. Хранимые процедуры, на которые ссылается таблица tabTestValues.

CREATE PROCEDURE sproc_1
 @first_name varchar(24) = 'dan',
 @last_name varchar(24) = 'johnson',
 @age int = 40
AS
 PRINT 'Input value 1: ' + @first_name
 PRINT 'Input value 2: ' + @last_name
 PRINT @age
 RETURN 0
GO
CREATE PROCEDURE sproc_2
 @product varchar(24) = 'mens winter coat',
 @quantity int = 1,
 @unit_price money = 22.54,
 @total money output
AS
 SELECT @total = @quantity * @unit_price
 RETURN 0
GO
CREATE PROCEDURE sproc_3
 @last_name varchar(15) = 'lastname',
 @error_message varchar(30) output
AS
 SELECT @error_message = 'Logic Error: 100'
 RETURN 0
GO
CREATE PROCEDURE up_lookupPrice
  @product_id int,
  @product_price money OUTPUT
AS
 IF object_id('dbo.products') IS NULL
    RETURN 10                                       /* Data table does not exist. */
 SELECT @product_price = product_price
   FROM Products WHERE product_id = @product_id
 IF @@rowcount = 0                         /* row not found */
    RETURN 11
 IF @product_price IS NULL                /* unit_price is null */
    RETURN 12
 RETURN
GO

Листинг 4. Хранимая процедура up_AutoTest.

CREATE PROCEDURE up_autotest
AS
 DECLARE @test_number int, @number_tests int
 SET @test_number = 0
 
/* Get number of tests to run. */
 SET @number_tests = (SELECT max(test_number) FROM tabTestValues )
 
/* For each test... */
 WHILE @test_number < @number_tests
  BEGIN
 
/* Declare and initialize. */
  SET @test_number = @test_number + 1
  DECLARE @proc_type char(1), @parm_type varchar(12), @test varchar(4),
   @parm_name varchar(12), @data_type varchar(15), @sproc_name varchar(24),
   @parm_value varchar(24), @parm_list varchar(128), @tabTestValues  varchar(128), @count 
      tinyint
  SET @count = 0
  SET @parm_list = NULL
  SET @tabTestValues  = NULL
 
/* Get test values.*/
  DECLARE curParms cursor FOR
   SELECT proc_name, proc_type, test_number, parm_name, data_type, parm_type, test_value
   FROM tabTestValues 
   WHERE test_number = @test_number
  FOR read only
  OPEN curParms
  FETCH curParms INTO @sproc_name, @proc_type, @test_number,
    @parm_name, @data_type, @parm_type, @parm_value
 
/* Build parameter list.*/
  WHILE (@@fetch_status = 0)
    BEGIN                                   /* For each test... */
 
  /* Verify that @parm-list is long enough to hold all parameters. */
    IF len(@parm_list) > 128
      RETURN 10                             /* Parameter list exceeds 128 characters. */
    IF @count = 0                       /* First parameter doesn't need a comma. */
     BEGIN
       SET @parm_list = @parm_value
       SET @tabTestValues  = @parm_name + ' = ' + @parm_value
     END
     ELSE
     IF @parm_type = 'output' 
      SET @parm_list = @parm_list + ', @output_parm OUTPUT '
     ELSE
     BEGIN
       SET @parm_list = @parm_list + ', ' + @parm_value
       SET @tabTestValues  = @tabTestValues  + ', ' + @parm_name + ' = ' + @parm_value 
     END
     FETCH curParms INTO @sproc_name, @proc_type, @test, @parm_name, @data_type, 
       @parm_type,  @parm_value
     SET @count = @count + 1
     END
 
/* Close cursor. */
  CLOSE curParms
  DEALLOCATE curParms
 
/* Build procedure query. */ 
  DECLARE @query varchar(500)
  SET @query = ''
/* Procedures with output parameters require different format. */
  IF @proc_type = 'O'
    BEGIN
     SET @query =
      ' DECLARE @return_status int, @output_parm ' + @data_type +
      ' EXECUTE @return_status = ' + @sproc_name + ' ' + @parm_list +
 
         ' INSERT TabTestResults (test_number, proc_name, tabTestValues , return_value, 
              expect, output )' +
         ' values (' + @test + ',''' + @sproc_name + ''',''' + @tabTestValues  + ''', 
              @return_status, '
 
  IF @data_type = 'int' or @data_type = 'money'  
     /* @parm_value must handle strings as well as numbers. */
       BEGIN              /* Data type is numeric. */
       SELECT @query = @query +
         ' str(' + @parm_value + ',10,2) ,' + ' str(@output_parm,10,2)) ' 
       END
       ELSE               /* Data type is varchar. */
       SELECT @query = @query + '''' + @parm_value + ''',@output_parm )' 
    END
 
  ELSE                    /* Procedure has no output parameter. */ 
    BEGIN
         SET @query = 
             ' DECLARE @return_status int ' + 
             ' EXECUTE @return_status = ' + @sproc_name + ' ' + @parm_list +
             ' INSERT tabTestResults (output, test_number, proc_name,
             return_value, expect, tabTestValues ) ' +
             ' values (' + '''None'',' + @test + ',''' + @sproc_name + ''',
             @return_status, ' + '''None''' + ', ''' + @tabTestValues  + ''')'
    END
 
/* Execute procedure dynamically. */
    EXEC (@query)
 
 END
 
/* Compute/store test results: */
    UPDATE TabTestResults
    SET output =
              CASE
                 WHEN output IS NULL
                     THEN 'Missing'
                 ELSE
                     output
              END,
                 test_result = 
              CASE
                 WHEN output = 'None' and return_value = 0
                      THEN 'Passed'
                      WHEN expect = output and return_value = 0
                      THEN 'Passed' 
              ELSE
                    'Failed'
              END
 
/* Produce Web report for test results. */ 
  EXEC sp_makewebtask
    @outputfile = 'c:\webtask.htm',
    @resultstitle = 'Test Results',
    @query = 'SELECT test_number AS ''Test'',
                                    proc_name AS ''Procedure'',
                                    tabTestValues  AS ''Test Values'', 
                                    return_value AS ''Return Value'', 
                                    expect AS ''Expected Value'', 
                                    output AS ''Actual Output Value'',
                                    test_result AS ''Test Result''          
                     FROM TabTestResults
                ORDER BY test_result, proc_name'
  RETURN 0
  

Листинг 5. Код, создающий таблицу tabTestResults.

CREATE TABLE tabTestResults (
  test_number varchar(4),
  proc_name varchar(24),
  return_value int, 
  expect varchar(24),
  output varchar(24),
  test_result varchar(8),
  tabTestValues  varchar(128) )