Subversion Repositories eFlore/Projets.eflore-projets

Rev

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

Rev 739 Rev 741
Line 1... Line 1...
1 
#!/bin/bash
 1 
#!/bin/bash
- 
 
 2 
# cf l'historique SVN pour plus de commentaires
2 
 
 3 
# lancer:
- 
 
 4 
# bash cel_references.sh |mysql --show-warnings tb_eflore
3 
DB='`tb_cel`.`cel_references`';
 5 
DB='`tb_cel`.`cel_references`';
4 
sed -e '1,/^-- DEBUT DE SCRIPT/d' -e "s;@dst;$DB;" "$0"
 6 
sed -e '1,/^-- DEBUT DE SCRIPT/d' -e "s;@dst;$DB;g" "$0"
5 
exit
 7 
exit
Line 6... Line 8...
6 
 
 8 
 
7 
-- DEBUT DE SCRIPT SQL substitué: ne pas retirer, ne pas modifier cette ligne
 9 
-- DEBUT DE SCRIPT SQL substitué: ne pas retirer, ne pas modifier cette ligne
Line -... Line 10...
- 
 
 10 
-- ci dessous ne doit exister que du SQL valide
8 
-- ci dessous ne doit exister que du SQL valide
 11 
 
9 
 
 12 
/*
10 
-- TODO:
 13 
TODO:
11 
-- fix référentiel: suppression n° de version et uniformisation
 - 
 
- 
 
 14 
* 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;
- 
 
 16 
* ajout INDEX nom_referentiel(5) sur cel_obs
12 
-- SELECT DISTINCT nom_referentiel, COUNT(id_observation) AS count FROM cel_obs GROUP BY nom_referentiel ORDER BY count DESC;
 17 
* ajout INDEX catminat_code sur baseflor_v2012_12_31
13 
 
 18 
* ajout INDEX num_taxon sur nva_v2013_06
- 
 
 19 
* 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();
14 
-- fix date: NULL pour les dates dans le futur
 21 
* 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)
- 
 
 23 
 
- 
 
 24 
CREATE INDEX i_nom_referentiel ON tb_cel.cel_obs (`nom_referentiel(5)`);
- 
 
 25 
CREATE INDEX i_catminat_code ON baseflor_v2012_12_31 (`catminat_code`);
Line 15... Line 26...
15 
-- SELECT courriel_utilisateur, id_observation, date_observation FROM cel_obs WHERE date_observation > NOW();
 26 
CREATE INDEX i_num_taxon ON nva_v2013_06 (`num_taxon`);
16 
 
 27 
*/
17 
 
 28 
 
18 
-- malheureusement ceci est impossible en SQL d'où l'utilisation du shell
 29 
-- malheureusement ceci est impossible en SQL d'où l'utilisation du shell-script
Line 19... Line 30...
19 
-- SET @destdb = 'tb_cel';
 30 
-- SET @destdb = 'tb_cel';
Line 27... Line 38...
27 
       -- bdtfx
 38 
       -- bdtfx
28 
       `num_nom` INT(9) NOT NULL DEFAULT '0' COMMENT 'depuis bdtfx',
 39 
       `num_nom` INT(9) NOT NULL DEFAULT '0' COMMENT 'depuis bdtfx',
29 
       `num_nom_retenu` VARCHAR(9) DEFAULT NULL COMMENT 'depuis bdtfx',
 40 
       `num_nom_retenu` VARCHAR(9) DEFAULT NULL COMMENT 'depuis bdtfx',
Line 30... Line 41...
30 
 
 41 
 
31 
       -- bdtfx + nvjfl_v2007 + nva_v2013_06
 42 
       -- bdtfx + nvjfl_v2007 + nva_v2013_06
Line 32... Line 43...
32 
       `num_taxon` int(9) NOT NULL COMMENT 'depuis bdtfx, nvjfl_v2007 et nva_v2013_06 (commun)',
 43 
       `num_taxon` int(9) NOT NULL COMMENT "depuis bdtfx, nvjfl_v2007 et nva_v2013_06 (commun), les noms non-associés ne sont pas intégrés pour l\'instant",
33 
 
 44 
 
34 
       -- bdtfx
 45 
       -- bdtfx
Line 41... Line 52...
41 
       --       `nom_ret` VARCHAR(255) DEFAULT NULL,
 52 
       --       `nom_ret` VARCHAR(255) DEFAULT NULL,
Line 42... Line 53...
42 
 
 53 
 
43 
       -- nvjfl_v2007 (`nom_vernaculaire` text NOT NULL)
 54 
       -- nvjfl_v2007 (`nom_vernaculaire` text NOT NULL)
44 
       -- mais NULL à cause de nva
 55 
       -- mais NULL à cause de nva
