@sql 单元测试

SQL unit testing is a testing method which allows us to test the smallest, atomic programmable part of a database object. SQL unit testing plays a key role in the modern database development cycle because it allows us to test individual parts of the database objects work as expected. SQL unit testing adds a great worth to the database project because unit tests are more reliable then manual test methods.

SQL单元测试是一种测试方法，它使我们可以测试数据库对象的最小原子可编程部分。 SQL单元测试在现代数据库开发周期中起着关键作用，因为它允许我们测试数据库对象的各个部分是否按预期工作。 SQL单元测试为数据库项目增加了巨大的价值，因为单元测试比手动测试方法更可靠。

I’ll now elaborate more on this idea; SQL unit testing increases confidence to the code because the developer fixes the flaws and defects of the code in initial stages of the development. The key point here is that production bug fixing is costlier than in the development phase. There is no doubt in that customers have little tolerance against errors in the applications therefore we must minimize bugs and malfunctions.

我现在将详细说明这个想法。 SQL单元测试可以提高对代码的信心，因为开发人员可以在开发的初始阶段修复代码的缺陷。 这里的关键点是修复生产错误的成本比开发阶段的成本高。 毫无疑问，客户对应用程序中的错误几乎没有容忍度，因此我们必须最大程度地减少错误和故障。

In terms of the database development, SQL unit testing is our as close to a silver bullet to minimize errors that we have available to us. In this article, you will find some suggestions for writing an effective SQL unit test and also we will make a bit of reverse engineering which means that we will examine a badly written unit test and then we will fix its errors according to the suggestions. In this way, we will clearly understand the differences between a bad and a good SQL unit test.

在数据库开发方面，SQL单元测试是我们的捷径，它可以最大限度地减少我们可以利用的错误。 在本文中，您将找到一些有关编写有效SQL单元测试的建议，并且还将进行一些逆向工程，这意味着我们将检查写得不好的单元测试，然后根据建议修复其错误。 这样，我们将清楚地理解不良和良好SQL单元测试之间的区别。

Note: If you don’t have a piece of basic knowledge about SQL unit testing and tSQLt framework details, see  article.

注意： 如果您不具备有关 SQL单元测试 和 tSQLt框架 详细信息 的基本知识 ，请参阅 。

情境 (Scenario)

You have discovered a scalar-valued function and this function is tested by a tSQLt unit test. The following script belongs to this scalar-valued function and this function’s name is GetProductName and it returns a vegetable/fruit name in a very simple case statement. This scalar-valued function unit test was written by an unknown developer. You decided to fix this SQL unit test design and logical errors.

您已经发现了标量值函数，并且该函数已通过tSQLt单元测试进行了测试。 以下脚本属于该标量值函数，该函数的名称为GetProductName，并且在非常简单的case语句中返回蔬菜/水果的名称。 此标量值函数单元测试由未知的开发人员编写。 您决定修复此SQL单元测试设计和逻辑错误。

At first we will create the scalar-valued function through the following script.

首先，我们将通过以下脚本创建标量值函数。

DROP TABLE IF EXISTS Tbl_TestName CREATE TABLE Tbl_TestName(Id INT , DefName VARCHAR(100))  INSERT INTO Tbl_TestName VALUES (1,'Apple') ,  (2,'Avocado'),  (3 , 'Carrot')GOCREATE OR ALTER FUNCTION GetProductName (@Nm INT)RETURNS VARCHAR(100)BEGINDECLARE @RetVal AS VARCHAR(100) SELECT  @RetVal = CASE @Nm  WHEN 1 THEN (SELECT TOP 1 DefName FROM Tbl_TestName ORDER BY Id DESC)WHEN 2 THEN 'Tomato'WHEN 3 THEN 'Banana'ELSE'Not Found'ENDRETURN  @RetValEND

Down below, we can find a very poorly written example of SQL unit testing which we mentioned in the previous section. In fact, this unit test case can be executed without any error; however, it does not fulfill the best suggestions according to unit test approach. Now, we will discuss details and straighten out defects of this unit test.

在下面，我们可以找到上一节中提到的编写非常糟糕SQL单元测试示例。 实际上，这个单元测试用例可以被执行而没有任何错误。 但是，根据单元测试方法，它不能提供最佳建议。 现在，我们将讨论细节并理清此单元测试的缺陷。

