チュートリアル : データベース リファクタリング機能の適用

  • [アーティクル]

Visual Studio Team System Database Edition でリファクタリングを使用すると、データベース スキーマのデザイン時および更新時に実行する必要がある、反復的でエラーが発生しやすいタスクの数を減らすことができます。 たとえば、データベース オブジェクトの名前を変更する必要がある場合や、オブジェクトを別のスキーマに移動する必要がある場合は、リファクタリングを使用してそのデータベース オブジェクトへの参照を更新できます。 この方法を採用すると、データベース デザインの日常的な変更の速度と精度の両方を向上できます。

このチュートリアルでは、データベース開発の一般的なシナリオを説明します。 既存のデータベースに機能を追加するために、初期実装を実行し、次に別のチーム メンバと共にレビューする必要があります。 レビュー時には、変更をチェックインする前に解決する必要があるいくつかの問題を特定します。 その後、さまざまなリファクタリング機能を使用してスキーマを変更します。

このチュートリアルでは、次のタスクについて説明します。

  • データベース スキーマのインポート

  • 一般的なデータベース開発タスクの実施

  • コーディング エラーの修正

  • 開発タスクの完了

  • コード レビューのフィードバックの解決

前提条件

このチュートリアルを実行するための用件は、次のとおりです。

  • Database Edition。

  • AdventureWorks データベースがインストールされているデータベース サーバーへの読み取り専用アクセス許可。

データベース スキーマのインポート

チーム環境でスキーマを変更する前に、通常はバージョン管理システムから既存のプロジェクトをチェックアウトします。 このチュートリアルでは、データベース プロジェクトを作成し、AdventureWorks サンプル データベースのスキーマをインポートします。

データベース プロジェクトを作成するには

  1. [ファイル] メニューの [新規作成] をポイントし、[プロジェクト] をクリックします。

    [新しいプロジェクト] ダイアログ ボックスが表示されます。

  2. [プロジェクトの種類] ボックスの一覧の [データベース プロジェクト] ノードを展開し、[SQL Server 2005] をクリックします。

  3. [テンプレート] ボックスの一覧の [SQL Server 2005 データベース プロジェクト] をクリックします。

  4. [名前] に「RefactorAdventureWorks」と入力し、[OK] をクリックします。

    テスト (サンドボックス) プロジェクトとして RefactorAdventureWorks という名前の空のデータベース プロジェクトを含むソリューションが作成されます。 このプロジェクトの作業中に、他のユーザーがこのプロジェクトにアクセスすることはできません。

    次に、AdventureWorks データベースの配置済みインスタンスからスキーマをインポートします。

AdventureWorks データベースをインポートするには

  1. ソリューション エクスプローラまたはスキーマ ビューで、[RefactorAdventureWorks] をクリックします。

  2. [プロジェクト] メニューの [データベースのオブジェクトと設定のインポート] をクリックします。

    Dd193272.alert_note(ja-jp,VS.90).gifメモ :

    [RefactorAdventureWorks] を右クリックし、[データベースのオブジェクトと設定のインポート] をクリックすることもできます。

    データベースのインポート ウィザードが表示されます。

  3. [ソース データベース接続] ボックスの一覧で、AdventureWorks データベースに対応する接続をクリックします。

    Dd193272.alert_caution(ja-jp,VS.90).gif重要 :

    データベースに接続していない場合は、最初に [新しい接続] をクリックしてデータベースへの接続を作成する必要があります。 詳細については、「方法 : データベース接続を作成する」を参照してください。

  4. [開始] をクリックします。オブジェクトと設定がインポートされたら、[完了] をクリックします。

    スキーマがインポートされると、データベースのオブジェクトに対応するプロジェクト項目がソリューション エクスプローラおよびスキーマ ビューでデータベース プロジェクトの下に表示されます。

    Dd193272.alert_note(ja-jp,VS.90).gifメモ :

    スキーマをインポートするためにデータベースに接続していても、この時点では接続が切断され、オフラインでの作業になります。

    次に、データベース開発の一般的なタスクである、データベース プロジェクトへのコードの追加を実行します。

一般的なデータベース開発タスクの実施

