function article_get_summary($id)
{
if (ereg("^[0-9]+\$", $id)) {
$id = "PMID:{$id}";
}
$html = theDb()->getOne("SELECT article_summary FROM articles WHERE article_id=?", array($id));
if (theDb()->isError($html)) {
article_create_tables();
$html = NULL;
}
if ($html === NULL) {
// Fetch summary from external source
if (ereg("^PMID:", $id)) {
$html = pubmed_get_summary($id);
}
// (Support other article reference types here)
// Update the cache
if ($html !== NULL) {
theDb()->query("REPLACE INTO articles SET article_id=?, article_summary=?, article_summary_retrieved=NOW()", array($id, $html));
}
}
if ($html === NULL) {
return $id;
}
$html = preg_replace('{(\\. )([^\\.]+)(\\. )}', '$1<strong>$2</strong>$3', $html, 1);
$html = article_add_external_links($html);
return $html;
}
function seealso_related($gene, $aa_pos, $skip_variant_id)
{
$related_variants = theDb()->getAll("SELECT v.variant_gene gene, CONCAT(v.variant_aa_from,v.variant_aa_pos,v.variant_aa_to) aa_long FROM variants v WHERE v.variant_gene=? AND v.variant_aa_pos=? AND v.variant_id <> ?", array($gene, $aa_pos, $skip_variant_id));
$seealso = "";
foreach ($related_variants as $x) {
$x["aa_short"] = aa_short_form($x["aa_long"]);
$seealso .= "<LI>See also: <A href=\"" . $x["gene"] . "-" . $x["aa_short"] . "\">" . $x["gene"] . " " . $x["aa_short"] . "</A></LI>\n";
}
if ($seealso) {
$seealso = "<DIV id=\"seealso\"><UL>{$seealso}</UL></DIV>\n";
}
return $seealso;
}
function sqlflush(&$sql, &$sqlparam)
{
if (count($sqlparam) == 0) {
$sql = "";
return;
}
$sql = ereg_replace(',$', '', $sql);
$q = theDb()->query("REPLACE INTO flat_summary (variant_id, flat_summary, autoscore, webscore, n_genomes) VALUES {$sql}", $sqlparam);
if (theDb()->isError($q)) {
die($q->getMessage());
}
$sql = "";
$sqlparam = array();
}
function print_content($x)
{
global $report_title;
print "<h1>{$report_title}</h1>\n\n";
$q = theDb()->query("SELECT edit_oid, count(*) edit_count, UNIX_TIMESTAMP(MAX(edit_timestamp)) t, eb_users.* FROM edits\n\tLEFT JOIN eb_users ON edit_oid=oid\n\tWHERE is_draft=0\n\tGROUP BY edit_oid\n\tORDER BY edit_timestamp DESC");
if (theDb()->isError($q)) {
die($q->getMessage());
}
print "<UL>";
while ($row =& $q->fetchRow()) {
print "<LI>";
print "<A href=\"edits?oid=" . urlencode($row["edit_oid"]) . "\">" . htmlspecialchars($row["fullname"] ? $row["fullname"] : $row["nickname"]) . "</A>";
print " -- " . $row["edit_count"] . " edits";
print strftime(" -- latest %b %e", $row["t"]);
print "</LI>\n";
}
print "</UL>\n";
}
// Copyright 2009 Scalable Computing Experts, Inc.
// Author: Tom Clegg
if ($_SERVER["argc"] != 2) {
die("Usage: " . $_SERVER["argv"][0] . " ns.json\n");
}
chdir('public_html');
require_once 'lib/setup.php';
require_once 'lib/genomes.php';
require_once 'lib/openid.php';
require_once 'lib/bp.php';
ini_set("output_buffering", FALSE);
ini_set("memory_limit", 67108864);
print "Creating/updating get-evidence tables...";
evidence_create_tables();
print "\n";
print "Inserting/replacing rows in allele_frequency...";
$json = file_get_contents($_SERVER["argv"][1]);
$json = "[" . ereg_replace("}\n{", "},\n{", $json) . "]";
$in = json_decode($json);
$done = 0;
foreach ($in as $row) {
if (!sizeof($row->taf)) {
continue;
}
if (isset($row->taf->all_n) && $row->taf->all_d > 0 && $row->taf->all_d >= $row->taf->all_n) {
theDb()->query("REPLACE INTO allele_frequency\n\t\t(chr, chr_pos, allele, dbtag, num, denom)\n\t\tVALUES (?, ?, ?, ?, ?, ?)", array($row->chromosome, $row->coordinates, $row->trait_allele, 'HapMap', $row->taf->all_n, $row->taf->all_d));
$done += theDb()->affectedRows();
print ".";
}
}
print "{$done}\n";
function yahoo_boss_update_external($variant_id)
{
$cache = yahoo_boss_lookup($variant_id);
if (!$cache) {
print "No search results\n";
return;
}
$variant = theDb()->getRow("SELECT v.*, vo.rsid rsid FROM variants v\n\t\t\t\t LEFT JOIN variant_occurs vo\n\t\t\t\t ON v.variant_id=vo.variant_id\n\t\t\t\t AND vo.rsid IS NOT NULL\n\t\t\t\t WHERE v.variant_id=?\n\t\t\t\t GROUP BY v.variant_id", array($variant_id));
if (!$variant || theDb()->isError($variant)) {
print "No such variant\n";
return FALSE;
}
if ($variant["variant_gene"]) {
$gene_aa_long = $variant["variant_gene"] . " " . $variant["variant_aa_from"] . $variant["variant_aa_pos"] . $variant["variant_aa_to"];
$gene_aa_short = $variant["variant_gene"] . " " . aa_short_form($variant["variant_aa_from"]) . $variant["variant_aa_pos"] . aa_short_form($variant["variant_aa_to"]);
$search_string = "{$gene_aa_long} OR {$gene_aa_short}";
if (($rsid = $variant["variant_rsid"]) || ($rsid = $variant["rsid"])) {
$search_string .= " OR rs{$rsid}";
}
} else {
$search_string = "rs" . $variant["variant_rsid"];
}
$user_url = "http://search.yahoo.com/search?p=" . urlencode($search_string);
$content = "";
$skipped_hits = 0;
if ($cache["hitcount"] > 0) {
preg_match_all('{<result>.*?</result>}is', $cache["xml"], $matches, PREG_PATTERN_ORDER);
foreach ($matches[0] as $result) {
$resulttag = array();
foreach (array("url", "dispurl", "abstract", "title") as $t) {
if (preg_match("{<{$t}>(.*?)</{$t}>}i", $result, $regs)) {
$resulttag[$t] = preg_replace('{<\\!\\[CDATA\\[(.*?)\\]\\]>}s', '$1', $regs[1]);
} else {
$resulttag = FALSE;
continue;
}
}
if (ereg("snp\\.med\\.harvard\\.edu|evidence\\.personalgenomes\\.org", $resulttag["url"])) {
$skipped_hits++;
continue;
}
if ($resulttag) {
$content .= "<LI><A href=\"" . $resulttag["url"] . "\">" . $resulttag["title"] . "</A><BR />" . $resulttag["abstract"] . "<BR /><DIV class=\"searchurl\">" . $resulttag["dispurl"] . "</DIV></LI>\n";
}
}
}
// If we skipped some hits (because they point to this page or
// Trait-o-matic), subtract them from the cached hitcount so "no
// web results except this page" gets counted as 0 for
// statistics/display.
if ($skipped_hits > 0 && preg_match('/<resultset_web\\b[^<]*\\sdeephits="?(\\d+)"?/s', $cache["xml"], $regs) && $regs[1] >= $skipped_hits) {
$hitcount = $regs[1] - $skipped_hits;
if ($hitcount != $cache["hitcount"]) {
$cache["hitcount"] = $hitcount;
theDb()->query("UPDATE yahoo_boss_cache SET hitcount=? WHERE variant_id=?", array($hitcount, $variant_id));
}
}
// Build html display for variant page
$content = "<UL><STRONG>Web search results (" . $cache["hitcount"] . " hit" . ($cache["hitcount"] == 1 ? "" : "s") . " -- <A href=\"" . $user_url . "\">see all</A>)</STRONG>" . $content . "</UL>";
theDb()->query("DELETE FROM variant_external WHERE variant_id=? AND tag=?", array($variant_id, "Yahoo!"));
$q = theDb()->query("INSERT INTO variant_external SET variant_id=?, tag=?, content=?, url=NULL, updated=NOW()", array($variant_id, "Yahoo!", $content));
}
if (aa_sane($_POST["variant_aa_change"])) {
if (ereg("^([^0-9]+)([0-9]+)([^0-9]+)\$", aa_long_form($_POST["variant_aa_change"]), $regs)) {
$aa_from = $regs[1];
$aa_pos = $regs[2];
$aa_to = $regs[3];
}
$gene = strtoupper($_POST["variant_gene"]);
$variant_id = evidence_get_variant_id($gene, $aa_pos, $aa_from, $aa_to, true);
$edit_id = evidence_get_latest_edit($variant_id, 0, 0, 0, true);
$response["latest_edit_v{$variant_id}a0g0"] = $edit_id;
$response["latest_edit_id"] = $edit_id;
$response["variant_id"] = $variant_id;
$response["please_reload"] = true;
$response["variant_key"] = "{$gene} " . aa_short_form("{$aa_from}{$aa_pos}{$aa_to}");
} else {
die("Invalid variant specified");
}
}
if ($article_pmid || $genome_id || $disease_id) {
$latest_edit_id = evidence_get_latest_edit($variant_id, $article_pmid, $genome_id, $disease_id, true);
$response["latest_edit_v{$variant_id}a{$article_pmid}g{$genome_id}"] = $latest_edit_id;
$response["latest_edit_id"] = $latest_edit_id;
$renderer = new evidence_row_renderer();
$renderer->render_row(theDb()->getRow("SELECT * FROM edits WHERE edit_id=?", array($latest_edit_id)));
$response["html"] = $renderer->html();
ereg("id=\"([^\"]+)", $response["html"], $regs);
$response["e_id"] = $regs[1];
}
header("Content-type: application/json");
print json_encode($response);
}
include "lib/setup.php";
$response = array();
if (!getCurrentUser()) {
$response["errors"][] = "Not logged in";
} else {
if ($item_id = evidence_get_latest_edit($_POST[v], $_POST[a], $_POST[g], $_POST[d])) {
$q = theDb()->query("INSERT INTO edits SET\n\t\t\tedit_oid=?, edit_timestamp=NOW(),\n\t\t\tis_draft=1, is_delete=1,\n\t\t\tprevious_edit_id=?,\n\t\t\tvariant_id=?,\n\t\t\tarticle_pmid=?, genome_id=?, disease_id=?", array(getCurrentUser("oid"), $item_id, $_POST[v], $_POST[a], $_POST[g], $_POST[d]));
if (theDb()->isError($q)) {
$response["errors"][] = $q->getMessage();
} else {
if ($delete_id = theDb()->getOne("SELECT LAST_INSERT_ID()")) {
evidence_submit($delete_id);
$response["deleted"] = true;
if ($_POST[v] && ($_POST[a] || $_POST[g]) && !$_POST[d]) {
// Delete per-disease entries for an article/genome entry
// TODO: evidence_submit() should take care of this
// TODO: snap_release will need special handling for this case too
theDb()->query("DELETE FROM snap_latest WHERE variant_id=? AND article_pmid=? AND genome_id=?", array($_POST[v], $_POST[a], $_POST[g]));
}
}
}
} else {
$response["errors"][] = "Nothing to delete";
}
}
header("Content-type: application/json");
print json_encode($response);
?>
function print_content($x)
{
global $report_title, $where_sql, $where_param, $orderby_sql;
if (!$_GET["bareli"]) {
print "<h1>{$report_title}</h1>\n\n";
}
$sql_limit = $_GET["all"] ? "" : "LIMIT 300";
$q = theDb()->query("SELECT *, diseases.disease_name disease_name, edits.genome_id genome_id, UNIX_TIMESTAMP(edit_timestamp) t FROM edits\n\tLEFT JOIN eb_users ON edit_oid=oid\n\tLEFT JOIN variants ON variants.variant_id=edits.variant_id\n\tLEFT JOIN genomes ON edits.genome_id>0 AND edits.genome_id=genomes.genome_id\n\tLEFT JOIN diseases ON diseases.disease_id=edits.disease_id\n\tWHERE {$where_sql} AND is_draft=0\n\tORDER BY {$orderby_sql}\n\t{$sql_limit}", $where_param);
if (theDb()->isError($q)) {
die($q->getMessage());
}
$relevant_fields = array("is_delete", "variant_id", "article_pmid", "genome_id", "disease_id", "edit_oid");
$lastrow = FALSE;
$output_count = 0;
if (!$_GET["bareli"]) {
print "<UL>";
}
while ($row =& $q->fetchRow()) {
if ($lastrow && arrays_partially_equal($lastrow, $row, $relevant_fields) && $lastrow["previous_edit_id"] > 0 == $row["previous_edit_id"] > 0) {
continue;
}
$variant_link = evidence_get_variant_name($row, " ", true);
$version_link = "<A href=\"" . evidence_get_variant_name(&$row, "-") . ";{$row['edit_id']}\">view</A>";
if (!$lastrow || $lastrow[variant_id] != $row[variant_id]) {
$variant_link = "<A href=\"" . evidence_get_variant_name(&$row, "-") . "\">{$variant_link}</A>";
}
$lastrow = $row;
print "<LI>";
print strftime("%b %e ", $row["t"]);
print $variant_link;
if ($row["is_delete"] && !$row["article_pmid"] && !$row["genome_id"] && !$row["disease_id"]) {
print " deleted by ";
} else {
if (!$row["previous_edit_id"] && !$row["article_pmid"] && !$row["genome_id"]) {
print " added by ";
} else {
print " edited by ";
}
}
print "<A href=\"edits?oid=" . urlencode($row["edit_oid"]) . "\">" . htmlspecialchars($row["fullname"] ? $row["fullname"] : $row["nickname"]) . "</A>";
if ($row["genome_id"]) {
if (!($genome_name = $row["name"])) {
if (!($genome_name = $row["global_human_id"])) {
$genome_name = "#" . $row["genome_id"];
}
}
}
$genome_name = htmlspecialchars($genome_name);
if ($row["article_pmid"] && $row["is_delete"]) {
print " (PMID {$row['article_pmid']} removed)";
} else {
if ($row["genome_id"] && $row["is_delete"]) {
print " ({$genome_name} removed)";
} else {
if ($row["article_pmid"] && !$row["previous_edit_id"]) {
print " (PMID {$row['article_pmid']} added)";
} else {
if ($row["genome_id"] && !$row["previous_edit_id"]) {
print " ({$genome_name} added)";
} else {
if ($row["disease_id"]) {
print " (" . htmlspecialchars($row["disease_name"]) . ")";
}
}
}
}
}
print " -- {$version_link}";
print "</LI>\n";
if (!$_GET["all"] && ++$output_count >= 30) {
print "<SPAN> <BR />\n";
foreach (array("", "&all=1") as $all) {
print "<BUTTON onclick=\"\$('busysignal').removeClassName('csshide'); this.disabled = true; new Ajax.Updater (this.parentNode, 'edits?" . htmlentities(ereg_replace('&?(before_edit_id|bareli)=[0-9]+', '', $_SERVER[QUERY_STRING])) . "&before_edit_id={$row[edit_id]}&bareli=1{$all}', { onFailure: function() { this.disabled = false; \$('busysignal').addClassName('csshide'); } }); return false;\">" . ($all == "" ? "Next page" : "Show all") . "</BUTTON> \n";
}
print " <IMG style=\"vertical-align: middle;\" id=\"busysignal\" class=\"csshide\" src=\"/img/busy.gif\" width=\"16\" height=\"16\" alt=\"\" /></SPAN>\n";
break;
}
}
if (!$_GET["bareli"]) {
print "</UL>\n";
}
}
$newrow["variant_impact"] = "pharmacogenetic";
$new_edit_id = evidence_save_draft(null, $newrow);
evidence_submit($new_edit_id);
++$n_marked_pharma;
}
if (ereg('^[0-9]+$', $row["pmid"])) {
evidence_get_latest_edit($variant_id, $row["pmid"], 0, 0, true);
}
$did[$variant_name] = $variant_id;
theDb()->query("UPDATE pharmgkb SET variant_id=? WHERE pharmgkb_id=?", array($variant_id, $row["pharmgkb_id"]));
++$n;
if ($n % 100 == 0) {
print "{$n}...";
}
}
print "{$n} ({$n_marked_pharma} existing variants changed to pharmacogenetic)\n";
print "Updating variant_external...";
theDb()->query("LOCK TABLES variant_external WRITE");
theDb()->query("DELETE FROM variant_external WHERE tag='PharmGKB'");
$q = theDb()->query("INSERT INTO variant_external\n (variant_id, tag, content, url, updated)\n SELECT variant_id, 'PharmGKB', annotation, url, NOW()\n FROM pharmgkb\n WHERE variant_id > 0");
if (theDb()->isError($q)) {
print "[" . $q->getMessage() . "]";
}
print theDb()->affectedRows();
print "\n";
theDb()->query("UNLOCK TABLES");
if (getenv("DEBUG")) {
theDb()->query("DROP TABLE IF EXISTS pharmgkb_last");
theDb()->query("CREATE TABLE IF NOT EXISTS pharmgkb_last LIKE pharmgkb");
theDb()->query("INSERT INTO pharmgkb_last SELECT * FROM pharmgkb");
}
}
}
}
if ($compressed > 0) {
print "removed {$compressed}\nCompressing paths...";
}
}
print "done\n";
$g_sql = "";
$g_sql_param = array();
$out = 0;
foreach ($official as $aka => $canonical) {
$g_sql .= "(?, ?), ";
array_push($g_sql_param, $aka, $canonical);
++$out;
}
print "Strung together {$out} outputs\n";
if (!$out) {
exit;
}
print "Importing to database...";
theDb()->query("LOCK TABLES gene_canonical_name WRITE");
theDb()->query("DELETE FROM gene_canonical_name");
$q = theDb()->query("INSERT IGNORE INTO gene_canonical_name (aka, official) VALUES " . ereg_replace(', $', '', $g_sql), $g_sql_param);
if (theDb()->isError($q)) {
die($q->getMessage());
}
print theDb()->affectedRows();
print "\n";
theDb()->query("UNLOCK TABLES");
fclose($fh);
pclose($ph);
printf("%10d input rows...", $line_count);
exit;
}
if (!($child > 0)) {
die("fork failed, giving up.\n");
}
reconnectDb();
$q = theDb()->query("CREATE TEMPORARY TABLE import_1000genomes_onefile LIKE import_1000genomes");
$q = theDb()->query("LOAD DATA LOCAL INFILE ?\n\t INTO TABLE import_1000genomes_onefile\n\t FIELDS TERMINATED BY '\t'\n\t LINES TERMINATED BY '\n'", array($fifo));
if (theDb()->isError($q)) {
print $q->getMessage();
}
print theDb()->affectedRows();
print "\n";
print "Merging...";
$q = theDb()->query("INSERT INTO import_1000genomes\n SELECT import_1000genomes_onefile.*\n FROM import_1000genomes_onefile\n LEFT JOIN variant_occurs vo\n\t ON import_1000genomes_onefile.chr=vo.chr\n\t AND import_1000genomes_onefile.chr_pos=vo.chr_pos\n\t AND import_1000genomes_onefile.allele=vo.allele\n LEFT JOIN variant_locations vl\n\t ON import_1000genomes_onefile.chr=vl.chr\n\t AND import_1000genomes_onefile.chr_pos=vl.chr_pos\n\t AND import_1000genomes_onefile.allele=vl.allele\n WHERE (vo.variant_id IS NOT NULL OR vl.variant_id IS NOT NULL)\n GROUP BY import_1000genomes_onefile.chr,\n\timport_1000genomes_onefile.chr_pos,\n\timport_1000genomes_onefile.allele\n ON DUPLICATE KEY UPDATE\n\t import_1000genomes.occur=import_1000genomes.occur+VALUES(occur),\n\t import_1000genomes.denom=import_1000genomes.denom+VALUES(denom)");
if (theDb()->isError($q)) {
print $q->getMessage();
}
print theDb()->affectedRows();
print "\n";
$q = theDb()->query("DROP TEMPORARY TABLE import_1000genomes_onefile");
}
print "Copying data into allele_frequency table...";
theDb()->query("REPLACE INTO allele_frequency\n (dbtag, chr, chr_pos, allele, num, denom)\n SELECT ?, chr, chr_pos, allele, occur, denom\n FROM import_1000genomes", array("1000g"));
print theDb()->affectedRows();
print "\n";
theDb()->query("DROP TEMPORARY TABLE variant_frequency_tmp");
print "Deleting disputed rows from variant_occurs...";
theDb()->query("DELETE v.*\nFROM variant_occurs_not n, variant_occurs v\nWHERE n.variant_id=v.variant_id\n AND n.dataset_id=v.dataset_id");
print theDb()->affectedRows();
print "\n";
// For each deleted variant+genome_id assoc, if the latest edit was
// made by this program (i.e., nobody has written any comments about
// this variant+genome_id association), and there is no longer any
// evidence in variant_occurs supporting it, add a "delete" edit and
// remove the entry from snap_latest.
print "Entering \"delete\" edits for \"genome\" comment which have no supporting evidence after deleting those disputed rows and have not been edited by users...";
$q = theDb()->query("\nINSERT IGNORE INTO edits\n(variant_id, genome_id, article_pmid, previous_edit_id, is_draft, is_delete,\n edit_oid, edit_timestamp)\nSELECT old.variant_id, old.genome_id, 0, old.edit_id, 0, 1, ?, ?\nFROM variant_occurs_not del\nLEFT JOIN datasets deld\n ON deld.dataset_id=del.dataset_id\nLEFT JOIN snap_latest old\n ON old.variant_id=del.variant_id\n AND old.article_pmid=0\n AND old.genome_id=deld.genome_id\n AND old.edit_oid=?\nLEFT JOIN variant_occurs v\n ON old.variant_id=v.variant_id\nLEFT JOIN datasets d\n ON v.dataset_id=d.dataset_id\n AND d.genome_id=deld.genome_id\nWHERE old.edit_id IS NOT NULL\n AND d.dataset_id IS NULL\nGROUP BY del.variant_id, deld.genome_id\n", array(getCurrentUser("oid"), $timestamp, getCurrentUser("oid")));
if (theDb()->isError($q)) {
print $q->getMessage();
}
print $count_removals = theDb()->affectedRows();
print "\n";
if ($count_removals > 0) {
print "Really removing them from snap_latest...";
theDb()->query("\nDELETE FROM snap_latest\nWHERE edit_id IN (SELECT previous_edit_id FROM edits WHERE edit_oid=? AND edit_timestamp=? AND is_delete=1)\n", array(getCurrentUser("oid"), $timestamp));
print theDb()->affectedRows();
print "\n";
}
// Clean up
if (getenv("DEBUG")) {
theDb()->query("DROP TABLE IF EXISTS import_genomes_last");
theDb()->query("CREATE TABLE import_genomes_last LIKE import_genomes_tmp");
theDb()->query("INSERT INTO import_genomes_last SELECT * FROM import_genomes_tmp");
}
theDb()->query("DROP TEMPORARY TABLE variant_occurs_not");
theDb()->query("DROP TEMPORARY TABLE import_genomes_tmp");
<?php
include "lib/setup.php";
include "lib/openid.php";
evidence_create_tables();
openid_create_tables();
if ($_REQUEST["test-insert"]) {
if (!getCurrentUser("is_admin")) {
die("yours is not an admin account.");
}
header("Content-type: text/plain");
$variant_id = evidence_get_variant_id("NPHP4", 848, "Arg", "Trp", true);
$e = evidence_edit_id_generate(null, $variant_id);
evidence_save_draft($e, array("variant_impact" => "unknown", "variant_dominance" => "unknown", "summary_short" => "This variant has been found together with R682X as a compound heterozygote in three nephronophthisis and retinitis pigmentosa, (Senior-Loken syndrome) patients from one family."));
print "after evidence_save_draft\n";
print_r(theDb()->getRow("SELECT * FROM edits WHERE edit_id=?", array($e)));
evidence_submit($e);
print "after evidence_submit\n";
print_r(evidence_get_report("latest", $variant_id));
evidence_signoff($e);
$a = evidence_edit_id_generate(null, $variant_id);
evidence_save_draft($a, array("article_pmid" => 12205563, "summary_short" => "Otto, E. et al. A gene mutated in nephronophthisis and retinitis pigmentosa encodes a novel protein, nephroretinin, conserved in evolution. Am J Hum Genet 71, 1161-1167, doi:S0002-9297(07)60408-X [pii]"));
evidence_submit($a);
evidence_signoff($a);
$a = evidence_edit_id_generate(null, $variant_id);
evidence_save_draft($a, array("article_pmid" => 9734597, "summary_short" => "Lemmink, H. H. et al. Novel mutations in the thiazide-sensitive NaCl cotransporter gene in patients with Gitelman syndrome with predominant localization to the C-terminal domain. Kidney Int 54, 720-730, doi:10.1046/j.1523-1755.1998.00070.x (1998)."));
evidence_submit($a);
print "release:\n";
print_r(evidence_get_report("release", $variant_id));
print "latest:\n";
print_r(evidence_get_report("latest", $variant_id));
function print_content()
{
global $config;
global $sql_where;
global $sql_having;
global $sql_occur_filter;
global $sql_orderby;
global $sql_right_join;
global $sql_params;
global $min_certainty;
global $max_certainty;
global $snap;
global $gTheTextile;
global $want_column;
$q = theDb()->query($sql = "\nSELECT s.*, v.*, g.*, fs.*,\n-- gs.summary_short AS g_summary_short,\n MAX(o.zygosity) AS max_zygosity,\n d.dataset_id AS d_dataset_id,\n g.genome_id AS g_genome_id,\n y.hitcount AS hitcount,\n vf.f AS variant_frequency\nFROM snap_{$snap} s\n{$sql_right_join}\nLEFT JOIN flat_summary fs ON s.variant_id=fs.variant_id\nLEFT JOIN variants v ON s.variant_id=v.variant_id\nLEFT JOIN variant_frequency vf ON vf.variant_id=v.variant_id\nLEFT JOIN variant_occurs o ON v.variant_id=o.variant_id AND {$sql_occur_filter}\nLEFT JOIN datasets d ON o.dataset_id=d.dataset_id\nLEFT JOIN genomes g ON d.genome_id=g.genome_id\nLEFT JOIN yahoo_boss_cache y ON s.variant_id=y.variant_id\nLEFT JOIN variant_external omim ON omim.variant_id=s.variant_id AND omim.tag='OMIM'\n-- LEFT JOIN snap_{$snap} gs ON gs.variant_id=s.variant_id AND gs.article_pmid=0 AND gs.genome_id=g.genome_id\nWHERE s.article_pmid=0 AND s.genome_id=0 AND s.disease_id=0 AND {$sql_where}\nGROUP BY v.variant_id,g.genome_id\nHAVING {$sql_having}\n{$sql_orderby}\n", $sql_params);
$colcount = 5 + count($want_column);
if (theDb()->isError($q)) {
die("DB Error: " . $q->getMessage() . "<br>" . $sql);
}
print "<TABLE class=\"report_table\" style=\"width: 100%\">\n";
print "<TR><TD colspan=\"{$colcount}\" id=\"reportpage_turner_copy\" style=\"text-align: right;\"> </TD></TR>\n";
print "<TR><TH>" . join("</TH><TH>", array("Variant", "Impact", "Inheritance pattern", "Summary", "Genomes"));
foreach ($want_column as $k => $v) {
print "</TH><TH>{$v}";
}
print "</TH></TR>\n";
$output_row = 0;
$output_page = 0;
$output_cut_off_after = 0;
$tr_attrs = "";
$genome_rows = array();
for ($row =& $q->fetchRow(); $row; $row =& $nextrow) {
$row["name"] = $row["name"] ? $row["name"] : "[" . $row["global_human_id"] . "]";
$genome_rows[$row["genome_id"]] = $row;
$nextrow =& $q->fetchRow();
if ($nextrow && $row["variant_id"] == $nextrow["variant_id"]) {
continue;
}
$certainty = evidence_compute_certainty($row["variant_quality"], $row["variant_impact"]);
$certainty = substr($certainty, 0, 1);
if ($certainty == 0 && ($min_certainty == 0 || $max_certainty == -1)) {
// need to distinguish between "insufficiently evaluated" and "uncertain"
$have_2or3 = 0;
$have_4or5 = 0;
$nonempty_scores = 0;
$category_index = -1;
$scores = str_split(str_pad($row["variant_quality"], 6, "-"));
foreach ($scores as $score) {
if ($score != "-") {
++$nonempty_scores;
}
}
if ($nonempty_scores < 4 || $scores[2] == "-" && $scores[3] == "-" || $scores[4] == "-" && $scores[5] == "-") {
$certainty = -1;
}
}
if ($min_certainty > $certainty || $max_certainty < $certainty) {
$genome_rows = array();
continue;
}
++$output_row;
if ($output_row % $config["ROWSPERPAGE"] == 1) {
++$output_page;
$tr_attrs = " class=\"reportpage reportpage_{$output_page}\"";
if ($output_page > 1) {
$tr_attrs = " class=\"reportpage reportpage_{$output_page} csshide\"";
}
}
if ($output_page > $config["MAXPAGES"]) {
if (!$output_cut_off_after) {
$output_cut_off_after = $output_row - 1;
}
$genome_rows = array();
continue;
}
$variant_name_display = evidence_get_variant_name($row, " ", true);
$variant_name_link = evidence_get_variant_name($row, "-", true);
$rowspan = count($genome_rows);
if ($rowspan < 1) {
$rowspan = 1;
}
$rowspan = "rowspan=\"{$rowspan}\"";
$impact = ereg_replace("^likely ", "l.", $row["variant_impact"]);
if ($certainty == -1) {
$impact = "insufficiently evaluated / uncertain {$impact}";
} else {
if ($certainty == 1) {
$impact = "likely {$impact}";
} else {
if ($certainty <= 0) {
$impact = "uncertain {$impact}";
}
}
}
if (strlen($row["variant_frequency"])) {
$impact .= sprintf(", f=%.3f", $row["variant_frequency"]);
}
$summary_short = $gTheTextile->textileRestricted($row["summary_short"]);
if ($row["hitcount"] > 0) {
$s = $row["hitcount"] == 1 ? "" : "s";
$summary_short .= "<P>({$row['hitcount']} web hit{$s})</P>";
}
printf("<TR{$tr_attrs}><TD {$rowspan}>%s</TD><TD {$rowspan}>%s</TD><TD {$rowspan}>%s</TD><TD {$rowspan}>%s</TD>", "<A href=\"{$variant_name_link}\">{$variant_name_display}</A>", $impact, $row["variant_dominance"], $summary_short);
$rownum = 0;
foreach ($genome_rows as $id => $row) {
if (++$rownum > 1) {
print "</TR>\n<TR{$tr_attrs}>";
}
if (!$row["g_genome_id"]) {
print "<TD></TD>";
} else {
print "<TD width=\"15%\"><A href=\"{$variant_name_link}#g{$id}\">" . htmlspecialchars($row["name"]) . "</A>";
if ($row["max_zygosity"] == 'homozygous') {
print " (hom)";
}
print "</TD>";
}
if ($rownum == 1) {
foreach ($want_column as $k => $v) {
printf("<TD {$rowspan}>%s</TD>", $row[$k]);
}
}
}
print "</TR>\n";
$genome_rows = array();
}
if ($output_page > 1) {
print "<TR><TD colspan=\"{$colcount}\" id=\"reportpage_turner\" style=\"text-align: right;\">Page: ";
for ($p = 1; $p <= $output_page && $p <= $config["MAXPAGES"]; $p++) {
print "<A class=\"reportpage_turnbutton\" href=\"#\" onclick=\"reportpage_goto({$p});\">{$p}</A> ";
}
print "<BR /><STRONG>Total results: {$output_row}</STRONG>";
if ($output_cut_off_after) {
print "<BR />(Only displaying first {$output_cut_off_after} results)";
}
print "</TD></TR>\n";
print "<SCRIPT type=\"text/javascript\"><!--\n";
print "reportpage_init();\n";
print "\n// -->\n</SCRIPT>";
} else {
print "<TR><TD colspan=\"{$colcount}\" style=\"text-align: right;\"><STRONG>Total results: {$output_row}</STRONG></TD></TR>";
}
print "</TABLE>\n";
}
protected function __construct($oid)
{
$this->row =& theDb()->getRow("SELECT * FROM eb_users WHERE oid=?", array($oid));
}
$q = theDb()->query("LOAD DATA LOCAL INFILE ?\n\t INTO TABLE dbsnp_tmp\n\t FIELDS TERMINATED BY '\t'\n\t LINES TERMINATED BY '\n'", array($fifo));
if (theDb()->isError($q)) {
die($q->getMessage());
}
print theDb()->affectedRows();
print "\n";
print "Removing chr=\"Multi\" rows...";
theDb()->query("DELETE FROM dbsnp_tmp WHERE chr=?", array("Multi"));
print theDb()->affectedRows();
print "\n";
print "Removing chr_pos=0 rows...";
theDb()->query("DELETE FROM dbsnp_tmp WHERE chr_pos=0");
print theDb()->affectedRows();
print "\n";
print "Adding \"chr\" prefix to chr column...";
theDb()->query("UPDATE dbsnp_tmp SET chr=CONCAT('chr',chr)");
print theDb()->affectedRows();
print "\n";
print "Adding 1 to chr_pos column to get 1-based coordinates...";
theDb()->query("UPDATE dbsnp_tmp SET chr_pos=chr_pos+1");
print theDb()->affectedRows();
print "\n";
print "Copying data to real dbsnp table...";
theDb()->query("LOCK TABLES dbsnp WRITE");
theDb()->query("DELETE FROM dbsnp");
theDb()->query("INSERT INTO dbsnp (id,chr,chr_pos,orient) SELECT * FROM dbsnp_tmp");
print theDb()->affectedRows();
theDb()->query("UNLOCK TABLES");
print "\n";
theDb()->query("DROP TEMPORARY TABLE dbsnp_tmp");
if ($flat["impact"] == "unknown" || $flat["impact"] == "none") {
$flat["impact"] = "not reviewed";
} else {
$flat["impact"] = ereg_replace("^likely ", "", $flat["impact"]);
}
}
foreach ($columns as $c) {
print $flat[$c] . "\t";
}
print ereg_replace("[\t\n]", " ", $row["summary_short"]) . "\n";
}
exit;
}
if ($snap) {
$q = theDb()->query("SELECT v.*, s.*,\n\t\t\tif(vo.rsid,concat('rs',vo.rsid),null) dbsnp_id,\n\t\t\tCOUNT(vo.dataset_id) genome_hits,\n\t\t\ty.hitcount web_hits,\n\t\t\tvf.num overall_frequency_n,\n\t\t\tvf.denom overall_frequency_d,\n\t\t\tvf.f overall_frequency\n\t\t\tFROM variants v\n\t\t\tLEFT JOIN snap_{$snap} s ON s.variant_id=v.variant_id\n\t\t\tLEFT JOIN variant_frequency vf ON v.variant_id=vf.variant_id\n\t\t\tLEFT JOIN variant_occurs vo ON v.variant_id=vo.variant_id\n\t\t\tLEFT JOIN yahoo_boss_cache y ON v.variant_id=y.variant_id\n\t\t\tWHERE s.variant_id IS NOT NULL\n\t\t\tAND s.article_pmid=0\n\t\t\tAND s.genome_id=0\n\t\t\tAND s.disease_id=0\n\t\t\tGROUP BY v.variant_id");
if (theDb()->isError($q)) {
header("HTTP/1.1 500 Internal server error");
die("Database error: " . $q->getMessage());
}
header("Content-type: text/tab-separated-values");
$fieldlist = array("variant_gene", "variant_aa_change", "variant_dominance", "variant_impact", "dbsnp_id", "overall_frequency_n", "overall_frequency_d", "overall_frequency", "gwas_max_or", "genome_hits", "web_hits", "summary_short");
print ereg_replace("\tvariant_", "\t", ereg_replace("variant_dominance", "variant_inheritance", implode("\t", $fieldlist)));
print "\n";
ini_set("output_buffering", true);
while ($row =& $q->fetchRow()) {
$out = "";
$row["variant_aa_change"] = $row["variant_aa_from"] . $row["variant_aa_pos"] . $row["variant_aa_to"];
foreach ($fieldlist as $field) {
$v = $row[$field];
if (strlen($out)) {
$out .= "\t";
<?php
// Copyright 2009 Scalable Computing Experts, Inc.
// Author: Tom Clegg
require_once "DB.php";
function &theDb()
{
global $gDb;
return $gDb;
}
function reconnectDb()
{
global $gDb;
global $gDsn;
@$gDb->disconnect();
$gDb = DB::connect($gDsn);
if (DB::isError($gDb)) {
die($gDb->getMessage());
}
}
$gDsn = "mysql://{$gDbUser}:{$gDbPassword}@{$gDbHost}/{$gDbDatabase}";
$gDb = DB::connect($gDsn);
if (DB::isError($gDb)) {
die(theDb()->getMessage());
}
theDb()->setFetchMode(DB_FETCHMODE_ASSOC);
theDb()->query("SET CHARACTER SET 'utf8'");
function openid_create_tables()
{
theDb()->query('
CREATE TABLE IF NOT EXISTS eb_users (
oid VARCHAR(255) NOT NULL PRIMARY KEY,
nickname VARCHAR(64),
fullname VARCHAR(128),
email VARCHAR(128),
is_admin TINYINT NOT NULL DEFAULT 0
)');
theDb()->query('ALTER TABLE eb_users ADD is_admin TINYINT NOT NULL DEFAULT 0');
}
require_once 'lib/genomes.php';
require_once 'lib/openid.php';
require_once 'lib/bp.php';
ini_set("output_buffering", FALSE);
ini_set("memory_limit", 67108864);
genomes_create_tables();
print "Finding snap_latest rows with no \"variant added\" row... ";
theDb()->query("create temporary table no_variant_add as select distinct variant_id from snap_latest");
print theDb()->affectedRows();
print " variants total\n";
theDb()->query("delete nva.* from no_variant_add nva\n LEFT JOIN snap_latest s\n ON nva.variant_id=s.variant_id\n AND s.article_pmid=0\n AND s.genome_id=0\n AND s.disease_id=0\n WHERE s.variant_id IS NOT NULL");
print theDb()->affectedRows();
print " alrady have \"add\" entries\n";
theDb()->query("CREATE TEMPORARY TABLE edits_to_add LIKE edits");
theDb()->query("ALTER TABLE edits_to_add ADD UNIQUE(variant_id)");
theDb()->query("INSERT IGNORE INTO edits_to_add\n SELECT e.* FROM no_variant_add nva\n LEFT JOIN edits e ON e.variant_id=nva.variant_id\n ORDER BY edit_timestamp");
print theDb()->affectedRows();
print " inserted\n";
theDb()->query("ALTER TABLE edits_to_add CHANGE edit_id edit_id BIGINT, DROP KEY `edit_id`");
theDb()->query("UPDATE edits_to_add SET article_pmid=0,genome_id=0,disease_id=0,summary_short='',summary_long='',talk_text='',variant_quality='',variant_quality_text='',variant_impact='not reviewed',variant_dominance='unknown',previous_edit_id=null,edit_id=NULL");
print theDb()->affectedRows();
print " modified to become \"add\" entries\n";
theDb()->query("INSERT INTO edits SELECT * FROM edits_to_add");
print theDb()->affectedRows();
print " added to \"edits\"\n";
theDb()->query("INSERT IGNORE INTO snap_latest\n SELECT e.* FROM edits_to_add eta\n LEFT JOIN edits e ON eta.variant_id=e.variant_id AND e.article_pmid=0 AND e.genome_id=0 AND e.disease_id=0 WHERE e.edit_id IS NOT NULL\n ORDER BY edit_id DESC");
print theDb()->affectedRows();
print " added to \"snap_latest\"\n";
theDb()->query("DELETE fs.* FROM edits_to_add eta\n LEFT JOIN flat_summary fs ON fs.variant_id=eta.variant_id");
print theDb()->affectedRows();
print " deleted from \"flat_summary\"\n";
foreach (array("variant_impact", "variant_dominance", "variant_quality", "variant_quality_text", "summary_short", "summary_long", "talk_text") as $k) {
$fields_allowed[$k] = 1;
}
foreach (explode("-", $_GET["edit_ids"]) as $edit_id) {
if (!ereg("^[0-9]+\$", $edit_id)) {
continue;
}
// Look for drafts ("d") already saved by this user based on the
// given edit and newer ("n") submissions from other users (danger
// of conflict)
$q =& theDb()->query("SELECT d.*, n.edit_id newer_edit_id, (d.variant_impact <> a.variant_impact OR d.variant_dominance <> a.variant_dominance OR d.variant_quality <> a.variant_quality OR d.variant_quality_text <> a.variant_quality_text OR d.summary_short <> a.summary_short OR d.summary_long <> a.summary_long OR d.talk_text <> a.talk_text OR d.article_pmid <> a.article_pmid) draft_differs\n\t\t\tFROM edits a\n\t\t\tLEFT JOIN edits d ON d.previous_edit_id=a.edit_id AND d.edit_oid=? AND d.is_draft\n\t\t\tLEFT JOIN snap_latest n ON n.variant_id=a.variant_id AND n.article_pmid=a.article_pmid AND n.genome_id=a.genome_id AND n.disease_id=a.disease_id\n\t\t\tWHERE a.edit_id=?", array(getCurrentUser("oid"), $edit_id));
while ($row =& $q->fetchRow()) {
if ($row["edit_id"]) {
if (!$row[draft_differs]) {
// draft saved, but content is identical -- just delete it
theDb()->query("DELETE FROM edits WHERE edit_id=? AND edit_oid=?", array($row["edit_id"], getCurrentUser("oid")));
continue;
}
// Existing draft
foreach (array_keys($fields_allowed) as $field) {
if ($row["disease_id"] > 0 && ereg('^{', $row[$field]) && ($dict = json_decode($row["{$field}"], true))) {
// decode json return and return key/value pairs in separate fields
foreach ($dict as $k => $v) {
$response["saved__{$edit_id}__{$field}__{$k}"] = $v;
$response["preview__{$edit_id}__{$field}__{$k}"] = htmlspecialchars($v);
}
continue;
}
if ($field == "variant_quality") {
for ($i = 0; $i < strlen($row[$field]); $i++) {
$response["saved__{$edit_id}__{$field}__{$i}"] = substr($row[$field], $i, 1);
print "Updating variant_external for existing searches...";
$q = theDb()->query("SELECT DISTINCT variant_id FROM yahoo_boss_cache");
if ($q && !theDb()->isError($q)) {
$n = 0;
while ($row =& $q->fetchRow()) {
yahoo_boss_update_external($row["variant_id"]);
++$n;
if ($n % 10 == 0) {
print ".";
}
}
print "{$n}";
} else {
print "(none)";
}
print "\n";
}
print "Building queue...";
$q = theDb()->query("CREATE TEMPORARY TABLE yahoo_boss_queue (\n variant_id BIGINT UNSIGNED NOT NULL\n) AS\n SELECT v.variant_id\n FROM variants v\n LEFT JOIN gene_disease\n ON gene=v.variant_gene\n LEFT JOIN flat_summary\n ON v.variant_id=flat_summary.variant_id AND n_genomes=1\n LEFT JOIN yahoo_boss_cache c\n ON c.variant_id=v.variant_id\n WHERE (gene IS NOT NULL OR flat_summary.variant_id IS NOT NULL)\n AND c.xml IS NULL\n GROUP BY v.variant_id");
if (theDb()->isError($q)) {
die($q->getMessage());
}
print theDb()->affectedRows();
print "\n";
$q = theDb()->query("SELECT q.variant_id variant_id, v.*\n FROM yahoo_boss_queue q\n LEFT JOIN variants v\n ON v.variant_id=q.variant_id");
while ($row =& $q->fetchRow()) {
$r = yahoo_boss_lookup($row["variant_id"]);
yahoo_boss_update_external($row["variant_id"]);
printf("%8d %s %s%d%s (%d)\n", $r["hitcount"], $row["variant_gene"], $row["variant_aa_from"], $row["variant_aa_pos"], $row["variant_aa_to"], $row["variant_id"]);
sleep(1);
}
theDb()->query("DELETE FROM variant_external WHERE tag='GWAS'");
$q = theDb()->query("INSERT INTO variant_external\n (variant_id, tag, content, url, updated)\n SELECT variant_id, 'GWAS', CONCAT(disease_trait,' (',risk_allele,')\n',first_author,' ',pub_date,' in ',journal,'\nOR or beta: ',or_or_beta,' ',ci_95_text,IF(risk_allele_frequency is null,'',CONCAT('\nRisk allele frequency: ',risk_allele_frequency)),IF(p_value is null,'',CONCAT('\np-value: ',p_value,' ',p_value_text)),'\nInitial sample: ',initial_sample_size,'\nReplication sample: ',replication_sample_size), url, NOW()\n FROM gwas\n WHERE variant_id > 0");
if (theDb()->isError($q)) {
print "[" . $q->getMessage() . "]";
}
print theDb()->affectedRows();
print "\n";
theDb()->query("UNLOCK TABLES");
if ($inputformat == "genome.gov") {
print "Adding or_or_beta_is_or column...";
theDb()->query("ALTER TABLE gwas ADD or_or_beta_is_or CHAR(1)");
theDb()->query("UPDATE gwas\nSET or_or_beta_is_or=IF(or_or_beta IS NOT NULL\n AND or_or_beta <> 'NR'\n AND (ci_95_text LIKE '%]'\n OR ci_95_text LIKE '%] (%)')\n AND ci_95_text NOT LIKE '%]%]','Y','N')");
print theDb()->affectedRows();
print "\n";
}
print "Adding/updating gwas_max_or column in variants table...";
theDb()->query("ALTER TABLE variants ADD gwas_max_or DECIMAL(6,3)");
theDb()->query("CREATE TEMPORARY TABLE gwas_or_tmp\n AS SELECT variant_id, MAX(or_or_beta) or_or_beta\n FROM gwas\n WHERE variant_id IS NOT NULL AND or_or_beta_is_or='Y'\n GROUP BY variant_id");
print theDb()->affectedRows();
print "...";
$q = theDb()->query("UPDATE gwas_or_tmp\n LEFT JOIN variants\n ON variants.variant_id=gwas_or_tmp.variant_id\n SET variants.gwas_max_or=or_or_beta\n ");
if (theDb()->isError($q)) {
print "[" . $q->getMessage() . "]";
}
print theDb()->affectedRows();
print "\n";
if (getenv("DEBUG")) {
theDb()->query("DROP TABLE IF EXISTS gwas_last");
theDb()->query("CREATE TABLE IF NOT EXISTS gwas_last LIKE gwas");
theDb()->query("INSERT INTO gwas_last SELECT * FROM gwas");
}
function evidence_set_my_web_vote($variant_id, $url, $score)
{
if (!($oid = getCurrentUser("oid"))) {
return;
}
theDb()->query("INSERT INTO web_vote_history SET\n\t\t\tvariant_id=?, url=?, vote_oid=?, vote_score=?", array($variant_id, $url, $oid, $score));
theDb()->query("REPLACE INTO web_vote_latest SET\n\t\t\tvariant_id=?, url=?, vote_oid=?, vote_score=?", array($variant_id, $url, $oid, $score));
$current =& theDb()->getAll("SELECT COUNT(*) c, vote_score\n\t\t\t\tFROM web_vote_latest\n\t\t\t\tWHERE variant_id=? AND url=?\n\t\t\t\tGROUP BY vote_score", array($variant_id, $url));
$vote_0 = 0;
$vote_1 = 0;
foreach ($current as $c) {
if ($c["vote_score"] == 1) {
$vote_1 += $c["c"];
} else {
if (strlen($c["vote_score"])) {
$vote_0 += $c["c"];
}
}
}
theDb()->query("REPLACE INTO web_vote SET variant_id=?, url=?, vote_0=?, vote_1=?", array($variant_id, $url, $vote_0, $vote_1));
if (theDb()->affectedRows() > 0) {
evidence_update_flat_summary($variant_id);
}
}
print theDb()->affectedRows();
print "\n";
print "Editing \"unknown\" variants to \"likely pathogenic\"...";
$timestamp = theDb()->getOne("SELECT NOW()");
$q = theDb()->query("INSERT INTO edits\n\t(variant_id, genome_id, article_pmid, is_draft,\n\t previous_edit_id, variant_dominance, variant_impact,\n\t summary_short, summary_long,\n\t edit_oid, edit_timestamp)\n\tSELECT DISTINCT s.variant_id, 0, 0, 0,\n\t edit_id, variant_dominance, ?,\n\t summary_short, summary_long,\n\t ?, ?\n\tFROM omim_a\n\tLEFT JOIN snap_latest s ON omim_a.variant_id=s.variant_id AND s.article_pmid=0 AND s.genome_id=0\n\tWHERE s.variant_impact='none'", array('likely pathogenic', getCurrentUser("oid"), $timestamp));
if (theDb()->isError($q)) {
print $q->getMessage();
print "...";
}
print theDb()->affectedRows();
print "\n";
print "Copying edits to snap_latest...";
theDb()->query("REPLACE INTO snap_latest SELECT * FROM edits WHERE edit_oid=? AND edit_timestamp=?", array(getCurrentUser("oid"), $timestamp));
print theDb()->affectedRows();
print "\n";
print "Filling in url field...";
theDb()->query("\nUPDATE omim_a\nSET url = CONCAT('http://www.ncbi.nlm.nih.gov/entrez/dispomim.cgi?id=',SUBSTRING_INDEX(SUBSTRING_INDEX(allelic_variant_id,'omim:',-1),'.',1))\nWHERE allelic_variant_id LIKE 'omim:%.%'\n");
print theDb()->affectedRows();
print "\n";
print "Updating variant_external...";
theDb()->query("LOCK TABLES variant_external WRITE");
theDb()->query("DELETE FROM variant_external WHERE tag='OMIM'");
theDb()->query("INSERT INTO variant_external\n (variant_id, tag, content, url, updated)\n SELECT variant_id, 'OMIM', phenotype, url, NOW()\n FROM omim_a\n WHERE variant_id > 0");
print theDb()->affectedRows();
print "\n";
theDb()->query("UNLOCK TABLES");
if (getenv("DEBUG")) {
theDb()->query("DROP TABLE IF EXISTS omim_last");
theDb()->query("CREATE TABLE IF NOT EXISTS omim_last LIKE omim_a");
theDb()->query("INSERT INTO omim_last SELECT * FROM omim_a");
}