SQL Server单元测试方法的尝试

Question

我自己写过一些脚本，当时也写过测试脚本去验证，但是到了后面花在找这些脚本或者修改它们的时间也越来越多，感觉不够方便。
       后来接触了测试驱动开发（TDD），觉得这样似乎能够解决我的问题：1.验证是自动进行的（不用人工判断数据的正确，因为代码里已经“记住了”怎么判断）；2.Setup保证了不用每次运行前都要修改脚本来适应当前环境；3.Teardown保证了测试之间是独立的，没有运行顺序的要求。主要思路：T-SQL不是面向对象的语言, 但我们可以利用数据库的事务机制来模拟Setup和Teardown功能.数据库的单元测试较麻烦的一点是结果集的比较, 这里给出一个存储过程(up_TableIsEqual)来处理.

Answer 1

/* 
Test case :模板前置条件: 
*/
SET NOCOUNT ON
BEGIN TRAN
GO
--Setup Begin
    --相当于一个测试类的Setup函数, 可以把各个测试用例都会用到的初始化脚本放到这里
--Setup EndGO
SAVE TRAN aa  --相当于测试类中的一个测试函数
GO    
    --这里写每个函数不同的初始化脚本    --被测试模块 Begin
    SELECT A = 1 INTO TableA
    --被测试模块 End
    
    --验证    
    SELECT A = 1 INTO #Target-- 把预期的结果放到固定名称的临时表#Target中
    SELECT * INTO #Result FROM TableA -- 把处理的结果放到固定名称的临时表#Result中
    
    EXEC up_ASSERT '测试函数1'  -- up_ASSERT过程里会比较#Result和#Target
GO
ROLLBACK TRAN aa  -- 回滚, 把这个"测试函数"的影响取消
GO
SAVE TRAN aa  --相当于测试类中的另一个测试函数
GO    
    --这里写每个函数不同的初始化脚本    --被测试模块 Begin
    SELECT A = 2 INTO TableA
    --被测试模块 End
    
    --验证    
    SELECT A = 2 INTO #Target-- 把预期的结果放到固定名称的临时表#Target中
    SELECT * INTO #Result FROM TableA -- 把处理的结果放到固定名称的临时表#Result中
    
    EXEC up_ASSERT '测试函数2'  -- up_ASSERT过程里会比较#Result和#Target
GO
ROLLBACK TRAN aa  -- 回滚, 把这个"测试函数"的影响取消GO
--Teardown
ROLLBACK  -- 回滚, 把整个测试的影响都取消

Answer 2

--里面用到的过程
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[up_TableIsEqual]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[up_TableIsEqual]
GO
SET NOCOUNT ON
GOCREATE PROC up_TableIsEqual
/**//*结果集比较 liye
*/
@SourceTable varCHAR(100), 
@TargetTable varCHAR(100),
@IsDebug BIT = 0
--1 Equal
AS
DECLARE @Result INTCREATE TABLE #SrcTbCol(
[name] sysname,
xtype tinyint
)
CREATE TABLE #TagTbCol(
[name] sysname,
xtype tinyint
)
SELECT Result = 0
INTO #ResultInTableisEqualProc
-- 比较表结构
IF SUBSTRING(@SourceTable, 1, 1) = '#'
BEGIN
 INSERT INTO #SrcTbCol
 SELECT [name], xtype 
 FROM tempdb..syscolumns
 WHERE [ID] = OBJECT_ID('tempdb..' + @SourceTable)
 ORDER BY colid
END
ELSE BEGIN
 INSERT INTO #SrcTbCol
 SELECT [name], xtype 
 FROM syscolumns
 WHERE [ID] = OBJECT_ID(@SourceTable)
 ORDER BY colid
ENDIF SUBSTRING(@TargetTable, 1, 1) = '#'
BEGIN
 INSERT INTO #TagTbCol
 SELECT [name], xtype 
 FROM tempdb..syscolumns
 WHERE [ID] = OBJECT_ID('tempdb..' + @TargetTable)
 ORDER BY colid
END
ELSE BEGIN
 INSERT INTO #TagTbCol
 SELECT [name], xtype 
 FROM syscolumns
 WHERE [ID] = OBJECT_ID(@TargetTable)
 ORDER BY colid
