Discussion :

[fsmrel]

Bonsoir almoha,

L'utilisateur ne peut réinitialiser qu'une règle ayant le statut acquis.

J’ai oublié de traiter de la réinitialisation...
Si un utilisateur ne peut réinitialiser qu'une règle ayant le statut acquis, à charge de l’application de faire ce que doit :

UPDATE CHOISIR SET Statut = 'à acquérir'
                 , Compteur = 0
    WHERE UtilisateurId = identifiant de l’utilisateur  
      AND RegleId = identifiant de la règle 
;

DELETE FROM  REPONDRE
    WHERE UtilisateurId = identifiant de l’utilisateur  
      AND RegleId = identifiant de la règle 
;   

Si MySQL était au niveau des autres SGBD, on aurait pu sous-traiter à un trigger la remise à 0 de Compteur et la suppression des lignes dans la table REPONDRE. Hélas, la modification de l’attribut Compteur est refusée quand on essaie de créer le trigger suivant (activé quand on modifie le statut ou autre donnée de la table CHOISIR) :

CREATE TRIGGER CHOISIR_UPDATE_AFTER AFTER UPDATE ON CHOISIR 
FOR EACH ROW
    BEGIN
        
        SET @StatutAcquerir = 'à acquérir' ;
        SET @StatutAcquis = 'acquis' ;

-- Au cas où Raoul demande la réinitilisation de son statut :

        IF old.Statut = @StatutAcquis AND new.Statut = @StatutAcquerir THEN
            
             DELETE FROM  REPONDRE
                   WHERE UtilisateurId = new.UtilisateurId 
                     AND RegleId = new.RegleId 
             ;  
             UPDATE CHOISIR SET Compteur = 0   --  Rejeté par MySQL...
                   WHERE UtilisateurId = new.UtilisateurId 
                     AND RegleId = new.RegleId 
             ; 
        END IF ;
            
    END 
GO

A titre de curiosité, l’équivalent avec SQL Server (qui accepte la modification) :

CREATE TRIGGER CHOSIR_UPDATE_AFTER ON CHOISIR AFTER UPDATE AS
 
DECLARE @StatutAcquerir AS VARCHAR(24) ;  
DECLARE @StatutAcquis AS VARCHAR(24) ;

        
SET @StatutAcquerir = 'à acquérir' ;
SET @StatutAcquis = 'acquis' ;

-- Au cas où Raoul demande la réinitilisation de son statut :

DELETE FROM  REPONDRE
       WHERE EXISTS 
            (SELECT ''
             FROM   DELETED AS x, INSERTED AS y
             WHERE  REPONDRE.UtilisateurId = x.UtilisateurId
               AND  REPONDRE.RegleId = x.RegleId
               AND  x.UtilisateurId = y.UtilisateurId 
               AND  x.RegleId = y.RegleId
               AND  x.Statut = @StatutAcquis 
               AND  y.Statut = @StatutAcquerir)

UPDATE CHOISIR 
   SET Compteur = 0
       WHERE EXISTS 
            (SELECT ''
             FROM   DELETED AS x, INSERTED AS y
             WHERE  REPONDRE.UtilisateurId = x.UtilisateurId
               AND  REPONDRE.RegleId = x.RegleId
               AND  x.UtilisateurId = y.UtilisateurId 
               AND  x.RegleId = y.RegleId
               AND  x.Statut = @StatutAcquis 
               AND  y.Statut = @StatutAcquerir)
GO

Bon, tant pis, on se passera du trigger...


Une remarque concernant la validité de l’attribut Statut : il est préférable de ne pas se tromper dans l’application, en écrivant par exemple « aquis » au lieu de « acquis », ça pourrait causer des dégâts...

1) Soit vous définissez une table STATUT à laquelle fait référence la table CHOISIR :

CREATE TABLE STATUT
(
        StatutId             INT           NOT NULL
      , StatutLibelle        VARCHAR(24)   NOT NULL
    , CONSTRAINT STATUT_PK (StatutId)
) ;

CREATE TABLE CHOISIR
(
        UtilisateurId        INT             NOT NULL
      , RegleId              INT             NOT NULL
      , StatutId             INT             NOT NULL 
      , Compteur             INT             NOT NULL DEFAULT 0
    , CONSTRAINT CHOISIR_PK PRIMARY KEY (UtilisateurId, RegleId)  
    , CONSTRAINT CHOISIR_UTILISATEUR_FK FOREIGN KEY (UtilisateurId) 
      REFERENCES UTILISATEUR (UtilisateurId) ON DELETE CASCADE    
    , CONSTRAINT CHOISIR_REGLE_FK FOREIGN KEY (RegleId) 
      REFERENCES REGLE (RegleId)
    , CONSTRAINT CHOISIR_STATUT_FK FOREIGN KEY (StatutId) 
      REFERENCES STATUT(StatutId)
) ;

2) Soit vous définissez un trigger « BEFORE INSERT » et un trigger « BEFORE UPDATE » pour la table CHOISIR afin de contrôler les valeurs prises par l'attribut Statut (avec les autres SGBD, on peut fusionner les deux triggers, mais pas avec MySQL...) :

CREATE TRIGGER CHOISIR_INSERT_BEFORE BEFORE INSERT ON CHOISIR 
FOR EACH ROW
    BEGIN
        SET @Table = 'CHOISIR' ;
        
        SET @StatutAcquerir = 'à acquérir' ;
        SET @StatutEnCours = 'acquisition en cours' ;
        SET @StatutAcquis = 'acquis' ;
        
        IF new.Statut NOT IN (@StatutAcquerir, @StatutEnCours, @StatutAcquis) THEN
            SET @Erreur = CONCAT('Table ', @Table, ', attribut Statut : la valeur "', new.Statut, '" est erronée.') ;
            SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = @Erreur ;        
        END IF ;
    END 
GO

CREATE TRIGGER CHOISIR_UPDATE_BEFORE BEFORE UPDATE ON CHOISIR 
FOR EACH ROW
    BEGIN
        SET @Table = 'CHOISIR' ;
        
        SET @StatutAcquerir = 'à acquérir' ;
        SET @StatutEnCours = 'acquisition en cours' ;
        SET @StatutAcquis = 'acquis' ;
                
        IF new.Statut NOT IN (@StatutAcquerir, @StatutEnCours, @StatutAcquis) THEN
            SET @Erreur = CONCAT('Table ', @Table, ', attribut Statut : la valeur "', new.Statut, '" est erronée.') ;
            SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = @Erreur ;        
        END IF ;
    END 
GO

Pour afficher l'arborescence complète des règles (PARTIE -> le cas échéant SOUS_PARTIE -> le cas échéant RUBRIQUE -> REGLE), je m'interroge sur la requête à effectuer compte tenu de la relation réflexive. Une jointure entre la table COMPOSITION et elle-même est-elle possible ?

La jointure est possible, mais comme dans le cas général des nomenclatures elle doit être récursive... Les principaux SGBD proposent ce type de jointure, mais pas MySQL (décidément...)

Ça n’est pas bien grave, on passera par une procédure récursive, et je suis en train d’en mettre une au point. Ça n’est pas très compliqué à faire, mais quand même, cela nécessite un peu d’huile de coude...

Je vous transmettrai cette procédure dès que.

[fsmrel]

Bonsoir almoha,


Et voilà un exemple de procédure récursive, à secouer dans tous les sens...

Si l’organisation du résultat ne convient pas, on pourra la revoir.

Je reprends les tables du MLD :

CREATE TABLE TYPE_ELEMENT 
(
        TypeElementId        INT             NOT NULL
      , TypeElementLibelle   VARCHAR(16)     NOT NULL
    , CONSTRAINT TYPE_ELEMENT_PK PRIMARY KEY (TypeElementId)
) ;

CREATE TABLE ELEMENT 
(
        ElementId            INT             NOT NULL
      , TypeElementId        INT             NOT NULL
      , ElementTexte         VARCHAR(64)     NOT NULL
    , CONSTRAINT ELEMENT_PK PRIMARY KEY (ElementId)
    , CONSTRAINT ELEMENT_TYPE_ELEMENT_FK FOREIGN KEY (TypeElementId)
      REFERENCES TYPE_ELEMENT (TypeElementId) ON DELETE CASCADE
) ;

CREATE TABLE COMPOSITION 
(
        ElementComposantId       INT             NOT NULL
      , ElementComposeId         INT             NOT NULL
    , CONSTRAINT COMPOSITION_ELEMENT_COMPOSANT_FK FOREIGN KEY (ElementComposantId)
      REFERENCES ELEMENT (ElementId) ON DELETE CASCADE
    , CONSTRAINT COMPOSITION_ELEMENT_COMPOSE_FK FOREIGN KEY (ElementComposeId)
      REFERENCES ELEMENT (ElementId)      
) ;

CREATE TABLE REGLE 
(
        RegleId              INT             NOT NULL
      , ElementId            INT             NOT NULL        
      , RegleTexte           VARCHAR(64)     NOT NULL
      , NbPhrases            INT             NOT NULL
    , CONSTRAINT REGLE_PK PRIMARY KEY (RegleId)
    , CONSTRAINT REGLE_ELEMENT_FK FOREIGN KEY (ElementId)
      REFERENCES ELEMENT (ElementId)
 ) ;

Un jeu d’essai :

INSERT INTO TYPE_ELEMENT (TypeElementId, TypeElementLibelle) VALUES 
    (1, 'Partie'), (2, 'Sous-partie'), (3, 'Rubrique') ;
    
 SELECT * FROM TYPE_ELEMENT ;

INSERT INTO ELEMENT (ElementId, TypeElementId, ElementTexte) VALUES
    (1, 1, 'Formes et accords du verbe')
  , (2, 1, 'Formes et accords du nom, de l''adjectif et de l''adverbe')
  , (3, 1, 'Orthographe lexicale, signes graphiques et syntaxe')
  , (4, 1, 'Ces mots que l''on confond')
  , (5, 2, 'Le verbe : ses formes')
  , (6, 2, 'Syntaxe')
  , (7, 3, 'L''infinitif')
  , (8, 3, 'Les noms : leur féminin, leur pluriel')
  , (9, 3, 'L''imparfait du subjonctif')  
  , (10, 2, 'Le verbe, ses pièges')   
 ;
 
SELECT * FROM ELEMENT ; 

INSERT INTO COMPOSITION  (ElementComposantId, ElementComposeId) VALUES
    (5,1), (6, 3), (7, 5), (8, 2), (9, 5), (10, 1) ;
;

SELECT * FROM COMPOSITION ; 
 
INSERT INTO REGLE (RegleId, ElementId, RegleTexte, NbPhrases) VALUES
    (1, 8, 'Le pluriel des noms : cas généraux', 5)
  , (2, 6, 'est-ce que', 15) 
  , (3, 4, 'a ; à', 2)
  , (4, 4, 'pécheur ; pêcheur', 6)
  , (5, 4, 'or ; hors', 4)  
  , (6, 9, 'La 3e personne du singulier', 5)
  , (7, 9, 'La 3e personne du pluriel', 3)  
;

SELECT * FROM REGLE ; 

J’ajoute une table destinée à contenir le résultat :

CREATE TABLE PILE 
(

        PileId               INT             NOT NULL AUTO_INCREMENT           
      , Niveau               INT             NOT NULL
      , ElementId            INT             NOT NULL
      , ConcatenationNum     VARCHAR(64)     NOT NULL        
      , ConcatenationTexte   VARCHAR(512)    NOT NULL DEFAULT ''        
    , CONSTRAINT PILE_PK PRIMARY KEY (PileId)
    , CONSTRAINT PILE_AK UNIQUE (ConcatenationNum)  
) ;

Le code de la procédure récursive et l’appel (amorce) à celle-ci :

CREATE PROCEDURE RecursonsJoyeusement
(
   IN Amorce BOOLEAN, ElementIdIn VARCHAR(10)
)

BEGIN

    DECLARE theNiveau INT ;
    DECLARE Kount INT ;
 
    SET theNiveau = (SELECT DISTINCT COALESCE(MAX(Niveau) + 1, 1) FROM PILE) ;
 
    IF Amorce = TRUE THEN
         IF ElementIdIn > 0 THEN
             INSERT INTO PILE (ElementId, Niveau, ConcatenationNum, ConcatenationTexte)
                SELECT ElementIdIn, 1
                     , RTRIM(CAST(ElementIdIn AS CHAR(4)))
                     , CONCAT(CHAR(34), x.ElementTexte, CHAR(34))
                FROM   ELEMENT AS x
                WHERE  x.ElementId = ElementIdIn  
             ;                          
        ELSE
            INSERT INTO PILE (ElementId, Niveau, ConcatenationNum, ConcatenationTexte)
                SELECT ElementId, 1
                     , RTRIM(CAST(ElementId AS CHAR(4)))
                     , CONCAT(CHAR(34), ElementTexte, CHAR(34))                
                FROM   ELEMENT
                WHERE  TypeElementId = 1 ;
        END IF ;
         
        INSERT INTO PILE (ElementId, Niveau, ConcatenationNum, ConcatenationTexte) 
             SELECT ElementComposantId, 2
                  , CONCAT(RTRIM(CAST(x.ElementComposeId AS CHAR(4))), ', ', RTRIM(CAST(x.ElementComposantId AS CHAR(4))))
                  , CONCAT(CHAR(34), z.ElementTexte, CHAR(34), ', ', CHAR(34), y.ElementTexte, CHAR(34))                     
             FROM   COMPOSITION AS x JOIN ELEMENT AS y ON x.ElementComposantId = y.ElementId
                                     JOIN ELEMENT AS z ON x.ElementComposeId = z.ElementId
             WHERE  x.ElementComposeId = y.ElementId 
             ;
         CALL RecursonsJoyeusement(FALSE, '') ;
    ELSE
        SET Kount = (SELECT COUNT(*) 
                     FROM  (SELECT x.ElementComposantId, Niveau  
                            FROM   COMPOSITION AS x INNER JOIN PILE AS y 
                                   ON x.ElementComposeId = y.ElementId
                            WHERE  Niveau = TheNiveau - 1) as truc) ;
        IF Kount > 0 THEN
            INSERT INTO PILE (ElementId, Niveau, ConcatenationNum, ConcatenationTexte) 
                SELECT x.ElementComposantId, TheNiveau
                     , CONCAT(y.ConcatenationNum, ', ', RTRIM(CAST(x.ElementComposantId AS CHAR(4))))
                     , CONCAT(y.ConcatenationTexte, ', ', CHAR(34), z.ElementTexte, CHAR(34))  
                FROM   COMPOSITION AS x JOIN PILE AS y ON x.ElementComposeId = y.ElementId 
                                        JOIN ELEMENT AS z ON x.ElementComposantId = z.ElementId
               WHERE  Niveau = TheNiveau - 1 ; 

            CALL RecursonsJoyeusement(FALSE, '') ;
            
        ELSE
            INSERT INTO PILE (ElementId, Niveau, ConcatenationNum, ConcatenationTexte)
                SELECT 9999, TheNiveau
                     , CONCAT(y.ConcatenationNum, ', ', RTRIM(CAST(x.RegleId AS CHAR(4))))
                     , CONCAT(y.ConcatenationTexte, ', ', CHAR(34), RegleTexte, CHAR(34)) 
                 FROM  REGLE AS x JOIN PILE AS y ON x.ElementId = y.ElementId
            ;                 
        END IF ;

   END IF;