このタスクでは、各従業員の欠勤の記録を追跡するためのサポートを実装するよう求められています。 このタスクの一部として、次のオブジェクトを作成する必要があります。

  • 各欠勤期間の開始日および終了日と、欠勤の種類 (休暇、病欠、陪審義務、変動休日、無給休暇、または忌引き) を記録するテーブル。 このチュートリアルの後の手順で、このテーブルを Person スキーマに追加します。 このテーブル内のデータには次の制限があります。

    • 欠勤は 5 日を超えない (それ以上の欠勤は複数のエントリに分割される)。

    • 欠勤は有効な日付範囲を持つ。

    • テーブルは EmployeeID によって Employee テーブルに関連付けられている。

  • 各従業員の完全な欠勤記録を表示するビュー。

  • 欠勤を記録し、欠勤の種類が休暇の場合は従業員の休暇時間を更新するストアド プロシージャ。

コードの追加の準備をするには

  1. [表示] メニューの [スキーマ ビュー] をクリックします。

  2. スキーマ ビューで、[RefactorAdventureWorks] ノードを展開します。

  3. スキーマ ビューがオブジェクトの種類を基準として並べ替えられている場合、ツール バーの [オブジェクトをスキーマ別に表示] をクリックします。

    Dd193272.alert_note(ja-jp,VS.90).gifメモ :

    "テーブル" および "ビュー" という名前のノードが含まれている場合は、スキーマ ビューはオブジェクトの種類を基準として並べ替えられています。 スキーマ ビューに "スキーマ" という名前のノードが含まれている場合は、そのまま次の手順に進むことができます。

    次に、AbsenceHistory テーブルをデータベース プロジェクトに追加します。

AbsenceHistory テーブルを追加するには

  1. スキーマ ビューで、[スキーマ] ノードを展開し、[Person] サブノードを展開して、[テーブル] サブノードを展開します。

  2. [テーブル] サブノードを右クリックし、[追加] をポイントして、[テーブル] をクリックします。

    [新しい項目の追加] ダイアログ ボックスが表示されます。

  3. [名前] に「AbsenceHistory」と入力し、[追加] をクリックします。

    Transact-SQL (T-SQL) エディタが開き、AbsenceHistory テーブルの定義が表示されます。

  4. T-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 (T-SQL) エディタが開き、制約の定義が表示されます。

  4. T-SQL エディタで、既存の制約定義を次のコードで置き換えます。

    ALTER TABLE [Person].[AbsenceHistory]
