/*************************************************************************
* *
* This file is part of the 20n/act project. *
* 20n/act enables DNA prediction for synthetic biology/bioengineering. *
* Copyright (C) 2017 20n Labs, Inc. *
* *
* Please direct all queries to act@20n.com. *
* *
* This program is free software: you can redistribute it and/or modify *
* it under the terms of the GNU General Public License as published by *
* the Free Software Foundation, either version 3 of the License, or *
* (at your option) any later version. *
* *
* This program is distributed in the hope that it will be useful, *
* but WITHOUT ANY WARRANTY; without even the implied warranty of *
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the *
* GNU General Public License for more details. *
* *
* You should have received a copy of the GNU General Public License *
* along with this program. If not, see <http://www.gnu.org/licenses/>. *
* *
*************************************************************************/
package com.act.lcms.db.model;
import com.act.lcms.db.io.DB;
import com.act.lcms.MassCalculator;
import com.act.utils.TSVParser;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.tuple.Pair;
import java.io.IOException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.List;
import java.util.Map;
public class ChemicalOfInterest extends BaseDBModel<ChemicalOfInterest> {
public static final String TABLE_NAME = "chemicals_of_interest";
protected static final ChemicalOfInterest INSTANCE = new ChemicalOfInterest();
public static ChemicalOfInterest getInstance() {
return INSTANCE;
}
private enum DB_FIELD implements DBFieldEnumeration {
ID(1, -1, "id"),
NAME(2, 1, "name"),
INCHI(3, 2, "inchi"),
DESCRIPTOR(4, 3, "descriptor"),
;
private final int offset;
private final int insertUpdateOffset;
private final String fieldName;
DB_FIELD(int offset, int insertUpdateOffset, String fieldName) {
this.offset = offset;
this.insertUpdateOffset = insertUpdateOffset;
this.fieldName = fieldName;
}
@Override
public int getOffset() {
return offset;
}
@Override
public int getInsertUpdateOffset() {
return insertUpdateOffset;
}
@Override
public String getFieldName() {
return fieldName;
}
@Override
public String toString() {
return this.fieldName;
}
public static String[] names() {
DB_FIELD[] values = DB_FIELD.values();
String[] names = new String[values.length];
for (int i = 0; i < values.length; i++) {
names[i] = values[i].getFieldName();
}
return names;
}
}
// TODO: it might be easier to use parts of Spring-Standalone to do named binding in these queries.
protected static final List<String> ALL_FIELDS = Collections.unmodifiableList(Arrays.asList(DB_FIELD.names()));
// id is auto-generated on insertion.
protected static final List<String> INSERT_UPDATE_FIELDS = INSTANCE.makeInsertUpdateFields();
@Override
public String getTableName() {
return TABLE_NAME;
}
@Override
public List<String> getAllFields() {
return ALL_FIELDS;
}
@Override
public List<String> getInsertUpdateFields() {
return INSERT_UPDATE_FIELDS;
}
protected static final String GET_BY_ID_QUERY = INSTANCE.makeGetByIDQuery();
@Override
protected String getGetByIDQuery() {
return GET_BY_ID_QUERY;
}
protected static final String INSERT_QUERY = INSTANCE.makeInsertQuery();
@Override
public String getInsertQuery() {
return INSERT_QUERY;
}
protected static final String UPDATE_QUERY = INSTANCE.makeUpdateQuery();
@Override
public String getUpdateQuery() {
return UPDATE_QUERY;
}
@Override
protected List<ChemicalOfInterest> fromResultSet(ResultSet resultSet) throws SQLException {
List<ChemicalOfInterest> results = new ArrayList<>();
while (resultSet.next()) {
Integer id = resultSet.getInt(DB_FIELD.ID.getOffset());
String name = resultSet.getString(DB_FIELD.NAME.getOffset());
String inchi = resultSet.getString(DB_FIELD.INCHI.getOffset());
String descriptor = resultSet.getString(DB_FIELD.DESCRIPTOR.getOffset());
results.add(new ChemicalOfInterest(id, name, inchi, descriptor));
}
return results;
}
public static final String QUERY_GET_CURATED_CHEMICAL_BY_NAME = INSTANCE.makeGetQueryForSelectField("name");
public List<ChemicalOfInterest> getChemicalOfInterestByName(DB db, String name) throws SQLException {
try (PreparedStatement stmt = db.getConn().prepareStatement(QUERY_GET_CURATED_CHEMICAL_BY_NAME)) {
stmt.setString(1, name);
try (ResultSet resultSet = stmt.executeQuery()) {
return fromResultSet(resultSet);
}
}
}
public static final String QUERY_GET_CURATED_CHEMICAL_BY_INCHI = INSTANCE.makeGetQueryForSelectField("inchi");
public List<ChemicalOfInterest> getChemicalOfInterestByInChI(DB db, String inchi) throws SQLException {
try (PreparedStatement stmt = db.getConn().prepareStatement(QUERY_GET_CURATED_CHEMICAL_BY_INCHI)) {
stmt.setString(1, inchi);
try (ResultSet resultSet = stmt.executeQuery()) {
return fromResultSet(resultSet);
}
}
}
public static final String QUERY_GET_CURATED_CHEMICAL_BY_NAME_INCHI_AND_DESCRIPTOR = StringUtils.join(new String[]{
"SELECT", StringUtils.join(INSTANCE.getAllFields(), ','),
"from", INSTANCE.getTableName(),
"where name = ?",
" and inchi = ?",
" and descriptor = ?",
}, " ");
public ChemicalOfInterest getChemicalOfInterestByNameInChIAndDescriptor(
DB db, String name, String inchi, String descriptor) throws SQLException, IOException, ClassNotFoundException {
try (PreparedStatement stmt =
db.getConn().prepareStatement(QUERY_GET_CURATED_CHEMICAL_BY_NAME_INCHI_AND_DESCRIPTOR)) {
stmt.setString(1, name);
stmt.setString(2, inchi);
stmt.setString(3, descriptor);
try (ResultSet resultSet = stmt.executeQuery()) {
return expectOneResult(resultSet,
String.format("name = %s, inchi = %s, descriptor = %s", name, inchi, descriptor));
}
}
}
public Double getAnyAvailableMassByName(DB db, String name) throws SQLException {
List<ChemicalOfInterest> chemicalsOfInterest = INSTANCE.getChemicalOfInterestByName(db, name);
if (chemicalsOfInterest == null || chemicalsOfInterest.size() == 0) {
return null;
}
if (chemicalsOfInterest.size() > 0) {
System.err.format("WARNING: found %d chemicals of interest for name %s where one was expected, using first.\n",
chemicalsOfInterest.size(), name);
}
ChemicalOfInterest chemicalOfInterest = chemicalsOfInterest.get(0);
return MassCalculator.calculateMass(chemicalOfInterest.getInchi());
}
// Insert/Update
protected void bindInsertOrUpdateParameters(
PreparedStatement stmt, String name, String inchi, String descriptor) throws SQLException {
stmt.setString(DB_FIELD.NAME.getInsertUpdateOffset(), name);
stmt.setString(DB_FIELD.INCHI.getInsertUpdateOffset(), inchi);
stmt.setString(DB_FIELD.DESCRIPTOR.getInsertUpdateOffset(), descriptor);
}
// TODO: this could return the number of parameters it bound to make it easier to set additional params.
@Override
protected void bindInsertOrUpdateParameters(PreparedStatement stmt, ChemicalOfInterest c) throws SQLException, IOException {
bindInsertOrUpdateParameters(stmt, c.getName(), c.getInchi(), c.getDescriptor());
}
public ChemicalOfInterest insert(DB db, String name, String inchi, String descriptor) throws SQLException, IOException {
return INSTANCE.insert(db, new ChemicalOfInterest(null, name, inchi, descriptor));
}
// Parsing/Loading
public static List<Pair<Integer, DB.OPERATION_PERFORMED>> insertOrUpdateChemicalOfInterestsFromTSV(
DB db, TSVParser parser) throws SQLException, IOException, ClassNotFoundException {
List<Map<String, String>> entries = parser.getResults();
List<Pair<Integer, DB.OPERATION_PERFORMED>> operationsPerformed = new ArrayList<>(entries.size());
for (Map<String, String> entry : entries) {
String name = entry.get("name");
String inchi = entry.get("inchi");
String descriptor = entry.get("descriptor");
// TODO: should we fall back to searching by name if we can't find the InChI?
ChemicalOfInterest chem = INSTANCE.getChemicalOfInterestByNameInChIAndDescriptor(db, name, inchi, descriptor);
DB.OPERATION_PERFORMED op = null;
if (chem == null) {
chem = INSTANCE.insert(db, name, inchi, descriptor);
op = DB.OPERATION_PERFORMED.CREATE;
} else {
chem.setName(name);
chem.setInchi(inchi);
chem.setDescriptor(descriptor);
INSTANCE.update(db, chem);
op = DB.OPERATION_PERFORMED.UPDATE;
}
// Chem should only be null if we couldn't insert the row into the DB.
if (chem == null) {
operationsPerformed.add(Pair.of((Integer)null, DB.OPERATION_PERFORMED.ERROR));
} else {
operationsPerformed.add(Pair.of(chem.getId(), op));
}
}
return operationsPerformed;
}
private String name;
private String inchi;
private String descriptor;
private ChemicalOfInterest() { }
protected ChemicalOfInterest(Integer id, String name, String inchi, String descriptor) {
this.id = id;
this.name = name;
this.inchi = inchi;
this.descriptor = descriptor;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getInchi() {
return inchi;
}
public void setInchi(String inchi) {
this.inchi = inchi;
}
public String getDescriptor() {
return descriptor;
}
public void setDescriptor(String descriptor) {
this.descriptor = descriptor;
}
}