EXEC tSQLt.NewTestClass 'Func_ProductTest'GOCREATE OR ALTER PROCEDURE Func_ProductTest.[Test product function]ASDECLARE @Expected AS VARCHAR(100)='Carrot'DECLARE @Actual AS VARCHAR(100)SELECT @Actual = dbo.GetProductName(1)EXEC tSQLt.AssertEquals @Expected,@ActualSET @Expected='Tomato'SELECT @Actual = dbo.GetProductName(2)EXEC tSQLt.AssertEquals @Expected,@ActualSET @Expected='Banana'SELECT @Actual = dbo.GetProductName(3)EXEC tSQLt.AssertEquals @Expected,@ActualSET @Expected='Not Found'SELECT @Actual = dbo.GetProductName(4)EXEC tSQLt.AssertEquals @Expected,@ActualGOEXEC tSQLt.Run 'Func_ProductTest.[Test product function]'

As you can see in the below image, the SQL unit test worked properly but it includes various problems. Let’s start to fix these issues.

正如您在下图中看到的那样，SQL单元测试工作正常，但是它包含各种问题。 让我们开始解决这些问题。

将SQL单元测试与依赖项隔离 (Isolate the SQL unit tests from dependencies)

The main purpose of SQL unit testing is to test the functional and independently programmable part; however, the tested object may have dependencies on other objects. If we face these circumstances, we must isolate the SQL unit test from other dependencies. The main reason why we do this is to avoid fragility of the unit test. Now, we will apply this idea over the example unit test. In this SQL unit test the expected value is taken from Tbl_TestName, but, this table data might be change so this changing will directly affect the result of the SQL unit test. However, this changing should not influence the GetProductName scalar-valued function unit test case. Hence, we must isolate the Tbl_TestName dependency from our SQL unit test. But how we can achieve that?

SQL单元测试的主要目的是测试功能和独立可编程的部分。 但是，被测对象可能与其他对象有依赖性。 如果遇到这些情况，则必须将SQL单元测试与其他依赖项隔离开。 我们这样做的主要原因是为了避免单元测试的脆弱性。 现在，我们将把这种想法应用到示例单元测试中。 在此SQL单元测试中，期望值取自Tbl_TestName，但是，此表数据可能会更改，因此此更改将直接影响SQL单元测试的结果。 但是，此更改不应影响GetProductName标量值函数单元测试用例。 因此，我们必须隔离Tbl_TestName依赖项 从我们SQL单元测试。 但是我们如何实现呢？

Mocking is the right answer for this issue. Mocking can be described like this; we create a fake object which simulates the behavior of the real object during the unit test so that we can isolate the dependencies of the unit test. tSQLt framework allows us to create mock tables through the tSQLt.FakeTable stored procedure. It takes a table name as a parameter, so, it creates an empty but otherwise duplicate of the original table without constraints. If we look to the working mechanism of the tSQLt.FakeTable stored procedure, it renames the original table name during the test execution and it creates a copy of the original table and test operations use this table.

模拟是解决此问题的正确方法。 模拟可以这样描述： 我们创建一个伪造的对象，该对象在单元测试期间模拟真实对象的行为，以便我们可以隔离单元测试的依赖项。 tSQLt框架允许我们通过tSQLt.FakeTable存储过程创建模拟表。 它以表名作为参数，因此，它会创建一个空的，但没有限制的原始表副本。 如果我们查看tSQLt.FakeTable存储过程的工作机制，它将在测试执行期间重命名原始表名称，并创建原始表的副本，并且测试操作将使用该表。

In our example, we will create a fake Tbl_TestName table and then we will insert new values to this fake table so that we can compare the expected and actual value which returns from the scalar-valued function. Because during the test execution the GetProductName function will retrieve data from the fake table, in this way we can ensure the expected value and also we will prevent external impacts.

在我们的示例中，我们将创建一个伪造的Tbl_TestName表，然后将新值插入到该伪造表中，以便我们可以比较从标量值函数返回的期望值和实际值。 因为在测试执行过程中， GetProductName函数将从假表中检索数据，所以我们可以确保期望的值，并且还可以防止外部影响。

Let’s apply it to our example. As you can see in the below, we will create a fake version of the Tbl_TestName table with help of the tSQLt.FakeTable procedure and we will insert some values to it so that the function always returns max value which we inserted. Also, this insert operation does not affect the original table data.

让我们将其应用于我们的示例。 如下所示，我们将在tSQLt.FakeTable过程的帮助下创建伪造的Tbl_TestName表， 并向其中插入一些值，以便函数始终返回所插入的最大值。 此外，此插入操作不会影响原始表数据。

