65.9K
CodeProject 正在变化。 阅读更多。
Home

为 SQL 中的图书构建基本测试数据生成器

Haroon Ashraf
starIconstarIconstarIconstarIcon
emptyStarIcon
starIcon

4.77/5 (9投票s)

2017年10月21日

CPOL

10分钟阅读

viewsIcon

22313

downloadIcon

510

本文帮助初学者在 SQL 中为像书籍这样的数据库对象生成超过一百万条随机测试数据。

引言

在本文中，我将引导您完成在 SQL 中为像书籍这样的数据库对象创建测试数据的步骤。

本文的目的是让您对为感兴趣的对象生成测试数据以进行开发测试或解决问题有一个基本的了解。

本文的另一个目标是提供一个关于测试数据生成器如何工作的高级（基本）视图。

背景

如果您刚刚开始您的 SQL 之旅并渴望入门，那么本文非常适合您的需求。

如果您对 SQL 有基本的了解，本文可以帮助您掌握一个非常基本的测试数据生成器，并为进一步探索提供了可能性。

我们经常遇到需要生成测试数据的情况。有许多方法可以生成测试数据，包括使用第三方工具和预编写的测试脚本。

我们将进一步探讨本文，就像一个真实世界的场景一样，您会遇到一个问题，并且必须尽快提出解决方案。

问题

一个书店解决方案将书籍信息保存在一个名为 `Book` 的数据库对象（`表`）中。工作人员（系统的最终用户）将属于不同类别的书籍信息添加到 `Book` 表中。

该表还包含书籍的价格和库存数量以及用于唯一标识书籍的 `Id`。书籍的标题也保存在同一个表中，如下所示：

上面的表现在是测试对象，测试团队需要它被填充到一定程度，次数不限，以便他们可以对表进行测试。

使用系统填充表是一个耗时的过程，因为它要求测试人员执行以下操作：

  1. 登录系统
  2. 选择“添加新书”选项
  3. 填写相关字段的表单
  4. 保存表单
  5. 查看表单

由于这不是一个 UI 测试（测试人员也会测试图形界面，在这种情况下必须通过上述方式或自动化方式完成），所以表如何填充并不重要。

他们还希望书名与它们的类别相关联，而价格和库存可以有任何随机值。

解决方案

现在，假设您刚刚开始您的 SQL 职业生涯（但有扎实的 SQL 背景），那么主动权就在您手中。让我们非常仔细地选择解决方案的第一步。

初步分析

需求有点模糊，因此值得进行初步分析。

经过一些初步分析和信息收集后，需求现在细化如下：

  1. （有一点可以肯定）需要一个 SQL 存储过程来填充 `Book` 表，包含以下字段：
    1. `BookId` (自动生成)
    2. 标题
    3. 类别
    4. 价格
    5. 库存
  2. 书籍的 `Title` 应该与 `Category` 相关联，这意味着如果有一本名为“`SQL Adventures`”的书，它应该属于“`Development`”类别，而不是“`Adventure`”，依此类推。

既然已经清楚地了解了具体需求，为什么不直接跳到编码部分呢？我不想通过遵循一整套标准流程来分散初学者的注意力，而是希望专注于编码逻辑及其实现以获得解决方案。

开发（代码和逻辑）

创建数据结构（Book表）

为了满足要求，我们需要做的第一件事是在 SQL 中创建所需的表，如下所示：

-- Create book table
CREATE TABLE Book
(BookId INT PRIMARY KEY IDENTITY(1,1),
Title VARCHAR(100),
Category VARCHAR(30),
Price DECIMAL(10,2),
Stock INT);
GO

现在测试运行该表，它将没有数据，如下所示：

-- Check the Book table structure
SELECT BookId, Title, Category, Price, Stock FROM Book

用测试数据填充数据结构（Book表）

下一步是用测试数据填充 `Book` 表，这些数据应该是随机的，但标题需要与类别关联。

为了填充 `表`，我们需要将问题分为两部分：

  1. 价格和库存列必须包含数字（`INT` 和 `DECIMAL`）
  2. 标题和类别列必须包含字符串（`VARCHAR`）

生成库存的测试数据

现在，通过使用 `RAND()` 函数可以生成范围从 1 到 10 的随机库存值，该函数生成随机数，如下所示：

-- Generating Test Data for Stock in the range 1-10
SELECT CONVERT(INT,RAND()*10+1) as Stock

生成价格的测试数据

现在我们需要创建 `Price` 测试数据，范围更广，介于 1 到 50 之间，这可以通过更改上面的代码行，将 10 替换为 50 来实现，如下所示：

-- Generating Test Data for Price in the range 1-50
SELECT CONVERT(INT,RAND()*50+1) as Price

合并库存和价格的测试数据脚本

合并两个脚本如下：

-- Generating Test Data for Stock from 1-10 and Price from 1-50

