如何全方位地給 SQL Server 做測(cè)試

點(diǎn)擊藍(lán)色“有關(guān)SQL”關(guān)注我喲

我在知乎上有個(gè)付費(fèi)問(wèn)答專欄，69元一次。雖然設(shè)置了付費(fèi)，卻經(jīng)常收到咨詢，詢問(wèn)數(shù)據(jù)庫(kù)行業(yè)入門與求職技巧問(wèn)題?；卮饚状沃?，發(fā)現(xiàn)一個(gè)共性，大部分朋友對(duì)數(shù)據(jù)庫(kù)非常感興趣，但苦于項(xiàng)目經(jīng)驗(yàn)缺少，遲遲找不到好工作。

那么數(shù)據(jù)庫(kù)經(jīng)驗(yàn)從哪里來(lái)呢？最重要的是，真正的實(shí)戰(zhàn)經(jīng)驗(yàn)，而非三流培訓(xùn)機(jī)構(gòu)的口水項(xiàng)目。有的培訓(xùn)機(jī)構(gòu)還是相當(dāng)奧里給，有專職的一對(duì)一老師，輔導(dǎo)員。配備的項(xiàng)目，也是從從企業(yè)真實(shí)項(xiàng)目濃縮出來(lái)的。

真正能給初學(xué)者練手的項(xiàng)目非常少，如果你得到了，請(qǐng)珍惜。如果確實(shí)沒(méi)有經(jīng)驗(yàn)，也別灰心，曲徑通幽，辦法總是有的，比如你可以從數(shù)據(jù)庫(kù)測(cè)試做起。

數(shù)據(jù)庫(kù)測(cè)試，給外人看起來(lái)非常簡(jiǎn)單，點(diǎn)點(diǎn)界面，偶爾手工Mockup （模擬），然后比對(duì)下數(shù)據(jù)。但真實(shí)的情況，可能遠(yuǎn)比你想象的復(fù)雜。

簡(jiǎn)單羅列下，數(shù)據(jù)庫(kù)測(cè)試有這些：

看到這，估計(jì)很多朋友都會(huì)頭皮發(fā)麻，這可不比寫 SQL 輕松啊。是的，開(kāi)發(fā)不需要去知道這些，所以他們成為了開(kāi)發(fā)，而不需要對(duì)數(shù)據(jù)庫(kù)容量，壓力和性能，高可用負(fù)責(zé)，而這些恰恰都是企業(yè)要考慮的真實(shí)需求。

如果建庫(kù)不考慮容量,執(zhí)行 create database 的時(shí)候，你準(zhǔn)備給它分多少硬盤容量，準(zhǔn)備采購(gòu)幾塊硬盤？數(shù)據(jù)庫(kù)幾經(jīng)周折，終于能用了，但現(xiàn)有硬件配置，參數(shù)配置，能抗每秒多少并發(fā)不知道，那系統(tǒng)正式上線了，會(huì)不會(huì)崩？

這些都需要測(cè)試，當(dāng)一個(gè)測(cè)試員并不比開(kāi)發(fā)輕松。

而且測(cè)試進(jìn)階階段，除了像開(kāi)發(fā)一樣要寫測(cè)試代碼，還有一套測(cè)試體系等著你去創(chuàng)建，用它去衡量乙方供應(yīng)商的軟件。比如這些：

那么有同學(xué)會(huì)納悶了，看這些內(nèi)容比開(kāi)發(fā)還難，越發(fā)沒(méi)信心學(xué)好數(shù)據(jù)庫(kù)了。別急，下面通過(guò)實(shí)戰(zhàn)演示，如何快速地在 SQL Server 中進(jìn)行全方位的測(cè)試。

在演示之前，我先把演示用到的軟件羅列下，以方便大家跟著練：

• windows server 2016

• sql server 2019

• SQLToolbeltEssentials

三款軟件都需要收費(fèi)，好在他們都有一段時(shí)間的免費(fèi)期，各自的官網(wǎng)都提供下載。

而這里，最重要的一款軟件就是 SQLToolbeltEssentials, redgate 出品，贊！

除了測(cè)試套件之外，還有很多其他優(yōu)秀的功能，比如對(duì)比數(shù)據(jù)集差異，對(duì)比 schema 差異等。這些對(duì)于 ETL Girl/Boy 來(lái)肯定非常高效。

那么我們說(shuō)說(shuō)測(cè)試步驟：

• 建庫(kù)

• 建表

• 建sp (即 Stored Procedure:存儲(chǔ)過(guò)程)

• 建測(cè)試用例

• 運(yùn)行測(cè)試

• 解讀測(cè)試報(bào)告

建庫(kù)-建表-建sp 代碼如下：

CREATE?TABLE?[dbo].[workflow](
????[flowid]?[INT]?NULL,
????[flowamount]?[INT]?NULL,
????[flowcount]?[INT]?NULL
)?ON?[PRIMARY]
GO



