Subversion Repositories eFlore/Projets.eflore-projets

Rev

Rev 130 | Rev 336 | Go to most recent revision | Only display areas with differences | Ignore whitespace | Details | Blame | Last modification | View Log | RSS feed

Rev 130 Rev 335
1 
<?php
 1 
<?php
2 
//declare(encoding='UTF-8');
 2 
//declare(encoding='UTF-8');
3 
/**
 3 
/**
4 
 * Exemple de lancement du script : :
 4 
 * Exemple de lancement du script : :
5 
 * /opt/lampp/bin/php cli.php bdtfx -a chargerTous
 5 
 * /opt/lampp/bin/php cli.php bdtfx -a chargerTous
6 
 *
 6 
 *
7 
 * @category	php 5.2
 7 
 * @category	php 5.2
8 
 * @package		eFlore/Scripts
 8 
 * @package		eFlore/Scripts
9 
 * @author		Jennifer DHÉ <jennifer@tela-botanica.org>
 9 
 * @author		Jennifer DHÉ <jennifer@tela-botanica.org>
10 
 * @author		Jean-Pascal MILCENT <jpm@tela-botanica.org>
 10 
 * @author		Jean-Pascal MILCENT <jpm@tela-botanica.org>
11 
 * @copyright	Copyright (c) 2011, Tela Botanica (accueil@tela-botanica.org)
 11 
 * @copyright	Copyright (c) 2011, Tela Botanica (accueil@tela-botanica.org)
12 
 * @license		http://www.cecill.info/licences/Licence_CeCILL_V2-fr.txt Licence CECILL
 12 
 * @license		http://www.cecill.info/licences/Licence_CeCILL_V2-fr.txt Licence CECILL
13 
 * @license		http://www.gnu.org/licenses/gpl.html Licence GNU-GPL
 13 
 * @license		http://www.gnu.org/licenses/gpl.html Licence GNU-GPL
14 
 * @version		$Id$
 14 
 * @version		$Id$
15 
 */
 15 
 */
16 
class Bdtfx extends EfloreScript {
 16 
class Bdtfx extends EfloreScript {
17 
 
 17 
 
18 
	private $table = null;
 18 
	private $table = null;
19 
	private $pasInsertion = 1000;
 19 
	private $pasInsertion = 1000;
20 
	private $departInsertion = 0;
 20 
	private $departInsertion = 0;
21 
 
 21 
 
22 
	protected $parametres_autorises = array(
 22 
	protected $parametres_autorises = array(
23 
		'-t' => array(false, false, 'Permet de tester le script sur un jeux réduit de données (indiquer le nombre de lignes).'));
 23 
		'-t' => array(false, false, 'Permet de tester le script sur un jeux réduit de données (indiquer le nombre de lignes).'));
24 
 
 24 
 
25 
	public function executer() {
 25 
	public function executer() {
26 
		try {
 26 
		try {
27 
			$this->initialiserProjet('bdtfx');
 27 
			$this->initialiserProjet('bdtfx');
28 
 
 28 
 
29 
			// Lancement de l'action demandée
 29 
			// Lancement de l'action demandée
30 
			$cmd = $this->getParametre('a');
 30 
			$cmd = $this->getParametre('a');
31 
			switch ($cmd) {
 31 
			switch ($cmd) {
32 
				case 'chargerTous' :
 32 
				case 'chargerTous' :
33 
					$this->chargerStructureSql();
 33 
					$this->chargerStructureSql();
34 
					$this->chargerBdtfx();
 34 
					$this->chargerBdtfx();
35 
					$this->genererNomSciHtml();
 35 
					$this->genererNomSciHtml();
36 
					$this->genererDonneesTestMultiVersion();
 36 
					$this->genererDonneesTestMultiVersion();
37 
					break;
 37 
					break;
38 
				case 'chargerStructureSql' :
 38 
				case 'chargerStructureSql' :
39 
					$this->chargerStructureSql();
 39 
					$this->chargerStructureSql();
40 
					break;
 40 
					break;
41 
				case 'chargerBdtfx' :
 41 
				case 'chargerBdtfx' :
42 
					$this->chargerBdtfx();
 42 
					$this->chargerBdtfx();
43 
					break;
 43 
					break;
44 
				case 'genererNomSciHtml' :
 44 
				case 'genererNomSciHtml' :
45 
					$this->genererNomSciHtml();
 45 
					$this->genererNomSciHtml();
46 
					break;
 46 
					break;
- 
 
 47 
				case 'genererChpFamille' :
- 
 
 48 
					$this->genererChpFamille();
- 
 
 49 
					break;
47 
				case 'genererDonneesTestMultiVersion' :
 50 
				case 'genererDonneesTestMultiVersion' :
48 
					$this->genererDonneesTestMultiVersion();
 51 
					$this->genererDonneesTestMultiVersion();
49 
					break;
 52 
					break;
50 
				case 'supprimerDonneesTestMultiVersion' :
 53 
				case 'supprimerDonneesTestMultiVersion' :
51 
					$this->supprimerDonneesTestMultiVersion();
 54 
					$this->supprimerDonneesTestMultiVersion();
52 
					break;
 55 
					break;
53 
				case 'supprimerTous' :
 56 
				case 'supprimerTous' :
54 
					$this->supprimerTous();
 57 
					$this->supprimerTous();
55 
					break;
 58 
					break;
56 
				default :
 59 
				default :
57 
					throw new Exception("Erreur : la commande '$cmd' n'existe pas!");
 60 
					throw new Exception("Erreur : la commande '$cmd' n'existe pas!");
58 
			}
 61 
			}
59 
		} catch (Exception $e) {
 62 
		} catch (Exception $e) {
60 
			$this->traiterErreur($e->getMessage());
 63 
			$this->traiterErreur($e->getMessage());
61 
		}
 64 
		}
62 
	}
 65 
	}
63 
 
