Insertion dans une table relationnelle

Version imprimable

Bonjour,

j'utilise Postgresql.
je possède une bdd avec deux tables produit et commande et une table relationnelle produit_commande.
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 table t_produit ( cli_Id integer, cli_Libelle varchar(32) ); table t_commande ( cmd_Id integer auto_increment, cmd_Date date ); table r_prod_cmd ( cmd_Id integer, prod_Id integer, qute integer, remise integer );
J'aimerais développé un formulaire qui permet de saisir de nouvelle commandes. La clef de la table commande est auto-incrementée. J'utilise pour cela l'astuce fournit par SQLPro, l'utilisation d'une autre table possédant les valeurs des clefs.
Ainsi, mon formulaire consiste à choisir les produits rattachés à la commande. Une fois que l'utilisateur valide, le script php doit permettre de remplir la table commande et la table relationnelle.
Ma difficulté est de remplir la table relationnelle r_prod_cmd. Je dois connaître l'id de la commande crée, mais celui-ci est auto-incrementé. Donc depuis mon script je ne peux le connaître. A moins qu'il y ait une solution.
Je ne peux donc exécuter séparément, l'insertion à la table commande et l'insertion à la table relationnelle. Dois-je créer une procédure stockée qui gère l'ajout de nouvelles commandes ?

Merci pour votre aide.

++

30/10/2005, 21h01
etiennegaloup

Snif, Snif pas de réponse.
Je m'étais dit que cela devait être un problème rencontré fréquemment.

++
30/10/2005, 23h43
TheLeadingEdge

Bonjour,

Il faudrait en savoir plus sur le fonctionnement de ton appli. pour te conseiller. A priori, comme ça, je te proposerais de mémoriser ttes tes lignes de commandes et de n'enregistrer le tout (n° de commande, commande et lignes, maj des stocks éventuellement etc...) en 1 seule fois dans une transaction que lorsque ton utilisateur valide sa commande.

A +
31/10/2005, 09h50
etiennegaloup

Bonjour,

Citation:

A priori, comme ça, je te proposerais de mémoriser ttes tes lignes de commandes et de n'enregistrer le tout (n° de commande, commande et lignes, maj des stocks éventuellement etc...) en 1 seule fois dans une transaction que lorsque ton utilisateur valide sa commande

Oui j'aimerais bien faire ainsi. Mais:

Une nouvelle commande est crée. Est alors ajouté à la table t_commande, la date de la commande. Le numéro de commande lui est automatiquement généré. En effet, sur un trigger insertion, une fonction est appelée et génère automatiquement, un nouveau numéro de commande en incrémentant de 1 le dernier numéro ajouté et en concaténant le numéro obtenu avec la chaîne 'COM'.
Ainsi, j'ai crée mon numéro de commande.
Mais voilà, ensuite, pour insérer dans la table relationnelle, la liste des produits se rapportant à la commande, je dois connaître le numéro de commande.
Je m'étais dit que ce problème était connu, ou avais déjà été rencontré. Comment faites-vous dans ce cas, pour insérer dans une table relationnelle un id généré automatiquement ?
Il faut développer une procedure stockée, qui gère toute la transaction ?

Merci pour votre aide.
++
31/10/2005, 10h46
Admin

Citation:

Envoyé par etiennegaloup

un nouveau numéro de commande en incrémentant de 1 le dernier numéro ajouté et en concaténant le numéro obtenu avec la chaîne 'COM'.

Ca c'est extremement dangereux, si 2 personne valide une commande en meme temps, tu te retrouvera avec 2 commandes portant le meme numéro.

concernant ton probleme d'increment auto, personnellement une fois que j'ai inséré la ligne de commande, je fais un select sur la table avec des critères CLAIREMENT discriminant pour trouver l'ID.

Par exemple, dans une de mes applications, j'enregistre l'utilisateur et la station de saisie, je fait donc une requete pour trouver le dernier enregistrement fait pour cet utilisateur et cette station, il est impossible que le meme opérateur sur la meme station valide 2 commandes en moins de 1 minute.

Il existe plusieurs méthodes plus ou moins correctes :
http://nepomiachty.developpez.com/index_access/
31/10/2005, 11h53
zekey
Salut,

sous postgres tu peux faire nextval('foo_bar_seq')
et le mettre dans une variable pour le reutiliser apres.

Pour la table:
Code:

1 2 3 4 create sequence foo_bar_seq; create table foo (bar integer unique not null default nextval('foo_bar_seq'));
--------------------------------------------------------------------
Steve Hostettler
ze_key@hotmail.com / www.zekey.net
31/10/2005, 11h53
etiennegaloup

