La gestion des erreurs en SQL procédural avec MySQL

Un trigger est un déclencheur. Autrement dit, c'est la possibilité d'associer un certain code à un évènement.
Cela fait beaucoup penser à la programmation évènementielle qui associe elle aussi du code à un évènement (onclick, onfocus…). C'est, en quelque sorte, la même chose, mis à part que les évènements ne sont pas directement liés aux actions de l'utilisateur, mais aux requêtes effectuées auprès du SGBD.

Liste des évènements possibles :

• INSERT
• UPDATE
• DELETE

Puisque ces évènements provoquent une modification dans le contenu de la base de données, il est possible d'agir avant (BEFORE) ou après (AFTER) ces modifications. Nous agissons souvent après pour ajouter des tuples qui seront liés aux nouvelles données, alors que nous utiliserons généralement BEFORE dans un souci de contrôle de l'intégrité (annuler une « mauvaise donnée »).

Prenons l'exemple d'un trigger qui se déclenchera avant un insert sur la table Participer :

CREATE TRIGGER `nomTrigger`
BEFORE INSERT
ON `Participer`
FOR EACH ROW
BEGIN
    -- Code à exécuter;
END;

DELIMITER //

CREATE TRIGGER `nomTrigger`
BEFORE INSERT
ON `table`
FOR EACH ROW
BEGIN
    -- Code à exécuter;
END;//

Nous allons maintenant créer un trigger qui permettra de contrôler l'insertion. En reprenant le problème précédemment posé, il nous serait utile de n'effectuer les insertions dans Participer que s'il reste de la place dans la course concernée.

CREATE TRIGGER `TR_INSERT_PARTICIPANT`
BEFORE INSERT
ON `Participer`
FOR EACH ROW
BEGIN
    DECLARE CURRENT_NB INTEGER;
    DECLARE MAX_NB INTEGER;
    
    SELECT COUNT(*) INTO CURRENT_NB FROM `Participer` WHERE numCourse = NEW.numCourse;
    SELECT `nbMaxParticipants` INTO MAX_NB FROM `Course` WHERE numCourse = NEW.numCourse;

    IF (CURRENT_NB >= MAX_NB) THEN
        SET NEW.numCourse = NULL;
        SET NEW.numSportif = NULL;
    END IF;
END; 
//

Le fonctionnement de ce trigger est simple : avant chaque insertion, on vérifie s'il reste de la place et, si ce n'est pas le cas, on modifiera les informations saisies, provoquant ainsi un viol des contraintes d'intégrité, et un refus de l'insertion de la part du SGBD. Cela n'est pas la manière la plus élégante de faire, puisqu'il est impossible de savoir si l'échec d'insertion provient d'une mauvaise saisie, ou bien d'un manque de place. Nous allons peu à peu faire évoluer le système pour arriver à quelque chose de correct.

Si le SGBD est très performant dans sa gestion de l'intégrité, le développeur est soumis au risque de commettre des erreurs et d'oublier des cas possibles (c'est d'ailleurs ce que nous cherchons à éviter en reportant la gestion des erreurs sur le SGBD). Ici, nous avons prévu que l'on puisse ajouter des participations, mais rien n'empêche de modifier le nombre maximum de participations. Il faut donc prévoir et traiter ce cas.
Nous allons empêcher une diminution trop importante du nombre maximal de participations à une course.

CREATE TRIGGER `TR_UPDATE_COURSE`
BEFORE UPDATE
ON `Course`
FOR EACH ROW
BEGIN
    DECLARE CURRENT_NB INTEGER;
    DECLARE MAX_NB INTEGER;
    
    IF (NEW.nbMaxParticipants < OLD.nbMaxParticipants) THEN
        SELECT COUNT(*) INTO CURRENT_NB FROM `Participer` WHERE numCourse = OLD.numCourse;
        SET MAX_NB = NEW.nbMaxParticipants;
    
        IF (CURRENT_NB > MAX_NB) THEN
            SET NEW.nbMaxParticipants = NULL;
        END IF;
    END IF;
END; 
//

Nous sommes maintenant certains que le nombre de participations réelles dans Participer sera en accord avec le nombre maximal de participations prévu par course.

