Subversion Repositories eFlore/Projets.eflore-projets

Rev

Rev 1074 | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
1074 aurelien 1
/*
2
TODO:
3
* fix référentiel: suppression n° de version et uniformisation
4
SELECT DISTINCT nom_referentiel, COUNT(id_observation) AS count FROM `BASEEDIT`.`cel_obs` GROUP BY nom_referentiel ORDER BY count DESC;
5
* ajout INDEX nom_referentiel(5) sur `BASEEDIT`.`cel_obs`
6
* ajout INDEX catminat_code sur TABLEBASEFLOR
7
* ajout INDEX num_taxon sur TABLEBDTXAVERNA
8
* fix date: set NULL pour les dates dans le futur
9
SELECT courriel_utilisateur, id_observation, date_observation FROM `BASEEDIT`.`cel_obs` WHERE date_observation > NOW();
10
* intégrer les noms non-associés à un taxon (bdtfx where num_taxonomique = '')
11
* intégrer les noms non-associés à un taxon (bdtxa where num_tax = '' || num_tax IS NULL)
12
 
13
CREATE INDEX i_nom_referentiel ON `BASEEDIT`.`cel_obs` (`nom_referentiel`(5));
14
CREATE INDEX i_catminat_code ON TABLEBASEFLOR (`catminat_code`);
15
CREATE INDEX i_num_taxon ON TABLEBDTXAVERNA (`num_taxon`);
16
*/
17
 
18
-- malheureusement ceci est impossible en SQL d'où l'utilisation du shell-script
19
-- SET @destdb = 'tb_cel';
20
-- SET @desttable = 'cel_references';
21
-- -- SET BASEEDIT = `tb_cel`;
22
-- SET @dst = CONCAT('`',@destdb,'`','.`',@desttable,'`');
23
 
24
DROP TABLE IF EXISTS `BASEEDIT`.`cel_references`;
25
CREATE TABLE IF NOT EXISTS `BASEEDIT`.`cel_references` (
26
       `referentiel` CHAR(5) NOT NULL COMMENT 'eg: "bdtfx", "bdtfx", "bdtxa", ... No ENUM!',
27
 
28
       -- bdtfx
29
       `num_nom` INT(9) NOT NULL DEFAULT '0' COMMENT 'depuis bdtfx',
30
       `num_nom_retenu` INT(9) DEFAULT NULL COMMENT 'depuis bdtfx',
31
 
32
       -- bdtfx + TABLEBDTFXVERNA + TABLEBDTXAVERNA
33
       `num_taxon` int(9) NOT NULL COMMENT "depuis bdtfx, TABLEBDTFXVERNA et TABLEBDTXAVERNA (commun), les noms non-associés ne sont pas intégrés pour l'instant", -- 'relax emacs
34
 
35
       -- bdtfx
36
       `nom_sci` VARCHAR(500) NOT NULL COMMENT 'depuis bdtfx',
37
       `auteur` VARCHAR(100) DEFAULT NULL COMMENT 'depuis bdtfx',
38
 
39
 
40
       -- `BASEEDIT`.`cel_obs`
41
       --       `nom_ret_nn` DECIMAL(9,0) DEFAULT NULL COMMENT 'Numéro du nom retenu.',
42
       --       `nom_ret` VARCHAR(255) DEFAULT NULL,
43
 
44
       -- TABLEBDTFXVERNA (`nom_vernaculaire` text NOT NULL)
45
       -- mais NULL à cause de nva
46
       `nom_commun` VARCHAR(60) NULL COMMENT 'nom_vernaculaire pour TABLEBDTFXVERNA et TABLEBDTXAVERNA',
47
 
48
       -- TABLEBASEFLOR
49
       `catminat_code` varchar(18) DEFAULT 'inconnu' COMMENT 'depuis TABLEBASEFLOR',
1081 aurelien 50
       `ve_lumiere` char(1) DEFAULT NULL COMMENT 'depuis TABLEBASEFLOR',
51
       `ve_temperature` char(1) DEFAULT NULL COMMENT 'depuis TABLEBASEFLOR',
52
       `ve_continentalite` char(1) DEFAULT NULL COMMENT 'depuis TABLEBASEFLOR',
53
       `ve_humidite_atmos` char(1) DEFAULT NULL COMMENT 'depuis TABLEBASEFLOR',
54
       `ve_humidite_edaph` varchar(2) DEFAULT NULL COMMENT 'depuis TABLEBASEFLOR',
55
       `ve_reaction_sol` char(1) DEFAULT NULL COMMENT 'depuis TABLEBASEFLOR',
56
       `ve_nutriments_sol` char(1) DEFAULT NULL COMMENT 'depuis TABLEBASEFLOR',
57
       `ve_salinite` char(1) DEFAULT NULL COMMENT 'depuis TABLEBASEFLOR',
58
       `ve_texture_sol` char(1) DEFAULT NULL COMMENT 'depuis TABLEBASEFLOR',
59
       `ve_mat_org_sol` char(1) DEFAULT NULL COMMENT 'depuis TABLEBASEFLOR',
1074 aurelien 60
 
61
       -- TABLEBASEVEG
62
       `syntaxon` varchar(255) NULL COMMENT 'depuis TABLEBASEVEG',
63
PRIMARY KEY (`referentiel`, `num_nom`),
64
INDEX (`referentiel`(5)),
65
INDEX (`num_nom`),
66
INDEX (`num_nom_retenu`),
67
INDEX (`num_taxon`)
68
) ENGINE=MyISAM
69
  DEFAULT CHARSET=utf8
