测试 SQL 查询的最佳方法

In the example, because each view adds only one transformation, each can be independently tested to find errors, and the tests are simple.

Here's the base table in the example:

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

This table is flawed, because it uses two columns, month and year, to represent one datum, an absolute month. Here's our specification for the new, calculated column:

我们将这样做作为一个线性转换，这样它的排序与(年、月)相同，并且对于任何(年、月)元组，只有一个值，并且所有值都是连续的:

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

现在我们要测试的是我们的规范中固有的东西，即对于任何元组(年、月) ，只有一个(绝对 _ 个月) ，而且(绝对 _ 个月)是连续的。让我们写一些测试。

我们的测试将是一个 SQLselect查询，具有以下结构: 一个测试名称和一个连接在一起的 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 );

现在，让我们将我们的测试(仅仅是查询)放到一个文件中，并在数据库中运行该脚本。实际上，如果我们将视图定义存储在一个脚本(或者脚本，我建议每个相关视图对应一个文件)中，以便针对数据库运行，我们可以将每个视图的测试添加到 一样脚本中，这样(重新)创建视图的操作也会运行视图的测试。这样，当我们重新创建视图时，我们都会得到回归测试，并且，当视图创建与生产运行时，视图也将在生产中进行测试。