sql重命名数据库
This article is focussed on clever database object naming from both development and SQL unit testing point of view.
本文从开发和SQL单元测试的角度着眼于巧妙的数据库对象命名。
This article also highlights the importance of naming database objects going through different development transitions including the SQL unit testing phase due to the agile nature of requirements.
本文还强调了由于需求的敏捷性，命名经过不同开发过渡（包括SQL单元测试阶段）的数据库对象的重要性。
The purpose is to understand the long term positive effect of clever naming of your database object right from the beginning and most importantly during the SQL unit testing when you are up against not so fixed business requirements.
目的是从一开始就了解聪明地命名数据库对象的长期积极作用，最重要的是，在SQL单元测试期间，当您遇到不太固定的业务需求时。
关于命名数据库对象 (About Naming database objects)
Let us first talk about naming database objects in general.
首先让我们首先讨论一下命名数据库对象。
典型的开发场景 (Typical development scenario )
A typical database development scenario (also known as conventional database development style) is as follows:
典型的数据库开发方案（也称为常规数据库开发样式）如下：
- You create a new database (if it does not already exists) 您创建一个新数据库（如果尚不存在）
- You create a database object (based on internal or external business requirements) 您创建一个数据库对象（基于内部或外部业务需求）
- You write code for the database object (to meet internal or external business requirements) 您为数据库对象编写代码（以满足内部或外部业务需求）
- You create a SQL unit test for the database object (to ensure the object is working properly) 您为数据库对象创建一个SQL单元测试（以确保该对象正常工作）
- You run the SQL unit test for the database object (to validate business requirements embedded in the database object) 您对数据库对象运行SQL单元测试（以验证嵌入在数据库对象中的业务需求）
特殊发展方案 (Special development scenario )
A special database development scenario (such as test-driven database development) has the following steps:
特殊的数据库开发方案（例如测试驱动的数据库开发）具有以下步骤：
- You create a new database (if it does not already exists) 您创建一个新数据库（如果尚不存在）
- You create a SQL unit test for a potential database object (to meet internal or external requirements) 您为潜在的数据库对象创建一个SQL单元测试（以满足内部或外部要求）
- You run a SQL unit test for the potential database object (which must fail to comply with test-driven database development) 您对潜在的数据库对象运行SQL单元测试（该对象必须不符合测试驱动的数据库开发）
- You create the potential database object (to work properly to pass the unit test) 您创建潜在的数据库对象（以正常工作以通过单元测试）
- You run the SQL unit test (to validate the internal or external requirements) 您运行SQL单元测试（以验证内部或外部需求）
如何命名数据库对象 (What about naming a database object)
If we look at both typical and special database development scenarios it seems it does not even matter how we name the object.
如果我们同时查看典型和特殊的数据库开发方案，看来如何命名对象都没有关系。
The truth is, it matters a lot as you proceed further and this is what I am going to explain in this article in the context of SQL unit testing.
事实是，当您继续进行时，这很重要，这就是我将在本文中有关SQL单元测试的内容进行解释的内容。
The time you think of the potential object (in test-driven database development) or the actual object (in conventional database development) to be created based on business or internal requirements you must think of naming the object cleverly because the database object is going to be referenced throughout its lifetime by that name unless refactoring requirement to rename the object arrives.
当您考虑根据业务或内部需求创建潜在对象（在测试驱动的数据库开发中）或实际对象（在传统数据库开发中）时，您必须考虑巧妙地命名该对象，因为数据库对象将要在整个生命周期中都使用该名称进行引用，除非重命名该对象的重构要求到达。
SQL单元测试对象简介 (Introducing SQL Unit Testing Object)
Please remember that I am coining a new term SQL Unit Testing Object to be interchangeably used with database object where by SQL unit testing object I particularly mean the database object which must be unit tested to validate it is functioning properly.
请记住，我创造了一个新术语SQL单元测试对象 ，可以与数据库对象互换使用，其中SQL单元测试对象特别是指必须进行单元测试以验证其功能正常的数据库对象。
您应该如何命名对象 (How you should name the object)
Please consider the following things when naming your database or SQL unit testing object:
在命名数据库或SQL单元测试对象时，请考虑以下事项：
- The name of the object should not be based on the type of the object 对象的名称不应基于对象的类型
- The name of the object should not be confusing 对象的名称不应混淆
- The name of the object should be standardized 对象名称应标准化
- The name of the object should reflect its purpose 对象的名称应反映其用途
- The name of the object should not be verbose (too long) 对象的名称不能太冗长（太长）
- The object name should be based on considering SQL unit testing in mind 对象名称应基于考虑到SQL单元测试的考虑
The sixth point in the above list is the most important point.
上面列表中的第六点是最重要的一点。
Let us understand database object naming in the form of two scenarios where each scenario represents a business requirement that keeps on changing with time.
让我们以两种情况的形式来理解数据库对象的命名，其中每种情况都代表着随时间变化的业务需求。
先决条件 (Pre-requisites)
There are some pre-requisites before we proceed further to understanding database object naming from development and SQL unit testing perspective.
在从开发和SQL单元测试的角度进一步理解数据库对象命名之前，需要满足一些先决条件。
T-SQL和TSQLt熟悉 (T-SQL and TSQLt familiarity )
This article assumes that the readers are well familiar with T-SQL scripting and tSQLt which is a very well-known database unit testing framework for SQL Server.
本文假定读者对T-SQL脚本和tSQLt非常熟悉，后者是SQL Server的非常著名的数据库单元测试框架。
设置样本数据库（Toyshop） (Setup sample database (Toyshop))
This article also assumes that a sample database named Toyshop has been created with the following tables:
本文还假定已使用下表创建了一个名为Toyshop的示例数据库 ：
- Toy 玩具
- ToySale 玩具销售
Please use the following script to setup the sample database Toyshop:
请使用以下脚本来设置示例数据库Toyshop：
-- Create toyshop database
CREATE DATABASE Toyshop;
GO
-- Create Toy and ToySale Tables
USE Toyshop
CREATE TABLE [dbo].[Toy]
(
[ToyId] INT NOT NULL,
[Name] VARCHAR(40) NOT NULL,
[Price] DECIMAL(10,2) NOT NULL,
[Detail] VARCHAR(400) NULL,
CONSTRAINT [PK_Toy] PRIMARY KEY ([ToyId])
);
GO
CREATE TABLE [dbo].[ToySale]
(
[ToySaleId] INT NOT NULL,
[ToyId] INT NOT NULL,
[Date] DATETIME2 NOT NULL,
[Quantity] INT NOT NULL,
[Revenue] DECIMAL(10,2) NOT NULL,
CONSTRAINT [PK_ToySale] PRIMARY KEY ([ToySaleId]),
CONSTRAINT [FK_ToySale_ToTable] FOREIGN KEY ([ToyId]) REFERENCES [Toy]([ToyId])
);
GO
设置tSQLt单元测试框架 (Setup tSQLt unit testing framework )
You need to install tSQLt unit testing framework in order to follow the examples in this article. You can check
您需要安装tSQLt单元测试框架才能遵循本文中的示例。 你可以检查
tSQLt.org to download tSQLt unit testing framework.
tSQLt.org下载tSQLt单元测试框架。
Please refer to Conventional SQL Unit Testing with tSQLt in Simple Words article for a better understanding on how to install tSQLt framework if you have not installed it before.
如果您以前没有安装过tSQLt框架，请参阅“简单单词中的使用tSQLt进行常规SQL单元测试”一文，以更好地了解如何安装tSQLt框架。
Open tSQLt.class.sql file in SSMS (SQL Server Management Studio) and Run tSQL.class.sql script against the sample database Toyshop.
在SSMS （SQL Server Management Studio）中打开tSQLt.class.sql文件，然后对示例数据库Toyshop运行tSQL.class.sql脚本。
常规SQL单元测试
(Conventional SQL unit testing
)
We are using conventional SQL unit testing in this article in order to focus entirely on the objectives (naming SQL unit testing object rather than choosing the best unit testing methodology) although I strongly recommend test-driven database development (TDDD).
尽管我强烈建议测试驱动的数据库开发（TDDD），但是我们在本文中使用常规SQL单元测试是为了完全专注于目标（命名SQL单元测试对象而不是选择最佳的单元测试方法） 。
方案1：第一个业务需求 (Scenario 1: First business requirement )
The first case is when you receive the business requirement for the first time.
第一种情况是您首次收到业务需求。
业务需求 (Business requirement )
“The end user should be able to see a sales report for all the toys with their names which have been sold.”
“最终用户应该能够看到所有带有其名称的玩具的销售报告。”
潜在SQL单元测试或数据库对象 (Potential SQL unit testing or database object)
As per conventional SQL unit testing or test-driven database development in order to meet the business requirement, you have to come up with a potential SQL unit testing object (database object) capable of meeting the business specification.
为了满足业务需求，按照常规SQL单元测试或测试驱动的数据库开发，您必须提出一个能够满足业务规范的潜在SQL单元测试对象（数据库对象）。
命名SQL单元测试对象 (Naming SQL unit testing object)
This is the time when you have to cleverly choose the name of the database or SQL unit testing object.
这是您必须聪明地选择数据库或SQL单元测试对象的名称的时候。
Hold on! Before choosing the name of SQL unit testing object do you really need to decide the type of object?
坚持，稍等！ 在选择SQL单元测试对象的名称之前，您是否真的需要确定对象的类型？
For example, is the potential object going to be SQL function or SQL stored procedure?
例如，潜在对象是SQL函数还是SQL存储过程？
The key is to forget the type first and simply name your SQL unit testing object to denote its purpose only which must not give clue to its type.
关键是先忘记类型，然后简单地命名您SQL单元测试对象，以仅表示其目的，这绝不能说明其类型。
We are naming the object as ToySalesReport due to the following reasons:
由于以下原因，我们将对象命名为ToySalesReport ：
- The object name clearly speaks out its purpose and the purpose must map to an internal or external business or system requirement 对象名称明确说明了其用途，并且该用途必须映射到内部或外部业务或系统要求
- The object name is not bound to a specific type such as stored procedure or function which is beneficial in the long run 对象名称未绑定到特定类型，例如存储过程或函数，这从长远来看是有益的
- The object name is easy to understand and does not require additional documentation to explain which business requirement it is intended to meet 对象名称易于理解，不需要其他文档来解释它打算满足的业务需求
SQL单元测试对象的类型 (Type of SQL unit testing object)
Let us follow the principle of least privilege here in the context of choosing the type of SQL unit testing object. This means to think and ask do you really need a stored procedure here or not? The answer is No.
让我们在选择SQL单元测试对象的类型时遵循最小特权原则。 这意味着思考并询问您是否真的需要此处的存储过程？ 答案是不。
A stored procedure is not required because this requirement can be easily fulfilled by a simple SQL view.
不需要存储过程，因为此要求可以通过简单SQL视图轻松实现。
创建ToySalesReport（SQL视图） (Create ToySalesReport (SQL View))
-- Create ToySalesReport (view)
CREATE VIEW ToySalesReport
AS
SELECT [s].[ToySaleId],[t].[Name], [s].[Date], [s].[Revenue] FROM ToySale s
INNER JOIN Toy t
on s.ToyId=s.ToyId
The object has been created so we are going to create a SQL unit test next.
对象已创建，因此我们接下来将创建一个SQL单元测试。
创建ToySalesReportTests类 (Create ToySalesReportTests class)
In order to write tSQLt unit tests, we have to create a test class first.
为了编写tSQLt单元测试，我们必须首先创建一个测试类。
Please create ToySalesReportTests schema (test class) as follows:
请按以下方式创建ToySalesReportTests模式（测试类）：
USE Toyshop
GO
-- Creating unit test class ArticleTests
CREATE SCHEMA [ToySalesReportTests]
Authorization dbo
GO
EXECUTE sp_addextendedproperty @name='tSQLt.TestClass',@value=1,@level0type='SCHEMA',@level0name='ToySalesReportTests'
创建SQL单元测试以检查对象功能 (Create SQL unit test to check object functionality)
Once the test class is created, the next step in SQL unit testing from tSQLt framework perspective is to create a unit test to check if the object is functioning properly (in conventional SQL unit testing).
一旦创建了测试类，从tSQLt框架的角度来看，SQL单元测试的下一步就是创建一个单元测试，以检查对象是否正常运行（在常规SQL单元测试中）。
Create SQL unit test to check object is functioning properly (meeting business requirement) as follows:
创建SQL单元测试以检查对象是否正常运行（满足业务需求），如下所示：
CREATE PROCEDURE [ToySalesReportTests].[test to check object shows sales report]
AS
-- Assemble
EXEC tSQLt.FakeTable @TableName='dbo.Toy'--,@Identity=1 -- Fake Toy table
EXEC tSQLt.FakeTable @TableName='dbo.ToySale'--,@Identity=1 -- Fake ToySale table
INSERT INTO Toy
(ToyId,Name,Price)
VALUES
(1,'Remote Control Car',10.00)
INSERT INTO ToySale
(ToySaleId,ToyId,Date,Quantity,Revenue)
VALUES
(1,1,'01 Jan 2018',1,10.00)
Create TABLE ToySalesReportTests.Expected -- Create expected table
(
[ToySaleId] INT NOT NULL,
[Name] VARCHAR(40) NOT NULL,
[Date] DATETIME2 NOT NULL,
[Revenue] DECIMAL(10,2) NOT NULL
)
INSERT INTO ToySalesReportTests.Expected -- Insert data into expected table
(ToySaleId,Name,Date,Revenue)
VALUES
(1,'Remote Control Car','01 Jan 2018',10.00)
-- Act
SELECT * INTO ToySalesReportTests.Actual FROM dbo.ToySalesReport -- Run object put the records from Author table into Actual table
-- Assert (compare expected table with actual table results)
EXEC tSQLt.AssertEqualsTable @Expected='ToySalesReportTests.Expected',@Actual='ToySalesReportTests.Actual'
运行SQL单元测试 (Run SQL unit test )
Run the tSQLt unit test to see the results:
运行tSQLt单元测试以查看结果：
-- Running tSQLt all unit tests
EXEC tSQLt.RunAll
The SQL unit test has passed.
SQL单元测试已通过。
Think of this SQL unit testing example from clever naming of potential object point of view as well.
从潜在对象的巧妙命名的角度考虑这个SQL单元测试示例。
Things are going to be clearer as we move on to the next scenario.
当我们转到下一个场景时，情况将变得更加清晰。
方案2：业务需求变更 (Scenario 2: Change in business requirement)
As you know that SQL unit test written in tSQLt in the previous scenario has passed which ensures that ToySalesReport object meets the business requirement.
如您所知，在前面的场景中用tSQLt编写SQL单元测试已通过，这确保ToySalesReport对象满足业务需求。
Let’s say after some time there is a change in business requirement and we are asked to meet the updated business requirement.
假设一段时间后，业务需求发生了变化，并且要求我们满足更新的业务需求。
业务需求变更 (Business requirement change )
“The end user should be able to see a sales report for all the toys with their names for a specified year which have been sold.”
“最终用户应该能够看到在指定年份内已售出其名称的所有玩具的销售报告。”
巧妙命名 (Clever naming)
This is how clever naming of the SQL unit testing object (ToySalesReport) helps because we are not going to introduce a new object with another name to meet new business requirements rather we are going to keep the same object.
这就是对SQL单元测试对象（ToySalesReport）进行巧妙命名的方式，因为我们不会引入具有其他名称的新对象来满足新的业务需求，而是会保留相同的对象。
更新ToySalesReport（将视图更改为过程） (Update ToySalesReport (changing the view into procedure))
We are simply going to convert the SQL view into a SQL stored procedure so that the required sales data for a specified year can be displayed as per business requirements.
我们仅要将SQL视图转换为SQL存储过程，以便可以根据业务需求显示指定年份的所需销售数据。
Please write and run the following script to update ToySalesReport object to meet the changes in the business requirement:
请编写并运行以下脚本来更新ToySalesReport对象，以满足业务需求的变化：
-- Drop ToySalesReport (SQL view)
DROP VIEW dbo.ToySalesReport ;
GO
-- Create ToySalesReport (SQL procedure)
CREATE PROCEDURE dbo.ToySalesReport
@SalesYear INT
AS
SET NOCOUNT ON
SELECT [s].[ToySaleId],[t].[Name], [s].[Date], [s].[Revenue] FROM ToySale s
INNER JOIN Toy t
on s.ToyId=s.ToyId
WHERE Year(s.Date)=@SalesYear
更新SQL单元测试（基于年度的报告） (Update SQL unit test (Year based reporting))
We are updating SQL unit test to meet year based reporting requirement as follows:
我们正在更新SQL单元测试，以满足基于年度的报告要求，如下所示：
ALTER PROCEDURE [ToySalesReportTests].[test to check object shows sales report]
AS
-- Assemble
EXEC tSQLt.FakeTable @TableName='dbo.Toy'--,@Identity=1 -- Fake Toy table
EXEC tSQLt.FakeTable @TableName='dbo.ToySale'--,@Identity=1 -- Fake ToySale table
INSERT INTO Toy
(ToyId,Name,Price)
VALUES
(1,'Remote Control Car',10.00)
INSERT INTO ToySale
(ToySaleId,ToyId,Date,Quantity,Revenue)
VALUES
(1,1,'01 Jan 2018',1,10.00),
(1,1,'01 Jan 2019',1,10.00)
Create TABLE ToySalesReportTests.Expected -- Create expected table
(
[ToySaleId] INT NOT NULL,
[Name] VARCHAR(40) NOT NULL,
[Date] DATETIME2 NOT NULL,
[Revenue] DECIMAL(10,2) NOT NULL
)
INSERT INTO ToySalesReportTests.Expected -- Insert data into expected table
(ToySaleId,Name,Date,Revenue)
VALUES
(1,'Remote Control Car','01 Jan 2019',10.00)
Create TABLE ToySalesReportTests.Actual -- Create actual table
(
[ToySaleId] INT NOT NULL,
[Name] VARCHAR(40) NOT NULL,
[Date] DATETIME2 NOT NULL,
[Revenue] DECIMAL(10,2) NOT NULL
)
-- Act
INSERT INTO ToySalesReportTests.Actual -- put the results into actual table
EXEC dbo.ToySalesReport 2019 -- call the object with the year 2019
-- Assert (compare expected table with actual table results)
EXEC tSQLt.AssertEqualsTable @Expected='ToySalesReportTests.Expected',@Actual='ToySalesReportTests.Actual'
运行SQL单元测试 (Run SQL unit test )
We need to run the updated SQL unit test:
我们需要运行更新SQL单元测试：
-- Running tSQLt all unit tests
EXEC tSQLt.RunAll
Congratulations! The SQL unit test has passed again.
恭喜你！ SQL单元测试再次通过。
We have not changed the SQL unit testing object name at all rather we updated the SQL unit test after converting the view into a stored procedure and everything seems to be working well.
我们根本没有更改SQL单元测试对象的名称，而是在将视图转换为存储过程之后更新了SQL单元测试，并且一切似乎都运行良好。
This is just one simple example of clever naming of SQL unit testing object which lets us easily adapt to changing business requirements with time and if you keep your standard naming like this you can even cover slightly complicated scenarios without ever changing the names of the database or SQL unit testing objects.
这只是对SQL单元测试对象进行巧妙命名的一个简单示例，它使我们能够轻松地随时间适应不断变化的业务需求，如果您保持这样的标准命名，甚至可以覆盖稍微复杂的场景而无需更改数据库名称或SQL单元测试对象。
进一步阅读 (Further Reading)
I strongly recommend the beginners and database professionals who would like to proceed further with SQL unit testing using tSQLt to go through the following articles:
我强烈建议希望进一步使用tSQLt进行SQL单元测试的初学者和数据库专业人员来阅读以下文章：
- tSQLt – A Forgotten Treasure in Database Unit Testing tSQLt –数据库单元测试中被遗忘的宝藏
- Conventional SQL Unit Testing with tSQLt in Simple Words 简单单词中使用tSQLt进行的常规SQL单元测试
- Fundamentals of Test-Driven Database Development (TDDD) with tSQLt unit testing tSQLt单元测试的测试驱动数据库开发（TDDD）基础
- 10 Most Common SQL Unit Testing Mistakes 10个最常见SQL单元测试错误
You can also refer to the below articles, written by my fellow author, Esat Erkec:
您还可以参考由我的同伴Esat Erkec撰写的以下文章：
- SQL unit testing with the tSQLt framework for beginners 使用tSQLt框架SQL单元测试面向初学者
- How to use fake tables in SQL unit testing? 如何在SQL单元测试中使用假表？
- SQL unit testing best practices SQL单元测试最佳实践
目录 (Table of contents)
| tSQLt –数据库单元测试中被遗忘的宝藏 |
| 简单单词中使用tSQLt进行的常规SQL单元测试 |
| tSQLt单元测试的测试驱动数据库开发（TDDD）基础 |
| 10个最常见SQL单元测试错误 |
| 为什么要为SQL单元测试巧妙地命名数据库对象 |
| 您可以针对任何存储过程编写三个标准SQL单元测试 |
| 使用tSQLt创建SQL单元测试实用程序过程 |
翻译自: https://www.sqlshack.com/why-you-should-cleverly-name-database-objects-for-sql-unit-testing/
sql重命名数据库
2706
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