END

GO

DELIMITER ;

-- ----------------------------------------------------------------------
-- Appel à la procédure récursive
-- ----------------------------------------------------------------------

SET @@GLOBAL.max_sp_recursion_depth = 4;  -- Pour éviter les boucles infinies...
SET @@session.max_sp_recursion_depth = 4; 

-- -------------------------------------------------------------------------------
-- @Entree : L'élément d'identifiant ElementId = @Entree dans la table ELEMENT
-- -------------------------------------------------------------------------------

SET @Entree := 1 ;  -- L'élément d'identifiant ElementId = 1 dans la table ELEMENT (Formes et accords du verbe) 
-- SET @Entree := 5 ;  -- L'élément d'identifiant ElementId = 5 dans la table ELEMENT (Le verbe : ses formes)
-- SET @Entree := 9 ;  -- L'élément d'identifiant ElementId = 7 dans la table ELEMENT (l'imparfait du subjonctif)
-- SET @Entree := 0 ;  -- La totale

SET @Amorce  := TRUE ;

CALL RecursonsJoyeusement(@Amorce, @Entree);

-- ----------------------------------------------------------
-- Au résultat
-- ----------------------------------------------------------

SELECT ConcatenationNum, ConcatenationTexte FROM PILE
ORDER BY ConcatenationNum ;

Exemple de résultat avec @Entree = 1. ConcatenationTexte représente un élément et son ascendance, et ConcatenationNum en est la version numérique (concaténation de l’identifiant d’un élément et des identifiants de ses ascendants).

ConcatenationNum   ConcatenationTexte
1                  "Formes et accords du verbe"
1, 10              "Formes et accords du verbe", "Le verbe, ses pièges"
1, 5               "Formes et accords du verbe", "Le verbe : ses formes"
1, 5, 7            "Formes et accords du verbe", "Le verbe : ses formes", "L'infinitif"
1, 5, 9            "Formes et accords du verbe", "Le verbe : ses formes", "L'imparfait du subjonctif"
1, 5, 9, 5         "Formes et accords du verbe", "Le verbe : ses formes", "L'imparfait du subjonctif", "La 3e personne du singulier"
1, 5, 9, 6         "Formes et accords du verbe", "Le verbe : ses formes", "L'imparfait du subjonctif", "La 3e personne du pluriel"

[almoha]

Bonjour Fsmrel,

Et voilà un exemple de procédure récursive

En un seul mot : Impressionnant ! Votre proposition m'inspire plusieurs questions :

- cette procédure permettra d’afficher l’arborescence complète de toutes les règles (SET @Entree := 0).
Est-il possible d’obtenir un affichage qui s’apparente à un sommaire ?

Formes et accords du verbe
----- Le verbe : ses formes
----------- L'infinitif
----------- L'imparfait du subjonctif
----------------- La 3e personne du singulier
----------------- La 3e personne du pluriel
Etc.
(les tirets ne sont là que pour améliorer la présentation du message).

- la procédure permet-elle à l’utilisateur de visualiser l’arborescence propre à la règle qu’il vient de choisir (fil d’Ariane) ?
Ex : Formes et accords du verbe -> Le verbe : ses formes -> L'imparfait du subjonctif -> La 3e personne du pluriel

- à chaque appel de la procédure, faut-il d’abord vider la table PILE si elle existe déjà ?

- une procédure récursive s’impose-t-elle pour gérer la fonctionnalité « choix d’une règle » telle que j’envisage de l’implémenter dans l’application ?
Je m’explique. Dans l’application, le choix d’une règle par l’utilisateur se fera au travers de listes déroulantes liées :

- Une 1re liste déroulante liste les PARTIES.
=> ex. l’utilisateur choisit la PARTIE «Formes et accords du verbe »

- La liste déroulante suivante est dynamiquement peuplée par le ou les ascendant(s) direct(s) de la PARTIE choisie.
=> dans l’exemple, la liste affiche les SOUS-PARTIES suivantes : « Le verbe : ses formes » et « Le verbe, ses pièges ». L’utilisateur choisit la SOUS-PARTIE « Le verbe : ses formes »

- La liste déroulante suivante est dynamiquement peuplée par le ou les ascendant(s) direct(s) de la SOUS-PARTIE choisie.
=> dans l’exemple, la liste affiche la RUBRIQUE suivante : « L'imparfait du subjonctif ». L’utilisateur choisit cette rubrique.

- La liste déroulante suivante est dynamiquement peuplée par le ou les ascendant(s) direct(s) de la RUBRIQUE choisie.
=> dans l’exemple, la liste affiche les REGLES suivantes : « La 3e personne du singulier» et «La 3e personne du pluriel » L’utilisateur choisit une des deux règles et peut alors répondre aux questions associées.

Je suppose que ce système de filtre nécessitera plutôt plusieurs requêtes sql successives ?

Je n'oublie pas vos 2 messages précédents (incrémentation automatique de l'attribut "Fois" notamment). Je teste et vous tiens au courant. Merci encore.

[fsmrel]

Bonsoir almoha,

Est-il possible d’obtenir un affichage qui s’apparente à un sommaire ?

Formes et accords du verbe
----- Le verbe : ses formes
----------- L'infinitif
----------- L'imparfait du subjonctif
----------------- La 3e personne du singulier
----------------- La 3e personne du pluriel
Etc.
(les tirets ne sont là que pour améliorer la présentation du message).

C’est possible ! A cet effet, j’a déclaré dans la procédure une variable thePadding, qu’on peut valoriser à '-----', à blanc ou tout caractère de « padding ». Au fil des opérations, la procédure insère à gauche du texte de l’élément concerné le contenu de thePadding, multiplié par la profondeur atteinte dans la pile : REPEAT(thePadding, theNiveau - 1).

La procédure devient :

CREATE PROCEDURE RecursonsJoyeusement
(
   IN Amorce BOOLEAN, ElementIdIn VARCHAR(10)
)

BEGIN

    DECLARE theNiveau INT ;
    DECLARE Kount INT ;
    DECLARE thePadding VARCHAR(64) ;
     
    SET theNiveau = (SELECT DISTINCT COALESCE(MAX(Niveau) + 1, 1) FROM PILE) ;
    
    SET thePadding = '-----' ;  -- pour présenter le résultat à la façon d'un sommaire
    
    IF Amorce = TRUE THEN
    
    -- ----------------------------------------
    -- Niveau 1 (racine)
    -- ----------------------------------------
         IF ElementIdIn > 0 THEN
             INSERT INTO PILE (ElementId, Niveau, ConcatenationNum, ConcatenationTexte)
                SELECT ElementIdIn, 1
                     , RTRIM(CAST(ElementIdIn AS CHAR(4)))
                     , CONCAT(CHAR(34), x.ElementTexte, CHAR(34))
                FROM   ELEMENT AS x
                WHERE  x.ElementId = ElementIdIn  
             ;                          
        ELSE
            INSERT INTO PILE (ElementId, Niveau, ConcatenationNum, ConcatenationTexte)
                SELECT ElementId, 1
                     , RTRIM(CAST(ElementId AS CHAR(4)))
                     , CONCAT(CHAR(34), ElementTexte, CHAR(34))                
                FROM   ELEMENT
                WHERE  TypeElementId = 1 ;
        END IF ;
     -- ----------------------------------------
    -- Niveau 2
    -- ----------------------------------------
        
        INSERT INTO PILE (ElementId, Niveau, ConcatenationNum, ConcatenationTexte) 
             SELECT ElementComposantId, 2
                  , CONCAT(RTRIM(CAST(x.ElementComposeId AS CHAR(4))), ', ', RTRIM(CAST(x.ElementComposantId AS CHAR(4))))
                  , CONCAT(REPEAT(thePadding, theNiveau - 1), CHAR(34), y.ElementTexte, CHAR(34))                     
             FROM   COMPOSITION AS x JOIN ELEMENT AS y ON x.ElementComposantId = y.ElementId
                                     JOIN ELEMENT AS z ON x.ElementComposeId = z.ElementId
             WHERE  x.ElementComposeId = y.ElementId 
             ;
         CALL RecursonsJoyeusement(FALSE, '') ;
    ELSE

    -- ----------------------------------------
    -- Niveau > 2 (noeud)
    -- ----------------------------------------
    
        SET Kount = (SELECT COUNT(*) 
                     FROM  (SELECT x.ElementComposantId, Niveau  
                            FROM   COMPOSITION AS x INNER JOIN PILE AS y 
                                   ON x.ElementComposeId = y.ElementId
                            WHERE  Niveau = TheNiveau - 1) as truc) ;
        IF Kount > 0 THEN
        
    -- ----------------------------------------
    -- Niveau > 2 (noeud, non feuille)
    -- ----------------------------------------
        
            INSERT INTO PILE (ElementId, Niveau, ConcatenationNum, ConcatenationTexte) 
                SELECT x.ElementComposantId, TheNiveau
                     , CONCAT(y.ConcatenationNum, ', ', RTRIM(CAST(x.ElementComposantId AS CHAR(4))))
                     , CONCAT(REPEAT(thePadding, theNiveau - 1), CHAR(34), z.ElementTexte, CHAR(34))  
                FROM   COMPOSITION AS x JOIN PILE AS y ON x.ElementComposeId = y.ElementId 
                                        JOIN ELEMENT AS z ON x.ElementComposantId = z.ElementId
               WHERE  Niveau = TheNiveau - 1 ; 

            CALL RecursonsJoyeusement(FALSE, '') ;
            
        ELSE
        
    -- ----------------------------------------
    -- Niveau feuille (règle)
    -- ----------------------------------------
        
            INSERT INTO PILE (ElementId, Niveau, ConcatenationNum, ConcatenationTexte)
                SELECT 9999, TheNiveau
                     , CONCAT(y.ConcatenationNum, ', ', RTRIM(CAST(x.RegleId AS CHAR(4))))
                     , CONCAT(REPEAT(thePadding, theNiveau - 1),  CHAR(34), RegleTexte, CHAR(34)) 
                 FROM  REGLE AS x JOIN PILE AS y ON x.ElementId = y.ElementId
            ;                 
        END IF ;

    END IF;

END

GO

à chaque appel de la procédure, faut-il d’abord vider la table PILE si elle existe déjà ?

Oui, il faut vider la table, sinon il y aurait des doublons et le SGBD déclencherait une erreur :

SET @Entree := ... ;  

SET @Amorce := TRUE ;

DELETE FROM PILE ;    -- on vide la table

CALL RecursonsJoyeusement(@Amorce, @Entree);

...

Pour vos autres questions, je vais regarder ce que je peux faire ^^, mais je dois m’absenter, j’étudierai donc tout ça ce soir.


En passant, n’hésitez pas à cliquer sur fsmrel, puis « Voir le profil Pro », puis « Confirmer les compétences » si vous estimez que j’ai effectivement quelque compétence. Être reconnu ça fait toujours plaisir...

[fsmrel]

Bonsoir almoha,

la procédure permet-elle à l’utilisateur de visualiser l’arborescence propre à la règle qu’il vient de choisir (fil d’Ariane) ?
Ex : Formes et accords du verbe -> Le verbe : ses formes -> L'imparfait du subjonctif -> La 3e personne du pluriel

Il faudrait aménager la procédure de telle sorte qu’elle tienne compte du sens de parcours (à paramétrer donc) : c’est faisable, mais la procédure deviendrait bien chargée, donc pratiquement obscure, c'est-à-dire délicate à maintenir. Le moindre pet de travers dans une procédure récursive pouvant conduire à sa réécriture complète, je préfère donc ne pas y toucher, je vais en coder une autre, orientée « fil d’Ariane ».

une procédure récursive s’impose-t-elle pour gérer la fonctionnalité « choix d’une règle » telle que j’envisage de l’implémenter dans l’application ?

Comme disait Albert, essayons de faire simple, mais pas plus simple... En l'occurrence, il est préférable de laisser tomber la récursivité, procéder par requêtes successives, autrement dit, pour chaque niveau atteint par Raoul dans sa quête, coder une requête fournissant les éléments du niveau inférieur.



Incidemment, je constate vous n’avez pas voté pour les messages 17, 20, 21 (et 24 à l’heure où j’écris). Ils n’ont pas apporté les réponses que vous attendiez ?

En tout cas, merci pour la reconnaissance des compétences !

[fsmrel]

Bonsoir almoha,


Merci pour les votes


La nuit portant conseil, voici la procédure « Fil d’Ariane » que j’ai concoctée.

Tout d’abord, vous aurez peut-être envie de conserver au frais la table PILE actuelle, aussi, bien que ce ne soit pas indispensable, déclarons sa sœur jumelle, PILE_ASC :

-- ---------------------------------------------------------------------------------------
-- TABLE PILE_ASC - Simulation de la jointure récursive (en remontant depuis une règle)
-- ----------------------------------------------------------------------------------------

CREATE TABLE PILE_ASC 
(

        PileId               INT             NOT NULL AUTO_INCREMENT
      , Niveau               INT             NOT NULL
      , ElementId            INT             NOT NULL
      , ConcatenationNum     VARCHAR(64)     NOT NULL        
      , ConcatenationTexte   VARCHAR(512)    NOT NULL DEFAULT ''
    , CONSTRAINT PILE_ASC_PK PRIMARY KEY (PileId)
    , CONSTRAINT PILE_ASC_AK UNIQUE (ConcatenationNum)  
) ;

Le code de la procédure « Fil d’Ariane » (à coller par exemple à la suite de la procédure RecursonsJoyeusement) :

CREATE PROCEDURE RecursonsFilAriane
(
   IN Amorce BOOLEAN, ElementIdIn VARCHAR(10)
)

