软件测试宝藏图_tSQLt –数据库单元测试中被遗忘的宝藏

软件测试宝藏图

This article is an overview of tSQLt, which is one of the best SQL unit testing frameworks, due to number of reasons, including the ease of writing unit tests in the same language (T-SQL) in which the database objects themselves are written.

本文概述了tSQLt，它是最好SQL单元测试框架之一，由于多种原因，包括易于编写与编写数据库对象本身相同的语言（T-SQL）来编写单元测试。

Most of this article is knowledge based and has been written keeping in mind the first time tSQLt and SQL unit testing learners.

本文的大部分内容都是基于知识的，并且在撰写时要牢记第一次使用tSQLt和SQL单元测试的学习者。

This article also highlights the very compatible nature of tSQLt with core concepts of SQL unit testing there by making it best fit for the purpose along with the things which are hard to notice in the fast paced SQL unit testing world but serve as driving force to build state of the art database unit tests.

本文还强调了tSQLt与SQL单元测试的核心概念的高度兼容特性，使其最适合该目的，以及在快速发展SQL单元测试世界中难以注意到的事物，但它们却是构建SQL Server的驱动力最新的数据库单元测试。

SQL单元测试核心概念和tSQLt (SQL unit testing Core Concepts and tSQLt )

It is very unlikely for an experienced database developer or database tester to be unaware of the core concepts of SQL unit testing, but it is worth reviewing them (if you are already familiar) especially when they are discussed in relation to tSQLt.

有经验的数据库开发人员或数据库测试人员不太可能不了解SQL单元测试的核心概念，但是（如果您已经很熟悉的话）有必要对其进行复习（特别是在与tSQLt相关的讨论中）。

什么是SQL单元测试？ (What is SQL unit testing? )

SQL unit testing as the name suggests is testing individual units of the database by writing unit tests which are also called test cases and tSQLt makes this task easy and instant.

顾名思义，SQL单元测试是通过编写单元测试（也称为测试用例）来测试数据库的各个单元，而tSQLt使得此任务既轻松又即时。

什么是数据库单位？ (What is a Database Unit?)

A database unit is any database object such as view or stored procedure which contributes partially or fully to meet business requirement directly or indirectly and tSQLt is capable of giving full coverage to all the database objects in a database.

数据库单元是任何数据库对象，例如视图或存储过程，它们部分或全部有助于直接或间接地满足业务需求，并且tSQLt能够完全覆盖数据库中的所有数据库对象。

何时创建数据库单元测试？ (When do you Create Database Unit Tests?)

Database unit tests are created right from the database development phase alongside creating database objects and tSQLt makes it easy to do that and lets you create these unit tests instantly.

数据库单元测试是在数据库开发阶段即创建数据库对象的同时直接创建的，而tSQLt使其易于实现，并使您可以立即创建这些单元测试。

您应该在数据库对象之前还是之后创建单元测试？ (Should you Create Unit Tests Before or After Database Objects?)

In conventional database development pattern, you create database objects first and then you write database unit tests for those objects, however, this is not always the case.

在传统的数据库开发模式中，首先创建数据库对象，然后为这些对象编写数据库单元测试，但是，并非总是如此。

You can create database unit tests before or after creating a database object and tSQLt supports both testing patterns (which are discussed in detail in the upcoming articles of this series).

您可以在创建数据库对象之前或之后创建数据库单元测试，并且tSQLt支持两种测试模式（在本系列的后续文章中将对此进行详细讨论）。

谁编写数据库单元测试？ (Who Writes Database Unit Tests?)

Mostly, database developers who create database objects also create database unit tests and learning tSQLt to write database unit tests is easier than other SQL unit testing frameworks.

通常，创建数据库对象的数据库开发人员还会创建数据库单元测试，并且学习tSQLt来编写数据库单元测试比其他SQL单元测试框架更容易。

谁运行数据库单元测试？ (Who Runs Database Unit Tests?)

Database Unit Tests are generally run by the development team members who write them in first place followed by test team as a part of manual or automated process during the build and release time while further details of these processes are beyond the scope of this article.

数据库单元测试通常由开发团队成员运行，他们首先将其编写，然后在构建和发布期间将它们作为手动或自动过程的一部分进行测试，而这些过程的更多细节不在本文的讨论范围之内。

了解tSQLt的特殊功能 (Understanding Special Features of tSQLt)

We are purely going to focus on what makes tSQLt one of the best SQL unit testing frameworks.

