[Gmod-apollo-cmts] CVS: apollo/src/java/apollo/dataadapter/chado/jdbc FlybaseChadoInstance.java,1.16,1.17 JdbcChadoAdapter.java,1.60,1.61 TigrSybilChadoInstance.java,1.9,1.10

[Mark G. <mg...@us...>]

Update of /cvsroot/gmod/apollo/src/java/apollo/dataadapter/chado/jdbc
In directory sc8-pr-cvs1.sourceforge.net:/tmp/cvs-serv26798/src/java/apollo/dataadapter/chado/jdbc

Modified Files:
	FlybaseChadoInstance.java JdbcChadoAdapter.java 
	TigrSybilChadoInstance.java 
Log Message:
Retrieving dbxrefs for gene. One dbxref comes from the feature table
itself (is this some primary dbxref or something?) and the rest come
from the many-to-many feature_dbxref - the 2 are unioned in one
query. still doesnt seem to bad on time (5 seconds for heix gene) -
need to try on scaffold.



Index: FlybaseChadoInstance.java
===================================================================
RCS file: /cvsroot/gmod/apollo/src/java/apollo/dataadapter/chado/jdbc/FlybaseChadoInstance.java,v
retrieving revision 1.16
retrieving revision 1.17
diff -C2 -d -r1.16 -r1.17
*** FlybaseChadoInstance.java	15 Jul 2004 17:24:03 -0000	1.16
--- FlybaseChadoInstance.java	16 Jul 2004 00:10:08 -0000	1.17
***************
*** 201,205 ****
      boolean doGenes = false;
      //if (doGenes)
!       jdbcChadoAdapter.addGeneModels(conn, seq, annotations, featLocImp, true,true);
      adapter.fireProgressEvent(new ProgressEvent(this,d100, "Gene models retrieved"));
      debugPrintTime();
--- 201,205 ----
      boolean doGenes = false;
      //if (doGenes)
!       jdbcChadoAdapter.addGeneModels(conn, seq, annotations, featLocImp, true,true,true);
      adapter.fireProgressEvent(new ProgressEvent(this,d100, "Gene models retrieved"));
      debugPrintTime();

