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).'/'); |
} |