Subversion Repositories eFlore/Projets.eflore-projets

Compare Revisions

No changes between revisions

Ignore whitespace Rev 1075 → Rev 1078

/tags/v5.8-aspergeraie/scripts/modules/cel/Cel.php
New file
0,0 → 1,106
<?php
//declare(encoding='UTF-8');
/**
* Exemple de lancement du script : :
* /opt/lampp/bin/php cli.php cel -a chargerTous
*
* @category php 5.2
* @package eFlore/Scripts
* @author Jean-Pascal MILCENT <jpm@tela-botanica.org>
* @copyright Copyright (c) 2011, Tela Botanica (accueil@tela-botanica.org)
* @license http://www.cecill.info/licences/Licence_CeCILL_V2-fr.txt Licence CECILL
* @license http://www.gnu.org/licenses/gpl.html Licence GNU-GPL
* @version $Id$
*/
class Cel extends EfloreScript {
 
public function executer() {
try {
$this->initialiserProjet('cel');
 
// Lancement de l'action demandée
$cmd = $this->getParametre('a');
switch ($cmd) {
case 'chargerTous' :
$this->chargerStructureSql();
$this->chargerCel();
break;
case 'supprimerTous' :
$this->supprimerTous();
break;
case 'genererScriptTableReference' :
$this->genererScriptTableReference();
break;
default :
throw new Exception("Erreur : la commande '$cmd' n'existe pas!");
}
} catch (Exception $e) {
$this->traiterErreur($e->getMessage());
}
}
 
protected function initialiserProjet($projet) {
$bases = $this->getListeBases();
parent::initialiserProjet($projet);
$this->verifierPresenceBdd($bases);
}
 
private function getListeBases() {
$requete = "SHOW DATABASES";
$bases = $this->getBdd()->recupererTous($requete);
return $bases;
}
 
private function verifierPresenceBdd($bases) {
$bddNom = Config::get('bdd_nom');
$existe = false;
foreach ($bases as $base) {
if ($base['Database'] == $bddNom) {
$existe = true;
break;
}
}
if ($existe === false) {
$message = "Veuillez créer la base de données '$bddNom'.";
throw new Exception($message);
}
}
 
public function chargerCel() {
$tablesCodes = array_keys(Config::get('tables'));
foreach ($tablesCodes as $code) {
echo "Chargement de la table : $code\n";
$this->chargerFichierTsvDansTable($code);
}
}
 
private function chargerFichierTsvDansTable($code) {
$chemin = Config::get('chemins.'.$code);
$table = Config::get('tables.'.$code);
$requete = "LOAD DATA INFILE '$chemin' ".
"REPLACE INTO TABLE $table ".
'CHARACTER SET utf8 '.
'FIELDS '.
" TERMINATED BY '\t' ".
" ENCLOSED BY '' ".
" ESCAPED BY '\\\' ".
'IGNORE 1 LINES';
$this->getBdd()->requeter($requete);
}
 
private function supprimerTous() {
$requete = "DROP TABLE IF EXISTS cel_meta, cel_images, cel_inventory, cel_mots_cles_images, cel_mots_cles_obs, ".
"cel_obs_images, locations ";
$this->getBdd()->requeter($requete);
}
private function genererScriptTableReference() {
$script_sql = file_get_contents(__DIR__.'/cel_references.tpl.sql');
$config_references = Config::get('references');
foreach($config_references as $a_remplacer => $remplacement) {
$script_sql = str_replace($a_remplacer, $remplacement, $script_sql);
file_put_contents(__DIR__.'/cel_references.sql', $script_sql);
}
}
}
?>
/tags/v5.8-aspergeraie/scripts/modules/cel/sphinx-maj.log
New file
0,0 → 1,39
Array
(
[no_nom_sel] => Array
(
[count] => 0
)
 
[not found] => Array
(
[count] => 6597
)
 
[too many] => Array
(
[count] => 1065
)
 
[fixable] => Array
(
[count] => 1448
)
 
[sauvages] => Array
(
[count] => 590
)
 
[sphinx errors] => Array
(
[count] => 0
)
 
[ref pb] => Array
(
[count] => 1543
)
 
)
total traité: 11243
/tags/v5.8-aspergeraie/scripts/modules/cel/.current
New file
0,0 → 1,9
-- conserver ce fichier, il illustre les substitutions effectuées par le Makefile
-- et permet à celui-ci de déterminer et d'informer quelles bases seront utilisées/concernées par
-- les changements
BASEEDIT=`BASEEDIT`
BASEANNUAIRE=`BASEANNUAIRE`
BASESOURCE=`BASESOURCE`
TABLE_BDTFX=TABLEBDTFX
TABLE_BDTXA=TABLEBDTXA
TABLE_ISFAN=TABLEISFAN
/tags/v5.8-aspergeraie/scripts/modules/cel/cel_references.tpl.sql
New file
0,0 → 1,129
/*
TODO:
* fix référentiel: suppression n° de version et uniformisation
SELECT 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 TABLEBASEFLOR
* ajout INDEX num_taxon sur TABLEBDTXAVERNA
* fix date: set NULL pour les dates dans le futur
SELECT 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 TABLEBASEFLOR (`catminat_code`);
CREATE INDEX i_num_taxon ON TABLEBDTXAVERNA (`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` INT(9) DEFAULT NULL COMMENT 'depuis bdtfx',
 
-- bdtfx + TABLEBDTFXVERNA + TABLEBDTXAVERNA
`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
 
-- 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,
 
-- TABLEBDTFXVERNA (`nom_vernaculaire` text NOT NULL)
-- mais NULL à cause de nva
`nom_commun` VARCHAR(60) NULL COMMENT 'nom_vernaculaire pour TABLEBDTFXVERNA et TABLEBDTXAVERNA',
 
-- TABLEBASEFLOR
`catminat_code` varchar(18) DEFAULT 'inconnu' COMMENT 'depuis TABLEBASEFLOR',
`ve_lumiere` int(1) DEFAULT NULL COMMENT 'depuis TABLEBASEFLOR',
`ve_temperature` int(1) DEFAULT NULL COMMENT 'depuis TABLEBASEFLOR',
`ve_continentalite` int(1) DEFAULT NULL COMMENT 'depuis TABLEBASEFLOR',
`ve_humidite_atmos` int(1) DEFAULT NULL COMMENT 'depuis TABLEBASEFLOR',
`ve_humidite_edaph` int(2) DEFAULT NULL COMMENT 'depuis TABLEBASEFLOR',
`ve_reaction_sol` int(1) DEFAULT NULL COMMENT 'depuis TABLEBASEFLOR',
`ve_nutriments_sol` int(1) DEFAULT NULL COMMENT 'depuis TABLEBASEFLOR',
`ve_salinite` int(1) DEFAULT NULL COMMENT 'depuis TABLEBASEFLOR',
`ve_texture_sol` int(1) DEFAULT NULL COMMENT 'depuis TABLEBASEFLOR',
`ve_mat_org_sol` int(1) DEFAULT NULL COMMENT 'depuis TABLEBASEFLOR',
 
-- TABLEBASEVEG
`syntaxon` varchar(255) NULL COMMENT 'depuis TABLEBASEVEG',
PRIMARY KEY (`referentiel`, `num_nom`),
INDEX (`referentiel`(5)),
INDEX (`num_nom`),
INDEX (`num_nom_retenu`),
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 temporaires
DROP TEMPORARY TABLE IF EXISTS `T_TABLEBDTFXVERNA`, `T_TABLEBDTXAVERNA`, `T_basevegflor`;
 
-- 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)
CREATE TEMPORARY TABLE T_TABLEBDTFXVERNA ( INDEX(`num_taxon`) ) AS
-- ( 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) );
-- ( 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) );
( 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 );
 
-- table temporaire uniquement parce qu'il manque un index-key, autrement le LEFT JOIN ci-dessous est bien trop long
CREATE TEMPORARY TABLE T_TABLEBDTXAVERNA ( INDEX(`num_taxon`) ) AS
-- ( 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
-- 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`.`TABLEBDTXAVERNAINDEX` 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.sql
CREATE 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`.`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"
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
 
 
-- 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.syntaxon
FROM `BASESOURCE`.`TABLEBDTFX` b LEFT JOIN T_TABLEBDTFXVERNA 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_TABLEBDTXAVERNA 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_TABLEBDTFXVERNA`, `T_TABLEBDTXAVERNA`, `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)
/tags/v5.8-aspergeraie/scripts/modules/cel/maj-referentiel-201307.sql
New file
0,0 → 1,78
/*
Mise à jour de réferentiels NULL ou vides pour les observations dotées d'un nom_sel_nn
75427 observations trouvées au 2013/07/19
 
Les observations problématiques sont les suivantes:
SELECT id_observation, nom_referentiel, nom_sel, nom_sel_nn, nom_ret, nom_ret_nn, nt, famille
FROM `BASEEDIT`.`cel_obs`
WHERE nom_referentiel IS NULL AND nom_sel != '' AND nom_sel IS NOT NULL AND nom_ret_nn IS NOT NULL;
 
Or maj-cleanup-201307.sql reset les valeurs des observations ayant un nom_sel NULL ou '', de plus la préférence de sélection est
donné au nom_sel (incluant les synonymes) plutôt que nom_ret_nn.
 
La requête est donc:
SELECT id_observation, nom_referentiel, nom_sel, nom_sel_nn, nom_ret, nom_ret_nn, nt, famille
FROM `BASEEDIT`.`cel_obs` WHERE nom_referentiel IS NULL AND nom_sel_nn IS NOT NULL;
=> 76486
*/
 
 
DROP TEMPORARY TABLE IF EXISTS T_bis;
DROP PROCEDURE IF EXISTS majreferentiel;
 
CREATE TEMPORARY TABLE IF NOT EXISTS T_bis ( INDEX(`nom`(30))) AS \
SELECT "bdtfx" AS valid_ref, CONCAT(b.nom_sci, ' ', b.auteur) AS nom, b.num_nom, b.num_taxonomique, b.famille FROM `BASESOURCE`.`TABLEBDTFX` b UNION ALL \
SELECT "bdtxa" AS valid_ref, CONCAT(a.nom_sci, ' ', a.auteur) AS nom, a.num_nom, a.num_tax, a.famille FROM `BASESOURCE`.`TABLEBDTXA` a UNION ALL \
SELECT "isfan" AS valid_ref, CONCAT(i.nom_sci, ' ', i.auteur) AS nom, i.num_nom, i.num_taxonomique, i.famille FROM `BASESOURCE`.`TABLEISFAN` i;
 
/* Donc nous JOINons:
-- INNER JOIN sur bdtfx: 62633
SELECT id_observation, nom_referentiel, nom_sel, nom_sel_nn, nom_ret, nom_ret_nn, nt, c.famille FROM `BASEEDIT`.`cel_obs` c INNER JOIN `BASESOURCE`.`TABLEBDTFX` b ON (b.num_nom = c.nom_sel_nn) WHERE nom_referentiel IS NULL AND nom_sel_nn IS NOT NULL;
-- INNER JOIN sur bdtxa: 9469
SELECT id_observation, nom_referentiel, nom_sel, nom_sel_nn, nom_ret, nom_ret_nn, nt, c.famille FROM `BASEEDIT`.`cel_obs` c INNER JOIN `BASESOURCE`.`TABLEISFAN` i ON (i.num_nom = c.nom_sel_nn) WHERE nom_referentiel IS NULL AND nom_sel_nn IS NOT NULL;
-- INNER JOIN sur isfan: 1991
SELECT id_observation, nom_referentiel, nom_sel, nom_sel_nn, nom_ret, nom_ret_nn, nt, c.famille FROM `BASEEDIT`.`cel_obs` c INNER JOIN `BASESOURCE`.`TABLEISFAN` i ON (i.num_nom = c.nom_sel_nn) WHERE nom_referentiel IS NULL AND nom_sel_nn IS NOT NULL;
-- INNER JOIN sur les 3 référentiels (bdtxa + bdtfx + isfan): 74093
SELECT id_observation, valid_ref, nom_sel, nom_sel_nn, nom_ret, nom_ret_nn, nt, c.famille FROM `BASEEDIT`.`cel_obs` c INNER JOIN T_bis b ON (b.num_nom = c.nom_sel_nn) WHERE nom_referentiel IS NULL AND nom_sel_nn IS NOT NULL;
*/
 
