Subversion Repositories eFlore/Projets.eflore-projets

Rev

Rev 760 | Rev 818 | Go to most recent revision | Show entire file | Ignore whitespace | Details | Blame | Last modification | View Log | RSS feed

Rev 760 Rev 816
Line 1... Line -...
1 
#!/bin/bash
 - 
 
2 
# cf l'historique SVN pour plus de commentaires
 - 
 
3 
# lancer:
 - 
 
4 
# bash cel_references.sh |mysql --show-warnings tb_eflore
 - 
 
5 
DB='`tb_cel`.`cel_references`';
 - 
 
6 
sed -e '1,/^-- DEBUT DE SCRIPT/d' -e "s;@dst;$DB;g" "$0"
 - 
 
7 
exit
 - 
 
8 
 
 - 
 
9 
-- DEBUT DE SCRIPT SQL substitué: ne pas retirer, ne pas modifier cette ligne
 - 
 
10 
-- ci dessous ne doit exister que du SQL valide
 - 
 
11 
 
 - 
 
12 
/*
 1 
/*
13 
TODO:
 2 
TODO:
14 
* fix référentiel: suppression n° de version et uniformisation
 3 
* fix référentiel: suppression n° de version et uniformisation
15 
SELECT DISTINCT nom_referentiel, COUNT(id_observation) AS count FROM cel_obs GROUP BY nom_referentiel ORDER BY count DESC;
 4 
SELECT DISTINCT nom_referentiel, COUNT(id_observation) AS count FROM `BASEEDIT`.`cel_obs` GROUP BY nom_referentiel ORDER BY count DESC;
16 
* ajout INDEX nom_referentiel(5) sur cel_obs
 5 
* ajout INDEX nom_referentiel(5) sur `BASEEDIT`.`cel_obs`
17 
* ajout INDEX catminat_code sur baseflor_v2012_12_31
 6 
* ajout INDEX catminat_code sur baseflor_v2012_12_31
18 
* ajout INDEX num_taxon sur nva_v2013_06
 7 
* ajout INDEX num_taxon sur nva_v2013_06
19 
* fix date: set NULL pour les dates dans le futur
 8 
* fix date: set NULL pour les dates dans le futur
20 
SELECT courriel_utilisateur, id_observation, date_observation FROM cel_obs WHERE date_observation > NOW();
 9 
SELECT courriel_utilisateur, id_observation, date_observation FROM `BASEEDIT`.`cel_obs` WHERE date_observation > NOW();
21 
* intégrer les noms non-associés à un taxon (bdtfx where num_taxonomique = '')
 10 
* intégrer les noms non-associés à un taxon (bdtfx where num_taxonomique = '')
22 
* intégrer les noms non-associés à un taxon (bdtxa where num_tax = '' || num_tax IS NULL)
 11 
* intégrer les noms non-associés à un taxon (bdtxa where num_tax = '' || num_tax IS NULL)
Line 23... Line 12...
23 
 
 12 
 
24 
CREATE INDEX i_nom_referentiel ON tb_cel.cel_obs (`nom_referentiel`(5));
 13 
CREATE INDEX i_nom_referentiel ON `BASEEDIT`.`cel_obs` (`nom_referentiel`(5));
25 
CREATE INDEX i_catminat_code ON baseflor_v2012_12_31 (`catminat_code`);
 14 
CREATE INDEX i_catminat_code ON baseflor_v2012_12_31 (`catminat_code`);
26 
CREATE INDEX i_num_taxon ON nva_v2013_06 (`num_taxon`);
 15 
CREATE INDEX i_num_taxon ON nva_v2013_06 (`num_taxon`);
Line 27... Line 16...
27 
*/
 16 
*/
28 
 
 17 
 
29 
-- malheureusement ceci est impossible en SQL d'où l'utilisation du shell-script
 18 
-- malheureusement ceci est impossible en SQL d'où l'utilisation du shell-script
- 
 
 19 
-- SET @destdb = 'tb_cel';
30 
-- SET @destdb = 'tb_cel';
 20 
-- SET @desttable = 'cel_references';
Line 31... Line 21...
31 
-- SET @desttable = 'cel_references';
 21 
