1,5 → 1,6 |
<?php |
/** Exemple lancement: |
/** Corriger les codes langues pays genre et nombre à la main |
* Exemple lancement: |
* /opt/lampp/bin/php -d memory_limit=3500M ~/web/eflore-projets/scripts/cli.php nva -a chargerTous |
*/ |
class Nva extends EfloreScript { |
13,9 → 14,10 |
switch ($cmd) { |
case 'chargerTous' : |
$this->chargerStructureSql(); |
$this->chargerDonnees(); |
$this->genererChampCodeLangue(); |
$this->genererChampNumTax(); |
$this->chargerDonnees(Config::get('chemins.nva'), Config::get('tables.nva')); |
$this->chargerDonnees(Config::get('chemins.index'), Config::get('tables.index')); |
$this->ajouterChampNomVernaIndex(); |
$this->chargerDonnees(Config::get('chemins.ontologies'), Config::get('tables.ontologies')); |
break; |
case 'chargerStructureSql' : |
$this->chargerStructureSql(); |
23,12 → 25,9 |
case 'chargerDonnees' : |
$this->chargerDonnees(); |
break; |
case 'genererChampNumTax' : |
$this->genererChampNumTax(); |
case 'ajouterChampNomVernaIndex' : |
$this->ajouterChampNomVernaIndex(); |
break; |
case 'genererChampCodeLangue' : |
$this->genererChampCodeLangue(); |
break; |
case 'supprimerTous' : |
$this->supprimerTous(); |
break; |
40,9 → 39,7 |
} |
} |
|
private function chargerDonnees() { |
$chemin = Config::get('chemins.nva'); |
$table = Config::get('tables.nva'); |
private function chargerDonnees($chemin, $table) { |
$requete = "LOAD DATA INFILE '$chemin' ". |
"REPLACE INTO TABLE $table ". |
'CHARACTER SET utf8 '. |
54,94 → 51,46 |
$this->getBdd()->requeter($requete); |
} |
|
private function genererChampNumTax() { |
$this->preparerTablePrChpNumTax(); |
$nns_taxons = $this->recupererTuplesPrNumTax(); |
$this->remplirChpNumTax($nns_taxons); |
private function ajouterChampNomVernaIndex() { |
$this->preparerTablePrChpNomVerna(); |
$this->remplirChpNomVerna(); |
} |
|
private function preparerTablePrChpNumTax() { |
$table = Config::get('tables.nva'); |
$requete = "SHOW COLUMNS FROM $table LIKE 'num_taxon' "; |
private function preparerTablePrChpNomVerna() { |
$table = Config::get('tables.index'); |
$requete = "SHOW COLUMNS FROM $table LIKE 'nom_vernaculaire' "; |
$resultat = $this->getBdd()->recuperer($requete); |
if ($resultat === false) { |
$requete = "ALTER TABLE $table ". |
'ADD num_taxon INT(10) '; |
'ADD `nom_vernaculaire` VARCHAR( 40 ) NOT NULL , |
ADD `code_langue` VARCHAR( 10 ) NOT NULL , |
ADD `num_genre` INT( 1 ), ADD `num_nombre` INT( 1 ) '; |
$this->getBdd()->requeter($requete); |
} |
} |
|
|
private function recupererTuplesPrNumTax() { |
$table_ref = Config::get('tables.bdtxa'); |
$requete = 'SELECT num_tax, num_nom '. |
"FROM ".$table_ref." "; |
$resultat = $this->getBdd()->recupererTous($requete); |
return $resultat; |
} |
|
private function remplirChpNumTax($nns_taxons) { |
$table = Config::get('tables.nva'); |
foreach ($nns_taxons as $nn) { |
if (!empty($nn['num_tax']) && is_numeric($nn['num_tax'])) { |
$requete = "UPDATE $table SET num_taxon = ".$nn['num_tax']." WHERE num_nom = ".$nn['num_nom']." "; |
$resultat = $this->getBdd()->requeter($requete); |
} |
if ($resultat === false) { |
throw new Exception("Erreur d'insertion pour le tuple $id"); |
} |
$this->afficherAvancement("Insertion des nums taxonomiques dans la base en cours"); |
private function remplirChpNomVerna() { |
$table = Config::get('tables.index'); |
$requete = "UPDATE `nva_index_v2_03` |
SET nom_vernaculaire=(select n.nom_vernaculaire from nva_v2_03 n where n.num_nom_vernaculaire=nva_index_v2_03.num_nom_vernaculaire), |
code_langue=(select n.code_langue from nva_v2_03 n where n.num_nom_vernaculaire=nva_index_v2_03.num_nom_vernaculaire), |
num_genre=(select n.num_genre from nva_v2_03 n where n.num_nom_vernaculaire=nva_index_v2_03.num_nom_vernaculaire), |
num_nombre=(select n.num_nombre from nva_v2_03 n where n.num_nom_vernaculaire=nva_index_v2_03.num_nom_vernaculaire)"; |
$resultat = $this->getBdd()->requeter($requete); |
if ($resultat === false) { |
throw new Exception("Erreur d'update d'ajouts des noms"); |
} |
$this->afficherAvancement("Ajout des noms vernaculaires à l'index"); |
echo "\n"; |
} |
|
private function genererChampCodeLangue() { |
$this->preparerTablePrChpCodeLangue(); |
$codes_langues = $this->recupererTuplesPrCodeLangue(); |
$this->remplirChpCodeLangue($codes_langues); |
} |
|
private function preparerTablePrChpCodeLangue() { |
$table = Config::get('tables.nva'); |
$requete = "SHOW COLUMNS FROM $table LIKE 'code_langue' "; |
$resultat = $this->getBdd()->recuperer($requete); |
if ($resultat === false) { |
$requete = "ALTER TABLE $table ". |
'ADD code_langue VARCHAR(3) '; |
$this->getBdd()->requeter($requete); |
} |
} |
|
private function recupererTuplesPrCodeLangue() { |
$codes_langues = array(); |
if (($handle = fopen(Config::get("dossierTsv")."codes_langues.csv", "r")) !== false) { |
while (($data = fgetcsv($handle, 1000, ",")) !== false) { |
$codes_langues[] = array('code_iso_639_3' => $data[0], |
'nom' => $data[1]); |
} |
fclose($handle); |
} |
|
return $codes_langues; |
} |
|
private function remplirChpCodeLangue($codes_langues) { |
$table = Config::get('tables.nva'); |
foreach ($codes_langues as $code) { |
if (!empty($code['nom'])) { |
$requete = "UPDATE $table SET code_langue = ".$this->getBdd()->proteger($code['code_iso_639_3'])." ". |
"WHERE TRIM(langue) = ".$this->getBdd()->proteger(trim($code['nom']))." "; |
$resultat = $this->getBdd()->requeter($requete); |
} |
if ($resultat === false) { |
throw new Exception("Erreur d'insertion pour le tuple $id"); |
} |
$this->afficherAvancement("Insertion des codes de langues dans la base en cours"); |
} |
echo "\n"; |
} |
|
|
|
private function supprimerTous() { |
$requete = "DROP TABLE IF EXISTS ".Config::get('tables.nva').", nva_meta "; |
$requete = "DROP TABLE IF EXISTS ".Config::get('tables.nva').", ".Config::get('tables.index'). |
", ".Config::get('tables.ontologies').", nva_meta "; |
$this->getBdd()->requeter($requete); |
} |
} |