Index: JdbcChadoAdapter.java
===================================================================
RCS file: /cvsroot/gmod/apollo/src/java/apollo/dataadapter/chado/jdbc/JdbcChadoAdapter.java,v
retrieving revision 1.60
retrieving revision 1.61
diff -C2 -d -r1.60 -r1.61
*** JdbcChadoAdapter.java	15 Jul 2004 17:35:16 -0000	1.60
--- JdbcChadoAdapter.java	16 Jul 2004 00:10:08 -0000	1.61
***************
*** 646,650 ****
    protected void addGeneModels(Connection c, SequenceI refSeq, StrandedFeatureSet sfs,
                                 FeatureLocImplementation featLoc, boolean getFeatProps,
!                                boolean getSynonyms) {
      // NOTE: the following assumes that negative numbers are not being used as Chado feature_ids
      int currentGeneId = -1; 
--- 646,650 ----
    protected void addGeneModels(Connection c, SequenceI refSeq, StrandedFeatureSet sfs,
                                 FeatureLocImplementation featLoc, boolean getFeatProps,
!                                boolean getSynonyms, boolean getDbXRefs) {
      // NOTE: the following assumes that negative numbers are not being used as Chado feature_ids
      int currentGeneId = -1; 
***************
*** 676,680 ****
        "exonloc.rank, exonloc." + fminCol + " as fmin, exonloc."+fmaxCol+" as fmax,"
        +" exonloc.strand, exonloc.phase, exonloc.locgroup, " +
!       " trans.timelastmodified as transDate " +
        "FROM featureloc exonloc, feature exon, feature_relationship exon2trans, feature trans,  " +
        "     feature_relationship trans2gene, feature gene " +
--- 676,681 ----
        "exonloc.rank, exonloc." + fminCol + " as fmin, exonloc."+fmaxCol+" as fmax,"
        +" exonloc.strand, exonloc.phase, exonloc.locgroup, " +
!       " trans.timelastmodified as transDate, " +
!       " gene.dbxref_id as geneDbXRefId "+
        "FROM featureloc exonloc, feature exon, feature_relationship exon2trans, feature trans,  " +
        "     feature_relationship trans2gene, feature gene " +
***************
*** 742,745 ****
--- 743,748 ----
            if (getSynonyms)
              addAnnotSynonyms(currentGene,currentGeneId);
+           if (getDbXRefs) 
+             addAnnotDbXRefs(currentGene,currentGeneId,rs.getInt("geneDbXRefId"));
  	  genes.addElement(currentGene);
  	}
***************
*** 836,840 ****
  
    /** Retrieve all the feature props for the AnnotatedFeatureI(Transcript or gene), 
!       and stuff them in the appropriate places (as props or actual vars) */
    private void addAnnotationFeatProps(AnnotatedFeatureI annot,int feature_id) {
      // should we just select fp.type_id and map 
--- 839,846 ----
  
    /** Retrieve all the feature props for the AnnotatedFeatureI(Transcript or gene), 
!       and stuff them in the appropriate places (as props or actual vars).
!       2 reasons to do as separate query: may or may not have props so it would
!       have to add a troublesome outer join to main query, also may want to get these
!       separately on demand (when annot info brought up) */
    private void addAnnotationFeatProps(AnnotatedFeatureI annot,int feature_id) {
      // should we just select fp.type_id and map 
***************
*** 921,924 ****
--- 927,955 ----
    }
  
+   /** addGeneModels helper function. queries feature_dbxref, dbxref, and db for db
+       cross references. One dbxref is given in the feature table itself, so theres actually
+       2 retrievals that are unioned into one query */
+   private void addAnnotDbXRefs(AnnotatedFeatureI annot,int feature_id,int dbXRefId) {
+     String sql = "SELECT dx.accession, db.name as dbName "+
+       "FROM feature_dbxref fd, dbxref dx, db "+
+       "WHERE fd.feature_id = "+feature_id+" AND dx.dbxref_id = fd.dbxref_id "+
+       "AND dx.db_id = db.db_id "+ 
+       "UNION " +
+       "SELECT dx.accession, db.name FROM dbxref dx, db "+
+       "WHERE dbxref_id = "+dbXRefId+" AND dx.db_id = db.db_id";
+     debugMsgAndTime(sql);
+     try {
+       ResultSet rs = getConnection().createStatement().executeQuery(sql);
+       while (rs.next()) {
+         String id = rs.getString("accession");
+         String db = rs.getString("dbName");
+         annot.addDbXref(new DbXref("id",id,db));
+       }
+     } catch  (SQLException sqle) {
+       printSqlError(sqle,"retrieving annot dbxref",sql);
+     }
+ 
+   }
+ 
    /**
     * Retrieve a set of search hits (e.g. pairwise alignments) from Chado and convert them into instances

Index: TigrSybilChadoInstance.java
===================================================================
RCS file: /cvsroot/gmod/apollo/src/java/apollo/dataadapter/chado/jdbc/TigrSybilChadoInstance.java,v
retrieving revision 1.9
retrieving revision 1.10
diff -C2 -d -r1.9 -r1.10
*** TigrSybilChadoInstance.java	15 Jul 2004 17:24:03 -0000	1.9
--- TigrSybilChadoInstance.java	16 Jul 2004 00:10:08 -0000	1.10
***************
*** 154,158 ****
      // ------------------------------------------------
      adapter.fireProgressEvent(new ProgressEvent(this, new Double(0.0), "Retrieving gene models"));
!     jdbcChadoAdapter.addGeneModels(conn, seq, annotations, featLocImp,false,false);
      adapter.fireProgressEvent(new ProgressEvent(this, new Double(100.0), "Retrieving gene models"));
  
--- 154,158 ----
      // ------------------------------------------------
      adapter.fireProgressEvent(new ProgressEvent(this, new Double(0.0), "Retrieving gene models"));
!     jdbcChadoAdapter.addGeneModels(conn, seq, annotations, featLocImp,false,false,false);
      adapter.fireProgressEvent(new ProgressEvent(this, new Double(100.0), "Retrieving gene models"));