Subversion Repositories eFlore/Projets.eflore-projets

/*

  Mise à jour de réferentiels NULL ou vides pour les observations dotées d'un nom_sel_nn

  75427 observations trouvées au 2013/07/19

  Les observations problématiques sont les suivantes:

  SELECT id_observation, nom_referentiel, nom_sel, nom_sel_nn, nom_ret, nom_ret_nn, nt, famille

  FROM `BASEEDIT`.`cel_obs`

  WHERE nom_referentiel IS NULL AND nom_sel != '' AND nom_sel IS NOT NULL AND nom_ret_nn IS NOT NULL;

  Or maj-cleanup-201307.sql reset les valeurs des observations ayant un nom_sel NULL ou '', de plus la préférence de sélection est

  donné au nom_sel (incluant les synonymes) plutôt que nom_ret_nn.

  La requête est donc:

  SELECT id_observation, nom_referentiel, nom_sel, nom_sel_nn, nom_ret, nom_ret_nn, nt, famille

  FROM `BASEEDIT`.`cel_obs` WHERE nom_referentiel IS NULL AND nom_sel_nn IS NOT NULL;

  => 76486

*/

DROP TEMPORARY TABLE IF EXISTS T_bis;

DROP PROCEDURE IF EXISTS majreferentiel;

CREATE TEMPORARY TABLE IF NOT EXISTS T_bis ( INDEX(`nom`(30))) AS \

           SELECT "bdtfx" AS valid_ref, CONCAT(b.nom_sci, ' ', b.auteur) AS nom, b.num_nom, b.num_taxonomique, b.famille FROM `BASESOURCE`.`TABLEBDTFX` b UNION ALL \

           SELECT "bdtxa" AS valid_ref, CONCAT(a.nom_sci, ' ', a.auteur) AS nom, a.num_nom, a.num_tax, a.famille FROM `BASESOURCE`.`TABLEBDTXA` a UNION ALL \

           SELECT "isfan" AS valid_ref, CONCAT(i.nom_sci, ' ', i.auteur) AS nom, i.num_nom, i.num_taxonomique, i.famille FROM `BASESOURCE`.`TABLEISFAN` i;

/* Donc nous JOINons:

-- INNER JOIN sur bdtfx: 62633

SELECT id_observation, nom_referentiel, nom_sel, nom_sel_nn, nom_ret, nom_ret_nn, nt, c.famille FROM `BASEEDIT`.`cel_obs` c INNER JOIN `BASESOURCE`.`TABLEBDTFX` b ON (b.num_nom = c.nom_sel_nn) WHERE nom_referentiel IS NULL AND nom_sel_nn IS NOT NULL;

-- INNER JOIN sur bdtxa: 9469

SELECT id_observation, nom_referentiel, nom_sel, nom_sel_nn, nom_ret, nom_ret_nn, nt, c.famille FROM `BASEEDIT`.`cel_obs` c INNER JOIN `BASESOURCE`.`TABLEISFAN` i ON (i.num_nom = c.nom_sel_nn) WHERE nom_referentiel IS NULL AND nom_sel_nn IS NOT NULL;

-- INNER JOIN sur isfan: 1991

SELECT id_observation, nom_referentiel, nom_sel, nom_sel_nn, nom_ret, nom_ret_nn, nt, c.famille FROM `BASEEDIT`.`cel_obs` c INNER JOIN `BASESOURCE`.`TABLEISFAN` i ON (i.num_nom = c.nom_sel_nn) WHERE nom_referentiel IS NULL AND nom_sel_nn IS NOT NULL;

-- INNER JOIN sur les 3 référentiels (bdtxa + bdtfx + isfan): 74093

SELECT id_observation, valid_ref, nom_sel, nom_sel_nn, nom_ret, nom_ret_nn, nt, c.famille FROM `BASEEDIT`.`cel_obs` c INNER JOIN T_bis b ON (b.num_nom = c.nom_sel_nn) WHERE nom_referentiel IS NULL AND nom_sel_nn IS NOT NULL;

*/

/* mais de nombreux noms sont exactement présents dans plusieurs référentiels,

   d'où GROUP BY id_observation HAVING count(id_observation) = 1,

   ce qui ne produit plus que 51359 matches (soit 22734 dups) */

/*

SELECT id_observation, nom_referentiel, nom_sel, nom_sel_nn, nom_ret, nom_ret_nn, nt, c.famille

FROM `BASEEDIT`.`cel_obs` c

INNER JOIN T_bis b

ON (b.num_nom = c.nom_sel_nn)

WHERE nom_referentiel IS NULL AND nom_sel_nn IS NOT NULL GROUP BY id_observation HAVING count(id_observation) = 1;

-- 51359, tous bdtfx...

*/

delimiter |

CREATE PROCEDURE majreferentiel()

BEGIN

DECLARE done INT DEFAULT 0;

DECLARE subst INT DEFAULT 0;

DECLARE _id_observation bigint(20) DEFAULT 0;

DECLARE _valid_ref varchar(20) DEFAULT NULL;

DECLARE cur1 CURSOR FOR SELECT id_observation, valid_ref FROM `BASEEDIT`.`cel_obs` c INNER JOIN T_bis b ON (b.num_nom = c.nom_sel_nn)

                        WHERE nom_referentiel IS NULL AND nom_sel_nn IS NOT NULL GROUP BY id_observation HAVING count(id_observation) = 1;

-- DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

OPEN cur1;

REPEAT

        FETCH cur1 INTO _id_observation, _valid_ref;

        UPDATE `BASEEDIT`.`cel_obs` c SET nom_referentiel = _valid_ref WHERE id_observation = _id_observation;

        SET subst = subst + 1;

UNTIL done END REPEAT;

select subst AS 'nombre de mises à jour de référentiel effectuées';

CLOSE cur1;

END

|

delimiter ;