RegionSetManager.java

/**
 * 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.io.File;
import java.io.IOException;
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.Collection;
import java.util.Iterator;
import java.util.List;

import com.healthmarketscience.sqlbuilder.InsertQuery;
import com.healthmarketscience.sqlbuilder.SelectQuery;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import org.ut.biolab.medsavant.server.db.MedSavantDatabase;
import org.ut.biolab.medsavant.server.db.MedSavantDatabase.RegionSetColumns;
import org.ut.biolab.medsavant.server.db.MedSavantDatabase.RegionSetMembershipColumns;
import org.ut.biolab.medsavant.server.db.ConnectionController;
import org.ut.biolab.medsavant.server.db.PooledConnection;
import org.ut.biolab.medsavant.shared.importing.FileFormat;
import org.ut.biolab.medsavant.shared.importing.ImportDelimitedFile;
import org.ut.biolab.medsavant.shared.model.GenomicRegion;
import org.ut.biolab.medsavant.shared.model.RegionSet;
import org.ut.biolab.medsavant.server.MedSavantServerUnicastRemoteObject;
import org.ut.biolab.medsavant.shared.model.SessionExpiredException;
import org.ut.biolab.medsavant.shared.serverapi.RegionSetManagerAdapter;


/**
 *
 * @author Andrew
 */
public class RegionSetManager extends MedSavantServerUnicastRemoteObject implements RegionSetManagerAdapter, RegionSetMembershipColumns {
    private static final Log LOG = LogFactory.getLog(RegionSetManager.class);

    private static RegionSetManager instance;

    private RegionSetManager() throws RemoteException, SessionExpiredException {
    }

    public static synchronized RegionSetManager getInstance() throws RemoteException, SessionExpiredException {
        if (instance == null) {
            instance = new RegionSetManager();
        }
        return instance;
    }

    @Override
    public void addRegionSet(String sessID, String regionSetName, int genomeID, char delim, FileFormat fileFormat, int numHeaderLines, int fileID) throws IOException, SQLException, RemoteException, SessionExpiredException {

        Connection conn = ConnectionController.connectPooled(sessID);

        try {
            // TODO: Since we're using the MyISAM engine for this table, rolling back has no effect.
            conn.setAutoCommit(false);

            //add region set
            InsertQuery query = MedSavantDatabase.RegionSetTableSchema.preparedInsert(RegionSetColumns.NAME);
            PreparedStatement prep = conn.prepareStatement(query.toString(), Statement.RETURN_GENERATED_KEYS);
            prep.setString(1, regionSetName);
            prep.executeUpdate();

            ResultSet rs = prep.getGeneratedKeys();
            rs.next();
            int regionSetID = rs.getInt(1);

            File f = NetworkManager.getInstance().getFileByTransferID(sessID, fileID);
            Iterator<String[]> i = ImportDelimitedFile.getFileIterator(f.getAbsolutePath(), delim, numHeaderLines, fileFormat);

            query = MedSavantDatabase.RegionSetMembershipTableSchema.preparedInsert(GENOME_ID, REGION_SET_ID, CHROM, START, END, DESCRIPTION);
            prep = conn.prepareStatement(query.toString());
            while (i.hasNext() && !Thread.currentThread().isInterrupted()){
                String[] line = i.next();
                LOG.info(StringUtils.join(line, '\t'));
                prep.setInt(1, genomeID);
                prep.setInt(2, regionSetID);
                prep.setString(3, line[0]);
                prep.setString(4, line[1]);
                prep.setString(5, line[2]);
                prep.setString(6, line[3]);
                prep.executeUpdate();
            }

            // Since this table is defined with the MyISAM engine, the rollback doesn't actually do anything.
            if (Thread.currentThread().isInterrupted()) {
                conn.rollback();
            } else {
                conn.commit();
            }
            conn.setAutoCommit(true);
        } finally {
            conn.close();
        }
    }

