SQL Server单元测试方法的尝试

       我自己写过一些脚本，当时也写过测试脚本去验证，但是到了后面花在找这些脚本或者修改它们的时间也越来越多，感觉不够方便。
       后来接触了测试驱动开发（TDD），觉得这样似乎能够解决我的问题：

1.验证是自动进行的（不用人工判断数据的正确，因为代码里已经“记住了”怎么判断）；

2.Setup保证了不用每次运行前都要修改脚本来适应当前环境；

3.Teardown保证了测试之间是独立的，没有运行顺序的要求。

主要思路：

T-SQL不是面向对象的语言, 但我们可以利用数据库的事务机制来模拟Setup和Teardown功能.

数据库的单元测试较麻烦的一点是结果集的比较, 这里给出一个存储过程(up_TableIsEqual)来处理.

 

/**/ /* 
Test case :模板

前置条件: 
*/
SET  NOCOUNT  ON
BEGIN   TRAN
GO
-- Setup Begin
     -- 相当于一个测试类的Setup函数, 可以把各个测试用例都会用到的初始化脚本放到这里
-- Setup End

GO
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    --  回滚, 把整个测试的影响都取消

--里面用到的过程

 

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
GO

CREATE   PROC  up_TableIsEqual
/**/ /*结果集比较 liye
*/
@SourceTable   varCHAR ( 100 ), 
@TargetTable   varCHAR ( 100 ),
@IsDebug   BIT   =   0
-- 1 Equal
AS
DECLARE   @Result   INT

CREATE   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
END

IF   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
END

IF  ( 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
END

DECLARE   @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 )
END

BEGIN    --  生成新的临时表, 合并重复行, 和记录重复行的个数
  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
END

EXEC ( @Prepare   +   @s )

SELECT   @Result   =  Result  FROM  #ResultInTableisEqualProc

DROP   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 ]
GO



CREATE   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
END




GO

 

问题:  

多连接时,锁的测试 (这个不太好办, 可能还得用程序来实现)

 结果集数据量很大时, (可以用基本表代替临时表保存预期的结果, 并建立索引以优化表连接的效率, 或者考虑用CHECKSUM_AGG来比较)

 限制:

 不能把事务回滚当作正常的处理

 

 这么写数据库脚本也挺有意思的，请大家多提意见.