Subversion Repositories eFlore/Projets.eflore-projets

Rev

Rev 895 | Only display areas with differences | Ignore whitespace | Details | Blame | Last modification | View Log | RSS feed

Rev 895 Rev 973
1 
-- suppress dup:
 1 
-- suppress dup:
2 
-- >= 115 au 22/07/2013
 2 
-- >= 115 au 22/07/2013
3 
-- mysql -N <<<"SELECT distinct ce_utilisateur FROM `BASEEDIT`.`cel_obs` GROUP BY ce_utilisateur, ordre HAVING COUNT(*) > 1;" > ordre-dup.txt
 3 
-- mysql -N <<<"SELECT distinct ce_utilisateur FROM `BASEEDIT`.`cel_obs` GROUP BY ce_utilisateur, ordre HAVING COUNT(*) > 1;" > ordre-dup.txt
4 
 
 4 
 
5 
DROP FUNCTION IF EXISTS next_ordre;
 5 
DROP FUNCTION IF EXISTS next_ordre;
6 
DROP PROCEDURE IF EXISTS ordre_need_update;
 6 
DROP PROCEDURE IF EXISTS ordre_need_update;
7 
DROP PROCEDURE IF EXISTS update_ordre_user;
 7 
DROP PROCEDURE IF EXISTS update_ordre_user;
8 
DROP PROCEDURE IF EXISTS update_ordre_users;
 8 
DROP PROCEDURE IF EXISTS update_ordre_users;
9 
 
 9 
 
10 
DELIMITER |
 10 
DELIMITER |
11 
 
 11 
 
12 
CREATE FUNCTION next_ordre( s1 VARCHAR(255) )
 12 
CREATE FUNCTION next_ordre( s1 VARCHAR(255) )
13 
  RETURNS INT
 13 
  RETURNS INT
14 
  NOT DETERMINISTIC
 14 
  READS SQL DATA
15 
  BEGIN
 15 
  BEGIN
16 
        DECLARE c INT;
 16 
        DECLARE c INT;
17 
        SET c = (SELECT MAX(ordre) + 1 FROM `BASEEDIT`.`cel_obs` where ce_utilisateur = s1);
 17 
        SET c = (SELECT MAX(ordre) + 1 FROM `BASEEDIT`.`cel_obs` where ce_utilisateur = s1);
18 
        RETURN c;
 18 
        RETURN c;
19 
  END
 19 
  END
20 
|
 20 
|
21 
-- SELECT next_ordre("");
 21 
-- SELECT next_ordre("");
22 
 
 22 
 
23 
CREATE PROCEDURE ordre_need_update(IN _s1 VARCHAR(255), OUT _ordre INT, OUT _c INT, OUT _min_obs INT)
 23 
CREATE PROCEDURE ordre_need_update(IN _s1 VARCHAR(255), OUT _ordre INT, OUT _c INT, OUT _min_obs INT)
24 
  BEGIN
 24 
  BEGIN
25 
        SELECT ordre, count(ordre), MIN(id_observation) INTO _ordre, _c, _min_obs FROM `BASEEDIT`.`cel_obs` WHERE ce_utilisateur = _s1 GROUP BY ordre HAVING COUNT(ordre) > 1 LIMIT 1;
 25 
        SELECT ordre, count(ordre), MIN(id_observation) INTO _ordre, _c, _min_obs FROM `BASEEDIT`.`cel_obs` WHERE ce_utilisateur = _s1 GROUP BY ordre HAVING COUNT(ordre) > 1 LIMIT 1;
26 
  END
 26 
  END
27 
|
 27 
|
28 
-- SELECT ordre_need_update("");
 28 
-- SELECT ordre_need_update("");
29 
 
 29 
 
30 
CREATE PROCEDURE update_ordre_user(IN _s1 VARCHAR(255))
 30 
CREATE PROCEDURE update_ordre_user(IN _s1 VARCHAR(255))
31 
  BEGIN
 31 
  BEGIN
32 
    DECLARE obs_match int default -1;
 32 
    DECLARE obs_match int default -1;
33 
    CALL ordre_need_update(_s1, @o, @c, @minobs);
 33 
    CALL ordre_need_update(_s1, @o, @c, @minobs);
34 
    -- pour chaque ordre dupliqué
 34 
    -- pour chaque ordre dupliqué
35 
    WHILE @o IS NOT NULL DO
 35 
    WHILE @o IS NOT NULL DO
36 
       SELECT CONCAT("    ", @o) as "   ordre", @c as "(count/doublons)";
 36 
       SELECT CONCAT("    ", @o) as "   ordre", @c as "(count/doublons)";
37 
       -- SELECT id_observation FROM `BASEEDIT`.`cel_obs` WHERE ce_utilisateur = _s1 AND ordre = @o AND id_observation != @minobs;
 37 
       -- SELECT id_observation FROM `BASEEDIT`.`cel_obs` WHERE ce_utilisateur = _s1 AND ordre = @o AND id_observation != @minobs;
