Rev 935 | Blame | Compare with Previous | Last modification | View Log | RSS feed
/*TODO:* fix référentiel: suppression n° de version et uniformisationSELECT DISTINCT nom_referentiel, COUNT(id_observation) AS count FROM `BASEEDIT`.`cel_obs` GROUP BY nom_referentiel ORDER BY count DESC;* ajout INDEX nom_referentiel(5) sur `BASEEDIT`.`cel_obs`* ajout INDEX catminat_code sur baseflor_v2012_12_31* ajout INDEX num_taxon sur nva_v2013_06* fix date: set NULL pour les dates dans le futurSELECT courriel_utilisateur, id_observation, date_observation FROM `BASEEDIT`.`cel_obs` WHERE date_observation > NOW();* intégrer les noms non-associés à un taxon (bdtfx where num_taxonomique = '')* intégrer les noms non-associés à un taxon (bdtxa where num_tax = '' || num_tax IS NULL)CREATE INDEX i_nom_referentiel ON `BASEEDIT`.`cel_obs` (`nom_referentiel`(5));CREATE INDEX i_catminat_code ON baseflor_v2012_12_31 (`catminat_code`);CREATE INDEX i_num_taxon ON nva_v2013_06 (`num_taxon`);*/-- malheureusement ceci est impossible en SQL d'où l'utilisation du shell-script-- SET @destdb = 'tb_cel';-- SET @desttable = 'cel_references';-- -- SET BASEEDIT = `tb_cel`;-- SET @dst = CONCAT('`',@destdb,'`','.`',@desttable,'`');DROP TABLE IF EXISTS `BASEEDIT`.`cel_references`;CREATE TABLE IF NOT EXISTS `BASEEDIT`.`cel_references` (`referentiel` CHAR(5) NOT NULL COMMENT 'eg: "bdtfx", "bdtfx", "bdtxa", ... No ENUM!',-- bdtfx`num_nom` INT(9) NOT NULL DEFAULT '0' COMMENT 'depuis bdtfx',`num_nom_retenu` VARCHAR(9) DEFAULT NULL COMMENT 'depuis bdtfx',-- bdtfx + nvjfl_v2007 + nva_v2013_06`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", -- 'relax emacs-- bdtfx`nom_sci` VARCHAR(500) NOT NULL COMMENT 'depuis bdtfx',`auteur` VARCHAR(100) DEFAULT NULL COMMENT 'depuis bdtfx',-- `BASEEDIT`.`cel_obs`-- `nom_ret_nn` DECIMAL(9,0) DEFAULT NULL COMMENT 'Numéro du nom retenu.',-- `nom_ret` VARCHAR(255) DEFAULT NULL,-- nvjfl_v2007 (`nom_vernaculaire` text NOT NULL)-- mais NULL à cause de nva`nom_commun` VARCHAR(60) NULL COMMENT 'nom_vernaculaire pour nvjfl_v2007 et nva_v2013_06',-- baseflor_v2012_12_31`catminat_code` varchar(18) DEFAULT 'inconnu' COMMENT 'depuis baseflor_v2012_12_31',`ve_lumiere` int(1) DEFAULT NULL COMMENT 'depuis baseflor_v2012_12_31',`ve_temperature` int(1) DEFAULT NULL COMMENT 'depuis baseflor_v2012_12_31',`ve_continentalite` int(1) DEFAULT NULL COMMENT 'depuis baseflor_v2012_12_31',`ve_humidite_atmos` int(1) DEFAULT NULL COMMENT 'depuis baseflor_v2012_12_31',`ve_humidite_edaph` int(2) DEFAULT NULL COMMENT 'depuis baseflor_v2012_12_31',`ve_reaction_sol` int(1) DEFAULT NULL COMMENT 'depuis baseflor_v2012_12_31',`ve_nutriments_sol` int(1) DEFAULT NULL COMMENT 'depuis baseflor_v2012_12_31',`ve_salinite` int(1) DEFAULT NULL COMMENT 'depuis baseflor_v2012_12_31',`ve_texture_sol` int(1) DEFAULT NULL COMMENT 'depuis baseflor_v2012_12_31',`ve_mat_org_sol` int(1) DEFAULT NULL COMMENT 'depuis baseflor_v2012_12_31',-- baseveg_v2013_01_09`syntaxon` varchar(255) NULL COMMENT 'depuis baseveg_v2013_01_09',PRIMARY KEY (`referentiel`, `num_nom`),INDEX (`referentiel`(5)),INDEX (`num_nom`),INDEX (`num_taxon`)) ENGINE=MyISAM \DEFAULT CHARSET=utf8 \COMMENT 'table générée par eflore/projets/scripts/modules/cel/cel_references.sql à partir de `TABLEBDTFX`, `TABLEBDTXA` et `TABLEISFAN`';-- tables temporairesDROP TEMPORARY TABLE IF EXISTS `T_nvjfl_v2007`, `T_nva_v2013_06`, `T_basevegflor`;-- pour nvjfl_v2007, le nom recommandé ou typique est celui pour lequel num_statut = 1 (mais plusieurs sont possibles, d'où le GROUP BY num_taxon)CREATE TEMPORARY TABLE T_nvjfl_v2007 ( INDEX(`num_taxon`) ) AS \-- ( 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) );-- ( 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) );( SELECT n.num_taxon, n.nom_vernaculaire FROM `BASESOURCE`.`nvjfl_v2007` n WHERE n.code_langue = 'fra' AND n.num_statut = 1 GROUP BY n.num_taxon );-- table temporaire uniquement parce qu'il manque un index-key, autrement le LEFT JOIN ci-dessous est bien trop longCREATE TEMPORARY TABLE T_nva_v2013_06 ( INDEX(`num_taxon`) ) AS \-- ( 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-- 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)( SELECT n.num_taxon, n.nom_vernaculaire FROM `BASESOURCE`.`nva_index_v2_03` n WHERE n.code_langue = 'fra' /* AND n.num_statut = 0 */ GROUP BY n.num_taxon);-- JOIN ON num_taxon_originel car INDEX-- cf: eflore/projets/donnees/baseflor/2012-12-31/baseflor.sqlCREATE TEMPORARY TABLE T_basevegflor ( INDEX(`num_nomen`), INDEX(`num_taxon`) ) AS \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, \v.syntaxon \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" \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-- INSERTIONS-- pour le futur: attention au numéro taxonomique à 0 (WHERE b.num_taxonomique != '')INSERT INTO `BASEEDIT`.`cel_references` (`referentiel`, `num_nom`, `num_nom_retenu`, `num_taxon`, `nom_sci`, `auteur`, `nom_commun`, \`catminat_code`, `ve_lumiere`, `ve_temperature`, `ve_continentalite`, `ve_humidite_atmos`, `ve_humidite_edaph`, \`ve_reaction_sol`, `ve_nutriments_sol`, `ve_salinite`, `ve_texture_sol`, `ve_mat_org_sol`, `syntaxon`) \SELECT "bdtfx", b.num_nom, b.num_nom_retenu, b.num_taxonomique, b.nom_sci, b.auteur, n.nom_vernaculaire, \bf.catminat_code, bf.ve_lumiere, bf.ve_temperature, bf.ve_continentalite, bf.ve_humidite_atmos, bf.ve_humidite_edaph, \bf.ve_reaction_sol, bf.ve_nutriments_sol, bf.ve_salinite, bf.ve_texture_sol, bf.ve_mat_org_sol, bf.syntaxonFROM `BASESOURCE`.`TABLEBDTFX` b LEFT JOIN T_nvjfl_v2007 n ON (b.num_taxonomique = n.num_taxon ) \LEFT JOIN T_basevegflor bf ON (b.num_taxonomique = bf.num_taxon AND b.num_nom = bf.num_nomen);-- pour le futur: attention au numéro taxonomique à 0 (WHERE b.num_tax IS NOT NULL AND b.num_tax != '')INSERT INTO `BASEEDIT`.`cel_references` (`referentiel`, `num_nom`, `num_nom_retenu`, `num_taxon`, `nom_sci`, `auteur`, `nom_commun`) \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_nva_v2013_06 n ON (b.num_tax = n.num_taxon);INSERT INTO `BASEEDIT`.`cel_references` (`referentiel`, `num_nom`, `num_nom_retenu`, `num_taxon`, `nom_sci`, `auteur`) \SELECT "isfan", b.num_nom, b.num_nom_retenu, b.num_taxonomique, b.nom_sci, b.auteur FROM `BASESOURCE`.`TABLEISFAN` b;DROP TEMPORARY TABLE IF EXISTS `T_nvjfl_v2007`, `T_nva_v2013_06`, `T_basevegflor`;SELECT SUM(theorie.a) AS théorie, pratique.a AS total FROM \(SELECT COUNT(1) AS a FROM `BASESOURCE`.`TABLEBDTFX` UNION ALL \SELECT COUNT(1) AS a FROM `BASESOURCE`.`TABLEBDTXA` UNION ALL \SELECT COUNT(1) AS a FROM `BASESOURCE`.`TABLEISFAN` \) AS theorie, \(SELECT COUNT(1) AS a FROM `BASEEDIT`.cel_references) AS pratique;-- bdtfx+bdtxa+isfan: 141181 (2013/07/23)