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

Ответ 1

Вы бы не написали приложение с функциями длиной 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 - это просто case when тестовый оператор then 'passed' else 'failed' end.

Тестовые операторы будут просто 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.

Выполнение этого запроса дает такой результат: For every (year, month) there is one and only one (absolute_month): passed

Пока в 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 );

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

Ответ 2

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

Ответ 3

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

Другая вещь, которую вы, возможно, захотите сделать, - это профилировать стек выполнения SQL Server и выяснить, действительно ли все запросы правильные, например, если вы используете только один запрос, который возвращает как правильные, так и неправильные результаты, а затем ясно используемый запрос является вопросом, но как насчет того, отправляет ли ваше приложение разные запросы в разных точках кода?

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

Ответ 4

Re: tpdi

case when ( select count(*) from cm_abs_month a join cm_abs_month b  
on (( a.m + 1 = b.m and a.y = b.y) or (a.m = 12 and b.m = 1 and a.y + 1 = b.y) )   
where a.am + 1 <> b.am ) = 0  

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

Также я что-то пропущу, или вторая половина этого предложения ВКЛЮЧАЕТ неправильное значение месяца? (т.е. проверяет, что 12/2011 приходит после 1/2010)

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

Не забудьте протестировать тест из ваших тестовых случаев!

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