On peut aussi programmer ses propres exceptions selon certaines conditions.
Nous allons étudier ça dans une procédure de création d'un utilisateur.
1. Le modèle de données
Règle de gestion :
R1 : un utilisateur est une personne et une personne peut être un utilisateur.
MCD (fait avec JMerise) :
Tables :
te_personne_prs (prs_id, prs_nom, prs_prenom, prs_date_naissance)
th_utilisateur_uti (uti_id_personne, uti_login, uti_mot_passe, uti_date_creation, uti_valide)
Remarque : j'utilise un système de nommage des tables, colonnes, contraintes et autres éléments de la BDD inspiré de celui de SQLPro.
Code de génération des tables :
- Pour MySQL / Maria DB
Code SQL : | Sélectionner tout |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | CREATE TABLE te_personne_prs ( prs_id int(11) NOT NULL AUTO_INCREMENT COMMENT 'Identifiant de la personne', prs_nom varchar(40) NOT NULL COMMENT 'Nom usuel de la personne', prs_prenom varchar(40) NOT NULL COMMENT 'Prénom usuel de la personne', prs_date_naissance date NOT NULL COMMENT 'Date de naissance de la personne', PRIMARY KEY (prs_id), UNIQUE KEY xu_prs_nom_prenom_naissance (prs_nom, prs_prenom, prs_date_naissance), KEY `x_prs_nom` (`prs_nom`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Personnes physiques'; CREATE TABLE th_utilisateur_uti ( uti_id_personne int(11) NOT NULL COMMENT 'Identifiant de la personne', uti_login varchar(40) NOT NULL COMMENT 'Nom d''utilisateur', uti_mot_passe varchar(40) NOT NULL COMMENT 'Mot de passe de lutilisateur', uti_date_creation date NOT NULL COMMENT 'Date de création de lutilisateur', uti_valide tinyint(1) NOT NULL DEFAULT 0 COMMENT 'L''utilisateur est-il valide ?', PRIMARY KEY (uti_id_personne), UNIQUE KEY xu_uti_login (uti_login), CONSTRAINT fk_uti_id_personne FOREIGN KEY (uti_id_personne) REFERENCES te_personne_prs (prs_id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Utilisateurs de lapplication'; |
- Pour PostgreSQL :
Code SQL : | Sélectionner tout |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 | -- Table: public.te_personne_prs CREATE TABLE public.te_personne_prs ( prs_id serial NOT NULL, -- Identifiant de la personne prs_nom character varying(40) NOT NULL, -- Nom usuel de la personne prs_prenom character varying(40) NOT NULL DEFAULT '', -- Prénom usuel de la personne prs_date_naissance date NOT NULL, CONSTRAINT pk_prs_id PRIMARY KEY (prs_id), CONSTRAINT xu_prs_nom_prenom_naissance UNIQUE (prs_nom, prs_prenom, prs_date_naissance) ) COMMENT ON TABLE public.te_personne_prs IS 'Personnes physiques'; COMMENT ON COLUMN public.te_personne_prs.prs_id IS 'Identifiant de la personne'; COMMENT ON COLUMN public.te_personne_prs.prs_nom IS 'Nom usuel de la personne'; COMMENT ON COLUMN public.te_personne_prs.prs_prenom IS 'Prénom usuel de la personne'; CREATE INDEX x_prs_nom ON public.te_personne_prs USING btree (prs_nom); -- Table: public.th_utilisateur_uti CREATE TABLE public.th_utilisateur_uti ( uti_id_personne integer NOT NULL, -- Identifiant de la personne uti_login character varying(40) NOT NULL, -- Nom d'utilisateur uti_mot_passe character varying(40) NOT NULL, -- Mot de passe de l'utilisateur uti_date_creation date NOT NULL DEFAULT CURRENT_DATE, -- Date de création de l'utilisateur uti_valide boolean NOT NULL DEFAULT false, -- L'utilisateur est-il valide ? CONSTRAINT pk_uti_id_personne PRIMARY KEY (uti_id_personne), CONSTRAINT fk_uti_id_personne FOREIGN KEY (uti_id_personne) REFERENCES public.te_personne_prs (prs_id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE, -- Clé étrangère référençant te_personne_prs CONSTRAINT xu_uti_login UNIQUE (uti_login) ); COMMENT ON TABLE public.th_utilisateur_uti IS 'Utilisateurs de lapplication'; COMMENT ON COLUMN public.th_utilisateur_uti.uti_id_personne IS 'Identifiant de la personne'; COMMENT ON COLUMN public.th_utilisateur_uti.uti_login IS 'Nom d''utilisateur'; COMMENT ON COLUMN public.th_utilisateur_uti.uti_mot_passe IS 'Mot de passe de lutilisateur'; COMMENT ON COLUMN public.th_utilisateur_uti.uti_date_creation IS 'Date de création de lutilisateur'; COMMENT ON COLUMN public.th_utilisateur_uti.uti_valide IS 'L''utilisateur est-il valide ?'; COMMENT ON CONSTRAINT fk_uti_id_personne ON public.th_utilisateur_uti IS 'Clé étrangère référençant te_personne_prs'; |
2. Procédure de création d'un utilisateur
Comme th_utilisateur_uti hérite de te_personne_prs, la clé primaire de th_utilisateur_uti est également une clé étrangère référençant te_personne_prs.
Lors de la création d'un utilisateur, on doit normalement fournir à la requête d'insertion l'identifiant de la personne. Il est alors intéressant de créer directement l'utilisateur via une procédure qui créera en même temps la personne, récupérera l'identifiant généré et donnera la bonne clé étrangère à l'insertion dans la table th_utilisateur_uti.
Voici la procédure simple, sans gestion des erreurs et sans programmation d'exceptions :
- Pour MySQL / MariaDB
Code SQL : | Sélectionner tout |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | DELIMITER // CREATE OR REPLACE PROCEDURE pi_ajout_utilisateur ( INOUT id_personne INTEGER, -- Identifiant de la personne -- Données pour la création éventuelle de la personne IN nom_usuel VARCHAR(40), -- Nom usuel de la personne IN prenom_usuel VARCHAR(40), -- Prénom usuel de la personne IN date_naissance DATE, -- Date de naissance de la personne -- Données pour la création de l'utilisateur IN login VARCHAR(40), -- Nom d'utilisateur IN mot_passe VARCHAR(40) -- Mot de passe de l'utilisateur (on suppose qu'il est crypté par l'application) -- Remarque : la date de création et la validité de l'utilisateur sont automatiques lors de sa création ) BEGIN IF id_personne IS NULL THEN -- Identifiant de la personne non fourni : on crée d'abord la personne INSERT INTO te_personne_prs (prs_nom, prs_prenom, prs_date_naissance) VALUES (nom_usuel, prenom_usuel, date_naissance); -- Récupération du prs_id généré par l'auto-incrémentation SET id_personne = LAST_INSERT_ID(); END IF; -- Création de l'utilisateur INSERT INTO th_utilisateur_uti (uti_id_personne, uti_login, uti_mot_passe, uti_date_creation, uti_valide) VALUES (id_personne, login, mot_passe, CURRENT_DATE, FALSE); END;// |
Pour PostgreSQL
Code SQL : | Sélectionner tout |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | CREATE OR REPLACE FUNCTION pi_ajout_utilisateur ( INOUT id_personne BIGINT, -- Identifiant de la personne -- Données pour la création éventuelle de la personne IN nom_usuel VARCHAR(40), -- Nom usuel de la personne IN prenom_usuel VARCHAR(40), -- Prénom usuel de la personne IN date_naissance DATE, -- Date de naissance de la personne -- Données pour la création de l'utilisateur IN login VARCHAR(40), -- Nom d'utilisateur IN mot_passe VARCHAR(40) -- Mot de passe de l'utilisateur (on suppose qu'il est crypté par l'application) -- Remarque : la date de création et la validité de l'utilisateur sont automatiques lors de sa création ) AS $BODY$ BEGIN IF id_personne IS NULL THEN -- Identifiant de la personne non fourni : on crée d'abord la personne INSERT INTO te_personne_prs (prs_nom, prs_prenom, prs_date_naissance) VALUES (nom_usuel, prenom_usuel, date_naissance); -- Récupération du prs_id généré par l'auto-incrémentation SELECT CURRVAL('te_personne_prs_prs_id_seq') INTO id_personne; END IF; -- Création de l'utilisateur INSERT INTO th_utilisateur_uti (uti_id_personne, uti_login, uti_mot_passe, uti_date_creation, uti_valide) VALUES (id_personne, login, mot_passe, CURRENT_DATE, FALSE); END; $BODY$ LANGUAGE plpgsql VOLATILE; |
Voyons maintenant si ces procédures fonctionnent...
Chez MySQL / MariaDB :
Code SQL : | Sélectionner tout |
1 2 3 4 5 6 7 8 9 10 11 12 13 | -- Cas normal : Données de la personne complètes et correctes SET @id_personne = NULL; CALL pi_ajout_utilisateur (@id_personne, 'Leménager', 'Philippe', '1963-07-24', 'plemenager', 'Mon_Pass'); SELECT @id_personne; -- > @id_personne = 1 => OK -- Cas normal : Insertion d'une personne puis utilisation de son identifiant pour l'appel de la procédure INSERT INTO te_personne_prs (prs_nom, prs_prenom, prs_date_naissance) VALUES ('Bellucci', 'Monica', '1964-09-30'); SET @id_personne = LAST_INSERT_ID(); CALL pi_ajout_utilisateur (@id_personne, NULL, NULL, NULL, 'mbellucci', 'Son_Pass'); SELECT @id_personne; -- > @id_personne = 2 => OK |
Chez PostgreSQL :
Code SQL : | Sélectionner tout |
1 2 3 4 5 6 7 8 9 | -- Cas normal : Données de la personne complètes et correctes SELECT pi_ajout_utilisateur (NULL, 'Leménager', 'Philippe', '1963-07-24', 'plemenager', 'Mon_Pass'); -- > pi_ajout_utilisateur = 1 => OK -- Cas normal : Insertion d'une personne puis utilisation de son identifiant pour l'appel de la procédure INSERT INTO te_personne_prs (prs_nom, prs_prenom, prs_date_naissance) VALUES ('Bellucci', 'Monica', '1964-09-30'); SELECT pi_ajout_utilisateur (CURRVAL('te_personne_prs_prs_id_seq'), NULL, NULL, NULL, 'mbellucci', 'Son_Pass'); -- > pi_ajout_utilisateur = 2 => OK |
Jusque là, tout va bien !
3. Exceptions personnalisées
En l'état, je peux très bien enregistrer comme date de naissance '1863-07-24' (record absolu de longévité ; appelons vite le Guiness Book ! ) ou bien '2018-07-24' (précoce le gamin ! ).
Il faut donc contrôler un minimum la cohérence de la date de naissance qui est passée à la procédure... et interrompre le processus en renvoyant un message d'erreur à l'application qui a lancé la procédure en lui passant de mauvaises données.
Remarque : on pourrait utiliser une contrainte CHECK et capturer le message d'erreur renvoyé par le SGBD, mais comme MySQL / MariaDB ne l'a toujours pas implémenté , on va générer une exception dans la procédure pour nos deux SGBD.
Nous allons considérer ci-après que notre utilisateur ne peut pas être âgé de plus de 100 ans ni de moins de 10 ans.
Chez MySQL / MariaDB, on génère l'exception à l'aide de l'instruction SIGNAL.
Pour une exception personnalisée, il est de coutume d'utiliser le SQLSTATE '45000'.
On peut ensuite donner au SIGNAL un "numéro d'erreur" MYSQL_ERRNO qui doit être un nombre à 5 chiffres. Pour un numéro personnalisé , il vaut mieux éviter d'utiliser les numéros standards de MySQL et donc commencer la numérotation des exceptions personnalisées à partir de 10001.
Enfin, Le message personnalisé est donné par la variable MESSAGE_TEXT et ne doit pas dépasser 64 caractères sous peine d'être tronqué.
Voilà ce que ça donne dans le code de notre procédure MySQL /MariaDB (voir partie du code -- Contrôle de la date de naissance) :
Code SQL : | Sélectionner tout |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | DELIMITER // CREATE OR REPLACE PROCEDURE pi_ajout_utilisateur ( INOUT id_personne INTEGER, -- Identifiant de la personne -- Données pour la création éventuelle de la personne IN nom_usuel VARCHAR(40), -- Nom usuel de la personne IN prenom_usuel VARCHAR(40), -- Prénom usuel de la personne IN date_naissance DATE, -- Date de naissance de la personne -- Données pour la création de l'utilisateur IN login VARCHAR(40), -- Nom d'utilisateur IN mot_passe VARCHAR(40) -- Mot de passe de l'utilisateur (on suppose qu'il est crypté par l'application) -- Remarque : la date de création et la validité de l'utilisateur sont automatiques lors de sa création ) BEGIN -- Contrôle de la date de naissance IF date_naissance NOT BETWEEN DATE_SUB(CURRENT_DATE, INTERVAL 100 YEAR) AND DATE_SUB(CURRENT_DATE, INTERVAL 10 YEAR) THEN -- Si la date de naissance n'est pas comprise entre aujourd'hui moins 100 ans et aujourd'hui moins 10 ans => erreur ! SIGNAL SQLSTATE '45000' SET MYSQL_ERRNO = 10001, MESSAGE_TEXT = 'Date de naissance incorrecte'; END IF; -- Contrôle de l'id_personne et ajout éventuel de la personne IF id_personne IS NULL THEN -- Identifiant de la personne non fourni : on crée d'abord la personne INSERT INTO te_personne_prs (prs_nom, prs_prenom, prs_date_naissance) VALUES (nom_usuel, prenom_usuel, date_naissance); -- Récupération du prs_id généré par l'auto-incrémentation SET id_personne = LAST_INSERT_ID(); END IF; -- Création de l'utilisateur INSERT INTO th_utilisateur_uti (uti_id_personne, uti_login, uti_mot_passe, uti_date_creation, uti_valide) VALUES (id_personne, login, mot_passe, CURRENT_DATE, FALSE); END;// |
Chez PostgreSQL, une erreur ou exception se déclenche avec la fonction RAISE.
Comme on veut déclencher une exception personnalisée, on écrit simplement RAISE EXCEPTION 'le message exception'.
PostgreSQL est plus permissif sur les codes d'erreurs personnalisés puisqu'ils sont alphanumériques, mais toujours limités à 5 caractères. On ajoute à ce qui précède USING ERRCODE = 'code1'. On peut ainsi se créer ses propres familles de codes, mais, là encore, je recommande de ne pas utiliser ceux de PostgreSQL pour éviter toute confusion entre une erreur renvoyée par PostgreSQL et qui n'aurait pas été prévue d'être capturée par le programme, et une exception personnalisée spécialement programmée pour les besoins de l'application.
Dans le code de notre procédure ci-dessous, j'ai décidé de commencer les codes d'erreur par DVP puis de les numéroter de 01 à potentiellement 99.
Code SQL : | Sélectionner tout |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 | CREATE OR REPLACE FUNCTION pi_ajout_utilisateur ( INOUT id_personne BIGINT, -- Identifiant de la personne -- Données pour la création éventuelle de la personne IN nom_usuel VARCHAR(40), -- Nom usuel de la personne IN prenom_usuel VARCHAR(40), -- Prénom usuel de la personne IN date_naissance DATE, -- Date de naissance de la personne -- Données pour la création de l'utilisateur IN login VARCHAR(40), -- Nom d'utilisateur IN mot_passe VARCHAR(40) -- Mot de passe de l'utilisateur (on suppose qu'il est crypté par l'application) -- Remarque : la date de création et la validité de l'utilisateur sont automatiques lors de sa création ) AS $BODY$ BEGIN -- Vérification de la date de naissance IF date_naissance NOT BETWEEN CURRENT_DATE - INTERVAL '100' YEAR AND CURRENT_DATE - INTERVAL '10' YEAR THEN -- Si la date de naissance n'est pas comprise entre il y a 100 ans et il y a 10 ans => Erreur RAISE EXCEPTION 'Date de naissance incorrecte' USING ERRCODE = 'DVP01'; END IF; IF id_personne IS NULL THEN -- Identifiant de la personne non fourni : on crée d'abord la personne INSERT INTO te_personne_prs (prs_nom, prs_prenom, prs_date_naissance) VALUES (nom_usuel, prenom_usuel, date_naissance); -- Récupération du prs_id généré par l'auto-incrémentation SELECT CURRVAL('te_personne_prs_prs_id_seq') INTO id_personne; END IF; -- Création de l'utilisateur INSERT INTO th_utilisateur_uti (uti_id_personne, uti_login, uti_mot_passe, uti_date_creation, uti_valide) VALUES (id_personne, login, mot_passe, CURRENT_DATE, FALSE); END; $BODY$ LANGUAGE plpgsql VOLATILE; |
Essayons maintenant nos procédures modifiées en leur donnant de mauvaises dates de naissance...
Avec MySQL / MariaDB :
Code SQL : | Sélectionner tout |
1 2 3 4 5 6 7 8 9 10 11 | -- Cas erreur : date de naissance trop ancienne (> 100 ans) SET @id_personne = NULL; CALL pi_ajout_utilisateur (@id_personne, 'Chaplin', 'Charlie', '1899-04-16', 'cchaplin', 'Son_Pass'); SELECT @id_personne; -- > #10001 - Date de naissance incorrecte => OK -- Cas erreur : date de naissance trop récente (< 10 ans) SET @id_personne = NULL; CALL pi_ajout_utilisateur (@id_personne, 'Doll', 'Baby', '2018-05-15', 'bdoll', 'Son_Pass'); SELECT @id_personne; -- > #10001 - Date de naissance incorrecte => OK |
Si vous regardez le contenu de la table te_personne_prs, vous verrez que l'envoi du SIGNAL a empêché la création de ces deux personnes. Idem, elles ne sont pas présentes non plus dans la table th_utilisateur_uti.
Passons sous PostgreSQL :
Code SQL : | Sélectionner tout |
1 2 3 4 5 6 7 | -- Cas erreur : date de naissance trop ancienne (> 100 ans) SELECT pi_ajout_utilisateur (NULL, 'Chaplin', 'Charlie', '1899-04-16', 'cchaplin', 'Son_Pass'); -- > ERROR: Date de naissance incorrecte / État SQL :DVP01 => OK -- Cas erreur : date de naissance trop récente (< 10 ans) SELECT pi_ajout_utilisateur (NULL, 'Doll', 'Baby', '2018-05-15', 'bdoll', 'Son_Pass'); -- > ERROR: Date de naissance incorrecte / État SQL :DVP01 => OK |
4. Captation des erreurs du SGBD
Dans la table te_personne_prs, j'ai créé une contrainte d'unicité sur le triplet {nom, prénom, date de naissance} de manière à ne pas enregistrer deux fois la même personne (le cas de deux personnes différentes ayant les mêmes nom, prénom et date de naissance est très improbable).
Dans la table th_utilisateur_uti, j'ai une contrainte d'unicité sur le login (2 utilisateurs différents ne doivent pas avoir le même login) et une clé étrangère référençant te_personne_prs.
Si je viole une de ces contraintes, le SGBD va renvoyer une erreur dans son jargon qui n'est pas toujours très compréhensible.
Exemple avec MySQL / MariaDB en essayant de créer un compte utilisateur via un identifiant ne figurant pas encore dans la table te_personne_prs...
Code SQL : | Sélectionner tout |
1 2 3 4 | SET @id_personne = 100; -- < identifiant inexistant dans te_personne_prs CALL pi_ajout_utilisateur (@id_personne, NULL, NULL, NULL, 'plemenager', 'Mon_Pass'); SELECT @id_personne; -- > #1452 - Cannot add or update a child row: a foreign key constraint fails (`test`.`th_utilisateur_uti`, CONSTRAINT `fk_uti_id_personne` FOREIGN KEY (`uti_id_personne`) REFERENCES `te_personne_prs` (`prs_id`) ON DELETE CASCADE ON UPDATE CASCADE) |
Nous allons donc capturer ces erreurs et en personnaliser le message.
Pour se faire, vous aurez peut-être remarqué que j'ai pris soin de nommer les contraintes (xu_prs_nom_prenom_naissance, fk_uti_id_personne...). Nos SGBD enregistrent les erreurs qu'ils produisent dans une sorte de journal d'événements. Nous allons chercher, dans le texte de la dernière erreur rencontrée (celle qui a fait planter l'exécution de la procédure), le nom de la contrainte qui a été violée.
Avec MySQL / MariaDB, nous créons un HANDLER qui va exécuter des instructions en cas d'erreur renvoyée par le SGBD. Celui-ci fait partie des déclarations de variables précédent le corps du code de la procédure, juste après le BEGIN :
Code SQL : | Sélectionner tout |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 | DELIMITER // CREATE OR REPLACE PROCEDURE pi_ajout_utilisateur ( INOUT id_personne INTEGER, -- Identifiant de la personne -- Données pour la création éventuelle de la personne IN nom_usuel VARCHAR(40), -- Nom usuel de la personne IN prenom_usuel VARCHAR(40), -- Prénom usuel de la personne IN date_naissance DATE, -- Date de naissance de la personne -- Données pour la création de l'utilisateur IN login VARCHAR(40), -- Nom d'utilisateur IN mot_passe VARCHAR(40) -- Mot de passe de l'utilisateur (on suppose qu'il est crypté par l'application) -- Remarque : la date de création et la validité de l'utilisateur sont automatiques lors de sa création ) BEGIN -- Handler d'erreur DECLARE err_condition CONDITION FOR SQLSTATE '23000'; -- Erreur de violation de contrainte DECLARE EXIT HANDLER FOR err_condition BEGIN -- Les instructions ci-dessous seront exécutées seulement en cas d'erreur du SGBD GET DIAGNOSTICS @nb_errors = NUMBER; -- On récupère dans les diagnostics le numéro du dernier événement (l'erreur à capturer) GET DIAGNOSTICS CONDITION @nb_errors @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT; -- Puis on récupère les informations qui nous intéressent de cette erreur IF LOCATE('fk_uti_id_personne', @text) > 0 THEN -- Si on touve le nom de la clé étrangère référençant la personne dans le texte de l'erreur, on prépare notre erreur personnalisée SET @errno = 10001; -- Notre code d'erreur personnalisé SET @text = 'Identifiant de la personne inconnu.'; -- Notre message personnalisé ELSEIF LOCATE('xu_uti_login', @text) > 0 THEN -- Si on trouve le nom de la contrainte d'unicité, on prépare notre erreur personnalisée SET @errno = 10002; SET @text = 'Ce login existe déjà.'; ELSEIF LOCATE('xu_prs_nom_prenom_naissance', @text) > 0 THEN -- Si le trplet {prs_nom, prs_prenom, prs_date_naissance} existe déjà, on prépare notre erreur personnalisée SET @errno = 10003; SET @text = 'Une personne portant ce nom, ce prénom et née ce jour existe déjà.'; END IF; -- Génération de l'erreur personnalisée SIGNAL SQLSTATE VALUE '23000' SET MYSQL_ERRNO = @errno, MESSAGE_TEXT = @text; END; -- Contrôle de la date de naissance IF date_naissance NOT BETWEEN DATE_SUB(CURRENT_DATE, INTERVAL 100 YEAR) AND DATE_SUB(CURRENT_DATE, INTERVAL 10 YEAR) THEN -- Si la date de naissance n'est pas comprise entre aujourd'hui moins 100 ans et aujourd'hui moins 10 ans => erreur ! SIGNAL SQLSTATE '45000' SET MYSQL_ERRNO = 10001, MESSAGE_TEXT = 'Date de naissance incorrecte'; END IF; -- Contrôle de l'id_personne et ajout éventuel de la personne IF id_personne IS NULL THEN -- Identifiant de la personne non fourni : on crée d'abord la personne INSERT INTO te_personne_prs (prs_nom, prs_prenom, prs_date_naissance) VALUES (nom_usuel, prenom_usuel, date_naissance); -- Récupération du prs_id généré par l'auto-incrémentation SET id_personne = LAST_INSERT_ID(); END IF; -- Création de l'utilisateur INSERT INTO th_utilisateur_uti (uti_id_personne, uti_login, uti_mot_passe, uti_date_creation, uti_valide) VALUES (id_personne, login, mot_passe, CURRENT_DATE, FALSE); END;// |
Avec PostgreSQL, la même opération se fait dans la procédure en y ajoutant une structure de contrôle EXCEPTION (voir chapitre 38.6.5 de la documentation). Nous déclarons aussi au préalable (avec le BEGIN de la procédure) une variable msg_erreur destinée à récupérer le texte de l'erreur PostgreSQL :
Code SQL : | Sélectionner tout |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 | CREATE OR REPLACE FUNCTION pi_ajout_utilisateur ( INOUT id_personne BIGINT, -- Identifiant de la personne -- Données pour la création éventuelle de la personne IN nom_usuel VARCHAR(40), -- Nom usuel de la personne IN prenom_usuel VARCHAR(40), -- Prénom usuel de la personne IN date_naissance DATE, -- Date de naissance de la personne -- Données pour la création de l'utilisateur IN login VARCHAR(40), -- Nom d'utilisateur IN mot_passe VARCHAR(40) -- Mot de passe de l'utilisateur (on suppose qu'il est crypté par l'application) -- Remarque : la date de création et la validité de l'utilisateur sont automatiques lors de sa création ) AS $BODY$ DECLARE msg_erreur CHARACTER VARYING DEFAULT ''; -- Message d'erreur éventuel BEGIN -- Vérification de la date de naissance IF date_naissance NOT BETWEEN CURRENT_DATE - INTERVAL '100' YEAR AND CURRENT_DATE - INTERVAL '10' YEAR THEN -- Si la date de naissance n'est pas comprise entre il y a 100 ans et il y a 10 ans => Erreur RAISE EXCEPTION 'Date de naissance incorrecte' USING ERRCODE = 'DVP01'; END IF; IF id_personne IS NULL THEN -- Identifiant de la personne non fourni : on crée d'abord la personne INSERT INTO te_personne_prs (prs_nom, prs_prenom, prs_date_naissance) VALUES (nom_usuel, prenom_usuel, date_naissance); -- Récupération du prs_id généré par l'auto-incrémentation SELECT CURRVAL('te_personne_prs_prs_id_seq') INTO id_personne; END IF; -- Création de l'utilisateur INSERT INTO th_utilisateur_uti (uti_id_personne, uti_login, uti_mot_passe, uti_date_creation, uti_valide) VALUES (id_personne, login, mot_passe, CURRENT_DATE, FALSE); EXCEPTION WHEN foreign_key_violation THEN -- En cas d'erreur de clé étrangère, on récupère l'erreur et on la personnalise GET STACKED DIAGNOSTICS msg_erreur = MESSAGE_TEXT; -- On récupère le message de l'erreur PostgreSQL IF POSITION('fk_uti_id_personne' IN msg_erreur) > 0 THEN -- Erreur de clé étrangère sur l'id_personne RAISE EXCEPTION 'Identifiant de la personne non trouvé' -- Notre texte d'erreur personnalisé USING ERRCODE = 'DEV01'; -- Notre code d'erreur personnalisé END IF; WHEN unique_violation THEN -- En cas de violation de contrainte d'unicité GET STACKED DIAGNOSTICS msg_erreur = MESSAGE_TEXT; IF POSITION('xu_uti_login' IN msg_erreur) > 0 THEN -- Violation de l'unicité du login RAISE EXCEPTION 'Login existant' -- Notre texte d'erreur personnalisé USING ERRCODE = 'DEV02'; -- Notre code d'erreur personnalisé ELSEIF POSITION('xu_prs_nom_prenom_naissance' IN msg_erreur) > 0 THEN -- Violation de l'unicité du triplet {prs_nom, psr_prenom, psr_date_naissance} RAISE EXCEPTION 'Une personne portant ce nom, ce prénom et née ce jour existe déjà.' -- Notre texte d'erreur personnalisé USING ERRCODE = 'DEV03'; -- Notre code d'erreur personnalisé END IF; END; $BODY$ LANGUAGE plpgsql VOLATILE; |
Testons ces dernières procédures complètes...
Avec MySQL / MariaDB :
Code SQL : | Sélectionner tout |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | -- Cas erreur : identifiant de la personne inconnu SET @id_personne = 100; CALL pi_ajout_utilisateur (@id_personne, NULL, NULL, NULL, 'bdoll', 'Son_Pass'); SELECT @id_personne; -- > #10001 - Identifiant de la personne inconnu. => OK -- Cas erreur : Login existant SET @id_personne = NULL; CALL pi_ajout_utilisateur (@id_personne, 'Colucci', 'Michel', '1944-10-28', 'mbellucci', 'Son_Pass'); SELECT @id_personne; -- > #10002 - Ce login existe déjà. => OK -- Cas erreur : Nom, prénom et date de naissance existants SET @id_personne = NULL; CALL pi_ajout_utilisateur (@id_personne, 'Leménager', 'Philippe', '1963-07-24', 'CinéPhil', 'Mon_Pass'); SELECT @id_personne; -- > #10003 - Une personne portant ce nom, ce prénom et née ce jour existe déjà. => OK |
Avec PostgreSQL :
Code SQL : | Sélectionner tout |
1 2 3 4 5 6 7 8 9 10 11 | -- Cas erreur : identifiant de la personne inconnu SELECT pi_ajout_utilisateur (100, NULL, NULL, NULL, 'bdoll', 'Son_Pass'); -- > ERROR: Identifiant de la personne non trouvé / État SQL :DEV01 => OK -- Cas erreur : Login existant SELECT pi_ajout_utilisateur (NULL, 'Colucci', 'Michel', '1944-10-28', 'mbellucci', 'Son_Pass'); -- > ERROR: Login existant / État SQL :DEV02 => OK -- Cas erreur : Nom, prénom et date de naissance existants SELECT pi_ajout_utilisateur (NULL, 'Leménager', 'Philippe', '1963-07-24', 'CinéPhil', 'Mon_Pass'); -- > ERROR: Une personne portant ce nom, ce prénom et née ce jour existe déjà. / État SQL :DEV03 => OK |
Tout fonctionne !
Nous avons maintenant une procédure qui personnalise les erreurs du SGBD et qui programme des erreurs spécifiques au besoin.
Vos commentaires sont les bienvenus pour améliorer ce billet.