45 
       `nom_commun` VARCHAR(60) NULL COMMENT 'nom_vernaculaire pour nvjfl_v2007 et nva_v2013_06',
 - 
 
Line -... Line 56...
- 
 
 56 
       `nom_commun` VARCHAR(60) NULL COMMENT 'nom_vernaculaire pour nvjfl_v2007 et nva_v2013_06',
- 
 
 57 
 
- 
 
 58 
       -- baseflor_v2012_12_31
- 
 
 59 
       `catminat_code` varchar(18) DEFAULT 'inconnu' COMMENT 'depuis baseflor_v2012_12_31',
- 
 
 60 
       `ve_lumiere` int(1) DEFAULT NULL COMMENT 'depuis baseflor_v2012_12_31',
- 
 
 61 
       `ve_temperature` int(1) DEFAULT NULL COMMENT 'depuis baseflor_v2012_12_31',
- 
 
 62 
       `ve_continentalite` int(1) DEFAULT NULL COMMENT 'depuis baseflor_v2012_12_31',
- 
 
 63 
       `ve_humidite_atmos` int(1) DEFAULT NULL COMMENT 'depuis baseflor_v2012_12_31',
- 
 
 64 
       `ve_humidite_edaph` int(2) DEFAULT NULL COMMENT 'depuis baseflor_v2012_12_31',
- 
 
 65 
       `ve_reaction_sol` int(1) DEFAULT NULL COMMENT 'depuis baseflor_v2012_12_31',
- 
 
 66 
       `ve_nutriments_sol` int(1) DEFAULT NULL COMMENT 'depuis baseflor_v2012_12_31',
- 
 
 67 
       `ve_salinite` int(1) DEFAULT NULL COMMENT 'depuis baseflor_v2012_12_31',
- 
 
 68 
       `ve_texture_sol` int(1) DEFAULT NULL COMMENT 'depuis baseflor_v2012_12_31',
- 
 
 69 
       `ve_mat_org_sol` int(1) DEFAULT NULL COMMENT 'depuis baseflor_v2012_12_31',
- 
 
 70 
 
- 
 
 71 
       -- baseveg_v2013_01_09
- 
 
 72 
       `syntaxon` varchar(255) NULL COMMENT 'depuis baseveg_v2013_01_09',
- 
 
 73 
PRIMARY KEY (`referentiel`, `num_nom`),
- 
 
 74 
INDEX (`referentiel`(5)),
46 
PRIMARY KEY (`referentiel`, `num_nom`)
 75 
INDEX (`num_nom`),
47 
 
 76 
INDEX (`num_taxon`)
48 
) ENGINE=MyISAM \
 77 
) ENGINE=MyISAM \
49 
  DEFAULT CHARSET=utf8 \
 - 
 
50 
  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';
 - 
 
51 
 
 - 
 
52 
 
 - 
 
53 
/*
 - 
 
54 
way 1: theory
 - 
 
55 
INSERT INTO @dst (`referentiel`, `num_nom`, `num_nom_retenu`, `nom_sci`, `auteur`, `nom_commun`) \
 - 
 
56 
       SELECT "bdtfx", b.num_nom, b.num_nom_retenu, b.nom_sci, b.auteur, n.nom_vernaculaire, MAX(n.num_statut)	FROM bdtfx_v1_01 b LEFT JOIN nvjfl_v2007 n	ON (b.num_taxonomique = n.num_taxon AND n.code_langue = 'fra' )	GROUP BY b.num_nom \
 - 
 
57 
       UNION \
 - 
 
58 
       SELECT "bdtxa", b.num_nom, b.num_nom_retenu, b.nom_sci, b.auteur, n.nom_vernaculaire, NULL				FROM bdtxa_v1_00 b LEFT JOIN nva_v2013_06 n	ON (b.num_tax = n.num_taxon AND n.code_langue = 'fra' )			GROUP BY b.num_nom;
 - 
 
59 
SELECT "isfan", b.num_nom, b.num_nom_retenu, b.nom_sci, b.auteur, NULL FROM isfan_v2013 b;
 - 
 
60 
*/
 - 
 
61 
 
 - 
 
62 
 
 - 
 
