Subversion Repositories eFlore/Projets.eflore-projets

Rev

Details | Last modification | View Log | RSS feed

Rev Author Line No. Line
739 raphael 1
#!/bin/bash
741 raphael 2
# cf l'historique SVN pour plus de commentaires
3
# lancer:
4
# bash cel_references.sh |mysql --show-warnings tb_eflore
739 raphael 5
DB='`tb_cel`.`cel_references`';
741 raphael 6
sed -e '1,/^-- DEBUT DE SCRIPT/d' -e "s;@dst;$DB;g" "$0"
739 raphael 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
 
741 raphael 12
/*
13
TODO:
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
17
* ajout INDEX catminat_code sur baseflor_v2012_12_31
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();
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)
737 raphael 23
 
753 raphael 24
CREATE INDEX i_nom_referentiel ON tb_cel.cel_obs (`nom_referentiel`(5));
741 raphael 25
CREATE INDEX i_catminat_code ON baseflor_v2012_12_31 (`catminat_code`);
26
CREATE INDEX i_num_taxon ON nva_v2013_06 (`num_taxon`);
27
*/
737 raphael 28
 
741 raphael 29
-- malheureusement ceci est impossible en SQL d'où l'utilisation du shell-script
739 raphael 30
-- SET @destdb = 'tb_cel';
31
-- SET @desttable = 'cel_references';
32
-- SET @dst = CONCAT('`',@destdb,'`','.`',@desttable,'`');
737 raphael 33
 
739 raphael 34
DROP TABLE IF EXISTS @dst;
35
CREATE TABLE IF NOT EXISTS @dst (
36
       `referentiel` CHAR(5) NOT NULL COMMENT 'eg: "bdtfx", "bdtfx", "bdtxa", ... No ENUM!',
737 raphael 37
 
38
       -- bdtfx
739 raphael 39
       `num_nom` INT(9) NOT NULL DEFAULT '0' COMMENT 'depuis bdtfx',
40
       `num_nom_retenu` VARCHAR(9) DEFAULT NULL COMMENT 'depuis bdtfx',
737 raphael 41
 
739 raphael 42
       -- bdtfx + nvjfl_v2007 + nva_v2013_06
741 raphael 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",
738 raphael 44
 
45
       -- bdtfx
739 raphael 46
       `nom_sci` VARCHAR(500) NOT NULL COMMENT 'depuis bdtfx',
47
       `auteur` VARCHAR(100) DEFAULT NULL COMMENT 'depuis bdtfx',
737 raphael 48
 
738 raphael 49
 
737 raphael 50
       -- cel_obs
51
       --       `nom_ret_nn` DECIMAL(9,0) DEFAULT NULL COMMENT 'Numéro du nom retenu.',
52
       --       `nom_ret` VARCHAR(255) DEFAULT NULL,
53
 
54
       -- nvjfl_v2007 (`nom_vernaculaire` text NOT NULL)
738 raphael 55
       -- mais NULL à cause de nva
739 raphael 56
       `nom_commun` VARCHAR(60) NULL COMMENT 'nom_vernaculaire pour nvjfl_v2007 et nva_v2013_06',
737 raphael 57
 
741 raphael 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)),
75
INDEX (`num_nom`),
76
INDEX (`num_taxon`)
739 raphael 77
) ENGINE=MyISAM \
78
  DEFAULT CHARSET=utf8 \
760 raphael 79
  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';
737 raphael 80
 
741 raphael 81
-- tables temporaires
82
DROP TEMPORARY TABLE IF EXISTS `T_nvjfl_v2007`, `T_nva_v2013_06`, `T_basevegflor`;
739 raphael 83
 
737 raphael 84
CREATE TEMPORARY TABLE T_nvjfl_v2007 ( INDEX(`num_taxon`) ) AS \
738 raphael 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) );
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) );
737 raphael 87
 
738 raphael 88
-- table temporaire uniquement parce qu'il manque un index-key, autrement le LEFT JOIN ci-dessous est bien trop long
737 raphael 89
CREATE TEMPORARY TABLE T_nva_v2013_06 ( INDEX(`num_taxon`) ) AS \
738 raphael 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
737 raphael 91
 
92
 
741 raphael 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
760 raphael 103
-- pour le futur: attention au numéro taxonomique à 0 (WHERE b.num_taxonomique != '')
741 raphael 104
INSERT INTO @dst (`referentiel`, `num_nom`, `num_nom_retenu`, `num_taxon`, `nom_sci`, `auteur`, `nom_commun`, \
105
      `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`) \
107
 
108
       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, \
110
     bf.ve_reaction_sol, bf.ve_nutriments_sol, bf.ve_salinite, bf.ve_texture_sol, bf.ve_mat_org_sol, bf.syntaxon
760 raphael 111
  FROM 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);
741 raphael 113
 
760 raphael 114
 
115
-- pour le futur: attention au numéro taxonomique à 0 (WHERE b.num_tax IS NOT NULL AND b.num_tax != '')
739 raphael 116
INSERT INTO @dst (`referentiel`, `num_nom`, `num_nom_retenu`, `num_taxon`, `nom_sci`, `auteur`, `nom_commun`) \
760 raphael 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);
738 raphael 118
 
760 raphael 119
 
120
INSERT INTO @dst (`referentiel`, `num_nom`, `num_nom_retenu`, `num_taxon`, `nom_sci`, `auteur`) \
121
       SELECT "isfan", b.num_nom, b.num_nom_retenu, b.num_taxonomique, b.nom_sci, b.auteur FROM isfan_v2013 b;
122
 
123
 
124
 
125
 
741 raphael 126
DROP TEMPORARY TABLE IF EXISTS `T_nvjfl_v2007`, `T_nva_v2013_06`, `T_basevegflor`;
756 raphael 127
 
760 raphael 128
SELECT SUM(theorie.a) AS théorie, pratique.a AS total FROM \
129
	(SELECT COUNT(1) AS a FROM tb_eflore.bdtfx_v1_01 UNION ALL \
130
	 SELECT COUNT(1) AS a FROM tb_eflore.bdtxa_v1_00 UNION ALL \
131
	 SELECT COUNT(1) AS a FROM tb_eflore.isfan_v2013 \
132
	) AS theorie, \
133
	(SELECT COUNT(1) AS a FROM tb_cel.cel_references) AS pratique;
134
-- bdtfx+bdtxa+isfan: 141181 (2013/07/23)
135