Citation:

Ca c'est extremement dangereux, si 2 personne valide une commande en meme temps, tu te retrouvera avec 2 commandes portant le meme numéro.

Ah ouais, c juste.
J'ai suivi le tutoriel de SQLPro. Clefs auto incrémentées.
http://sql.developpez.com/clefs/
Je vais aller le relire, des fois que j'aurais loupé quelque chose.

Mais si je mets l'insertion d'une nouvelle commande dans une transaction, ça ne pourrait pas aller ?

Je vais aller voir le lien que tu m'as filé,
merci
++
31/10/2005, 12h06
Admin

Citation:

Envoyé par etiennegaloup

Ah ouais, c juste.
J'ai suivi le tutoriel de SQLPro. Clefs auto incrémentées.
http://sql.developpez.com/clefs/
Je vais aller le relire, des fois que j'aurais loupé quelque chose.

Ca c'est sur car SQLPro désapprouve completement cette manière de faire.

Citation:

Envoyé par etiennegaloup

Mais si je mets l'insertion d'une nouvelle commande dans une transaction, ça ne pourrait pas aller ?

La ca dépend de la perméabilité de la transaction, dans le cas d'une transaction nonperméable, le probleme sera encore plus violent.
31/10/2005, 12h27
etiennegaloup

Citation:

un nouveau numéro de commande en incrémentant de 1 le dernier numéro ajouté et en concaténant le numéro obtenu avec la chaîne 'COM'.

Quand je disais ça, je n'ai pas précisé, mais le numéro incrémenté de 1 figure dans une table clef. Ainsi, si une nouvelle valeur est ajouté, le numéro est automatiquement incrémenté, et modifié dans la table clef. Le numéro obtenu est concaténé à la chaîne de caractère 'COM'. J'obtiens alors le numéro de commande, et je l'ajoute à ma table commande.
C mieux comme ça ?

Parce que effectivement, si je prends la valeur max des clefs de la table commande, là je reconnaît que ça craint. Mais en utilisant, une table clef, ça pose moins de problème, n'est-ce pas ?

En conclusion,
je peux réaliser une fonction d'insertion de nouvelles commandes.
Cette fonction me retourne le numéro de commande auto-généré avec la méthode décrite ci-dessus.
Avec ce numéro de commande, je peux éditer la table relationnelle en insérant les articles rattachés à la commande.
Qu'en pensez-vous ?

++
31/10/2005, 13h11
Admin

Citation:

Envoyé par etiennegaloup

Quand je disais ça, je n'ai pas précisé, mais le numéro incrémenté de 1 figure dans une table clef. Ainsi, si une nouvelle valeur est ajouté, le numéro est automatiquement incrémenté, et modifié dans la table clef. Le numéro obtenu est concaténé à la chaîne de caractère 'COM'. J'obtiens alors le numéro de commande, et je l'ajoute à ma table commande.
C mieux comme ça ?

Toujours pas. Le probleme est le meme, rien n'empeche que 2 select soit fait.

Exemple :
Max(Cle) = 48

Si 2 personne lance la meme action :
Temps 1 : Process 1 -> Lecture Clé
Temps 2 : Process 2 -> Lecture Clé
Temps 3 : Process 1 -> Mise à jour Clé = Clé +1
Temps 4 : Process 2 -> Mise à jour Clé = Clé +1

Process 1 et 2 on la meme clé 49 et en plus la clé 50 n'est pas utilisé.

Toute opération du type select max(champ) from table, quelque soit la table n'est pas une bonne solution.
31/10/2005, 13h21
Admin

Olivier propose une solution pas mal, c'est de generer un numéro aléatoire que tu met dans un champs quelconque lors de ton INSERT.

Tu fais un

Code:

Select id where monchamp=monnombrealeatoire

La probabilité que 2 personnes fasse un insert en meme temps avec le meme nombre aléatoire généré est quasiment impossible.

Une fois que tu as récupéré ton ID, tu vide le champs qui contient la valeur aléatoire.

Maintenant tu es sur que l'ID que tu as est bien le tiens et pas celui du voisin.

:lol:
Je reconnais que je n'ai pas été du tout très clair.

En fait, je n'utilise pas select(max).

Comme il est conseillé dans l'article de SQLPro, j'utilise un trigger et une fonction.

Voici le trigger:
Code:

1 2 3 4 CREATE TRIGGER trgInsCommande BEFORE INSERT ON fournisseur FOR EACH ROW EXECUTE PROCEDURE insCommande();
et voici la fonction:
Code:

1 2 3 4 5 6 7 8 9 10 CREATE FUNCTION insCommande() RETURNS "trigger" AS ' begin new.commandeId=((''COM'')||((select clef from cle where nomtable like ''commande'')+1)); update cle set clef=clef+1 where nomtable like ''commande''; return new; end; ' LANGUAGE plpgsql;
Et voili, voilou.

Alors cette fois-ci, ça va ? C mieux, non ?
Et donc, je n'ai plus qu'à rajouter dans cette fonction un renvoi de la clef insérée. Ainsi mon formulaire php, récupérera cette clef, et l'utilisera pour ajouter dans la table relationnelle, les articles rattachés à la commande.

Qu'en pensez-vous ?
++

31/10/2005, 13h51
etiennegaloup

Citation:

Olivier propose une solution pas mal, c'est de generer un numéro aléatoire que tu met dans un champs quelconque lors de ton INSERT.

Ouais c plutôt bien ça.

Et ma proposition précédente ?
31/10/2005, 13h56
zekey

En quoi la solution de la sequence et du nextval, ne résout pas ton probleme de facon propre ?

--------------------------------------------------------------------
Steve Hostettler
ze_key@hotmail.com / www.zekey.net
31/10/2005, 14h01
etiennegaloup

Si cette solution est super aussi,
je vais la tester.
++
31/10/2005, 14h03
Admin
Pour moi, le fait de faire un
Code:

1 2 SELECT clé+1 UPDATE clé=clé+1
Pose toujours le meme probleme, si quelqu'un lance également un insert avant que l'autre soit fini, tu te retrouve de nouveau avec un truc bizarre.

Exemple :
Temps 1 : (Proc 1) Insert dans la table Commande (clé 49 en auto-incrément)
Temps 2 : (Proc 1) Trigger déclenché, lecture clé : 48
Temps 3 : (Proc 2) Insert dans la table Commande (clé 50 en auto-increment)
Temps 4 : (Proc 2) Trigger déclenché, lecture clé : 48
Temps 5 : (Proc 1) update clé : 48+1
Temps 6 : (Proc 1) update clé : 48+1

Moralité ta proc te renvoi 49 les 2 fois, alors que c'est vrai juste dans 1 des 2 cas.

Effectivement le principe d'un SELECT suivi immediatement d'un UPDATE par trigger limite la probabilité, mais elle n'est pas nulle.

C'est une solution satisfaisante.
31/10/2005, 14h41
hpalpha

Bonjour,

je n'avais pas vu ce post, mais tu aurais du le mettre sur le forum PostgreSQL directement.
Pour ton probleme de clef autoinc, pg dispose de sequence, c'est un moyen sur et efficace, tu trouvera un tuto dessus ici :
http://dgriessinger.developpez.com/p...sql/sequences/
31/10/2005, 15h34
etiennegaloup
Citation:
Pour moi, le fait de faire un

Code:

1 2 3 SELECT clé+1 UPDATE clé=clé+1

Pose toujours le meme probleme, si quelqu'un lance également un insert avant que l'autre soit fini, tu te retrouve de nouveau avec un truc bizarre.
Ok, je vois (enfin) ce que tu veux dire. Il est vrai qu'il y a un risque qui n'est pas insignifiant. Merci en tout cas, pour tes réponses, et ta patience.

Citation:

Envoyé par ze_key

Salut,

sous postgres tu peux faire nextval('foo_bar_seq')
et le mettre dans une variable pour le reutiliser apres.

Suite à ta réponse et à la réponse de hpalpha, j'ai lu le tuto sur les séquences et je pense effectivement, que je vais me tourner vers les séquences.

Merci à tous pour vos nombreuses réponses.

++
31/10/2005, 20h35
TheLeadingEdge

Citation:

Ca c'est extremement dangereux, si 2 personne valide une commande en meme temps, tu te retrouvera avec 2 commandes portant le meme numéro.

Citation:

Pose toujours le meme probleme, si quelqu'un lance également un insert avant que l'autre soit fini, tu te retrouve de nouveau avec un truc bizarre.

C bien pour ça que j'avais conseillé de faire ttes les maj ds 1 transaction. Tant que tu n'a pas fait 'commit' ou 'rollback' personne ne peux modifier quoi que ce soit ds les tables.
01/11/2005, 00h38
Admin

Citation:

Envoyé par TheLeadingEdge

Tant que tu n'a pas fait 'commit' ou 'rollback' personne ne peux modifier quoi que ce soit ds les tables.

J'aimerai bien voir ça ??????
Le principe de la transaction permet de garder l'unicité d'un traitement en aucun cas de gérer des problèmes de concurrence d'accès à l'insertion (enfin pas dans ce cas là).