/* mais de nombreux noms sont exactement présents dans plusieurs référentiels,
d'où GROUP BY id_observation HAVING count(id_observation) = 1,
ce qui ne produit plus que 51359 matches (soit 22734 dups) */
/*
SELECT id_observation, valid_ref, nom_sel, nom, nom_sel_nn, nom_ret, nom_ret_nn, nt, c.famille
FROM `BASEEDIT`.`cel_obs` c
INNER JOIN T_bis b
ON (b.num_nom = c.nom_sel_nn)
WHERE nom_referentiel IS NULL AND nom_sel_nn IS NOT NULL GROUP BY id_observation HAVING count(id_observation) = 1;
-- 63941, tous bdtfx...
*/
 
 
delimiter |
 
CREATE PROCEDURE majreferentiel()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE subst INT DEFAULT 0;
DECLARE _id_observation bigint(20) DEFAULT 0;
DECLARE _valid_ref varchar(20) DEFAULT NULL;
DECLARE cur1 CURSOR FOR SELECT id_observation, valid_ref FROM `BASEEDIT`.`cel_obs` c INNER JOIN T_bis b ON (b.num_nom = c.nom_sel_nn)
WHERE nom_referentiel IS NULL AND nom_sel_nn IS NOT NULL GROUP BY id_observation HAVING count(id_observation) = 1;
-- DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
 
OPEN cur1;
REPEAT
FETCH cur1 INTO _id_observation, _valid_ref;
UPDATE `BASEEDIT`.`cel_obs` c SET nom_referentiel = _valid_ref WHERE id_observation = _id_observation;
SET subst = subst + 1;
UNTIL done END REPEAT;
select subst AS 'nombre de mises à jour de référentiel effectuées';
CLOSE cur1;
END
|
 
delimiter ;
 
CALL majreferentiel;
/tags/v5.8-aspergeraie/scripts/modules/cel/cel_references.sql
New file
0,0 → 1,128
/*
TODO:
* fix référentiel: suppression n° de version et uniformisation
SELECT 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 futur
SELECT 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 temporaires
DROP 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 long
CREATE 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.sql
CREATE 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.syntaxon
FROM `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)
/tags/v5.8-aspergeraie/scripts/modules/cel/dedup-ordre-201307.sql
New file
0,0 → 1,109
-- suppress dup:
-- >= 115 au 22/07/2013
-- mysql -N <<<"SELECT distinct ce_utilisateur FROM `BASEEDIT`.`cel_obs` GROUP BY ce_utilisateur, ordre HAVING COUNT(*) > 1;" > ordre-dup.txt
 
DROP FUNCTION IF EXISTS next_ordre;
DROP PROCEDURE IF EXISTS ordre_need_update;
DROP PROCEDURE IF EXISTS update_ordre_user;
DROP PROCEDURE IF EXISTS update_ordre_users;
 
DELIMITER |
 
CREATE FUNCTION next_ordre( s1 VARCHAR(255) )
RETURNS INT
READS SQL DATA
BEGIN
DECLARE c INT;
SET c = (SELECT MAX(ordre) + 1 FROM `BASEEDIT`.`cel_obs` where ce_utilisateur = s1);
RETURN c;
END
|
-- SELECT next_ordre("");
 
CREATE PROCEDURE ordre_need_update(IN _s1 VARCHAR(255), OUT _ordre INT, OUT _c INT, OUT _min_obs INT)
BEGIN
SELECT ordre, count(ordre), MIN(id_observation) INTO _ordre, _c, _min_obs FROM `BASEEDIT`.`cel_obs` WHERE ce_utilisateur = _s1 GROUP BY ordre HAVING COUNT(ordre) > 1 LIMIT 1;
END
|
-- SELECT ordre_need_update("");
 
CREATE PROCEDURE update_ordre_user(IN _s1 VARCHAR(255))
BEGIN
DECLARE obs_match int default -1;
CALL ordre_need_update(_s1, @o, @c, @minobs);
-- pour chaque ordre dupliqué
WHILE @o IS NOT NULL DO
SELECT CONCAT(" ", @o) as " ordre", @c as "(count/doublons)";
-- SELECT id_observation FROM `BASEEDIT`.`cel_obs` WHERE ce_utilisateur = _s1 AND ordre = @o AND id_observation != @minobs;
-- pour chaque obs concernée, exceptée la première, on met à jour l'ordre,
-- en utilisant next_ordre()
WHILE obs_match != 0 DO
-- SELECT CONCAT("== do update on", @o);
UPDATE `BASEEDIT`.`cel_obs` SET ordre = next_ordre(_s1)
WHERE ce_utilisateur = _s1 AND ordre = @o AND id_observation != @minobs LIMIT 1;
SELECT ROW_COUNT() into obs_match;
-- SELECT @o, obs_match;
END WHILE;
-- toutes les observations dupliquées pour l'ordre @o ont été mises à jour
-- un nouvel ordre à mettre à jour va être obtenu par ordre_need_update()
-- dont nous restaurons obs_match à une valeur qui n'empêche pas la boucle
-- contenant l'UPDATE
SELECT -1 into obs_match;
CALL ordre_need_update(_s1, @o, @c, @minobs);
-- SELECT "====X", @o, @c;
END WHILE;
END
|
-- CALL update_ordre_user("");
 
 
CREATE PROCEDURE update_ordre_users()
BEGIN
DECLARE _nom VARCHAR(255);
DECLARE subst INT DEFAULT 0;
DECLARE done INT DEFAULT 1;
 
-- temp table
-- the following fails, pas d'index (see EXPLAIN + http://dba.stackexchange.com/questions/48231 ?)
-- ( SELECT DISTINCT ce_utilisateur FROM `BASEEDIT`.`cel_obs` GROUP BY ce_utilisateur, ordre HAVING COUNT(*) > 1 );
IF (SELECT SUBSTR(version(),3,1)) != 5 THEN
-- mais celle-ci fonctionne, car l'ordre du GROUP BY correspond à l'INDEX [id_obs] : 16 secondes
CREATE TEMPORARY TABLE IF NOT EXISTS _temp_users (ce_utilisateur VARCHAR(255)) ENGINE=MEMORY AS \
( SELECT DISTINCT ce_utilisateur FROM `BASEEDIT`.`cel_obs` GROUP BY ordre, ce_utilisateur HAVING COUNT(1) > 1 );
ELSE
-- alternativement, comme solution de replis (nécessaire pour MariaDB ?):
CREATE TEMPORARY TABLE IF NOT EXISTS _temp_users (ce_utilisateur VARCHAR(255)) ENGINE=MEMORY AS \
( SELECT DISTINCT ce_utilisateur FROM `BASEEDIT`.`cel_obs` WHERE ce_utilisateur IN \
(SELECT ce_utilisateur FROM `BASEEDIT`.`cel_obs` GROUP BY ce_utilisateur, ordre HAVING COUNT(1) > 1) );
END IF;
 
SELECT COUNT(*) INTO done FROM _temp_users;
-- la requête principale de sélection des utilisateurs à mettre à jour
WHILE done > 0 DO
SELECT ce_utilisateur INTO _nom FROM _temp_users LIMIT 1;
SELECT _nom AS "utilisateur en mise à jour:";
CALL update_ordre_user(_nom);
SET subst = subst + 1;
DELETE FROM _temp_users WHERE ce_utilisateur = _nom;
SELECT COUNT(*) INTO done FROM _temp_users;
END WHILE;
SELECT subst AS "utilisateurs mis à jour";
END
|
 
DELIMITER ;
 
 
CALL update_ordre_users();
 
 
DROP FUNCTION IF EXISTS next_ordre;
DROP PROCEDURE IF EXISTS ordre_need_update;
DROP PROCEDURE IF EXISTS update_ordre_user;
DROP PROCEDURE IF EXISTS update_ordre_users;
 
-- clef unique sur (id_utilisateur, ordre)
-- [mais seulement si on a dédupliqué TOUS les utilisateurs, y compris l'utilisateur ''
-- à voir aussi: maj-hash-id-obs-migr.sql]
DROP INDEX `id_obs` ON `BASEEDIT`.`cel_obs`;
CREATE UNIQUE INDEX `id_obs` ON `BASEEDIT`.`cel_obs` (`ce_utilisateur` ASC, `ordre` ASC);
/tags/v5.8-aspergeraie/scripts/modules/cel/A_LIRE.txt
New file
0,0 → 1,117
Créer une base de données tb_cel avant de lancer les scripts
 
== Sommaire ==
1) à propos de la mise à jour de septembre 2013
2) à propos de la table cel_references
==============
 
1) cel_references.sql
création de la table `cel_references`
 
2) maj-struct-201307.sql
mise à jour des structure de table (les index notamment):
 
3) maj-cleanup-201307.sql
uniformisation des données (lon/lat, date, ...)
et des NULL vs 0 (pour nom_sel_nn et nom_ret_nn)
 
4) fix-utilisateur-32.sql
 
5) dedup-ordre-201307.sql
 
6) maj-referentiel-201307.sql
fix le référentiel pour les observation ayant un nom_sel_nn sans nom_referentiel en se
basant sur une match exact de CONCAT(nom_sci, auteur) parmi bdtfx, bdtxa et isfan
 
7) referonosaure.sql
MAJ des observations (valides) avec les nouvelles données générées, à partir de bdtfx/bdtxa/isfan
 
=====
8) TODO: maj-nom-ret.sql
TODO (pas sûr) MAJ du référentiel pour les observation ayant un nom_ret sans nom_ret_nn mais dont le nom_ret
ne match pas le nom_sci en BDTFX (car en BDTFX nom_ret_nn peut être égal à 0 !)
 
9) maj-referentiel-und-201307.sql
MAJ du référentiel pour les observation n'ayant pas de nom_ret_nn (tentative de détermination par nom)
 
 
 
 
 
 
 
 
=== 2: À propos de la table cel_references ===
Celle-ci existe car:
* les projets doivent être indépendants (eflore, cel, projets nvjfl, ...)
* les données nécessaires à l'export et à l'import sont massives
* or les webservices s'appellent parfois récursivement, sont lents et inadaptés
 
La conséquence est que la construction d'une table dérivée de bdtfx/bdtxa/isfan contenant
les informations utiles pour CEL s'avère nécessaire.
cel_references.sql construit une telle table.
 
Suivent quelques éléments de compréhension et exemples de requêtes liés à cette initialisation:
 
1) Détermination des noms vernaculaires meilleurs et uniques:
 
Ce sont ceux qui ont le num_statut le plus élevé pour un num_taxon donné dans nvjfl_v2007.
Plusieurs méthodes sont exposées ci-dessous, sachant que le couple (référentiel, num_nom) est la clef
unique de cel_references.
Il existe à ce jour 16146 nom communs français distincts, 12312 num_taxon pour code_lang = fra et aucun num_statut NULL en français.
1.1:
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;
# 12311 résultats
 
1.2:
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;
# 2680 résultats
 
1.3:
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;
# 2680 résultats
Mais problème ensuite: SELECT n.* from cel_references NATURAL JOIN nvjfl_v2007 n WHERE `nom_commun` = '' AND n.code_langue = 'fra';
 
 
 
2) à propos de l'insertion dans cel_references proprement dit:
Le modèle simplifié théorique de base est le suivant:
 
INSERT INTO @dst (`referentiel`, `num_nom`, `num_nom_retenu`, `nom_sci`, `auteur`, `nom_commun`) \
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 \
UNION \
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 \
UNION \
SELECT "isfan", b.num_nom, b.num_nom_retenu, b.nom_sci, b.auteur, NULL FROM isfan_v2013 b;
 
Mais évidemment, les noms communs n'existent que pour bdtfx[nvjfl], bdtxa[nva], de même que les données baseflor/baseveg.
Plusieurs tables temporaires sont donc nécessaires en particulier puisque toutes les colonnes n'ont pas
des indexes adaptés pour effectuer des JOIN efficaces dans le cadre de ce script d'intégration particulier.
 
Une version plus aboutie, mais spécifique à bdtfx, après création préalable de T_nvjfl_v2007, était la suivante (présence des noms communs):
INSERT INTO @dst (`referentiel`, `num_nom`, `num_nom_retenu`, `num_taxon`, `nom_sci`, `auteur`, `nom_commun`) \
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 );
 
 
À noter:
SELECT b.num_nom, b.num_nom_retenu, b.num_taxonomique, b.nom_sci FROM bdtfx_v1_01 b where b.num_taxonomique = '';
# 3968, c'est à dire des num_taxon vides, pourtant INDEX et NOT NULL.
Idem pour bdtxa
 
 
3) à propos de baseveg/baseflor:
Note au 16/07/2013: les schémas sont susceptibles de changer à l'avenir.
La jointure entre bdtfx et baseflor se fait sur le référentiel ("bdtfx") et num_nom.
À partir de là nous disposons d'un catminat_code qui correspond au code_catminat dans baseveg, afin d'obtenir le syntaxon.
Quelques exemples:
 
SELECT code_catminat, syntaxon, lumiere, hum_atmos, temperature, oceanite, ph_sol, hum_edaph, texture_sol FROM baseveg_v2013_01_09;
SELECT * from baseflor_v2012_12_31 where cle = 1174;
 
SELECT * from baseveg_v2013_01_09 where code_catminat = '05/3.0.1.0.2' AND niveau = 'ALL';
# 7 résultats
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 FROM baseflor_v2012_12_31 f LEFT JOIN baseveg_v2013_01_09 v ON (f.catminat_code = v.code_catminat) WHERE f.BDNT = "BDTFX" and f.cle = 1174;
# 7 résultats
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 FROM baseflor_v2012_12_31 f LEFT JOIN baseveg_v2013_01_09 v ON (f.catminat_code = v.code_catminat AND v.niveau = 'ALL') WHERE f.BDNT = "BDTFX" and f.cle = 1174;
# 1 résultat
Nous utilisons v.niveau = ALL pour nous assurer la présence d'un seul f.num_nomen dans `T_basevegflor` et donc assurer l'unicité de la PRIMARY KEY de `cel_references`
Property changes:
Added: svn:eol-style
+native
\ No newline at end of property
/tags/v5.8-aspergeraie/scripts/modules/cel/redempteur.sql
New file
0,0 → 1,114
/*
À l'origine les observations nécessiteuses sont celles n'ayant pas de données génénées,
Soit: nom_ret, nom_ret_nn, nt ou famille à NULL|0|''
 
Eg:
SELECT id_observation, nom_sel
FROM `BASEEDIT`.`cel_obs`
WHERE (
nom_ret IS NULL or nom_ret = ''
OR nom_ret_nn IS NULL or nom_ret_nn = 0
OR nt IS NULL or nt = 0
OR famille IS NULL or famille = ''
)
 
Sauf que:
1) on exclue celles sans nom_sel (elles sont sans espoir):
nom_sel IS NOT NULL AND nom_sel != ''
2) on exclue celles qui on un nom_ret_nn à 0, car cela peut-être légal, cf maj-201307.sql à ce propos
# donc pas de `nom_ret_nn = 0` dans la requête
3) on exclue, dans un premier temps, celles dont le référentiel n'est pas défini
AND (nom_referentiel IS NULL)
 
D'où, les 3621 observations suivantes (2206 nom_sel distincts)
SELECT id_observation, nom_sel
FROM `BASEEDIT`.`cel_obs`
WHERE (
nom_sel IS NOT NULL AND nom_sel != ''
AND (
nom_ret IS NULL OR nom_ret = ''
OR nom_ret_nn IS NULL
OR nt IS NULL or nt = 0
OR famille IS NULL or famille = ''
)
AND (nom_referentiel IS NOT NULL)
)
 
Dans un premier temps nous travaillons avec le bdtfx, c'est à dire que
AND (nom_referentiel IS NOT NULL)
devient
AND (nom_referentiel like 'bdtfx%')
soit 3597/3621 observations:
 
Et effectuons une jointure sur bdtfx:
SELECT id_observation, nom_sel, b.num_nom, b.famille
FROM `BASEEDIT`.`cel_obs` c INNER JOIN `BASESOURCE`.`TABLEBDTFX` b ON (b.nom_sci = c.nom_sel)
WHERE (
nom_sel IS NOT NULL AND nom_sel != ''
AND (
nom_ret IS NULL OR nom_ret = ''
OR nom_ret_nn IS NULL
OR nt IS NULL OR nt = 0
OR c.famille IS NULL OR c.famille = ''
)
AND (nom_referentiel like 'bdtfx%')
)
 
* Or nous observons que la famille est parfois légitimement NULL ! Ce n'est pas pertinent de l'utiliser
comme critère de caractérisation d'une observation buggée, contentons-nous donc de empty ('')
 
* Or nous observons que le numéro taxonomique est parfois légitimement 0 ! Ce n'est pas pertinent de l'utiliser
comme critère de caractérisation d'une observation buggée, contentons-nous donc de NULL
 
 
 
Soit 84 lignes, cependant, un nom_sel peut correspondre à plusieurs num_nom_retenu dans bdtfx ! (et oui, les suffixes latins et d'auteur).
Il s'agit donc de ne pas traiter ceux qui risquerait d'être mal-corrigé (sans les 100% de certitude).
Ainsi un ` GROUP BY id_observation HAVING count(id_observation) = 1 ` sera du meilleur effet.
 
Nous obtenons donc ainsi les 69 observations à mettre à jour:
SELECT id_observation, nom_sel, nom_ret, nom_ret_nn, nt, c.famille, b.num_nom, b.nom_sci, b.num_taxonomique, b.famille
FROM `BASEEDIT`.`cel_obs` c INNER JOIN `BASESOURCE`.`TABLEBDTFX` b ON (b.nom_sci = c.nom_sel)
WHERE (
nom_sel IS NOT NULL AND nom_sel != ''
AND (
nom_ret IS NULL OR nom_ret = ''
OR nom_ret_nn IS NULL
OR nt IS NULL
OR c.famille = ''
)
AND (nom_referentiel like 'bdtfx%')
)
GROUP BY id_observation HAVING count(id_observation) = 1
 
 
=== la mise à jour ===
Comme nous voulons utiliser UPDATE, nous devons remplacer le JOIN par des conditions du WHERE, mais le GROUP BY bloque de
toute manière, un SUB-SELECT (table temporaire) est donc nécessaire:
 
=== finale ===
*/
 
CREATE TEMPORARY TABLE T_bis ( INDEX(`id_observation`)) AS
SELECT id_observation, b.num_nom, CONCAT(b.nom_sci, ' ', b.auteur), b.num_taxonomique, b.famille
FROM `BASEEDIT`.`cel_obs` c, `BASESOURCE`.`TABLEBDTFX` b
WHERE (
b.nom_sci = c.nom_sel
AND nom_sel IS NOT NULL AND nom_sel != ''
AND (
nom_ret IS NULL OR nom_ret = ''
OR nom_ret_nn IS NULL
OR nt IS NULL OR nt = 0
OR c.famille = ''
)
AND (nom_referentiel like 'bdtfx%')
)
GROUP BY id_observation HAVING count(id_observation) = 1
 
UPDATE `BASEEDIT`.`cel_obs` c, T_bis t SET
c.nom_ret = t.nom_sci,
c.nom_ret_nn = t.num_nom,
c.nt = t.num_taxonomique,
c.famille = t.famille
WHERE (c.id_observation = t.id_observation);
DROP TEMPORARY TABLE T_bis;
/tags/v5.8-aspergeraie/scripts/modules/cel/referonosaure.sql
New file
0,0 → 1,116
/*
 
Objectif: prendre les observations dont nom_sel_nn est défini
(et donc dans laquelles les informations générées sont correctes)
et mettre à jour ces dernières à partir de la dernière version du référentiel
(bdtfx, bdtxa et isfan).
 
Pour éviter un maximum de faux-positifs, nous vérifions aussi que la famille
est conservée (même dans certains cas celle-ci a légitimement changé) et que
la première partie du nom_sel correspond toujours à la première partie du nouveau nom_sci
qui serait attribué.
 
-- la requête --
-- SELECT id_observation, b.num_nom, CONCAT(b.nom_sci, ' ', b.auteur), b.num_taxonomique, b.famille
SELECT id_observation, nom_ret, nom_ret_nn, nt, c.famille
FROM `BASEEDIT`.`cel_obs` c, `BASESOURCE`.`TABLEBDTFX` b
WHERE (
nom_sel_nn IS NOT NULL
AND nom_referentiel like 'bdtfx%'
AND nom_sel_nn = num_nom
)
ORDER BY id_observation asc;
 
 
Cependant le nom_ret_nn n'est pas directement le num_num du taxon dont le nom est
retenu. Pour cela, une jointure en bdtfx sur num_nom_retenu est nécessaire et c'est
ce dernier taxon dont le num_nom est utilisé pour nom_ret_nn.
Cependant il peut aussi être vide (si aucun nom_retenu "officiel" n'existe).
 
Attention, les nom_sel_nn = 0 doivent avoir disparus de cel_obs *AU PRÉALABLE* car le test
n'est pas effectué.
cf: maj-cleanup-201307.sql
 
Ici, contrairement à referonosaure_fromNomRet.sql, nous partons du nom_sel en admettant qu'il est
toujours correct et c'est donc sur ce champ que s'effectue la jointure.
Quelques exceptions notables existent cependant:
- certaines observations issues de sauvages sont corrompues, leur nom_sel_nn n'est donc PAS fiable
- il a été remarqué des observations pour lesquelles le nom_sel_nn était corrompu, impliquant une changement
de nom de famille incohérent. Pour se prémunir de cela, la famille doit être identique ou presque.
- enfin, la première partie du nom_sel doit matcher exactement la première partie du nom_sci
 
Consulter referonosaure_fromNomRet.sql pour des informations complémentaires.
*/
 
 
 
/* test:
SELECT c.nom_ret_nn, c.nom_ret, bLAST.num_nom, bLAST.nom_sci, bLAST.auteur, c.famille, bLAST.famille, c.nt, bLAST.num_taxonomique
FROM cel_obs c, tb_eflore.bdtfx_v1_01 b, tb_eflore.bdtfx_v1_01 bLAST
WHERE (
bLAST.num_nom = b.num_nom_retenu
AND nom_sel_nn IS NOT NULL AND nom_ret_nn IS NOT NULL AND nom_ret_nn != 0 AND nom_referentiel = 'bdtfx'
AND nom_ret_nn = bLAST.num_nom
AND (LOWER(c.famille) = LOWER(b.famille) OR c.famille IS NULL)
AND (c.famille != b.famille OR c.nom_ret != CONCAT(bLAST.nom_sci, ' ', bLAST.auteur) OR c.nt != b.num_taxonomique OR c.nom_ret_nn != bLAST.num_nom)
);
*/
 
-- l'update BDTFX avec nom_sel_nn seul
UPDATE `BASEEDIT`.`cel_obs` c, `BASESOURCE`.`TABLEBDTFX` b, `BASESOURCE`.`TABLEBDTFX` b_nom_ret SET
c.nom_ret = CONCAT(b_nom_ret.nom_sci, ' ', b_nom_ret.auteur),
c.nom_ret_nn = b_nom_ret.num_nom,
c.nt = b.num_taxonomique,
c.famille = b.famille,
c.date_modification = NOW() -- a supprimer pour estimer le nombre de changements réel
WHERE (
b_nom_ret.num_nom = b.num_nom_retenu
AND nom_sel_nn IS NOT NULL
AND nom_referentiel = 'bdtfx'
AND nom_sel_nn = b.num_nom
-- TODO: bug transferts multiples + mobile.js
-- Note: SELECT IF(NULL NOT LIKE "%blah%", 1, 0) : 0
AND (c.mots_cles_texte IS NULL OR c.mots_cles_texte NOT LIKE '%WidgetFlorileges Sauvages%')
AND (LOWER(c.famille) = LOWER(b.famille) OR c.famille IS NULL OR c.famille = 'Famille inconnue')
AND SUBSTRING_INDEX(c.nom_sel, ' ', 1) = SUBSTRING_INDEX(b.nom_sci, ' ', 1)
);
-- 42315 avec indirection num_nom_retenu
SELECT ROW_COUNT() AS "BDTFX upd après correction sur nom_sel_nn";
 
 
-- l'update BDTXA avec nom_sel_nn seul
UPDATE `BASEEDIT`.`cel_obs` c, `BASESOURCE`.`TABLEBDTXA` a, `BASESOURCE`.`TABLEBDTXA` a_nom_ret SET
c.nom_ret = CONCAT(a_nom_ret.nom_sci, ' ', a_nom_ret.auteur),
c.nom_ret_nn = a_nom_ret.num_nom,
c.nt = a.num_tax,
c.famille = a.famille,
c.date_modification = NOW()
WHERE (
a_nom_ret.num_nom = a.num_nom_retenu
AND nom_sel_nn IS NOT NULL
AND nom_referentiel = 'bdtxa'
AND nom_sel_nn = a.num_nom
AND (LOWER(c.famille) = LOWER(a.famille) OR c.famille IS NULL)
AND SUBSTRING_INDEX(c.nom_sel, ' ', 1) = SUBSTRING_INDEX(a.nom_sci, ' ', 1)
);
-- 49 avec les restrictions sur famille et SUBSTRING_INDEX()
-- 48 sans les restrictions sur famille et SUBSTRING_INDEX()
SELECT ROW_COUNT() AS "BDTXA upd après correction sur nom_sel_nn";
 
 
-- l'update ISFAN avec nom_sel_nn seul
UPDATE `BASEEDIT`.`cel_obs` c, `BASESOURCE`.`TABLEISFAN` i, `BASESOURCE`.`TABLEISFAN` i_nom_ret SET
c.nom_ret = CONCAT(i_nom_ret.nom_sci, ' ', i_nom_ret.auteur),
c.nom_ret_nn = IF(i_nom_ret.num_nom=0,NULL,i_nom_ret.num_nom),
c.nt = i.num_taxonomique,
c.famille = i.famille,
c.date_modification = NOW()
WHERE (
i_nom_ret.num_nom = i.num_nom_retenu
AND nom_sel_nn IS NOT NULL
AND nom_referentiel = 'isfan'
AND nom_sel_nn = i.num_nom
AND (LOWER(c.famille) = LOWER(i.famille) OR c.famille IS NULL)
);
-- 0
SELECT ROW_COUNT() AS "ISFAN upd après correction sur nom_sel_nn";
/tags/v5.8-aspergeraie/scripts/modules/cel/Makefile
New file
0,0 → 1,195
# Ce Makefile effectue les substitutions de nom de base de données
# nécessaire au préalable de l'application des scripts SQL
 
# Cela est d'un part moins complexe:
# - qu'un script PHP (interpréteur, getopt, framework, ...)
# - qu'un shell-script (lancement avec make)
# et d'autre part plus maintenable qu'un shell-script car
# le versionnage des fichiers (inc ".current") permet certaines facilités.
 
 
# TODO:
# idéalement, ce Makefile devrait permettre une bonne gestion du jeu de dépendances
# entre les scripts, seulement le lancement d'un script pouvant nécessiter un login/mdp
# il est difficile de vouloir rester "simple".
# Ce serait cependant la meilleure manière de procéder, ainsi "maj2" ne serait lancé qu'en
# cas de succès de "maj1", celui-ci pouvant être détecté comme "déjà exécuté" ou non.
# cf target "maj1" ci-dessous
 
 
# à l'aide de, note certains de ces fichiers n'ont pas cours dans le cadre de la maj1 (septembre 2013)
# echo $(egrep -l 'BASE(SOURCE|EDIT|ANNUAIRE)' *.sql)
fichiers = cel_references.sql dedup-ordre-201307.sql fix-utilisateur-32.sql maj-cleanup-201307.sql maj-nom-ret.sql \
maj-referentiel-201307.sql maj-referentiel-und-201307.sql maj-struct-201307.sql redempteur.sql \
referonosaure.sql \
.current
 
# la base de données à modifier
alterdb ?= tb_cel_test
 
# pour bdtfx, bdtxa, isfan, nvjfl, nva, baseflor, ... lecture seule;
# utilisée pour actualiser les enregistrements de cel_obs dans referonosaure.sql
sourcedb ?= tb_eflore
 
# pour annuaire_tela, lecture seule;
# utilisée pour initialiser cel_utilisateurs dans maj-struct-201307.sql
annuairedb ?= tela_prod_v4
 
bdtfx ?= 1_01
bdtxa ?= 1_00
isfan ?= 2013
bdtfx_table = bdtfx_v$(bdtfx)
bdtxa_table = bdtxa_v$(bdtxa)
isfan_table = isfan_v$(isfan)
 
# TODO: simply override bdd_user
ifdef bdd_user
bdd_user_h = -u$(bdd_user)
endif
 
ifneq ($(origin bdd_pass), undefined)
bdd_pass_h = "-p$(bdd_pass)"
endif
 
mysqlbin ?= mysql
mysqlcmd = $(mysqlbin) $(bdd_user_h) $(bdd_pass_h)
 
# macro utilisable pour les targets nécessitant de tester la présence d'un couple (base,table)
# exemples:
# * $(call is_table,tb_eflore,bdtfx_v1_01)
# * $(call is_table,$(annuairedb),annuaire_tela)
# argument 1: base de données
# argument 2: table
is_table = $(mysqlcmd) -N $(1) <<<"DESC $(2)" &> /dev/null
 
# macro utilisable pour effectuer des substitutions:
do_subst = sed -e "1i--\n-- fichier d'origine: \"${1}\"\n" \
-e 's/`BASEEDIT`/`$(alterdb)`/g' \
-e 's/`BASEANNUAIRE`/`$(annuairedb)`/g' \
-e 's/`BASESOURCE`/`$(sourcedb)`/g' \
-e 's/`TABLEBDTFX`/`$(bdtfx_table)`/g' \
-e 's/`TABLEBDTXA`/`$(bdtxa_table)`/g' \
-e 's/`TABLEISFAN`/`$(isfan_table)`/g' \
-e 's/TABLEBDTFX/`$(bdtfx_table)`/g' \
-e 's/TABLEBDTXA/`$(bdtxa_table)`/g' \
-e 's/TABLEISFAN/`$(isfan_table)`/g' \
$(1)
 
# default target
help:
@echo "make [alterdb=<$(alterdb)>] [sourcedb=<$(sourcedb)>] [annuairedb=<$(annuairedb)>] [bdtfx=<$(bdtfx)>] [bdtxa=<$(bdtxa)>] [isfan=$(isfan)] [bdd_user=\"\"] [bdd_pass=\"\"] [mysqlbin=mysql]"
@echo "make o_maj1 mysqlbin=/usr/local/mysql/bin/mysql bdd_user=telabotap bdd_pass=XXX"
 
# génère les fichiers avec les bases de données souhaitées
compile: reset
sed -i -e 's/`BASEEDIT`/`$(alterdb)`/g' \
-e 's/`BASEANNUAIRE`/`$(annuairedb)`/g' \
-e 's/`BASESOURCE`/`$(sourcedb)`/g' \
-e 's/TABLEBDTFX/`$(bdtfx_table)`/g' \
-e 's/TABLEBDTXA/`$(bdtxa_table)`/g' \
-e 's/`TABLEISFAN`/`$(isfan_table)`/g' \
$(fichiers)
printf "Attention: les changements s'appliqueront sur la base \"%s\"\nLes sources utilisées seront: annuaire=\"%s\" , sources=\"%s\" (%s,%s,%s)\n(Ctrl+C pour interrompre, Enter pour continuer)\n" \
`grep ^BASEEDIT .current|cut -d '\`' -f2` \
`grep ^BASEANNUAIRE .current|cut -d '\`' -f2` \
`grep ^BASESOURCE .current|cut -d '\`' -f2` \
`grep ^TABLE_BDTFX .current|cut -d '=' -f2` \
`grep ^TABLE_BDTXA .current|cut -d '=' -f2` \
`grep ^TABLE_ISFAN .current|cut -d '=' -f2`
read
 
reset:
svn revert -q $(fichiers)
 
# supprime les fichiers "compilés" (concaténation de plusieurs scripts SQL substitués)
clean:
rm -f *.comp.sql
 
### mises à jour
 
# mise à jour de septembre 2013
# spécifier les targets dans l'ordre (cf A_LIRE.txt)
 
 
# première version: substitution des fichiers: pas bon
# attention, si un prérequis ne génère pas de SQL, cela n'empêchera pas le fichier
# final de maj d'être généré,
#maj1: compile cel_references maj-struct-201307 maj-cleanup-201307 fix-utilisateur-32 dedup-ordre-201307 maj-referentiel-201307
# echo done
 
o_maj1: fichiers_generes = $(addsuffix .comp.sql,$(filter-out clean,$?))
o_maj1: clean o_cel_references o_maj-struct-201307 o_maj-cleanup-201307 o_fix-utilisateur-32 o_dedup-ordre-201307 o_maj-referentiel-201307 o_referonosaure
cat $(fichiers_generes) > maj1.comp.sql
echo done
 
### fin: mises à jour
 
 
### tools
 
check_cel_obs:
$(call is_table,$(alterdb),cel_obs)
 
### fin: tools
 
 
### mises à jour individuelles (scripts)
### pour chacun d'entre-eux, deux versions existent,
### 1) L'un compile (après substitution des noms dans le fichier SQL original)
### et pipe vers mysql directement, ce qui suppose aussi un .my.cnf ou autre
### 2) L'autre (préfixé par o_), renvoie le fichier substitué en sortie standard
### et le target principal s'occupe de concaténer et de créer un fichier de destination
### Cette méthode est de loin préférable et conforme à la philosophie Makefile
cel_references:
$(call is_table,$(sourcedb),$(bdtfx_table))
$(call is_table,$(sourcedb),nvjfl_v2007)
$(call is_table,$(sourcedb),nva_index_v2_03)
$(call is_table,$(alterdb),cel_references) || $(mysqlcmd) < cel_references.sql
o_cel_references:
$(call is_table,$(sourcedb),$(bdtfx_table))
$(call is_table,$(sourcedb),nvjfl_v2007)
$(call is_table,$(sourcedb),nva_index_v2_03)
$(call is_table,$(alterdb),cel_references) || $(call do_subst,cel_references.sql) > $@.comp.sql
 
maj-struct-201307: check_cel_obs
$(call is_table,$(annuairedb),annuaire_tela)
$(mysqlcmd) -N $(alterdb) <<<"DESC cel_obs nom_sel"|grep -q 601 || $(mysqlcmd) < maj-struct-201307.sql
o_maj-struct-201307: check_cel_obs
$(call is_table,$(annuairedb),annuaire_tela)
$(mysqlcmd) -N $(alterdb) <<<"DESC cel_obs nom_sel"|grep -q 601 || $(call do_subst,maj-struct-201307.sql) > $@.comp.sql
 
maj-cleanup-201307: check_cel_obs
! $(mysqlcmd) -N $(alterdb) <<<"SELECT 1 FROM cel_obs WHERE nom_ret = 'null' LIMIT 1"|grep -q 1 || $(mysqlcmd) < maj-cleanup-201307.sql
o_maj-cleanup-201307:
# tb_cel_test clean
! $(mysqlcmd) -N $(alterdb) <<<"SELECT 1 FROM cel_obs WHERE nom_ret = 'null' LIMIT 1"|grep -q 1 || $(call do_subst,maj-cleanup-201307.sql) > $@.comp.sql
 
fix-utilisateur-32: check_cel_obs
$(mysqlcmd) -N $(alterdb) <<<"DESC cel_obs ce_utilisateur"|grep -q 255 || $(mysqlcmd) < fix-utilisateur-32.sql
o_fix-utilisateur-32: check_cel_obs
$(mysqlcmd) -N $(alterdb) <<<"DESC cel_obs ce_utilisateur"|grep -q 255 || $(call do_subst,fix-utilisateur-32.sql) > $@.comp.sql
 