-- -- SET BASEEDIT = `tb_cel`;
32 
-- SET @dst = CONCAT('`',@destdb,'`','.`',@desttable,'`');
 22 
-- SET @dst = CONCAT('`',@destdb,'`','.`',@desttable,'`');
33 
 
 23 
 
Line 34... Line 24...
34 
DROP TABLE IF EXISTS @dst;
 24 
DROP TABLE IF EXISTS `BASEEDIT`.`cel_references`;
35 
CREATE TABLE IF NOT EXISTS @dst (
 25 
CREATE TABLE IF NOT EXISTS `BASEEDIT`.`cel_references` (
36 
       `referentiel` CHAR(5) NOT NULL COMMENT 'eg: "bdtfx", "bdtfx", "bdtxa", ... No ENUM!',
 26 
       `referentiel` CHAR(5) NOT NULL COMMENT 'eg: "bdtfx", "bdtfx", "bdtxa", ... No ENUM!',
Line 45... Line 35...
45 
       -- bdtfx
 35 
       -- bdtfx
46 
       `nom_sci` VARCHAR(500) NOT NULL COMMENT 'depuis bdtfx',
 36 
       `nom_sci` VARCHAR(500) NOT NULL COMMENT 'depuis bdtfx',
47 
       `auteur` VARCHAR(100) DEFAULT NULL COMMENT 'depuis bdtfx',
 37 
       `auteur` VARCHAR(100) DEFAULT NULL COMMENT 'depuis bdtfx',
Line 48... Line 38...
48 
 
 38 
 
49 
 
 39 
 
50 
       -- cel_obs
 40 
       -- `BASEEDIT`.`cel_obs`
Line 51... Line 41...
51 
       --       `nom_ret_nn` DECIMAL(9,0) DEFAULT NULL COMMENT 'Numéro du nom retenu.',
 41 
       --       `nom_ret_nn` DECIMAL(9,0) DEFAULT NULL COMMENT 'Numéro du nom retenu.',
52 
       --       `nom_ret` VARCHAR(255) DEFAULT NULL,
 42 
       --       `nom_ret` VARCHAR(255) DEFAULT NULL,
Line 80... Line 70...
80 
 
 70 
 
81 
-- tables temporaires
 71 
-- tables temporaires
Line 82... Line 72...
82 
DROP TEMPORARY TABLE IF EXISTS `T_nvjfl_v2007`, `T_nva_v2013_06`, `T_basevegflor`;
 72 
DROP TEMPORARY TABLE IF EXISTS `T_nvjfl_v2007`, `T_nva_v2013_06`, `T_basevegflor`;
83 
 
 73 
 
84 
CREATE TEMPORARY TABLE T_nvjfl_v2007 ( INDEX(`num_taxon`) ) AS \
 74 
CREATE TEMPORARY TABLE T_nvjfl_v2007 ( INDEX(`num_taxon`) ) AS \
Line 85... Line 75...
85 
       -- ( SELECT n.num_taxon, n.nom_vernaculaire FROM nvjfl_v2007 n WHERE n.code_langue = 'fra' GROUP BY n.num_taxon, n.num_statut HAVING n.num_statut = MAX(n.num_statut) );
 75 
       -- ( SELECT n.num_taxon, n.nom_vernaculaire FROM `BASESOURCE`.`nvjfl_v2007` n WHERE n.code_langue = 'fra' GROUP BY n.num_taxon, n.num_statut HAVING n.num_statut = MAX(n.num_statut) );
86 
       ( SELECT n.num_taxon, n.nom_vernaculaire, n.num_statut as void, MAX(n.num_statut) as void2 FROM nvjfl_v2007 n WHERE n.code_langue = 'fra' GROUP BY n.num_taxon HAVING n.num_statut = MAX(n.num_statut) );
 76 
       ( SELECT n.num_taxon, n.nom_vernaculaire, n.num_statut as void, MAX(n.num_statut) as void2 FROM `BASESOURCE`.`nvjfl_v2007` n WHERE n.code_langue = 'fra' GROUP BY n.num_taxon HAVING n.num_statut = MAX(n.num_statut) );
87 
 
 77 
 
Line 88... Line 78...
88 
-- table temporaire uniquement parce qu'il manque un index-key, autrement le LEFT JOIN ci-dessous est bien trop long
 78 
-- table temporaire uniquement parce qu'il manque un index-key, autrement le LEFT JOIN ci-dessous est bien trop long
89 
CREATE TEMPORARY TABLE T_nva_v2013_06 ( INDEX(`num_taxon`) ) AS \
 79 
CREATE TEMPORARY TABLE T_nva_v2013_06 ( INDEX(`num_taxon`) ) AS \
90 
       ( SELECT n.num_taxon, n.nom_vernaculaire FROM nva_v2013_06 n WHERE n.code_langue = 'fra' /* DB pb */ AND n.num_taxon IS NOT NULL /* /DB pb */ GROUP BY n.num_nom); -- aggrégat arbitraire car pas de num_statut
 80 
       ( SELECT n.num_taxon, n.nom_vernaculaire FROM `BASESOURCE`.`nva_v2013_06` n WHERE n.code_langue = 'fra' /* DB pb */ AND n.num_taxon IS NOT NULL /* /DB pb */ GROUP BY n.num_nom); -- aggrégat arbitraire car pas de num_statut
91 
 
 81 
 
92 
 
 82 
 
93 
-- JOIN ON num_taxon_originel car INDEX
 83 
-- JOIN ON num_taxon_originel car INDEX
94 
-- cf: eflore/projets/donnees/baseflor/2012-12-31/baseflor.sql
 84 
-- cf: eflore/projets/donnees/baseflor/2012-12-31/baseflor.sql
Line 95... Line 85...
95 
CREATE TEMPORARY TABLE T_basevegflor ( INDEX(`num_nomen`), INDEX(`num_taxon`) ) AS \
 85 
CREATE TEMPORARY TABLE T_basevegflor ( INDEX(`num_nomen`), INDEX(`num_taxon`) ) AS \
96 
SELECT f.num_nomen, f.num_taxon, f.catminat_code, f.ve_lumiere, f.ve_temperature, f.ve_continentalite, f.ve_humidite_atmos, f.ve_humidite_edaph, f.ve_reaction_sol, f.ve_nutriments_sol, f.ve_salinite, f.ve_texture_sol, f.ve_mat_org_sol, \
 86 
SELECT f.num_nomen, f.num_taxon, f.catminat_code, f.ve_lumiere, f.ve_temperature, f.ve_continentalite, f.ve_humidite_atmos, f.ve_humidite_edaph, f.ve_reaction_sol, f.ve_nutriments_sol, f.ve_salinite, f.ve_texture_sol, f.ve_mat_org_sol, \
97 
v.syntaxon \
 87 
v.syntaxon \
98 
FROM baseflor_v2012_12_31 f LEFT JOIN baseveg_v2013_01_09 v ON (f.catminat_code = v.code_catminat AND v.niveau = 'ALL' AND v.syntaxon IS NOT NULL) WHERE f.BDNT = "BDTFX" \
 88 
FROM `BASESOURCE`.`baseflor_v2012_12_31` f LEFT JOIN `BASESOURCE`.`baseveg_v2013_01_09` v ON (f.catminat_code = v.code_catminat AND v.niveau = 'ALL' AND v.syntaxon IS NOT NULL) WHERE f.BDNT = "BDTFX" \
99 
GROUP BY f.num_nomen, f.num_taxon; -- group by car plusieurs couple (f.num_nomen, f.num_taxon) peuvent exister dans baseveg_v2013_01_09 or num_nom est PRIMARY dans cel_references
 89 
GROUP BY f.num_nomen, f.num_taxon; -- group by car plusieurs couple (f.num_nomen, f.num_taxon) peuvent exister dans baseveg_v2013_01_09 or num_nom est PRIMARY dans cel_references
Line 100... Line 90...
100 
 
 90 
 
101 
 
 91 
 
102 
-- INSERTIONS
 92 
-- INSERTIONS
103 
-- pour le futur: attention au numéro taxonomique à 0 (WHERE b.num_taxonomique != '')
 93 
-- pour le futur: attention au numéro taxonomique à 0 (WHERE b.num_taxonomique != '')
104 
INSERT INTO @dst (`referentiel`, `num_nom`, `num_nom_retenu`, `num_taxon`, `nom_sci`, `auteur`, `nom_commun`, \
 94 
INSERT INTO `BASEEDIT`.`cel_references` (`referentiel`, `num_nom`, `num_nom_retenu`, `num_taxon`, `nom_sci`, `auteur`, `nom_commun`, \
Line 105... Line 95...
105 
      `catminat_code`, `ve_lumiere`, `ve_temperature`, `ve_continentalite`, `ve_humidite_atmos`, `ve_humidite_edaph`, \
 95 
      `catminat_code`, `ve_lumiere`, `ve_temperature`, `ve_continentalite`, `ve_humidite_atmos`, `ve_humidite_edaph`, \
106 
      `ve_reaction_sol`, `ve_nutriments_sol`, `ve_salinite`, `ve_texture_sol`, `ve_mat_org_sol`, `syntaxon`) \
 96 
      `ve_reaction_sol`, `ve_nutriments_sol`, `ve_salinite`, `ve_texture_sol`, `ve_mat_org_sol`, `syntaxon`) \
107 
 
 97 
 
Line 108... Line 98...
108 
       SELECT "bdtfx", b.num_nom, b.num_nom_retenu, b.num_taxonomique, b.nom_sci, b.auteur, n.nom_vernaculaire, \
 98 
       SELECT "bdtfx", b.num_nom, b.num_nom_retenu, b.num_taxonomique, b.nom_sci, b.auteur, n.nom_vernaculaire, \
109 
     bf.catminat_code, bf.ve_lumiere, bf.ve_temperature, bf.ve_continentalite, bf.ve_humidite_atmos, bf.ve_humidite_edaph, \
 99 
     bf.catminat_code, bf.ve_lumiere, bf.ve_temperature, bf.ve_continentalite, bf.ve_humidite_atmos, bf.ve_humidite_edaph, \
Line 110... Line 100...
110 
     bf.ve_reaction_sol, bf.ve_nutriments_sol, bf.ve_salinite, bf.ve_texture_sol, bf.ve_mat_org_sol, bf.syntaxon
 100 
     bf.ve_reaction_sol, bf.ve_nutriments_sol, bf.ve_salinite, bf.ve_texture_sol, bf.ve_mat_org_sol, bf.syntaxon
Line 111... Line 101...
111 
  FROM bdtfx_v1_01 b LEFT JOIN T_nvjfl_v2007 n ON (b.num_taxonomique = n.num_taxon ) \
 101 
  FROM `BASESOURCE`.`bdtfx_v1_01` b LEFT JOIN T_nvjfl_v2007 n ON (b.num_taxonomique = n.num_taxon ) \
112 
  LEFT JOIN T_basevegflor bf ON (b.num_taxonomique = bf.num_taxon AND b.num_nom = bf.num_nomen);
 102 
  LEFT JOIN T_basevegflor bf ON (b.num_taxonomique = bf.num_taxon AND b.num_nom = bf.num_nomen);
113 
 
 103 
 
114 
 
 104 
 
115 
-- pour le futur: attention au numéro taxonomique à 0 (WHERE b.num_tax IS NOT NULL AND b.num_tax != '')
 105 
-- pour le futur: attention au numéro taxonomique à 0 (WHERE b.num_tax IS NOT NULL AND b.num_tax != '')
116 
INSERT INTO @dst (`referentiel`, `num_nom`, `num_nom_retenu`, `num_taxon`, `nom_sci`, `auteur`, `nom_commun`) \
 106 
INSERT INTO `BASEEDIT`.`cel_references` (`referentiel`, `num_nom`, `num_nom_retenu`, `num_taxon`, `nom_sci`, `auteur`, `nom_commun`) \
117 
       SELECT "bdtxa", b.num_nom, b.num_nom_retenu, b.num_tax, b.nom_sci, b.auteur, n.nom_vernaculaire FROM bdtxa_v1_00 b LEFT JOIN T_nva_v2013_06 n ON (b.num_tax = n.num_taxon);
 107 
       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);
118 
 
 -