《软件测试自动化之道》读书笔记 之 SQL 存储过程测试
2014-09-28
待测程序
测试程序
创建测试用例以及测试结果存储
执行T-SQL脚本
使用BCP工具导入测试用例数据
创建T-SQL 测试套件
当待测存储过程返回行集的时候，如何判断测试结果是否通过
当待测存储过程返回out参数时，如何判断测试结果是否通过
当待测存储过程没有返回值时，如何判断测试结果是否通过
许多基于Windows的系统都使用了SQL Server作为后台组件。待测程序经常通过存储过程来访问数据库。对于这些应用场景，可以把SQL存储过程当成应用程序的辅助函数。有两种方法可以用来编写对SQL存储过程的自动化测试：
- 测试套件代码用T-SQL语言来编写，在类似于查询分析器（Query Analyzer）或者Management Studio这样的程序里执行
- 测试套件代码用.NET语言连编写
待测程序
下面代码创建数据库‘dbEmployees’；创建表‘talEmployee’，插入相应数据；创建存储过程‘usp_HireAfter’，创建登陆用户‘employeesLogin’并赋予访问数据库和存储过程的权限：
makeDbEmployees.sql：
-- Database setup: makeDbEmployees.sql use master
go if exists (select * from sysdatabases where name='dbEmployees')
drop database dbEmployees
go if exists (select * from syslogins where name = 'employeesLogin')
exec sp_droplogin 'employeesLogin'
go create database dbEmployees
go use dbEmployees
go create table tblEmployees
(
empID char(3) primary key,
empLast varchar(35) not null,
empDOH datetime not null,
)
go -- this is dev data, not test case data
insert into tblEmployees values('e11','Adams', '06/15/1998')
insert into tblEmployees values('e22','Baker', '06/15/2001')
go exec sp_addlogin 'employeesLogin', 'September,2014'
go
exec sp_grantdbaccess 'employeesLogin'
go create procedure usp_HiredAfter
@dt datetime
as
select * from tblEmployees where empDOH > @dt
go grant execute on usp_HiredAfter to employeesLogin
go -- end script
注意：当测试SQL存储过程时，有两个理由使你最好不要使用用于开发的数据库进行测试：
- 测试存储过程有时会修改包含这个存储过程的数据库
- 开发的那个数据库通常没有足够多的数据或者这些数据不是为了测试的目的而设计的
SQL数据库支持两种不同的安全模式：使用Windows认证可通过Windows账号ID和密码连接数据库，使用混合模式认证可以通过SQL登陆ID和SQL密码来连接数据库。若想通过SQL认证来连接测数据库，应该使用系统存储过程sp_addlogin()创建SQL登陆账号和密码。
SQL登陆账号和SQL用户区别：
- SQL登陆账号是服务器范围的对象，它用来控制针对装有SQL Server的机器的连接许可；
- SQL用户是数据库方位的对象，他用来控制数据库以及他所包含的表、存储过程和其他一些对象的权限许可。
当为一个SQL登陆账号分配权限的时候，会自动创建一个名为SQL用户。所以最终会有一个SQL登陆账号和一个SQL用户，两个名字相同且相互关联。当然也可以让不同名字的登陆账号和用户相互关联。
测试程序
创建测试用例以及测试结果存储
以下T-SQL脚本，创建一个数据库然后创建一些表用来保存测试用例的输入数据和测试结果；创建一个专用SQL登陆账号，赋予一定权限：
makeDbTestCasesAndResults.sql：
-- Test case data and results setup: makeDbTestCasesAndResults.sql use master
go if exists (select * from sysdatabases where name='dbTestCasesAndResults')
drop database dbTestCasesAndResults
go if exists (select * from syslogins where name = 'testLogin')
exec sp_droplogin 'testLogin'
go create database dbTestCasesAndResults
go use dbTestCasesAndResults
go create table tblTestCases
(
caseID char(4) primary key,
input datetime not null,
expectedChecksum int not null
)
go -- this is the test case data for usp_HiredAfter using a checksum expected
-- value approach
-- can also read from a text file using BCP, DTS, or a C# program
insert into tblTestCases values('','01/01/1998', 1042032)
insert into tblTestCases values('','01/01/1998', 9999999) -- deliberate error
insert into tblTestCases values('','01/01/2000', 25527856)
insert into tblTestCases values('','01/01/2006', 0)
go create table tblResults
(
caseID char(4) not null,
result char(4) null,
whenRun datetime not null
)
go exec sp_addlogin 'testLogin', 'September,2014'
go
exec sp_grantdbaccess 'testLogin'
go grant select, insert, delete, update on tblTestCases to testLogin
go grant select, insert, delete, update on tblResults to testLogin
go -- end script
执行T-SQL脚本
运行T-SQL脚本，有好几种方法：
- 使用查询分析器；
- 使用osql.exe;
- 使用批处理（BAT）
下面使用osql.exe程序使用以下命令执行这个脚本：
osql.exe -S(local) -U loginID -P loginPassword -i makeDbTestCasesAndResults.sql -n > RESULT.txt
或
osql.exe -S(local) -E -i makeDbTestCasesAndResults.sql -n > RESULT.txt
-E表示使用Windows认证模式。
注意：osql.exe的参数是大小写敏感的。
使用BCP工具导入测试用例数据
创建一个BCP格式的文件，用于把你想要导入的文本文件信息映射到目标SQL表中。然后把上述格式的文件作为参数传给命令行工具bcp.exe
Step 1: 先看一下如何从表‘tblTestCases’中导出数据
create table 'tblTestCases'的脚本：
create table tblTestCases
(
caseID char(4) primary key,
input datetime not null,
expectedChecksum int not null
)
用BCP导出表‘tblTestCases’到dat文件中：
bcp dbTestCasesAndResults.dbo.tblTestCases out C:\Code\AutomationTest\newData.dat -c -T
图１导出的数据
Step 2: 用BCP导出格式文件
EXEC master..xp_cmdshell 'bcp dbTestCasesAndResults.dbo.tblTestCases format nul -f C:\Code\AutomationTest\newData.fmt -c -T'
newData.fmt内容：
11.0
3
1 SQLCHAR 0 4 "\t" 1 caseID SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 24 "\t" 2 input ""
3 SQLCHAR 0 12 "\r\n" 3 expectedChecksum ""
上述内容中，
- 11.0是SQL Server2012的版本号；
- 3表示从第３行以后的内容是映射信息。
- 每个映射行有８列，前５列代表于输入数据有关的信息（本例中，指newData.dat），后３个列代表要导入的目标信息（本例中，指SQL表）：
- 第一列其实是从第一列开始的一系列数字
- 第二列是要导入的数据的类型（当从文本文件导入时，不过什么值，都是SQLCHAR）
- 第三列是前缀长度（prefix length）
- 第四列是输入字段字符的最大长度
- 第五列是分隔符
- 第六列第七列分别指SQL表里相应列的顺序和名称
- 第八列指SQL排序规则（SQL_Latin1_General_CP1_CI_ASSQL默认排序规则）
Step3: 修改newData.dat，导入
修改后newData.dat内容：
000 2007-01-01 00:00:00.000 7
用BCP命令导入：
bcp.exe dbTestCasesAndResults.dbo.tblTestCases in newData.dat -f newData.fmt -S. -T
创建T-SQL 测试套件
使用SQL游标（cursor）遍历这个测试用例数据表。针对每个测试用例，调用待测存储过程并且取得它的返回值。把它的实际值于期望值进行比较，判定结果，保存测试结果。
SQL游标设计用来处理单个的数据行并不向其他SQL操作那个处理行集（rowset）。
首先声明一个指向保存测试数据的SQL表的游标
declare tCursor cursor fast_forward
for select caseID, input, expectedChecksum
from dbTestCasesAndResults.dbo.tblTestCases
order by caseID
注意：游标于其它SQL变量不同，游标变量的名字前面并没有@字符。可供声明游标的有好几种。FAST_FORWARD最适合用来读取测试用例数据，它实际上就是FORWAR_ONLY和READ_ONLY的别名。
在使用游标前，必须先打开游标。然后，如果想要遍历整个数据库表，则必须通过fetch next语句于都数据库表的第一行：
open tCursor
fetch next
from tCursor
into @caseID, @input, @expectedChecksum
对第一行进行预读是为了对下面的循环进行控制，我们使用变量@@fetch_status来控制用于读取的这个循环，这个变量表示最近一次fetch操作的状态。如果操作成功，@@fetch_status值为０；若失败，@@fetch_status值为－１，－２。因此，可向下面这样遍历整个数据库表：
while @@fetch_status = 0
begin --运行测试用例 fetch next
from tCursor
into @caseID, @input, @expectedChecksum
end
在主循环内部，我们需要调用待测存储过程，并且把测试用力输入数据传给它。去会的值打印出来：
（注意：下面脚本调用存储过程‘dbEmployees.dbo.usp_HiredAfter时，这里假设它只返回单个值）
exec @actual = dbEmployees.dbo.usp_HiredAfter @input if(@actual=@expected)
begin
set @resultLine=@caseID + ': Pass'
print @resultLine
end
else
begin
set @resultLine=@caseID + ': Fail'
print @resultLine
end
使用完一个SQL游标之后，必须关闭这个游标并且调用deallocate命令把它作为一个资源释放：
close tCursor
deallocate tCursor
SQLspTest.sql测试脚本：
-- ===========================================================
-- TestAuto.sql truncate table dbEmployees.dbo.tblEmployees insert into dbEmployees.dbo.tblEmployees
values('e11','Adams', '06/15/1998')
insert into dbEmployees.dbo.tblEmployees
values('e22','Baker', '06/15/2001')
insert into dbEmployees.dbo.tblEmployees
values('e33','Young', '06/15/1998')
insert into dbEmployees.dbo.tblEmployees
values('e44','Zetta', '06/15/2001')
-- other data would be inserted too declare tCursor cursor fast_forward
for select caseID, input, expectedChecksum
from dbTestCasesAndResults.dbo.tblTestCases
order by caseID declare @caseID char(4), @input datetime, @expectedChecksum int
declare @whenRun datetime
declare @actualChecksum int
declare @resultLine varchar(50) set @whenRun = getdate() open tCursor
fetch next
from tCursor
into @caseID, @input, @expectedChecksum while @@fetch_status = 0
begin exec @actualChecksum=dbEmployees.dbo.usp_HiredAfter @input if (@actualChecksum = @expectedChecksum)
begin
set @resultLine = @caseID + ' Pass'
print @resultLine
insert into dbTestCasesAndResults.dbo.tblResults values(@caseID, 'Pass', @whenRun)
end
else
begin
set @resultLine = @caseID + ' Fail'
print @resultLine
insert into dbTestCasesAndResults.dbo.tblResults values(@caseID, 'Fail', @whenRun)
end fetch next
from tCursor
into @caseID, @input, @expectedChecksum end close tCursor
deallocate tCursor
-- end script
当待测存储过程返回行集的时候，如何判断测试结果是否通过
待测存储过程脚本如下：
create procedure usp_HiredAfter
@dt datetime
as
select * from tblEmployees where empDOH > @dt
首先，应该创建一个临时表，用于保存存储过程返回的QL行集：
create table #resultRowset
(
empID char(3) primary key,
empLast varchar(35) not null,
empDOH datetime not null,
)
然后，我们可以调用待测存储过程并把返回的行集存入临时表：
insert #resultRowset (empID, empLast, empDOH) -- call sp under test
exec dbEmployees.dbo.usp_HiredAfter @input
接下来，我们计算临时表的聚合校验，并把实际值和期望值进行比较：
if (@@rowcount = 0)
set @actualChecksum =0
else
select @actualChecksum = checksum_agg(binary_checksum(*)) from #resultRowset
if (@actualChecksum = @expectedChecksum)
print 'Pass'
else
print 'Fail'
上面脚本中，内建的binary_checksum()函数返回表里的一行的校验和。checksum_agg()函数返回一组值的聚合校验和。这是待测存储过程返回行集的时候，判断测试是否通过的一种方法。
示例‘SQLspTest.sql’脚本：
-- ===========================================================
-- Test automation harness: SQLspTest.sql
-- test dbEmployees..usp_HiredAfter
-- reads test case data and writes results
-- to dbTestCasesAndResults set nocount on if not exists
(select * from master.dbo.sysdatabases where name='dbTestCasesAndResults')
raiserror('Fatal error: dbTestCasesAndResults not found', 16, 1)
go if exists (select * from sysobjects where name='tap_Reset')
drop procedure tap_Reset
go create procedure tap_Reset
as
truncate table dbEmployees.dbo.tblEmployees insert into dbEmployees.dbo.tblEmployees
values('e11','Adams', '06/15/1998')
insert into dbEmployees.dbo.tblEmployees
values('e22','Baker', '06/15/2001')
insert into dbEmployees.dbo.tblEmployees
values('e33','Young', '06/15/1998')
insert into dbEmployees.dbo.tblEmployees
values('e44','Zetta', '06/15/2001')
-- other data would be inserted too
go -- prepare dbEmployees with rich data
exec tap_Reset
go declare tCursor cursor fast_forward
for select caseID, input, expectedChecksum
from dbTestCasesAndResults.dbo.tblTestCases
order by caseID declare @caseID char(4), @input datetime, @expectedChecksum int
declare @whenRun datetime
declare @resultMsg varchar(80)
declare @actualChecksum int create table #resultRowset -- for checksum technique
(
empID char(3) primary key,
empLast varchar(35) not null,
empDOH datetime not null,
) set @whenRun = getdate() print 'Stored procedure under test = usp_HiredAfter'
print ' '
print 'CaseID Input Expected Actual Result'
print '===============================================' open tCursor
fetch next
from tCursor
into @caseID, @input, @expectedChecksum while @@fetch_status = 0
begin exec tap_Reset -- reset test bed data truncate table #resultRowset -- empty out the result rowset insert #resultRowset (empID, empLast, empDOH) -- call sp under test
exec dbEmployees.dbo.usp_HiredAfter @input if (@@rowcount = 0)
set @actualChecksum = 0
else
select @actualChecksum = checksum_agg(binary_checksum(*)) from #resultRowset if (@actualChecksum = @expectedChecksum)
begin
set @resultMsg = @caseID + ' ' + cast(@input as varchar(11)) +
' ' + cast(@expectedChecksum as varchar(20)) + ' ' +
cast(@actualChecksum as varchar(20)) + ' Pass'
print @resultMsg
insert into dbTestCasesAndResults.dbo.tblResults values(@caseID, 'Pass',
@whenRun)
end
else
begin
set @resultMsg = @caseID + ' ' + cast(@input as varchar(11)) +
' ' + cast(@expectedChecksum as varchar(20)) + ' ' +
cast(@actualChecksum as varchar(20)) + ' FAIL'
print @resultMsg
insert into dbTestCasesAndResults.dbo.tblResults values(@caseID, 'FAIL',
@whenRun)
end fetch next
from tCursor
into @caseID, @input, @expectedChecksum end close tCursor
deallocate tCursor drop table #resultRowset -- end script
当待测存储过程返回out参数时，如何判断测试结果是否通过
待测存储过程脚本如下：
create procedure usp_GetLast
@empID char(3)
@empLast varchar(35) out
as
select @empLast =empLast from tblEmployees where empID = @empID
return @@rowcount
测试这个存储过程的脚本如下：
declare @input char(3)
declare @empLat varchar(35)
declare @retval int declare @expectedLast varchar(35)
declare @expectedRet int set @input = 'e22'
set @expectedLast = 'Baker'
set @expectedRet=1 exec @retval =dbEmployees.dbo.usp_GetLast @input, @empLat out
if(@retval=@expectedRet and @empLat = @expectedLast)
print 'Pass'
else
print 'Fail'
注解：
SQL存储过程有一个常用的设计模式，即存储过程可以通过参数返回一个或多个值。当存储过程返回的值不是int类型时，这个模式是必须的，因为return关键字只接受int类型的变量。
当待测存储过程没有返回值时，如何判断测试结果是否通过
待测存储过程脚本如下：
create procedure usp_DeleteEmployee
@empID char(3)
as
delete from dbEmployees.dbo.tblEmployees where empID=@empID
测试这个存储过程的脚本如下：
declare @input char(3) declare @expected int
declare @actual int set @input = 'e22'
set @expected = 150847775 exec dbEmployees.dbo.usp_DeleteEmployee @input
select @actual=checksum_agg(checksum(*)) from dbEmployees.dbo.tblEmployees
if(@actual=@expected)
print 'Pass'
else
print 'Fail'
参考
[1] bcp命令详解