SQL存储过程测试

　　许多基于Windows的系统都使用了SQL Server作为后台组件。待测程序(AUT：Application Under Test)或者待测系统(SUT:System Under Test)经常通过存储过程来访问数据库。对于这些应用场景，可以把SQL存储过程想象成应用程序的辅助函数。有两种基本的方法可以用来编写针对SQL存储过程的轻量级的自动化测试。

　　第一种方法是在原始的SQL环境中编写自动化测试代码，也就是说测试套件代码(harness code)用于T-SQL语言来编写的，并且在类似于查询分析器(QueryAnalyzer)或者Management Studio这样的程序里被执行。

　　第二种方法是在.NET环境下编写自动测试代码，测试套件代码是用C#或者其他.NET语言来编写的，并且在诸如命令行程序等常规的Windows环境下被执行。

　　此次介绍如何使用原始的SQL环境来测试SQL存储过程。

　　问题

　　如何创建SQL数据库以及表结构以用来保存测试用例的输入数据和测试结果。

　　设计

　　编写T-SQL脚本，创建一个数据库然后创建一些表用来保存测试用例的输入数据和测试结果。如果想通过SQL认证的方式连接到上一步创建的数据库，则要创建一个专用的SQL登录账号，然后在查询分析器里或者通过oSQL.exe运行T-SQL脚本。

　　方案

　　下面的脚本创建了一个叫dbTestCasesAndResults的数据库，它包括一个用于存储测试用例数据的表和一个胜于存储测试结果的表，以及一个专用的SQL登录账号，这个账号便于程序通过Windows认证或者SQL认证的方式连接数据库。下面来看代码。

　　第一步设定当前的数据库为SQLServer的主数据库(master database)。当创建一个新的用户数据库的时候，这一步是必不可少的。如果要创建的数据库已经存在，系统就会给一个错误，为了防止这种错误，可以在试图删除新数据库的旧版本之前通过查询sysdatabases表来检查旧版的数据库是否存在。然后可以通过数据库创建用于测试用例存储的数据库。用于创建数据库的语句有许多可选的参数，但是对于轻量级的自动测试来说，接受这些参数的默认值通常就可以满足需要了。

　　--makeDbTestCasesAndResults.SQL

　　usemastergo

　　ifexists(select*fromsysdatebases wherename ='dbTestCasesAndResults')

　　dropdatabasedbTestCasesAndResultsgo

　　ifexists(select*fromsysxlogins wherename ='testLogin')

　　execsp_droplogin 'testLogin'

　　go

　　createdatabasedbTestCasesAndResultsgo

　　usedbTestCasesAndResultsgo

　　createtabletblTestCases

　　(

　　caseID char(4) primarykey,

　　input char(3) notnull,--an empID

　　expected intnotnull)go

　　--下面插入的是用于usp_StatusCode的测试用例数据

　　--也可以通过BCP，DTS，或者C#程序从文本文件读入

　　insertintotblTestCases values('0001','e11',77)insertintotblTestCases values('0002','e22',77) ——should be 66

　　insertintotblTestCases values('0003','e33',99)insertintotblTestCases values('0004','e44',88)go

　　createtabletblResults

　　(

　　caseID char(4) notnull,

　　result char(4) null,

　　whenRen datetimenotnull)go

　　execsp_addlogin 'testLogin','secret'

　　go

　　execsp_grantdbaccess 'testLogin'

　　go

　　grantselect,insert,deleteontblTestCases totestLogingo

　　grantselect,insertontblResult totestLogingo

　　注解

　　SQL数据库支持两种不现的安全模式：使用Windows认证可以通过Windows账号ID和密码连接数据库，使用混合模式认证可以通过SQL登录ID 和SQL密码来连接数据库。如果想要通过SQL认证来连接数据库，应该使用系统存储过程sp_addlogin()创建一个SQL登录账号以及相应的密码。如果要删除一个SQL登录账号，可以先查询sysxlogin()表检查相应的账号是否存在，然后调用sp_droplogin()来删除这个账号。创建好一个SQL登录账号以后，应该赋予登录账号连接数据库的许可。然后需要针对数据库里的表，赋予登录账号与SQL语句相关的权限许可，比如：SELECT,INSERT,DELETE以及UPDATE。

