Subversion Repositories eFlore/Projets.eflore-projets

Rev

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

Rev 818 Rev 819
Line 1... Line 1...
1
/*
1
/*
2
TODO:
2
TODO:
3
* fix référentiel: suppression n° de version et uniformisation
3
* fix référentiel: suppression n° de version et uniformisation
4
SELECT DISTINCT nom_referentiel, COUNT(id_observation) AS count FROM `tb_cel_test`.`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;
5
* ajout INDEX nom_referentiel(5) sur `tb_cel_test`.`cel_obs`
5
* ajout INDEX nom_referentiel(5) sur `BASEEDIT`.`cel_obs`
6
* ajout INDEX catminat_code sur baseflor_v2012_12_31
6
* ajout INDEX catminat_code sur baseflor_v2012_12_31
7
* ajout INDEX num_taxon sur nva_v2013_06
7
* ajout INDEX num_taxon sur nva_v2013_06
8
* fix date: set NULL pour les dates dans le futur
8
* fix date: set NULL pour les dates dans le futur
9
SELECT courriel_utilisateur, id_observation, date_observation FROM `tb_cel_test`.`cel_obs` WHERE date_observation > NOW();
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 = '')
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)
11
* intégrer les noms non-associés à un taxon (bdtxa where num_tax = '' || num_tax IS NULL)
Line 12... Line 12...
12
 
12
 
13
CREATE INDEX i_nom_referentiel ON `tb_cel_test`.`cel_obs` (`nom_referentiel`(5));
13
CREATE INDEX i_nom_referentiel ON `BASEEDIT`.`cel_obs` (`nom_referentiel`(5));
14
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`);
15
CREATE INDEX i_num_taxon ON nva_v2013_06 (`num_taxon`);
15
CREATE INDEX i_num_taxon ON nva_v2013_06 (`num_taxon`);
Line 16... Line 16...
16
*/
16
*/
17
 
17
 
18
-- 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';
19
-- SET @destdb = 'tb_cel';
20
-- SET @desttable = 'cel_references';
20
-- SET @desttable = 'cel_references';
Line 21... Line 21...
21
-- -- SET BASEEDIT = `tb_cel`;
21
-- -- SET BASEEDIT = `tb_cel`;
22
-- SET @dst = CONCAT('`',@destdb,'`','.`',@desttable,'`');
22
-- SET @dst = CONCAT('`',@destdb,'`','.`',@desttable,'`');
23
 
23
 
Line 24... Line 24...
24
DROP TABLE IF EXISTS `tb_cel_test`.`cel_references`;
24
DROP TABLE IF EXISTS `BASEEDIT`.`cel_references`;
25
CREATE TABLE IF NOT EXISTS `tb_cel_test`.`cel_references` (
25
CREATE TABLE IF NOT EXISTS `BASEEDIT`.`cel_references` (
26
       `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 35... Line 35...
35
       -- bdtfx
35
       -- bdtfx
36
       `nom_sci` VARCHAR(500) NOT NULL COMMENT 'depuis bdtfx',
36
       `nom_sci` VARCHAR(500) NOT NULL COMMENT 'depuis bdtfx',
37
       `auteur` VARCHAR(100) DEFAULT NULL COMMENT 'depuis bdtfx',
37
       `auteur` VARCHAR(100) DEFAULT NULL COMMENT 'depuis bdtfx',
Line 38... Line 38...
38
 
38
 
39
 
39
 
40
       -- `tb_cel_test`.`cel_obs`
40
       -- `BASEEDIT`.`cel_obs`
Line 41... Line 41...
41
       --       `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.',
42
       --       `nom_ret` VARCHAR(255) DEFAULT NULL,
42
       --       `nom_ret` VARCHAR(255) DEFAULT NULL,
Line 70... Line 70...
70
 
70
 
71
-- tables temporaires
71
-- tables temporaires
Line 72... Line 72...
72
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`;
73
 
73
 
74
CREATE TEMPORARY TABLE T_nvjfl_v2007 ( INDEX(`num_taxon`) ) AS \
74
CREATE TEMPORARY TABLE T_nvjfl_v2007 ( INDEX(`num_taxon`) ) AS \
Line 75... Line 75...
75
       -- ( SELECT n.num_taxon, n.nom_vernaculaire FROM `tb_eflore`.`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) );
76
       ( SELECT n.num_taxon, n.nom_vernaculaire, n.num_statut as void, MAX(n.num_statut) as void2 FROM `tb_eflore`.`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) );
77
 
77
 
Line 78... Line 78...
78
-- 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
79
CREATE TEMPORARY TABLE T_nva_v2013_06 ( INDEX(`num_taxon`) ) AS \
79
CREATE TEMPORARY TABLE T_nva_v2013_06 ( INDEX(`num_taxon`) ) AS \
80
       ( SELECT n.num_taxon, n.nom_vernaculaire FROM `tb_eflore`.`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
81
 
81
 
82
 
82
 
83
-- JOIN ON num_taxon_originel car INDEX
83
-- JOIN ON num_taxon_originel car INDEX
84
-- cf: eflore/projets/donnees/baseflor/2012-12-31/baseflor.sql
84
-- cf: eflore/projets/donnees/baseflor/2012-12-31/baseflor.sql
Line 85... Line 85...
85
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 \
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, \
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, \
87
v.syntaxon \
87
v.syntaxon \
88
FROM `tb_eflore`.`baseflor_v2012_12_31` f LEFT JOIN `tb_eflore`.`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" \
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
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 90... Line 90...
90
 
90
 
91
 
91
 
92
-- INSERTIONS
92
-- INSERTIONS
93
-- 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 != '')
94
INSERT INTO `tb_cel_test`.`cel_references` (`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 95... Line 95...
95
      `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`, \
96
      `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`) \
97
 
97
 
Line 98... Line 98...
98
       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, \
99
     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 100... Line 100...
100
     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 101... Line 101...
101
  FROM `tb_eflore`.`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 ) \
102
  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);
103
 
103
 
104
 
104
 
105
-- 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 != '')
106
INSERT INTO `tb_cel_test`.`cel_references` (`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`) \
107
       SELECT "bdtxa", b.num_nom, b.num_nom_retenu, b.num_tax, b.nom_sci, b.auteur, n.nom_vernaculaire FROM `tb_eflore`.`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);