SQL Server 单元测试框架
4.38/5 (9投票s)
一个用于 T-SQL 的简单单元测试框架。
背景
数据库模式会发生变化。字段会被添加、删除或重命名，并且会引入新的约束。然而，由于 SQL Server 本身没有“编译器”，因此这些更改可能对依赖于这些数据库的代码造成破坏性更改，因此一组“单元测试”是一个有价值的安全网。
此单元测试框架除了 SQL Server 本身之外没有任何依赖项，也不依赖于通用语言运行时。下载的源文件 (*.sql) 中的表和函数最好在服务器上一个独立的数据库中创建，该服务器托管正在测试的数据库。
这个过程对于使用过 NUnit、MSTest 等单元测试框架的人来说会很熟悉。首先，您设置测试的先决条件（测试前设置），然后执行断言（单元测试），最后清理单元测试创建的任何内容（拆卸）。
需求
需求是让一个夜间或计划作业运行并对开发服务器上的所有数据库执行一组标准的单元测试，以提供“冒烟测试”和单元测试功能，从而在任何开发更改破坏现有数据库功能时提醒我们。单元测试框架必须完全托管在 SQL Server 内部，并且必须利用标准的 T-SQL。测试框架必须允许同一服务器上存储不同类型的数据库（又名数据模型）的可能性，并且测试只会在相关的模型上运行。
所有测试验证都会被记录下来，以便可以快速发现任何破坏性更改，并且可以轻松识别。
1. 单元测试
运行单个单元测试需要以下过程：运行先决条件以获取测试所需的数据库项，运行测试步骤，然后运行与每个测试步骤相关的验证，最后运行拆卸代码以将数据库恢复到测试运行前的状态。最后一部分对于确保任何单元测试都可重运行非常重要。
每个单元测试都有唯一的名称，并且测试的意图描述也记录在表 [Unit test definition] 中。
CREATE TABLE [Unit Test Definition] (
[Test Name] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Data Model] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS _
NOT NULL CONSTRAINT [DF_Unit Test Definition_Data Model] _
DEFAULT ([dbo].[DefaultReferenceDataItem]('Data Model')),
[Description] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Date Updated] [datetime] NOT NULL CONSTRAINT [DF_Unit Test Definition_Date Updated] _
DEFAULT (getdate()),
[Username] [varchar] (220) COLLATE SQL_Latin1_General_CP1_CI_AS _
NOT NULL CONSTRAINT [DF_Unit Test Definition_Username] DEFAULT (suser_sname()),
CONSTRAINT [PK_Unit Test Definition] PRIMARY KEY CLUSTERED
(
[Test Name]
) ON [PRIMARY]
) ON [PRIMARY]
GO
流程
1.1 测试先决条件
测试先决条件是在运行测试之前在目标数据库上执行的一组 SQL 命令。它们按照 [Unit Test Initialise] 表中读取的步骤顺序执行。
CREATE TABLE [Unit Test Initialise] (
[Test Name] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Initialise Command Step Number] [int] NOT NULL ,
[Command] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [PK_Unit Test Initialise] PRIMARY KEY CLUSTERED
(
[Test Name],
[Initialise Command Step Number]
) ON [PRIMARY]
) ON [PRIMARY]
GO
这些是通过存储过程 BeforeRunUnittest 执行的。
CREATE PROCEDURE dbo.BeforeRunUnitTest (@TestName char(20), @TargetDatabase varchar(220)) AS
BEGIN
declare @Step_Number [int] ,
@Command varchar(2000),
@tmpErr int, @tmpRows int,
@test_master_db varchar(240) -- The name of the database that
-- the test framework resides on
select @test_master_db = DB_NAME()
DECLARE BeforeUnitTestCursor CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR
SELECT [Initialise Command Step Number] ,
[Command]
FROM [Unit test Initialise]
WHERE [Test name] = @TestName
ORDER BY [Initialise Command Step Number]
OPEN BeforeUnitTestCursor
FETCH NEXT FROM BeforeUnitTestCursor INTO @Step_Number, @Command
WHILE @@FETCH_STATUS = 0
BEGIN
exec SetUnitTestStepVariables @TestName , @TargetDatabase , _
@Step_Number, 1, 'Initialise'
set @command = dbo.ReplaceUnitTestvariablesWithValues_
(@TestName, @TargetDatabase, @command)
exec dbo.RunDatabaseCommand @command, @TargetDatabase, @tmpErr , @tmpRows
exec SetUnitTestStepVariables @TestName , @TargetDatabase , _
@Step_Number, 0, 'Initialise'
-- Get the next pending command to execute
FETCH NEXT FROM BeforeUnitTestCursor INTO @Step_Number, @Command
END
CLEAN_EXIT:
-- Always close and deallocate the cursor, if in error or not
CLOSE BeforeUnitTestCursor
DEALLOCATE BeforeUnitTestCursor
END
1.2 变量
您会注意到有两个命令用于设置单元测试变量，并将变量替换为它们的当前值。这是必需的，以便您可以编写单元测试步骤，其 T-SQL 定义仅在测试运行时设置（例如，要获取具有标识字段的表中最新记录的 ID，您需要创建一个单元测试变量并使用 T-SQL select IDENT_CURRENT('tablename') 填充它。
为每个单元测试定义的变量保存在表 [Unit Test Variable Definition] 中。
CREATE TABLE [Unit Test Variable Definitions] (
[Test Name] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Variable Name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Description] [varchar] (220) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_Unit Test Variable Definitions] PRIMARY KEY CLUSTERED
(
[Test Name],
[Variable Name]
) ON [PRIMARY]
) ON [PRIMARY]
GO
当测试运行时，这些变量对于给定命名数据库的当前值存储在表 [Unit Test Variables] 中。
CREATE TABLE [Unit Test Variables] (
[Test Name] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Database Name] [varchar] (220) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Variable Name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Value] [varchar] (220) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_Unit Test Variables] PRIMARY KEY CLUSTERED
(
[Test Name],
[Database Name],
[Variable Name]
) ON [PRIMARY]
) ON [PRIMARY]
GO
用于设置每个变量的 T-SQL 保存在表 [Unit test Variable Assignment] 中。
CREATE TABLE [Unit Test Variable Assignment] (
[Test Name] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Step Number] [int] NOT NULL ,
[Step Type] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Variable Name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Run Before Step] [bit] NOT NULL ,
[Assignment Command] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [PK_Unit Test Variable Assignment] PRIMARY KEY CLUSTERED
(
[Test Name],
[Step Number],
[Variable Name],
[Run Before Step]
) ON [PRIMARY]
) ON [PRIMARY]
GO
您会注意到此表也包含步骤编号，因为同一个变量可以在单元测试的每个步骤后分配不同的值（例如，如果您正在进行累计总计）。
单元测试变量的设置由存储过程 SetUnittestvariables 执行。
CREATE PROCEDURE dbo.SetUnitTestStepVariables _
(@TestName char(20), @TargetDatabase varchar(220) , @Step_Number int , _
@PreTest bit, @Step_Type varchar(20)) AS
BEGIN
DECLARE @command varchar(3000),
@variable_name varchar(100),
@local_variable_name varchar(101),
@local_command nvarchar(3100),
@params_def nvarchar(200),
@value varchar(220),
@errnum int
DECLARE UnitTestStepVariableAssignment CURSOR READ_ONLY LOCAL FOR
SELECT [Variable Name],
[Assignment Command]
FROM [dbo].[Unit test Variable Assignment]
WHERE [Test name] = @Testname
AND [Step Number] = @step_number
AND [Run Before Step] = @Pretest
AND [Step Type] = @Step_Type
OPEN UnitTestStepVariableAssignment
FETCH UnitTestStepVariableAssignment INTO @variable_name, @command
WHILE @@fetch_status = 0
BEGIN
-- Make a valid variable name for use in an SQL statement
SET @local_variable_name = '@output_value'
SET @local_command = 'SET ' + @local_variable_name + _
' = ( ' + replace(@command, '[database]','[' + @TargetDatabase + ']') + ')'
SET @params_def = @local_variable_name + ' VARCHAR(100) OUTPUT'
-- execute the command to populate the @value field
EXEC sp_executesql @stmt = @local_command,
@params = @params_def,
@output_value = @value output
-- Get the error number if any
SELECT @ErrNum = ISNULL(@@Error, 0)
-- If we got the value without any error, update the holding table
IF (@value IS NOT NULL) AND (@ErrNum = 0)
UPDATE [dbo].[Unit Test variables]
SET [Value] = @Value
WHERE [Test Name] = @testname
AND [Database Name] = @TargetDatabase
AND [Variable name] = @variable_name
FETCH UnitTestStepVariableAssignment INTO @variable_name, @command
END
CLOSE UnitTestStepVariableAssignment
DEALLOCATE UnitTestStepVariableAssignment
END
GO
单元测试变量在命令的实际 SQL 中用“@@”前后标记。然后在运行时，通过标量函数 ReplaceUnitTestvariablesWithValues 将变量名替换为变量值。
-- [ RepaceUnitTestvariablesWithValues ]---------------------------------------
-- Replaces and @@variable@@ with the value looked up from the
-- [Unit test Variables] table for the give test on the given database
-------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[ReplaceUnitTestvariablesWithValues]
(
@test_name varchar(20),
@database_name varchar(220),
@command_in varchar(3000)
)
RETURNS varchar(3000)
AS
BEGIN
declare @command_out varchar(3000),
@value varchar(220),
@variable_name varchar(100)
set @command_out = @command_in
DECLARE variables_substitution CURSOR READ_ONLY LOCAL FOR
SELECT ltrim(rtrim([Variable Name])),
ISNULL([Value], 'NULL')
FROM [dbo].[Unit Test Variables]
WHERE [test name] = @test_name
AND [database name] = @database_name
OPEN variables_substitution
FETCH variables_substitution INTO @variable_name, @value
WHILE @@fetch_status = 0
BEGIN
SET @command_out = replace(@command_out, '@@' + @variable_name + '@@', @value)
FETCH variables_substitution INTO @variable_name, @value
END
CLOSE variables_substitution
DEALLOCATE variables_substitution
return @command_out
END
GO
1.3 运行单个 T-SQL 命令
要运行单个命令，我们首先将 [database] 替换为传入的数据库名称，然后使用 exec 命令来运行它。
CREATE PROCEDURE dbo.RunDatabaseCommand (@Command varchar(2000), _
@TargetDatabase varchar(220), @error int output, @rows int output) AS
BEGIN
select @command = replace(@command, '[database]', '[' + @targetDatabase +']')
exec (@command)
select @error = isnull(@@error,0), @rows = isnull(@@rowcount,0)
END
GO
@@error 和 @@rowcount 系统变量会被传回，以记录命令的执行情况。
1.4 测试步骤验证
在每个测试步骤之后，您可能希望测试步骤运行后的数据库状态。这是一个测试步骤验证，验证的定义存储在表 [Unit Test Step Validation] 中。
CREATE TABLE [Unit Test Step Validation] (
[Test Name] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Step Number] [int] NOT NULL ,
[Validation Number] [int] NOT NULL ,
[Data Table Name] [varchar] (220) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Field Name] [varchar] (220) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Test Type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS _
NOT NULL CONSTRAINT [DF_Unit Test Step Validation_test Type] DEFAULT _
([dbo].[DefaultReferenceDataItem]('Unit Test Comparison')),
[Field Comparison] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Where clause] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Username] [varchar] (220) COLLATE SQL_Latin1_General_CP1_CI_AS _
NOT NULL CONSTRAINT [DF_Unit Test Step Validation_Username] DEFAULT (suser_sname()),
CONSTRAINT [PK_Unit Test Step Validation] PRIMARY KEY CLUSTERED
(
[Test Name],
[Step Number],
[Validation Number]
) ON [PRIMARY]
) ON [PRIMARY]
GO
可以执行的验证类型是：
- 'Exists' - 数据表中有一个或多个记录符合
where子句。 - 'Not exists' - 数据表中没有记录符合
where子句。 - 'Equals' - 数据表中符合
where子句的行的字段等于字段比较值。 - 'Less Than' - 同上，但小于比较值。
- 'More Than' - 同上，但大于比较值。
- 'Not Equals' - 同上，但等于比较值。
单元测试过程中执行这些验证测试的部分如下：
DECLARE @ValidationNumber int,
@DataTableName [varchar] (220) ,
@FieldName [varchar] (220),
@TestType [varchar] (50) ,
@FieldComparison [varchar] (2000),
@WhereClause [varchar] (200),
@ValidationCommand [nvarchar] (3000) ,
@xtype tinyint
-- Otherwise execute all the post-step tests in turn
DECLARE UnitTestValidationCursor CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR
SELECT [Validation Number],
[Data Table Name],
[Field Name] ,
[Test Type],
[Field Comparison],
[Where clause]
FROM [Unit Test Step Validation]
WHERE [Test name] = @TestName
AND [Step Number] = @Step_Number
ORDER BY [Validation Number]
OPEN UnitTestValidationCursor
FETCH NEXT FROM UnitTestValidationCursor INTO @ValidationNumber ,
@DataTableName ,
@FieldName ,
@TestType,
@FieldComparison ,
@WhereClause
WHILE @@FETCH_STATUS = 0
BEGIN
-- Create the SQL to run the test
SET @ValidationCommand = 'SELECT @rows = _
count(1) FROM [' + @targetdatabase + ']..' + @DataTableName + _
' WHERE ' + @Whereclause
IF (@TestType != 'Not Exists' AND @TestType != 'Exists')
BEGIN
-- Get the data type of the field to compare to...
SELECT @xtype = ISNULL(xtype,0)
FROM syscolumns
WHERE OBJECT_NAME([ID]) = @DataTablename
AND [name] = @Fieldname
SET @ValidationCommand = @ValidationCommand + _
' AND ' + @fieldname + ' '
IF @TestType = 'Equals'
SET @ValidationCommand = @ValidationCommand + ' = '
IF @TestType = 'Greater Than'
SET @ValidationCommand = @ValidationCommand + ' > '
IF @TestType = 'Less Than'
SET @ValidationCommand = @ValidationCommand + ' < '
IF @TestType = 'Like'
SET @ValidationCommand = @ValidationCommand + ' LIKE '
IF @TestType = 'Not Equals'
SET @ValidationCommand = @ValidationCommand + ' != '
--characters (TYPE = 231, 175) NEED QUOTES...
if @xtype in (231,175)
SET @ValidationCommand = @ValidationCommand + _
dbo.quotestring(@fieldcomparison)
else if @xtype = 61
SET @ValidationCommand = @ValidationCommand + _
dbo.quotedate(@fieldcomparison)
else
SET @ValidationCommand = @ValidationCommand + @fieldcomparison
END
-- Execute the test
IF @ValidationCommand != ''
begin
set @validationcommand = _
dbo.ReplaceUnitTestvariablesWithValues_
(@TestName, @TargetDatabase, @validationcommand)
EXEC sp_executesql @validationcommand,
N'@rows int output',
@rows = @tmprows OUTPUT
select @tmpErr = isnull(@@error,0)
end
2. 数据模型
数据模型是每个数据库的一个附加属性，它指定了数据库的类型。例如，如果一个公司有一种用于记录航班时刻表的数据库类型，另一种用于记录员工详细信息的数据库类型，那么在前一种数据库中有意义的测试在后一种数据库中将无法合理地运行。因此，存在一个表（[Production databases]），该表将每个数据库映射到该数据库是其实例的数据模型。
CREATE TABLE [Production Databases] (
[Server name] [varchar] (220) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Database name] [varchar] (220) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Model Name] [varchar] (220) COLLATE SQL_Latin1_General_CP1_CI_AS _
NOT NULL CONSTRAINT [DF_Production Databases_Model name] _
DEFAULT ([dbo].[DefaultReferenceDataitem]('Database Model')),
CONSTRAINT [PK_Production Databases] PRIMARY KEY CLUSTERED
(
[Server name],
[Database name]
) ON [PRIMARY]
) ON [PRIMARY]
GO
示例
以下是一个非常简单的示例，以传达这个概念：
在我们的模型数据库中，我们有一个名为 Fund 的表，它有两个字段：PriceRound 和 UnitRound，它们被定义为 INT 类型，我们想添加一个测试行，其 UnitRound 为 4，PriceRound 为 2。
设置单元测试的脚本
------------------------------------------------------------------
-- fund class rounding unit test
-- Purpose:
-- Comments:
-- Author:
------------------------------------------------------------------
declare @test_name char(20)
select @test_name = 'FundClassRounding'
delete [Unit test Definition]
where [test name] = @test_name
insert into [Unit test Definition] ([test name], [description]) _
values (@test_name, 'FundClass table rounding test')
-- Remove any existing initialisation for this test
delete [Unit test Initialise]
where [Test Name] = @test_name
-- Add steps to delete any funds called "Duncan%"
insert into [Unit Test Initialise] values _
(@test_name, 2, 'delete [dbo].[Fund] where fund LIKE ' + dbo.quotestring('Duncan%'))
insert into [Unit Test Initialise] values _
(@test_name, 1, 'delete [dbo].[FundClass] where fundclass LIKE ' + dbo.quotestring('Duncan%'))
insert into [Unit Test Initialise] values (@test_name, 3, 'insert into [dbo].[Fund] _
(FundName, Fund, Parent) VALUES (' + dbo.quotestring('Duncan') + ',' + _
dbo.quotestring('Duncan') + ',' + dbo.quotestring('Duncan') + ')' )
insert into [Unit Test Initialise] values (@test_name, 4, 'insert into _
[dbo].[FundClass] (FundClass, Fund) VALUES (' + dbo.quotestring('Duncan') + ',' + _
dbo.quotestring('Duncan') + ')' )
-- Add the individual test steps...
delete [Unit test step]
where [Test Name] = @test_name
insert into [Unit Test Step] values (@test_name, 1, _
'Update [dbo].[FundClass] set UnitRound = 2, _
PriceRound = 4 where FundClass = ' + dbo.quotestring('Duncan') , 1, suser_sname() )
-- Add the post step tests
delete [Unit Test Step Validation]
where [Test Name] = @test_name
insert into [Unit Test Step Validation] values (@test_name, 1, 1, '[dbo].[FundClass]', _
'[UnitRound]', 'Equals', '2', 'FundClass = ' + dbo.quotestring('Duncan'), suser_sname())
insert into [Unit Test Step Validation] values (@test_name, 1, 2, '[dbo].[FundClass]', _
'[PriceRound]', 'Greater Than', '3', 'FundClass = ' + dbo.quotestring('Duncan'), suser_sname())
insert into [Unit Test Step Validation] values (@test_name, 1, 3, '[dbo].[FundClass]', _
'[PriceRound]', 'Less Than', '5', 'FundClass = ' + dbo.quotestring('Duncan'), suser_sname())
insert into [Unit Test Step Validation] values (@test_name, 1, 4, '[dbo].[FundClass]', _
'[PriceRound]', 'Less Than', '2', 'FundClass = ' + dbo.quotestring('Duncan'), suser_sname())
-- Remove any existsing cleanup steps for this test
delete [Unit test Teardown]
where [Test Name] = @test_name
-- Add steps to delete any funds called "Duncan%"
insert into [Unit Test Teardown] values (@test_name, 1, _
'delete [dbo].[FundClass] where fundclass LIKE ' + dbo.quotestring('Duncan%'))
insert into [Unit Test Teardown] values (@test_name, 2, _
'delete [dbo].[Fund] where fund LIKE ' + dbo.quotestring('Duncan%'))
示例结果
| 测试名称 | 步骤编号 | 验证编号 | 运行开始 | 运行结束 | 错误编号 | 版本 | 结果 | 用户名 |
FundClassRounding | 1 | 0 | 2007-01-22 12:00:02 | 2007-01-22 12:00:03 | 0 | 4.36.0 | 查询成功执行 | jonesde |
FundClassRounding | 1 | 1 | 2007-01-22 12:00:03 | 2007-01-22 12:00:03 | 0 | 4.36.0 | 验证成功：Equals | jonesde |
FundClassRounding | 1 | 2 | 2007-01-22 12:00:03 | 2007-01-22 12:00:03 | 0 | 4.36.0 | 验证成功：Greater Than | jonesde |
FundClassRounding | 1 | 3 | 2007-01-22 12:00:03 | 2007-01-22 12:00:04 | 0 | 4.36.0 | 验证成功：Less Than | jonesde |
FundClassRounding | 1 | 4 | 2007-01-22 12:00:04 | 2007-01-22 12:00:04 | 0 | 4.36.0 | 验证失败：Less Than | jonesde |
进一步开发或评论？
此单元测试框架当前实现的完整源代码已附加到本文。我很想听听您对进一步开发或改进的任何建议。
历史
- 2014年9月25日：初版