演练：应用数据库重构技术

  • 项目

通过使用 Visual Studio 高级专业版 或 Visual Studio 旗舰版 中的重构技术，可以减少设计和更新数据库架构时必须执行的重复且容易出错的任务的数量。 例如，如果数据库对象的名称必须更改或者该对象必须移到不同的架构中，则可以使用重构来更新对该对象的引用。 如果您采用此方法，则可以提高数据库设计例程更改的速度和准确性。

本演练演示数据库开发中的一个典型方案。 若要向现有数据库添加功能，您必须先执行初始实现，然后与另一团队成员一起进行评审。 评审期间，您将确定在签入更改之前必须解决的几个问题。 然后将使用各种重构技术更改架构。

本演练阐释了以下任务：

  • 导入数据库架构

  • 执行典型数据库开发任务

  • 更正编码错误

  • 完成开发任务

  • 处理代码评审反馈

系统必备

若要完成本演练，需要以下项：

  • Visual Studio 高级专业版 或 Visual Studio 旗舰版

  • 对安装有 AdventureWorks2008 数据库的数据库服务器的只读访问权限。

导入数据库架构

在团队环境下更改架构之前，通常应先检查版本控制系统之外的现有项目。 对于本演练，将创建一个数据库项目并从 AdventureWorks2008 示例数据库中导入架构。

创建数据库项目

  1. 在**“文件”菜单上指向“新建”，再单击“项目”**。

    此时将出现**“新建项目”**对话框。

  2. 在**“已安装的模板”下，展开“数据库”节点，然后单击“SQL Server”**节点。

  3. 在模板列表中单击**“SQL Server 2008 数据库项目”**。

  4. 在**“名称”中键入 RefactorAdventureWorks，然后单击“确定”**。

    随即会创建一个包含名为 RefactorAdventureWorks 的空数据库项目的解决方案作为您的测试（也称为沙盒）项目。

    接下来，从 AdventureWorks 数据库的部署实例中导入架构。

导入 AdventureWorks 数据库

  1. 在**“解决方案资源管理器”或“架构视图”中，单击“RefactorAdventureWorks”**。

  2. 在**“项目”菜单上，单击“导入数据库对象和设置”**。

    提示

    还可以右击“RefactorAdventureWorks”，再单击“导入数据库对象和设置”。

    将出现**“导入数据库向导”**。

  3. 在**“源数据库连接”**列表中，单击与 AdventureWorks 数据库相对应的连接。

    重要说明重要事项

    如果尚未连接该数据库，则必须先单击“新建连接”创建到该数据库的连接。 有关更多信息，请参见如何：创建数据库连接。

  4. 导入对象和设置后，单击**“开始”，然后单击“完成”**。

    架构导入后，与数据库中的对象对应的项目项显示在**“解决方案资源管理器”和“架构视图”**中数据库项目的下面。

    提示

    即使是连接到数据库导入架构，现在也已断开连接并脱机工作。

    接下来，将在数据库开发过程中执行典型任务，并向数据库项目添加代码。

执行典型数据库开发任务

对于此任务，已请求您实现支持以跟踪每个员工的请假历史记录。 作为此任务的一部分，您必须创建以下对象：

  • 一个表，用于跟踪每次请假的开始和结束日期以及请假的类型（休假、病假、应急假、产假、无薪假或丧假）。 在本演练后面的部分中，您将向 Person 架构中添加此表。 表中的数据具有以下限制：

    • 请假时间不得超过五天。 （更长时间的请假将分成多个条目。）

    • 请假具有有效日期范围。

    • 此表通过 EmployeeID 与 Employee 表相关。

  • 一个视图，用于显示每个员工的完整请假历史记录。

  • 一个存储过程，用于记录请假情况和更新员工的休假时间（如果请假类型为休假）。

准备添加代码

  1. 在**“视图”菜单上，单击“数据库架构视图”**。

  2. 在**“架构视图”中展开“RefactorAdventureWorks”**节点。

  3. 如果**“架构视图”按对象类型排序，请在工具栏上单击“更改对象分组”**。

    提示

    如果架构视图包含的节点名为“表”和“视图”，则“架构视图”按对象类型排序。 如果“架构视图”包含名为“架构”的节点，则可以继续下一过程。

    接下来，将向数据库项目添加 AbsenceHistory 表。

