SQL Server : Idempotence d’un script SQL Server

Dans le métier d’un développeur FullStack .Net, nous sommes amenés à travailler régulièrement avec la base de données. Et plus particulièrement lors du déploiement de nouvelles fonctionnalités. Les livrables sont souvent l’ensemble des fichiers binaires des différents projets (Front Office, site Back office, site API etc..) mais aussi un fichier .sql, pour apporter les différentes opérations de modifications du modèle de données (ajout de table, modification de procédure stockée etc..). Et l’un des préceptes à ne pas oublier lors de la génération/écriture de ce script est l’idempotence ! Sous ce nom barbare, l’idempotence désigne :

L’idempotence signifie qu’une opération a le même effet qu’on l’applique une ou plusieurs fois.

Dans notre situation, cela signifie que notre script de déploiement, bien qu’il soit appliqué 1, 2 ou 20 fois doit conduire au même résultat.

Cette notion d’idempotence implique de rajouter ainsi des sécurités lors de l’écriture de notre script ou à rajouter après sa génération (en effet, le générateur de script Schema Compare de Visual Studio n’a pas ce genre de préoccupation). Car la création d’une procédure stockée via un CREATE PROCEDURE plantera simplement à la seconde exécution (car la procédure est déjà existante).

Ici, je vais vous lister une partie des différents tests d’existence d’objets au sein d’une base SQL Server.

Procédure Stockée

Plutôt que d’exécuter un DROP suivi d’un CREATE PROCEDURE, il est préférable de tester l’existence de la procédure afin d’effectuer un « faux » CREATE si la procédure n’existe pas et ensuite un ALTER avec le contenu de la procédure. Dans le cadre du DROP, si certaines règles de sécurité ont été implantés ensuite, vous perdrez ainsi ces notions de droits, ce qu’un ALTER n’affectera pas.

IF OBJECT_ID(‘maProcedure’,’P’) IS NULL — Test d’existence de la procédure

EXEC(‘CREATE PROCEDURE maProcedure as Select 1;’) — Création d’une procédure vide

GO

ALTER PROCEDURE maProcedure — Edition réelle de la procédure

AS BEGIN

— TEXTE DE LA PROCEDURE

END

 

Fonction

La même technique est applicable aux autres objets, en modifiant bien sûr le code de test d’existence de l’objet.

IF NOT EXISTS(SELECT * FROM dbo.sysobjects WHERE name = ‘maFonction’ AND type IN (‘TF’, ‘FN’, ‘FS’, ‘FT’, ‘AF’)) — Test d’existence de la fonction

EXEC (‘CREATE FUNCTION maFonction() RETURNS int AS BEGIN RETURN 1 END’) — Création de la fonction vide

GO

ALTER FUNCTION maFonction — Edition réelle de la fonction

RETURNS INT

AS BEGIN

— TEXTE DE LA FONCTION

END

Correspondance des types de fonction (doc MSDN) :

  • TF : SQL table-valued-function (à partir de SQL SERVER 2012)
  • FN : SQL scalar function
  • FS : Assembly (CLR) scalar-function
  • FT : Assembly (CLR) table-valued function
  • AF : Aggregate function (CLR)

Vue

IF OBJECT_ID(‘maVue’, ‘v’) IS NULL — Test d’existence de la vue

EXEC (‘CREATE VIEW maVue AS SELECT 1 AS maColonneDeVue’) — Création de la vue vide

GO

ALTER VIEW maVue — Edition réelle de la vue

AS

— TEXTE DE LA VUE

END

 

Table

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA =’monSchema’ AND TABLE_NAME = ‘maTable’) — Test d’existence de la table

CREATE TABLE maTable (maColonneDeTable int) — TEXTE DE CREATION DE LA TABLE

Colonne

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ‘maTable’ AND COLUMN_NAME = ‘maColonneDeTable2’) — Test d’existence de la colonne

ALTER TABLE maTable ADD maColonneDeTable2 INT — TEXTE DE LA CREATION DE LA COLONNE

Et on peut même vérifier au cas où si la table que l’on cherche existe aussi bien avant 🙂

Contrainte

Dans le cas d’une contrainte, l’opération est plus simple car ici pas de système de permissions. Un simple test d’existence suivi d’un DROP suffit.

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_NAME =’maContrainte’) — Contrainte de Clé Etrangère

DROP CONSTRAINT maContrainte

 

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS WHERE CONSTRAINT_NAME =’maContrainte’) — Contrainte de Check

DROP CONSTRAINT maContrainte

%d blogueurs aiment cette page :