commit 07804f962277fc0b48528b9d0d0a5fb7ac37e4a9
parent 4d3196c8f7d1111bfb80f2c3a3aee0e1f06812ff
Author: Georges Dupéron <jahvascriptmaniac+github@free.fr>
Date: Thu, 10 Feb 2011 16:50:22 +0100
Corrections de bugs sur les requêtes SQL.
Diffstat:
2 files changed, 33 insertions(+), 14 deletions(-)
diff --git a/code/serveur/dump2sqlite.sh b/code/serveur/dump2sqlite.sh
@@ -18,7 +18,7 @@ create table type_relation(name, num, extended_name, info);
create table user(login primary key, mail, hash_passwd, score);
create table game(gid integer primary key autoincrement, eid_central_word, relation_1, relation_2, difficulty);
create table game_cloud(gid, num, difficulty, eid_word, totalWeight, probaR1, probaR2, probaR0, probaTrash);
-create table played_game(pgid integer primary key autoincrement, gid, login, played);
+create table played_game(pgid integer primary key autoincrement, gid, login, timestamp);
create table played_game_cloud(pgid, gid, type, num, relation, weight, score);
insert into user(login, mail, hash_passwd, score) values('foo', 'foo@isp.com', '$(echo -n 'bar' | md5sum | cut -d ' ' -f 1)', 0);
EOF
@@ -38,11 +38,13 @@ cat "$1" \
| grep -v '^//' \
| grep -v '^$'
+# Note : l'index i_played_game_all sert à la vérification lors du set_partie.
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);
+create index i_played_game_all on played_game(pgid, gid, login, timestamp);
commit;
EOF
diff --git a/code/serveur/php/pticlic.php b/code/serveur/php/pticlic.php
@@ -98,7 +98,6 @@ function cg_build_result_sets($cloudSize, $centerEid, $r1, $r2)
// Voisins 1 saut via r_associated (0), donc qu'on voudrait spécifier si possible.
array('w'=>40, 'd'=>2, 's'=>"select end as eid, 0.25 as r1, 0.25 as r2, 0.5 as r0, 0 as trash from relation where start = $centerEid and type = 0 order by random();"),
// Voisins 1 saut via les autres relations
- // TODO ! certains de ces select pourraient renvoyer des mots de types systèmes (4, 12, 36, 18, 29, 45, 46, 47, 48, 1000, 1001), il faut les éliminer.
array('w'=>20, 'd'=>3, 's'=>"select end as eid, 0.1 as r1, 0.1 as r2, 0.8 as r0, 0 as trash from relation where start = $centerEid and type not in (0, $r1, $r2, 4, 12, 36, 18, 29, 45, 46, 47, 48, 1000, 1001) 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();"
@@ -109,12 +108,13 @@ function cg_build_result_sets($cloudSize, $centerEid, $r1, $r2)
// 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'=>20, 'd'=>6, 's'=>"select B.end as eid, (B.type = $r1) * 0.75 as r1, (B.type = $r2) * 0.75 as r2, 0.25 as r0, 0 as trash 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, 0.3 as r0, 0.5 as trash from relation where start in (select end from relation where start = $centerEid and type not in (4, 12, 36, 18, 29, 45, 46, 47, 48, 1000, 1001)) and type not in (4, 12, 36, 18, 29, 45, 46, 47, 48, 1000, 1001) order by random();"), // TODO : Georges : Optimiser.
+ // Version optimisée de : "select end as eid, 0.1 as r1, 0.1 as r2, 0.3 as r0, 0.5 as trash from relation where start in (select end from relation where start = $centerEid and type not in (4, 12, 36, 18, 29, 45, 46, 47, 48, 1000, 1001)) and type not in (4, 12, 36, 18, 29, 45, 46, 47, 48, 1000, 1001) order by random();"
+ array('w'=>10, 'd'=>8, 's'=>"select x as eid, 0.1 as r1, 0.1 as r2, 0.3 as r0, 0.5 as trash from (select x from (select X.eid + Y.dumb as x from (select B.end 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.start = A.end limit ".($cloudSize*4).") as X, (select 0 as dumb) as Y)) 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, 0.2 as r0, 0.6 as trash 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, 'd'=>8, 's'=>"select x as eid, 0.1 as r1, 0.1 as r2, 0.2 as r0, 0.6 as trash 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 = $centerEid and B.type not in (4, 12, 36, 18, 29, 45, 46, 47, 48, 1000, 1001) and B.end = A.end limit ".($cloudSize*4).") as X, (select 0 as dumb) as Y)) order by random();"),
'rand' => array('w'=>5, 'd'=>10, 's'=>false) // random. Le r1 et r2 de random sont juste en-dessous
);
@@ -268,21 +268,38 @@ function cg_insert($centerEid, $cloud, $r1, $r2, $totalDifficulty)
$db->exec("INSERT INTO game(gid, eid_central_word, relation_1, relation_2, difficulty)
VALUES (null, $centerEid, $r1, $r2, $totalDifficulty);");
$gid = $db->lastInsertRowID();
-
- $db->exec("INSERT INTO played_game(pgid, gid, login, played)
- VALUES (null, $gid, null, 1);");
- $pgid = $db->lastInsertRowID();
-
+
+ $t = time();
+ $db->exec("INSERT INTO played_game(pgid, gid, login, timestamp)
+ VALUES (null, $gid, null, $t);");
+ $pgid1 = $db->lastInsertRowID();
+ $db->exec("INSERT INTO played_game(pgid, gid, login, timestamp)
+ VALUES (null, $gid, null, $t);");
+ $pgid2 = $db->lastInsertRowID();
+ $db->exec("INSERT INTO played_game(pgid, gid, login, timestamp)
+ VALUES (null, $gid, null, $t);");
+ $pgid0 = $db->lastInsertRowID();
+ $db->exec("INSERT INTO played_game(pgid, gid, login, timestamp)
+ VALUES (null, $gid, null, $t);");
+ $pgidT = $db->lastInsertRowID();
+
+ // TODO : R0 et Trash + corrections
foreach ($cloud as $c)
{
$db->exec("INSERT INTO game_cloud(gid, num, difficulty, eid_word, totalWeight, probaR1, probaR2, probaR0, probaTrash)
VALUES ($gid, ".$c['pos'].", ".$c['d'].", ".$c['eid'].", 2, ".$c['probaR1'].", ".$c['probaR2'].", ".$c['probaR0'].", ".$c['probaTrash'].");");
$db->exec("INSERT INTO played_game_cloud(pgid, gid, type, num, relation, weight, score)
- VALUES ($pgid, $gid, 0, ".$c['pos'].", $r1, ".$c['probaR1'].", 0);");
+ VALUES ($pgid1, $gid, 0, ".$c['pos'].", $r1, ".$c['probaR1'].", 0);");
+
+ $db->exec("INSERT INTO played_game_cloud(pgid, gid, type, num, relation, weight, score)
+ VALUES ($pgid2, $gid, 0, ".$c['pos'].", $r2, ".$c['probaR2'].", 0);");
+
+ $db->exec("INSERT INTO played_game_cloud(pgid, gid, type, num, relation, weight, score)
+ VALUES ($pgid0, $gid, 0, ".$c['pos'].", 0, ".$c['probaR0'].", 0);");
$db->exec("INSERT INTO played_game_cloud(pgid, gid, type, num, relation, weight, score)
- VALUES ($pgid, $gid, 0, ".$c['pos'].", $r1, ".$c['probaR1'].", 0);");
+ VALUES ($pgidT, $gid, 0, ".$c['pos'].", -1, ".$c['probaTrash'].", 0);");
}
$db->exec("commit;");
@@ -321,7 +338,7 @@ function game2json($game_id)
global $db, $user;
// TODO : planter si la requête suivante échoue pour quelque raison que ce soit.
- $db->exec("INSERT INTO played_game(pgid, gid, login, played) VALUES (null, $game_id, '$user', 0);");
+ $db->exec("INSERT INTO played_game(pgid, gid, login, timestamp) VALUES (null, $game_id, '$user', -1);");
$pgid = $db->lastInsertRowID();
// TODO Yoann : faire des tests d'erreur pour ces select ?
@@ -417,14 +434,14 @@ function setGame()
$pgid = intval($_GET['pgid']);
$gid = intval($_GET['gid']);
- if ('t' !== $db->querySingle("SELECT 't' FROM played_game WHERE pgid = $pgid and $gid = $gid and played = 0 and login = '$user';"))
+ if ('ok' !== $db->querySingle("SELECT 'ok' FROM played_game WHERE pgid = $pgid and $gid = $gid and login = '$user' and timestamp = -1;"))
mDie(4,"Cette partie n'est associée à votre nom d'utilisateur, ou bien vous l'avez déjà jouée.");
$userReputation = $db->querySingle("SELECT score FROM user WHERE login='$user';");
$userReputation = ($userReputation > 0) ? log($userReputation) : 0;
$db->exec("begin transaction;");
- $db->exec("update played_game set played = 1 where pgid = $pgid;");
+ $db->exec("update played_game set timestamp = ".time()." where pgid = $pgid;");
$r0 = 0;
$trash = -1;
