使用 tSQLt 測試資料庫

      在〈使用 tSQLt 測試資料庫〉中留言功能已關閉

使用 LeetCode 595. Big Countries 進行練習 :

There is a table World

+-----------------+------------+------------+--------------+---------------+
| name            | continent  | area       | population   | gdp           |
+-----------------+------------+------------+--------------+---------------+
| Afghanistan     | Asia       | 652230     | 25500100     | 20343000      |
| Albania         | Europe     | 28748      | 2831741      | 12960000      |
| Algeria         | Africa     | 2381741    | 37100000     | 188681000     |
| Andorra         | Europe     | 468        | 78115        | 3712000       |
| Angola          | Africa     | 1246700    | 20609294     | 100990000     |
+-----------------+------------+------------+--------------+---------------+

A country is big if it has an area of bigger than 3 million square km or a population of more than 25 million.

Write a SQL solution to output big countries’ name, population and area.

For example, according to the above table, we should output:

+--------------+-------------+--------------+
| name         | population  | area         |
+--------------+-------------+--------------+
| Afghanistan  | 25500100    | 652230       |
| Algeria      | 37100000    | 2381741      |
+--------------+-------------+--------------+

 

1. 下載 tSQLt

2. 開啟SSMS，執行啟用 SQL CLR :

EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;

3. 啟動 TRUSTWORTHY

DECLARE @cmd NVARCHAR(MAX);
SET @cmd='ALTER DATABASE ' + QUOTENAME(DB_NAME()) + ' SET TRUSTWORTHY ON;';
EXEC(@cmd);

 

4. 到 LeetCode595 資料庫裡面，執行 tSQLt.class.sql

執行之後，資料庫會產生許多 tSQLt 開頭的資料表跟 Stored Procedure

5. 建立資料庫 LeetCode 595 及資料表 World :

CREATE TABLE [dbo].[World](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[name] [nvarchar](50) NOT NULL,
	[continent] [nvarchar](50) NULL,
	[area] [bigint] NULL,
	[population] [bigint] NULL,
	[gdp] [bigint] NULL,
 CONSTRAINT [PK_World] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

6. 建立測試 Class :

EXEC tSQLt.NewTestClass 'BigCountries';
GO

 

7. 建立測試 :

CREATE PROCEDURE BigCountries.[test Big Countries - LeetCode]
AS
BEGIN
    IF OBJECT_ID('actual') IS NOT NULL DROP TABLE actual;
    IF OBJECT_ID('expected') IS NOT NULL DROP TABLE expected;


------Fake Table
--+-----------------+------------+------------+--------------+---------------+
--| name            | continent  | area       | population   | gdp           |
--+-----------------+------------+------------+--------------+---------------+
--| Afghanistan     | Asia       | 652230     | 25500100     | 20343000      |
--| Albania         | Europe     | 28748      | 2831741      | 12960000      |
--| Algeria         | Africa     | 2381741    | 37100000     | 188681000     |
--| Andorra         | Europe     | 468        | 78115        | 3712000       |
--| Angola          | Africa     | 1246700    | 20609294     | 100990000     |
--+-----------------+------------+------------+--------------+---------------+

    EXEC tSQLt.FakeTable  'World';

    INSERT INTO World (name, continent, area, population,gdp) VALUES ('Afghanistan', 'Asia',652230,25500100,20343000);
	INSERT INTO World (name, continent, area, population,gdp) VALUES ('Albania', 'Europe',28748,2831741,12960000);
	INSERT INTO World (name, continent, area, population,gdp) VALUES ('Algeria', 'Africa',2381741,37100000,188681000);
	INSERT INTO World (name, continent, area, population,gdp) VALUES ('Andorra', 'Europe',468,78115,3712000);
	INSERT INTO World (name, continent, area, population,gdp) VALUES ('Angola', 'Africa',1246700,20609294,100990000);

------Execution
	Select name,population,area 
      INTO actual
      from World Where area > 3000000 OR population > 25000000

------Assertion
--+--------------+-------------+--------------+
--| name         | population  | area         |
--+--------------+-------------+--------------+
--| Afghanistan  | 25500100    | 652230       |
--| Algeria      | 37100000    | 2381741      |
--+--------------+-------------+--------------+

    CREATE TABLE expected (
	    name nvarchar(50),
	    population bigint,
	    area bigint,
    );

	INSERT INTO expected (name, population, area) values('Afghanistan',25500100,652230)
	INSERT INTO expected (name, population, area) values('Algeria',37100000,2381741)

	EXEC tSQLt.AssertEqualsTable 'expected', 'actual';
END;
GO

 

8. 執行測試 :

EXEC tSQLt.RunAll

 

 

進到 SP 裡面修改一下值，確定測試真的有在運作 :

 

 

參考 :

[SQL]使用tSQLt進行資料庫單元測試 | 攻城獅跳火圈 – 點部落

tSQLt上的教程

print