@sql 单元测试_简单单词中使用tSQLt进行的常规SQL单元测试

@sql 单元测试

This article is about basics of conventional SQL unit testing concepts and its implementation through tSQLt, a highly acclaimed SQL unit testing framework because of being written in T-SQL and its built-in design support for SQL SQL unit testing needs ranging from simple to complex scenarios.

本文介绍了常规SQL单元测试概念的基础及其通过tSQLt的实现，tSQLt是一个备受赞誉SQL单元测试框架，因为它是用T-SQL编写的，并且其内置的设计支持SQL从简单到复杂SQL单元测试需求场景。

This article also highlights the importance of understanding the core concepts of conventional database unit tests before you start writing and running these unit tests with tSQLt.

本文还强调了在开始使用tSQLt编写和运行这些单元测试之前，了解常规数据库单元测试的核心概念的重要性。

In this article the readers are going to be familiarised with tSQLt to write basic database unit tests chasing a simple business requirement with respect to conventional database development.

在本文中，读者将熟悉tSQLt，以编写基本的数据库单元测试，以追踪有关常规数据库开发的简单业务需求。

常规SQL单元测试基础 (Conventional SQL Unit Testing Basics)

Please remember that database development is linked with SQL unit testing whether we adopt conventional SQL unit testing style or not.

请记住，无论我们是否采用常规SQL单元测试样式，数据库开发都与SQL单元测试相关联。

Let us now go through basics of conventional SQL unit testing.

现在让我们看一下常规SQL单元测试的基础。

简单定义 (Simple definition)

A conventional SQL unit testing is a method of unit testing database objects after they are created.

常规SQL单元测试是一种在创建数据库对象之后对它们进行单元测试的方法。

替代定义 (Alternative definition)

The database (objects) development followed by their unit testing is termed as conventional SQL unit testing.

数据库（对象）开发及其后的单元测试被称为常规SQL单元测试。

关于角色扮演数据库对象 (About role playing database objects)

Database objects referenced in both versions of the definition are mainly role playing database objects.

在定义的两个版本中引用的数据库对象主要是角色扮演数据库对象。

The database objects such as SQL view, stored procedure, function etc. created to provide certain functionality can be referred to as role playing database objects, a term I am coining for the first time (but not to be confused with role playing dimensions in business intelligence solutions) to clarify the purpose of these objects in SQL unit testing through tSQLt.

创建以提供某些功能的数据库对象（例如SQL视图，存储过程，函数等）可以称为角色扮演数据库对象，这是我第一次创造的术语（但不要与业务中的角色扮演维度相混淆）智能解决方案），以阐明这些对象在通过tSQLt进行SQL单元测试中的目的。

角色扮演数据库对象与需求 (Role playing database objects vs. Requirements )

So, we know about role playing database objects now let us find out how they are linked with business requirements.

因此，我们了解角色扮演数据库对象，现在让我们了解它们如何与业务需求联系在一起。

Obviously, the standard goal of any database development project is to meet business requirements unless otherwise specified and the role playing database objects help the developers to map the business requirements.

显然，除非另有说明，否则任何数据库开发项目的标准目标都是满足业务需求，而扮演角色的数据库对象可帮助开发人员映射业务需求。

In other words, the database role playing objects are designed keeping in mind business requirements so if they are functioning properly it can be said that they are meeting the business requirement and the best way to ensure this is to unit test them using tSQLt.

换句话说，数据库角色扮演对象的设计考虑了业务需求，因此，如果它们正常运行，则可以说它们满足了业务需求，而确保这一点的最佳方法是使用tSQLt对它们进行单元测试。

常规SQL单元测试步骤 (Conventional SQL unit testing steps)

The main steps generally considered in conventional database development and unit testing are as follows:

常规数据库开发和单元测试中通常考虑的主要步骤如下：

1. Receive and review the business requirements
   接收并查看业务需求
