チュートリアル: データベース リファクタリング手法の適用
Visual Studio Premium または Visual Studio Ultimate のリファクタリングを使用することで、データベース スキーマを設計および更新するときに実行する必要のある、反復タスクとエラーが発生する可能性のあるタスクの数を減らすことができます。 たとえば、リファクタリングを使用することで、データベース オブジェクトの名前の変更やオブジェクトの別のスキーマへの移動が必要な場合に、そのデータベース オブジェクトへの参照を更新できます。 この方法を採用すると、データベース設計に対する日常的な変更作業をより迅速で正確に実行できます。
このチュートリアルでは、データベース開発の一般的なシナリオについて説明します。 既存のデータベースに機能を追加するには、初期実装を実行し、他のチーム メンバーとそれをレビューする必要があります。 レビューの実行時に、変更をチェックインする前に対処する必要のある問題を特定します。 次に、さまざまなリファクタリング手法を使用して、スキーマを変更します。
このチュートリアルでは、次の作業について説明します。
データベース スキーマのインポート
一般的なデータベース開発タスクのインポート
コード エラーの修正
開発タスクの完了
コード レビュー フィードバックへの対処
必須コンポーネント
このチュートリアルを実行するための要件は次のとおりです。
Visual Studio Premium または Visual Studio Ultimate
AdventureWorks2008 データベースがインストールされているデータベース サーバーへの読み取り専用アクセス。
データベース スキーマのインポート
チーム環境のスキーマを変更する前に、通常、バージョン管理システムから既存のプロジェクトをチェックアウトします。 このチュートリアルでは、データベース プロジェクトを作成し、AdventureWorks2008 サンプル データベースからスキーマをインポートします。
データベース プロジェクトを作成するには
[ファイル] メニューの [新規作成] をポイントし、[プロジェクト] をクリックします。
[新しいプロジェクト] ダイアログ ボックスが表示されます。
[インストールされたテンプレート] で、[データベース] ノードを展開し、[SQL Server] ノードをクリックします。
テンプレートの一覧の [SQL Server 2008 データベース プロジェクト] をクリックします。
[名前] ボックスに「RefactorAdventureWorks」と入力し、[OK] をクリックします。
サンドボックスとも呼ばれるテスト プロジェクトとして、RefactorAdventureWorks という名前の空のデータベース プロジェクトを含むソリューションが作成されます。
次に、配置された AdventureWorks データベースのインスタンスからスキーマをインポートします。
AdventureWorks データベースをインポートするには
ソリューション エクスプローラーまたはスキーマ ビューで、[RefactorAdventureWorks] をクリックします。
[プロジェクト] メニューの [データベースのオブジェクトと設定のインポート] をクリックします。
注意
[RefactorAdventureWorks] を右クリックし、[データベースのオブジェクトと設定のインポート] をクリックすることもできます。
データベースのインポート ウィザードが表示されます。
[ソース データベース接続] ボックスの一覧で、AdventureWorks データベースに対応する接続をクリックします。
重要
データベースに接続していない場合は、最初に [新しい接続] をクリックしてデータベースへの接続を作成する必要があります。 詳細については、「方法 : データベース接続を作成する」を参照してください。
[開始] をクリックし、オブジェクトと設定がインポートされたら、[完了] をクリックします。
スキーマがインポートされると、データベースのオブジェクトに対応するプロジェクト項目がソリューション エクスプローラーおよびスキーマ ビューでデータベース プロジェクトの下に表示されます。
注意
スキーマをインポートするためにデータベースに接続していても、この時点では接続が切断され、オフラインでの作業になります。
次に、データベース開発における一般的なタスクである、データベース プロジェクトへのコードの追加を行います。
一般的なデータベース開発タスクのインポート
このタスクでは、各従業員の休暇履歴を追跡するサポートの実装を求められています。 このタスクの一部として、次のオブジェクトを作成する必要があります。
各休暇期間の開始日と終了日および休暇の種類 (休暇、病気、陪審義務、変動休日、無給休暇、または忌引) を追跡するテーブル。 このチュートリアルの後半で、このテーブルを Person スキーマに追加します。 テーブル内のデータには次の制限があります。
休暇期間は 5 日間を超えない (これより長い休暇期間は、複数のエントリに分割されます)。
休暇期間は有効な日付の範囲で指定する。
テーブルは、EmployeeID によって Employee テーブルに関連付けられる。
各従業員のすべての休暇履歴を表示するビュー。
休暇を記録し、休暇の種類が休暇の場合に従業員の休暇時間を更新するストアド プロシージャ。
コード追加の準備をするには
[表示] メニューの [スキーマ ビュー] をクリックします。
スキーマ ビューで、[RefactorAdventureWorks] ノードを展開します。
スキーマ ビューがオブジェクトの種類別に並べ替えられている場合、ツール バーの [オブジェクトのグループ化の変更] をクリックします。
注意
スキーマ ビューに [テーブルとビュー] という名前のノードが含まれている場合、ビューはオブジェクトの種類別に並べ替えられます。 スキーマ ビューに [スキーマ] という名前のノードが含まれている場合は、次の手順に進んでください。
次に、データベース プロジェクトに AbsenceHistory テーブルを追加します。
AbsenceHistory テーブルを追加するには
スキーマ ビューで、[スキーマ] ノードを展開し、[人] サブノードを展開します。次に、[テーブル] サブノードを展開します。
[テーブル] サブノードを右クリックし、[追加] をポイントします。次に、[テーブル] をクリックします。
[新しい項目の追加] ダイアログ ボックスが表示されます。
[名前] ボックスに、「AbsenceHistory」と入力し、[追加] をクリックします。
Transact-SQL エディターが開き、AbsenceHistory テーブルの定義が表示されます。
Transact-SQL エディターで、既存のテーブル定義を次のコードに置き換えます。
CREATE TABLE [Person].[AbsenceHistory] ( [EmployeeID] INT NOT NULL, [BeginDate] DateTime NOT NULL, [EndDate] DateTime NOT NULL, [AbsenceType] NCHAR(1) NOT NULL );[ファイル] メニューの [Person.AbsenceHistory.table.sql の保存] をクリックします。
次に、AbsenceHistory テーブルに CHECK 制約を追加します。
テーブルに CHECK 制約を追加するには
スキーマ ビューで、[AbsenceHistory] ノードを展開します。
[制約] ノードを右クリックし、[追加] をポイントします。次に、[CHECK 制約] をクリックします。
[新しい項目の追加] ダイアログ ボックスが表示されます。
[名前] ボックスに「CK_AbsenceHistory_ValidDates」と入力し、[追加] をクリックします。
Transact-SQL エディターが開き、制約の定義が表示されます。
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 テーブルに外部キーを追加します。
外部キー定義を追加するには
スキーマ ビューで、[キー] ノードを右クリックし、[追加] をポイントします。次に、[外部キー] をクリックします。
[新しい項目の追加] ダイアログ ボックスが表示されます。
[名前] ボックスに、「FK_AbsenceHistory_Employee_EmployeeID」と入力し、[追加] をクリックします。
Transact-SQL エディターが開き、外部キーの定義が表示されます。
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 の間の外部キーのリレーションシップを定義します。
[ファイル] メニューの [Person.AbsenceHistory.FK_AbsenceHistory_Employee_EmployeeID.fkey.sql の保存] をクリックします。
この時点で、このテーブルは HumanResources スキーマに配置する必要があることがわかります。 次の手順では、このエラーを修正します。
コード エラーの修正
既に制約と外部キーを定義してあるため、通常、テーブルとそれに関連するオブジェクトを別のスキーマに移動するには長い時間がかかります。 データベース リファクタリングを使用すると、テーブルとそれに関連するオブジェクトをすばやく簡単に正しいスキーマに移動して、作業を続行できます。
AbsenceHistory テーブルを HumanResources スキーマに移動するには
スキーマ ビューで、[AbsenceHistory] テーブルを右クリックし、[リファクター] をポイントします。次に、[スキーマへ移動] をクリックします。
[移動スキーマ] ダイアログ ボックスが表示されます。
[新しいスキーマ] ボックスの一覧の [HumanResources] をクリックします。
[変更のプレビュー] チェック ボックスがオンになっていることを確認して、[OK] をクリックします。
[変更のプレビュー] ダイアログ ボックスが表示されます。 その変更を、データベース プロジェクトに適用する前に確認できます。
[適用] をクリックします。
リファクタリングの変更が、データベース プロジェクトに適用されます。 AbsenceHistory テーブルが、関連するすべてのオブジェクトと共に Person スキーマから HumanResources スキーマに移動します。
スキーマ ビューで、[HumanResources] スキーマ ノードを展開し、[テーブル] ノードを展開します。
正しいスキーマに AbsenceHistory テーブルが表示されます。
注意
オブジェクトを正しいスキーマに移動したときに、オブジェクトが定義されているファイルの名前を変更しませんでした。 ファイル名を更新する場合は、ソリューション エクスプローラーで名前を変更する必要があります。
次に、開発タスクの残りの手順を完了します。
開発タスクの完了
テーブルのスキーマを修正したので、次のオブジェクトを作成する必要があります。
各従業員のすべての休暇履歴を表示するビュー。
休暇を記録し、休暇の種類が休暇の場合に従業員の休暇時間を更新するストアド プロシージャ。
vEmployeeAbsenceHistory ビューを追加するには
スキーマ ビューで、HumanResources スキーマの [ビュー] ノードを展開します。
[ビュー] ノードを右クリックし、[追加] をポイントします。次に、[ビュー] をクリックします。
[新しい項目の追加] ダイアログ ボックスが表示されます。
[ファイル名] ボックスに「vEmployeeAbsenceHistory」と入力し、[追加] をクリックします。
Transact-SQL エディターが開き、ビューの定義が表示されます。
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 の各テーブルからデータを返すビューを定義します。
[ファイル] メニューの [HumanResources.vEmployeeAbsenceHistory.view.sql の保存] をクリックします。
次に、ストアド プロシージャを追加します。
uspRecordAbsence ストアド プロシージャを追加するには
スキーマ ビューで、HumanResources スキーマの [プログラミング] ノードを展開し、[ストアド プロシージャ] ノードを展開します。
[ストアド プロシージャ] ノードを右クリックし、[追加] をポイントします。次に、[ストアド プロシージャ] をクリックします。
[新しい項目の追加] ダイアログ ボックスが表示されます。
[名前] ボックスに「uspRecordAbsence」と入力し、[追加] をクリックします。
Transact-SQL エディターが開き、ストアド プロシージャの定義が表示されます。
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 テーブルに行を追加し、休暇の種類が 'V' の場合に Employee テーブルの VacationHours フィールドを更新するストアド プロシージャを定義します。
[ファイル] メニューの [dbo.uspRecordAbsence.proc.sql の保存] をクリックします。
次の手順では、コード レビューで受け取ったフィードバックに対処します。
コード レビュー フィードバックへの対処
チームの他のメンバーのコードをレビューしたときに、いくつかのベスト プラクティスに関するフィードバックを受け取りました。 データベース コードに対してスタティック コード分析を実行すると SELECT * によって警告が発生するため、その使用を避けるように求められました。 また、ストアド プロシージャで使用する名前を完全修飾するように求められました。 最後に、AbsenceHistory テーブルの BeginDate 列の名前を "StartDate" に変更するように求められました。
注意
コーディング規則と要件はチームごとに異なります。 記述する Transact-SQL コードに組織のコーディング規則を適用する必要があります。 このチュートリアルでは、2 つの問題について説明します。 また、通常、これらの手法は、単一のデータベース オブジェクトではなくすべての新しいコードに適用します (たとえば、新しいコードのすべての名前の完全修飾)。
これらの種類の変更は、実装が面倒であるだけでなく間違いのもとになる可能性もあります。 データベース リファクタリングを使用すると、データベース コード、テスト コード、およびデータ生成の計画をすばやく簡単に更新できます。
ビュー定義の SELECT * を展開するには
スキーマ ビューで、[vEmployeeAbsenceHistory] ビューをダブルクリックします。
Transact-SQL エディターが開き、ビューの定義が表示されます。
[データ] メニューの [リファクター] をポイントし、[ワイルドカードの展開] をクリックします。
[変更のプレビュー] ダイアログ ボックスが表示されます。
[ワイルドカードの展開] ボックスの一覧の [a.*] をクリックします。
[変更のプレビュー] ウィンドウに、ビューに適用する更新内容が表示されます。
[適用] をクリックします。
変更がデータベース プロジェクトに適用されます。 次に、このチュートリアルの前の手順で定義したストアド プロシージャの名前を完全修飾します。
ストアド プロシージャの名前を完全修飾するには
スキーマ ビューで、uspRecordAbsence ストアド プロシージャをダブルクリックします。
Transact-SQL エディターが開き、ストアド プロシージャの定義が表示されます。
[データ] メニューの [リファクター] をポイントし、[完全修飾名] をクリックします。
[変更のプレビュー] ダイアログ ボックスが開き、プロジェクトにリファクタリング操作を適用すると実行されるすべての変更が表示されます。
変更内容を確認した後、[適用] をクリックします。
変更がデータベース プロジェクトに適用されます。
BeginDate 列の名前を変更するには
スキーマ ビューで、[AbsenceHistory] テーブルを展開し、[列] ノードを展開します。次に、[BeginDate] 列をクリックします。
[データ] メニューの [リファクター] をポイントし、[名前の変更] をクリックします。
[名前の変更] ダイアログ ボックスが表示されます。
注意
スキーマ ビューで [BeginDate] を右クリックし、[リファクター] をポイントし、[名前の変更] をクリックする方法もあります。
[名前] ボックスに「StartDate」と入力します。
[変更のプレビュー] チェック ボックスをオンにして、[OK] をクリックします。
[変更のプレビュー] ダイアログ ボックスが開き、データベース プロジェクトに名前変更操作を適用すると実行されるすべての変更が表示されます。
[適用] をクリックします。
変更が実行されます。 列名が更新され、変更された各オブジェクトについて新しい列名がスキーマ ビューに表示されます。 このトピックの前半で指定した日付の制約の定義を開くと、その制約も新しい列名を参照するように更新されます。
次の手順
通常、この時点で、コード レビューを行うチーム メンバーと共に更新内容をレビューした後、変更をバージョン コントロールにチェックインします。 このとき、データベース スキーマのオフライン形式であるデータベース プロジェクトが更新されます。 配置されたスキーマを更新するには、データベース プロジェクトを対象のデータベースに配置する必要があります。
sp_rename または ALTER を使用してオブジェクトの名前を変更したりオブジェクトを移動したりできる場合、リファクタリング操作をデータベース プロジェクトに適用するときに、その操作に関する情報がリファクタリング ログ ファイルに記録されます。 このチュートリアルでは、ログ ファイルの名前は RefactorAdventureWorks.refactorlog です。 リファクタリング ログ ファイルは、配置時にリファクタリングによる変更の意図を維持するために使用されます。 たとえば、列の名前を変更した場合、その変更がリファクタリング ログに記録されます。 配置時に、その情報によって、古い名前の列がその列に含まれているデータと共にドロップされるのを防止し、空の列を新しい名前で作成します。 リファクタリングを使用すると、配置前スクリプトと配置後スクリプトにステートメントを追加してデータを保持する必要はありません。