commit 21bb2972067c19061012963dcfdf3352e45d8e1d
parent f04c6d5124c2933233485683ff1538a7dfa3e7ef
Author: Georges Dupéron <jahvascriptmaniac+github@free.fr>
Date: Tue, 1 Feb 2011 23:46:50 +0100
Serveur : requêtes Ok 90%
Diffstat:
1 file changed, 66 insertions(+), 26 deletions(-)
diff --git a/code/serveur/php/pticlic.php b/code/serveur/php/pticlic.php
@@ -37,17 +37,10 @@ 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;");
}
-function create_game($cloudSize) {
+// 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;
- // select random node
- $centerEid = random_node();
- $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];
-
// 'w' => weight (poids), 's' => select
// 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)";
@@ -59,30 +52,51 @@ function create_game($cloudSize) {
// Voisins 1 saut via les autres relations
array('w'=>10, 's'=>"select end as eid 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 ]
- // TODO !! Optimiser cette requête
- array('w'=>10, 's'=>"select end as eid from relation where start in (select end from relation where start = $centerEid and $typer1r2) and $typer1r2 order by random();"),
+ // Version optimisée de : "select end as eid from relation where $typer1r2 and start in oneHopWithType order by random();"
+ array('w'=>10, 's'=>"select B.end as eid 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
- // TODO !! Optimiser cette requête
- array('w'=>10, 's'=>"select end as eid from relation where start in (select end from relation where start = $centerEid and $typer1r2) and type = 5 order by random();"),
- // TODO !! Optimiser cette requête
- array('w'=>10, 's'=>"select end as eid from relation where start in (select end from relation where start = $centerEid and type = 5) and $typer1r2 order by random();"),
+ // Version optimisée de : "select end as eid from relation where start in oneHopWithType and type = 5 order by random();"
+ array('w'=>10, 's'=>"select B.end as eid 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, 's'=>"select B.end as eid 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, 's'=>"select end as eid 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.
- // TODO !! Optimiser cette requête
- array('w'=>10, 's'=>"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();")
+ // 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, 's'=>"select x as eid 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
);
$sumWeights = 0;
- echo " centereid = $centerEid ";
- foreach ($sources as $x) {
+ foreach ($sources as $k => $x) {
$sumWeights += $x['w'];
- $x['resultSet'] = $db->query($x['s']);
+ if ($x['s'] !== false) {
+ $sources[$k]['resultSet'] = $db->query($x['s']);
+ } else {
+ $sources[$k]['resultSet'] = false;
+ }
}
-
+ 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
- $finishedSources = 0;
- while ($finishedSources < 2) {
+ $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;
@@ -90,13 +104,39 @@ function create_game($cloudSize) {
foreach ($sources as $x) {
$sumw += $x['w'];
if ($rands < $sumw) {
- $resultSet = $x['resultSet'];
+ $resultSet = $x['resultSet'] ? $x['resultSet'] : false;
break;
}
}
- $finishedSources++;
+ if ($resultSet) {
+ // TODO : vérifier qu'on peut fetch avant !
+ if (!$res = $resultSet->fetchArray()) {
+ $nbFailed++;
+ continue;
+ }
+ $res = $res['eid'];
+ } else {
+ $res = random_node();
+ }
+ if (in_array($res, $cloud)) {
+ $nbFailed++;
+ continue;
+ }
+ $cloud[] = $res;
+ $i++;
}
+ return $cloud;
+}
+function create_game($cloudSize) {
+ global $db;
+ // select random node
+ $centerEid = random_node();
+ $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);
+
+ var_dump($cloud);
exit;
// select neighbors 1 hop