　　SQL登录账号很容易与SQL用户搞混。SQL登录账号是服务器范围的对象，它用来控制针对装有SQL Server的机器的连接许可。而SQL用户是数据库范围的对象，它用来控制数据库以及它所包含的表，存储过程和其他一些对象的权限许可。当为一个SQL 登录账号分配权限的时候，会自动创建一个同名的SQL用户。所以最终会有一个SQL登录账号和一个SQL用户，两个名字相同并且相互关联。尽管也可以上让不同名的账号和用户相互关联，但是这太容易让人迷惑了，所以最好还是使用名字相同的默认机制。

　　为了测试存储过程而测试用例存储结构的时候，必须决定在什么时候以及如何往表中插入胜于测试用例的那些数据。最简单的办法是在创建表的时候直接加入用于测试用例的数据。使用本解决方案所演示的INSERT语句可以很快的完成这件事情。但是，在测试的过程中几乎肯定要在很多地方碰上需要添加或者移除测试用例数据的情况，所以更灵活的方法是在后面使用BCP(Bulk Copy Program),DTS(Data Transformation Services)或者一个辅助的C#程序来插入数据。如果想要插入以及删除测试用例数据，那么就应当针对存储测试用例数据的那张表为SQL登录账号赋予 INSERT和DELETE的权限许可。

　　这个用于创建测试用例和存储测试结果的脚本可以通过几种方法来运行，一种方法是在查询分析器程序里打开并通过Excute命令。第二种方法是使用OSQL.EXE程序来执行这个SQL脚本。

　　问题

　　如何使用BCP从一个文本文件把测试用例数据导入到SQL表

　　设计

　　创建一个BCP格式的文件用于把想导入的文本文件信息映射到目标SQL表，然后把上述格式的文件作为参数传给命令行工具bcp.exe。

　　方案

　　假设SQL表定义如下：　

　　createtabletblTestCases

　　(

　　caseID char(4) primarykey,

　　input char(3) notnull,

　　expected intnotnull　　)

　　还有一个包含测试用例数据的文本文件叫作newData.dat

　　0020,e13,66

　　0021,e14,77

　　0022,e15,88

　　0023,e16,99

　　0024,e17,66

　　创建一个叫newData.fmt的BCP格式的文件，这个文件采用制表符(tab)作为分隔符。

　　8.0

　　3

　　1 SQLCHAR 0 4 "," 1 caseID SQL_Latin1_General_CP1_CI_AS

　　2 SQLCHAR 0 3 "," 2 input SQL_Latin1_General_CP1_CI_AS

　　3 SQLCHAR 0 2 "rn" 3 excepted SQL_Latin1_General_CP1_CI_AS

　　用于导入测试用例数据的命令如下：

　　C:>bcp.exe dbTestCasesAndResults..tblTestCases in newData.dat -fnewData.fmt -S. -UtestLogin -Psecret

　　此命令的意思是针对数据库dbTestCasesAndResults里的表tblTestCases运行BCP程序，把newData.dat里的数据按照newData.fmt所定义的格式映射导入到上述表中。这些命令是针对本地的SQLServer所执行的，连连数据库的时候使用叫作 testLogin的SQL登录账号，SQL密码是secret。

　　使用这种技术，关键是要理解bcp.exe程序所使用的格式文件的结构。这个文件的第1行只有一个单独的值用来表示SQL Server的版本号。SQL Server 7.0的版本号是7.0，SQL Server 2000的版本号是8.0，SQL Server2005的版本号是9.0。格式文件的第二行是一个整数值，它表示格式文件中映射实际开始的行号。第3行以后的的内容都是映射信息。每个映射行(mapping line)有8个列。前5个列代表与输入数据(本例中指文件文件)有关信息。后3个列代表要导入的目标信息(本例中指SQL表)。第一列其实就是从1开始的系列数字。这些值总是1、2、3等，依此类推。(这些数字以及其他一些BCP格式文件里的信息看上去是没有必要的，但是在其他一些情况下确实会用到它们。)映射行里的第二列是要导入的数据类型。当从文本文件导入数据的时候，不管这个值代表的是什么，它的类型总是SQLCHAR。第三例是前缀长度(prefix length)。这是当进行从SQL到SQL数据拷贝时，BCP用于优化的一个相当复杂的参数。幸运的是，当把文本数据导入SQL的时候，前缀总长度值总是0.第四列表示输入字段字符的最大长度。第五列表示字段分隔会，在此用逗号来分割所有的字段。比如说，如果输入数据文件的字段是用制表符来分割，应该在映射文件中指定为t。第六列到第八列指代的是目标SQL表，而不是输入文件。第六列和第七列分别指SQL表里相应列的顺序和名称。映射行第八列用来指定要使用的SQL排序规则。