我们将仅关注于使tSQLt成为最佳SQL单元测试框架之一的原因。

Let us rebuild tSQLt by putting all its best things together one by one to understand its distinctive features assuming you are tasked to build tSQLt framework.

让我们通过将所有最好的功能一一并存以了解其独特功能来重建tSQLt，假设您要负责构建tSQLt框架。

First, visualise the features in the illustration below:

首先，可视化下图中的功能：

没有从T-SQL切换上下文 (No Context Switching From T-SQL )

If you have to build an all-purpose SQL unit testing framework you would like it to be in the same language in which database development is taking place which is T-SQL.

如果必须构建一个通用SQL单元测试框架，则希望它使用与数据库开发相同的语言，即T-SQL。

This is exactly what tSQLt is offering to its users as writing database unit tests in tSQLt is same as writing database objects in T-SQL.

这正是tSQLt向用户提供的，因为在tSQLt中编写数据库单元测试与在T-SQL中编写数据库对象相同。

In other words there is no context switching from T-SQL to any other language or tool to write unit tests so it does not require additional learning skills and it is easy and quick to write database unit tests.

换句话说，没有上下文可以从T-SQL切换到任何其他语言或工具来编写单元测试，因此不需要其他学习技能，并且可以轻松快捷地编写数据库单元测试。

数据库单元测试分组 (Database Unit Tests Grouping)

Your proposed SQL unit testing framework must be able to arrange unit tests in groups so that you can run unit tests on these groups independently and on demand without needing to run all the unit tests.

建议SQL单元测试框架必须能够按组安排单元测试，以便您可以按需独立地在这些组上运行单元测试，而无需运行所有单元测试。

tSQLt lets you arrange your unit tests into different groups and then individual group unit tests can be run independently or on demand which is such a relief when it comes to narrowing the scope of unit testing to test particular business requirement.

使用tSQLt，您可以将单元测试划分为不同的组，然后可以独立地或按需运行单个组单元测试，这在缩小单元测试范围以测试特定业务需求方面是一种缓解。

For example you can put all the unit tests related to a business requirement into one group such as AuthorsReportTests group contains all the unit tests related to Authors Report.

例如，您可以将与业务需求相关的所有单元测试归为一组，例如AuthorsReportTests组包含与Authors Report相关的所有单元测试。

In case of troubleshooting Authors Report you can only run AuthorsReportTests as a first step to check the unit tests are all fine or not.

如果要对Authors Report进行故障排除，则只能运行AuthorsReportTests作为第一步，以检查单元测试是否正常。

真正隔离测试对象 (True Isolation of Objects under Test)

Another important factor in building a unit testing framework is to make sure that it provides isolation of the objects under test which means it should be able to focus on a single database object there by isolating it from any other dependent objects.

构建单元测试框架的另一个重要因素是要确保提供测试对象的隔离，这意味着它应该能够通过将其与任何其他从属对象隔离来专注于单个数据库对象。

Isolating object under test from other dependent objects is very distinctive feature of tSQLt.

将被测对象与其他依赖对象隔离是tSQLt的非常独特的功能。

For example if you are unit testing a stored procedure called AddAuthor()which adds a new author to the database table but also internally calls another stored procedure LogUser()which stores the information of the database user who is performing this action of adding new author then tSQLt isolates AddAuthor() completely from LogUser() stored procedure in such a way that it feels LogUser() is not there anymore and the only object under test is AddAuthor().

例如，如果您正在单元测试一个名为AddAuthor（）的存储过程，该存储过程将一个新作者添加到数据库表中，但还内部调用另一个存储过程LogUser（） ，该存储过程将存储执行此添加新作者操作的数据库用户的信息然后tSQLt会将AddAuthor（）与LogUser（）存储过程完全隔离开 ， 以至于感觉到LogUser（）不再存在，唯一的测试对象是AddAuthor（） 。

This does however require you to write another unit test for LogUser() this time isolating it from AddAuthor().

但是，这确实需要您这次为LogUser（）编写另一个单元测试，以将其与AddAuthor（）隔离。

测试在事务中运行 (Tests Run in Transactions )

You would like to see our proposed SQL unit testing framework to run unit tests in transactions so that they can be run in parallel and in a consistent manner.

您希望看到我们建议SQL单元测试框架，该框架可以在事务中运行单元测试，以便可以并行且一致地运行它们。