添加 AbsenceHistory 表

  1. 在**“架构视图”中，依次展开“架构”节点、“Person”子节点和“表”**子节点。

  2. 右击**“表”子节点，指向“添加”，然后单击“表”**。

    此时会出现**“添加新项”**对话框。

  3. 在**“名称”中，键入 AbsenceHistory，然后单击“添加”**。

    Transact-SQL 编辑器将打开并显示 AbsenceHistory 表的定义。

  4. 在 Transact-SQL 编辑器中，用下面的代码替换现有表定义：

    CREATE TABLE [Person].[AbsenceHistory]
(
[EmployeeID] INT NOT NULL, 
[BeginDate] DateTime NOT NULL,
[EndDate] DateTime NOT NULL,
[AbsenceType] NCHAR(1) NOT NULL
);

  5. 在**“文件”菜单上，单击“保存 Person.AbsenceHistory.table.sql”**。

    接下来将向 AbsenceHistory 表添加 CHECK 约束。

向表添加 CHECK 约束

  1. 在**“架构视图”中展开“AbsenceHistory”**节点。

  2. 右击**“约束”节点，指向“添加”，然后单击“CHECK 约束”**。

    此时会出现**“添加新项”**对话框。

  3. 在**“名称”中键入 CK_AbsenceHistory_ValidDates，然后单击“添加”**。

    此时将打开 Transact-SQL 编辑器，并显示约束的定义。

  4. 在 Transact-SQL 编辑器中，用下面的代码替换现有约束定义：

    ALTER TABLE [Person].[AbsenceHistory]
ADD CONSTRAINT [CK_AbsenceHistory_ValidDates] 
CHECK  (EndDate >= BeginDate AND DateDiff(day, EndDate, BeginDate) <= 5)
go
EXECUTE sp_addextendedproperty @name = N'MS_Description', 
@value = 'Check constraint [EndDate]>= [BeginDate]', 
@level0type = N'SCHEMA', 
@level0name = N'Person', 
@level1type = N'TABLE', 
@level1name = N'AbsenceHistory', 
@level2type = N'CONSTRAINT', 
@level2name = N'CK_AbsenceHistory_ValidDates';

    此代码在表上定义约束，以确保结束日期在开始日期之后，并且它们之间的增量不超过五天。

  5. 在**“文件”菜单上，单击“保存 Person.AbsenceHistory.CK_AbsenceHistory_ValidDates.chkconst.sql”**。

    接下来将向“AbsenceHistory”表中添加一个外键。

添加外键定义

  1. 在**“架构视图”中，右击“键”节点，指向“添加”，然后单击“外键”**。

    此时会出现**“添加新项”**对话框。

  2. 在**“名称”中键入 FK_AbsenceHistory_Employee_EmployeeID，然后单击“添加”**。

    此时将打开 Transact-SQL 编辑器，并显示外键的定义。

  3. 在 Transact-SQL 编辑器中，用下面的代码替换外键的现有定义：

    ALTER TABLE [Person].[AbsenceHistory]
ADD CONSTRAINT [FK_AbsenceHistory_Employee_EmployeeID] 
FOREIGN KEY ([EmployeeID]) 
REFERENCES [HumanResources].[Employee] ([BusinessEntityID]) 
ON DELETE NO ACTION ON UPDATE NO ACTION;

GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', 
    @value = 'Foreign key constraint referencing Employee.BusinessEntityID.', 
    @level0type = N'SCHEMA', 
    @level0name = N'Person', 
    @level1type = N'TABLE', 
    @level1name = N'AbsenceHistory', 
    @level2type = N'CONSTRAINT', 
    @level2name = N'FK_AbsenceHistory_Employee_EmployeeID';

    此代码定义 AbsenceHistory 表中 EmployeeID 与 [HumanResources].[Employee] 表中 BusinessEntityID 之间的关系。

  4. 在**“文件”菜单上，单击“保存 Person.AbsenceHistory.FK_AbsenceHistory_Employee_EmployeeID.fkey.sql”**。

    此时，您意识到表应该处于 HumanResources 架构中。 您将在下一过程中更正此错误。

更正编码错误

由于您已经定义了约束和外键，因此将表及其相关对象移到不同的架构时通常将花费大量的时间。 可以先通过数据库重构快速轻松地将表及其相关对象移到正确的架构中，然后再继续。

