Subversion Repositories eFlore/Applications.cel

Rev

Rev 2027 | Show entire file | Ignore whitespace | Details | Blame | Last modification | View Log | RSS feed

Rev 2027 Rev 2032
Line 14... Line 14...
14 
	private $config;
 14 
	private $config;
15 
	private $mode;
 15 
	private $mode;
Line 16... Line 16...
16
16
17 
	private $table_liaison;
 17 
	private $table_liaison;
- 
 
 18 
	private $table_mots_cles;
18 
	private $table_mots_cles;
 19
Line 19... Line 20...
19 
	private $champ_id_element_lie;
 20 
	//TODO: trigger pour les tables liaisons
20
21
21 
	public function GestionMotsClesChemin($config, $mode = 'obs') {
 22 
	public function GestionMotsClesChemin($config, $mode = 'obs') {
22 
		$this->config = $config;
 23 
		$this->config = $config;
Line 23... Line 24...
23 
		//TODO: switch suivant mode
 24 
		//TODO: switch suivant mode
24 
		$this->mode = $mode;
 25 
		$this->mode = $mode;
25
26
26 
		if($mode == 'obs') {
 - 
 
27 
			$this->table_liaison = 'cel_obs_tags_path_liaison';
 27 
		if($mode == 'obs') {
28 
			$this->table_mots_cles = 'cel_obs_tags_path';
 28 
			$this->table_liaison = 'cel_obs_tags_path_liaison';
29 
			$this->champ_id_element_lie = 'id_obs';
 29 
			$this->table_mots_cles = 'cel_obs_tags_path';
30 
		} else {
 - 
 
31 
			$this->table_liaison = 'cel_images_tags_path_liaison';
 30 
		} else {
32 
			$this->table_mots_cles = 'cel_images_tags_path';
 31 
			$this->table_liaison = 'cel_images_tags_path_liaison';
33 
			$this->champ_id_element_lie = 'id_image';
 - 
 
34 
		}
 - 
 
35 
	}
 - 
 
36
- 
 
Line 37... Line -...
37 
	public function viderTables() {
 - 
 
38 
		$requete = "TRUNCATE ".$this->table_mots_cles." ";
 - 
 
39 
		$vidage = Cel::db()->executer($requete.' -- '.__FILE__.':'.__LINE__);
 - 
 
40
- 
 
41 
		$requete = "TRUNCATE ".$this->table_liaison." ";
 - 
 
42 
		$vidage = Cel::db()->executer($requete.' -- '.__FILE__.':'.__LINE__);
 - 
 
43
32 
			$this->table_mots_cles = 'cel_images_tags_path';
44 
		return $vidage;
 33 
		}
45 
	}
 34 
	}
Line 46... Line 35...
46
35
Line 59... Line 48...
59 
	public function construireRequeteCheminAnd($mots_cles) {
 48 
	public function construireRequeteCheminAnd($mots_cles) {
60 
		return $this->construireRequeteChemin($mots_cles, 'AND');
 49 
		return $this->construireRequeteChemin($mots_cles, 'AND');
61 
	}
 50 
	}
Line 62... Line 51...
62
51
63 
	public function construireRequete($mots_cles, $operateur = 'AND') {
 - 
 
64 
		$requete = "SELECT * FROM cel_images_tags_path_liaison ";
 52 
	public function construireRequete($mots_cles, $operateur = 'AND') {
65 
		$criteres = array();
 53 
		$criteres = array();
- 
 
 54 
		$premier = array_pop($mots_cles);
- 
 
 55 
		$requete = "SELECT * FROM ".$this->table_liaison." lmc ".
66 
		$premier = array_pop($mots_cles);
 56 
		           "INNER JOIN ".$this->table_mots_cles." mc ".
67 
		$requete .= 'WHERE (tag = '.Cel::db()->proteger($premier).' ';
 - 
 
68
57 
		           "ON mc.tag = ".Cel::db()->proteger($premier)." AND lmc.id_tag = mc.id_tag ";
69 
		foreach ($mots_cles as $mot_cle) {
 58 
		foreach ($mots_cles as $mot_cle) {
70 
			$requete .= " ".$operateur." id_image IN (SELECT id_image ".
 59 
			$requete .= " ".$operateur." id_element_lie IN (SELECT id_element_lie FROM ".$this->table_liaison." lmc ".
71 
										"FROM cel_images_tags_path_liaison ".
 60 
							"INNER JOIN ".$this->table_mots_cles." mc ".
72 
										"WHERE tag = ".Cel::db()->proteger($mot_cle)." ".
 61 
		           			"ON mc.tag = ".Cel::db()->proteger($mot_cle)." AND lmc.id_tag = mc.id_tag ".
73 
										") ";
 62 
						") ";
74 
		}
 - 
 
- 
 
 63 
		}
75 
		$requete .= ")";
 64 
 
76 
		$images = Cel::db()->requeter($requete);
 65 
		$images = Cel::db()->requeter($requete);
77 
		return $images;
 66 
		return $images;
Line 78... Line 67...
78 
	}
 67 
	}
