Subversion Repositories eFlore/Applications.del

Rev

Go to most recent revision | Details | Last modification | View Log | RSS feed

Rev Author Line No. Line
1444 raphael 1
DELIMITER |
2
 
3
-- initialisation (structure)
4
DROP PROCEDURE IF EXISTS update_struct;
5
CREATE PROCEDURE update_struct()
6
  BEGIN
7
       -- ajout de la colonne "proposition_retenue"
8
       DECLARE CONTINUE HANDLER FOR SQLSTATE '42S21' SELECT "colonne proposition_retenue déjà existante"; /* = Duplicate column name */
9
       ALTER TABLE `BASEDEL`.`del_commentaire` ADD `proposition_retenue` INT(1) NOT NULL DEFAULT 0 COMMENT "La proposition \"validée\" une fois que l'auteur à validé et que le nom_sel de cel_obs a été modifié à partir du nom_sel de del_commentaire."; -- '
10
  END;
11
|
12
 
13
-- petite functon bonus
14
DROP PROCEDURE IF EXISTS getCommentaires;
15
CREATE PROCEDURE getCommentaires(IN _s1 INT(20))
16
  BEGIN
17
        SELECT id_commentaire, ce_observation, ce_proposition, texte, nom_sel, nom_sel_nn, proposition_retenue FROM `BASEDEL`.`del_commentaire` WHERE ce_observation = _s1;
18
  END;
19
|
20
 
21
-- procédure créant des tables temporaires permettant de faciliter le processus de MAJ
1447 raphael 22
DROP PROCEDURE IF EXISTS rebuild_retenues;
1444 raphael 23
CREATE PROCEDURE rebuild_retenues()
24
  BEGIN
25
  DROP TEMPORARY TABLE IF EXISTS _temp_having_retenue, _temp_multi_prop, _temp_mono_prop;
26
 
27
  -- les observations déjà retenues (pour pouvoir être exclues des processus postérieurs)
28
  CREATE TEMPORARY TABLE _temp_having_retenue ENGINE=MEMORY AS ( \
29
       SELECT ce_observation FROM `BASEDEL`.`del_commentaire` co WHERE proposition_retenue = 1);
30
 
31
  -- observations ayant plusieurs propositions (= commentaire dont nom_sel IS NOT NULL)
32
  CREATE TEMPORARY TABLE IF NOT EXISTS _temp_multi_prop ENGINE=MEMORY AS ( \
33
       SELECT ce_observation FROM `BASEDEL`.`del_commentaire` co \
34
       WHERE nom_sel IS NOT NULL \
35
             AND proposition_retenue = 0 /* == ce_observation NOT IN (SELECT ce_proposition FROM _temp_having_retenue) */ \
36
       GROUP BY ce_observation HAVING COUNT(id_commentaire) > 1); -- 1053
37
 
38
  -- observations ayant une seule proposition (= commentaire dont nom_sel IS NOT NULL)
39
  CREATE TEMPORARY TABLE IF NOT EXISTS _temp_mono_prop ENGINE=MEMORY AS ( \
40
       SELECT ce_observation FROM `BASEDEL`.`del_commentaire` \
41
       WHERE nom_sel IS NOT NULL \
42
             AND proposition_retenue = 0 /* == ce_observation NOT IN (SELECT ce_proposition FROM _temp_having_retenue) */ \
43
       GROUP BY ce_observation HAVING COUNT(id_commentaire) = 1);
44
 
45
  END;
46
|
47
 
48
DELIMITER ;
49
 
50
 
51
CALL update_struct();
52
-- table temporaire de tous les comptages (somme des votes pour chaque proposition de del_commentaire):
53
CREATE TEMPORARY TABLE IF NOT EXISTS _temp_stats ENGINE=MEMORY AS ( SELECT ce_proposition, SUM(valeur) AS val FROM `BASEDEL`.`del_commentaire_vote` GROUP BY ce_proposition); -- 5912
54
-- propositions ayant eu au moins 1 vote positif non-anonyme
55
CREATE TEMPORARY TABLE IF NOT EXISTS _temp_authvotepos ENGINE=MEMORY AS (SELECT DISTINCT ce_proposition FROM `BASEDEL`.`del_commentaire_vote` WHERE valeur = 1 AND LENGTH(ce_utilisateur) != 32 ORDER BY ce_proposition); -- 3794
56
CALL rebuild_retenues(); -- initialisation
57
 
58
-- core
59
-- SELECT ce_observation, id_commentaire FROM `BASEDEL`.`del_commentaire` co INNER JOIN _temp_multi_prop mp USING (ce_observation) INNER JOIN `BASECEL`.`cel_obs` ce ON mp.ce_observation = id_observation AND (co.nom_sel = ce.nom_sel AND co.nom_sel_nn = ce.nom_sel_nn) WHERE co.nom_sel_nn != 0 AND co.proposition_retenue = 0; -- 470, dont 464 ce_observation unique
60
-- SELECT ce_observation, id_commentaire FROM `BASEDEL`.`del_commentaire` co INNER JOIN _temp_multi_prop mp USING (ce_observation) INNER JOIN `BASECEL`.`cel_obs` ce ON mp.ce_observation = id_observation AND (co.nom_sel = ce.nom_sel OR co.nom_sel_nn = ce.nom_sel_nn) WHERE co.nom_sel_nn != 0 AND co.proposition_retenue = 0; -- 520, dont 493 ce_observation unique
61
 