70
  COMMENT 'table générée par eflore/projets/scripts/modules/cel/cel_references.sql à partir de `TABLEBDTFX`, `TABLEBDTXA` et `TABLEISFAN`';
71
 
72
-- tables temporaires
73
DROP TEMPORARY TABLE IF EXISTS `T_TABLEBDTFXVERNA`, `T_TABLEBDTXAVERNA`, `T_basevegflor`;
74
 
75
-- pour TABLEBDTFXVERNA, le nom recommandé ou typique est celui pour lequel num_statut = 1 (mais plusieurs sont possibles, d'où le GROUP BY num_taxon)
76
CREATE TEMPORARY TABLE T_TABLEBDTFXVERNA ( INDEX(`num_taxon`) ) AS
77
       -- ( SELECT n.num_taxon, n.nom_vernaculaire FROM `BASESOURCE`.`TABLEBDTFXVERNA` n WHERE n.code_langue = 'fra' GROUP BY n.num_taxon, n.num_statut HAVING n.num_statut = MAX(n.num_statut) );
78
       -- ( SELECT n.num_taxon, n.nom_vernaculaire, n.num_statut as void, MAX(n.num_statut) as void2 FROM `BASESOURCE`.`TABLEBDTFXVERNA` n WHERE n.code_langue = 'fra' GROUP BY n.num_taxon HAVING n.num_statut = MAX(n.num_statut) );
79
       ( SELECT n.num_taxon, n.nom_vernaculaire FROM `BASESOURCE`.`TABLEBDTFXVERNA` n WHERE n.code_langue = 'fra' AND n.num_statut = 1 GROUP BY n.num_taxon );
80
 
81
-- table temporaire uniquement parce qu'il manque un index-key, autrement le LEFT JOIN ci-dessous est bien trop long
82
CREATE TEMPORARY TABLE T_TABLEBDTXAVERNA ( INDEX(`num_taxon`) ) AS
83
       -- ( SELECT n.num_taxon, n.nom_vernaculaire FROM `BASESOURCE`.`TABLEBDTXAVERNA` 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
84
       -- pour nva_index, le nom recommandé ou typique est celui pour lequel num_statut = 0 (mais il n'y en a aucun à l'heure actuelle) (mais plusieurs sont possibles, d'où le GROUP BY num_nom)
85
       ( SELECT n.num_taxon, n.nom_vernaculaire FROM `BASESOURCE`.`TABLEBDTXAVERNAINDEX` n WHERE n.code_langue = 'fra' /* AND n.num_statut = 0 */ GROUP BY n.num_taxon);
86
 
87
 
88
-- JOIN ON num_taxon_originel car INDEX
89
-- cf: eflore/projets/donnees/baseflor/2012-12-31/baseflor.sql
90
CREATE TEMPORARY TABLE T_basevegflor ( INDEX(`num_nomen`), INDEX(`num_taxon`) ) AS
91
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,
92
v.syntaxon
93
FROM `BASESOURCE`.`TABLEBASEFLOR` f LEFT JOIN `BASESOURCE`.`TABLEBASEVEG` v ON (f.catminat_code = v.code_catminat AND v.niveau = 'ALL' AND v.syntaxon IS NOT NULL) WHERE f.BDNT = "BDTFX"
94
GROUP BY f.num_nomen, f.num_taxon; -- group by car plusieurs couple (f.num_nomen, f.num_taxon) peuvent exister dans TABLEBASEVEG or num_nom est PRIMARY dans cel_references
95
 
96
 
97
-- INSERTIONS
98
-- pour le futur: attention au numéro taxonomique à 0 (WHERE b.num_taxonomique != '')
99
INSERT INTO `BASEEDIT`.`cel_references` (`referentiel`, `num_nom`, `num_nom_retenu`, `num_taxon`, `nom_sci`, `auteur`, `nom_commun`,
100
      `catminat_code`, `ve_lumiere`, `ve_temperature`, `ve_continentalite`, `ve_humidite_atmos`, `ve_humidite_edaph`,
101
      `ve_reaction_sol`, `ve_nutriments_sol`, `ve_salinite`, `ve_texture_sol`, `ve_mat_org_sol`, `syntaxon`)
102
 
103
       SELECT "bdtfx", b.num_nom, b.num_nom_retenu, b.num_taxonomique, b.nom_sci, b.auteur, n.nom_vernaculaire,
104
     bf.catminat_code, bf.ve_lumiere, bf.ve_temperature, bf.ve_continentalite, bf.ve_humidite_atmos, bf.ve_humidite_edaph,
105
     bf.ve_reaction_sol, bf.ve_nutriments_sol, bf.ve_salinite, bf.ve_texture_sol, bf.ve_mat_org_sol, bf.syntaxon
106
  FROM `BASESOURCE`.`TABLEBDTFX` b LEFT JOIN T_TABLEBDTFXVERNA n ON (b.num_taxonomique = n.num_taxon )
107
  LEFT JOIN T_basevegflor bf ON (b.num_taxonomique = bf.num_taxon AND b.num_nom = bf.num_nomen);
108
 
109
 
110
-- pour le futur: attention au numéro taxonomique à 0 (WHERE b.num_tax IS NOT NULL AND b.num_tax != '')
111
INSERT INTO `BASEEDIT`.`cel_references` (`referentiel`, `num_nom`, `num_nom_retenu`, `num_taxon`, `nom_sci`, `auteur`, `nom_commun`)
112
       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_TABLEBDTXAVERNA n ON (b.num_tax = n.num_taxon);
113
 
114
 
115
INSERT INTO `BASEEDIT`.`cel_references` (`referentiel`, `num_nom`, `num_nom_retenu`, `num_taxon`, `nom_sci`, `auteur`)
116
       SELECT "isfan", b.num_nom, b.num_nom_retenu, b.num_taxonomique, b.nom_sci, b.auteur FROM `BASESOURCE`.`TABLEISFAN` b;
117
 
118
 
119
 
120
 
121
DROP TEMPORARY TABLE IF EXISTS `T_TABLEBDTFXVERNA`, `T_TABLEBDTXAVERNA`, `T_basevegflor`;
122
 
123
SELECT SUM(theorie.a) AS théorie, pratique.a AS total FROM
124
	(SELECT COUNT(1) AS a FROM `BASESOURCE`.`TABLEBDTFX` UNION ALL
125
	 SELECT COUNT(1) AS a FROM `BASESOURCE`.`TABLEBDTXA` UNION ALL
126
	 SELECT COUNT(1) AS a FROM `BASESOURCE`.`TABLEISFAN`
127
	) AS theorie,
128
	(SELECT COUNT(1) AS a FROM `BASEEDIT`.cel_references) AS pratique;
129
-- bdtfx+bdtxa+isfan: 141181 (2013/07/23)