CREATE OR ALTER PROCEDURE Func_ProductTest.[Test product function]ASDECLARE @Expected AS VARCHAR(100)='Mango'EXEC tSQLt.FakeTable 'Tbl_TestName'INSERT INTO Tbl_TestName VALUES (1,'Watermelon')INSERT INTO Tbl_TestName VALUES (2,'Grape')INSERT INTO Tbl_TestName VALUES (3,'Mango')DECLARE @Actual AS VARCHAR(100)SELECT @Actual = dbo.GetProductName(1) GO EXEC tSQLt.Run 'Func_ProductTest.[Test product function]'

In the Tbl_TestName, we could not find any watermelon, grape and mango record.

在Tbl_TestName中，我们找不到任何西瓜，葡萄和芒果的记录。

SELECT * FROM Tbl_TestName

In this section, we will fix the isolation problem of the sample SQL unit set; however, it includes other flaws. In further sections, we will continue to discuss it and fix these problems.

在本节中，我们将解决示例SQL单元集的隔离问题。 但是，它还包含其他缺陷。 在其他部分，我们将继续讨论并解决这些问题。

避免不良SQL单元测试命名 (Avoid poor SQL unit test naming)

SQL unit test names must be easily understood by being self-describing and implying intent because we must figure out some question answers without a look at the SQL unit test code. In the point of this view; good naming offers a more readable SQL unit test so that it facilitates maintenance. We can create our own naming conventions, or we can find unit test naming conventions. Here, I want to notice about that, tSQLt framework unit tests have to start with “test” prefix if you don’t do this you can’t run your unit tests. Now, we will fix the badly written example name with the following naming convention.

必须通过自我描述和暗示意图来轻松理解SQL单元测试名称，因为我们必须找出一些问题答案，而无需查看SQL单元测试代码。 从这个观点来看； 良好的命名提供了更具可读性SQL单元测试，以便于维护。 我们可以创建自己的命名约定，也可以找到单元测试的命名约定。 在这里，我要注意的是， tSQLt框架单元测试必须以“ test ”前缀开头，如果不这样做，就无法运行单元测试。 现在，我们将使用以下命名约定来修复写得不好的示例名称。

Prefix	object name	test description	expected result
test	GetProductName	casewhen1withfaketable	Exepected_Apple

字首	对象名称	测试说明	预期结果
测试	GetProductName	带假桌子的情况	预期_苹果

CREATE OR ALTER PROCEDURE Func_ProductTest.[test GetProductName  casewhen1withfaketable  Expected_Mango]ASDECLARE @Expected AS VARCHAR(100)='Mango' EXEC tSQLt.FakeTable 'Tbl_TestName'INSERT INTO Tbl_TestName VALUES (1,'Watermelon')INSERT INTO Tbl_TestName VALUES (2,'Grape')INSERT INTO Tbl_TestName VALUES (3,'Mango') DECLARE @Actual AS VARCHAR(100) SELECT @Actual = dbo.GetProductName(1)EXEC tSQLt.AssertEquals @Expected,@Actual GO EXEC tSQLt.Run 'Func_ProductTest.[test GetProductName casewhen1withfaketable  Expected_Mango]'

尽量不要在SQL单元测试中使用多个断言 (As possible as don’t use more than one assert in the SQL unit tests)

Our sample SQL unit test includes more than one assert, but this type of usage is not recommended because when the first test case fails, we don t have any idea following test case results for this reason if it is possible we have not used to multiple asserts in the SQL unit test. Now, we will prove it with a very simple example and then we will fix our example.

我们的示例SQL单元测试包含多个断言，但是不建议使用这种类型的用法，因为当第一个测试用例失败时，由于这个原因，如果有可能我们不习惯于多次使用，我们就没有任何想法。在SQL单元测试中声明。 现在，我们将通过一个非常简单的示例对其进行证明，然后我们将修复该示例。

EXEC tSQLt.NewTestClass 'Multiple_Assert'GOCREATE OR ALTER PROCEDURE Multiple_Assert.[test multiple assert test]ASDECLARE  @Expected AS INT = 1DECLARE  @Actual   AS INT = 2EXEC tSQLt.AssertEquals @expected ,@actual DECLARE  @ExpectedTest2 AS VARCHAR(10) = 'A'DECLARE  @ActualTest2   AS VARCHAR(10) = 'Z'EXEC tSQLt.AssertEquals @ExpectedTest2 ,@ActualTest2 GO  EXEC tSQLt.run 'Multiple_Assert.[test multiple assert test]'

As you can see in the image, we can’t see any information about second test case. For this reason, we need to separate multiple asserts to single ones. At first, we will apply naming conventions to our SQL unit test and then we will create separated unit tests.

正如您在图像中看到的，我们看不到有关第二个测试用例的任何信息。 因此，我们需要将多个断言分离为单个断言。 首先，我们将命名约定应用于SQL单元测试，然后创建分离的单元测试。

