1 |
/*
|
1 |
/*
|
2 |
Mise à jour de réferentiels NULL ou vides pour les observations dotées d'un nom_sel_nn
|
2 |
Mise à jour de réferentiels NULL ou vides pour les observations dotées d'un nom_sel_nn
|
3 |
75427 observations trouvées au 2013/07/19
|
3 |
75427 observations trouvées au 2013/07/19
|
4 |
|
4 |
|
5 |
Les observations problématiques sont les suivantes:
|
5 |
Les observations problématiques sont les suivantes:
|
6 |
SELECT id_observation, nom_referentiel, nom_sel, nom_sel_nn, nom_ret, nom_ret_nn, nt, famille
|
6 |
SELECT id_observation, nom_referentiel, nom_sel, nom_sel_nn, nom_ret, nom_ret_nn, nt, famille
|
7 |
FROM `BASEEDIT`.`cel_obs`
|
7 |
FROM `BASEEDIT`.`cel_obs`
|
8 |
WHERE nom_referentiel IS NULL AND nom_sel != '' AND nom_sel IS NOT NULL AND nom_ret_nn IS NOT NULL;
|
8 |
WHERE nom_referentiel IS NULL AND nom_sel != '' AND nom_sel IS NOT NULL AND nom_ret_nn IS NOT NULL;
|
9 |
|
9 |
|
10 |
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
|
10 |
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
|
11 |
donné au nom_sel (incluant les synonymes) plutôt que nom_ret_nn.
|
11 |
donné au nom_sel (incluant les synonymes) plutôt que nom_ret_nn.
|
12 |
|
12 |
|
13 |
La requête est donc:
|
13 |
La requête est donc:
|
14 |
SELECT id_observation, nom_referentiel, nom_sel, nom_sel_nn, nom_ret, nom_ret_nn, nt, famille
|
14 |
SELECT id_observation, nom_referentiel, nom_sel, nom_sel_nn, nom_ret, nom_ret_nn, nt, famille
|
15 |
FROM `BASEEDIT`.`cel_obs` WHERE nom_referentiel IS NULL AND nom_sel_nn IS NOT NULL;
|
15 |
FROM `BASEEDIT`.`cel_obs` WHERE nom_referentiel IS NULL AND nom_sel_nn IS NOT NULL;
|
16 |
=> 76486
|
16 |
=> 76486
|
17 |
*/
|
17 |
*/
|
18 |
|
18 |
|
19 |
|
19 |
|
20 |
DROP TEMPORARY TABLE IF EXISTS T_bis;
|
20 |
DROP TEMPORARY TABLE IF EXISTS T_bis;
|
21 |
DROP PROCEDURE IF EXISTS majreferentiel;
|
21 |
DROP PROCEDURE IF EXISTS majreferentiel;
|
22 |
|
22 |
|
23 |
CREATE TEMPORARY TABLE IF NOT EXISTS T_bis ( INDEX(`nom`(30))) AS \
|
23 |
CREATE TEMPORARY TABLE IF NOT EXISTS T_bis ( INDEX(`nom`(30))) AS \
|
24 |
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 \
|
24 |
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 \
|
25 |
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 \
|
25 |
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 \
|
26 |
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;
|
26 |
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;
|
27 |
|
27 |
|
28 |
/* Donc nous JOINons:
|
28 |
/* Donc nous JOINons:
|
29 |
-- INNER JOIN sur bdtfx: 62633
|
29 |
-- INNER JOIN sur bdtfx: 62633
|
30 |
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;
|
30 |
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;
|
31 |
-- INNER JOIN sur bdtxa: 9469
|
31 |
-- INNER JOIN sur bdtxa: 9469
|
32 |
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;
|
32 |
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;
|
33 |
-- INNER JOIN sur isfan: 1991
|
33 |
-- INNER JOIN sur isfan: 1991
|
34 |
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;
|
34 |
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;
|
35 |
-- INNER JOIN sur les 3 référentiels (bdtxa + bdtfx + isfan): 74093
|
35 |
-- INNER JOIN sur les 3 référentiels (bdtxa + bdtfx + isfan): 74093
|
36 |
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;
|
36 |
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;
|
37 |
*/
|
37 |
*/
|
38 |
|
38 |
|
39 |
/* mais de nombreux noms sont exactement présents dans plusieurs référentiels,
|
39 |
/* mais de nombreux noms sont exactement présents dans plusieurs référentiels,
|
40 |
d'où GROUP BY id_observation HAVING count(id_observation) = 1,
|
40 |
d'où GROUP BY id_observation HAVING count(id_observation) = 1,
|
41 |
ce qui ne produit plus que 51359 matches (soit 22734 dups) */
|
41 |
ce qui ne produit plus que 51359 matches (soit 22734 dups) */
|
42 |
/*
|
42 |
/*
|
43 |
SELECT id_observation, nom_referentiel, nom_sel, nom_sel_nn, nom_ret, nom_ret_nn, nt, c.famille
|
43 |
SELECT id_observation, valid_ref, nom_sel, nom, nom_sel_nn, nom_ret, nom_ret_nn, nt, c.famille
|
44 |
FROM `BASEEDIT`.`cel_obs` c
|
44 |
FROM `BASEEDIT`.`cel_obs` c
|
45 |
INNER JOIN T_bis b
|
45 |
INNER JOIN T_bis b
|
46 |
ON (b.num_nom = c.nom_sel_nn)
|
46 |
ON (b.num_nom = c.nom_sel_nn)
|
47 |
WHERE nom_referentiel IS NULL AND nom_sel_nn IS NOT NULL GROUP BY id_observation HAVING count(id_observation) = 1;
|
47 |
WHERE nom_referentiel IS NULL AND nom_sel_nn IS NOT NULL GROUP BY id_observation HAVING count(id_observation) = 1;
|
48 |
-- 51359, tous bdtfx...
|
48 |
-- 63941, tous bdtfx...
|
49 |
*/
|
49 |
*/
|
50 |
|
50 |
|
51 |
|
51 |
|
52 |
delimiter |
|
52 |
delimiter |
|
53 |
|
53 |
|
54 |
CREATE PROCEDURE majreferentiel()
|
54 |
CREATE PROCEDURE majreferentiel()
|
55 |
BEGIN
|
55 |
BEGIN
|
56 |
DECLARE done INT DEFAULT 0;
|
56 |
DECLARE done INT DEFAULT 0;
|
57 |
DECLARE subst INT DEFAULT 0;
|
57 |
DECLARE subst INT DEFAULT 0;
|
58 |
DECLARE _id_observation bigint(20) DEFAULT 0;
|
58 |
DECLARE _id_observation bigint(20) DEFAULT 0;
|
59 |
DECLARE _valid_ref varchar(20) DEFAULT NULL;
|
59 |
DECLARE _valid_ref varchar(20) DEFAULT NULL;
|
60 |
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)
|
60 |
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)
|
61 |
WHERE nom_referentiel IS NULL AND nom_sel_nn IS NOT NULL GROUP BY id_observation HAVING count(id_observation) = 1;
|
61 |
WHERE nom_referentiel IS NULL AND nom_sel_nn IS NOT NULL GROUP BY id_observation HAVING count(id_observation) = 1;
|
62 |
-- DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
|
62 |
-- DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
|
63 |
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
|
63 |
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
|
64 |
|
64 |
|
65 |
OPEN cur1;
|
65 |
OPEN cur1;
|
66 |
REPEAT
|
66 |
REPEAT
|
67 |
FETCH cur1 INTO _id_observation, _valid_ref;
|
67 |
FETCH cur1 INTO _id_observation, _valid_ref;
|
68 |
UPDATE `BASEEDIT`.`cel_obs` c SET nom_referentiel = _valid_ref WHERE id_observation = _id_observation;
|
68 |
UPDATE `BASEEDIT`.`cel_obs` c SET nom_referentiel = _valid_ref WHERE id_observation = _id_observation;
|
69 |
SET subst = subst + 1;
|
69 |
SET subst = subst + 1;
|
70 |
UNTIL done END REPEAT;
|
70 |
UNTIL done END REPEAT;
|
71 |
select subst AS 'nombre de mises à jour de référentiel effectuées';
|
71 |
select subst AS 'nombre de mises à jour de référentiel effectuées';
|
72 |
CLOSE cur1;
|
72 |
CLOSE cur1;
|
73 |
END
|
73 |
END
|
74 |
|
|
74 |
|
|
75 |
|
75 |
|
76 |
delimiter ;
|
76 |
delimiter ;
|
- |
|
77 |
|
- |
|
78 |
CALL majreferentiel;
|