63 
/*
 - 
 
64 
Détermination des nom vernaculaires meilleurs et uniques:
 - 
 
65 
 
 - 
 
66 
way 1:
 - 
 
67 
-- SELECT n.num_taxon, n.nom_vernaculaire, n.num_statut, n2.num_statut FROM nvjfl_v2007 n LEFT JOIN nvjfl_v2007 n2 ON (n.num_taxon = n2.num_taxon) WHERE n.num_taxon < 32 AND n.code_langue = 'fra' GROUP BY n.num_taxon, n.num_statut HAVING n.num_statut = MAX(n2.num_statut) LIMIT 100;
 - 
 
68 
-- 12311
 - 
 
69 
-- # distinct=
 - 
 
70 
 
 - 
 
71 
way 2:
 - 
 
72 
-- SELECT n.num_taxon, n.nom_vernaculaire FROM nvjfl_v2007 n INNER JOIN nvjfl_v2007 n2 ON (n.num_taxon = n2.num_taxon AND n.code_langue = n2.code_langue AND n.num_statut > n2.num_statut) WHERE n.code_langue = 'fra' GROUP BY n.num_taxon;
 - 
 
73 
-- 2680
 - 
 
74 
 
 - 
 
75 
way 2":
 - 
 
Line 76... Line -...
76 
-- SELECT n.num_taxon, n.nom_vernaculaire FROM nvjfl_v2007 n LEFT JOIN nvjfl_v2007 n2 ON (n.num_taxon = n2.num_taxon AND n.code_langue = n2.code_langue AND n.num_statut > n2.num_statut) WHERE n.code_langue = 'fra' AND n2.num_statut IS NOT NULL GROUP BY num_taxon;
 - 
 
77 
-- 2680, Mais problème ensuite: SELECT n.* from cel_references NATURAL JOIN nvjfl_v2007 n WHERE `nom_commun` = '' AND n.code_langue = 'fra';
 78 
  DEFAULT CHARSET=utf8 \
78 
 
 - 
 
79 
 
 - 
 
80 
Note: 16146 nom communs français distincts, 12312 num_taxon fr, aucun num_statut NULL en français
 - 
 
81 
*/
 79 
  COMMENT 'table générée par eflore/projets/scripts/modules/cel/cel_references.sql à partir de bdtfx_v1_02, nvjfl_v2007 et bdtxa_v1_00';
Line 82... Line 80...
82 
 
 80 
 
83 
 
 81 
-- tables temporaires
84 
DROP TEMPORARY TABLE IF EXISTS `T_nvjfl_v2007`, `T_nva_v2013_06`;
 82 
DROP TEMPORARY TABLE IF EXISTS `T_nvjfl_v2007`, `T_nva_v2013_06`, `T_basevegflor`;
Line 85... Line 83...
85 
 
 83 
 
86 
CREATE TEMPORARY TABLE T_nvjfl_v2007 ( INDEX(`num_taxon`) ) AS \
 84 
CREATE TEMPORARY TABLE T_nvjfl_v2007 ( INDEX(`num_taxon`) ) AS \
87 
       -- ( 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) );
 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) );
Line -... Line 86...
- 
 
 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) );
- 
 
 87 
 
- 
 
 88 
-- 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 \
- 
 
 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
- 
 
 91 
 
- 
 
 92 
 
- 
 
 93 
-- JOIN ON num_taxon_originel car INDEX
- 
 
 94 
-- cf: eflore/projets/donnees/baseflor/2012-12-31/baseflor.sql
- 
 
 95 
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, \
- 
 
 97 
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" \
- 
 
 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
- 
 
 100 
 
- 
 
 101 
 
- 
 
 102 
-- INSERTIONS
- 
 
 103 
INSERT INTO @dst (`referentiel`, `num_nom`, `num_nom_retenu`, `num_taxon`, `nom_sci`, `auteur`, `nom_commun`, \
- 
 
 104 
      `catminat_code`, `ve_lumiere`, `ve_temperature`, `ve_continentalite`, `ve_humidite_atmos`, `ve_humidite_edaph`, \
- 
 
 105 
      `ve_reaction_sol`, `ve_nutriments_sol`, `ve_salinite`, `ve_texture_sol`, `ve_mat_org_sol`, `syntaxon`) \
88 
       ( 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) );
 106 
 
89 
 
 107 
       SELECT "bdtfx", b.num_nom, b.num_nom_retenu, b.num_taxonomique, b.nom_sci, b.auteur, n.nom_vernaculaire, \
90 
-- table temporaire uniquement parce qu'il manque un index-key, autrement le LEFT JOIN ci-dessous est bien trop long
 108 
     bf.catminat_code, bf.ve_lumiere, bf.ve_temperature, bf.ve_continentalite, bf.ve_humidite_atmos, bf.ve_humidite_edaph, \
91 
CREATE TEMPORARY TABLE T_nva_v2013_06 ( INDEX(`num_taxon`) ) AS \
 - 
 
Line 92... Line 109...
92 
       ( 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
 109 
     bf.ve_reaction_sol, bf.ve_nutriments_sol, bf.ve_salinite, bf.ve_texture_sol, bf.ve_mat_org_sol, bf.syntaxon