将 AbsenceHistory 表移到 HumanResources 架构

  1. 在**“架构视图”中，右击“AbsenceHistory”表，指向“重构”，然后单击“移动到架构”**。

    即会出现**“移动架构”**对话框。

  2. 在**“新架构”列表中，单击“HumanResources”**。

  3. 验证是否选中了**“预览更改”复选框，然后单击“确定”**。

    此时将显示**“预览更改”**对话框。 可以先检查所做的更改，然后再将更改应用于数据库项目。

  4. 单击**“应用”**。

    重构更改即会应用于数据库项目。 AbsenceHistory 表将与所有相关对象一起从 Person 架构移到 HumanResources 架构。

  5. 在**“架构视图”中，展开“HumanResources”架构节点，然后展开“表”**节点。

    此时 AbsenceHistory 表将出现在正确的架构中。

    提示

    将对象移动至正确的架构时，不会更改在其中定义对象的文件的名称。 如果要更新文件名，则必须在“解决方案资源管理器”中重命名文件。

    接下来，您将完成开发任务的剩余步骤。

完成开发任务

既然更正了表的架构，就必须创建以下对象：

  • 一个视图，用于显示每个员工的完整请假历史记录。

  • 一个存储过程，用于记录请假情况和更新员工的休假时间（如果请假类型为休假）。

添加 vEmployeeAbsenceHistory 视图

  1. 在**“架构视图”的 HumanResources 架构中，展开“视图”**节点。

  2. 右击**“视图”节点，指向“添加”，然后单击“视图”**。

    此时会出现**“添加新项”**对话框。

  3. 在**“名称”中键入 vEmployeeAbsenceHistory，然后单击“添加”**。

    此时将打开 Transact-SQL 编辑器，并显示视图的定义。

  4. 在 Transact-SQL 编辑器中，用下面的代码替换现有视图定义：

    CREATE VIEW [HumanResources].[vEmployeeAbsenceHistory]
AS 
SELECT 
    a.* 
    ,c.[Title] 
    ,c.[FirstName] 
    ,c.[MiddleName] 
    ,c.[LastName] 
    ,c.[Suffix] 
FROM [HumanResources].[Employee] e
    INNER JOIN [Person].[Person] c 
    ON c.[BusinessEntityID] = e.[BusinessEntityID]
    INNER JOIN [AbsenceHistory] a 
    ON e.[BusinessEntityID] = a.[EmployeeID] 
;

GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', 
@value = 'Returns employee name and absence history.', 
@level0type = N'SCHEMA', 
@level0name = N'HumanResources', 
@level1type = N'VIEW', 
@level1name = N'vEmployeeAbsenceHistory';

    此代码定义从 Employee、Contact 和 AbsenceHistory 表组合中返回数据的视图。

  5. 在**“文件”菜单上，单击“保存 HumanResources.vEmployeeAbsenceHistory.view.sql”**。

    接下来将添加存储过程。

添加 uspRecordAbsence 存储过程

  1. 在**“架构视图”的 HumanResources 架构中，展开“可编程性”节点，然后展开“存储过程”**节点。

  2. 右击**“存储过程”节点，指向“添加”，然后单击“存储过程”**。

    此时会出现**“添加新项”**对话框。

  3. 在**“名称”中，键入 uspRecordAbsence，然后单击“添加”**。

    Transact-SQL 编辑器将打开并显示存储过程的定义。

  4. 在 Transact-SQL 编辑器中，用下面的代码替换存储过程的现有定义：

    CREATE PROCEDURE [HumanResources].[uspRecordAbsence]
@EmployeeID INT,
@AbsenceType NCHAR(1),
@StartDate DATETIME,
@EndDate DATETIME
AS
BEGIN
BEGIN TRANSACTION
INSERT INTO [AbsenceHistory] (EmployeeID, BeginDate, EndDate, AbsenceType)
VALUES(@EmployeeID, @StartDate, @EndDate, @AbsenceType)
IF (@AbsenceType = 'V')
BEGIN
UPDATE [Employee]
SET [VacationHours] = [VacationHours] - DateDiff(day, @StartDate, @EndDate)
WHERE [BusinessEntityID] = @EmployeeID
END
COMMIT TRANSACTION
END;

    此代码定义的存储过程在 AbsenceHistory 表中添加行，并更新 Employee 表中的 VacationHours 字段（如果请假类型为“休假”）。

  5. 在**“文件”菜单上，单击“保存 dbo.uspRecordAbsence.proc.sql”**。

    在下一个过程中，将处理您在代码评审中收到的反馈。

