/trunk/scripts/modules/cel/redempteur.sql |
---|
42,7 → 42,7 |
Et effectuons une jointure sur bdtfx: |
SELECT id_observation, nom_sel, b.num_nom, b.famille |
FROM `BASEEDIT`.`cel_obs` c INNER JOIN `BASESOURCE`.`TABLEBDTFX` b ON (b.nom_sci = c.nom_sel) |
FROM `BASEEDIT`.`cel_obs` c INNER JOIN `BASESOURCE`.`bdtfx_v1_01` b ON (b.nom_sci = c.nom_sel) |
WHERE ( |
nom_sel IS NOT NULL AND nom_sel != '' |
AND ( |
68,7 → 68,7 |
Nous obtenons donc ainsi les 69 observations à mettre à jour: |
SELECT id_observation, nom_sel, nom_ret, nom_ret_nn, nt, c.famille, b.num_nom, b.nom_sci, b.num_taxonomique, b.famille |
FROM `BASEEDIT`.`cel_obs` c INNER JOIN `BASESOURCE`.`TABLEBDTFX` b ON (b.nom_sci = c.nom_sel) |
FROM `BASEEDIT`.`cel_obs` c INNER JOIN `BASESOURCE`.`bdtfx_v1_01` b ON (b.nom_sci = c.nom_sel) |
WHERE ( |
nom_sel IS NOT NULL AND nom_sel != '' |
AND ( |
91,7 → 91,7 |
CREATE TEMPORARY TABLE T_bis ( INDEX(`id_observation`)) AS |
SELECT id_observation, b.num_nom, CONCAT(b.nom_sci, ' ', b.auteur), b.num_taxonomique, b.famille |
FROM `BASEEDIT`.`cel_obs` c, `BASESOURCE`.`TABLEBDTFX` b |
FROM `BASEEDIT`.`cel_obs` c, `BASESOURCE`.`bdtfx_v1_01` b |
WHERE ( |
b.nom_sci = c.nom_sel |
AND nom_sel IS NOT NULL AND nom_sel != '' |
/trunk/scripts/modules/cel/Makefile |
---|
35,11 → 35,6 |
# utilisée pour initialiser cel_utilisateurs dans maj-struct-201307.sql |
annuairedb ?= tela_prod_v4 |
bdtfx ?= 1_01 |
bdtxa ?= 1_00 |
bdtfx_table = bdtfx_v$(bdtfx) |
bdtxa_table = bdtfx_v$(bdtxa) |
# macro utilisable pour les targets nécessitant de tester la présence d'un couple (base,table) |
# exemples: |
# * $(call is_table,tb_eflore,bdtfx_v1_01) |
54,15 → 49,11 |
@sed -i -e 's/`BASEEDIT`/`$(alterdb)`/g' \ |
-e 's/`BASEANNUAIRE`/`$(annuairedb)`/g' \ |
-e 's/`BASESOURCE`/`$(sourcedb)`/g' \ |
-e 's/`TABLEBDTFX`/`$(bdtfx_table)`/g' \ |
-e 's/`TABLEBDTXA`/`$(bdtxa_table)`/g' \ |
$(fichiers) |
@printf "Attention: les changements s'appliqueront sur la base \"%s\"\nLes sources utilisées seront: annuaire=\"%s\" , sources=\"%s\"\n(Ctrl+C pour interrompre, Enter pour continuer)\n" \ |
`grep ^BASEEDIT .current|cut -d '\`' -f2` \ |
`grep ^BASEANNUAIRE .current|cut -d '\`' -f2` \ |
`grep ^BASESOURCE .current|cut -d '\`' -f2` \ |
`grep ^TABLE_BDTFX .current|cut -d '=' -f2` \ |
`grep ^TABLE_BDTXA .current|cut -d '=' -f2` |
`grep ^BASESOURCE .current|cut -d '\`' -f2` |
@read |
reset: |
70,7 → 61,7 |
help: |
@echo "make [alterdb=<tb_cel_test>] [sourcedb=<tb_eflore>] [annuairedb=<tela_prod_v4>] [bdtfx=<1_01>] [bdtxa=<1_00>]" |
@echo "make [alterdb=<tb_cel_test>] [sourcedb=<tb_eflore>] [annuairedb=<tela_prod_v4>]" |
# mise à jour de juillet/août 2013 |
/trunk/scripts/modules/cel/referonosaure.sql |
---|
9,7 → 9,7 |
/* |
-- SELECT id_observation, b.num_nom, CONCAT(b.nom_sci, ' ', b.auteur), b.num_taxonomique, b.famille |
SELECT id_observation, nom_ret, nom_ret_nn, nt, c.famille |
FROM `BASEEDIT`.`cel_obs` c, `BASESOURCE`.`TABLEBDTFX` b |
FROM `BASEEDIT`.`cel_obs` c, `BASESOURCE`.`bdtfx_v1_01` b |
WHERE ( |
nom_sel_nn IS NOT NULL AND nom_sel_nn != 0 |
AND nom_referentiel like 'bdtfx%' |
19,7 → 19,7 |
*/ |
--- l'update --- |
UPDATE `BASEEDIT`.`cel_obs` c, `BASESOURCE`.`TABLEBDTFX` b SET |
UPDATE `BASEEDIT`.`cel_obs` c, `BASESOURCE`.`bdtfx_v1_01` b SET |
c.nom_ret = CONCAT(b.nom_sci, ' ', b.auteur), |
c.nom_ret_nn = b.num_nom, |
c.nt = b.num_taxonomique, |
35,7 → 35,7 |
-- 31524 avec les restrictions sur famille et SUBSTRING_INDEX() |
UPDATE `BASEEDIT`.`cel_obs` c, `BASESOURCE`.`TABLEBDTXA` a SET |
UPDATE `BASEEDIT`.`cel_obs` c, `BASESOURCE`.`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, |
/trunk/scripts/modules/cel/.current |
---|
4,5 → 4,3 |
BASEEDIT=`BASEEDIT` |
BASEANNUAIRE=`BASEANNUAIRE` |
BASESOURCE=`BASESOURCE` |
TABLE_BDTFX=TABLEBDTFX |
TABLE_BDTXA=TABLEBDTXA |
/trunk/scripts/modules/cel/maj-referentiel-201307.sql |
---|
21,12 → 21,12 |
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; |
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 `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; |
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 `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; |
*/ |
/trunk/scripts/modules/cel/cel_references.sql |
---|
66,7 → 66,7 |
INDEX (`num_taxon`) |
) ENGINE=MyISAM \ |
DEFAULT CHARSET=utf8 \ |
COMMENT 'table générée par eflore/projets/scripts/modules/cel/cel_references.sql à partir de TABLEBDTFX, TABLEBDTXA et nvjfl_v2007'; |
COMMENT 'table générée par eflore/projets/scripts/modules/cel/cel_references.sql à partir de bdtfx_v1_01, nvjfl_v2007 et bdtxa_v1_00'; |
-- tables temporaires |
DROP TEMPORARY TABLE IF EXISTS `T_nvjfl_v2007`, `T_nva_v2013_06`, `T_basevegflor`; |
98,13 → 98,13 |
SELECT "bdtfx", b.num_nom, b.num_nom_retenu, b.num_taxonomique, b.nom_sci, b.auteur, n.nom_vernaculaire, \ |
bf.catminat_code, bf.ve_lumiere, bf.ve_temperature, bf.ve_continentalite, bf.ve_humidite_atmos, bf.ve_humidite_edaph, \ |
bf.ve_reaction_sol, bf.ve_nutriments_sol, bf.ve_salinite, bf.ve_texture_sol, bf.ve_mat_org_sol, bf.syntaxon |
FROM `BASESOURCE`.`TABLEBDTFX` b LEFT JOIN T_nvjfl_v2007 n ON (b.num_taxonomique = n.num_taxon ) \ |
FROM `BASESOURCE`.`bdtfx_v1_01` b LEFT JOIN T_nvjfl_v2007 n ON (b.num_taxonomique = n.num_taxon ) \ |
LEFT JOIN T_basevegflor bf ON (b.num_taxonomique = bf.num_taxon AND b.num_nom = bf.num_nomen); |
-- pour le futur: attention au numéro taxonomique à 0 (WHERE b.num_tax IS NOT NULL AND b.num_tax != '') |
INSERT INTO `BASEEDIT`.`cel_references` (`referentiel`, `num_nom`, `num_nom_retenu`, `num_taxon`, `nom_sci`, `auteur`, `nom_commun`) \ |
SELECT "bdtxa", b.num_nom, b.num_nom_retenu, b.num_tax, b.nom_sci, b.auteur, n.nom_vernaculaire FROM `BASESOURCE`.`TABLEBDTXA` b LEFT JOIN T_nva_v2013_06 n ON (b.num_tax = n.num_taxon); |
SELECT "bdtxa", b.num_nom, b.num_nom_retenu, b.num_tax, b.nom_sci, b.auteur, n.nom_vernaculaire FROM `BASESOURCE`.`bdtxa_v1_00` b LEFT JOIN T_nva_v2013_06 n ON (b.num_tax = n.num_taxon); |
INSERT INTO `BASEEDIT`.`cel_references` (`referentiel`, `num_nom`, `num_nom_retenu`, `num_taxon`, `nom_sci`, `auteur`) \ |
116,8 → 116,8 |
DROP TEMPORARY TABLE IF EXISTS `T_nvjfl_v2007`, `T_nva_v2013_06`, `T_basevegflor`; |
SELECT SUM(theorie.a) AS théorie, pratique.a AS total FROM \ |
(SELECT COUNT(1) AS a FROM `BASESOURCE`.`TABLEBDTFX` UNION ALL \ |
SELECT COUNT(1) AS a FROM `BASESOURCE`.`TABLEBDTXA` UNION ALL \ |
(SELECT COUNT(1) AS a FROM `BASESOURCE`.`bdtfx_v1_01` UNION ALL \ |
SELECT COUNT(1) AS a FROM `BASESOURCE`.`bdtxa_v1_00` UNION ALL \ |
SELECT COUNT(1) AS a FROM `BASESOURCE`.`isfan_v2013` \ |
) AS theorie, \ |
(SELECT COUNT(1) AS a FROM tb_cel.cel_references) AS pratique; |
/trunk/scripts/modules/cel/maj-nom-ret.sql |
---|
6,25 → 6,25 |
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 `BASESOURCE`.`TABLEBDTFX` (`nom_sci`(8)) |
CREATE INDEX i_nom_ret ON bdtfx_v1_01 (`nom_sci`(8)) |
2) regarder les num_nom_ret orphelins de taxon en BDTFX: |
SELECT * FROM `BASESOURCE`.`TABLEBDTFX` WHERE num_nom_retenu = 0; # 3960 |
SELECT * FROM bdtfx_v1_01 WHERE num_nom_retenu = 0; # 3960 |
3) regarder les num_nom_ret orphelins de taxon en BDTXA: |
SELECT * FROM `BASESOURCE`.`TABLEBDTXA` WHERE num_nom_retenu = 0; # 0 |
SELECT * FROM bdtxa_v1_00 WHERE num_nom_retenu = 0; # 0 |
4) regarder les orphelins équivalents dans `BASEEDIT`.`cel_obs`: |
SELECT date_observation, SUBSTRING(nom_sel, 1, 50), nom_ret_nn, nom_ret, b.nom_sci FROM `BASEEDIT`.`cel_obs` c LEFT JOIN `BASESOURCE`.`TABLEBDTFX` b on (c.nom_ret = b.nom_sci) WHERE nom_ret_nn = 0; # 7740 |
SELECT date_observation, SUBSTRING(nom_sel, 1, 50), nom_ret_nn, nom_ret, b.nom_sci FROM `BASEEDIT`.`cel_obs` c LEFT JOIN `BASESOURCE`.`bdtfx_v1_01` 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 `BASEEDIT`.`cel_obs` c LEFT JOIN `BASESOURCE`.`TABLEBDTFX` 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 `BASEEDIT`.`cel_obs` c, `BASESOURCE`.`TABLEBDTFX` b WHERE c.nom_ret = b.nom_sci AND c.nom_ret_nn = 0 ); # 960 |
SELECT date_observation, SUBSTRING(nom_sel, 1, 50), nom_ret_nn, nom_ret, b.nom_sci FROM `BASEEDIT`.`cel_obs` c LEFT JOIN `BASESOURCE`.`bdtfx_v1_01` 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 `BASEEDIT`.`cel_obs` c, `BASESOURCE`.`bdtfx_v1_01` b WHERE c.nom_ret = b.nom_sci AND c.nom_ret_nn = 0 ); # 960 |
*/ |
-- D'où la requête : |
UPDATE `BASEEDIT`.`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 `BASEEDIT`.`cel_obs` c LEFT JOIN `BASESOURCE`.`TABLEBDTFX` 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 `BASEEDIT`.`cel_obs` c, `BASESOURCE`.`TABLEBDTFX` b WHERE c.nom_ret = b.nom_sci AND c.nom_ret_nn = 0 ) ); |
( SELECT id_observation FROM `BASEEDIT`.`cel_obs` c LEFT JOIN `BASESOURCE`.`bdtfx_v1_01` 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 `BASEEDIT`.`cel_obs` c, `BASESOURCE`.`bdtfx_v1_01` b WHERE c.nom_ret = b.nom_sci AND c.nom_ret_nn = 0 ) ); |
-- TODO |
-- UPDATE `BASEEDIT`.`cel_obs` SET nom_ret_nn = NULL WHERE nom_ret_nn = 0; |
/trunk/scripts/modules/cel/maj-referentiel-und-201307.sql |
---|
14,7 → 14,7 |
-- 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 `BASESOURCE`.`TABLEBDTFX` b WHERE nom_sci = _nom UNION ALL SELECT count(1) FROM `BASESOURCE`.`TABLEBDTXA` a WHERE nom_sci = _nom) AS req; |
SELECT sum(c) INTO param1 FROM (SELECT count(1) as c FROM `BASESOURCE`.`bdtfx_v1_01` b WHERE nom_sci = _nom UNION ALL SELECT count(1) FROM `BASESOURCE`.`bdtxa_v1_00` a WHERE nom_sci = _nom) AS req; |
END |
| |
-- retourne les paramètres d'une match |
21,9 → 21,9 |
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 `BASESOURCE`.`TABLEBDTFX` b WHERE nom_sci = _nom |
(SELECT "bdtfx", CONCAT(b.nom_sci, ' ', b.auteur), b.num_nom, b.num_taxonomique, b.famille FROM `BASESOURCE`.`bdtfx_v1_01` b WHERE nom_sci = _nom |
UNION ALL |
SELECT "bdtxa", CONCAT(a.nom_sci, ' ', a.auteur), a.num_nom, a.num_tax, a.famille FROM `BASESOURCE`.`TABLEBDTXA` a WHERE nom_sci = _nom) AS req; |
SELECT "bdtxa", CONCAT(a.nom_sci, ' ', a.auteur), a.num_nom, a.num_tax, a.famille FROM `BASESOURCE`.`bdtxa_v1_00` a WHERE nom_sci = _nom) AS req; |
END |
| |
31,7 → 31,7 |
-- 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 `BASESOURCE`.`TABLEBDTFX` b WHERE CONCAT(nom_sci, ' ', auteur) = _nom UNION ALL SELECT count(1) FROM `BASESOURCE`.`TABLEBDTXA` a WHERE CONCAT(nom_sci, ' ', auteur) = _nom) AS req; |
SELECT sum(c) INTO param1 FROM (SELECT count(1) as c FROM `BASESOURCE`.`bdtfx_v1_01` b WHERE CONCAT(nom_sci, ' ', auteur) = _nom UNION ALL SELECT count(1) FROM `BASESOURCE`.`bdtxa_v1_00` a WHERE CONCAT(nom_sci, ' ', auteur) = _nom) AS req; |
END |
| |
-- retourne les paramètres d'une match |
38,9 → 38,9 |
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 `BASESOURCE`.`TABLEBDTFX` b WHERE CONCAT(nom_sci, ' ', auteur) = _nom AND auteur != '' |
(SELECT "bdtfx", CONCAT(b.nom_sci, ' ', b.auteur), b.num_nom, b.num_taxonomique, b.famille FROM `BASESOURCE`.`bdtfx_v1_01` 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 `BASESOURCE`.`TABLEBDTXA` a WHERE CONCAT(nom_sci, ' ', auteur) = _nom AND auteur != '') AS req; |
SELECT "bdtxa", CONCAT(a.nom_sci, ' ', a.auteur), a.num_nom, a.num_tax, a.famille FROM `BASESOURCE`.`bdtxa_v1_00` a WHERE CONCAT(nom_sci, ' ', auteur) = _nom AND auteur != '') AS req; |
END |
| |