Hors mode propre à Postgres ou mode transactionnelle particulier, les 95% de transaction utilisé sont dites non perméable, c'est à dire que les opérations effectué avant le COMMIT ne sont pas validé dans la base et donc pas disponible pour un SELECT hors transaction.

Par exemple, si tu insère dans une table Commande une ligne de commande, puis dans une table commande_article la liste des articles commandés, il faut une transaction car la commande et son détail est une opération atomique au sens fonctionnel.

Il ne faut pas que quelqu'un puisse voir la commande avant que le détail ne soit enregistré dans la base, d'ou le principe de transaction.

Justement dans notre cas il faut au contraire ABSOLUMENT que les autres curseur soient averti de l'utilisation d'un numero de clé.

Bonjour,

Comme je ne suis plus sur de rien, je récapitule ce que j'ai compris du pb :
Créer 1 cde avec 1 n° auto
Créer les lignes de cette cde
2 contraintes :
il faut renseigner le N° de cde ds las lignes.
il ne faut pas mélanger les lignes de plusieurs cdes.

J'ai essayé ça ce matin :

Création schéma

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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
DROP SEQUENCE seqprod;
DROP SEQUENCE seqcmde;
CREATE SEQUENCE seqprod;
CREATE SEQUENCE seqcmde;
 
DROP TABLE r_prod_cmd;
DROP TABLE t_commande;
DROP TABLE t_produit;
CREATE TABLE t_produit 
( 
   	prod_Id INTEGER NOT NULL DEFAULT NEXTVAL ('seqprod'),
   	prod_Libelle VARCHAR(32) NOT NULL,
	PRIMARY KEY (prod_Id)
) TABLESPACE "DVP";
 
CREATE TABLE t_commande 
( 
   	cmd_Id INTEGER NOT NULL DEFAULT NEXTVAL ('seqcmde'),
   	cmd_Date TIMESTAMP NOT NULL,
	PRIMARY KEY (cmd_Id)
) TABLESPACE "DVP";
 
CREATE TABLE r_prod_cmd 
( 
   	cmd_Id INTEGER NOT NULL,
   	prod_Id INTEGER NOT NULL,
   	qte INTEGER NOT NULL,
	PRIMARY KEY (cmd_Id, prod_Id),
	FOREIGN KEY (cmd_Id) REFERENCES t_commande (cmd_Id),
	FOREIGN KEY (prod_Id) REFERENCES t_produit (prod_Id)	
) TABLESPACE "DVP";
 
INSERT INTO t_produit (prod_Libelle)
(
SELECT 'Produit 1' 
UNION ALL
SELECT 'Produit 2'
UNION ALL
SELECT 'Produit 3'
UNION ALL
SELECT 'Produit 4'
);

Insertion d'1 cde et de ces lignes :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
BEGIN TRANSACTION;
 
INSERT INTO t_commande (cmd_Date)
(
SELECT current_date
);
 
INSERT INTO r_prod_cmd (cmd_Id, prod_Id, qte) 
(
SELECT MAX(cmd_Id), 1, 10 FROM t_commande
);
INSERT INTO r_prod_cmd (cmd_Id, prod_Id, qte) 
(
SELECT MAX(cmd_Id), 3, 30 FROM t_commande
);

J'ai volontairement omis le COMMIT, et j'ai lancé plusieurs autres insertions dans des fenêtres SQL différentes. Elles sont ttes restées bloquées par le 'LOCK' tant que je n'ai pas committé la première par COMMIT WORK;
C'est ce que je voulais obtenir :
-La création d'une cde avec 1 id autoincrementé
-les lignes de cdes contiennent bien l'id de la cde.
-tant que cette cde la n'est pas validée, on ne peut pas en insérer 1 autre, donc pas de risque de mélange.

@Gaël Donat: Est-ce que j'ai 1 risque en faisant comme ça ? J'ai tjours cru que c'était bon, mais comme ça ne semble pas aussi simple, merci de me corriger.

NB : J'ai fait la même chose avec SQL Server avec le même résultat.

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
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
USE DVP;
 
DROP TABLE r_prod_cmd;
DROP TABLE t_commande;
DROP TABLE t_produit;
CREATE TABLE t_produit 
( 
   	prod_Id INTEGER NOT NULL IDENTITY (1,1), 
   	prod_Libelle VARCHAR(32) NOT NULL,
	PRIMARY KEY (prod_Id)
); 
 
CREATE TABLE t_commande 
( 
   	cmd_Id INTEGER NOT NULL IDENTITY (1,1),
   	cmd_Date DATETIME NOT NULL,
	PRIMARY KEY (cmd_Id)
); 
 