dedup-ordre-201307: check_cel_obs
#$(mysqlcmd) -N $(alterdb) <<<'SELECT distinct ce_utilisateur FROM `cel_obs` GROUP BY ce_utilisateur, ordre HAVING COUNT(*) > 1'|grep -q . || $(mysqlcmd) < dedup-ordre-201307.sql
$(mysqlcmd) -N $(alterdb) <<<"SHOW INDEX FROM cel_obs"|grep -q couple_user_ordre || $(mysqlcmd) < dedup-ordre-201307.sql
o_dedup-ordre-201307: check_cel_obs
# l'index doit sur cel_obs doit avoir deux lignes dont le champs "non_unique" = 0
$(mysqlcmd) -N $(alterdb) <<<"SHOW INDEX FROM cel_obs"|grep -w id_obs|awk '{print $$2}'|tr -d "\n"|grep -q 00 || $(call do_subst,dedup-ordre-201307.sql) > $@.comp.sql
 
# maj-referentiel-201307.sql: # pas de test aisé et rapide
# doit passer APRÈS o_maj-cleanup-201307 (pas de nom_ret_nn = 0)
o_maj-referentiel-201307: check_cel_obs
$(call do_subst,maj-referentiel-201307.sql) > $@.comp.sql
 
# pas de test aisé non plus pour savoir s'il doit repasser
# néanmoins c'est un script sur (peut-être invoqué répétivement)
o_referonosaure: check_cel_obs
$(call do_subst,referonosaure.sql) > $@.comp.sql
 
 
 
# pour une prochaine maj
maj-nom-ret:
$(mysqlcmd) -N <<<'SELECT count(1) FROM `$(alterdb)`.`cel_obs` c LEFT JOIN `$(sourcedb)`.`$(bdtfx_table)` b on (c.nom_ret = b.nom_sci) WHERE nom_ret_nn = 0 AND c.nom_ret != "" AND id_observation NOT IN ( SELECT id_observation FROM `$(alterdb)`.`cel_obs` c, `$(sourcedb)`.`$(bdtfx_table)` b WHERE c.nom_ret = b.nom_sci AND c.nom_ret_nn = 0 );'|grep -q 0
o_maj-nom-ret:
$(call do_subst,maj-nom-ret.sql) > $@.comp.sql
Property changes:
Added: svn:eol-style
+native
\ No newline at end of property
/tags/v5.8-aspergeraie/scripts/modules/cel/fix-utilisateur-32.sql
New file
0,0 → 1,26
-- corriger les addresses erronées:
-- SELECT distinct ce_utilisateur FROM `BASEEDIT`.`cel_obs` WHERE LENGTH(ce_utilisateur) = 32 AND ce_utilisateur LIKE '%@%' AND ce_utilisateur NOT REGEXP '\.(fr|com)$';
 
ALTER TABLE `BASEEDIT`.`cel_obs` MODIFY ce_utilisateur VARCHAR(255) NOT NULL;
 
UPDATE `BASEEDIT`.`cel_obs` SET ce_utilisateur = CONCAT(SUBSTRING_INDEX(ce_utilisateur,'@', 1), '@apprenti.isa-lille.fr') WHERE ce_utilisateur LIKE '%@apprenti.isa-%';
UPDATE `BASEEDIT`.`cel_obs` SET ce_utilisateur = 'amardeilh.michel@club-internet.fr' WHERE ce_utilisateur = 'amardeilh.michel@club-internet.f';
UPDATE `BASEEDIT`.`cel_obs` SET ce_utilisateur = 'claude.figureau.plantnet@gmail.com' WHERE ce_utilisateur = 'claude.figureau.plantnet@gmail.c';
UPDATE `BASEEDIT`.`cel_obs` SET ce_utilisateur = 'francoise.delachaussee@dbmail.com' WHERE ce_utilisateur = 'francoise.delachaussee@dbmail.co';
UPDATE `BASEEDIT`.`cel_obs` SET ce_utilisateur = 'lucie.boust@proxalys-environnement.com' WHERE ce_utilisateur = 'lucie.boust@proxalys-environneme';
UPDATE `BASEEDIT`.`cel_obs` SET ce_utilisateur = 'alexis.cochereau@plante-et-cite.fr' WHERE ce_utilisateur = 'alexis.cochereau@plante-et-cite.';
UPDATE `BASEEDIT`.`cel_obs` SET ce_utilisateur = 'environnement@andernos-les-bains.fr' WHERE ce_utilisateur = 'environnement@andernos-les-bains';
UPDATE `BASEEDIT`.`cel_obs` SET ce_utilisateur = 'hugounenc.guilhem@mairie-perpignan.fr' WHERE ce_utilisateur = 'hugounenc.guilhem@mairie-perpign';
UPDATE `BASEEDIT`.`cel_obs` SET ce_utilisateur = 'jean-pierre-blanchet@club-internet.fr' WHERE ce_utilisateur = 'jean-pierre-blanchet@club-intern';
UPDATE `BASEEDIT`.`cel_obs` SET ce_utilisateur = 'stephanie.grosset@ville-montpellier.fr' WHERE ce_utilisateur = 'stephanie.grosset@ville-montpell';
 
UPDATE `BASEEDIT`.`cel_images` SET ce_utilisateur = CONCAT(SUBSTRING_INDEX(ce_utilisateur,'@', 1), '@apprenti.isa-lille.fr') WHERE ce_utilisateur LIKE '%@apprenti.isa-%';
UPDATE `BASEEDIT`.`cel_images` SET ce_utilisateur = 'amardeilh.michel@club-internet.fr' WHERE ce_utilisateur = 'amardeilh.michel@club-internet.f';
UPDATE `BASEEDIT`.`cel_images` SET ce_utilisateur = 'claude.figureau.plantnet@gmail.com' WHERE ce_utilisateur = 'claude.figureau.plantnet@gmail.c';
UPDATE `BASEEDIT`.`cel_images` SET ce_utilisateur = 'francoise.delachaussee@dbmail.com' WHERE ce_utilisateur = 'francoise.delachaussee@dbmail.co';
UPDATE `BASEEDIT`.`cel_images` SET ce_utilisateur = 'lucie.boust@proxalys-environnement.com' WHERE ce_utilisateur = 'lucie.boust@proxalys-environneme';
UPDATE `BASEEDIT`.`cel_images` SET ce_utilisateur = 'alexis.cochereau@plante-et-cite.fr' WHERE ce_utilisateur = 'alexis.cochereau@plante-et-cite.';
UPDATE `BASEEDIT`.`cel_images` SET ce_utilisateur = 'environnement@andernos-les-bains.fr' WHERE ce_utilisateur = 'environnement@andernos-les-bains';
UPDATE `BASEEDIT`.`cel_images` SET ce_utilisateur = 'hugounenc.guilhem@mairie-perpignan.fr' WHERE ce_utilisateur = 'hugounenc.guilhem@mairie-perpign';
UPDATE `BASEEDIT`.`cel_images` SET ce_utilisateur = 'jean-pierre-blanchet@club-internet.fr' WHERE ce_utilisateur = 'jean-pierre-blanchet@club-intern';
UPDATE `BASEEDIT`.`cel_images` SET ce_utilisateur = 'stephanie.grosset@ville-montpellier.fr' WHERE ce_utilisateur = 'stephanie.grosset@ville-montpell';
/tags/v5.8-aspergeraie/scripts/modules/cel/cel.ini
New file
0,0 → 1,43
version="1_00"
dossierTsv = "{ref:dossierDonneesEflore}cel/2011-11-10/"
dossierSql = "{ref:dossierTsv}"
bdd_nom = "tb_cel"
 
[tables]
obs = cel_inventory
obsImages = cel_obs_images
images = cel_images
motsClesImages = cel_mots_cles_images
motsClesObs = cel_mots_cles_obs
zoneGeo = locations
 
[fichiers]
structureSql = "cel_v{ref:version}.sql"
obs = "{ref:tables.obs}.tsv"
obsImages = "{ref:tables.obsImages}.tsv"
images = "{ref:tables.images}.tsv"
motsClesImages = "{ref:tables.motsClesImages}.tsv"
motsClesObs = "{ref:tables.motsClesObs}.tsv"
zoneGeo = "{ref:tables.zoneGeo}.tsv"
 
[chemins]
structureSql = "{ref:dossierSql}{ref:fichiers.structureSql}"
obs = "{ref:dossierTsv}{ref:fichiers.obs}"
obsImages = "{ref:dossierTsv}{ref:fichiers.obsImages}"
images = "{ref:dossierTsv}{ref:fichiers.images}"
motsClesImages = "{ref:dossierTsv}{ref:fichiers.motsClesImages}"
motsClesObs = "{ref:dossierTsv}{ref:fichiers.motsClesObs}"
zoneGeo = "{ref:dossierTsv}{ref:fichiers.zoneGeo}"
 
[references]
BASEEDIT = "tb_cel"
BASESOURCE = "tb_eflore"
TABLEBASEFLOR = "baseflor_v2014_01_06"
TABLEBASEVEG = "baseveg_v2014_01_16"
TABLEBDTFXVERNA = "nvjfl_v2007"
TABLEBDTXAVERNAINDEX = "nva_index_v2_03"
TABLEBDTXAVERNA = "nva_v2013_06"
TABLEBDTXA = "bdtxa_v1_01"
TABLEBDTFX = "bdtfx_v2_01"
TABLEISFAN = "isfan_v2013"
 
/tags/v5.8-aspergeraie/scripts/modules/cel/maj-struct-201307.sql
New file
0,0 → 1,21
-- dépot "cel", r1739
ALTER TABLE `BASEEDIT`.`cel_obs` MODIFY nom_sel VARCHAR(601) NULL DEFAULT NULL;
ALTER TABLE `BASEEDIT`.`cel_obs` MODIFY nom_ret VARCHAR(601) NULL DEFAULT NULL;
 
 
-- dépot "cel", r1739
CREATE INDEX nom_referentiel ON `BASEEDIT`.`cel_obs` (`nom_referentiel`(5));
 
-- depot "cel", r1809
ALTER TABLE `BASEEDIT`.`cel_obs` MODIFY altitude INTEGER(5) DEFAULT NULL;
 
-- depot "cel", r1811
CREATE OR REPLACE VIEW `BASEEDIT`.`cel_utilisateurs` AS
SELECT at.U_ID AS id_utilisateur, at.U_SURNAME AS prenom, at.U_NAME AS nom, at.U_MAIL AS courriel, at.U_PASSWD AS mot_de_passe,
ui.licence_acceptee, ui.admin, ui.preferences, ui.date_premiere_utilisation
FROM `BASEANNUAIRE`.`annuaire_tela` AS at
LEFT JOIN `BASEEDIT`.`cel_utilisateurs_infos` AS ui ON (ui.id_utilisateur = at.U_ID);
 
-- MySQL 5.6.5 only:
-- ALTER TABLE `BASEEDIT`.`cel_obs` CHANGE COLUMN `date_modification` `date_modification` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ;
-- otherwise, convert `date_modification` to TIMESTAMP ?
/tags/v5.8-aspergeraie/scripts/modules/cel/sphinx-maj-nom-ret.php
New file
0,0 → 1,203
<?php
/*
* @author Raphaël Droz <raphael@tela-botanica.org>
* @copyright Copyright (c) 2011, 2013 Tela Botanica (accueil@tela-botanica.org)
* @license http://www.cecill.info/licences/Licence_CeCILL_V2-fr.txt Licence CECILL
* @license http://www.gnu.org/licenses/gpl.html Licence GNU-GPL
*
* Corrige les erreurs de saisie de nom à l'aide d'une recherche via un index sphinx
* pour les observations ayant un nom saisi et dont l'un au moins de nom_ret[nn],
* nt ou famille est NULL ou vide.
*
*/
 
// time php -d memory_limit=1024M sphinx-maj-nom-ret.php 0 > sphinx-maj.log
// 23 secondes
 
// settings
define('USE_NVJFL', FALSE);
define('ESCAPE_ON_SPHINX_SYNERROR', TRUE);
 
