Subversion Repositories eFlore/Projets.eflore-projets

Rev

Rev 738 | Go to most recent revision | Only display areas with differences | Ignore whitespace | Details | Blame | Last modification | View Log | RSS feed

Rev 738 Rev 739
- 
 
 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 
 
1 
-- TODO:
 10 
-- TODO:
2 
-- fix référentiel: suppression n° de version et uniformisation
 11 
-- fix référentiel: suppression n° de version et uniformisation
3 
-- SELECT DISTINCT nom_referentiel, COUNT(id_observation) AS count FROM cel_obs GROUP BY nom_referentiel ORDER BY count DESC;
 12 
-- SELECT DISTINCT nom_referentiel, COUNT(id_observation) AS count FROM cel_obs GROUP BY nom_referentiel ORDER BY count DESC;
4 
 
 13 
 
5 
-- fix date: NULL pour les dates dans le futur
 14 
-- fix date: NULL pour les dates dans le futur
6 
-- SELECT courriel_utilisateur, id_observation, date_observation FROM cel_obs WHERE date_observation > NOW();
 15 
-- SELECT courriel_utilisateur, id_observation, date_observation FROM cel_obs WHERE date_observation > NOW();
7 
 
 16 
 
- 
 
 17 
 
- 
 
 18 
-- malheureusement ceci est impossible en SQL d'où l'utilisation du shell
- 
 
 19 
-- SET @destdb = 'tb_cel';
- 
 
 20 
-- SET @desttable = 'cel_references';
8 
 
 21 
-- SET @dst = CONCAT('`',@destdb,'`','.`',@desttable,'`');
9 
 
 22 
 
10 
DROP TABLE IF EXISTS `cel_references`;
 23 