CREATE TABLE r_prod_cmd 
( 
   	cmd_Id INTEGER NOT NULL,
   	prod_Id INTEGER NOT NULL,
   	qte INTEGER NOT NULL,
	PRIMARY KEY (cmd_Id, prod_Id),
	FOREIGN KEY (cmd_Id) REFERENCES t_commande (cmd_Id),
	FOREIGN KEY (prod_Id) REFERENCES t_produit (prod_Id)	
);
 
INSERT INTO t_produit (prod_Libelle) 
(
SELECT 'Produit 1'
UNION ALL
SELECT 'Produit 2'
UNION ALL
SELECT 'Produit 3'
);
 
--SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
 
INSERT INTO t_commande (cmd_Date)
(
SELECT getdate()
);
 
INSERT INTO r_prod_cmd (cmd_Id, prod_Id, qte) 
(
SELECT MAX(cmd_Id), 1, 10 FROM t_commande
);
INSERT INTO r_prod_cmd (cmd_Id, prod_Id, qte) 
(
SELECT MAX(cmd_Id), 3, 30 FROM t_commande
);
 
COMMIT WORK;
--SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

01/11/2005, 16h41
Admin

Citation:

Envoyé par TheLeadingEdge

@Gaël Donat: Est-ce que j'ai 1 risque en faisant comme ça ? J'ai tjours cru que c'était bon, mais comme ça ne semble pas aussi simple, merci de me corriger.

Non c'est correct au sens fonctionnel, le lock empeche evidemment que 2 personnes se voient assigné le meme identifiant de commande, ce qui est ce qu'on veut, donc pour répondre au probleme c'est correct.

Maintenant imaginons que l'outil de commande soit utilisé par 800 personnes chargés de prendre des commandes, si chaque vendeur attend son tour pour que la base donne un numéro de commande, le dernier arrivé risque d'attendre fort longtemps, c'est ce qu'on nomme la contention.

Mon premier exemple avec un select sur des critères discriminants permet de ne pas avoir des soucis de cet ordre là, prenons un exemple concret :

Imaginons 25 opérateurs (avec un login pour chacun du type "OP1", "OP2"..."OP25") qui valide ensemble la saisie de 25 commandes, le moteur de base de données va ajouter 25 numéro de commande en meme temps dans la base de données.

Comme c'est le SGBD qui s'occupe d'attribuer des numéros, nous n'avons pas de soucis de clé primaire doublonnés, c'est impossible car c'est un auto-incrément.

Notre problème réside dans le fait de trouver quel numéro de commande est attribué par le SGBD pour chaque opérateurs car l'application à besoin de savoir quel est le numéro attribué pour inserer ensuite le détail des commandes dans la table détail-commande avec comme clé étrangère le fameux numéro de commande.

Si on fait un SELECT Max(num_commande) from commandes; la requete va invariablement nous renvoyer le meme numéro de commande pour les 25 opérateurs, ce qui à l'evidence n'est pas correcte.

Une solution consiste donc à trouver une autre information discriminante, dans mon cas j'utilise le login de l'opérateur.

J'insere pour chaque commande le login ("OPxx") de l'opérateur qui a saisie la commande.

Pour trouver le numéro de la commande que l'opérateur à saisie j'ai juste à faire :
SELECT Max(num_commande) from commandes where oper="OP1";

Ma requete me donne la dernière commande saisie par l'opérateur OP1, c'est exactement ce que je veux savoir.

Et le résultat est evidemment différent si je met OP2 ou OP3

En faisant ça de 1 à 25, je retrouve bien mes 25 ID de commande pour chaque opérateur.

Je peux donc continuer mon insertion sans risque de chevauchement.

Je ne sais pas si je suis clair.
01/11/2005, 17h39
TheLeadingEdge

re,

Ouf...Tu me rassures ! J'avoue que ton précédent post m'avait 1 peu inquiété !

Citation:

Envoyé par Gaël Donat

Je ne sais pas si je suis clair.

Limpide !

Pour les cas ou 'locker' la table par 1 tx est inapproprié (trop de tables, ou trop long, ...) , j'utilise 1 'lock' logique, 1 timestamp ds le dernier tuple inséré ds la table, qui est non nul tant qu'une 'transaction' est en cours, ce qui indique aux autres qu'il faut attendre. Mais pour que ça marche, il faut que tt le monde fasse la même chose (ce qui n'est pas gagné tant qu'on aura pas encapsulé les ordres SQL ds des objects spécialisés, mais là je disgresse).
Ta solution est nettement plus souple. Je l'ai dèjà rangée ta ds ma 'boite à trucs utiles'.

Merci pour ta réponse,

A +