2. Map business requirements to code (database objects)
   将业务需求映射到代码（数据库对象）
3. Create database objects to meet the business requirements
   创建数据库对象以满足业务需求
4. Create (first time) or modify (if unit tests already exist) database unit tests to check if objects are functioning properly or not
   创建（第一次）或修改（如果已经存在单元测试）数据库单元测试，以检查对象是否正常运行
5. Run database unit tests and go to step 1 if unit tests pass or step 4 if unit tests fail
   运行数据库单元测试，如果单元测试通过，则转到步骤1，如果单元测试失败，则转到步骤4

Please remember that to keep things simple we are not going into the details of whether choosing database objects to map the business requirements is the best option or not.

请记住，为简单起见，我们没有讨论选择数据库对象来映射业务需求是否是最佳选择的细节。

先决条件 (Pre-Requisites)

Let us go through pre-requisites of this article.

让我们仔细阅读本文的先决条件。

数据库概念和T-SQL熟悉度 (Database concepts and T-SQL familiarity)

The article assumes that the readers have basic know how of T-SQL and database development concepts.

本文假定读者具有T-SQL和数据库开发概念的基本知识。

Please refer to tSQLt – A Forgotten Treasure in Database Unit Testing for further information about basics of tSQLt.

有关tSQLt基础的更多信息，请参考tSQLt –数据库单元测试中被遗忘的宝藏 。

关于样本数据库 (About sample database )

In order to understand the tSQLt implementation it is better to have a sample database in hand so that we can write tSQLt unit tests against it.

为了理解tSQLt的实现，最好手头有一个示例数据库，以便我们可以针对它编写tSQLt单元测试。

I have purposely chosen to prepare a two table sample database with only primary key constraints in place to encourage the readers to focus solely on SQL unit testing rather than trying to resolve the complexity of the database being unit tested.

我有意选择准备仅具有主键约束的两表样本数据库，以鼓励读者仅专注于SQL单元测试，而不是试图解决被单元测试的数据库的复杂性。

The sample database consists of the following two tables:

该示例数据库由以下两个表组成：

1. Author
   作者
2. Article
   文章

设置样本数据库 (Setup sample database )

Let us first create a sample database called SQLDevArticlesV2 by running the following SQL script:

让我们首先通过运行以下SQL脚本创建一个名为SQLDevArticlesV2的示例数据库：

 -- 1 Create SQLDevArticlesV2 database
CREATE DATABASE SQLDevArticlesV2;
GO
 
USE SQLDevArticlesV2;
GO
 
-- 2 Create author table
CREATE TABLE [dbo].[Author] (
    [AuthorId]         INT           IDENTITY (1, 1) NOT NULL,
    [Name]             VARCHAR (40)  NOT NULL,
    [RegistrationDate] DATETIME2 (7) NULL
);
 
-- 3 Create article tables
CREATE TABLE [dbo].[Article] (
    [ArticleId]      INT           IDENTITY (1, 1) NOT NULL,
    [Title]          VARCHAR (300) NOT NULL,
    [Published_Date] DATETIME2 (7) NOT NULL
);
 
-- 4 Populate author table
SET IDENTITY_INSERT [dbo].[Author] ON
INSERT INTO [dbo].[Author] ([AuthorId], [Name], [RegistrationDate]) VALUES (1, N'Asif', N'2018-01-01 00:00:00')
INSERT INTO [dbo].[Author] ([AuthorId], [Name], [RegistrationDate]) VALUES (2, N'Peter', N'2018-02-01 00:00:00')
INSERT INTO [dbo].[Author] ([AuthorId], [Name], [RegistrationDate]) VALUES (3, N'Sarah', N'2018-03-02 00:00:00')
INSERT INTO [dbo].[Author] ([AuthorId], [Name], [RegistrationDate]) VALUES (4, N'Adil', N'2018-04-02 00:00:00')
INSERT INTO [dbo].[Author] ([AuthorId], [Name], [RegistrationDate]) VALUES (5, N'Sam', N'2019-01-01 00:00:00')
SET IDENTITY_INSERT [dbo].[Author] OFF
 
