// the username that you created, or were given, to access your database
$dbpass = "******";
// the password that you created, or were given, to access your database
mysql_connect($dbhost, $dbuser, $dbpass) or die("MySQL Error 1: " . mysql_error());
mysql_select_db($dbname) or die("MySQL Error 2: " . mysql_error());
function strip_cdata($str)
{
return preg_replace('/<!\\[CDATA\\[(.*)\\]\\]>/', '$1', $str);
}
$result = mysql_query("SELECT * FROM " . $dbname . ".Log");
while ($record = mysql_fetch_assoc($result)) {
$pmid = $record['PMID'];
$result2 = mysql_query("SELECT * FROM " . $dbname . ".Articles WHERE PMID = '" . $pmid . "'");
$record2 = mysql_fetch_assoc($result2);
$index = intval($record['Experiment']);
$exs = json_decode(strip_cdata($record2["Experiments"]));
echo $record['PMID'] . " '" . $record['Type'] . "'\n";
if ($index == -1) {
$id = -1;
} else {
if ($index < count($exs)) {
$id = $exs[$index]->id;
} else {
$id = $index;
}
}
$q = "UPDATE " . $dbname . ".Log SET Experiment='" . $id . "' ";
$q .= "WHERE UniqueID='" . $record['UniqueID'] . "'";
$result3 = mysql_query($q);
mysql_query($q) or die("ERROR: Unable to process query: " . $q . "\n");
mysql_free_result($result2);
function add_log($query)
{
global $dbname;
global $connection;
header("Access-Control-Allow-Origin: *");
switch ($query['type']) {
case "Vote":
$vote = $query['TagVote'];
// 1. Log user's vote
//-------------------
// Update/Insert user's vote
$type = "Vote;" . $query['TagOntology'] . ";" . $query['TagName'];
$q = "SELECT Data FROM Log WHERE";
$q .= " UserName = '******'UserName'] . "' AND";
$q .= " PMID = '" . $query['PMID'] . "' AND";
$q .= " Experiment = '" . $query['Experiment'] . "' AND";
$q .= " Type = '" . $type . "'";
$result = mysqli_query($connection, $q);
if (mysqli_num_rows($result) >= 1) {
$record = mysqli_fetch_assoc($result);
$prevVote = $record["Data"];
mysqli_free_result($result);
if ($vote == -1 || $vote == 1) {
// Update user's vote on tag
$q = "UPDATE Log SET Data = " . $vote . " WHERE";
$q .= " UserName = '******'UserName'] . "' AND";
$q .= " PMID = '" . $query['PMID'] . "' AND";
$q .= " Experiment = '" . $query['Experiment'] . "' AND";
$q .= " Type = '" . $type . "'";
$result = mysqli_query($connection, $q);
if ($result) {
echo "Successfully updated user's vote\n";
} else {
echo "ERROR: Unable to update user's vote: " . $q . "\n";
}
} else {
$q = "DELETE FROM Log WHERE";
$q .= " UserName = '******'UserName'] . "' AND";
$q .= " PMID = '" . $query['PMID'] . "' AND";
$q .= " Experiment = '" . $query['Experiment'] . "' AND";
$q .= " Type = '" . $type . "'";
$result = mysqli_query($connection, $q);
}
} else {
$prevVote = 0;
// Insert user's vote on tag
$q = "INSERT INTO Log (UserName,PMID,Experiment,Type,Data) VALUES(";
$q .= "'" . $query['UserName'] . "',";
$q .= "'" . $query['PMID'] . "',";
$q .= "'" . $query['Experiment'] . "',";
$q .= "'" . $type . "',";
$q .= "'" . $vote . "')";
$result = mysqli_query($connection, $q);
if ($result) {
echo "Successfully added user's vote\n";
} else {
echo "ERROR: Unable to add user's vote: " . $q . "\n";
}
}
// 2. Update/Insert article:experiment:tag's agree/disagree stats
//--------------------------------------------------------
if ($query['Experiment'] < 0) {
// <0: Article level MeSH tag
$result = mysqli_query($connection, "SELECT Metadata FROM Articles WHERE PMID = '" . $query['PMID'] . "'");
$record = mysqli_fetch_assoc($result);
$metadata = json_decode(strip_cdata($record["Metadata"]));
$tags = $metadata->meshHeadings;
// Find tag (should always be present)
$flagFound = 0;
foreach ($tags as $t) {
if ($t->name == $query['TagName']) {
$flagFound = 1;
break;
}
}
if (!isset($t->agree)) {
$t->agree = 0;
}
if (!isset($t->disagree)) {
$t->disagree = 0;
}
// Update tag statistics, based on user's vote
echo "prevvote:" . $prevVote . " vote:" . $vote . " agree:" . $t->agree . " disagree:" . $t->disagree . "</br>";
if ($prevVote != 0) {
if ($vote == -2) {
if ($prevVote == -1) {
$t->disagree--;
}
if ($prevVote == 1) {
$t->agree--;
}
} else {
if ($vote != $prevVote) {
$t->agree += $vote;
$t->disagree -= $vote;
}
}
} else {
// User is voting for the 1st time
if ($vote > 0) {
$t->agree += 1;
} else {
$t->disagree += 1;
}
}
// Update experiments
$result = mysqli_query($connection, "SELECT * FROM Articles WHERE PMID = '" . $query['PMID'] . "'");
if (mysqli_num_rows($result) >= 1) {
$q = "UPDATE Articles SET Metadata=";
$q .= "'" . json_encode($metadata) . "'";
$q .= " WHERE PMID='" . $query['PMID'] . "'";
$result2 = mysqli_query($connection, $q);
if ($result2) {
echo "SUCCESS ";
} else {
echo "ERROR: Unable to process query: " . $q;
}
// index_lucene($article);
}
mysqli_free_result($result);
} else {
// >=0: Experiment level tag
$result = mysqli_query($connection, "SELECT Experiments FROM Articles WHERE PMID = '" . $query['PMID'] . "'");
$record = mysqli_fetch_assoc($result);
$exps = json_decode(strip_cdata($record["Experiments"]));
// HISTORY: This used to be $exp=$exps[$query['Experiment']], when Log indexed experiments by array position
for ($i = 0; $i < count($exps); $i++) {
if ($exps[$i]->id == $query['Experiment']) {
break;
}
}
if ($i == count($exps)) {
echo "ERROR: Experiment ID " . $query['Experiment'] . " not found";
continue;
}
$exp = $exps[$i];
// Find tag, if already present
$flagFound = 0;
if (isset($exp->tags)) {
for ($j = 0; $j < count($exp->tags); $j++) {
$t = $exp->tags[$j];
if ($t->ontology == $query['TagOntology'] && $t->name == $query['TagName']) {
$flagFound = 1;
break;
}
}
} else {
$exp->tags = array();
}
// If tag is added for the 1st time, initialise it
if (!$flagFound) {
$t = json_decode('{"name":"' . $query['TagName'] . '","ontology":"' . $query['TagOntology'] . '","agree":0,"disagree":0}');
array_push($exp->tags, $t);
}
// Update tag statistics, based on user's vote
if ($prevVote != 0) {
if ($vote == -2) {
if ($prevVote == -1) {
$t->disagree--;
}
if ($prevVote == 1) {
$t->agree--;
}
// if user retracted the only vote, remove the tag
if ($flagFound && $t->disagree == 0 && $t->agree == 0) {
array_splice($exp->tags, $j, 1);
}
} else {
if ($vote != $prevVote) {
$t->agree += $vote;
$t->disagree -= $vote;
}
}
} else {
// User is voting for the 1st time
if ($vote > 0) {
$t->agree += 1;
} else {
$t->disagree += 1;
}
}
// Update experiments
$result = mysqli_query($connection, "SELECT * FROM Articles WHERE PMID = '" . $query['PMID'] . "'");
if (mysqli_num_rows($result) >= 1) {
$q = "UPDATE Articles SET Experiments=";
$q .= "'" . mysqli_real_escape_string($connection, json_encode($exps)) . "'";
// CDATA removed, escape_string added
$q .= " WHERE PMID='" . $query['PMID'] . "'";
$result2 = mysqli_query($connection, $q);
if ($result2) {
echo "SUCCESS ";
} else {
echo "ERROR: Unable to process query: " . $q;
}
// index_lucene($article);
}
mysqli_free_result($result);
}
break;
case "MarkTable":
// 1. Log user's vote
//-------------------
// Update/Insert user's vote
$type = "MarkTable";
$q = "SELECT Data FROM Log WHERE";
$q .= " UserName = '******'UserName'] . "' AND";
$q .= " PMID = '" . $query['PMID'] . "' AND";
$q .= " Experiment = '" . $query['Experiment'] . "' AND";
$q .= " Type = '" . $type . "'";
$result = mysqli_query($connection, $q);
if (mysqli_num_rows($result) >= 1) {
$record = mysqli_fetch_assoc($result);
$prevMark = $record["Data"];
mysqli_free_result($result);
// Update user's vote on tag
$q = "UPDATE Log SET Data = " . $query['Mark'] . " WHERE";
$q .= " UserName = '******'UserName'] . "' AND";
$q .= " PMID = '" . $query['PMID'] . "' AND";
$q .= " Experiment = '" . $query['Experiment'] . "' AND";
$q .= " Type = '" . $type . "'";
$result = mysqli_query($connection, $q);
if ($result) {
$out["userUpdate"] = "Successfully updated user's table mark";
} else {
$out["userUpdate"] = "ERROR: Unable to update user's table mark: " . $q;
}
} else {
$prevMark = -1;
// Insert user's table mark to experiment
$q = "INSERT INTO Log (UserName,PMID,Experiment,Type,Data) VALUES(";
$q .= "'" . $query['UserName'] . "',";
$q .= "'" . $query['PMID'] . "',";
$q .= "'" . $query['Experiment'] . "',";
$q .= "'" . $type . "',";
$q .= "'" . $query['Mark'] . "')";
$result = mysqli_query($connection, $q);
if ($result) {
$out["userInsert"] = "Successfully added user's table mark";
} else {
$out["userInsert"] = "ERROR: Unable to add user's table mark: " . $q;
}
}
// 2. Update/Insert article:experiment:tag's bad/ok stats
//--------------------------------------------------------
$result = mysqli_query($connection, "SELECT Experiments FROM Articles WHERE PMID = '" . $query['PMID'] . "'");
$record = mysqli_fetch_assoc($result);
$exps = json_decode(strip_cdata($record["Experiments"]));
// HISTORY: This used to be $exp=$exps[$query['Experiment']];
for ($i = 0; $i < count($exps); $i++) {
if ($exps[$i]->id == $query['Experiment']) {
break;
}
}
if ($i == count($exps)) {
echo "ERROR: Experiment ID " . $query['Experiment'] . " not found";
continue;
}
$exp = $exps[$i];
// Find previous table mark, if already present, create it otherwise
if (isset($exp->markBadTable)) {
$m = $exp->markBadTable;
} else {
$m = json_decode('{"bad":0,"ok":0}');
$exp->markBadTable = $m;
}
// Update table mark count, based on user's table mark
$mark = $query['Mark'];
if (!isset($m->bad)) {
$m->bad = 0;
}
if (!isset($m->ok)) {
$m->ok = 0;
}
// User is changing mind
if ($prevMark == -1) {
// User is voting for the 1st time
if ($mark == 1) {
$m->bad += 1;
} else {
$m->ok += 1;
}
} else {
if ($mark != $prevMark) {
$m->bad += 2 * $mark - 1;
$m->ok += 1 - 2 * $mark;
}
}
$out["result"] = $m;
// Update experiments
$result = mysqli_query($connection, "SELECT * FROM Articles WHERE PMID = '" . $query['PMID'] . "'");
if (mysqli_num_rows($result) >= 1) {
$q = "UPDATE Articles SET Experiments=";
$q .= "'" . mysqli_real_escape_string($connection, json_encode($exps)) . "'";
// cdata removed, escape_string added
$q .= " WHERE PMID='" . $query['PMID'] . "'";
$result2 = mysqli_query($connection, $q);
if ($result2) {
$out["articleUpdate"] = "Successfully updated experiment with table mark ";
} else {
$out["articleUpdate"] = "ERROR: Unable to add table mark: " . $q;
}
// index_lucene($article);
}
mysqli_free_result($result);
echo json_encode($out);
break;
case "StereoSpace":
// 1. Log user's vote
//-------------------
// Update/Insert user's vote
$type = "StereoSpace";
$q = "SELECT Data FROM Log WHERE";
$q .= " UserName = '******'UserName'] . "' AND";
$q .= " PMID = '" . $query['PMID'] . "' AND";
$q .= " Experiment = '-1' AND";
$q .= " Type = '" . $type . "'";
$result = mysqli_query($connection, $q);
if (mysqli_num_rows($result) >= 1) {
$record = mysqli_fetch_assoc($result);
$prevSpace = $record["Data"];
mysqli_free_result($result);
// Update user's vote on tag
$q = "UPDATE Log SET Data = '" . $query['StereoSpace'] . "' WHERE";
$q .= " UserName = '******'UserName'] . "' AND";
$q .= " PMID = '" . $query['PMID'] . "' AND";
$q .= " Experiment = '-1' AND";
$q .= " Type = '" . $type . "'";
$result = mysqli_query($connection, $q);
if ($result) {
$out["userUpdate"] = "Successfully updated user's stereotaxic space";
} else {
$out["userUpdate"] = "ERROR: Unable to update user's stereotaxic space: " . $q;
}
} else {
$prevSpace = -1;
// Insert user's table mark to experiment
$q = "INSERT INTO Log (UserName,PMID,Experiment,Type,Data) VALUES(";
$q .= "'" . $query['UserName'] . "',";
$q .= "'" . $query['PMID'] . "',";
$q .= "'-1',";
$q .= "'" . $type . "',";
$q .= "'" . $query['StereoSpace'] . "')";
$result = mysqli_query($connection, $q);
if ($result) {
$out["userInsert"] = "Successfully added user's stereotaxic space";
} else {
$out["userInsert"] = "ERROR: Unable to add user's stereotaxic space: " . $q;
}
}
// 2. Update/Insert article stereotaxic space
//-------------------------------------------
$result = mysqli_query($connection, "SELECT Metadata FROM Articles WHERE PMID = '" . $query['PMID'] . "'");
$record = mysqli_fetch_assoc($result);
$meta = json_decode($record["Metadata"]);
// Find previous stereotaxic space data, if already present, create it otherwise
if (isset($meta->stereo)) {
$m = $meta->stereo;
} else {
$m = json_decode('{"Talairach":0,"MNI":0}');
$meta->stereo = $m;
}
// Update stereotaxic space counts, based on user's stereotaxic space
$space = $query['StereoSpace'];
if (!isset($m->Talairach)) {
$m->Talairach = 0;
}
if (!isset($m->MNI)) {
$m->MNI = 0;
}
// User is changing her mind
if ($prevSpace == -1) {
// User is voting for the 1st time
$m->{$space}++;
} else {
if ($space != $prevSpace) {
$m->{$prevSpace}--;
$m->{$space}++;
}
}
$out["result"] = $m;
// Update experiments
$result = mysqli_query($connection, "SELECT * FROM Articles WHERE PMID = '" . $query['PMID'] . "'");
if (mysqli_num_rows($result) >= 1) {
$q = "UPDATE Articles SET Metadata=";
$q .= "'" . mysqli_real_escape_string($connection, json_encode($meta)) . "'";
$q .= " WHERE PMID='" . $query['PMID'] . "'";
$result2 = mysqli_query($connection, $q);
if ($result2) {
$out["articleUpdate"] = "Successfully updated article with stereotaxic space ";
} else {
$out["articleUpdate"] = "ERROR: Unable to add stereotaxic space: " . $q;
}
// index_lucene($article);
}
mysqli_free_result($result);
echo json_encode($out);
break;
case "NSubjects":
// 1. Log user's nsubjects
//-------------------
// Update/Insert user's nsubjects
$type = "NSubjects";
$q = "SELECT Data FROM Log WHERE";
$q .= " UserName = '******'UserName'] . "' AND";
$q .= " PMID = '" . $query['PMID'] . "' AND";
$q .= " Experiment = '-1' AND";
$q .= " Type = '" . $type . "'";
$result = mysqli_query($connection, $q);
if (mysqli_num_rows($result) >= 1) {
$record = mysqli_fetch_assoc($result);
$prevNSubjects = $record["Data"];
mysqli_free_result($result);
// Update user's vote on tag
$q = "UPDATE Log SET Data = '" . $query['NSubjects'] . "' WHERE";
$q .= " UserName = '******'UserName'] . "' AND";
$q .= " PMID = '" . $query['PMID'] . "' AND";
$q .= " Experiment = '-1' AND";
$q .= " Type = '" . $type . "'";
$result = mysqli_query($connection, $q);
if ($result) {
$out["userUpdate"] = "Successfully updated user's nsubjects";
} else {
$out["userUpdate"] = "ERROR: Unable to update user's nsubjects: " . $q;
}
} else {
$prevNSubjects = -1;
// Insert user's table mark to experiment
$q = "INSERT INTO Log (UserName,PMID,Experiment,Type,Data) VALUES(";
$q .= "'" . $query['UserName'] . "',";
$q .= "'" . $query['PMID'] . "',";
$q .= "'-1',";
$q .= "'" . $type . "',";
$q .= "'" . $query['NSubjects'] . "')";
$result = mysqli_query($connection, $q);
if ($result) {
$out["userInsert"] = "Successfully added user's nsubjects";
} else {
$out["userInsert"] = "ERROR: Unable to add user's nsubjects: " . $q;
}
}
// 2. Update/Insert article nsubjects
//-------------------------------------------
$result = mysqli_query($connection, "SELECT Metadata FROM Articles WHERE PMID = '" . $query['PMID'] . "'");
$record = mysqli_fetch_assoc($result);
$meta = json_decode($record["Metadata"]);
// Find previous stereotaxic space data, if already present, create it otherwise
if (!isset($meta->nsubjects)) {
$meta->nsubjects = array();
}
// Update nsubjects array, based on user's nsubjects
$nsubjects = $query['NSubjects'];
// User is changing her mind
if ($prevNSubjects == -1) {
// User is voting for the 1st time
array_push($meta->nsubjects, $nsubjects);
} else {
if ($nsubjects != $prevNSubjects) {
/* find $prevNSubjects among previous values and change it by $nsubjects */
$key = array_search($prevNSubjects, $meta->nsubjects);
$meta->nsubjects[$key] = $nsubjects;
}
}
$out["result"] = $meta->nsubjects;
// Update experiments
$result = mysqli_query($connection, "SELECT * FROM Articles WHERE PMID = '" . $query['PMID'] . "'");
if (mysqli_num_rows($result) >= 1) {
$q = "UPDATE Articles SET Metadata=";
$q .= "'" . mysqli_real_escape_string($connection, json_encode($meta)) . "'";
$q .= " WHERE PMID='" . $query['PMID'] . "'";
$result2 = mysqli_query($connection, $q);
if ($result2) {
$out["articleUpdate"] = "Successfully updated article with nsubjects";
} else {
$out["articleUpdate"] = "ERROR: Unable to add nsubjects: " . $q;
}
// index_lucene($article);
}
mysqli_free_result($result);
echo json_encode($out);
break;
case "Comment":
// 1. Log user's comment
//----------------------
$type = "Comment";
$q = "INSERT INTO Log (UserName,PMID,Experiment,Type,Data) VALUES(";
$q .= "'" . $query['UserName'] . "',";
$q .= "'" . $query['PMID'] . "',";
$q .= "'-1',";
$q .= "'" . $type . "',";
$q .= "'" . $query['Comment'] . "')";
$result = mysqli_query($connection, $q);
if ($result) {
$out["userInsert"] = "Successfully added user's comment";
} else {
$out["userInsert"] = "ERROR: Unable to add user's comment: " . $q;
}
// 2. Insert comment in the article
//---------------------------------
$result = mysqli_query($connection, "SELECT Metadata FROM Articles WHERE PMID = '" . $query['PMID'] . "'");
$record = mysqli_fetch_assoc($result);
$meta = json_decode($record["Metadata"]);
// Find previous comments, if already present, create it otherwise
if (!isset($meta->comments)) {
$meta->comments = array();
}
// Add new comment
//$comment=json_decode(stripslashes($query['Comment']));
$comment = json_decode($query['Comment']);
array_push($meta->comments, $comment);
$out["result"] = json_encode($comment);
// Update metadata
$result = mysqli_query($connection, "SELECT * FROM Articles WHERE PMID = '" . $query['PMID'] . "'");
if (mysqli_num_rows($result) >= 1) {
$q = "UPDATE Articles SET Metadata=";
$q .= "'" . mysqli_real_escape_string($connection, json_encode($meta)) . "'";
$q .= " WHERE PMID='" . $query['PMID'] . "'";
$result2 = mysqli_query($connection, $q);
if ($result2) {
$out["articleUpdate"] = "Successfully updated article with comment";
} else {
$out["articleUpdate"] = "ERROR: Unable to add comment: " . $q;
}
// index_lucene($article);
}
mysqli_free_result($result);
echo json_encode($out);
break;
case "KeyValue":
$type = $query["Key"];
$data = $query["Value"];
if (!isset($query['store'])) {
$store = "append";
} else {
$store = $query['store'];
}
/* 2 types: append and replace, append by default */
if ($store == "replace") {
$q = "SELECT * FROM Log WHERE UserName = '******' AND PMID = '" . $query["PMID"] . "' AND Experiment = '" . $query["Experiment"] . "' AND Type = '" . $type . "'";
$result = mysqli_query($connection, $q);
if ($result) {
$q = "DELETE FROM Log WHERE UserName = '******'UserName'] . "' AND PMID = '" . $query['PMID'] . "' AND Experiment = '" . $query['Experiment'] . "' AND Type = '" . $type . "'";
$result = mysqli_query($connection, $q);
}
}
$q = "INSERT INTO Log (UserName,PMID,Experiment,Type,Data) VALUES(";
$q .= "'" . $query['UserName'] . "',";
$q .= "'" . $query['PMID'] . "',";
$q .= "'" . $query['Experiment'] . "',";
$q .= "'" . $type . "',";
$q .= "'" . $data . "')";
$result = mysqli_query($connection, $q);
if ($result) {
$out["userInsert"] = "Successfully logged key/value: " . $q;
} else {
$out["userInsert"] = "ERROR: Unable log key/value: " . $q;
}
echo json_encode($out);
}
}
print_r($metadata);
echo "<br /><br />";
}
if (count($metadata["meshHeadings"]) == 0) {
echo "<b>WARNING</b>: MeSH descriptors not available<br />";
}
/*------------------------------/
/ Upload to database /
/------------------------------*/
$addToLucene = 0;
if ($addToMySQL) {
$result = mysqli_query($connection, "SELECT * FROM " . $dbname . ".Articles WHERE PMID = '" . $pmid . "'");
if (mysqli_num_rows($result) >= 1) {
$row = mysqli_fetch_array($result);
// Check for MeSH descriptors
$m = json_decode(strip_cdata($row["Metadata"]));
if (count($m->meshHeadings) == 0) {
echo "<b>The article with PMID [" . $pmid . "] does not have MeSH descriptors</b><br />";
if (count($metadata["meshHeadings"]) != 0) {
$m->meshHeadings = $metadata["meshHeadings"];
$q = "UPDATE " . $dbname . ".Articles SET Metadata = '" . mysqli_real_escape_string($connection, json_encode($m)) . "' WHERE PMID = '" . $pmid . "'";
$result2 = mysqli_query($connection, $q);
$addToLucene = 1;
echo "<b>MeSH descriptors were added from brainspell.xml</b><br />";
} else {
echo "<b>The are no MeSH descriptors for this article in brainspell.xml either</b><br />";
}
}
// Check for DOI
if ($row["DOI"] == "") {
echo "<b>The article with PMID [" . $pmid . "] does not have a DOI</b><br />";
$doi = $record['DOI'];
$exs = $record['Experiments'];
$title = $record['Title'];
echo "PMID: " . $pmid . "\n";
if ($pmid == "") {
var_dump($record);
continue;
}
// Decode experiments cdata and add experiment ids if required
$exs = json_decode(strip_cdata($exs));
for ($i = 0; $i < count($exs); $i++) {
$ex = $exs[$i];
if ($ex->id == "") {
$ex->id = 90000 + $i;
}
}
// Decode title cdata
$title = strip_cdata($title);
$q = "UPDATE " . $dbname . ".Articles SET ";
$q .= "Title='" . mysql_real_escape_string($title) . "', ";
$q .= "Experiments='" . mysql_real_escape_string(json_encode($exs)) . "' ";
$q .= " WHERE PMID='" . $pmid . "'";
$result2 = mysql_query($q);
if ($result2) {
echo "SUCCESS\n";
} else {
echo "ERROR: Unable to process query: " . $q . "\n";
var_dump($record);
}
}
mysql_free_result($result);
