commit 71a70ba1565e75b58d75f647032ce2366e694037
parent df1b86dab520e2203af9f1b91adf90c3cda54abc
Author: Georges Dupéron <jahvascriptmaniac+github@free.fr>
Date: Mon, 31 Jan 2011 22:46:39 +0100
Serveur partiellement fonctionnel.
Diffstat:
6 files changed, 122 insertions(+), 121 deletions(-)
diff --git a/code/serveur/dump2mysql.sh b/code/serveur/dump2mysql.sh
@@ -29,6 +29,7 @@ cat "$1" \
| sed -e 's/X/XX/g' | sed -e 's/A/Xa/g' | tr '\n' 'A' | sed -e 's/A")/")/g' | tr 'A' '\n' | sed -e 's/Xa/A/g' | sed -e 's/XX/X/g' \
| pv -s $(wc -c "$1" | cut -d ' ' -f 1) \
| sed -e "s#'#''#g" \
+| sed -e 's/\\//g' \
| sed -E -e 's#^// [0-9]+ occurrences of relations ([a-z_]+) \(t=([0-9]+) nom_etendu="([^"]+)" info="([^"]+)"\)$#insert into type_relation(name, num, extended_name, info) values('\''\1'\'', \2, '\''\3'\'', '\''\4'\'');#' \
| sed -E -e 's#^// [0-9]+ occurrences of nodes ([a-z_]+) \(t=([0-9]+)\)$#insert into type_node(name, num) values('\''\1'\'', \2);#' \
| sed -E -e 's#^eid=([0-9]+):n="(.*)":t=([0-9]+):w=(-?[0-9]+)$#insert into node(eid, name, type, weight) values(\1, '\''\2'\'', '\''\3'\'', '\''\4'\'');#' \
@@ -42,7 +43,5 @@ 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_end_type on relation(end,type);
-create index i_sessid_login on sessid(login);
-create index i_sessid_sid on sessid(sid);
commit;
EOF
diff --git a/code/serveur/dump2sqlite.sh b/code/serveur/dump2sqlite.sh
@@ -9,13 +9,12 @@ cat <<EOF
begin transaction;
create table node(eid integer primary key autoincrement, name, type, weight);
create table relation(rid integer primary key autoincrement, start, end, type, weight);
-create table type_node(nom, num);
-create table type_relation(nom, num, nom_etendu, info);
-create table user(login primary key, mail, hash_mdp);
-create table sessid(login, sid);
-create table partie(pid, eid_mot_central, relation_1, relation_2, relation_3, relation_4);
-create table partie_nuage(pid, num, eid_mod);
-create table partie_reference(pid, num, relation, poids);
+create table type_node(name, num);
+create table type_relation(name, num, extended_name, info);
+create table user(login primary key, mail, hash_passwd);
+create table game(gid integer primary key autoincrement, eid_central_word, relation_1, relation_2, relation_3, relation_4, reference_played_game);
+create table game_cloud(gid, num, difficulty, eid_word);
+create table played_game(gid, type, num, relation, weight);
EOF
# tr : pour virer le CRLF qui traîne
@@ -25,11 +24,11 @@ cat "$1" \
| tr '\r' ' ' \
| sed -e 's/X/XX/g' | sed -e 's/A/Xa/g' | tr '\n' 'A' | sed -e 's/A")/")/g' | tr 'A' '\n' | sed -e 's/Xa/A/g' | sed -e 's/XX/X/g' \
| pv -s $(wc -c "$1" | cut -d ' ' -f 1) \
-| sed -e "s#'#''#g" \
-| sed -E -e 's#^// [0-9]+ occurrences of relations ([a-z_]+) \(t=([0-9]+) nom_etendu="([^"]+)" info="([^"]+)"\)$#insert into type_relation(nom, num, nom_etendu, info) values('\''\1'\'', \2, '\''\3'\'', '\''\4'\'');#' \
-| sed -E -e 's#^// [0-9]+ occurrences of nodes ([a-z_]+) \(t=([0-9]+)\)$#insert into type_node(nom, num) values('\''\1'\'', \2);#' \
-| sed -E -e 's#^eid=([0-9]+):n="(.*)":t=([0-9]+):w=(-?[0-9]+)$#insert into node(eid, name, type, weight) values(\1, '\''\2'\'', '\''\3'\'', '\''\4'\'');#' \
-| sed -E -e 's#^rid=([0-9]+):n1=([0-9]+):n2=([0-9]+):t=([0-9]+):w=(-?[0-9]+)#insert into relation(rid, start, end, type, weight) values(\1,\2,\3,\4,\5);#' \
+| sed -E -e "s#'#''#g" \
+ -e 's#^// [0-9]+ occurrences of relations ([a-z_]+) \(t=([0-9]+) nom_etendu="([^"]+)" info="([^"]+)"\)$#insert into type_relation(name, num, extended_name, info) values('\''\1'\'', \2, '\''\3'\'', '\''\4'\'');#' \
+ -e 's#^// [0-9]+ occurrences of nodes ([a-z_]+) \(t=([0-9]+)\)$#insert into type_node(name, num) values('\''\1'\'', \2);#' \
+ -e 's#^eid=([0-9]+):n="(.*)":t=([0-9]+):w=(-?[0-9]+)$#insert into node(eid, name, type, weight) values(\1, '\''\2'\'', '\''\3'\'', '\''\4'\'');#' \
+ -e 's#^rid=([0-9]+):n1=([0-9]+):n2=([0-9]+):t=([0-9]+):w=(-?[0-9]+)#insert into relation(rid, start, end, type, weight) values(\1,\2,\3,\4,\5);#' \
| grep -v '^//' \
| grep -v '^$'
@@ -38,7 +37,5 @@ 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_end_type on relation(end,type);
-create index i_sessid_login on sessid(login);
-create index i_sessid_sid on sessid(sid);
commit;
EOF
diff --git a/code/serveur/parties.json b/code/serveur/parties.json
@@ -1,4 +1,4 @@
-// Requête : http://serveur/pticlic.php?action=getparties&nb=2&mode=normal
+// Requête : http://serveur/pticlic.php?action=getparties&nb=2&mode=normal&user=foo&passwd=bar
[
{
id: 1234,
@@ -7,6 +7,7 @@
cat3: 35,
cat4: 12,
center: { id: 555, "chat" },
+ cloudsize: 3,
cloud: [
{ id: 123, name: "souris" },
{ id: 111, name: "lait" },
@@ -20,6 +21,7 @@
cat3 : -1,
cat4 : -1,
center: { id: 666, "bouteille" },
+ cloudsize: 2,
cloud: [
{ id: 11359, name: "jus de fruit" },
{ id: 111, name: "lait" }
diff --git a/code/serveur/php/config.php b/code/serveur/php/config.php
@@ -3,15 +3,8 @@
/** fichier de configuration */
// Général
-$email_admin = ''; // Adresse e-mail Administrateur.
-
-// Base de donées.
-$sql_serveur = 'localhost'; // Serveur de la Base De Donnée (BDD).
-$sql_login = 'root'; // Login BDD.
-$sql_pass = ' '; // Pass BDD.
-$sql_bdd = 'pticlic'; // Nom de la BDD.
-
+
// Quelques fonctions utiles.
-include("function.php");
+// include("function.php");
?>
diff --git a/code/serveur/php/function.php b/code/serveur/php/function.php
@@ -5,9 +5,9 @@
// Connexion à la base de données.
function sqlConnect()
{
- global $sql_serveur, $sql_login, $sql_pass, $sql_bdd;
+ global $sql_server, $sql_login, $sql_pass, $sql_bdd;
//connexion au serveur
- $linkid = @mysql_connect($sql_serveur,$sql_login,$sql_pass) or die ("Erreur lors de la connection au serveur MySQL !");
+ $linkid = @mysql_connect($sql_server,$sql_login,$sql_pass) or die ("Erreur lors de la connection au serveur MySQL !");
//selection de la base
@mysql_select_db($sql_bdd,$linkid) or die("Impossible de selectionner la base de données\n<br>\nVoici l'erreur renvoyée par le serveur MySQL :\n<br>\n".mysql_error());
@@ -22,24 +22,13 @@ function secure($string)
}
else
{
- $string = mysql_real_escape_string($string);
+ $string = sqlite_escape_string($string);
$string = addcslashes($string, '%_');
}
return $string;
}
-// TODO Yoann : fonction qui échappe les "
-function escape_json_string($str) {
- return $str;
-}
-
-function mDie($err,$msg)
-{
- echo "{ error:\"".escape_json_string($err)."\", msg:\"".escape_json_string($msg)."\"}";
- exit(1);
-}
-
function writeRequest($request)
{
diff --git a/code/serveur/php/pticlic.php b/code/serveur/php/pticlic.php
@@ -1,111 +1,132 @@
<?php
-require_once("./config/config.php");
+// Requête : http://serveur/pticlic.php?action=getparties&nb=2&mode=normal&user=foo&passwd=bar
-if(!isset($_POST['cmd']) || !isset($_POST['psd']) || !isset($_POST['passwd']))
- mDie(1,"La requête est incomplète");
-
-$cmd = secure($_POST['cmd']);
-$psd = secure($_POST['psd']);
-$passwd = md5($_POST['passwd']);
+$email_admin = ''; // Adresse e-mail Administrateur.
-$req = "SELECT passwd FROM member WHERE pseudo='$psd'";
+$SQL_DBNAME = (dirname(__FILE__) . "/db");
-$sql = sqlConnect();
-$resp = mysql_query($req);
+function mDie($err,$msg)
+{
+ echo "{ error:".json_encode("".$err).", msg:".json_encode("".$msg)."}";
+ exit;
+}
-if(mysql_num_rows($res) < 1)
- mDie(2,"Utilisateur non enregistré");
-
-$data = mysql_fetch_array($resp);
+if (!$db = new SQLite3('db')) {
+ mDie(1,"Erreur lors de l'ouverture de la base de données SQLite3");
+}
+
+function initdb() {
+ global $db;
+ $db->exec("insert into user(login, mail, hash_passwd) values('foo', 'foo@isp.com', '".md5('bar')."');");
+}
-mysql_close($sql);
+// initdb();
-if(strcmp($data['passwd'],$passwd) != 0)
- mDie(3,"Nom d'utilisateur ou mot de passe incorrect");
-
+if(!isset($_GET['action']) || !isset($_GET['user']) || !isset($_GET['passwd']))
+ mDie(2,"La requête est incomplète");
+
+// Login
+$action = $_GET['action'];
+$user = $_GET['user'];
+$hash_passwd = md5($_GET['passwd']);
+if ($hash_passwd !== $db->querySingle("SELECT hash_passwd FROM user WHERE login='".SQLite3::escapeString($user)."';"))
+ mDie(3,"Utilisateur non enregistré ou mauvais mot de passe");
function random_node() {
- return mysql("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 desc limit 1;");
+ global $db;
+ 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($cloud_size) {
+function create_game($cloudSize) {
+ global $db;
// select random node
- $eid_center=random_node();
+ $centerEid = random_node();
// select neighbors 1 hop
- $niveau1=mysql("select end from relation where start = 42 limit $taille_nuage;");
+ 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
- $niveau2=mysql("select * from relation where start in (select end from relation where start = 42) limit $taille_nuage;");
+ 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"); }
// select neighbors relative to the end (one hop start->end, one hop start<-end).
- $niveau3=mysql("select * 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) limit $taille_nuage;");
-
- // pour compléter si nécessaire :
- // select random words
- $niveau4=array();
- for ($i=0; $i < $cloud_size; $i++) {
- $niveau4[$i] = ???
+ 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"); }
+
+ // 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.");");
}
-
- // start transaction;
- // insert into game $eid_center
- // insert into game_cloud [$cloud_size mots choisis dans $niveau1, $niveau2, $niveau3, $niveau4]
- // insert into game_played une partie de référence.
- // commit;
+ // TODO : insert into game_played une partie de référence.
+
+ $db->exec("commit;");
}
+create_game(10);
-// Sinon tout est bon on effectu l'opération correspondant à la commande passée.
-if($cmd == 0) // "Get partie"
-{
- // Requête sql de création de partie.
- $req = "...";
+// // Sinon tout est bon on effectu l'opération correspondant à la commande passée.
+// if($action == 0) // "Get partie"
+// {
+// // Requête sql de création de partie.
+// $req = "...";
- $sql = sqlConnect();
- $resp = mysql_query($req);
+// $sql = sqlConnect();
+// $resp = mysql_query($req);
- if(mysql_num_rows($resp) == 0)
- echo mysql_error();
- else
- {
- $sequence = "...";
- echo $sequence;
- }
+// if(mysql_num_rows($resp) == 0)
+// echo mysql_error();
+// else
+// {
+// $sequence = "...";
+// echo $sequence;
+// }
- mysql_close($sql);
-}
-else if($cmd == 1) // "Set partie"
-{
- // Requête sql d'ajout d'informations (et calcul de résultat).
- $req = "...";
+// mysql_close($sql);
+// }
+// else if($action == 1) // "Set partie"
+// {
+// // Requête sql d'ajout d'informations (et calcul de résultat).
+// $req = "...";
- $sql = sqlConnect();
- $resp = mysql_query($req);
+// $sql = sqlConnect();
+// $resp = mysql_query($req);
- if(mysql_num_rows($resp) == 0)
- echo mysql_error();
- else
- {
- $sequence = "...";
- echo $sequence;
- }
+// if(mysql_num_rows($resp) == 0)
+// echo mysql_error();
+// else
+// {
+// $sequence = "...";
+// echo $sequence;
+// }
- mysql_close($sql);
-}
-else if($cmd == 2)
-{
-
-}
-else if($cmd == 3)
-{
-
-}
-else if($cmd == 4)
-{
-
-}
-else
- die("Commande inconnue");
+// mysql_close($sql);
+// }
+// else if($action == 2)
+// {
+
+// }
+// else if($action == 3)
+// {
+
+// }
+// else if($action == 4)
+// {
+
+// }
+// else
+// die("Commande inconnue");
?>
\ No newline at end of file
