[PB CONCEPTUEL] avec compteur/trigger

**kase74** · 23/03/2004, 12h57

Bonjour, je vais essaye d'exposer mon pb le plus simplement possible :
J'ai 2 tables :

Code :

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

1
2
3
4
5
6
TABLEA (
CHAMP_A1  DATE
CHAMP_A2  INTEGER
CHAMP_A3 INTEGER
...
PRIMARY KEY (CHAMPS_A1,CHAMP_A2) )

Code :

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

1
2
3
4
5
6
7
TABLEB (
CHAMP_B1  DATE
CHAMP_B2  INTEGER
CHAMP_B3  INTEGER
...
PRIMARY KEY (CHAMP_B1, CHAMP_B2, CHAMP_B3)
FOREIGN KEY (CHAMP_B1, CHAMP_B2) REFERENCES TABLEA (CHAMPS_A1,CHAMP_A2) )

TABLEA est la table de mes entete de dossier. avec CHAMP_A1 = Date de creation de l'entete, CHAMP_A2 = numero sequentiel du dossier cree dans la meme journee. CHAMP_A3 = Nombre de ligne de detail que cet entete comprend.

TABLEB est la table de mes lignes de details de dossier. CHAMP_B1/CHAMP_B2 = liaison avec l'entete. CHAMP_B3 = numero sequentiel de la ligne cree pour un meme entete.

Question 1 : Pour determiner CHAMP_A2, je n'ai pas trouve mieux que de faire une procedure stockee que je doit appeler avant chaque INSERT TABLEA pour avoir le bon numero sequentiel. Pour cela je passe par un compteur, et Chaque jour, je remets le generator a zero.
* Est ce que ce ne sera pas un peu trop lourd dans la pratique sachant qu'il y aura plusieurs appels INSERT a la minute en multi-utilisateur ?
* Est ce que quelqu'un voit une meilleure solution ?

CODE DE LA PROCEDURE STOCKEE :

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
PROCEDURE "PROC_CLE_ENTETE"
RETURNS( "DATEENTETE" DATE, "HEUREENTETE" TIME, "NUMENTETE" INTEGER)
AS
   declare variable i integer;
   declare variable n integer;
begin
   DateEntete = CURRENT_DATE;
   HeureEntete = CURRENT_TIME;
   /* GESTION DE L'INCREMENTATION DE NUM_ENTETE AVEC LE GENERATOR GEN_NUM_ENTETE */
   /* JE VEUX QUE CHAQUE JOUR LE GENERATOR REPARTE A ZERO*/
   select max(Num_Entete)
   from entete_dossier_encours
   where entete_dossier_encours.date_entete = :DateEntete
   into i;
   n = GEN_ID(GEN_NUM_ENTETE, 1);
   if (i is null) then
      /* Remise a zero du generator par incrementation negative */
      /* car impossible de le faire avec SET GENERATOR => interdit dans procedures ou triggers */
      NumEntete = GEN_ID(GEN_NUM_ENTETE,-n+1);
   else
      NumEntete = n;
end

Question 2 : Pour determiner CHAMP_B3, impossible de passer par un generator puisque je ne peux pas gerer dynamiquement un generator par entete. Donc je n'ai rien trouve d'autre que de passer par un champ renfermant le nombre de ligne (CHAMP_A3) que j'incremente a chaque INSERT TABLEB. VRAI GROS PROBLEME : A chaque INSERT TABLEB, je dois acceder a CHAMP_A3 2 fois : en lecture pour connaitre le prochain numero de ma ligne et en update pour l'incrementer.
* Encore une fois, est ce que c'est la meilleure solution ?
* Comment etre sur qu'il n'y aura pas de conflits lorsque 2 utilisateurs feront des INSERT TABLEB en meme temps ? J'ai bien cru comprendre qu'il fallait passer par les transactions mais je n'ai pas vraiment compris comment ca fonctionnait.

CODE DU TRIGGER GERANT LE NUMERO DE TABLEB :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
(...)
   /* Renseignement de la zone NUM_LIGNE */
   UPDATE ENTETE_DOSSIER_ENCOURS E
		SET E.NB_LIGNE = E.NB_LIGNE+1
		WHERE E.DATE_ENTETE= NEW.DATE_ENTETE
		  AND E.NUM_ENTETE = NEW.NUM_ENTETE;
   SELECT NB_LIGNE,
		FROM ENTETE_DOSSIER_ENCOURS E
		WHERE E.DATE_ENTETE= NEW.DATE_ENTETE
		  AND E.NUM_ENTETE = NEW.NUM_ENTETE
		INTO NEW.NUM_LIGNE;
