private String addNewInteraction(String fingerprint, BiologicalInteraction interaction)
throws SQLException {
String sql =
String.format(
"INSERT IGNORE INTO interaction (fingerprint) VALUES (%s)",
SqlUtil.toSqlVarchar(fingerprint));
if (1 != DBUtil.update(sql)) {
System.out.println("SQL update failed? " + sql);
}
String interaction_id = DBUtil.querySingle("SELECT LAST_INSERT_ID()");
Iterator<BiologicalEntity> iter = interaction.getEntityIterator();
StringBuffer sb =
new StringBuffer(
"INSERT IGNORE INTO interaction_pool (interaction_id, gene_id, entity_src_table, role) VALUES ");
while (iter.hasNext()) {
BiologicalEntity entity = iter.next();
sb.append("(")
.append(interaction_id)
.append(", ")
.append(SqlUtil.toSqlVarchar(entity.getId()))
.append(", '")
.append(srcTableLookup.get(entity.getId_src()))
.append("', '")
.append(entity.getRole())
.append("')");
if (iter.hasNext()) sb.append(", ");
}
if (DBUtil.update(sb.toString()) != interaction.getEnvolvedEntityCount()) {
System.out.println("SQL update failed? " + sb);
}
return interaction_id;
}
public void login(HttpServletRequest request, HttpServletResponse response) throws IOException {
String username = request.getParameter("username");
String password = request.getParameter("password");
if (username == null) {
response.sendRedirect("index.html");
return;
}
String sql =
"SELECT id FROM curator WHERE name="
+ SqlUtil.toSqlVarchar(username)
+ " AND passwd=PASSWORD("
+ SqlUtil.toSqlVarchar(password)
+ ")";
String id = DBUtil.querySingle(sql);
if (id == null) {
response.sendRedirect("index.html");
return;
}
Cookie cookie = new Cookie("username", username);
if (request.getParameter("remember_me") != null
&& request.getParameter("remember_me").equals("on")) {
cookie.setMaxAge(86400 * 365);
} else {
cookie.setMaxAge(-1);
}
response.addCookie(cookie);
cookie = new Cookie("userid", id);
if (request.getParameter("remember_me") != null
&& request.getParameter("remember_me").equals("on")) {
cookie.setMaxAge(86400 * 365);
} else {
cookie.setMaxAge(-1);
}
response.addCookie(cookie);
}
private String guessProtein(String name) {
String sql = "SELECT pname FROM proteinGene WHERE gene='" + name + "'";
String s = DBUtil.querySingle(sql);
if (s != null) {
return s;
}
return null;
}
private String guessOther(String key) {
String sql = "SELECT gene_id FROM other_entity WHERE name='" + key + "'";
String s = DBUtil.querySingle(sql);
if (s != null) {
return s;
}
return null;
}
private BiologicalEntity guessEntity(String key) {
String sql;
if (key.endsWith("_HUMAN")) {
sql = "SELECT * FROM proteinGene WHERE pname='" + key + "'";
HashMap<String, String> line = DBUtil.fillFirstRow(sql);
if (line != null) {
BiologicalEntity be = new BiologicalEntity();
be.setId(line.get("geneid"));
be.setId_src(BiologicalEntity.ENTREZ);
be.setType(BiologicalEntity.GENE);
be.setName(key); // line.get("pname"));
return be;
}
}
sql = "SELECT geneid FROM gene WHERE symbol='" + key + "' AND taxid=9606";
String s = DBUtil.querySingle(sql);
if (s != null) {
BiologicalEntity be = new BiologicalEntity();
be.setId(s);
be.setName(key);
be.setId_src(BiologicalEntity.ENTREZ);
be.setType(BiologicalEntity.GENE);
return be;
}
sql =
"SELECT geneid FROM geneAlias, gene WHERE gene.taxid=9606 AND geneAlias.alias='"
+ key
+ "' AND geneAlias.id_list=geneid";
s = DBUtil.querySingle(sql);
if (s != null) {
BiologicalEntity be = new BiologicalEntity();
be.setId(s);
be.setName(key);
be.setId_src(BiologicalEntity.ENTREZ);
be.setType(BiologicalEntity.GENE);
return be;
}
System.err.println("Cannot find an entry for " + key);
return null;
}
public void flush() throws SQLException {
InteractionFingerprinter fingerprinter = new InteractionFingerprinter();
for (BiologicalInteraction interaction : interactions) {
String fingerprint = fingerprinter.getFingerprint(interaction);
String sql =
String.format(
"SELECT interaction_id FROM interaction WHERE fingerprint=%s",
SqlUtil.toSqlVarchar(fingerprint));
String id = DBUtil.querySingle(sql);
if (id == null) {
id = addNewInteraction(fingerprint, interaction);
}
interaction.setInteractionId(Integer.valueOf(id));
}
System.out.println("Evidences count: " + evidences.size());
for (Evidence evidence : evidences) {
String sql =
"INSERT INTO evidence SET "
+ "interaction_id="
+ evidence.getInteraction().getInteractionId()
+ ", "
+ "negative=0, "
+ "directed="
+ (evidence.getInteraction().isDirected() ? 1 : 0)
+ ", "
+ "interaction_type='"
+ evidence.getInteraction().getInteractionType()
+ "', "
+ "source='"
+ evidence.getDataSource()
+ "', "
+ "macro_id='"
+ evidence.getMacroId()
+ "', "
+ "micro_id='"
+ evidence.getMicroId()
+ "', "
+ "confidence=100, "
+ "evidence_text="
+ SqlUtil.toSqlVarchar(evidence.getEvidenceText())
+ ", "
+ "curator=0";
DBUtil.update(sql);
}
}