[Gmod-apollo-cmts] CVS: apollo/src/java/apollo/dataadapter/chado/jdbc JdbcChadoAdapter.java,1.9,1.10

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 454-5900

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

Modified Files:
	JdbcChadoAdapter.java PostgresChadoAdapter.java 
	SchemaVersion.java SybaseChadoAdapter.java 
Log Message:
Changed chado adapter files to use 2-space indenting.

Index: JdbcChadoAdapter.java
===================================================================
RCS file: /cvsroot/gmod/apollo/src/java/apollo/dataadapter/chado/jdbc/JdbcChadoAdapter.java,v
retrieving revision 1.9
retrieving revision 1.10
diff -C2 -d -r1.9 -r1.10
*** JdbcChadoAdapter.java	27 Apr 2004 14:17:30 -0000	1.9
--- JdbcChadoAdapter.java	27 Apr 2004 14:42:05 -0000	1.10
***************
*** 43,726 ****
  public abstract class JdbcChadoAdapter {

!     // TO DO - extract all the embedded SQL in this class and its subclasses into a set of more easily-edited files?
!     // TO DO - remove hard-coded cvterm IDs from all the SQL statements

!     // -----------------------------------------------------------------------
!     // Instance variables
!     // -----------------------------------------------------------------------

!     /**
[...2279 lines suppressed...]
!    * @param strand
!    * @return
!    */
!   protected Exon _makeExon(int low, int high, int strand) {
!     Exon e = new Exon();
!     e.setLow(low);
!     e.setHigh(high);
!     e.setStrand(strand);
!     return e;
!   }

    /** Whether feature locations are stored redundantly (tigr does, fb doesnt)
***************
*** 1237,1241 ****
        }
      }
-     
    }

--- 1237,1240 ----

Index: PostgresChadoAdapter.java
===================================================================
RCS file: /cvsroot/gmod/apollo/src/java/apollo/dataadapter/chado/jdbc/PostgresChadoAdapter.java,v
retrieving revision 1.3
retrieving revision 1.4
diff -C2 -d -r1.3 -r1.4
*** PostgresChadoAdapter.java	27 Apr 2004 14:17:30 -0000	1.3
--- PostgresChadoAdapter.java	27 Apr 2004 14:42:05 -0000	1.4
***************
*** 81,87 ****

      // sim4
! //     adapter.fireProgressEvent(new ProgressEvent(this, new Double(0.0), "Retrieving sim4 results"));
! //     addSearchHits(conn, seqFeatId, 0, "sim4", 1, 0, false, seq, results);
! //     adapter.fireProgressEvent(new ProgressEvent(this, new Double(100.0), "Retrieving sim4 results"));

      adapter.fireProgressEvent(new ProgressEvent(this, new Double(0.0), "Retrieving genscan results"));
--- 81,87 ----

      // sim4
!     //     adapter.fireProgressEvent(new ProgressEvent(this, new Double(0.0), "Retrieving sim4 results"));
!     //     addSearchHits(conn, seqFeatId, 0, "sim4", 1, 0, false, seq, results);
!     //     adapter.fireProgressEvent(new ProgressEvent(this, new Double(100.0), "Retrieving sim4 results"));

      adapter.fireProgressEvent(new ProgressEvent(this, new Double(0.0), "Retrieving genscan results"));

Index: SchemaVersion.java
===================================================================
RCS file: /cvsroot/gmod/apollo/src/java/apollo/dataadapter/chado/jdbc/SchemaVersion.java,v
retrieving revision 1.3
retrieving revision 1.4
diff -C2 -d -r1.3 -r1.4
*** SchemaVersion.java	23 Apr 2004 22:31:02 -0000	1.3
--- SchemaVersion.java	27 Apr 2004 14:42:05 -0000	1.4
***************
*** 22,150 ****
  public class SchemaVersion {

!     // -----------------------------------------------------------------------
!     // Static/class variables for the known Chado schema versions
!     // -----------------------------------------------------------------------

!     /**
!      * Pre-flybase migration build 
!      */
!     public static SchemaVersion PRE_FMB = new SchemaVersion("pre_fmb");

!     /**
!      * Flybase migration build 
!      */
!     public static SchemaVersion FMB = new SchemaVersion("fmb");

!     // -----------------------------------------------------------------------
!     // Instance variables
!     // -----------------------------------------------------------------------

!     /**
!      * A String that uniquely identifies a Chado schema version.
!      */
!     protected String tag;
! 
!     // -----------------------------------------------------------------------
!     // Constructor
!     // -----------------------------------------------------------------------

!     /**
!      * Note that the constructor is private so that no other class can create instances.
!      */
!     private SchemaVersion(String tag) {
! 	this.tag = tag;
!     }

!     // -----------------------------------------------------------------------
!     // SchemaVersion - static methods
!     // -----------------------------------------------------------------------

!     /**
!      * Attempt to determine the version of a chado schema by interrogating one
!      * or two key fields or tables.  Hopefully it will be possible to do this in a more 
!      * structured manner in future e.g., by retrieving an explicitly-stored schema
!      * version number from the database (hint, hint).
!      *
!      * @param c A connection to a Chado database whose schema version must be be determined.
!      * @return  The predicted version of the Chado database represented by <code>c</code>.
!      */
!     public static SchemaVersion guessSchemaVersion(Connection c) {
! 	if (c != null) {
! 	    try {
! 		Statement s = c.createStatement();
! 		// the 'db' table does not exist in the pre_fmb schema
! 		ResultSet rs = s.executeQuery("select count(*) from db");
! 		long count; 
! 		while (rs.next()) { count = rs.getLong(1); }
! 	    } catch (SQLException sqle) {
! 		return PRE_FMB;
! 	    }
! 	}

! 	return FMB;
      }

!     // -----------------------------------------------------------------------
!     // SchemaVersion
!     // -----------------------------------------------------------------------
!     
!     // Methods that return column names that differ between the various schema versions; 
!     // this will have to be generalized in future.

!     /**
!      * @return The name of the chado column formerly known as featureprop.pkey_id
!      */ 
!     protected String getPKeyCol() {
! 	return (this.equals(SchemaVersion.PRE_FMB)) ? "pkey_id" : "type_id";
!     }

!     /**
!      * @return The name of the chado column formerly known as featureprop.pkey_id
!      */
!     protected String getPValCol() {
! 	return (this.equals(SchemaVersion.PRE_FMB)) ? "pval" : "value";
!     }

!     /**
!      * @return The name of the chado column formerly known as featureloc.fmin
!      */
!     protected String getFMinCol() {
! 	return (this.equals(SchemaVersion.PRE_FMB)) ? "nbeg" : "fmin";
!     }

!     /**
!      * @return The name of the chado column formerly known as featureloc.nend
!      */
!     protected String getFMaxCol() {
! 	return (this.equals(SchemaVersion.PRE_FMB)) ? "nend" : "fmax";
!     }

!     /**
!      * @return The name of the chado column formerly known as feature_relationship.subjfeature_id
!      */
!     protected String getSubjFeatCol() {
! 	return (this.equals(SchemaVersion.PRE_FMB)) ? "subjfeature_id" : "subject_id";
!     }

!     /**
!      * @return The name of the chado column formerly known as feature_relationship.objfeature_id
!      */
!     protected String getObjFeatCol() {
! 	return (this.equals(SchemaVersion.PRE_FMB)) ? "objfeature_id" : "object_id";
!     }

!     /**
!      * @return The name of the chado column formerly known as organism.abbrev
!      */
!     protected String getAbbrevCol() {
! 	return (this.equals(SchemaVersion.PRE_FMB)) ? "abbrev" : "abbreviation";
!     }

!     // -----------------------------------------------------------------------
!     // Object
!     // -----------------------------------------------------------------------

!     public String toString() { return tag; }

  }
