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

Question

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

по сути, это приводит к отправке почты неправильным клиентам и другим подобным "проблемам".

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

так вот некоторые мои мысли и ограничения к ним.

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

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

Спасибо за любой вклад, который вы можете дать моей проблеме.

Answer 1

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

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

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

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

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

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

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

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

create table month_value( 
    eid int not null, m int, y int,  v int );

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

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

create view cm_abs_month as 
select *, y * 12 + m as am from month_value;

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

Наш тест будет SQL select запрос со следующей структурой: имя теста и оператор 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 (y, m) there is one and only one (am): ', 
-- 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 y, m) from month_value) 
  --expected value,
  = ( select count(distinct am) from cm_abs_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 (y,m) there is one and only one (am): passed

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

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

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

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

select concat( '(am)s are consecutive: ',
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 
then 'passed' else 'failed' end );

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

Answer 2

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

Answer 3

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

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

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

Answer 4

Re: ТПДИ

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 полностью отключен.

Также я что-то упускаю, или вторая половина этого предложения ON увеличивает неправильное значение месяца? (т.е. проверяет, что 12/2011 наступает после 1/2010)

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

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

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