SELECT (SELECT CONVERT(INT,RAND()*10+1)) as [Stock 1-10],
(SELECT CONVERT(INT,RAND()*50+1)) AS [Price 1-50]

运行上述脚本两次，分别用于 `Stock` 和 `Price`，结果如下：

值得一提的是，即使我们排除了内部的 `SELECT` 关键字，这些脚本仍然会起作用，但为了方便快速测试，我将它们保留原样。

推导生成数字测试数据的逻辑

现在是推导生成数字测试数据通用公式的好时机，无论是用于书籍的 `Stock` 数量或 `Price`，还是任何其他用数字填充的列。

如果 `SELECT CONVERT(INT,RAND()*10+1)` 能让我们得到 1 到 10 的随机数，

那么它可以概括如下：

SELECT CONVERT(INT,RAND()*MAX+1)

同样，为了生成 1-100 的数字作为测试数据，牢记上述公式，我们可以简单地在 SQL 中编写以下代码片段：

-- Generating number from 1 to 100
SELECT CONVERT(INT,RAND()*100+1)

生成类别的测试数据（使用 CHOOSE）

现在产生一个问题：“我们能否使用上述公式来生成类别的测试数据？”答案是“能”。

如果我们可以将类别名称转换为数字，那么我们就可以使用数字的测试数据生成器。

让我们首先快速定义一些书籍类别：

  1. 冒险
  2. 科学
  3. 开发

现在，假设每个类别都有一个代码，在上面的示例中，`1` 代表 `Adventure`，`2` 代表 `Science`，`3` 代表 `Development`。

我们可以很容易地使用通用公式来选择 1 到 3 之间的任何数字，如下所示：

-- Generating code from 1 to 3
SELECT CONVERT(INT,RAND()*3+1)

接下来，根据随机选择的值，我们必须将其与一个类别关联起来，这可以通过使用 `CHOOSE()` 函数来实现。该函数返回与传递给它的数字关联的项，如下所示：

-- Passing 1 in CHOOSE get us First Choice
-- Passing 2 in CHOOSE get us Second Choice
SELECT CHOOSE(2,'First Choice','Second Choice')

同样，在我们的例子中，我们将使用 `CHOOSE` 函数提供从 3 个数字中随机选择的数字，然后根据该数字，`CHOOSE` 将返回在该位置存在的 `Category`，如下所示：

-- Declare variable to hold randomly selected category out of 3
DECLARE @CategoryCode INT=(SELECT RAND()*3+1),@Category VARCHAR(30)
 
-- Initialise variable with random category
SET @Category=(SELECT CHOOSE(@CategoryCode,'Adventure','Science','Development'))

-- Check chosen category
SELECT @CategoryCode as [CategoryCode 1-3],@Category as [Category 1-3]

生成与类别关联的标题测试数据（使用 CASE）

这部分有点棘手。我们必须有一些预定义的标题与类别关联。

例如，如果我说书“`a`”属于“`Adventure`”类别，书“`b`”属于“`Science`”类别，那么每当我们为“`Adventure`”类别提供测试数据时，它应该是“`a`”，而不是“`b`”。

这可以通过使用 `CASE` 语句实现，如下所示：

DECLARE @Category VARCHAR(40)='Adventure' -- Set Adventure Category
DECLARE @I INT =1 -- Helping Number

SELECT CASE @Category -- Check Category
WHEN 'Adventure' THEN CONCAT('An Adventure City ',@I) -- Create Adventure Book Title
WHEN 'Science' THEN CONCAT('The Amazing World of Science Part-',@I) -- Create Science Book Title
WHEN 'Development' _
   THEN CONCAT('The Art and Science of SQL Chapter-',@I) -- Create Development Book Title
END

值得注意的是，有一个辅助数字用于创建尽可能多的书籍，但没有两本书同名。例如，`Adventure` 类别中的第一本书是“`An Adventure City 1`”，下一本书可能是“`An Adventure City 10`”，依此类推。

最终脚本 - 基本图书测试数据生成器

最后，请参见下面正在运行的完整测试数据生成器脚本（请参阅下载源代码部分以获取最新版本的代码）

-- (1) Create book table with Title
CREATE TABLE Book
(BookId INT PRIMARY KEY IDENTITY(1,1),
Title VARCHAR(100),
Category VARCHAR(30),
Price DECIMAL(10,2),
Stock INT);
GO

-- (2) Declare Counter (to repeat steps to generate test data), Category Code, Category and Title
DECLARE @Counter INT=1
DECLARE @CatogeryCode INT
DECLARE @Category VARCHAR(30)
DECLARE @Title VARCHAR(100)

WHILE @Counter<30
BEGIN
-- (3) Initialize CategoryCode variable with randomly selected category out of 3
SET @CatogeryCode =(SELECT RAND()*3+1)
 
-- (4) Initialize Category based on above generated CategoryCode
SET @Category=(SELECT CHOOSE(@CatogeryCode,'Adventure','Science','Development'))

