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

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

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

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

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

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

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

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

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

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 'false' end.

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

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

--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 );

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