Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Discussion :

Gestion de blindtests; comment faire ?


Sujet :

Schéma

  1. #41
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    septembre 2006
    Messages
    6 788
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Essonne (Île de France)

    Informations professionnelles :
    Activité : Spécialiste en bases de données
    Secteur : Conseil

    Informations forums :
    Inscription : septembre 2006
    Messages : 6 788
    Points : 25 036
    Points
    25 036
    Billets dans le blog
    16
    Par défaut
    Citation Envoyé par Mike888 Voir le message
    si je résume, voici les corrections à apporter par rapport à tout ce qui a été posté dans les messages #18 à #21
    La structure de la table BLINDTEST est effectivement à modifier comme vous l’avez fait :

    CREATE TABLE BLINDTEST 
    (
            blindtestId      INT  IDENTITY      NOT NULL
          , Numero           VARCHAR(8)         NOT NULL
          , animateurId      INT                NOT NULL 
        , CONSTRAINT BLINDTEST_PK PRIMARY KEY (blindtestId)
        , CONSTRAINT BLINDTEST_AK UNIQUE (Numero)
        , CONSTRAINT BLINDTEST_ANIMATEUR_FK FOREIGN KEY (animateurId)
              REFERENCES ANIMATEUR (animateurId)
    ) ;
    
    Mais les vues et les triggers sont aussi à mettre à niveau car blindtestCode y est présent, donc à remplacer par Numero.

    A cette occasion, je joins le code où la paire {artiste, titre} devient clé alternative, tandis que disparaît l’attribut extraitCode.

    Création de la table EXTRAIT

    
    DROP TABLE IF EXISTS EXTRAIT ;
    
    GO
    
    CREATE TABLE EXTRAIT 
    (
            extraitId        INT  IDENTITY      NOT NULL 
          , artiste          VARCHAR(48)        NOT NULL
          , titre            VARCHAR(48)        NOT NULL
        , CONSTRAINT EXTRAIT_PK PRIMARY KEY (extraitId)
        , CONSTRAINT EXTRAIT_AK UNIQUE (artiste, titre)
    ) ;
    

    Création des vues PARTICIPER_V et COMPOSER_V

    DROP VIEW IF EXISTS PARTICIPER_V ;
    
    GO
    
    CREATE VIEW PARTICIPER_V (pseudo, Numero)
    AS
        SELECT  pseudo, Numero
        FROM    PARTICIPER as x 
           JOIN BLINDEUR as y ON x.joueurId = y.blindeurId  -- pour avoir le pseudo
           JOIN BLINDTEST as z ON x.blindtestId = z.blindtestId  -- pour avoir le Numero
    ;
    GO
    
    DROP VIEW IF EXISTS COMPOSER_V ;
    
    GO
    
    CREATE VIEW COMPOSER_V (Numero, artiste, titre)
    AS
        SELECT  Numero, artiste, titre
        FROM    COMPOSER as x 
           JOIN BLINDTEST as y ON x.blindtestId = y.blindtestId  -- pour avoir le Numero
           JOIN EXTRAIT as z ON x.extraitId = z.extraitId  -- pour avoir l'artiste et le titre
    ;
    GO


    Création du trigger PARTICIPER_TR sur la vue PARTICIPER_V :

    DROP TRIGGER IF EXISTS PARTICIPER_TR ;
    GO
     CREATE TRIGGER PARTICIPER_TR ON PARTICIPER_V INSTEAD OF INSERT, UPDATE
    AS
    
    DECLARE @Engueulade AS VARCHAR(512) ;
    DECLARE @n as INT ;
    DECLARE @inserted TABLE
    (
        joueurId     INT
      , blindtestId  INT
    ) ;
    DECLARE @deleted TABLE
    (
        joueurId     INT
      , blindtestId  INT
    ) ;
    ---- pour debug ---- select '' as INSERTED, * from INSERTED  
    ---- pour debug ---- select '' as DELETED, * from DELETED  
    
    INSERT INTO @inserted (joueurId, blindtestId)
        SELECT DISTINCT y.blindeurId, z.blindtestId
        FROM   INSERTED AS x
          JOIN BLINDEUR AS y ON x.pseudo = y.pseudo
          JOIN BLINDTEST AS z ON x.Numero = z.Numero
    ;
    ---- pour debug ---- select '' as '@inserted', * from @inserted 
    
    -------------------------------------------------------
    -- comptage des viols de la contrainte d'exclusion
    -------------------------------------------------------
    SET @n = 
        (SELECT COUNT(*) 
         FROM   @inserted AS x
                  JOIN BLINDTEST AS y  
                       ON x.blindtestId = y.blindtestId
                       AND x.joueurId = y.animateurId)
    
    ---- pour debug ---- select @n as '@n' 
    
    ----------------------------------------------------
    -- Si on constate une tentative de viol de la
    -- contrainte d'exclusion, on rouspète 
    -- et on s'en va.
    ----------------------------------------------------
    IF @n > 0
        BEGIN
            SET @Engueulade = 'Un blinder ne peut pas participer au blindtest qu''il anime !'
            SELECT @Engueulade AS Engueulons  --, * FROM INSERTED
            RAISERROR (@Engueulade, 0,1)  -- state = 0 pour les tests
     ----        RAISERROR (@Engueulade, 16,1)  -- state = 16 pour bloquer  
            RETURN  
        END
    ----------------------------------------------------
    -- On n'a pas constaté d'anomalie : 
    -- on met la table PARTICIPER à jour.
    ----------------------------------------------------
    
    ---------------------------------------------------
    -- S’il y a quelque chose dans DELETED
    -- alors c’est qu’un UPDATE est en cours,
    -- on commence par faire le ménage dans COMPOSER.
    ---------------------------------------------------
    
    IF (select COUNT(*) FROM DELETED) > 0
        BEGIN 
            INSERT INTO @deleted (joueurId, blindtestId)
                SELECT DISTINCT y.blindeurId, z.blindtestId
                FROM   DELETED AS x
                  JOIN BLINDEUR AS y ON x.pseudo = y.pseudo
                  JOIN BLINDTEST AS z ON x.Numero = z.Numero
            ;
    ---- pour debug ---- select '' as '@deleted' , * from @deleted
    ---- pour debug ---- select '' as 'participer avant delete', * from PARTICIPER
    
            DELETE FROM PARTICIPER
                WHERE joueurId IN (SELECT joueurId FROM @deleted)
                  AND blindtestId IN (SELECT blindtestId FROM @deleted) 
    
    ---- pour debug ---- select '' as 'participer après delete', * from PARTICIPER
        END
    
    ---------------------------------
    -- On est en INSERT ou UPDATE
    ---------------------------------
    
    INSERT INTO PARTICIPER (joueurId, blindtestId)
        SELECT joueurId, blindtestId 
        FROM @inserted
    ;
    ---- pour debug ----  select '' as 'participer après insert', * from PARTICIPER
    
    GO
    

    Création du trigger COMPOSER_TR sur la vue COMPOSER_V :

    DROP TRIGGER IF EXISTS COMPOSER_TR ;
    GO
    
    CREATE TRIGGER COMPOSER_TR ON COMPOSER_V INSTEAD OF INSERT, UPDATE
    AS
    
    DECLARE @theTrigger as VARCHAR(64) = 'COMPOSER_TR - '
    DECLARE @Engueulade AS VARCHAR(512) ;
    DECLARE @nbDeleted as INT ;
    DECLARE @NumeroKount as INT ;
    DECLARE @extraitCodeKount as INT ;
    
    DECLARE @t1 as TABLE 
    (
        Numero varchar(48)
      , artiste varchar(48)
      , titre varchar(48)
    )
    
    DECLARE @t2 as TABLE 
    (
        Numero varchar(48)
    )
    
    DECLARE @t3 as TABLE 
    (
        Numero varchar(48)
      , artiste varchar(48)
      , titre varchar(48)
    )
    
    -----------------------------------------------------------
    -- tables temporaires pour les clés primaires
    -----------------------------------------------------------
    
    DECLARE @inserted as TABLE
    (
        blindtestId  INT
      , extraitId    INT
    ) ;
    DECLARE @deleted as TABLE
    (
        blindtestId  INT
      , extraitId    INT
    ) ;
    ---- pour debug ---- select '' as INSERTED, * from INSERTED  
    ---- pour debug ---- select '' as DELETED, * from DELETED  
    
    -------------------------------------------------------
    -- S’il y a quelque chose dans DELETED
    -- alors c’est qu’on effectue un UPDATE,
    -- on commence par effectuer quelques contrôles.
    -------------------------------------------------------
    
    SET @nbDeleted = (SELECT COUNT(*) FROM DELETED)
    
    IF @nbDeleted > 0
        BEGIN
            ---------------------------------------------------
            -- si un Numero est candidat au
            -- remplacement d'un autre Numero,
            -- il doit être connu dans la table BLINDTEST
            ---------------------------------------------------
    
            ---------------------------------------
            -- Les codes blintests inconnus
            ---------------------------------------
    
            INSERT INTO @t1 (Numero, artiste, titre)
                SELECT DISTINCT Numero, artiste, titre 
                FROM   INSERTED AS x
                WHERE  NOT EXISTS (SELECT Numero
                                   FROM   BLINDTEST AS y
                                   WHERE  x.Numero = y.Numero)
            ;
            INSERT INTO @t2 (Numero) 
                SELECT DISTINCT Numero FROM @t1 ;
    
            SET @NumeroKount = (SELECT COUNT(*) FROM @t2)
    
            IF @NumeroKount > 0
                BEGIN
                    IF @NumeroKount = 1
                        BEGIN
                            SET @Engueulade 
                              = @theTrigger + 'update composition - '
                              + 'blindtest nouveau : inconnu.' 
                        END
                    ELSE
                        BEGIN
                           SET @Engueulade 
                             = @theTrigger + 'update composition - '
                             + 'Il y a '
                             + CAST(@NumeroKount as VARCHAR(5)) 
                             + ' blindtests inconnus.'
                        END
                    SELECT @Engueulade AS Engueulons
                         , Numero as 'Numero (nouveau mais inconnu)'
                         , artiste, titre 
                    FROM @t1
                    RAISERROR (@Engueulade, 0,1) -- state = 0 pour les tests
        ----        RAISERROR (@Engueulade, 16,1)  -- state = 16 pour bloquer  
                    RETURN  
              END
            ---------------------------------------------------
            -- si une paire  {artiste, titre} est candidate au
            -- remplacement d'une autre paire,
            -- elle doit être connue dans la table EXTRAIT
            ---------------------------------------------------
    
            -----------------------------------------
            -- Les paires {artiste, titre} inconnues
            -----------------------------------------
    
            INSERT INTO @t1 (Numero, artiste, titre)
                SELECT DISTINCT Numero, artiste, titre 
                FROM   INSERTED AS x
                WHERE  NOT EXISTS (SELECT artiste, titre
                                   FROM   EXTRAIT AS y
                                   WHERE  x.artiste = y.artiste 
                                      AND x.titre = y.titre)
            ;
            INSERT INTO @t3 (Numero, artiste, titre) 
                SELECT DISTINCT Numero, artiste, titre FROM @t1 ;
    
            SET @extraitCodeKount = (SELECT COUNT(*) FROM @t3)
    
            IF @extraitCodeKount > 0
                BEGIN
                    IF @extraitCodeKount = 1
                        BEGIN
                            SET @Engueulade 
                              = @theTrigger + 'update composition - '
                              + 'Paire nouvelle {artiste, titre} : inconnue.' 
                        END
                    ELSE
                        BEGIN
                           SET @Engueulade 
                             = @theTrigger + 'update composition - '
                             + 'Il y a ' + cast(@NumeroKount as varchar(5)) 
                             + ' paire nouvelle {artiste, titre}  inconnues.'
                        END
                    SELECT @Engueulade AS Engueulons
     --                    , artiste + ' ' + titre as 'paire (nouvelle mais inconnue)'
                           , Numero, artiste, titre FROM @t3
                    RAISERROR (@Engueulade, 0,1) -- state = 0 pour les tests
        ----        RAISERROR (@Engueulade, 16,1)  -- state = 16 pour bloquer  
                    RETURN  
              END
    
        END
    
    ------------------------------------------------------
    -- traduction des clés alternatives (naturelles) en
    -- clés primaires (artificielles)
    ------------------------------------------------------
    INSERT INTO @inserted (blindtestId, extraitId)
        SELECT DISTINCT y.blindtestId, z.extraitId
        FROM   INSERTED AS x
          JOIN BLINDTEST AS y ON x.Numero = y.Numero
          JOIN EXTRAIT AS z ON x.artiste = z.artiste
                           AND x.titre = z.titre
    ;
    ---- pour debug ---- select '' as '@inserted', * from @inserted 
    
    ---------------------------------------------------
    -- S’il y a quelque chose dans DELETED
    -- alors c’est qu’on effectue un UPDATE,
    -- on commence par faire le ménage dans COMPOSER.
    ---------------------------------------------------
    IF (SELECT COUNT(*) FROM DELETED) > 0
        BEGIN
            INSERT INTO @deleted (blindtestId, extraitId)
                SELECT DISTINCT y.blindtestId, z.extraitId
                FROM   DELETED AS x
                  JOIN BLINDTEST AS y ON x.Numero = y.Numero
                  JOIN EXTRAIT AS z ON x.artiste = z.artiste
                                   AND x.titre = z.titre
            ;
    ---- pour debug ---- select '' as '@deleted' , * from @deleted
    
            DELETE FROM COMPOSER
                WHERE blindtestId IN (SELECT blindtestId FROM @deleted)
                  AND extraitId IN (SELECT extraitId FROM @deleted)  
           ;
    
    ---- pour debug ---- select '' as 'composer après delete', * from COMPOSER
    
        END
    
    ---------------------------------
    -- On est en INSERT ou UPDATE :
    -- on charge.
    ---------------------------------
    INSERT INTO COMPOSER (blindtestId, extraitId)
        SELECT blindtestId, extraitId 
        FROM @inserted
    ;
    
    ---- pour debug ---- select '' as 'composer après insert', * from COMPOSER
    
    GO
    Un bout de jeu d’essai

    DELETE FROM TROUVER ;
    DELETE FROM PARTICIPER ;
    DELETE FROM COMPOSER ;
    DELETE FROM EXTRAIT ;
    DELETE FROM BLINDTEST ;
    DELETE FROM BLINDEUR ;
    
    GO
    
    ----------------------------------------------
    -- blindeurs
    ----------------------------------------------
    INSERT INTO BLINDEUR (pseudo) VALUES
        ('fernand'), ('raoul'), ('paul'), ('mado')
      , ('folace'), ('jean'), ('antoine'), ('patricia') 
    ;
    SELECT pseudo from BLINDEUR
    ;
    ----------------------------------------
    -- animateurs
    ----------------------------------------
    INSERT INTO ANIMATEUR (animateurId) 
        SELECT blindeurId FROM BLINDEUR WHERE pseudo IN ('fernand', 'raoul', 'paul', 'mado')
    ;
    SELECT pseudo AS Animateur 
    FROM ANIMATEUR AS x
    JOIN BLINDEUR AS y on x.animateurId = y.blindeurId
    ;
    ----------------------------------------
    -- joueurs
    ----------------------------------------
    INSERT INTO JOUEUR (joueurId)
        SELECT blindeurId 
        FROM BLINDEUR 
        WHERE pseudo IN ('fernand', 'mado', 'folace', 'jean', 'antoine', 'patricia')
    ;
    ------------------------------
    -- pour voir
    ------------------------------
    SELECT pseudo AS Joueur 
    FROM JOUEUR AS x
    JOIN BLINDEUR AS y on x.joueurId = y.blindeurId
    ;
    ------------------------------------------------
    -- Les blindtests
    ------------------------------------------------
    INSERT INTO BLINDTEST (Numero, animateurId)
        SELECT 'blindt01', blindeurId
        FROM    BLINDEUR
        WHERE pseudo = 'fernand'    
    ;
    INSERT INTO BLINDTEST (Numero, animateurId)
        SELECT 'blindt02', blindeurId
        FROM    BLINDEUR
        WHERE pseudo = 'mado'    
    ;  
    INSERT INTO BLINDTEST (Numero, animateurId)
        SELECT 'blindt03', blindeurId
        FROM    BLINDEUR
        WHERE pseudo = 'fernand'    
    ;  
    INSERT INTO BLINDTEST (Numero, animateurId)
        SELECT 'blindt04', blindeurId
        FROM    BLINDEUR
        WHERE pseudo = 'fernand'    
    ;
    INSERT INTO BLINDTEST (Numero, animateurId)
        SELECT 'blindt05', blindeurId
        FROM    BLINDEUR
        WHERE pseudo = 'fernand'    
    ;
    
    ------------------------------
    -- pour voir
    ------------------------------  
    SELECT '' as blindtest, Numero, pseudo
    FROM   BLINDTEST AS x
      JOIN BLINDEUR AS y ON x.animateurId = y.blindeurId
    ;
    ---------------------------------------------------
    -- participation des joueurs aux blindtests
    ---------------------------------------------------
    INSERT INTO PARTICIPER_V (pseudo, Numero) VALUES 
        ('folace', 'blindt01') 
      , ('folace', 'blindt02') 
      , ('jean', 'blindt01') 
    ;
    -- Le blindeur fernand anime le blindtest blindt01 
    -- et ne pourra donc y participer, le trigger veillant au grain 
    INSERT INTO PARTICIPER_V (pseudo, Numero) VALUES 
        ('fernand', 'blindt01')
    ;
    -- Le blindeur mado a le droit de participer
    -- au blindtest blindt01,
    -- mais mado anime le  blindtest blindt02 
    -- et ne pourra donc y participer, le trigger veillant au grain. 
    INSERT INTO PARTICIPER_V (pseudo, Numero) VALUES 
        ('mado', 'blindt01')
    ;
    UPDATE PARTICIPER_V
        SET Numero = 'blindt02' WHERE pseudo = 'mado'
    ;
    -- pour jean, pas de problème
    update PARTICIPER_V
        SET Numero = 'blindt02' WHERE pseudo = 'jean'
    ;
    ------------------------------
    -- pour voir
    ------------------------------  
    SELECT '' as participer, Numero, pseudo 
    FROM PARTICIPER_V
    ;
    ---------------------------------
    -- des extraits
    ---------------------------------
    
    --delete from EXTRAIT  ;
    
    INSERT INTO EXTRAIT (artiste, titre) VALUES
        ('Animals', 'House of rising sun'), ('Brassens', 'Old Leon'), ('Corneille', 'Marquise')
      , ('Ferré', 'Le scaphandrier'), ('Brel', 'Le plat pays')
      , ('G. Brassens', 'Le vieux Léon')
    ;
    ------------------------------
    -- pour voir
    ------------------------------  
    SELECT artiste, titre , *
    FROM EXTRAIT ;
    ;
    -------------------------------------------
    -- compositions blindtests-extraits
    --------------------------------------------
    INSERT INTO COMPOSER_V (Numero, artiste, titre) VALUES
          ('blindt01','Brassens', 'Old Leon')
        , ('blindt01', 'Brel', 'Le plat pays')
        , ('blindt02', 'Brassens', 'Old Leon')
        , ('blindt02', 'Corneille', 'Marquise')
       ;
    ------------------------------
    -- pour voir
    ------------------------------  
    SELECT Numero, artiste, titre
    FROM   COMPOSER_V 
    ; 
    ----------------------------------------
    -- vue COMPOSER_V : updates
    ----------------------------------------
    
    -- tentative anomalie
    
    UPDATE COMPOSER_V 
       SET Numero  = '$lindt03'
       WHERE Numero = 'blindt01' 
       AND artiste = 'Brel' AND titre = 'Le plat pays'
    ;
    
    -- tentative anomalie
    
    UPDATE COMPOSER_V 
       SET Numero  = '$$' + right(Numero,6)
    ;
    
    -- correct
    
    UPDATE COMPOSER_V 
       SET Numero  = 'blindt03'
       WHERE artiste = 'Brel' AND titre = 'Le plat pays'
    ;
    
    -- anomalie
    
    UPDATE COMPOSER_V 
       set artiste  = 'Brassens', titre =  'Marquise'
         , Numero  = 'blindt04'
       WHERE artiste  = 'Corneille' AND titre =  'Marquise'
         AND Numero = 'blindt02'   
    ;
    
    -- correct
    
    INSERT INTO EXTRAIT (artiste, titre) VALUES
        ('Brassens', 'Marquise')
    ;
    UPDATE COMPOSER_V 
       set artiste  = 'Brassens', titre =  'Marquise'
         , Numero  = 'blindt05'
       WHERE artiste  = 'Corneille' AND titre =  'Marquise'
         AND Numero = 'blindt02'   
    ;
    
    -- anomalie
    --
    update composer_v set Numero = 'xx' where Numero = 'blindt01'
    
    ------------------------------
    -- pour voir
    ------------------------------  
    SELECT '' as 'COMPOSER après update'
         , Numero, x.artiste, x.titre
    FROM   COMPOSER_V as x
      JOIN EXTRAIT as y ON x.artiste = y.artiste 
                       AND x.titre = y.titre 
    ;
    
    

     
    Faites simple, mais pas plus simple ! (A. Einstein)
    E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    Je ne réponds pas aux questions techniques par MP. Les forums sont là pout ça.
    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench

  2. #42
    Membre régulier
    Profil pro
    Inscrit en
    décembre 2003
    Messages
    430
    Détails du profil
    Informations personnelles :
    Localisation : Belgique

    Informations forums :
    Inscription : décembre 2003
    Messages : 430
    Points : 97
    Points
    97
    Par défaut
    Merci beaucoup pour tout ce travail fourni !

  3. #43
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    septembre 2006
    Messages
    6 788
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Essonne (Île de France)

    Informations professionnelles :
    Activité : Spécialiste en bases de données
    Secteur : Conseil

    Informations forums :
    Inscription : septembre 2006
    Messages : 6 788
    Points : 25 036
    Points
    25 036
    Billets dans le blog
    16
    Par défaut
    Bonsoir Mike,


    Citation Envoyé par Mike888 Voir le message
    je vais avoir des milliers d'extraits à encoder et j'aimerais que ça soit le plus automatisé possible.
    Vous devriez donc en principe vous en sortir au moyen de la paire {artiste, titre}. Concernant la mise en relation de ces milliers d’extraits avec les blindtests, prévoyez-vous de faire les « branchements » un par un ?


     
    Faites simple, mais pas plus simple ! (A. Einstein)
    E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    Je ne réponds pas aux questions techniques par MP. Les forums sont là pout ça.
    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench

  4. #44
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    septembre 2006
    Messages
    6 788
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Essonne (Île de France)

    Informations professionnelles :
    Activité : Spécialiste en bases de données
    Secteur : Conseil

    Informations forums :
    Inscription : septembre 2006
    Messages : 6 788
    Points : 25 036
    Points
    25 036
    Billets dans le blog
    16
    Par défaut
    En complément :

    Les vues sont bien utiles pour les requêtes un peu copieuses, notamment en jointures...

    Ainsi, il peut être opportun d’en créer une pour la table TROUVER :

    DROP VIEW IF EXISTS TROUVER_V ;
    
    GO
    
    CREATE VIEW TROUVER_V (pseudo, Numero, artiste, titre)
    AS
        SELECT  pseudo, Numero, artiste, titre
        FROM    TROUVER as x 
           JOIN BLINDEUR as y ON x.joueurId = y.blindeurId  
           JOIN COMPOSER as z ON x.blindtestId = z.blindtestId 
                             AND x.extraitId = z.extraitId 
           JOIN BLINDTEST as t ON z.blindtestId = t.blindtestId
           JOIN EXTRAIT as u on z.extraitId = u.extraitId
    ;
    GO
    
    Sans cete vue, on arrive encore à maîtriser l’insertion de lignes dans la table TROUVER :

    -----------------------------------------------
    -- quelques participations supplémentaires
    -----------------------------------------------
    INSERT INTO PARTICIPER_V (pseudo, Numero) VALUES 
        ('folace', 'blindt05')
      , ('jean', 'blindt05')
    ;
    ------------------------------------------------------
    -- une ligne supplémentaire pour la table COMPOSER
    ------------------------------------------------------
    INSERT INTO COMPOSER_V (Numero, artiste, titre) VALUES
          ('blindt01','Animals', 'House of rising sun')
    ;
    -----------------------------------------------
    -- ils ont trouvé
    -----------------------------------------------
    
    INSERT INTO TROUVER (joueurId, blindtestId, extraitId) VALUES
          ((SELECT blindeurId FROM BLINDEUR WHERE pseudo = 'folace')
         , (SELECT blindtestId FROM BLINDTEST WHERE Numero = 'blindt05')
         , (SELECT extraitId FROM EXTRAIT WHERE artiste = 'Brassens' 
                                            AND titre = 'Marquise'))
    ;
    INSERT INTO TROUVER (joueurId, blindtestId, extraitId) VALUES
          ((SELECT blindeurId FROM BLINDEUR WHERE pseudo = 'jean')
         , (SELECT blindtestId FROM BLINDTEST WHERE Numero = 'blindt02')
         , (SELECT extraitId FROM EXTRAIT WHERE artiste = 'Brassens' 
                                            AND titre = 'Old Leon'))
    ;
    INSERT INTO TROUVER (joueurId, blindtestId, extraitId) VALUES
          ((SELECT blindeurId FROM BLINDEUR WHERE pseudo = 'jean')
         , (SELECT blindtestId FROM BLINDTEST WHERE Numero = 'blindt05')
         , (SELECT extraitId FROM EXTRAIT WHERE artiste = 'Brassens' 
                                            AND titre = 'Marquise'))
    ;
    INSERT INTO TROUVER (joueurId, blindtestId, extraitId) VALUES
          ((SELECT blindeurId FROM BLINDEUR WHERE pseudo = 'mado')
         , (SELECT blindtestId FROM BLINDTEST WHERE Numero = 'blindt01')
         , (SELECT extraitId FROM EXTRAIT WHERE artiste = 'Animals' 
                                            AND titre = 'House of rising sun'))
    ;
    
    Avec la vue, l’accès aux données est simplifié :

    SELECT * FROM TROUVER_V
    
    =>

    pseudo    Numero      artiste      titre
    mado      blindt01    Animals      House of rising sun
    folace    blindt05    Brassens     Marquise
    jean      blindt02    Brassens     Old Leon
    jean      blindt05    Brassens     Marquise
    

    Cela dit, si vous préférez que les inserts dans la table passent par la vue TROUVER_V, donc sous forme simplifiée :

    INSERT INTO TROUVER_V (pseudo, Numero, artiste, titre) VALUES
          ('mado', 'blindt01', 'Animals', 'House of rising sun')
        , ('folace', 'blindt05', 'Brassens', 'Marquise')
        , ('jean', 'blindt02', 'Brassens', 'Old Leon')
        , ('jean', 'blindt05', 'Brassens', 'Marquise')
    ;
    
    Alors pas de problème, on codera le trigger qui va bien


     
    Faites simple, mais pas plus simple ! (A. Einstein)
    E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    Je ne réponds pas aux questions techniques par MP. Les forums sont là pout ça.
    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench

  5. #45
    Membre régulier
    Profil pro
    Inscrit en
    décembre 2003
    Messages
    430
    Détails du profil
    Informations personnelles :
    Localisation : Belgique

    Informations forums :
    Inscription : décembre 2003
    Messages : 430
    Points : 97
    Points
    97
    Par défaut
    Citation Envoyé par fsmrel Voir le message
    Bonsoir Mike,
    Vous devriez donc en principe vous en sortir au moyen de la paire {artiste, titre}. Concernant la mise en relation de ces milliers d’extraits avec les blindtests, prévoyez-vous de faire les « branchements » un par un ?
    Qu'entendez-vous par branchement un par un ?

    Par rapport à votre dernier message, passer par la nouvelle vue me semble plus logique, non ?

  6. #46
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    septembre 2006
    Messages
    6 788
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Essonne (Île de France)

    Informations professionnelles :
    Activité : Spécialiste en bases de données
    Secteur : Conseil

    Informations forums :
    Inscription : septembre 2006
    Messages : 6 788
    Points : 25 036
    Points
    25 036
    Billets dans le blog
    16
    Par défaut
    Bonsoir Mike,


    Citation Envoyé par Mike888 Voir le message
    Qu'entendez-vous par branchement un par un ?
    Une fois que vous avez chargé les centaines (?) de blindtests et les milliers d’extraits, il s’agit de procéder à l’opération les associant (la table COMPOSER a pour vocation de contenir les associations résultant de cette opération) : branchement est ici synonyme d’association. Quand je dis « un par un », cela veut dire : pour chaque blindtest lui associer des extraits, opération qui peut être longue manuellement parlant, mais peut-être avez-vous un moyen de simplifier ça efficacement ?


    Citation Envoyé par Mike888 Voir le message
    Par rapport à votre dernier message, passer par la nouvelle vue me semble plus logique, non ?
    Certes, mais il faut prendre le temps de développer le trigger nécessaire pour effectuer les inserts ! Ça n’est pas bien compliqué (il est dans le style de COMPOSER__TR), mais il faut tester...


    En passant, pourriez-vous donner une définition du blindtest tel que vous le percevez ?

     
    Faites simple, mais pas plus simple ! (A. Einstein)
    E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    Je ne réponds pas aux questions techniques par MP. Les forums sont là pout ça.
    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench

  7. #47
    Membre régulier
    Profil pro
    Inscrit en
    décembre 2003
    Messages
    430
    Détails du profil
    Informations personnelles :
    Localisation : Belgique

    Informations forums :
    Inscription : décembre 2003
    Messages : 430
    Points : 97
    Points
    97
    Par défaut
    Citation Envoyé par fsmrel Voir le message
    Bonsoir Mike,
    Une fois que vous avez chargé les centaines (?) de blindtests et les milliers d’extraits, il s’agit de procéder à l’opération les associant (la table COMPOSER a pour vocation de contenir les associations résultant de cette opération) : branchement est ici synonyme d’association. Quand je dis « un par un », cela veut dire : pour chaque blindtest lui associer des extraits, opération qui peut être longue manuellement parlant, mais peut-être avez-vous un moyen de simplifier ça efficacement ?
    Pour le moment, je n'ai pas encore beaucoup réfléchi à la manière d'automatiser l'encodage... Ca va être un travail titanesque vu le nombre d'extraits qu'il y a !

    Citation Envoyé par fsmrel Voir le message
    Certes, mais il faut prendre le temps de développer le trigger nécessaire pour effectuer les inserts ! Ça n’est pas bien compliqué (il est dans le style de COMPOSER__TR), mais il faut tester...En passant, pourriez-vous donner une définition du blindtest tel que vous le percevez ?
    Alors, sur un autre forum, j'ai créé un topic Blindtest en mai/juin 2005. Chacun peut venir apporter un blindtest et y faire participer les autres ! Un BT (blindtest) se compose en moyenne de 25 extraits, et le nombre de participants varie fort mais en moyenne, il y en a 5/6.

    Voici à quoi ressemble un récapitulatif en fin de jeu :

    Nom : 123.png