ADD CONSTRAINT [CK_AbsenceHistory_ValidDates] 
CHECK  (EndDate >= BeginDate AND DateDiff(days, 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 日以内であることを確認する制約をテーブルに定義しています。

  5. [ファイル] メニューの [Person.AbsenceHistory.CK_AbsenceHistory_ValidDates.chkconst.sql の保存] をクリックします。

    次に、AbsenceHistory テーブルに外部キーを追加します。

外部キー定義を追加するには

  1. スキーマ ビューで [キー] ノードを右クリックし、[追加] をポイントして、[外部キー] をクリックします。

    [新しい項目の追加] ダイアログ ボックスが表示されます。

  2. [名前] に「FK_AbsenceHistory_Employee_EmployeeID」と入力し、[追加] をクリックします。

    Transact-SQL (T-SQL) エディタが開き、外部キーの定義が表示されます。

  3. T-SQL エディタで、外部キーの既存の定義を次のコードで置き換えます。

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

GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', 
    @value = 'Foreign key constraint referencing Employee.EmployeeID.', 
    @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] テーブルの EmployeeID との間に、外部キー リレーションシップを定義しています。

  4. [ファイル] メニューの [Person.AbsenceHistory.FK_AbsenceHistory_Employee_EmployeeID.fkey.sql の保存] をクリックします。

    この時点で、テーブルは現在のスキーマではなく HumanResources スキーマに存在する必要があることがわかります。 次の手順で、このエラーを修正します。

コーディング エラーの修正

既に制約と外部キーを定義しているので、テーブルとその関連オブジェクトを別のスキーマに移動するのは、通常は非常に時間がかかります。 次に進む前に、データベース リファクタリングを使用すると、短時間で簡単にテーブルとその関連オブジェクトを正しいスキーマに移動できます。

AbsenceHistory テーブルを HumanResources スキーマに移動するには

  1. スキーマ ビューで [AbsenceHistory] テーブルを右クリックし、[リファクタ] をポイントして、[スキーマへ移動] をクリックします。

    [スキーマへ移動] ダイアログ ボックスが表示されます。

  2. [新しいスキーマ] ボックスの一覧の [HumanResources] をクリックします。

  3. [変更のプレビュー] チェック ボックスがオンになっていることを確認して、[OK] をクリックします。

    [変更のプレビュー] ダイアログ ボックスが表示されます。 変更をデータベース プロジェクトに適用する前に確認できます。

  4. [適用] をクリックします。

    リファクタリングの変更がデータベース プロジェクトに適用されます。 AbsenceHistory テーブルとすべての関連オブジェクトが、Person スキーマから HumanResources スキーマに移動します。

  5. スキーマ ビューで、[HumanResources] スキーマ ノードを展開し、[テーブル] ノードを展開します。

    AbsenceHistory テーブルが正しいスキーマに表示されます。

    Dd193272.alert_note(ja-jp,VS.90).gifメモ :

    オブジェクトを正しいスキーマに移動したときに、オブジェクトが定義されているファイルの名前を変更しませんでした。 ファイル名を更新する場合は、ソリューション エクスプローラで名前を変更する必要があります。

    次に、開発タスクの残りの手順を完了します。

開発タスクの完了

テーブルのスキーマを修正したので、次のオブジェクトを作成する必要があります。

  • 各従業員の完全な欠勤記録を表示するビュー。

  • 欠勤を記録し、欠勤の種類が休暇の場合は従業員の休暇時間を更新するストアド プロシージャ。

vEmployeeAbsenceHistory ビューを追加するには

  1. スキーマ ビューの HumanResources スキーマで、[ビュー] ノードを展開します。

  2. [ビュー] ノードを右クリックし、[追加] をポイントして、[ビュー] をクリックします。

    [新しい項目の追加] ダイアログ ボックスが表示されます。

  3. [名前] に「vEmployeeAbsenceHistory」と入力し、[追加] をクリックします。

    Transact-SQL (T-SQL) エディタが開き、ビューの定義が表示されます。

  4. T-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].[Contact] c 
    ON c.[ContactID] = e.[ContactID]
    INNER JOIN [AbsenceHistory] a 
    ON e.[EmployeeID] = 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 (T-SQL) エディタが開き、ストアド プロシージャの定義が表示されます。

  4. T-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(days, @StartDate, @EndDate)
WHERE [EmployeeID] = @EmployeeID
END
COMMIT TRANSACTION
END;

    このコードは、AbsenceHistory テーブルに行を追加し、欠勤の種類が "V" である場合に Employee テーブルの VacationHours フィールドを更新するストアド プロシージャを定義しています。

  5. [ファイル] メニューの [dbo.uspRecordAbsence.proc.sql の保存] をクリックします。

    次の手順では、コード レビューで受け取ったフィードバックを解決します。

コード レビューのフィードバックの解決

別のチーム メンバと共にコードをレビューしたときに、いくつかのベスト プラクティスに関するフィードバックを受け取りました。 データベース コードにスタティック コード分析を実行すると、SELECT * の使用により警告が発生するので、SELECT * は使用しないように求められました。 また、ストアド プロシージャで使用した名前を完全修飾するように求められました。 最後に、AbsenceHistory テーブルの BeginDate カラムの名前を StartDate に変更するように求められました。

Dd193272.alert_note(ja-jp,VS.90).gifメモ :

コーディングの規則と要件は、チームによってさまざまです。 作成する T-SQL コードに自分の組織のコーディング規則を適用する必要があります。 このチュートリアルでは、2 つの問題について説明します。 また、通常は、1 つのデータベース オブジェクトだけでなくすべての新しいコードにこれらの手法を適用します (たとえば、新しいコード内のすべての名前を完全修飾するなど)。