-- 5 Populate article table
SET IDENTITY_INSERT [dbo].[Article] ON
INSERT INTO [dbo].[Article] ([ArticleId], [Title], [Published_Date]) VALUES (1, N'Fundamentals of Database Programming', N'2018-01-02 00:00:00')
INSERT INTO [dbo].[Article] ([ArticleId], [Title], [Published_Date]) VALUES (2, N'Advanced Database Programming', N'2018-01-03 00:00:00')
INSERT INTO [dbo].[Article] ([ArticleId], [Title], [Published_Date]) VALUES (3, N'Understanding SQL Stored Procedures ', N'2018-02-02 00:00:00')
INSERT INTO [dbo].[Article] ([ArticleId], [Title], [Published_Date]) VALUES (4, N'Database Design Concepts', N'2018-03-02 00:00:00')
INSERT INTO [dbo].[Article] ([ArticleId], [Title], [Published_Date]) VALUES (5, N'Power BI Desktop Fundamentals', N'2019-01-02 00:00:00')
SET IDENTITY_INSERT [dbo].[Article] OFF;
GO

The sample database is now ready to be unit tested as per requirements.

现在，示例数据库已准备好根据需求进行单元测试。

Please remember that in real world scenario sample database is going to be replaced with your development database.

请记住，在现实世界中，示例数据库将被您的开发数据库替换。

tSQLt设置 (tSQLt Setup )

Next big thing, once you are familiarised with basics of conventional SQL unit testing and sample database is setup, is to understand how to setup tSQLt framework to get ready to unit test your database.

一旦您熟悉了常规SQL单元测试的基础知识并设置了示例数据库，接下来的大事就是了解如何设置tSQLt框架以准备对单元数据库进行单元测试。

如何安装tSQLt？ (How tSQLt is installed?)

tSQLt is installed in the form of running its script against the desired database which in turn creates tSQLt objects in the desired database.

tSQLt以针对所需数据库运行其脚本的形式安装，这反过来又在所需数据库中创建了tSQLt对象。

tSQLt下载并解压缩 (tSQLt download and extract)

Download tSQLt from the official website and then extract the downloaded zip folder

从官方网站下载tSQLt ，然后解压缩下载的zip文件夹

运行tSQLt脚本 (Run tSQLt script)

Open tSQLt.class.sql file in SSMS (SQL Server Management Studio) and Run tSQL.class.sql script against the sample database SQLDevArticlesV2:

在SSMS （SQL Server Management Studio）中打开tSQLt.class.sql文件，然后对示例数据库SQLDevArticlesV2运行tSQL.class.sql脚本：

刷新数据库 (Refresh database)

Right Click Databases and then click Refresh and then click Tables (to expand) under SQLDevArticles database to see tSQLt has been installed successfully:

右键单击“ 数据库” ，然后单击“ 刷新” ，然后单击“ SQLDevArticles”数据库下的“ 表” （以展开），以查看tSQLt已成功安装：

Our sample database is ready to be unit tested after the successful installation of tSQLt framework.

成功安装tSQLt框架后，我们的示例数据库已准备好进行单元测试。

创建并运行数据库单元测试 (Creating and running database unit test)

In real world scenarios business requirements drive the database development and testing process.

在现实世界中，业务需求驱动着数据库开发和测试过程。

业务需求 (Business requirement)

Let us assume that you have received the following business requirement:

让我们假设您已收到以下业务要求：

“The end user must be able to add new author to the (database) system”

“最终用户必须能够向（数据库）系统添加新作者”

常规数据库对象开发 (Conventional database object development )

In order to meet the business requirement we must focus on the best suited database object which is capable of meeting the requirement in the best possible way.

为了满足业务需求，我们必须关注能够以最佳方式满足需求的最合适的数据库对象。