    @Override
    public void removeRegionSet(String sessID, int regionSetID) throws SQLException, SessionExpiredException {

        Connection conn = ConnectionController.connectPooled(sessID);
        try {
            conn.createStatement().executeUpdate(MedSavantDatabase.RegionSetMembershipTableSchema.delete(REGION_SET_ID, regionSetID).toString());
            conn.createStatement().executeUpdate(MedSavantDatabase.RegionSetTableSchema.delete(RegionSetColumns.REGION_SET_ID, regionSetID).toString());
        } finally {
            conn.close();
        }
    }

    @Override
    public List<RegionSet> getRegionSets(String sessID) throws SQLException, SessionExpiredException {

        PooledConnection conn = ConnectionController.connectPooled(sessID);

        try {
            SelectQuery query = MedSavantDatabase.RegionSetMembershipTableSchema.groupBy(REGION_SET_ID).leftJoin(MedSavantDatabase.RegionSetTableSchema, "region_set_id").select(REGION_SET_ID, "NAME", "COUNT(*)");
            LOG.info("getRegionSets: " + query);
            ResultSet rs = conn.createStatement().executeQuery(query.toString());
            List<RegionSet> result = new ArrayList<RegionSet>();
            while (rs.next()) {
                result.add(new RegionSet(rs.getInt(1), rs.getString(2), rs.getInt(3)));
            }
            return result;
        } finally {
            conn.close();
        }
    }

    @Override
    public List<GenomicRegion> getRegionsInSet(String sessID, RegionSet set) throws SQLException, SessionExpiredException {

        Connection conn = ConnectionController.connectPooled(sessID);

        try {
            SelectQuery query = MedSavantDatabase.RegionSetMembershipTableSchema.where(REGION_SET_ID, set.getID()).select(DESCRIPTION, CHROM, START, END);
            LOG.info("getRegionsInSet(" + set.getName() + "): " + query);
            ResultSet rs = conn.createStatement().executeQuery(query.toString());

            List<GenomicRegion> result = new ArrayList<GenomicRegion>();
            while (rs.next()) {
                result.add(new GenomicRegion(rs.getString(1), rs.getString(2), rs.getInt(3), rs.getInt(4)));
            }
            return result;
        } finally {
            conn.close();
        }
    }

    @Override
    public List<GenomicRegion> getRegionsInSets(String sessID, Collection<RegionSet> sets) throws SQLException, SessionExpiredException {

        PooledConnection conn = ConnectionController.connectPooled(sessID);

        try {
            List<Integer> ids = new ArrayList<Integer>(sets.size());
            int maxRegions = 0;
            for (RegionSet s: sets) {
                ids.add(s.getID());
                maxRegions += s.getSize();
            }
            SelectQuery query = MedSavantDatabase.RegionSetMembershipTableSchema.distinct().whereIn(REGION_SET_ID, ids).select(DESCRIPTION, CHROM, START, END);
            ResultSet rs = conn.executeQuery(query.toString());

            List<GenomicRegion> result = new ArrayList<GenomicRegion>();
            while (rs.next()) {
                result.add(new GenomicRegion(rs.getString(1), rs.getString(2), rs.getInt(3), rs.getInt(4)));
            }
            return result;
        } finally {
            conn.close();
        }
    }

    @Override
    public void addToRegionSet(String sessID, RegionSet set, int genomeID, String chrom, int start, int end, String desc) throws SQLException, RemoteException, SessionExpiredException{
        PooledConnection conn = ConnectionController.connectPooled(sessID);
        try{
            InsertQuery query = MedSavantDatabase.RegionSetMembershipTableSchema.insert(GENOME_ID, genomeID, REGION_SET_ID, set.getID(), CHROM, chrom, START, start, END, end, DESCRIPTION, desc);
            conn.executeUpdate(query.toString());
        } finally{
            conn.close();
        }

    }
}