ENDIF (SELECT COUNT(*) FROM #SrcTbCol) <> (SELECT COUNT(*) FROM #SrcTbCol)
OR (SELECT COUNT(*) FROM #SrcTbCol) 
<> (SELECT COUNT(*) FROM #SrcTbCol a INNER JOIN #TagTbCol b ON a.[name] = b.[name])
BEGIN
  RETURN 0
ENDDECLARE @Prepare varCHAR(4000), @ColList varCHAR(1000)
--获取列的名称列表: A,B,C
BEGIN
 SET @ColList = ''
 
 SELECT @ColList = @ColList + [name] + ','
 FROM #SrcTbCol
 WHERE xtype NOT IN (34, 35, 99)
 
 SET @ColList = SUBSTRING(@ColList, 1, LEN(@ColList) - 1)
ENDBEGIN  -- 生成新的临时表, 合并重复行, 和记录重复行的个数
 SET @Prepare = '
 SELECT _ColList_, CountForTableCompare = COUNT(*)
 INTO _SourceTb_2
 FROM _SourceTb_
 GROUP BY _ColList_
 
 SELECT _ColList_, CountForTableCompare = COUNT(*)
 INTO _TargetTb_2
 FROM _TargetTb_
 GROUP BY _ColList_
 
 '
 SET @Prepare = REPLACE(@Prepare, '_ColList_', @ColList)
 SET @Prepare = REPLACE(@Prepare, '_SourceTb_', @SourceTable)
 SET @Prepare = REPLACE(@Prepare, '_TargetTb_', @TargetTable)
END--加入重复行数列
INSERT INTO #SrcTbCol SELECT [name] = 'CountForTableCompare', 56
INSERT INTO #TagTbCol SELECT [name] = 'CountForTableCompare', 56-- 比较结果数
DECLARE @s varCHAR(8000)
SET @s = 'UPDATE #ResultInTableisEqualProc
SET Result = 1 
WHERE (SELECT COUNT(*) FROM _SourceTb_2) = (SELECT COUNT(*) FROM _TargetTb_2)
AND (SELECT COUNT(*) FROM _SourceTb_2) = (SELECT COUNT(*) FROM _SourceTb_2 a INNER JOIN _TargetTb_2 b ON 'SELECT @s = @s + ' a.' + [name] + ' = b.' + [name] + ' AND'
FROM #SrcTbCol
WHERE xtype NOT IN (34, 35, 99)SET @s = REPLACE(@s, '_SourceTb_', @SourceTable)
SET @s = REPLACE(@s, '_TargetTb_', @TargetTable)
SET @s = SUBSTRING(@s, 1, LEN(@s) - 3)
SET @s = @s + ')'IF @IsDebug = 1
BEGIN
 PRINT @Prepare + @s
ENDEXEC(@Prepare + @s)SELECT @Result = Result FROM #ResultInTableisEqualProcDROP TABLE #SrcTbCol
DROP TABLE #TagTbCol
RETURN @Result
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[up_ASSERT]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[up_ASSERT]
GOCREATE PROC up_ASSERT
@ErrorMessage varCHAR(100) = ''
AS
DECLARE @Res INT
EXEC @Res = up_TableIsEqual '#Target', '#Result'  -- 比较结果和期望值IF @Res <> 1
BEGIN
  IF @ErrorMessage IS NULL
  BEGIN
    RAISERROR('Error', 16, 1)
  END
  ELSE BEGIN
    RAISERROR(@ErrorMessage, 16, 1)
  END  
  SELECT * FROM #Target
  SELECT * FROM #Result
ENDGO

Answer 3

问题:  多连接时,锁的测试 (这个不太好办, 可能还得用程序来实现) 结果集数据量很大时, (可以用基本表代替临时表保存预期的结果, 并建立索引以优化表连接的效率, 或者考虑用CHECKSUM_AGG来比较) 限制:  不能把事务回滚当作正常的处理  这么写数据库脚本也挺有意思的，请大家多提意见.http://blog.csdn.net/lydmc/archive/2006/10/26/1352387.aspx

Answer 4

在T－SQL中单元测试的一种实现方法，
如果想自动地验证自己写的SQL脚本，可以试试这种方法。请大家多提意见，使它更完善。

Answer 5

操作界面其实是现成的：查询分析器就行了，执行脚本后显示“命令已成功完成。”就OK了。
不过“Keep it green”就要改为“Keep it black”了。不过锁的测试恐怕最后还得用程序来完成，T－SQL不能自己打开一个连接啊。