define('TRY_FORCE_START_LINE', TRUE);
define('TRY_SPLIT', TRUE);
define('TRY_EXACT', TRUE);
define('TRY_REF', TRUE);
define('TRY_SPLIT_AND_AUTEUR', FALSE);
define('TRY_REMOVE_L', TRUE);
 
define('M_TRY_SPLIT', 0x01);
define('M_TRY_EXACT', 0x02);
define('M_TRY_REF', 0x04);
define('M_TRY_SPLIT_AND_AUTEUR', 0x08);
 
error_reporting(E_ALL);
$db = mysql_connect('localhost', 'root', '') or die('no mysql');
mysql_select_db('tb_cel', $db);
mysql_query("SET NAMES utf8", $db) or die('no sphinx');
$dbs = mysql_connect('127.0.0.1:9306', NULL, NULL, TRUE);
 
$req = <<<EOF
SELECT id_observation, nom_sel, nom_sel_nn, nom_ret, nom_ret_nn, nt, famille, nom_referentiel
FROM `cel_obs`
WHERE nom_sel IS NOT NULL AND nom_sel != '' AND
id_observation BETWEEN %d AND %d AND
( nom_ret IS NULL or nom_ret = ''
OR nt IS NULL or nt = 0 or nt = ''
OR famille IS NULL or famille = '' )
LIMIT %d, %d
EOF;
// non: car nom_ret_nn peut-être légitimement à 0 [taxon identifié, sans nom_retenu]
// OR nom_ret_nn IS NULL or nom_ret_nn = 0 or nom_ret_nn = ''
 
 
array_shift($argv);
$start = array_shift($argv);
$max = array_shift($argv);
$chunk_size = array_shift($argv);
 
if(!$start) $start = 0;
// 1036314
if(!$max) $max = intval(mysql_fetch_assoc(mysql_query("SELECT MAX(id_observation) AS max FROM cel_obs", $db))['max']) + 1;
if(!$chunk_size) $chunk_size = 50000;
 
 
// escape sphinx
$from = array ( '\\', '(',')','|','-','!','@','~','"','&', '/', '^', '$', '=', "'", "\x00", "\n", "\r", "\x1a" );
$to = array ( '\\\\', '\\\(','\\\)','\\\|','\\\-','\\\!','\\\@','\\\~','\\\"', '\\\&', '\\\/', '\\\^', '\\\$', '\\\=', "\\'", "\\x00", "\\n", "\\r", "\\x1a" );
 
 
$stats = ['no_nom_sel' => ['count' => 0, 'data' => [] ],
'not found' => ['count' => 0, 'data' => [] ],
'too many' => ['count' => 0, 'data' => [] ],
'fixable' => ['count' => 0, 'data' => [] ],
'sauvages' => ['count' => 0, 'data' => [] ],
'sphinx errors' => ['count' => 0, 'data' => [] ],
'ref pb' => ['count' => 0, 'data' => [] ], ];
 
$sphinx_req = sprintf("SELECT * FROM i_bdtfx %s WHERE MATCH('%%s') LIMIT 5", USE_NVJFL ? ", i_nvjfl" : "");
 
for($current = 0; $current < intval($max/$chunk_size) + 1; $current++) {
// printf("current = %d, chunk_size = %d, max = %d (rmax = %d) [real limit: %d]\n", $current, $chunk_size, $max, intval($max/$chunk_size) + 1, $current*$chunk_size);
// printf(strtr($req, "\n", " ") . "\n", $start, $max, $current*$chunk_size, $chunk_size);
$data = mysql_query(sprintf($req, $start, $max, $current*$chunk_size, $chunk_size), $db);
if(!$data) { var_dump(mysql_error()); die('end'); }
while($d = mysql_fetch_assoc($data)) {
$n = trim($d['nom_sel']);
//d: fprintf(STDERR, "$n\n");
 
if(!$n) {
$stats['no_nom_sel']['count']++;
// $stats['no_nom_sel']['data'][] = [$d['id_observation'], $n];*/
continue;
}
 
if($n == 'Autre(s) espèce(s) (écrire le/les nom(s) dans les notes)' ||
$n == '-') {
$stats['sauvages']['count']++;
// $stats['sauvages']['data'][] = [$d['id_observation'], $n];
continue;
}
 
$MASQUE = 0;
 
if(TRY_REMOVE_L) {
$n = str_replace(' L.','', $n);
}
 
$orig_n = $n;
 
recherche:
if(TRY_FORCE_START_LINE && !_has($MASQUE, M_TRY_EXACT)) {
$n = '^' . $n;
}
 
$s = mysql_query(sprintf($sphinx_req, $n), $dbs);
 
 
if(!$s && ESCAPE_ON_SPHINX_SYNERROR) {
$s = mysql_query(sprintf($sphinx_req, str_replace($from,$to,$n)), $dbs);
}
if(!$s) {
$stats['sphinx errors']['count']++;
// $stats['sphinx errors']['data'][] = [$d['id_observation'], $orig_n];
continue;
}
 
$c = mysql_num_rows($s);
//d: fprintf(STDERR, "\t search [nb:%d] \"%s\" (msk:%d)\n", $c, $n, $MASQUE);
 
if($c == 0) {
if(TRY_SPLIT && !_has($MASQUE, M_TRY_SPLIT)) {
require_once('lib-split-auteur.php');
$MASQUE |= M_TRY_SPLIT;
// $n = RechercheInfosTaxonBeta::supprimerAuteur($orig_n);
// list($ret, $m) = RechercheInfosTaxonBeta::contientAuteur($orig_n);
$ret = RechercheInfosTaxonBeta::supprimerAuteurBis($orig_n, $m);
if($ret) {
// printf("===================== SPLIT: contientAuteur \"%s\" [@%s @%s)\n", $orig_n, $ret, $m);
$n = sprintf('%s @auteur %s', $ret, $m);
goto recherche;
}
}
if(TRY_SPLIT_AND_AUTEUR && !_has($MASQUE, M_TRY_SPLIT_AND_AUTEUR) && strpos($orig_n, ' ') !== FALSE) {
require_once('lib-split-auteur.php');
$MASQUE |= M_TRY_SPLIT_AND_AUTEUR;
$ns = RechercheInfosTaxonBeta::supprimerAuteur($orig_n);
if($ns) {
$a = trim(substr($orig_n, strlen($n)));
$n = sprintf("%s @auteur %s", $ns, $a);
// echo "===================== SPLIT N/A: $n\n";
goto recherche;
}
}
 
$stats['not found']['count']++;
// $stats['not found']['data'][] = [$d['id_observation'], $orig_n];
continue;
}
 
if($c > 1) {
 
if($c == 2) {
if(mysql_fetch_array($s)['group_id'] !=
mysql_fetch_array($s)['group_id']) {
// recherche donne seulement 2 résultats dans 2 référentiels
// potentiellement fixable si l'on peut se référer à $d['nom_referentiel']
$stats['ref pb']['count']++;
// $stats['ref pb']['data'][] = [$d['id_observation'], $orig_n];
continue;
}
}
 
if(TRY_EXACT && !_has($MASQUE, M_TRY_EXACT)) {
$MASQUE |= M_TRY_EXACT;
$n = '"^' . trim($orig_n) . '$"';
goto recherche;
}
if(TRY_REF && isset($d['nom_referentiel']) && !_has($MASQUE, M_TRY_REF)) {
$MASQUE |= M_TRY_REF;
$n = $orig_n . ' @group_id ' . $d['nom_referentiel'];
goto recherche;
}
 
$stats['too many']['count']++;
// $stats['too many']['data'][] = [$d['id_observation'], $orig_n];
continue;
}
 
 
ok:
$stats['fixable']['count']++;
// $stats['fixable']['data'][] = [$d['id_observation'], $orig_n];
 
}
}
 
function _has($v, $r) {
return ($v & $r) == $r;
}
 
 
array_walk($stats, function(&$v) { unset($v['data']); });
print_r($stats);
printf("total traité: %d\n", array_sum(array_map(function($v) { return $v['count']; }, $stats)));
/tags/v5.8-aspergeraie/scripts/modules/cel/maj-nom-ret.sql
New file
0,0 → 1,45
/*
Cleanup des observation ayant un nom_ret_nn à 0 (mais un nom_ret défini...):
En effet, on peut pour l'instant POSTer $nom_ret, d'où bien souvent nom_sel == nom_ret, cependant nom_ret_nn = 0
(pas d'autodétection).
Nous pourrions donc les nullifier sans remord, ... mais ...
nom_ret_nn == 0 est VALIDE (car bdtfx.num_nom_retenu == 0 est "valide", 3960 taxons sont "orphelins" de nom_retenu)
 
1) créer un index pour les jointures:
CREATE INDEX i_nom_ret ON `BASESOURCE`.`TABLEBDTFX` (`nom_sci`(8))
2) regarder les num_nom_ret orphelins de taxon en BDTFX:
SELECT * FROM `BASESOURCE`.`TABLEBDTFX` WHERE num_nom_retenu = 0; # 3960
3) regarder les num_nom_ret orphelins de taxon en BDTXA:
SELECT * FROM `BASESOURCE`.`TABLEBDTXA` WHERE num_nom_retenu = 0; # 0
4) regarder les orphelins équivalents dans `BASEEDIT`.`cel_obs`:
SELECT date_observation, SUBSTRING(nom_sel, 1, 50), nom_ret_nn, nom_ret, b.nom_sci FROM `BASEEDIT`.`cel_obs` c LEFT JOIN `BASESOURCE`.`TABLEBDTFX` b on (c.nom_ret = b.nom_sci) WHERE nom_ret_nn = 0; # 7740
 
Donc ceux dont le nom_ret à été POSTé manuellement et qui matchent le nom_sci de BDTFX : on les conserve.
Mais les autres, qui ont un nom_ret probablement erroné et un nom_ret_nn à 0, on NULLify les données car elles seront théoriquement correctement autoregénérées !
 
Cela concerne:
SELECT date_observation, SUBSTRING(nom_sel, 1, 50), nom_ret_nn, nom_ret, b.nom_sci FROM `BASEEDIT`.`cel_obs` c LEFT JOIN `BASESOURCE`.`TABLEBDTFX` b ON (c.nom_ret = b.nom_sci) WHERE nom_ret_nn = 0
AND c.nom_ret != '' AND id_observation NOT IN ( SELECT id_observation FROM `BASEEDIT`.`cel_obs` c, `BASESOURCE`.`TABLEBDTFX` b WHERE c.nom_ret = b.nom_sci AND c.nom_ret_nn = 0 ); # 960
*/
-- D'où la requête :
UPDATE `BASEEDIT`.`cel_obs` SET nom_sel_nn = NULL, nom_ret = NULL, nom_ret_nn = NULL, nt = NULL, famille = NULL WHERE id_observation IN
( SELECT id_observation FROM `BASEEDIT`.`cel_obs` c LEFT JOIN `BASESOURCE`.`TABLEBDTFX` b on (c.nom_ret = b.nom_sci) WHERE nom_ret_nn = 0
AND c.nom_ret != '' AND id_observation NOT IN ( SELECT id_observation FROM `BASEEDIT`.`cel_obs` c, `BASESOURCE`.`TABLEBDTFX` b WHERE c.nom_ret = b.nom_sci AND c.nom_ret_nn = 0 ) );
 
-- TODO
-- UPDATE `BASEEDIT`.`cel_obs` SET nom_ret_nn = NULL WHERE nom_ret_nn = 0;
 
/*
UPDATE `BASEEDIT`.`cel_obs` SET nom_sel = NULL, nom_sel_nn = NULL, nom_ret = NULL, nom_ret_nn = NULL, nt = NULL, famille = NULL,
FROM `BASEEDIT`.`cel_obs`
WHERE nom_sel IS NULL AND
(
(nom_ret IS NOT NULL AND nom_ret != '') OR
(nt IS NOT NULL AND nt != 0 AND nt != '') OR
(famille IS NOT NULL AND famille != '')
)
 
-- pas de test de nullité sur nom_ret_nn qui peut légitimement être NULL
(nom_ret_nn IS NOT NULL AND nom_ret_nn != 0 AND nom_ret_nn != '') OR
*/
 
