[SQL Server] T-SQL 測試輔助工具或方案 (3) : tSQLt + SSDT

-- ================================================
-- 刪除相關的 stored procedure , view, table
-- ================================================
DROP PROCEDURE IF EXISTS [dbo].[usp_AlarmInterest];
GO
DROP PROCEDURE IF EXISTS [dbo].[usp_CalculateInterest];
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
-- ================================================
-- 建立 Stored Procedure
-- ================================================
-- -----------------------
-- usp_CalculateInterest
-- -----------------------
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
-- -----------------------
-- [usp_AlarmInterest]
-- -----------------------
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

3.. 在 Visual Studio 建立第 1 個資料庫專案 (MyDemo), 並匯入 SQL Server 資料庫

(1) 建立空白方案: tSQLt_Demo

(2) 加入資料庫專案: MyDemo

(3) 匯入資料庫: 把剛才建立的 tSQLt_Demo 資料庫, 匯入到 MyDemo 專案

4.. 在 Visual Studio 建立第 2 個資料庫專案 (MyDemo_Tests), 用以存放 tSQLt 相關程式及測試程式碼 (Test Code)

(1) 加入另外一個資料庫專案 (MyDemo_Tests)

(2) 加入資料庫參考: MyDemo 專案, 資料庫位置: 相同資料庫

(3) 加入資料庫參考: master, 資料庫位置: 不同資料庫, 相同伺服器, 資料庫名稱: sys

--> 這個動作, 主要是因為 tSQLt 參考了系統資料表; 若不參考, 在建置資料庫專案時, 會發生錯誤

(4) 利用 SSMS 安裝 tSQLt (in tSQLt Database)

(5) 在 Visual Studio 進行資料庫結構比對 (來源: tSQLt_Demo 資料庫, 目標: MyDemo_Tests)

只保留 tSQLt 的部份, 但看來 SSDT 蠻聰明的, 會濾掉當初在 MyDemo 專案裡匯入的物件.

(6) 加入預先部署指令碼 (Pre-Deployment Script) : Script.PreDeployment.Setup_tSQLt.sql (內容來自 tSQLt 的 SetClrEnabled.sql)

EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;
GO

(7) 加入部署後指令碼 (Post-Deployment Script) : Script.PostDeployment.Run_tSQLt_Tests.sql

EXEC tSQLt.RunAll
GO

5.. 加入測試程式碼 (Test Code)

(1) 加入 TestClass : (這個動作, 建議在 SSMS 進行, 再同步到 SSDT)

其實就是加一個 Schema, 讓測試案例歸屬在該 Schema 下; 當然, 如果有多個 stored procedure, 要作分類, 也可以加入多個 TestClass

EXEC tSQLt.NewTestClass 'BankTest';
GO

在 SSMS 執行上述程式碼

同步至 SSDT

(2) 加入第1個測試式段 (in SSMS), 並同步至 SSDT

會這麼作的原因, 是為了在 MyDemo_Tests 資料庫專案, 能夠自動建立剛才加入的 BankTest 這個 schema 對應的資料夾

[[ 建立測試案例_OK ]]

CREATE PROCEDURE [BankTest].[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

(2) 加入第2個測試式段 (in SSDT)

[BankTest].[test_usp_CalculateInterest_本金-10000_利率0.10_借365天_應回傳錯誤代碼100_訊息本金輸入有誤]

Tips: stored procedure name, 要輸入全名, 且用 [ ] 包圍; 不然 SSDT 會很貼心地自動幫您作切割 ...

有支援 intellisense

-- 輸入負值的本金, 實務上會作檢核, 利用 @po_retcode, @po_retmsg 回傳; 但因為 Product Code 沒有回傳預期的 @po_retmsg, 所以會造成測試失敗
CREATE PROCEDURE [BankTest].[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

6.. 部署及測試

為簡化起見, 我們假設已開發完成, 只是在撰寫測試程式, 故先忽略 MyDemo 專案的部署. 只針對 MyDemo_Tests 進行部署.

(1) 選取 MyDemo_Tests 專案作發行 (Publish)

(2) 設定發行選項, [產生指令碼], 看一下內容

以下僅是擷取部份重要內容, 可以發現先前 PreDepolyment 及 PostDeployment 的 script, 都在執行之列.

另外, 還記得嗎? 剛剛第2個測試案例, 還沒有同步到 tSQLt_Demo 資料庫, 所以在這個發佈的指令碼會出現建立該測試方法的程式段.

/*
tSQLt_Demo 的部署指令碼
這段程式碼由工具產生。
變更這個檔案可能導致不正確的行為，而且如果重新產生程式碼，
變更將會遺失。
*/
/*
......
*/


/*
 預先部署指令碼樣板                                            
--------------------------------------------------------------------------------------
 此檔案包含要在組建指令碼之前執行的 SQL 陳述式     
 使用 SQLCMD 語法可將檔案包含在預先部署指令碼中                 
 範例:      :r .\myfile.sql                                                 
 使用 SQLCMD 語法可參考預先部署指令碼中的變數            
 範例:      :setvar TableName MyTable                                              
               SELECT * FROM [$(TableName)]                                
--------------------------------------------------------------------------------------
*/
EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;
GO
--DECLARE @cmd NVARCHAR(MAX);
--SET @cmd='ALTER DATABASE ' + QUOTENAME(DB_NAME()) + ' SET TRUSTWORTHY ON;';
--EXEC(@cmd);
--GO
GO
GO
PRINT N'正在建立 [BankTest].[test_usp_CalculateInterest_本金-10000_利率0.10_借365天_應回傳錯誤代碼100_訊息本金輸入有誤]...';
GO
-- 輸入負值的本金, 實務上會作檢核, 利用 @po_retcode, @po_retmsg 回傳; 但因為 Product Code 沒有回傳預期的 @po_retmsg, 所以會造成測試失敗
CREATE PROCEDURE [BankTest].[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
/*
部署後指令碼樣板                                               
--------------------------------------------------------------------------------------
 此檔案包含要附加到組建指令碼的 SQL 陳述式         
 使用 SQLCMD 語法可將檔案包含在部署後指令碼中                   
 範例:      :r .\myfile.sql                                                 
 使用 SQLCMD 語法可參考部署後指令碼中的變數       
 範例:      :setvar TableName MyTable                                              
               SELECT * FROM [$(TableName)]                                
--------------------------------------------------------------------------------------
*/
EXEC tSQLt.RunAll
GO
GO
PRINT N'更新完成。';
GO

當您按下左上角的 [執行] (黃框), 可以看到整個測試的結果. 以本文的測試案例而言, 會有 1 個成功, 1 個失敗.