Поделиться через

Пошаговое руководство. Применение методов рефакторинга базы данных

  • Статья

С помощью рефакторинга в Visual Studio Premium или Visual Studio Ultimate можно сократить число повторяющихся задач, повышающих вероятность ошибок, которые необходимо выполнять при проектировании и обновлении схемы базы данных. Например, рефакторинг можно использовать для обновления ссылок на объект базы данных, если его имя должно измениться или если он сам должен быть перемещен в другую схему. При таком подходе можно повысить скорость и точность изменений процедур в структуре базы данных.

В этом пошаговом руководстве демонстрируется типичный сценарий разработки базы данных. Для добавления возможностей в имеющуюся базу данных следует выполнить первоначальное внедрение, а затем проанализировать его с другими участниками команды. В процессе анализа будет выявлено несколько проблем, которые необходимо устранить перед возвратом изменений. Затем будут применены различные методы рефакторинга для внесения изменений в схему.

В данном пошаговом руководстве рассмотрены следующие задачи:

  • Импорт схемы базы данных

  • Реализация типичной задачи разработки базы данных

  • Исправление ошибки в коде

  • Выполнение задачи разработки

  • Учет отзывов по результатам анализа кода

Обязательные компоненты

Для выполнения данного пошагового руководства необходимо следующее.

  • Visual Studio Premium или Visual Studio Ultimate.

  • Доступ только для чтения к серверу базы данных, на котором установлена база данных AdventureWorks2008.

Импорт схемы базы данных

Обычно, перед внесением изменений в схему в среде на основе рабочих групп выполняется извлечение имеющегося проекта из системы управления версиями. Для этого пошагового руководства нужно создать проект базы данных и импортировать схему из образца базы данных AdventureWorks2008.

Создание проекта базы данных

  1. В меню Файл последовательно выберите пункты Создать и Проект.

    Откроется диалоговое окно Новый проект.

  2. В области Установленные шаблоны разверните узел База данных и щелкните узел SQL Server.

  3. В списке шаблонов выберите Проект базы данных SQL Server 2008.

  4. В поле Имя введите RefactorAdventureWorks и нажмите кнопку ОК.

    Будет создано решение, содержащее пустой проект базы данных с именем RefactorAdventureWorks, который будет выполнять функцию тестового проекта (который также называют песочницей).

    Затем необходимо импортировать схему из развернутого экземпляра базы данных AdventureWorks.

Импорт базы данных AdventureWorks

  1. В обозревателе решений или представлении схемы щелкните RefactorAdventureWorks.

  2. В меню Проект щелкните Импортировать объекты и параметры базы данных.

    Примечание

    Можно также щелкнуть правой кнопкой мыши RefactorAdventureWorks, а затем выбрать команду Выполняется импорт объектов и параметров базы данных.

    Появится Мастер импорта базы данных.

  3. В списке Подключение к исходной базе данных щелкните подключение, соответствующее базе данных AdventureWorks.

    Важно!

    Если вы еще не подключались к этой базе данных, сначала щелкните Новое подключение, чтобы создать к ней подключение.Дополнительные сведения см. в разделе Практическое руководство. Создание подключения к базе данных.

  4. Нажмите кнопку Начать, а затем кнопку Готово после завершения импорта объектов и параметров.

    При импорте схемы элементы проекта, соответствующие объектам в базе данных, появляются в проекте базы данных в обозревателе решений и представлении схемы.

    Примечание

    Хотя вы подключались к базе данных для импорта схемы, теперь вы отключены и работаете автономно.

    Далее будет выполнена типичная задача разработки базы данных: добавление кода в проект базы данных.

Реализация типичной задачи разработки базы данных

Для выполнения этой задачи потребовалось внедрить поддержку для отслеживания журнала отсутствия каждого сотрудника. В рамках этой задачи следует создать следующие объекты.

  • Таблицы для отслеживания даты начала и даты завершения каждого отсутствия и его типа (отпуск, болезнь, выполнение функций присяжного, переходящий праздник, неоплачиваемый отпуск или отпуск в связи со смертью близкого). Добавление таблицы в схему Person будет описано далее в этом пошаговом руководстве. У данных в таблице есть следующие ограничения.

    • Длительность отсутствия не превышает пяти дней. (Случаи более длительного отсутствия делятся на несколько записей.)

    • Диапазоны дат отсутствия допустимы.

    • Таблица связана с таблицей Employee при помощи EmployeeID.

  • Представление, в котором отображается полный журнал отсутствия для каждого сотрудника.

  • Хранимая процедура, записывающая отсутствие и обновляющая часы отпуска сотрудника, если тип отсутствия – это отпуск.

Подготовка к добавлению кода

  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.

Добавление проверочного ограничения для таблицы

  1. В представлении схемы разверните узел AbsenceHistory.

  2. Щелкните правой кнопкой мыши узел Ограничения, выберите команду Добавить и щелкните Проверочное ограничение.

    Откроется диалоговое окно Добавление нового элемента.

  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';

    Этот код определяет отношение с использованием внешнего ключа между EmployeeID в таблице AbsenceHistory и BusinessEntityID в таблице [HumanResources].[Employee].

  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, и обновляет поле VacationHours в таблице Employee, если тип отсутствия – "V".

  5. В меню Файл выберите Сохранить dbo.uspRecordAbsence.proc.sql.

    В следующей процедуре будут учтены отзывы, полученные в ходе анализа кода.

Учет отзывов по результатам анализа кода

В процессе анализа кода с другими участниками команды были получены отзывы с несколькими рекомендациями. Было рекомендовано отказаться от использования оператора SELECT *, поскольку это приводит к отображению предупреждений при выполнении статического анализа кода базы данных. Кроме того, для имен, используемых в хранимой процедуре, было рекомендовано использовать полную форму. И, наконец, было предложено переименовать столбец BeginDate в таблице AbsenceHistory в 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

Анализ кода базы данных с целью улучшения качества кода