使用 TSQLT 进行 SQL 单元测试的介绍
4.53/5 (10投票s)
如何使用 tSQLt 为 SQL 中的存储过程和函数编写单元测试用例？本文也可作为教程。
引言
tSQLt（网站）是一个用于 Microsoft SQL Server 的数据库单元测试框架。它提供用于创建和执行测试用例的 API，并能将其与持续集成服务器（如 CruiseControl、TFS 和 Bamboo）集成。tSQLt 兼容 SQL Server 2005（需要 service pack 2）及以上版本的所有版本。
背景
如今，对代码进行单元测试的重视程度越来越高。甚至客户有时也不会接受或审查没有编写单元测试用例的代码。我们可以遵循 TDD（测试驱动开发）在编写代码之前编写单元测试，或者在编写代码之后编写单元测试。在这个许多项目遵循敏捷开发的现代世界里，TDD 可以说是“伪装的恩赐”。
在编写单元测试时，开发人员可以使用各种单元测试框架，如 NUnit、JUnit、Moq 或 Microsoft Visual Studio 自带的单元测试框架。所有这些框架以及许多其他类似的框架在编写服务器端代码的单元测试时都非常有用。也有用于测试前端代码的框架，例如 Jasmine、Mocha 和 Karma 等。同样，tSQLt 是一个用于单元测试用 SQL 编写的代码（如存储过程和函数）的框架。
为 SQL 代码编写单元测试的重要性是什么？
许多人认为没有必要为用 SQL 编写的代码编写单元测试，因为解决方案的 DAL（数据访问层）或连接数据库并获取、更新或插入数据的服务器代码已经有测试用例了。以下几点将阐明为数据库代码编写单元测试的重要性。
- 在使用 NUnit 或任何其他服务器端框架测试数据库时，我们也必须编写清理代码。这是必需的，否则这些测试用例将导致数据库中不必要的更新、插入或删除。而使用 tSQLt，测试会在事务中自动执行，从而消除了编写清理代码的需要。
- 使用服务器端框架会使测试用例高度依赖数据库中的数据。如果有人更改了这些数据（数据库刷新、有人删除表中的行或更新它们），测试用例就会开始失败。tSQLt 提供了模拟表和视图的能力，以及创建存储过程间谍，从而消除了对底层数据的依赖。
- 假设我们的存储过程根据事件的开始日期和结束日期返回事件。为此存储过程编写的服务器端测试用例现在可能通过了，但在几天后，当某个事件过期时，它返回的结果将发生变化，测试用例也会失败。因此，即使没有人更改底层数据，测试用例仍然会开始失败。由于 tSQLt 完全独立于数据，因此可以避免这种情况。
- 一些存储过程可能包含非常复杂的逻辑和计算。有了 tSQLt，我们可以确信所有这些都得到了充分的测试。
现在，在开始编写 UTC 之前，我们需要注意一些先决条件。我建议先在本地数据库中安装 tSQLt，并编写一些示例测试用例并执行它们，然后再将其安装在开发数据库中。
必备组件
- 从 Sourceforge 下载 tSQLt。
- 在数据库所在的服务器上启用 CLR。如果要先在本地数据库中尝试编写 UTC，则应使用本地 SQL Server，否则请在开发服务器上进行。
执行以下脚本进行操作。
EXEC sp_configure 'clr enabled', 1; RECONFIGURE;
- 将数据库设置为“可信”以便 tSQLt 运行。我在本地创建了一个名为
POC_UnitTests的数据库。执行以下脚本。ALTER DATABASE POC_UnitTests SET TRUSTWORTHY ON;
- 通过运行下载文件中提供的 tSQLt.class.sql 脚本文件在数据库上安装 tSQLt。这将在数据库中添加一些表和许多存储过程。tSQLt 需要它们才能正常运行。
注意：由于我们只在本地和开发服务器上编写 UTC，因此在这些服务器上启用 CLR 和设置为可信是没问题的。然而，不建议在任何其他服务器上这样做，尤其是在生产服务器上。
Using the Code
使用 tSQLt 编写单元测试用例
既然我们已经完成了所有先决条件，我们就可以开始编写测试用例了。
EXEC tSQLt.NewTestClass 'testJobs';
GO
上述语句在 tSQLt 中创建了一个名为“testJobs”的类。这很有帮助，因为我们可以将一个模块的所有测试用例分组到一个类中，并为每个模块设置不同的类。此外，每个开发人员都可以拥有自己的类来编写测试用例。编写完所有测试用例后，我们可以只执行一个特定类的测试用例，或者执行所有类的所有测试用例。可以使用以下语句来实现这一点。
EXEC tSQLt.RunAll --for executing all modules
EXEC tSQLt.Run 'testJobs'; --for executing only testJobs class/module
让我们从一个简单的测试用例开始，以检查 tSQLt 框架是否正常工作。
CREATE PROCEDURE testJobs.[testing simple UTC]
AS
BEGIN
DECLARE @sum INT;
SELECT @sum = 3 + 2;
EXEC tSQLt.AssertEquals 5, @sum;
END;
tSQLt 为每个测试用例在数据库中创建一个存储过程，该存储过程位于一个模式（在此例中为 testJobs）中，并为其命名（在此例中为 testing simple UTC）。上述测试用例应该会成功，因为我们只是尝试检查 3 和 2 的总和是否等于 5。要执行此测试用例，请使用命令：
EXEC tSQLt.Run 'testJobs';
测试用例 1（在数据库中执行以下命令）
EXEC tSQLt.NewTestClass 'testJobs';
GO
---------------------------------------------------------------------------------------------------
CREATE PROCEDURE testJobs.[testing simple UTC]
AS
BEGIN
DECLARE @sum INT;
SELECT @sum = 3 + 2;
EXEC tSQLt.AssertEquals 5, @sum;
END;
GO
-----------------------------------------------------------------------------------------
EXEC tSQLt.Run 'testJobs';
以下消息将确认测试用例已成功执行。
现在我们已经编写了一个简单的 UTC，让我们编写一些涉及实际表、存储过程和函数的 UTC。
首先，我在数据库中创建了一个表、一个函数和一个存储过程。
CREATE TABLE [dbo].[ReportSchedulerJob](
[ReportSchedulerJobID] [bigint] IDENTITY(1,1) NOT NULL,
[Status] [nvarchar](50) NOT NULL,
[ExecutionStatus] [nvarchar](50) NULL,
[StartDate] [datetime2](7) NOT NULL,
[EndDate] [datetime2](7) NULL,
[JobType] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_ReportSchedulerJob] PRIMARY KEY CLUSTERED
(
[ReportSchedulerJobID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, _
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE FUNCTION GetStatusMessage()
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @NumParticles INT;
SELECT @NumParticles = COUNT(1) FROM dbo.ReportSchedulerJob;
--RETURN 'The Accelerator is prepared with ' +
--CAST(@NumParticles AS NVARCHAR(MAX)) + ' particles.';
RETURN 'testing function with row count '+ CAST(@NumParticles AS NVARCHAR(MAX));
END;
CREATE Procedure GetJobsFromDB(@jobType nvarchar(25))
AS
BEGIN
select top 1 [Status],[ExecutionStatus],[StartDate],_
[EndDate],[JobType] from ReportSchedulerJob where JobType = @jobType
RETURN;
END
现在我们可以基于这些数据库对象编写测试用例了。
测试用例 2
CREATE PROCEDURE testJobs.[testing a function with a fake table]
AS
BEGIN
--Assemble: Fake the ReportSchedulerJob table to make sure it is empty
--and that constraints will not be a problem
EXEC tSQLt.FakeTable 'dbo.ReportSchedulerJob';
--Put 2 test records into the table
INSERT INTO [dbo].[ReportSchedulerJob] ([Status],[ExecutionStatus],[StartDate],[EndDate],[JobType])
VALUES ('Success','Complete','07-14-2015', '07-14-2015', 'manual');
INSERT INTO [dbo].[ReportSchedulerJob] ([Status],[ExecutionStatus],[StartDate],[EndDate],[JobType])
VALUES ('Success','Complete','07-14-2015', '07-14-2015', 'manual');
--Act: Call the GetStatusMessage Function
DECLARE @StatusMessage NVARCHAR(MAX);
SELECT @StatusMessage = dbo.GetStatusMessage();
--Assert: Make sure the status message is correct
EXEC tSQLt.AssertEqualsString 'testing function with row count 2', @StatusMessage;
END;
在上述测试用例中，我们模拟了数据库中已存在的 ReportSchedulerJob 表。当我们模拟一个表时，tSQLt 会在我们的 UTC 模式（在此例中为 testJobs）中创建一个同名的临时表。该表是空的，默认没有约束（没有约束使插入更容易）。在模拟表后，我们向表中插入了 2 行（虚拟记录）。然后我们执行了我们的函数，该函数基本上返回表中的记录数。如我们所见，它返回的数量是 2，尽管实际表中没有记录（因为我们没有在实际表中插入任何内容）。我们成功地测试了一个函数，而无需查看它引用的表中的数据。因此，模拟表使我们的 UTC 独立于其中存在的数据。
测试用例 3
CREATE PROCEDURE [testJobs].[testing a SP which gets Jobs From DB table]
AS
BEGIN
-- Create Actual and Expected table to hold the actual results of GetJobsFromDB SP
-- and the results that we expect
CREATE TABLE [testJobs].[Actual] ([Status] nvarchar(50) null, _
[ExStatus] nvarchar(50) null, StartDate DateTime2, EndDate DateTime2, JobType nvarchar(50));
CREATE TABLE [testJobs].[Expected] ([Status] nvarchar(50) null, _
[ExStatus] nvarchar(50) null, StartDate DateTime2, EndDate DateTime2, JobType nvarchar(50));
--Fake Table - Isolate this test from the real data
EXEC tSQLt.FakeTable 'dbo.ReportSchedulerJob'
INSERT [dbo].[ReportSchedulerJob] ([Status],[ExecutionStatus],[StartDate],[EndDate],[JobType])
VALUES ('Success','Complete','07-14-2015', '07-14-2015', 'manual')
--Execution
INSERT [testJobs].[Actual]([Status],[ExStatus],[StartDate],[EndDate],[JobType])
EXEC dbo.GetJobsFromDB 'manual';
-- Create the expected output
INSERT [testJobs].[Expected] ([Status],[ExStatus],[StartDate],[EndDate],[JobType])
VALUES ('Success','Complete','07-14-2015', '07-14-2015', 'manual');
--Check that Expected and Actual tables contain the same results
DECLARE @expectedRowCount int; SET @expectedRowCount = 1;
DECLARE @actualRowCount int; SET @actualRowCount = (SELECT COUNT(*) FROM [testJobs].[Actual])
--! Assert
EXEC tSQLt.AssertEquals @expectedRowCount, @actualRowCount, 'The number of rows returned is incorrect';
EXEC tSQLt.AssertEqualsTable '[testJobs].[Expected]', _
'[testJobs].[Actual]', 'The column order in the result set is incorrect';
END;
在上述测试用例中，我们通过模拟一个表来测试了一个存储过程。通过这样做，我们的测试用例将不会依赖于表中存在的实际数据。
现在我们可以通过运行以下命令来执行我们编写的所有 3 个 UTC。
EXEC tSQLt.Run 'testJobs'
执行后将看到以下消息：
关注点
因此，我们刚刚看到了如何轻松地使用 tSQLt 测试我们的函数和存储过程。它使用事务来运行测试用例，并在执行测试用例后清理为测试用例而编写的所有不必要的数据库对象。这意味着我们不必担心从模拟表中删除数据，或删除模拟表，或删除我们在测试用例中创建的表，如 Actual 和 Expected。tSQLt 还有许多其他功能，可以使编写 UTC 非常简单。我将尝试撰写另一篇文章来介绍所有这些有趣的功能。您可以通过 tSQLt 用户指南阅读更多关于 tSQLt 的信息。如果您在编写 UTC 时遇到困难或有任何其他问题，请随时与我联系。
我附带了一个包含我们刚刚创建的 3 个 UTC 的脚本。
历史
此帖子的第一个版本。