Subversion Repositories eFlore/Projets.eflore-projets

Compare Revisions

Ignore whitespace Rev 1077 → Rev 1078

/tags/v5.8-aspergeraie/scripts/modules/cel/dedup-ordre-201307.sql
New file
0,0 → 1,109
-- suppress dup:
-- >= 115 au 22/07/2013
-- mysql -N <<<"SELECT distinct ce_utilisateur FROM `BASEEDIT`.`cel_obs` GROUP BY ce_utilisateur, ordre HAVING COUNT(*) > 1;" > ordre-dup.txt
 
DROP FUNCTION IF EXISTS next_ordre;
DROP PROCEDURE IF EXISTS ordre_need_update;
DROP PROCEDURE IF EXISTS update_ordre_user;
DROP PROCEDURE IF EXISTS update_ordre_users;
 
DELIMITER |
 
CREATE FUNCTION next_ordre( s1 VARCHAR(255) )
RETURNS INT
READS SQL DATA
BEGIN
DECLARE c INT;
SET c = (SELECT MAX(ordre) + 1 FROM `BASEEDIT`.`cel_obs` where ce_utilisateur = s1);
RETURN c;
END
|
-- SELECT next_ordre("");
 
CREATE PROCEDURE ordre_need_update(IN _s1 VARCHAR(255), OUT _ordre INT, OUT _c INT, OUT _min_obs INT)
BEGIN
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;
END
|
-- SELECT ordre_need_update("");
 
CREATE PROCEDURE update_ordre_user(IN _s1 VARCHAR(255))
BEGIN
DECLARE obs_match int default -1;
CALL ordre_need_update(_s1, @o, @c, @minobs);
-- pour chaque ordre dupliqué
WHILE @o IS NOT NULL DO
SELECT CONCAT(" ", @o) as " ordre", @c as "(count/doublons)";
-- SELECT id_observation FROM `BASEEDIT`.`cel_obs` WHERE ce_utilisateur = _s1 AND ordre = @o AND id_observation != @minobs;
-- pour chaque obs concernée, exceptée la première, on met à jour l'ordre,
-- en utilisant next_ordre()
WHILE obs_match != 0 DO
-- SELECT CONCAT("== do update on", @o);
UPDATE `BASEEDIT`.`cel_obs` SET ordre = next_ordre(_s1)
WHERE ce_utilisateur = _s1 AND ordre = @o AND id_observation != @minobs LIMIT 1;
SELECT ROW_COUNT() into obs_match;
-- SELECT @o, obs_match;
END WHILE;
-- toutes les observations dupliquées pour l'ordre @o ont été mises à jour
-- un nouvel ordre à mettre à jour va être obtenu par ordre_need_update()
-- dont nous restaurons obs_match à une valeur qui n'empêche pas la boucle
-- contenant l'UPDATE
SELECT -1 into obs_match;
CALL ordre_need_update(_s1, @o, @c, @minobs);
-- SELECT "====X", @o, @c;
END WHILE;
END
|
-- CALL update_ordre_user("");
 
 
CREATE PROCEDURE update_ordre_users()
BEGIN
DECLARE _nom VARCHAR(255);
DECLARE subst INT DEFAULT 0;
DECLARE done INT DEFAULT 1;
 
-- temp table
-- the following fails, pas d'index (see EXPLAIN + http://dba.stackexchange.com/questions/48231 ?)
-- ( SELECT DISTINCT ce_utilisateur FROM `BASEEDIT`.`cel_obs` GROUP BY ce_utilisateur, ordre HAVING COUNT(*) > 1 );
IF (SELECT SUBSTR(version(),3,1)) != 5 THEN
-- mais celle-ci fonctionne, car l'ordre du GROUP BY correspond à l'INDEX [id_obs] : 16 secondes
CREATE TEMPORARY TABLE IF NOT EXISTS _temp_users (ce_utilisateur VARCHAR(255)) ENGINE=MEMORY AS \
( SELECT DISTINCT ce_utilisateur FROM `BASEEDIT`.`cel_obs` GROUP BY ordre, ce_utilisateur HAVING COUNT(1) > 1 );
ELSE
-- alternativement, comme solution de replis (nécessaire pour MariaDB ?):
CREATE TEMPORARY TABLE IF NOT EXISTS _temp_users (ce_utilisateur VARCHAR(255)) ENGINE=MEMORY AS \
( SELECT DISTINCT ce_utilisateur FROM `BASEEDIT`.`cel_obs` WHERE ce_utilisateur IN \
(SELECT ce_utilisateur FROM `BASEEDIT`.`cel_obs` GROUP BY ce_utilisateur, ordre HAVING COUNT(1) > 1) );
END IF;
 
SELECT COUNT(*) INTO done FROM _temp_users;
-- la requête principale de sélection des utilisateurs à mettre à jour
WHILE done > 0 DO
SELECT ce_utilisateur INTO _nom FROM _temp_users LIMIT 1;
SELECT _nom AS "utilisateur en mise à jour:";
CALL update_ordre_user(_nom);
SET subst = subst + 1;
DELETE FROM _temp_users WHERE ce_utilisateur = _nom;
SELECT COUNT(*) INTO done FROM _temp_users;
END WHILE;
SELECT subst AS "utilisateurs mis à jour";
END
|
 
DELIMITER ;
 
 
CALL update_ordre_users();
 
 
DROP FUNCTION IF EXISTS next_ordre;
DROP PROCEDURE IF EXISTS ordre_need_update;
DROP PROCEDURE IF EXISTS update_ordre_user;
DROP PROCEDURE IF EXISTS update_ordre_users;
 
-- clef unique sur (id_utilisateur, ordre)
-- [mais seulement si on a dédupliqué TOUS les utilisateurs, y compris l'utilisateur ''
-- à voir aussi: maj-hash-id-obs-migr.sql]
DROP INDEX `id_obs` ON `BASEEDIT`.`cel_obs`;
CREATE UNIQUE INDEX `id_obs` ON `BASEEDIT`.`cel_obs` (`ce_utilisateur` ASC, `ordre` ASC);