Prefix	object name	test description	expected result
test	GetProductName	casewhen1withfaketable	Expected_Mango
test	GetProductName	casewhen2	Expected_Tomato
test	GetProductName	casewhen3withfaketable	Expected_Banana
test	GetProductName	casewhen4elsewithfaketable	Expected_NotFound

字首	对象名称	测试说明	预期结果
测试	GetProductName	带假桌子的情况	Expected_Mango
测试	GetProductName	casewhen2	Expected_Tomato
测试	GetProductName	带假表的情况	Expected_香蕉
测试	GetProductName	附带假表的情况	Expected_NotFound

EXEC tsqlt.NewTestClass 'Func_ProductTest'GOCREATE OR ALTER PROCEDURE Func_ProductTest.[test GetProductName casewhen1withfaketable  Expected_Mango]ASDECLARE @Expected AS VARCHAR(100)='Mango'DECLARE @Actual AS VARCHAR(100)EXEC tSQLt.FakeTable 'Tbl_TestName'INSERT INTO Tbl_TestName VALUES (1,'Watermelon')INSERT INTO Tbl_TestName VALUES (2,'Grape')INSERT INTO Tbl_TestName VALUES (3,'Mango')SELECT @Actual = dbo.GetProductName(1)EXEC tSQLt.AssertEquals @Expected,@ActualGO CREATE OR ALTER PROCEDURE Func_ProductTest.[test GetProductName casewhen2 Expected_Tomato]ASDECLARE @Expected AS VARCHAR(100)='Tomato'DECLARE @Actual AS VARCHAR(100)SELECT @Actual = dbo.GetProductName(2)EXEC tSQLt.AssertEquals @Expected,@ActualGO CREATE OR ALTER PROCEDURE Func_ProductTest.[test GetProductName casewhen3 Expected_Banana]ASDECLARE @Expected AS VARCHAR(100)='Banana'DECLARE @Actual AS VARCHAR(100)SELECT @Actual = dbo.GetProductName(3)EXEC tSQLt.AssertEquals @Expected,@ActualGOCREATE OR ALTER PROCEDURE Func_ProductTest.[test GetProductName casewhenelse Expected_NotFound]ASDECLARE @Expected AS VARCHAR(100)='Not Found'DECLARE @Actual AS VARCHAR(100)SELECT @Actual = dbo.GetProductName(4)EXEC tSQLt.AssertEquals @Expected,@ActualGO EXEC tSQLt.Run 'Func_ProductTest'

As you can see, we created four SQL unit test because GetProductName function will return four different results according to given parameter, so that we can test all behavior of the GetProductName scalar-valued function. In addition, I want to discuss one point, the tSQLt framework allows us to create individual test class so that we can gather similar SQL unit tests in the same test class. This approach takes advantage of running this unit test classes separately.

如您所见，我们创建了四个SQL单元测试，因为GetProductName函数将根据给定的参数返回四个不同的结果，以便我们可以测试GetProductName标量值函数的所有行为。 另外，我想讨论一点，tSQLt框架允许我们创建单独的测试类，以便我们可以在同一测试类中收集相似SQL单元测试。 这种方法利用了单独运行此单元测试类的优势。

Another benefit of using single asserts in a SQL unit test is that it allows us to develop a more readable, simple and understandable SQL unit test.

在SQL单元测试中使用单个断言的另一个好处是，它使我们能够开发出更具可读性，简单性和可理解性SQL单元测试。

Finally, we fixed various issue of a badly written SQL unit test. A well-written SQL unit test can provide the following advantages;

最后，我们解决了编写错误SQL单元测试的各种问题。 编写良好SQL单元测试可以提供以下优点；

• It is readable, understandable and simple
  可读，易懂，简单
• It is robust and does not affect from dependencies
  它很健壮，不受依赖关系的影响
• It is manageable and easily redesigned
  它易于管理且易于重新设计

结论 (Conclusion)

In this article, we discussed and learned some suggestions concerning SQL unit testing and applied these suggestions to a badly written unit test and then we fixed it. Now, let us again highlighted three main suggestions;

在本文中，我们讨论并学习了一些有关SQL单元测试的建议，并将这些建议应用于写得不好的单元测试中，然后对其进行了修复。 现在，让我们再次强调三个主要建议：

• Isolate SQL unit tests from dependencies
  将SQL单元测试与依赖项隔离
• Consider proper SQL unit test names
  考虑适当SQL单元测试名称
• Avoid multiple asserts
  避免多个断言

目录 (Table of contents)

SQL unit testing best practices

SQL单元测试最佳实践

翻译自:

@sql 单元测试