tSQLt by default supports running unit tests in transactions which also mean no need to clean up test objects afterwards since they are going to be automatically cleaned.

默认情况下，tSQLt支持在事务中运行单元测试，这也意味着以后无需清理测试对象，因为它们将被自动清理。

数据库连续集成（CI）支持 (Database Continuous Integration (CI) Support)

A good SQL unit testing framework is the one which can be integrated with other integration tools such Azure DevOps and fully supports Database Lifecycle Management (DLM) so you want to see this integration capability in your proposed unit testing framework.

一个好SQL单元测试框架是可以与其他集成工具（如Azure DevOps）集成并完全支持数据库生命周期管理（DLM）的框架，因此您希望在建议的单元测试框架中看到这种集成功能。

The good news is that tSQLt provides the integration support for Database Continuous Integration and Deployment through DevOps for databases.

好消息是，tSQLt通过数据库的DevOps为数据库持续集成和部署提供了集成支持。

In simple words the unit tests written in tSQLt can be fully automated in such a way that any changes to the database deployed to target environment automatically run the associated unit tests.

简而言之，以tSQLt编写的单元测试可以完全自动化，这样，对部署到目标环境的数据库所做的任何更改都会自动运行关联的单元测试。

高级SQL单元测试支持 (Advanced SQL unit testing Support)

One of the most wanted features in a SQL unit testing framework is to provide advanced SQL unit testing support and this is what you would like to see in your proposed testing framework.

SQL单元测试框架中最需要的功能之一是提供高级SQL单元测试支持，这就是您想要在建议的测试框架中看到的。

tSQLt also provides advanced SQL unit testing support so this means cross-database objects (database objects from different databases) can be unit tested as well.

tSQLt还提供了高级SQL单元测试支持，因此这意味着跨数据库对象（来自不同数据库的数据库对象）也可以进行单元测试。

定价模型，定制和竞争对手 (Pricing Model, Customisation and Competitors)

As you have just become familiar with the special features of tSQLt it is worth to see pricing model and competitors of tSQLt.

当您刚刚熟悉tSQLt的特殊功能时，有必要了解tSQLt的定价模型和竞争对手。

tSQLt定价模型 (tSQLt Pricing Model )

The pricing model of tSQLt is cost free model which means it is free to use personally and commercially because tSQLt is an open source project capable of serving the testing needs ranging from individuals to corporates.

tSQLt的定价模型是免费模型，这意味着它可以个人和商业使用，因为tSQLt是一个开源项目，能够满足从个人到公司的测试需求。

准备贡献/定制 (Ready for the Contribution / Customisation )

Since tSQLt is an open source project, its code is easily available for further contribution or customisation, however, this does require certain level of expertise in this area though.

由于tSQLt是一个开源项目，其代码可轻松用于进一步的贡献或自定义，但是，这确实需要在该领域具有一定水平的专业知识。

^第三方测试工具vs tSQLt (3^rd Party Testing Tools vs tSQLt )

One would simply argue that what is the point of using tSQLt when third-party unit testing tools are there.

可以简单地说，当存在第三方单元测试工具时，使用tSQLt有什么意义。

Yes, this is true the database testing tools are available in the market today by famous vendors who have made SQL unit testing very easy and intuitive along with fancy GUI support.

是的，这确实是当今市场上著名的供应商提供的数据库测试工具，这些供应商已经使SQL单元测试变得非常容易和直观，并提供了出色的GUI支持。

However, do you know that the underlying unit testing framework behind almost all the famous third-party database testing tools is tSQLt.

但是，您知道几乎所有著名的第三方数据库测试工具背后的基础单元测试框架都是tSQLt。

Yes, all the market leading SQL unit testing tools are based on tSQLt which shows the power, flexibility and coverage of tSQLt.

是的，所有市场领先SQL单元测试工具都基于tSQLt，它显示了tSQLt的强大功能，灵活性和覆盖范围。

在tSQLt中创建和运行单元测试的理念 (Philosophy of Creating and Running Unit Tests in tSQLt )

Let us look at the some of the core SQL unit testing concepts and their conceptual implementation to get better understanding of SQL unit testing.

让我们看一下一些核心SQL单元测试概念及其概念实现，以更好地理解SQL单元测试。

了解数据库单元测试解剖（AAA规则） (Understanding Database Unit Test Anatomy (AAA Rule))

A database unit test is based on AAA rule which consists of the following steps:

数据库单元测试基于AAA规则，该规则包含以下步骤：

