前言
最近工作上, 可能會需要寫 stored procedure, 想說找一下有沒有測試輔助工具或方案, 以提昇 stored procedure 的品質, 避免後續維護時, 出現改東壞西的狀況.
筆者以免費的工具或解決方案為優先, 找到的第1個方案是 tSQLt. 至於要付費的, 如參考文件 5.., 筆者還沒有時間去試用.
tSQLt 是一套 Open Source 的 Unit Test Framework; 可以協助 SQL Server 開發人員, 直接在 SQL Server 上進行測試.
所謂測試, 是指實際執行值, 與預期值是否相符的驗證. 基本上分為功能性 (單元測試, 整合測試, 使用者測試) 與非功能 (效能測試, 壓力測試).
以下僅就單元測試與整合測試, 用易於瞭解的描述作說明. 至於嚴謹的定義, 可以參考 91哥的系列文章: [30天快速上手TDD]目錄與附錄. 筆者在測試的部份, 深受 91哥 "自動測試與 TDD 實務開發(使用C#)" 課程的影響.
單元測試: 只針對單一方法作測試 (Procedure A), 若測試的對象 (Procedure A), 會呼叫另一個方法 (Procedure B), 則必須要將被呼叫的方法 (Procedure B) 的相依性作隔離, 以求專注於 Procedure A 的邏輯.
整合測試: 但光是單元測試就夠了嗎? 整合起來會不會有問題呢? 故需將測試對象 (Procedure A) 連同其相依的方法 (Procedure B), 也一併納入測試範圍. 整合測試的粒度(範圍) 比較大, 要準備的測試資料也比較多.
似乎很難理解上述單元測試裡的離相依性? 我們來看一個例子, 以下是 pseudo code. Procedure A 有自己的處理邏輯 A.1 及 A.2; 其中 A.2 會依 呼叫 Procedure B 的結果, 而有不同的行為表現.
Procedure A
begin
code block A.1
execute Procedure B
code block A.2
end
Procedure B
begin
code block B
end
如果我們要測試 Procedure B, 因為沒有呼叫其它方法, 所以很單純, 只要直接寫測試程式就好.
如果我們要測試 Procedure A, 但 Procedure B 因為某些因素, 還沒有完成; 那我們要怎麼作測試呢?
所以最好隔離相依性:
(1) 把相依方法 (Procedure B) 可能的回傳值狀況列示出來, 每一個回傳值, 都可作為一個測試案例. 可參考以下 (進階版) 的範例.
(2) 或者它沒有回傳值 (例如: 寫 log), 我們可驗證傳入的參數是否正確, 或是否被呼叫到. 可參考官網 Tutorial 的 Example 4
即使後來 Procedure B 完成了, 我們仍然可以執行上述撰寫的測試程式碼.
以 C# 而言, 可以利用重構 (相依於界面 + DI + IOC), 或利用現成的 mocking framework (RhinoMocks, NSubstistute), 模擬被呼叫方法的行為. (參考文件 4..)
但 T-SQL 本身不是物件導向的程式, 沒有界面; 那要怎麼作呢? 以 tSQLt 而言, 可以利用 FakeTable, 也可以 SpyProcedure, FakeFunction, 模擬相依對象的行為, 讓關注點集中在要測試的對象 (view, stored procedure, function ...),
以下茲分為 7 個段落作說明:
3.. 實作過程 (初階版): 測試對象, 完全沒有相依於其它方法.
4.. 實作過程 (中階版): 為了要測試 View 的內容, 但因為 View 相依於 Table, 所以利用 FakeTable 的方式, 製造測試資料; 但不用自己寫程式 clean up 測試資料, 因為測試完成, 會 Rollback.
5.. 實作過程 (進階版): 為了要測試 Procedure AlarmInterest(), 但因為會呼叫 Procedure CalculateInterest(), 所以利用 SpyProcedure 的方式, 模擬 CalculateInterest() 的回傳值.
1.. 環境設置
(2) SQL Server 伺服器層級, 因為 tSQLt 內含 .NET Assembly, 請打開 SQLCLR
(3) SQL Server 資料庫層級, 要信任 tSQLt 的 .NET Assembly
EXEC sp_configure 'clr enabled', 1; RECONFIGURE;
DECLARE @cmd NVARCHAR(MAX); SET @cmd='ALTER DATABASE ' + QUOTENAME(DB_NAME()) + ' SET TRUSTWORTHY ON;'; EXEC(@cmd);
(4) SQL Server 資料庫層級, 安裝 tSQLt
執行解壓縮後的 tSQLt.class.sql
2.. 如何偵錯
依一般 T-SQL 的方式去偵錯即可, 經筆者實測, tSQLt 的 tSQLt.Private_RunTest 會呼叫您的測試方法 ( EXEC (@Cmd), 再逐步偵錯即可. 如下圖.
您可以留意一下, 它在這個 tSQLt.Private_RunTest() 裡有作了 BEGIN TRAN + SAVE TRAN @TranName; 也有對應的 ROLLBACK TRAN @TranName; 或 ROLLBACK, 亦即任何測試方法執行到最後, 都會被 ROLLBACK, 這樣就不用再費心去清測試資枓了.
3.. 實作過程 (初階版)
(1) 建立 Production Code
DROP PROCEDURE IF EXISTS [dbo].[usp_CalculateInterest];
GO
CREATE PROCEDURE [dbo].[usp_CalculateInterest]
@pi_principal INT, -- 本金
@pi_rate DECIMAL(3,2), -- 年利率
@pi_days INT, -- 天數
@po_interest INT OUTPUT, -- 回傳的利息
@po_retcode INT OUTPUT,
@po_retmsg NVARCHAR(4000) OUTPUT
AS
BEGIN
DECLARE @days_of_year int = 365
SET NOCOUNT ON;
BEGIN TRY
-- 設定回傳變數的初值
SELECT @po_retcode = 0, @po_retmsg = N'';
-- 檢查傳入參數
IF @pi_principal <= 0
BEGIN
SELECT @po_retcode = 100; -- 這裡故意註解掉 @po_retmsg 的部份, 以展示測試失敗的案例
-- SELECT @po_retmsg = '本金輸入有誤';
END
-- 處理邏輯
-- 利息 = 本金 * 利率 * 天數 / 365 , 無條件捨去至整數
SELECT @po_interest = CEILING(@pi_principal * @pi_rate * @pi_days / @days_of_year)
END TRY
BEGIN CATCH
THROW
END CATCH
END
GO
(2) 建立測試的 class (其實就是一個 schema name, 其下可以建立多個測試案例)
EXEC tSQLt.NewTestClass 'Bank'; GO
(3) 建立測試案例_OK
DROP PROCEDURE [Bank].[test_usp_CalculateInterest_本金10000_利率0.10_借365天_應回傳利息1000]
GO
CREATE PROCEDURE [Bank].[test_usp_CalculateInterest_本金10000_利率0.10_借365天_應回傳利息1000]
AS
BEGIN
DECLARE @actual INT;
DECLARE @li_principal INT = 10000
, @ld_rate DECIMAL(3,2) = 0.10
, @li_days INT = 365
, @li_retcode INT = 0
, @ls_retmsg NVARCHAR(4000) = N''
;
EXEC [dbo].[usp_CalculateInterest] @li_principal, @ld_rate, @li_days,
@actual OUTPUT, @li_retcode OUTPUT, @ls_retmsg OUTPUT;
DECLARE @expected INT = 1000;
EXEC tSQLt.AssertEquals @expected, @actual;
END
GO
(4) 建立測試案例_NG
DROP PROCEDURE [Bank].[test_usp_CalculateInterest_本金-10000_利率0.10_借365天_應回傳錯誤代碼100_訊息本金輸入有誤]
GO
-- 輸入負值的本金, 實務上會作檢核, 利用 @po_retcode, @po_retmsg 回傳; 但因為 Product Code 沒有回傳預期的 @po_retmsg, 所以會造成測試失敗
CREATE PROCEDURE [Bank].[test_usp_CalculateInterest_本金-10000_利率0.10_借365天_應回傳錯誤代碼100_訊息本金輸入有誤]
AS
BEGIN
DECLARE @actual INT;
DECLARE @li_principal INT = -10000
, @ld_rate DECIMAL(3,2) = 0.10
, @li_days INT = 365
, @li_retcode INT = 0
, @ls_retmsg NVARCHAR(4000) = N''
;
EXEC [dbo].[usp_CalculateInterest] @li_principal, @ld_rate, @li_days,
@actual OUTPUT, @li_retcode OUTPUT, @ls_retmsg OUTPUT;
DECLARE @expected_retcode INT = 100;
DECLARE @expected_retmsg NVARCHAR(4000) = N'本金輸入有誤';
EXEC tSQLt.AssertEquals @expected_retcode, @li_retcode;
EXEC tSQLt.AssertEqualsString @expected_retmsg, @ls_retmsg;
END
GO
(5) 執行測試
EXEC tSQLt.RunTestClass N'Bank'; GO
(6) 測試結果
4.. 實作過程 (中階版)
利用 FakeTable 的功能, 可以避免將資料直接新增到真正的 table; 依官網的說明, 主要是因為真實的 table, 常常會有 Check / Foreign ... 等 Constraints, 造成建立測試資料有些不便, 所以, tSQLt 提供了 FakeTable 的功能, 建立一個與原來同名, 但完全沒有 Constraint 的 table, 如下 A.. 的實測說明.
以下的範例, 係以 vwEmployees 及 Employees 作展示.
因為 vwEmployees 相依於 Employees, 為了除除相依性, 所以對 Employees 作 Fake; 經實測,
A.. 它會把原來的 table 作 rename, 再建一個空的 Employees table.
B.. 在整個結束後, 它會作 Rollback, 而回復原狀
C.. 注意: FakeTable 有以下限制: 如果建立 View 時, 有設 WITH SCHEMABINDING, 則無法 Fake 其使用到的 base table. 因為 SCHEMABINDINGG, 除非把 CREATE VIEW 的 script 找出來, 再用 ALTER VIEW 的方式 (此時不再設定 WITH SCHEMABINDING), 測試完成, 記得要設回去 @@
以下是整個 Sample Code
-- 刪除相關的 stored procedure , view, table
DROP PROCEDURE IF EXISTS [Bank].[test_vwEmployeesCount]
GO
DROP VIEW IF EXISTS [dbo].[vwEmployees]
GO
DROP TABLE IF EXISTS [dbo].[Employees]
GO
-- 建立 table
CREATE TABLE [dbo].[Employees]
( [Id] INT IDENTITY (1,1) NOT NULL
, [Name] NVARCHAR(20) NOT NULL
, [City] NVARCHAR(20) NOT NULL
, [Salary] INT
CONSTRAINT PK_Employees PRIMARY KEY ( [Id] )
);
GO
-- 建立 View
CREATE VIEW [dbo].[vwEmployees]
AS
SELECT *
FROM [dbo].[Employees]
WHERE [City] = '台北市'
GO
-- 建立初始資料
INSERT INTO [dbo].[Employees]
VALUES ( 'jasper', '台北市', 30000 )
, ( 'jeff', '台北市', 35000 )
, ( 'joseph', '台中市', 40000)
;
GO
-- 建立測試案例
CREATE PROCEDURE [Bank].[test_vwEmployeesCount]
AS
BEGIN
DECLARE @expected INT = 1 ;
DECLARE @actual INT = 0;
------Fake Table, 自動編號起值為 1
EXEC tSQLt.FakeTable '[dbo].[Employees]', @Identity = 1;
INSERT INTO [dbo].[Employees] ( [Name], [City], [Salary])
VALUES ( 'fish', '台北市', 32500 );
------Execution
SELECT @actual = Count(1) FROM [dbo].[vwEmployees];
------Assertion
EXEC tSQLt.assertEquals @expected, @actual;
END;
GO
-- 執行測試
exec tSQLt.Run N'[dbo].[test_vwEmployeesCount]';
GO
以下是測試結果
5.. 實作過程 (進階版)
關於 SpyProcedure 的應用, 有以下 2 種情境:
A.. 想要設定相依方法的回傳值, 以驗證測試對象的行為 (如本例)
B.. 想要驗證測試對象與相依方法的互動, 例如: 有沒有被呼叫, 呼叫時傳入的參數, 是否有被相依方法收到. (可參考官網 Tutorial 的 Example 4)
以下的 usp_AlarmInterest 會呼叫 usp_CalculateInterest, 假設 usp_CalculateInterest 內容尚未完成, 但至少已訂好輸入出參數, 但要對 usp_AlarmInterest 這個呼叫端測試其邏輯是否正確; 可以採用 SpyProcedure 的方式, 逐一設定多個回傳的參數值.
(1) 建立 Production Code
DROP PROCEDURE IF EXISTS [dbo].[usp_AlarmInterest];
GO
CREATE PROCEDURE [dbo].[usp_AlarmInterest]
@pi_principal INT, -- 本金
@pi_rate DECIMAL(3,2), -- 年利率
@pi_days INT, -- 天數
@po_interest INT OUTPUT, -- 回傳的利息
@po_retcode INT OUTPUT,
@po_retmsg NVARCHAR(4000) OUTPUT
AS
BEGIN
DECLARE @days_of_year INT = 365
SET NOCOUNT ON;
BEGIN TRY
-- 設定回傳變數的初值
SELECT @po_retcode = 0, @po_retmsg = N'';
-- 檢查傳入參數
IF @pi_principal <= 0
BEGIN
SELECT @po_retcode = 100; -- 這裡故意註解掉 @po_retmsg 的部份, 以展示測試失敗的案例
SELECT @po_retmsg = '本金輸入有誤';
END
-- 呼叫 usp_CalculateInterest, 取回利息
EXEC [dbo].[usp_CalculateInterest] @pi_principal, @pi_rate, @pi_days,
@po_interest OUTPUT, @po_retcode OUTPUT, @po_retmsg OUTPUT;
-- 依利息進行處理
SELECT @po_retmsg =
CASE
WHEN @po_interest >= 10000 THEN N'利息大於或等於10000'
WHEN @po_interest >= 5000 AND @po_interest <10000 THEN N'利息介於 5000 至 9999'
WHEN @po_interest >= 0 AND @po_interest < 5000 THEN N'利息介於 0 至 4999'
END
PRINT '*** IN [usp_AlarmInterest] interest=' + CAST(@po_interest AS VARCHAR) +
' retcode=' + CAST(@po_retcode AS VARCHAR) +
' retmsg=' + @po_retmsg;
END TRY
BEGIN CATCH
THROW
END CATCH
END
GO
(2) 建立測試的 class (其實就是一個 schema name, 其下可以建立多個測試案例) //如果您是由本文最開始閱讀, 且進行實作, 因為在
EXEC tSQLt.NewTestClass 'Bank'; GO
(3) 建立測試案例_OK (Test Code)
DROP PROCEDURE IF EXISTS [Bank].[test_usp_AlarmInterest_應回傳_利息介於_0 至_4999];
GO
CREATE PROCEDURE [Bank].[test_usp_AlarmInterest_應回傳_利息介於_0 至_4999]
AS
BEGIN
DECLARE @actual NVARCHAR(4000) = N'';
DECLARE @li_principal INT = 100000
, @ld_rate DECIMAL(3,2) = 0.10
, @li_days INT = 365
, @li_interest INT = 0
, @li_retcode INT = 0
, @ls_retmsg NVARCHAR(4000) = N''
;
-- 不管進入 usp_CalculateInterest 的參數是什麼, 其回傳的值永遠都被固定: @po_interest 為 2000, @po_retcode 為 0
EXEC tSQLt.SpyProcedure '[dbo].[usp_CalculateInterest]', 'SET @po_interest=2000; SET @po_retcode=0';
EXEC [dbo].[usp_AlarmInterest] @li_principal, @ld_rate, @li_days,
@li_interest OUTPUT, @li_retcode OUTPUT, @actual OUTPUT;
PRINT '*** IN [test_usp_AlarmInterest_應回傳_利息介於_0 至_4999] interest=' + CAST(@li_interest AS VARCHAR) +
' retmsg=' + @actual;
DECLARE @expected NVARCHAR(4000) = N'利息介於 0 至 4999';
EXEC tSQLt.AssertEqualsString @expected, @actual;
END;
GO
(4) 執行測試
EXEC tSQLt.Run N'[Bank].[test_usp_AlarmInterest_應回傳_利息介於_0 至_4999]'; GO
(5) 測試結果
6.. 結論
1.. 適用情境:
(1) 該工具同時適合 單元測試 與 整合測試
(2) 適合純 T-SQL 的開發人員
(3) 與 SQL Server 完全整合, 不需要額外的工具 (例如: Visual Studio); DBA 只要安裝設定完成, T-SQL 開發人員, 就能夠撰寫及執行測試.
2.. 限制:
(1) 文字模式顯示測試結果, 可讀性比不上整合至 Visual Studio 裡的測試工具. 當然, 也可以搭配其它付費的測試工具, 看廠商的截圖, 是有提供 GUI 的界面, 只是筆者還沒有試過.
(2) 若除了 T-SQL 之外, 專案裡尚有其它的程式語言, 例如: C#, 則無法進行統一的測試案例管理.
(3) 官網的 User Guide 有缺 SalesApp (in FakeFunction) 的 Production Code; 但有找到 Tutorial 的 Production Code and Test Code ( http://downloads.tsqlt.org/tSQLt_demo.zip ); 有興趣的朋友, 可以試一下 Tutorial 的範例.
7.. 參考文件
==> 整個 TDD 系列的文章
==> 該篇是用 Rhino Mocks, 也可以用其的, 例如: NSubstitute
5.. 付費的 T-SQL 單元測試軟體:
6.. DISORDERLY DATA, "AN INTRODUCTION TO DATABASE UNIT TESTING WITH TSQLT"
7.. DISORDERLY DATA, "SETTING UP SSDT DATABASE PROJECTS AND TSQLT"
7.. DISORDERLY DATA, "SETTING UP SSDT DATABASE PROJECTS AND TSQLT"
==> 這 2 篇文章是後來 (2017.12.11) 才看到的, 第1篇對 tSQLt 作了簡要的介紹, 並說明了一下單元測試的 3A 原則 (Arrange, Act, Assert); 第2篇主要對 SSDT 與 tSQLt 如何搭配進行說明.
沒有留言:
張貼留言