Subversion Repositories eFlore/Projets.eflore-projets

Rev

Go to most recent revision | Details | Last modification | View Log | RSS feed

Rev Author Line No. Line
739 raphael 1
#!/bin/bash
2
 
3
DB='`tb_cel`.`cel_references`';
4
sed -e '1,/^-- DEBUT DE SCRIPT/d' -e "s;@dst;$DB;" "$0"
5
exit
6
 
7
-- DEBUT DE SCRIPT SQL substitué: ne pas retirer, ne pas modifier cette ligne
8
-- ci dessous ne doit exister que du SQL valide
9
 
737 raphael 10
-- TODO:
11
-- fix référentiel: suppression n° de version et uniformisation
12
-- SELECT DISTINCT nom_referentiel, COUNT(id_observation) AS count FROM cel_obs GROUP BY nom_referentiel ORDER BY count DESC;
13
 
14
-- fix date: NULL pour les dates dans le futur
15
-- SELECT courriel_utilisateur, id_observation, date_observation FROM cel_obs WHERE date_observation > NOW();
16
 
17
 
739 raphael 18
-- malheureusement ceci est impossible en SQL d'où l'utilisation du shell
19
-- SET @destdb = 'tb_cel';
20
-- SET @desttable = 'cel_references';
21
-- SET @dst = CONCAT('`',@destdb,'`','.`',@desttable,'`');
737 raphael 22
 
739 raphael 23
DROP TABLE IF EXISTS @dst;
24
CREATE TABLE IF NOT EXISTS @dst (
25
       `referentiel` CHAR(5) NOT NULL COMMENT 'eg: "bdtfx", "bdtfx", "bdtxa", ... No ENUM!',
737 raphael 26
 
27
       -- bdtfx
739 raphael 28
       `num_nom` INT(9) NOT NULL DEFAULT '0' COMMENT 'depuis bdtfx',
29
       `num_nom_retenu` VARCHAR(9) DEFAULT NULL COMMENT 'depuis bdtfx',
737 raphael 30
 
739 raphael 31
       -- bdtfx + nvjfl_v2007 + nva_v2013_06
32
       `num_taxon` int(9) NOT NULL COMMENT 'depuis bdtfx, nvjfl_v2007 et nva_v2013_06 (commun)',
738 raphael 33
 
34
       -- bdtfx
739 raphael 35
       `nom_sci` VARCHAR(500) NOT NULL COMMENT 'depuis bdtfx',
36
       `auteur` VARCHAR(100) DEFAULT NULL COMMENT 'depuis bdtfx',
737 raphael 37
 
738 raphael 38
 
737 raphael 39
       -- cel_obs
40
       --       `nom_ret_nn` DECIMAL(9,0) DEFAULT NULL COMMENT 'Numéro du nom retenu.',
41
       --       `nom_ret` VARCHAR(255) DEFAULT NULL,
42
 
43
       -- nvjfl_v2007 (`nom_vernaculaire` text NOT NULL)
738 raphael 44
       -- mais NULL à cause de nva
739 raphael 45
       `nom_commun` VARCHAR(60) NULL COMMENT 'nom_vernaculaire pour nvjfl_v2007 et nva_v2013_06',
737 raphael 46
PRIMARY KEY (`referentiel`, `num_nom`)
47
 
739 raphael 48
) 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';
737 raphael 51
 
739 raphael 52
 
738 raphael 53
/*
54
way 1: theory
739 raphael 55
INSERT INTO @dst (`referentiel`, `num_nom`, `num_nom_retenu`, `nom_sci`, `auteur`, `nom_commun`) \
738 raphael 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
*/
737 raphael 61
 
62
 
738 raphael 63
/*
64
Détermination des nom vernaculaires meilleurs et uniques:
737 raphael 65
 
738 raphael 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=
737 raphael 70
 
738 raphael 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
737 raphael 74
 
738 raphael 75
way 2":
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
 
79
 
80
Note: 16146 nom communs français distincts, 12312 num_taxon fr, aucun num_statut NULL en français
81
*/
82
 
83
 
84
DROP TEMPORARY TABLE IF EXISTS `T_nvjfl_v2007`, `T_nva_v2013_06`;
85
 
737 raphael 86
CREATE TEMPORARY TABLE T_nvjfl_v2007 ( INDEX(`num_taxon`) ) AS \
738 raphael 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) );
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) );
737 raphael 89
 
738 raphael 90
-- table temporaire uniquement parce qu'il manque un index-key, autrement le LEFT JOIN ci-dessous est bien trop long
737 raphael 91
CREATE TEMPORARY TABLE T_nva_v2013_06 ( INDEX(`num_taxon`) ) AS \
738 raphael 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
737 raphael 93
 
94
 
739 raphael 95
INSERT INTO @dst (`referentiel`, `num_nom`, `num_nom_retenu`, `num_taxon`, `nom_sci`, `auteur`, `nom_commun`) \
738 raphael 96
       SELECT "bdtfx", b.num_nom, b.num_nom_retenu, b.num_taxonomique, b.nom_sci, b.auteur, n.nom_vernaculaire	FROM bdtfx_v1_01 b LEFT JOIN T_nvjfl_v2007 n ON (b.num_taxonomique = n.num_taxon ) \
737 raphael 97
       UNION \
738 raphael 98
       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);
99
 
100
DROP TEMPORARY TABLE IF EXISTS `T_nvjfl_v2007`, `T_nva_v2013_06`;