Discussion :

[Akirouille]

Bonjour, je poste pour la première fois sur ce forum qui m'a été conseillé par un ami.
Je m'excuse d'avance pour la très probable difformité de mon post, je n'ai pas tellement l'habitude des forums non plus ! =/

Mon problème est le suivant : je souhaite réalisé une petite base de donnée pas très compliquée (3 table actuellement) qui mettent à jour des données automatiquement.

J'utilise phpmyadmin et donc MySQL à priori (sauf erreur). Il a été proposé quelque part sur internet de passer par autre chose, mais je souhaiterais finir ce petit projet sur la même chose.

J'ai choisi d'apprendre un peu le SQL en prenant pour sujet un des jeu auquel je joue. le but de cette base de donnée est de calculer automatiquement des coûts de fabrication. Puis du côté php de pouvoir sélectionner plusieurs recette, additionner les quantitées de ressources nécessaire etc...

Je dispose de 3 table :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
 
CREATE TABLE Ressource
(
	Nom_ressource varchar(100) NOT NULL,
	Type_hdv varchar(100) NOT NULL,
	Prix_hdv int,
	Disponibilité_hdv varchar(100),
	Quantitée_banque varchar(100),
	constraint pk_ressource primary key(Nom_ressource)
);
 
CREATE TABLE Recette
(
	Nom_item int NOT NULL,
	Quantitée_1 int,
	Ressource_1 varchar(100),
	Quantitée_2 int,
	Ressource_2 varchar(100),
	Quantitée_3 int,
	Ressource_3 varchar(100),
	Quantitée_4 int,
	Ressource_4 varchar(100),
	Quantitée_5 int,
	Ressource_5 varchar(100),
	Quantitée_6 int,
	Ressource_6 varchar(100),
	Quantitée_7 int,
	Ressource_7 varchar(100),
	Quantitée_8 int,
	Ressource_8 varchar(100),
	constraint pk_nom_item primary key(Nom_item),
	constraint fk_ressource_1 foreign key(Ressource_1) references Ressource(Nom_ressource),
	constraint fk_ressource_2 foreign key(Ressource_2) references Ressource(Nom_ressource),
	constraint fk_ressource_3 foreign key(Ressource_3) references Ressource(Nom_ressource),
	constraint fk_ressource_4 foreign key(Ressource_4) references Ressource(Nom_ressource),
	constraint fk_ressource_5 foreign key(Ressource_5) references Ressource(Nom_ressource),
	constraint fk_ressource_6 foreign key(Ressource_6) references Ressource(Nom_ressource),
	constraint fk_ressource_7 foreign key(Ressource_7) references Ressource(Nom_ressource),
	constraint fk_ressource_8 foreign key(Ressource_8) references Ressource(Nom_ressource)
);
 
CREATE TABLE Item
(
	Nom_item int NOT NULL,
	Type_hdv varchar(100) NOT NULL,
	Prix_hdv int,
	Prix_craft int,
	Pourcentage_benefice int,
	constraint pk_nom_item primary key(Nom_item),
	constraint fk_nom_item foreign key(Nom_item) references Recette(Nom_item)
);

Comme indiqué dans le titre, mon problème est dans un curseur situé à l'intérieur d'un triger.

Fonctionnement du triger globalement :
Déclenchement lors d'un UPDATE ou INSERT sur la table Ressource
Création d'une table temporaire copiant celle des items (car d'après ce que j'ai lu, il est dangereux de faire un UPDATE directement sur une table où un curseur se balade).
Création d'un curseur sur ladite table temporaire précédent concernant la colonne des noms.
| Début de boucle
Pour le chaque nom d'item du curseur, on récupère la quantitée et le prix de chaque ressource nécessaire à la création de l'item.
On calcul ce qu'il nous faut.
On envoie les résultats du calcul dans une mise à jour sur la table Item
| Fin de la boucle
Fin du trigger.

Voici le code du trigger en entier :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
 
DELIMITER |
CREATE TRIGGER after_update_ressource AFTER UPDATE
ON Ressource FOR EACH ROW
BEGIN
	-- Variable servant pour le fetch sur la table temporaire
	DECLARE v_nom_item varchar(100);
	DECLARE v_prix_hdv int;
	-- Creation de la table temporaire similaire à la table Item, parce qu'il est dangereux de modifier une table où il y a un curseur d'ouvert,
	-- donc je vais parcourir la table temporaire et modifier la table initiale
	CREATE TEMPORARY TABLE Item_temp
