function evidence_get_variant_id($gene, $aa_pos = false, $aa_from = false, $aa_to = false, $create_flag = false) { if ($aa_pos === false && ereg('^rs([0-9]+)$', $gene, $regs)) { // Return the gene/AA variant if one is already known to be caused // by this rsid $variant_id = theDb()->getOne("SELECT v.variant_id\n\t\t\t\t FROM variant_occurs vo\n\t\t\t\t LEFT JOIN variants v\n\t\t\t\t ON v.variant_id = vo.variant_id\n\t\t\t\t WHERE vo.rsid=?\n\t\t\t\t AND v.variant_rsid IS NULL\n\t\t\t\t AND v.variant_id IS NOT NULL\n\t\t\t\t LIMIT 1", array($regs[1])); if (theDb()->isError($variant_id)) { die($variant_id->getMessage()); } if ($variant_id) { return $variant_id; } if ($create_flag) { $q = theDb()->query("INSERT IGNORE INTO variants\n\t\t\t SET variant_rsid=?", array($regs[1])); if (!theDb()->isError($q) && theDb()->affectedRows()) { return theDb()->getOne("SELECT LAST_INSERT_ID()"); } } return theDb()->getOne("SELECT variant_id FROM variants\n\t\t\t\tWHERE variant_rsid=?", array($regs[1])); } if ($aa_pos === false) { if (ereg("^([-A-Za-z0-9_]+)[- ]+([A-Za-z]+)([0-9]+)([A-Za-z\\*]+)\$", $gene, $regs)) { $gene = $regs[1]; $aa_from = $regs[2]; $aa_pos = $regs[3]; $aa_to = $regs[4]; } else { return null; } } else { if (!aa_sane("{$aa_from}{$aa_pos}{$aa_to}")) { return null; } } $gene = strtoupper($gene); $aa_from = aa_long_form($aa_from); $aa_to = aa_long_form($aa_to); if ($create_flag) { $official_gene = theDb()->getOne("SELECT official FROM gene_canonical_name WHERE aka=?", array($gene)); if (!theDb()->isError($official_gene) && strlen($official_gene) && !theDb()->getOne("SELECT 1 FROM variants WHERE variant_gene=? AND variant_aa_pos=? AND variant_aa_from=? AND variant_iaa_to=?", array($gene, $aa_pos, $aa_from, $aa_to))) { $gene = $official_gene; } $q = theDb()->query("INSERT IGNORE INTO variants\n\t\t\tSET variant_gene=?,\n\t\t\tvariant_aa_pos=?,\n\t\t\tvariant_aa_from=?,\n\t\t\tvariant_aa_to=?", array($gene, $aa_pos, $aa_from, $aa_to)); if (!theDb()->isError($q) && theDb()->affectedRows()) { return theDb()->getOne("SELECT LAST_INSERT_ID()"); } } return theDb()->getOne("SELECT variant_id FROM variants\n\t\t\t\tWHERE variant_gene=?\n\t\t\t\tAND variant_aa_pos=?\n\t\t\t\tAND variant_aa_from=?\n\t\t\t\tAND variant_aa_to=?", array($gene, $aa_pos, $aa_from, $aa_to)); }
$q = theDb()->query("ALTER TABLE counsyl_a\n ADD aa_pos INT,\n ADD aa_from CHAR(4),\n ADD aa_to CHAR(4)"); if (theDb()->isError($q)) { print $q->getMessage(); } $q = theDb()->query("UPDATE counsyl_a\n SET aa_pos=SUBSTR(aa_change,2,LENGTH(aa_change)-2),\n aa_from=SUBSTR(aa_change,1,1),\n aa_to=SUBSTR(aa_change,LENGTH(aa_change),1)"); if (theDb()->isError($q)) { print $q->getMessage(); } print theDb()->affectedRows(); print "\n"; print "Translating AA to 3-letter symbols..."; foreach (array_keys($aa_13) as $aa) { theDb()->query("UPDATE counsyl_a SET aa_from=? WHERE aa_from=?", array(aa_long_form($aa), $aa)); print theDb()->affectedRows(); print ","; theDb()->query("UPDATE counsyl_a SET aa_to=? WHERE aa_to=?", array(aa_long_form($aa), $aa)); print theDb()->affectedRows(); print ","; } print "\n"; print "Deleting bogus rows..."; $q = theDb()->query("DELETE FROM counsyl_a WHERE aa_pos=0 OR LENGTH(aa_from)<3 OR LENGTH(aa_to)<3"); if (theDb()->isError($q)) { print $q->getMessage(); } print theDb()->affectedRows(); print "\n"; print "Looking up gene,aa -> variant_id..."; theDb()->query("ALTER TABLE counsyl_a ADD variant_id BIGINT UNSIGNED, ADD INDEX(variant_id)"); $q = theDb()->query("UPDATE counsyl_a c\n LEFT JOIN variants v\n ON v.variant_gene=c.gene\n AND v.variant_aa_from=c.aa_from\n AND v.variant_aa_pos=c.aa_pos\n AND v.variant_aa_to=c.aa_to\n set c.variant_id=v.variant_id"); print theDb()->affectedRows();
$variant_name = evidence_get_variant_name($variant_id, "-", true); if ($regs[1] != $variant_name) { header("Location: {$variant_name}"); exit; } $max_edit_id = $regs[3]; } else { if (ereg("^([A-Za-z0-9_-]+)[- \t\n]+([A-Za-z]+[0-9]+[A-Za-z\\*]+)(;([0-9]+))?\$", $_GET["q"], $regs) && aa_sane($aa = $regs[2])) { $gene = strtoupper($regs[1]); $max_edit_id = $regs[4]; $variant_id = evidence_get_variant_id($variant_name = "{$gene} {$aa}"); } } } if (!$variant_id && $aa) { $aa_long = aa_long_form($aa); $aa_short = aa_short_form($aa_long); header("HTTP/1.1 404 Not found"); $gOut["title"] = "{$gene} {$aa_short}"; $gOut["content"] = <<<EOF <H1>{$gene} {$aa_short}</H1> <P>({$gene} {$aa_long})</P> <P>There is no GET-Evidence entry for this variant.</P> EOF; if (ereg("[0-9]+", $aa, $regs)) { $gOut["content"] .= seealso_related($gene, $regs[0], 0); } if (getCurrentUser()) {
} if (ereg("^[0-9]+\$", $_POST["genome_id"], $regs)) { $genome_id = $regs[0]; } else { $genome_id = 0; } if (ereg("^[0-9]+\$", $_POST["disease_id"], $regs)) { $disease_id = $regs[0]; } else { $disease_id = 0; } if (ereg("^[0-9]+\$", $_POST["variant_id"], $regs)) { $variant_id = $regs[0]; } else { 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"); }