DatabaseDocument.java

  
  /**
   * Similar to <code>String.indexOf(String, int)</code>, but it ignores
   * case
   */
   private static int indexOfIgnoreCase(String textToSearch, 
                                        String pattern,
                                        int fromIndex)
  {
    int n = pattern.length();
    while(textToSearch.length() > ((fromIndex + n) - 1))
    {
      if(textToSearch.regionMatches(true, fromIndex, pattern, 0, n))
        return fromIndex;
      fromIndex++;
    }
    return -1;
  }

  
  /**
   * Get a list of the CV names
   * 
   * @return
   */
  public static List getCvControledCurationNames()
  {
    if(cvControledCuratioNames != null)
      return cvControledCuratioNames;
    cvControledCuratioNames = new Vector();
    final Enumeration enum_cvterm = cvterms.elements();
    while(enum_cvterm.hasMoreElements())
    {
      final CvTerm cvTerm = (CvTerm)enum_cvterm.nextElement();
      final String cvNameStr = cvTerm.getCv().getName();
      
      if(cvNameStr.startsWith(DatabaseDocument.CONTROLLED_CURATION_TAG_CVNAME) && 
         !cvControledCuratioNames.contains(cvNameStr))
        cvControledCuratioNames.add(cvNameStr);
    }
    
    return cvControledCuratioNames;
  }

  /**
   * Look up synonym type names e.g. synonym, systematic_id.
   * @return    the synonym tag names
   */
  public static String[] getSynonymTypeNames(final String cv_name, 
                                             final GFFStreamFeature feature)
  {
    if(cvterms == null)
    {
      DatabaseDocument doc = (DatabaseDocument)feature.getDocumentEntry().getDocument();
      try
      {
        Cv cv = new Cv();
        cv.setName(cv_name);
        List synonymCvTerms = doc.getDAO().getCvTermByNameInCv(null, cv);
        String synonymNames[] = new String[synonymCvTerms.size()];
        for(int i=0; i<synonymCvTerms.size(); i++)
          synonymNames[i] = ((CvTerm) synonymCvTerms.get(i)).getName();

        return synonymNames;
      }
      catch(ConnectException e){}
      catch(SQLException e){}   
    }
    
    Vector synonym_names = new Vector();
    Enumeration cvterm_enum = cvterms.elements();
    while(cvterm_enum.hasMoreElements())
    {
      CvTerm cvterm = (CvTerm)cvterm_enum.nextElement();
      if(cvterm.getCv().getName().equals(cv_name))
        synonym_names.add(cvterm.getName());
    }
    
    return (String[])synonym_names.toArray(
                       new String[synonym_names.size()]);
  }
  
  public void insertCvTerm(CvTerm cvTerm)
  {
    final GmodDAO dao = getDAOOnly();
    dao.persist(cvTerm);
    cvTerm = dao.getCvTermByNameAndCvName(cvTerm.getName(), cvTerm.getCv().getName());
    cvterms.put(new Integer(cvTerm.getCvTermId()), cvTerm);
  }
  
  /**
   * Get the sequence for a feature.
   * @param dao   the data access object
   * @param buff  the buffer to add the sequence to
   * @return      the resulting buffer
   * @throws java.sql.SQLException
   */
  private Feature getChadoSequence(GmodDAO dao, ByteBuffer buff)
  {
    Feature feature = dao.getFeatureById(Integer.parseInt(srcFeatureId));
    getChadoSequence(feature, buff);
    return feature;
  }
  
  /**
   * Find the gene by the ID or synonym on the gene or transcript.
   * @param id
   * @return
   */
  public Feature getChadoGeneByAnyCurrentName(String id)
  {
    Feature chadoFeature = 
      (Feature)(getDAOOnly().getFeaturesByAnyCurrentName(id).get(0));
    
    if(!chadoFeature.getCvTerm().equals("gene"))
    {
      Iterator<FeatureRelationship> parents =
        chadoFeature.getFeatureRelationshipsForSubjectId().iterator();
      while(parents.hasNext())
      {
        FeatureRelationship fr = parents.next();
        Feature parent = fr.getFeatureByObjectId();
        if(parent.getCvTerm().getName().equals("gene"))
          chadoFeature = parent;
      }
    }
    return chadoFeature;
  }
  
  
  /**
   * Get the sequence for a feature.
   * @param dao   the data access object
   * @param buff  the buffer to add the sequence to
   * @return      the resulting buffer
   * @throws java.sql.SQLException
   */
  private void getChadoSequence(final Feature feature, ByteBuffer buff)
  {
    buff.append("##FASTA\n>");
    buff.append(feature.getUniqueName());
    buff.append("\n");
    buff.append(feature.getResidues());
  }

  /**
   * Get the CDS FeatureLoc's associated with a give protein
   * @param peptideName
   * @return
   */
  public List getCdsFeatureLocsByPeptideName(final String peptideName)
  {
    Feature peptideFeature = getFeatureByUniquename(peptideName);
    Collection frs = peptideFeature.getFeatureRelationshipsForSubjectId();
    Iterator it = frs.iterator();
    Feature transcriptFeature = null;
    while(it.hasNext())
    {
      FeatureRelationship fr = (FeatureRelationship)it.next();
      if(fr.getCvTerm().getName().equalsIgnoreCase("derives_from"))
      {
        transcriptFeature = fr.getFeatureByObjectId();
        logger4j.debug("TRANSCRIPT :: "+transcriptFeature.getUniqueName());
        break;
      }
    }
    
    if(transcriptFeature == null)
      return null;
    return getCdsFeatureLocsByTranscriptName(transcriptFeature.getUniqueName());
  }
  
  /**
   * Get the CDS FeatureLoc's associated with a given transcript
   * @param transcriptName
   * @return
   */
  public List getCdsFeatureLocsByTranscriptName(final String transcriptName)
  {
    Feature transcriptFeature = getFeatureByUniquename(transcriptName);
    if(transcriptFeature == null)
      return null;
    
    Collection frs = transcriptFeature.getFeatureRelationshipsForObjectId();
    Iterator it = frs.iterator();
    List cdsFeatureLocs = new Vector();
    while(it.hasNext())
    {
      FeatureRelationship fr = (FeatureRelationship)it.next();
      org.gmod.schema.sequence.Feature child = fr.getFeatureBySubjectId();
      if(child.getCvTerm().getName().equals("exon") || 
         child.getCvTerm().getName().equals("pseudogenic_exon"))
      {
        Collection featureLocs = child.getFeatureLocsForFeatureId();

        Iterator it2 = featureLocs.iterator();
        while(it2.hasNext())
        {
          FeatureLoc featureLoc = (FeatureLoc) it2.next();
          cdsFeatureLocs.add(featureLoc);
        }
      }
    }

    Collections.sort(cdsFeatureLocs, new LocationComarator());
    return cdsFeatureLocs;
  }
  
  /**
   * Get the sequence for a feature.
   * @param uniqueName   the feature
   * @return      the resulting buffer
   */
  public PartialSequence getChadoSequence(final String uniqueName)
  {
    Feature feature = (Feature) getDAOOnly().getResiduesByUniqueName(uniqueName).get(0);
    char[] c = getChars(feature.getResidues());
    
    PartialSequence ps = new PartialSequence(c, feature.getSeqLen(),
                                feature.getFeatureLoc().getFmin().intValue()+1,
                                feature.getFeatureLoc().getStrand(),
                                feature.getFeatureLoc().getPhase());
    return ps;
  }
  
  /**
   * Convert byte array to char array
   * @param b byte array
   * @return  char array
   */
  private char[] getChars(final byte b[])
  {
    char[] c = new char[b.length];

    for(int i = 0; i < b.length; i++)
      c[i] = (char)b[i];
    return c;
  }
  
  /**
   * Get the <code>List</code> of available schemas.
   * @return  the <code>List</code> of available schemas
   */
  public List getSchema()
  {
    return schema_list;
  }
  
  public Feature getFeatureByUniquename(final String uniqueName) 
  {
    GmodDAO dao = getDAOOnly();
    return (Feature) dao.getFeatureByUniqueName(uniqueName, null);
  }
  
  /**
   * Given a gene unique name return the poplypeptide chado features that belong
   * to that gene
   * @param geneName
   * @return
   */
  public Vector getPolypeptideFeatures(final String geneName)
  {
    Feature geneFeature =  getFeatureByUniquename(geneName);
    if(geneFeature == null)
      return null;
    
    Collection frs = geneFeature.getFeatureRelationshipsForObjectId();
    Iterator it = frs.iterator();
    List transcripts = new Vector(frs.size());
    while(it.hasNext())
    {
      FeatureRelationship fr = (FeatureRelationship)it.next();
      transcripts.add(fr.getFeatureBySubjectId());
    }
    
    Vector polypep = new Vector();
    for(int i=0; i<transcripts.size(); i++)
    {
      org.gmod.schema.sequence.Feature transcript = 
        (org.gmod.schema.sequence.Feature) transcripts.get(i);
      frs = transcript.getFeatureRelationshipsForObjectId();
      it = frs.iterator();
      while(it.hasNext())
      {
        FeatureRelationship fr = (FeatureRelationship)it.next();
        if(fr.getCvTerm().getName().equalsIgnoreCase("derives_from"))
          if(fr.getFeatureBySubjectId().getCvTerm().getName().equalsIgnoreCase("polypeptide"))
            polypep.add(fr.getFeatureBySubjectId());
      }
    }
    return polypep;
  }
  
  /**
   * Given a gene unique name return the poplypeptides that belong
   * to that gene
   * @param geneName
   * @return
   */
  /*public Vector getPolypeptideNames(final String geneName)
  {
    Vector polypeptides = getPolypeptideFeatures(geneName);
    Vector polypeptideNames = new Vector(polypeptides.size());
    for(int i=0; i<polypeptides.size(); i++)
    {
      Feature feature = (Feature)polypeptides.get(i);
      polypeptideNames.add(feature.getUniqueName());
    }
    return polypeptideNames;
  }*/
  
  public List getClustersByFeatureIds(final List featureIds)
  {
    GmodDAO dao = getDAOOnly();
    return dao.getClustersByFeatureIds(featureIds);
  }
  
  public List getParentFeaturesByChildFeatureIds(final List subjectIds)
  {
    GmodDAO dao = getDAOOnly();
    return dao.getParentFeaturesByChildFeatureIds(subjectIds);
  }
  
  public List getFeatureDbXRefsByFeatureId(final List featureIds)
  {
    GmodDAO dao = getDAOOnly();
    return dao.getFeatureDbXRefsByFeatureId(featureIds);
  }
  
  
  /**
   * Used by SimilarityLazyQualifierValue.bulkRetrieve() to get the match features
   * @param featureIds the <code>List</code> of feature_id's
   * @return  the corresponding features
   */
  public List getFeaturesByListOfIds(final List featureIds)
  {
    GmodDAO dao = getDAOOnly();
    return dao.getFeaturesByListOfIds(featureIds);
  }
  
  public List getFeaturePropByFeatureIds(final List featureIds)
  {
    GmodDAO dao = getDAOOnly();
    return dao.getFeaturePropByFeatureIds(featureIds);
  }
  
  public List getSimilarityMatches(List featureIds)
  {
    GmodDAO dao = getDAOOnly();
    if(featureIds == null)
      return dao.getSimilarityMatches(new Integer(srcFeatureId));
    else
      return dao.getSimilarityMatchesByFeatureIds(featureIds);
  }
  
  public List getFeatureLocsByListOfIds(List featureIds)
  {
    GmodDAO dao = getDAOOnly();
    return dao.getFeatureLocsByListOfIds(featureIds);
  }
  
  /**
   * Test the database connection.
   * @throws ConnectException
   * @throws SQLException
   */
  public void ping() throws ConnectException, SQLException
  {
    getDAO().getAllCvs();
  }
  
  /**
   * Load the cvterms
   * @throws ConnectException
   * @throws SQLException
   */
  public void loadCvTerms() throws ConnectException, SQLException
  {
    GmodDAO dao = getDAO();
    cvThread = new CvTermThread(dao);
    cvThread.start();
  }
  
  /**
   * Get a list of the organisms with sequences
   * @return
   * @throws ConnectException
   * @throws java.sql.SQLException
   */
  public List getOrganismsContainingSrcFeatures()
         throws ConnectException, java.sql.SQLException
  {
    List list = null;
    try
    { 
      GmodDAO dao = getDAO();
      cvThread = new CvTermThread(dao);
      cvThread.start();
      
      list = dao.getOrganismsContainingSrcFeatures();
      
      Collections.sort(list, new Comparator()
      {
        public int compare(Object o1, Object o2)
        {
          Organism org1 = (Organism)o1;
          Organism org2 = (Organism)o2;
          
          String name1 = org1.getCommonName();
          String name2 = org2.getCommonName();
          
          if(name1 == null)
            name1 = org1.getGenus() + "." + org1.getSpecies();
          
          if(name2 == null)
            name2 = org2.getGenus() + "." + org2.getSpecies();
          return name1.compareToIgnoreCase( name2 );
        } 
      });  
    }
    catch(RuntimeException sqlExp)
    {
      JOptionPane.showMessageDialog(null, "SQL Problems...\n"+
                                    getLocation()+"\n"+
                                    sqlExp.getMessage(), 
                                    "SQL Error",
                                    JOptionPane.ERROR_MESSAGE);
      
      logger4j.debug(sqlExp.getMessage());
    }
    catch(ConnectException exp)
    {
      JOptionPane.showMessageDialog(null, "Connection Problems...\n"+
            exp.getMessage(), 
            "Connection Error",
            JOptionPane.ERROR_MESSAGE);
      logger4j.debug(exp.getMessage());
      throw exp;
    }
    catch(java.sql.SQLException sqlExp)
    {
      JOptionPane.showMessageDialog(null, "SQL Problems....\n"+
                                    getLocation()+"\n"+
                                    sqlExp.getMessage(), 
                                    "SQL Error",
                                    JOptionPane.ERROR_MESSAGE);
      logger4j.debug(sqlExp.getMessage());
      throw sqlExp;
    }

    return list;
  }
  
  public static boolean isCvThreadAlive()
  {
    if(cvThread == null)
      return false;

    if(cvThread.isAlive())
      return true;
    else
      return false;
  }
  
  public List getResidueFeatures(Integer organismId)
  {
    return getDAOOnly().getResidueFeatures(organismId);  
  }
  
  /**
   * 
   */
  public void showCvTermLookUp()
  {
    try
    {
      new ChadoCvTermView( getDAO() );
    }
    catch(ConnectException e)
    {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }
    catch(SQLException e)
    {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }
  }
  
  /**
   * Get a list of the available graphs
   * @return
   */
  public List getGraphs()
  {
    GmodDAO dao = getDAOOnly();
    
    List list = dao.getTableColumns("graph");
    if(list == null || list.size() == 0)
      return null;
    return dao.getGraphs(Integer.parseInt(srcFeatureId));
  }
  
  /**
   * Get the graph data from the database. 
   * This uses JDBC and the org.postgresql.largeobject API.
   * @param graphId
   * @param name
   * @return
   */
  public LargeObjectDocument getGraphData(int graphId, String name)
  {
    LargeObjectDocument doc = null;
    try
    {
      // this causes class cast problems probably because it gets
      // a pool connection rather than the underlying real connection
      // Connection conn = ((SimpleDataSource)connIB.getDataSource()).getConnection();
      SimpleDataSource ds = (SimpleDataSource)connIB.getDataSource();
      Connection conn = DriverManager.getConnection(
          ds.getJdbcUrl(), ds.getJdbcUsername(), ds.getJdbcPassword());


      // All LargeObject API calls must be within a transaction
      conn.setAutoCommit(false);

      // Get the Large Object Manager to perform operations with
      LargeObjectManager lobj =
         ((org.postgresql.PGConnection)conn).getLargeObjectAPI();
      
      PreparedStatement pstmt =
          conn.prepareStatement("SELECT * FROM graph.graph WHERE graph_id=?");
      pstmt.setInt(1, graphId);
      ResultSet rs = pstmt.executeQuery();
      
      if (rs != null) 
      {
        while(rs.next()) 
        {
          // open the large object for reading
          int oid = rs.getInt(5);
          doc = new LargeObjectDocument(ds.getJdbcUrl(), name, 
             lobj.open(oid, LargeObjectManager.READ));
        }
        rs.close();
      }
      pstmt.close();
    }
    catch (SQLException e)
    {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }
    return doc;
  }
  
  /**
   * Get the data access object (DAO).
   * @return data access object
   */
  private GmodDAO getDAO()
     throws java.net.ConnectException, SQLException
  { 
    if(!iBatis)
    {
      if(jdbcDAO == null)
       jdbcDAO = new JdbcDAO((String)getLocation(), pfield); 
      return jdbcDAO;
    }
    else
    {   
      if(connIB == null)
      {
        System.setProperty("chado", (String)getLocation());
        connIB = new IBatisDAO(pfield);
      }
      return connIB;
    }
  }
  
  /**
   * Get the username for this connection
   * @return
   */
  public String getUserName()
  {
    // "localhost:10001/backup?chado"
    
    String url = (String)getLocation();
    int index  = url.indexOf("?");
    
    String userName = url.substring(index+1).trim();
    if(userName.startsWith("user="))
      userName = userName.substring(5);
    
    return userName;
  }
  
  private GmodDAO getDAOOnly()
  {
    GmodDAO dao = null;
    try
    {
      dao = getDAO();
    }
    catch(RuntimeException sqlExp)
    {
      JOptionPane.showMessageDialog(null, "SQL Problems...\n"+
                                    sqlExp.getMessage(), 
                                    "SQL Error",
                                    JOptionPane.ERROR_MESSAGE);
    }
    catch(ConnectException exp)
    {
      JOptionPane.showMessageDialog(null, "Connection Problems...\n"+
            exp.getMessage(), 
            "Connection Error",
            JOptionPane.ERROR_MESSAGE);
    }
    catch(java.sql.SQLException sqlExp)
    {
      JOptionPane.showMessageDialog(null, "SQL Problems....\n"+
                                    sqlExp.getMessage(), 
                                    "SQL Error",
                                    JOptionPane.ERROR_MESSAGE);
    }
    return dao;
  }


  /**
   * Create a new OutputStream object from this Document. The contents of the
   * Document can be written from the stream.
   * 
   * @exception IOException
   *              Thrown if the Document can't be written.
   */
  public OutputStream getOutputStream() throws IOException
  {
    final File write_file = new File(System.getProperty("user.dir")+
                                     System.getProperty("file.separator")+
                                     getName());

    final FileOutputStream file_output_stream =
      new FileOutputStream(write_file);

    if(write_file.getName().endsWith(".gz")) 
    {
      // assume this file should be gzipped
      return new java.util.zip.GZIPOutputStream (file_output_stream);
    } 
    else 
      return file_output_stream;
  }

  /**
   * Commit the <code>ChadoTransaction</code> SQL back to the
   * database.
   * @param sql the collection of <code>ChadoTransaction</code> objects
   * @return
   */
  public int commit(final Vector sql,
                    final boolean force)
  {
    GmodDAO dao = null;
    int ncommit = -1;
    final Hashtable featureIdStore = new Hashtable();
    boolean useTransactions = false;

    try
    {
      dao = getDAO();
      
      if(!force && dao instanceof IBatisDAO)
        useTransactions = true;
      
      if(useTransactions)
      {
        ((IBatisDAO) dao).startTransaction();
        logger4j.debug("START TRANSACTION");
      }
      boolean unchanged;
      
      //
      // check feature timestamps have not changed
      Vector names_checked = new Vector();
      for(int i = 0; i < sql.size(); i++)
      {
        final ChadoTransaction tsn = (ChadoTransaction)sql.get(i);
        final Object uniquenames[] = getUniqueNames(tsn);
        
        if(uniquenames == null)
          continue;
        
        for(int j=0; j<uniquenames.length; j++)
        {
          final String uniquename = (String) uniquenames[j];
          
          if(uniquename == null || names_checked.contains(uniquename))
            continue;

          names_checked.add(uniquename);
          final String keyName = tsn.getFeatureKey();

          unchanged = checkFeatureTimestamp(schema, uniquename, 
              dao, keyName, featureIdStore, tsn);
          if(!unchanged)
          {
            if(useTransactions)
              ((IBatisDAO) dao).endTransaction();
            return 0;
          }
        }
      }  

      final Timestamp ts = new Timestamp(new java.util.Date().getTime());
      //
      // commit to database
      for(ncommit = 0; ncommit < sql.size(); ncommit++)
      {
        try
        {
          ChadoTransaction tsn = (ChadoTransaction) sql.get(ncommit);
          commitChadoTransaction(tsn, dao, ts);
        }
        catch (RuntimeException re)
        {
          if(!force)
            throw re;
          logger4j.warn(constructExceptionMessage(re, sql, ncommit));
          logger4j.warn("NOW TRYING TO CONTINUE TO COMMIT");
        }
      }

      //
      // update timelastmodified timestamp
      names_checked = new Vector();
      for(int i = 0; i < sql.size(); i++)
      {
        final ChadoTransaction tsn = (ChadoTransaction) sql.get(i);
        final Object uniquenames[] = getUniqueNames(tsn);
             
        if(uniquenames == null)
          continue;
        
        if(tsn.getType() == ChadoTransaction.UPDATE &&
           tsn.getFeatureObject() instanceof Feature)
        {
          for(int j=0; j<uniquenames.length; j++)
            names_checked.add((String) uniquenames[j]);
          continue;  
        }
        
        for(int j=0; j<uniquenames.length; j++)
        {
          final String uniquename = (String) uniquenames[j];
          if(uniquename == null || names_checked.contains(uniquename))
            continue;

          names_checked.add(uniquename);

          final Feature feature;

          // retieve from featureId store
          if(featureIdStore != null && featureIdStore.containsKey(uniquename))
          {
            Feature f = (Feature) featureIdStore.get(uniquename);

            feature = new Feature();
            feature.setFeatureId(f.getFeatureId());
            feature.setUniqueName(uniquename);
            feature.setObsolete(f.isObsolete());
          }
          else
            feature = dao.getFeatureByUniqueName(uniquename, 
                                       tsn.getFeatureKey());
          if(feature != null)
          {
            feature.setTimeLastModified(ts);
            feature.setName("0");  // do not change name
            dao.merge(feature);
          }
        }

        GFFStreamFeature gff_feature = (GFFStreamFeature) tsn.getGff_feature();
        gff_feature.setLastModified(ts);
      }

      final String nocommit = System.getProperty("nocommit");
      if( useTransactions && 
          (nocommit == null || nocommit.equals("false")))
      {
        ((IBatisDAO) dao).commitTransaction();
        logger4j.debug("TRANSACTION COMPLETE");
      }
      else if(useTransactions && 
          (nocommit != null && nocommit.equals("true")))
        logger4j.debug("TRANSACTION NOT COMMITTED : nocommit property set to true");
    }
    catch (java.sql.SQLException sqlExp)
    {
      JOptionPane.showMessageDialog(null, "Problems Writing...\n" +
                                    sqlExp.getMessage(),
                                    "Problems Writing to Database ",
                                    JOptionPane.ERROR_MESSAGE);
      sqlExp.printStackTrace();
    }
    catch (java.net.ConnectException conn_ex)
    {
      JOptionPane.showMessageDialog(null, "Problems connecting..."+
                                    conn_ex.getMessage(),
                                    "Database Connection Error - Check Server",
                                    JOptionPane.ERROR_MESSAGE);
      conn_ex.printStackTrace();
    }
    catch (RuntimeException re)
    {
      final String msg = constructExceptionMessage(re, sql, ncommit);
      JOptionPane.showMessageDialog(null, msg,
          "Problems Writing to Database ",
          JOptionPane.ERROR_MESSAGE);
      logger4j.error(msg);
      //re.printStackTrace();
    }
    finally
    {
      if(useTransactions)
        try
        {
          ((IBatisDAO) dao).endTransaction();
          logger4j.debug("END TRANSACTION");
        }
        catch(SQLException e){ e.printStackTrace(); }
    }
    
    if(featureIdStore != null)
      featureIdStore.clear();
        
    return ncommit;
  }
  
  /**
   * Get the uniquenames involved in a transaction
   * @param tsn
   * @return
   */
  private Object[] getUniqueNames(final ChadoTransaction tsn)
  {
    if(tsn.getGff_feature() == null)
      return null;
    if(tsn.getGff_feature().getSegmentRangeStore() == null ||
       tsn.getGff_feature().getSegmentRangeStore().size() < 2 ||
       tsn.getFeatureObject() instanceof FeatureProp)
      return new Object[]{ tsn.getUniquename() };
    else 
      return tsn.getGff_feature().getSegmentRangeStore().keySet().toArray();
  }
  
  /**
   * Construct an exeption message from the ChadoTransaction
   * @param re
   * @param sql
   * @param ncommit
   * @return
   */
  private String constructExceptionMessage(final RuntimeException re,
                                           final Vector sql,
                                           final int ncommit)
  {
    String msg = "";
    if(ncommit > -1 && ncommit < sql.size())
    {
      final ChadoTransaction t_failed = (ChadoTransaction)sql.get(ncommit);
      
      if(t_failed.getType() == ChadoTransaction.DELETE)
        msg = "DELETE failed ";
      else if(t_failed.getType() == ChadoTransaction.INSERT)
        msg = "INSERT failed ";
      else if(t_failed.getType() == ChadoTransaction.UPDATE)
        msg = "UPDATE failed ";
        
      if(t_failed.getUniquename() != null)
        msg = msg + "for " + t_failed.getUniquename()+":";
      else if(t_failed.getFeatureObject() != null &&
              t_failed.getFeatureObject() instanceof Feature)
      {
        final Feature chadoFeature = (Feature)t_failed.getFeatureObject();
        if(chadoFeature.getUniqueName() != null)
          msg = msg + "for " + chadoFeature.getUniqueName() +":";
      }
        
      msg = msg+"\n";
    }
    
    return msg + re.getMessage();  
  }
  
  /**
   * Commit a single chado transaction
   * @param tsn
   * @param dao
   */
  private void commitChadoTransaction(final ChadoTransaction tsn,
                                      final GmodDAO dao,
                                      final Timestamp ts)
  {
    if(tsn.getType() == ChadoTransaction.UPDATE)
    {
      if(tsn.getFeatureObject() instanceof Feature)
      {
        Feature feature = (Feature)tsn.getFeatureObject();

        if(feature.getUniqueName() != null)
        {
          final String uniquename;
          if(tsn.getOldUniquename() != null)
            uniquename = (String)tsn.getOldUniquename();
          else
            uniquename = feature.getUniqueName();
          
          Feature old_feature
              = dao.getFeatureByUniqueName(uniquename, tsn.getFeatureKey());
          
          if(old_feature != null)
            feature.setFeatureId( old_feature.getFeatureId() );
          
          tsn.setOldUniquename(feature.getUniqueName());
        }
        feature.setTimeLastModified(ts);
      }
      
      if(tsn.getFeatureObject() instanceof FeatureLoc)
      {
        // update any featurelocs on the polypeptide
        String keyStr = tsn.getGff_feature().getKey().getKeyString();
        if(keyStr.equals("polypeptide"))
          adjustChildCoordinates((FeatureLoc)tsn.getFeatureObject(), dao);
      }
      
      dao.merge(tsn.getFeatureObject());
      
      if(tsn.getFeatureObject() instanceof FeatureLoc)
      {
        // if CDS featureloc is changed update residues on
        // associated features
        List tsns = getUpdateResiduesColumnTransactions(tsn);
        if(tsns != null)
        {
          for(int i=0; i<tsns.size(); i++)
            dao.merge( ((ChadoTransaction)tsns.get(i)).getFeatureObject() );
        }
      }
    }
    else if(tsn.getType() == ChadoTransaction.INSERT)
    {
      if(tsn.getFeatureObject() instanceof FeatureCvTerm)
        ArtemisUtils.inserFeatureCvTerm(dao, (FeatureCvTerm)tsn.getFeatureObject());
      else
      {
        // set srcfeature_id
        if(tsn.getFeatureObject() instanceof Feature &&
            ((Feature) tsn.getFeatureObject()).getFeatureLoc() != null)
        {
          FeatureLoc featureloc = ((Feature) tsn.getFeatureObject()).getFeatureLoc();
          Feature featureBySrcFeatureId = new Feature();
          featureBySrcFeatureId.setFeatureId(Integer.parseInt(srcFeatureId));
          featureloc.setFeatureBySrcFeatureId(featureBySrcFeatureId);
        }

        if(tsn.getFeatureObject() instanceof Feature &&
           tsn.getGff_feature() != null)
        {
          String keyStr = tsn.getGff_feature().getKey().getKeyString();
          if(GeneUtils.isFeatureToUpdateResidues(keyStr))
          {
            String residues = GeneUtils.deriveResidues(tsn.getGff_feature());
            if(residues != null)
              ((Feature)tsn.getFeatureObject()).setResidues(residues.getBytes());
          }
        }
        dao.persist(tsn.getFeatureObject());
      }
      
    }
    else if(tsn.getType() == ChadoTransaction.DELETE)
    {
      if(tsn.getFeatureObject() instanceof FeatureCvTerm)
        ArtemisUtils.deleteFeatureCvTerm(dao, (FeatureCvTerm)tsn.getFeatureObject());
      else
        dao.delete(tsn.getFeatureObject());
    }
  }
  
  /**
   * Check the <code>Timestamp</code> on a feature (for versioning).
   * @param schema      the schema
   * @param uniquename  the feature uniquename
   * @param timestamp   the last read feature timestamp