/tags/v5.8-aspergeraie/scripts/modules/cel/maj-cleanup-201307.sql
New file
0,0 → 1,62
-- date d'observation dans le futur
UPDATE `BASEEDIT`.`cel_obs` SET date_observation = NULL WHERE date_observation > now();
-- cleanup
UPDATE `BASEEDIT`.`cel_obs` SET date_observation = NULL WHERE date_observation = '0000-00-00 00:00:00';
-- cleanup
UPDATE `BASEEDIT`.`cel_obs` SET latitude = NULL, longitude = NULL WHERE longitude = 0 and latitude = 0;
 
-- referentiels: 65800 NULL, 13000 ''
UPDATE `BASEEDIT`.`cel_obs` SET nom_referentiel = SUBSTRING_INDEX(nom_referentiel, ':', 1);
UPDATE `BASEEDIT`.`cel_obs` SET nom_referentiel = 'bdtfx' WHERE nom_referentiel IN ('bdtfx_v1','bdnff');
 
-- pas de raison historique mémorisée à une différence '' vs NULL
UPDATE `BASEEDIT`.`cel_obs` SET nom_referentiel = NULL where nom_referentiel = '';
 
-- uniformisation NULL / vide pour nom_sel
UPDATE `BASEEDIT`.`cel_obs` SET nom_sel = NULL WHERE nom_sel = '';
 
-- uniformisation NULL / vide pour nom_sel_nn
UPDATE `BASEEDIT`.`cel_obs` SET nom_sel_nn = NULL WHERE nom_sel_nn = 0;
 
-- restauration de nom_sel vraisemblablement valides, mais vides: 48 obs
UPDATE `BASEEDIT`.`cel_obs` SET nom_sel = nom_ret WHERE nom_sel IS NULL AND nom_ret IS NOT NULL AND nom_ret != '' AND nom_sel_nn IS NOT NULL;
 
 
-- suppression des infos générées pour les observations dont le nom_sel à été supprimé par l'utilisateur
-- 3380
UPDATE `BASEEDIT`.`cel_obs` c SET
c.nom_ret = '',
c.nom_sel_nn = NULL,
c.nom_ret = NULL,
c.nom_ret_nn = NULL,
c.nt = NULL,
c.famille = NULL
WHERE nom_sel IS NULL OR nom_ret = 'undefined';
 
-- problème n°1: mauvais référentiel (bdtfx au lieu de bdtxa), on utilise les lieudit "bdtxa" pour
-- corriger les observations qui pourraient être étiquetées avec un mauvais nom_referentiel: 49 obs
CREATE TEMPORARY TABLE T_cleanref (lieu VARCHAR(255)) ENGINE=MEMORY AS ( SELECT DISTINCT TRIM(lieudit) FROM `BASEEDIT`.`cel_obs` WHERE nom_referentiel = 'bdtxa' );
UPDATE `BASEEDIT`.`cel_obs` SET nom_referentiel = 'bdtxa' WHERE nom_referentiel != 'bdtxa' AND lieudit != '' AND lieudit IN (SELECT lieu FROM T_cleanref);
DROP TEMPORARY TABLE T_cleanref;
 
-- problème n°2: backslashes + newline: 90 + 217 obs
UPDATE `BASEEDIT`.`cel_obs` SET commentaire = REPLACE(commentaire, "\n\\\'", "'");
UPDATE `BASEEDIT`.`cel_obs` SET commentaire = REPLACE(commentaire, "\\\'", "'");
 
-- problème n°3: ce_zone_geo inutile: 57802 obs
UPDATE `BASEEDIT`.`cel_obs` SET ce_zone_geo = NULL WHERE ce_zone_geo = 'INSEE-C:';
 
-- trim nom_sel
UPDATE `BASEEDIT`.`cel_obs` SET nom_sel = REPLACE(nom_sel, '\\', '');
UPDATE `BASEEDIT`.`cel_obs` SET nom_sel = TRIM(LEADING "." FROM TRIM("\t" FROM TRIM(nom_sel)));
UPDATE `BASEEDIT`.`cel_obs` SET nom_sel = TRIM(TRIM('\\' FROM TRIM('‘' FROM TRIM('‘' FROM TRIM('"' FROM nom_sel))))) WHERE nom_sel REGEXP '^[\\"‘’].*[\\"‘’]$';
UPDATE `BASEEDIT`.`cel_obs` SET nom_sel = TRIM("'" FROM nom_sel) WHERE nom_sel REGEXP "^'.*'$"; -- ' relax emacs
UPDATE `BASEEDIT`.`cel_obs` SET nom_sel = TRIM('"' FROM nom_sel) WHERE nom_sel REGEXP '^"[^"]+$';
UPDATE `BASEEDIT`.`cel_obs` SET nom_sel = REPLACE(nom_sel, 'A©', 'é') WHERE nom_sel LIKE '%A©%';
-- nom_ret = "null"
UPDATE `BASEEDIT`.`cel_obs` SET nom_ret = NULL WHERE nom_ret = 'null';
 
 
-- inconsistence de date_transmission avec transmission (cf r1860)
UPDATE `BASEEDIT`.`cel_obs` SET date_transmission = date_creation WHERE date_transmission IS NULL AND transmission = 1;
UPDATE `BASEEDIT`.`cel_obs` SET date_transmission = NULL WHERE date_transmission IS NOT NULL AND transmission = 0;
/tags/v5.8-aspergeraie/scripts/modules/cel/maj-referentiel-und-201307.sql
New file
0,0 → 1,100
/*
Mise à jour de réferentiels NULL ou vides pour les observations orphelines (sans nom_ret_nn)
2722 observations trouvées au 2013/07/19
*/
 
DROP PROCEDURE IF EXISTS getNomSci;
DROP PROCEDURE IF EXISTS getNomSciCount;
DROP PROCEDURE IF EXISTS getNomSciAuteur;
DROP PROCEDURE IF EXISTS getNomSciAuteurCount;
DROP PROCEDURE IF EXISTS cur;
 
delimiter |
 
-- obtient le nombre de matches sur nom_sel = nom_sci
CREATE PROCEDURE getNomSciCount(IN _nom varchar(500), OUT param1 INT)
BEGIN
SELECT sum(c) INTO param1 FROM (SELECT count(1) as c FROM `BASESOURCE`.`TABLEBDTFX` b WHERE nom_sci = _nom UNION ALL SELECT count(1) FROM `BASESOURCE`.`TABLEBDTXA` a WHERE nom_sci = _nom) AS req;
END
|
-- retourne les paramètres d'une match
CREATE PROCEDURE getNomSci(IN _nom varchar(500), OUT param1 char(5), OUT param2 varchar(601), OUT param3 INT, OUT param4 INT, OUT param5 varchar(255))
BEGIN
SELECT * INTO param1, param2, param3, param4, param5 FROM
(SELECT "bdtfx", CONCAT(b.nom_sci, ' ', b.auteur), b.num_nom, b.num_taxonomique, b.famille FROM `BASESOURCE`.`TABLEBDTFX` b WHERE nom_sci = _nom
UNION ALL
SELECT "bdtxa", CONCAT(a.nom_sci, ' ', a.auteur), a.num_nom, a.num_tax, a.famille FROM `BASESOURCE`.`TABLEBDTXA` a WHERE nom_sci = _nom) AS req;
END
|
 
-- obtient le nombre de matches sur nom_sel = CONCAT(nom_sci, " ", auteur)
-- quasiment identique à ci-dessus, sauf que nous excluons de la recherche de bdtfx et bdtxa les nom dont le nom d'auteur est ''
CREATE PROCEDURE getNomSciAuteurCount(IN _nom varchar(500), OUT param1 INT)
BEGIN
SELECT sum(c) INTO param1 FROM (SELECT count(1) as c FROM `BASESOURCE`.`TABLEBDTFX` b WHERE CONCAT(nom_sci, ' ', auteur) = _nom UNION ALL SELECT count(1) FROM `BASESOURCE`.`TABLEBDTXA` a WHERE CONCAT(nom_sci, ' ', auteur) = _nom) AS req;
END
|
-- retourne les paramètres d'une match
CREATE PROCEDURE getNomSciAuteur(IN _nom varchar(500), OUT param1 char(5), OUT param2 varchar(601), OUT param3 INT, OUT param4 INT, OUT param5 varchar(255))
BEGIN
SELECT * INTO param1, param2, param3, param4, param5 FROM
(SELECT "bdtfx", CONCAT(b.nom_sci, ' ', b.auteur), b.num_nom, b.num_taxonomique, b.famille FROM `BASESOURCE`.`TABLEBDTFX` b WHERE CONCAT(nom_sci, ' ', auteur) = _nom AND auteur != ''
UNION ALL
SELECT "bdtxa", CONCAT(a.nom_sci, ' ', a.auteur), a.num_nom, a.num_tax, a.famille FROM `BASESOURCE`.`TABLEBDTXA` a WHERE CONCAT(nom_sci, ' ', auteur) = _nom AND auteur != '') AS req;
END
|
 
CREATE PROCEDURE cur()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE subst INT DEFAULT 0;
DECLARE _id_observation bigint(20) DEFAULT 0;
DECLARE _nom varchar(255) DEFAULT NULL;
 
-- la requête principale de sélection des observations à mettre à jour
DECLARE cur1 CURSOR FOR SELECT id_observation, nom_sel FROM `BASEEDIT`.`cel_obs` WHERE nom_referentiel IS NULL AND nom_sel != '' AND nom_sel IS NOT NULL AND nom_ret_nn IS NULL; -- 78149
-- DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
 
OPEN cur1;
 
REPEAT
FETCH cur1 INTO _id_observation, _nom;
CALL getNomSciCount(_nom, @a);
-- SELECT _id_observation, _nom, @a;
IF @a = 1 THEN
CALL getNomSci(_nom, @_ref, @_nom, @_num_nom, @_num_tax, @_famille);
SELECT "updb: getNomSci", _id_observation, _nom, '=', @_ref, @_nom, @_num_nom, @_num_tax, @_famille;
UPDATE `BASEEDIT`.`cel_obs` c SET
c.nom_referentiel = @_ref,
c.nom_ret = @_nom,
c.nom_ret_nn = @_num_nom,
c.nt = @_num_tax,
c.famille = @_famille
WHERE id_observation = _id_observation;
SET subst = subst + 1;
/* ELSE
CALL getNomSciAuteurCount(_nom, @a);
IF @a = 1 THEN
CALL getNomSciAuteur(_nom, @_ref, @_nom, @_num_nom, @_num_tax, @_famille);
SELECT "updb: getNomSciAuteur", _id_observation, _nom, '=', @_ref, @_nom, @_num_nom, @_num_tax, @_famille;
UPDATE `BASEEDIT`.`cel_obs` c SET
c.nom_referentiel = @_ref,
c.nom_ret = @_nom,
c.nom_ret_nn = @_num_nom,
c.nt = @_num_tax,
c.famille = @_famille
WHERE id_observation = _id_observation;
SET subst = subst + 1;
END IF;*/
END IF;
UNTIL done END REPEAT;
select subst AS 'nombre de mises à jour effectuées';
CLOSE cur1;
END
 
 
 
|
 
delimiter ;
/tags/v5.8-aspergeraie/scripts/modules/cel/referonosaure_fromNomRet.sql
New file
0,0 → 1,105
/*
Ceci est une version dérivée de referonosaure.sql dans laquel est postulé que
les nom_ret sont un critère tangible.
En effet, sauf bug, il n'y a pas de raison qu'un num_nom_retenu soit moins fiable qu'un num_nom.
Cependant un taxon peut changer de num_nom_retenu, et auquel cas c'est bien referonosaure.sql
qu'il faut utiliser.
Cependant pour un simple "rafraîchissement" des chaînes de caractères attribuées au noms retenus,
ce script, referonosaure_fromNomRet.sql, doit suffire.
 
 
Attention, les nom_sel_nn = 0 doivent avoir disparus de cel_obs *AU PRÉALABLE* car le test
n'est pas effectué.
cf: maj-cleanup-201307.sql
*/
 
 
/* test:
SELECT c.nom_ret_nn, c.nom_ret, b.nom_sci, b.auteur, c.famille, b.famille, c.nt, b.num_taxonomique
FROM cel_obs c, tb_eflore.bdtfx_v1_01 b
WHERE (
nom_sel_nn IS NOT NULL AND nom_ret_nn IS NOT NULL AND nom_ret_nn != 0
AND nom_referentiel = 'bdtfx'
AND nom_ret_nn = num_nom
AND (LOWER(c.famille) = LOWER(b.famille) OR c.famille IS NULL)
AND (c.famille != b.famille OR c.nom_ret != CONCAT(b.nom_sci, ' ', b.auteur) OR c.nt != b.num_taxonomique)
);
= 2 taxons: 75134 et 75468 (changement de nt)
*/
 