创建数据库对象（存储过程） (Creating database object (stored procedure))

A stored procedure called AddAuthor has been chosen to be created to meet the business requirement.

已选择创建一个名为AddAuthor的存储过程来满足业务需求。

Create the stored procedure as follows:

创建存储过程，如下所示：

 -- Creating database object (stored procedure) AddAuthor to add new author to the author table
CREATE PROCEDURE AddAuthor(@Name VARCHAR(40),@RegistrationDate DATETIME2)
  
AS
BEGIN
  
  SET NOCOUNT ON;
  INSERT INTO dbo.Author (Name, RegistrationDate)
  VALUES
  (@Name,@RegistrationDate)
    
END
GO

了解tSQLt中的单元测试架构 (Understanding unit testing architecture in tSQLt )

Before you start creating database unit tests with tSQLt it is important to understand how unit tests architecture works with respect to tSQLt.

在开始使用tSQLt创建数据库单元测试之前，重要的是要了解单元测试架构相对于tSQLt的工作方式。

Database unit testing with tSQLt is primarily based on the following things:

使用tSQLt进行数据库单元测试主要基于以下几点：

1. All the database unit tests are grouped into classes
   所有数据库单元测试都分组为类
2. Each class is represented by creating a database schema
   每个类都通过创建数据库模式来表示
3. Creating a database unit test is same as creating a stored procedure within a test class
   创建数据库单元测试与在测试类中创建存储过程相同
4. A Database unit test follows AAA principle (Arrange, Act and Assert) where expected results are compared with actual results in the end
   数据库单元测试遵循AAA原则（安排，行为和声明），最终将预期结果与实际结果进行比较
5. Running a tSQLt unit test is simply running a SQL stored procedure
   运行tSQLt单元测试只是运行SQL存储过程

空运行所有单元测试 (Dry run all unit tests )

Let us learn the first thing to do once tSQLt is installed successfully and that is to run all the unit tests.

让我们学习成功安装tSQLt之后要做的第一件事，那就是运行所有单元测试。

You can run all the unit tests written in tSQLt by running the following script against the sample database:

您可以通过对示例数据库运行以下脚本来运行所有用tSQLt编写的单元测试：

  -- Test run all the unit tests 
  EXEC tSQLt.RunAll

Since there are no unit tests written yet, so no results to see, however, it confirms that tSQLt framework is readily accepting unit tests.

由于还没有编写任何单元测试，因此也看不到任何结果，这证明tSQLt框架很容易接受单元测试。

创建数据库单元测试类 (Creating database unit test class)

Now that we have created a stored procedure AddAuthor to add new author to the database table, it is time to create unit test to check if the object is working properly or not.

现在，我们已经创建了一个存储过程AddAuthor，以将新作者添加到数据库表中，是时候创建单元测试来检查对象是否正常工作了。

The first thing in this regard is to create database unit test class.

在这方面的第一件事是创建数据库单元测试类。

Create a test class called AuthorTests by creating a schema in the sample database (SQLDevArticlesV2) as follows:

通过在示例数据库（SQLDevArticlesV2）中创建架构，创建一个名为AuthorTests的测试类，如下所示：

CREATE SCHEMA [AuthorTests]
Authorization dbo
GO
EXECUTE sp_addextendedproperty @name='tSQLt.TestClass',@value=1,@level0type='SCHEMA',@level0name='AuthorTests'

创建单元测试以添加作者 (Creating unit test to add author)

Create a unit test to check if AddAuthor stored procedure is working properly or not.

创建一个单元测试以检查AddAuthor存储过程是否正常工作。

Please remember that the stored procedure to be unit tested is responsible to meet business requirement, so its unit test when successful confirms that the object under test works properly and therefore it meets the requirement.

请记住，要进行单元测试的存储过程负责满足业务需求，因此成功进行存储单元测试可以确认被测对象正常工作，因此满足要求。