(...)

Merci d'avance pour votre aide.

**Barbibulle** · 23/03/2004, 15h46

Que répondre mis à part que vous vous compliquez la vie à cause de la structure de vos tables...

Votre solution 1 avec une PS ne fonctionnera pas en mode multi utilisateur non plus malgrès l'utilisation d'un générateur...
Pourquoi ?
a) Si deux personnes insèrent en même temps la première entète de la journée. Chacun aura un i à null (dans la PS) et donc chacun va remettre à zero le générateur et donc optenir le même numéro...
b) Si vous annuler une transaction après avoir exécuté votre procédure, le numéro est perdu et donc vous arrez des trous dans votre numérotation.

Et tout ces problèmes viennent du fait que vous essayer d'enregistrer dans votre table des données qui peuvent être facilement déduites (et donc son inutiles) et en plus vous les mettez dans votre clé primaire.

Votre table A serait bien plus facile à gérer si vous utilisiez qu'un integer comme clé primaire (géré classiquement par un générateur + trigger) et sur votre date ajoutez un index.
Vous allez me dire oui mais cet integer s'il n'est jamais remis à zero il ne représente pas un numéro d'ordre comme le Champ_A2... oui c'est vrai mais Champ_A2 est très facilement calculable s'il vous le faut vraiment grace à une petite PS par exemple.

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
 
CREATE TABLE TABLEA (
    ID      INTEGER NOT NULL,
    CHAMP_A1  DATE
);
ALTER TABLE TABLEA ADD CONSTRAINT PK_TABLEA PRIMARY KEY (ID);
CREATE INDEX TABLEA_IDX1 ON TABLEA (CHAMP_A1);
 
 
 
CREATE PROCEDURE TABLEA_S 
RETURNS (
    ID INTEGER,
    CHAMP_A1 DATE,
    CHAMP_A2 INTEGER)
AS
DECLARE VARIABLE DT DATE;
BEGIN
  DT = NULL;
  FOR SELECT ID,
             CHAMP_A1
      FROM TABLEA
      order by CHAMP_A1, ID
      INTO :ID,
           :CHAMP_A1
  DO
  BEGIN
    IF ((DT<>CHAMP_A1) or (DT is null)) then
    BEGIN
      DT = CHAMP_A1;
      CHAMP_A2 = 0;
    END
    SUSPEND;
    CHAMP_A2 = CHAMP_A2 +1;
  END
END

Voilà reste plus qu'à faire un simple

Code :

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

Select CHAMP_A1, CHAMP_A2 from TABLEA_S

Pour CHAMP_A3 il suffit de modifier la PS pour faire un

Code :

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

select count(ID) from TABLEB where FK_TABLEA_ID=:ID INTO :CHAMP_A3;

Bien entendu je modifierai tableB également de la même manière un ID de type integer qui sert de cle primaire et une clé étrangère FK_TABLEA_ID de type integer qui pointe vers la table A.
Et règlerai donc champ_B3 de la même manière...

Par contre si champ_A2 et champ_B3 dans mon systeme ne peuvent identifier une ligne seul ID le peux.
Tout dépend donc de ce que vous vouliez mettre dans ces champs.
Si vous voulez qu'ils concervent le même numéro et donc accepter les trous dans la numérotation :

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
CREATE PROCEDURE TABLEA_S 
RETURNS (
    ID INTEGER,
    MADATE DATE,
    CHAMP_A2 INTEGER)
AS
DECLARE VARIABLE DT DATE;
DECLARE VARIABLE ID2 INTEGER;
BEGIN
  DT = NULL;
  FOR SELECT ID,
             MADATE
      FROM TABLEA
      order by MADATE, ID
      INTO :ID,
           :MADATE
  DO
  BEGIN
    IF ((DT<>MADATE) or (DT is null)) then
    BEGIN
      DT = MADATE;
      ID2 = ID;
    END
    CHAMP_A2 = ID - ID2;
    SUSPEND;
  END
END

Et le tour est joué...
Bon courrage.

**kase74** · 23/03/2004, 16h34

Tres juste Barbibulle, vous avez raison. Mais si j'ais fait comme ca c'est pour eviter un jour d'arriver hors limite du generator !
Et ce que vous me proposez de faire revient a cela.
Et je ne peux pas imposer de mettre les tables a vide pour remettre les generators a zero.
N'est-il vraiment pas possible de verouiller le multi-acces le temps de ces requettes ? Je pense que les transactions sont faites pour ca, je me trompes ? Si oui, comment fait-on ?

**Barbibulle** · 23/03/2004, 17h10