このような種類の変更の場合も、実施が面倒でエラーが発生する可能性があります。 データベース リファクタリングを使用すると、データベース コード、テスト コード、およびデータ生成計画を短時間で簡単に更新できます。

ビュー定義の SELECT * を展開するには

  1. スキーマ ビューで、[vEmployeeAbsenceHistory] ビューをダブルクリックします。

    T-SQL エディタが開き、ビューの定義が表示されます。

  2. [データ] メニューの [リファクタ] をポイントし、[ワイルドカードの展開] をクリックします。

    [変更のプレビュー] ダイアログ ボックスが表示されます。

  3. [ワイルドカードの展開] ボックスの一覧の [a.*] をクリックします。

    [変更のプレビュー] ペインに、ビューに適用される更新が表示されます。

  4. [適用] をクリックします。

    変更がデータベース プロジェクトに適用されます。 次に、このチュートリアルの前の手順で定義したストアド プロシージャ内の名前を完全修飾します。

ストアド プロシージャ内の名前を完全修飾するには

  1. スキーマ ビューで、[uspRecordAbsence] ストアド プロシージャをダブルクリックします。

    T-SQL エディタが開き、ストアド プロシージャの定義が表示されます。

  2. [データ] メニューの [リファクタ] をポイントし、[完全修飾名] をクリックします。

    [変更のプレビュー] ダイアログ ボックスが表示され、プロジェクトにリファクタリング操作を適用した場合に行われるすべての変更が表示されます。

  3. 変更を確認して、[適用] をクリックします。

    変更がデータベース プロジェクトに適用されます。

BeginDate 列の名前を変更するには

  1. スキーマ ビューで、[AbsenceHistory] テーブルを展開し、[列] ノードを展開して、[BeginDate] 列をクリックします。

  2. [データ] メニューの [リファクタ] をポイントし、[名前の変更] をクリックします。

    [名前の変更] ダイアログ ボックスが表示されます。

    Dd193272.alert_note(ja-jp,VS.90).gifメモ :

    スキーマ ビューで [BeginDate] を右クリックし、[リファクタ] をポイントして、[名前の変更] をクリックする方法もあります。

  3. [新しい名前] に「StartDate」と入力します。

  4. [変更のプレビュー] チェック ボックスをオンにして、[OK] をクリックします。

    [変更のプレビュー] ダイアログ ボックスが表示され、データベース プロジェクトに名前の変更操作を適用した場合に行われるすべての変更が表示されます。

  5. [適用] をクリックします。

    変更が実行されます。列名が更新され、変更された各オブジェクトについて新しい列名がスキーマ ビューに表示されます。 このトピックの前の手順で指定した日付制約の定義を開くと、この制約も新しい列名を参照するように更新されています。

次の手順

ここで、通常はコード レビューを行ったチーム メンバと共に更新を確認してから、バージョン管理に変更をチェックインします。 この時点で、データベース スキーマをオフライン形式で表したデータベース プロジェクトの更新が完了します。 配置済みのスキーマを更新するには、そのデータベース プロジェクトをターゲット データベースに配置する必要があります。

sp_rename または ALTER の使用によりオブジェクトの名前変更や移動が可能である場合、リファクタリング操作をデータベース プロジェクトに適用すると、その操作に関する情報がリファクタリング ログ ファイルに記録されます。 このチュートリアルでは、ログ ファイルの名前は RefactorAdventureWorks.refactorlog です。 リファクタリング ログ ファイルは、配置時に、リファクタリングの変更の意図を保持するために使用されます。 たとえば、列の名前を変更すると、リファクタリング ログにその変更が記録されます。 配置時に、その情報が、古い名前の列とそこに含まれるデータが削除されて新しい名前の空の列が作成されるのを防ぎます。 リファクタリングを使用する場合は、データを保持するために配置前スクリプトと配置後スクリプトにステートメントを追加する必要はありません。

参照

処理手順

方法 : データベース リファクタリングの変更を配置する

概念

別のスキーマにデータベース オブジェクトを移動する

データベース オブジェクトの名前の完全修飾

SELECT ステートメントでワイルドカード文字を展開する

スタティック分析によるデータベース コードの改善

その他の技術情報

チュートリアル : データベース リファクタリングの変更の配置