commit fda5ca174f6efdf7d5e70a28c4f47196a9e37a08 parent ea79a5dc71c8772fb44a1c138923835693429833 Author: Bertrand BRUN <bertrand0brun@gmail.com> Date: Wed, 2 Feb 2011 10:05:00 +0100 Merge branch 'master' of https://github.com/jsmaniac/2011-m1s2-ter Diffstat:
29 files changed, 122 insertions(+), 31 deletions(-)
diff --git a/code/images/Aide36.png b/code/images/Aide36.png Binary files differ. diff --git a/code/images/Aide48.png b/code/images/Aide48.png Binary files differ. diff --git a/code/images/Aide72.png b/code/images/Aide72.png Binary files differ. diff --git a/code/images/Contenant36.png b/code/images/Contenant36.png Binary files differ. diff --git a/code/images/Contenant48.png b/code/images/Contenant48.png Binary files differ. diff --git a/code/images/Contenant72.png b/code/images/Contenant72.png Binary files differ. diff --git a/code/images/Contenu36.png b/code/images/Contenu36.png Binary files differ. diff --git a/code/images/Contenu48.png b/code/images/Contenu48.png Binary files differ. diff --git a/code/images/Contenu72.png b/code/images/Contenu72.png Binary files differ. diff --git a/code/images/Contraire36.png b/code/images/Contraire36.png Binary files differ. diff --git a/code/images/Contraire48.png b/code/images/Contraire48.png Binary files differ. diff --git a/code/images/Contraire72.png b/code/images/Contraire72.png Binary files differ. diff --git a/code/images/FondLogo.png b/code/images/FondLogo.png Binary files differ. diff --git a/code/images/ModeNormal36.png b/code/images/ModeNormal36.png Binary files differ. diff --git a/code/images/ModeNormal48.png b/code/images/ModeNormal48.png Binary files differ. diff --git a/code/images/ModeNormal72.png b/code/images/ModeNormal72.png Binary files differ. diff --git a/code/images/ModeOmbre36.png b/code/images/ModeOmbre36.png Binary files differ. diff --git a/code/images/ModeOmbre48.png b/code/images/ModeOmbre48.png Binary files differ. diff --git a/code/images/ModeOmbre72.png b/code/images/ModeOmbre72.png Binary files differ. diff --git a/code/images/Rapport36.png b/code/images/Rapport36.png Binary files differ. diff --git a/code/images/Rapport48.png b/code/images/Rapport48.png Binary files differ. diff --git a/code/images/Rapport72.png b/code/images/Rapport72.png Binary files differ. diff --git a/code/images/Synonyme36.png b/code/images/Synonyme36.png Binary files differ. diff --git a/code/images/Synonyme48.png b/code/images/Synonyme48.png Binary files differ. diff --git a/code/images/Synonyme72.png b/code/images/Synonyme72.png Binary files differ. diff --git a/code/serveur/dump2mysql.sh b/code/serveur/dump2mysql.sh @@ -42,6 +42,7 @@ cat <<EOF create index i_relation_start on relation(start); create index i_relation_end on relation(end); create index i_relation_type on relation(type); +create index i_relation_start_type on relation(start,type); create index i_relation_end_type on relation(end,type); commit; EOF diff --git a/code/serveur/dump2sqlite.sh b/code/serveur/dump2sqlite.sh @@ -36,6 +36,7 @@ cat <<EOF create index i_relation_start on relation(start); create index i_relation_end on relation(end); create index i_relation_type on relation(type); +create index i_relation_start_type on relation(start,type); create index i_relation_end_type on relation(end,type); commit; EOF diff --git a/code/serveur/php/pticlic.php b/code/serveur/php/pticlic.php @@ -37,46 +37,126 @@ function random_node() { return $db->querySingle("select eid from node where eid = (abs(random()) % (select max(eid) from node))+1 or eid = (select max(eid) from node where eid > 0) order by eid limit 1;"); } +// TODO : Yoann : peut-être faire une classe create_game avec les fonctions ci-dessous comme méthodes ? + +function cg_build_result_sets($cloudSize, $centerEid, $r1, $r2) { + global $db; + // 'w' => weight (poids), 'd' => difficulté, 's' => select + // Le select doit ranvoyer trois colonnes : + // eid => l'eid du mot à mettre dans le nuage, + // r1 => la probabilité pour que le mot soit dans r1, entre -1 et 1 (négatif = ne devrait pas y être, positif = devrait y être à coup sûr, 0 = on sait pas). + // TODO : comment mettre un poids sur random, sachant qu'il ne peut / devrait pas être dans ces select, mais plutôt un appel à random_node() ? + $typer1r2 = "type in ($r1, $r2)"; + $sources = array( + // Voisins 1 saut du bon type (= relations déjà existantes) + array('w'=>10, 'd'=>1, 's'=>"select end as eid, type = $r1 as r1, type = $r2 as r2 from relation where start = $centerEid and $typer1r2 order by random();"), + // Voisins 1 saut via r_associated (0), donc qu'on voudrait spécifier si possible. + array('w'=>10, 'd'=>2, 's'=>"select end as eid, 0 as r1, 0 as r2 from relation where start = $centerEid and type = 0 order by random();"), + // Voisins 1 saut via les autres relations + array('w'=>10, 'd'=>3, 's'=>"select end as eid, 0 as r1, 0 as r2 from relation where start = $centerEid and type not in (0, $r1, $r2) order by random();"), + // Voisins 2 sauts, avec un mix de R1 et R2 pour les liens. Par ex [ A -R1-> B -R2-> C ] ou bien [ A -R2-> B -R2-> C ] + // Version optimisée de : "select end as eid from relation where $typer1r2 and start in oneHopWithType order by random();" + array('w'=>10, 'd'=>4, 's'=>"select B.end as eid, ((A.type = $r1) + (B.type = $r1)) / 3 as r1, ((A.type = $r2) + (B.type = $r2)) / 3 as r2 from relation as A, relation as B where A.start = $centerEid and A.$typer1r2 and B.start = A.end and B.$typer1r2 order by random();"), + // Voisins 1 saut r1/r2 + 1 saut synonyme + // Version optimisée de : "select end as eid from relation where start in oneHopWithType and type = 5 order by random();" + array('w'=>10, 'd'=>5, 's'=>"select B.end as eid, (A.type = $r1) * 0.75 as r1, (A.type = $r2) * 0.75 as r2 from relation as A, relation as B where A.start = $centerEid and A.$typer1r2 and B.start = A.end and B.type = 5 order by random();"), + // Version optimisée de : "select end as eid from relation where start in (select end from relation where start = $centerEid and type = 5) and $typer1r2 order by random();" + array('w'=>10, 'd'=>6, 's'=>"select B.end as eid, (B.type = $r1) * 0.75 as r1, (B.type = $r2) * 0.75 as r2 from relation as A, relation as B where A.start = $centerEid and A.type = 5 and B.start = A.end and B.$typer1r2 order by random();"), + // Voisins 2 sauts (tous) + array('w'=>10, 'd'=>7, 's'=>"select end as eid, 0.1 as r1, 0.1 as r2 from relation where start in (select end from relation where start = $centerEid) order by random();"), + // Centre pointe vers X, M pointe vers X aussi, on prend M. + // Version optimisée de : "select start as eid from relation where end in (select end from relation where start = $centerEid) and type not in (4, 12, 36, 18, 29, 45, 46, 47, 48, 1000, 1001) order by random();" + // 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 (4, 12, 36, 18, 29, 45, 46, 47, 48, 1000, 1001) and A.start = $centerEid and B.type not in (4, 12, 36, 18, 29, 45, 46, 47, 48, 1000, 1001) and B.end = A.end limit 1) order by random();" + // 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… :) + array('w'=>10, 'd'=>8, 's'=>"select x as eid, -0.1 as r1, -0.1 as r2 from (select x from (select X.eid + Y.dumb as x from (select B.start as eid from relation as A, relation as B where A.type not in (4, 12, 36, 18, 29, 45, 46, 47, 48, 1000, 1001) and A.start = 74860 and B.type not in (4, 12, 36, 18, 29, 45, 46, 47, 48, 1000, 1001) and B.end = A.end limit $cloudSize) as X, (select 0 as dumb) as Y)) order by random();"), + array('w'=>10, 's'=>false) // random. Le r1 et r2 de random sont juste en-dessous + ); + + $sumWeights = 0; + foreach ($sources as $k => $x) { + $sumWeights += $x['w']; + if ($x['s'] !== false) { + $sources[$k]['resultSet'] = array(); + $res = $db->query($x['s']); + $i = 0; + while ($i < 10 && $sources[$k]['resultSet'][] = $res->fetchArray()) { + $i++; + } + } else { + $sources[$k]['resultSet'] = array(); + for ($i = 0; $i < 10; $i++) { + $sources[$k]['resultSet'][] = array('eid'=>random_node(), 'r1'=>-1, 'r2'=>-1); + } + } + } + return array($sources, $sumWeights); +} + +function cg_choose_relations() { + $relations = array(5, 7, 9, 10); + $r1 = rand(0,count($relations)-1); + $r2 = rand(0,count($relations)-2); + if ($r2 >= $r1) $r2++; + $r1 = $relations[$r1]; + $r2 = $relations[$r2]; + return array($r1, $r2); +} + +function cg_build_cloud($cloudSize, $sources, $sumWeights) { + // On boucle tant qu'il n'y a pas eu au moins 2 sources épuisées + $cloud = array(); + $nbFailed = 0; + $i = 0; + while ($i < $cloudSize && $nbFailed < 50) { + // On choisit une source aléatoire en tennant compte des poids. + $rands = rand(1,$sumWeights); + $sumw = 0; + $res = false; // TODO + foreach ($sources as $x) { + $sumw += $x['w']; + if ($rands < $sumw) { + $res = $x['resultSet']; + break; + } + } + if (/* dépassé la fin de ce set */) { + $nbFailed++; + continue; + } + $res = $res['eid']; + if (in_array($res, $cloud)) { + $nbFailed++; + continue; + } + $cloud[] = $res; + $i++; + } + return $cloud; +} + function create_game($cloudSize) { global $db; // select random node $centerEid = random_node(); - - // select neighbors 1 hop - if (!$difficulty_1 = $db->query("select end as eid from relation where start = 42 order by random() limit " . $cloudSize . ";")) { mDie(4,"Erreur dans la requête d1"); } - - - // select neighbors 2 hops - if (!$difficulty_2 = $db->query("select end as eid from relation where start in (select end from relation where start = 42) order by random() limit " . $cloudSize . ";")) { mDie(4,"Erreur dans la requête d1"); } + $r1 = cg_choose_relations(); $r2 = $r1[1]; $r1 = $r1[0]; + $sources = cg_build_result_sets($cloudSize, $centerEid, $r1, $r2); $sumWeights = $sources[1]; $sources = $sources[0]; + $cloud = cg_build_cloud($cloudSize, $sources, $sumWeights); - // select neighbors relative to the end (one hop start->end, one hop start<-end). - if (!$difficulty_3 = $db->query("select start as eid from relation where end in (select end from relation where start = 42) and type not in (4, 12, 36, 18, 29, 45, 46, 47, 48, 1000, 1001) order by random() limit " . $cloudSize . ";")) { mDie(4,"Erreur dans la requête d1"); } + var_dump($cloud); + exit; - // TODO : faire les select ci-dessous en les limitant à certaines relations. $db->exec("begin transaction;"); $db->exec("insert into game(gid, eid_central_word, relation_1, relation_2, relation_3, relation_4, reference_played_game) values (null, ".$centerEid.", 1,2,3,4,null);"); $gid = $db->lastInsertRowID(); - for ($i=0; $i < $cloudSize; $i++) { - switch (rand(1,4)) { - case 1: - if ($eid = $difficulty_1->fetchArray()) { $eid=$eid['eid']; $difficulty=1; break; } - case 2: - if ($eid = $difficulty_2->fetchArray()) { $eid=$eid['eid']; $difficulty=2; break; } - case 3: - if ($eid = $difficulty_3->fetchArray()) { $eid=$eid['eid']; $difficulty=3; break; } - case 4: - $eid = random_node(); - $difficulty=4; - } - $db->exec("insert into game_cloud(gid, num, difficulty, eid_word) values(".$gid.", ".$i.", ".$difficulty.", ".$eid.");"); + foreach ($cloud as $eid) { + $db->exec("insert into game_cloud(gid, num, difficulty, eid_word) values(".$gid.", ".$i.", ".$difficulty.", ".$eid.");"); } // TODO : insert into game_played une partie de référence. - $db->exec("commit;"); } create_game(10); -echo "ok"; +echo 'ok'; // // Sinon tout est bon on effectu l'opération correspondant à la commande passée. // if($action == 0) // "Get partie" diff --git a/rapport/rapport.tex b/rapport/rapport.tex @@ -118,12 +118,21 @@ Android Developer, 2011. (\url{http://developer.android.com/}) \section{Notes Georges} -Les relations suivantes seront peut-être utilisées (* = oui, c'est sûr) -r\_carac 17 -r\_agent 13 -r\_patient 14 -* r\_syn 5 - +Les relations suivantes seront peut-être utilisées (* = oui, c'est sûr, on a/doit faire les icônes et des requêtes sql)~: + +\begin{tabular}{|c|l|l|l|} +\hline +icône~? & nom & num & signification \\ +\hline +$*$ & r\_syn & 5 & synonyme (chat -> matou) \\ +$*$ & r\_anto & 7 & antonyme (bon -> mauvais) \\ +$*$ & r\_has\_part & 9 & A comme partie (chat -> patte) \\ +$*$ & r\_holo & 10 & Fait partie de (patte -> chat) \\ + & r\_agent & 13 & Peut exécuter comme action (chat -> manger) \\ + & r\_patient & 14 & Peut subir comme action (chat -> être lavé) \\ + & r\_carac & 17 & Caractéristique (chat -> affectueux (ou pas…)) \\ +\hline +\end{tabular} \newpage \appendix
