/branches/v5.12-baouque/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 ? |
/branches/v5.12-baouque/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" |
/branches/v5.12-baouque/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))); |
/branches/v5.12-baouque/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 |
*/ |
/branches/v5.12-baouque/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; |
/branches/v5.12-baouque/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 |
*/ |
/branches/v5.12-baouque/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 ; |
/branches/v5.12-baouque/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; |
} |
} |
/branches/v5.12-baouque/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); |
} |
} |
} |
?> |
/branches/v5.12-baouque/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 |
/branches/v5.12-baouque/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 |
/branches/v5.12-baouque/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` char(1) DEFAULT NULL COMMENT 'depuis TABLEBASEFLOR', |
`ve_temperature` char(1) DEFAULT NULL COMMENT 'depuis TABLEBASEFLOR', |
`ve_continentalite` char(1) DEFAULT NULL COMMENT 'depuis TABLEBASEFLOR', |
`ve_humidite_atmos` char(1) DEFAULT NULL COMMENT 'depuis TABLEBASEFLOR', |
`ve_humidite_edaph` varchar(2) DEFAULT NULL COMMENT 'depuis TABLEBASEFLOR', |
`ve_reaction_sol` char(1) DEFAULT NULL COMMENT 'depuis TABLEBASEFLOR', |
`ve_nutriments_sol` char(1) DEFAULT NULL COMMENT 'depuis TABLEBASEFLOR', |
`ve_salinite` char(1) DEFAULT NULL COMMENT 'depuis TABLEBASEFLOR', |
`ve_texture_sol` char(1) DEFAULT NULL COMMENT 'depuis TABLEBASEFLOR', |
`ve_mat_org_sol` char(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) |
/branches/v5.12-baouque/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; |
/branches/v5.12-baouque/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` char(1) DEFAULT NULL COMMENT 'depuis baseflor_v2012_12_31', |
`ve_temperature` char(1) DEFAULT NULL COMMENT 'depuis baseflor_v2012_12_31', |
`ve_continentalite` char(1) DEFAULT NULL COMMENT 'depuis baseflor_v2012_12_31', |
`ve_humidite_atmos` char(1) DEFAULT NULL COMMENT 'depuis baseflor_v2012_12_31', |
`ve_humidite_edaph` varchar(2) DEFAULT NULL COMMENT 'depuis baseflor_v2012_12_31', |
`ve_reaction_sol` char(1) DEFAULT NULL COMMENT 'depuis baseflor_v2012_12_31', |
`ve_nutriments_sol` char(1) DEFAULT NULL COMMENT 'depuis baseflor_v2012_12_31', |
`ve_salinite` char(1) DEFAULT NULL COMMENT 'depuis baseflor_v2012_12_31', |
`ve_texture_sol` char(1) DEFAULT NULL COMMENT 'depuis baseflor_v2012_12_31', |
`ve_mat_org_sol` char(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) |
/branches/v5.12-baouque/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); |
/branches/v5.12-baouque/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 |
/branches/v5.12-baouque/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; |
/branches/v5.12-baouque/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"; |
/branches/v5.12-baouque/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 |
/branches/v5.12-baouque/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'; |
/branches/v5.12-baouque/scripts/modules/cel/. |
---|
New file |
Property changes: |
Added: svn:ignore |
+o_*.comp.sql |