处理代码评审反馈

与另一团队成员一起评审代码之后，您会收到有关若干最佳做法的反馈。 其中要求您避免使用 SELECT *，因为如果您对数据库代码运行静态代码分析，则 SELECT * 会生成警告。 另外，要求您完全限定在存储过程中使用的名称。 最后，还要求您将 AbsenceHistory 表中的 BeginDate 列重命名为 StartDate。

提示

编码标准和要求因团队而异。 您应该将组织的编码标准应用于所编写的 Transact-SQL 代码。 本演练将阐释两个问题。 另外，通常应将这些技术应用于所有新代码（例如，完全限定新代码中的所有名称），而不是仅应用于单个数据库对象。

同样，这些类型的更改实施起来可能单调乏味并且易于出错。 可以通过数据库重构快速轻松地更新您的数据库代码、测试代码和数据生成计划。

在视图定义中展开 SELECT *

  1. 在**“架构视图”**中双击“vEmployeeAbsenceHistory”视图。

    此时将打开 Transact-SQL 编辑器，并显示视图的定义。

  2. 在**“数据”菜单上，指向“重构”，然后单击“扩展通配符”**。

    此时将显示**“预览更改”**对话框。

  3. 在**“扩展通配符”列表中，单击“a.*”**。

    此时**“预览更改”**窗格会显示将应用于视图的更新。

  4. 单击**“应用”**。

    更改即会应用于数据库项目。 接下来，您将完全限定在本演练前面过程所定义的存储过程中的名称。

完全限定存储过程中的名称

  1. 在**“架构视图”**中双击“uspRecordAbsence”存储过程。

    Transact-SQL 编辑器将打开并显示存储过程的定义。

  2. 在**“数据”菜单上，指向“重构”，然后单击“完全限定名称”**。

    **“预览更改”**对话框随即出现，并显示将进行的所有更改（如果您将重构操作应用于项目）。

  3. 检查更改之后，单击**“应用”**。

    更改即会应用于数据库项目。

重命名 BeginDate 列

  1. 在**“架构视图”**中，依次展开“AbsenceHistory”表和“列”节点，然后单击“BeginDate”列。

  2. 在**“数据”菜单上，指向“重构”，然后单击“重命名”**。

    出现**“重命名”**对话框。

    提示

    也可以右击“架构视图”中的“BeginDate”，指向“重构”，然后单击“重命名”。

  3. 在**“新名称”**中，键入 StartDate。

  4. 选中**“预览更改”复选框，然后单击“确定”**。

    **“预览更改”**对话框随即出现，并显示将进行的所有更改（如果您将重命名操作应用于数据库项目）。

  5. 单击**“应用”**。

    此时将进行更改。 列名已更新，**“架构视图”**中显示每个更新对象的新列名。 如果您打开在本主题前面部分指定的日期约束的定义，则也会更新该约束以引用新列名称。

后续步骤

此时，通常应该与执行代码评审的团队成员一起检查更新，然后将更改签入版本控制。 现在，您已经更新了数据库项目，即数据库架构的脱机表示形式。 必须将该数据库项目部署到目标数据库才能更新部署的架构。

将重构操作应用于数据库项目时，如果您可以使用 sp_rename 或 ALTER 来重命名或移动对象，则将在重构日志文件中记录有关该操作的信息。 在本演练中，日志文件名为 RefactorAdventureWorks.refactorlog。 在部署时使用重构日志文件是为了尝试保留重构更改的意图。 例如，如果重命名某个列，则重构日志将记录您的更改。 在部署时，该信息将防止删除名称较早的列及其所包含的任何数据，并将用新名称来创建空列。 如果使用重构，则不必向预先部署脚本和后期部署脚本中添加语句来保留数据。

请参见

任务

如何：部署数据库重构更改

概念

将数据库对象移至另一个架构

完全限定数据库对象的名称

扩展 SELECT 语句中的通配符

分析数据库代码以提高代码质量