/tags/v5.0-agropyraie-20130830/scripts/modules/cel/A_LIRE.txt |
---|
New file |
0,0 → 1,112 |
Créer une base de données tb_cel avant de lancer les scripts |
== Sommaire == |
1) à propos de la mise à jour de juillet 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, ...) |
4) fix-utilisateur-32.sql |
5) dedup-ordre-201307.sql |
===== |
6) TODO: maj-nom-ret.sql |
TODO (pas sur) MAJ du référentiel pour les observation ayant un nom_ret_nn |
7) maj-referentiel-201307.sql |
MAJ du référentiel pour les observation ayant un nom_ret_nn |
8) 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) |
9) referonosaure.sql |
MAJ des observations (valides) avec les nouvelles données générées, à partir de bdtfx/bdtxa |
=== 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écessaire à l'export et à l'import sont massif |
* 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 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 nom vernaculaires meilleurs et uniques: |
Ce sont ceux qui ont le num_statut le plus élevés 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'existe que pour bdtfx, 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 efficace 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.0-agropyraie-20130830/scripts/modules/cel/Cel.php |
---|
New file |
0,0 → 1,94 |
<?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; |
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); |
} |
} |
?> |
/tags/v5.0-agropyraie-20130830/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.0-agropyraie-20130830/scripts/modules/cel/Makefile |
---|
New file |
0,0 → 1,153 |
# 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 (juillet/août 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 |
bdtfx_table = bdtfx_v$(bdtfx) |
bdtxa_table = bdtxa_v$(bdtxa) |
# 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 = @mysql -N $(1) <<<"DESC $(2)" &> /dev/null |
# macro utilisable pour effectuer des substitutions: |
do_subst = sed -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' \ |
$(1) |
# 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' \ |
$(fichiers) |
@printf "Attention: les changements s'appliqueront sur la base \"%s\"\nLes sources utilisées seront: annuaire=\"%s\" , sources=\"%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` |
@read |
reset: |
svn revert -q $(fichiers) |
# supprime les fichiers "compilés" (concaténation de plusieurs scripts SQL substitués) |
clean: |
rm -f *.comp.sql |
help: |
@echo "make [alterdb=<tb_cel_test>] [sourcedb=<tb_eflore>] [annuairedb=<tela_prod_v4>] [bdtfx=<1_01>] [bdtxa=<1_00>]" |
### mises à jour |
# mise à jour de juillet/août 2013 |
# spécifier dans les targets l'ordre (cf A_LIRE.txt) |
# 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é (bien, |
maj1: compile cel_references maj-struct-201307 maj-cleanup-201307 fix-utilisateur-32 dedup-ordre-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 |
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 |
cel_references: |
$(call is_table,$(sourcedb),$(bdtfx_table)) |
$(call is_table,$(alterdb),cel_references) || mysql < cel_references.sql |
o_cel_references: |
$(call is_table,$(sourcedb),$(bdtfx_table)) |
$(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) |
@mysql -N $(alterdb) <<<"DESC cel_obs nom_sel"|grep -q 601 || mysql < maj-struct-201307.sql |
o_maj-struct-201307: check_cel_obs |
$(call is_table,$(annuairedb),annuaire_tela) |
@mysql -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 |
@ ! mysql -N $(alterdb) <<<"SELECT 1 FROM cel_obs WHERE date_observation = '0000-00-00 00:00:00' LIMIT 1"|grep -q 1 || mysql < maj-cleanup-201307.sql |
o_maj-cleanup-201307: |
@ ! mysql -N $(alterdb) <<<"SELECT 1 FROM cel_obs WHERE date_observation = '0000-00-00 00:00:00' LIMIT 1"|grep -q 1 || $(call do_subst,maj-cleanup-201307.sql) > $@.comp.sql |
fix-utilisateur-32: check_cel_obs |
@mysql -N $(alterdb) <<<"DESC cel_obs ce_utilisateur"|grep -q 255 || mysql < fix-utilisateur-32.sql |
o_fix-utilisateur-32: check_cel_obs |
@mysql -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 |
#@mysql -N $(alterdb) <<<'SELECT distinct ce_utilisateur FROM `cel_obs` GROUP BY ce_utilisateur, ordre HAVING COUNT(*) > 1'|grep -q . || mysql < dedup-ordre-201307.sql |
@mysql -N $(alterdb) <<<"SHOW INDEX FROM cel_obs"|grep -q couple_user_ordre || mysql < dedup-ordre-201307.sql |
o_dedup-ordre-201307: check_cel_obs |
@mysql -N $(alterdb) <<<"SHOW INDEX FROM cel_obs"|grep -q couple_user_ordre || $(call do_subst,dedup-ordre-201307.sql) > $@.comp.sql |
# pour une prochaine maj |
maj-nom-ret: |
@mysql -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 |
Property changes: |
Added: svn:eol-style |
+native |
\ No newline at end of property |
/tags/v5.0-agropyraie-20130830/scripts/modules/cel/referonosaure.sql |
---|
New file |
0,0 → 1,84 |
/* |
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 et bdtxa). |
*/ |
--- 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_sel_nn != 0 |
AND nom_referentiel like 'bdtfx%' |
AND nom_sel_nn = num_nom |
) |
ORDER BY id_observation asc; |
*/ |
--- l'update --- |
UPDATE `BASEEDIT`.`cel_obs` c, `BASESOURCE`.`TABLEBDTFX` b SET |
c.nom_ret = CONCAT(b.nom_sci, ' ', b.auteur), |
c.nom_ret_nn = b.num_nom, |
c.nt = b.num_taxonomique, |
c.famille = b.famille |
WHERE ( |
nom_sel_nn IS NOT NULL AND nom_sel_nn != 0 |
AND nom_referentiel like 'bdtfx%' |
AND nom_sel_nn = num_nom |
AND LOWER(c.famille) = LOWER(b.famille) |
AND SUBSTRING_INDEX(c.nom_sel, ' ', 1) = SUBSTRING_INDEX(b.nom_sci, ' ', 1) |
); |
-- 31739 sans les restrictions sur famille et SUBSTRING_INDEX() |
-- 31524 avec les restrictions sur famille et SUBSTRING_INDEX() |
UPDATE `BASEEDIT`.`cel_obs` c, `BASESOURCE`.`TABLEBDTXA` a SET |
c.nom_ret = CONCAT(a.nom_sci, ' ', a.auteur), |
c.nom_ret_nn = a.num_nom, |
c.nt = a.num_tax, |
c.famille = a.famille |
WHERE ( |
nom_sel_nn IS NOT NULL AND nom_sel_nn != 0 |
AND nom_referentiel like 'bdtxa%' |
AND nom_sel_nn = num_nom |
AND LOWER(c.famille) = LOWER(a.famille) |
AND SUBSTRING_INDEX(c.nom_sel, ' ', 1) = SUBSTRING_INDEX(a.nom_sci, ' ', 1) |
); |
-- 49 sans les restrictions sur famille et SUBSTRING_INDEX() |
-- 47 avec les restrictions sur famille et SUBSTRING_INDEX() |
UPDATE `BASEEDIT`.`cel_obs` c, `BASESOURCE`.`isfan_v2013` a SET |
c.nom_ret = CONCAT(a.nom_sci, ' ', a.auteur), |
c.nom_ret_nn = a.num_nom, |
c.nt = a.num_taxonomique, |
c.famille = a.famille |
WHERE ( |
nom_sel_nn IS NOT NULL AND nom_sel_nn != 0 |
AND nom_referentiel like 'isfan%' |
AND nom_sel_nn = num_nom |
); |
/* |
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 |
*/ |
/tags/v5.0-agropyraie-20130830/scripts/modules/cel/fix-utilisateur-32.sql |
---|
New file |
0,0 → 1,10 |
-- 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'; |
/tags/v5.0-agropyraie-20130830/scripts/modules/cel/.current |
---|
New file |
0,0 → 1,8 |
-- 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 |
/tags/v5.0-agropyraie-20130830/scripts/modules/cel/cel.ini |
---|
New file |
0,0 → 1,31 |
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}" |
/tags/v5.0-agropyraie-20130830/scripts/modules/cel/maj-referentiel-201307.sql |
---|
New file |
0,0 → 1,69 |
/* |
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; |
/* Donc nous JOINons: |
-- INNER JOIN sur bdtfx: 69719 |
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: 79471 dont 9752 en dup (bdtxa + bdtfx), eg: 1005047 |
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 T_bis b ON (b.num_nom = c.nom_sel_nn) WHERE nom_referentiel AND nom_sel_nn IS NOT NULL; |
*/ |
/* d'où GROUP BY id_observation HAVING count(id_observation) = 1 */ |
/* |
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 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; |
-- 59967, 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 ; |
/tags/v5.0-agropyraie-20130830/scripts/modules/cel/maj-struct-201307.sql |
---|
New file |
0,0 → 1,17 |
-- 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); |
/tags/v5.0-agropyraie-20130830/scripts/modules/cel/cel_references.sql |
---|
New file |
0,0 → 1,124 |
/* |
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", |
-- 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 nvjfl_v2007'; |
-- tables temporaires |
DROP TEMPORARY TABLE IF EXISTS `T_nvjfl_v2007`, `T_nva_v2013_06`, `T_basevegflor`; |
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) ); |
-- 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 |
-- 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`.`isfan_v2013` 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`.`isfan_v2013` \ |
) AS theorie, \ |
(SELECT COUNT(1) AS a FROM tb_cel.cel_references) AS pratique; |
-- bdtfx+bdtxa+isfan: 141181 (2013/07/23) |
/tags/v5.0-agropyraie-20130830/scripts/modules/cel/dedup-ordre-201307.sql |
---|
New file |
0,0 → 1,99 |
-- 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 |
NOT DETERMINISTIC |
BEGIN |
DECLARE c INT; |
SET c = (SELECT MAX(ordre) + 1 FROM `BASEEDIT`.`cel_obs` where ce_utilisateur = s1); |
RETURN c; |
END |
| |
-- SELECT next_ordre("vincent.vuillermoz@apprenti.isa-lille.fr"); |
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("vincent.vuillermoz@apprenti.isa-lille.fr"); |
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("vincent.vuillermoz@apprenti.isa-lille.fr"); |
CREATE PROCEDURE update_ordre_users() |
BEGIN |
DECLARE _nom VARCHAR(255); |
DECLARE subst INT DEFAULT 0; |
DECLARE done INT DEFAULT 1; |
-- temp table |
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 ce_utilisateur, ordre HAVING COUNT(*) > 1 ); |
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] |
CREATE UNIQUE INDEX couple_user_ordre ON `BASEEDIT`.`cel_obs`(ce_utilisateur, ordre); |
/tags/v5.0-agropyraie-20130830/scripts/modules/cel/maj-cleanup-201307.sql |
---|
New file |
0,0 → 1,37 |
-- 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 = ''; |
-- suppression des infos générées pour les observations dont le nom_sel à été supprimé par l'utilisateur |
-- 3172 |
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 = '' OR nom_sel IS NULL; |
-- problème n°1: mauvais référentiel (bdtfx) |
UPDATE `BASEEDIT`.`cel_obs` SET nom_referentiel = 'bdtxa' WHERE lieudit = 'Grenade'; |
-- problème n°2: nom_sel NULL, nom_ret NOT NULL: 26 obs |
UPDATE `BASEEDIT`.`cel_obs` SET nom_sel = nom_ret, nom_sel_nn = nom_ret_nn WHERE nom_sel IS NULL AND nom_ret IS NOT NULL; |
-- problème n°3: 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°4: ce_zone_geo inutile: 57802 obs |
UPDATE `BASEEDIT`.`cel_obs` SET ce_zone_geo = NULL WHERE ce_zone_geo = 'INSEE-C:'; |
/tags/v5.0-agropyraie-20130830/scripts/modules/cel/maj-nom-ret.sql |
---|
New file |
0,0 → 1,44 |
/* |
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 == 0 est VALIDE (car bdtfx.num_nom_retenu == 0 est valide) [ 3800 nom_retenu "orphelins" de taxon ] |
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 censées être (correctement) autogé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_nn = NULL WHERE nom_sel_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 = '' OR nom_sel IS NULL) AND |
( |
(nom_ret IS NOT NULL AND nom_ret != '') OR |
(nom_ret_nn IS NOT NULL AND nom_ret_nn != 0 AND nom_ret_nn != '') OR |
(nt IS NOT NULL AND nt != 0 AND nt != '') OR |
(famille IS NOT NULL AND famille != '') |
) |
*/ |
/tags/v5.0-agropyraie-20130830/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.0-agropyraie-20130830/scripts/modules/cel/. |
---|
New file |
Property changes: |
Added: svn:ignore |
+o_*.comp.sql |