(
	Nom_item int NOT NULL,
	Type_hdv varchar(100) NOT NULL,
	Prix_hdv int,
	Prix_craft int,
	Pourcentage_benefice int,
	constraint pk_nom_item primary key(Nom_item),
	constraint fk_nom_item foreign key(Nom_item) references Recette(Nom_item)
);
	-- Attribution des valeurs de Item dans Item_temp
	INSERT INTO Item_temp
	SELECT * from  Item;
	-- Déclaration du curseur
	DECLARE curseur_item CURSOR FOR	
		SELECT Nom_item, Prix_hdv
		FROM Item_temp;
	-- Variable fin servant à sortir de la boucle loop un peu plus loin
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET fin = 1; 
	-- Début du déplacement du curseur
	OPEN curseur_item;
		-- Début de la boucle !
		loop_curseur: LOOP
 
			DECLARE cout int, Q1 int, Q2 int, Q3 int, Q4 int, Q5 int, Q6 int, Q7 int, Q8 int, Q9 int, R1 int, R2 int, R3 int, R4 int, R5 int, R6 int, R7 int, R8 int, R9 int, P1 int, P2 int, P3 int, P4 int, P5 int, P6 int, P7 int, P8 int, P9 int;
 
			fetch curseur_item INTO v_nom_item, v_prix_hdv;
			-- If de test de fin de boucle
			IF fin = 1 THEN
				LEAVE loop_curseur;
			END IF;
 
			-- Variable de quantitée et prix ressource
			SET Q1 = (SELECT  Quantitée_1 FROM Recette WHERE Nom_item = v_nom_item);
			SET Q2 = (SELECT  Quantitée_2 FROM Recette WHERE Nom_item = v_nom_item);
			SET Q3 = (SELECT  Quantitée_3 FROM Recette WHERE Nom_item = v_nom_item);
			SET Q4 = (SELECT  Quantitée_4 FROM Recette WHERE Nom_item = v_nom_item);
			SET Q5 = (SELECT  Quantitée_5 FROM Recette WHERE Nom_item = v_nom_item);
			SET Q6 = (SELECT  Quantitée_6 FROM Recette WHERE Nom_item = v_nom_item);
			SET Q7 = (SELECT  Quantitée_7 FROM Recette WHERE Nom_item = v_nom_item);
			SET Q8 = (SELECT  Quantitée_8 FROM Recette WHERE Nom_item = v_nom_item);
 
			SET R1 = (SELECT  Ressource_1 FROM Recette WHERE Nom_item = v_nom_item);
			SET R2 = (SELECT  Ressource_2 FROM Recette WHERE Nom_item = v_nom_item);
			SET R3 = (SELECT  Ressource_3 FROM Recette WHERE Nom_item = v_nom_item);
			SET R4 = (SELECT  Ressource_4 FROM Recette WHERE Nom_item = v_nom_item);
			SET R5 = (SELECT  Ressource_5 FROM Recette WHERE Nom_item = v_nom_item);
			SET R6 = (SELECT  Ressource_6 FROM Recette WHERE Nom_item = v_nom_item);
			SET R7 = (SELECT  Ressource_7 FROM Recette WHERE Nom_item = v_nom_item);
			SET R8 = (SELECT  Ressource_8 FROM Recette WHERE Nom_item = v_nom_item);
 
			SET P1 = (SELECT Prix_hdv FROM Ressource WHERE Nom_ressource = R1);
			SET P2 = (SELECT Prix_hdv FROM Ressource WHERE Nom_ressource = R2);
			SET P3 = (SELECT Prix_hdv FROM Ressource WHERE Nom_ressource = R3);
			SET P4 = (SELECT Prix_hdv FROM Ressource WHERE Nom_ressource = R4);
			SET P5 = (SELECT Prix_hdv FROM Ressource WHERE Nom_ressource = R5);
			SET P6 = (SELECT Prix_hdv FROM Ressource WHERE Nom_ressource = R6);
			SET P7 = (SELECT Prix_hdv FROM Ressource WHERE Nom_ressource = R7);
			SET P8 = (SELECT Prix_hdv FROM Ressource WHERE Nom_ressource = R8);
 
			SET cout = (Q1 * P1) + (Q2 * P2) + (Q3 * P3) + (Q4 * P4) + (Q5 * P5) + (Q6 * P6) + (Q7 * P7) + (Q8 * P8);
 
			--Mise à jouer des colonnes de prix 
			UPDATE Item
			SET Prix_craft = cout
			WHERE Nom_item = v_nom_item;
 
			UPDATE Item
			SET Pourcentage_benefice = cout / v_prix_hdv
			WHERE Nom_item = v_nom_item;
 
		END LOOP
 
	CLOSE curseur_item;
 