　　注解

　　使用BCP工具可以高效地以自动化的方式把测试用例数据从文本文件引入到SQL测试用例表中。特别注意，测试用例的数据文件在数据块的最后一行之后一定不能再有换行符。如果有换行符，就会被BCP解释成一个新的空行。而用于映射的格式文件在最后一行之后一定要有一个换行符。如果没有这个换行符，BCP就不会读入映射文件的最后一行。

　　即使文本文件的数据格式与SQL表的结果不完全吻合，也可以通过BCP工具从文本文件导入数据。换句话说，即使文本文件的数据与相应的SQL列顺序不一致或者文本文件的数据有多作的字段，仍然可以使用BCP工具来导入数据，例如：

　　0020,66,useless,e13

　　0021,77,no-need,e14

　　0022,88,go-away,e15

　　0023,99,drop-it,e16

　　这个文件有些额外的信息并不想将其导入，而且各个字段(caseID,expected value,unneeded data,input value)的顺序也和SQL列(caseID,input,expected)的顺序不一致。针对这个文本文件的BCP文件的格式如下：

　　8.0

　　4

　　1 SQLCHAR 0 4 "," 1 caseID SQL_Latin1_General_CP1_CI_AS

　　2 SQLCHAR 0 2 "," 3 excepted SQL_Latin1_General_CP1_CI_AS

　　3 SQLCHAR 0 7 "," 0 junk SQL_Latin1_General_CP1_CI_AS

　　4 SQLCHAR 0 3 "rn" 2 input SQL_Latin1_General_CP1_CI_AS

　　映射文件的第6列通过设置该列的值来指定文本文件中相应数据的插入顺序，如果该列的值设为0，则在插入数据的时候忽略这个列。

　　因为bcp.exe是一个命令行程序，所以可以手动运行它也可以把想要执行的命令放到一个简单的BAT文件，然后在自己的程序里调用这个BAT文件。如果想在SQL环境下使用BCP，可以通过 BULK INSERT 命令来完成。

　　bulk insert dbTestCasesAndResults..tblTestCases

　　from 'C:somewherenewData.dat'

　　with (formatfile = 'C:somewherenewData.fmt')

　　问题

　　如何创建一个T-SQL测试套件用于测试SQL存储过程。

　　设计

　　首无，通过插入大量测试平台数据准备好一个包含待测存储过程的底层数据库。接下来，使用一个SQL游标(cursor)遍历这个测试用例数据表。针对每个测试用例，调用待测存储过程并且取得它的返回值，把实际返回值与期望值进行比较，从而判定测试结果是通过与否，然后显示或保存测试结果。

　　方案

　　——testAuto.SQL

　　——为dbEmployees填充数据

　　truncatetabledbEmployees.dbo.tblEmployees

　　insertintodbEmployees.dbo.tblEmployees values('e11','Adams','15/10/2009')

　　insertintodbEmployees.dbo.tblEmployees values('e22','Baker','15/10/2009')

　　insertintodbEmployees.dbo.tblEmployees values('e33','Young','15/10/2009')

　　insertintodbEmployees.dbo.tblEmployees values('e44','Zetta','15/10/2009')

　　——此处插入更多数据

　　declaretCursor cursorfast_forward

　　forselectcaseID,input,expected

　　fromdbTestCasesAndResults.dbo.tblTestCases

　　orderbycaseID

　　declare@caseIDchar(4),@inputchar(3),@expectedint　　declare@actualint,@whenRundatetime　　declare@resultLinevarchar(50)

　　set@whenRun=getdate()

　　opentCursor

　　fetchnext　　fromtCursor

　　into@caseID,@input,@expected　　while@@fetch_status=0　　begin　　exec@actual=dbEmployees.dbo.usp_StatusCode @input　　if(@actual=@expected)

　　begin　　set@resultLine=@caseID+': Pass'　　print@resultLine　　end　　else　　begin　　set@resultLine=@caseID+': FAIL'　　print@resultLine　　end　　fetchnext　　fromtCursor

　　into@caseID,@input,@expected　　end　　closetCursor

　　deallocatetCursor

　　——endscript