--- 22,149 ----
  public class SchemaVersion {

!   // -----------------------------------------------------------------------
!   // Static/class variables for the known Chado schema versions
!   // -----------------------------------------------------------------------

!   /**
!    * Pre-flybase migration build 
!    */
!   public static SchemaVersion PRE_FMB = new SchemaVersion("pre_fmb");

!   /**
!    * Flybase migration build 
!    */
!   public static SchemaVersion FMB = new SchemaVersion("fmb");

!   // -----------------------------------------------------------------------
!   // Instance variables
!   // -----------------------------------------------------------------------

!   /**
!    * A String that uniquely identifies a Chado schema version.
!    */
!   protected String tag;

!   // -----------------------------------------------------------------------
!   // Constructor
!   // -----------------------------------------------------------------------

!   /**
!    * Note that the constructor is private so that no other class can create instances.
!    */
!   private SchemaVersion(String tag) {
!     this.tag = tag;
!   }

!   // -----------------------------------------------------------------------
!   // SchemaVersion - static methods
!   // -----------------------------------------------------------------------

!   /**
!    * Attempt to determine the version of a chado schema by interrogating one
!    * or two key fields or tables.  Hopefully it will be possible to do this in a more 
!    * structured manner in future e.g., by retrieving an explicitly-stored schema
!    * version number from the database (hint, hint).
!    *
!    * @param c A connection to a Chado database whose schema version must be be determined.
!    * @return  The predicted version of the Chado database represented by <code>c</code>.
!    */
!   public static SchemaVersion guessSchemaVersion(Connection c) {
!     if (c != null) {
!       try {
! 	Statement s = c.createStatement();
! 	// the 'db' table does not exist in the pre_fmb schema
! 	ResultSet rs = s.executeQuery("select count(*) from db");
! 	long count; 
! 	while (rs.next()) { count = rs.getLong(1); }
!       } catch (SQLException sqle) {
! 	return PRE_FMB;
!       }
      }

!     return FMB;
!   }

!   // -----------------------------------------------------------------------
!   // SchemaVersion
!   // -----------------------------------------------------------------------
!     
!   // Methods that return column names that differ between the various schema versions; 
!   // this will have to be generalized in future.

!   /**
!    * @return The name of the chado column formerly known as featureprop.pkey_id
!    */ 
!   protected String getPKeyCol() {
!     return (this.equals(SchemaVersion.PRE_FMB)) ? "pkey_id" : "type_id";
!   }

!   /**
!    * @return The name of the chado column formerly known as featureprop.pkey_id
!    */
!   protected String getPValCol() {
!     return (this.equals(SchemaVersion.PRE_FMB)) ? "pval" : "value";
!   }

!   /**
!    * @return The name of the chado column formerly known as featureloc.fmin
!    */
!   protected String getFMinCol() {
!     return (this.equals(SchemaVersion.PRE_FMB)) ? "nbeg" : "fmin";
!   }

!   /**
!    * @return The name of the chado column formerly known as featureloc.nend
!    */
!   protected String getFMaxCol() {
!     return (this.equals(SchemaVersion.PRE_FMB)) ? "nend" : "fmax";
!   }

!   /**
!    * @return The name of the chado column formerly known as feature_relationship.subjfeature_id
!    */
!   protected String getSubjFeatCol() {
!     return (this.equals(SchemaVersion.PRE_FMB)) ? "subjfeature_id" : "subject_id";
!   }

!   /**
!    * @return The name of the chado column formerly known as feature_relationship.objfeature_id
!    */
!   protected String getObjFeatCol() {
!     return (this.equals(SchemaVersion.PRE_FMB)) ? "objfeature_id" : "object_id";
!   }

!   /**
!    * @return The name of the chado column formerly known as organism.abbrev
!    */
!   protected String getAbbrevCol() {
!     return (this.equals(SchemaVersion.PRE_FMB)) ? "abbrev" : "abbreviation";
!   }

!   // -----------------------------------------------------------------------
!   // Object
!   // -----------------------------------------------------------------------

+   public String toString() { return tag; }

  }