 66 
 
64 
	private function chargerBdtfx() {
 67 
	private function chargerBdtfx() {
65 
		$chemin = Config::get('chemins.bdtfx');
 68 
		$chemin = Config::get('chemins.bdtfx');
66 
		$table = Config::get('tables.bdtfx');
 69 
		$table = Config::get('tables.bdtfx');
67 
		$requete = "LOAD DATA INFILE '$chemin' ".
 70 
		$requete = "LOAD DATA INFILE '$chemin' ".
68 
				"REPLACE INTO TABLE $table ".
 71 
				"REPLACE INTO TABLE $table ".
69 
				'CHARACTER SET utf8 '.
 72 
				'CHARACTER SET utf8 '.
70 
				'FIELDS '.
 73 
				'FIELDS '.
71 
				"	TERMINATED BY '\t' ".
 74 
				"	TERMINATED BY '\t' ".
72 
				"	ENCLOSED BY '' ".
 75 
				"	ENCLOSED BY '' ".
73 
				"	ESCAPED BY '\\\' ".
 76 
				"	ESCAPED BY '\\\' ".
74 
				'IGNORE 1 LINES';
 77 
				'IGNORE 1 LINES';
75 
		$this->getBdd()->requeter($requete);
 78 
		$this->getBdd()->requeter($requete);
76 
	}
 79 
	}
77 
 
 80 
 
78 
	private function genererNomSciHtml() {
 81 
	private function genererNomSciHtml() {
79 
		$this->initialiserGenerationNomsSciHtml();
 82 
		$this->initialiserGenerationChamps();
80 
		$this->preparerTable();
 83 
		$this->preparerTablePrChpNomSciHtml();
81 
		$generateur = new GenerateurNomSciHtml();
 84 
		$generateur = new GenerateurNomSciHtml();
82 
		$nbreTotal = $this->recupererNbTotalTuples();
 85 
		$nbreTotal = $this->recupererNbTotalTuples();
- 
 
 86 
		$this->departInsertion = 0;
83 
		while ($this->departInsertion < $nbreTotal) {
 87 
		while ($this->departInsertion < $nbreTotal) {
84 
			$resultat = $this->recupererTuples();
 88 
			$resultat = $this->recupererTuples();
85 
			$nomsSciEnHtml = $generateur->generer($resultat);
 89 
			$nomsSciEnHtml = $generateur->generer($resultat);
86 
			$this->lancerRequeteModification($nomsSciEnHtml);
 90 
			$this->lancerRequeteModification($nomsSciEnHtml);
87 
			$this->departInsertion += $this->pasInsertion;
 91 
			$this->departInsertion += $this->pasInsertion;
88 
			$this->afficherAvancement("Insertion des noms scientifique au format HTML dans la base par paquet de {$this->pasInsertion} en cours");
 92 
			$this->afficherAvancement("Insertion des noms scientifique au format HTML dans la base par paquet de {$this->pasInsertion} en cours");
89 
			if ($this->stopperLaBoucle($this->getParametre('t'))) break;
 93 
			if ($this->stopperLaBoucle($this->getParametre('t'))) break;
90 
		}
 94 
		}
91 
		echo "\n";
 95 
		echo "\n";
92 
	}
 96 
	}
93 
 
