Subversion Repositories eFlore/Applications.cel

Compare Revisions

Ignore whitespace Rev 2031 → Rev 2032

/trunk/jrest/lib/GestionMotsClesChemin.php
16,8 → 16,9
private $table_liaison;
private $table_mots_cles;
private $champ_id_element_lie;
//TODO: trigger pour les tables liaisons
public function GestionMotsClesChemin($config, $mode = 'obs') {
$this->config = $config;
//TODO: switch suivant mode
26,24 → 27,12
if($mode == 'obs') {
$this->table_liaison = 'cel_obs_tags_path_liaison';
$this->table_mots_cles = 'cel_obs_tags_path';
$this->champ_id_element_lie = 'id_obs';
} else {
$this->table_liaison = 'cel_images_tags_path_liaison';
$this->table_mots_cles = 'cel_images_tags_path';
$this->champ_id_element_lie = 'id_image';
}
}
public function viderTables() {
$requete = "TRUNCATE ".$this->table_mots_cles." ";
$vidage = Cel::db()->executer($requete.' -- '.__FILE__.':'.__LINE__);
$requete = "TRUNCATE ".$this->table_liaison." ";
$vidage = Cel::db()->executer($requete.' -- '.__FILE__.':'.__LINE__);
return $vidage;
}
public function construireRequeteOr($mots_cles) {
return $this->construireRequete($mots_cles, 'OR');
}
61,156 → 50,39
}
public function construireRequete($mots_cles, $operateur = 'AND') {
$requete = "SELECT * FROM cel_images_tags_path_liaison ";
$criteres = array();
$premier = array_pop($mots_cles);
$requete .= 'WHERE (tag = '.Cel::db()->proteger($premier).' ';
$requete = "SELECT * FROM ".$this->table_liaison." lmc ".
"INNER JOIN ".$this->table_mots_cles." mc ".
"ON mc.tag = ".Cel::db()->proteger($premier)." AND lmc.id_tag = mc.id_tag ";
foreach ($mots_cles as $mot_cle) {
$requete .= " ".$operateur." id_image IN (SELECT id_image ".
"FROM cel_images_tags_path_liaison ".
"WHERE tag = ".Cel::db()->proteger($mot_cle)." ".
") ";
$requete .= " ".$operateur." id_element_lie IN (SELECT id_element_lie FROM ".$this->table_liaison." lmc ".
"INNER JOIN ".$this->table_mots_cles." mc ".
"ON mc.tag = ".Cel::db()->proteger($mot_cle)." AND lmc.id_tag = mc.id_tag ".
") ";
}
$requete .= ")";
 
$images = Cel::db()->requeter($requete);
return $images;
}
private function construireRequeteChemin($mots_cles, $operateur = 'AND') {
$requete = "SELECT * FROM cel_images_tags_path_liaison ";
$criteres = array();
$criteres = array();
$premier = array_pop($mots_cles);
$requete .= 'WHERE (chemin LIKE '.Cel::db()->proteger('%/'.$premier.'/%').' ';
$requete = "SELECT * FROM ".$this->table_liaison." lmc ".
"INNER JOIN ".$this->table_mots_cles." mc ".
"ON mc.chemin LIKE ".Cel::db()->proteger('%'.$premier.'%')." AND lmc.id_tag = mc.id_tag ";
foreach ($mots_cles as $mot_cle) {
$requete .= " ".$operateur." id_image IN (SELECT id_image ".
"FROM cel_images_tags_path_liaison ".
"WHERE chemin LIKE ".Cel::db()->proteger('%/'.$mot_cle.'/%')." ".
") ";
$requete .= " ".$operateur." id_element_lie IN (SELECT id_element_lie FROM ".$this->table_liaison." lmc ".
"INNER JOIN ".$this->table_mots_cles." mc ".
"ON mc.chemin LIKE ".Cel::db()->proteger('%'.$mot_cle.'%')." AND lmc.id_tag = mc.id_tag ".
") ";
}
$requete .= ")";
$images = Cel::db()->requeter($requete);
return $images;
}
private function obtenirMotsClesOriginaux($mode) {
if($mode == 'obs') {
$requete_arbre = "SELECT id_mot_cle_obs as id_mot_cle, ce_mot_cle_obs_parent as parent, id_utilisateur, mot_cle ".
"FROM cel_mots_cles_obs_srv ORDER BY id_utilisateur, niveau";
} else {
$requete_arbre = "SELECT id_mot_cle_image as id_mot_cle, ce_mot_cle_image_parent as parent, id_utilisateur, mot_cle ".
"FROM cel_mots_cles_images_srv ORDER BY id_utilisateur, niveau";
}
$mots_cles = Cel::db()->requeter($requete_arbre);
$mots_cles_hierarchiques = array();
$correspondances_id_mots_cles_obs = array();
$rien = 0;
foreach($mots_cles as &$mot) {
if(!empty($mot['mot_cle'])) {
$chemin = '/';
$cle_parent = $mot['parent'].'-'.$mot['id_utilisateur'];
if(isset($mots_cles_hierarchiques[$cle_parent])) {
$chemin = $mots_cles_hierarchiques[$cle_parent]['chemin'];
}
$chemin .= self::simplifier($mot['mot_cle']).'/';
$chemin = str_replace("//", "/", $chemin);
$cle = $mot['id_mot_cle'].'-'.$mot['id_utilisateur'];
$mots_cles_hierarchiques[$cle] = array(
'id_utilisateur' => $mot['id_utilisateur'],
'chemin' => $chemin,
'tag' => $mot['mot_cle']
);
}
}
return $mots_cles_hierarchiques;
}
private function obtenirLiaisonsMotsClesOriginaux($mode) {
if($mode == 'obs') {
$requete_liaisons = "SELECT comc.id_observation as id_element_lie, comc.id_mot_cle_obs as id_mot_cle, id_utilisateur as id_utilisateur ".
"FROM cel_obs_mots_cles_srv comc ";
//"INNER JOIN cel_obs o ON o.id_observation = comc.id_observation ";
} else {
$requete_liaisons = "SELECT comc.id_image as id_element_lie, comc.id_mot_cle_image as id_mot_cle, id_utilisateur as id_utilisateur ".
"FROM cel_images_mots_cles_srv comc ";
//"INNER JOIN cel_images o ON o.id_image = comc.id_image ";
}
$liaisons = Cel::db()->requeter($requete_liaisons);
return $liaisons;
}
public function convertirNestedSetVersPathEnum() {
$mots_cles_hierarchiques = $this->obtenirMotsClesOriginaux($this->mode);
$liaisons = $this->obtenirLiaisonsMotsClesOriginaux($this->mode);
/*foreach($mots_cles_hierarchiques as $mot) {
echo $mot['chemin'].'<br />';
}*/
//echo '<pre>'.print_r($liaisons,true).'</pre>';
//exit;
$time_start = microtime();
$mots_cles_slice = array_chunk($mots_cles_hierarchiques, 800, true);
foreach($mots_cles_slice as $mots_cles_tranche) {
$requete_insertion_mots_cles = "INSERT INTO ".$this->table_mots_cles." (chemin, id_utilisateur, tag) ".
" VALUES ";
foreach($mots_cles_tranche as $cle => $mot_cle_tranche) {
$requete_insertion_mots_cles .= "(".
cel::db()->proteger($mot_cle_tranche['chemin']).", ".
cel::db()->proteger($mot_cle_tranche['id_utilisateur']).", ".
cel::db()->proteger($mot_cle_tranche['tag'])." ".
"), ";
}
$requete_insertion_mots_cles = rtrim($requete_insertion_mots_cles, ", ");
cel::db()->executer($requete_insertion_mots_cles);
}
$gne = 0;
$liaisons_slice = array_chunk($liaisons, 800, true);
foreach($liaisons_slice as &$liaison_tranche) {
$requete_insertion_liaisons = "INSERT INTO ".$this->table_liaison." (".$this->champ_id_element_lie.", chemin, tag, id_utilisateur) ".
" VALUES ";
$lier = false;
foreach($liaison_tranche as &$liaison_obs_mot_cle) {
$cle = $liaison_obs_mot_cle['id_mot_cle'].'-'.$liaison_obs_mot_cle['id_utilisateur'];
if(isset($mots_cles_hierarchiques[$cle])) {
$requete_insertion_liaisons .= "(".
cel::db()->proteger($liaison_obs_mot_cle['id_element_lie']).", ".
cel::db()->proteger($mots_cles_hierarchiques[$cle]['chemin']).", ".
cel::db()->proteger($mots_cles_hierarchiques[$cle]['tag']).", ".
cel::db()->proteger($liaison_obs_mot_cle['id_utilisateur'])." ".
"), ";
$lier = true;
}
}
if($lier) {
$requete_insertion_liaisons = rtrim($requete_insertion_liaisons, ", ");
cel::db()->executer($requete_insertion_liaisons);
}
}
$time = microtime() - $time_start;
echo "La génération d'un tableau de ".count($mots_cles_hierarchiques).' éléments et de '.count($liaisons).' liaisons a pris '.$time.' secondes';
exit;
}
public function obtenirArbre($id_utilisateur, $chemin = "/") {
 
$requete = "SELECT * FROM ".$this->table_mots_cles." ".
228,13 → 100,13
$chemin_mot_cle = self::getCheminHarmonise($chemin_parent, $mot_cle);
$requete = "INSERT INTO ".$this->table_mots_cles." (chemin, id_utilisateur, tag) ".
$requete = "INSERT IGNORE INTO ".$this->table_mots_cles." (chemin, id_utilisateur, tag) ".
"VALUES (".
Cel::db()->proteger($chemin_mot_cle).", ".
Cel::db()->proteger($id_utilisateur).", ".
Cel::db()->proteger($mot_cle)." ".
")";
 
$insertion = Cel::db()->executer($requete.' -- '.__FILE__.':'.__LINE__);
return $insertion;
244,14 → 116,14
$mot_cle_simp = self::simplifier($mot_cle);
$sous_requete_chemin = "(SELECT chemin FROM ".$this->table_mots_cles." ".
"WHERE id_tag = ".Cel::db()->proteger($id_parent).")";
$sous_requete_chemin = "(SELECT chemin FROM ".$this->table_mots_cles." ctp ".
"WHERE ctp.id_tag = ".Cel::db()->proteger($id_parent).")";
$requete = "INSERT INTO ".$this->table_mots_cles."(chemin, id_utilisateur, tag) ".
"VALUES (".
"CONCAT(".$sous_requete_chemin.",".Cel::db()->proteger($mot_cle_simp).",'/') ".
"CONCAT(".$sous_requete_chemin.",".Cel::db()->proteger($mot_cle_simp).",'/'), ".
Cel::db()->proteger($id_utilisateur).", ".
Cel::db()->proteger($tag).", ".
Cel::db()->proteger($mot_cle)." ".
")";
$insertion = Cel::db()->executer($requete.' -- '.__FILE__.':'.__LINE__);
259,18 → 131,14
return $insertion;
}
public function lierParId($id_mot_cle, $id_observation, $id_utilisateur) {
$sous_requete_chemin = "(SELECT chemin FROM ".$this->table_mots_cles." ".
"WHERE id_tag = ".Cel::db()->proteger($id_mot_cle).") ";
$requete = "INSERT INTO ".$this->table_liaison." (".$this->champ_id_element_lie.", chemin, id_utilisateur) ".
public function lierParId($id_mot_cle, $id_element_lie) {
$requete = "INSERT INTO ".$this->table_liaison." (id_element_lie, id_tag) ".
"VALUES (".
Cel::db()->proteger($id_observation).", ".
$sous_requete_chemin.",".
Cel::db()->proteger($id_utilisateur)." ".
Cel::db()->proteger($id_element_lie).", ".
Cel::db()->proteger($id_mot_cle)." ".
") ".
"ON DUPLICATE KEY UPDATE ".$this->champ_id_element_lie." = ".$this->champ_id_element_lie." ";
"ON DUPLICATE KEY UPDATE id_element_lie = id_element_lie ";
$liaison = Cel::db()->executer($requete.' -- '.__FILE__.':'.__LINE__);
277,15 → 145,19
return $liaison;
}
public function lierParChemin($chemin, $id_observation, $id_utilisateur) {
$requete = "INSERT INTO ".$this->table_liaison." (id_obs, chemin, id_utilisateur) ".
public function lierParChemin($chemin, $id_element_lie, $id_utilisateur) {
 
$sous_requete_chemin = '(SELECT id_tag FROM '.$this->table_mots_cles.' '.
'WHERE chemin = '.Cel::db()->proteger(self::harmoniserChemin($chemin)).' '.
'AND id_utilisateur = '.Cel::db()->proteger($id_utilisateur).' '.
')';
$requete = "INSERT INTO ".$this->table_liaison." (id_element_lie, id_tag) ".
"VALUES (".
Cel::db()->proteger($id_observation).", ".
Cel::db()->proteger($sous_requete_chemin).", ".
Cel::db()->proteger($id_utilisateur)." ".
Cel::db()->proteger($id_element_lie).", ".
$sous_requete_chemin." ".
") ".
"ON DUPLICATE KEY UPDATE ".$this->champ_id_element_lie." = ".$this->champ_id_element_lie." ";
"ON DUPLICATE KEY UPDATE id_element_lie = id_element_lie ";
$liaison = Cel::db()->executer($requete.' -- '.__FILE__.':'.__LINE__);
292,6 → 164,8
return $liaison;
}
// OK jusqu'ici !
// Déplacer un mot clé et le renommer est une même opération, dans le cas du renommage,
// seule la fin du chemin change
public function renommerChemin($ancien_chemin, $nouveau_chemin, $id_utilisateur) {
307,7 → 181,7
"SET chemin = REPLACE(chemin,".$ancien_chemin_p.", ".$nouveau_chemin_p.") ".
"WHERE chemin LIKE ".Cel::db()->proteger($ancien_chemin."%")." AND ".
"id_utilisateur = ".Cel::db()->proteger($id_utilisateur)." ";
echo $requete.'<br />';
$deplacement = Cel::db()->executer($requete.' -- '.__FILE__.':'.__LINE__);
return $deplacement;
341,32 → 215,6
return $cmp;
}
/*
CREATE TRIGGER maj_chemin BEFORE UPDATE
ON cel_obs_tag_path FOR EACH ROW
BEGIN
UPDATE cel_obs_tag_path_liaison
SET cel_obs_tag_path_liaison.chemin = NEW.CHEMIN
WHERE cel_obs_tag_path_liaison.chemin LIKE CONCAT(OLD.chemin,'%')
AND cel_obs_tag_path_liaison.id_utilisateur = OLD.id_utilisateur
END;
CREATE TRIGGER suppr_chemin BEFORE UPDATE
ON cel_obs_tag_path FOR EACH ROW
BEGIN
DELETE FROM cel_obs_tag_path_liaison
WHERE cel_obs_tag_path_liaison.chemin LIKE CONCAT(OLD.chemin,'%')
AND cel_obs_tag_path_liaison.id_utilisateur = OLD.id_utilisateur
END;
CREATE TRIGGER suppr_obs BEFORE DELETE
ON cel_obs FOR EACH ROW
BEGIN
DELETE FROM cel_obs_tag_path_liaison
WHERE cel_obs_tag_path_liaison.id_obs = OLD.id_observation
END;
*/
static public function getCheminHarmonise($chemin_parent, $mot_cle) {
return self::harmoniserChemin($chemin_parent.'/'.self::simplifier($mot_cle).'/');
}