boucle recursive et inclusion: trigger avec boucle de contrôle [Résolu]

[kpatson]

Bonjour,
J'ai cherché un peu sur le forum, mais je n'ai pas vraiment trouvé de solution à mon problème. Seulement un sujet s'y rapprochant sur les intervalles.

Je vous explique :

Je souhaite créer des groupes, pouvant inclure d'autres groupes.
A la condition qu'un groupe ne puisse pas se contenir lui-même, et bien sûr qu'un groupe inclus dans un autre ne puisse jamais pouvoir contenir celui dans lequel il est déjà contenu directement ou indirectement.

Voila ce que j'ai sorti comme bout de code, mais j'obtiens l'erreur 1415....

Code :

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
CREATE DATABASE hierarchie;
USE hierarchie;
 
CREATE TABLE groupe
(
id_groupe INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
nom VARCHAR (10) NOT NULL,
UNIQUE (id_groupe, nom)
);
 
 
INSERT INTO groupe (nom) VALUES ("G1");
INSERT INTO groupe (nom) VALUES ("G2");
INSERT INTO groupe (nom) VALUES ("G3");
INSERT INTO groupe (nom) VALUES ("G4");
INSERT INTO groupe (nom) VALUES ("G5");
INSERT INTO groupe (nom) VALUES ("G6");
 
CREATE TABLE inclusion (
id_inclusion INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
id_groupe INT(10),
id_groupe_inclus INT(10),
UNIQUE (id_groupe,id_groupe_inclus),
FOREIGN KEY (id_groupe) REFERENCES groupe (id_groupe),
FOREIGN KEY (id_groupe_inclus) REFERENCES groupe (id_groupe)
);

Pour pouvoir donc entrer des données dans la table inclusion, il me faut donc faire un petit contrôle par rapport à la logique d'inclusion:

Code :

1
2
3
4
 
INSERT INTO inclusion (id_groupe, id_groupe_inclus) VALUES (1,2);
INSERT INTO inclusion (id_groupe, id_groupe_inclus) VALUES (2,3); # en principe 1 devrait inclure 3, et donc 3 ne pourrait pas inclure 1
INSERT INTO inclusion (id_groupe, id_groupe_inclus) VALUES (3,2); # 3 nepeut donc pas inclure 2

Par ailleurs, faut-il pour ce besoin un trigger ou une procédure ?

Code :

1
2
3
4
5
6
7
8
9
10
11
12
DELIMITER $$
CREATE TRIGGER controle_inclusion BEFORE INSERT ON inclusion
FOR EACH ROW
BEGIN	#END$$
	IF NEW.id_groupe = NEW.id_groupe_inclus 	OR
	(NEW.id_groupe IN (SELECT id_groupe_inclus FROM groupe WHERE id_groupe=NEW.id_groupe)) 
	AND (NEW.id_groupe_inclus IN (SELECT id_groupe FROM groupe WHERE id_groupe_inclus=NEW.id_groupe)) 
	THEN
		SELECT 'inclusion impossible';		
	#ELSE	
	END IF;
END$$

Je vous remercie par avance pour votre aide

[semaphore1984]

Bonjour,

Première chose, l'erreur 1415 signifie que l'ordre :

Code :

SELECT 'inclusion impossible';

dans le trigger est interdit. µUn trigger ne peut par retourner de resultset.
Pour ça, en attendant mieux, je te conseille de mettre :

Code :

CALL inclusion_impossible();

la fonction appelée n'existant pas, ça fera échouer l'insertion.

Deuxième chose :
Il y a une erreur dans ton trigger :

Code :

1
2
3
4
5
6
 
	(NEW.id_groupe IN (SELECT id_groupe_inclus FROM inclusion WHERE id_groupe=NEW.id_groupe_inclus)) 
	AND (NEW.id_groupe_inclus IN (SELECT id_groupe FROM inclusion WHERE id_groupe_inclus=NEW.id_groupe)) 
	THEN
		CALL inclusion_impossible();		
	END IF;

Troisième chose :
Le problème posé est un problème de relation transitive ou relation d'ordre, et c'est un problème qui est tout sauf trivial.

Peut-tu déjà restreindre le problème en ne permettant pas que 2 groupes incluent un troisième sans que l'un des deux premier soit inclus d'ans l'autre ?
Autrement dit qu'il n' ait pas de "sibling" ?

Après, selon la volumétrie, différents axe sont possibles :
as tu un nombre max de groupe fixé ? si oui combien ?
est-ce que tous les groupes sont créés au départ, ou bien y en aura-t-il de nouveaux par la suite.

Plus le périmètre est précis, plus on a de chance de trouver une solution adaptée (et viable en perf)

En espérant que ça fasse un peu avancer

Cordialement

[kpatson]

Bonjour semaphore1984,

Merci pour la reponse.
En ce qui concerne la syntaxe mysql, corrigé. Par contre toujours pas trouvé de solution au problème que j'ai du contourner autrement avec un autre modèle.
Les groupes ne sont pas tous créés dans mon cas d'utilisation, difficile de prévoir à l'avance le nombre de groupex a inclure dans un autre....

merci. J’espère que d'ici là, on aura le moyen d'avoir une set de resultat.

bonne journée

[CinePhil]

La structure de tes groupes est-elle purement hiérarchique, tel que ci-dessous ?
G1
|-G1.1
|-G1.2
G2
|-G2.1
G3
|-G1.2 <== Impossible !

Ou bien un groupe peut-il appartenir à plusieurs groupes de même niveau comme le groupe G1.2 dans mon exemple dit impossible ci-dessus ?

Si c'est purement hiérarchique, regarde l'article de SQLPro sur la modélisation d'arbre par représentation intervallaire.

[ced]

Un des nombreux problèmes avec MySQL, c'est qu'il n'implémente pas les contraintes CHECK (même s'il laisse passer la syntaxe, il n'effectue aucune vérification de cette contrainte, ce qui est encore plus frustrant ).
Du coup, là où une simple contrainte aurait suffi (vérifier que dans chaque ligne d'inclusion, id_groupe et id_groupe_inclus sont différents), ça devient une usine à gaz .

[CinePhil]

Envoyé par ced

Un des nombreux problèmes avec MySQL, c'est qu'il n'implémente pas les contraintes CHECK (même s'il laisse passer la syntaxe, il n'effectue aucune vérification de cette contrainte, ce qui est encore plus frustrant ).
Du coup, là où une simple contrainte aurait suffi (vérifier que dans chaque ligne d'inclusion, id_groupe et id_groupe_inclus sont différents), ça devient une usine à gaz .

Moralité : changez de SGBD si vous en avez la possibilité !
Gratuit : Postgresql
Gratuit mais limité et uniquement sous Windows : SQL Server

[kpatson]

Merci à vous pour vos retours.
J'ai modélisé autrement ma base sans changer de SGBD.