　　如果待测存储过程依赖于外部数据(大多数情况下都是这样的)，则必须把大量的测试平台数据填充到底层的数据库表。在SQL测试环境下，通常有两个数据库：开发数据库供开发者在编写代码时使用，测试数据库供测试者在测试时使用。因为对存储过程进行测试经常会改变包含存储过程的这个数据库(因为存储过程经常对数据进行插入、更新、删除)，因此不希望在开发数据襄轴上运行测试程序。因此，应该复制一份开发数据库的拷贝，并且使用这个拷贝来做测试。这样，开发数据库的表里面保存的就是供开发者使用的数据。这些数据对于在开发待测系统(SUT)的过程中进行基本的验证测试(verification testing)是非常有必要的。但是，这些数据对于进行全面而严格的测试来说，通常是不够丰富的。

　　有好几种方法可以遍历一个存储测试用例数据的数据库表，使用SQL游标是其他简单有效的一种。SQL游标被设计用来处理单个数据而不是像其他SQL操作(比如SELECT和INSERT)那样处理行集(rowset)。首先应该声明一个指向保存测试用例数据的SQL表的游标：　

　　declaretCursor cursorfast_forward

　　forselectcaseID,input,expected

　　fromdbTestCasesAndResults.dbo.tblTestCases

　　orderbycaseID

　　注意：与其他SQL变量不同，游标变量的名字前面并没有@字符。可供声明的游标有好几种。FAST_FORWARD最适合用来读取测试用例数据。其他游标类型包括FORWARD_ONLY,READ_ONLY,以及OPTIMISTIC。FAST_FORWARD实际上就是FORWARD_ONLY加上 READ_ONLY的别名。

　　在使用游标之前，必须先打开游标。然后，如果想要遍历整个数据库表，则必须通过fetch next语句预读取数据库表的第一行：　

　　opentCursor

　　fetchnext　　fromtCursor

　　into@caseID,@input,@expected

　　对第一行进行预读取是为了对下面的循环进行控制，使用变量@@fetch_status来控制用于读取的这个循环，这个变量表示最近一次fetch操作的状态。如果fetch操作成功，则@@fetch_status值为0 。如果值为-1或-2则意味fetch失败。因此可以像下面这样每次一行地遍历整个数据库表：

　　while@@fetch_status=0　　begin　　——运行测试用例

　　fetchnext　　fromtCursor

　　into@caseID,@input,@expected　　end

　　在主循环内部，需要调用待测存储过程，并且把测试用例输入数据传给它。接下来取回返回值并打印pass或fail消息：　

　　exec@actual=dbEmployees.dbo.usp_StatusCode @input　　if(@actual=@expected)

　　begin　　set@resultLine=@caseID+': Pass'　　print@resultLine　　end　　else　　begin　　set@resultLine=@caseID+': FAIL'　　print@resultLine　　end

　　使用完一个SQL游标之后，必须要关闭这个游标并且调用deallocate命令把它作为一个资源进行释放：

　　closetCursor

　　deallocatetCursor

　　如果忘了对游标进行deallocate操作，下一次执行测试套件脚本需要声明游标的时候，脚本就会报错。

　　注解

　　除了打印pass/fail信息之外，还可以把测试结果插入到SQL数据表里：

　　——declare@actualint,@whenRundatetime　　——set@whenRun=getdate()

　　while@@fetch_status=0　　begin　　exec@actual=dbEmployees.dbo.usp_StatusCode @input　　if(@actual=@expected)

　　insertintodbTestCasesAndResults.dbo.tblResults values(@caseID,'Pass',@whenRun)

　　else　　insertintodbTestCasesAndResults.dbo.tblResults values(@caseID,'FAIL',@whenRun)

　　fetchnext　　fromtCursor

　　into@caseID,@input,@expected　　end

　　如果不采用通过待测存储过程以及硬编码的语句来生成底层数据库，也可以使用BULK INSERT语句：

　　——为dbEmployees填充数据

　　truncatetabledbEmployees.dbo.tblEmployees

　　bulkinsertdbEmployees.dbo.tblEmployees

　　from'C:somewhererichTestbedData.dat'　　with(formatfile ='C:somewhererichTestbedDate.fmt')

　　使用这种方法的好处是，可以让测试套件模块化程度更高并且更为灵活，但是它也有不好的一面：它在这个原本已经有很多对象的测试套件系统里又引入了一个文件，从而增加了复杂性。