Partager via

Procédure pas à pas : appliquer des techniques de refactorisation de base de données

  • Article

Grâce à la refactorisation dans Visual Studio Premium ou Visual Studio Ultimate, vous pouvez réduire le nombre de tâches répétitives et sujettes aux erreurs que vous devez effectuer pendant la conception et la mise à jour d'un schéma de base de données. Par exemple, vous pouvez utiliser la refactorisation pour mettre à jour des références à un objet de base de données si son nom doit changer ou si l'objet doit être déplacé dans un schéma différent. Si vous adoptez cette approche, vous pouvez augmenter à la fois la vitesse et l'exactitude des modifications habituelles de conception de la base de données.

Cette procédure pas à pas illustre un scénario typique du développement de base de données. Pour ajouter des fonctions à une base de données existante, vous devez exécuter l'implémentation initiale puis l'examiner avec un autre membre de l'équipe. Pendant l'examen, vous identifierez plusieurs problèmes que vous devez corriger avant d'archiver les modifications. Vous utiliserez ensuite diverses techniques de refactorisation pour modifier le schéma.

Cette procédure pas à pas décrit les tâches suivantes :

  • Importation de votre schéma de base de données

  • Implémentation d'une tâche de développement de base de données typique

  • Correction d'une erreur de codage

  • Réalisation de la tâche de développement

  • Traitement des commentaires de révision du code

Composants requis

Pour exécuter cette procédure pas à pas, vous devez disposer des éléments suivants :

  • Visual Studio Premium ou Visual Studio Ultimate

  • Accès en lecture seule à un serveur de base de données sur lequel la base de données AdventureWorks2008 est installée.

Importation de votre schéma de base de données

Avant de modifier un schéma dans un environnement de travail en équipe, vous extrayez généralement un projet existant de votre système de contrôle de version. Pour cette procédure pas à pas, vous créez un projet de base de données et importez le schéma à partir de l'exemple de base de données AdventureWorks2008.

Pour créer un projet de base de données

  1. Dans le menu Fichier, pointez sur Nouveau, puis cliquez sur Fichier.

    La boîte de dialogue Nouveau projet s'affiche.

  2. Sous Modèles installés, développez le nœud Base de données, puis cliquez sur le nœud SQL Server.

  3. Dans la liste des modèles, cliquez sur Projet de base de données SQL Server 2008.

  4. Dans Nom, tapez RefactorAdventureWorks et cliquez sur OK.

    Une solution est créée avec un projet de base de données vide, nommé RefactorAdventureWorks, pour votre projet de test (également appelé bac à sable (sandbox)).

    Ensuite, importez le schéma à partir d'une instance déployée de la base de données AdventureWorks.

Pour importer la base de données AdventureWorks

  1. Dans l'Explorateur de solutions ou la Vue Schéma, cliquez sur RefactorAdventureWorks.

  2. Dans le menu Projet, cliquez sur Importer des objets et des paramètres de base de données.

    Notes

    Vous pouvez également cliquer avec le bouton droit sur RefactorAdventureWorks, puis cliquer sur Importer des objets et des paramètres de base de données.

    L'Assistant Importation de base de données apparaît.

  3. Dans la liste Connexion de base de données source, cliquez sur la connexion qui correspond à la base de données AdventureWorks.

    Important

    Si vous ne vous êtes pas encore connecté à cette base de données, vous devez d'abord cliquer sur Nouvelle connexion pour créer une connexion. Pour plus d'informations, consultez Comment : créer une connexion de base de données.

  4. Cliquez sur Démarrer, puis sur Terminer lorsque les objets et les paramètres ont été importés.

    Lorsque le schéma est importé, les éléments de projet qui correspondent aux objets dans votre base de données apparaissent sous le projet de base de données dans l'Explorateur de solutions et la vue Schéma.

    Notes

    Bien que vous vous soyez connecté à la base de données pour importer le schéma, vous êtes maintenant déconnecté et travaillez hors connexion.

    Ensuite, vous allez effectuer une tâche typique de développement de base de données, en ajoutant du code au projet de base de données.

Implémentation d'une tâche de développement de base de données typique

Pour cette tâche, vous avez dû implémenter une prise en charge de suivi d'historique des absences de chaque employé. Dans le cadre de cette tâche, vous devez créer les objets suivants :

  • Table permettant de suivre les dates de début et de fin de chaque absence ainsi que le type d'absence (congé, maladie, devoir de juré, congé mobile, congé non rémunéré ou deuil). Vous ajouterez la table au schéma Person lors d'une étape ultérieure de cette procédure. Les données de cette table possèdent les restrictions suivantes :

    • Les absences ne sont jamais supérieures à cinq jours. (Les absences plus longues sont fractionnées en plusieurs entrées.)

    • Les absences possèdent des plages de dates valides.

    • La table est mise en rapport avec la table Employee à l'aide de l'EmployeeID.

  • Vue qui affiche l'historique complet des absences de chaque employé.

  • Procédure stockée qui enregistre une absence et met à jour les heures de congé de l'employé si l'absence est de type congé.

