本文共 16543 字,大约阅读时间需要 55 分钟。
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单元测试期间,当您遇到不太固定的业务需求时。
Let us first talk about naming database objects in general.
首先让我们首先讨论一下命名数据库对象。
A typical database development scenario (also known as conventional database development style) is as follows:
典型的数据库开发方案(也称为常规数据库开发样式)如下:
A special database development scenario (such as test-driven database development) has the following steps:
特殊的数据库开发方案(例如测试驱动的数据库开发)具有以下步骤:
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.
当您考虑根据业务或内部需求创建潜在对象(在测试驱动的数据库开发中)或实际对象(在传统数据库开发中)时,您必须考虑巧妙地命名该对象,因为数据库对象将要在整个生命周期中都使用该名称进行引用,除非重命名该对象的重构要求到达。
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单元测试对象特别是指必须进行单元测试以验证其功能正常的数据库对象。
Please consider the following things when naming your database or SQL unit testing object:
在命名数据库或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.
让我们以两种情况的形式来理解数据库对象的命名,其中每种情况都代表着随时间变化的业务需求。
There are some pre-requisites before we proceed further to understanding database object naming from development and SQL unit testing perspective.
在从开发和SQL单元测试的角度进一步理解数据库对象命名之前,需要满足一些先决条件。
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的非常著名的数据库单元测试框架。
This article also assumes that a sample database named Toyshop has been created with the following tables:
本文还假定已使用下表创建了一个名为Toyshop的示例数据库 :
Please use the following script to setup the sample database Toyshop:
请使用以下脚本来设置示例数据库Toyshop:
-- Create toyshop databaseCREATE DATABASE Toyshop;GO -- Create Toy and ToySale TablesUSE 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
You need to install tSQLt unit testing framework in order to follow the examples in this article. You can check
您需要安装tSQLt单元测试框架才能遵循本文中的示例。 你可以检查
to download tSQLt unit testing framework.
下载tSQLt单元测试框架。
Please refer to article for a better understanding on how to install tSQLt framework if you have not installed it before.
如果您以前没有安装过tSQLt框架,请参阅进行文,以更好地了解如何安装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脚本。
We are using 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 .
尽管我强烈建议但是我们在本文中使用是为了完全专注于目标(命名SQL单元测试对象而不是选择最佳的单元测试方法 。
The first case is when you receive the business requirement for the first time.
第一种情况是您首次收到业务需求。
“The end user should be able to see a sales report for all the toys with their names which have been sold.”
“最终用户应该能够看到所有带有其名称的玩具的销售报告。”
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单元测试对象(数据库对象)。
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 :
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视图轻松实现。
-- Create ToySalesReport (view)CREATE VIEW ToySalesReportASSELECT [s].[ToySaleId],[t].[Name], [s].[Date], [s].[Revenue] FROM ToySale sINNER JOIN Toy ton s.ToyId=s.ToyId
The object has been created so we are going to create a SQL unit test next.
对象已创建,因此我们接下来将创建一个SQL单元测试。
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 ToyshopGO -- Creating unit test class ArticleTestsCREATE SCHEMA [ToySalesReportTests]Authorization dboGOEXECUTE sp_addextendedproperty @name='tSQLt.TestClass',@value=1,@level0type='SCHEMA',@level0name='ToySalesReportTests'
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-- AssembleEXEC 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) -- ActSELECT * 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'
Run the tSQLt unit test to see the results:
运行tSQLt单元测试以查看结果:
-- Running tSQLt all unit testsEXEC 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.
当我们转到下一个场景时,情况将变得更加清晰。
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.
假设一段时间后,业务需求发生了变化,并且要求我们满足更新的业务需求。
“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.”
“最终用户应该能够看到在指定年份内已售出其名称的所有玩具的销售报告。”
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)进行巧妙命名的方式,因为我们不会引入具有其他名称的新对象来满足新的业务需求,而是会保留相同的对象。
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 INTASSET NOCOUNT ON SELECT [s].[ToySaleId],[t].[Name], [s].[Date], [s].[Revenue] FROM ToySale sINNER JOIN Toy ton s.ToyId=s.ToyIdWHERE Year(s.Date)=@SalesYear
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-- AssembleEXEC 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) -- ActINSERT INTO ToySalesReportTests.Actual -- put the results into actual tableEXEC 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'
We need to run the updated SQL unit test:
我们需要运行更新SQL单元测试:
-- Running tSQLt all unit testsEXEC 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单元测试对象。
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单元测试的初学者和数据库专业人员来阅读以下文章:
You can also refer to the below articles, written by my fellow author, Esat Erkec:
您还可以参考由我的同伴Esat Erkec撰写的以下文章:
Why you should cleverly name Database Objects for SQL Unit Testing |
为什么要为SQL单元测试巧妙地命名数据库对象 |
翻译自:
sql重命名数据库
转载地址:http://wpswd.baihongyu.com/