79
- 
 
80 
	private function construireRequeteChemin($mots_cles, $operateur = 'AND') {
 68
81 
		$requete = "SELECT * FROM cel_images_tags_path_liaison ";
 69 
	private function construireRequeteChemin($mots_cles, $operateur = 'AND') {
- 
 
 70 
			$criteres = array();
- 
 
 71 
		$premier = array_pop($mots_cles);
82 
		$criteres = array();
 72 
		$requete = "SELECT * FROM ".$this->table_liaison." lmc ".
83 
		$premier = array_pop($mots_cles);
 - 
 
84 
		$requete .= 'WHERE (chemin LIKE '.Cel::db()->proteger('%/'.$premier.'/%').' ';
 73 
		           "INNER JOIN ".$this->table_mots_cles." mc ".
85
74 
		           "ON mc.chemin LIKE ".Cel::db()->proteger('%'.$premier.'%')." AND lmc.id_tag = mc.id_tag ";
86 
		foreach ($mots_cles as $mot_cle) {
 75 
		foreach ($mots_cles as $mot_cle) {
87 
			$requete .= " ".$operateur." id_image IN (SELECT id_image ".
 76 
			$requete .= " ".$operateur." id_element_lie IN (SELECT id_element_lie FROM ".$this->table_liaison." lmc ".
88 
												"FROM cel_images_tags_path_liaison ".
 77 
							"INNER JOIN ".$this->table_mots_cles." mc ".
89 
												"WHERE chemin LIKE ".Cel::db()->proteger('%/'.$mot_cle.'/%')." ".
 78 
		           			"ON mc.chemin LIKE ".Cel::db()->proteger('%'.$mot_cle.'%')." AND lmc.id_tag = mc.id_tag ".
90 
												") ";
 - 
 
- 
 
 79 
						") ";
91 
		}
 80 
		}
92 
		$requete .= ")";
 81
93 
		$images = Cel::db()->requeter($requete);
 82 
		$images = Cel::db()->requeter($requete);
Line 94... Line -...
94 
		return $images;
 - 
 
95 
	}
 - 
 
96
- 
 
97 
	private function obtenirMotsClesOriginaux($mode) {
 - 
 
98
- 
 
99 
		if($mode == 'obs') {
 - 
 
100 
			$requete_arbre = "SELECT id_mot_cle_obs as id_mot_cle, ce_mot_cle_obs_parent as parent, id_utilisateur, mot_cle ".
 - 
 
101 
							"FROM cel_mots_cles_obs_srv ORDER BY id_utilisateur, niveau";
 - 
 
102 
		} else {
 - 
 
103 
			$requete_arbre = "SELECT id_mot_cle_image as id_mot_cle, ce_mot_cle_image_parent as parent, id_utilisateur, mot_cle ".
 - 
 
104 
							"FROM cel_mots_cles_images_srv ORDER BY id_utilisateur, niveau";
 - 
 
105 
		}
 - 
 
106
- 
 
107 
		$mots_cles = Cel::db()->requeter($requete_arbre);
 - 
 
108
- 
 
109 
		$mots_cles_hierarchiques = array();
 - 
 
110 
		$correspondances_id_mots_cles_obs = array();
 - 
 
111
- 
 
112 
		$rien = 0;
 - 
 
113
- 
 
114 
		foreach($mots_cles as &$mot) {
 - 
 
115
- 
 
116 
			if(!empty($mot['mot_cle'])) {
 - 
 
117 
				$chemin = '/';
 - 
 
118 
				$cle_parent = $mot['parent'].'-'.$mot['id_utilisateur'];
 - 
 
119 
				if(isset($mots_cles_hierarchiques[$cle_parent])) {
 - 
 
120 
					$chemin = $mots_cles_hierarchiques[$cle_parent]['chemin'];
 - 
 
121 
				}
 - 
 
122 
				$chemin .= self::simplifier($mot['mot_cle']).'/';
 - 
 
123 
				$chemin = str_replace("//", "/", $chemin);
 - 
 
124
- 
 
125 
				$cle = $mot['id_mot_cle'].'-'.$mot['id_utilisateur'];
 - 
 
126
- 
 
127 
				$mots_cles_hierarchiques[$cle] = array(
 - 
 
128 
								'id_utilisateur' => $mot['id_utilisateur'],
 - 
 
129 
								'chemin' => $chemin,
 - 
 
130 
								'tag' => $mot['mot_cle']
 - 
 
131 
				);
 - 
 
132 
			}
 - 
 
133 
		}
 - 
 
134
- 
 
135 
		return $mots_cles_hierarchiques;
 - 
 
136 
	}
 - 
 
137
- 
 
138 
	private function obtenirLiaisonsMotsClesOriginaux($mode) {
 - 
 
139 
		if($mode == 'obs') {
- 
 
140 
			$requete_liaisons = "SELECT comc.id_observation as id_element_lie, comc.id_mot_cle_obs as id_mot_cle, id_utilisateur as id_utilisateur ".
 - 
 
141 
														"FROM cel_obs_mots_cles_srv comc ";
 - 
 
142 
														//"INNER JOIN cel_obs o ON o.id_observation = comc.id_observation ";
 - 
 
143 
		} else {
- 
 
144 
			$requete_liaisons = "SELECT comc.id_image as id_element_lie, comc.id_mot_cle_image as id_mot_cle, id_utilisateur as id_utilisateur ".
 - 
 
145 
																	"FROM cel_images_mots_cles_srv comc ";
 - 
 
146 
																	//"INNER JOIN cel_images o ON o.id_image = comc.id_image ";
 - 
 
147 
		}
 - 
 
148
- 
 
149 
		$liaisons = Cel::db()->requeter($requete_liaisons);
 - 
 
150 
		return $liaisons;
 - 
 
151 
	}
 - 
 
152
- 
 
153 
	public function convertirNestedSetVersPathEnum() {
 - 
 
154
- 
 
155 
		$mots_cles_hierarchiques = $this->obtenirMotsClesOriginaux($this->mode);
 - 
 
156 
		$liaisons = $this->obtenirLiaisonsMotsClesOriginaux($this->mode);
 - 
 
157
- 
 
158 
		/*foreach($mots_cles_hierarchiques as $mot) {
 - 
 
159 
			echo $mot['chemin'].'<br />';
 - 
 
160 
		}*/
 - 
 
161 
		//echo '<pre>'.print_r($liaisons,true).'</pre>';
 - 
 
162 
		//exit;
 - 
 
163
- 
 
164 
		$time_start = microtime();
 - 
 
165
- 
 
166 
		$mots_cles_slice = array_chunk($mots_cles_hierarchiques, 800, true);
 - 
 
167 
		foreach($mots_cles_slice as $mots_cles_tranche) {
 - 
 
168 
			$requete_insertion_mots_cles = "INSERT INTO ".$this->table_mots_cles." (chemin, id_utilisateur, tag) ".
 - 
 
169 
			                     			" VALUES ";
 - 
 
170
- 
 
171 
			foreach($mots_cles_tranche as $cle => $mot_cle_tranche) {
 - 
 
172 
				$requete_insertion_mots_cles .= "(".
 - 
 
173 
					cel::db()->proteger($mot_cle_tranche['chemin']).", ".
 - 
 
174 
					cel::db()->proteger($mot_cle_tranche['id_utilisateur']).", ".
 - 
 
175 
					cel::db()->proteger($mot_cle_tranche['tag'])." ".
- 
 
176 
				"), ";
 - 
 
177 
			}
 - 
 
178
- 
 
179 
			$requete_insertion_mots_cles = rtrim($requete_insertion_mots_cles, ", ");
 - 
 
180 
			cel::db()->executer($requete_insertion_mots_cles);
 - 
 
181 
		}
 - 
 
182
- 
 
183 
		$gne = 0;
 - 
 
184 
		$liaisons_slice = array_chunk($liaisons, 800, true);
 - 
 
185 
		foreach($liaisons_slice as &$liaison_tranche) {
 - 
 
186 
			$requete_insertion_liaisons = "INSERT INTO ".$this->table_liaison." (".$this->champ_id_element_lie.", chemin, tag, id_utilisateur) ".
 - 
 
187 
									      " VALUES ";
 - 
 
188 
			$lier = false;
 - 
 
189
- 
 
190 
			foreach($liaison_tranche as &$liaison_obs_mot_cle) {
 - 
 
191 
				$cle = $liaison_obs_mot_cle['id_mot_cle'].'-'.$liaison_obs_mot_cle['id_utilisateur'];
 - 
 
192 
				if(isset($mots_cles_hierarchiques[$cle])) {
 - 
 
193 
					$requete_insertion_liaisons .= "(".
 - 
 
194 
						cel::db()->proteger($liaison_obs_mot_cle['id_element_lie']).", ".
 - 
 
195 
						cel::db()->proteger($mots_cles_hierarchiques[$cle]['chemin']).", ".
 - 
 
196 
						cel::db()->proteger($mots_cles_hierarchiques[$cle]['tag']).", ".
 - 
 
197 
						cel::db()->proteger($liaison_obs_mot_cle['id_utilisateur'])." ".
 - 
 
198 
					"), ";
 - 
 
199 
					$lier = true;
 - 
 
200 
				}
 - 
 
201 
			}
 - 
 
202
- 
 
203 
			if($lier) {
 - 
 
204 
				$requete_insertion_liaisons = rtrim($requete_insertion_liaisons, ", ");
 - 
 
205 
				cel::db()->executer($requete_insertion_liaisons);
 - 
 
206 
			}
 - 
 
207 
		}
 - 
 
208
- 
 
209 
		$time = microtime() - $time_start;
 - 
 
210 
		echo "La génération d'un tableau de ".count($mots_cles_hierarchiques).' éléments et de '.count($liaisons).' liaisons a pris '.$time.' secondes';
 - 
 
211 
		exit;
 83 
		return $images;
Line 212... Line 84...
212 
	}
 84 
	}