38 
       -- pour chaque obs concernée, exceptée la première, on met à jour l'ordre,
 38 
       -- pour chaque obs concernée, exceptée la première, on met à jour l'ordre,
39 
       -- en utilisant next_ordre()
 39 
       -- en utilisant next_ordre()
40 
       WHILE obs_match != 0 DO
 40 
       WHILE obs_match != 0 DO
41 
             -- SELECT CONCAT("== do update on", @o);
 41 
             -- SELECT CONCAT("== do update on", @o);
42 
             UPDATE `BASEEDIT`.`cel_obs` SET ordre = next_ordre(_s1)
 42 
             UPDATE `BASEEDIT`.`cel_obs` SET ordre = next_ordre(_s1)
43 
                    WHERE ce_utilisateur = _s1 AND ordre = @o AND id_observation != @minobs LIMIT 1;
 43 
                    WHERE ce_utilisateur = _s1 AND ordre = @o AND id_observation != @minobs LIMIT 1;
44 
             SELECT ROW_COUNT() into obs_match;
 44 
             SELECT ROW_COUNT() into obs_match;
45 
             -- SELECT @o, obs_match;
 45 
             -- SELECT @o, obs_match;
46 
       END WHILE;
 46 
       END WHILE;
47 
       -- toutes les observations dupliquées pour l'ordre @o ont été mises à jour
 47 
       -- toutes les observations dupliquées pour l'ordre @o ont été mises à jour
48 
       -- un nouvel ordre à mettre à jour va être obtenu par ordre_need_update()
 48 
       -- un nouvel ordre à mettre à jour va être obtenu par ordre_need_update()
49 
       -- dont nous restaurons obs_match à une valeur qui n'empêche pas la boucle
 49 
       -- dont nous restaurons obs_match à une valeur qui n'empêche pas la boucle
50 
       -- contenant l'UPDATE
 50 
       -- contenant l'UPDATE
51 
       SELECT -1 into obs_match;
51 
       SELECT -1 into obs_match;
52 
       CALL ordre_need_update(_s1, @o, @c, @minobs);
 52 
       CALL ordre_need_update(_s1, @o, @c, @minobs);
53 
       -- SELECT "====X", @o, @c;
 53 
       -- SELECT "====X", @o, @c;
54 
    END WHILE;
 54 
    END WHILE;
55 
  END
 55 
  END
56 
|
 56 
|
57 
-- CALL update_ordre_user("");
 57 
-- CALL update_ordre_user("");
58 
 
 58 
 
59 
 
 59 
 
60 
CREATE PROCEDURE update_ordre_users()
 60 
CREATE PROCEDURE update_ordre_users()
61 
  BEGIN
 61 
  BEGIN
62 
    DECLARE _nom VARCHAR(255);
 62 
    DECLARE _nom VARCHAR(255);
63 
    DECLARE subst INT DEFAULT 0;
 63 
    DECLARE subst INT DEFAULT 0;
64 
    DECLARE done INT DEFAULT 1;
 64 
    DECLARE done INT DEFAULT 1;
65 
 
 65 
 
66 
    -- temp table
 66 
    -- temp table
67 
    -- the following fails, pas d'index (see EXPLAIN + http://dba.stackexchange.com/questions/48231 ?)
 67 
    -- the following fails, pas d'index (see EXPLAIN + http://dba.stackexchange.com/questions/48231 ?)
68 
    -- ( SELECT DISTINCT ce_utilisateur FROM `BASEEDIT`.`cel_obs` GROUP BY ce_utilisateur, ordre HAVING COUNT(*) > 1 );
 68 
    -- ( SELECT DISTINCT ce_utilisateur FROM `BASEEDIT`.`cel_obs` GROUP BY ce_utilisateur, ordre HAVING COUNT(*) > 1 );
69 
    IF (SELECT SUBSTR(version(),3,1)) != 5 THEN
 69 
    IF (SELECT SUBSTR(version(),3,1)) != 5 THEN
70 
       -- mais celle-ci fonctionne, car l'ordre du GROUP BY correspond à l'INDEX [id_obs] : 16 secondes
 70 
       -- mais celle-ci fonctionne, car l'ordre du GROUP BY correspond à l'INDEX [id_obs] : 16 secondes
71 
       CREATE TEMPORARY TABLE IF NOT EXISTS _temp_users (ce_utilisateur VARCHAR(255)) ENGINE=MEMORY AS \
 71 
       CREATE TEMPORARY TABLE IF NOT EXISTS _temp_users (ce_utilisateur VARCHAR(255)) ENGINE=MEMORY AS \
72 
        ( SELECT DISTINCT ce_utilisateur FROM `BASEEDIT`.`cel_obs` GROUP BY ordre, ce_utilisateur HAVING COUNT(1) > 1 );
 72 
        ( SELECT DISTINCT ce_utilisateur FROM `BASEEDIT`.`cel_obs` GROUP BY ordre, ce_utilisateur HAVING COUNT(1) > 1 );
