commit 1f7076b09db297349355e5629ef2be7519b10cd4
parent 542dac8822ba367c87f1aafeccc46922d44163e7
Author: Georges Dupéron <jahvascriptmaniac+github@free.fr>
Date: Thu, 24 Feb 2011 23:07:37 +0100
Sélection de mots intéressants lors des RANDOM.
Diffstat:
4 files changed, 37 insertions(+), 10 deletions(-)
diff --git a/code/serveur/README.sh b/code/serveur/README.sh
@@ -3,23 +3,30 @@
# cat dump.url
# Aller à cette adresse, et télécharger le dernier dump
echo
-echo "Étape 1/3 : Téléchargement"
-echo "=========================="
+echo "Étape 1/5 : Téléchargement de la version du dump"
+echo "================================================"
latest="$(wget 'http://www.lirmm.fr/~lafourcade/JDM-LEXICALNET-FR/?C=M;O=D' -O- | grep '\-LEXICALNET\-JEUXDEMOTS\-FR\-\(NOHTML\)\?\.txt' | head -n 1 | sed -E -e 's/^.*<a href="([0-9]*-LEXICALNET-JEUXDEMOTS-FR-(NOHTML)?\.txt)">.*$/\1/')"
+
+echo
+echo "Étape 2/5 : Téléchargement du dump"
+echo "=================================="
wget -c 'http://www.lirmm.fr/~lafourcade/JDM-LEXICALNET-FR/'"$latest"
echo
-echo "Étape 2/3 : Conversion vers sql"
+echo "Étape 3/5 : Conversion vers sql"
echo "==============================="
./dump2sqlite.sh "$latest" > sql
echo
-echo "Étape 3/3 : Insertion dans la bdd"
+echo "Étape 4/5 : Insertion dans la bdd"
echo "================================="
[ -e php/db ] && mv php/db php/db.old
pv sql | sqlite3 php/db
+echo
+echo "Étape 5/5 : Réglage des permissions"
+echo "==================================="
: > /tmp/log-chmod-pticlic
sudo chgrp -R www-data php > /tmp/log-chmod-pticlic || sudo chgrp -R www php > /tmp/log-chmod-pticlic || {
cat /tmp/log-chmod-pticlic
diff --git a/code/serveur/dump2sqlite.sh b/code/serveur/dump2sqlite.sh
@@ -30,6 +30,8 @@ create table game(gid integer primary key autoincrement, eid_central_word, relat
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, timestamp);
create table played_game_cloud(pgid, gid, type, num, relation, weight, score);
+create table random_cloud_node(eid,nbneighbors);
+create table random_center_node(eid);
insert into user(login, mail, hash_passwd, score) values('$(echo "$user" | sed -e "s/'/''/g")', 'foo@isp.com', '$(echo "$passwd" | dd bs=1 count="${#passwd}" | (if which md5sum >/dev/null 2>&1; then md5sum; else md5; fi) | cut -d ' ' -f 1)', 0);
EOF
@@ -56,5 +58,18 @@ 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);
+insert into random_cloud_node(eid,nbneighbors) select eid,sum(nb) from (
+ select (select type from node where node.eid = relation.start) as type,
+ start as eid,
+ count(start) as nb from relation where type not in (4, 12, 36, 18, 29, 45, 46, 47, 48, 1000, 1001)
+ group by start
+ union
+ select (select type from node where node.eid = relation.start) as type,
+ end as eid,
+ count(end) as nb from relation where type not in (4, 12, 36, 18, 29, 45, 46, 47, 48, 1000, 1001)
+ group by end
+) where type = 1 group by eid;
+create index i_random_cloud_node_nbneighbors on random_cloud_node(nbneighbors);
+insert into random_center_node(eid) select eid from random_cloud_node where nbneighbors > 3;
commit;
EOF
diff --git a/code/serveur/php/.gitignore b/code/serveur/php/.gitignore
@@ -1 +1,2 @@
db.old
+log.txt
diff --git a/code/serveur/php/pticlic.php b/code/serveur/php/pticlic.php
@@ -75,11 +75,16 @@ if ($action == 3) {
/** Selectionne aléatoirement un noeud.
*/
-function random_node()
+function random_center_node()
{
global $db;
+ return $db->querySingle("select eid from random_center_node where rowid = (abs(random()) % (select max(rowid) from random_center_node))+1;");
+}
- 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 random_cloud_node()
+{
+ global $db;
+ return $db->querySingle("select eid from random_cloud_node where rowid = (abs(random()) % (select max(rowid) from random_cloud_node))+1;");
}
@@ -99,7 +104,6 @@ function cg_build_result_sets($cloudSize, $centerEid, $r1, $r2)
// 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)
@@ -153,7 +157,7 @@ function cg_build_result_sets($cloudSize, $centerEid, $r1, $r2)
for ($i = 0; $i < 10; $i++)
{
- $sources[$k]['resultSet'][] = array('eid'=>random_node(), 'r1'=>0, 'r2'=>0, 'r0'=>0, 'trash'=>1);
+ $sources[$k]['resultSet'][] = array('eid'=>random_cloud_node(), 'r1'=>0, 'r2'=>0, 'r0'=>0, 'trash'=>1);
$sources[$k]['rsSize']++;
}
}
@@ -254,7 +258,7 @@ function cg_build_cloud($cloudSize, $sources, $sumWeights)
while ($i < $cloudSize)
{
$totalDifficulty += $sources['rand']['d'];
- $cloud[$i] = array('pos'=>$i++, 'd'=>$sources['rand']['d'], 'eid'=>random_node(), 'probaR1'=>$res['r1'], 'probaR2'=>$res['r2'], 'probaR0'=>$res['r0'], 'probaTrash'=>$res['trash']);
+ $cloud[$i] = array('pos'=>$i++, 'd'=>$sources['rand']['d'], 'eid'=>random_cloud_node(), 'probaR1'=>$res['r1'], 'probaR2'=>$res['r2'], 'probaR0'=>$res['r0'], 'probaTrash'=>$res['trash']);
}
return array($cloud, $totalDifficulty);
@@ -400,7 +404,7 @@ function createGameCore($cloudSize)
global $db;
// select random node
- $centerEid = random_node();
+ $centerEid = random_center_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];
