我自己写过一些脚本,当时也写过测试脚本去验证,但是到了后面花在找这些脚本或者修改它们的时间也越来越多,感觉不够方便。
后来接触了测试驱动开发(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 -- 回滚, 把整个测试的影响都取消
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
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
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来比较)
限制:
不能把事务回滚当作正常的处理
这么写数据库脚本也挺有意思的,请大家多提意见.