Bienvenue chez les artisans développeurs !

MariaDB / MySQL - La souplesse des procédures stockées

Posté par Alexis
Dernière révision :

Introduction

Les procédures stockées permettent une découplage entre la structure de la base de données MariaDB / MySQL et leurs méthodes d'accès.
Je vous invite à découvrir sur ce billet un exemple de modification de structure de base avec 0 downtime.

Le problème

Imaginez avoir confié le développement de la gestion des personnes à un jeune développeur inexpérimenté. Ce dernier a évidemment créer un champ Civilité dans la table Personnes parce que les jointures c'est lent 😕 et puis ça marche jamais 😕 et puis qu'il n'a pas appris à l'école 😈 (bon c'est un exemple mais je suis sûr qu'une analogie vous vient en tête hein 😉).
Mais vous n'avez pas eu le temps de vérifier et maintenant c'est en prod partout 😭

Vous allez devoir passer de ça :
Personnes ( IDPersonnes, Civilité, Prénom, Nom )
A ça :
Civilités ( IDCivilités, Libellé )
Personnes ( IDPersonnes, Nom, Prénom,IDCivilités )

Sans procédure stockée c'est une gageure.
Avec les procédures stockées, c'est très facile 😎
Suivez le guide !

Notes : Je pars du principe que vous avez des procédures d'accès/création à la table Personnes.


Accès :
  1. CREATE PROCEDURE pcdPersonnesDataA10(IN _IDPersonnes INT UNSIGNED)
  2.  
  3. BEGIN
  4.  
  5. SELECT
  6. P.Nom,
  7. P.Prénom,
  8. P.Civilité
  9. FROM
  10. Personnes P
  11. WHERE
  12. IDPersonnes=_IDPersonnes
  13. ;
  14.  
  15. END
Création :
  1. CREATE PROCEDURE pcdPersonnesAddA10(IN _Nom VARCHAR(50),IN _Prénom VARCHAR(50),IN _Civilité VARCHAR(3))
  2.  
  3. BEGIN
  4.  
  5. INSERT INTO
  6. Personnes(Nom,Prénom,Civilité)
  7. VALUES
  8. (_Nom,_Prénom,_Civilité)
  9. ;
  10.  
  11. END

La solution

1. La table 'Civilités'

On commence par créer la table Civilités et la remplir avec 'M', 'Mme' et 'Mlle'.

2. Le champ 'Personnes.IDCivilités'

Puis création du champ 'IDCivilités' dans la table 'Personnes' avec NULL en valeur par défaut et contrainte d'intégrité avec le champ 'Civilités.IDCivilités'.

3. Modification de pcdPersonnesAddA10()
  1. CREATE OR REPLACE PROCEDURE pcdPersonnesAddA10(IN _Nom VARCHAR(50),IN _Prénom VARCHAR(50),IN _Civilité VARCHAR(3))
  2.  
  3. BEGIN
  4.  
  5. INSERT INTO
  6. Personnes(Nom,Prénom,Civilité,IDCivilités)
  7. VALUES
  8. (_Nom,
  9. _Prénom,
  10. _Civilité,
  11. (SELECT C.IDCivilités FROM Civilités C WHERE C.Libellé=_Civilité)
  12. )
  13. ;
  14.  
  15. END
4. Update des données existantes
  1. UPDATE
  2. Personnes P
  3. INNER JOIN Civilités C ON C.Libellé=P.Civilité
  4. SET
  5. P.IDCivilités=C.IDCivilités
  6. ;
5. On modifie la procédure stockée de lecture
  1. CREATE OR REPLACE PROCEDURE pcdPersonneDataA10(IN _IDPersonnes INT UNSIGNED)
  2.  
  3. BEGIN
  4.  
  5. SELECT
  6. P.Nom,
  7. P.Prénom,
  8. C.Libellé AS Civilité
  9. FROM
  10. Personnes P
  11. INNER JOIN Civilités C ON C.IDCivilités=P.IDCivilités
  12. WHERE
  13. IDPersonnes=_IDPersonnes
  14. ;
  15.  
  16. END

Notez en ligne 8 l'alias Civilité afin que le résultat de la requête soit strictement identique avant/après modification.

6. Ultime modification de pcdPersonnesAddA10()

On a plus besoin d'écrire Personnes.Civilité

  1. CREATE OR REPLACE PROCEDURE pcdPersonnesAddA10(IN _Nom VARCHAR(50),IN _Prénom VARCHAR(50),IN _Civilité VARCHAR(3))
  2.  
  3. BEGIN
  4.  
  5. INSERT INTO
  6. Personnes(Nom,Prénom,IDCivilités)
  7. VALUES
  8. (_Nom,
  9. _Prénom,
  10. (SELECT C.IDCivilités FROM Civilités C WHERE C.Libellé=_Civilité)
  11. )
  12. ;
  13.  
  14. END
7. Ultime modification de la table 'Personnes'

Suppression du champ 'Civilité'.
On enlève également la possibilité de valeurs NULL pour 'IDCivilités'.

C'est terminé.

Conclusion

Avec un peu d'organisation, il est très simple de passer ce genre de modifications pourtant craintes par nombre de développeurs.
Même s'il ne faut pas rêver car cela ne solutionne pas 100% des cas, la technique est intéressante à connaître.
Et puis, 0 downtime quand même ! Et pas de nécessité de faire ça en dehors des heures "ouvertes".
J'espère qu'après la lecture de ce billet, vous verrez les modifications de structures de bases MariaDB / MySQL sous un autre jour 😋 .

Commentaires

Pas encore de commentaire



Je ne suis pas un robot et je sais que