 97 
 
94 
	private function initialiserGenerationNomsSciHtml() {
 98 
	private function initialiserGenerationChamps() {
95 
		$this->table = Config::get('tables.bdtfx');
 99 
		$this->table = Config::get('tables.bdtfx');
96 
	}
 100 
	}
97 
 
 101 
 
98 
	private function preparerTable() {
 102 
	private function preparerTablePrChpNomSciHtml() {
99 
		$requete = "SHOW COLUMNS FROM {$this->table} LIKE 'nom_sci_html' ";
 103 
		$requete = "SHOW COLUMNS FROM {$this->table} LIKE 'nom_sci_html' ";
100 
		$resultat = $this->getBdd()->recuperer($requete);
 104 
		$resultat = $this->getBdd()->recuperer($requete);
101 
		if ($resultat === false) {
 105 
		if ($resultat === false) {
102 
			$requete = 	"ALTER TABLE {$this->table} ".
 106 
			$requete = 	"ALTER TABLE {$this->table} ".
103 
							'ADD nom_sci_html VARCHAR( 500 ) '.
 107 
				'ADD nom_sci_html VARCHAR( 500 ) '.
104 
							'CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ';
 108 
				'CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ';
105 
			$this->getBdd()->requeter($requete);
 109 
			$this->getBdd()->requeter($requete);
106 
		}
 110 
		}
107 
	}
 111 
	}
108 
 
 112 
 
109 
	private function recupererNbTotalTuples(){
 113 
	private function recupererNbTotalTuples(){
110 
		$requete = "SELECT count(*) AS nb FROM {$this->table} ";
 114 
		$requete = "SELECT count(*) AS nb FROM {$this->table} ";
111 
		$resultat = $this->getBdd()->recuperer($requete);
 115 
		$resultat = $this->getBdd()->recuperer($requete);
112 
		return $resultat['nb'];
 116 
		return $resultat['nb'];
113 
	}
 117 
	}
114 
 
 118 
 
115 
	private function recupererTuples() {
 119 
	private function recupererTuples() {
116 
		$requete = 'SELECT 	num_nom, rang, nom_supra_generique, genre, epithete_infra_generique, '.
 120 
		$requete = 'SELECT 	num_nom, rang, nom_supra_generique, genre, epithete_infra_generique, '.
117 
					'	epithete_sp, type_epithete, epithete_infra_sp,cultivar_groupe, '.
 121 
			'	epithete_sp, type_epithete, epithete_infra_sp,cultivar_groupe, '.
118 
					'	nom_commercial, cultivar '.
 122 
			'	nom_commercial, cultivar '.
119 
					"FROM {$this->table} ".
 123 
			"FROM {$this->table} ".
120 
					"LIMIT {$this->departInsertion},{$this->pasInsertion} ";
 124 
			"LIMIT {$this->departInsertion},{$this->pasInsertion} ";
121 
		$resultat = $this->getBdd()->recupererTous($requete);
 125 
		$resultat = $this->getBdd()->recupererTous($requete);
122 
		return $resultat;
 126 
		return $resultat;
123 
	}
 127 
	}
124 
 
 128 
 
125 
	private function lancerRequeteModification($nomsSciHtm) {
 129 
	private function lancerRequeteModification($nomsSciHtm) {
126 
		foreach ($nomsSciHtm as $id => $html) {
 130 
		foreach ($nomsSciHtm as $id => $html) {
127 
			$html = $this->getBdd()->proteger($html);
 131 
			$html = $this->getBdd()->proteger($html);
128 
			$requete = "UPDATE {$this->table} ".
 132 
			$requete = "UPDATE {$this->table} ".
129 
				"SET nom_sci_html = $html ".
 133 
				"SET nom_sci_html = $html ".
130 
				"WHERE num_nom = $id ";
 134 
				"WHERE num_nom = $id ";
131 
			$resultat = $this->getBdd()->requeter($requete);
 135 
			$resultat = $this->getBdd()->requeter($requete);
132 
			if ($resultat === false) {
 136 
			if ($resultat === false) {
133 
				throw new Exception("Erreur d'insertion pour le tuple $id");
 137 
				throw new Exception("Erreur d'insertion pour le tuple $id");
134 
			}
 138 
			}
135 
		}
 139 
		}
136 
	}
 140 
	}
- 
 
 141
- 
 