1. Arrange: This is where you arrange database objects taking part in the test along with creating expected objects and mocking actual tables 安排：在这里安排您参与测试的数据库对象，以及创建预期对象和模拟实际表
2. ACT:This is where you run the object (stored procedure or view) under test and put the results into mocked actual table ACT：在此处运行测试对象（存储过程或视图）并将结果放入模拟的实际表中
3. Assert:Assert is the final step which compares expected results with actual results and on the bases of comparison a test is passed or failed 断言：断言是将预期结果与实际结果进行比较的最后一步，并且在比较的基础上，测试是否通过或失败

AAA简单示例 (AAA Simple Example )

Let us understand how unit test anatomy revolves around Arrange, Act and Assert with an example:

让我们通过一个例子来了解单元测试的解剖结构是如何围绕“排列”，“行为”和“断言”展开的：

前提条件： (Preconditions:)

You are unit testing a SQL view named Authors which shows records of all the authors stored in Author table.

您正在对名为AuthorsSQL视图进行单元测试，该视图显示了存储在Author表中的所有作者的记录。

To better understand this let us do some coding and create a very simple Author table as follows:

为了更好地理解这一点，让我们进行一些编码并创建一个非常简单的Author表，如下所示：

CREATE TABLE [dbo].[Author]
(
  [AuthorId] INT NOT NULL,  
  [Name] VARCHAR(40) NOT NULL,
    
);

Next create Authors view:

接下来创建作者视图：

CREATE VIEW [dbo].[Authors]
  AS SELECT [AuthorId], [Name] FROM [Author]

安排 (Arrange)

In the first step we create expected object and populate it with expected value.

在第一步中，我们创建期望的对象并用期望的值填充它。

In order to unit test Authors view we first need to create an expected table Authors_Expected to hold the expected results:

为了对作者视图进行单元测试，我们首先需要创建一个预期表Authors_Expected来保存预期结果：

CREATE TABLE [dbo].[Author_Expected]
(
  [AuthorId] INT NOT NULL,
  [Name] VARCHAR(40) NOT NULL
)

Next insert one author record into the expected table:

接下来，将一个作者记录插入到预期表中：

INSERT INTO [dbo].[Author_Expected] ([AuthorId], [Name]) VALUES (1, N'Asif')

Insert the same record into original table:

将相同的记录插入原始表中：

INSERT INTO [dbo].[Author] ([AuthorId], [Name]) VALUES (1, N'Asif')

法案 (Act)

In the next step we have to test run the object under test which is Authors view and put the result into a new Author_Actual table as follows:

在下一步中，我们必须测试运行被测试对象（即Authors视图），并将结果放入新的Author_Actual表中，如下所示：

-- Run object under test (Authors view) and put results into a new table Author_Actual
SELECT * INTO Author_Actual FROM Authors

断言 (Assert)

In the final stage actual results are compared with expected results and in our case we will compare Author_Actual table with Author_Expected table.

在最后阶段，将实际结果与预期结果进行比较，在本例中，我们将比较Author_Actual表和Author_Expected表。

-- See if there are any records in expected table but not in actual table
SELECT * FROM Author_Expected
EXCEPT 
SELECT * FROM Author_Actual
 
-- See if there are any records in actual table but not in expected table
SELECT * FROM Author_Actual
EXCEPT
SELECT * FROM Author_Expected

If no records are found that means both expected and actual results match so test has passed.

如果未找到任何记录，则意味着预期结果与实际结果均匹配，因此测试已通过。

Keeping this simple example in mind tSQLt lets you apply these concepts with ease and flexibility by hiding complexities and isolating objects under test in such a way that unit testing a database object becomes easy, interesting and flexible.

牢记这个简单的示例tSQLt通过隐藏复杂性并隔离测试对象，从而使单元测试数据库对象变得容易，有趣和灵活，从而使您轻松，灵活地应用这些概念。

Congratulations! You have familiarised yourself with some of the core concepts of SQL unit testing along with some key facts of tSQLt which is going to help you in writing database unit tests with tSQLt in the upcoming articles of this series.

恭喜你！ 您已经熟悉了SQL单元测试的一些核心概念以及tSQLt的一些关键事实，这将帮助您在本系列的后续文章中使用tSQLt编写数据库单元测试。

目录 (Table of contents)

翻译自: https://www.sqlshack.com/tsqlt-a-forgotten-treasure-in-database-unit-testing/

软件测试宝藏图