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来比较)

 限制:

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

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