Une fonction utilisateur, comme dans la plupart des langages procéduraux, est un processus recevant des paramètres et renvoyant une valeur. MySQL permet aussi les procédures stockées (qui sont comme leur nom l'indique, de simples procédures), mais elles ne nous seront pas utiles dans ce tutoriel. Le but est de créer et utiliser une interface entre le code applicatif et les données. Des fonctions feront l'intermédiaire et filtreront ce qui doit effectivement être contrôlé, et renverront au besoin un code d'erreur.
Reprenons le premier problème, l'ajout de participation :

CREATE FUNCTION `NEW_PARTICIPATION` (P_numSportif INT, P_numCourse INT) RETURNS INT
BEGIN
    DECLARE CURRENT_NB INTEGER;
    DECLARE MAX_NB INTEGER;
    DECLARE C_ERROR INTEGER;
    
    SET C_ERROR = 0;
    SELECT COUNT(*) INTO CURRENT_NB FROM `Participer` WHERE numCourse = P_numCourse;
    SELECT `nbMaxParticipants` INTO MAX_NB FROM `Course` WHERE numCourse = P_numCourse;
    
    IF (CURRENT_NB < MAX_NB) THEN
        INSERT INTO `Participer` (`numSportif`, `numCourse`) VALUES (P_numSportif, P_numCourse);
    ELSE
        SET C_ERROR = 1001;
    END IF;
    
    RETURN C_ERROR;
END;
//

Il suffit d'appeler cette fonction pour toutes les insertions dans la table Participer, et de se servir de son code de retour pour évaluer le succès de l'insertion :

SELECT NEW_PARTICIPATION(<numSportif>, <numCourse>) AS ERROR;

Si ERROR vaut 0, alors l'insertion s'est déroulée sans problème ; si ERROR vaut 1000, c'est qu'il y a déjà trop de participants.
Il faut traiter la mise à jour du maximum de participants de la même manière :

CREATE FUNCTION `MAJ_MAX_PARTICIPATION` (P_numCourse INT, P_newMax INT) RETURNS INT
BEGIN
    DECLARE CURRENT_NB INTEGER;
    DECLARE MAX_NB INTEGER;
    DECLARE C_ERROR INTEGER;
    
    SET C_ERROR = 0;
    SELECT COUNT(*) INTO CURRENT_NB FROM `Participer` WHERE numCourse = P_numCourse;
    SET MAX_NB = P_newMax;
    
    IF (CURRENT_NB <= MAX_NB) THEN
        UPDATE `Course` SET nbMaxParticipants = MAX_NB WHERE numCourse = P_numCourse;
    ELSE
        SET C_ERROR = 1002;
    END IF;
    
    RETURN C_ERROR;
END;
//

Tout comme la fonction d'insertion de Participer, MAJ_MAX_PARTICIPATIONS renvoie un code d'erreur.
Il suffit donc de passer par cette fonction pour mettre à jour le nombre maximum de participants :

SELECT MAJ_MAX_PARTICIPATIONS(<numCourse>, <nbParticipationsMaximum>) AS ERROR;

Ici ERROR vaudra 1002 si le nouveau nombre maximum de participations est inférieur aux participations déjà en base de données.

Nous allons donner un exemple d'utilisation de nos procédures au sein d'une application.

<?php
    function add_and_updateMax($idSportif, $idCourse, $newMax)
    {
        $errors = array();
        $connexion = mysql_connect(HOST, LOGIN, PASSWD);
        mysql_select_db(DB_NAME, $connexion);
        
        $rs = mysql_fetch_array(mysql_query("SELECT NEW_PARTICIPATION($idSportif, $idCourse) AS ERROR"));
        $rs2 = mysql_fetch_array(mysql_query("SELECT MAJ_MAX_PARTICIPATIONS($idCourse, $newMax) AS ERROR"));
        
        $errors[$rs[0]['ERROR']] = true; // Il y a eu une erreur à la première requête.
        $errors[$rs2[0]['ERROR']] = true; // Il y a eu une erreur à la seconde requête.
        
        foreach($errors as $currentKey => $currentValue)
        {
            switch($currentKey)
            {
                case 1001:
                    $toReturn .= "Trop de participants sont déjà présents dans cette course.<br/>";
                    break;
                case 1002:
                    $toReturn .= "Le nouveau maximum de participant est inférieur au nombre de participations déjà présents.<br/>";
                    break;
            }
        }
        return $toReturn;
    }
?>

Il ne reste plus qu'à appeler cette fonction en PHP pour effectuer les modifications sur la base de données, sans même se préoccuper des erreurs :

<?php
    echo add_and_updateMax(10, 1, 2);
    // On ajoute une participation du sportif 10 à la course 1.
    // Puis en définit le maximum de participants de la course 1 à 2.
    // Les erreurs seront directement affichées.
?>

Nous avons trouvé deux solutions à notre problème, la première avec des triggers. Son défaut est que l'on ne peut pas générer un code erreur spécifique en fonction de notre contexte métier.
La deuxième solution nous permet de connaître précisément d'où vient l'erreur au travers de codes erreurs spécifiques, mais rien n'oblige l'utilisateur à se servir de nos fonctions. Si on néglige ces fonctions, la base de données peut se trouver dans un état incohérent, ce qui doit être absolument évité. La solution n'est donc pas évidente, car même si l'on combine les deux, outre le fait que le coût en traitement s'avère très lourd pour la moindre insertion, nous ne garantissons pas de cibler l'erreur à tous les coups (dans le cas d'un insert sans NEW_PARTICIPATION par exemple).
Malheureusement, nous ne pouvons actuellement pas faire mieux avec MySQL, mais il existe dans le monde du SQL procédural des techniques qui permettent de gérer bien plus efficacement les erreurs. Nous allons les présenter, et comment MySQL prévoit de les implémenter.

La meilleure solution à notre problème, qui, pour peu qu'elle soit implémentée dans MySQL nous aurait évité tous ces développements, est la gestion des exceptions personnalisées.
Les exceptions sont assimilées à des variables qui sont propagées au travers de notre application. Elles conduiront à un arrêt de l'exécution du programme, et un retour de code d'erreur spécialisé. Il suffit de déclarer une exception, puis de la réveiller quand nous en avons besoin. MySQL n'implémente pas encore ce système, mais de la documentation est disponible et nous allons exposer les possibilités qui devraient être offertes par des versions ultérieures de MySQL. Nous nous inspirerons de cette documentation afin d'expliquer comment marcheront ces exceptions.

Ici sera présentée la même gestion que nous avons simulée, mais avec les futures fonctionnalités de MySQL : l'utilisation de l'instruction SIGNAL suivant la norme SQL:2003. Nous déclarons une « condition » et un « handler » permettant d'associer un évènement à déclencher lors de la propagation de l'erreur.

CREATE TRIGGER `TR_INSERT_PARTICIPANT`
BEFORE INSERT
ON `Participer`
FOR EACH ROW
BEGIN
    DECLARE CURRENT_NB INTEGER;
    DECLARE MAX_NB INTEGER;
    DECLARE TROP_DE_PARTICIPANT CONDITION FOR -1001;
    DECLARE EXIT HANDLER FOR TROP_DE_PARTICIPANT SET @error = 'Trop de participants'; 
    
    SELECT COUNT(*) INTO CURRENT_NB FROM `Participer` WHERE numCourse = NEW.numCourse;
    SELECT `nbMaxParticipants` INTO MAX_NB FROM `Course` WHERE numCourse = NEW.numCourse;

    IF (CURRENT_NB >= MAX_NB) THEN
        SIGNAL TROP_DE_PARTICIPANT;
    END IF;
END; 
//

Ici, la clause SIGNAL réveille la condition TROP_DE_PARTICIPANT associée à un HANDLER qui provoque l'action EXIT, c'est-à-dire l'arrêt de l'exécution.
Il sera possible de récupérer ce code d'erreur simplement avec mysql_errno(), à partir d'une application PHP par exemple.

<?php
// ...
mysql_query("INSERT INTO `Participer` (`numSportif`, `numCourse`) VALUES (<numSportif>, <numCourse>)");
echo mysql_errno(); // Retourne -1001 s’il y a trop de participants.
// ...
?>