Java tutorial
/** * See the NOTICE file distributed with this work for additional information * regarding copyright ownership. * * This is free software; you can redistribute it and/or modify it under the * terms of the GNU Lesser General Public License as published by the Free * Software Foundation; either version 2.1 of the License, or (at your option) * any later version. * * This software 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 Lesser General Public License for more * details. * * You should have received a copy of the GNU Lesser General Public License * along with this software; if not, write to the Free Software Foundation, * Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA, or see the FSF * site: http://www.fsf.org. */ package org.ut.biolab.medsavant.server.serverapi; import java.rmi.RemoteException; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import com.healthmarketscience.sqlbuilder.*; import com.healthmarketscience.sqlbuilder.OrderObject.Dir; import com.healthmarketscience.sqlbuilder.dbspec.Column; import com.healthmarketscience.sqlbuilder.dbspec.basic.DbColumn; import java.io.File; import java.io.IOException; import java.util.HashSet; import java.util.Set; import org.apache.commons.lang3.ArrayUtils; import org.apache.commons.lang3.StringUtils; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import org.ut.biolab.medsavant.server.MedSavantServerEngine; import org.ut.biolab.medsavant.shared.format.BasicVariantColumns; import org.ut.biolab.medsavant.server.db.MedSavantDatabase; import org.ut.biolab.medsavant.server.db.MedSavantDatabase.PatientFormatTableSchema; import org.ut.biolab.medsavant.server.db.MedSavantDatabase.PatientTablemapTableSchema; import org.ut.biolab.medsavant.server.db.MedSavantDatabase.ProjectTableSchema; import org.ut.biolab.medsavant.server.db.MedSavantDatabase.ReferenceTableSchema; import org.ut.biolab.medsavant.server.db.MedSavantDatabase.VariantFormatTableSchema; import org.ut.biolab.medsavant.server.db.MedSavantDatabase.VariantTablemapTableSchema; import org.ut.biolab.medsavant.shared.db.TableSchema; import org.ut.biolab.medsavant.server.db.ConnectionController; import org.ut.biolab.medsavant.server.db.PooledConnection; import org.ut.biolab.medsavant.server.db.util.DBSettings; import org.ut.biolab.medsavant.server.db.util.DBUtils; import org.ut.biolab.medsavant.server.db.variants.VariantManagerUtils; import org.ut.biolab.medsavant.shared.format.AnnotationFormat; import org.ut.biolab.medsavant.shared.format.CustomField; import org.ut.biolab.medsavant.shared.model.ProjectDetails; import org.ut.biolab.medsavant.shared.util.BinaryConditionMS; import org.ut.biolab.medsavant.server.MedSavantServerUnicastRemoteObject; import org.ut.biolab.medsavant.server.db.admin.SetupMedSavantDatabase; import org.ut.biolab.medsavant.shared.model.AnnotationDownloadInformation; import org.ut.biolab.medsavant.shared.model.Reference; import org.ut.biolab.medsavant.shared.model.SessionExpiredException; import org.ut.biolab.medsavant.shared.serverapi.ProjectManagerAdapter; import org.ut.biolab.medsavant.shared.util.DirectorySettings; import org.ut.biolab.medsavant.shared.util.VersionSettings; /** * * @author mfiume */ public class ProjectManager extends MedSavantServerUnicastRemoteObject implements ProjectManagerAdapter { private static final Log LOG = LogFactory.getLog(ProjectManager.class); private static ProjectManager instance; private ProjectManager() throws RemoteException, SessionExpiredException { } public static synchronized ProjectManager getInstance() throws RemoteException, SessionExpiredException { if (instance == null) { instance = new ProjectManager(); } return instance; } //This method will be removed from the public API. API users do not need to know database details. @Override @Deprecated public String getVariantTableName(String sessID, int updateID, int projectid, int refid) throws SQLException, SessionExpiredException, RemoteException { return DBSettings.getVariantViewName(projectid, refid); //throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates. } @Override public String[] getProjectNames(String sid) throws SQLException, SessionExpiredException { TableSchema table = MedSavantDatabase.ProjectTableSchema; SelectQuery query = new SelectQuery(); query.addFromTable(table.getTable()); query.addColumns(table.getDBColumn(ProjectTableSchema.COLUMNNAME_OF_NAME)); ResultSet rs = ConnectionController.executeQuery(sid, query.toString()); List<String> result = new ArrayList<String>(); while (rs.next()) { result.add(rs.getString(1)); } return result.toArray(new String[0]); } @Override public boolean containsProject(String sid, String projectName) throws SQLException, SessionExpiredException { TableSchema table = MedSavantDatabase.ProjectTableSchema; SelectQuery query = new SelectQuery(); query.addAllColumns(); query.addFromTable(table.getTable()); query.addCondition( BinaryConditionMS.equalTo(table.getDBColumn(ProjectTableSchema.COLUMNNAME_OF_NAME), projectName)); ResultSet rs = ConnectionController.executeQuery(sid, query.toString()); return rs.next(); } @Override public int getProjectID(String sid, String projectName) throws SQLException, SessionExpiredException { TableSchema table = MedSavantDatabase.ProjectTableSchema; SelectQuery query = new SelectQuery(); query.addFromTable(table.getTable()); query.addColumns(table.getDBColumn(ProjectTableSchema.COLUMNNAME_OF_PROJECT_ID)); query.addCondition( BinaryConditionMS.equalTo(table.getDBColumn(ProjectTableSchema.COLUMNNAME_OF_NAME), projectName)); ResultSet rs = ConnectionController.executeQuery(sid, query.toString()); if (rs.next()) { return rs.getInt(1); } else { return -1; } } @Override public void removeReferenceForProject(String sessID, int projID, int refID) throws SQLException, SessionExpiredException { PooledConnection conn = ConnectionController.connectPooled(sessID); try { TableSchema table = MedSavantDatabase.VarianttablemapTableSchema; SelectQuery query1 = new SelectQuery(); query1.addFromTable(table.getTable()); query1.addColumns(table.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_VARIANT_TABLENAME)); query1.addCondition(ComboCondition.and( BinaryConditionMS.equalTo( table.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_PROJECT_ID), projID), BinaryConditionMS.equalTo( table.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_REFERENCE_ID), refID))); ResultSet rs = conn.executeQuery(query1.toString()); while (rs.next()) { String tableName = rs.getString(1); DBUtils.dropTable(sessID, tableName); } DeleteQuery query2 = new DeleteQuery(table.getTable()); query2.addCondition(ComboCondition.and( BinaryConditionMS.equalTo( table.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_PROJECT_ID), projID), BinaryConditionMS.equalTo( table.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_REFERENCE_ID), refID))); conn.executeUpdate(query2.toString()); table = MedSavantDatabase.VariantformatTableSchema; query2 = new DeleteQuery(table.getTable()); query2.addCondition(ComboCondition.and( BinaryConditionMS.equalTo(table.getDBColumn(VariantFormatTableSchema.COLUMNNAME_OF_PROJECT_ID), projID), BinaryConditionMS.equalTo( table.getDBColumn(VariantFormatTableSchema.COLUMNNAME_OF_REFERENCE_ID), refID))); conn.executeUpdate(query2.toString()); } finally { conn.close(); } } @Override public String getProjectName(String sessID, int projID) throws SQLException, SessionExpiredException { TableSchema table = MedSavantDatabase.ProjectTableSchema; SelectQuery query = new SelectQuery(); query.addFromTable(table.getTable()); query.addColumns(table.getDBColumn(ProjectTableSchema.COLUMNNAME_OF_NAME)); query.addCondition( BinaryConditionMS.equalTo(table.getDBColumn(ProjectTableSchema.COLUMNNAME_OF_PROJECT_ID), projID)); ResultSet rs1 = ConnectionController.executeQuery(sessID, query.toString()); if (rs1.next()) { return rs1.getString(1); } else { return null; } } @Override public int[] getDefaultAnnotationIDs(String sessID, int projID, int refID) throws RemoteException, SQLException, SessionExpiredException { List<Integer> defaultAnnIds = new ArrayList<Integer>(); Reference[] refs = ReferenceManager.getInstance().getReferences(sessID); String refStr = null; for (Reference ref : refs) { if (ref.getID() == refID) { refStr = ref.getName(); break; } } if (refStr == null) { //can't find reference in database. LOG.error("Reference id " + refID + " is invalid"); throw new SQLException("Reference id " + refID + " is invalid"); } try { List<AnnotationDownloadInformation> annotations = AnnotationDownloadInformation .getDownloadableAnnotations(VersionSettings.getVersionString(), refStr); for (AnnotationDownloadInformation adi : annotations) { if (adi.isDefault()) { int id = AnnotationManager.getInstance().doInstallAnnotationForProject(sessID, projID, adi); if (id == -1) { LOG.error("Couldn't install annotation " + adi + " for project " + projID); } defaultAnnIds.add(id); //break; //DEBUG CODE! ONLY ALLOWS ONE ANNOTATION TO BE INSTALLED! } } } catch (Exception ex) { LOG.error(ex); } //return ids of these annotations. return ArrayUtils.toPrimitive(defaultAnnIds.toArray(new Integer[defaultAnnIds.size()])); } //The updID must always be 0, and so is ignored. staging has no meaning and is ignored. Both //arguments will be removed in the next API version. //This function will also be modified to add the default custom fields. Another method identical to this //one, but accepting a CustomField[] argument will be added for creating project with non-default customfields. @Override @Deprecated public String createVariantTable(String sessID, int projID, int refID, int updID, int[] annIDs, boolean staging) throws RemoteException, SQLException, SessionExpiredException { CustomField[] customFields = getCustomVariantFields(sessID, projID, refID, 0, true); String vt = addVariantTableToDatabase(sessID, projID, refID, 0, annIDs, customFields, false); String vts = addVariantTableToDatabase(sessID, projID, refID, 0, annIDs, customFields, true); addTableToMap(sessID, projID, refID, 0, false, vt, annIDs, vts); try { this.publishVariantTable(sessID, projID, refID); } catch (IOException iex) { LOG.error("Couldn't publish variant table", iex); throw new RemoteException("Couldn't publish variant table", iex); } return vt; } public String addVariantTableToDatabase(String sessID, int projID, int refID, int updID, int[] annIDs, CustomField[] customFields, boolean sub) throws RemoteException, SQLException, SessionExpiredException { // Create basic fields. String tableName = DBSettings.getVariantTableName(projID, refID, updID); if (sub) { tableName += "_subset"; } TableSchema variantSchema = new TableSchema(MedSavantDatabase.schema, tableName, BasicVariantColumns.REQUIRED_VARIANT_FIELDS); for (CustomField f : customFields) { variantSchema.addColumn(f); } String s = ""; for (DbColumn c : variantSchema.getColumns()) { s += c.getColumnNameSQL() + " "; } LOG.info("Creating variant table " + tableName + " with fields " + s); PooledConnection conn = ConnectionController.connectPooled(sessID); try { int j = 0; for (int ann : annIDs) { annIDs[j++] = ann; AnnotationFormat annFmt = AnnotationManager.getInstance().getAnnotationFormat(sessID, ann); for (CustomField f : annFmt.getCustomFields()) { variantSchema.addColumn(f); } } String updateString; if (MedSavantServerEngine.USE_INFINIDB_ENGINE) { updateString = variantSchema.getCreateQuery() + " ENGINE=INFINIDB;"; } else { updateString = variantSchema.getCreateQuery() + " ENGINE=BRIGHTHOUSE DEFAULT CHARSET=latin1 COLLATE=latin1_bin;"; } conn.executeUpdate(updateString); } finally { conn.close(); } return tableName; } public void setupTablesForVariantRemoval(String sessID, int projID, int refID, int updId, String newSubsetTableName) throws SQLException, RemoteException, SessionExpiredException { Object[] row = ProjectManager.getInstance().getVariantTableMapRecord(sessID, projID, refID, true); String tableName = (String) row[MedSavantDatabase.VarianttablemapTableSchema.INDEX_OF_VARIANT_TABLENAME]; int[] annotationIds = AnnotationManager.getInstance().getAnnotationIDs(sessID, projID, refID); addTableToMap(sessID, projID, refID, updId, false, tableName, annotationIds, newSubsetTableName); } //This method will be removed from the RMI-exposed API (but retained as a public non-RMI method). // published is redundant: it must always be false, and this argument will also be removed. @Deprecated @Override public void addTableToMap(String sessID, int projID, int refID, int updID, boolean published, String tableName, int[] annotationIDs, String subTableName) throws SQLException, RemoteException, SessionExpiredException { if (published != false) {//assert LOG.error("Can't add a published table to the variant table map"); published = false; } TableSchema variantTableMap = MedSavantDatabase.VarianttablemapTableSchema; InsertQuery query = new InsertQuery(variantTableMap.getTable()); query.addColumn(variantTableMap.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_PROJECT_ID), projID); query.addColumn(variantTableMap.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_REFERENCE_ID), refID); query.addColumn(variantTableMap.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_UPDATE_ID), updID); query.addColumn(variantTableMap.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_PUBLISHED), published); query.addColumn(variantTableMap.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_VARIANT_TABLENAME), tableName); query.addColumn( variantTableMap.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_VARIANT_SUBSET_TABLENAME), subTableName); float subMultiplier = 0.0f; query.addColumn(variantTableMap.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_SUBSET_MULTIPLIER), subMultiplier); if (annotationIDs.length > 0) { //Duplicate annotation IDs should never be passed to this function. Assert that they aren't //with the below error check, but don't muck up the database or crash if they are. Set<Integer> annotationIdSet = new HashSet<Integer>(); for (int id : annotationIDs) { annotationIdSet.add(id); } if (annotationIdSet.size() < annotationIDs.length) { LOG.error( "ERROR: Ignoring duplicate annotation ids in list " + StringUtils.join(annotationIDs, ",")); } String annIDString = ""; //for (int id : annotationIDs) { for (Integer id : annotationIdSet) { annIDString += id + ","; } annIDString = annIDString.substring(0, annIDString.length() - 1); // remove the last comma query.addColumn(variantTableMap.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_ANNOTATION_IDS), annIDString); } ConnectionController.executeUpdate(sessID, query.toString()); } private Object[] getVariantTableMapRecord(String sid, int projectid, int refid, boolean published) throws SQLException, SessionExpiredException { TableSchema table = MedSavantDatabase.VarianttablemapTableSchema; SelectQuery query = new SelectQuery(); query.addFromTable(table.getTable()); query.addAllColumns(); query.addCondition(BinaryConditionMS .equalTo(table.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_PROJECT_ID), projectid)); query.addCondition(BinaryConditionMS .equalTo(table.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_REFERENCE_ID), refid)); if (published) { query.addCondition(BinaryConditionMS .equalTo(table.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_PUBLISHED), true)); } query.addOrdering(table.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_UPDATE_ID), Dir.DESCENDING); ResultSet rs = ConnectionController.executeQuery(sid, query.toString()); if (rs.next()) { Object[] results = new Object[rs.getMetaData().getColumnCount()]; for (int i = 1; i <= rs.getMetaData().getColumnCount(); ++i) { results[i - 1] = rs.getObject(i); } return results; } else { return null; } } /* * Get the name of the most up-to-date variant table, or the most up-to-date, * published variant table. */ public String getNameOfVariantTable(String sid, int projectid, int refid, boolean published, boolean sub) throws SQLException, SessionExpiredException { Object[] row = getVariantTableMapRecord(sid, projectid, refid, published); return sub ? (String) row[VariantTablemapTableSchema.INDEX_OF_VARIANT_SUBSET_TABLENAME] : (String) row[VariantTablemapTableSchema.INDEX_OF_VARIANT_TABLENAME]; } //The published argument is ignored, and this method may be renamed in the future. //See below. @Override @Deprecated public String getVariantTableName(String sid, int projectid, int refid, boolean published) throws SQLException, SessionExpiredException { return getVariantTableName(sid, projectid, refid, published, false); } //The published argument is ignored and will be removed. This method returns the current variant VIEW, //which only applies to published tables. This method may be renamed in the future. @Override @Deprecated public String getVariantTableName(String sid, int projectid, int refid, boolean published, boolean sub) throws SQLException, SessionExpiredException { return sub ? DBSettings.getVariantViewName(projectid, refid) : DBSettings.getVariantViewName(projectid, refid); } public Object[] getVariantTableViewInfo(String sid, int projectid, int refid) throws SQLException, SessionExpiredException { TableSchema table = MedSavantDatabase.VarianttablemapTableSchema; SelectQuery query = new SelectQuery(); query.addFromTable(table.getTable()); query.addColumns(table.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_VARIANT_SUBSET_TABLENAME), table.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_SUBSET_MULTIPLIER)); query.addCondition(BinaryConditionMS .equalTo(table.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_PROJECT_ID), projectid)); query.addCondition(BinaryConditionMS .equalTo(table.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_REFERENCE_ID), refid)); query.addCondition(BinaryConditionMS .equalTo(table.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_PUBLISHED), true)); query.addOrdering(table.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_UPDATE_ID), Dir.DESCENDING); ResultSet rs = ConnectionController.executeQuery(sid, query.toString()); if (rs.next()) { //If the subset table doesn't exist, then the subset view cannot exist either. rs.getString(1); String subsetViewName = rs.wasNull() ? null : DBSettings.getVariantSubsetViewName(projectid, refid); return new Object[] { DBSettings.getVariantViewName(projectid, refid), subsetViewName, rs.getFloat(2) }; } else { return null; } } public void addSubsetInfoToMap(String sessID, int projID, int refID, int updID, String subTableName, float multiplier) throws SQLException, SessionExpiredException { TableSchema table = MedSavantDatabase.VarianttablemapTableSchema; UpdateQuery query = new UpdateQuery(table.getTable()); query.addCustomSetClause( table.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_VARIANT_SUBSET_TABLENAME), subTableName); query.addCustomSetClause(table.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_SUBSET_MULTIPLIER), multiplier); query.addCondition(BinaryCondition .equalTo(table.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_PROJECT_ID), projID)); query.addCondition(BinaryCondition .equalTo(table.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_REFERENCE_ID), refID)); query.addCondition(BinaryCondition .equalTo(table.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_UPDATE_ID), updID)); ConnectionController.executeUpdate(sessID, query.toString()); } private float getMultiplier(String sid, String table, String subTable) throws SQLException, RemoteException, SessionExpiredException { int numerator = VariantManager.getInstance().getNumFilteredVariantsHelper(sid, table, new Condition[0][]); int denominator = VariantManager.getInstance().getNumFilteredVariantsHelper(sid, subTable, new Condition[0][]); if (denominator == 0) { denominator = 1; } if (numerator > 0) { return (float) numerator / (float) denominator; } else { return 1.0f; } } @Override public int addProject(String sessID, String name, CustomField[] fields) throws SQLException, RemoteException, SessionExpiredException { TableSchema table = MedSavantDatabase.ProjectTableSchema; InsertQuery query = new InsertQuery(table.getTable()); query.addColumn(table.getDBColumn(ProjectTableSchema.COLUMNNAME_OF_NAME), name); Connection c = ConnectionController.connectPooled(sessID); PreparedStatement stmt = c.prepareStatement(query.toString(), Statement.RETURN_GENERATED_KEYS); stmt.execute(); ResultSet res = stmt.getGeneratedKeys(); res.next(); int projID = res.getInt(1); PatientManager.getInstance().createPatientTable(sessID, projID, fields); c.close(); return projID; } @Override public void removeProject(String sid, String projectName) throws SQLException, RemoteException, SessionExpiredException { TableSchema table = MedSavantDatabase.ProjectTableSchema; SelectQuery query = new SelectQuery(); query.addFromTable(table.getTable()); query.addColumns(table.getDBColumn(ProjectTableSchema.COLUMNNAME_OF_PROJECT_ID)); query.addCondition( BinaryConditionMS.equalTo(table.getDBColumn(ProjectTableSchema.COLUMNNAME_OF_NAME), projectName)); ResultSet rs = ConnectionController.executeQuery(sid, query.toString()); if (rs.next()) { removeProject(sid, rs.getInt(1)); } } @Override public void removeProject(String sid, int projectid) throws SQLException, RemoteException, SessionExpiredException { Connection c = ConnectionController.connectPooled(sid); TableSchema projectTable = MedSavantDatabase.ProjectTableSchema; TableSchema patientMapTable = MedSavantDatabase.PatienttablemapTableSchema; TableSchema patientFormatTable = MedSavantDatabase.PatientformatTableSchema; TableSchema variantMapTable = MedSavantDatabase.VarianttablemapTableSchema; //remove from project table DeleteQuery q1 = new DeleteQuery(projectTable.getTable()); q1.addCondition(BinaryConditionMS .equalTo(projectTable.getDBColumn(ProjectTableSchema.COLUMNNAME_OF_PROJECT_ID), projectid)); c.createStatement().execute(q1.toString()); //remove patient table SelectQuery q2 = new SelectQuery(); q2.addFromTable(patientMapTable.getTable()); q2.addColumns(patientMapTable.getDBColumn(PatientTablemapTableSchema.COLUMNNAME_OF_PATIENT_TABLENAME)); q2.addCondition(BinaryConditionMS.equalTo( patientMapTable.getDBColumn(PatientTablemapTableSchema.COLUMNNAME_OF_PROJECT_ID), projectid)); ResultSet rs1 = ConnectionController.executeQuery(sid, q2.toString()); rs1.next(); String patientTableName = rs1.getString(PatientTablemapTableSchema.COLUMNNAME_OF_PATIENT_TABLENAME); c.createStatement().execute("DROP TABLE IF EXISTS " + patientTableName); //remove from patient format table DeleteQuery q3 = new DeleteQuery(patientFormatTable.getTable()); q3.addCondition(BinaryConditionMS.equalTo( patientFormatTable.getDBColumn(PatientFormatTableSchema.COLUMNNAME_OF_PROJECT_ID), projectid)); c.createStatement().execute(q3.toString()); //remove from patient tablemap DeleteQuery q4 = new DeleteQuery(patientMapTable.getTable()); q4.addCondition(BinaryConditionMS.equalTo( patientMapTable.getDBColumn(PatientTablemapTableSchema.COLUMNNAME_OF_PROJECT_ID), projectid)); c.createStatement().execute(q4.toString()); //remove variant tables SelectQuery q5 = new SelectQuery(); q5.addFromTable(variantMapTable.getTable()); q5.addColumns(variantMapTable.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_VARIANT_TABLENAME)); q5.addCondition(BinaryConditionMS.equalTo( variantMapTable.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_PROJECT_ID), projectid)); ResultSet rs2 = ConnectionController.executeQuery(sid, q5.toString()); while (rs2.next()) { String variantTableName = rs2.getString(1); c.createStatement().execute("DROP TABLE IF EXISTS " + variantTableName); } //remove from variant tablemap DeleteQuery q6 = new DeleteQuery(variantMapTable.getTable()); q6.addCondition(BinaryConditionMS.equalTo( variantMapTable.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_PROJECT_ID), projectid)); c.createStatement().execute(q6.toString()); //remove cohort entries int[] cohIDs = CohortManager.getInstance().getCohortIDs(sid, projectid); for (int cohID : cohIDs) { CohortManager.getInstance().removeCohort(sid, cohID); } c.close(); } @Override public void setAnnotations(String sessID, int projID, int refID, int updID, String annIDs) throws SQLException, SessionExpiredException { String tablename = getNameOfVariantTable(sessID, projID, refID, true, false); TableSchema table = MedSavantDatabase.VarianttablemapTableSchema; UpdateQuery query = new UpdateQuery(table.getTable()); query.addSetClause(table.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_ANNOTATION_IDS), annIDs); query.addCondition(BinaryConditionMS .equalTo(table.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_PROJECT_ID), projID)); query.addCondition(BinaryConditionMS .equalTo(table.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_REFERENCE_ID), refID)); query.addCondition(BinaryConditionMS .equalTo(table.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_UPDATE_ID), updID)); query.addCondition(BinaryConditionMS .equalTo(table.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_VARIANT_TABLENAME), tablename)); //should only affect published table ConnectionController.executeUpdate(sessID, query.toString()); } @Override public ProjectDetails[] getProjectDetails(String sessID, int projID) throws SQLException, SessionExpiredException { TableSchema variantMapTable = MedSavantDatabase.VarianttablemapTableSchema; TableSchema refTable = MedSavantDatabase.ReferenceTableSchema; SelectQuery query = new SelectQuery(); query.addFromTable(variantMapTable.getTable()); query.addAllColumns(); query.addJoin(SelectQuery.JoinType.LEFT_OUTER, variantMapTable.getTable(), refTable.getTable(), BinaryConditionMS.equalTo( variantMapTable.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_REFERENCE_ID), refTable.getDBColumn(ReferenceTableSchema.COLUMNNAME_OF_REFERENCE_ID))); query.addCondition(BinaryConditionMS .equalTo(variantMapTable.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_PROJECT_ID), projID)); query.addCondition(BinaryConditionMS .equalTo(variantMapTable.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_PUBLISHED), true)); query.addOrdering(variantMapTable.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_UPDATE_ID), Dir.DESCENDING); ResultSet rs = ConnectionController.executeQuery(sessID, query.toString()); List<Integer> refs = new ArrayList<Integer>(); List<ProjectDetails> result = new ArrayList<ProjectDetails>(); while (rs.next()) { if (!refs.contains(rs.getInt(VariantTablemapTableSchema.COLUMNNAME_OF_REFERENCE_ID))) { // should be only one per reference (most recent and published) // Annotation IDs are stored as a comma-delimited string. String annString = rs.getString(VariantTablemapTableSchema.COLUMNNAME_OF_ANNOTATION_IDS); int[] annIDs; if (annString != null) { String[] anns = annString.split(","); annIDs = new int[anns.length]; for (int i = 0; i < anns.length; i++) { annIDs[i] = Integer.parseInt(anns[i]); } } else { annIDs = new int[0]; } result.add(new ProjectDetails(rs.getInt(VariantTablemapTableSchema.COLUMNNAME_OF_PROJECT_ID), rs.getInt(VariantTablemapTableSchema.COLUMNNAME_OF_REFERENCE_ID), rs.getInt(VariantTablemapTableSchema.COLUMNNAME_OF_UPDATE_ID), rs.getBoolean(VariantTablemapTableSchema.COLUMNNAME_OF_PUBLISHED), null, rs.getString(ReferenceTableSchema.COLUMNNAME_OF_NAME), annIDs)); refs.add(rs.getInt(VariantTablemapTableSchema.COLUMNNAME_OF_REFERENCE_ID)); } } return result.toArray(new ProjectDetails[0]); } @Override public void renameProject(String sid, int projectId, String newName) throws SQLException, SessionExpiredException { TableSchema table = MedSavantDatabase.ProjectTableSchema; UpdateQuery query = new UpdateQuery(table.getTable()); query.addSetClause(table.getDBColumn(ProjectTableSchema.COLUMNNAME_OF_NAME), newName); query.addCondition(BinaryConditionMS.equalTo(table.getDBColumn(ProjectTableSchema.COLUMNNAME_OF_PROJECT_ID), projectId)); ConnectionController.executeUpdate(sid, query.toString()); } @Override public void setCustomVariantFields(String sid, int projectId, int referenceId, int updateId, CustomField[] fields) throws SQLException, SessionExpiredException { Connection c = ConnectionController.connectPooled(sid); TableSchema table = MedSavantDatabase.VariantformatTableSchema; c.setAutoCommit(false); for (int i = 0; i < fields.length; i++) { CustomField f = fields[i]; InsertQuery insertQuery = new InsertQuery(table.getTable()); insertQuery.addColumn(table.getDBColumn(VariantFormatTableSchema.COLUMNNAME_OF_PROJECT_ID), projectId); insertQuery.addColumn(table.getDBColumn(VariantFormatTableSchema.COLUMNNAME_OF_REFERENCE_ID), referenceId); insertQuery.addColumn(table.getDBColumn(VariantFormatTableSchema.COLUMNNAME_OF_UPDATE_ID), updateId); insertQuery.addColumn(table.getDBColumn(VariantFormatTableSchema.COLUMNNAME_OF_POSITION), i); insertQuery.addColumn(table.getDBColumn(VariantFormatTableSchema.COLUMNNAME_OF_COLUMN_NAME), f.getColumnName()); insertQuery.addColumn(table.getDBColumn(VariantFormatTableSchema.COLUMNNAME_OF_COLUMN_TYPE), f.getTypeString()); insertQuery.addColumn(table.getDBColumn(VariantFormatTableSchema.COLUMNNAME_OF_FILTERABLE), (f.isFilterable() ? "1" : "0")); insertQuery.addColumn(table.getDBColumn(VariantFormatTableSchema.COLUMNNAME_OF_ALIAS), f.getAlias()); insertQuery.addColumn(table.getDBColumn(VariantFormatTableSchema.COLUMNNAME_OF_DESCRIPTION), f.getDescription()); c.createStatement().execute(insertQuery.toString()); } c.commit(); c.setAutoCommit(true); c.close(); } //This method is temporary. The boolean allows custom fields to be fetched from unpublished tables, which is only necessary when //creating a new project, as there is no mechanism for passing CustomFields on project creation. The boolean will be removed and //most of this method will replace the API-exposed getCustomVariantFields (it will be as if considerUnpublished is always false). @Deprecated private CustomField[] getCustomVariantFields(String sid, int projectId, int referenceId, int updateId, boolean considerUnpublished) throws SQLException, SessionExpiredException { TableSchema table = MedSavantDatabase.VariantformatTableSchema; TableSchema variantTableMap = MedSavantDatabase.VarianttablemapTableSchema; SelectQuery query = new SelectQuery(); Column[] cols = new Column[table.getColumns().size()]; int i = 0; for (DbColumn c : table.getColumns()) { cols[i++] = c; } query.addColumns(cols); //query.addFromTable(table.getTable()); //reference, project, update, if (!considerUnpublished) { Condition joinCondition = ComboCondition.and( BinaryCondition.equalTo(table.getDBColumn(VariantFormatTableSchema.COLUMNNAME_OF_PROJECT_ID), variantTableMap.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_PROJECT_ID)), BinaryCondition.equalTo(table.getDBColumn(VariantFormatTableSchema.COLUMNNAME_OF_REFERENCE_ID), variantTableMap.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_REFERENCE_ID)), BinaryCondition.equalTo(table.getDBColumn(VariantFormatTableSchema.COLUMNNAME_OF_UPDATE_ID), variantTableMap.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_UPDATE_ID))); //restrict by published query.addJoin(SelectQuery.JoinType.INNER, table.getTable(), variantTableMap.getTable(), joinCondition); } else { query.addFromTable(table.getTable()); } query.addCondition(BinaryConditionMS .equalTo(table.getDBColumn(VariantFormatTableSchema.COLUMNNAME_OF_PROJECT_ID), projectId)); query.addCondition(BinaryConditionMS .equalTo(table.getDBColumn(VariantFormatTableSchema.COLUMNNAME_OF_REFERENCE_ID), referenceId)); query.addCondition(BinaryConditionMS .equalTo(table.getDBColumn(VariantFormatTableSchema.COLUMNNAME_OF_UPDATE_ID), updateId)); if (!considerUnpublished) { query.addCondition(BinaryCondition .equalTo(variantTableMap.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_PUBLISHED), 1)); } query.addOrdering(table.getDBColumn(VariantFormatTableSchema.COLUMNNAME_OF_POSITION), Dir.ASCENDING); //System.out.println("Query: "+query.toString()); ResultSet rs = ConnectionController.executeQuery(sid, query.toString()); List<CustomField> result = new ArrayList<CustomField>(); while (rs.next()) { result.add(new CustomField( rs.getString(VariantFormatTableSchema.COLUMNNAME_OF_COLUMN_NAME)/*.toLowerCase()*/, //why force lower case here? rs.getString(VariantFormatTableSchema.COLUMNNAME_OF_COLUMN_TYPE), rs.getBoolean(VariantFormatTableSchema.COLUMNNAME_OF_FILTERABLE), rs.getString(VariantFormatTableSchema.COLUMNNAME_OF_ALIAS), rs.getString(VariantFormatTableSchema.COLUMNNAME_OF_DESCRIPTION))); } return result.toArray(new CustomField[0]); } //Get the most up-to-date custom fields, as specified in variant_format table. Only published //custom fields are considered. @Override public CustomField[] getCustomVariantFields(String sid, int projectId, int referenceId, int updateId) throws SQLException, SessionExpiredException { return getCustomVariantFields(sid, projectId, referenceId, updateId, false); } @Override public int getNewestUpdateID(String sid, int projectId, int referenceId, boolean published) throws SQLException, SessionExpiredException { TableSchema table = MedSavantDatabase.VarianttablemapTableSchema; SelectQuery query = new SelectQuery(); query.addFromTable(table.getTable()); query.addColumns(table.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_UPDATE_ID)); query.addCondition(BinaryConditionMS .equalTo(table.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_PROJECT_ID), projectId)); query.addCondition(BinaryConditionMS .equalTo(table.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_REFERENCE_ID), referenceId)); if (published) { query.addCondition(BinaryConditionMS .equalTo(table.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_PUBLISHED), true)); } query.addOrdering(table.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_UPDATE_ID), Dir.DESCENDING); ResultSet rs = ConnectionController.executeQuery(sid, query.toString()); rs.next(); return rs.getInt(VariantTablemapTableSchema.COLUMNNAME_OF_UPDATE_ID); } public void publishVariantTable(String sid, int projId, int refID) throws IOException, SQLException, SessionExpiredException { PooledConnection conn = ConnectionController.connectPooled(sid); publishVariantTable(sid, conn, projId, refID); } public void publishVariantTable(String sid, PooledConnection conn, int projId, int refID) throws IOException, SQLException, SessionExpiredException { publishVariantTable(sid, conn, projId, new int[] { refID }); } public void publishVariantTable(String sid, PooledConnection conn, int projID, int[] refIDs) throws IOException, SQLException, SessionExpiredException { TableSchema variantTableMapTable = MedSavantDatabase.VarianttablemapTableSchema; TableSchema variantFormatTable = MedSavantDatabase.VariantformatTableSchema; //Set Variant File IB Table <- Variant File MyISAM table SetupMedSavantDatabase.makeVariantFileIBTable(sid); Set<String> tablesToDrop = new HashSet<String>(); for (int refID : refIDs) { String fileTableName = SetupMedSavantDatabase.getVariantFileIBTableName(); String variantTableName = getNameOfVariantTable(sid, projID, refID, false, false); String subTableName = getNameOfVariantTable(sid, projID, refID, false, true); //Create a view into the table String variantViewName = DBSettings.getVariantViewName(projID, refID); String variantSubViewName = DBSettings.getVariantSubsetViewName(projID, refID); //if (!DBUtils.tableExists(sid, variantViewName)) { conn.executeUpdate("DROP VIEW IF EXISTS " + variantViewName); String viewQuery = "CREATE VIEW " + variantViewName + " AS " + "SELECT " + variantTableName + ".* FROM " + variantTableName + ", " + fileTableName + " WHERE (" + fileTableName + "." + MedSavantDatabase.VariantFileTableSchema.COLUMNNAME_OF_FILE_ID + " = " + variantTableName + "." + BasicVariantColumns.FILE_ID.getColumnName() + " AND " + fileTableName + "." + MedSavantDatabase.VariantFileTableSchema.COLUMNNAME_OF_UPLOAD_ID + " = " + variantTableName + "." + BasicVariantColumns.UPLOAD_ID.getColumnName() + " AND " + fileTableName + "." + MedSavantDatabase.VariantFileTableSchema.COLUMNNAME_OF_PROJECT_ID + " = " + projID + " AND " + fileTableName + "." + MedSavantDatabase.VariantFileTableSchema.COLUMNNAME_OF_REFERENCE_ID + " = " + refID + ")"; LOG.info(viewQuery); conn.executeUpdate(viewQuery); // } if (subTableName != null && !subTableName.isEmpty()/* && !DBUtils.tableExists(sid, variantSubViewName)*/) { conn.executeUpdate("DROP VIEW IF EXISTS " + variantSubViewName); viewQuery = "CREATE VIEW " + variantSubViewName + " AS " + "SELECT " + subTableName + ".* FROM " + subTableName + ", " + fileTableName + " WHERE (" + fileTableName + "." + MedSavantDatabase.VariantFileTableSchema.COLUMNNAME_OF_FILE_ID + " = " + subTableName + "." + BasicVariantColumns.FILE_ID.getColumnName() + " AND " + fileTableName + "." + MedSavantDatabase.VariantFileTableSchema.COLUMNNAME_OF_UPLOAD_ID + " = " + subTableName + "." + BasicVariantColumns.UPLOAD_ID.getColumnName() + " AND " + fileTableName + "." + MedSavantDatabase.VariantFileTableSchema.COLUMNNAME_OF_PROJECT_ID + " = " + projID + " AND " + fileTableName + "." + MedSavantDatabase.VariantFileTableSchema.COLUMNNAME_OF_REFERENCE_ID + " = " + refID + ")"; conn.executeUpdate(viewQuery); LOG.info(viewQuery); } //Get the names of the current published tables. SelectQuery sq = new SelectQuery(); sq.addFromTable(variantTableMapTable.getTable()); sq.addAllColumns(); sq.addCondition(BinaryConditionMS.equalTo( variantTableMapTable.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_PROJECT_ID), projID)); sq.addCondition(BinaryConditionMS.equalTo( variantTableMapTable.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_REFERENCE_ID), refID)); sq.addCondition(BinaryConditionMS.equalTo( variantTableMapTable.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_PUBLISHED), true)); //Queue all tables not named the same as the current table for later deletion, as the //last step of publication. ResultSet rs = conn.executeQuery(sq.toString()); while (rs.next()) { String tn = rs.getString(VariantTablemapTableSchema.COLUMNNAME_OF_VARIANT_TABLENAME); String sn = rs.getString(VariantTablemapTableSchema.COLUMNNAME_OF_VARIANT_SUBSET_TABLENAME); //System.out.println("Checking if tablename " + tn + " matches new tablename " + variantTableName); if (!tn.equals(variantTableName)) { tablesToDrop.add(tn); } if (!sn.equals(subTableName)) { tablesToDrop.add(sn); } } float multiplier = this.getMultiplier(sid, variantViewName, variantSubViewName); //update the table so everything points to the published version of the table, and the subset multiplier is set correctly. UpdateQuery query = new UpdateQuery(variantTableMapTable.getTable()); query.addSetClause( variantTableMapTable.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_VARIANT_TABLENAME), variantTableName); query.addSetClause(variantTableMapTable .getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_VARIANT_SUBSET_TABLENAME), subTableName); query.addSetClause( variantTableMapTable.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_SUBSET_MULTIPLIER), multiplier); query.addSetClause(variantTableMapTable.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_PUBLISHED), true); query.addCondition(BinaryConditionMS.equalTo( variantTableMapTable.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_PROJECT_ID), projID)); query.addCondition(BinaryConditionMS.equalTo( variantTableMapTable.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_REFERENCE_ID), refID)); conn.executeUpdate(query.toString()); //Clean out variant_format table to contain only the format of the last created table. int u = getNewestUpdateID(sid, projID, refID, true); DeleteQuery dq = new DeleteQuery(variantFormatTable.getTable()); dq.addCondition(BinaryConditionMS.equalTo( variantTableMapTable.getDBColumn(VariantFormatTableSchema.COLUMNNAME_OF_PROJECT_ID), projID)); dq.addCondition(BinaryConditionMS.equalTo( variantTableMapTable.getDBColumn(VariantFormatTableSchema.COLUMNNAME_OF_REFERENCE_ID), refID)); dq.addCondition(BinaryCondition.notEqualTo( variantTableMapTable.getDBColumn(VariantFormatTableSchema.COLUMNNAME_OF_UPDATE_ID), u)); conn.executeUpdate(dq.toString()); } //delete unused tables. for (String tableToDrop : tablesToDrop) { DBUtils.dropTable(sid, tableToDrop); } //delete old genotype files. cleanStaleGenotypeFiles(sid, projID); } public boolean hasUnpublishedChanges(String sessId, int projId, int refId) throws SQLException, SessionExpiredException { TableSchema table = MedSavantDatabase.VarianttablemapTableSchema; SelectQuery query = new SelectQuery(); query.addFromTable(table.getTable()); query.addAllColumns(); query.addCondition(BinaryConditionMS .equalTo(table.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_PROJECT_ID), projId)); query.addCondition(BinaryConditionMS .equalTo(table.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_REFERENCE_ID), refId)); query.addCondition(BinaryConditionMS .equalTo(table.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_PUBLISHED), false)); query.addOrdering(table.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_UPDATE_ID), Dir.DESCENDING); ResultSet rs = ConnectionController.executeQuery(sessId, query.toString()); return rs.first(); } @Override public ProjectDetails[] getUnpublishedChanges(String sessID) throws SQLException, SessionExpiredException { TableSchema variantMapTable = MedSavantDatabase.VarianttablemapTableSchema; TableSchema refTable = MedSavantDatabase.ReferenceTableSchema; TableSchema projectTable = MedSavantDatabase.ProjectTableSchema; SelectQuery query = new SelectQuery(); query.addFromTable(variantMapTable.getTable()); query.addAliasedColumn(projectTable.getDBColumn(ProjectTableSchema.COLUMNNAME_OF_NAME), "A"); query.addAliasedColumn(refTable.getDBColumn(ReferenceTableSchema.COLUMNNAME_OF_NAME), "B"); query.addAllTableColumns(variantMapTable.getTable()); query.addJoin(SelectQuery.JoinType.LEFT_OUTER, variantMapTable.getTable(), refTable.getTable(), BinaryConditionMS.equalTo( variantMapTable.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_REFERENCE_ID), refTable.getDBColumn(ReferenceTableSchema.COLUMNNAME_OF_REFERENCE_ID))); query.addJoin(SelectQuery.JoinType.LEFT_OUTER, variantMapTable.getTable(), projectTable.getTable(), BinaryConditionMS.equalTo( variantMapTable.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_PROJECT_ID), projectTable.getDBColumn(ProjectTableSchema.COLUMNNAME_OF_PROJECT_ID))); ResultSet rs = ConnectionController.executeQuery(sessID, query.toString()); Map<Integer, Map<Integer, ProjectDetails>> map = new HashMap<Integer, Map<Integer, ProjectDetails>>(); List<ProjectDetails> unpublished = new ArrayList<ProjectDetails>(); while (rs.next()) { int projID = rs.getInt(VariantTablemapTableSchema.COLUMNNAME_OF_PROJECT_ID); int refID = rs.getInt(VariantTablemapTableSchema.COLUMNNAME_OF_REFERENCE_ID); int updID = rs.getInt(VariantTablemapTableSchema.COLUMNNAME_OF_UPDATE_ID); boolean published = rs.getBoolean(VariantTablemapTableSchema.COLUMNNAME_OF_PUBLISHED); if (!map.containsKey(projID)) { map.put(projID, new HashMap<Integer, ProjectDetails>()); } if (!map.get(projID).containsKey(refID) || map.get(projID).get(refID).getUpdateID() < updID) { ProjectDetails pd = new ProjectDetails(projID, refID, updID, published, rs.getString("A"), rs.getString("B"), null); map.get(projID).put(refID, pd); if (!pd.isPublished()) { unpublished.add(pd); } } } return unpublished.toArray(new ProjectDetails[0]); } @Override public String[] getReferenceNamesForProject(String sessID, int projID) throws SQLException, SessionExpiredException { TableSchema refTable = MedSavantDatabase.ReferenceTableSchema; TableSchema variantMapTable = MedSavantDatabase.VarianttablemapTableSchema; SelectQuery query = new SelectQuery(); query.addFromTable(variantMapTable.getTable()); query.addColumns(refTable.getDBColumn(ReferenceTableSchema.COLUMNNAME_OF_NAME)); query.addJoin(SelectQuery.JoinType.LEFT_OUTER, variantMapTable.getTable(), refTable.getTable(), BinaryConditionMS.equalTo(refTable.getDBColumn(ReferenceTableSchema.COLUMNNAME_OF_REFERENCE_ID), variantMapTable.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_REFERENCE_ID))); query.addCondition(BinaryConditionMS .equalTo(variantMapTable.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_PROJECT_ID), projID)); query.addCondition(BinaryConditionMS .equalTo(variantMapTable.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_PUBLISHED), true)); ResultSet rs = ConnectionController.executeQuery(sessID, query.toString()); List<String> references = new ArrayList<String>(); while (rs.next()) { if (!references.contains(rs.getString(1))) { references.add(rs.getString(1)); } } return references.toArray(new String[0]); } @Override public int[] getReferenceIDsForProject(String sessID, int projID) throws SQLException, SessionExpiredException { TableSchema table = MedSavantDatabase.VarianttablemapTableSchema; SelectQuery query = new SelectQuery(); query.addFromTable(table.getTable()); query.addColumns(table.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_REFERENCE_ID)); query.addCondition(BinaryConditionMS .equalTo(table.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_PROJECT_ID), projID)); query.addCondition(BinaryConditionMS .equalTo(table.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_PUBLISHED), true)); ResultSet rs = ConnectionController.executeQuery(sessID, query.toString()); List<Integer> references = new ArrayList<Integer>(); while (rs.next()) { references.add(rs.getInt(1)); } return ArrayUtils.toPrimitive(references.toArray(new Integer[0])); } public synchronized void cancelPublish(String sessID, int projID, int refID) throws SQLException, SessionExpiredException, IOException { //Drop all variant tables that are no longer being used. i.e. those that do not have //published=1. TableSchema mapTable = MedSavantDatabase.VarianttablemapTableSchema; String idRestriction = "(" + VariantTablemapTableSchema.COLUMNNAME_OF_REFERENCE_ID + "=" + refID + " AND " + VariantTablemapTableSchema.COLUMNNAME_OF_PROJECT_ID + "=" + projID + ")"; //Select all tables with published=0 that do not also have published=1. String query = "SELECT DISTINCT " + mapTable.getTableName() + "." + MedSavantDatabase.VariantTablemapTableSchema.COLUMNNAME_OF_VARIANT_TABLENAME + "," + mapTable.getTableName() + "." + MedSavantDatabase.VariantTablemapTableSchema.COLUMNNAME_OF_VARIANT_SUBSET_TABLENAME + " FROM " + mapTable.getTableName() + " WHERE " + idRestriction + " AND " + MedSavantDatabase.VariantTablemapTableSchema.COLUMNNAME_OF_PUBLISHED + " = 0 AND " + MedSavantDatabase.VariantTablemapTableSchema.COLUMNNAME_OF_VARIANT_TABLENAME + " NOT IN " + "(SELECT " + MedSavantDatabase.VariantTablemapTableSchema.COLUMNNAME_OF_VARIANT_TABLENAME + " FROM " + mapTable.getTableName() + " WHERE " + idRestriction + " AND " + MedSavantDatabase.VariantTablemapTableSchema.COLUMNNAME_OF_PUBLISHED + " = 1)"; ResultSet rs = ConnectionController.executeQuery(sessID, query); List<Integer> droppedUpdateIDs = new ArrayList<Integer>(); while (rs.next()) { String tableToDrop = rs .getString(MedSavantDatabase.VariantTablemapTableSchema.COLUMNNAME_OF_VARIANT_TABLENAME); VariantManagerUtils.dropTableIfExists(sessID, tableToDrop); tableToDrop = rs .getString(MedSavantDatabase.VariantTablemapTableSchema.COLUMNNAME_OF_VARIANT_SUBSET_TABLENAME); VariantManagerUtils.dropTableIfExists(sessID, tableToDrop); droppedUpdateIDs.add(rs.getInt(MedSavantDatabase.VariantTablemapTableSchema.COLUMNNAME_OF_UPDATE_ID)); } //Delete unpublished tables from the variant table map. DeleteQuery dq1 = new DeleteQuery(mapTable.getTable()); dq1.addCondition(BinaryConditionMS .equalTo(mapTable.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_PROJECT_ID), projID)); dq1.addCondition(BinaryConditionMS .equalTo(mapTable.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_REFERENCE_ID), refID)); dq1.addCondition(BinaryConditionMS .equalTo(mapTable.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_PUBLISHED), 0)); ConnectionController.executeUpdate(sessID, dq1.toString()); LOG.info("Deleting unpublished tables from the variant table map: " + dq1.toString()); TableSchema formatTable = MedSavantDatabase.VariantformatTableSchema; //Delete unpublished variant_format entries. for (int uid : droppedUpdateIDs) { dq1 = new DeleteQuery(formatTable.getTable()); dq1.addCondition(BinaryConditionMS .equalTo(mapTable.getDBColumn(VariantFormatTableSchema.COLUMNNAME_OF_PROJECT_ID), projID)); dq1.addCondition(BinaryConditionMS .equalTo(mapTable.getDBColumn(VariantFormatTableSchema.COLUMNNAME_OF_REFERENCE_ID), refID)); dq1.addCondition(BinaryConditionMS .equalTo(mapTable.getDBColumn(VariantFormatTableSchema.COLUMNNAME_OF_UPDATE_ID), uid)); LOG.info("Deleting unpublished entries from variant format table: " + dq1.toString()); ConnectionController.executeUpdate(sessID, dq1.toString()); } //Drop the main file table, and restore the "backup" copy from the Infobright table. restorePublishedFileTable(sessID); cleanStaleGenotypeFiles(sessID, projID); } //Copies the published Infobright file table over the MyISAM file table. This effectively //undoes any changes that might be pending for unpublished variants. This method is used to //cancel publication, and also to assert that updates to the variant table happen ONLY on //published variants. public void restorePublishedFileTable(String sessID) throws SQLException, SessionExpiredException, IOException { TableSchema fileTable = MedSavantDatabase.VariantFileTableSchema; //Drop the main file table, and restore the "backup" copy from the Infobright table. VariantManagerUtils.dropTableIfExists(sessID, fileTable.getTableName()); SetupMedSavantDatabase.makeVariantFileTable(sessID, false); String query = "INSERT INTO " + fileTable.getTableName() + " SELECT * FROM " + SetupMedSavantDatabase.getVariantFileIBTableName(); ConnectionController.executeUpdate(sessID, query); } private static synchronized void cleanStaleGenotypeFiles(String sessID, int projID) throws SQLException, SessionExpiredException, RemoteException { String database = SessionManager.getInstance().getDatabaseForSession(sessID); TableSchema fileTable = MedSavantDatabase.VariantFileTableSchema; SelectQuery query = new SelectQuery(); query.addFromTable(fileTable.getTable()); query.addColumns(fileTable.getDBColumn(MedSavantDatabase.VariantFileTableSchema.COLUMNNAME_OF_FILE_NAME)); query.addCondition(BinaryConditionMS .equalTo(fileTable.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_PROJECT_ID), projID)); ResultSet rs = ConnectionController.executeQuery(sessID, query.toString()); Set<String> fileNames = new HashSet<String>(); while (rs.next()) { fileNames.add(rs.getString(1)); } File genotypeDir = DirectorySettings.getGenoTypeDirectory(database, projID); File[] listOfFiles = genotypeDir.listFiles(); for (File f : listOfFiles) { if (!fileNames.contains(f.getAbsolutePath())) { f.delete(); } } } /** * Remove any variant tables with updateMin <= updateId <= updateMax. */ @Deprecated public void removeTables(String sessID, int projID, int refID, int updateMax, int updateMin) throws SQLException, SessionExpiredException { //find update ids TableSchema mapTable = MedSavantDatabase.VarianttablemapTableSchema; SelectQuery query = new SelectQuery(); query.addFromTable(mapTable.getTable()); query.addColumns(mapTable.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_UPDATE_ID), mapTable.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_VARIANT_TABLENAME)); query.addCondition(BinaryConditionMS .equalTo(mapTable.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_PROJECT_ID), projID)); query.addCondition(BinaryConditionMS .equalTo(mapTable.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_REFERENCE_ID), refID)); query.addCondition(ComboCondition.and( BinaryCondition.lessThan(mapTable.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_UPDATE_ID), updateMax, true), BinaryCondition.greaterThan( mapTable.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_UPDATE_ID), updateMin, true))); ResultSet rs = ConnectionController.executeQuery(sessID, query.toString()); PooledConnection conn = ConnectionController.connectPooled(sessID); try { while (rs.next()) { int updateId = rs.getInt(1); String tableName = rs.getString(2); //remove variant table VariantManagerUtils.dropTableIfExists(sessID, tableName); //remove from variant tablemap DeleteQuery dq1 = new DeleteQuery(mapTable.getTable()); dq1.addCondition(BinaryConditionMS.equalTo( mapTable.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_PROJECT_ID), projID)); dq1.addCondition(BinaryConditionMS.equalTo( mapTable.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_REFERENCE_ID), refID)); dq1.addCondition(BinaryConditionMS.equalTo( mapTable.getDBColumn(VariantTablemapTableSchema.COLUMNNAME_OF_UPDATE_ID), updateId)); conn.executeUpdate(dq1.toString()); //remove from variant format TableSchema formatTable = MedSavantDatabase.VariantformatTableSchema; DeleteQuery dq2 = new DeleteQuery(formatTable.getTable()); dq2.addCondition(BinaryConditionMS.equalTo( formatTable.getDBColumn(VariantFormatTableSchema.COLUMNNAME_OF_PROJECT_ID), projID)); dq2.addCondition(BinaryConditionMS.equalTo( formatTable.getDBColumn(VariantFormatTableSchema.COLUMNNAME_OF_REFERENCE_ID), refID)); dq2.addCondition(BinaryConditionMS.equalTo( formatTable.getDBColumn(VariantFormatTableSchema.COLUMNNAME_OF_UPDATE_ID), updateId)); } } finally { conn.close(); } } }