 142 
	private function genererChpFamille() {
- 
 
 143 
		$this->initialiserGenerationChamps();
- 
 
 144 
		$this->preparerTablePrChpFamille();
- 
 
 145 
		$generateur = new GenerateurNomSciHtml();
- 
 
 146 
		$resultats = $this->recupererTuples();
- 
 
 147 
		$classif = array();
- 
 
 148 
		foreach ($resultats as $nom) {
- 
 
 149 
			if ($nom['rang'])
- 
 
 150 
			$this->afficherAvancement("Insertion des noms de famille dans la base par paquet de {$this->pasInsertion} en cours");
- 
 
 151 
			if ($this->stopperLaBoucle($this->getParametre('t'))) break;
- 
 
 152 
		}
- 
 
 153 
	}
- 
 
 154
- 
 
 155 
	private function preparerTablePrChpFamille() {
- 
 
 156 
		$requete = "SHOW COLUMNS FROM {$this->table} LIKE 'famille' ";
- 
 
 157 
		$resultat = $this->getBdd()->recuperer($requete);
- 
 
 158 
		if ($resultat === false) {
- 
 
 159 
			$requete = 	"ALTER TABLE {$this->table} ".
- 
 
 160 
				'ADD famille VARCHAR(255) '.
- 
 
 161 
				'CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ';
- 
 
 162 
			$this->getBdd()->requeter($requete);
- 
 
 163 
		}
- 
 
 164 
	}
- 
 
 165
- 
 
 166 
	private function recupererTuplesPrChpFamille() {
- 
 
 167 
		$requete = 'SELECT num_nom, num_tax_sup, rang, nom_sci '.
- 
 
 168 
				"FROM {$this->table} ".
- 
 
 169 
				"WHERE rang >= 180 ".
- 
 
 170 
				"ORDER BY rang ASC,num_tax_sup ASC ";
- 
 
 171 
		$resultat = $this->getBdd()->recupererTous($requete);
- 
 
 172 
		return $resultat;
- 
 
 173 
	}
137 
 
 174 
 
138 
	private function genererDonneesTestMultiVersion() {
 175 
	private function genererDonneesTestMultiVersion() {
139 
		$contenuSql = $this->recupererContenu(Config::get('chemins.structureSqlTest'));
 176 
		$contenuSql = $this->recupererContenu(Config::get('chemins.structureSqlTest'));
140 
		$this->executerScripSql($contenuSql);
 177 
		$this->executerScripSql($contenuSql);
141 
 
 178 
 
142 
		$table = Config::get('tables.bdtfx');
 179 
		$table = Config::get('tables.bdtfx');
143 
		$tableTest = Config::get('tables.bdtfxTest');
 180 
		$tableTest = Config::get('tables.bdtfxTest');
144 
		$requete = "INSERT INTO $tableTest SELECT * FROM $table";
 181 
		$requete = "INSERT INTO $tableTest SELECT * FROM $table";
145 
		$this->getBdd()->requeter($requete);
 182 
		$this->getBdd()->requeter($requete);
146 
	}
 183 
	}
147 
 
 184 
 
148 
	private function supprimerDonneesTestMultiVersion() {
 185 
	private function supprimerDonneesTestMultiVersion() {
149 
		$tableMeta = Config::get('tables.bdtfxMeta');
 186 
		$tableMeta = Config::get('tables.bdtfxMeta');
150 
		$requete = "DELETE FROM $tableMeta WHERE guid = 'urn:lsid:tela-botanica.org:bdtfx:1.02'";
 187 
		$requete = "DELETE FROM $tableMeta WHERE guid = 'urn:lsid:tela-botanica.org:bdtfx:1.02'";
151 
		$this->getBdd()->requeter($requete);
 188 
		$this->getBdd()->requeter($requete);
152 
 
 189 
 
153 
		$tableTest = Config::get('tables.bdtfxTest');
 190 
		$tableTest = Config::get('tables.bdtfxTest');
154 
		$requete = "DROP TABLE $tableTest";
 191 
		$requete = "DROP TABLE $tableTest";
155 
		$this->getBdd()->requeter($requete);
 192 
		$this->getBdd()->requeter($requete);
156 
	}
 193 
	}
157 
 
 194 
 
158 
	private function supprimerTous() {
 195 
	private function supprimerTous() {
159 
		$requete = "DROP TABLE bdtfx_meta, bdtfx_v1_01, bdtfx_v1_02";
 196 
		$requete = "DROP TABLE bdtfx_meta, bdtfx_v1_01, bdtfx_v1_02";
160 
		$this->getBdd()->requeter($requete);
 197 
		$this->getBdd()->requeter($requete);
161 
	}
 198 
	}
162 
}
 199 
}
163 
?>
 200 
?>