/trunk/scripts/modules/cel/maj-201307.sql |
---|
File deleted |
/trunk/scripts/modules/cel/maj-referentiel-und-201307.sql |
---|
New file |
0,0 → 1,100 |
/* |
Mise à jour de réferentiels NULL ou vides pour les observations orphelines (sans nom_ret_nn) |
2722 observations trouvées au 2013/07/19 |
*/ |
DROP PROCEDURE IF EXISTS getNomSci; |
DROP PROCEDURE IF EXISTS getNomSciCount; |
DROP PROCEDURE IF EXISTS getNomSciAuteur; |
DROP PROCEDURE IF EXISTS getNomSciAuteurCount; |
DROP PROCEDURE IF EXISTS cur; |
delimiter | |
-- obtient le nombre de matches sur nom_sel = nom_sci |
CREATE PROCEDURE getNomSciCount(IN _nom varchar(500), OUT param1 INT) |
BEGIN |
SELECT sum(c) INTO param1 FROM (SELECT count(1) as c FROM tb_eflore.bdtfx_v1_02 b WHERE nom_sci = _nom UNION ALL SELECT count(1) FROM tb_eflore.bdtxa_v1_00 a WHERE nom_sci = _nom) AS req; |
END |
| |
-- retourne les paramètres d'une match |
CREATE PROCEDURE getNomSci(IN _nom varchar(500), OUT param1 char(5), OUT param2 varchar(601), OUT param3 INT, OUT param4 INT, OUT param5 varchar(255)) |
BEGIN |
SELECT * INTO param1, param2, param3, param4, param5 FROM |
(SELECT "bdtfx", CONCAT(b.nom_sci, ' ', b.auteur), b.num_nom, b.num_taxonomique, b.famille FROM tb_eflore.bdtfx_v1_02 b WHERE nom_sci = _nom |
UNION ALL |
SELECT "bdtxa", CONCAT(a.nom_sci, ' ', a.auteur), a.num_nom, a.num_tax, a.famille FROM tb_eflore.bdtxa_v1_00 a WHERE nom_sci = _nom) AS req; |
END |
| |
-- obtient le nombre de matches sur nom_sel = CONCAT(nom_sci, " ", auteur) |
-- quasiment identique à ci-dessus, sauf que nous excluons de la recherche de bdtfx et bdtxa les nom dont le nom d'auteur est '' |
CREATE PROCEDURE getNomSciAuteurCount(IN _nom varchar(500), OUT param1 INT) |
BEGIN |
SELECT sum(c) INTO param1 FROM (SELECT count(1) as c FROM tb_eflore.bdtfx_v1_02 b WHERE CONCAT(nom_sci, ' ', auteur) = _nom UNION ALL SELECT count(1) FROM tb_eflore.bdtxa_v1_00 a WHERE CONCAT(nom_sci, ' ', auteur) = _nom) AS req; |
END |
| |
-- retourne les paramètres d'une match |
CREATE PROCEDURE getNomSciAuteur(IN _nom varchar(500), OUT param1 char(5), OUT param2 varchar(601), OUT param3 INT, OUT param4 INT, OUT param5 varchar(255)) |
BEGIN |
SELECT * INTO param1, param2, param3, param4, param5 FROM |
(SELECT "bdtfx", CONCAT(b.nom_sci, ' ', b.auteur), b.num_nom, b.num_taxonomique, b.famille FROM tb_eflore.bdtfx_v1_02 b WHERE CONCAT(nom_sci, ' ', auteur) = _nom AND auteur != '' |
UNION ALL |
SELECT "bdtxa", CONCAT(a.nom_sci, ' ', a.auteur), a.num_nom, a.num_tax, a.famille FROM tb_eflore.bdtxa_v1_00 a WHERE CONCAT(nom_sci, ' ', auteur) = _nom AND auteur != '') AS req; |
END |
| |
CREATE PROCEDURE cur() |
BEGIN |
DECLARE done INT DEFAULT 0; |
DECLARE subst INT DEFAULT 0; |
DECLARE _id_observation bigint(20) DEFAULT 0; |
DECLARE _nom varchar(255) DEFAULT NULL; |
-- la requête principale de sélection des observations à mettre à jour |
DECLARE cur1 CURSOR FOR SELECT id_observation, nom_sel FROM cel_obs WHERE (nom_referentiel IS NULL OR nom_referentiel = '') AND nom_sel != '' AND nom_sel IS NOT NULL AND nom_ret_nn IS NULL; -- 78149 |
-- 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, _nom; |
CALL getNomSciCount(_nom, @a); |
-- SELECT _id_observation, _nom, @a; |
IF @a = 1 THEN |
CALL getNomSci(_nom, @_ref, @_nom, @_num_nom, @_num_tax, @_famille); |
SELECT "updb: getNomSci", _id_observation, _nom, '=', @_ref, @_nom, @_num_nom, @_num_tax, @_famille; |
UPDATE tb_cel.cel_obs c SET |
c.nom_referentiel = @_ref, |
c.nom_ret = @_nom, |
c.nom_ret_nn = @_num_nom, |
c.nt = @_num_tax, |
c.famille = @_famille |
WHERE id_observation = _id_observation; |
SET subst = subst + 1; |
/* ELSE |
CALL getNomSciAuteurCount(_nom, @a); |
IF @a = 1 THEN |
CALL getNomSciAuteur(_nom, @_ref, @_nom, @_num_nom, @_num_tax, @_famille); |
SELECT "updb: getNomSciAuteur", _id_observation, _nom, '=', @_ref, @_nom, @_num_nom, @_num_tax, @_famille; |
UPDATE tb_cel.cel_obs c SET |
c.nom_referentiel = @_ref, |
c.nom_ret = @_nom, |
c.nom_ret_nn = @_num_nom, |
c.nt = @_num_tax, |
c.famille = @_famille |
WHERE id_observation = _id_observation; |
SET subst = subst + 1; |
END IF;*/ |
END IF; |
UNTIL done END REPEAT; |
select subst AS 'nombre de mises à jour effectuées'; |
CLOSE cur1; |
END |
| |
delimiter ; |
/trunk/scripts/modules/cel/A_LIRE.txt |
---|
1,7 → 1,43 |
Créer une base de données tb_cel avant de lancer les scripts |
== Sommaire == |
1) à propos de la mise à jour de juillet 2013 |
2) à propos de la table cel_references |
============== |
À propos de la table cel_references: celle-ci existe car: |
0) maj-referentiel-201307.sql |
mise à jour des structure de table (les index notamment): |
1) maj-cleanup-201307.sql |
uniformisation des données (lon/lat, date, ...) |
2) TODO: maj-nom-ret.sql |
TODO (pas sur) MAJ du référentiel pour les observation ayant un nom_ret_nn |
3) maj-referentiel-201307.sql |
MAJ du référentiel pour les observation ayant un nom_ret_nn |
4) maj-referentiel-und-201307.sql |
MAJ du référentiel pour les observation n'ayant pas de nom_ret_nn (tentative de détermination par nom) |
=== 2: À propos de la table cel_references === |
Celle-ci existe car: |
* les projets doivent être indépendants (eflore, cel, projets nvjfl, ...) |
* les données nécessaire à l'export et à l'import sont massif |
* or les webservices s'appellent parfois récursivement, sont lents et inadaptés |
/trunk/scripts/modules/cel/maj-referentiel-201307.sql |
---|
1,100 → 1,21 |
/* |
Mise à jour de réferentiels NULL ou vides. |
2722 observations trouvées au 2013/07/19 |
Mise à jour de réferentiels NULL ou vides pour les observations avec nom_ret_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 cel_obs |
WHERE (nom_referentiel IS NULL OR nom_referentiel = '') AND nom_sel != '' AND nom_sel IS NOT NULL AND nom_ret_nn IS NOT NULL; |
*/ |
DROP PROCEDURE IF EXISTS getNomSci; |
DROP PROCEDURE IF EXISTS getNomSciCount; |
DROP PROCEDURE IF EXISTS getNomSciAuteur; |
DROP PROCEDURE IF EXISTS getNomSciAuteurCount; |
DROP PROCEDURE IF EXISTS cur; |
delimiter | |
-- obtient le nombre de matches sur nom_sel = nom_sci |
CREATE PROCEDURE getNomSciCount(IN _nom varchar(500), OUT param1 INT) |
BEGIN |
SELECT sum(c) INTO param1 FROM (SELECT count(1) as c FROM tb_eflore.bdtfx_v1_02 b WHERE nom_sci = _nom UNION ALL SELECT count(1) FROM tb_eflore.bdtxa_v1_00 a WHERE nom_sci = _nom) AS req; |
END |
| |
-- retourne les paramètres d'une match |
CREATE PROCEDURE getNomSci(IN _nom varchar(500), OUT param1 char(5), OUT param2 varchar(601), OUT param3 INT, OUT param4 INT, OUT param5 varchar(255)) |
BEGIN |
SELECT * INTO param1, param2, param3, param4, param5 FROM |
(SELECT "bdtfx", CONCAT(b.nom_sci, ' ', b.auteur), b.num_nom, b.num_taxonomique, b.famille FROM tb_eflore.bdtfx_v1_02 b WHERE nom_sci = _nom |
/* |
TODO: |
SELECT * FROM cel_obs WHERE nom_ret_nn IS NULL; |
CREATE TEMPORARY TABLE T_bis ( INDEX(`nom`)) AS |
(SELECT "bdtfx", CONCAT(b.nom_sci, ' ', b.auteur) AS nom, b.num_nom, b.num_taxonomique, b.famille FROM tb_eflore.bdtfx_v1_02 b |
UNION ALL |
SELECT "bdtxa", CONCAT(a.nom_sci, ' ', a.auteur), a.num_nom, a.num_tax, a.famille FROM tb_eflore.bdtxa_v1_00 a WHERE nom_sci = _nom) AS req; |
END |
| |
SELECT "bdtxa", CONCAT(a.nom_sci, ' ', a.auteur) AS nom, a.num_nom, a.num_tax, a.famille FROM tb_eflore.bdtxa_v1_00 a); |
-- obtient le nombre de matches sur nom_sel = CONCAT(nom_sci, " ", auteur) |
-- quasiment identique à ci-dessus, sauf que nous excluons de la recherche de bdtfx et bdtxa les nom dont le nom d'auteur est '' |
CREATE PROCEDURE getNomSciAuteurCount(IN _nom varchar(500), OUT param1 INT) |
BEGIN |
SELECT sum(c) INTO param1 FROM (SELECT count(1) as c FROM tb_eflore.bdtfx_v1_02 b WHERE CONCAT(nom_sci, ' ', auteur) = _nom UNION ALL SELECT count(1) FROM tb_eflore.bdtxa_v1_00 a WHERE CONCAT(nom_sci, ' ', auteur) = _nom) AS req; |
END |
| |
-- retourne les paramètres d'une match |
CREATE PROCEDURE getNomSciAuteur(IN _nom varchar(500), OUT param1 char(5), OUT param2 varchar(601), OUT param3 INT, OUT param4 INT, OUT param5 varchar(255)) |
BEGIN |
SELECT * INTO param1, param2, param3, param4, param5 FROM |
(SELECT "bdtfx", CONCAT(b.nom_sci, ' ', b.auteur), b.num_nom, b.num_taxonomique, b.famille FROM tb_eflore.bdtfx_v1_02 b WHERE CONCAT(nom_sci, ' ', auteur) = _nom AND auteur != '' |
UNION ALL |
SELECT "bdtxa", CONCAT(a.nom_sci, ' ', a.auteur), a.num_nom, a.num_tax, a.famille FROM tb_eflore.bdtxa_v1_00 a WHERE CONCAT(nom_sci, ' ', auteur) = _nom AND auteur != '') AS req; |
END |
| |
CREATE PROCEDURE cur() |
BEGIN |
DECLARE done INT DEFAULT 0; |
DECLARE subst INT DEFAULT 0; |
DECLARE _id_observation bigint(20) DEFAULT 0; |
DECLARE _nom varchar(255) DEFAULT NULL; |
-- la requête principale de sélection des observations à mettre à jour |
DECLARE cur1 CURSOR FOR SELECT id_observation, nom_sel FROM cel_obs WHERE (nom_referentiel IS NULL OR nom_referentiel = '') AND nom_sel != '' AND nom_sel IS NOT NULL; -- 78149 |
-- 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, _nom; |
CALL getNomSciCount(_nom, @a); |
-- SELECT _id_observation, _nom, @a; |
IF @a = 1 THEN |
CALL getNomSci(_nom, @_ref, @_nom, @_num_nom, @_num_tax, @_famille); |
SELECT "updb: getNomSci", _id_observation, _nom, '=', @_ref, @_nom, @_num_nom, @_num_tax, @_famille; |
UPDATE tb_cel.cel_obs c SET |
c.nom_referentiel = @_ref, |
c.nom_ret = @_nom, |
c.nom_ret_nn = @_num_nom, |
c.nt = @_num_tax, |
c.famille = @_famille |
WHERE id_observation = _id_observation; |
SET subst = subst + 1; |
/* ELSE |
CALL getNomSciAuteurCount(_nom, @a); |
IF @a = 1 THEN |
CALL getNomSciAuteur(_nom, @_ref, @_nom, @_num_nom, @_num_tax, @_famille); |
SELECT "updb: getNomSciAuteur", _id_observation, _nom, '=', @_ref, @_nom, @_num_nom, @_num_tax, @_famille; |
UPDATE tb_cel.cel_obs c SET |
c.nom_referentiel = @_ref, |
c.nom_ret = @_nom, |
c.nom_ret_nn = @_num_nom, |
c.nt = @_num_tax, |
c.famille = @_famille |
WHERE id_observation = _id_observation; |
SET subst = subst + 1; |
END IF;*/ |
END IF; |
UNTIL done END REPEAT; |
select subst AS 'nombre de mises à jour effectuées'; |
CLOSE cur1; |
END |
| |
delimiter ; |
DROP TEMPORARY TABLE IF EXISTS T_bis; |
/trunk/scripts/modules/cel/maj-cleanup-201307.sql |
---|
New file |
0,0 → 1,11 |
-- date d'observation dans le futur |
UPDATE cel_obs SET date_observation = NULL WHERE date_observation > now(); |
-- cleanup |
UPDATE cel_obs SET date_observation = NULL WHERE date_observation = '0000-00-00 00:00:00'; |
-- cleanup |
UPDATE cel_obs SET latitude = NULL, longitude = NULL WHERE longitude = 0 and latitude = 0; |
-- referentiels: 65800 NULL, 13000 '' |
UPDATE cel_obs SET nom_referentiel = SUBSTRING_INDEX(nom_referentiel, ':', 1); |
UPDATE cel_obs SET nom_referentiel = 'bdtfx' WHERE nom_referentiel IN ('bdtfx_v1','bdnff'); |
-- TODO: UPDATE cel_obs SET nom_referentiel = NULL where nom_referentiel = ''; |
/trunk/scripts/modules/cel/maj-nom-ret.sql |
---|
New file |
0,0 → 1,44 |
/* |
Cleanup des observation ayant un nom_ret_nn à 0 (mais un nom_ret défini...): |
En effet, on peut pour l'instant POSTer $nom_ret, d'où bien souvent nom_sel == nom_ret, cependant nom_ret_nn = 0 |
(pas d'autodétection). |
Nous pourrions donc les nullifier sans remord, ... mais ... |
nom_ret == 0 est VALIDE (car bdtfx.num_nom_retenu == 0 est valide) [ 3800 nom_retenu "orphelins" de taxon ] |
1) créer un index pour les jointures: |
CREATE INDEX i_nom_ret ON bdtfx_v1_02 (`nom_sci`(8)) |
2) regarder les num_nom_ret orphelins de taxon en BDTFX: |
SELECT * FROM bdtfx_v1_02 WHERE num_nom_retenu = 0; # 3960 |
3) regarder les num_nom_ret orphelins de taxon en BDTFX: |
SELECT * FROM bdtxa_v1_00 WHERE num_nom_retenu = 0; # 0 |
4) regarder les orphelins équivalents dans cel_obs: |
SELECT date_observation, SUBSTRING(nom_sel, 1, 50), nom_ret_nn, nom_ret, b.nom_sci FROM cel_obs c LEFT JOIN tb_eflore.bdtfx_v1_02 b on (c.nom_ret = b.nom_sci) WHERE nom_ret_nn = 0; # 7740 |
Donc ceux dont le nom_ret à été POSTé manuellement et qui matchent le nom_sci de BDTFX : on les conserve. |
Mais les autres, qui ont un nom_ret probablement erroné et un nom_ret_nn à 0, on NULLify les données censées être (correctement) autogénérées ! |
Cela concerne: |
SELECT date_observation, SUBSTRING(nom_sel, 1, 50), nom_ret_nn, nom_ret, b.nom_sci FROM cel_obs c LEFT JOIN tb_eflore.bdtfx_v1_02 b on (c.nom_ret = b.nom_sci) WHERE nom_ret_nn = 0 |
AND c.nom_ret != '' AND id_observation NOT IN ( SELECT id_observation FROM cel_obs c, tb_eflore.bdtfx_v1_02 b WHERE c.nom_ret = b.nom_sci AND c.nom_ret_nn = 0 ); # 960 |
*/ |
-- D'où la requête : |
UPDATE cel_obs SET nom_sel_nn = NULL, nom_ret = NULL, nom_ret_nn = NULL, nt = NULL, famille = NULL WHERE id_observation IN |
( SELECT id_observation FROM cel_obs c LEFT JOIN tb_eflore.bdtfx_v1_02 b on (c.nom_ret = b.nom_sci) WHERE nom_ret_nn = 0 |
AND c.nom_ret != '' AND id_observation NOT IN ( SELECT id_observation FROM cel_obs c, tb_eflore.bdtfx_v1_02 b WHERE c.nom_ret = b.nom_sci AND c.nom_ret_nn = 0 ) ); |
-- TODO |
-- UPDATE cel_obs SET nom_ret_nn = NULL WHERE nom_ret_nn = 0; |
-- UPDATE cel_obs SET nom_sel_nn = NULL WHERE nom_sel_nn = 0; |
/* |
UPDATE cel_obs SET nom_sel = NULL, nom_sel_nn = NULL, nom_ret = NULL, nom_ret_nn = NULL, nt = NULL, famille = NULL, |
FROM cel_obs |
WHERE (nom_sel = '' OR nom_sel IS NULL) AND |
( |
(nom_ret IS NOT NULL AND nom_ret != '') OR |
(nom_ret_nn IS NOT NULL AND nom_ret_nn != 0 AND nom_ret_nn != '') OR |
(nt IS NOT NULL AND nt != 0 AND nt != '') OR |
(famille IS NOT NULL AND famille != '') |
) |
*/ |