Bienvenue chez les artisans développeurs !

MariaDB / MySQL - Les procédures stockées à la rescousse des suppressions

Posté par Alexis
Dernière révision :

Introduction

L'idée ici va être de vous exposer une technique qui permet de découpler les entités lors des suppressions dans vos bases MariaDB / MySQL.
Pour illustrer mon propos, je reprends l'exemple du billet MariaDB / MySQL - UPSERT efficace et clean.
J'ai donc 2 tables :
Applications_Clients ( IDApplications_Clients, IDApplications, IDClients )
Licences ( IDLicences, IDApplications_Clients, IDPostes )

Procédures stockées de suppression

Suppression de 'Licences'

  1. CREATE PROCEDURE pcdLicencesDelA10(IN _IDLicences INT UNSIGNED)
  2.  
  3. BEGIN
  4.  
  5. DELETE FROM
  6. Licences
  7. WHERE
  8. IDLicences=_IDLicences
  9. ;
  10.  
  11. END

Suppression de 'Applications_Clients'

  1. CREATE PROCEDURE pcdApplications_ClientsDelA10(IN _IDClients INT UNSIGNED)
  2.  
  3. BEGIN
  4.  
  5. -- Suppr Licences
  6. DELETE
  7. L
  8. FROM
  9. Licences L
  10. INNER JOIN Applications_Clients A_C ON A_C.IDApplications_Clients=L.IDApplications_Clients
  11. WHERE
  12. A_C.IDClients=_IDClients
  13. ;
  14.  
  15. -- Suppr Applications_Clients
  16. DELETE FROM
  17. Applications_Clients
  18. WHERE
  19. IDClients=_IDClients
  20. ;
  21.  
  22. END

Le problème

Tout fonctionne bien jusqu'au jour où vous souhaitez créer un historique des connexions. Vous ajouter donc une table Historiques ( IDHistoriques, DateHeure, IDLicences ). Et puis vous modifiez la procédure stockée de suppression de 'Licences'.

  1. CREATE PROCEDURE pcdLicencesDelA10(IN _IDLicences INT UNSIGNED)
  2.  
  3. BEGIN
  4.  
  5. DELETE FROM
  6. Historiques
  7. WHERE
  8. IDLicences=_IDLicences
  9. ;
  10.  
  11. DELETE FROM
  12. Licences
  13. WHERE
  14. IDLicences=_IDLicences
  15. ;
  16.  
  17. END

Oui mais voilà, vous oubliez la procédure de suppression des 'Applications_Clients' !!! La prochaine fois que vous souhaiterez supprimer une 'Applications_Clients' vous recevrez une erreur "... froreign key constraint fails..." car dans pcdApplications_ClientsDelA10() vous tentez de supprimer l'enregistrement de 'Licences' sans avoir supprimé ceux de 'Historiques'. Rien de dramatique pour autant mais cela fait mauvais genre. Aucun développeur n'aime qu'un client tombe sur un bug.
Et pourtant, cela fait partie de la vie d'une application d'ajouter des tables pour en améliorer les fonctionnalités. Alors, faut-il se résoudre à avoir des bugs car même avec une documentation précise et à jour ce cas peut facilement passer inaperçu ?
Eh bien non ! Je vais décrire la technique que j'utilise afin de pallier au problème.

La solution

Ma procédure initiale de suppression de 'Licences' reste identique. Mais voilà comment je procède pour celle de suppression de 'Applications_Clients'

  1. CREATE PROCEDURE pcdApplications_ClientsDelA10(IN _IDClients INT UNSIGNED)
  2.  
  3. BEGIN
  4.  
  5. DECLARE entIDLicences INT UNSIGNED;
  6.  
  7. LigneSupprLicence: LOOP
  8. SET entIDLicences=0;
  9.  
  10. SELECT
  11. L.IDLicences INTO entIDLicences
  12. FROM
  13. Licences L
  14. INNER JOIN Applications_Clients A_C ON A_C.IDApplications_Clients=L.IDApplications_Clients
  15. WHERE
  16. A_C.IDClients=_IDClients
  17. LIMIT
  18. 1
  19. ;
  20.  
  21. IF IFNULL(entIDLicences,0)=0 THEN
  22. LEAVE LigneSupprLicence;
  23. END IF;
  24.  
  25. CALL pcdLicencesDelA10(entIDLicences);
  26. END LOOP LigneSupprLicence;
  27.  
  28. DELETE FROM
  29. Applications_Clients
  30. WHERE
  31. IDClients=_IDClients
  32. ;
  33.  
  34. END

Cela mérite bien quelques explications...
Je ne vais pas faire assumer la totalité de la responsabilité de la suppression des licences à la procédure de suppression des 'Applications_Clients'.
Ce que je sais, c'est que lorsque je supprime une 'Applications_Client', il faut que je réalise aussi la suppression des 'Licences'.

Et là, ôh miracle, lorsque j'implémente ma table 'Historiques' et la modification de la procédure stockée pcdLicencesDelA10() tout continue à fonctionner.
Inutile de retoucher à quoi que ce soit ailleurs. Chouette non ?

Conclusion

Vous l'aurez constaté, ce n'est pas autre chose que d'appliquer la factorisation de code aux procédures stockées. Inutile et même dangereux d'avoir 2 fois le même code de suppression de licences.
Vous aurez remarqué que je n'utilise pas de curseurs... Je trouve ça un peu lourd et lorsque je peux m'en passer, je m'en passe !

Développez ainsi demande un poil plus de réflexion qu'un cochon copier/coller mais apporte une bien meilleure résistance aux changements de schéma de vos bases MariaDB / MySQL. Bien sûr ce n'est pas parfait car cela a une tendance à sévèrement ralentir les suppressions. Au lieu d'une opération de suppression ensembliste, on se retrouve à supprimer les lignes une à une ! Ce n'est donc pas le saint Graal mais c'est un outil supplémentaire qui vous rendra peut-être service un jour. Et si, comme moi, vous effectuez vos suppressions la nuit, le temps d'exécution devient un facteur secondaire.

Commentaires

Pas encore de commentaire



Je ne suis pas un robot et je sais que