Introducing TSQLUnit
TSQLUnit简介
TSQLUnit is an open source unit testing framework for T-SQL written by Henrik Ekelund and available from http://sourceforge.net/projects/tsqlunit. Here's an example of how I've used it.
My TSQLUnit tests take a similar pattern of three parts:
1) unit test setup, 测试设置
2) execution of the target procedure, and 执行测试目标存储过程.
3) checking results.检查结果.
In the unit test setup, I often check to make sure someone hasn't done bad things to my data when I wasn't looking:
在单元测试的设置中我经常检查其他人没有破坏我希望的数据:
DECLARE @nId INT, @nNewId INT —- @nNewId is for later
SELECT @nId = [ID] FROM MyTable WHERE MyField = 'whatever'
IF @nId IS NULL -- or @@ROWCOUNT = 0
EXEC tsu_failure 'The data has changed. ''whatever'' couldn''t be found'
The IF block checks for the expected record. If it couldn't be found, the test fails and will generate an error message. The test framework moves on to the next unit test. You don't need to use the name of the unit test in the failure message string, because TSQLUnit will name it for you when the test fails.
IF块检查期望的记录.如果无法找到,测试失败并生成一个错误消息.测试框架移动到下一个单元测试.你不用在失败的消息中使用测试的名字,因为TSQLUnit会在测试失败时命名的.
Now I call the stored procedure I'm about to write:
现在我调用我要写的存储过程:
EXEC CreateMyTableNewRec @nId, @nNewId OUTPUT
As you can see, I've determined that I need an output parameter from this new procedure. In checking the results, I make sure the output parameter really is filled with something:
你看,我将检查存储过程中需要返回的参数.在检查结果中,要确认输出参数真的被填充了.
IF @nNewId IS NULL
EXEC tsu_failure 'A new record was not created for table MyTable.'
I could further check the value to see if the new record was created in the way I wanted it to be created.
我可以检查更深层次的值,如果我要求的新记录被创建了.
Each TSQLUnit test is itself a stored procedure. Listing 1 shows what one looks like when all of the pieces are put together:
每个TSQLUnit测试都是一个存储过程.列表1显示了所有测试段落在一起的情况.
Listing 1. A complete unit test for T-SQL.
CREATE PROCEDURE ut_MyTable_NewRec
AS
--== Setup ==--
DECLARE @nID INT, @nNewId INT
SELECT @nId = ID FROM MyTable
WHERE MyField = 'whatever'
IF @nId IS NULL -- or @@ROWCOUNT = 0
EXEC tsu_failure 'The data has changed.
''Whatever'' couldn''t be found'
--== Execute ==--
EXEC CreateMyTableNewRec @nId, @nNewId OUTPUT
--== Check ==--
IF @nNewId IS NULL
EXEC tsu_failure 'A new record was not created
for table MyTable.'
GO
Note the three-part name of the stored procedure, ut_MyTable_NewRec. The prefix "ut_" alerts TSQLUnit that this is a unit test it should run. If you already use this prefix ut_ for other purposes, TSQLUnit lets you set it to something else. "MyTable" is the name of a group of related unit tests, known as a suite of tests. For instance, you could add another unit test called ut_MyTable_DeleteRec. The MyTable suite would test both adding and deleting a record to MyTable. The suite can be run separately from other test suites. The third part of the name–"NewRec" or "DeleteRec"–uniquely identifies this unit test.
三个段落的存储过程,前缀ut_是告诉TSQLUnit要运行的一个单元测试.如果你已经使用ut_前缀作其他的用途,TSQLUnit要求你修改为其他的名字.名字的第二个段落,显示了测试的组,例如,你可以加入一个单元测试名字为ut_MyTable_DeleteRec.这个组会测试添加和删除一个记录到MyTable,组也可以分开到其他的测试组中.名字的第三个段落,是测试的唯一标示.
Note that you don't need BEGIN TRAN and ROLLBACK in each unit test; TSQLUnit takes care of this for you.
你也不在需要BEGIN TRAN和ROLLBACK在每个单元测试.TSQLUnit负责为你处理
Running the unit test
运行单元测试
In order to run the unit test in Listing 1, you need to set up the framework. From Query Analyzer, run tsqlunit.sql on your development database. You need do this only once for the database. Next, create procedure ut_MyTable_NewRec, if you haven't already. Now you're set. Simply execute the unit test:
为了运行列表1中的单元测试,你要设置测试框架.在查询分析器中,运行TSQLUnit.SQL在你的开发数据库中..你仅仅要执行一次在数据库中.创建ut_myTable_newRec,现在你可以简单的执行单元测试了:
-- This will run all tests for suite MyTable, 这将运行MyTable组的所有测试
EXEC tsu_RunTests MyTable
Fixtures 设备
Suppose I want numerous records to be available for all the unit tests of a suite. I don't want to write the same setup code for each test. TSQLUnit solves the problem with a setup fixture. The code in the fixture will be run before each unit test.
在一个单元测试组中,我要求很多记录有效,我不想每次都写同样的设置代码,TSQLUnit使用了一个SetUp的标识设备:
For instance, the setup fixture for the previous MyTable suite would be named ut_MyTable_setup. The third part of the name "setup" alerts TSQLUnit to treat the procedure as a setup fixture for the suite. It will look something like this:
例如,前缀为MyTable的组可以使用ut_MyTable_Setup的设备:命名为Setup会让TSQLUnit认为这是个启动的设备.代码如下:
CREATE PROCEDURE ut_MyTable_setup
AS
INSERT INTO MyTable ([Description])
VALUES ('something')
--( more records inserted here
GO
The SQL Server community owes a huge debt of gratitude to Henrik Ekelund and his employer for making TSQLUnit open source.
SQLServer社区极大的感激Henrik Ekelund和他的职员让TSQLUNnit开放源码.
Link to http://sourceforge.net/projects/tsqlunit
Link to http://tsqlunit.sourceforge.net/tsqlunit_cookbook.htm (documentation)
