Discussion :

[fsmrel]

Et hop, un nouveau problème lié à l'identification relative.

Avez-vous essayé d’adapter ce qu’à fait CinePhil ?

Je joins quelque chose que j’ai retrouvé dans mes archives DB2 d’il y a 25 ans, en l’adaptant aux XXIe siècle et à SQL Server. C’est un curseur, donc ça n’est pas ensembliste, mais à l’époque ça marchait très bien (c'était mieux optimisé, mais voyons déjà comment tourne ce que je vous propose).

On est dans le cas où on l’on passe à une procédure (routine à l’époque...) une table de travail, que j’ai renommée en @WTEL et qui contient un peu de tout, notamment des numéros de téléphone pour des personnes n’en ayant pas encore.

Code SQL :

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
87
88
USE TEMP ;
 
IF EXISTS(SELECT name FROM sysobjects WHERE type='U' AND name = 'TELEPHONE')
   DROP TABLE TELEPHONE ;
 
IF EXISTS(SELECT name FROM sysobjects WHERE type='U' AND name = 'PERSONNE')
   DROP TABLE PERSONNE ;
 
CREATE TABLE PERSONNE  
( 
  PsnId             INT           NOT NULL, 
  PsnLibelle        VARCHAR(16)   NOT NULL, 
  CONSTRAINT PSN_PK PRIMARY KEY (PsnId)
) ;
CREATE TABLE TELEPHONE  
( 
  PsnId   INT            NOT NULL , 
  TelId   TINYINT        NOT NULL ,  
  TelNo   VARCHAR(24)    NOT NULL  
  CONSTRAINT TEL_PK PRIMARY KEY (PsnId, TelId), 
  CONSTRAINT TEL_FK FOREIGN KEY (PsnId) REFERENCES PERSONNE (PsnId) ON DELETE CASCADE 
) ;
 
INSERT INTO PERSONNE VALUES (1, 'Albert') ; 
INSERT INTO PERSONNE VALUES (2, 'Bernard') ; 
INSERT INTO PERSONNE VALUES (3, 'Carole') ; 
 
SELECT '' AS 'PERSONNE', * FROM PERSONNE ;
 
INSERT INTO TELEPHONE VALUES (1, 1, '00 33 (0)123 456 789') ; 
INSERT INTO TELEPHONE VALUES (1, 2, '00 33 (0)123 456 790') ; 
INSERT INTO TELEPHONE VALUES (2, 1, '00 33 (0)234 654 322') ; 
INSERT INTO TELEPHONE VALUES (2, 2, '00 33 (0)234 654 323') ; 
INSERT INTO TELEPHONE VALUES (2, 3, '00 33 (0)234 654 324') ; 
 
SELECT '' AS 'TELEPHONE', * FROM TELEPHONE ;
 
DECLARE @WTEL AS TABLE   
( 
  PsnId         INT          NOT NULL, 
  TelNo         VARCHAR(24)  NOT NULL  
) ;
 
INSERT INTO @WTEL VALUES (1, '00 33 (0)123 456 791') ; -- nouveau 
INSERT INTO @WTEL Values (1, '00 33 (0)123 456 792') ; -- et un autre
INSERT INTO @WTEL Values (2, '00 33 (0)234 654 325') ; -- nouveau
INSERT INTO @WTEL Values (2, '00 33 (0)234 654 326') ; -- et un autre
INSERT INTO @WTEL Values (3, '00 33 (0)300 000 001') ; -- le tout 1er pour la personne
INSERT INTO @WTEL Values (3, '00 33 (0)300 000 002') ; -- nouveau
INSERT INTO @WTEL Values (3, '00 33 (0)300 000 003') ; -- nouveau
 
SELECT '' as '@WTEL',* from @WTEL
 
DECLARE @PsnId INT, @TelId TINYINT, @TelNo VARCHAR(24) ;
DECLARE @TournezManege INT ;
 
DECLARE C1 CURSOR FOR
        SELECT PsnId, TelNo
        FROM   @WTEL ;
 
OPEN C1 ;
 
SET @TournezManege = 1
 
WHILE @TournezManege = 1
    BEGIN
        FETCH C1 INTO @PsnId, @TelNo
        IF @@FETCH_STATUS = 0
            BEGIN 
                SET @TelId = (SELECT COALESCE(MAX(x.TelId), 0)
                              FROM   TELEPHONE AS x JOIN @WTEL AS y 
                                     ON x.PsnId = y.PsnId
                              GROUP BY x.PsnId
                              HAVING x.PsnId = @PsnId
                             ) ;
                SET @TelId = CAST(COALESCE(@TelId,0) as INT) + 1
                INSERT INTO TELEPHONE (PsnId, TelId, TelNo) VALUES (@PsnId, @TelId, @TelNo)
            END
        ELSE
            BEGIN 
                SET @TournezManege = 0
            END            
    END
 
CLOSE C1 ;
DEALLOCATE C1 ;
 
SELECT '' AS 'TELEPHONE après', * FROM TELEPHONE ;

N.B. Concentrez vos messages ici...

P.-S. Notez qu'il n'y a pas de trigger.

[Kropernic]

Avez-vous essayé d’adapter ce qu’à fait CinePhil ?

C'est bien sûr la première chose que j'ai faite ^^.
Et j'ai produit ceci :

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
USE [GIFT_MANAGEMENT]
GO
/****** Object:  Trigger [dbo].[TR_INSTEAD_OF_INSERT_ACT]    Script Date: 10/08/2012 09:10:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[TR_INSTEAD_OF_INSERT_ACT] ON [dbo].[T_ACTIVATION_ACT]
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;
    
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

    BEGIN TRANSACTION
    SELECT 
            ROW_NUM = ROW_NUMBER() OVER(ORDER BY GFT_ID),
            *
    INTO    
            #INS
    FROM
            INSERTED;        
    
    DECLARE @ITER INT;
    DECLARE @COUNT INT;
    
    SELECT @ITER=MIN(ROW_NUM) FROM #INS;
    SELECT @COUNT=MAX(ROW_NUM) FROM #INS;
    
    WHILE @ITER <= @COUNT
    BEGIN
    
        INSERT INTO T_ACTIVATION_ACT(GFT_ID, ACT_ID, DOS_ID, ACT_DATE, ACT_VALUE)
        SELECT
                INS.GFT_ID,
                COALESCE(MAX(ACT.ACT_ID),0)+1,
                INS.DOS_ID,
                INS.ACT_DATE,
                INS.ACT_VALUE
        FROM
                T_ACTIVATION_ACT ACT
                    RIGHT OUTER JOIN #INS INS
                        ON    ACT.GFT_ID = INS.GFT_ID
        WHERE
                INS.ROW_NUM = @ITER
        GROUP BY
                INS.GFT_ID,
                INS.DOS_ID,
                INS.ACT_DATE,
                INS.ACT_VALUE;
        
        SET @ITER = @ITER+1;
    END
    
    DROP TABLE #INS;
    
    IF @@ERROR <> 0
        ROLLBACK TRANSACTION
    ELSE
        COMMIT TRANSACTION
    
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED
    
END

Cela fonctionne parfaitement mais ce n'est pas ensembliste et ça me chagrine fortement.

Je joins quelque chose que j’ai retrouvé dans mes archives DB2 d’il y a 25 ans, en l’adaptant aux XXIe siècle et à SQL Server. C’est un curseur, donc ça n’est pas ensembliste, mais à l’époque ça marchait très bien (c'était mieux optimisé, mais voyons déjà comment tourne ce que je vous propose).

On est dans le cas où on l’on passe à une procédure (routine à l’époque...) une table de travail, que j’ai renommée en @WTEL et qui contient un peu de tout, notamment des numéros de téléphone pour des personnes n’en ayant pas encore.
P.-S. Notez qu'il n'y a pas de trigger.

Donc si je comprends bien, à partir de la ligne où vous déclarez @PsnId, c'est le début de la procédure. Pourquoi pas ! Maintenant je vous avoue qu'avec tout le mal que j'ai pu lire sur les curseurs, quand j'ai le choix, je les évite (p-e à tord... Je ne fais que croire ce que d'autres ont écrit sur le net). Et vu que j'ai produit un trigger en m'inspirant de celui de Cinéphil qui fonctionne, je vais rester à cela pour le moment jusqu'à ce que je trouve (ou qu'on me propose^^) une solution qui soit ensembliste (mais est-ce seulement possible ?).

Sinon j'ai obtenu de la MOA qu'elle me dise que les règles de gestion sont ok mais j'attends tjs le document de retour avec le tampon dessus. A mon avis, vais encore devoir attendre un peu avant de le revoir celui-là... Sont grave quand même. Et malheureusement, je suis obligé de continuer à avancer dans le projet car la deadline approche à grand pas...

Quoi qu'il en soit, je garde votre procédure en stock au cas où .

[Kropernic]

Pour info, voici une version ensembliste du-dit trigger (sauce SQL SERVER 2005):

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
ALTER TRIGGER [dbo].[TR_INSTEAD_OF_INSERT_TST] 
   ON [dbo].T_TEST_TST
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 
BEGIN TRY;
 
    WITH 
    T0 AS (SELECT GFT_ID, DOS_ID, ACT_DATE, ACT_VALUE, ROW_NUMBER() OVER(PARTITION BY GFT_ID ORDER BY GFT_ID) AS ROW_NUM
           FROM   inserted)
    INSERT INTO T_TEST_TST (GFT_ID, ACT_ID, DOS_ID, ACT_DATE, ACT_VALUE)
    SELECT T0.GFT_ID,
           COALESCE(MAX(ACT.ACT_ID), 0)+ROW_NUM,
           T0.DOS_ID,
           T0.ACT_DATE,
           T0.ACT_VALUE
    FROM   T_TEST_TST AS ACT
           RIGHT OUTER JOIN T0 
                 ON    ACT.GFT_ID = T0.GFT_ID
    GROUP BY T0.GFT_ID,
             T0.DOS_ID,
             T0.ACT_DATE,
             T0.ACT_VALUE,
             T0.ROW_NUM;
 
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
 
END TRY
BEGIN CATCH
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
    RAISERROR('Erreur dans trigger', 16, 1)
END CATCH
 
END

[Kropernic]

Allez, on laisse tomber le côté physique et on revient dans le monde conceptuel.

C'est quelque chose que je n'ai pas abordé jusqu'ici car d'un point de vue purement théorique, je connais la solution pour l'avoir déjà mise en œuvre par le passé.

Je veux parler des libellés en plusieurs langues. Je sais bien que, normalement, il me faudrait quelque dans ce goût-là.

- Un type de transaction possède plusieurs traductions et une traduction est possédée par un type de transaction.
- Une traduction est précisée par une langue et une langue peut préciser plusieurs traductions.

Ce qui donne 3 entités (passons sur les attributs, c'est assez évident).

Ma question est :
Etant donné que je développe en interne pour la boîte dans laquelle je bosse et qu'on est certain que l'application ne sera jamais vendue ni qu'une troisième langue ne sera ajoutée, est-ce vraiment un drame si, plutôt que de faire cela, je place simplement un attribut par langue dans l'entité type de transaction ?

Je me rends bien compte que je cède à la facilité...
Si j'ai le temps, je mettrai en place la "bonne" solution. Mais j'aimerais bien avoir l'avis d'experts sur la question (je vais sans doute me faire rétrogradé de bombardier à troufion de première ligne ^^).

[fsmrel]

Avant de remonter au niveau conceptuel.


A propos de ROW_NUMBER() OVER(PARTITION BY GFT_ID ORDER BY GFT_ID).

Cette fonction fournit des numéros de lignes, donc strictement croissants, tous GFT_ID confondus. Pour rester dans cette logique vous pouvez faire l’économie du GROUP BY. Pour reprendre l’exemple des téléphones des personnes, l'insert se simplifie :

Code SQL :

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
USE TEMP ;
CREATE TABLE PERSONNE  
( 
  PsnId             INT           NOT NULL, 
  PsnLibelle        VARCHAR(16)   NOT NULL, 
  CONSTRAINT PSN_PK PRIMARY KEY (PsnId)
) ;
CREATE TABLE TELEPHONE  
( 
  PsnId   INT            NOT NULL , 
  TelId   INT            NOT NULL ,  
  TelNo   VARCHAR(24)    NOT NULL  
  CONSTRAINT TEL_PK PRIMARY KEY (PsnId, TelId), 
  CONSTRAINT TEL_FK FOREIGN KEY (PsnId) REFERENCES PERSONNE (PsnId) ON DELETE CASCADE 
) ;
 
INSERT INTO PERSONNE VALUES (1, 'Albert') ; 
INSERT INTO PERSONNE VALUES (2, 'Bernard') ; 
INSERT INTO PERSONNE VALUES (3, 'Carole') ; 
 
SELECT '' AS 'PERSONNE', * FROM PERSONNE ;
 
INSERT INTO TELEPHONE VALUES (1, 1, '00 33 (0)123 456 789') ; 
INSERT INTO TELEPHONE VALUES (1, 2, '00 33 (0)123 456 790') ; 
INSERT INTO TELEPHONE VALUES (2, 3, '00 33 (0)234 654 322') ; 
INSERT INTO TELEPHONE VALUES (2, 4, '00 33 (0)234 654 323') ; 
INSERT INTO TELEPHONE VALUES (2, 5, '00 33 (0)234 654 324') ; 
 
SELECT '' AS 'TELEPHONE avant Insert', * FROM TELEPHONE ;
 
-- Nouveaux numéros de téléphone --
 
DECLARE @WTEL AS TABLE   
( 
  PsnId         INT          NOT NULL, 
  TelNo         VARCHAR(24)  NOT NULL  
) ;
 
INSERT INTO @WTEL VALUES (1, '00 33 (0)123 456 791') ; -- nouveau 
INSERT INTO @WTEL VALUES (1, '00 33 (0)123 456 792') ; -- et un autre
INSERT INTO @WTEL VALUES (2, '00 33 (0)234 654 325') ; -- nouveau
INSERT INTO @WTEL VALUES (2, '00 33 (0)234 654 326') ; -- et un autre
INSERT INTO @WTEL VALUES (3, '00 33 (0)300 000 001') ; -- le tout 1er pour la personne
INSERT INTO @WTEL VALUES (3, '00 33 (0)300 000 002') ; -- nouveau
INSERT INTO @WTEL VALUES (3, '00 33 (0)300 000 003') ; -- nouveau
 
SELECT '' AS '@WTEL',* FROM @WTEL
 
-- Insertion dans la table TELEPHONE --
 
INSERT INTO TELEPHONE
    SELECT PsnId, (SELECT MAX(TelId) FROM TELEPHONE) + ROW_NUMBER() OVER(ORDER BY PsnId), TelNo
    FROM   @WTEL
 
SELECT '' AS 'TELEPHONE après Insertion', * FROM TELEPHONE ;

Remarques

1) Je rappelle que dans cet exemple la mise en œuvre de @WTEL {PsnId, Telno} n’est qu’un moyen comme un autre de fournir la liste les numéros de téléphone à insérer dans la table TELEPHONE.

2) La partie relative de l’identifiant n’est plus relative, mais ceci est secondaire. Toutefois, le type Tinyint pour l’attribut TelId n’est plus alors de mise, car la valeur 255 sera rapidement dépassée.

3) Avec l’insert que je propose, pas de WITH, pas besoin de verrouiller explicitement la tâche en mode exclusif (« ISOLATION LEVEL SERIALIZABLE »), on laisse le SGBD se débrouiller tout seul. Cette approche est à recommander surtout si, comme vous le dites, vous ne maîtrisez pas parfaitement le sujet du verrouillage dans le contexte des SGBD.

4) Pourquoi mettre en œuvre un trigger ?

5) Si vous tenez à ce trigger, pourquoi lui appliquer l’instruction ALTER TRIGGER ?

6) Si ce n’est pas déjà fait, n’oubliez pas de bâtir un prototype de performances pour assurer des temps de traitement conformes à l’attente de l’utilisateur.

[Kropernic]

Avant de remonter au niveau conceptuel.


A propos de ROW_NUMBER() OVER(PARTITION BY GFT_ID ORDER BY GFT_ID).

Cette fonction fournit des numéros de lignes, donc strictement croissants, tous GFT_ID confondus.

Non non, la clause PARTITION BY GFT_ID fait que la numérotation recommance à chaque changement de valeur pour GFT_ID.

Remarques

1) Je rappelle que dans cet exemple la mise en œuvre de @WTEL {PsnId, Telno} n’est qu’un moyen comme un autre de fournir la liste les numéros de téléphone à insérer dans la table TELEPHONE.

Bien d'accord

2) La partie relative de l’identifiant n’est plus relative, mais ceci est secondaire. Toutefois, le type Tinyint pour l’attribut TelId n’est plus alors de mise, car la valeur 255 sera rapidement dépassée.

Là je ne vous suis plus... Sans doute lié avec votre commentaire sur ROW_NUMBER().

3) Avec l’insert que je propose, pas de WITH, pas besoin de verrouiller explicitement la tâche en mode exclusif (« ISOLATION LEVEL SERIALIZABLE »), on laisse le SGBD se débrouiller tout seul. Cette approche est à recommander surtout si, comme vous le dites, vous ne maîtrisez pas parfaitement le sujet du verrouillage dans le contexte des SGBD.

Bin je me suis un peu renseigner depuis. En fait, si on regarde la norme sql, ce devrait le niveau d'isolation serializable par défaut ^^. Ensuite, ça ne change que dans le trigger. C'est pas bien grave. Puis ça fait un code assez court. Et j'avoue que j'ai une préférence pour ce genre de code

4) Pourquoi mettre en œuvre un trigger ?

Car quand l'utilisateur, via l'applicatif, voudra insérer des téléphones, vu que je ne comptes pas aller chaque fois récupérer le dernier numéro de téléphone du client pour connaître son numéro, je préfère que le sgdb le calcul pour moi via le trigger. J'avoue ne pas avoir creuser en détail votre procédure mais si c'est moi qui passe les numéros relatifs au sgdb, comment se règlent les accès concurrents ? (je vérifierai cela demain au travail)

5) Si vous tenez à ce trigger, pourquoi lui appliquer l’instruction ALTER TRIGGER ?

C'est juste le code que sql server affiche lorsque je double-clique sur le trigger. Oubliez cette partie pour ne regarde que le corps de ce dernier

6) Si ce n’est pas déjà fait, n’oubliez pas de bâtir un prototype de performances pour assurer des temps de traitement conformes à l’attente de l’utilisateur.

Alors là, j'ai souvent lu qu'il fallait faire cela mais je n'ai encore aucune idée de comment le faire.

[fsmrel]

Non non, la clause PARTITION BY GFT_ID fait que la numérotation recommance à chaque changement de valeur pour GFT_ID.

Exact. Dans le scénario dont je me suis servi, je n'ai pas pris en compte cette clause et ce que j’ai présenté peut seulement servir d’alternative (tout comme le curseur, lequel est améliorable) au cas où le prototypage montrerait que les performances sont à améliorer (comme dans le cas d’une histoire vécue, où le batch quotidien, contractuellement prévu à 8 heures, a duré 240 heures au démarrage en production, bien entendu sans aucun prototypage préalable...) Dans ce scénario, je me suis exercé à virer les WITH et le GROUP BY et il en est resté un travail incomplet, Sorry.

Je note que si l’on utilise la clause PARTITION BY, on peut conserver le type Tinyint, au moins dans le cas des téléphones.

quand l'utilisateur, via l'applicatif, voudra insérer des téléphones, vu que je ne comptes pas aller chaque fois récupérer le dernier numéro de téléphone du client pour connaître son numéro, je préfère que le sgdb le calcul pour moi via le trigger. J'avoue ne pas avoir creuser en détail votre procédure mais si c'est moi qui passe les numéros relatifs au sgdb, comment se règlent les accès concurrents ? (je vérifierai cela demain au travail)

Vous observerez que la table temporaire que j’utilise a pour structure {PsnId, TelNo}, où les valeurs de TelNo sont des numéros de téléphone tels que '00 33 (0)234 654 321' : silence radio quant à l’identifiant relatif.

Par exemple, si WTEL {PsnId, TelNo} est la table temporaire contenant les numéros de téléphone à insérer dans la table TELEPHONE, on peut imaginer l’organisation suivante :

1) Insérer dans WTEL les numéros proposés par l’utilisateur (en notant qu’un verrou d’exclusivité est alors posé d'office par le SGBD sur la table WTEL).

2) Mise à jour de la table TELEPHONE :

Code sql :

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

1
2
3
4
5
6
7
WITH  
    Truc AS (SELECT PsnId, ROW_NUMBER() OVER(PARTITION BY PsnId ORDER BY PsnId) AS TelId, TelNo
             FROM   WTEL)
    INSERT INTO TELEPHONE (PsnId, TelId, TelNo)
        SELECT Truc.PsnId, COALESCE(MAX(t.TelId), 0) + Truc.TelId, Truc.TelNo
        FROM   TELEPHONE AS t RIGHT OUTER JOIN Truc ON t.PsnId = Truc.PsnId
        GROUP BY Truc.PsnId, Truc.TelId, Truc.TelNo ;

j'ai souvent lu qu'il fallait faire cela mais je n'ai encore aucune idée de comment le faire.

C’est la partie ""pensum" du travail, mais pour reprendre l'exemple du batch quotidien à 240 heures, si les autres avaient fait leur boulot, on ne serait pas venu me chercher pour jouer les pompiers de l’informatique. En fait, pour faire court, l’essentiel est de faire en sorte que l’utilisateur ne reste pas plus de tant de secondes à attendre devant son écran que l’opération soit terminée, et je suppose que vous avez l'habitude de ce genre de défi...

Dans votre cas, vous n’avez pas 2000 utilisateurs branchés en même temps sur la même transaction, donc inutile d’envisager l’acquisition d’un logiciel dédié valant généralement la feau des pesses. Vous générez 1000 ou dix mille ou cent mille ou, etc. personnes, chacune ayant en moyenne N téléphones, vous bourrez la table WTEL comme un mulet, vous lancez les transactions en parallèle, vous engrangez et vérifiez les résultats, faites des campagnes d’EXPLAIN, suivez la fragmentation des tables et tout ça, en vaillant DBA...


P.-S.
A propos des traductions, qu'est-ce pour vous qu'un type de transaction ?

[Kropernic]

Ca doit être la fatigue mais j'ai du mal à suivre le fil de certaines choses...

Exact. Dans le scénario dont je me suis servi, je n'ai pas pris en compte cette clause et ce que j’ai présenté peut seulement servir d’alternative (tout comme le curseur, lequel est améliorable) au cas où le prototypage montrerait que les performances sont à améliorer (comme dans le cas d’une histoire vécue, où le batch quotidien, contractuellement prévu à 8 heures, a duré 240 heures au démarrage en production, bien entendu sans aucun prototypage préalable...) Dans ce scénario, je me suis exercé à virer les WITH et le GROUP BY et il en est resté un travail incomplet, Sorry.

"Dans ce scénario"... Lequel ? Celui du batch de 240 heures (j'avais déjà lu cette anecdote il y a quelques mois et j'avais halluciné) ou bien celui des téléphones ?

Je note que si l’on utilise la clause PARTITION BY, on peut conserver le type Tinyint, au moins dans le cas des téléphones.

Et pas pour les autres ? Il n'y aura jamais plus de 255 activation/désactivation par gift, c'est une certitude.

Vous observerez que la table temporaire que j’utilise a pour structure {PsnId, TelNo}, où les valeurs de TelNo sont des numéros de téléphone tels que '00 33 (0)234 654 321' : silence radio quant à l’identifiant relatif.

Par exemple, si WTEL {PsnId, TelNo} est la table temporaire contenant les numéros de téléphone à insérer dans la table TELEPHONE, on peut imaginer l’organisation suivante :

1) Insérer dans WTEL les numéros proposés par l’utilisateur (en notant qu’un verrou d’exclusivité est alors posé d'office par le SGBD sur la table WTEL).

2) Mise à jour de la table TELEPHONE :
Code sql :
Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7

WITH Truc AS (SELECT PsnId, ROW_NUMBER() OVER(PARTITION BY PsnId ORDER BY PsnId) AS TelId, TelNo FROM WTEL) INSERT INTO TELEPHONE (PsnId, TelId, TelNo) SELECT Truc.PsnId, COALESCE(MAX(t.TelId), 0) + Truc.TelId, Truc.TelNo FROM TELEPHONE AS t RIGHT OUTER JOIN Truc ON t.PsnId = Truc.PsnId GROUP BY Truc.PsnId, Truc.TelId, Truc.TelNo ;

J'ai du mal à comprendre en quoi c'est vraiment différent de ce que je fais avec le trigger. De ce que je comprends, je fais les insertions directement dans la "vraie" table et vous dans une table de travail. Je laisse un trigger s'occuper de l'identification relative et vous laissez une procédure s'en occuper. Il est plus que probable que ce soit dû à mon manque de conaissance/d'expérience mais j'avais l'impression qu'avec la procédure, si d'aventures 2 utilisateurs en arrivent à lancer la procédure en même temps, ça ne risquerait de provoquer des problèmes avec l'identification relative ?

/!\EUREKA/!\

Ca vient de faire tilt en écrivant ces lignes. Votre table WTEL est une table physique dans la DB. Je pensais jusqu'ici qu'il s'agissait d'une table temporaire créée pour chaque utilisateur lors de l'ajout de téléphones et que l'on passait simplement en paramètre de la procédure.
Est-ce bien cela ?

Quand bien même, quelle(s) différence(s) avec le trigger ? Je ne suis pas contre votre solution hein (j'ai l'impression que j'ai l'air têtu avec cette histoire), je veux juste comprendre ce que je fais/ferais. Avec le trigger en mode serializable, je comprends bien les insertions se feront une à une. Du coup, aucun souci d'accès concurrent et l'identification relative est 100% sûre.

C’est la partie ""pensum" du travail, mais pour reprendre l'exemple du batch quotidien à 240 heures, si les autres avaient fait leur boulot, on ne serait pas venu me chercher pour jouer les pompiers de l’informatique. En fait, pour faire court, l’essentiel est de faire en sorte que l’utilisateur ne reste pas plus de tant de secondes à attendre devant son écran que l’opération soit terminée, et je suppose que vous avez l'habitude de ce genre de défi...

J'ai bien l'habitude de ce genre de défi oui dans le sens que nous jouons les pompiers sur nos propres incendies ^^ (vu qu'on ne fait jamais de prototypage).

Dans votre cas, vous n’avez pas 2000 utilisateurs branchés en même temps sur la même transaction, donc inutile d’envisager l’acquisition d’un logiciel dédié valant généralement la feau des pesses. Vous générez 1000 ou dix mille ou cent mille ou, etc. personnes, chacune ayant en moyenne N téléphones, vous bourrez la table WTEL comme un mulet, vous lancez les transactions en parallèle, vous engrangez et vérifiez les résultats, faites des campagnes d’EXPLAIN, suivez la fragmentation des tables et tout ça, en vaillant DBA...

Vous surestimez mes compétences de DBA . Sinon dans mon cas, au maximum, je devrais avoir 15+3 utilisateurs en même temps sur l'application. Est-il vraiment nécessaire de faire un prototypage pour ça ? Les seuls moments où ça pourrait pédaler, ce serait lorsque qu'une requête devrait faire le tri dans plusieurs millions de lignes (il n'y aura que la table T_TRANSACTION_TRN qui en contiendra autant) et pour ces cas-là, les index sont là pour faire le boulot non ?
Surtout que grâce à vous, j'ai une DB bien normalisé et donc les tables ne contiennent pas 200 colonnes ^^.

P.-S.
A propos des traductions, qu'est-ce pour vous qu'un type de transaction ?

Les différents types de transaction sont (au pifomètre) :
- vente
- utilisation
- recharge (même pas certain que celui-là existe, ce sont des infos que j'irai récupéré dans une DB oracle de notre maison mère)

En gros, c'est pour permettre, via la table des transactions, de pouvoir suivre tout le cycle de vie d'un gift.

- Il a été vendu à la date D1 avec une valeur de N€.
- Il a été utilisé à la date D2 pour une valeur de O€.
- Il a été utilisé à la date D3 pour une valeur de P€.
- Il a été recharger à la date D4 pour une valeur de Q€.
- etc.

Et donc, quand l'utilisateur voudra consulté pour le gift G tout son historique, j'irai chercher dans la table transaction toutes les lignes avec GFT_ID = G avec une jointure sur la table type_transaction pour savoir de quel type d'action il s'agit.
Ma question à propos des traductions vient du fait qu'il faudra affiché cela en texte à l'utilisateur. Entendons-nous, il n'aura pas une jolie phrase. Ce sera bien dans une grille mais si je lui affiche dans la colonne "Type de transaction" une information numérique, ça ne va pas l'aider des masses.
J'afficherai donc dans cette colonne "Vente", "Utilisation", "Recharge", etc. Le tout en français et néerlandais (vive la Belgique '-_-).

[Kropernic]

Complément pour les traductions :

J'ai posé la question pour les types de transactions mais il en va de même pour chaque entité ayant un libellé dans sa liste d'attribut.

Par exemple, l'entité "TYPE ADRESSE" qui précise le type d'un adresse (livraison, facturation, etc.).

Je veux laisser la possibilité à des utilisateurs ayant des privilèges élevés sur l'application de pouvoir ajouter/modifier/supprimer les types d'adresses.

En cas d'ajout, il est évident qu'il falloir que cet utilisateur encode toutes les langues dans lesquelles l'application sera disponible (heureusement, juste 2 dans mon cas). Sinon, lors de la consultation des données par un utilisateur lambda, si le pauvre ne parle que le français et que cette info a été encodée uniquement en néerlandais, y a le syndicat qui va me tomber sur la tête.

C'est pour ça que je me demande s'il ne serait pas plus facile de mettre dans l'entité TYPE ADRESSE (pour continuer sur cet exemple), les attributs suivants :
- ID (type int);
- LIB_FR (type varchar(20));
- LIB_NL (type varchar(20)).

Ca aura au moins l'avantage, si j'ose dire, d'être cohérent avec la structure de mes objets dans l'application. Sans quoi, je devrais créer les objets en double. Des objets pour les consultations qui n'aurait qu'une propriété LIB qui contiendrait le texte dans le leur langue et des objets pour l'administration qui auraient autant de propriété LIB que de langues utilisées.

Je ne sais pas si je suis forcément plus clair en fait...

[fsmrel]

"Dans ce scénario"... Lequel ? Celui du batch de 240 heures (j'avais déjà lu cette anecdote il y a quelques mois et j'avais halluciné) ou bien celui des téléphones ?

Comme j’ai clos l’histoire des 240 heures au moyen d'une parenthèse, le scénario en cause est bien celui des téléphones. Pour le scénario cata, ce fut une drôle de paire de manches pour retrouver des temps acceptables...

Et pas pour les autres ? Il n'y aura jamais plus de 255 activation/désactivation par gift, c'est une certitude.

Ne vous privez pas, utilisez le type Tinyint.

Pour les tables pouvant dépasser 255 lignes mais pour lesquelles vous avez la certitude de ne jamais atteindre 32767 lignes (2^15 - 1), vous pouvez utiliser le type SMALLINT (deux octets).

Quand bien même, quelle(s) différence(s) avec le trigger ?

C’est que justement il n’y a pas de trigger...
Cela dit, alors que ma solution ne pose pas de problème de performance avec mon SGBD (DB2 for z/OS), il est possible qu’elle en pose avec SQL Server. En effet, une fois que les inserts ont été effectués dans la table TELEPHONE, il faut purger la table WTEL : quel que soit le nombre de lignes de cette table (0, 1, 10, 100, ..., 100 000 000, ...), avec DB2 l’instruction DELETE FROM WTEL ne consomme que quelques millisecondes, le temps de marquer un indicateur dans la structure du table space (structure d’accueil physique de la table), soit un seul accès disque. Je ne sais pas si SQL Server percute de la même façon...

Quant à l’instruction SET TRANSACTION ISOLATION LEVEL SERIALIZABLE, si vous ne l’avez pas déjà fait, il faudrait demander confirmation au spécialiste rugissant (tous aux abris !) si le niveau d’isolation retrouve bien son état initial quand on sort du trigger (auquel cas votre SET TRANSACTION ISOLATION LEVEL READ COMMITTED est poubellable). En effet, on lit ceci dans la doc (cf. CREATE TRIGGER) :

« Vous pouvez spécifier n'importe quelle instruction SET dans le déclencheur. L'option SET sélectionnée reste active pendant l'exécution du déclencheur, puis retrouve sa valeur d'origine. »

Mais ce point est sensible, et un coup de sécurité ne fait jamais de mal...

J'ai bien l'habitude de ce genre de défi oui dans le sens que nous jouons les pompiers sur nos propres incendies ^^ (vu qu'on ne fait jamais de prototypage).

Ach ! C’est du propre !

Vous surestimez mes compétences de DBA :aie :. Sinon dans mon cas, au maximum, je devrais avoir 15+3 utilisateurs en même temps sur l'application. Est-il vraiment nécessaire de faire un prototypage pour ça ?

Voui ! J’ai déjà vu une SSII se prendre des pénalités parce que le temps de réponse des transactions de prise de commande étaient les suivants à la livraison de l’application au client :

Un seul utilisateur prenant une commande : < 1 seconde.
Deux utilisateurs : < 1 seconde.
Trois utilisateurs : 45 secondes...

Le lendemain de ce désastre, coup de téléphone : « fsmrel, on est dans une m... noire... » Toutes affaires cessantes, après une courte nuit de sommeil, je pris l’avion de 7 heures du matin. Je vous raconterai la suite au prochain numéro...


Je n’ai pas encore eu le temps de regarder le coup des libellés en V.O. et sous-titrés.

Mais comme dit lui aussi B. Arnault : vive la Belgique '-_-.

[fsmrel]

je me demande s'il ne serait pas plus facile de mettre dans l'entité TYPE ADRESSE (pour continuer sur cet exemple), les attributs suivants :
- ID (type int);
- LIB_FR (type varchar(20));
- LIB_NL (type varchar(20)).

Vous vous doutez que je vous réponds : et le jour où l’on aura plus de deux langues ?

A priori, essayons de modéliser quelque chose comme ceci, où TYPE_ADR_LIB permet de connaître le libellé du type d’adresse par langue :

[TYPE_ADRESSE]----1,N----(TYPE_ADR_LIB{TypeAdrLibelle})----0,N----[LANGUE]

Avec une vue permettant de voir sous la forme {Id, LIB_langue1, Lib_langue2, ..., LIB_langueN}

Exemple SQL :

Table des langues :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE TABLE LANGUE  
( 
        LangueId           INT           NOT NULL 
      , LangueCode         CHAR(03)      NOT NULL
      , LangueLibelle      VARCHAR(64)   NOT NULL 
    , CONSTRAINT LANGUE_PK PRIMARY KEY (LangueId)
    , CONSTRAINT LANGUE_AK UNIQUE (LangueCode)
) ;
 
INSERT INTO LANGUE VALUES (1, 'fr', 'Français') ; 
INSERT INTO LANGUE VALUES (2, 'nl', 'Nederlands') ; 
INSERT INTO LANGUE VALUES (3, 'de', 'Deutsch') ; 
INSERT INTO LANGUE VALUES (4, 'es', 'Español') ; 
 
SELECT '' AS 'LANGUE', * FROM LANGUE ;

Table des types d’adresse :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE TYPE_ADRESSE  
( 
        TypeAdrId           INT           NOT NULL 
      , TypeAdrTruc         CHAR(04)      NOT NULL 
    , CONSTRAINT TYPE_ADR_PK PRIMARY KEY (TypeAdrId)
) ;
 
INSERT INTO TYPE_ADRESSE VALUES (1, 'fact') ; -- facturation
INSERT INTO TYPE_ADRESSE VALUES (2, 'livr') ; -- livraison 
INSERT INTO TYPE_ADRESSE VALUES (3, 'cour') ; -- courrier 
INSERT INTO TYPE_ADRESSE VALUES (4, 'domi') ; -- domicile 
 
SELECT '' AS 'TYPE_ADRESSE', * FROM TYPE_ADRESSE ;

Table des libellés des types d’adresse (je ne sais pas traduire en néerlandais... ) :

Code SQL :

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
CREATE TABLE TYPE_ADR_LIB  
( 
        TypeAdrId           INT           NOT NULL 
      , LangueId            INT           NOT NULL
      , TypeAdrLibelle      VARCHAR(64)   NOT NULL 
    , CONSTRAINT TYPE_ADR_LIB_PK PRIMARY KEY (TypeAdrId, LangueId)
    , CONSTRAINT TYPE_ADR_LIB_TYPE_ADR_FK FOREIGN KEY (TypeAdrId) REFERENCES TYPE_ADRESSE (TypeAdrId)
    , CONSTRAINT TYPE_ADR_LIB_LANGUE_FK FOREIGN KEY (LangueId) REFERENCES LANGUE (LangueId)
) ;
 
INSERT INTO TYPE_ADR_LIB VALUES (1, 1, 'Adresse de Facturation') ; 
INSERT INTO TYPE_ADR_LIB VALUES (1, 4, 'Dirreción de Facturación') ; 
INSERT INTO TYPE_ADR_LIB VALUES (2, 1, 'Adresse de Livraison') ; 
INSERT INTO TYPE_ADR_LIB VALUES (2, 4, 'Dirreción de Entrega') ; 
INSERT INTO TYPE_ADR_LIB VALUES (3, 1, 'Adresse de Contact') ; 
INSERT INTO TYPE_ADR_LIB VALUES (3, 4, 'Dirreción de Contacto') ; 
INSERT INTO TYPE_ADR_LIB VALUES (4, 1, 'Adresse de Courrier') ; 
INSERT INTO TYPE_ADR_LIB VALUES (4, 4, 'Dirreción de Correo') ; 
 
SELECT '' AS 'TYPE_ADR_LIB', * FROM TYPE_ADR_LIB ;

La vue permettant d’afficher sur une seule ligne :

Code sql :

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
CREATE VIEW MaVue (TypeAdrId, NbTypesAdr, TypesAdr) AS 
WITH V (TypeAdrId, LangueId, Liste, Nb) AS
(
SELECT TYPE_ADR_LIB.TypeAdrId, TYPE_ADR_LIB.LangueId, CAST('[' + TYPE_ADR_LIB.TypeAdrLibelle  + ']' AS VARCHAR(MAX)), 1
FROM   TYPE_ADR_LIB JOIN  
                (SELECT '' AS 'A, MIN(B)', TypeAdrId, MIN(LangueId) AS LangueId
                 FROM   TYPE_ADR_LIB
                 GROUP BY TypeAdrId) AS T
       ON TYPE_ADR_LIB.TypeAdrId = T.TypeAdrId AND TYPE_ADR_LIB.LangueId = T.LangueId 
UNION ALL
SELECT V.TypeAdrId, TYPE_ADR_LIB.LangueId, CAST((Liste + ', ' + '[' + TYPE_ADR_LIB.TypeAdrLibelle + ']') AS VARCHAR(MAX)), Nb + 1 
FROM   V JOIN TYPE_ADR_LIB
       ON TYPE_ADR_LIB.TypeAdrId = V.TypeAdrId
       AND TYPE_ADR_LIB.LangueId > V.LangueId
) 
SELECT V.TypeAdrId, V.Nb , V.Liste
FROM   V JOIN 
          (SELECT TypeAdrId, MAX(Nb) AS MAX_Nb
           FROM   V
           GROUP BY TypeAdrId) AS W
         ON V.TypeAdrId = W.TypeAdrId AND V.Nb = W.MAX_Nb ; 
go
 
SELECT * FROM MaVue ;

Sans quoi, je devrais créer les objets en double. Des objets pour les consultations qui n'aurait qu'une propriété LIB qui contiendrait le texte dans le leur langue et des objets pour l'administration qui auraient autant de propriété LIB que de langues utilisées.

Je ne saisis pas, donc je marque une pause...

[Kropernic]

Vous vous doutez que je vous réponds : et le jour où l’on aura plus de deux langues ?

Bin on ajoute un attribut en plus . Je plaisante évidemment. Je sais pertinament que d'un point de vue à long terme, cette pratique est à exclure. Et j'ai d'ailleurs déjà mis en oeuvre la solution que vous proposez pour un autre projet.

Seulement ne pourrait-on pas comparer cette problématique à celle de certaines informations qui suivant le cadre d'exploitation, sont décomposées ou non. Si on prend pas exemple le numéro de registre national (j'imagine que ça existe aussi en France) qui sert à identifier une personne de manière unique, si je devais écrire une application pour le service population d'une commune, il est évident que ce numéro serait décomposé en 3 attributs distincts. Par contre, pour une application en interne dans mon entreprise pour laquelle ces différents attribut n'ont aucune importance, je le laisserais tel quel.
Alors oui, je suis d'accord avec vous pour dire que, dans l'idéal, il faudrait une table des traductions mais dans le cadre qui me concerne, je n'aurai jamais besoin que de deux langues. Si un cahier des charges précis était établi (ce qui n'est jamais le cas ), il y serait écrit que l'application devrait être disponible en Français et en Néerlandais. Il n'y serait pas fait mention d'une éventuelle 3e langue. Par conséquent, pourquoi ne pas se simplifier la vie ?

Je ne saisis pas, donc je marque une pause...

Laissons cela de côté, j'avais mis ma casquette de programmeur mais je pense que j'étais à côté de la plaque.


EDIT : Un détail supplémentaire... L'application en question ne sera utilisée normalement que pendant une période de 2 ans environ (je vous passe le pourquoi pcq c'est compliqué)

[Richard_35]

Bonjour Kropernic et Fsmrel,

Fsmrel, désolé, je me permets juste une toute petite immixtion (ou immiscion)...

Par conséquent, pourquoi ne pas se simplifier la vie ?

==> ... en apparence, seulement !... l'option un attribut par langue dans une même entité est, carrément, "casse-gueule", à terme. En effet, les bienfaits de voir apparaître les différentes traductions en colonne sont incommensurables, comparés à les voir apparaître en ligne, notamment pour les diverses requêtes qui analyseront cette table.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
TypeAdrId LangueId TypeAdrLibelle
1         1        Adresse de Facturation
1         4        Dirreción de Facturación
2         1        Adresse de Livraison
2         4        Dirreción de Entrega
3         1        Adresse de Contact
3         4        Dirreción de Contacto
4         1        Adresse de Courrier
4         4        Dirreción de Correo

==> même nom de champ

est plus facile à exploiter que :

Code :

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

1
2
3
4
5
6
7
8
TypeAdrId TypeAdrLibelleFR          TypeAdrLibelleES
1         Adresse de Facturation    Dirreción de Facturación
2         Adresse de Livraison      Dirreción de Entrega
3         Adresse de Contact        Dirreción de Contacto
4         Adresse de Courrier       Dirreción de Correo

==> nom de champ différent

Quant à ta comparaison :

Seulement ne pourrait-on pas comparer cette problématique à celle de certaines informations qui suivant le cadre d'exploitation, sont décomposées ou non. Si on prend pas exemple le numéro de registre national (j'imagine que ça existe aussi en France) qui sert à identifier une personne de manière unique

==> ce n'est pas le même sujet : il s'agit, là, de la pertinence de donner une signification à une clé primaire (à un identifiant unique). Ce système est, également, "casse-gueule" (cf. changement de système d'immatriculation en France). A ce propos, tu peux jeter un coup d'oeil sur cette discussion qui, elle-même, renvoie à celle-ci.

Désolé pour l'excessivité de mon propos, mais ce sont deux sujets (surtout le second) pour lesquels "je me bats régulièrement contre les utilisateurs finaux".

[Kropernic]

Pour la comparaison où je parle du numéro de registre national, je n'ai jamais dit qu'il s'agirait d'une clé primaire... Par contre, il est évident qu'une contrainte d'unicité sera appliquée sur cette colonne.

Pour en revenir au fait de se simplifier la vie, je te garantis qu'avoir un champ par langue, à condition bien sûr que le nombre de langue soit réduit et ne varie jamais, me fait écrire bien moins de code VB.NET que si j'avais une table de traductions.
Coté sql par contre, c'est vrai que les requêtes sont un peu plus longues.

Histoire d'avoir la conscience tranquille, je vais mettre en oeuvre la solution normalisée car l'autre me pose un réel problème de conscience même si je reste convaincu que ce serait plus vite fait.

[Kropernic]

Par contre je viens de tester la vue proposée par fsmrel et j'ai du mal à voir comment exploiter cela de manière simple dans l'application.

Au final, je vais créer une vue qui simulera une table avec un colonne par langue... Sinon, ça va être la galère pour savoir dans quelle langue sont les libellés obtenus.

[fsmrel]

je me permets juste une toute petite immixtion (ou immiscion)...

Tant que ça n'est pas une miction...

[fsmrel]

Si un cahier des charges précis était établi (ce qui n'est jamais le cas ), il y serait écrit que l'application devrait être disponible en Français et en Néerlandais.

Bien sûr, mais la veille du démarrage, on vous fera parvenir une note :

« Nous connaissons votre réactivité et vous demandons une légère modification, à savoir la prise en compte de l'espéranto ; bien entendu c'est pour demain, la vie de l'entreprise en dépend. Nous comptons sur vous. »

Evidemment, sûr de votre bon droit, vous brandirez le cahier des charges, mais après ?

[Kropernic]

Bien sûr, mais la veille du démarrage, on vous fera parvenir une note :

« Nous connaissons votre réactivité et vous demandons une légère modification, à savoir la prise en compte de l'espéranto ; bien entendu c'est pour demain, la vie de l'entreprise en dépend. Nous comptons sur vous. »

Evidemment, sûr de votre bon droit, vous brandirez le cahier des charges, mais après ?

Bin après, je cherche un job dans une autre boîte car je préfère travailler avec des gens sérieux ?

[fsmrel]

Bonsoir,

En effet, les bienfaits de voir apparaître les différentes traductions en colonne sont incommensurables, comparés à les voir apparaître en ligne, notamment pour les diverses requêtes qui analyseront cette table.

« Incommensurable » est-il le qualificatif approprié, notamment en ce qui concerne l'analyse (les requêtes de consultation) ? Les SGBD SQL (SQL Server dans le cas présent) proposent des fonctions telles que PIVOT/UNPIVOT pour passer d’une représentation verticale à une représentation horizontale et inversement. Dans les cas analogues à celui de Kropernic, le danger vient plutôt du risque pris à prendre en compte des langues supplémentaires, car il faut en l’occurrence modifier la structure de chaque table impliquée dans cette affaire, donc la structure des requêtes d’INSERT, et il faut aussi vérifier l’impact sur chaque contrainte référentielle, vue, trigger, fonction, droit, requête SELECT ou UPDATE : il faut essayer de mesurer ce que cela représente.

Pour avoir une idée de ce vers quoi on s’embarque, voyons ce qu’il se passe ici au cas où il faudrait prendre en compte une langue supplémentaire, dans le cas de la version mono-colonne (verticale) d’une part et celui de la version multi-colonnes (horizontale) d’autre part.

Version mono-colonne

Je rappelle les structures initiales :

Table LANGUE :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE LANGUE  
( 
        LangueId           INT           NOT NULL 
      , LangueCode         CHAR(03)      NOT NULL
      , LangueLibelle      VARCHAR(64)   NOT NULL 
    , CONSTRAINT LANGUE_PK PRIMARY KEY (LangueId)
    , CONSTRAINT LANGUE_AK UNIQUE (LangueCode)
) ;
 
INSERT INTO LANGUE VALUES (1, 'fr', 'Français') ; 
INSERT INTO LANGUE VALUES (2, 'nl', 'Nederlands') ; 
 
SELECT '' AS 'LANGUE', * FROM LANGUE ;

Table TYPE_ADRESSE des types d’adresse :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE TYPE_ADRESSE  
( 
        TypeAdrId           INT           NOT NULL 
      , TypeAdrCode         VARCHAR(16)   NOT NULL 
    , CONSTRAINT TYPE_ADR_PK PRIMARY KEY (TypeAdrId)
) ;
 
INSERT INTO TYPE_ADRESSE VALUES (1, 'fact') ; -- facturation 
INSERT INTO TYPE_ADRESSE VALUES (2, 'livr') ; -- livraison
INSERT INTO TYPE_ADRESSE VALUES (3, 'corr') ; -- correspondance 
INSERT INTO TYPE_ADRESSE VALUES (4, 'Domi') ; -- domiciliation 
 
SELECT '' AS 'TYPE_ADRESSE', * FROM TYPE_ADRESSE ;

Table TYPE_ADR_LIB des libellés des types d’adresse :

Code SQL :

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
CREATE TABLE TYPE_ADR_LIB  
( 
        TypeAdrId           INT           NOT NULL 
      , LangueId            INT           NOT NULL
      , TypeAdrLibelle      VARCHAR(64)   NOT NULL 
    , CONSTRAINT TYPE_ADR_LIB_PK PRIMARY KEY (TypeAdrId, LangueId)
    , CONSTRAINT TYPE_ADR_LIB_TYPE_ADR_FK FOREIGN KEY (TypeAdrId) REFERENCES TYPE_ADRESSE (TypeAdrId)
    , CONSTRAINT TYPE_ADR_LIB_LANGUE_FK FOREIGN KEY (LangueId) REFERENCES LANGUE (LangueId)
) ;
 
INSERT INTO TYPE_ADR_LIB VALUES (1, 1, 'Adresse de facturation') ; 
INSERT INTO TYPE_ADR_LIB VALUES (1, 2, 'Factuuradres') ;      
INSERT INTO TYPE_ADR_LIB VALUES (2, 1, 'Adresse de livraison') ;   
INSERT INTO TYPE_ADR_LIB VALUES (2, 2, 'Efleveradres') ;  
INSERT INTO TYPE_ADR_LIB VALUES (3, 1, 'Adresse de correspondance') ; 
INSERT INTO TYPE_ADR_LIB VALUES (3, 2, 'Correspondentieadres') ; 
INSERT INTO TYPE_ADR_LIB VALUES (4, 1, 'Adresse de domiciliation') ; 
INSERT INTO TYPE_ADR_LIB VALUES (4, 2, 'Domicilieadres') ; 
 
SELECT '' AS 'TYPE_ADR_LIB', * FROM TYPE_ADR_LIB ;

Fonction permettant de récupérer un libellé en fonction du type d’adresse et de la langue :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
CREATE FUNCTION TypeAdrLangueFn (@TypeAdr CHAR(04), @Langue CHAR(02))
RETURNS VARCHAR(32)
AS 
BEGIN
    RETURN (SELECT x.TypeAdrLibelle
            FROM   TYPE_ADR_LIB AS x JOIN LANGUE AS y ON x.LangueId = y.LangueId 
                                     JOIN TYPE_ADRESSE AS z ON x.TypeAdrId = z.TypeAdrId
            WHERE  y.LangueCode = @Langue
            AND    z.TypeAdrCode = @TypeAdr)      
END

Exemple de récupération en néerlandais du libellé du type d’adresse de livraison :

Code SQL :

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

1
2
DECLARE @TexteLangue AS VARCHAR(32) ;
SET @TexteLangue = (SELECT dbo.TypeAdrLangueFn('livr', 'nl')) ;

Au résultat : Efleveradres


Version mono-colonne : prise en compte d’une langue supplémentaire

Mise à jour de la table LANGUE :

Code SQL :

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

INSERT INTO LANGUE VALUES (3, 'es', 'Español') ;

Mise à jour de la table TYPE_ADR_LIB :

Code SQL :

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

1
2
3
4
INSERT INTO TYPE_ADR_LIB VALUES (1, 3, 'Dirreción de facturación') ; 
INSERT INTO TYPE_ADR_LIB VALUES (2, 3, 'Dirreción de entrega') ; 
INSERT INTO TYPE_ADR_LIB VALUES (3, 3, 'Dirreción de correo') ; 
INSERT INTO TYPE_ADR_LIB VALUES (4, 3, 'Dirreción de domicilio') ;

Les modifications sont minimes.


Version multi-colonne

Structures initiales :

Table TYPE_ADRESSE des types d’adresse :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE TYPE_ADRESSE  
( 
        TypeAdrId             INT           NOT NULL 
      , TypeAdrCode           VARCHAR(16)   NOT NULL 
      , TypeAdrLibelleFR      VARCHAR(64)   NOT NULL 
      , TypeAdrLibelleNL      VARCHAR(64)   NOT NULL 
    , CONSTRAINT TYPE_ADRESSE_PK PRIMARY KEY (TypeAdrId)
) ;
INSERT INTO TYPE_ADRESSE VALUES (1, 'fact', 'Adresse de facturation', 'Factuuradres') ; 
INSERT INTO TYPE_ADRESSE VALUES (2, 'livr', 'Adresse de livraison', 'Efleveradres') ; 
INSERT INTO TYPE_ADRESSE VALUES (3, 'corr', 'Adresse de correspondance', 'Correspondentieadres') ; 
INSERT INTO TYPE_ADRESSE VALUES (4, 'domi', 'Adresse de domiciliation', 'Domicilieadres') ;

Fonction permettant de récupérer un libellé en fonction du type d’adresse et de la langue :

Code SQL :

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

1
2
3
4
5
6
7
8
9
CREATE FUNCTION TypeAdrLangueFn (@TypeAdr CHAR(04), @Langue CHAR(02))
RETURNS VARCHAR(32)
AS 
BEGIN
    RETURN (SELECT CASE WHEN @Langue = 'nl' THEN (SELECT TypeAdrLibelleES FROM TYPE_ADRESSE WHERE TypeAdrCode = @TypeAdr)
                        WHEN @Langue = 'fr' THEN (SELECT TypeAdrLibelleFR FROM TYPE_ADRESSE WHERE TypeAdrCode = @TypeAdr)
                        ELSE ('Y a d’l’erreur')  
                   END)
END

Récupération en néerlandais du libellé du type d’adresse de livraison (idem ci-dessus) :

Code SQL :

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

 SET @TexteLangue = (SELECT dbo.TypeAdrLangueFn('livr', 'nl')) ;

Au résultat : Efleveradres


Version multi-colonne : prise en compte d’une langue supplémentaire.

Il faudra modifier la structure de la table TYPE_ADRESSE (ajout d’une colonne pour la langue supplémentaire), et supprimer son contenu pour charger les lignes en conformité avec la nouvelle structure. Cette suppression a des effets secondaires : en effet la table TYPE_ADRESSE est liée à la table ADRESSE par intégrité référentielle. Il faudra veiller à supprimer provisoirement la contrainte correspondante.


Succession des opérations.


Suppression de la contrainte référentielle établie entre les tables TYPE_ADRESSE et ADRESSE :

Code SQL :

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

1
2
ALTER TABLE ADRESSE 
      DROP ADR_TYPE_ADR_FK ;

Suppression des lignes de la table TYPE_ADRESSE :

Code SQL :

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

DELETE FROM TYPE_ADRESSE ;

Modification de la structure de la table TYPE_ADRESSE (ajout d’une colonne) :

Code SQL :

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

1
2
ALTER TABLE TYPE_ADRESSE 
      ADD TypeAdrLibelleES VARCHAR(64) NOT NULL ;

Chargement de la table TYPE_ADRESSE :

Code SQL :

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

1
2
3
4
INSERT INTO TYPE_ADRESSE VALUES (1, 'fact', 'Adresse de facturation', 'Factuuradres', 'Dirreción de facturación') ; 
INSERT INTO TYPE_ADRESSE VALUES (2, 'livr', 'Adresse de livraison', 'Efleveradres', 'Dirreción de entrega') ; 
INSERT INTO TYPE_ADRESSE VALUES (3, 'corr', 'Adresse de correspondance', 'Correspondentieadres', 'Dirreción de correo') ; 
INSERT INTO TYPE_ADRESSE VALUES (4, 'domi', 'Adresse de domiciliation', 'Domicilieadres', 'Dirreción de domicilio') ;

Rétablissement de la contrainte référentielle :

Code SQL :

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

1
2
ALTER TABLE ADRESSE 
      ADD CONSTRAINT ADR_TYPE_ADR_FK FOREIGN KEY (TypeAdrId) REFERENCES TYPE_ADRESSE (TypeAdrId) ;

Modification de la fonction TypeAdrLangueFn pour prendre en compte la langue supplémentaire :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
ALTER FUNCTION TypeAdrLangueFn (@TypeAdr CHAR(04), @Langue CHAR(02))
RETURNS VARCHAR(32)
AS 
BEGIN
    RETURN (SELECT CASE WHEN @Langue = 'fr' THEN (SELECT TypeAdrLibelleFR FROM TYPE_ADRESSE WHERE TypeAdrCode = @TypeAdr)
                        WHEN @Langue = 'nl' THEN (SELECT TypeAdrLibelleNL FROM TYPE_ADRESSE WHERE TypeAdrCode = @TypeAdr)
                        WHEN @Langue = 'es' THEN (SELECT TypeAdrLibelleES FROM TYPE_ADRESSE WHERE TypeAdrCode = @TypeAdr)
                        ELSE ('Y a d’l’erreur')  
                   END)
END

Test pour vérifier que tout cela fonctionne :

Code SQL :

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

1
2
DECLARE @TexteLangue AS VARCHAR(32) ;
SET @TexteLangue = (SELECT dbo.TypeAdrLangueFn('livr', 'es')) ;

Pour conclure avec le scénario multi-colonnes :

Après vérification, en ce qui concerne la base de données de Kropernic, la prise en compte d’une langue supplémentaire pour la table TYPE_ADRESSE multi-colonnes est évidemment un peu plus lourde que dans l'autre cas, mais ça n’est quand même pas une tâche insurmontable, et le qualificatif « incommensurable » est ici excessif. A noter que les objets ne sont pas à supprimer (table TYPE_ADRESSE, fonction TypeAdrLangueFn), donc les autorisations, procédures, autres fonctions, etc. ne sont pas touchées. Cela dit, on se cantonne au contexte de la base de données « kropernicienne », il ne faudrait pas extrapoler pour d’autres bases de données.

Pour ma part, je jouerais la carte mono-colonne, mais je ne serais pas véhémentement contre si Kropernic choisissait la version multi-colonnes : à lui de peser le pour et le contre, c'est-à-dire de quantifier la charge que représente la prise en compte d’une langue supplémentaire, sur la base de ce qui précède, et le cas échéant d'approfondir les points que j'aurais omis de considérer.

[Richard_35]

Bonjour Fsmrel,

le danger vient plutôt du risque pris à prendre en compte des langues supplémentaires

==> c'est clair : c'est le premier risque. Mais cela, c'est acté, pour ma part . La représentation mono-colonne constitue, simplement, un argument de plus (de mon point de vue).

le qualificatif « incommensurable » est ici excessif

==> certes .

Les SGBD SQL (SQL Server dans le cas présent) proposent des fonctions telles que PIVOT/UNPIVOT pour passer d’une représentation verticale à une représentation horizontale et inversement.

==> effectivement, il est toujours possible de franchir des murs que nous avons, nous-mêmes, érigés... En clair, si, statistiquement, si le nombre de fois où l'on est obligé de PIVOTer/UNPIVOTer est plus important que le nombre de fois où l'on exploite cette table sans PIVOTer/UNPIVOTer, alors la solution choisie, n'aura pas été la bonne (l'une ou l'autre).

Pour ma part, je jouerais la carte mono-colonne

==>