END |;

Le problème semblant se situer ici à la ligne 25 :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
-- Déclaration du curseur
	DECLARE curseur_item CURSOR FOR	
		SELECT Nom_item, Prix_hdv
		FROM Item_temp;

Puisque je recois le message d'erreur suivant :

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE curseur_item CURSOR FOR SELECT Nom_item, Prix_hdv FROM Item_temp;' at line 23

De mon point de vue, la syntaxe a l'air correct, c'est exactement celle que j'ai trouvé un peu partout !

Si vous avez une solution ou des pistes à explorer je suis ouvert aux aides.

Merci beaucoup d'avance pour vos réponses.

[aieeeuuuuu]

Bonjour,

vous avez quelques problèmes de modélisation en amont. Si vous reglez ces problèmes, vous n'aurez plus besoin de ce curseur (en fait vous n'en avez deja pas besoin...), vous pourriez même vous passer du trigger, et connaitre le prix d'un item avec une simple requête.

1/ les clefs primaire en VARCHAR(100), c'est une mauvaise idée... choisissez une clef primaire numérique, auto-incrémentée, non significative. Comment ferez vous si vous devez changer le nom d'une ressource ? vous devrez changer également toutes les ligne de la table recette qui référencent la ressource...

2/ votre table Recette est mal conçue. Comment ferez vous si un item contient neuf ressources ? vous devrez changer voter modèle... par ailleurs cette modélisation complique les requetes, en plus de compliquer la tache pour le SGBD. votre table recette devrait avoir quatre colonnes :
- identifiant de l'item
-identifiant de la ressource
- numéro(rang) de la ressource (dans votre cas, de 1 à 8)
- la quantité
Avec une telle modélisation, connaitre le prix d'un item se résumerait à peu prés a ceci

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
SELECT I.Nom_item, SUM(R.prix_hdv * IR.quantite) as PxixTotal
FROM Item I
INNER JOIN Recette IR ON IR.Nom_item = I.Nom_item
INNER JOIN Ressource R ON Ressource.IDREssource = IR.IDRessource
GROUP BY I.Nom_Item

[Akirouille]

Coucou,

merci pour ta réponse aieeeuuuuu.

Effectivement, j'me suis laisser aller en erreur de base sur la modélisation ...
Je vais changer comme vous me l'avez proposer et vais voir ce qu'il en ressort.

Par contre, vous m'avez dit qu'un trigger n'était pas nécessaire hors je ne vois pas comment automatiser autrement la mise à jour des prix des items.
Je rappel que dans mon jeu, je compte mettre à jour régulièrement la table des ressources manuellement et j'aurais aimer connaitre le prix automatiquement dans la table des items. Je compte reprendre cette table par la suite pour la mettre dans un petit php pour décorer et me faire une 'tite mise en page plaisante

Merci encore pour la réponse, j'ai essayer de trouver un moyen d'automatiser la chose hier sans trigger, mais pas moyen de trouver

[aieeeuuuuu]

Bonjour,

Le prix étant une valeur calculée, elle ne devrait pas être faire l'objet d'une colonne dans une table.

Pour votre page PHP, vous pourrez effectuer le requête que je vous ai données. Avec les bons index, cela ne devrait pas poser de problème de performance, surtout sur une faible volumétrie.

Cependant, pour bien faire, vous devriez créer une vue à partir de cette requête, reprenant les colonnes de la table Item, et le prix total. Depuis vos pages PHP, vous effectuez vos requêtes sur cette vue.
Il me semble que MySQL ne propose pas les vue matérialisées, mais si vous rencontrez plus tard des problèmes de performance, vous pourrez alors envisager des solutions (ajouter votre colonne prixTotal dans votre table Item et mettre en place votre trigger et modifier votre vue en conséquence par exemple) et ainsi ne rien avoir à modifier dans votre code PHP.