Index: SybaseChadoAdapter.java
===================================================================
RCS file: /cvsroot/gmod/apollo/src/java/apollo/dataadapter/chado/jdbc/SybaseChadoAdapter.java,v
retrieving revision 1.8
retrieving revision 1.9
diff -C2 -d -r1.8 -r1.9
*** SybaseChadoAdapter.java	27 Apr 2004 14:17:30 -0000	1.8
--- SybaseChadoAdapter.java	27 Apr 2004 14:42:06 -0000	1.9
***************
*** 36,366 ****
  public class SybaseChadoAdapter extends JdbcChadoAdapter {

!     // -----------------------------------------------------------------------
!     // Static variables
!     // -----------------------------------------------------------------------

!     /**
!      * Whether to echo the text of caught exceptions to stderr.
!      */
!     public static boolean PRINT_ALL_EXCEPTIONS = false;
!     //public static boolean PRINT_ALL_EXCEPTIONS = true;

!     /**
!      * Whether to enable debugging print statements.
!      */
    public static boolean DEBUG = false;
    //public static boolean DEBUG = true;

!     // -----------------------------------------------------------------------
!     // Constructor
!     // -----------------------------------------------------------------------

!     public SybaseChadoAdapter() {
! 	// The following code should work with the 5.x version of the Sybase JDBC driver (JConnect):
! 	try {
! 	    Driver sybDriver = (Driver)Class.forName("com.sybase.jdbc2.jdbc.SybDriver").newInstance();
! 	    DriverManager.registerDriver(sybDriver);
! 	} catch (Throwable t) {
! 	    System.err.println(this.getClass() + ": error initializing Sybase JDBC driver JConnect");
! 	    t.printStackTrace(System.err);
! 	}
      }

!     // -----------------------------------------------------------------------
!     // SybaseChadoAdapter
!     // -----------------------------------------------------------------------

!     /**
!      * Run 'set forceplan on' or 'set forceplan off'; this is a Sybase option that forces
!      * the Sybase query optimizer to access the tables in the order that they are named
!      * in the FROM clause of the SQL query.  It is the equivalent of the Oracle optimizer
!      * hint "ORDERED".
!      *
!      * @param stmt  JDBC Statement used to execute the set forceplan command.
!      * @param on    Whether to set this Sybase option on (true) or off (false).
!      * @return      The JDBC result value from the executeUpdate command.
!      */
!     protected boolean setForcePlan(Statement stmt, boolean on) {
! 	String sql = "set forceplan " + (on ? "on" : "off");
! 	int resultval = -1;

! 	try {
! 	    resultval = stmt.executeUpdate(sql);
! 	} catch (SQLException sqle) {
! 	    return false;
! 	}
! 	
! 	return true;
      }

!     // -----------------------------------------------------------------------
!     // JdbcChadoAdapter
!     // -----------------------------------------------------------------------

!     // Overrides JdbcChadoAdapter.getConnection()
!     protected Connection getConnection() {
! 	Connection conn = null;
! 	Properties props = new Properties();

! 	// Set some Sybase-specific properties
! 	props.setProperty("applicationname", "Apollo");
! 	props.setProperty("user", this.username);
!  	props.setProperty("password", this.password);

!  	try {
! 	    // The database name gets appended to the JDBC URL
! 	    conn = DriverManager.getConnection(this.jdbcUrl + "/" + this.chadoDb, props);

! 	    if (DEBUG) {
! 		try {
! 		    DatabaseMetaData dmd = conn.getMetaData();
! 		    System.err.println("   DatabaseProductName = " + dmd.getDatabaseProductName());
! 		    System.err.println("DatabaseProductVersion = " + dmd.getDatabaseProductVersion());
! 		    System.err.println("  DatabaseMajorVersion = " + dmd.getDatabaseMajorVersion());
! 		    System.err.println("  DatabaseMinorVersion = " + dmd.getDatabaseMinorVersion());
! 		} catch (Throwable t) {}
! 	    }
! 	} catch (SQLException sqle) {
! 	    if (PRINT_ALL_EXCEPTIONS) {
! 		System.err.println(this.getClass() + ": exception connecting to Sybase Chado database");
! 		System.err.println(sqle);
! 		sqle.printStackTrace(System.err);

! 		// Print any chained exceptions too
! 		SQLException next = sqle;
! 		while ((next = next.getNextException()) != null) {
! 		    System.err.println(next);
! 		    next.printStackTrace(System.err);
! 		}
! 	    }
  	}
! 
! 	return conn;
      }

!     // Implements the abstract method JdbcChadoAdapter.getCurationSet
!     public CurationSet getCurationSet(ChadoAdapter adapter, String seqType, String seqId) {
! 	CurationSet cset = null;
! 	StrandedFeatureSet results = new StrandedFeatureSet(new FeatureSet(), new FeatureSet());
! 	StrandedFeatureSet annotations = new StrandedFeatureSet(new AnnotatedFeature(),
!                                                                 new AnnotatedFeature());

! 	// TO DO - allow the data retrieved by this method to be customized somehow, either in a 
! 	// configuration file or directly in the ChadoAdapterGUI (the latter may require some
! 	// additional communication between this class and ChadoAdapterGUI)

! 	Connection conn = getConnection();

! 	if (conn != null) {
! 	    cset = new CurationSet();

! 	    // Query for the Chado feature.feature_id of the requested sequence
! 	    long seqFeatId = getFeatureId(conn, seqType, seqId);

! 	    adapter.fireProgressEvent(new ProgressEvent(this, new Double(50.0), "Retrieving " + seqType + " sequence " + seqId));
! 	    // Retrieve the corresponding apollo.datamodel.Sequence object
! 	    Sequence seq = getSequence(conn, seqFeatId, false);
! 	    // Change the residue offset; Chado uses 0-based coordinates, and 1-based coordinates work better in Apollo
! 	    seq.setResidueOffset(1);
! 	    cset.setRefSequence(seq);
! 	    // TO DO - include sequence coordinates in CurationSet name when support for subsequence display is added:
! 	    cset.setName(seq.getDisplayId());
! 	    adapter.fireProgressEvent(new ProgressEvent(this, new Double(100.0), "Retrieving " + seqType + " sequence " + seqId));

! 	    cset.setStart(1);
! 	    cset.setEnd(seq.getLength());

! 	    // TO DO - set other CurationSet attributes using info. retrieved from Chado e.g., 
! 	    // cset.setOrganism("?");
! 	    // cset.setChromosome("1");

! 	    // ------------------------------------------------
! 	    // Gene models
! 	    // ------------------------------------------------
! 	    adapter.fireProgressEvent(new ProgressEvent(this, new Double(0.0), "Retrieving gene models"));
! 	    addGeneModels(conn, seqFeatId, 1, seq, annotations);
! 	    adapter.fireProgressEvent(new ProgressEvent(this, new Double(50.0), "Retrieving gene models"));
! 	    addGeneModels(conn, seqFeatId, -1, seq, annotations);
! 	    adapter.fireProgressEvent(new ProgressEvent(this, new Double(100.0), "Retrieving gene models"));

! 	    // ------------------------------------------------
! 	    // Features located directly on the reference sequence
! 	    // ------------------------------------------------    

! 	    // PROmer/NUCmer results (stored with strand = 0)
! 	    adapter.fireProgressEvent(new ProgressEvent(this, new Double(0.0), "Retrieving NUCmer annotations"));
! 	    addSearchHits(conn, seqFeatId, 0, "NUCmer%", 1, 0, false, seq, results);
! 	    adapter.fireProgressEvent(new ProgressEvent(this, new Double(100.0), "Retrieving NUCmer annotations"));

! 	    adapter.fireProgressEvent(new ProgressEvent(this, new Double(0.0), "Retrieving PROmer annotations"));
! 	    addSearchHits(conn, seqFeatId, 0, "PROmer%", 1, 0, false, seq, results);
! 	    adapter.fireProgressEvent(new ProgressEvent(this, new Double(100.0), "Retrieving PROmer annotations"));

! 	    // SNPs
! 	    adapter.fireProgressEvent(new ProgressEvent(this, new Double(0.0), "Retrieving SNPs"));
! 	    addSearchHits(conn, seqFeatId, 0, "TIGR SNP%", 0, 1, true, seq, results);
! 	    adapter.fireProgressEvent(new ProgressEvent(this, new Double(100.0), "Retrieving SNPs"));

! 	    // "region" analysis
! 	    adapter.fireProgressEvent(new ProgressEvent(this, new Double(0.0), "Retrieving region analyses"));
! 	    addSearchHits(conn, seqFeatId, 0, "region%", 1, 0, false, seq, results);
! 	    adapter.fireProgressEvent(new ProgressEvent(this, new Double(100.0), "Retrieving region analyses"));

! 	    // ------------------------------------------------
! 	    // Features featureloc'd to the sequence's proteins
! 	    // ------------------------------------------------

!     	    // PEffect
! 	    //	    adapter.fireProgressEvent(new ProgressEvent(this, new Double(0.0), "Retrieving peffect annotation"));
! 	    //	    addProteinAlignments(conn, seqFeatId, "peffect", results);
! 	    //	    adapter.fireProgressEvent(new ProgressEvent(this, new Double(100.0), "Retrieving peffect annotation"));

! 	    // BLASTP
! 	    adapter.fireProgressEvent(new ProgressEvent(this, new Double(0.0), "Retrieving BLASTP hits"));
! 	    addProteinAlignments(conn, seqFeatId, "blastp 2.0mp-washu", results);
! 	    adapter.fireProgressEvent(new ProgressEvent(this, new Double(100.0), "Retrieving BLASTP hits"));

! 	    // ------------------------------------------------
! 	    // Add annotations and results
! 	    // ------------------------------------------------
! 	    cset.setAnnots(annotations);
! 	    cset.setResults(results);

! 	    // ------------------------------------------------
! 	    // Close Sybase/JDBC connection
! 	    // ------------------------------------------------
! 	    try {
! 		conn.close();
! 	    } catch (SQLException sqle) {
! 		System.err.println(this.getClass() + ": failed to close JDBC Connection");
! 	    }
! 	}

! 	return cset;
!     } // end of getCurationSet

      // Overrides JdbcChadoAdapter.getAddProteinAlignments(); uses 'set forceplan on' and some
      // 'index' hints to get the query to run more quickly.
!     protected void addProteinAlignments(Connection c, long srcSeqId, String programName, StrandedFeatureSet sfs) {

! 	try {
! 	    // HACK - this SQL relies on the TIGR featurelocs between protein and genomic sequence,
! 	    // which are NOT part of the standard Chado gene model encoding.
! 	    // JC: Although Dave Emmert did mention that Harvard independently adopted the same convention!

! 	    // NOTE - currently we are not querying out the coordinates of the match on the protein
! 	    // sequence (featureloc fl2 and featureloc fl3).  

! 	    String fminCol = chadoVersion.getFMinCol();
! 	    String fmaxCol = chadoVersion.getFMaxCol();

! 	    String sql = "SELECT p1.uniquename AS query_name, " +
! 		" fl1." + fminCol + " AS query_fmin, " +
! 		" fl1." + fmaxCol + " AS query_fmax, " +
! 		" fl1.strand AS query_strand, " +
! 		" g2.uniquename AS seq2, " +
! 		" p2.uniquename AS target_name, " + 
! 		" fl4." + fminCol + " AS target_fmin, " +
! 		" fl4." + fmaxCol + " AS target_fmax, " +
! 		" fl4.strand AS target_strand, " +
! 		" a.program, " +
! 		" a.programversion, " +
! 		" af.pidentity, " +
! 		" af.rawscore, " +
! 		" af.normscore, " +
! 		" af.significance " +
! 		"FROM featureloc fl1 (index idx_fl_sid), featureloc fl2 (index idx_fl_sid), analysisfeature af, analysis a, " +
! 		"featureloc fl3 (index idx_fl_fid), feature p1, feature p2, featureloc fl4 (index idx_fl_fid), feature g2 " +
! 		"WHERE fl1.srcfeature_id = " + srcSeqId + " " +
! 		"AND fl1.feature_id = fl2.srcfeature_id " +
! 		"AND fl1.feature_id = p1.feature_id " +
! 		"AND fl2.feature_id = af.feature_id " +
! 		"AND af.analysis_id = a.analysis_id " +
! 		"AND a.program = '" + programName + "' " +
! 		"AND fl2.feature_id = fl3.feature_id " +
! 		"AND fl3.srcfeature_id = p2.feature_id " +
! 		"AND fl3.srcfeature_id = p2.feature_id " +
! 		"AND p1.feature_id != p2.feature_id " +
! 		"AND p2.feature_id = fl4.feature_id " +
! 		"AND fl4.srcfeature_id = g2.feature_id " +

! 		// HACK - remove hard-coded cvterm_id (for 'assembly') from the following:

! 		"AND g2.type_id = 5 " +  // find which *assembly* protein2 is feature-loc'd to
! 		"ORDER BY g2.uniquename ";

! 	    Statement s = c.createStatement();

! 	    // Need to 'set forceplan on' to get the query to run fast.
! 	    // JC: this probably is not true any more; we've improved our indexes since then
! 	    this.setForcePlan(s, true);

! 	    ResultSet rs = s.executeQuery(sql);
! 	    String lastTargetSeq = null;
! 	    String lastFeatType = null;
! 	    int lastQueryStrand = 0;

! 	    // Used to group FeaturePairs by target sequence
! 	    Vector featurePairs = new Vector();

! 	    while (rs.next()) {
! 		int fmin = rs.getInt("query_fmin");
! 		int fmax = rs.getInt("query_fmax");
! 		String name = rs.getString("query_name");     // name of the "query" protein
! 		int strand = rs.getInt("query_strand");

! 		String targetSeq = rs.getString("seq2");      // sequence on which the target protein resides
! 		String target = rs.getString("target_name");  // name of the "target" protein
! 		int tgt_fmin = rs.getInt("target_fmin");
! 		int tgt_fmax = rs.getInt("target_fmax");
! 		int tgt_strand = rs.getInt("target_strand");

! 		String program = rs.getString("program");
! 		String programversion = rs.getString("programversion");

! 		ChadoFeatureLoc queryLoc = new ChadoFeatureLoc(fmin, fmax, strand);
! 		ChadoFeatureLoc targetLoc = new ChadoFeatureLoc(tgt_fmin, tgt_fmax, tgt_strand);
! 		String featType = program + " " + programversion + " " + targetSeq;

! 		// Query SeqFeature
! 		SeqFeature queryFeat = _makeSeqFeature(queryLoc.getLow(), queryLoc.getHigh(), featType, queryLoc.getStrand());
! 		queryFeat.setName(name);

! 		// TO DO - only set scores that are NON-null?
! 		float pIdentity = rs.getFloat("pidentity");
! 		queryFeat.addScore(new Score("pidentity", pIdentity));
! 		queryFeat.addScore(new Score("rawscore", rs.getFloat("rawscore")));
! 		queryFeat.addScore(new Score("normscore", rs.getFloat("normscore")));
! 		queryFeat.addScore(new Score("significance", rs.getFloat("significance")));
! 		queryFeat.setScore(pIdentity);

! 		// Target SeqFeature
! 		SeqFeature tgtFeat = _makeSeqFeature(targetLoc.getLow(), targetLoc.getHigh(), featType, targetLoc.getStrand());
! 		tgtFeat.setName(target);

! 		if (targetSeq != lastTargetSeq) {
! 		    if (lastTargetSeq != null) {
! 			sfs.addFeature(_makeFeatureSet(featurePairs, lastTargetSeq, lastFeatType, lastQueryStrand));
! 			featurePairs.clear();
! 		    }
! 		    lastTargetSeq = targetSeq;
! 		    lastFeatType = featType;
! 		    lastQueryStrand = queryLoc.getStrand();
! 		}
! 		featurePairs.addElement(new FeaturePair(queryFeat, tgtFeat));
! 	    }
! 	    if (lastTargetSeq != null) {
! 		sfs.addFeature(_makeFeatureSet(featurePairs, lastTargetSeq, lastFeatType, lastQueryStrand));
! 		featurePairs.clear();
! 	    }
! 	    // Turn off 'set forceplan' option
! 	    this.setForcePlan(s, false);
! 	} 
! 	catch (SQLException sqle) {
! 	    System.err.println(this.getClass() + ": SQLException retrieving protein hsp_alignments for sequence with feature_id = " + srcSeqId);
! 	    sqle.printStackTrace(System.err);
  	}
      }

    /**
--- 36,366 ----
  public class SybaseChadoAdapter extends JdbcChadoAdapter {

!   // -----------------------------------------------------------------------
!   // Static variables
!   // -----------------------------------------------------------------------

!   /**
!    * Whether to echo the text of caught exceptions to stderr.
!    */
!   public static boolean PRINT_ALL_EXCEPTIONS = false;
!   //public static boolean PRINT_ALL_EXCEPTIONS = true;

!   /**
!    * Whether to enable debugging print statements.
!    */
    public static boolean DEBUG = false;
    //public static boolean DEBUG = true;

!   // -----------------------------------------------------------------------
!   // Constructor
!   // -----------------------------------------------------------------------

!   public SybaseChadoAdapter() {
!     // The following code should work with the 5.x version of the Sybase JDBC driver (JConnect):
!     try {
!       Driver sybDriver = (Driver)Class.forName("com.sybase.jdbc2.jdbc.SybDriver").newInstance();
!       DriverManager.registerDriver(sybDriver);
!     } catch (Throwable t) {
!       System.err.println(this.getClass() + ": error initializing Sybase JDBC driver JConnect");
!       t.printStackTrace(System.err);
      }
+   }

!   // -----------------------------------------------------------------------
!   // SybaseChadoAdapter
!   // -----------------------------------------------------------------------

!   /**
!    * Run 'set forceplan on' or 'set forceplan off'; this is a Sybase option that forces
!    * the Sybase query optimizer to access the tables in the order that they are named
!    * in the FROM clause of the SQL query.  It is the equivalent of the Oracle optimizer
!    * hint "ORDERED".
!    *
!    * @param stmt  JDBC Statement used to execute the set forceplan command.
!    * @param on    Whether to set this Sybase option on (true) or off (false).
!    * @return      The JDBC result value from the executeUpdate command.
!    */
!   protected boolean setForcePlan(Statement stmt, boolean on) {
!     String sql = "set forceplan " + (on ? "on" : "off");
!     int resultval = -1;

!     try {
!       resultval = stmt.executeUpdate(sql);
!     } catch (SQLException sqle) {
!       return false;
      }
+ 	
+     return true;
+   }

!   // -----------------------------------------------------------------------
!   // JdbcChadoAdapter
!   // -----------------------------------------------------------------------

!   // Overrides JdbcChadoAdapter.getConnection()
!   protected Connection getConnection() {
!     Connection conn = null;
!     Properties props = new Properties();

!     // Set some Sybase-specific properties
!     props.setProperty("applicationname", "Apollo");
!     props.setProperty("user", this.username);
!     props.setProperty("password", this.password);

!     try {
!       // The database name gets appended to the JDBC URL
!       conn = DriverManager.getConnection(this.jdbcUrl + "/" + this.chadoDb, props);

!       if (DEBUG) {
! 	try {
! 	  DatabaseMetaData dmd = conn.getMetaData();
! 	  System.err.println("   DatabaseProductName = " + dmd.getDatabaseProductName());
! 	  System.err.println("DatabaseProductVersion = " + dmd.getDatabaseProductVersion());
! 	  System.err.println("  DatabaseMajorVersion = " + dmd.getDatabaseMajorVersion());
! 	  System.err.println("  DatabaseMinorVersion = " + dmd.getDatabaseMinorVersion());
! 	} catch (Throwable t) {}
!       }
!     } catch (SQLException sqle) {
!       if (PRINT_ALL_EXCEPTIONS) {
! 	System.err.println(this.getClass() + ": exception connecting to Sybase Chado database");
! 	System.err.println(sqle);
! 	sqle.printStackTrace(System.err);

! 	// Print any chained exceptions too
! 	SQLException next = sqle;
! 	while ((next = next.getNextException()) != null) {
! 	  System.err.println(next);
! 	  next.printStackTrace(System.err);
  	}
!       }
      }

!     return conn;
!   }

!   // Implements the abstract method JdbcChadoAdapter.getCurationSet
!   public CurationSet getCurationSet(ChadoAdapter adapter, String seqType, String seqId) {
!     CurationSet cset = null;
!     StrandedFeatureSet results = new StrandedFeatureSet(new FeatureSet(), new FeatureSet());
!     StrandedFeatureSet annotations = new StrandedFeatureSet(new AnnotatedFeature(),
! 							    new AnnotatedFeature());

!     // TO DO - allow the data retrieved by this method to be customized somehow, either in a 
!     // configuration file or directly in the ChadoAdapterGUI (the latter may require some
!     // additional communication between this class and ChadoAdapterGUI)

!     Connection conn = getConnection();

!     if (conn != null) {
!       cset = new CurationSet();

!       // Query for the Chado feature.feature_id of the requested sequence
!       long seqFeatId = getFeatureId(conn, seqType, seqId);

!       adapter.fireProgressEvent(new ProgressEvent(this, new Double(50.0), "Retrieving " + seqType + " sequence " + seqId));
!       // Retrieve the corresponding apollo.datamodel.Sequence object
!       Sequence seq = getSequence(conn, seqFeatId, false);
!       // Change the residue offset; Chado uses 0-based coordinates, and 1-based coordinates work better in Apollo
!       seq.setResidueOffset(1);
!       cset.setRefSequence(seq);
!       // TO DO - include sequence coordinates in CurationSet name when support for subsequence display is added:
!       cset.setName(seq.getDisplayId());
!       adapter.fireProgressEvent(new ProgressEvent(this, new Double(100.0), "Retrieving " + seqType + " sequence " + seqId));

!       cset.setStart(1);
!       cset.setEnd(seq.getLength());
! 
!       // TO DO - set other CurationSet attributes using info. retrieved from Chado e.g., 
!       // cset.setOrganism("?");
!       // cset.setChromosome("1");

!       // ------------------------------------------------
!       // Gene models
!       // ------------------------------------------------
!       adapter.fireProgressEvent(new ProgressEvent(this, new Double(0.0), "Retrieving gene models"));
!       addGeneModels(conn, seqFeatId, 1, seq, annotations);
!       adapter.fireProgressEvent(new ProgressEvent(this, new Double(50.0), "Retrieving gene models"));
!       addGeneModels(conn, seqFeatId, -1, seq, annotations);
!       adapter.fireProgressEvent(new ProgressEvent(this, new Double(100.0), "Retrieving gene models"));

!       // ------------------------------------------------
!       // Features located directly on the reference sequence
!       // ------------------------------------------------    

!       // PROmer/NUCmer results (stored with strand = 0)
!       adapter.fireProgressEvent(new ProgressEvent(this, new Double(0.0), "Retrieving NUCmer annotations"));
!       addSearchHits(conn, seqFeatId, 0, "NUCmer%", 1, 0, false, seq, results);
!       adapter.fireProgressEvent(new ProgressEvent(this, new Double(100.0), "Retrieving NUCmer annotations"));

!       adapter.fireProgressEvent(new ProgressEvent(this, new Double(0.0), "Retrieving PROmer annotations"));
!       addSearchHits(conn, seqFeatId, 0, "PROmer%", 1, 0, false, seq, results);
!       adapter.fireProgressEvent(new ProgressEvent(this, new Double(100.0), "Retrieving PROmer annotations"));

!       // SNPs
!       adapter.fireProgressEvent(new ProgressEvent(this, new Double(0.0), "Retrieving SNPs"));
!       addSearchHits(conn, seqFeatId, 0, "TIGR SNP%", 0, 1, true, seq, results);
!       adapter.fireProgressEvent(new ProgressEvent(this, new Double(100.0), "Retrieving SNPs"));

!       // "region" analysis
!       adapter.fireProgressEvent(new ProgressEvent(this, new Double(0.0), "Retrieving region analyses"));
!       addSearchHits(conn, seqFeatId, 0, "region%", 1, 0, false, seq, results);
!       adapter.fireProgressEvent(new ProgressEvent(this, new Double(100.0), "Retrieving region analyses"));

!       // ------------------------------------------------
!       // Features featureloc'd to the sequence's proteins
!       // ------------------------------------------------

!       // PEffect
!       //	    adapter.fireProgressEvent(new ProgressEvent(this, new Double(0.0), "Retrieving peffect annotation"));
!       //	    addProteinAlignments(conn, seqFeatId, "peffect", results);
!       //	    adapter.fireProgressEvent(new ProgressEvent(this, new Double(100.0), "Retrieving peffect annotation"));

!       // BLASTP
!       adapter.fireProgressEvent(new ProgressEvent(this, new Double(0.0), "Retrieving BLASTP hits"));
!       addProteinAlignments(conn, seqFeatId, "blastp 2.0mp-washu", results);
!       adapter.fireProgressEvent(new ProgressEvent(this, new Double(100.0), "Retrieving BLASTP hits"));

!       // ------------------------------------------------
!       // Add annotations and results
!       // ------------------------------------------------
!       cset.setAnnots(annotations);
!       cset.setResults(results);

!       // ------------------------------------------------
!       // Close Sybase/JDBC connection
!       // ------------------------------------------------
!       try {
! 	conn.close();
!       } catch (SQLException sqle) {
! 	System.err.println(this.getClass() + ": failed to close JDBC Connection");
!       }
!     }

!     return cset;
!   } // end of getCurationSet

      // Overrides JdbcChadoAdapter.getAddProteinAlignments(); uses 'set forceplan on' and some
      // 'index' hints to get the query to run more quickly.
!   protected void addProteinAlignments(Connection c, long srcSeqId, String programName, StrandedFeatureSet sfs) {

!     try {
!       // HACK - this SQL relies on the TIGR featurelocs between protein and genomic sequence,
!       // which are NOT part of the standard Chado gene model encoding.
!       // JC: Although Dave Emmert did mention that Harvard independently adopted the same convention!

!       // NOTE - currently we are not querying out the coordinates of the match on the protein
!       // sequence (featureloc fl2 and featureloc fl3).  

!       String fminCol = chadoVersion.getFMinCol();
!       String fmaxCol = chadoVersion.getFMaxCol();

!       String sql = "SELECT p1.uniquename AS query_name, " +
! 	" fl1." + fminCol + " AS query_fmin, " +
! 	" fl1." + fmaxCol + " AS query_fmax, " +
! 	" fl1.strand AS query_strand, " +
! 	" g2.uniquename AS seq2, " +
! 	" p2.uniquename AS target_name, " + 
! 	" fl4." + fminCol + " AS target_fmin, " +
! 	" fl4." + fmaxCol + " AS target_fmax, " +
! 	" fl4.strand AS target_strand, " +
! 	" a.program, " +
! 	" a.programversion, " +
! 	" af.pidentity, " +
! 	" af.rawscore, " +
! 	" af.normscore, " +
! 	" af.significance " +
! 	"FROM featureloc fl1 (index idx_fl_sid), featureloc fl2 (index idx_fl_sid), analysisfeature af, analysis a, " +
! 	"featureloc fl3 (index idx_fl_fid), feature p1, feature p2, featureloc fl4 (index idx_fl_fid), feature g2 " +
! 	"WHERE fl1.srcfeature_id = " + srcSeqId + " " +
! 	"AND fl1.feature_id = fl2.srcfeature_id " +
! 	"AND fl1.feature_id = p1.feature_id " +
! 	"AND fl2.feature_id = af.feature_id " +
! 	"AND af.analysis_id = a.analysis_id " +
! 	"AND a.program = '" + programName + "' " +
! 	"AND fl2.feature_id = fl3.feature_id " +
! 	"AND fl3.srcfeature_id = p2.feature_id " +
! 	"AND fl3.srcfeature_id = p2.feature_id " +
! 	"AND p1.feature_id != p2.feature_id " +
! 	"AND p2.feature_id = fl4.feature_id " +
! 	"AND fl4.srcfeature_id = g2.feature_id " +

! 	// HACK - remove hard-coded cvterm_id (for 'assembly') from the following:

! 	"AND g2.type_id = 5 " +  // find which *assembly* protein2 is feature-loc'd to
! 	"ORDER BY g2.uniquename ";

!       Statement s = c.createStatement();

!       // Need to 'set forceplan on' to get the query to run fast.
!       // JC: this probably is not true any more; we've improved our indexes since then
!       this.setForcePlan(s, true);

!       ResultSet rs = s.executeQuery(sql);
!       String lastTargetSeq = null;
!       String lastFeatType = null;
!       int lastQueryStrand = 0;

!       // Used to group FeaturePairs by target sequence
!       Vector featurePairs = new Vector();

!       while (rs.next()) {
! 	int fmin = rs.getInt("query_fmin");
! 	int fmax = rs.getInt("query_fmax");
! 	String name = rs.getString("query_name");     // name of the "query" protein
! 	int strand = rs.getInt("query_strand");

! 	String targetSeq = rs.getString("seq2");      // sequence on which the target protein resides
! 	String target = rs.getString("target_name");  // name of the "target" protein
! 	int tgt_fmin = rs.getInt("target_fmin");
! 	int tgt_fmax = rs.getInt("target_fmax");
! 	int tgt_strand = rs.getInt("target_strand");

! 	String program = rs.getString("program");
! 	String programversion = rs.getString("programversion");

! 	ChadoFeatureLoc queryLoc = new ChadoFeatureLoc(fmin, fmax, strand);
! 	ChadoFeatureLoc targetLoc = new ChadoFeatureLoc(tgt_fmin, tgt_fmax, tgt_strand);
! 	String featType = program + " " + programversion + " " + targetSeq;

! 	// Query SeqFeature
! 	SeqFeature queryFeat = _makeSeqFeature(queryLoc.getLow(), queryLoc.getHigh(), featType, queryLoc.getStrand());
! 	queryFeat.setName(name);

! 	// TO DO - only set scores that are NON-null?
! 	float pIdentity = rs.getFloat("pidentity");
! 	queryFeat.addScore(new Score("pidentity", pIdentity));
! 	queryFeat.addScore(new Score("rawscore", rs.getFloat("rawscore")));
! 	queryFeat.addScore(new Score("normscore", rs.getFloat("normscore")));
! 	queryFeat.addScore(new Score("significance", rs.getFloat("significance")));
! 	queryFeat.setScore(pIdentity);

! 	// Target SeqFeature
! 	SeqFeature tgtFeat = _makeSeqFeature(targetLoc.getLow(), targetLoc.getHigh(), featType, targetLoc.getStrand());
! 	tgtFeat.setName(target);

! 	if (targetSeq != lastTargetSeq) {
! 	  if (lastTargetSeq != null) {
! 	    sfs.addFeature(_makeFeatureSet(featurePairs, lastTargetSeq, lastFeatType, lastQueryStrand));
! 	    featurePairs.clear();
! 	  }
! 	  lastTargetSeq = targetSeq;
! 	  lastFeatType = featType;
! 	  lastQueryStrand = queryLoc.getStrand();
  	}
+ 	featurePairs.addElement(new FeaturePair(queryFeat, tgtFeat));
+       }
+       if (lastTargetSeq != null) {
+ 	sfs.addFeature(_makeFeatureSet(featurePairs, lastTargetSeq, lastFeatType, lastQueryStrand));
+ 	featurePairs.clear();
+       }
+       // Turn off 'set forceplan' option
+       this.setForcePlan(s, false);
+     } 
+     catch (SQLException sqle) {
+       System.err.println(this.getClass() + ": SQLException retrieving protein hsp_alignments for sequence with feature_id = " + srcSeqId);
+       sqle.printStackTrace(System.err);
      }
+   }

    /**

[Gmod-apollo-cmts] CVS: apollo/src/java/apollo/dataadapter/chado/jdbc JdbcChadoAdapter.java,1.9,1.10

[Gmod-apollo-cmts] CVS: apollo/src/java/apollo/dataadapter/chado/jdbc JdbcChadoAdapter.java,1.9,1.10 PostgresChadoAdapter.java,1.3,1.4 SchemaVersion.java,1.3,1.4 SybaseChadoAdapter.java,1.8,1.9