Creating the database unit test for a procedure which adds new author to the table is done by keeping the following things in mind:

牢记以下几点来完成为向表中添加新作者的过程创建数据库单元测试：

1. Create a blank copy of the table Author which is done by using FakeTable function provided by tSQLt (so that we can ensure that there is no data present before the unit test)

   创建表Author的空白副本，这是使用tSQLt提供的FakeTable函数完成的（以便我们可以确保在单元测试之前不存在任何数据）

2. Create an expected table similar to original Author table and manually insert a record into it

   创建一个类似于原始Author表的预期表，并在其中手动插入一条记录

3. Add the same record by using AddAuthor stored procedure this time which is going to populate Author table

   这次使用AddAuthor存储过程添加相同的记录，这将填充Author表

4. Create an actual table out of Author table which contains data as a result of running AddAuthor procedure

   通过运行AddAuthor过程从Author表中创建一个包含数据的实际表

5. Compare actual table with expected table and if the result is same then the test has passed else troubleshoot the unit test to make it work for you

   将实际表与预期表进行比较，如果结果相同，则测试已通过，否则对单元测试进行故障排除以使其适合您

The code is as follows:

代码如下：

 CREATE PROCEDURE [AuthorTests].[test to check AddAuthor adds author to the table]
AS
-- Assemble
EXEC tSQLt.FakeTable @TableName='dbo.Author',@Identity=1 -- Fake Customer table 
 
Create TABLE [AuthorTests].[Expected] -- Create expected table
(
  [AuthorId] INT NOT NULL,
  [Name] VARCHAR(40) NOT NULL,
  [RegistrationDate] DATETIME2 NOT NULL
 
)
 
INSERT INTO AuthorTests.Expected -- Insert data into exepcted table
(AuthorId,Name,RegistrationDate)
VALUES
(1,'Naveed','01 Jun 2018')
 
-- Act
EXEC dbo.AddAuthor 'Naveed','01 Jun 2018' -- Run AddAuthor procedure which adds new author to Author table
SELECT * INTO AuthorTests.Actual FROM dbo.Author -- Put the records from Author table into Actual table
 
 -- Assert (compare expected table with actual table results)
EXEC tSQLt.AssertEqualsTable @Expected='AuthorTests.Expected',@Actual='AuthorTests.Actual'

运行单元测试 (Running the unit test )

Run the unit test to see the results:

运行单元测试以查看结果：

-- Run all tSQLt database unit tests
  EXEC tSQLt.RunAll

Congratulations! The database unit test has passed so you are good to go.

恭喜你！ 数据库单元测试已通过，因此您一切顺利。

After going through this article you are not only just familiar with conventional SQL unit testing concepts but can also create simple unit tests to check if your database objects are functioning properly.

阅读完本文后，您不仅会熟悉常规SQL单元测试概念，而且还可以创建简单的单元测试来检查数据库对象是否正常运行。

目录 (Table of contents)

tSQLt – A Forgotten Treasure in Database Unit Testing

Conventional SQL Unit Testing with tSQLt in Simple Words

Fundamentals of Test-Driven Database Development (TDDD) with tSQLt unit testing

10 Most Common SQL Unit Testing Mistakes

Why you should cleverly name Database Objects for SQL Unit Testing

Three Standard SQL Unit Tests you can write against any Stored Procedure

Creating SQL Unit Testing Utility Procedures with tSQLt

tSQLt –数据库单元测试中被遗忘的宝藏

简单单词中使用tSQLt进行的常规SQL单元测试

tSQLt单元测试的测试驱动数据库开发（TDDD）基础

10个最常见SQL单元测试错误

为什么要为SQL单元测试巧妙地命名数据库对象

您可以针对任何存储过程编写三个标准SQL单元测试

使用tSQLt创建SQL单元测试实用程序过程

翻译自: https://www.sqlshack.com/conventional-sql-unit-testing-with-tsqlt-in-simple-words/

@sql 单元测试