From: <azu...@us...> - 2008-03-28 21:43:20
|
Revision: 1668 http://obo.svn.sourceforge.net/obo/?rev=1668&view=rev Author: azurebrd Date: 2008-03-28 14:43:18 -0700 (Fri, 28 Mar 2008) Log Message: ----------- Queries work, commits are partway there. Modified Paths: -------------- phenote/trunk/src/java/phenote/dataadapter/worm/WormInteractionAdapter.java Modified: phenote/trunk/src/java/phenote/dataadapter/worm/WormInteractionAdapter.java =================================================================== --- phenote/trunk/src/java/phenote/dataadapter/worm/WormInteractionAdapter.java 2008-03-28 18:27:03 UTC (rev 1667) +++ phenote/trunk/src/java/phenote/dataadapter/worm/WormInteractionAdapter.java 2008-03-28 21:43:18 UTC (rev 1668) @@ -161,6 +161,19 @@ catch (SQLException se) { System.out.println("We got an exception while executing a history insert in insertPostgresVal table "+postgres_table+" joinkey "+joinkey+" value "+value+" : possibly bad SQL, or check the connection."); se.printStackTrace(); System.exit(1); } } } + private String queryPostgresCharacterNull(Statement s, String postgres_table, String joinkey) { + // see if the postgres value corresponding to a phenote cell has an entry at all (@row) ; returns ``null'' if no row + String default_value = null; + ResultSet rs = null; // intialize postgres query result + try { rs = s.executeQuery("SELECT * FROM "+postgres_table+" WHERE joinkey = '"+joinkey+"' ORDER BY app_timestamp"); } + catch (SQLException se) { + System.out.println("We got an exception while executing our "+postgres_table+" joinkey: that probably means our term SQL is invalid"); se.printStackTrace(); System.exit(1); } + try { while (rs.next()) { default_value = rs.getString(2); } } // assign the new term value + catch (SQLException se) { + System.out.println("We got an exception while getting a queryPostgresCharacter "+postgres_table+" result:this shouldn't happen: we've done something really bad."); se.printStackTrace(); System.exit(1); } + if (default_value == null) { default_value = "null"; } + return default_value; + } private void updateNormalField(Connection c, Statement s, String joinkey, String postgres_table, String tag_name, String tag_value) { // String postgres_value = "No postgres value assigned"; @@ -191,26 +204,23 @@ Connection c = connectToDB(); Statement s = null; -// Constraint const; -// ConstraintManager.addConstraint(const); - try { s = c.createStatement(); } catch (SQLException se) { System.out.println("We got an exception while creating a statement: that probably means we're no longer connected."); se.printStackTrace(); System.exit(1); } for (CharacterI chr : charList.getList()) { // System.out.println("Chr "+chr+" end"); try { - String allele = chr.getValueString("Object Name"); // get the allele value from the character, currently could have a column number - String pgdbid = chr.getValueString("PgdbId"); // get the allele value from the character, currently could have a column number + String objname = chr.getValueString("Object Name"); // get the objname value from the character, currently could have a column number + String pgdbid = chr.getValueString("PgdbId"); // get the postgres database ID for that character String joinkey = pgdbid; //System.out.println("pgdbid "+pgdbid+" end"); if ( (pgdbid == null) || (pgdbid == "") ) { // Integer joinkeyInt = Integer.parseInt(pgdbid); // this can't do anything since pgdbid must be blank Integer joinkeyInt = 0; ResultSet rs = null; - try { rs = s.executeQuery("SELECT joinkey FROM app_tempname "); } + try { rs = s.executeQuery("SELECT joinkey FROM int_name "); } catch (SQLException se) { - System.out.println("We got an exception while executing our app_tempname query: that probably means our column SQL is invalid"); se.printStackTrace(); System.exit(1); } + System.out.println("We got an exception while executing our int_name query: that probably means our column SQL is invalid"); se.printStackTrace(); System.exit(1); } try { while (rs.next()) { if (rs.getInt(1) > joinkeyInt) { joinkeyInt = rs.getInt(1); } } joinkeyInt++; joinkey = Integer.toString(joinkeyInt); } // get the next highest number joinkey for that character catch (SQLException se) { @@ -218,102 +228,27 @@ se.printStackTrace(); System.exit(1); } } //System.out.println("joinkey "+joinkey+" end"); - chr.setValue("PgdbId",joinkey); // assign the allele and the column + chr.setValue("PgdbId",joinkey); // assign the postgres database ID - String postgres_table = "app_type"; String tag_name = "Type"; String tag_value = chr.getValueString(tag_name); + String postgres_table = "int_name"; String tag_name = "Name"; String tag_value = chr.getValueString(tag_name); updateNormalField(c, s, joinkey, postgres_table, tag_name, tag_value); - postgres_table = "app_tempname"; tag_name = "Name"; tag_value = chr.getValueString(tag_name); - updateNormalField(c, s, joinkey, postgres_table, tag_name, tag_value); - postgres_table = "app_paper"; tag_name = "Pub"; tag_value = ""; + postgres_table = "int_paper"; tag_name = "Pub"; tag_value = ""; if ( chr.hasValue(tag_name) ) { tag_value = chr.getTerm(tag_name).getID(); } updateNormalField(c, s, joinkey, postgres_table, tag_name, tag_value); -// ADD paper_remark System.out.println("paper tag_value "+tag_value+" for postgres"); - if (tag_value != null) { - postgres_table = "app_paper_remark"; tag_name = "Paper Remark"; String paprem_value = chr.getValueString(tag_name); -System.out.println("paprem_value "+paprem_value+" for postgres"); - if ( (paprem_value != null) && (paprem_value != "") ) { insertPostgresHistVal(c, s, postgres_table, tag_value, paprem_value); } - postgres_table = "app_curation_status"; tag_name = "Curation Status"; String papsta_value = chr.getValueString(tag_name); -System.out.println("papsta_value "+papsta_value+" for postgres"); - if ( (papsta_value != null) && (papsta_value != "") ) { updateNormalField(c, s, tag_value, postgres_table, tag_name, papsta_value); } } - postgres_table = "app_person"; tag_name = "Person"; + postgres_table = "int_person"; tag_name = "Person"; updateListField(c, s, joinkey, postgres_table, tag_name, chr); - postgres_table = "app_laboratory"; tag_name = "Laboratory Evidence"; tag_value = chr.getValueString(tag_name); - updateNormalField(c, s, joinkey, postgres_table, tag_name, tag_value); - postgres_table = "app_intx_desc"; tag_name = "Genetic Intx Desc"; tag_value = chr.getValueString(tag_name); - updateNormalField(c, s, joinkey, postgres_table, tag_name, tag_value); - postgres_table = "app_curator"; tag_name = "Curator"; tag_value = ""; + postgres_table = "int_curator"; tag_name = "Curator"; tag_value = ""; if ( chr.hasValue(tag_name) ) { tag_value = chr.getTerm(tag_name).getID(); } updateNormalField(c, s, joinkey, postgres_table, tag_name, tag_value); - postgres_table = "app_not"; tag_name = "Not"; tag_value = chr.getValueString(tag_name); - updateNormalField(c, s, joinkey, postgres_table, tag_name, tag_value); - postgres_table = "app_term"; tag_name = "Phenotype"; tag_value = ""; - if ( chr.hasValue(tag_name) ) { tag_value = chr.getTerm(tag_name).getID(); } - updateNormalField(c, s, joinkey, postgres_table, tag_name, tag_value); - postgres_table = "app_phen_remark"; tag_name = "Phenotype Remark"; tag_value = chr.getValueString(tag_name); - updateNormalField(c, s, joinkey, postgres_table, tag_name, tag_value); - postgres_table = "app_anat_term"; tag_name = "Anatomy"; + postgres_table = "int_phenotype"; tag_name = "Phenotype"; updateListField(c, s, joinkey, postgres_table, tag_name, chr); - postgres_table = "app_entity"; tag_name = "Entity"; tag_value = ""; - if ( chr.hasValue(tag_name) ) { tag_value = chr.getTerm(tag_name).getID(); } + postgres_table = "int_remark"; tag_name = "Remark"; tag_value = chr.getValueString(tag_name); updateNormalField(c, s, joinkey, postgres_table, tag_name, tag_value); - postgres_table = "app_quality"; tag_name = "Quality"; tag_value = ""; - if ( chr.hasValue(tag_name) ) { tag_value = chr.getTerm(tag_name).getID(); } + postgres_table = "int_rnai"; tag_name = "RNAi"; tag_value = chr.getValueString(tag_name); updateNormalField(c, s, joinkey, postgres_table, tag_name, tag_value); - postgres_table = "app_suggested"; tag_name = "Suggested"; tag_value = chr.getValueString(tag_name); + postgres_table = "int_otherevi"; tag_name = "Other Evidence"; tag_value = chr.getValueString(tag_name); updateNormalField(c, s, joinkey, postgres_table, tag_name, tag_value); - postgres_table = "app_lifestage"; tag_name = "Life Stage"; - updateListField(c, s, joinkey, postgres_table, tag_name, chr); - postgres_table = "app_nature"; tag_name = "Allele Nature"; tag_value = ""; - if ( chr.hasValue(tag_name) ) { tag_value = chr.getTerm(tag_name).getID(); } - updateNormalField(c, s, joinkey, postgres_table, tag_name, tag_value); - postgres_table = "app_func"; tag_name = "Functional Change"; tag_value = ""; - if ( chr.hasValue(tag_name) ) { tag_value = chr.getTerm(tag_name).getID(); } - updateNormalField(c, s, joinkey, postgres_table, tag_name, tag_value); - postgres_table = "app_temperature"; tag_name = "Temperature"; tag_value = chr.getValueString(tag_name); - updateNormalField(c, s, joinkey, postgres_table, tag_name, tag_value); - postgres_table = "app_treatment"; tag_name = "Treatment"; tag_value = chr.getValueString(tag_name); - updateNormalField(c, s, joinkey, postgres_table, tag_name, tag_value); - postgres_table = "app_penetrance"; tag_name = "Penetrance"; tag_value = ""; - if ( chr.hasValue(tag_name) ) { tag_value = chr.getTerm(tag_name).getID(); } - updateNormalField(c, s, joinkey, postgres_table, tag_name, tag_value); - postgres_table = "app_percent"; tag_name = "Penetrance Remark"; tag_value = chr.getValueString(tag_name); - updateNormalField(c, s, joinkey, postgres_table, tag_name, tag_value); - postgres_table = "app_range_start"; tag_name = "Penetrance Range Start"; tag_value = chr.getValueString(tag_name); - String range_start = tag_value; - updateNormalField(c, s, joinkey, postgres_table, tag_name, tag_value); - postgres_table = "app_range_end"; tag_name = "Penetrance Range End"; tag_value = chr.getValueString(tag_name); - if ( ( (tag_value == "") || (tag_value == null) ) && (range_start != null) && (range_start != "")) { tag_value = range_start; - String m = "Penetrance range for ID : "+joinkey+" has no end value, using start value : "+range_start; - JOptionPane.showMessageDialog(null,m,"Worm stub",JOptionPane.INFORMATION_MESSAGE); } - updateNormalField(c, s, joinkey, postgres_table, tag_name, tag_value); -// postgres_table = "app_quantity_remark"; tag_name = "Quantity Remark"; tag_value = chr.getValueString(tag_name); -// updateNormalField(c, s, joinkey, postgres_table, tag_name, tag_value); // not for phenote Karen 2008 01 28 -// postgres_table = "app_quantity"; tag_name = "Quantity"; tag_value = chr.getValueString(tag_name); -// updateNormalField(c, s, joinkey, postgres_table, tag_name, tag_value); // not for phenote Karen 2008 01 28 - postgres_table = "app_heat_sens"; tag_name = "Heat Sensitive"; tag_value = chr.getValueString(tag_name); - updateNormalField(c, s, joinkey, postgres_table, tag_name, tag_value); - postgres_table = "app_heat_degree"; tag_name = "Heat Sensitive Degree"; tag_value = chr.getValueString(tag_name); - updateNormalField(c, s, joinkey, postgres_table, tag_name, tag_value); - postgres_table = "app_cold_sens"; tag_name = "Cold Sensitive"; tag_value = chr.getValueString(tag_name); - updateNormalField(c, s, joinkey, postgres_table, tag_name, tag_value); - postgres_table = "app_cold_degree"; tag_name = "Cold Sensitive Degree"; tag_value = chr.getValueString(tag_name); - updateNormalField(c, s, joinkey, postgres_table, tag_name, tag_value); - postgres_table = "app_mat_effect"; tag_name = "Maternal Effect"; tag_value = ""; - if ( chr.hasValue(tag_name) ) { tag_value = chr.getTerm(tag_name).getID(); } - updateNormalField(c, s, joinkey, postgres_table, tag_name, tag_value); - postgres_table = "app_pat_effect"; tag_name = "Paternal Effect"; tag_value = chr.getValueString(tag_name); - updateNormalField(c, s, joinkey, postgres_table, tag_name, tag_value); - postgres_table = "app_pat_effect"; tag_name = "Haplo"; tag_value = chr.getValueString(tag_name); - updateNormalField(c, s, joinkey, postgres_table, tag_name, tag_value); - postgres_table = "app_genotype"; tag_name = "Genotype"; tag_value = chr.getValueString(tag_name); - updateNormalField(c, s, joinkey, postgres_table, tag_name, tag_value); - postgres_table = "app_strain"; tag_name = "Strain"; tag_value = chr.getValueString(tag_name); - updateNormalField(c, s, joinkey, postgres_table, tag_name, tag_value); - postgres_table = "app_obj_remark"; tag_name = "Object Remark"; tag_value = chr.getValueString(tag_name); - updateNormalField(c, s, joinkey, postgres_table, tag_name, tag_value); - postgres_table = "app_allele_status"; tag_name = "Allele Status"; tag_value = chr.getValueString(tag_name); - updateNormalField(c, s, joinkey, postgres_table, tag_name, tag_value); } catch (Exception e) { System.out.println("Could not get terms from character: " + e); @@ -336,15 +271,8 @@ } private void init() { - // for now adding constraints here - take this out when configging is working - // done now in config - //ConstraintManager.inst().addConstraint(new WormConstraint()); - - // dont HAVE to use CharFieldEnum but it does enforce using same strings - // across different data adapters which is good to enforce - // the worm config needs to have "Pub" and "Object Name" + // dont HAVE to use CharFieldEnum but it does enforce using same strings across different data adapters which is good to enforce the worm config needs to have "Pub" and "Object Name" queryableFields.add(CharFieldEnum.PUB.getName()); // "Pub" -// queryableFields.add(CharFieldEnum.ALLELE.getName()); // "Allele" queryableFields.add("Object Name"); // "Object Name" // should their be a check that the current char fields have pub & allele? // queryableFields.add("NBP Date"); @@ -369,14 +297,8 @@ System.out.println("Registered the driver ok, so let's make a connection."); Connection c = null; try { - // The second and third arguments are the username and password, - // respectively. They should be whatever is necessary to connect - // to the database. - // c = DriverManager.getConnection("jdbc:postgresql://andiamo.caltech.edu/testdb", "postgres", ""); - // c = DriverManager.getConnection("jdbc:postgresql://131.215.52.86:5432/testdb", "postgres", ""); // andiamo, if postgres is running there + // The second and third arguments are the username and password, respectively. They should be whatever is necessary to connect to the database. c = DriverManager.getConnection("jdbc:postgresql://131.215.52.76:5432/testdb", "postgres", ""); // tazendra - // c = DriverManager.getConnection("jdbc:postgresql://localhost/testdb", "postgres", ""); // with /usr/local/pgsql/data/postgresql.conf set to localhost - //c = DriverManager.getConnection("jdbc:postgresql://localhost/booktown", "username", "password"); // sample } catch (SQLException se) { System.out.println("Couldn't connect: print out a stack trace and exit."); se.printStackTrace(); @@ -384,13 +306,6 @@ String getMessage = se.getMessage(); getMessage = getMessage + " \nYour IP is not recognized by the postgres database, please contact he...@wo... if you're a valid Phenotype WormBase curator."; JOptionPane.showMessageDialog(null,getMessage,"Postgres connection error",JOptionPane.INFORMATION_MESSAGE); -// Throwable throwCause = se.getCause(); -// String getCause = throwCause.toString(); -// JOptionPane.showMessageDialog(null,getCause,"getCause",JOptionPane.INFORMATION_MESSAGE); -// String m = "Your IP is not recognized by the postgres database, please contact he...@wo... if you're a valid Phenotype WormBase curator."; -// JOptionPane.showMessageDialog(null,m,"Worm stub",JOptionPane.INFORMATION_MESSAGE); - // System.exit(1); - // System.out.println("Couldn't connect: exit done."); } if (c != null) System.out.println("Hooray! We connected to the database!"); @@ -399,88 +314,21 @@ return c; } // private Connection connectToDB - private String queryPostgresCharacterNull(Statement s, String postgres_table, String joinkey) { - // see if the postgres value corresponding to a phenote cell has an entry at all (@row) ; returns ``null'' if no row - String default_value = null; - ResultSet rs = null; // intialize postgres query result - try { rs = s.executeQuery("SELECT * FROM "+postgres_table+" WHERE joinkey = '"+joinkey+"' ORDER BY app_timestamp"); } - catch (SQLException se) { - System.out.println("We got an exception while executing our "+postgres_table+" joinkey: that probably means our term SQL is invalid"); se.printStackTrace(); System.exit(1); } - try { while (rs.next()) { default_value = rs.getString(2); } } // assign the new term value - catch (SQLException se) { - System.out.println("We got an exception while getting a queryPostgresCharacter "+postgres_table+" result:this shouldn't happen: we've done something really bad."); se.printStackTrace(); System.exit(1); } - if (default_value == null) { default_value = "null"; } - return default_value; - } - - private String queryPostgresCharacter(Statement s, String postgres_table, String default_value, String joinkey) { - // get the value corresponding to a phenote cell from a postgres table by column + private String queryPostgresCharacter(Statement s, String postgres_table, String default_value, String joinkey) { // get the value corresponding to a phenote cell from a postgres table by column //System.out.println( "queryPostgresCharacter for "+postgres_table+" "+joinkey+" with default_value "+default_value+" end"); ResultSet rs = null; // intialize postgres query result // get the phenotype term in timestamp order where the allele and column number match - try { rs = s.executeQuery("SELECT * FROM "+postgres_table+" WHERE joinkey = '"+joinkey+"' ORDER BY app_timestamp"); } + try { rs = s.executeQuery("SELECT * FROM "+postgres_table+" WHERE joinkey = '"+joinkey+"' ORDER BY int_timestamp"); } catch (SQLException se) { System.out.println("We got an exception while executing our "+postgres_table+" joinkey: that probably means our term SQL is invalid"); se.printStackTrace(); System.exit(1); } try { while (rs.next()) { default_value = rs.getString(2); } } // assign the new term value catch (SQLException se) { System.out.println("We got an exception while getting a queryPostgresCharacter "+postgres_table+" result:this shouldn't happen: we've done something really bad."); se.printStackTrace(); System.exit(1); } -// System.out.println("Added in function charList term "+query+" column "+colI+"."); // comment out later -// if (default_value == null) { default_value = "postgres value is null"; } -// if (default_value == "") { default_value = "postgres value is blank"; } if (default_value == null) { default_value = ""; } //System.out.println( "queryPostgresCharacter for "+postgres_table+" "+joinkey+" gives "+default_value+" end"); return default_value; - } + } // private String queryPostgresCharacter(Statement s, String postgres_table, String default_value, String joinkey) - private String queryPostgresCharacterDate(Statement s, String postgres_table, String default_value, String joinkey) { - ResultSet rs = null; // intialize postgres query result - try { rs = s.executeQuery("SELECT * FROM "+postgres_table+" WHERE joinkey = '"+joinkey+"' ORDER BY app_timestamp"); } - catch (SQLException se) { - System.out.println("We got an exception while executing our "+postgres_table+" query: that probably means our term SQL is invalid"); se.printStackTrace(); System.exit(1); } - try { while (rs.next()) { default_value = rs.getString(3); } } // assign the new term value - catch (SQLException se) { - System.out.println("We got an exception while getting a queryPostgresCharacterDate "+postgres_table+" result:this shouldn't happen: we've done something really bad."); se.printStackTrace(); System.exit(1); } -// if (default_value == null) { default_value = "postgres value is null"; } -// if (default_value == "") { default_value = "postgres value is blank"; } - if (default_value == null) { default_value = ""; } - String date_match = find("([0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9] [0-9][0-9]:[0-9][0-9])", default_value); // Find a WBPaper followed by any amount of digits - if (date_match != null) { default_value = date_match; } // query for this, otherwise keep the default value - return default_value; - } - - private String queryPostgresPapAll(Statement s, String postgres_table, String joinkey) { - StringBuilder sb = new StringBuilder(); - ResultSet rs = null; // intialize postgres query result -// System.out.println("SELECT * FROM "+postgres_table+"_hst WHERE joinkey = '"+joinkey+"' ORDER BY app_timestamp"); - try { rs = s.executeQuery("SELECT * FROM "+postgres_table+"_hst WHERE joinkey = '"+joinkey+"' ORDER BY app_timestamp"); } - catch (SQLException se) { - System.out.println("We got an exception while executing our "+postgres_table+"_hst query: that probably means our term SQL is invalid"); se.printStackTrace(); System.exit(1); } - try { while (rs.next()) { sb.append(rs.getString(2)).append(" -- "); } } // append the new term value - catch (SQLException se) { - System.out.println("We got an exception while getting a queryPostgresPapAll"+postgres_table+"_hst result:this shouldn't happen: we've done something really bad."); se.printStackTrace(); System.exit(1); } - String pap_hist = sb.toString(); -// System.out.println("pap_hist "+pap_hist+" is not null"); -// if (pap_hist == null) { pap_hist = "postgres value is null"; } -// if (pap_hist == "") { pap_hist = "postgres value is blank"; } - if (pap_hist == null) { pap_hist = ""; } - return pap_hist; - } - - private String queryPostgresPap(Statement s, String postgres_table, String joinkey) { - String pap_latest = ""; - ResultSet rs = null; // intialize postgres query result -// System.out.println("SELECT * FROM "+postgres_table+"_hst WHERE joinkey = '"+joinkey+"' ORDER BY app_timestamp"); - try { rs = s.executeQuery("SELECT * FROM "+postgres_table+"_hst WHERE joinkey = '"+joinkey+"' ORDER BY app_timestamp"); } - catch (SQLException se) { - System.out.println("We got an exception while executing our "+postgres_table+"_hst query: that probably means our term SQL is invalid"); se.printStackTrace(); System.exit(1); } - try { while (rs.next()) { pap_latest = rs.getString(2); } } // append the new term value - catch (SQLException se) { - System.out.println("We got an exception while getting a queryPostgresPap"+postgres_table+"_hst result:this shouldn't happen: we've done something really bad."); se.printStackTrace(); System.exit(1); } -//System.out.println("pap_latest "+pap_latest+" for joinkey "+joinkey+" end"); - if (pap_latest == null) { pap_latest = ""; } - return pap_latest; - } - private CharacterListI queryPostgresCharacterMainList(CharacterListI charList, Statement s, String joinkey) { // populate a phenote character based on postgres value by joinkey, then append to character list try { @@ -488,151 +336,75 @@ //System.out.println("set PgdbId to "+joinkey+" END"); c1.setValue("PgdbId",joinkey); // assign the allele and the column - String postgres_table = "app_type"; String postgres_value = ""; // postgres_value = "No postgres value assigned"; + String postgres_table = "int_name"; String postgres_value = ""; // postgres_value = "No postgres value assigned"; postgres_value = queryPostgresCharacter(s, postgres_table, postgres_value, joinkey); - c1.setValue("Object Type",postgres_value); // assign the queried value - postgres_table = "app_tempname"; postgres_value = ""; // postgres_value = "No postgres value assigned"; - postgres_value = queryPostgresCharacter(s, postgres_table, postgres_value, joinkey); c1.setValue("Object Name",postgres_value); // assign the allele and the column - postgres_value = ""; // postgres_value = "No postgres value assigned"; - postgres_table = "app_term"; + postgres_table = "int_effector"; postgres_value = ""; // postgres_value = "No postgres value assigned"; postgres_value = queryPostgresCharacter(s, postgres_table, postgres_value, joinkey); - String phenotype_match = find("(WBPhenotype[0-9]*)", postgres_value); // Find a WBPhenotype followed by any amount of digits - if (phenotype_match != null) { postgres_value = phenotype_match; } // query for this, otherwise keep the default value -// if (postgres_value == "No postgres value assigned") { } else { c1.setValue("Phenotype",postgres_value); } // assign the queried value - if (postgres_value == "") { } else { c1.setValue("Phenotype",postgres_value); } // assign the queried value -//System.out.println("set Phenotype to "+postgres_value+" END"); - postgres_table = "app_intx_desc"; postgres_value = ""; // postgres_value = "No postgres value assigned"; + if (postgres_value == "") { } else { c1.setValue("Effector",postgres_value); } // assign the queried value + postgres_table = "int_torvariation"; postgres_value = ""; // postgres_value = "No postgres value assigned"; postgres_value = queryPostgresCharacter(s, postgres_table, postgres_value, joinkey); - if (postgres_value == "") { } else { c1.setValue("Genetic Intx Desc",postgres_value); } // assign the queried value - postgres_table = "app_curator"; postgres_value = ""; // postgres_value = "No postgres value assigned"; + if (postgres_value == "") { } else { c1.setValue("tor Variation",postgres_value); } // assign the queried value + postgres_table = "int_tortransgene"; postgres_value = ""; // postgres_value = "No postgres value assigned"; postgres_value = queryPostgresCharacter(s, postgres_table, postgres_value, joinkey); - if (postgres_value == "") { } else { c1.setValue("Curator",postgres_value); } // assign the queried value -//System.out.println("set Curator to "+postgres_value+" END"); - postgres_table = "app_not"; postgres_value = ""; // postgres_value = "No postgres value assigned"; + if (postgres_value == "") { } else { c1.setValue("tor Transgene",postgres_value); } // assign the queried value + postgres_table = "int_torremark"; postgres_value = ""; // postgres_value = "No postgres value assigned"; postgres_value = queryPostgresCharacter(s, postgres_table, postgres_value, joinkey); - if (postgres_value == "") { } else { c1.setValue("Not",postgres_value); } // assign the queried value - postgres_table = "app_phen_remark"; postgres_value = ""; // postgres_value = "No postgres value assigned"; + if (postgres_value == "") { } else { c1.setValue("tor Remark",postgres_value); } // assign the queried value + + postgres_table = "int_effected"; postgres_value = ""; // postgres_value = "No postgres value assigned"; postgres_value = queryPostgresCharacter(s, postgres_table, postgres_value, joinkey); - if (postgres_value == "") { } else { c1.setValue("Phenotype Remark",postgres_value); } // assign the queried value -//System.out.println("set PhenotypeRemark to "+postgres_value+" END"); - postgres_table = "app_anat_term"; postgres_value = ""; // postgres_value = "No postgres value assigned"; + if (postgres_value == "") { } else { c1.setValue("Effected",postgres_value); } // assign the queried value + postgres_table = "int_tedvariation"; postgres_value = ""; // postgres_value = "No postgres value assigned"; postgres_value = queryPostgresCharacter(s, postgres_table, postgres_value, joinkey); -//System.out.println("queryied anat_term to "+postgres_value+" END"); - if (postgres_value == "") { } else { c1.setValue("Anatomy",postgres_value); } // assign the queried IDs -// c1.setValue("Anatomy","WBbt:0004758"); // this works, assigning a term ID - postgres_table = "app_entity"; postgres_value = ""; // postgres_value = "No postgres value assigned"; + if (postgres_value == "") { } else { c1.setValue("ted Variation",postgres_value); } // assign the queried value + postgres_table = "int_tedtransgene"; postgres_value = ""; // postgres_value = "No postgres value assigned"; postgres_value = queryPostgresCharacter(s, postgres_table, postgres_value, joinkey); - if (postgres_value == "") { } else { c1.setValue("Entity",postgres_value); } // assign the queried value - postgres_table = "app_quality"; postgres_value = ""; // postgres_value = "No postgres value assigned"; + if (postgres_value == "") { } else { c1.setValue("ted Transgene",postgres_value); } // assign the queried value + postgres_table = "int_tedremark"; postgres_value = ""; // postgres_value = "No postgres value assigned"; postgres_value = queryPostgresCharacter(s, postgres_table, postgres_value, joinkey); - if (postgres_value == "") { } else { c1.setValue("Quality",postgres_value); } // assign the queried value - postgres_table = "app_suggested"; postgres_value = ""; // postgres_value = "No postgres value assigned"; + if (postgres_value == "") { } else { c1.setValue("ted Remark",postgres_value); } // assign the queried value + + postgres_table = "int_type"; postgres_value = ""; // postgres_value = "No postgres value assigned"; postgres_value = queryPostgresCharacter(s, postgres_table, postgres_value, joinkey); - if (postgres_value == "") { } else { c1.setValue("Suggested",postgres_value); } // assign the queried value - postgres_table = "app_lifestage"; postgres_value = ""; // postgres_value = "No postgres value assigned"; + if (postgres_value == "") { } else { c1.setValue("Type",postgres_value); } // assign the queried value + + postgres_value = ""; // postgres_value = "No postgres value assigned"; + postgres_table = "int_phenotype"; postgres_value = queryPostgresCharacter(s, postgres_table, postgres_value, joinkey); -//System.out.println("queryied lifestage to "+postgres_value+" END"); - if (postgres_value != null) { - if (postgres_value == "") { } else { c1.setValue("Life Stage",postgres_value); } // assign the queried value -} -//System.out.println("set LifeStage to "+postgres_value+" END"); - postgres_table = "app_nature"; postgres_value = ""; // postgres_value = "No postgres value assigned"; + String phenotype_match = find("(WBPhenotype[0-9]*)", postgres_value); // Find a WBPhenotype followed by any amount of digits + if (phenotype_match != null) { postgres_value = phenotype_match; } // query for this, otherwise keep the default value +// if (postgres_value == "No postgres value assigned") { } else { c1.setValue("Phenotype",postgres_value); } // assign the queried value + if (postgres_value == "") { } else { c1.setValue("Phenotype",postgres_value); } // assign the queried value +//System.out.println("set Phenotype to "+postgres_value+" END"); +// + postgres_value = ""; // postgres_value = "No postgres value assigned"; + postgres_table = "int_rnai"; postgres_value = queryPostgresCharacter(s, postgres_table, postgres_value, joinkey); - if (postgres_value == "") { } else { c1.setValue("Allele Nature",postgres_value); } // assign the queried value - postgres_table = "app_func"; postgres_value = ""; // postgres_value = "No postgres value assigned"; + String rnai_match = find("(WBRNAi[0-9]*)", postgres_value); // Find a WBPhenotype followed by any amount of digits + if (rnai_match != null) { postgres_value = rnai_match; } // query for this, otherwise keep the default value + if (postgres_value == "") { } else { c1.setValue("RNAi",postgres_value); } // assign the queried value + + postgres_table = "int_remark"; postgres_value = ""; // postgres_value = "No postgres value assigned"; postgres_value = queryPostgresCharacter(s, postgres_table, postgres_value, joinkey); - if (postgres_value == "") { } else { c1.setValue("Functional Change",postgres_value); } // assign the queried value - postgres_table = "app_temperature"; postgres_value = ""; // postgres_value = "No postgres value assigned"; - postgres_value = queryPostgresCharacter(s, postgres_table, postgres_value, joinkey); - if (postgres_value == "") { } else { c1.setValue("Temperature",postgres_value); } // assign the queried value -//System.out.println("set Temperature to "+postgres_value+" END"); - postgres_table = "app_treatment"; postgres_value = ""; // postgres_value = "No postgres value assigned"; - postgres_value = queryPostgresCharacter(s, postgres_table, postgres_value, joinkey); - if (postgres_value == "") { } else { c1.setValue("Treatment",postgres_value); } // assign the queried value - postgres_table = "app_penetrance"; postgres_value = ""; // postgres_value = "No postgres value assigned"; - postgres_value = queryPostgresCharacter(s, postgres_table, postgres_value, joinkey); - if (postgres_value == "") { } else { c1.setValue("Penetrance",postgres_value); } // assign the queried value - postgres_table = "app_percent"; postgres_value = ""; // postgres_value = "No postgres value assigned"; - postgres_value = queryPostgresCharacter(s, postgres_table, postgres_value, joinkey); - if (postgres_value == "") { } else { c1.setValue("Penetrance Remark",postgres_value); } // assign the queried value -//System.out.println("set PenetranceRemark to "+postgres_value+" END"); - postgres_table = "app_range_start"; postgres_value = ""; // postgres_value = "No postgres value assigned"; - postgres_value = queryPostgresCharacter(s, postgres_table, postgres_value, joinkey); - if (postgres_value == "") { } else { c1.setValue("Penetrance Range Start",postgres_value); } // assign the queried value - postgres_table = "app_range_end"; postgres_value = ""; // postgres_value = "No postgres value assigned"; - postgres_value = queryPostgresCharacter(s, postgres_table, postgres_value, joinkey); - if (postgres_value == "") { } else { c1.setValue("Penetrance Range End",postgres_value); } // assign the queried value -// postgres_table = "app_quantity"; postgres_value = ""; // postgres_value = "No postgres value assigned"; // not for phenote, Karen 2008 01 28 -// postgres_value = queryPostgresCharacter(s, postgres_table, postgres_value, joinkey); -// c1.setValue("Quantity",postgres_value); // assign the queried value -////System.out.println("set Quantity to "+postgres_value+" END"); -// postgres_table = "app_quantity_remark"; postgres_value = ""; // postgres_value = "No postgres value assigned"; // not for phenote, Karen 2008 01 28 -// postgres_value = queryPostgresCharacter(s, postgres_table, postgres_value, joinkey); -// c1.setValue("Quantity Remark",postgres_value); // assign the queried value - postgres_table = "app_heat_sens"; postgres_value = ""; // postgres_value = "No postgres value assigned"; - postgres_value = queryPostgresCharacter(s, postgres_table, postgres_value, joinkey); - if (postgres_value == "") { } else { c1.setValue("Heat Sensitive",postgres_value); } // assign the queried value - postgres_table = "app_heat_degree"; postgres_value = ""; // postgres_value = "No postgres value assigned"; - postgres_value = queryPostgresCharacter(s, postgres_table, postgres_value, joinkey); - if (postgres_value == "") { } else { c1.setValue("Heat Sensitive Degree",postgres_value); } // assign the queried value -//System.out.println("set HeatSens to "+postgres_value+" END"); - postgres_table = "app_cold_sens"; postgres_value = ""; // postgres_value = "No postgres value assigned"; - postgres_value = queryPostgresCharacter(s, postgres_table, postgres_value, joinkey); - if (postgres_value == "") { } else { c1.setValue("Cold Sensitive",postgres_value); } // assign the queried value - postgres_table = "app_cold_degree"; postgres_value = ""; // postgres_value = "No postgres value assigned"; - postgres_value = queryPostgresCharacter(s, postgres_table, postgres_value, joinkey); - if (postgres_value == "") { } else { c1.setValue("Cold Sensitive Degree",postgres_value); } // assign the queried value - postgres_table = "app_mat_effect"; postgres_value = ""; // postgres_value = "No postgres value assigned"; - postgres_value = queryPostgresCharacter(s, postgres_table, postgres_value, joinkey); - if (postgres_value == "") { } else { c1.setValue("Maternal Effect",postgres_value); } // assign the queried value - postgres_table = "app_pat_effect"; postgres_value = ""; // postgres_value = "No postgres value assigned"; - postgres_value = queryPostgresCharacter(s, postgres_table, postgres_value, joinkey); - if (postgres_value == "") { } else { c1.setValue("Paternal Effect",postgres_value); } // assign the queried value -//System.out.println("set PatEffect to "+postgres_value+" END"); - postgres_table = "app_haplo"; postgres_value = ""; // postgres_value = "No postgres value assigned"; - postgres_value = queryPostgresCharacter(s, postgres_table, postgres_value, joinkey); - if (postgres_value == "") { } else { c1.setValue("Haplo",postgres_value); } // assign the queried value - postgres_table = "app_genotype"; postgres_value = ""; // postgres_value = "No postgres value assigned"; - postgres_value = queryPostgresCharacter(s, postgres_table, postgres_value, joinkey); - if (postgres_value == "") { } else { c1.setValue("Genotype",postgres_value); } // assign the queried value - postgres_table = "app_strain"; postgres_value = ""; // postgres_value = "No postgres value assigned"; - postgres_value = queryPostgresCharacter(s, postgres_table, postgres_value, joinkey); - if (postgres_value == "") { } else { c1.setValue("Strain",postgres_value); } // assign the queried value - postgres_table = "app_obj_remark"; postgres_value = ""; // postgres_value = "No postgres value assigned"; - postgres_value = queryPostgresCharacter(s, postgres_table, postgres_value, joinkey); - if (postgres_value == "") { } else { c1.setValue("Object Remark",postgres_value); } // assign the queried value -//System.out.println("set ObjRem to "+postgres_value+" END"); - postgres_table = "app_allele_status"; postgres_value = ""; // postgres_value = "No postgres value assigned"; - postgres_value = queryPostgresCharacter(s, postgres_table, postgres_value, joinkey); - if (postgres_value == "") { } else { c1.setValue("Allele Status",postgres_value); } // assign the queried value + if (postgres_value == "") { } else { c1.setValue("Remark",postgres_value); } // assign the queried value - postgres_table = "app_paper"; postgres_value = ""; // postgres_value = "No postgres value assigned"; + postgres_table = "int_paper"; postgres_value = ""; // postgres_value = "No postgres value assigned"; postgres_value = queryPostgresCharacter(s, postgres_table, postgres_value, joinkey); if (postgres_value == "") { } else { c1.setValue("Pub",postgres_value); } // assign the queried value //System.out.println("set Pub to "+postgres_value+" END"); - if ( (postgres_value != null) && (postgres_value != "") ) { - String pap_value = postgres_value; - postgres_table = "app_paper_remark"; - postgres_value = queryPostgresPapAll(s, postgres_table, pap_value); - if (postgres_value == "") { } else { c1.setValue("Paper Remark History",postgres_value); } // assign the queried value - postgres_table = "app_curation_status"; - postgres_value = queryPostgresPap(s, postgres_table, pap_value); - if (postgres_value == "") { } else { c1.setValue("Curation Status",postgres_value); } } // assign the queried value - postgres_table = "app_person"; postgres_value = ""; // postgres_value = "No postgres value assigned"; + postgres_table = "int_person"; postgres_value = ""; // postgres_value = "No postgres value assigned"; postgres_value = queryPostgresCharacter(s, postgres_table, postgres_value, joinkey); if (postgres_value == "") { } else { c1.setValue("Person",postgres_value); } // assign the queried value //System.out.println("set Person to "+postgres_value+" END"); - postgres_table = "app_laboratory"; postgres_value = ""; // postgres_value = "No postgres value assigned"; + postgres_table = "int_curator"; postgres_value = ""; // postgres_value = "No postgres value assigned"; postgres_value = queryPostgresCharacter(s, postgres_table, postgres_value, joinkey); - if (postgres_value == "") { } else { c1.setValue("Laboratory Evidence",postgres_value); } // assign the queried value - postgres_table = "app_nbp"; postgres_value = ""; // postgres_value = "No postgres value assigned"; // NBP is nbp, not intx_desc + if (postgres_value == "") { } else { c1.setValue("Curator",postgres_value); } // assign the queried value +//System.out.println("set Curator to "+postgres_value+" END"); + postgres_table = "int_otherevi"; postgres_value = ""; // postgres_value = "No postgres value assigned"; postgres_value = queryPostgresCharacter(s, postgres_table, postgres_value, joinkey); - if (postgres_value == "") { } else { c1.setValue("NBP",postgres_value); } // assign the queried value - postgres_table = "app_nbp"; postgres_value = ""; // postgres_value = "No postgres value assigned"; - postgres_value = queryPostgresCharacterDate(s, postgres_table, postgres_value, joinkey); - if (postgres_value == "") { } else { c1.setValue("NBP Date",postgres_value); } // assign the queried value + if (postgres_value == "") { } else { c1.setValue("Other Evidence",postgres_value); } // assign the queried value charList.add(c1); // add the character to the character list } @@ -645,18 +417,11 @@ public CharacterListI query(String group, String field, String query) throws DataAdapterEx { - // something like this....? - // if (group.equals("default")) return queryForDefaultGroup(field,query) - // else if (group.equals("referenceMaker")) return queryForReferenceMaker(field,query); - // if (group.equals("default")) { System.out.println("Querying group default field "+field+" query "+query+" end"); } - // else if (group.equals("referenceMaker")) { System.out.println("Querying group referenceMaker field "+field+" query "+query+" end"); } - // String m = "Worm adapter query not yet implemented. field: "+field+" query: "+query; // JOptionPane.showMessageDialog(null,m,"Worm stub",JOptionPane.INFORMATION_MESSAGE); - String alleleString = "Object Name"; // the query could be for Allele or Pub + String nameString = "Object Name"; // the query could be for Allele or Pub String pubString = "Pub"; - String nbpString = "NBP Date"; CharacterListI charList = new CharacterList(); // create the CharacterList that we will return @@ -667,26 +432,22 @@ ResultSet rs = null; // intialize postgres query result List<String> joinkeys = new ArrayList<String>(2); - int foundAllele = 0; + int foundResults = 0; - if (field.equals(alleleString)) { // if querying the allele, get allele data - try { rs = s.executeQuery("SELECT * FROM app_tempname WHERE app_tempname ~ '"+query+"' ORDER BY joinkey"); } // find the allele that matches the queried allele switch to substring search for Karen 2008 02 21 - catch (SQLException se) { System.out.println("Exception while executing app_tempname alleleString "+query+" query: that probably means our SQL is invalid"); se.printStackTrace(); System.exit(1); } + if (field.equals(nameString)) { // if querying the name, get name data + try { rs = s.executeQuery("SELECT * FROM int_name WHERE int_name ~ '"+query+"' ORDER BY joinkey"); } // find the substring name that matches the queried name + catch (SQLException se) { System.out.println("Exception while executing int_name nameString "+query+" query: that probably means our SQL is invalid"); se.printStackTrace(); System.exit(1); } } else if (field.equals(pubString)) { // if querying the publication, get paper data - try { rs = s.executeQuery("SELECT * FROM app_paper WHERE app_paper ~ '"+query+"' ORDER BY joinkey"); } // find the allele that matches the queried allele - catch (SQLException se) { System.out.println("Exception while executing app_paper pubString "+query+" query: that probably means our SQL is invalid"); se.printStackTrace(); System.exit(1); } - } else if (field.equals(nbpString)) { // if querying the app_nbp NBP Date, get matching data - try { rs = s.executeQuery("SELECT * FROM app_nbp WHERE app_timestamp ~ '"+query+"' ORDER BY joinkey;"); } // get the alleles from a paper - catch (SQLException se) { System.out.println("Exception while executing app_nbp nbpString "+query+" query: that probably means our SQL is invalid"); se.printStackTrace(); System.exit(1); } + try { rs = s.executeQuery("SELECT * FROM int_paper WHERE int_paper ~ '"+query+"' ORDER BY joinkey"); } // find the name that matches the queried name + catch (SQLException se) { System.out.println("Exception while executing int_paper pubString "+query+" query: that probably means our SQL is invalid"); se.printStackTrace(); System.exit(1); } } else { - // if query has failed... throw new DataAdapterEx("Worm query of "+query+" of field "+field+" failed"); } try { while (rs.next()) { joinkeys.add(rs.getString(1)); - foundAllele++; } } + foundResults++; } } catch (SQLException se) { System.out.println("We got an exception while getting a query catch while rs.next tempname joinkey "+query+" result:this shouldn't happen: we've done something really bad."); se.printStackTrace(); System.exit(1); } @@ -696,7 +457,7 @@ charList = queryPostgresCharacterMainList(charList, s, joinkey); // System.out.println("END "+joinkey+" List"); } - if (foundAllele <= 0) { throw new DataAdapterEx("Worm query of "+query+" of field "+field+" has no match in postgres"); } // if there is no match for the allele in postgres + if (foundResults <= 0) { throw new DataAdapterEx("Worm query of "+query+" of field "+field+" has no match in postgres"); } // if there is no match for the allele in postgres else { return charList; } // if there is a match } // public CharacterListI query(String field, String query) throws DataAdapterEx @@ -708,3 +469,55 @@ // __END__ + +// private String queryPostgresPapAll(Statement s, String postgres_table, String joinkey) { +// StringBuilder sb = new StringBuilder(); +// ResultSet rs = null; // intialize postgres query result +//// System.out.println("SELECT * FROM "+postgres_table+"_hst WHERE joinkey = '"+joinkey+"' ORDER BY app_timestamp"); +// try { rs = s.executeQuery("SELECT * FROM "+postgres_table+"_hst WHERE joinkey = '"+joinkey+"' ORDER BY app_timestamp"); } +// catch (SQLException se) { +// System.out.println("We got an exception while executing our "+postgres_table+"_hst query: that probably means our term SQL is invalid"); se.printStackTrace(); System.exit(1); } +// try { while (rs.next()) { sb.append(rs.getString(2)).append(" -- "); } } // append the new term value +// catch (SQLException se) { +// System.out.println("We got an exception while getting a queryPostgresPapAll"+postgres_table+"_hst result:this shouldn't happen: we've done something really bad."); se.printStackTrace(); System.exit(1); } +// String pap_hist = sb.toString(); +//// System.out.println("pap_hist "+pap_hist+" is not null"); +//// if (pap_hist == null) { pap_hist = "postgres value is null"; } +//// if (pap_hist == "") { pap_hist = "postgres value is blank"; } +// if (pap_hist == null) { pap_hist = ""; } +// return pap_hist; +// } +// +// private String queryPostgresPap(Statement s, String postgres_table, String joinkey) { +// String pap_latest = ""; +// ResultSet rs = null; // intialize postgres query result +//// System.out.println("SELECT * FROM "+postgres_table+"_hst WHERE joinkey = '"+joinkey+"' ORDER BY app_timestamp"); +// try { rs = s.executeQuery("SELECT * FROM "+postgres_table+"_hst WHERE joinkey = '"+joinkey+"' ORDER BY app_timestamp"); } +// catch (SQLException se) { +// System.out.println("We got an exception while executing our "+postgres_table+"_hst query: that probably means our term SQL is invalid"); se.printStackTrace(); System.exit(1); } +// try { while (rs.next()) { pap_latest = rs.getString(2); } } // append the new term value +// catch (SQLException se) { +// System.out.println("We got an exception while getting a queryPostgresPap"+postgres_table+"_hst result:this shouldn't happen: we've done something really bad."); se.printStackTrace(); System.exit(1); } +////System.out.println("pap_latest "+pap_latest+" for joinkey "+joinkey+" end"); +// if (pap_latest == null) { pap_latest = ""; } +// return pap_latest; +// } + +// private String queryPostgresCharacterDate(Statement s, String postgres_table, String default_value, String joinkey) { +// ResultSet rs = null; // intialize postgres query result +// try { rs = s.executeQuery("SELECT * FROM "+postgres_table+" WHERE joinkey = '"+joinkey+"' ORDER BY app_timestamp"); } +// catch (SQLException se) { +// System.out.println("We got an exception while executing our "+postgres_table+" query: that probably means our term SQL is invalid"); se.printStackTrace(); System.exit(1); } +// try { while (rs.next()) { default_value = rs.getString(3); } } // assign the new term value +// catch (SQLException se) { +// System.out.println("We got an exception while getting a queryPostgresCharacterDate "+postgres_table+" result:this shouldn't happen: we've done something really bad."); se.printStackTrace(); System.exit(1); } +//// if (default_value == null) { default_value = "postgres value is null"; } +//// if (default_value == "") { default_value = "postgres value is blank"; } +// if (default_value == null) { default_value = ""; } +// String date_match = find("([0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9] [0-9][0-9]:[0-9][0-9])", default_value); // Find a WBPaper followed by any amount of digits +// if (date_match != null) { default_value = date_match; } // query for this, otherwise keep the default value +// return default_value; +// } + + + This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |