17,35 → 17,127 |
private $table_liaison; |
private $table_mots_cles; |
|
public function GestionMotsClesChemin($config, $mode) { |
public function GestionMotsClesChemin($config, $mode = 'obs') { |
$this->config = $config; |
//TODO: switch suivant mode |
$this->table_liaison = 'cel_obs_tag_path_liaison'; |
$this->table_mots_cles = 'cel_obs_tag_path'; |
$this->table_liaison = 'cel_obs_tags_path_liaison'; |
$this->table_mots_cles = 'cel_obs_tags_path'; |
} |
|
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 convertirNestedSetVersPathEnum() { |
|
$time_start = microtime(); |
|
$requete_arbre = "SELECT * FROM cel_mots_cles_obs_srv ORDER BY id_utilisateur, niveau"; |
$mots_cles = Cel::db()->requeter($requete_arbre); |
|
$requete_liaisons = "SELECT comc.id_observation, comc.id_mot_cle_obs, o.ce_utilisateur as id_utilisateur ". |
"FROM cel_obs_mots_cles comc ". |
"INNER JOIN cel_obs o ON o.id_observation = comc.id_observation "; |
|
$liaisons = Cel::db()->requeter($requete_liaisons); |
|
$mots_cles_hierarchiques = array(); |
$correspondances_id_mots_cles_obs = array(); |
|
foreach($mots_cles as &$mot) { |
|
$chemin = '/'; |
if(isset($mots_cles_hierarchiques[$mot['ce_mot_cle_obs_parent']])) { |
$chemin = $mots_cles_hierarchiques[$mot['ce_mot_cle_obs_parent']]['chemin']; |
} |
$chemin .= self::simplifier($mot['mot_cle']).'/'; |
$chemin = str_replace("//", "", $chemin); |
|
$cle = $mot['id_mot_cle_obs'].'-'.$mot['id_utilisateur']; |
|
$mots_cles_hierarchiques[$cle] = array( |
'id_utilisateur' => $mot['id_utilisateur'], |
'chemin' => $chemin, |
'tag' => $mot['mot_cle'] |
); |
} |
|
$time_end = microtime(); |
$time = $time_end - $time_start; |
|
$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); |
} |
|
$liaisons_slice = array_chunk($liaisons, 800, true); |
foreach($liaisons_slice as &$liaison_tranche) { |
$requete_insertion_liaisons = "INSERT INTO ".$this->table_liaison." (id_obs, chemin, id_utilisateur) ". |
" VALUES "; |
$lier = false; |
|
foreach($liaison_tranche as &$liaison_obs_mot_cle) { |
$cle = $liaison_obs_mot_cle['id_mot_cle_obs'].'-'.$liaison_obs_mot_cle['id_utilisateur']; |
if(isset($mots_cles_hierarchiques[$cle])) { |
$requete_insertion_liaisons .= "(". |
cel::db()->proteger($liaison_obs_mot_cle['id_observation']).", ". |
cel::db()->proteger($mots_cles_hierarchiques[$cle]['chemin']).", ". |
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); |
} |
} |
|
echo "La génération d'un tableau de ".count($mots_cles_hierarchiques).' éléments a pris '.$time.' secondes'; |
exit; |
} |
|
public function obtenirArbre($id_utilisateur, $chemin = "/") { |
|
$requete = "SELECT * FROM ".$this->table_mots_cles." ". |
"WHERE id_utilisateur = ".Cel::db()->proteger($id_utilisateur)." AND ". |
"chemin LIKE = ".Cel::db()->proteger($chemin."%")." ". |
"ORDER BY chemin" |
"chemin LIKE ".Cel::db()->proteger($chemin."%")." ". |
"ORDER BY chemin"; |
|
$arbre = Cel::db()->executer($requete.' -- '.__FILE__.':'.__LINE__); |
usort($arbre, array('GestionMotsClesChemin', 'comparerProfNoeuds'); |
$arbre = Cel::db()->requeter($requete.' -- '.__FILE__.':'.__LINE__); |
usort($arbre, array('GestionMotsClesChemin', 'comparerProfNoeuds')); |
|
return $arbre; |
} |
|
public function insererParChemin($mot_cle, $chemin, $id_utilisateur) { |
public function insererParChemin($mot_cle, $chemin_parent, $id_utilisateur) { |
|
$chemin_mot_cle = rtrim($chemin, '/').'/'.self::simplifier($mot_cle); |
$chemin_mot_cle = self::getCheminHarmonise($chemin_parent, $mot_cle); |
|
$requete = "INSERT INTO ".$this->table_mots_cles." (chemin, id_utilisateur, tag) ". |
"VALUES (". |
Cel::db()->proteger($chemin_mot_cle).", ". |
Cel::db()->proteger($id_utilisateur).", ". |
Cel::db()->proteger($tag).", ". |
Cel::db()->proteger($mot_cle)." ". |
")"; |
|
$insertion = Cel::db()->executer($requete.' -- '.__FILE__.':'.__LINE__); |
58,11 → 150,11 |
$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).")"; |
"WHERE 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).", ". |
")"; |
80,7 → 172,7 |
$requete = "INSERT INTO ".$this->table_liaison." (id_obs, chemin, id_utilisateur) ". |
"VALUES (". |
Cel::db()->proteger($id_observation).", ". |
$sous_requete_chemin."," |
$sous_requete_chemin.",". |
Cel::db()->proteger($id_utilisateur)." ". |
") ". |
"ON DUPLICATE KEY UPDATE id_obs = id_obs; "; |
108,12 → 200,22 |
// 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) { |
|
$ancien_chemin = self::harmoniserChemin($ancien_chemin); |
$nouveau_chemin = self::harmoniserChemin($nouveau_chemin); |
|
$ancien_chemin_p = Cel::db()->proteger($ancien_chemin); |
$nouveau_chemin_p = Cel::db()->proteger($nouveau_chemin); |
|
// TODO : triggers pour les tables liées ? |
$requete = "UPDATE ".$this->$table_mots_cles." ". |
// "SET tag = " |
$requete = "UPDATE ".$this->table_mots_cles." ". |
"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; |
} |
|
public function supprimerChemin($chemin, $id_utilisateur) { |
170,10 → 272,21 |
END; |
*/ |
|
static public function getCheminHarmonise($chemin_parent, $mot_cle) { |
return self::harmoniserChemin($chemin_parent.'/'.self::simplifier($mot_cle).'/'); |
} |
|
static public function harmoniserChemin($chemin) { |
$chemin = self::startsWith($chemin,'/') ? $chemin : '/'.$chemin; |
$chemin = self::endsWith($chemin,'/') ? $chemin : $chemin.'/'; |
return str_replace('//', '/', $chemin); |
} |
|
// fonction de slugification du mot clé |
// http://stackoverflow.com/questions/2955251/php-function-to-make-slug-url-string |
static public function simplifier($text) |
{ |
return trim(str_replace(array('\\','/'), '', $text)); |
// replace non letter or digits by - |
$text = preg_replace('~[^\\pL\d]+~u', '-', $text); |
|
196,6 → 309,14 |
|
return $text; |
} |
|
static public function startsWith($haystack, $needle) { |
return $needle === "" || strpos($haystack, $needle) === 0; |
} |
|
static public function endsWith($haystack, $needle) { |
return $needle === "" || substr($haystack, -strlen($needle)) === $needle; |
} |
} |
|
?> |