Pour préparer l'ajout de code

  1. Dans le menu Affichage, cliquez sur Vue Schéma de base de données.

  2. Dans la Vue Schéma, développez le nœud RefactorAdventureWorks.

  3. Si la Vue Schéma est triée par type d'objet, cliquez sur Modifier le regroupement des objets dans la barre d'outils.

    Notes

    La Vue Schéma est triée par type d'objet si elle contient des nœuds nommés Tables et Vues. Si la Vue Schéma contient un nœud nommé Schémas, vous pouvez passer à la procédure suivante.

    Ensuite, ajoutez la table AbsenceHistory au projet de base de données.

Pour ajouter la table AbsenceHistory

  1. Dans la Vue Schéma, développez le nœud Schémas, développez le sous-nœud Personne, puis le sous-nœud Tables.

  2. Cliquez avec le bouton droit sur le sous-nœud Tables, pointez sur Ajouter, puis cliquez sur Table.

    La boîte de dialogue Ajouter un nouvel élément s'affiche alors.

  3. Dans la zone Nom, tapez AbsenceHistory, puis cliquez sur Ajouter.

    L'éditeur Transact-SQL s'ouvre, en affichant la définition pour la table AbsenceHistory.

  4. Dans l'éditeur Transact-SQL, remplacez la définition de table existante par le code suivant :

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

  5. Dans le menu Fichier, cliquez sur Enregistrer Person.AbsenceHistory.table.sql.

    Ensuite, ajoutez une contrainte de validation à la table AbsenceHistory.

Pour ajouter la contrainte de validation à la table

  1. Dans la Vue Schéma, développez le nœud AbsenceHistory.

  2. Cliquez avec le bouton droit sur le nœud Contraintes, pointez sur Ajouter et cliquez sur Contrainte de validation.

    La boîte de dialogue Ajouter un nouvel élément s'affiche alors.

  3. Dans Nom, tapez CK_AbsenceHistory_ValidDates et cliquez sur Ajouter.

    L'éditeur Transact-SQL s'ouvre, en affichant la définition pour la contrainte.

  4. Dans l'éditeur Transact-SQL, remplacez la définition de contrainte existante par le code suivant :

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

    Ce code définit une contrainte pour la table pour s'assurer que la date de fin est postérieure à la date de début et que le delta entre ces deux dates ne dépasse pas cinq jours.

  5. Dans le menu Fichier, cliquez sur Enregistrer Person.AbsenceHistory.CK_AbsenceHistory_ValidDates.chkconst.sql.

    Ensuite, ajoutez une clé étrangère à la table AbsenceHistory.

Pour ajouter la définition de clé étrangère

  1. Dans la Vue Schéma, cliquez avec le bouton droit sur le nœud Clés, pointez sur Ajouter et cliquez sur Clé étrangère.

    La boîte de dialogue Ajouter un nouvel élément s'affiche alors.

  2. Dans Nom, tapez FK_AbsenceHistory_Employee_EmployeeID et cliquez sur Ajouter.

    L'éditeur Transact-SQL s'ouvre, en affichant la définition pour la clé étrangère.

  3. Dans l'éditeur Transact-SQL, remplacez la définition existante pour la clé étrangère par le code suivant :

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

    Ce code définit une relation de clé étrangère entre l'EmployeeID dans la table AbsenceHistory et le BusinessEntityID dans la table [HumanResources].[Employee].

  4. Dans le menu Fichier, cliquez sur Enregistrer Person.AbsenceHistory.FK_AbsenceHistory_Employee_EmployeeID.fkey.sql.

    À ce stade, vous réalisez que la table devrait se trouver en fait dans le schéma HumanResources. Dans la procédure suivante, vous corrigerez cette erreur.

Correction d'une erreur de codage

Étant donné que vous avez déjà défini des contraintes et des clés étrangères, le déplacement d'une table et de ses objets connexes vers un schéma différent serait généralement très long. Vous pouvez utiliser la refactorisation de base de données pour déplacer rapidement et facilement la table et ses objets connexes vers le schéma correct avant de continuer.