62
-- Étant donné les doublons nous pouvons commencer ou finir par filtrer sur les stats de vote
63
-- Comme il est plus simple de finir par ça, commençons correctement par les nom_sel comme ci-dessus avec ceux sans ambiguité:
64
-- C'est à dire observations dotées de multiples proposition dont une et une seule match exactement le nom-sel du carnet en ligne
65
CREATE TEMPORARY TABLE IF NOT EXISTS _temp_updatable1 ENGINE=MEMORY AS ( \
66
       SELECT /* ce_observation, */ id_commentaire FROM `BASEDEL`.`del_commentaire` co \
67
       INNER JOIN _temp_multi_prop mp USING (ce_observation) /* faisant partie des observations ayant plusieurs propositions */ \
68
       INNER JOIN `BASECEL`.`cel_obs` ce ON mp.ce_observation = id_observation AND (co.nom_sel = ce.nom_sel AND co.nom_sel_nn = ce.nom_sel_nn) \
69
       WHERE co.nom_sel_nn != 0 \
70
       GROUP BY co.ce_observation HAVING COUNT(co.id_commentaire) = 1 /* observations n'ayant pas d'ambiguité sur le commentaire correspondant au nom du carnet en ligne */ ); -- 335
71
 
72
UPDATE `BASEDEL`.`del_commentaire` co SET co.proposition_retenue = 1 WHERE co.id_commentaire IN (SELECT id_commentaire FROM _temp_updatable1);
73
SELECT ROW_COUNT() AS "nb MAJ par match sur nom_sel sans ambiguité";
74
CALL rebuild_retenues();
75
 
76
 
77
-- Pour les observations ayant plusieurs nom-sel communs au carnet en ligne, il s'agit de ne sélectionner que celle
78
-- ayant le plus de vote (idéalement les propositions doublonnes ne devraient pas exister)
79
CREATE TEMPORARY TABLE IF NOT EXISTS _temp_updatable2 ENGINE=MEMORY AS ( \
80
SELECT co.ce_observation, id_commentaire, i1.val FROM `BASEDEL`.`del_commentaire` co \
81
       INNER JOIN _temp_multi_prop mp USING (ce_observation) /* uniquement les multi-propositions, inclue le check sur _temp_having_retenue */ \
82
       INNER JOIN (SELECT ce_proposition, val FROM _temp_stats) AS i1 ON i1.ce_proposition = co.id_commentaire /* et ayant des votes */ \
83
       INNER JOIN `BASECEL`.`cel_obs` ce ON co.ce_observation = ce.id_observation AND (co.nom_sel = ce.nom_sel AND co.nom_sel_nn = ce.nom_sel_nn) /* et un nom_sel acceptable */ \
84
       WHERE co.nom_sel_nn != 0 \
85
       GROUP BY ce_observation HAVING i1.val = MAX(i1.val) /* parmis les doublonnes, prendre la proposition ayant le plus de SUM(votes) */ \
86
       ORDER BY ce_observation ); -- 249 (sans le processus précédent, moins autrement s'il l'on exclue les observation déjà "validée")
87
 
88
UPDATE `BASEDEL`.`del_commentaire` co SET co.proposition_retenue = 1 WHERE co.id_commentaire IN (SELECT id_commentaire FROM _temp_updatable2);
89
SELECT ROW_COUNT() AS "nb MAJ par nombre de vote";
90
CALL rebuild_retenues();
91
 
92
-- Enfin, les observation n'ayant qu'une seule proposition sont passées comme retenues si
93
-- elles ont été notée positivement au moins une fois par un utilisateur authentifié
94
CREATE TEMPORARY TABLE IF NOT EXISTS _temp_updatable3 ENGINE=MEMORY AS ( \
95
SELECT co.id_commentaire FROM `BASEDEL`.`del_commentaire` co \
96
       INNER JOIN _temp_mono_prop AS i1 USING (ce_observation) /* l'inverse de _temp_multi_prop, inclue le check sur _temp_having_retenue */ \
97
       INNER JOIN _temp_authvotepos ta ON co.id_commentaire = ta.ce_proposition /* et uniquement si faisant partie des commentaires doté d'un vote positif non-anonyme */ \
98
       INNER JOIN `BASECEL`.`cel_obs` ce ON co.ce_observation = ce.id_observation AND (co.nom_sel = ce.nom_sel AND co.nom_sel_nn = ce.nom_sel_nn) \
99
       WHERE co.nom_sel_nn != 0); -- 2216 sans test sur cel_obs, 2193 avec
100
 
101
UPDATE `BASEDEL`.`del_commentaire` co SET co.proposition_retenue = 1 WHERE co.id_commentaire IN (SELECT id_commentaire FROM _temp_updatable3);
102
SELECT ROW_COUNT() AS "nb MAJ ayant 1 seule proposition mais un vote non-anonyme positif";
103
CALL rebuild_retenues();