1)Les générateurs sont hors transaction (c'est justement l'intérret) donc lorqu'on leur demande un numéro, avec gen_id le générateur est immédiatement affecté et ce même si on annule la transaction.
2)En effet si vous utilisez interbase et que vous ne savez pas manipuler les transactions je vous suggère très fortement de les étudier pour bien comprendre ce que vous pouvez faire et comment le faire...

3)Avant que vous arriviez au bout d'un générateur, les poules auront des dents.... Un générateur peux prendre 2^65 valeurs soit 36 893 488 147 419 103 232 valeurs différentes. Donc avant que vous ayez autant de lignes dans votre base, j'ai bien peur que vous ne soyez déjà mort car en raison d'une insertion toutes les dix milli-secondes ca ferait 11 690 840 922 années environ à attendre. Sauf erreur 11 milliard c'est presque le temps que l'univers à mis pour se créer (à 3 milliard pres...).
Je ne suis pas certain non plus que dans 11 milliard d'année (sans vouloir vous offencer) on utilise encore votre programme avec interbase...
Voilà c'est vrai que l'espoir fait vivre mais si vous avez vraiment un truc pour vivre aussi longtemps, je suis interressé d'en connaitre le secret...

**kase74** · 24/03/2004, 09h27

Pfft, certaines fois j'ai honte de mon ignorance !
Vous avez un humour un peu sarcastique barbibulle ! Mais j'adore ! Et puis je l'ai bien merite.

Merci mille fois de nous venir en aide et vive cet excellent forum !

**kase74** · 24/03/2004, 10h43

Quand meme une petite chose qui me tracasse :
OK j'ai vu que la limite du generator est de 2^64.
MAIS que ce soit dans TABLEA ou TABLEB, ID est un INTEGER. Et la plage d'un INTEGER = -2 147 483 648 - +2 147 483 647.
Ce qui veut dire que, a la cadence d'un INSERT toutes les 10 milisecondes, ID explose au bout de 0,68 an, soit 248,5 jours.

Cela dit, pour etre plus realiste, pour mon appli, il y a en moyenne 1 INSERT / Heure.
Et la, ID explosera au bout de 244 978,7 ans. Ce qui est un peu limite parceque non seulement je comptes bien vivre jusque la, mais en plus je pense prochainement prospecter dans la galaxie de Hunazedvar. Et eux, ils vivent bien plus longtemps encore.

(It's a joke of course !)

**Barbibulle** · 25/03/2004, 11h02

Envoyé par kase74

Pfft, certaines fois j'ai honte de mon ignorance !
Vous avez un humour un peu sarcastique barbibulle ! Mais j'adore ! Et puis je l'ai bien merite.

Merci mille fois de nous venir en aide et vive cet excellent forum !

Contant que vous le preniez comme ça, oui souvant je suis un peu saignant. Mais je ne suis pas méchant (du moins c'est pas le but...)

Envoyé par kase74

Quand meme une petite chose qui me tracasse :
OK j'ai vu que la limite du generator est de 2^64.
MAIS que ce soit dans TABLEA ou TABLEB, ID est un INTEGER. Et la plage d'un INTEGER = -2 147 483 648 - +2 147 483 647.
Ce qui veut dire que, a la cadence d'un INSERT toutes les 10 milisecondes, ID explose au bout de 0,68 an, soit 248,5 jours.

Cela dit, pour etre plus realiste, pour mon appli, il y a en moyenne 1 INSERT / Heure.
Et la, ID explosera au bout de 244 978,7 ans. Ce qui est un peu limite parceque non seulement je comptes bien vivre jusque la, mais en plus je pense prochainement prospecter dans la galaxie de Hunazedvar. Et eux, ils vivent bien plus longtemps encore.

(It's a joke of course !)

Oui c'est une tres bonne remarque et en effet integer est sur 32 bits et non 64. Mais si vous voulez vraiment vivre encore plus longtemps utilisez à la place de INTEGER DECIMAL (ou NUMERIC) en 18,0
Si vous êtes bien en dialect 3 alors le NUMERIC(18,0) est en fait enregistré dans un INT64 (un entier sur 64 bits comme les générateurs).
Ce qui vous laisse donc encore plus de temps avant d'arriver à trouver un doublon. (Je vous laisse faire le calcul)...

de -999 999 999 999 999 999 à + 999 999 999 999 999 999 valeurs. Qui à vue de nez ferait tout de même dans les 3 milliards d'années au lieux des 11 annoncé...

[PB CONCEPTUEL] avec compteur/trigger

SQL Firebird

Discussions similaires

Partager

Partager