NodeTestTool.php (20463B)
1 <?php 2 // Ctrl + F : TODO to find where to start working... 3 /** 4 * 5 * TODO: analysis of relationships of relations : 6 * - which are sometimes together ? 7 * - which are never together ? 8 * - why ? 9 * - are there other interesting combinations possible ? 10 * - are some always included in others ? ex. "synonym" and "related to" 11 * - 12 * 13 * IDEAS: 14 * 15 * - function or class that gives more info on clourds : 16 * ->make sure the same word dosn't appear twice 17 * ->make sure the main word does't appear in cloud 18 * ->filter to make all other words nouns, adg, etc. 19 * ->word count for percentage of words possible (if few words, small cloud ; if lots 20 * of words, big cloud 21 * -> 22 */ 23 require_once("db.php"); 24 25 class NodeTestTool { 26 27 //private static $typer1r2 = "type in ($r1, $r2)"; 28 //private static $banned_types = "4, 12, 36, 18, 29, 45, 46, 47, 48, 1000, 1001"; 29 30 private static $relations = array(0 => "r_associated", 31 5 => "r_syn", 6 => "r_isa", 7 => "r_anto", 8 => "r_hypo", 9 => "r_haspart", 32 10 => "r_holo", 13 => "r_agent", 15 => "r_lieu", 16 => "r_inst", 17 => "r_carac"); 33 34 /***********************GENERAL METHODS***********************************/ 35 36 public static function getWordFromEID($eid) { 37 $db = getDB(); 38 $query = "SELECT name FROM node WHERE eid = $eid"; 39 $result = $db->querySingle($query, true); 40 if($result != null) return $result['name']; 41 else return null; 42 } 43 44 public static function getEIDFromWord($word) { 45 $db = getDB(); 46 $query = "SELECT eid FROM node WHERE name = '$word'"; 47 $result = $db->querySingle($query, true); 48 if($result != null) return $result['eid']; 49 else return null; 50 } 51 52 public static function isEIDInDB($eid) { 53 $db = getDB(); 54 $query = "SELECT eid FROM node WHERE eid = $eid"; 55 $result = $db->querySingle($query, true); 56 if ($result != null) 57 return true; 58 return false; 59 } 60 61 public static function isWordInDB($word) { 62 $db = getDB(); 63 $query = "SELECT eid FROM node WHERE name = '$word'"; 64 $result = $db->querySingle($query, true); 65 if ($result != null) 66 return true; 67 return false; 68 } 69 70 public static function isCentralEIDinDB($centralEID) { 71 $db = getDB(); 72 $query = "SELECT eid FROM random_center_node WHERE eid = $centralEID"; 73 $result = $db->querySingle($query, true); 74 if ($result != null) 75 return true; 76 return false; 77 } 78 79 public static function isCentralWordInDB($word) { 80 $centralEID = NodeTestTool::getEIDFromWord($word); 81 return NodeTestTool::isCentralEIDinDB($centralEID); 82 } 83 84 public static function isCloudEIDInDB($cloudEID) { 85 $db = getDB(); 86 $query = "SELECT eid FROM random_cloud_node WHERE eid = $cloudEID"; 87 $result = $db->querySingle($query, true); 88 if ($result != null) 89 return true; 90 return false; 91 } 92 93 public static function isCloudWordInDB($cloudWord) { 94 $db = getDB(); 95 $cloudEID = NodeTestTool::getEIDFromWord($cloudWord); 96 return NodeTestTool::isCloudEIDinDB($cloudEID); 97 } 98 99 100 public static function getPOSsFromEID($eid) { 101 $db = getDB(); 102 $query = "SELECT end FROM relation WHERE type = 4 AND start = $eid;"; 103 $res = $db->query($query); 104 105 $adj = $adv = $nom = $ver = false; 106 $s = ""; 107 108 while ($tuple = $res->fetchArray()) { 109 $endEID = $tuple['end']; 110 $query = "SELECT name FROM node WHERE eid = $endEID"; 111 $res2 = $db->querySingle($query, true); 112 $POSline = $res2['name']; 113 114 if (preg_match("/^Adj:/", $POSline)) { 115 if ($adj == false) { 116 $s .= "Adj "; 117 $adj = true; 118 } 119 } else if (preg_match("/^Adv:/", $POSline)) { 120 if ($adv == false) { 121 $s .= "Adv "; 122 $adv = true; 123 } 124 } else if (preg_match("/^Nom:/", $POSline)) { 125 if ($nom == false) { 126 $s .= "Nom "; 127 $nom = true; 128 } 129 } else if (preg_match("/^Ver:/", $POSline)) { 130 if ($ver == false) { 131 $s .= "Ver "; 132 $ver = true; 133 } 134 } 135 } 136 return $s; 137 } 138 139 public static function getPOSsFromWord($word) { 140 $eid = NodeTestTool::getEIDFromWord($word); 141 $POSs = NodeTestTool::getPOSsFromEID($eid); 142 return $POSs; 143 } 144 145 146 public static function getCloudEIDsFromCentralEIDAndRelNo($centralEID, $relNo) { 147 if (NodeTestTool::isCentralEIDinDB($centralEID)) { 148 $db = getDB(); 149 $query = "SELECT end FROM relation WHERE type = $relNo AND start = $centralEID"; 150 $res = $db->query($query); 151 152 $s = ""; 153 154 while ($tuple = $res->fetchArray()) { 155 $eid2 = $tuple['end']; 156 $query = "SELECT eid FROM node WHERE eid = $eid2"; 157 $res2 = $db->querySingle($query, true); 158 $s .= $res2['eid'] . ":"; 159 } 160 if(strlen($s) > 0) $s = substr($s, 0, -1); 161 return $s; 162 } 163 return null; 164 } 165 166 public static function getCloudWordsFromCentralEIDAndRelNo($centralEID, $relNo) { 167 if (NodeTestTool::isCentralEIDInDB($centralEID)) { 168 $db = getDB(); 169 $query = "SELECT end FROM relation WHERE type = $relNo AND start = $centralEID"; 170 $res = $db->query($query); 171 172 $s = ""; 173 174 while ($tuple = $res->fetchArray()) { 175 $eid2 = $tuple['end']; 176 $query = "SELECT name FROM node WHERE eid = $eid2"; 177 $res2 = $db->querySingle($query, true); 178 $s .= $res2['name'] . ":"; 179 } 180 if(strlen($s) > 0) $s = substr($s, 0, -1); 181 return $s; 182 } 183 return null; 184 } 185 186 public static function getCloudEIDsFromCentralWordAndRelNo($centralWord, $relNo) { 187 $centralEID = NodeTestTool::getEIDFromWord($centralWord); 188 if (NodeTestTool::isCentralEIDInDB($centralEID)) { 189 return NodeTestTool::getCloudEIDsFromCentralEIDandRelNo($centralEID, $relNo); 190 } 191 return false; 192 } 193 194 public static function getCloudWordsFromCentralWordAndRelNo($centralWord, $relNo) { 195 if (NodeTestTool::isCentralWordInDB($centralWord)) { 196 $centralEID = NodeTestTool::getEIDFromWord($centralWord); 197 return NodeTestTool::getCloudWordsFromCentralEIDAndRelNo($centralEID, $relNo); 198 } 199 return false; 200 } 201 202 public static function getAllCloudEIDsFromCentralEID($centralEID) { 203 $s = ""; 204 foreach (NodeTestTool::$relations AS $relNo => $relName) { 205 $cloud = NodeTestTool::getCloudEIDsFromCentralEIDAndRelNo($centralEID, $relNo); 206 $s .= $relNo . "=>" . $relName . ": " . $cloud . "<br />"; 207 } 208 return $s; 209 } 210 211 public static function getAllCloudWordsFromCentralEID($centralEID) { 212 $s = ""; 213 foreach (NodeTestTool::$relations AS $relNo => $relName) { 214 $cloud = NodeTestTool::getCloudWordsFromCentralEIDAndRelNo($centralEID, $relNo); 215 $s .= $relNo . "=>" . $relName . ": " . $cloud . "<br />"; 216 } 217 return $s; 218 } 219 220 public static function getAllCloudEIDsFromCentralWord($centralWord) { 221 $s = ""; 222 foreach (NodeTestTool::$relations AS $relNo => $relName) { 223 $cloud = NodeTestTool::getCloudEIDsFromCentralWordAndRelNo($centralWord, $relNo); 224 $s .= $relNo . "=>" . $relName . ": " . $cloud . "<br />"; 225 } 226 return $s; 227 } 228 229 public static function getAllCloudWordsFromCentralWord($centralWord) { 230 $s = ""; 231 foreach (NodeTestTool::$relations AS $relNo => $relName) { 232 $cloud = NodeTestTool::getCloudWordsFromCentralWordAndRelNo($centralWord, $relNo); 233 $s .= $relNo . "=>" . $relName . ": " . $cloud . "<br />"; 234 } 235 return $s; 236 } 237 238 239 /*****************************RANDOM METHODS**************************/ 240 241 public static function getRandomCentralEID() { 242 $db = getDB(); 243 $query = "SELECT eid FROM random_center_node WHERE rowid = 244 (ABS(RANDOM()) % (SELECT MAX(rowid) FROM random_center_node)) + 1"; 245 $res = $db->querySingle($query, true); 246 if ($res != null) 247 return $res['eid']; 248 else 249 return null; 250 } 251 252 public static function getRandomCentralWord() { 253 $eid = NodeTestTool::getRandomCentralEID(); 254 return NodeTestTool::getWordFromEID($eid); 255 } 256 257 public static function getAbsoluteRandomCloudEID() { 258 $db = getDB(); 259 $query = "SELECT eid FROM random_cloud_node WHERE rowid = 260 (ABS(RANDOM()) % (SELECT MAX(rowid) FROM random_center_node)) + 1"; 261 $res = $db->querySingle($query, true); 262 if ($res != null) 263 return $res['eid']; 264 else 265 return null; 266 } 267 268 public static function getAbsoluteRandomCloudWord() { 269 $eid = NodeTestTool::getAbsoluteRandomCloudEID(); 270 return NodeTestTool::getWordFromEID($eid); 271 } 272 273 public static function getRandomCloudEIDFromCentralEID($centralEID, $relNo){ 274 $s = NodeTestTool::getCloudEIDsFromCentralEIDAndRelNo($centralEID, $relNo); 275 $ar = preg_split("/:/", $s); 276 if(count($ar) > 0) return $ar[rand(0, count($ar)-1)]; 277 return null; 278 } 279 280 public static function getRandomCloudWordFromCentralEID($centralEID, $relNo){ 281 $eid = NodeTestTool::getRandomCloudEIDFromCentralEID($centralEID, $relNo); 282 if($eid != null) return NodeTestTool::getWordFromEID($eid); 283 return null; 284 } 285 286 public static function getRandomCloudEIDFromCentralWord($centralWord, $relNo){ 287 $centralEID = NodeTestTool::getEIDFromWord($centralWord); 288 if($centralEID != null) return NodeTestTool::getRandomCloudEIDFromCentralEID($centralEID, $relNo); 289 return null; 290 } 291 292 public static function getRandomCloudWordFromCentralWord($centralWord, $relNo){ 293 $centralEID = NodeTestTool::getEIDFromWord($centralWord); 294 if($centralEID != null) return NodeTestTool::getRandomCloudWordFromCentralEID($centralEID, $relNo); 295 return null; 296 } 297 298 public static function getWordCloudDistance2($centralWord, $relNoDist1, $relNoDist2){ 299 $randomCloudWord = NodeTestTool::getRandomCloudWordFromCentralWord($centralWord, $relNoDist1); 300 echo "Mot intermédiaire : " . $randomCloudWord . "<br />"; 301 if(strlen($randomCloudWord) > 0) 302 return NodeTestTool::getCloudWordsFromCentralWordAndRelNo($randomCloudWord, $relNoDist2); 303 return null; 304 } 305 306 /* 307 * In the implementation of the function getWordCloudDistance2, 8 different 308 * functions could be made corresponding to returning a word cloud or an 309 * eid cloud, the naming would have to be changed to reflect the different 310 * parameters and return values... 311 */ 312 313 //TODO: FROM HERE DOWN ISN4T IN nodeTest01.php START FROM HERE !! 314 315 //getRandomCloudFromCentralEID($centralEID, $numWords) 316 //getRandomCloudFromCentralWord($centralWord, $numWords) 317 318 319 /* * *******************************WRONG OR DISTORTED CLOUD WORDS********* */ 320 321 public static function getRandomCloudEIDofCloudEID($couldEID, $relNum) { 322 323 } 324 325 public static function getRandomCloudWordOfCloudWord($cloudWord, $relNum) { 326 327 } 328 329 /*************************ACCESSEURS************************/ 330 331 public static function getRelations(){ 332 return NodeTestTool::$relations; 333 } 334 335 336 337 /* * ***********************************GD****************************** */ 338 339 /** 340 * (gd) Voisins 1 saut du bon type (= relations déjà existantes) 341 * @global <type> $typer1r2 342 * @param <type> $r1 343 * @param <type> $r2 344 * @param <type> $centerEID 345 */ 346 public static function gd1JumpGoodType($r1, $r2, $centerEID) { 347 global $typer1r2; 348 $db = getDB(); 349 $query = "SELECT end AS eid, type = $r1 as r1, type = $r2 as r2, 350 0 as r0, 0 as trash FROM relation WHERE start = $centerEID AND 351 $typer1r2 order by random()"; 352 $result = $db->querySingle($query, true); //?? 353 return $result['end']; //?? 354 } 355 356 /** 357 * (gd) Voisins 1 saut via r_associated (0), donc qu'on voudrait spécifier 358 * si possible. 359 * @param <type> $centerEID 360 */ 361 public static function gd1JumpViaRAssociated0($centerEID) { 362 $db = getDB(); 363 $query = "SELECT end AS eid, 0.25 AS r1, 0.25 AS r2, 0.5 AS r0, 364 0 AS trash FROM relation WHERE start = $centerEid AND 365 type = 0 order by random()"; 366 $result = $db->querySingle($query, true); //?? 367 return $result['end']; //?? 368 } 369 370 /** 371 * (gd) Voisins 1 saut via les autres relations 372 * @global $banned_types 373 * @param <type> $centerEid 374 * @param <type> $r1 375 * @param <type> $r2 376 * @return <type> 377 */ 378 public static function gd1JumpViaOtherRelation($centerEid, $r1, $r2) { 379 global $banned_types; 380 $query = "SELECT end AS eid, 0.1 AS r1, 0.1 AS r2, 0.8 AS r0, 0 AS trash 381 FROM relation WHERE start = $centerEid AND type NOT IN 382 (0, $r1, $r2, $banned_types) ORDER BY random()"; 383 $result = $db->querySingle($query, true); //?? 384 return $result['end']; //?? 385 } 386 387 /** 388 * (gd) Voisins 2 sauts, avec un mix de R1 et R2 pour les liens. Par ex 389 * [ A -R1-> B -R2-> C ] ou bien [ A -R2-> B -R2-> C ] 390 * Version optimisée de : "SELECT end AS eid FROM relation WHERE $typer1r2 391 * AND start IN oneHopWithType ORDER BY random();" 392 * @global $typer1r2 393 * @param <type> $r1 394 * @param <type> $r2 395 * @param <type> $centerEid 396 * @return <type> 397 */ 398 public static function gd2JumpWithMixR1R2ForLinks($r1, $r2, $centerEid) { 399 global $typer1r2; 400 $db = getDB(); 401 $query = "SELECT B.end AS eid, ((A.type = $r1) + (B.type = $r1)) / 3. 402 AS r1, ((A.type = $r2) + (B.type = $r2)) / 3. AS r2, 1/6. AS r0, 1/6. 403 AS trash FROM relation AS A, relation AS B WHERE A.start = $centerEid 404 AND A.$typer1r2 AND B.start = A.end AND B.$typer1r2 ORDER BY random()"; 405 $result = $db->querySingle($query, true); //?? 406 return $result['end']; //?? 407 } 408 409 /** 410 * (gd) Voisins 1 saut r1/r2 + 1 saut synonyme 411 * Version optimisée de : "SELECT end AS eid FROM relation WHERE start IN 412 * oneHopWithType AND type = 5 order by random()"; 413 * @global $typer1r2 414 * @param <type> $r1 415 * @param <type> $r2 416 * @param <type> $centerEid 417 * @return <type> 418 */ 419 public static function gd1JumpR1DivR2Plus1JumpSynonymOneHopWithType($r1, $r2, $centerEid) { 420 global $typer1r2; 421 $db = getDB(); 422 $query = "SELECT B.end AS eid, (A.type = $r1) * 0.75 as r1, 423 (A.type = $r2) * 0.75 AS r2, 0.25 AS r0, 0 AS trash FROM relation AS A, 424 relation AS B WHERE A.start = $centerEid AND A.$typer1r2 AND B.start = A.end 425 AND B.type = 5 ORDER BY random()"; 426 //$result = $db->querySingle($query, true); //?? 427 //return $result['end']; //?? 428 } 429 430 /** 431 * Version optimisée de : "SELECT end AS eid FROM relation WHERE start IN 432 * (SELECT end FROM relation WHERE start = $centerEid AND type = 5) AND $typer1r2 433 * ORDER BY random();" 434 * @global $typer1r2 435 * @param <type> $r1 436 * @param <type> $r2 437 * @param <type> $centerEid 438 * @return <type> 439 */ 440 public static function gd1JumpR1DivR2Plus1JumpSynonym($r1, $r2, $centerEid) { 441 global $typer1r2; 442 $db = getDB(); 443 $query = "SELECT B.end AS eid, (B.type = $r1) * 0.75 as r1, 444 (B.type = $r2) * 0.75 AS r2, 0.25 AS r0, 0 AS trash FROM relation AS A, 445 relation AS B WHERE A.start = $centerEid AND A.type = 5 AND B.start = A.end 446 AND B.$typer1r2 ORDER BY random()"; 447 //$result = $db->querySingle($query, true); //?? 448 //return $result['end']; //?? 449 } 450 451 /** 452 * // Voisins 2 sauts (tous) 453 * Version optimisée de : "SELECT end AS eid, 0.1 AS r1, 0.1 AS r2, 0.3 AS r0, 454 * 0.5 AS trash FROM relation WHERE start IN (SELECT end FROM relation 455 * WHERE start = $centerEid AND type NOT IN ($banned_types)) AND type NOT IN 456 * ($banned_types) ORDER BY random();" 457 * @global $banned_types $banned_types 458 * @param <type> $centerEid 459 * @param <type> $cloudSize 460 * @return <type> 461 */ 462 public static function gd2JumpAll($centerEid, $cloudSize) { 463 global $banned_types; 464 $db = getDB(); 465 $query = "SELECT x AS eid, 0.1 AS r1, 0.1 AS r2, 0.3 AS r0, 0.5 AS trash 466 FROM (SELECT x FROM (SELECT X.eid + Y.dumb AS x FROM (SELECT B.end AS eid 467 FROM relation AS A, relation AS B WHERE A.type NOT IN ($banned_types) 468 AND A.start = $centerEid AND B.type NOT IN ($banned_types) AND 469 B.start = A.end LIMIT " . ($cloudSize * 4) . ") AS X, (SELECT 0 AS dumb) 470 AS Y)) ORDER BY random()"; 471 //$result = $db->querySingle($query, true); //?? 472 //return $result['end']; //?? 473 } 474 475 /** 476 * (gd) Centre pointe vers X, M pointe vers X aussi, on prend M. 477 * Version optimisée de : "select start as eid from relation where end in (select end from relation where start = $centerEid) and type not in ($banned_types) order by random();" 478 * Ce n'est toujours pas ça… : "select eid from (select B.start as eid from relation as A, relation as B where A.type not in ($banned_types) and A.start = $centerEid and B.type not in ($banned_types) and B.end = A.end limit 1) order by random();" 479 * Tordu, mais ça marche \o/ . En fait il faut empêcher l'optimiseur de ramener le random avant le limit (et l'optimiseur est malin… :) 480 * @global <type> $banned_types 481 * @param <type> $cloudSize 482 */ 483 public static function gdXPointsToMMPointsToXTakeM($cloudSize) { 484 global $banned_types; 485 $db = getDB(); 486 $query = "SELECT x as eid, 0.1 as r1, 0.1 as r2, 0.2 as r0, 0.6 as trash 487 FROM (SELECT x from (SELECT X.eid + Y.dumb AS x FROM (SELECT 488 B.start AS eid FROM relation AS A, relation AS B WHERE A.type NOT IN 489 ($banned_types) AND A.start = $centerEid AND B.type NOT IN ($banned_types) 490 AND B.end = A.end limit " . ($cloudSize * 4) . ") AS X, (SELECT 0 AS dumb) AS Y)) 491 ORDER BY random()"; 492 //$result = $db->querySingle($query, true); //?? 493 //return $result['end']; //?? 494 } 495 496 public static function gdGetGidFromGame() { 497 return "select gid from game where gid = (abs(random()) % (select max(gid) from game))+1 or gid = (select max(gid) from game where gid > 0) order by gid limit 1;"; 498 } 499 500 // TODO Yoann : faire des tests d'erreur pour ces select ? 501 public static function sqlGetGamesForId($gameId) { 502 return "select gid, (select name from node where eid = eid_central_word) as name_central_word, eid_central_word, relation_1, relation_2 from game where gid = " . $gameId . ";"; 503 } 504 505 public static function sqlGetWordEidAndName($gameId) { 506 return "select eid_word,(select name from node where eid=eid_word) as name_word from game_cloud where gid = " . $gameId . ";"; 507 } 508 509 public static function sqlGetInformationAboutGame($gameId) { 510 return "select eid_word,(select name from node where eid=eid_word) as name_word, num, difficulty, totalWeight, probaR1, probaR2, probaR0, probaTrash from game_cloud where gid = " . $gameId . ";"; 511 } 512 513 public static function sqlGameIsOK($pgid, $gid, $user) { 514 return "SELECT 'ok' FROM played_game WHERE pgid = $pgid and $gid = $gid and login = '$user' and timestamp = -1;"; 515 } 516 517 public static function sqlGetScoreForUser($user) { 518 return "SELECT score FROM user WHERE login='" . $user . "';"; 519 } 520 521 public static function sqlGetPlayedGameTime($pgid, $gid, $user) { 522 return "SELECT timestamp FROM played_game WHERE pgid = $pgid and $gid = $gid and login = '$user';"; 523 } 524 525 public static function sqlGetNumAndScoreFromGame($pgid, $gid) { 526 return "SELECT num,score from played_game_cloud where pgid = $pgid and gid = $gid;"; 527 } 528 } 529 530 ?>