Subversion Repositories eFlore/Projets.eflore-projets

Compare Revisions

Ignore whitespace Rev 827 → Rev 828

/trunk/scripts/modules/cel/.current
4,3 → 4,5
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`.`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;
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;
 
/* 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`.`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`.`TABLEBDTFX` 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 bdtfx_v1_01, nvjfl_v2007 et bdtxa_v1_00';
COMMENT 'table générée par eflore/projets/scripts/modules/cel/cel_references.sql à partir de TABLEBDTFX, TABLEBDTXA et nvjfl_v2007';
 
-- 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`.`bdtfx_v1_01` b LEFT JOIN T_nvjfl_v2007 n ON (b.num_taxonomique = n.num_taxon ) \
FROM `BASESOURCE`.`TABLEBDTFX` 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`.`bdtxa_v1_00` 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`.`TABLEBDTXA` 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`.`bdtfx_v1_01` UNION ALL \
SELECT COUNT(1) AS a FROM `BASESOURCE`.`bdtxa_v1_00` UNION ALL \
(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`.`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 bdtfx_v1_01 (`nom_sci`(8))
CREATE INDEX i_nom_ret ON `BASESOURCE`.`TABLEBDTFX` (`nom_sci`(8))
2) regarder les num_nom_ret orphelins de taxon en BDTFX:
SELECT * FROM bdtfx_v1_01 WHERE num_nom_retenu = 0; # 3960
SELECT * FROM `BASESOURCE`.`TABLEBDTFX` WHERE num_nom_retenu = 0; # 3960
3) regarder les num_nom_ret orphelins de taxon en BDTXA:
SELECT * FROM bdtxa_v1_00 WHERE num_nom_retenu = 0; # 0
SELECT * FROM `BASESOURCE`.`TABLEBDTXA` 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`.`bdtfx_v1_01` 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`.`TABLEBDTFX` 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`.`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
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
*/
-- 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`.`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 ) );
( 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 ) );
 
-- 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`.`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;
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;
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`.`bdtfx_v1_01` b WHERE nom_sci = _nom
(SELECT "bdtfx", CONCAT(b.nom_sci, ' ', b.auteur), b.num_nom, b.num_taxonomique, b.famille FROM `BASESOURCE`.`TABLEBDTFX` 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`.`bdtxa_v1_00` 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`.`TABLEBDTXA` 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`.`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;
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;
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`.`bdtfx_v1_01` 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`.`TABLEBDTFX` 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`.`bdtxa_v1_00` 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`.`TABLEBDTXA` a WHERE CONCAT(nom_sci, ' ', auteur) = _nom AND auteur != '') AS req;
END
|
 
/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`.`bdtfx_v1_01` b ON (b.nom_sci = c.nom_sel)
FROM `BASEEDIT`.`cel_obs` c INNER JOIN `BASESOURCE`.`TABLEBDTFX` 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`.`bdtfx_v1_01` b ON (b.nom_sci = c.nom_sel)
FROM `BASEEDIT`.`cel_obs` c INNER JOIN `BASESOURCE`.`TABLEBDTFX` 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`.`bdtfx_v1_01` b
FROM `BASEEDIT`.`cel_obs` c, `BASESOURCE`.`TABLEBDTFX` b
WHERE (
b.nom_sci = c.nom_sel
AND nom_sel IS NOT NULL AND nom_sel != ''
/trunk/scripts/modules/cel/Makefile
35,6 → 35,11
# 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)
49,11 → 54,15
@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 ^BASESOURCE .current|cut -d '\`' -f2` \
`grep ^TABLE_BDTFX .current|cut -d '=' -f2` \
`grep ^TABLE_BDTXA .current|cut -d '=' -f2`
@read
 
reset:
61,7 → 70,7
 
 
help:
@echo "make [alterdb=<tb_cel_test>] [sourcedb=<tb_eflore>] [annuairedb=<tela_prod_v4>]"
@echo "make [alterdb=<tb_cel_test>] [sourcedb=<tb_eflore>] [annuairedb=<tela_prod_v4>] [bdtfx=<1_01>] [bdtxa=<1_00>]"
 
 
# 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`.`bdtfx_v1_01` b
FROM `BASEEDIT`.`cel_obs` c, `BASESOURCE`.`TABLEBDTFX` 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`.`bdtfx_v1_01` b SET
UPDATE `BASEEDIT`.`cel_obs` c, `BASESOURCE`.`TABLEBDTFX` 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`.`bdtxa_v1_00` a SET
UPDATE `BASEEDIT`.`cel_obs` c, `BASESOURCE`.`TABLEBDTXA` a SET
c.nom_ret = CONCAT(a.nom_sci, ' ', a.auteur),
c.nom_ret_nn = a.num_nom,
c.nt = a.num_tax,