213
85
214 
	public function obtenirArbre($id_utilisateur, $chemin = "/") {
 86 
	public function obtenirArbre($id_utilisateur, $chemin = "/") {
Line 226... Line 98...
226
98
Line 227... Line 99...
227 
	public function insererParChemin($mot_cle, $chemin_parent, $id_utilisateur) {
 99 
	public function insererParChemin($mot_cle, $chemin_parent, $id_utilisateur) {
Line 228... Line 100...
228
100
229 
		$chemin_mot_cle = self::getCheminHarmonise($chemin_parent, $mot_cle);
 101 
		$chemin_mot_cle = self::getCheminHarmonise($chemin_parent, $mot_cle);
230
102
231 
		$requete = "INSERT INTO ".$this->table_mots_cles." (chemin, id_utilisateur, tag) ".
 103 
		$requete = "INSERT IGNORE INTO ".$this->table_mots_cles." (chemin, id_utilisateur, tag) ".
232 
		           "VALUES (".
 104 
		           "VALUES (".
233 
		           		Cel::db()->proteger($chemin_mot_cle).", ".
 105 
		           		Cel::db()->proteger($chemin_mot_cle).", ".
234 
						Cel::db()->proteger($id_utilisateur).", ".
 106 
						Cel::db()->proteger($id_utilisateur).", ".
235 
						Cel::db()->proteger($mot_cle)." ".
 107 
						Cel::db()->proteger($mot_cle)." ".
Line 236... Line 108...
236 
		           ")";
 108 
		           ")";
237
109 
 
Line 238... Line 110...
238 
		$insertion = Cel::db()->executer($requete.' -- '.__FILE__.':'.__LINE__);
 110 
		$insertion = Cel::db()->executer($requete.' -- '.__FILE__.':'.__LINE__);
Line 239... Line 111...
239
111
Line 240... Line 112...
240 
		return $insertion;
 112 
		return $insertion;
241 
	}
 113 
	}
Line 242... Line 114...
242
114
243 
	public function insererParIdParent($mot_cle, $id_parent, $id_utilisateur) {
 115 
	public function insererParIdParent($mot_cle, $id_parent, $id_utilisateur) {
244
116
245 
		$mot_cle_simp = self::simplifier($mot_cle);
 117 
		$mot_cle_simp = self::simplifier($mot_cle);
246
118
247 
		$sous_requete_chemin = "(SELECT chemin FROM ".$this->table_mots_cles." ".
 119 
		$sous_requete_chemin = "(SELECT chemin FROM ".$this->table_mots_cles." ctp ".
Line 248... Line 120...
248 
		                       "WHERE id_tag = ".Cel::db()->proteger($id_parent).")";
 120 
		                       "WHERE ctp.id_tag = ".Cel::db()->proteger($id_parent).")";
Line 249... Line 121...
249
121
250 
		$requete = "INSERT INTO ".$this->table_mots_cles."(chemin, id_utilisateur, tag) ".
 122 
		$requete = "INSERT INTO ".$this->table_mots_cles."(chemin, id_utilisateur, tag) ".
Line 251... Line 123...
251 
		           "VALUES (".
 123 
		           "VALUES (".
252 
						"CONCAT(".$sous_requete_chemin.",".Cel::db()->proteger($mot_cle_simp).",'/') ".
 124 
						"CONCAT(".$sous_requete_chemin.",".Cel::db()->proteger($mot_cle_simp).",'/'), ".
253 
						Cel::db()->proteger($id_utilisateur).", ".
 - 
 
254 
						Cel::db()->proteger($tag).", ".
 - 
 
255 
		           ")";
 - 
 
256
125 
						Cel::db()->proteger($id_utilisateur).", ".
257 
		$insertion = Cel::db()->executer($requete.' -- '.__FILE__.':'.__LINE__);
 126 
						Cel::db()->proteger($mot_cle)." ".
258
127 
		           ")";
259 
		return $insertion;
 - 
 
260 
	}
 128
261
129 
		$insertion = Cel::db()->executer($requete.' -- '.__FILE__.':'.__LINE__);
262 
	public function lierParId($id_mot_cle, $id_observation, $id_utilisateur) {
 130
Line 263... Line 131...
263
131 
		return $insertion;
Line 264... Line 132...
264 
		$sous_requete_chemin = "(SELECT chemin FROM ".$this->table_mots_cles." ".
 132 
	}
265 
				   		"WHERE id_tag = ".Cel::db()->proteger($id_mot_cle).") ";
 133
Line 266... Line 134...
266
134 
	public function lierParId($id_mot_cle, $id_element_lie) {
- 
 
 135
- 
 
 136 
		$requete = "INSERT INTO ".$this->table_liaison." (id_element_lie, id_tag) ".
- 
 
 137 
					"VALUES (".
- 
 
 138 
				   		Cel::db()->proteger($id_element_lie).", ".
- 
 
 139 
				   		Cel::db()->proteger($id_mot_cle)." ".
267 
		$requete = "INSERT INTO ".$this->table_liaison." (".$this->champ_id_element_lie.", chemin, id_utilisateur) ".
 140 
				   	") ".
268 
					"VALUES (".
 141 
					"ON DUPLICATE KEY UPDATE id_element_lie = id_element_lie ";
269 
				   		Cel::db()->proteger($id_observation).", ".
 142
270 
				   		$sous_requete_chemin.",".
 143 
		$liaison = Cel::db()->executer($requete.' -- '.__FILE__.':'.__LINE__);
271 
				   		Cel::db()->proteger($id_utilisateur)." ".
 144
272 
				   	") ".
 - 
 
273 
					"ON DUPLICATE KEY UPDATE ".$this->champ_id_element_lie." = ".$this->champ_id_element_lie." ";
 145 
		return $liaison;
274
146 
	}
Line 275... Line 147...
275 
		$liaison = Cel::db()->executer($requete.' -- '.__FILE__.':'.__LINE__);
 147
Line 276... Line 148...
276
148 
	public function lierParChemin($chemin, $id_element_lie, $id_utilisateur) {
277 
		return $liaison;
 149 
 
Line -... Line 150...
- 
 
 150 
		$sous_requete_chemin = '(SELECT id_tag FROM '.$this->table_mots_cles.' '.
- 
 
 151 
								'WHERE chemin = '.Cel::db()->proteger(self::harmoniserChemin($chemin)).' '.
278 
	}
 152 
								'AND id_utilisateur = '.Cel::db()->proteger($id_utilisateur).' '.
279
153 
								')';
280 
	public function lierParChemin($chemin, $id_observation, $id_utilisateur) {
 154
Line 281... Line 155...
281
155 
		$requete = "INSERT INTO ".$this->table_liaison." (id_element_lie, id_tag) ".
Line 305... Line 179...
305 
		// TODO : triggers pour les tables liées ?
 179 
		// TODO : triggers pour les tables liées ?
306 
		$requete = "UPDATE ".$this->table_mots_cles." ".
 180 
		$requete = "UPDATE ".$this->table_mots_cles." ".
307 
		       	   "SET chemin = REPLACE(chemin,".$ancien_chemin_p.", ".$nouveau_chemin_p.") ".
181 
		       	   "SET chemin = REPLACE(chemin,".$ancien_chemin_p.", ".$nouveau_chemin_p.") ".
308 
				   "WHERE chemin LIKE ".Cel::db()->proteger($ancien_chemin."%")." AND ".
 182 
				   "WHERE chemin LIKE ".Cel::db()->proteger($ancien_chemin."%")." AND ".
309 
		           "id_utilisateur = ".Cel::db()->proteger($id_utilisateur)." ";
183 
		           "id_utilisateur = ".Cel::db()->proteger($id_utilisateur)." ";
310 
		echo $requete.'<br />';
 - 
 
- 
 
 184
311 
		$deplacement = Cel::db()->executer($requete.' -- '.__FILE__.':'.__LINE__);
 185 
		$deplacement = Cel::db()->executer($requete.' -- '.__FILE__.':'.__LINE__);
Line 312... Line 186...
312
186
313 
		return $deplacement;
 187 
		return $deplacement;
Line 339... Line 213...
339 
		}
 213 
		}
Line 340... Line 214...
340
214
341 
		return $cmp;
 215 
		return $cmp;
Line 342... Line -...
342 
	}
 - 
 
343
- 
 
344 
	/*
 - 
 
345 
		CREATE TRIGGER maj_chemin BEFORE UPDATE
 - 
 
346 
		ON cel_obs_tag_path FOR EACH ROW
- 
 
347 
		BEGIN
 - 
 
348 
			UPDATE cel_obs_tag_path_liaison
 - 
 
349 
			SET cel_obs_tag_path_liaison.chemin = NEW.CHEMIN
 - 
 
350 
			WHERE cel_obs_tag_path_liaison.chemin LIKE CONCAT(OLD.chemin,'%')
 - 
 
351 
			AND cel_obs_tag_path_liaison.id_utilisateur = OLD.id_utilisateur
 - 
 
352 
		END;
 - 
 
353
- 
 
354 
		CREATE TRIGGER suppr_chemin BEFORE UPDATE
 - 
 
355 
		ON cel_obs_tag_path FOR EACH ROW
- 
 
356 
		BEGIN
 - 
 
357 
			DELETE FROM cel_obs_tag_path_liaison
 - 
 
358 
			WHERE cel_obs_tag_path_liaison.chemin LIKE CONCAT(OLD.chemin,'%')
 - 
 
359 
			AND cel_obs_tag_path_liaison.id_utilisateur = OLD.id_utilisateur
 - 
 
360 
		END;
 - 
 
361
- 
 
362 
		CREATE TRIGGER suppr_obs BEFORE DELETE
 - 
 
363 
		ON cel_obs FOR EACH ROW
- 
 
364 
		BEGIN
 - 
 
365 
			DELETE FROM cel_obs_tag_path_liaison
 - 
 
366 
			WHERE cel_obs_tag_path_liaison.id_obs = OLD.id_observation
 - 
 
367 
		END;
 - 
 
368 
	*/
 216 
	}
369
217
370 
	static public function getCheminHarmonise($chemin_parent, $mot_cle) {
 218 
	static public function getCheminHarmonise($chemin_parent, $mot_cle) {
Line 371... Line 219...
371 
		return self::harmoniserChemin($chemin_parent.'/'.self::simplifier($mot_cle).'/');
 219 
		return self::harmoniserChemin($chemin_parent.'/'.self::simplifier($mot_cle).'/');