Subversion Repositories eFlore/Projets.eflore-projets

Compare Revisions

Ignore whitespace Rev 753 → Rev 754

/trunk/scripts/modules/cel/referonosaure.sql
30,3 → 30,15
AND nom_sel_nn = num_nom
);
-- 31739
 
UPDATE tb_cel.cel_obs c, tb_eflore.bdtxa_v1_00 a SET
c.nom_ret = CONCAT(a.nom_sci, ' ', a.auteur),
c.nom_ret_nn = a.num_nom,
c.nt = a.num_tax,
c.famille = a.famille
WHERE (
nom_sel_nn IS NOT NULL AND nom_sel_nn != 0
AND nom_referentiel like 'bdtxa%'
AND nom_sel_nn = num_nom
);
-- 49
/trunk/scripts/modules/cel/maj-201307.sql
New file
0,0 → 1,57
-- 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');
-- UPDATE cel_obs SET nom_referentiel = NULL where nom_referentiel = '';
 
 
/*
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 != '')
)
*/
 
/trunk/scripts/modules/cel/maj-referentiel-201307.sql
New file
0,0 → 1,100
/*
Mise à jour de réferentiels NULL ou vides.
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; -- 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/maj-struct-201307.sql
New file
0,0 → 1,10
-- dépot "cel", r1739
ALTER TABLE tb_cel.cel_obs MODIFY nom_sel VARCHAR(601) NULL DEFAULT NULL;
ALTER TABLE tb_cel.cel_obs MODIFY nom_ret VARCHAR(601) NULL DEFAULT NULL;
 
 
-- dépot "cel", r1739
CREATE INDEX nom_referentiel ON cel_obs (`nom_referentiel`(5));
 
 
-- TODO: créer une clef unique sur (id_utilisateur, ordre)