DROP TABLE IF EXISTS @dst;
11 
CREATE TABLE IF NOT EXISTS `cel_references` (
 24 
CREATE TABLE IF NOT EXISTS @dst (
12 
       `referentiel` CHAR(5) NOT NULL, -- ENUM ("bdtfx", "bdtfx", "bdtxa", ...),
 25 
       `referentiel` CHAR(5) NOT NULL COMMENT 'eg: "bdtfx", "bdtfx", "bdtxa", ... No ENUM!',
13 
 
 26 
 
14 
       -- bdtfx
 27 
       -- bdtfx
15 
       `num_nom` INT(9) NOT NULL DEFAULT '0',
 28 
       `num_nom` INT(9) NOT NULL DEFAULT '0' COMMENT 'depuis bdtfx',
16 
       `num_nom_retenu` VARCHAR(9) DEFAULT NULL,
 29 
       `num_nom_retenu` VARCHAR(9) DEFAULT NULL COMMENT 'depuis bdtfx',
17 
 
 30 
 
18 
       -- bdtfx + nvjfl_v2007
 31 
       -- bdtfx + nvjfl_v2007 + nva_v2013_06
19 
       `num_taxon` int(9) NOT NULL,
 32 
       `num_taxon` int(9) NOT NULL COMMENT 'depuis bdtfx, nvjfl_v2007 et nva_v2013_06 (commun)',
20 
 
 33 
 
21 
       -- bdtfx
 34 
       -- bdtfx
22 
       `nom_sci` VARCHAR(500) NOT NULL,
 35 
       `nom_sci` VARCHAR(500) NOT NULL COMMENT 'depuis bdtfx',
23 
       `auteur` VARCHAR(100) DEFAULT NULL,
 36 
       `auteur` VARCHAR(100) DEFAULT NULL COMMENT 'depuis bdtfx',
24 
 
 37 
 
25 
 
 38 
 
26 
       -- cel_obs
 39 
       -- cel_obs
27 
       --       `nom_ret_nn` DECIMAL(9,0) DEFAULT NULL COMMENT 'Numéro du nom retenu.',
 40 
       --       `nom_ret_nn` DECIMAL(9,0) DEFAULT NULL COMMENT 'Numéro du nom retenu.',
28 
       --       `nom_ret` VARCHAR(255) DEFAULT NULL,
 41 
       --       `nom_ret` VARCHAR(255) DEFAULT NULL,
29 
 
 42 
 
30 
       -- nvjfl_v2007 (`nom_vernaculaire` text NOT NULL)
 43 
       -- nvjfl_v2007 (`nom_vernaculaire` text NOT NULL)
31 
       -- mais NULL à cause de nva
 44 
       -- mais NULL à cause de nva
32 
       `nom_commun` VARCHAR(60) NULL,
 45 
       `nom_commun` VARCHAR(60) NULL COMMENT 'nom_vernaculaire pour nvjfl_v2007 et nva_v2013_06',
33 
PRIMARY KEY (`referentiel`, `num_nom`)
 46 
PRIMARY KEY (`referentiel`, `num_nom`)
- 
 
 47 
 
- 
 
 48 
) ENGINE=MyISAM \
34 
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
 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';
35 
 
 51 
 
36 
 
 52 
 
37 
/*
 53 
/*
38 
way 1: theory
 54 
way 1: theory
39 
INSERT INTO `cel_references` (`referentiel`, `num_nom`, `num_nom_retenu`, `nom_sci`, `auteur`, `nom_commun`) \
 55 
INSERT INTO @dst (`referentiel`, `num_nom`, `num_nom_retenu`, `nom_sci`, `auteur`, `nom_commun`) \
40 
       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 \
 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 \
41 
       UNION \
 57 
       UNION \
42 
       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;
 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;
43 
SELECT "isfan", b.num_nom, b.num_nom_retenu, b.nom_sci, b.auteur, NULL FROM isfan_v2013 b;
 59 
SELECT "isfan", b.num_nom, b.num_nom_retenu, b.nom_sci, b.auteur, NULL FROM isfan_v2013 b;
44 
*/
 60 
*/
45 
 
 61 
 
46 
 
 62 
 
47 
/*
 63 
/*
48 
Détermination des nom vernaculaires meilleurs et uniques:
 64 
Détermination des nom vernaculaires meilleurs et uniques:
49 
 
 65 
 
50 
way 1:
 66 
way 1:
51 
-- 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;
 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;
52 
-- 12311
 68 
-- 12311
53 
-- # distinct=
 69 
-- # distinct=
54 
 
 70 
 
55 
way 2:
 71 
way 2:
56 
-- 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;
 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;
57 
-- 2680
 73 
-- 2680
58 
 
 74 
 
59 
way 2":
 75 
way 2":
60 
-- 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;
 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;
61 
-- 2680, Mais problème ensuite: SELECT n.* from cel_references NATURAL JOIN nvjfl_v2007 n WHERE `nom_commun` = '' AND n.code_langue = 'fra';
 77 
-- 2680, Mais problème ensuite: SELECT n.* from cel_references NATURAL JOIN nvjfl_v2007 n WHERE `nom_commun` = '' AND n.code_langue = 'fra';
62 
 
 78 
 
63 
 
 79 
 
64 
Note: 16146 nom communs français distincts, 12312 num_taxon fr, aucun num_statut NULL en français
 80 
Note: 16146 nom communs français distincts, 12312 num_taxon fr, aucun num_statut NULL en français
65 
*/
 81 
*/
66 
 
 82 
 
67 
 
 83 
 
68 
DROP TEMPORARY TABLE IF EXISTS `T_nvjfl_v2007`, `T_nva_v2013_06`;
 84 
DROP TEMPORARY TABLE IF EXISTS `T_nvjfl_v2007`, `T_nva_v2013_06`;
69 
 
 85 
 
70 
CREATE TEMPORARY TABLE T_nvjfl_v2007 ( INDEX(`num_taxon`) ) AS \
 86 
CREATE TEMPORARY TABLE T_nvjfl_v2007 ( INDEX(`num_taxon`) ) AS \
71 
       -- ( 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) );
 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) );
72 
       ( 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) );
 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) );
73 
 
 89 
 
74 
-- table temporaire uniquement parce qu'il manque un index-key, autrement le LEFT JOIN ci-dessous est bien trop long
 90 
-- table temporaire uniquement parce qu'il manque un index-key, autrement le LEFT JOIN ci-dessous est bien trop long
75 
CREATE TEMPORARY TABLE T_nva_v2013_06 ( INDEX(`num_taxon`) ) AS \
 91 
CREATE TEMPORARY TABLE T_nva_v2013_06 ( INDEX(`num_taxon`) ) AS \
76 
       ( 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
 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
77 
 
 93 
 
78 
 
 94 
 
79 
INSERT INTO `cel_references` (`referentiel`, `num_nom`, `num_nom_retenu`, `num_taxon`, `nom_sci`, `auteur`, `nom_commun`) \
 95 
INSERT INTO @dst (`referentiel`, `num_nom`, `num_nom_retenu`, `num_taxon`, `nom_sci`, `auteur`, `nom_commun`) \
80 
       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 ) \
 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 ) \
81 
       UNION \
 97 
       UNION \
82 
       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);
 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);
83 
 
 99 
 
84 
DROP TEMPORARY TABLE IF EXISTS `T_nvjfl_v2007`, `T_nva_v2013_06`;
 100 
DROP TEMPORARY TABLE IF EXISTS `T_nvjfl_v2007`, `T_nva_v2013_06`;