WITH?L0
AS?(SELECT?*
????FROM
????(
????????VALUES
????????????(1),
????????????(2),
????????????(3)
????)?AS?T?(C)?),
?????L1
AS?(SELECT?a.C,
???????????b.C?AS?BC
????FROM?L0?AS?a
????????CROSS?JOIN?L0?AS?b),
?????L2
AS?(SELECT?a.C,
???????????b.C?AS?BC
????FROM?L1?AS?a
????????CROSS?JOIN?L1?AS?b),
?????L3
AS?(SELECT?a.C,
???????????b.C?AS?BC
????FROM?L2?AS?a
????????CROSS?JOIN?L2?AS?b),
?????L4
AS?(SELECT?a.C,
???????????b.C?AS?BC
????FROM?L3?AS?a
????????CROSS?JOIN?L3?AS?b),
?????L5
AS?(SELECT?a.C,
???????????b.C?AS?BC
????FROM?L4?AS?a
????????CROSS?JOIN?L4?AS?b)
INSERT?INTO?dbo.workflow
(
????flowid,
????flowamount,
????flowcount
)
SELECT?TOP?50000
???????RNK,
???????RNK?*?10,
???????RNK?+?20
FROM
(SELECT?ROW_NUMBER()?OVER?(ORDER?BY?(SELECT?NULL))?AS?RNK?FROM?L5)?M;

GO?



CREATE?PROCEDURE?
????dbo.workflowAddUpdate?
????@flowid?int?,?
????@flowamount?int?,?
????@flowcount?int?=?1??
AS?
BEGIN?

????IF?EXISTS(SELECT?TOP?1?1?FROM?dbo.workflow?WITH(NOLOCK)?WHERE?flowid?=?@flowid?)
????BEGIN?
????????UPDATE?workflow?SET?flowamount?=?@flowamount?,flowcount=?@flowcount?
????????WHERE?flowid?=?@flowid??
????END?
????ELSE?
????BEGIN?
????????INSERT?INTO?dbo.workflow
????????(
????????????flowid,
????????????flowamount,
????????????flowcount
????????)
????????VALUES
????????(???@flowid,?--?flowid?-?int
????????????@flowamount,?--?flowamount?-?int
????????????@flowcount?--?flowcount?-?int
????????????)
????END?

END?

GO?

測(cè)試部分就值得書寫下：

當(dāng)我們安裝完畢 SQLToolbeltEssentials，在工具欄會(huì)看到這些按鈕：

今天的主角是 SQL Test. 它引用的測(cè)試框架是 tSQLt，開(kāi)源。

點(diǎn)開(kāi) SQL Test 選擇需要測(cè)試的數(shù)據(jù)庫(kù) factory, tSQLt 會(huì)自動(dòng)安裝一些表，存儲(chǔ)過(guò)程.所以不建議在生產(chǎn)環(huán)境安裝 SQLToolbeltEssentials.

隨著 tSQLt 安裝的是 SQLCop, 它包括了一系列對(duì)數(shù)據(jù)庫(kù)屬性做的測(cè)試，這大概是寶藏所在了。它會(huì)測(cè)試數(shù)據(jù)庫(kù)配置的合理性，性能參數(shù)配置的正確性，還有數(shù)據(jù)庫(kù)服務(wù)的安全設(shè)置。初學(xué)者可以學(xué)到很多有意思的內(nèi)核硬知識(shí)。

因 SQLCop 并不執(zhí)行運(yùn)行用戶代碼，所以不影響測(cè)試覆蓋率。

這里重要的是測(cè)試覆蓋率，底下有講

此時(shí)，數(shù)據(jù)庫(kù)中僅有一張表和一個(gè)存儲(chǔ)過(guò)程，我們要給該存儲(chǔ)過(guò)程寫測(cè)試用例，測(cè)試程序是不是正常運(yùn)行。因此需要在 factory 下面增加一個(gè)測(cè)試組 ?factorytest：

測(cè)試名稱( Test Name)需要命名得有一定意義，比如 factory_workflow_insert ，表示在表 workflow 上測(cè)試 insert 操作:

框架幫我們自動(dòng)生成了測(cè)試用存儲(chǔ)過(guò)程，[factorytest].[test factory_workflow_insert]:

我們唯一要做的就是判斷什么時(shí)候，這個(gè)測(cè)試用例會(huì)失敗：

USE?[factory]
GO
SET?ANSI_NULLS?ON
GO
SET?QUOTED_IDENTIFIER?ON
GO
--??Comments?here?are?associated?with?the?test.
--??For?test?case?examples,?see:?http://tsqlt.org/user-guide/tsqlt-tutorial/
ALTER?PROCEDURE?[factorytest].[test?factory_workflow_insert]
AS