Affichages : 48
Taille : 44,0 Ko

    Donc, un Blindtest, c'est un jeu de connaissance musicale.
    Le programme et la DB sur lesquels on bosse pour le moment vont me servir (et aux autres aussi) à accéder à une multitude d'infos, une fois tout l'encodage terminé ! Bien sur, ça va me prendre des mois, j'en suis bien conscient...

  8. #48
    Membre régulier
    Profil pro
    Inscrit en
    décembre 2003
    Messages
    430
    Détails du profil
    Informations personnelles :
    Localisation : Belgique

    Informations forums :
    Inscription : décembre 2003
    Messages : 430
    Points : 97
    Points
    97
    Par défaut
    Bonjour Fsmrel,

    Comprenez-vous mieux le principe du blindtest à présent ?
    Sinon, je continue à expliquer

  9. #49
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    septembre 2006
    Messages
    6 788
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Essonne (Île de France)

    Informations professionnelles :
    Activité : Spécialiste en bases de données
    Secteur : Conseil

    Informations forums :
    Inscription : septembre 2006
    Messages : 6 788
    Points : 25 036
    Points
    25 036
    Billets dans le blog
    16
    Par défaut
    Bonsoir Mike,


    Citation Envoyé par Mike888 Voir le message
    Ca va être un travail titanesque vu le nombre d'extraits qu'il y a !
    Travail de titan en effet !


    Citation Envoyé par Mike888 Voir le message
    Comprenez-vous mieux le principe du blindtest à présent ?
    Sinon, je continue à expliquer
    A 10 000 mètres d’altitude, je commence à voir à peu près


    Cela dit, je suis en train d’aménager les triggers pour que vous puissiez disposer des données des lignes concernées en cas d’erreur (pseudo inconnu, etc.), le but est d’afficher des messages plus explicites que ceux de SQL Server qui en reste assez souvent au minimum syndical (signification seulement de l’erreur, par exemple dans le cas des clés étrangères), ce qui est irritant dans le cas des inserts et updates en masse, quand on veut savoir quels pseudos, numéros de blindtests, titres et artistes sont la cause des erreurs.

    Le code des triggers devient assez verbeux, mais n’est pas rendu compliqué pour autant. Je joindrai au fur et à mesure les nouvelles versions. Commençons par le commencement, avec le trigger PARTICIPER_TR qui s’enrichît...


    Trigger PARTICIPER_TR

    DROP TRIGGER IF EXISTS PARTICIPER_TR ;
    GO
    
     CREATE TRIGGER PARTICIPER_TR ON PARTICIPER_V INSTEAD OF INSERT, UPDATE
    AS
    
    DECLARE @tableCible as VARCHAR(48) = 'PARTICIPER'
    DECLARE @theTrigger as VARCHAR(64) = @tableCible + '_TR - '
    DECLARE @EngueuladeInit AS VARCHAR(128) = @theTrigger + 'insert/update ' + @tableCible + ' - ';
    DECLARE @Engueulade AS VARCHAR(512) ;
    DECLARE @nbInserted as INT ;
    DECLARE @nbDeleted as INT ;
    DECLARE @NumeroKount as INT ;
    DECLARE @pseudoKount as INT ;
    DECLARE @blindtestConnu as CHAR(1) = 'Y'
    DECLARE @pseudoConnu as CHAR(1) = 'Y'
    DECLARE @pseudoJoueur as CHAR(1) = 'Y'
    DECLARE @exclusionRespectee as CHAR(1) = 'Y'
    
    DECLARE @nbExclusions as INT ;
    
    -----------------------------------------------------------
    -- tables temporaires pour les clés primaires
    -----------------------------------------------------------
    
    DECLARE @inserted TABLE
    (
        joueurId     INT
      , blindtestId  INT
    ) ;
    DECLARE @deleted TABLE
    (
        joueurId     INT
      , blindtestId  INT
    ) ;
    
    
    DECLARE @t1 as TABLE 
    (
        pseudo varchar(48)
      , Numero varchar(48)
    )
    
    DECLARE @t2 as TABLE 
    (
        Numero varchar(48)
    )
    
    DECLARE @t3 as TABLE 
    (
        pseudo varchar(48)
    )
    
    --------------------------------------------------
    -- c'est parti
    --------------------------------------------------
    
    ---- pour debug ---- select '' as INSERTED, * from INSERTED  
    ---- pour debug ---- select '' as DELETED, * from DELETED  
    
    ---------------------------------------------
    -- Si INSERTED est vide, on dégage
    ---------------------------------------------
    
    SET @nbInserted = (SELECT COUNT(*) FROM INSERTED) ;
    
    IF @nbInserted = 0
        BEGIN
            SET @Engueulade = @EngueuladeInit 
                            + 'La requête propose un contenu vide, ' 
                            + 'la table ' + @tableCible 
                            + ' ne sera donc pas mise à jour.' 
            RAISERROR (@Engueulade, 0,1) -- state = 0 pour les tests
    ----    RAISERROR (@Engueulade, 16,1)  -- state = 16 pour bloquer  
            RETURN  
        END
    
    ------------------------------------------------------
    -- traduction des clés alternatives (naturelles) en
    -- clés primaires (artificielles)
    ------------------------------------------------------
    
    INSERT INTO @inserted (joueurId, blindtestId)
        SELECT DISTINCT y.blindeurId, z.blindtestId
        FROM   INSERTED AS x
          JOIN BLINDEUR AS y ON x.pseudo = y.pseudo
          JOIN BLINDTEST AS z ON x.Numero = z.Numero
    ;
    ---- pour debug ---- select '' as '@inserted', * from @inserted 
    
    ----------------------------------------
    -- les numéros des blindtests doivent
    -- exister
    ----------------------------------------
    
        INSERT INTO @t1 (pseudo, Numero)
            SELECT DISTINCT pseudo, Numero 
        FROM   INSERTED AS x
        WHERE  NOT EXISTS (SELECT Numero
                           FROM   BLINDTEST AS y
                           WHERE  x.Numero = y.Numero)
    ;
    ------------------------------------------
    -- Nombre exacts de numéros inconnus
    ------------------------------------------
    
    INSERT INTO @t2 (Numero) 
        SELECT DISTINCT Numero FROM @t1 ;
    
    SET @NumeroKount = (SELECT COUNT(*) FROM @t2)
    
    IF @NumeroKount > 0
        BEGIN
        -----------------------------------------
        -- Les numéros effectivement inconnus
        -----------------------------------------
            IF @NumeroKount = 1
                BEGIN
                    SET @Engueulade 
                      = @EngueuladeInit
                      + 'blindtest proposé : inconnu.' 
                END
            ELSE
                BEGIN
                    SET @Engueulade 
                      = @EngueuladeInit
                      + 'Il y a '
                      + CAST(@NumeroKount as VARCHAR(5)) 
                      + ' blindtests proposés mais inconnus.'
                END
            SELECT @Engueulade AS Engueulons
                 , pseudo 
                 , Numero as 'Numero proposé inconnu'
            FROM @t1
            SET @blindtestConnu = 'N'
            RAISERROR (@Engueulade, 0,1) -- state = 0 pour les tests
    ----    RAISERROR (@Engueulade, 16,1)  -- state = 16 pour bloquer  
    ----------------------        RETURN
      
        END
    
    ----------------------------------------
    -- les pseudos des joueurs doivent
    -- exister
    ----------------------------------------
    
    DELETE FROM @t1 ;
    
    INSERT INTO @t1 (pseudo, Numero)
        SELECT DISTINCT pseudo, Numero 
        FROM   INSERTED AS x
        WHERE  NOT EXISTS (SELECT pseudo
                           FROM   BLINDEUR AS y
                           WHERE  x.pseudo = y.pseudo)
    ;
    ------------------------------------------
    -- Nombre exacts de pseudos inconnus
    ------------------------------------------
    
    INSERT INTO @t3 (pseudo) 
        SELECT DISTINCT pseudo FROM @t1 ;
    
    SET @pseudoKount = (SELECT COUNT(*) FROM @t3)
    
    IF @pseudoKount > 0
        BEGIN
        -----------------------------------------
        -- Les pseudos effectivement inconnus
        -----------------------------------------
            IF @pseudoKount = 1
                BEGIN
                    SET @Engueulade 
                      = @EngueuladeInit
                      + 'pseudo proposé : inconnu.' 
                END
            ELSE
                BEGIN
                    SET @Engueulade 
                      = @EngueuladeInit
                      + 'Il y a '
                      + CAST(@pseudoKount as VARCHAR(5)) 
                      + ' pseudos proposés mais inconnus.'
                END
            SELECT @Engueulade AS Engueulons
                 , pseudo as 'pseudo proposé inconnu'
                 , Numero
            FROM @t1
            SET @pseudoConnu = 'N'
            RAISERROR (@Engueulade, 0,1) -- state = 0 pour les tests
    ----    RAISERROR (@Engueulade, 16,1)  -- state = 16 pour bloquer  
    ----------------------        RETURN
      
        END
    
    ------------------------------------------
    -- les blindeurs doivent être des joueurs
    ------------------------------------------
    
    DELETE FROM @t1 ;
    DELETE FROM @t3 ;
    
    IF @pseudoConnu = 'Y' AND @blindtestConnu = 'y'
        BEGIN
            INSERT INTO @t1 (pseudo, Numero)
                SELECT DISTINCT pseudo, Numero 
                FROM   INSERTED AS x
                WHERE  NOT EXISTS (SELECT ''
                                   FROM   BLINDEUR AS y                            
                                     JOIN JOUEUR AS z ON y.blindeurId = z.joueurId 
                                   WHERE  x.pseudo = y.pseudo) 
            ;
            INSERT INTO @t3 (pseudo) 
                SELECT DISTINCT pseudo FROM @t1 ;
    
            SET @pseudoKount = (SELECT COUNT(*) FROM @t3)
    
            IF @pseudoKount > 0
                -----------------------------------------------------
                -- Les blindeurs visant à participer aux blindtests 
                -- alors qu'ils ne sont pas déclarés comme joueurs
                -----------------------------------------------------
                BEGIN
                    IF @pseudoKount = 1
                        BEGIN
                            SET @Engueulade 
                              = @EngueuladeInit
                              + 'le pseudo proposé n''est pas celui d'' un joueur' 
                        END
                    ELSE
                        BEGIN
                           SET @Engueulade 
                             = @EngueuladeInit
                             + 'Il y a '
                             + CAST(@pseudoKount as VARCHAR(5)) 
                             + ' pseudos proposés qui ne sont pas ceux de joueurs.'
                        END
                    SELECT @Engueulade AS Engueulons
                         , pseudo as 'blindeur non joueur'
                         , Numero
                    FROM @t1
                    SET @pseudoJoueur = 'N' ; 
                    RAISERROR (@Engueulade, 0,1) -- state = 0 pour les tests
    ----            RAISERROR (@Engueulade, 16,1)  -- state = 16 pour bloquer 
    -------------------                RETURN  
                END
        END
    
    -------------------------------------------------------
    -- comptage des viols de la contrainte d'exclusion :
    -- l'animateur d'un blindtest ne peut pas y participer
    -------------------------------------------------------
    
    IF @blindtestConnu = 'Y' AND @pseudoConnu = 'Y' 
      AND @pseudoJoueur = 'Y'
    
        BEGIN   
            DELETE FROM @t1 ;
    
            INSERT INTO @t1 (pseudo, Numero)
                SELECT DISTINCT pseudo, Numero 
                FROM   @inserted AS x
                  JOIN BLINDTEST AS y ON x.blindtestId = y.blindtestId 
                   AND x.joueurId = y.animateurId
                  JOIN BLINDEUR AS z on x.joueurId = z.blindeurId                                  
            ;
    ---- pour debug ---- select '' as '@inserted', * from @inserted
    ---- pour debug ---- select '' as '@t1', * from @t1
    
            INSERT INTO @t3 (pseudo) 
                SELECT DISTINCT pseudo FROM @t1 ;
    
            SET @nbExclusions = (SELECT COUNT(*) FROM @t3)
    
            ---- pour debug ---- select @nbExclusions as '@nbExclusions' 
    
            ----------------------------------------------------
            -- Si on constate une tentative de viol de la
            -- contrainte d'exclusion, on rouspète 
            -- et on s'en va.
            ----------------------------------------------------
    
            IF @nbExclusions > 0
                BEGIN
                        
                    IF @nbExclusions = 1
                        BEGIN  
                            SET @Engueulade 
                              = @EngueuladeInit
                              + 'le pseudo proposé est celui de ' 
                              + 'l''animateur du blindtest.' 
                        END  
                    ELSE
                        BEGIN  
                            SET @Engueulade 
                              = @EngueuladeInit
                              + 'Il y a '
                              + CAST(@nbExclusions as VARCHAR(5)) 
                              + ' pseudos proposés qui sont ceux '
                              + 'd''animateurs des blindtests.'
                        END
      
                    SELECT @Engueulade AS Engueulons
                         , pseudo as 'pseudo déjà animateur du blindtest'
                         , Numero
                    FROM @t1
    
                    SET @exclusionRespectee = 'N'
                    RAISERROR (@Engueulade, 0,1) -- state = 0 pour les tests
            ----        RAISERROR (@Engueulade, 16,1)  -- state = 16 pour bloquer  
    ----------------------        RETURN
                END  
        END 
    
    ----------------------------------------------------
    -- On n'a pas constaté d'anomalie : 
    -- on met la table PARTICIPER à jour.
    ----------------------------------------------------
    
    IF @blindtestConnu = 'Y' AND @pseudoConnu = 'Y' 
      AND @pseudoJoueur = 'Y' AND @exclusionRespectee = 'Y'
    
        BEGIN
     
            ---------------------------------------------------
            -- S’il y a quelque chose dans DELETED
            -- alors c’est qu’un UPDATE est en cours.
            ---------------------------------------------------
    
            IF (SELECT COUNT(*) FROM DELETED) > 0
                BEGIN 
                     INSERT INTO @deleted (joueurId, blindtestId)
                        SELECT DISTINCT y.blindeurId, z.blindtestId
                        FROM   DELETED AS x
                          JOIN BLINDEUR AS y ON x.pseudo = y.pseudo
                          JOIN BLINDTEST AS z ON x.Numero = z.Numero
    
                    ---- pour debug ----  select '' as '@inserted avant update', * from @inserted
                    ---- pour debug ----  select '' as '@deleted avant update', * from @deleted
                    ---- pour debug ----  select '' as 'participer avant update', * from PARTICIPER
    
                     UPDATE PARTICIPER 
                        SET  
                            joueurId =  
                            (SELECT DISTINCT joueurId
                             from   @inserted)
                          ,  blindtestId = 
                            (SELECT DISTINCT blindtestId 
                             from   @inserted)
                           WHERE joueurId IN (SELECT DISTINCT joueurId FROM @deleted) 
                             AND blindtestId IN (SELECT DISTINCT blindtestId FROM @deleted)
                            
                ---- pour debug ----  select '' as 'participer après update', * from PARTICIPER
    
                END
            ELSE
                BEGIN
                ---------------------------------
                -- On est en INSERT
                ---------------------------------
    
                ---- pour debug ---- select '' as 'participer avant insert', * from PARTICIPER
    
                    INSERT INTO PARTICIPER (joueurId, blindtestId)
                        SELECT joueurId, blindtestId 
                        FROM @inserted
                    ;
                ---- pour debug ---- select '' as 'participer après insert', * from PARTICIPER
                END
        END
    
    GO
    

    Un début de jeu d’essai :

    ----------------------------------------------
    -- blindeurs
    ----------------------------------------------
    INSERT INTO BLINDEUR (pseudo) VALUES
        ('fernand'), ('raoul'), ('paul'), ('mado')
      , ('folace'), ('jean'), ('antoine'), ('patricia') 
    ;
    ----------------------------------------
    -- joueurs
    ----------------------------------------
    INSERT INTO JOUEUR (joueurId)
        SELECT blindeurId 
        FROM BLINDEUR 
        WHERE pseudo IN ('fernand', 'mado', 'folace'
                       , 'jean', 'antoine', 'patricia')
    ;
    ------------------------------------------------
    -- Les blindtests
    ------------------------------------------------
    INSERT INTO BLINDTEST (Numero, animateurId)
        SELECT 'blindt01', blindeurId
        FROM    BLINDEUR
        WHERE pseudo = 'fernand'    
    ;
    INSERT INTO BLINDTEST (Numero, animateurId)
        SELECT 'blindt02', blindeurId
        FROM    BLINDEUR
        WHERE pseudo = 'mado'    
    ;  
    INSERT INTO BLINDTEST (Numero, animateurId)
        SELECT 'blindt03', blindeurId
        FROM    BLINDEUR
        WHERE pseudo = 'fernand'    
    ;  
    INSERT INTO BLINDTEST (Numero, animateurId)
        SELECT 'blindt04', blindeurId
        FROM    BLINDEUR
        WHERE pseudo = 'fernand'    
    ;
    INSERT INTO BLINDTEST (Numero, animateurId)
        SELECT 'blindt05', blindeurId
        FROM    BLINDEUR
        WHERE pseudo = 'fernand'    
    ;
    -------------------------------------
    -- joueurs participant aux blindtests
    -------------------------------------
    INSERT INTO PARTICIPER_V (pseudo, Numero) VALUES 
        ('folace', 'blindt01') 
      , ('folace', 'blindt02') 
      , ('folace', 'blindt05') 
      , ('jean', 'blindt02') 
      , ('jean', 'blindt05') 
      , ('mado', 'blindt01') 
    ;
    

    Dans l’état actuel du trigger, on ne peut modifier par UPDATE qu’une seule ligne de la table. A moins qu’un champion de SQL passe par ici et donne la solution miracle de l’update ensembliste, Je mettrai probablement en oeuvre un curseur pour updater cacahuète par cacahuète...


    En prime : le trigger PARTICIPER_TR_DELETE qui permet de supprimer des lignes de la table PARTICIPER au moyen des clés naturelles.

    Trigger PARTICIPER_TR_DELETE

    DROP TRIGGER IF EXISTS PARTICIPER_TR_DELETE ;
    
    GO
    
    CREATE TRIGGER PARTICIPER_TR_DELETE ON PARTICIPER_V INSTEAD OF DELETE
    AS
    
    DECLARE @tableCible as VARCHAR(48) = 'PARTICIPER'
    DECLARE @theTrigger as VARCHAR(64) = @tableCible + '_TR_DELETE - '
    DECLARE @EngueuladeInit AS VARCHAR(128) = @theTrigger + 'delete dans ' + @tableCible + ' - ';
    DECLARE @Engueulade AS VARCHAR(512) ;
    DECLARE @nbDeleted as INT ;
    
    -----------------------------------------------------------
    -- tables temporaires pour les clés primaires
    -----------------------------------------------------------
    
    DECLARE @deleted as TABLE
    (
        joueurId       INT
      , blindtestId    INT
    ) ;
    
    -----------------------------------------------------
    -- c'est parti !
    -----------------------------------------------------
    
    ---- pour debug ---- select '' as 'DELETED (on delete)', * from DELETED  
    
    ---------------------------------------------
    -- Si DELETED est vide, on dégage
    ---------------------------------------------
    
    SET @nbdeleted = (SELECT COUNT(*) FROM DELETED) ;
    
    IF @nbdeleted = 0
        BEGIN
            SET @Engueulade = @EngueuladeInit 
                            + 'La requête propose un contenu vide, ' 
                            + 'la table ' + @tableCible 
                            + ' ne sera donc pas mise à jour.' 
            RAISERROR (@Engueulade, 0,1) -- state = 0 pour les tests
    ----    RAISERROR (@Engueulade, 16,1)  -- state = 16 pour bloquer  
            RETURN  
     
        END
    
    ------------------------------------------------------
    -- de DELETED vers @deleted :
    -- Traduction des clés alternatives (naturelles) en
    -- clés primaires (artificielles)
    ------------------------------------------------------
    
    INSERT INTO @deleted (joueurId, blindtestId)
        SELECT DISTINCT  y.blindeurId, z.blindtestId
        FROM   DELETED AS x
          JOIN BLINDEUR AS y ON x.pseudo = y.pseudo
          JOIN BLINDTEST AS z ON x.Numero = z.Numero
    ;
    
    ---- pour debug ---- select '' as '@deleted' , * from @deleted
    
    ------------------------------------------------------
    -- Mise à jour de la table PARTICIPER.
    ------------------------------------------------------
    
    DELETE FROM PARTICIPER
           WHERE  joueurId IN (SELECT joueurId FROM @deleted)
              AND blindtestId IN (SELECT blindtestId FROM @deleted)
    ;
    
    ---- pour debug ---- select '' as 'PARTICIPER après delete', * from PARTICIPER
    
    GO
    

    Exemple :

    -------------------------------------
    -- folace ne participe plus à rien
    -------------------------------------
    DELETE FROM PARTICIPER_v  where pseudo = 'folace';
    

    A noter que j’ai opté pour la suppression et la modification en cascade des participations (ON DELETE CASCADE, ON UPDATE CASCADE) :


    CREATE TABLE TROUVER 
    (
            joueurId         INT                NOT NULL
          , blindtestId      INT                NOT NULL
          , extraitId        INT                NOT NULL 
        , CONSTRAINT TROUVER_PK PRIMARY KEY (joueurId, blindtestId, extraitId) 
        , CONSTRAINT TROUVER_PARTICIPER_FK FOREIGN KEY (joueurId, blindtestId)
              REFERENCES PARTICIPER (joueurId,blindtestId)    ON DELETE CASCADE 
                                                              ON UPDATE CASCADE 
        , CONSTRAINT TROUVER_COMPOSER_FK FOREIGN KEY (blindtestId, extraitId)
              REFERENCES COMPOSER (blindtestId, extraitId) ON DELETE NO ACTION
    ) ;
    
    Autrement dit, si une participation est supprimée ou modifiée (changement par exemple de joueur pour tel blindtest), c’est répercuté dans la table TROUVER (propagation des suppressions/modifications). Si vous préférez au contraire que l’opération soit rejetée, il suffira de supprimer ON DELETE CASCADE et ON UPDATE CASCADE.


    Si vous avez besoin de précisions...


    Les triggers associés aux vues COMPOSER_V et TROUVER_V vont suivre.


     
    Faites simple, mais pas plus simple ! (A. Einstein)
    E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    Je ne réponds pas aux questions techniques par MP. Les forums sont là pout ça.
    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench

  10. #50
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    septembre 2006
    Messages
    6 788
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Essonne (Île de France)

    Informations professionnelles :
    Activité : Spécialiste en bases de données
    Secteur : Conseil

    Informations forums :
    Inscription : septembre 2006
    Messages : 6 788
    Points : 25 036
    Points
    25 036
    Billets dans le blog
    16
    Par défaut
    Bonsoir Mike,


    Triggers, suite, ravalement de façade :

    Vue COMPOSER_V : trigger COMPOSER_TR_DELETE pour supprimer des lignes dans COMPOSER :

    DROP TRIGGER IF EXISTS COMPOSER_TR_DELETE ;
    GO
    
    CREATE TRIGGER COMPOSER_TR_DELETE ON COMPOSER_V INSTEAD OF DELETE
    AS
    
    DECLARE @tableCible as VARCHAR(48) = 'COMPOSER'
    DECLARE @theTrigger as VARCHAR(64) = @tableCible + '_TR_DELETE - '
    DECLARE @Engueulade AS VARCHAR(512) ;
    DECLARE @nbDeleted as INT ;
    
    -----------------------------------------------------------
    -- tables temporaires pour les clés primaires
    -----------------------------------------------------------
    
    DECLARE @deleted as TABLE
    (
        blindtestId  INT
      , extraitId    INT
    ) ;
    
    -----------------------------------------------------
    -- c'est parti !
    -----------------------------------------------------
    
    ---- pour debug ---- 
    select '' as 'DELETED (on delete)', * from DELETED  
    
    ---------------------------------------------
    -- Si DELETED est vide, on dégage
    ---------------------------------------------
    
    SET @nbdeleted = (SELECT COUNT(*) FROM DELETED) ;
    
    IF @nbdeleted = 0
        BEGIN
            SET @Engueulade = @theTrigger + 'La requête propose un contenu vide, ' 
                            + 'la table ' + @tableCible 
                            + ' ne sera donc pas mise à jour.' 
            RAISERROR (@Engueulade, 0,1) -- state = 0 pour les tests
            ----    RAISERROR (@Engueulade, 16,1)  -- state = 16 pour bloquer  
            RETURN  
        END
    
    ------------------------------------------------------
    -- de DELETED vers @deleted :
    -- Traduction des clés alternatives (naturelles) en
    -- clés primaires (artificielles)
    ------------------------------------------------------
    
    INSERT INTO @deleted (blindtestId, extraitId)
        SELECT DISTINCT  z.blindtestId, t.extraitId
        FROM   DELETED AS x
          JOIN BLINDTEST AS z ON x.Numero = z.Numero
          JOIN EXTRAIT AS t ON x.artiste = t.artiste
                           AND x.titre = t.titre
    ;
    
    ---- pour debug ---- select '' as '@deleted' , * from @deleted
    
    ------------------------------------------------------
    -- Mise à jour de la table COMPOSER.
    ------------------------------------------------------
    
    DELETE FROM COMPOSER
           WHERE  blindtestId IN (SELECT blindtestId FROM @deleted)
              AND extraitId IN (SELECT extraitId FROM @deleted)  
    ;
    
    ---- pour debug ---- select '' as 'COMPOSER après delete', * from COMPOSER
    
    GO
    

    Vue COMPOSER_V : trigger COMPOSER_TR pour insérer, modifier des lignes dans COMPOSER :

    
    DROP TRIGGER IF EXISTS COMPOSER_TR ;
    
    GO
    
    CREATE TRIGGER COMPOSER_TR ON COMPOSER_V INSTEAD OF INSERT, UPDATE
    AS
    
    DECLARE @tableCible as VARCHAR(48) = 'COMPOSER'
    DECLARE @theTrigger as VARCHAR(64) = @tableCible + '_TR - '
    DECLARE @EngueuladeInit AS VARCHAR(128) = @theTrigger + 'insert/update ' + @tableCible + ' - ';
    DECLARE @Engueulade AS VARCHAR(512) ;
    DECLARE @nbInserted as INT ;
    DECLARE @nbDeleted as INT ;
    DECLARE @NumeroKount as INT ;
    DECLARE @extraitCodeKount as INT ;
    DECLARE @blindtestConnu as CHAR(1) = 'Y'
    DECLARE @blindtestParticipant as CHAR(1) = 'Y'
    DECLARE @extraitConnu as CHAR(1) = 'Y'
    DECLARE @extraitParticipant as CHAR(1) = 'Y'
    
    DECLARE @t1 as TABLE 
    (
        Numero varchar(48)
      , artiste varchar(48)
      , titre varchar(48)
    )
    
    DECLARE @t2 as TABLE 
    (
        Numero varchar(48)
    )
    
    DECLARE @t3 as TABLE 
    (
        Numero varchar(48)
      , artiste varchar(48)
      , titre varchar(48)
    )
    
    -----------------------------------------------------------
    -- tables temporaires pour les clés primaires
    -----------------------------------------------------------
    
    DECLARE @inserted as TABLE
    (
        blindtestId  INT
      , extraitId    INT
    ) ;
    DECLARE @deleted as TABLE
    (
        blindtestId  INT
      , extraitId    INT
    ) ;
    
    --------------------------------------------------
    -- c'est parti
    --------------------------------------------------
    
    ---- pour debug ---- select '' as INSERTED, * from INSERTED  
    ---- pour debug ---- select '' as DELETED, * from DELETED  
    
    ---------------------------------------------
    -- Si INSERTED est vide, on dégage
    ---------------------------------------------
    
    SET @nbInserted = (SELECT COUNT(*) FROM INSERTED) ;
    
    IF @nbInserted = 0
        BEGIN
            SET @Engueulade = @EngueuladeInit 
                            + 'La requête propose un contenu vide, ' 
                            + 'la table ' + @tableCible 
                            + ' ne sera donc pas mise à jour.' 
            RAISERROR (@Engueulade, 0,1) -- state = 0 pour les tests
        ----    RAISERROR (@Engueulade, 16,1)  -- state = 16 pour bloquer  
            RETURN  
        END
    
    ------------------------------------------------------
    -- traduction des clés alternatives (naturelles) en
    -- clés primaires (artificielles)
    ------------------------------------------------------
    INSERT INTO @inserted (blindtestId, extraitId)
        SELECT DISTINCT y.blindtestId, z.extraitId
        FROM   INSERTED AS x
          JOIN BLINDTEST AS y ON x.Numero = y.Numero
          JOIN EXTRAIT AS z ON x.artiste = z.artiste
                           AND x.titre = z.titre
    ;
    ---- pour debug ---- select '' as '@inserted', * from @inserted 
    
    ---------------------------------------------------
    -- si un Numero est candidat au
    -- remplacement d'un autre Numero,
    -- il doit être connu dans la table BLINDTEST
    ---------------------------------------------------
    
    ---------------------------------------
    -- @t1 : les codes blintests inconnus
    ---------------------------------------
    
    INSERT INTO @t1 (Numero, artiste, titre)
        SELECT DISTINCT Numero, artiste, titre 
        FROM   INSERTED AS x
        WHERE  NOT EXISTS (SELECT Numero
                           FROM   BLINDTEST AS y
                           WHERE  x.Numero = y.Numero)
    ;
    ------------------------------------------
    -- Nombre exacts de numéros inconnus
    ------------------------------------------
    
    INSERT INTO @t2 (Numero) 
        SELECT DISTINCT Numero FROM @t1 ;
    
    SET @NumeroKount = (SELECT COUNT(*) FROM @t2)
    
    IF @NumeroKount > 0
        BEGIN
        -----------------------------------------
        -- Les numéros effectivement inconnus
        -----------------------------------------
            IF @NumeroKount = 1
                BEGIN
                    SET @Engueulade = @EngueuladeInit
                      + 'blindtest proposé : inconnu.' 
                END
            ELSE
                BEGIN
                    SET @Engueulade = @EngueuladeInit 
                      + 'Il y a '
                      + CAST(@NumeroKount as VARCHAR(5)) 
                      + ' blindtests proposés mais inconnus.'
                END
            SELECT @Engueulade AS Engueulons
                 , Numero as 'Numero proposé inconnu'
                 , artiste, titre 
            FROM @t1
            SET @blindtestConnu = 'N'
            RAISERROR (@Engueulade, 0,1) -- state = 0 pour les tests
    ----    RAISERROR (@Engueulade, 16,1)  -- state = 16 pour bloquer  
    ----------------------        RETURN
      
        END
    
    ---------------------------------------------------------
    -- recherche  dans INSERTED des paires {artiste, titre}
    -- inconnues, celles-ci figureront dans @t1
    ---------------------------------------------------------
    DELETE FROM @t1 ;
    
    INSERT INTO @t1 (Numero, artiste, titre)
        SELECT DISTINCT Numero, artiste, titre 
        FROM   INSERTED AS x
        WHERE  NOT EXISTS (SELECT artiste, titre
                           FROM   EXTRAIT AS y
                           WHERE  x.artiste = y.artiste 
                              AND x.titre = y.titre)
    ;
    INSERT INTO @t3 (artiste, titre) 
        SELECT DISTINCT artiste, titre FROM @t1 ;
    
    SET @extraitCodeKount = (SELECT COUNT(*) FROM @t3)
    
    IF @extraitCodeKount > 0
        BEGIN
        ------------------------------------------------------
        -- Les paires {artiste, titre} effectivement inconnues
        -------------------------------------------------------
            IF @extraitCodeKount = 1
                BEGIN
                    SET @Engueulade = @EngueuladeInit 
                      + 'extrait {artiste, titre} non reconnu.' 
                END
            ELSE
                BEGIN
                    SET @Engueulade = @EngueuladeInit 
                      + 'Il y a ' + cast(@NumeroKount as varchar(5)) 
                      + ' extraits {artiste, titre} proposés non reconnus.'
                END
            SELECT @Engueulade AS Engueulons
                 , Numero
                 , '{' + artiste + ', ' + titre + '}' as 'extrait proposé non reconnu' 
            FROM @t1
            SET @extraitConnu = 'N'
            RAISERROR (@Engueulade, 0,1) -- state = 0 pour les tests
    ----    RAISERROR (@Engueulade, 16,1)  -- state = 16 pour bloquer  
    -------------       RETURN  
        END
    
    ------------------------------------------------------
    -- Mise à jour de la table COMPOSER.
    -- Si on a détecté des erreurs, on ne fait rien.
    ------------------------------------------------------
    
    IF @blindtestConnu = 'Y' AND @extraitConnu = 'Y'
        BEGIN
            -------------------------------------------------------
            -- S’il y a quelque chose dans DELETED
            -- alors c’est qu’un UPDATE est en cours.
            -------------------------------------------------------
            IF (SELECT COUNT(*) FROM DELETED) > 0
                BEGIN 
                    INSERT INTO @deleted (blindtestId, extraitId)
                        SELECT DISTINCT y.blindtestId, z.extraitId
                        FROM   DELETED AS x
                          JOIN BLINDTEST AS y ON x.Numero = y.Numero
                               JOIN EXTRAIT AS z ON x.artiste = z.artiste
                                                AND x.titre = z.titre
                    ;
    
                    ---- pour debug ---- select '' as '@deleted', * from @deleted 
                    ---- pour debug ---- select '' as 'composer avant delete', * from COMPOSER
    
                     UPDATE COMPOSER 
                        SET  
                            blindtestId = 
                                (SELECT DISTINCT blindtestId 
                                 FROM   @inserted)
                          , extraitId =  
                                (SELECT DISTINCT extraitId
                                 FROM   @inserted)
                           WHERE blindtestId IN (SELECT DISTINCT blindtestId FROM @deleted)
                             AND extraitId IN (SELECT DISTINCT extraitId FROM @deleted) 
                            
                ---- pour debug ----  select '' as 'composer après update', * from COMPOSER
    
                END
            ELSE
                BEGIN
                ---------------------------------
                -- On est en INSERT
                ---------------------------------
    
                ---- pour debug ---- select '' as 'composer avant insert', * from COMPOSER
    
                    INSERT INTO COMPOSER (blindtestId, extraitId)
                        SELECT blindtestId, extraitId 
                        FROM @inserted
                   ;
                ---- pour debug ---- select '' as 'composer après insert', * from COMPOSER
                END
        END
    
    GO
    
    

    La programmation de UPDATE ne me satisfait toujours pas, du fait qu’on ne puisse modifier qu’une seule ligne à la fois dans la table COMPOSER, je réfléchis à une programmation moins contraignante...


     
    Faites simple, mais pas plus simple ! (A. Einstein)
    E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    Je ne réponds pas aux questions techniques par MP. Les forums sont là pout ça.
    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench

  11. #51
    Membre régulier
    Profil pro
    Inscrit en
    décembre 2003
    Messages
    430
    Détails du profil
    Informations personnelles :
    Localisation : Belgique

    Informations forums :
    Inscription : décembre 2003
    Messages : 430
    Points : 97
    Points
    97
    Par défaut
    Merci pour tout ce travail !

Discussions similaires

  1. [AC-2010] Gestion d'évaluation comment faire
    Par moniteur41 dans le forum Modélisation
    Réponses: 18
    Dernier message: 26/12/2012, 18h20
  2. Réponses: 9
    Dernier message: 23/06/2011, 18h34
  3. [Gestion des Exceptions] Comment faire ?
    Par caballero dans le forum Services Web
    Réponses: 3
    Dernier message: 21/02/2008, 09h39
  4. Réponses: 7
    Dernier message: 02/01/2007, 17h22
  5. Réponses: 4
    Dernier message: 05/06/2006, 11h34

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo