Subversion Repositories eFlore/Projets.eflore-projets

Compare Revisions

Ignore whitespace Rev 818 → Rev 819

/trunk/scripts/modules/cel/maj-referentiel-201307.sql
4,7 → 4,7
 
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 `tb_cel_test`.`cel_obs`
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
12,7 → 12,7
 
La requête est donc:
SELECT id_observation, nom_referentiel, nom_sel, nom_sel_nn, nom_ret, nom_ret_nn, nt, famille
FROM `tb_cel_test`.`cel_obs` WHERE nom_referentiel IS NULL AND nom_sel_nn IS NOT NULL;
FROM `BASEEDIT`.`cel_obs` WHERE nom_referentiel IS NULL AND nom_sel_nn IS NOT NULL;
=> 76486
*/
 
21,20 → 21,20
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 `tb_eflore`.`bdtfx_v1_01` 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 `tb_eflore`.`bdtxa_v1_00` a;
SELECT "bdtfx" AS valid_ref, CONCAT(b.nom_sci, ' ', b.auteur) AS nom, b.num_nom, b.num_taxonomique, b.famille FROM `BASESOURCE`.`bdtfx_v1_01` 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`.`bdtxa_v1_00` a;
 
/* Donc nous JOINons:
-- INNER JOIN sur bdtfx: 69719
SELECT id_observation, nom_referentiel, nom_sel, nom_sel_nn, nom_ret, nom_ret_nn, nt, c.famille FROM `tb_cel_test`.`cel_obs` c INNER JOIN `tb_eflore`.`bdtfx_v1_01` b ON (b.num_nom = c.nom_sel_nn) WHERE nom_referentiel IS NULL AND nom_sel_nn IS NOT NULL;
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`.`bdtfx_v1_01` 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: 79471 dont 9752 en dup (bdtxa + bdtfx), eg: 1005047
SELECT id_observation, nom_referentiel, nom_sel, nom_sel_nn, nom_ret, nom_ret_nn, nt, c.famille FROM `tb_cel_test`.`cel_obs` c INNER JOIN T_bis b ON (b.num_nom = c.nom_sel_nn) WHERE nom_referentiel AND nom_sel_nn IS NOT NULL;
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 AND nom_sel_nn IS NOT NULL;
*/
 
/* d'où GROUP BY id_observation HAVING count(id_observation) = 1 */
/*
SELECT id_observation, nom_referentiel, nom_sel, nom_sel_nn, nom_ret, nom_ret_nn, nt, c.famille
FROM `tb_cel_test`.`cel_obs` c
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;
50,7 → 50,7
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 `tb_cel_test`.`cel_obs` c INNER JOIN T_bis b ON (b.num_nom = c.nom_sel_nn)
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;
58,7 → 58,7
OPEN cur1;
REPEAT
FETCH cur1 INTO _id_observation, _valid_ref;
UPDATE `tb_cel_test`.`cel_obs` c SET nom_referentiel = _valid_ref WHERE id_observation = _id_observation;
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';