BEGIN
??--Assemble
??--??This?section?is?for?code?that?sets?up?the?environment.?It?often
??--??contains?calls?to?methods?such?as?tSQLt.FakeTable?and?tSQLt.SpyProcedure
??--??along?with?INSERTs?of?relevant?data.
??--??For?more?information,?see?http://tsqlt.org/user-guide/isolating-dependencies/

??DECLARE?@flowid?INT?=?1,
????????@flowamount?INT?=?100?


??--Act
??--??Execute?the?code?under?test?like?a?stored?procedure,?function?or?view
??--??and?capture?the?results?in?variables?or?tables.
??EXEC?dbo.workflowAddUpdate?
????@flowid?=?@flowid,?
????@flowamount?=?@flowamount?


??--Assert
??--??Compare?the?expected?and?actual?values,?or?call?tSQLt.Fail?in?an?IF?statement.??
??--??Available?Asserts:?tSQLt.AssertEquals,?tSQLt.AssertEqualsString,?tSQLt.AssertEqualsTable
??--??For?a?complete?list,?see:?http://tsqlt.org/user-guide/assertions/
??IF?NOT?EXISTS(SELECT?TOP?1?1?FROM?dbo.workflow?WITH(NOLOCK)?WHERE?flowid?=?@flowid)
??BEGIN?????
????EXEC?tSQLt.Fail?'TODO:Implement?this?test.'
??END?

END;

這個(gè)時(shí)候，有了測(cè)試用例，就可以開(kāi)始跑測(cè)試了，點(diǎn)下 Run Tests, factorytest 和 SQLCop 會(huì)跑起來(lái)：

如果寫的測(cè)試用例沒(méi)有語(yǔ)法問(wèn)題，factorytest 下面寫好的測(cè)試用例都會(huì)成功跑完，而 SQLCop 會(huì)幫我們把配置錯(cuò)誤或不合理的地方抓出來(lái)。SQLCop 涉及到 DBA 的范疇，暫且不談，先說(shuō)測(cè)試覆蓋率。

這就是測(cè)試覆蓋率報(bào)告。dbo.workflowAddUpdate 就是要測(cè)試的存儲(chǔ)過(guò)程，可以看到有 3 個(gè)測(cè)試入口，而我們的測(cè)試用例（參數(shù)）只測(cè)試到了 2 個(gè)入口，測(cè)試覆蓋率就是66.67. 剩下的一個(gè)入口沒(méi)有測(cè)到，那么說(shuō)不到哪天，新的參數(shù)傳進(jìn)來(lái)了，會(huì)出現(xiàn) bug.

SQL Test 強(qiáng)悍的地方在這里，我們編寫的測(cè)試用例，測(cè)到了哪條分支，會(huì)幫我們標(biāo)綠：

從示意圖馬上得到這么個(gè)消息，還有 insert 沒(méi)有測(cè)完。因?yàn)橹俺跏蓟瘮?shù)據(jù)的時(shí)候，flowid = 1 已經(jīng)插入了，所以第一個(gè)測(cè)試用例，只是走了第一個(gè)分支，第二個(gè)分支沒(méi)有走。為此，我們還要再建一個(gè)測(cè)試用例，讓它走完另一個(gè)分支。

USE?[factory]
GO
SET?ANSI_NULLS?ON
GO
SET?QUOTED_IDENTIFIER?ON
GO
--??Comments?here?are?associated?with?the?test.
--??For?test?case?examples,?see:?http://tsqlt.org/user-guide/tsqlt-tutorial/
ALTER?PROCEDURE?[factorytest].[test?factory_workflow_update]
AS
BEGIN
??DECLARE?@flowid?INT?=?999999,
????????@flowamount?INT?=?100?,?
????????@flowcount?INT?=?10?;


??--Act
??--??Execute?the?code?under?test?like?a?stored?procedure,?function?or?view
??--??and?capture?the?results?in?variables?or?tables.
??EXEC?dbo.workflowAddUpdate?
????@flowid?=?@flowid,?
????@flowamount?=?@flowamount?,?
????@flowcount?=?@flowcount


??--Assert
??--??Compare?the?expected?and?actual?values,?or?call?tSQLt.Fail?in?an?IF?statement.??
??--??Available?Asserts:?tSQLt.AssertEquals,?tSQLt.AssertEqualsString,?tSQLt.AssertEqualsTable
??--??For?a?complete?list,?see:?http://tsqlt.org/user-guide/assertions/
??IF?NOT?EXISTS(SELECT?TOP?1?1?FROM?dbo.workflow?WITH(NOLOCK)?WHERE?flowid?=?@flowid)
??BEGIN?????
????EXEC?tSQLt.Fail?'TODO:Implement?this?test.'
??END?
END;

再重新運(yùn)行下測(cè)試，看下報(bào)告，測(cè)試覆蓋率就是 100% 了，剩余的代碼也都標(biāo)綠：