BEGIN

    DECLARE theNiveau INT ;
    DECLARE thePadding VARCHAR(64) ;
    
    SET thePadding = '-----' ;  -- pour présenter le résultat à la façon d'un sommaire
    
    IF Amorce = TRUE THEN
  
        SET theNiveau = (SELECT MAX(TypeElementId) + 1 FROM TYPE_ELEMENT) ;  -- niveau règle

    -- ----------------------------------------
    -- Niveau 1 (feuille = règle)
    -- ----------------------------------------

        INSERT INTO PILE_ASC (ElementId, Niveau, ConcatenationNum, ConcatenationTexte)
            SELECT 9999, theNiveau
                 , '9999'
                 , CONCAT(REPEAT(thePadding, theNiveau - 1), CHAR(34), RegleTexte, CHAR(34))
            FROM   REGLE
            WHERE  RegleId = ElementIdIn  
        ;                          
     -- ----------------------------------------
    -- Niveau 2 (parent de règle)
    -- ----------------------------------------
        
        INSERT INTO PILE_ASC (ElementId, Niveau, ConcatenationNum, ConcatenationTexte) 
             SELECT y.ElementId, theNiveau  - 1
                  , CONCAT(RTRIM(CAST(y.ElementId AS CHAR(4))), ', ', 9999)
                  , CONCAT(REPEAT(thePadding, theNiveau - 2), CHAR(34), y.ElementTexte, CHAR(34))                     
             FROM   REGLE AS x JOIN ELEMENT AS y ON x.ElementId = y.ElementId
             WHERE  RegleId = ElementIdIn
             ;
             
        CALL RecursonsFilAriane(FALSE, '') ;
        
    ELSE

        SET theNiveau = (SELECT MIN(Niveau) - 1 FROM PILE_ASC) ;
        
    -- -------------------------------------------------------
    -- Niveau > 2 (parent de parent, jusqu'à racine)
    -- -------------------------------------------------------
    
        IF theNiveau > 1 THEN
       
    -- ----------------------------------------
    -- Niveau > 2 (noeud, non feuille)
    -- ----------------------------------------
       
            INSERT INTO PILE_ASC (ElementId, Niveau, ConcatenationNum, ConcatenationTexte) 
                SELECT x.ElementComposeId, TheNiveau                
                     , CONCAT(RTRIM(CAST(x.ElementComposeId AS CHAR(4))), ', ', y.ConcatenationNum)                     
                     , CONCAT(REPEAT(thePadding, theNiveau - 1), CHAR(34), z.ElementTexte, CHAR(34))  
                FROM   COMPOSITION AS x JOIN PILE_ASC AS y ON x.ElementComposantId = y.ElementId 
                                        JOIN ELEMENT AS z ON x.ElementComposeId = z.ElementId
                WHERE  y.Niveau = TheNiveau + 1 
            ;
            
            CALL RecursonsFilAriane(FALSE, '') ;
            
        ELSE
        
    -- ----------------------------------------
    -- Niveau racine (partie)
    -- ----------------------------------------
        
            INSERT INTO PILE_ASC  (ElementId, Niveau, ConcatenationNum, ConcatenationTexte)
                SELECT x.ElementComposeId, TheNiveau
                     , CONCAT(RTRIM(CAST(x.ElementComposeId AS CHAR(4))), ', ', y.ConcatenationNum)                      
                     , CONCAT(CHAR(34), z.ElementTexte, CHAR(34))                      
                FROM   COMPOSITION AS x JOIN PILE_ASC AS y ON x.ElementComposantId = y.ElementId 
                                        JOIN ELEMENT AS z ON x.ElementComposeId = z.ElementId
                WHERE  y.Niveau = TheNiveau + 1
            ;             
        END IF ;

    END IF;

END

GO

L’appel initial à la procédure est identique à celui que vous connaissez, si ce n’est qu’on passe en paramètre l’identifiant RegleId (cf. tables REGLE ou CHOISIR) de la règle choisie par Raoul :

- ----------------------------------------------------------------------
-- Appel à la procédure récursive
-- ----------------------------------------------------------------------

SET @@GLOBAL.max_sp_recursion_depth = 4;
SET @@session.max_sp_recursion_depth = 4; 

-- -------------------------------------------------------------------------------
-- @Entree : Identifiant (RegleId) dans la table REGLE
-- -------------------------------------------------------------------------------

SET @Entree := 7 ;  -- Remontée à partir de RegleId = 7 (3e personne du pluriel) 

SET @Amorce  := TRUE ;

DELETE FROM PILE_ASC ;  -- on vide la table

CALL RecursonsFilAriane(@Amorce, @Entree);

-- ----------------------------------------------------------
-- Au résultat
-- ----------------------------------------------------------

SELECT * FROM PILE_ASC
ORDER BY Niveau  ;

SELECT Niveau, ConcatenationTexte 
FROM   PILE_ASC
ORDER BY Niveau ;

Cela vous convient-il ?

[almoha]

Bonsoir fsmrel,

Merci pour les votes

C'est la moindre des choses !

Le code de la procédure « Fil d’Ariane » ... Cela vous convient-il ?

Cela me convient mais, après avoir testé, je me suis rendu compte qu'en choisissant les règles RegleId3, RegleId4 et RegleId5 (règles rattachées seulement à une PARTIE : en l'occurrence à l'ElementId4 "Ces mots que l'on confond"), j'ai une erreur. Ce qui est normal car la table COMPOSITION ne contient pas en l'état de référence à l'ElementId4.

Pour éviter l'erreur j'ai fait l'INSERT suivant :

Code :

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

1
2
INSERT INTO COMPOSITION  (ElementComposantId, ElementComposeId) VALUES
(4,4) ;

J'obtiens alors le résultat suivant :

Code :

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

1
2
3
4
5
ConcatenationNum	ConcatenationTexte
4, 1, 4, 4, 4, 4, 9999	"Ces mots que l'on confond"
3, 2, 4, 4, 4, 9999	-----"Ces mots que l'on confond"
2, 3, 4, 4, 9999	----------"Ces mots que l'on confond"
1, 4, 9999, 9999	---------------"a ; à"

Voyez-vous une solution plus "satisfaisante" pour gérer ce cas de figure ? Merci.

[fsmrel]

Bonsoir almoha,


Je regarderai ce cas de rattachement direct d'une règle à une partie.


Juste une petite remarque en passant :

A l’occasion de la recopie de la procédure RecursonsJoyeusement en RecursonsFilAriane, il est manifestement resté un commentaire qui doit être corrigé, il s’agit du tout dernier :

-- ----------------------------------------
-- Niveau feuille (règle)
-- ----------------------------------------

Comme on remonte des règles vers les parties (honni soit qui mal y pense ^^) , il faut lire :

	     
-- ----------------------------------------
-- Niveau racine (partie)
-- ----------------------------------------

J’ai modifié le message #26 en conséquence.

[fsmrel]

Bonsoir à nouveau,

je me suis rendu compte qu'en choisissant les règles RegleId3, RegleId4 et RegleId5 (règles rattachées seulement à une PARTIE : en l'occurrence à l'ElementId4 "Ces mots que l'on confond"), j'ai une erreur.

Mea culpa, on part pour une boucle infinie

On remplace donc le code de la procédure fautive par celui-ci (correctif en bleu) :

CREATE PROCEDURE RecursonsFilAriane
(
   IN Amorce BOOLEAN, ElementIdIn VARCHAR(10)
)

BEGIN

    DECLARE theNiveau INT ;
    DECLARE thePadding VARCHAR(64) ;
    DECLARE theTypeElementId INT ;
    
    SET thePadding = '-----' ;  -- pour présenter le résultat à la façon d'un sommaire
    
    IF Amorce = TRUE THEN
  
        SET theNiveau = (SELECT MAX(TypeElementId) + 1 FROM TYPE_ELEMENT) ;  -- niveau règle = Niveau maxi + 1

    -- ----------------------------------------
    -- Niveau 1 (feuille = règle)
    -- ----------------------------------------

        INSERT INTO PILE_ASC (ElementId, Niveau, ConcatenationNum, ConcatenationTexte)
            SELECT 9999, theNiveau
                 , '9999'
                 , CONCAT(REPEAT(thePadding, theNiveau - 1), CHAR(34), RegleTexte, CHAR(34))
            FROM   REGLE
            WHERE  RegleId = ElementIdIn  
        ;                          
     -- ----------------------------------------
    -- Niveau 2 (parent de règle)
    -- ----------------------------------------
        
        INSERT INTO PILE_ASC (ElementId, Niveau, ConcatenationNum, ConcatenationTexte) 
             SELECT y.ElementId, theNiveau  - 1
                  , CONCAT(RTRIM(CAST(y.ElementId AS CHAR(4))), ', ', 9999)
                  , CONCAT(REPEAT(thePadding, theNiveau - 2), CHAR(34), y.ElementTexte, CHAR(34))                     
             FROM   REGLE AS x JOIN ELEMENT AS y ON x.ElementId = y.ElementId
             WHERE  RegleId = ElementIdIn
             ;
             
     -- -----------------------------------------------------------------------
    -- Si la règle est directement rattachée à la partie, on a fini,
    -- et il est préférable de ne pas s'embarquer dans une boucle infinie...
    -- ------------------------------------------------------------------------
             
        SET theTypeElementId = (SELECT DISTINCT TypeElementId
                                FROM   ELEMENT AS x JOIN PILE_ASC AS y ON x.ElementId = y.ElementId  
                               ) ;
                               
        IF theTypeElementId > 1 THEN                                
            CALL RecursonsFilAriane(FALSE, '') ;
        END IF ;

    ELSE

        SET theNiveau = (SELECT MIN(Niveau) - 1 FROM PILE_ASC) ;
        
    -- -------------------------------------------------------
    -- Niveau > 2 (parent de parent, jusqu'à racine)
    -- -------------------------------------------------------
    
        IF theNiveau > 1 THEN
       
    -- ----------------------------------------
    -- Niveau > 2 (noeud, non feuille)
    -- ----------------------------------------
       
            INSERT INTO PILE_ASC (ElementId, Niveau, ConcatenationNum, ConcatenationTexte) 
                SELECT x.ElementComposeId, TheNiveau                
                     , CONCAT(RTRIM(CAST(x.ElementComposeId AS CHAR(4))), ', ', y.ConcatenationNum)                     
                     , CONCAT(REPEAT(thePadding, theNiveau - 1), CHAR(34), z.ElementTexte, CHAR(34))  
                FROM   COMPOSITION AS x JOIN PILE_ASC AS y ON x.ElementComposantId = y.ElementId 
                                        JOIN ELEMENT AS z ON x.ElementComposeId = z.ElementId
                WHERE  y.Niveau = TheNiveau + 1 
            ;
            
            CALL RecursonsFilAriane(FALSE, '') ;
            
        ELSE
        
    -- ----------------------------------------
    -- Niveau racine (Partie)
    -- ----------------------------------------
        
            INSERT INTO PILE_ASC (ElementId, Niveau, ConcatenationNum, ConcatenationTexte)
                SELECT x.ElementComposeId, TheNiveau
                     , CONCAT(RTRIM(CAST(x.ElementComposeId AS CHAR(4))), ', ', y.ConcatenationNum)                      
                     , CONCAT(CHAR(34), z.ElementTexte, CHAR(34))                      
                FROM   COMPOSITION AS x JOIN PILE_ASC AS y ON x.ElementComposantId = y.ElementId 
                                        JOIN ELEMENT AS z ON x.ElementComposeId = z.ElementId
                WHERE  y.Niveau = TheNiveau + 1
            ;
             
        END IF ;

    END IF ;

END

GO

Au passage, je joins un SELECT permettant d’afficher le type d’élément dans le résultat :

SELECT x.ElementId
     , CASE TypeElementId 
            WHEN 1 THEN 'Partie' 
            WHEN 2 THEN 'Sous-partie'
            WHEN 3 THEN 'Rubrique' 
            ELSE 'Règle' 
       END 
     , ConcatenationTexte
FROM   PILE_ASC AS x LEFT JOIN ELEMENT AS y ON x.ElementId = y.ElementId
ORDER BY Niveau ;

Pour éviter l'erreur j'ai fait l'INSERT suivant :

INSERT INTO COMPOSITION  (ElementComposantId, ElementComposeId) VALUES (4,4) ;

Aïe ! Il y a un effet de bord, à savoir que cette fois-ci, c’est la procédure RecursonsJoyeusement qui part allègrement dans une boucle infinie

Plus généralement, il est conseillé de ne pas toucher aux données d’une table pour corriger le comportement d’une requête ou d’une procédure, ce sont ces dernières qui sont à revoir (quod erat demonstrandum)...

[almoha]

Bonsoir fsmrel,

Merci pour le nouveau code de la procédure "RecursonsFilAriane". Il remplit parfaitement son office.

Plus généralement, il est conseillé de ne pas toucher aux données d’une table pour corriger le comportement d’une requête ou d’une procédure, ce sont ces dernières qui sont à revoir (quod erat demonstrandum)...

Dont acte

Je reviens sur la procédure "RecursonsJoyeusement". Si je transpose le SELECT d'affichage que vous m'avez proposé pour la procédure "RecursonsFilAriane", j'obtiens comme résultat (avec SET @Entree := 0) :



Je me demande s'il est possible, s'agissant d'une règle, d'obtenir son véritable ElementId dans la colonne dédiée du résultat. Par ex. pour la règle "la 3e personne du singulier", son ElementId est actuellement de 9999 alors que je souhaiterais récupérer son ElementId véritable (6), lequel est d'ailleurs présent, à la fin, dans le champ ConcatenationNum. Cela me permettrait de faire le lien avec les phrases associées à une règle.

[fsmrel]

Bonsoir almoha,

Je me demande s'il est possible, s'agissant d'une règle, d'obtenir son véritable ElementId dans la colonne dédiée du résultat.

On peut l’obtenir (une ligne à changer (en bleu) dans les procédures, on ne va donc pas se priver ^^)

Je rappelle que le but de la manoeuvre est de ne pas prendre une vessie pour une lanterne, c'est-à-dire confondre une valeur de RegleId avec une valeur d’ElementId, d’où la valeur 9999 que vous avez pu observer dans la table PILE (en supposant que la table ELEMENT ne contiendra pas 9999 lignes... allez, pour être au large on va passer à 100000).


Procédure RecursonsJoyeusement :

CREATE PROCEDURE RecursonsJoyeusement
(
   IN Amorce BOOLEAN, ElementIdIn VARCHAR(10)
)

BEGIN

    DECLARE theNiveau INT ;
    DECLARE Kount INT ;
     
    SET theNiveau = (SELECT DISTINCT COALESCE(MAX(Niveau) + 1, 1) FROM PILE) ;
        
    IF Amorce = TRUE THEN
    
    -- ----------------------------------------
    -- Niveau 1 (racine)
    -- ----------------------------------------
    
         IF ElementIdIn > 0 THEN
             INSERT INTO PILE (ElementId, Niveau, ConcatenationNum, ConcatenationTexte)
                SELECT ElementIdIn, 1
                     , RTRIM(CAST(ElementIdIn AS CHAR(4)))
                     , CONCAT(CHAR(34), x.ElementTexte, CHAR(34))
                FROM   ELEMENT AS x
                WHERE  x.ElementId = ElementIdIn  
             ;                          
        ELSE
            INSERT INTO PILE (ElementId, Niveau, ConcatenationNum, ConcatenationTexte)
                SELECT ElementId, 1
                     , RTRIM(CAST(ElementId AS CHAR(4)))
                     , CONCAT(CHAR(34), ElementTexte, CHAR(34))                
                FROM   ELEMENT
                WHERE  TypeElementId = 1 ;
        END IF ;
        
     -- ----------------------------------------
    -- Niveau 2
    -- ----------------------------------------
        
        INSERT INTO PILE (ElementId, Niveau, ConcatenationNum, ConcatenationTexte) 
             SELECT ElementComposantId, 2
                  , CONCAT(RTRIM(CAST(x.ElementComposeId AS CHAR(4))), ', ', RTRIM(CAST(x.ElementComposantId AS CHAR(4))))
                  , CONCAT(CHAR(34), y.ElementTexte, CHAR(34))                     
             FROM   COMPOSITION AS x JOIN ELEMENT AS y ON x.ElementComposantId = y.ElementId
                                     JOIN ELEMENT AS z ON x.ElementComposeId = z.ElementId
             WHERE  x.ElementComposeId = y.ElementId 
             ;
             
        CALL RecursonsJoyeusement(FALSE, '') ;

    ELSE

    -- ----------------------------------------
    -- Niveau > 2 (noeud)
    -- ----------------------------------------
    
        SET Kount = (SELECT COUNT(*) 
                     FROM  (SELECT x.ElementComposantId, Niveau  
                            FROM   COMPOSITION AS x INNER JOIN PILE AS y 
                                   ON x.ElementComposeId = y.ElementId
                            WHERE  Niveau = TheNiveau - 1) as truc) ;
        IF Kount > 0 THEN
        
    -- ----------------------------------------
    -- Niveau > 2 (noeud, non feuille)
    -- ----------------------------------------
        
            INSERT INTO PILE (ElementId, Niveau, ConcatenationNum, ConcatenationTexte) 
                SELECT x.ElementComposantId, TheNiveau
                     , CONCAT(y.ConcatenationNum, ', ', RTRIM(CAST(x.ElementComposantId AS CHAR(4))))
                     , CONCAT(CHAR(34), z.ElementTexte, CHAR(34))  
                FROM   COMPOSITION AS x JOIN PILE AS y ON x.ElementComposeId = y.ElementId 
                                        JOIN ELEMENT AS z ON x.ElementComposantId = z.ElementId
               WHERE  Niveau = TheNiveau - 1 ; 

            CALL RecursonsJoyeusement(FALSE, '') ;
            
        ELSE
        
    -- ----------------------------------------
    -- Niveau feuille (règle)
    -- ----------------------------------------
        
            INSERT INTO PILE (ElementId, Niveau, ConcatenationNum, ConcatenationTexte)
                 SELECT x.RegleId + 100000, TheNiveau  -- On fait + 100000 pour ne pas confondre les id des règles et des éléments
                     , CONCAT(y.ConcatenationNum, ', ', RTRIM(CAST(x.RegleId AS CHAR(4))))
                     , CONCAT(CHAR(34), RegleTexte, CHAR(34)) 
                 FROM  REGLE AS x JOIN PILE AS y ON x.ElementId = y.ElementId
            ;
            
        END IF ;

    END IF;

END

GO

Conséquence au niveau des requêtes : il suffit de retrancher 100000 pour retrouver la valeur de RegleId :

SELECT   
       CASE 
           WHEN x.ElementId < 100000 THEN x.ElementId
           ELSE x.ElementId - 100000
       END 
       AS ElementId
     , CASE TypeElementId 
            WHEN 1 THEN 'Partie' 
            WHEN 2 THEN 'Sous-partie'
            WHEN 3 THEN 'Rubrique' 
            ELSE 'Règle' 
       END
       AS TypeElement
     , ConcatenationNum       
     , CASE TypeElementId 
            WHEN 1 THEN ConcatenationTexte 
            WHEN 2 THEN CONCAT('----', ConcatenationTexte)
            WHEN 3 THEN CONCAT('--------', ConcatenationTexte) 
            ELSE CONCAT('------------', ConcatenationTexte) 
       END
       AS ConcatenationTexte       
FROM   PILE AS x LEFT JOIN ELEMENT AS y ON x.ElementId = y.ElementId
ORDER BY ConcatenationNum ;

On peut procéder de la même façon pour la procédure RecursonsFilAriane :

CREATE PROCEDURE RecursonsFilAriane
(
   IN Amorce BOOLEAN, ElementIdIn VARCHAR(10)
)

BEGIN

    DECLARE theNiveau INT ;
    DECLARE theTypeElementId INT ;
        
    IF Amorce = TRUE THEN
  
        SET theNiveau = (SELECT MAX(TypeElementId) + 1 FROM TYPE_ELEMENT) ;  -- niveau règle = Niveau maxi + 1

    -- ----------------------------------------
    -- Niveau 1 (feuille = règle)
    -- ----------------------------------------

        INSERT INTO PILE_ASC (ElementId, Niveau, ConcatenationNum, ConcatenationTexte)
            SELECT 100000 + RegleId, theNiveau  
                 , RegleId                  
                 , CONCAT(CHAR(34), RegleTexte, CHAR(34))
            FROM   REGLE
            WHERE  RegleId = ElementIdIn  
        ;                          
     -- ----------------------------------------
    -- Niveau 2 (parent de règle)
    -- ----------------------------------------
        
        INSERT INTO PILE_ASC (ElementId, Niveau, ConcatenationNum, ConcatenationTexte) 
             SELECT y.ElementId, theNiveau  - 1  
                  , CONCAT(RTRIM(CAST(y.ElementId AS CHAR(4))), ', ', RegleId) 
                  , CONCAT(CHAR(34), y.ElementTexte, CHAR(34))    
             FROM   REGLE AS x JOIN ELEMENT AS y ON x.ElementId = y.ElementId
             WHERE  RegleId = ElementIdIn
             ;
             
     -- -----------------------------------------------------------------------
    -- Si la règle est directement rattachée à la partie, on a fini,
    -- et il est préférable de ne pas s'embarquer dans une boucle infinie...
    -- ------------------------------------------------------------------------
             
        SET theTypeElementId = (SELECT DISTINCT TypeElementId
                                FROM   ELEMENT AS x JOIN PILE_ASC AS y ON x.ElementId = y.ElementId  
                               ) ;
                               
        IF theTypeElementId > 1 THEN                                
            CALL RecursonsFilAriane(FALSE, '') ;
        END IF ;
        
    ELSE

        SET theNiveau = (SELECT MIN(Niveau) - 1 FROM PILE_ASC) ;
        
    -- -------------------------------------------------------
    -- Niveau > 2 (parent de parent, jusqu'à racine)
    -- -------------------------------------------------------
    
        IF theNiveau > 1 THEN
       
    -- ----------------------------------------
    -- Niveau > 2 (noeud, non feuille)
    -- ----------------------------------------
       
            INSERT INTO PILE_ASC (ElementId, Niveau, ConcatenationNum, ConcatenationTexte) 
                SELECT x.ElementComposeId, TheNiveau                
                     , CONCAT(RTRIM(CAST(x.ElementComposeId AS CHAR(4))), ', ', y.ConcatenationNum)    
                     , CONCAT(CHAR(34), z.ElementTexte, CHAR(34))                     
                FROM   COMPOSITION AS x JOIN PILE_ASC AS y ON x.ElementComposantId = y.ElementId 
                                        JOIN ELEMENT AS z ON x.ElementComposeId = z.ElementId
                WHERE  y.Niveau = TheNiveau + 1 
            ;
            
            CALL RecursonsFilAriane(FALSE, '') ;
            
        ELSE
        
    -- ----------------------------------------
    -- Niveau racine (Partie)
    -- ----------------------------------------
        
            INSERT INTO PILE_ASC (ElementId, Niveau, ConcatenationNum, ConcatenationTexte)
                SELECT x.ElementComposeId, TheNiveau
                     , CONCAT(RTRIM(CAST(x.ElementComposeId AS CHAR(4))), ', ', y.ConcatenationNum)    
                     , CONCAT(CHAR(34), z.ElementTexte, CHAR(34))                      
                FROM   COMPOSITION AS x JOIN PILE_ASC AS y ON x.ElementComposantId = y.ElementId 
                                        JOIN ELEMENT AS z ON x.ElementComposeId = z.ElementId
                WHERE  y.Niveau = TheNiveau + 1
            ;             

        END IF ;

    END IF ;

END

GO

Impact du « 100000 » sur les requêtes :

SELECT
       CASE
           WHEN x.ElementId < 100000 THEN x.ElementId
           ELSE x.ElementId - 100000
       END 
       AS ElementId

     , CASE TypeElementId 
            WHEN 1 THEN 'Partie' 
            WHEN 2 THEN 'Sous-partie'
            WHEN 3 THEN 'Rubrique' 
            ELSE 'Règle' 
       END
       AS TypeElement 
     , ConcatenationNum

     , CASE TypeElementId 
            WHEN 1 THEN ConcatenationTexte 
            WHEN 2 THEN CONCAT('----', ConcatenationTexte)
            WHEN 3 THEN CONCAT('--------', ConcatenationTexte) 
            ELSE CONCAT('------------', ConcatenationTexte) 
       END
       AS Element       

FROM   PILE_ASC AS x LEFT JOIN ELEMENT AS y ON x.ElementId = y.ElementId
ORDER BY Niveau ;

Et voilà pour ce soir... J’espère ne pas m’être mélangé dans mes copier/coller, vous me direz...


P.-S. J'ai viré le padding ('---------------') dans les procédures, on le maîtrise mieux au niveau des requêtes.

[almoha]

Bonjour fsmrel,

Merci pour la modification des deux procédures qui répondent parfaitement à ce que je recherchais.

S'agissant de la fonctionnalité « choix d’une règle » :

Comme disait Albert, essayons de faire simple, mais pas plus simple... En l'occurrence, il est préférable de laisser tomber la récursivité, procéder par requêtes successives, autrement dit, pour chaque niveau atteint par Raoul dans sa quête, coder une requête fournissant les éléments du niveau inférieur.

Ces requêtes successives comprendront-elles des auto-jointures comme celles que je pense identifier dans les procédures ?

-> 1ere requête : affiche les parties;
-> 2e requête : affiche, en fonction de la partie choisie, la ou les sous-partie(s) associées ou, le cas échéant, la ou les rubrique(s) associées ou, le cas échéant, les règles associées;
-> 3e requête : affiche, en fonction du choix précédent, soit la ou les rubrique(s) associée(s), soit les règles associées, soit les phrases associées (dans ce dernier cas plus de requête à effectuer);
-> le cas échéant 4e requête : affiche, en fonction du choix précédent, soit les règles associées, soit les phrases associées à la règle choisie (dans ce dernier cas plus de requête à effectuer);
-> le cas échéant 5e requête : affiche les phrases associées à la règle choisie.

Pouvez-vous m'aider à construire ces requêtes ? Merci.

Par ailleurs, j'ai testé l'incrémentation automatique de l'attribut "Fois". Cela marche à merveille

[fsmrel]

Bonsoir almoha,

Je ne suis pas sûr d’avoir parfaitement compris ce que vous souhaitez, en tout cas, je vous proposeun jeu de requêtes permettant d’afficher (dans le mode « soit, soit ») :

L’ensemble des parties ;

Les sous-parties d’une partie ;

Les rubriques directement rattachées à une partie ;

Les règles directement rattachées à une partie ;

Les rubriques rattachées à une sous-partie ;

Les règles directement rattachées à une sous-partie ;

Les règles rattachées à une rubrique ;

Les phrases rattachées à une règle.

-- -----------------------------------------------------------------------
-- Requête R1
-- Sélection des parties
-- -----------------------------------------------------------------------

SELECT ElementTexte
FROM  ELEMENT
WHERE TypeElementId = 1
;

-- ---------------------------------------------------------------------------
-- Sélection des éléments directement rattachés à la partie choisie par Raoul
-- ---------------------------------------------------------------------------

-- ---------------------------------------------------
-- Requête R2
-- Raoul choisit la partie "Formes et accords du verbe"
-- (table COMPOSITION, ElementComposeId = 1)
-- et on ne souhaite afficher que les sous-parties
-- (TypeElementId = 2).
-- --------------------------------------------------

SELECT z.TypeElementLibelle, x.ElementTexte, 'sous-parties de partie 1' AS Commentaire 
FROM   ELEMENT AS x JOIN COMPOSITION as y ON x.ElementId = y.ElementComposantId
                    JOIN TYPE_ELEMENT AS z ON x.TypeElementId = z.TypeElementId 
WHERE  y.ElementComposeId = 1  -- Partie choisie par Raoul
  AND  x.TypeElementId = 2 -- pour n'afficher que les sous-parties
;

-- ---------------------------------------------------
-- Requête R3
-- Raoul choisit la partie "Formes et accords du verbe"
-- (table COMPOSITION, ElementComposeId = 1)
-- et on ne souhaite afficher que les rubriques
-- directement rattachées à la partie
-- (TypeElementId = 3).
-- --------------------------------------------------

SELECT z.TypeElementLibelle, x.ElementTexte, 'rubriques directement rattachées à partie 1' AS Commentaire
FROM   ELEMENT AS x JOIN COMPOSITION as y ON x.ElementId = y.ElementComposantId
                    JOIN TYPE_ELEMENT AS z ON x.TypeElementId = z.TypeElementId 
WHERE  y.ElementComposeId = 1  -- Partie choisie par Raoul
  AND  x.TypeElementId = 3 -- pour n'afficher que les rubriques directement rattachées à la partie  
;

-- ---------------------------------------------------
-- Requête R4
-- Raoul choisit la partie "Formes et accords du verbe"
-- (table REGLE, ElementId = 1)
-- et on ne souhaite afficher que les règles
-- directement rattachées à la partie.
-- --------------------------------------------------

SELECT 'Règle', RegleTexte, 'règles directement rattachées à partie 1' AS Commentaire
FROM   REGLE 
WHERE  ElementId = 1  -- Partie choisie par Raoul (on n'affiche que les règles)
;

-- -------------------------------------------------------------------------------------
-- Sélection des éléments directement rattachés à la sous-partie choisie par Raoul
-- -------------------------------------------------------------------------------------

-- ---------------------------------------------------
-- Requête R5
-- Raoul choisit la sous-partie "le verbe, ses formes"
-- (table COMPOSITION, ElementComposeId = 5)
-- et on ne souhaite afficher que les rubriques
-- rattachées à la sous-partie.
-- --------------------------------------------------

SELECT z.TypeElementLibelle, x.ElementTexte, 'rubriques directement rattachées à sous-partie 5' AS Commentaire
FROM   ELEMENT AS x JOIN COMPOSITION as y ON x.ElementId = y.ElementComposantId
                    JOIN TYPE_ELEMENT AS z ON x.TypeElementId = z.TypeElementId 
WHERE  y.ElementComposeId = 5  -- le verbe, ses formes
  AND  x.TypeElementId = 3 -- pour n'afficher que les rubriques directement rattachées à la sous-partie
;

-- ---------------------------------------------------
-- Requête R6
-- Raoul choisit la sous-partie "le verbe, ses formes"
-- (table COMPOSITION, ElementComposeId = 5)
-- et on ne souhaite afficher que les règles
-- directement rattachées à la sous-partie.
-- --------------------------------------------------

SELECT 'Règle', RegleTexte, 'règles directement rattachées à sous-partie 5' AS Commentaire
FROM   REGLE 
WHERE  ElementId = 5  -- sous-partie choisie par Raoul (on n'affiche que les règles)
;

-- -------------------------------------------------------------------------------------
-- Sélection des règles rattachées à la rubrique choisie par Raoul
-- -------------------------------------------------------------------------------------

-- ---------------------------------------------------
-- Requête R7
-- Raoul choisit la rubrique "Rubrique-à-brac"
-- (table REGLE, ElementId = 14)
-- on affiche les règles rattachées à rubrique.
-- --------------------------------------------------

SELECT 'Règle', RegleTexte, 'règles rattachées à rubrique 14' AS Commentaire
FROM   REGLE 
WHERE  ElementId = 14  -- rubrique choisie par Raoul
;

-- ---------------------------------------------------
-- Requête R8
-- Raoul choisit la règle "Règle-à-brac"
-- (table REGLE, Règle = 9)
-- on affiche les phrases rattachées à règle.
-- ---------------------------------------------------

SELECT 'Phrase', Texte, 'Phrases rattachées à la règle 9' AS Commentaire
FROM   REGLE AS x JOIN PHRASE AS y ON x.RegleId = y.RegleId
WHERE  x.RegleId = 9 -- L'imparfait du subjonctif
;

En paramétrant le choix de Raoul, et le type d’élément, on peut bien sûr ne faire qu’une seule requête des requêtes R2, R3 et R5, même chose pour les requêtes 4, 6 et 7.

Sommes-nous en phase ?

[almoha]

Bonsoir fsmrel,

Merci pour votre réponse. J’ai oublié de rappeler que les requêtes participent, au niveau applicatif, au fonctionnement d’un système de listes déroulantes liées au travers desquelles l’utilisateur choisit une règle à étudier.

I - Dans le cas plus simple : une règle se rattache à une partie, à une sous-partie et à une rubrique

Pour les besoins de l’exemple que je vais utiliser, je complète la table PHRASE :

Code :

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

1
2
3
4
INSERT INTO `phrase` (`RegleId`, `PhraseId`, `DifficulteId`, `Position`, `Texte`) VALUES
(7, 1, 3, 0, 'Qu''il aimât cela !'),
(7, 2, 3, 2, 'Qu''il n''aimat pas cela !')
;

L'enchainement logique est le suivant :

1 - Une liste déroulante affiche les parties :

Requête 1 :

Code :

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

1
2
3
4
SELECT ElementTexte
FROM  ELEMENT
WHERE TypeElementId = 1
;

Résultat :
ElementTexte
Formes et accords du verbe
Formes et accords du nom, de l'adjectif et de l'adverbe
Orthographe lexicale, signes graphiques et syntaxe
Ces mots que l'on confond

==> problématique : il faudrait que la requête ci-dessus me fournisse le ElementComposeId de chaque partie pour que l'application puisse fournir ce critère de filtre à la requête suivante (WHERE y.ElementComposeId)

=> ex. l’utilisateur choisit la PARTIE « Formes et accords du verbe »

2 - Une liste déroulante (liée à la précédente) est dynamiquement peuplée par le ou les descendant(s) direct(s) de la PARTIE choisie, soit en l’espèce par les sous-parties associées :

Requête 2 :

Code :

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

1
2
3
4
5
6
SELECT z.TypeElementLibelle, x.ElementTexte, 'sous-parties de partie 1' AS Commentaire 
FROM   ELEMENT AS x JOIN COMPOSITION as y ON x.ElementId = y.ElementComposantId
                    JOIN TYPE_ELEMENT AS z ON x.TypeElementId = z.TypeElementId 
WHERE  y.ElementComposeId = 1  -- Partie choisie par Raoul
  AND  x.TypeElementId = 2 -- pour n'afficher que les sous-parties
;

Résultat :
TypeElementLibelle | ElementTexte|Commentaire
Sous-partie | "Le verbe : ses formes" | "sous-parties de partie 1"
Sous-partie | "Le verbe, ses pièges" | "sous-parties de partie 1"

==> problématique : il faudrait que la requête ci-dessus me fournisse le ElementComposeId de chaque sous-partie pour que l'application puisse fournir ce critère de filtre à la requête suivante (WHERE y.ElementComposeId).

=> ex. l’utilisateur choisit la SOUS-PARTIE « Le verbe : ses formes ».

3 - Une liste déroulante (liée à la précédente) est dynamiquement peuplée par le ou les descendant(s) direct(s) de la SOUS-PARTIE choisie, soit en l’espèce par les rubriques associées :

Requête 3 :

Code :

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

1
2
3
4
5
6
SELECT z.TypeElementLibelle, x.ElementTexte, 'rubriques directement rattachées à sous-partie 5' AS Commentaire
FROM   ELEMENT AS x JOIN COMPOSITION as y ON x.ElementId = y.ElementComposantId
                    JOIN TYPE_ELEMENT AS z ON x.TypeElementId = z.TypeElementId 
WHERE  y.ElementComposeId = 5  -- le verbe, ses formes
  AND  x.TypeElementId = 3 -- pour n'afficher que les rubriques directement rattachées à la sous-partie
;

Résultat :
TypeElementLibelle | ElementTexte | Commentaire
Rubrique | L'infinitif | "rubriques directement rattachées à sous-partie 5"
Rubrique | "L'imparfait du subjonctif" | "rubriques directement rattachées à sous-partie 5"

==> problématique : je peux demander à la requête ci-dessus (en rajoutant dans le SELECT, x.ElementId) qu'elle me fournisse le ElementId de chaque rubrique pour que l'application puisse fournir ce critère de filtre à la requête suivante (WHERE ElementId).

=> ex. l’utilisateur choisit la rubrique « L'imparfait du subjonctif ».

4 - Une liste déroulante (liée à la précédente) est dynamiquement peuplée par le ou les descendant(s) direct(s) de la RUBRIQUE choisie, soit en l’espèce par les règles associées :

Requête 4 :

Code :

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

1
2
3
4
SELECT 'Règle', RegleTexte, 'règles rattachées à rubrique 9' AS Commentaire
FROM   REGLE 
WHERE  ElementId = 9  -- ElementId de la rubrique choisie par Raoul
;

Résultat :
Règle | RegleTexte | Commentaire
Règle | "La 3e personne du singulier" | "règles rattachées à rubrique 9"
Règle | "La 3e personne du pluriel" | "règles rattachées à rubrique 9"

==> problématique : je peux demander à la requête ci-dessus (en rajoutant dans le SELECT, RegleId) qu'elle me fournisse le RegleId de chaque règle pour que l'application puisse fournir ce critère de filtre à la requête suivante (WHERE x.RegleId).

=> ex. l’utilisateur choisit la règle « La 3e personne du singulier».

5 – Les phrases associées à la règle choisie sont disponibles :

Requête 5 :

Code :

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

1
2
3
4
SELECT 'Phrase', Texte, 'Phrases rattachées à la règle 7' AS Commentaire
FROM   REGLE AS x JOIN PHRASE AS y ON x.RegleId = y.RegleId
WHERE  x.RegleId = 7 -- ElementId de la règle choisie par Raoul
;

Résultat :
Phrase | Texte | Commentaire
Phrase | "Qu'il aimât cela !" | "Phrases rattachées à la règle 7"
Phrase | "Qu'il n'aimat pas cela !" | "Phrases rattachées à la règle 7"


II – Quid du cas où une règle n’est pas rattachée à une sous-partie et/ou à une rubrique

En pareille hypothèse, comment prévoir quels sont le ou les descendant(s) direct(s) de la PARTIE ou de la SOUS-PARTIE pour adapter la requête suivante ?

Ex : une règle de la PARTIE 1 n'est pas contenue dans une sous-partie mais directement dans une rubrique. En pareille hypothèse, la requête suivant celle listant les parties serait une requête affichant directement les rubriques puisqu'il n'existe pas de sous-partie(s) associée(s).

Ex : la PARTIE 1 fait directement référence à une règle (donc cette dernière n'est contenue ni dans une sous-partie ni dans une rubrique). En pareille hypothèse, la requête suivant celle listant les parties serait une requête affichant directement les règles puisqu'il n'existe pas de sous-partie(s) ni de rubriques associée(s).

Je me suis relu mais j'ignore si j'ai clairement exprimé la teneur de l’objectif que j'ai en tête... En tout cas, merci de votre patience.

[fsmrel]

Bonsoir almoha,



D’accord. Pour chaque proposition faite à Raoul, par exemple au niveau Partie, il faut afficher les éléments du niveau qui en dépend.

Pour faciliter le travail à faire, j’enrichis la table PILE, dont le script de création devient le suivant :

-- --------------------------------------------------------------------
-- TABLE PILE - Simulation de la jointure récursive
-- --------------------------------------------------------------------
CREATE TABLE PILE 
(
        PileId               INT             NOT NULL AUTO_INCREMENT
      , Niveau               INT             NOT NULL
      , TypeParentId         INT             NOT NULL
      , TypeParentNom        VARCHAR(16)     NOT NULL
      , ElementParentId      INT             NOT NULL
      , ElementParentNom     VARCHAR(64)     NOT NULL
      , TypeElementId        INT             NOT NULL
      , TypeElementNom       VARCHAR(16)     NOT NULL
      , ElementId            INT             NOT NULL
      , ConcatenationNum     VARCHAR(64)     NOT NULL     
      , ElementNom           VARCHAR(64)     NOT NULL DEFAULT ''
    , CONSTRAINT PILE_PK PRIMARY KEY (PileId)
    , CONSTRAINT PILE_AK UNIQUE (ElementParentId, TypeElementId, ElementId)
) ;

L’idée étant essentiellement, pour chaque élément d’identifiant ElementId, de disposer de l’identifiant ElementParentId de son parent. Sont présentes les données suivantes : le nom de l’élément (ElementNom, anciennement ConcatenationTexte), son type (TypeElementId, TypeElementNom), l’identifiant du parent de l’élément (ElementParentId), son nom (ElementParentNom), son type (TypeParentId, TypeParentNom). Les attributs PileId, Niveau, ConcatenationNum sont ceux d’origine.

En outre, la table PILE contient désormais les caractéristiques des phrases, outre celles des éléments et des règles.

La clé alternative (contrainte d’unicité) PILE_AK est le triplet {ElementParentId, TypeElementId, ElementId}. Elle ne peut pas se réduire à la paire {ElementParentId, ElementId}, car par exemple, <1, 15> vaut pour l’élément 15 (une sous-partie) de la partie 1 ("Formes et accords du verbe") et pour une règle 15 qui serait directement rattachée à cette même partie 1. A fortiori, {ElementId} ne peut évidemment pas être clé candidate, puisque par exemple, <15> peut être une valeur d’élément (partie, sous-partie rubrique), de règle ou de phrase.

La procédure RecursonsJoyeusement a été enrichie en conséquence et son code devient le suivant :

CREATE PROCEDURE RecursonsJoyeusement
(
   IN Amorce BOOLEAN, ElementIdIn VARCHAR(10)
)

BEGIN

    DECLARE theNiveau INT ;
    DECLARE NiveauRegle INT ;
    DECLARE NiveauPhrase INT ; 
    DECLARE Kount INT ;
     
    SET theNiveau = (SELECT DISTINCT COALESCE(MAX(Niveau) + 1, 1) FROM PILE) ;
        
    IF Amorce = TRUE THEN
    
    -- ----------------------------------------
    -- Niveau 1 (racine)
    -- ----------------------------------------
    
        IF ElementIdIn > 0 THEN
            INSERT INTO PILE (ElementId, TypeElementId, TypeElementNom, Niveau, ElementParentId, ElementParentNom, TypeParentId, TypeParentNom, ConcatenationNum, ElementNom)
                SELECT ElementIdIn                                                                                    -- Identifiant de l'élément, passé par l'appelant
                     , x.TypeElementId                                                                                -- Identifiant du type de l'élément
                     , COALESCE((SELECT z.TypeElementlibelle                                                          -- Nom du type de l'élément
                                 FROM   COMPOSITION AS x JOIN ELEMENT AS y ON x.ElementComposantId = y.ElementId
                                                         JOIN TYPE_ELEMENT AS z ON y.TypeElementId = z.TypeElementId 
                                 WHERE  x.ElementComposantId = ElementIdIn), '/')  
                     , 1                                                                                              -- Niveau
                     , COALESCE((SELECT ElementComposeId                                                              -- Identifiant du parent de l'élément
                                 FROM   COMPOSITION 
                                 WHERE ElementComposantId = ElementIdIn), 0)
                     , COALESCE((SELECT y.ElementTexte                                                                -- Nom du parent de l'élément
                                 FROM   COMPOSITION AS x JOIN ELEMENT AS y ON x.ElementComposeId = y.ElementId                               
                                 WHERE  x.ElementComposantId = ElementIdIn), '/')
                     , COALESCE((SELECT y.TypeElementId                                                               -- Identifiant du type du parent de l'élément
                                 FROM   COMPOSITION AS x JOIN ELEMENT AS y ON x.ElementComposeId = y.ElementId 
                                 WHERE  x.ElementComposantId = ElementIdIn), 0)
                     , COALESCE((SELECT z.TypeElementlibelle                                                          -- Nom du type du parent de l'élément 
                                 FROM   COMPOSITION AS x JOIN ELEMENT AS y ON x.ElementComposeId = y.ElementId  
                                                         JOIN TYPE_ELEMENT AS z ON y.TypeElementId = z.TypeElementId
                                 WHERE  x.ElementComposantId = ElementIdIn), 0)
                     , RTRIM(CAST(ElementIdIn AS CHAR(4)))                                                            -- Début concaténation des id
                     , x.ElementTexte                                                                                 -- Nom de l'élément   
                FROM   ELEMENT AS x
                WHERE  x.ElementId = ElementIdIn  
            ;                          
        ELSE
            -- ---------------------------------------------
            -- ElementIdIn = 0 : on prend tout
            -- ---------------------------------------------
            INSERT INTO PILE (ElementId, TypeElementId, TypeElementNom, Niveau, ElementParentId, ElementParentNom, TypeParentId, TypeParentNom, ConcatenationNum, ElementNom)
                SELECT ElementId                             -- Identifiant de chaque élément racine (0 : on prend tout)
                     , TypeElementId                          -- Identifiant du type de l'élément (à savoir : 1)
                     , '/'                                    -- TypeElementNom
                     , 1                                       -- Niveau
                     , 0                                       -- Identifiant du parent de l'élément
                     , '/'                                     -- Nom du parent de l'élément
                     , 0                                       -- Identifiant du type du parent de l'élément
                     , '/'                                     -- Nom du type du parent de l'élément                 
                     , RTRIM(CAST(ElementId AS CHAR(4)))       -- Début concaténation des id
                     , ElementTexte                            -- Nom de l'élément           
                FROM   ELEMENT
                WHERE  TypeElementId = 1 ;
        END IF ;
        
     -- ------------------------------------------------------------------------------
    -- Niveau 2 (rattachement direct à un racine : sous-partie ou rubrique ou règle)
    -- -------------------------------------------------------------------------------
        
        INSERT INTO PILE (ElementId, TypeElementId, TypeElementNom, Niveau, ElementParentId, ElementParentNom, TypeParentId, TypeParentNom, ConcatenationNum, ElementNom) 
             SELECT x.ElementComposantId                                                                                    -- Identifiant de l'élément à récupérer
                  , y.TypeElementId                                                                                         -- Identifiant du type de l'élément
                  , u.TypeElementlibelle	                                                                                -- Nom du type de l'élément           
                  , 2                                                                                                       -- Niveau
                  , x.ElementComposeId                                                                                      -- Identifiant du parent de l'élément
                  , z.ElementTexte                                                                                          -- Nom du parent de l'élément
                  , z.TypeElementId                                                                                         -- Identifiant du type du parent de l'élément
                  , t.TypeElementlibelle                                                                                    -- Nom du type du parent de l'élément                 
                  , CONCAT(RTRIM(CAST(x.ElementComposeId AS CHAR(4))), ', ', RTRIM(CAST(x.ElementComposantId AS CHAR(4))))  -- Concaténation des id  
                  , y.ElementTexte                                                                                          -- Nom de l'élément                     
             FROM   COMPOSITION AS x JOIN ELEMENT AS y ON x.ElementComposantId = y.ElementId
                                     JOIN ELEMENT AS z ON x.ElementComposeId = z.ElementId
                                     JOIN TYPE_ELEMENT AS t ON z.TypeElementId = t.TypeElementId
                                     JOIN TYPE_ELEMENT AS u ON y.TypeElementId = t.TypeElementId   
             WHERE  x.ElementComposeId = y.ElementId 
             ;
             

        -- -------------------------------------------
        -- Et un tour de manège
        -- -------------------------------------------

        CALL RecursonsJoyeusement(FALSE, '') ;

    ELSE

    -- -----------------------------------------------
    -- Niveau > 2 (sous-partie ou rubrique ou règle)
    -- ----------------------------------------------
    
        SET Kount = (SELECT COUNT(*) 
                     FROM  (SELECT x.ElementComposantId, Niveau  
                            FROM   COMPOSITION AS x INNER JOIN PILE AS y 
                                   ON x.ElementComposeId = y.ElementId
                            WHERE  Niveau = TheNiveau - 1) as truc) ;
        IF Kount > 0 THEN
        
    -- ---------------------------------------------------
    -- Niveau > 2 (on n'a pas atteint le niveau règle)
    -- ---------------------------------------------------
        
            INSERT INTO PILE (ElementId, TypeElementId, TypeElementNom, Niveau, ElementParentId, ElementParentNom, TypeParentId, TypeParentNom, ConcatenationNum, ElementNom) 
                SELECT x.ElementComposantId                                                                 -- Identifiant de l'élément à récupérer
                     , z.TypeElementId                                                                      -- Identifiant du type de l'élément
                     , v.TypeElementlibelle                                                                 -- Nom du type de l'élément
                     , theNiveau                                                                            -- Niveau
                     , x.ElementComposeId                                                                   -- Identifiant du parent de l'élément
                     , t.ElementTexte                                                                       -- Nom du parent de l'élément                     
                     , t.TypeElementId                                                                      -- Identifiant du type du parent de l'élément
                     , u.TypeElementlibelle                                                                 -- Nom du type du parent de l'élément             
                     , CONCAT(y.ConcatenationNum, ', ', RTRIM(CAST(x.ElementComposantId AS CHAR(4))))       -- Concaténation des id
                     , z.ElementTexte                                                                       -- Nom de l'élément
                FROM   COMPOSITION AS x JOIN PILE AS y ON x.ElementComposeId = y.ElementId 
                                        JOIN ELEMENT AS z ON x.ElementComposantId = z.ElementId
                                        JOIN ELEMENT AS t ON x.ElementComposeId = t.ElementId
                                        JOIN TYPE_ELEMENT AS u ON t.TypeElementId = u.TypeElementId
                                        JOIN TYPE_ELEMENT AS v ON z.TypeElementId = v.TypeElementId
               WHERE  Niveau = TheNiveau - 1 ; 

            -- -------------------------------------------
            -- Et un tour de manège
            -- -------------------------------------------

            CALL RecursonsJoyeusement(FALSE, '') ;
            
        ELSE
        
    -- ----------------------------------------
    -- On a atteint le niveau règle
    -- ----------------------------------------
        
            SET NiveauRegle = (SELECT MAX(TypeElementId) + 1 FROM TYPE_ELEMENT) ;  -- Identifiant du type de la règle : id du plus grand type d'élément + 1      
            SET NiveauPhrase = NiveauRegle + 1 ;                                   -- Identifiant du type de la phrase 
         
            -- ----------------------------------------------
            -- Récupération des règles
            -- ----------------------------------------------

            INSERT INTO PILE (ElementId, TypeElementId, TypeElementNom, Niveau, ElementParentId, ElementParentNom, TypeParentId, TypeParentNom, ConcatenationNum, ElementNom)
                 SELECT x.RegleId                                                             -- Identifiant de la règle à récupérer
                     , NiveauRegle                                                            -- Identifiant du type de la règle : id du plus grand type d'élément + 1
                     , 'Règle'                                                                -- Nom du type de la règle : "Règle"
                     , theNiveau                                                              -- Niveau
                     , x.ElementId                                                            -- Identifiant du parent de la règle
                     , z.ElementTexte                                                         -- Nom du parent de la règle                 
                     , z.TypeElementId                                                        -- Identifiant du type du parent de la règle
                     , t.TypeElementlibelle                                                   -- Nom du type du parent de la règle                 
                     , CONCAT(y.ConcatenationNum, ', ', RTRIM(CAST(x.RegleId AS CHAR(6))))    -- Concaténation des id
                     , RegleTexte                                                             -- Nom de la règle
                 FROM  REGLE AS x JOIN PILE AS y ON x.ElementId = y.ElementId
                                  JOIN ELEMENT AS z ON x.ElementId = z.ElementId
                                  JOIN TYPE_ELEMENT AS t ON z.TypeElementId = t.TypeElementId 
            ;

            -- ----------------------------------------------
            -- Récupération des phrases
            -- ----------------------------------------------

             INSERT INTO PILE (ElementId, TypeElementId, TypeElementNom, Niveau, ElementParentId, ElementParentNom, TypeParentId, TypeParentNom, ConcatenationNum, ElementNom)
                SELECT x.PhraseId                                                              -- Identifiant de la phrase à récupérer
                     , NiveauPhrase                                                            -- Identifiant du type de la phrase : id du type "règle" + 1
                     , 'Phrase'                                                                -- Nom du type de la phrase : "Phrase"
                     , theNiveau + 1                                                           -- Niveau atteint
                     , x.RegleId                                                               -- Identifiant de la règle parente
                     , y.RegleTexte                                                            -- Nom de la règle parente 
                     , NiveauRegle                                                             -- Identifiant du type "Règle"
                     , 'Règle'                                                                 -- Nom du type "Règle"
                     , CONCAT(z.ConcatenationNum, ', ', RTRIM(CAST(x.PhraseId AS CHAR(6))))    -- Concaténation des id
                     , x.Texte                                                                 -- Nom de la phrase
                FROM   PHRASE AS x JOIN REGLE AS y ON x.RegleId = y.RegleId
                                   JOIN PILE As z ON x.RegleId = z.ElementId AND z.TypeElementId = NiveauRegle    
            ;
  
        END IF ;

    END IF;

END

GO

A noter que j’avais écrit :

Je rappelle que le but de la manoeuvre est de ne pas prendre une vessie pour une lanterne, c'est-à-dire confondre une valeur de RegleId avec une valeur d’ElementId, d’où la valeur 9999 que vous avez pu observer dans la table PILE (en supposant que la table ELEMENT ne contiendra pas 9999 lignes...

J’ai pu contourner le problème, donc (pour des raisons de clarté) ma remarque ne vaut plus.


Après l’appel à RecursonsJoyeusement, pour voir le contenu de la table PILE :

SELECT * FROM PILE
ORDER BY ElementParentId, ElementId ;

De même j’avais écrit :

Conséquence au niveau des requêtes : il suffit de retrancher 100000 pour retrouver la valeur de RegleId :

SELECT   
       CASE 
           WHEN x.ElementId < 100000 THEN x.ElementId
           ELSE x.ElementId - 100000
       END 
       AS ElementId
     , CASE TypeElementId 
            WHEN 1 THEN 'Partie' 
            WHEN 2 THEN 'Sous-partie'
            WHEN 3 THEN 'Rubrique' 
            ELSE 'Règle' 
       END
       AS TypeElement
     , ConcatenationNum       
     , CASE TypeElementId 
            WHEN 1 THEN ConcatenationTexte 
            WHEN 2 THEN CONCAT('----', ConcatenationTexte)
            WHEN 3 THEN CONCAT('--------', ConcatenationTexte) 
            ELSE CONCAT('------------', ConcatenationTexte) 
       END
       AS ConcatenationTexte       
FROM   PILE AS x LEFT JOIN ELEMENT AS y ON x.ElementId = y.ElementId
ORDER BY ConcatenationNum ;

Cette requête devient (en prenant les phrases en compte) :

SELECT x.elementId
     , x.TypeElementNom AS TypeElement
     , ConcatenationNum       
     , CASE x.TypeElementId 
           WHEN 1 THEN ElementNom 
           WHEN 2 THEN CONCAT('---- ', ElementNom)
           WHEN 3 THEN CONCAT('-------- ', ElementNom)
           WHEN 4 THEN CONCAT('------------ ', ElementNom)
        ELSE CONCAT('---------------- ', ElementNom) 
      END
       AS ElementNom       
FROM   PILE AS x LEFT JOIN ELEMENT AS y ON x.ElementId = y.ElementId
ORDER BY ConcatenationNum ;

Si on veut faire abstraction des phrases :

-- ------------------------------
-- Sans les phrases
-- ------------------------------
SELECT x.elementId
     , x.TypeElementNom AS TypeElement
     , ConcatenationNum       
     , CASE x.TypeElementId 
           WHEN 1 THEN ElementNom 
           WHEN 2 THEN CONCAT('---- ', ElementNom)
           WHEN 3 THEN CONCAT('-------- ', ElementNom)
           WHEN 4 THEN CONCAT('------------ ', ElementNom)
      END
       AS ElementNom       
FROM   PILE AS x LEFT JOIN ELEMENT AS y ON x.ElementId = y.ElementId
WHERE   x.TypeElementId < 5
ORDER BY ConcatenationNum ;

Venons-en requêtes (R1, R2, etc.)

Pour obtenir seulement les parties et leurs descendants directs, la requête ci-dessous devrait convenir, où l’attribut TypeParentId est à valoriser à 1 (type = "Partie") :

-- -----------------------------------------------------------------------
-- Requête R1
-- Sélections des parties et de leurs descendants directs
-- -----------------------------------------------------------------------

SELECT  TypeParentId, TypeParentNom, ElementParentId, ElementParentNom
      , TypeElementId, TypeElementNom, Elementid, ElementNom  
FROM    PILE
WHERE   TypeParentId = 1  
ORDER BY ElementParentId, TypeElementId -- Pour avoir dans l'ordre des types d'élément par partie
;

Cas de la requête R2 :

-- ---------------------------------------------------------------------------------
-- Requête R2
-- Raoul a choisi "Le verbe, ses formes" (TypeElementId = 2, ElementId = 5).
-- Pour avoir la descendance directe : TypeParentId = 2 et ElementParentId = 5.
-- ---------------------------------------------------------------------------------

SELECT  TypeParentId, TypeParentNom, ElementParentId, ElementParentNom
      , TypeElementId, TypeElementNom, Elementid, ElementNom 
FROM   PILE
WHERE  TypeParentId = 2 AND ElementParentId = 5 
ORDER BY ElementParentId, TypeElementId
;

Cas de la requête R3 :

-- --------------------------------------------------------------------------------------
-- Requête R3
-- Raoul a choisi "L'imparfait du subjonctif" (TypeElementId = 3 et ElementId = 9)
-- Pour avoir la descendance directe : TypeParentId = 3 et ElementParentId = 9.
-- --------------------------------------------------------------------------------------

SELECT  TypeParentId, TypeParentNom, ElementParentId, ElementParentNom
      , TypeElementId, TypeElementNom, Elementid, ElementNom 
FROM    PILE
WHERE   TypeParentId = 3 AND ElementParentId = 9 
ORDER BY ElementParentId, TypeElementId
;

Cas de la requête R4 :

-- --------------------------------------------------------------------------------------
-- Requête R4
-- Raoul a choisi "La 3e personne du singulier" (TypeElementId = 4 et ElementId = 6)
-- Pour avoir la descendance directe : TypeParentId = 4 et ElementParentId = 6.
-- --------------------------------------------------------------------------------------

SELECT  TypeParentId, TypeParentNom, ElementParentId, ElementParentNom
      , TypeElementId, TypeElementNom, Elementid, ElementNom 
FROM    PILE
WHERE   TypeParentId = 4 AND ElementParentId = 6
ORDER BY ElementParentId, TypeElementId
;

Les requêtes R2, R3, R4 ayant la même structure, on peut éventuellement en faire une requête unique, au sein d’une procédure :

CREATE PROCEDURE RaoulNavigue
(
   IN TypeParentChoisiId INT, ParentChoisiId INT
)

BEGIN

-- --------------------------------------------------------------------------------------
-- Requête Rxy
-- Raoul a choisi un sujet (TypeElementId = x et ElementId = y)
-- Pour avoir la descendance directe : TypeParentId = x et ElementParentId = y.
-- --------------------------------------------------------------------------------------

SELECT  TypeParentId, TypeParentNom, ElementParentId, ElementParentNom
      , TypeElementId, TypeElementNom, Elementid, ElementNom 
FROM    PILE
WHERE   TypeParentId = TypeParentChoisiId AND ElementParentId = ParentChoisiId 
ORDER BY ElementParentId, TypeElementId ; -- Pour avoir dans l'ordre des types d'élément par partie

END

GO
 

Procédure faisant l’objet d’appels ad-hoc :


Requête R2 :

SET @TypeParentId  = 2 ;
SET @ElementParentId = 5 ;  

CALL RaoulNavigue(@TypeParentId, @ElementParentId) ;

Requête R3 :

SET @TypeParentId  = 3 ;
SET @ElementParentId = 9 ;  

CALL RaoulNavigue(@TypeParentId, @ElementParentId) ;

Requête R4 :

SET @TypeParentId  = 4 ;
SET @ElementParentId = 6 ;   

CALL RaoulNavigue(@TypeParentId, @ElementParentId) ;

J’espère qu’il n’y a pas trop d’erreurs de copier/coller, vous me direz....

[almoha]

Bonjour fsmrel,

Merci pour votre réponse très complète. Je rappelle le jeu d'essai que j'utilise pour mes tests :

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
--
-- Contenu de la table COMPOSITION
--

INSERT INTO COMPOSITION  (ElementComposantId, ElementComposeId) VALUES
    (5,1), (6, 3), (7, 5), (8, 2), (9, 5), (10, 1) ;
;


--
-- Contenu de la table ELEMENT
--

INSERT INTO ELEMENT (ElementId, TypeElementId, ElementTexte) VALUES
    (1, 1, 'Formes et accords du verbe')
  , (2, 1, 'Formes et accords du nom, de l''adjectif et de l''adverbe')
  , (3, 1, 'Orthographe lexicale, signes graphiques et syntaxe')
  , (4, 1, 'Ces mots que l''on confond')
  , (5, 2, 'Le verbe : ses formes')
  , (6, 2, 'Syntaxe')
  , (7, 3, 'L''infinitif')
  , (8, 3, 'Les noms : leur féminin, leur pluriel')
  , (9, 3, 'L''imparfait du subjonctif')  
  , (10, 2, 'Le verbe, ses pièges')   
 ;


--
-- Contenu de la table TYPE_ELEMENT
--

INSERT INTO TYPE_ELEMENT (TypeElementId, TypeElementLibelle) VALUES 
    (1, 'Partie'), (2, 'Sous-partie'), (3, 'Rubrique') ;


--
-- Contenu de la table REGLE
--

INSERT INTO REGLE (RegleId, ElementId, RegleTexte, NbPhrases) VALUES
    (1, 8, 'Le pluriel des noms : cas généraux', 5)
  , (2, 6, 'est-ce que', 15) 
  , (3, 4, 'a ; à', 2)
  , (4, 4, 'pécheur ; pêcheur', 6)
  , (5, 4, 'or ; hors', 4)  
  , (6, 9, 'La 3e personne du singulier', 5)
  , (7, 9, 'La 3e personne du pluriel', 3)  
;


--
-- Contenu de la table PHRASE
--

INSERT INTO PHRASE (RegleId, PhraseId, DifficulteId, Position, Texte) VALUES
    (1, 1, 1, 5, 'Le petit chat est morts')
  , (1, 2, 2, 5, 'Il a un petit soucis')
  , (1, 3, 1, 3, 'Des yeux marrons')
  , (1, 4, 2, 4, 'Il est huit heure')
  , (1, 5, 3, 0, 'Huit cent deux kilos')
  , (3, 1, 2, 0, 'Je vais à Paris')
  , (3, 2, 2, 0, 'Pierre a faim')
  , (3, 3, 4, 0, 'Ce crayon est a moi')  
  , (4, 1, 1, 2, 'Il et fort et agile')
  , (4, 2, 1, 0, 'Elle est grande')
  , (4, 3, 4, 0, 'Il aime le cuissot de chevreuil')
  , (4, 4, 4, 5, 'Il aime aussi le cuissot de veau')
  , (4, 5, 3, 0, 'C''est une imbécillité')
  , (7, 1, 3, 0, 'Qu''il aimât cela !')
  , (7, 2, 3, 2, 'Qu''il n''aimat pas cela !')
;

Au moment de commencer les tests , je me suis posé les questions suivantes :

- dans mon esprit, la procédure RecursonsJoyeusement doit-être lancée une seule fois pour toute pour compléter la table PILE avec l'arborescence complète, laquelle table fera ensuite l'objet des requêtes SELECT. Est-ce bien cela ?

- dans l'hypothèse de l'arborescence complète, pouvez-vous me dire avec quels paramètres doit être lancée la procédure ?

Mes différents essais de lancement aboutissent au contenu suivant dans la table PILE :



Je ne suis pas sûr de partir sur les bonnes bases

[fsmrel]

Bonsoir almoha,

dans mon esprit, la procédure RecursonsJoyeusement doit-être lancée une seule fois pour toute pour compléter la table PILE avec l'arborescence complète, laquelle table fera ensuite l'objet des requêtes SELECT. Est-ce bien cela ?

Oui, à ceci près que « compléter » n’est pas le mot juste : la pile doit être réinitialisée, vidée, avant qu’on n’appelle la procédure, c'est-à-dire que l’appel doit être précédé d’une instruction DELETE FROM PILE, sinon le SGBD rouspétera dès qu’on tentera de créer des clés en double.

Avez-vous vérifié que MySQL n’a pas rouspété ?


J’ai refait un test avec votre jeu d’essai (et @Entree := 0) :

-- ---------------------------------------
-- Appel à la procédure récursive
-- ---------------------------------------

SET @@GLOBAL.max_sp_recursion_depth = 4;  -- Pour éviter les boucles infinies...
SET @@session.max_sp_recursion_depth = 4; 

-- -------------------------------------------------------------------------------
-- @Entree : L'élément d'identifiant ElementId = @Entree dans la table ELEMENT
-- -------------------------------------------------------------------------------

-- SET @Entree := 1 ;  -- L'élément d'identifiant ElementId = 1 dans la table ELEMENT (Formes et accords du verbe)
--   SET @Entree := 5 ;  -- L'élément d'identifiant ElementId = 5 dans la table ELEMENT (Le verbe : ses formes)
 -- SET @Entree := 9 ;  -- L'élément d'identifiant ElementId = 9 dans la table ELEMENT (l'imparfait du subjonctif)
   SET @Entree := 0 ;  -- La totale

SET @Amorce  := TRUE ;

 DELETE FROM PILE ;  

CALL RecursonsJoyeusement(@Amorce, @Entree);

-- ----------------------------
-- Au résultat
-- ----------------------------
SELECT * FROM PILE
ORDER BY ElementParentId, ElementId ;

=>






Affaire à suivre...



P.-S. Pour vérifier plus facilement le résultat, j’ai parfois modifié le texte des phrases, par exemple :

'rg1, ph1, le petit chat est morts'

Etc.

[almoha]

Bonsoir fsmrel,

Effectivement MySQL a rouspété. J'ai donc réinitialisé ma base test, en corrigeant notamment une coquille dans la table phrase :

Code :

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

1
2
3
  , (6, 1, 3, 0, 'Qu''il aimât cela !')
  , (6, 2, 3, 2, 'Qu''il n''aimat pas cela !')

et obtiens désormais le résultat escompté

Les requêtes successives interrogeant la table PILE permettent à l'utilisateur de choisir au final la règle qu'il souhaite travailler. C'est parfait. En l'état ce système fait abstraction de la difficulté des phrases auxquelles il va répondre. A votre avis, est-il possible d'intégrer ce critère de difficulté dans l'affichage de chaque proposition ? Je m'explique : avant de parcourir l'arborescence, l'application permettra à l'utilisateur, s'il le souhaite, de sélectionner la difficulté (par défaut, ce critère n'est pas actif). Par exemple, l'utilisateur choisit la difficulté "Assez facile". L'application ne devra lui proposer que des phrases de ce niveau. Pour cela, le parcours de l'arborescence devra être "filtré" en fonction de la difficulté choisie. L'affichage se fait dans la même logique que le système actuel, mais tient compte de la difficulté des phrases :

-> affichage de toutes les parties (toutes les phrases se rattachent à une partie, quelle que soit la difficulté) -> affichage du ou des élément(s) descendant(s) direct(s) (sous-partie, rubrique, règle) chapeautant des phrases uniquement du niveau choisi par l'utilisateur, en l'espèce d'une difficulté "Assez facile".

Pensez-vous qu'il est possible d'implémenter une telle fonctionnalité ?

[fsmrel]

Bonsoir almoha,

Les requêtes successives interrogeant la table PILE permettent à l'utilisateur de choisir au final la règle qu'il souhaite travailler.

Ouf !

Pensez-vous qu'il est possible d'implémenter une telle fonctionnalité ?

Hum... Vous utilisez le présent de l’indicatif, vous me mettez donc au pied du mur...

Essayons ceci :


1) On va commencer par modifier la structure de la table PILE, en ajoutant une colonne « Difficulte » :

CREATE TABLE PILE 
(
        PileId               INT             NOT NULL AUTO_INCREMENT
      , Niveau               INT             NOT NULL
      , TypeParentId         INT             NOT NULL
      , TypeParentNom        VARCHAR(16)     NOT NULL
      , ElementParentId      INT             NOT NULL
      , ElementParentNom     VARCHAR(64)     NOT NULL
      , TypeElementId        INT             NOT NULL
      , TypeElementNom       VARCHAR(16)     NOT NULL
      , ElementId            INT             NOT NULL
      , ConcatenationNum     VARCHAR(64)     NOT NULL     
      , ElementNom           VARCHAR(64)     NOT NULL DEFAULT ''
      , Difficulte           INT             NOT NULL DEFAULT 0
    , CONSTRAINT DIFFICULTE_PK PRIMARY KEY (DifficulteId)
    , CONSTRAINT DIFFICULTE_AK UNIQUE (DifficulteNiveau)
;

2) Bien que ça ne soit pas strictement indispensable, pour ne pas avoir à faire participer la clé primaire {DifficulteId} de la table DIFFICULTE aux requêtes (R1, R2, etc.), je préfère définir un attribut supplémentaire DifficulteNiveau donnant lieu à une clé alternative {DifficulteNiveau} exposée à l’application (vous pouvez donc en redéfinir les valeurs comme vous l’entendez) :

CREATE TABLE DIFFICULTE 
(
        DifficulteId         INT             NOT NULL
      , DifficulteNiveau     INT             NOT NULL DEFAULT 1	
      , DifficulteTexte      VARCHAR(32)     NOT NULL
    , CONSTRAINT DIFFICULTE_PK PRIMARY KEY (DifficulteId)
    , CONSTRAINT DIFFICULTE_AK UNIQUE (DifficulteNiveau)    
) ;

Les colonnes DifficulteId et DifficulteNiveau ont ici les mêmes valeurs, mais en vertu de ce qui précède, ça n’est pas une obligation :

INSERT INTO DIFFICULTE (DifficulteId, DifficulteNiveau, DifficulteTexte) VALUES
    (1, 1, 'Facile'), (2, 2, 'Assez facile'), (3, 3, 'Piège classique'), (4, 4, 'Niveau Mérimée') ;  

3) La procédure RecursonsJoyeusement est légèrement modifiée (éléments surchargés en rouge) :

CREATE PROCEDURE RecursonsJoyeusement
(
   IN Amorce BOOLEAN, ElementIdIn VARCHAR(10)
)

BEGIN

    DECLARE theNiveau INT ;
    DECLARE NiveauRegle INT ;
    DECLARE NiveauPhrase INT ; 
    DECLARE Kount INT ;
     
    SET theNiveau = (SELECT DISTINCT COALESCE(MAX(Niveau) + 1, 1) FROM PILE) ;
        
    IF Amorce = TRUE THEN
    
    -- ----------------------------------------
    -- Niveau 1 (racine)
    -- ----------------------------------------
    
        IF ElementIdIn > 0 THEN
            INSERT INTO PILE (ElementId, TypeElementId, TypeElementNom, Niveau, ElementParentId, ElementParentNom, TypeParentId, TypeParentNom, ConcatenationNum, ElementNom)
                SELECT ElementIdIn                                                                                    -- Identifiant de l'élément, passé par l'appelant
                     , x.TypeElementId                                                                                -- Identifiant du type de l'élément
                     , COALESCE((SELECT z.TypeElementlibelle                                                          -- Nom du type de l'élément
                                 FROM   COMPOSITION AS x JOIN ELEMENT AS y ON x.ElementComposantId = y.ElementId
                                                         JOIN TYPE_ELEMENT AS z ON y.TypeElementId = z.TypeElementId 
                                 WHERE  x.ElementComposantId = ElementIdIn), '/')  
                     , 1                                                                                              -- Niveau
                     , COALESCE((SELECT ElementComposeId                                                              -- Identifiant du parent de l'élément
                                 FROM   COMPOSITION 
                                 WHERE ElementComposantId = ElementIdIn), 0)
                     , COALESCE((SELECT y.ElementTexte                                                                -- Nom du parent de l'élément
                                 FROM   COMPOSITION AS x JOIN ELEMENT AS y ON x.ElementComposeId = y.ElementId                               
                                 WHERE  x.ElementComposantId = ElementIdIn), '/')
                     , COALESCE((SELECT y.TypeElementId                                                               -- Identifiant du type du parent de l'élément
                                 FROM   COMPOSITION AS x JOIN ELEMENT AS y ON x.ElementComposeId = y.ElementId 
                                 WHERE  x.ElementComposantId = ElementIdIn), 0)
                     , COALESCE((SELECT z.TypeElementlibelle                                                          -- Nom du type du parent de l'élément 
                                 FROM   COMPOSITION AS x JOIN ELEMENT AS y ON x.ElementComposeId = y.ElementId  
                                                         JOIN TYPE_ELEMENT AS z ON y.TypeElementId = z.TypeElementId
                                 WHERE  x.ElementComposantId = ElementIdIn), 0)
                     , RTRIM(CAST(ElementIdIn AS CHAR(4)))                                                            -- Début concaténation des id
                     , x.ElementTexte                                                                                 -- Nom de l'élément   
                FROM   ELEMENT AS x
                WHERE  x.ElementId = ElementIdIn  
            ;                          
        ELSE
            -- ---------------------------------------------
            -- ElementIdIn = 0 : on prend tout
            -- ---------------------------------------------
            INSERT INTO PILE (ElementId, TypeElementId, TypeElementNom, Niveau, ElementParentId, ElementParentNom, TypeParentId, TypeParentNom, ConcatenationNum, ElementNom)
                SELECT ElementId                             -- Identifiant de chaque élément racine (0 : on prend tout)
                     , TypeElementId                          -- Identifiant du type de l'élément (à savoir : 1)
                     , '/'                                    -- TypeElementNom
                     , 1                                       -- Niveau
                     , 0                                       -- Identifiant du parent de l'élément
                     , '/'                                     -- Nom du parent de l'élément
                     , 0                                       -- Identifiant du type du parent de l'élément
                     , '/'                                     -- Nom du type du parent de l'élément                 
                     , RTRIM(CAST(ElementId AS CHAR(4)))       -- Début concaténation des id
                     , ElementTexte                            -- Nom de l'élément           
                FROM   ELEMENT
                WHERE  TypeElementId = 1 ;
        END IF ;
        
     -- ------------------------------------------------------------------------------
    -- Niveau 2 (rattachement direct à un racine : sous-partie ou rubrique ou règle)
    -- -------------------------------------------------------------------------------
        
        INSERT INTO PILE (ElementId, TypeElementId, TypeElementNom, Niveau, ElementParentId, ElementParentNom, TypeParentId, TypeParentNom, ConcatenationNum, ElementNom) 
             SELECT x.ElementComposantId                                                                                    -- Identifiant de l'élément à récupérer
                  , y.TypeElementId                                                                                         -- Identifiant du type de l'élément
                  , u.TypeElementlibelle	                                                                                -- Nom du type de l'élément           
                  , 2                                                                                                       -- Niveau
                  , x.ElementComposeId                                                                                      -- Identifiant du parent de l'élément
                  , z.ElementTexte                                                                                          -- Nom du parent de l'élément
                  , z.TypeElementId                                                                                         -- Identifiant du type du parent de l'élément
                  , t.TypeElementlibelle                                                                                    -- Nom du type du parent de l'élément                 
                  , CONCAT(RTRIM(CAST(x.ElementComposeId AS CHAR(4))), ', ', RTRIM(CAST(x.ElementComposantId AS CHAR(4))))  -- Concaténation des id  
                  , y.ElementTexte                                                                                          -- Nom de l'élément                     
             FROM   COMPOSITION AS x JOIN ELEMENT AS y ON x.ElementComposantId = y.ElementId
                                     JOIN ELEMENT AS z ON x.ElementComposeId = z.ElementId
                                     JOIN TYPE_ELEMENT AS t ON z.TypeElementId = t.TypeElementId
                                     JOIN TYPE_ELEMENT AS u ON y.TypeElementId = t.TypeElementId   
             WHERE  x.ElementComposeId = y.ElementId 
             ;
             
        -- -------------------------------------------
        -- et un tour de manège
        -- -------------------------------------------

        CALL RecursonsJoyeusement(FALSE, '') ;

    ELSE

    -- -----------------------------------------------
    -- Niveau > 2 (sous-partie ou rubrique ou règle)
    -- ----------------------------------------------
    
        SET Kount = (SELECT COUNT(*) 
                     FROM  (SELECT x.ElementComposantId, Niveau  
                            FROM   COMPOSITION AS x INNER JOIN PILE AS y 
                                   ON x.ElementComposeId = y.ElementId
                            WHERE  Niveau = TheNiveau - 1) as truc) ;
        IF Kount > 0 THEN
        
    -- ---------------------------------------------------
    -- Niveau > 2 (on n'a pas atteint le niveau règle)
    -- ---------------------------------------------------
        
            INSERT INTO PILE (ElementId, TypeElementId, TypeElementNom, Niveau, ElementParentId, ElementParentNom, TypeParentId, TypeParentNom, ConcatenationNum, ElementNom) 
                SELECT x.ElementComposantId                                                                 -- Identifiant de l'élément à récupérer
                     , z.TypeElementId                                                                      -- Identifiant du type de l'élément
                     , v.TypeElementlibelle                                                                 -- Nom du type de l'élément
                     , theNiveau                                                                            -- Niveau
                     , x.ElementComposeId                                                                   -- Identifiant du parent de l'élément
                     , t.ElementTexte                                                                       -- Nom du parent de l'élément                     
                     , t.TypeElementId                                                                      -- Identifiant du type du parent de l'élément
                     , u.TypeElementlibelle                                                                 -- Nom du type du parent de l'élément             
                     , CONCAT(y.ConcatenationNum, ', ', RTRIM(CAST(x.ElementComposantId AS CHAR(4))))       -- Concaténation des id
                     , z.ElementTexte                                                                       -- Nom de l'élément
                 FROM   COMPOSITION AS x JOIN PILE AS y ON x.ElementComposeId = y.ElementId 
                                        JOIN ELEMENT AS z ON x.ElementComposantId = z.ElementId
                                        JOIN ELEMENT AS t ON x.ElementComposeId = t.ElementId
                                        JOIN TYPE_ELEMENT AS u ON t.TypeElementId = u.TypeElementId
                                        JOIN TYPE_ELEMENT AS v ON z.TypeElementId = v.TypeElementId
               WHERE  Niveau = TheNiveau - 1 ; 
             
            -- -------------------------------------------
            -- et un tour de manège
            -- -------------------------------------------

            CALL RecursonsJoyeusement(FALSE, '') ;
            
        ELSE
        
    -- ----------------------------------------
    -- On a atteint le niveau règle
    -- ----------------------------------------
        
            SET NiveauRegle = (SELECT MAX(TypeElementId) + 1 FROM TYPE_ELEMENT) ;  -- Identifiant du type de la règle : id du plus grand type d'élément + 1      
            SET NiveauPhrase = NiveauRegle + 1 ;                                   -- Identifiant du type de la phrase 
         
            -- ----------------------------------------------
            -- Récupération des règles
            -- ----------------------------------------------

            INSERT INTO PILE (ElementId, TypeElementId, TypeElementNom, Niveau, ElementParentId, ElementParentNom, TypeParentId, TypeParentNom, ConcatenationNum, ElementNom)
                 SELECT x.RegleId                                                             -- Identifiant de la règle à récupérer
                     , NiveauRegle                                                            -- Identifiant du type de la règle : id du plus grand type d'élément + 1
                     , 'Règle'                                                                -- Nom du type de la règle : "Règle"
                     , theNiveau                                                              -- Niveau
                     , x.ElementId                                                            -- Identifiant du parent de la règle
                     , z.ElementTexte                                                         -- Nom du parent de la règle                 
                     , z.TypeElementId                                                        -- Identifiant du type du parent de la règle
                     , t.TypeElementlibelle                                                   -- Nom du type du parent de la règle                 
                     , CONCAT(y.ConcatenationNum, ', ', RTRIM(CAST(x.RegleId AS CHAR(6))))    -- Concaténation des id
                     , RegleTexte                                                             -- Nom de la règle
                 FROM  REGLE AS x JOIN PILE AS y ON x.ElementId = y.ElementId
                                  JOIN ELEMENT AS z ON x.ElementId = z.ElementId
                                  JOIN TYPE_ELEMENT AS t ON z.TypeElementId = t.TypeElementId 
            ;

            -- ----------------------------------------------
            -- Récupération des phrases
            -- ----------------------------------------------

            INSERT INTO PILE (ElementId, TypeElementId, TypeElementNom, Niveau, ElementParentId, ElementParentNom, TypeParentId, TypeParentNom, ConcatenationNum, ElementNom, Difficulte)
                SELECT x.PhraseId                                                              -- Identifiant de la phrase à récupérer
                     , NiveauPhrase                                                            -- Identifiant du type de la phrase : id du type "règle" + 1
                     , 'Phrase'                                                                -- Nom du type de la phrase : "Phrase"
                     , theNiveau + 1                                                           -- Niveau atteint
                     , x.RegleId                                                               -- Identifiant de la règle parente
                     , y.RegleTexte                                                            -- Nom de la règle parente 
                     , NiveauRegle                                                             -- Identifiant du type "Règle"
                     , 'Règle'                                                                 -- Nom du type "Règle"
                     , CONCAT(z.ConcatenationNum, ', ', RTRIM(CAST(x.PhraseId AS CHAR(6))))    -- Concaténation des id
                     , x.Texte                                                                 -- Nom de la phrase
                     , t.DifficulteNiveau                                                      -- Niveau de difficulté de la phrase                     
                FROM   PHRASE AS x JOIN REGLE AS y ON x.RegleId = y.RegleId
                                   JOIN PILE As z ON x.RegleId = z.ElementId AND z.TypeElementId = NiveauRegle    
                                   JOIN DIFFICULTE AS t ON x.DifficulteId = t.DifficulteId 
           ;

        END IF ;

    END IF;

END

GO

4) La procédure RaoulNavigue est aménagée :

CREATE PROCEDURE RaoulNavigue
(
   IN TypeParentChoisiId INT, ParentChoisiId INT, DifficulteChoisie INT
)

BEGIN

-- --------------------------------------------------------------------------------------
-- Requête Rxy
-- Raoul a choisi un sujet (TypeElementId = x et ElementId = y)
-- Pour avoir la descendance directe : TypeParentId = x et ElementParentId = y.
-- Si le descendant est une phrase (TypeElementId = 5), et si Raoul veut 
-- seulement travailler sur un certain niveau de difficulté (DifficulteChoisie > 0),
-- alors on filtre sur ce niveau.  
-- --------------------------------------------------------------------------------------
 
IF DifficulteChoisie = 0 OR TypeParentChoisiId < 4 THEN
    SELECT  TypeParentId, TypeParentNom, ElementParentId, ElementParentNom
          , TypeElementId, TypeElementNom, Elementid, ElementNom 
    FROM    PILE
    WHERE   TypeParentId = TypeParentChoisiId AND ElementParentId = ParentChoisiId 
    ORDER BY ElementParentId, TypeElementId ; -- Pour avoir dans l'ordre des types d'élément par partie
ELSE
    SELECT  TypeParentId, TypeParentNom, ElementParentId, ElementParentNom
          , TypeElementId, TypeElementNom, Elementid, ElementNom 
    FROM    PILE
    WHERE   TypeParentId = TypeParentChoisiId AND ElementParentId = ParentChoisiId
      AND   Difficulte = DifficulteChoisie
      AND   TypeElementId = 5                      -- Element de type phrase = 5
    ORDER BY ElementParentId, TypeElementId ;
END IF ;

END

GO

5) Appel à la procédure RaoulNavigue (ceci concerne les requêtes R2, R3, R4) : on doit ajouter le paramètre concernant le niveau de difficulté.

Exemple de la requête R2, pour ne présenter que les phrases de niveau 2 :

SET @TypeParentId  = 2 ;          -- 'sous-partie'
SET @ElementParentId = 5 ;        -- 'le verbe, ses formes'
SET @Difficulte = 2 ;             -- 'niveau de difficulté (0 si pas de filtre)'

CALL RaoulNavigue(@TypeParentId, @ElementParentId, @Difficulte) ;

Si Raoul ne veut pas de filtre, @Difficulte doit être égal à 0.


Même principe pour les requêtes R3 et R4.


Croisons les doigts... A vous de secouer tout ça... ^^

[almoha]

Bonjour fsmrel,

Croisons les doigts... A vous de secouer tout ça... ^^

Excellent, l'aménagement proposé remplit parfaitement son office, merci

Le chapitre de l'arborescence étant selon moi terminé, permettez-moi d'en aborder un nouveau : la création de questionnaires !
Jusqu'à présent, on a permis à l'utilisateur de s'entraîner en répondant à des phrases correspondant à des règles qu'il a choisies de travailler. Maintenant, il s'agit pour ce dernier de tester son niveau dans les conditions d'un examen en répondant à un questionnaire comportant un nombre déterminé de phrases de difficulté variable.

Voici les règles que j'ai identifiées, sur lesquelles je sollicite votre savant éclairage :

- l'utilisateur choisit d'abord le nombre de phrases que comportera le questionnaire : 50, 100 ou 200 phrases;

- le système qui générera le questionnaire (par le truchement d'une procédure alimentant une table QUESTIONNAIRE ?) devra respecter une structure déterminée :

-> pour un même questionnaire, chaque phrase ne doit figurer qu'une seule fois;
-> le questionnaire devra comporter 35 % de phrases de niveau 1; 35 % de phrases de niveau 2; 30 % de phrases de niveau 3 (mon application n'aura que 3 niveaux de difficulté);
-> le questionnaire devra suivre la séquence suivante : d'abord les phrases de niveau 1, puis celles de niveau 2 et enfin celles de niveau 3;
-> hormis l’ordre des niveaux à respecter, l'ordre des phrases au sein de chaque niveau devra être aléatoire.

- chaque bonne réponse donnée par l'utilisateur lui rapporte 5 points; chaque mauvaise réponse lui rapporte 0 point;

- à la fin du questionnaire, le score obtenu par l'utilisateur lui est notifié (score sur 1000 s'il a répondu à 200 questions; score sur 500 s'il a répondu à 100 questions; score sur 250 s'il a répondu à 50 questions);

- dans le but d'améliorer son score, l'utilisateur peut décider de répondre à nouveau à un questionnaire auquel il a déjà répondu;

- chaque questionnaire devra être conservé dans la base de façon à pouvoir retrouver le ou les score(s) de l'utilisateur et la ou les date(s) à laquelle ou auxquelles il y a répondu.

A votre avis, quel est le meilleur moyen d'implémenter cette nouvelle fonctionnalité dans le MCD actuel ? Faut-il scinder ce dernier en deux modules, "apprentissage" d'une part et "examen" d'autre part ? Merci.