73 
    ELSE
 73 
    ELSE
74 
        -- alternativement, comme solution de replis (nécessaire pour MariaDB ?):
 74 
        -- alternativement, comme solution de replis (nécessaire pour MariaDB ?):
75 
        CREATE TEMPORARY TABLE IF NOT EXISTS _temp_users (ce_utilisateur VARCHAR(255)) ENGINE=MEMORY AS \
 75 
        CREATE TEMPORARY TABLE IF NOT EXISTS _temp_users (ce_utilisateur VARCHAR(255)) ENGINE=MEMORY AS \
76 
        ( SELECT DISTINCT ce_utilisateur FROM `BASEEDIT`.`cel_obs` WHERE ce_utilisateur IN \
 76 
        ( SELECT DISTINCT ce_utilisateur FROM `BASEEDIT`.`cel_obs` WHERE ce_utilisateur IN \
77 
        (SELECT ce_utilisateur FROM `BASEEDIT`.`cel_obs` GROUP BY ce_utilisateur, ordre HAVING COUNT(1) > 1) );
 77 
        (SELECT ce_utilisateur FROM `BASEEDIT`.`cel_obs` GROUP BY ce_utilisateur, ordre HAVING COUNT(1) > 1) );
78 
    END IF;
 78 
    END IF;
79 
 
 79 
 
80 
    SELECT COUNT(*) INTO done FROM _temp_users;
 80 
    SELECT COUNT(*) INTO done FROM _temp_users;
81 
    -- la requête principale de sélection des utilisateurs à mettre à jour
 81 
    -- la requête principale de sélection des utilisateurs à mettre à jour
82 
    WHILE done > 0 DO
 82 
    WHILE done > 0 DO
83 
        SELECT ce_utilisateur INTO _nom FROM _temp_users LIMIT 1;
 83 
        SELECT ce_utilisateur INTO _nom FROM _temp_users LIMIT 1;
84 
        SELECT _nom AS "utilisateur en mise à jour:";
 84 
        SELECT _nom AS "utilisateur en mise à jour:";
85 
        CALL update_ordre_user(_nom);
 85 
        CALL update_ordre_user(_nom);
86 
        SET subst = subst + 1;
 86 
        SET subst = subst + 1;
87 
        DELETE FROM _temp_users WHERE ce_utilisateur = _nom;
 87 
        DELETE FROM _temp_users WHERE ce_utilisateur = _nom;
88 
        SELECT COUNT(*) INTO done FROM _temp_users;
 88 
        SELECT COUNT(*) INTO done FROM _temp_users;
89 
    END WHILE;
 89 
    END WHILE;
90 
    SELECT subst AS "utilisateurs mis à jour";
 90 
    SELECT subst AS "utilisateurs mis à jour";
91 
END
 91 
END
92 
|
 92 
|
93 
 
 93 
 
94 
DELIMITER ;
 94 
DELIMITER ;
95 
 
 95 
 
96 
 
 96 
 
97 
CALL update_ordre_users();
 97 
CALL update_ordre_users();
98 
 
 98 
 
99 
 
 99 
 
100 
DROP FUNCTION IF EXISTS next_ordre;
 100 
DROP FUNCTION IF EXISTS next_ordre;
101 
DROP PROCEDURE IF EXISTS ordre_need_update;
 101 
DROP PROCEDURE IF EXISTS ordre_need_update;
102 
DROP PROCEDURE IF EXISTS update_ordre_user;
 102 
DROP PROCEDURE IF EXISTS update_ordre_user;
103 
DROP PROCEDURE IF EXISTS update_ordre_users;
 103 
DROP PROCEDURE IF EXISTS update_ordre_users;
104 
 
 104 
 
105 
-- clef unique sur (id_utilisateur, ordre)
 105 
-- clef unique sur (id_utilisateur, ordre)
106 
-- [mais seulement si on a dédupliqué TOUS les utilisateurs, y compris l'utilisateur ''
 106 
-- [mais seulement si on a dédupliqué TOUS les utilisateurs, y compris l'utilisateur ''
107 
-- à voir aussi: maj-hash-id-obs-migr.sql]
 107 
-- à voir aussi: maj-hash-id-obs-migr.sql]
108 
DROP INDEX `id_obs` ON `BASEEDIT`.`cel_obs`;
 108 
DROP INDEX `id_obs` ON `BASEEDIT`.`cel_obs`;
109 
CREATE UNIQUE INDEX `id_obs` ON `BASEEDIT`.`cel_obs` (`ce_utilisateur` ASC, `ordre` ASC);
 109 
CREATE UNIQUE INDEX `id_obs` ON `BASEEDIT`.`cel_obs` (`ce_utilisateur` ASC, `ordre` ASC);