-- l'update BDTFX avec nom_sel_nn et nom_ret_nn corrects
UPDATE `BASEEDIT`.`cel_obs` c, `BASESOURCE`.`TABLEBDTFX` b SET
c.nom_ret = CONCAT(b.nom_sci, ' ', b.auteur),
c.nt = b.num_taxonomique,
c.famille = b.famille
WHERE (
nom_sel_nn IS NOT NULL AND nom_ret_nn IS NOT NULL AND nom_ret_nn != 0
AND nom_referentiel = 'bdtfx'
AND nom_ret_nn = num_nom
AND (c.mots_cles_texte IS NULL OR c.mots_cles_texte NOT LIKE '%WidgetFlorileges Sauvages%') -- TODO: bug transferts multiples + mobile.js
AND (LOWER(c.famille) = LOWER(b.famille) OR c.famille IS NULL OR c.famille = 'Famille inconnue')
);
-- 25584
SELECT ROW_COUNT() AS "BDTFX upd après correction sur nom_ret_nn + nom_sel_nn";
 
 
 
-- l'update BDTXA avec nom_sel_nn et nom_ret_nn corrects
UPDATE `BASEEDIT`.`cel_obs` c, `BASESOURCE`.`TABLEBDTXA` a SET
c.nom_ret = CONCAT(a.nom_sci, ' ', a.auteur),
c.nt = a.num_tax,
c.famille = a.famille
WHERE (
nom_sel_nn IS NOT NULL AND nom_ret_nn IS NOT NULL AND nom_ret_nn != 0
AND nom_referentiel = 'bdtxa'
AND nom_ret_nn = num_nom
AND (LOWER(c.famille) = LOWER(a.famille) OR c.famille IS NULL)
);
-- 2
SELECT ROW_COUNT() AS "BDTXA upd après correction sur nom_ret_nn + nom_sel_nn";
 
 
 
 
-- l'update ISFAN avec nom_sel_nn et nom_ret_nn corrects --
UPDATE `BASEEDIT`.`cel_obs` c, `BASESOURCE`.`TABLEISFAN` i SET
c.nom_ret = CONCAT(i.nom_sci, ' ', i.auteur),
c.nt = i.num_taxonomique,
c.famille = i.famille
WHERE (
nom_sel_nn IS NOT NULL AND nom_ret_nn IS NOT NULL AND nom_ret_nn != 0
AND nom_referentiel = 'isfan'
AND nom_ret_nn = num_nom
AND (LOWER(c.famille) = LOWER(i.famille) OR c.famille IS NULL)
);
-- 2 ou 0
SELECT ROW_COUNT() AS "ISFAN upd après correction sur nom_ret_nn + nom_sel_nn";
 
 
 
/*
Pour observer les différences:
wdiff -w '$(tput bold;tput setaf 1)' -x '$(tput sgr0)' -y '$(tput bold;tput setaf 2)' -z '$(tput sgr0)' pre.log post.log | \
ansi2html.sh --palette=solarized | \
sed '/^[0-9]/{/span/!d}' > diff.html
 
# extract les familles ayant changé: sed '/^[0-9]/{/<\/span>$/!d}'
# lowercase toutes les familles: awk '{ NF=tolower($NF); print }'
 
 
# filtre sed: changements de famille "normaux"
/aceraceae.*sapindaceae/d
/scrophulariaceae.*plantaginaceae/d
/globulariaceae.*plantaginaceae/d
/Famille inconnue.*null/d
 
# changement "anormaux"
/rosaceae.*caprifoliaceae/d
/valerianaceae.*caprifoliaceae/d
 
 
 
SELECT nom_sel, nom_ret FROM cel_obs GROUP BY nom_sel, nom_ret INTO OUTFILE '/tmp/new.csv' ;
SELECT id_observation, nom_sel, nom_sel_nn, nom_ret, nom_ret_nn FROM cel_obs INTO OUTFILE '/tmp/id.csv' ;
$ wdiff x y|sed -n "/\x1b/p"|less
*/
/tags/v5.8-aspergeraie/scripts/modules/cel/lib-split-auteur.php
New file
0,0 → 1,165
<?php
/*
fork temporaire de cel/jrest/lib/RechercheInfosTaxonBeta.php
[php-5.4 & co]
*/
 
require_once('/home/raphael/cel/jrest/lib/Cel.php');
// require_once('/home/raphael/cel/jrest/lib/RechercheInfosTaxonBeta.php');
require_once('/home/raphael/cel/jrest/lib/NameParser.php');
 
class RechercheInfosTaxonBeta {
static function getSpaceNoAfter($nom_saisi, $pattern, $offset) {
if( ($q = strpos($nom_saisi, $pattern, max(0, $offset))) ) {
// position du premier espace après $pattern,
// ou position de fin de $pattern autrement
if(! ($r = strpos($nom_saisi, ' ', $offset + strlen($pattern))) )
return $offset + strlen($pattern);
return $r;
}
return FALSE;
}
 
static function supprimerAuteurBis($nom_saisi, &$auteur = null) {
$strip_pos = 600;
 
$before = 600;
$after = 0;
// temp var
$p = $q = NULL;
 
if(strpos($nom_saisi, ' ') === FALSE) return FALSE; // pas d'espace, pas de nom d'auteur
 
// si "(", break après "Gp)" si présent, sinon, avant "("
if( ($p = strpos($nom_saisi, ' Gp)')) ) {
$after = $p + 4;
goto sendAuthor;
}
 
 
// si ".":
if( ($p = strpos($nom_saisi, '.')) ) {
// " f. "
 
/* SELECT nom_sci, LOCATE(' ', SUBSTRING_INDEX(nom_sci, ' f. ', -1)) AS space_pos
FROM tb_eflore.bdtfx_v1_02 WHERE nom_sci LIKE '% f. %' HAVING space_pos > 0; */
// f. suivi de 1 mot sauf, "Rosa pomifera f. x longicruris" (2)
/*if( ($q = strpos($nom_saisi, ' f. ', $p - 2)) ) {
$after = max($after, strpos($nom_saisi, ' ', $q + 4)); // premier espace après ' f. '
}*/
$after = max($after, self::getSpaceNoAfter($nom_saisi, ' f. ', $p - 2));
 
// " var. "
// var. n'est pas un repère vraiment adéquat, on sait juste qu'il fait partie du nom sci
// $after = min($strip_pos, strpos($nom_saisi, ' var. '));
$after = max($after, self::getSpaceNoAfter($nom_saisi, ' var. ', $p - 4));
 
// " subsp. "
// après subsp.: le plus souvent un ' x ', donc pas vraiment de règle (1 ou 2 mots)
$after = max($after, self::getSpaceNoAfter($nom_saisi, ' subsp. ', $p - 6));
 
// AUTEUR "."
// autrement, avant un "." dans la partie auteur, il peut y avoir entre 1 et 7 mots à gauche
// grep -o '^[^.]*\.' liste-auteur.txt|while read f; do grep -o ' '<<<"$f"|wc -l; done|sort -n|tail -1
if(!$after) { // si le "." rencontré n'est pas l'un du "nom_sci", c'est de "auteur"
$before = min($before, $p);
}
 
}
 
 
if( ($p = strpos($nom_saisi, ' x ')) ) {
$after = max($after, strpos($nom_saisi, ' x ', $p + 3));
}
 
// " (L.)" et " L."
if( ($p = strpos($nom_saisi, ' (L.)')) ) {
$before = min($before, $p);
}
 
// note: on supprime le " L." en amont
// if( ($p = strpos($nom_saisi, ' L.')) ) $before = min($before, $p);
 
 
 
// "(" et ")", uniquement dans nom_sci dans le cadre de " Gp)", autrement: auteur
// XXX: ce cas englobe " (L.)"
if( ($p = strpos($nom_saisi, '(')) ) {
$before = min($before, $p);
}
 
 
// TODO: gérer le " sp." [taxon supérieur], pour l'instant return FALSE
if( ($p = strpos($nom_saisi, ' sp.')) ) {
return FALSE;
}
// TODO: idem
if( ($p = strpos($nom_saisi, ' sp ')) ) {
return FALSE;
}
 
// si "&": auteur, et entre 1 et 10 mots à gauche
// grep -o '^[^&]*&' liste-auteur.txt|while read f; do grep -o ' '<<<"$f"|wc -l; done|sort -n|tail -1
 
// si ",": auteur, et entre 1 et 5 mots à gauche
// grep -o '^[^,]*,' liste-auteur.txt|while read f; do grep -o ' '<<<"$f"|wc -l; done|sort -n|tail -1
 
// TRIM auteurs:
// sed -e 's/[()]//g' liste-auteur.txt|awk '{print $1}'|sed -e 's/[ .,]*$//g'|awk '{print $1}'|sed -r '/^.{0,3}$/d'|sort -u|wc -l
 
sendAuthor:
$x = self::contientAuteur($nom_saisi, $after, $auteur);
if($x) {
$n = substr($nom_saisi, 0, min($before, strpos($nom_saisi, $auteur[1])));
$auteur = trim(substr($nom_saisi, strlen($n)));
return trim($n, " \t\n\r\0\x0B(),.&");
}
return FALSE;
}
 
static function contientAuteur($nom_saisi, $start, &$auteur = NULL) {
static $auteurs;
// XXX: PHP-5.3
// $auteurs = file_get_contents(dirname(__FILE__) . "/../static/auteurs-bdtfx.min.txt");
$auteurs = file_get_contents(__DIR__ . "/auteurs-bdtfx.min.txt") or die('no file: auteurs-bdtfx.min.txt');
return preg_match('^\s(' . $auteurs . ')^S', $nom_saisi, $auteur, 0, $start);
}
 
 
static function supprimerAuteur($nom_saisi, &$auteur = null) {
// TODO: gérer les hybrides
if(self::estUnHybride($nom_saisi) || self::estUneFormuleHybridite($nom_saisi)) {
 
$nom_decoupe = explode(' ', $nom_saisi);
$derniere_position_hybride = end(array_keys($nom_decoupe, 'x'));
$nom_saisi_sans_auteur = implode(' ',array_slice($nom_decoupe, 0, $derniere_position_hybride + 2));
 
/*
var_dump($nom_saisi, $nom_decoupe, $derniere_position_hybride, $nom_saisi_sans_auteur);
if($auteur != NULL) {
$c = strpos($nom_saisi, ' x ');
$auteur = substr($nom_saisi, $c + 3);
return substr($nom_saisi, 0, $c);
}
var_dump(substr($nom_saisi, 0, strpos($nom_saisi, ' x ')));
echo "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX\n";*/
} else {
/* Attention le parseur de nom n'est pas fiable à 100%
mais ça marche dans la plupart des cas
à part les formules d'hybridité saisies avec un auteur */
$nameparser = new NameParser();
$auteur = $nameparser->parse_auth($nom_saisi);
$nom_saisi_sans_auteur = str_replace($auteur, '', $nom_saisi);
}
 
return trim($nom_saisi_sans_auteur);
}
static function estUneFormuleHybridite($nom_saisi) {
return strpos($nom_saisi,' x ') !== false;
}
static function estUnHybride($nom_saisi) {
return strpos($nom_saisi,'x ') === 0;
}
}
/tags/v5.8-aspergeraie/scripts/modules/cel/.
New file
Property changes:
Added: svn:ignore
+o_*.comp.sql