Pour déplacer la table AbsenceHistory vers le schéma HumanResources

  1. Dans la Vue Schéma, cliquez avec le bouton droit sur la table AbsenceHistory, pointez sur Refactoriser, puis cliquez sur Déplacer vers le schéma.

    La boîte de dialogue Déplacer le schéma s'affiche.

  2. Dans la liste Nouveau schéma, cliquez sur HumanResources.

  3. Vérifiez que la case à cocher Aperçu des modifications est activée, et cliquez sur OK.

    La boîte de dialogue Aperçu des modifications apparaît. Vous pouvez ensuite examiner les modifications avant de les appliquer à votre projet de base de données.

  4. Cliquez sur Appliquer.

    Les modifications de refactorisation sont appliquées à votre projet de base de données. La table AbsenceHistory est déplacée du schéma Person vers le schéma HumanResources, avec tous les objets connexes.

  5. Dans la Vue Schéma, développez le nœud de schéma HumanResources, puis développez le nœud Tables.

    La table AbsenceHistory s'affiche dans le schéma correct.

    Notes

    Lorsque vous avez déplacé les objets vers le schéma correct, vous n'avez pas changé les noms des fichiers dans lesquels les objets sont définis. Si vous souhaitez mettre à jour les noms de fichiers, vous devez les renommer dans l'Explorateur de solutions.

    Ensuite, vous allez effectuer les étapes restantes de la tâche de développement.

Réalisation de la tâche de développement

Maintenant que vous avez corrigé le schéma pour la table, vous devez créer les objets suivants :

  • Vue qui affiche, pour chaque employé, l'historique complet des absences.

  • Procédure stockée qui enregistre une absence et met à jour les heures de congé de l'employé si l'absence est de type congé.

Pour ajouter la vue vEmployeeAbsenceHistory

  1. Dans la Vue Schéma, dans le schéma HumanResources, développez le nœud Vues.

  2. Cliquez avec le bouton droit sur le nœud Vues, pointez sur Ajouter, puis cliquez sur Vue.

    La boîte de dialogue Ajouter un nouvel élément s'affiche alors.

  3. Dans la zone Nom, tapez vEmployeeAbsenceHistory, puis cliquez sur Ajouter.

    L'éditeur Transact-SQL s'ouvre, en affichant la définition pour la vue.

  4. Dans l'éditeur Transact-SQL, remplacez la définition de vue existante par le code suivant :

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

    Ce code définit une vue qui retourne des données à partir d'une combinaison des tables Employee, Contact et AbsenceHistory.

  5. Dans le menu Fichier, cliquez sur Enregistrer HumanResources.vEmployeeAbsenceHistory.view.sql.

    Ensuite, ajoutez une procédure stockée.

Pour ajouter la procédure stockée uspRecordAbsence

  1. Dans la Vue Schéma, dans le schéma HumanResources, développez le nœud Programmabilité, puis le nœud Procédures stockées.

  2. Cliquez avec le bouton droit sur le nœud Procédures stockées, pointez sur Ajouter, puis cliquez sur Procédure stockée.

    La boîte de dialogue Ajouter un nouvel élément s'affiche alors.

  3. Dans la zone Nom, tapez uspRecordAbsence, puis cliquez sur Ajouter.

    L'éditeur Transact-SQL s'ouvre, en affichant la définition pour la procédure stockée.

  4. Dans l'éditeur Transact-SQL, remplacez la définition existante pour la procédure stockée par le code suivant :

    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;

    Ce code définit une procédure stockée qui ajoute une ligne dans la table AbsenceHistory et met à jour le champ VacationHours dans la table Employee si le type d'absence est « V ».

  5. Dans le menu Fichier, cliquez sur Enregistrer dbo.uspRecordAbsence.proc.sql.

    Dans la procédure suivante, vous traiterez les commentaires que vous recevez dans une révision du code.

Traitement des commentaires de révision du code

Lorsque vous avez examiné votre code avec un autre membre de l'équipe, vous avez reçu des commentaires sur plusieurs meilleures pratiques. Il vous a été demandé d'éviter d'utiliser SELECT * car il génère des avertissements si vous exécutez une analyse du code statique sur votre code de base de données. De plus, vous avez dû attribuer des noms qualifiés complets dans la procédure stockée. Enfin, vous avez dû renommer la colonne BeginDate dans la table AbsenceHistory en StartDate.

Notes

Les standards et impératifs de codage varient entre les équipes. Vous devez appliquer les standards de codage de votre organisation au code Transact-SQL que vous écrivez. Cette procédure pas à pas illustre deux problèmes. De plus, vous appliqueriez généralement ces techniques à tout nouveau code (par exemple, en attribuant des noms qualifiés complets dans le nouveau code), et pas seulement à un objet de base de données unique.

