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, 測試設(shè)置
2) execution of the target procedure, and 執(zhí)行測試目標(biāo)存儲過程.
3) checking results.檢查結(jié)果.
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:
在單元測試的設(shè)置中我經(jīng)常檢查其他人沒有破壞我希望的數(shù)據(jù):
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塊檢查期望的記錄.如果無法找到,測試失敗并生成一個錯誤消息.測試框架移動到下一個單元測試.你不用在失敗的消息中使用測試的名字,因?yàn)閠sqlunit會在測試失敗時命名的.
now i call the stored procedure i'm about to write:
現(xiàn)在我調(diào)用我要寫的存儲過程:
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:
你看,我將檢查存儲過程中需要返回的參數(shù).在檢查結(jié)果中,要確認(rèn)輸出參數(shù)真的被填充了.
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.
我可以檢查更深層次的值,如果我要求的新記錄被創(chuàng)建了.
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要運(yùn)行的一個單元測試.如果你已經(jīng)使用ut_前綴作其他的用途,tsqlunit要求你修改為其他的名字.名字的第二個段落,顯示了測試的組,例如,你可以加入一個單元測試名字為ut_mytable_deleterec.這個組會測試添加和刪除一個記錄到mytable,組也可以分開到其他的測試組中.名字的第三個段落,是測試的唯一標(biāo)示.
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負(fù)責(zé)為你處理
running the unit test
運(yùn)行單元測試
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:
為了運(yùn)行列表1中的單元測試,你要設(shè)置測試框架.在查詢分析器中,運(yùn)行tsqlunit.sql在你的開發(fā)數(shù)據(jù)庫中..你僅僅要執(zhí)行一次在數(shù)據(jù)庫中.創(chuàng)建ut_mytable_newrec,現(xiàn)在你可以簡單的執(zhí)行單元測試了:
-- this will run all tests for suite mytable, 這將運(yùn)行mytable組的所有測試
exec tsu_runtests mytable
fixtures 設(shè)備
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.
在一個單元測試組中,我要求很多記錄有效,我不想每次都寫同樣的設(shè)置代碼,tsqlunit使用了一個setup的標(biāo)識設(shè)備:
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的設(shè)備:命名為setup會讓tsqlunit認(rèn)為這是個啟動的設(shè)備.代碼如下:
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社區(qū)極大的感激henrik ekelund和他的職員讓tsqlunnit開放源碼.
link to http://sourceforge.net/projects/tsqlunit
link to http://tsqlunit.sourceforge.net/tsqlunit_cookbook.htm (documentation)
