Лучший способ протестировать [закрытые] SQL-запросы

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

Зачем писать свой SQL таким образом?

Декомпозируйте свои запросы, точно так же, как вы разлагаете свои функции. Это делает их короче, проще, легче для понимания, легче для тестирования , легче для рефакторинга. И это позволяет вам добавлять между ними «прокладки» и «обертки», как вы это делаете в процедурном коде.

Как вы это делаете? Делая каждую значимую вещь, запрос делает в представление. Затем вы составляете более сложные запросы из этих более простых представлений, точно так же, как вы сочиняете более сложные функции из более примитивных функций.

И самое замечательное, для большинства композиций представлений вы получите точно такую ​​же производительность от вашей RDBMS. (Для некоторых вы этого не сделаете; ну и что? Преждевременная оптимизация - корень всего зла. Сначала правильно кодируйте, , а затем оптимизируйте, если вам нужно.)

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

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

Вот базовая таблица в примере:

create table month_value( 
    eid int not null, month int, year int,  value int );

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

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

create view cm_absolute_month as 
select *, year * 12 + month as absolute_month from month_value;

Теперь то, что мы должны проверить, присуще нашей спецификации, а именно, что для любого кортежа (год, месяц), есть один и только один (absolute_month) ), и эти (absolute_month) являются последовательными. Давайте напишем несколько тестов.

Наш тест будет SQL-запросом select со следующей структурой: имя теста и оператор case, соединенные вместе. Имя теста - это просто произвольная строка. Оператор case - это просто случай, когда тестовых операторов затем «пройдено», иначе «не удалось» завершиться .

Тестовые операторы будут просто SQL-выборками (подзапросами), которые должны быть истинными для тест для прохождения.

Вот наш первый тест:

--a select statement that catenates the test name and the case statement
select concat( 
-- the test name
'For every (year, month) there is one and only one (absolute_month): ', 
-- the case statement
   case when 
-- one or more subqueries
-- in this case, an expected value and an actual value 
-- that must be equal for the test to pass
  ( select count(distinct year, month) from month_value) 
  --expected value,
  = ( select count(distinct absolute_month) from cm_absolute_month)  
  -- actual value
  -- the then and else branches of the case statement
  then 'passed' else 'failed' end
  -- close the concat function and terminate the query 
  ); 
  -- test result.

Выполнение этого запроса дает такой результат: Для каждого (год, month) есть один и только один (absolute_month): пройдено

Если в month_value достаточно тестовых данных, этот тест работает.

Мы также можем добавить тест для достаточного количества тестовых данных:

select concat( 'Sufficient and sufficiently varied month_value test data: ',
   case when 
      ( select count(distinct year, month) from month_value) > 10
  and ( select count(distinct year) from month_value) > 3
  and ... more tests 
  then 'passed' else 'failed' end );

Сейчас давайте проверим его последовательно:

select concat( '(absolute_month)s are consecutive: ',
case when ( select count(*) from cm_absolute_month a join cm_absolute_month b 
on (     (a.month + 1 = b.month and a.year = b.year) 
      or (a.month = 12 and b.month = 1 and a.year + 1 = b.year) )  
where a.absolute_month + 1 <> b.absolute_month ) = 0 
then 'passed' else 'failed' end );

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

пройдено

До тех пор, пока в month_value достаточно тестовых данных, этот тест работает.

Мы также можем добавить тест для достаточных тестовых данных:

select concat( 'Sufficient and sufficiently varied month_value test data: ',
   case when 
      ( select count(distinct year, month) from month_value) > 10
  and ( select count(distinct year) from month_value) > 3
  and ... more tests 
  then 'passed' else 'failed' end );

Теперь давайте проверим его последовательно:

select concat( '(absolute_month)s are consecutive: ',
case when ( select count(*) from cm_absolute_month a join cm_absolute_month b 
on (     (a.month + 1 = b.month and a.year = b.year) 
      or (a.month = 12 and b.month = 1 and a.year + 1 = b.year) )  
where a.absolute_month + 1 <> b.absolute_month ) = 0 
then 'passed' else 'failed' end );

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

пройдено

До тех пор, пока в month_value достаточно тестовых данных, этот тест работает.

Мы также можем добавить тест для достаточных тестовых данных:

select concat( 'Sufficient and sufficiently varied month_value test data: ',
   case when 
      ( select count(distinct year, month) from month_value) > 10
  and ( select count(distinct year) from month_value) > 3
  and ... more tests 
  then 'passed' else 'failed' end );

Теперь давайте проверим его последовательно:

select concat( '(absolute_month)s are consecutive: ',
case when ( select count(*) from cm_absolute_month a join cm_absolute_month b 
on (     (a.month + 1 = b.month and a.year = b.year) 
      or (a.month = 12 and b.month = 1 and a.year + 1 = b.year) )  
where a.absolute_month + 1 <> b.absolute_month ) = 0 
then 'passed' else 'failed' end );

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

select concat( 'Sufficient and sufficiently varied month_value test data: ',
   case when 
      ( select count(distinct year, month) from month_value) > 10
  and ( select count(distinct year) from month_value) > 3
  and ... more tests 
  then 'passed' else 'failed' end );

Теперь давайте проверим его последовательность:

select concat( '(absolute_month)s are consecutive: ',
case when ( select count(*) from cm_absolute_month a join cm_absolute_month b 
on (     (a.month + 1 = b.month and a.year = b.year) 
      or (a.month = 12 and b.month = 1 and a.year + 1 = b.year) )  
where a.absolute_month + 1 <> b.absolute_month ) = 0 
then 'passed' else 'failed' end );

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

select concat( 'Sufficient and sufficiently varied month_value test data: ',
   case when 
      ( select count(distinct year, month) from month_value) > 10
  and ( select count(distinct year) from month_value) > 3
  and ... more tests 
  then 'passed' else 'failed' end );

Теперь давайте проверим его последовательность:

select concat( '(absolute_month)s are consecutive: ',
case when ( select count(*) from cm_absolute_month a join cm_absolute_month b 
on (     (a.month + 1 = b.month and a.year = b.year) 
      or (a.month = 12 and b.month = 1 and a.year + 1 = b.year) )  
where a.absolute_month + 1 <> b.absolute_month ) = 0 
then 'passed' else 'failed' end );

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

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

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