De même, les modifications de ce type peuvent s'avérer laborieuses et sujettes aux erreurs lors de leur implémentation. Vous pouvez utiliser la refactorisation de base de données pour mettre à jour rapidement et facilement votre code de base de données, votre code de test et vos plans de génération de données.

Pour développer SELECT * dans la définition de la vue

  1. Dans la Vue Schéma, double-cliquez sur la vue vEmployeeAbsenceHistory.

    L'éditeur Transact-SQL s'ouvre, en affichant la définition de la vue.

  2. Dans le menu Données, pointez sur Refactoriser, puis cliquez sur Développer les caractères génériques.

    La boîte de dialogue Aperçu des modifications apparaît.

  3. Dans la liste Développer les caractères génériques, cliquez sur a.*.

    Le volet Aperçu des modifications affiche les mises à jour qui s'appliqueront à la vue.

  4. Cliquez sur Appliquer.

    Les modifications sont appliquées à votre projet de base de données. Ensuite, attribuez des noms qualifiés complets dans la procédure stockée que vous avez définie précédemment dans cette procédure pas à pas.

Pour attribuer des noms qualifiés complets dans la procédure stockée

  1. Dans la Vue Schéma, double-cliquez sur la procédure stockée uspRecordAbsence.

    L'éditeur Transact-SQL s'ouvre, en affichant la définition de la procédure stockée.

  2. Dans le menu Données, pointez sur Refactoriser, puis cliquez sur Noms qualifiés complets.

    La boîte de dialogue Aperçu des modifications apparaît et affiche toutes les modifications qui seront effectuées si vous appliquez l'opération de refactorisation à votre projet.

  3. Après avoir examiné les modifications, cliquez sur Appliquer.

    Les modifications sont appliquées à votre projet de base de données.

Pour renommer la colonne BeginDate

  1. Dans la Vue Schéma, développez la table AbsenceHistory, développez le nœud Colonnes et cliquez sur la colonne BeginDate.

  2. Dans le menu Données, pointez sur Refactoriser, puis cliquez sur Renommer.

    La boîte de dialogue Renommer s'affiche.

    Notes

    Vous pouvez également cliquer avec le bouton droit sur BeginDate dans la Vue Schéma, pointer sur Refactoriser, puis cliquer sur Renommer.

  3. Dans Nouveau nom, tapez StartDate.

  4. Activez la case à cocher Aperçu des modifications, puis cliquez sur OK.

    La boîte de dialogue Aperçu des modifications apparaît et affiche toutes les modifications qui seront effectuées si vous appliquez l'opération de changement de nom à votre projet.

  5. Cliquez sur Appliquer.

    Les modifications sont apportées. Le nom de colonne est mis à jour et le nouveau nom de colonne apparaît dans la vue Schéma pour chaque objet qui a été mis à jour. Si vous ouvrez la définition pour la contrainte de date spécifiée précédemment dans cette rubrique, la contrainte a également été mise à jour pour référencer le nouveau nom de colonne.

Étapes suivantes

À ce stade, vous examineriez généralement les mises à jour avec le membre de l'équipe ayant exécuté la révision du code, puis vous archiveriez vos modifications dans le contrôle de version. Pour l'instant, vous avez mis à jour votre projet de base de données, représentation hors connexion de votre schéma de base de données. Vous devez déployer ce projet de base de données dans une base de données cible pour mettre à jour le schéma déployé.

Lorsque vous appliquez une opération de refactorisation à votre projet de base de données, les informations sur cette opération sont enregistrées dans un fichier journal de refactorisation si vous pouvez renommer ou déplacer l'objet à l'aide de sp_rename ou ALTER. Dans cette procédure pas à pas, le fichier journal est nommé RefactorAdventureWorks.refactorlog. Le fichier journal de refactorisation est utilisé au moment du déploiement pour essayer de conserver l'objectif de vos modifications de refactorisation. Par exemple, le journal de refactorisation enregistrera vos modifications si vous renommez une colonne. Au moment du déploiement, ces informations empêchent la colonne dotée de l'ancien nom d'être supprimée, avec toutes les données qu'elle contient, et de créer une colonne vide avec un nouveau nom. Si vous utilisez la refactorisation, vous n'avez pas besoin d'ajouter d'instructions aux scripts de prédéploiement et de post-déploiement pour conserver les données.

Voir aussi

Tâches

Comment : déployer des modifications de refactorisation d'une base de données

Concepts

Déplacer un objet de base de données vers un autre schéma

Qualifier complètement le nom d'objets de base de données

Développer les caractères génériques dans les instructions SELECT

Analyse du code de base de données pour en améliorer la qualité