-- (5) Initialize Title of the Book based on above generated Category
SET @Title=(SELECT CASE @Category
WHEN 'Adventure' THEN CONCAT('An Adventure City ',@Counter)
WHEN 'Science' THEN CONCAT('The Amazing World of Science Part-',@Counter)
WHEN 'Development' THEN CONCAT('The Art and Science of SQL Chapter-',@Counter)
END)
 
-- (5) Add data to the table Book
INSERT INTO dbo.Book
        ( Title,Category, Price, Stock )
VALUES
(@Title -- Randomly selected Title based on selected category
,@Category, -- Randomly selected category
(SELECT CONVERT(INT,RAND()*50+1)),(SELECT CONVERT(INT,RAND()*10+1))) -- Randomly selected price and stock

SET @Counter+=1

END
 
-- (6) View table
SELECT B.BookId ,
    B.Title,
       B.Category ,
       B.Price ,
       B.Stock FROM dbo.Book B
 
-- (7) Cleanup script (drop table)
DROP TABLE dbo.Book

输出如下

第二次运行脚本会给我们不同的结果

正如所说，脚本的两次输出都不会相同，因此随机测试数据生成器脚本现在已准备就绪。

最终将脚本转换为存储过程

将脚本转换为存储过程后，将对测试团队非常方便，因为他们只需调用该过程即可填充 `Book` 表。

现在让我们快速关注我们需要将脚本转换为过程的事项。

为了充分发挥脚本的潜力（考虑到需求），我们需要做以下事情：

  1. 一个用于演示目的的数据库（`BooksDemo`）需要创建
  2. 数据库中需要创建一个表（`Book`）
  3. 一个用于创建图书测试数据的存储过程
  4. 调用存储过程后，会填充 `Book` 表作为结果。
  5. 存储过程应该能够接受要创建的测试数据记录总数。

现在，在存储过程中，需要创建一个与 `book` 表相同的临时表。诀窍是简单地在名称前添加 `#` 即可完成。

-- Table Book 
CREATE TABLE Book
(BookId INT PRIMARY KEY IDENTITY(1,1),
Title VARCHAR(100),
Category VARCHAR(30),
Price DECIMAL(10,2),
Stock INT)

-- Temporary Table Book
CREATE TABLE #Book
(BookId INT PRIMARY KEY IDENTITY(1,1),
Title VARCHAR(100),
Category VARCHAR(30),
Price DECIMAL(10,2),
Stock INT)

现在，存储过程中的临时表首先被填充，并将其结果返回给外部的 `Book` 表，此外还有一些需要在过程中进行的调整（例如禁用标识插入以填充 ID 字段）。

-- (3) Generate Test Data for Books and put it into table Book
INSERT INTO Book
(
BookId,
Title,
Category ,
Price ,
Stock)
EXEC GenerateBooksTestData @Total

以上代码仅用于演示目的，除非创建了被调用的过程和表，否则不会运行。

请参阅下载源代码部分以获取上述代码的可运行版本。

生成超过一百万条记录

一旦根据脚本创建了存储过程，下一步就是测试运行该过程以生成相对较大的数据集。

通过将 `@Total` 变量初始化为所需值，存储过程在 1 分 14 秒内（在开发机器上）成功生成了超过一百万条记录，如下所示：

简而言之，仅仅将脚本转换为存储过程，你就让你的解决方案更加灵活可靠，它也能够生成超过一百万条测试数据记录。

关注点

如果您有兴趣生成大型数据集，请在存储过程中初始化 @Total 变量（请参阅下载代码部分），并用一个很大的数字来查看您可以走多远。

该脚本每次运行时都会生成不同的结果集，这使得单元测试变得有些困难。

另一个巨大的挑战将是为此类生成随机数据的存储过程创建单元测试，因为我们不确定输出究竟会是什么，所以预期值不能轻易地与实际值进行比较，而这是单元测试的要求之一。

使用此脚本的另一种有趣方式是考虑填充两个不同的图书表，然后比较它们以进行比较分析测试。

值得进一步改进随机数生成器公式，使其能够生成任何范围的数字，而不仅仅是从 1 开始。

上述讨论的任何想法都可能成为基于本文的另一篇文章的基础。

历史

  • 2017-10-21 版本 1.0 完成
  • 2017-10-22 版本 1.1 改进了源代码和文章
  • 2017-11-02 版本 1.2 脚本转换为存储过程（源代码和文章更新）
  • 2017-11-09 版本 1.3 生成超过一百万条记录并修复了一些错别字（文章更新）
  • 2017-11-13 版本 1.4 随机数的最小值必须是 1（文章和源代码更新）
  • 2017-11-14 版本 1.5 微小修复（确保随机价格在 1 到 50 之间）并添加了另一个兴趣点（文章更新）
© . All rights reserved.