treebase-guts — Commit and tracker messages

[Treebase-guts] SF.net SVN: treebase:[520] trunk/treebase-core/db

[<vga...@us...>]

Revision: 520
          http://treebase.svn.sourceforge.net/treebase/?rev=520&view=rev
Author:   vgapeyev
Date:     2010-02-26 16:02:32 +0000 (Fri, 26 Feb 2010)

Log Message:
-----------
Patch to add taxonlabel.tb1legacyid field

Modified Paths:
--------------
    trunk/treebase-core/db/init_db_uptodate.pg

Added Paths:
-----------
    trunk/treebase-core/db/patches/0005_add-taxonabel-tb1legacyid.sql

Modified: trunk/treebase-core/db/init_db_uptodate.pg
===================================================================
--- trunk/treebase-core/db/init_db_uptodate.pg	2010-02-25 15:33:06 UTC (rev 519)
+++ trunk/treebase-core/db/init_db_uptodate.pg	2010-02-26 16:02:32 UTC (rev 520)
@@ -8,5 +8,7 @@
 \i patches/0002_drop-geospot_id_sequence.sql
 \i patches/0003_rename-stepmatrixelement-pk.sql
 \i patches/0004_create-indexes.sql
+\i patches/0005_add-taxonabel-tb1legacyid.sql
 
 
+

Added: trunk/treebase-core/db/patches/0005_add-taxonabel-tb1legacyid.sql
===================================================================
--- trunk/treebase-core/db/patches/0005_add-taxonabel-tb1legacyid.sql	                        (rev 0)
+++ trunk/treebase-core/db/patches/0005_add-taxonabel-tb1legacyid.sql	2010-02-26 16:02:32 UTC (rev 520)
@@ -0,0 +1,8 @@
+insert into versionhistory(patchnumber, patchlabel, patchdescription) 
+       values (5, 'add-taxonlabel-tb1legacyid', 
+       'Add tb1legacyid field to the taxonlabel table'); 
+
+alter table taxonlabel add column tb1legacyid varchar(20); 
+
+
+       
\ No newline at end of file


This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.

[Treebase-guts] SF.net SVN: treebase:[519] trunk/treebase-core/db/TODO.txt

[<vga...@us...>]

Revision: 519
          http://treebase.svn.sourceforge.net/treebase/?rev=519&view=rev
Author:   vgapeyev
Date:     2010-02-25 15:33:06 +0000 (Thu, 25 Feb 2010)

Log Message:
-----------
misc

Modified Paths:
--------------
    trunk/treebase-core/db/TODO.txt

Modified: trunk/treebase-core/db/TODO.txt
===================================================================
--- trunk/treebase-core/db/TODO.txt	2010-02-25 15:32:32 UTC (rev 518)
+++ trunk/treebase-core/db/TODO.txt	2010-02-25 15:33:06 UTC (rev 519)
@@ -44,8 +44,10 @@
      2. I got the user_id for the "migration" user, and it happened to be 9955
      3. I ran this statement: "UPDATE submission SET user_id = 9955 WHERE user_id IS NULL"
      Now all migrated data belong to the user "migration". "
+   Also, see "null->'tb1'; http://www.treebase.org/~piel/taxlabels_fix.zip; Study 22 (Jan 8)"
      
      
+     
 Data cleanups on production: 
 ============================
 


This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.

[Treebase-guts] SF.net SVN: treebase:[518] trunk/treebase-core/src/main

[<vga...@us...>]

Revision: 518
          http://treebase.svn.sourceforge.net/treebase/?rev=518&view=rev
Author:   vgapeyev
Date:     2010-02-25 15:32:32 +0000 (Thu, 25 Feb 2010)

Log Message:
-----------
Fixed run-time errors caused by improper transaction handling.

Modified Paths:
--------------
    trunk/treebase-core/src/main/java/org/cipres/treebase/util/CitationDataImporter.java
    trunk/treebase-core/src/main/java/org/cipres/treebase/util/CitationDataImporterInterface.java
    trunk/treebase-core/src/main/resources/applicationContext-standalone.xml

Modified: trunk/treebase-core/src/main/java/org/cipres/treebase/util/CitationDataImporter.java
===================================================================
--- trunk/treebase-core/src/main/java/org/cipres/treebase/util/CitationDataImporter.java	2010-02-24 14:53:46 UTC (rev 517)
+++ trunk/treebase-core/src/main/java/org/cipres/treebase/util/CitationDataImporter.java	2010-02-25 15:32:32 UTC (rev 518)
@@ -112,8 +112,8 @@
 				person.setFirstName(nameParts[1]);
 				person.setMiddleName("");
 				person.setEmailAddressString("");
-				personHome.save(person);
-				personHome.store(person);				
+				//personHome.save(person);   //-- VG 2010-02-24  These were messing up parent transactions by 
+				//personHome.store(person);  //-- VG		     saving too early. 		
 			}
 			warn(person.getLastName());
 			authors.add(person);
@@ -263,18 +263,136 @@
 			in = new BufferedReader(
 					new InputStreamReader(new FileInputStream(inFileName), "UTF8"));
 
-			me.processCitationFile(in, testmode);
+			//me.processCitationFile(in, testmode);  //VG-2010-02-25 now deprecated
+			me.processAllCitations(in, testmode);
 		} 
 		catch ( Exception e ) {
 			e.printStackTrace();
 		}
 	}	
 	
+	/** Go through the open citation file and transactionally save each successfully processed citation. 
+	 *  Processing stops on error, with prior transactions committed. 
+	 *  Derived from the deprecated processCitationsFile by factoring out most of the while-loop code
+	 *  into its own method, so that Spring can make it transactional. 
+	 */
+	public void processAllCitations(BufferedReader in, boolean testMode) throws NumberFormatException, IOException {
+		String line;
+        while ( ( line = in.readLine()) != null ) {
+        	String[] fields = line.split("\t"); 
+        	processOneCitation(fields, testMode); 
+        }
+        in.close();
+	}	
+
+	/** Process one citation from the file and save the result transactionally.  
+	 *  This method is declared PROPAGATION_REQUIRES_NEW in the bean descriptor.
+	 */
+	public void processOneCitation(String[] fields, boolean testMode) throws NumberFormatException, IOException {
+    	if ( fields[0].startsWith("*") ) {
+    		warn("Line is commented out, skipping");
+    		return;
+    	}
+    	String tb1StudyId = fields[18];
+    	Study study = findStudy(tb1StudyId);
+    	if ( study == null ) {
+    		warn("No such study (TB1 accession): " + tb1StudyId);
+    		return;
+    	}
+    	Citation citationStub = study.getCitation();
+    	Citation citation = null;
+    	if ( citationStub != null ) {
+    		warn("Study "+tb1StudyId+" already associated with a citation "+citationStub.getId());
+//    		 In general, citation stubs already exist for each study. However, these
+//    		 stubs are all of type ArticleCitation, which means that, in the case of
+//    		 BookCitations (for example) we'll get a class cast exception when we try
+//    		 to cast the citation to a book in order to set the editors.
+    		//getCitationService().deleteCitation(citationStub);
+    		//citation = createCitation(fields[0]);
+    		citation = citationStub;
+    	}		
+    	else {
+    		warn("No citation stub association with study (TB1 accession) " + tb1StudyId);
+    		citation = createCitation(fields[0]);
+    	}
+    	if ( citation == null ) {
+    		warn("No citation was instantiated, skipping this record (TB1 accession) "+tb1StudyId);
+    		return;
+    	}
+    	for ( int i = 0; i < fields.length; i++ ) {
+    		if ( ! TreebaseUtil.isEmpty(fields[i]) ) {
+    			switch(i) {
+    				case 0: citation.setCitationType(fields[i]); break;
+    				case 1: setAuthors(citation,fields[i],personHome,false); break;
+    				case 2: citation.setPublishYear(Integer.parseInt(fields[i])); break;
+    				case 3: if ( citation instanceof BookCitation ) { 
+    					((BookCitation)citation).setBookTitle(fields[i]);
+    					citation.setTitle(fields[i]);
+    				} else {
+    					citation.setTitle(fields[i]);
+    				} break;
+    				case 4: setAuthors(citation,fields[i],personHome,true); break;
+    				case 5: if ( citation instanceof ArticleCitation ) {
+    					((ArticleCitation)citation).setJournal(fields[i]);
+    					warn("Setting journal name "+fields[i]);
+    				} else {
+    					warn("Setting book title "+fields[i]);
+    					((BookCitation)citation).setBookTitle(fields[i]);
+    				} break;
+    				case 6: if ( citation instanceof BookCitation ) {                 					
+    					((BookCitation)citation).setCity(fields[i]); 
+    				} else {
+    					warn("Record has an entry for city, yet it's not a book (TB1 accession) "+tb1StudyId);
+    				} break;
+    				case 7: if ( citation instanceof BookCitation ) {                 					
+    					((BookCitation)citation).setPublisher(fields[i]);
+    				} else {
+    					warn("Record has an entry for publisher, yet it's not a book (TB1 accession) "+tb1StudyId);
+    				} break;
+    				case 8: if ( citation instanceof ArticleCitation ) { 
+    					((ArticleCitation)citation).setVolume(fields[i]);
+    				} else {
+    					warn("Record has an entry for volume, yet it's not an article (TB1 accession) "+tb1StudyId);
+    				} break;
+    				case 10: if ( citation instanceof ArticleCitation ) { 
+    					((ArticleCitation)citation).setIssue(fields[i]);
+    				} else {
+    					warn("Record has an entry for issue, yet it's not an article (TB1 accession)"+tb1StudyId);
+    				} break;
+    				case 11: citation.setPages(fields[i]); break;
+    				case 15: if ( fields[i].equals("in press") ) {
+    					citation.setCitationStatusDescription(CitationStatus.INPRESS);                					
+    				}
+    				case 16: citation.setKeywords(fields[i]); break;
+    				case 17: citation.setAbstract(checkAbstractLength(fields[i])); break;
+    				case 18: citation.setStudy(study); break;
+    				case 19: citation.setURL(fields[i]); break;
+    				case 20: citation.setDoi(fields[i]); break;
+    				default: warn("Empty field expected, found: " + fields[i] + " for line: " + /*was: line */fields.toString()); break;
+    			}
+    		}
+    	}
+    	warn("Storing citation "+citation.getId());
+    	warn(citation.getAuthorsCitationStyleWithoutHtml());   
+    	if ( ! testMode ) {
+        	study.setCitation(citation);
+        	getStudyService().update(study);
+        	Long id = study.getCitation().getId();
+        	warn("Stored citation at ID " + id); 
+    	}
+    	else {
+    		warn("Test run, not saving");
+    	}		
+	}
+	
 	/**
-	 * 
-	 * 
-	 * @see org.cipres.treebase.util.CitationDataImporterInterface#processCitationFile(java.io.BufferedReader, boolean)
+	 * Deprecated, use processAllCitations() instead. 
+	 * The programmatic transaction handling via the commit(); beginTransaction(); pair at the end of this method
+	 * was leaving an open transaction, leading to an exception. Also, the "-test" command line switch had no effect. 
+	 * Instead, fine-grained transactions (for each citation) are now achieved by embracing Spring's declarative 
+	 * transactions: transactions are at boundaries of designated methods.  VG-2010-02-25
 	 */
+	@Deprecated
 	public void processCitationFile(BufferedReader in, boolean testMode) throws NumberFormatException, IOException {
 		String line;
 		Long maxId = new Long(0);

Modified: trunk/treebase-core/src/main/java/org/cipres/treebase/util/CitationDataImporterInterface.java
===================================================================
--- trunk/treebase-core/src/main/java/org/cipres/treebase/util/CitationDataImporterInterface.java	2010-02-24 14:53:46 UTC (rev 517)
+++ trunk/treebase-core/src/main/java/org/cipres/treebase/util/CitationDataImporterInterface.java	2010-02-25 15:32:32 UTC (rev 518)
@@ -24,4 +24,7 @@
 	public void setCitationHome(CitationHome citationHome);
 	
 	public void processCitationFile(BufferedReader in, boolean testMode) throws NumberFormatException, IOException;
+	public void processAllCitations(BufferedReader in, boolean testMode) throws NumberFormatException, IOException;
+    public void processOneCitation(String[] fields, boolean testMode) throws NumberFormatException, IOException;
+
 }

Modified: trunk/treebase-core/src/main/resources/applicationContext-standalone.xml
===================================================================
--- trunk/treebase-core/src/main/resources/applicationContext-standalone.xml	2010-02-24 14:53:46 UTC (rev 517)
+++ trunk/treebase-core/src/main/resources/applicationContext-standalone.xml	2010-02-25 15:32:32 UTC (rev 518)
@@ -222,6 +222,8 @@
 		<property name="transactionAttributes">
 			<props>
 				<prop key="processCitationFile">PROPAGATION_REQUIRED</prop>
+				<prop key="processOneCitation">PROPAGATION_REQUIRES_NEW</prop>
+				<prop key="processAllCitations">PROPAGATION_SUPPORTS</prop>
 			</props>
 		</property>
 	</bean>


This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.

[Treebase-guts] SF.net SVN: treebase:[517] trunk

[<yo...@us...>]

Revision: 517
          http://treebase.svn.sourceforge.net/treebase/?rev=517&view=rev
Author:   youjun
Date:     2010-02-24 14:53:46 +0000 (Wed, 24 Feb 2010)

Log Message:
-----------
add new algorithm option classes, update jsp page accordingly 

Modified Paths:
--------------
    trunk/treebase-core/src/main/java/org/cipres/treebase/auxdata/CommitStudy.java
    trunk/treebase-core/src/main/java/org/cipres/treebase/domain/study/Algorithm.java
    trunk/treebase-core/src/main/resources/hibernate.cfg.xml
    trunk/treebase-web/src/main/java/org/cipres/treebase/web/Constants.java
    trunk/treebase-web/src/main/java/org/cipres/treebase/web/controllers/DisplayAnalysisController.java
    trunk/treebase-web/src/main/java/org/cipres/treebase/web/controllers/SearchSummaryController.java
    trunk/treebase-web/src/main/java/org/cipres/treebase/web/controllers/SummaryController.java
    trunk/treebase-web/src/main/java/org/cipres/treebase/web/listeners/StartupListener.java
    trunk/treebase-web/src/main/java/org/cipres/treebase/web/model/AnalysisStepCommand.java
    trunk/treebase-web/src/main/java/org/cipres/treebase/web/util/ControllerUtil.java
    trunk/treebase-web/src/main/webapp/WEB-INF/pages/submissionSummaryView.jsp

Added Paths:
-----------
    trunk/treebase-core/src/main/java/org/cipres/treebase/domain/study/BayesianAlgorithm.java
    trunk/treebase-core/src/main/java/org/cipres/treebase/domain/study/EvolutionAlgorithm.java
    trunk/treebase-core/src/main/java/org/cipres/treebase/domain/study/JoiningAlgorithm.java
    trunk/treebase-core/src/main/java/org/cipres/treebase/domain/study/UPGMAAlgorithm.java

Removed Paths:
-------------
    trunk/treebase-core/src/main/java/org/cipres/treebase/domain/study/DistanceAlgorithm.java

Modified: trunk/treebase-core/src/main/java/org/cipres/treebase/auxdata/CommitStudy.java
===================================================================
--- trunk/treebase-core/src/main/java/org/cipres/treebase/auxdata/CommitStudy.java	2010-02-19 21:50:27 UTC (rev 516)
+++ trunk/treebase-core/src/main/java/org/cipres/treebase/auxdata/CommitStudy.java	2010-02-24 14:53:46 UTC (rev 517)
@@ -18,7 +18,10 @@
 import org.cipres.treebase.domain.study.AnalyzedMatrix;
 import org.cipres.treebase.domain.study.AnalyzedTree;
 import org.cipres.treebase.domain.study.Citation;
-import org.cipres.treebase.domain.study.DistanceAlgorithm;
+import org.cipres.treebase.domain.study.BayesianAlgorithm;	
+import org.cipres.treebase.domain.study.EvolutionAlgorithm;
+import org.cipres.treebase.domain.study.JoiningAlgorithm;
+import org.cipres.treebase.domain.study.UPGMAAlgorithm;
 import org.cipres.treebase.domain.study.LikelihoodAlgorithm;
 import org.cipres.treebase.domain.study.OtherAlgorithm;
 import org.cipres.treebase.domain.study.ParsimonyAlgorithm;
@@ -222,11 +225,16 @@
 			
 			Algorithm theAlgorithm;
 			String tb1Algorithm = an.getString("algorithm");
-			
 			if (tb1Algorithm.equalsIgnoreCase("parsimony")) {
 				theAlgorithm = new ParsimonyAlgorithm();
-			} else if (tb1Algorithm.equalsIgnoreCase("distance")) {
-				theAlgorithm = new DistanceAlgorithm();
+			} else if (tb1Algorithm.equalsIgnoreCase("bayesian")) {
+				theAlgorithm = new BayesianAlgorithm();
+			} else if (tb1Algorithm.equalsIgnoreCase("evolution")) {
+				theAlgorithm = new EvolutionAlgorithm();
+			} else if (tb1Algorithm.equalsIgnoreCase("joining")) {
+				theAlgorithm = new JoiningAlgorithm();
+			} else if (tb1Algorithm.equalsIgnoreCase("UPGMA")) {
+				theAlgorithm = new UPGMAAlgorithm();
 			} else if (tb1Algorithm.equalsIgnoreCase("likelihood")) {
 				theAlgorithm = new LikelihoodAlgorithm();
 			} else {

Modified: trunk/treebase-core/src/main/java/org/cipres/treebase/domain/study/Algorithm.java
===================================================================
--- trunk/treebase-core/src/main/java/org/cipres/treebase/domain/study/Algorithm.java	2010-02-19 21:50:27 UTC (rev 516)
+++ trunk/treebase-core/src/main/java/org/cipres/treebase/domain/study/Algorithm.java	2010-02-24 14:53:46 UTC (rev 517)
@@ -98,11 +98,15 @@
 	}
 	
 	/* constants returned by getAlgorithmType */
-	public static final String LikelihoodAlgorithm = "maximum likelihood";
-	public static final String DistanceAlgorithm = "distance";
+	public static final String LikelihoodAlgorithm = "maximum likelihood";	
 	public static final String OtherAlgorithm = "other algorithm";
 	public static final String ParsimonyAlgorithm = "parsimony";
+	public static final String BayesianAlgorithm ="bayesian inference";	
+	public static final String EvolutionAlgorithm ="minimum evolution";
+	public static final String JoiningAlgorithm ="neighbor joining";
+	public static final String UPGMAAlgorithm = "UPGMA";
 	
+	
 	/**
 	 * @return a string describing the algorithm type (for example, "parsimony")
 	 * @author mjd 20080723

Added: trunk/treebase-core/src/main/java/org/cipres/treebase/domain/study/BayesianAlgorithm.java
===================================================================
--- trunk/treebase-core/src/main/java/org/cipres/treebase/domain/study/BayesianAlgorithm.java	                        (rev 0)
+++ trunk/treebase-core/src/main/java/org/cipres/treebase/domain/study/BayesianAlgorithm.java	2010-02-24 14:53:46 UTC (rev 517)
@@ -0,0 +1,33 @@
+package org.cipres.treebase.domain.study;
+
+import javax.persistence.DiscriminatorValue;
+import javax.persistence.Entity;
+import javax.persistence.Transient;
+
+/**
+ * UPGMAAlgorithm.java
+ * 
+ * Created on Feb 23, 2010
+ * 
+ * @author Youjun Guo
+ * 
+ */
+@SuppressWarnings("serial")
+@Entity
+@DiscriminatorValue("B")
+
+public class BayesianAlgorithm extends Algorithm{
+	/**
+	 * Constructor.
+	 */
+	public BayesianAlgorithm() {
+		super();
+	}
+
+	@Override
+	@Transient
+	public String getAlgorithmType() {
+		return BayesianAlgorithm;
+	}
+
+}

Deleted: trunk/treebase-core/src/main/java/org/cipres/treebase/domain/study/DistanceAlgorithm.java
===================================================================
--- trunk/treebase-core/src/main/java/org/cipres/treebase/domain/study/DistanceAlgorithm.java	2010-02-19 21:50:27 UTC (rev 516)
+++ trunk/treebase-core/src/main/java/org/cipres/treebase/domain/study/DistanceAlgorithm.java	2010-02-24 14:53:46 UTC (rev 517)
@@ -1,33 +0,0 @@
-package org.cipres.treebase.domain.study;
-
-import javax.persistence.DiscriminatorValue;
-import javax.persistence.Entity;
-import javax.persistence.Transient;
-
-/**
- * DistanceAlgorithm.java
- * 
- * Created on Mar 13, 2006
- * 
- * @author Jin Ruan
- * 
- */
-@SuppressWarnings("serial")
-@Entity
-@DiscriminatorValue("D")
-public class DistanceAlgorithm extends Algorithm {
-
-	/**
-	 * Constructor.
-	 */
-	public DistanceAlgorithm() {
-		super();
-	}
-
-	@Override
-	@Transient
-	public String getAlgorithmType() {
-		return DistanceAlgorithm;
-	}
-
-}

Added: trunk/treebase-core/src/main/java/org/cipres/treebase/domain/study/EvolutionAlgorithm.java
===================================================================
--- trunk/treebase-core/src/main/java/org/cipres/treebase/domain/study/EvolutionAlgorithm.java	                        (rev 0)
+++ trunk/treebase-core/src/main/java/org/cipres/treebase/domain/study/EvolutionAlgorithm.java	2010-02-24 14:53:46 UTC (rev 517)
@@ -0,0 +1,33 @@
+package org.cipres.treebase.domain.study;
+
+import javax.persistence.DiscriminatorValue;
+import javax.persistence.Entity;
+import javax.persistence.Transient;
+
+/**
+ * UPGMAAlgorithm.java
+ * 
+ * Created on Feb 23, 2010
+ * 
+ * @author Youjun Guo
+ * 
+ */
+@SuppressWarnings("serial")
+@Entity
+@DiscriminatorValue("E")
+
+public class EvolutionAlgorithm extends Algorithm {
+	/**
+	 * Constructor.
+	 */
+	public EvolutionAlgorithm() {
+		super();
+	}
+
+	@Override
+	@Transient
+	public String getAlgorithmType() {
+		return EvolutionAlgorithm;
+	}
+
+}

Added: trunk/treebase-core/src/main/java/org/cipres/treebase/domain/study/JoiningAlgorithm.java
===================================================================
--- trunk/treebase-core/src/main/java/org/cipres/treebase/domain/study/JoiningAlgorithm.java	                        (rev 0)
+++ trunk/treebase-core/src/main/java/org/cipres/treebase/domain/study/JoiningAlgorithm.java	2010-02-24 14:53:46 UTC (rev 517)
@@ -0,0 +1,33 @@
+package org.cipres.treebase.domain.study;
+
+import javax.persistence.DiscriminatorValue;
+import javax.persistence.Entity;
+import javax.persistence.Transient;
+
+/**
+ * UPGMAAlgorithm.java
+ * 
+ * Created on Feb 23, 2010
+ * 
+ * @author Youjun Guo
+ * 
+ */
+@SuppressWarnings("serial")
+@Entity
+@DiscriminatorValue("J")
+
+public class JoiningAlgorithm extends Algorithm {
+	/**
+	 * Constructor.
+	 */
+	public JoiningAlgorithm() {
+		super();
+	}
+
+	@Override
+	@Transient
+	public String getAlgorithmType() {
+		return JoiningAlgorithm;
+	}
+
+}

Copied: trunk/treebase-core/src/main/java/org/cipres/treebase/domain/study/UPGMAAlgorithm.java (from rev 516, trunk/treebase-core/src/main/java/org/cipres/treebase/domain/study/DistanceAlgorithm.java)
===================================================================
--- trunk/treebase-core/src/main/java/org/cipres/treebase/domain/study/UPGMAAlgorithm.java	                        (rev 0)
+++ trunk/treebase-core/src/main/java/org/cipres/treebase/domain/study/UPGMAAlgorithm.java	2010-02-24 14:53:46 UTC (rev 517)
@@ -0,0 +1,33 @@
+package org.cipres.treebase.domain.study;
+
+import javax.persistence.DiscriminatorValue;
+import javax.persistence.Entity;
+import javax.persistence.Transient;
+
+/**
+ * UPGMAAlgorithm.java
+ * 
+ * Created on Feb 23, 2010
+ * 
+ * @author Youjun Guo
+ * 
+ */
+@SuppressWarnings("serial")
+@Entity
+@DiscriminatorValue("U")
+public class UPGMAAlgorithm extends Algorithm {
+
+	/**
+	 * Constructor.
+	 */
+	public UPGMAAlgorithm() {
+		super();
+	}
+
+	@Override
+	@Transient
+	public String getAlgorithmType() {
+		return UPGMAAlgorithm;
+	}
+
+}

Modified: trunk/treebase-core/src/main/resources/hibernate.cfg.xml
===================================================================
--- trunk/treebase-core/src/main/resources/hibernate.cfg.xml	2010-02-19 21:50:27 UTC (rev 516)
+++ trunk/treebase-core/src/main/resources/hibernate.cfg.xml	2010-02-24 14:53:46 UTC (rev 517)
@@ -104,8 +104,10 @@
 		<mapping class="org.cipres.treebase.domain.study.BookCitation" />
 		<mapping class="org.cipres.treebase.domain.study.Citation" />
 		<mapping class="org.cipres.treebase.domain.study.CitationStatus" />
-		<mapping class="org.cipres.treebase.domain.study.DistanceAlgorithm" />
+		<mapping class="org.cipres.treebase.domain.study.BayesianAlgorithm" />
+		<mapping class="org.cipres.treebase.domain.study.EvolutionAlgorithm" />		
 		<mapping class="org.cipres.treebase.domain.study.InBookCitation" />
+		<mapping class="org.cipres.treebase.domain.study.JoiningAlgorithm" />
 		<mapping class="org.cipres.treebase.domain.study.LikelihoodAlgorithm" />
 		<mapping class="org.cipres.treebase.domain.study.OtherAlgorithm" />
 		<mapping class="org.cipres.treebase.domain.study.ParsimonyAlgorithm" />
@@ -113,7 +115,8 @@
 		<mapping class="org.cipres.treebase.domain.study.Study" />
 		<mapping class="org.cipres.treebase.domain.study.StudyStatus" />
 		<mapping class="org.cipres.treebase.domain.study.Submission" />
-	
+		<mapping class="org.cipres.treebase.domain.study.UPGMAAlgorithm" />
+			
 		<mapping class="org.cipres.treebase.domain.taxon.LSIDTaxonLink" />
 		<mapping class="org.cipres.treebase.domain.taxon.SpecimenLabel" />
 		<mapping class="org.cipres.treebase.domain.taxon.Taxon" />

Modified: trunk/treebase-web/src/main/java/org/cipres/treebase/web/Constants.java
===================================================================
--- trunk/treebase-web/src/main/java/org/cipres/treebase/web/Constants.java	2010-02-19 21:50:27 UTC (rev 516)
+++ trunk/treebase-web/src/main/java/org/cipres/treebase/web/Constants.java	2010-02-24 14:53:46 UTC (rev 517)
@@ -77,9 +77,13 @@
 	public static final String ALGORITHM_TYPES = "algorithmtypes";
 	public static final String ALGORITHM_LIKELIHOOD = Algorithm.LikelihoodAlgorithm;
 	public static final String ALGORITHM_PARSIMONY = Algorithm.ParsimonyAlgorithm;
-	public static final String ALGORITHM_DISTANCE = Algorithm.DistanceAlgorithm;
+	//public static final String ALGORITHM_DISTANCE = Algorithm.DistanceAlgorithm;
 	public static final String ALGORITHM_OTHER = Algorithm.OtherAlgorithm;
-
+	public static final String ALGORITHM_Bayesian =Algorithm.BayesianAlgorithm;	
+	public static final String ALGORITHM_Evolution =Algorithm.EvolutionAlgorithm;
+	public static final String ALGORITHM_Joining =Algorithm.JoiningAlgorithm;
+	public static final String ALGORITHM_UPGMA = Algorithm.UPGMAAlgorithm;
+    	
 	public static final String SEARCH_COMMAND = "searchCommand";
 
 	public static final String MATRIX_KEY = "matrix";

Modified: trunk/treebase-web/src/main/java/org/cipres/treebase/web/controllers/DisplayAnalysisController.java
===================================================================
--- trunk/treebase-web/src/main/java/org/cipres/treebase/web/controllers/DisplayAnalysisController.java	2010-02-19 21:50:27 UTC (rev 516)
+++ trunk/treebase-web/src/main/java/org/cipres/treebase/web/controllers/DisplayAnalysisController.java	2010-02-24 14:53:46 UTC (rev 517)
@@ -19,13 +19,16 @@
 import org.cipres.treebase.domain.study.AnalyzedData;
 import org.cipres.treebase.domain.study.AnalyzedMatrix;
 import org.cipres.treebase.domain.study.AnalyzedTree;
-import org.cipres.treebase.domain.study.DistanceAlgorithm;
+import org.cipres.treebase.domain.study.BayesianAlgorithm;
+import org.cipres.treebase.domain.study.EvolutionAlgorithm;
+import org.cipres.treebase.domain.study.JoiningAlgorithm;
 import org.cipres.treebase.domain.study.LikelihoodAlgorithm;
 import org.cipres.treebase.domain.study.OtherAlgorithm;
 import org.cipres.treebase.domain.study.ParsimonyAlgorithm;
 import org.cipres.treebase.domain.study.Study;
 import org.cipres.treebase.domain.study.StudyService;
 import org.cipres.treebase.domain.study.Submission;
+import org.cipres.treebase.domain.study.UPGMAAlgorithm;
 import org.cipres.treebase.framework.ExecutionResult;
 import org.cipres.treebase.web.Constants;
 import org.cipres.treebase.web.model.AnalysisCommand;
@@ -128,11 +131,17 @@
 					algorithmType = Constants.ALGORITHM_LIKELIHOOD;
 				} else if (algorithm instanceof ParsimonyAlgorithm) {
 					algorithmType = Constants.ALGORITHM_PARSIMONY;
-				} else if (algorithm instanceof DistanceAlgorithm) {
-					algorithmType = Constants.ALGORITHM_DISTANCE;
-				} else if (algorithm instanceof OtherAlgorithm) {
+				}  else if (algorithm instanceof OtherAlgorithm) {
 					algorithmType = Constants.ALGORITHM_OTHER;
-				}
+				}else if (algorithm instanceof BayesianAlgorithm) {
+					algorithmType = Constants.ALGORITHM_Bayesian;
+				} else if (algorithm instanceof EvolutionAlgorithm) {
+					algorithmType = Constants.ALGORITHM_Evolution;
+				} else if (algorithm instanceof JoiningAlgorithm) {
+					algorithmType = Constants.ALGORITHM_Joining;
+			} else if (algorithm instanceof UPGMAAlgorithm) {
+				algorithmType = Constants.ALGORITHM_UPGMA;	
+			}
 				// add algorithm type for analysisStepCommand
 				analysisStepCommand.setAlgorithmType(algorithmType);
 

Modified: trunk/treebase-web/src/main/java/org/cipres/treebase/web/controllers/SearchSummaryController.java
===================================================================
--- trunk/treebase-web/src/main/java/org/cipres/treebase/web/controllers/SearchSummaryController.java	2010-02-19 21:50:27 UTC (rev 516)
+++ trunk/treebase-web/src/main/java/org/cipres/treebase/web/controllers/SearchSummaryController.java	2010-02-24 14:53:46 UTC (rev 517)
@@ -27,13 +27,16 @@
 import org.cipres.treebase.domain.study.AnalyzedData;
 import org.cipres.treebase.domain.study.AnalyzedMatrix;
 import org.cipres.treebase.domain.study.AnalyzedTree;
-import org.cipres.treebase.domain.study.DistanceAlgorithm;
+import org.cipres.treebase.domain.study.BayesianAlgorithm;
+import org.cipres.treebase.domain.study.EvolutionAlgorithm;
+import org.cipres.treebase.domain.study.JoiningAlgorithm;
 import org.cipres.treebase.domain.study.LikelihoodAlgorithm;
 import org.cipres.treebase.domain.study.OtherAlgorithm;
 import org.cipres.treebase.domain.study.ParsimonyAlgorithm;
 import org.cipres.treebase.domain.study.Study;
 import org.cipres.treebase.domain.study.StudyService;
 import org.cipres.treebase.domain.study.Submission;
+import org.cipres.treebase.domain.study.UPGMAAlgorithm;
 import org.cipres.treebase.domain.taxon.Taxon;
 import org.cipres.treebase.domain.taxon.TaxonLabel;
 import org.cipres.treebase.domain.taxon.TaxonLabelService;
@@ -197,11 +200,19 @@
 					algorithmType = Constants.ALGORITHM_LIKELIHOOD;
 				} else if (algorithm instanceof ParsimonyAlgorithm) {
 					algorithmType = Constants.ALGORITHM_PARSIMONY;
-				} else if (algorithm instanceof DistanceAlgorithm) {
-					algorithmType = Constants.ALGORITHM_DISTANCE;
-				} else if (algorithm instanceof OtherAlgorithm) {
+				}else if (algorithm instanceof OtherAlgorithm) {
 					algorithmType = Constants.ALGORITHM_OTHER;
-				}
+				}else if (algorithm instanceof BayesianAlgorithm) {
+					algorithmType = Constants.ALGORITHM_Bayesian;
+				} else if (algorithm instanceof EvolutionAlgorithm) {
+					algorithmType = Constants.ALGORITHM_Evolution;
+				} else if (algorithm instanceof JoiningAlgorithm) {
+					algorithmType = Constants.ALGORITHM_Joining;
+				} else if (algorithm instanceof UPGMAAlgorithm) {
+					algorithmType = Constants.ALGORITHM_UPGMA;	
+				} 
+				
+				
 				// add algorithm type for analysisStepCommand
 				analysisStepCommand.setAlgorithmType(algorithmType);
 
@@ -336,11 +347,18 @@
 					algorithmType = Constants.ALGORITHM_LIKELIHOOD;
 				} else if (algorithm instanceof ParsimonyAlgorithm) {
 					algorithmType = Constants.ALGORITHM_PARSIMONY;
-				} else if (algorithm instanceof DistanceAlgorithm) {
-					algorithmType = Constants.ALGORITHM_DISTANCE;
-				} else if (algorithm instanceof OtherAlgorithm) {
+				}else if (algorithm instanceof OtherAlgorithm) {
 					algorithmType = Constants.ALGORITHM_OTHER;
-				}
+				}else if (algorithm instanceof BayesianAlgorithm) {
+					algorithmType = Constants.ALGORITHM_Bayesian;
+				}else if (algorithm instanceof EvolutionAlgorithm) {
+					algorithmType = Constants.ALGORITHM_Evolution;
+				}else if (algorithm instanceof JoiningAlgorithm) {
+					algorithmType = Constants.ALGORITHM_Joining;
+				}else if (algorithm instanceof UPGMAAlgorithm) {
+					algorithmType = Constants.ALGORITHM_UPGMA;	
+				} 
+				
 				// add algorithm type for analysisStepCommand
 				analysisStepCommand.setAlgorithmType(algorithmType);
 

Modified: trunk/treebase-web/src/main/java/org/cipres/treebase/web/controllers/SummaryController.java
===================================================================
--- trunk/treebase-web/src/main/java/org/cipres/treebase/web/controllers/SummaryController.java	2010-02-19 21:50:27 UTC (rev 516)
+++ trunk/treebase-web/src/main/java/org/cipres/treebase/web/controllers/SummaryController.java	2010-02-24 14:53:46 UTC (rev 517)
@@ -26,8 +26,10 @@
 import org.cipres.treebase.domain.study.AnalyzedMatrix;
 import org.cipres.treebase.domain.study.AnalyzedTree;
 import org.cipres.treebase.domain.study.ArticleCitation;
+import org.cipres.treebase.domain.study.BayesianAlgorithm;
 import org.cipres.treebase.domain.study.Citation;
-import org.cipres.treebase.domain.study.DistanceAlgorithm;
+import org.cipres.treebase.domain.study.EvolutionAlgorithm;
+import org.cipres.treebase.domain.study.JoiningAlgorithm;
 import org.cipres.treebase.domain.study.LikelihoodAlgorithm;
 import org.cipres.treebase.domain.study.OtherAlgorithm;
 import org.cipres.treebase.domain.study.ParsimonyAlgorithm;
@@ -35,6 +37,7 @@
 import org.cipres.treebase.domain.study.StudyService;
 import org.cipres.treebase.domain.study.Submission;
 import org.cipres.treebase.domain.study.SubmissionService;
+import org.cipres.treebase.domain.study.UPGMAAlgorithm;
 import org.cipres.treebase.web.Constants;
 import org.cipres.treebase.web.model.AnalysisCommand;
 import org.cipres.treebase.web.model.AnalysisStepCommand;
@@ -212,11 +215,18 @@
 					algorithmType = Constants.ALGORITHM_LIKELIHOOD;
 				} else if (algorithm instanceof ParsimonyAlgorithm) {
 					algorithmType = Constants.ALGORITHM_PARSIMONY;
-				} else if (algorithm instanceof DistanceAlgorithm) {
-					algorithmType = Constants.ALGORITHM_DISTANCE;
 				} else if (algorithm instanceof OtherAlgorithm) {
 					algorithmType = Constants.ALGORITHM_OTHER;
-				}
+				}else if (algorithm instanceof BayesianAlgorithm) {
+					algorithmType = Constants.ALGORITHM_Bayesian;
+				} else if (algorithm instanceof EvolutionAlgorithm) {
+					algorithmType = Constants.ALGORITHM_Evolution;
+				} else if (algorithm instanceof JoiningAlgorithm) {
+					algorithmType = Constants.ALGORITHM_Joining;
+				} else if (algorithm instanceof UPGMAAlgorithm) {
+					algorithmType = Constants.ALGORITHM_UPGMA;	
+				} 
+				
 				// add algorithm type for analysisStepCommand
 				analysisStepCommand.setAlgorithmType(algorithmType);
 

Modified: trunk/treebase-web/src/main/java/org/cipres/treebase/web/listeners/StartupListener.java
===================================================================
--- trunk/treebase-web/src/main/java/org/cipres/treebase/web/listeners/StartupListener.java	2010-02-19 21:50:27 UTC (rev 516)
+++ trunk/treebase-web/src/main/java/org/cipres/treebase/web/listeners/StartupListener.java	2010-02-24 14:53:46 UTC (rev 517)
@@ -73,7 +73,10 @@
 		List algorithmTypes = new ArrayList();
 		algorithmTypes.add(Constants.ALGORITHM_LIKELIHOOD);
 		algorithmTypes.add(Constants.ALGORITHM_PARSIMONY);
-		algorithmTypes.add(Constants.ALGORITHM_DISTANCE);
+		algorithmTypes.add(Constants.ALGORITHM_Bayesian);
+		algorithmTypes.add(Constants.ALGORITHM_Evolution);
+		algorithmTypes.add(Constants.ALGORITHM_Joining);
+		algorithmTypes.add(Constants.ALGORITHM_UPGMA);
 		algorithmTypes.add(Constants.ALGORITHM_OTHER);
 		context.setAttribute(Constants.ALGORITHM_TYPES, algorithmTypes);
 

Modified: trunk/treebase-web/src/main/java/org/cipres/treebase/web/model/AnalysisStepCommand.java
===================================================================
--- trunk/treebase-web/src/main/java/org/cipres/treebase/web/model/AnalysisStepCommand.java	2010-02-19 21:50:27 UTC (rev 516)
+++ trunk/treebase-web/src/main/java/org/cipres/treebase/web/model/AnalysisStepCommand.java	2010-02-24 14:53:46 UTC (rev 517)
@@ -12,11 +12,14 @@
 import org.cipres.treebase.domain.matrix.PolyTCount;
 import org.cipres.treebase.domain.study.Algorithm;
 import org.cipres.treebase.domain.study.AnalysisStep;
-import org.cipres.treebase.domain.study.DistanceAlgorithm;
+import org.cipres.treebase.domain.study.BayesianAlgorithm;
+import org.cipres.treebase.domain.study.EvolutionAlgorithm;
+import org.cipres.treebase.domain.study.JoiningAlgorithm;
 import org.cipres.treebase.domain.study.LikelihoodAlgorithm;
 import org.cipres.treebase.domain.study.OtherAlgorithm;
 import org.cipres.treebase.domain.study.ParsimonyAlgorithm;
 import org.cipres.treebase.domain.study.Software;
+import org.cipres.treebase.domain.study.UPGMAAlgorithm;
 import org.cipres.treebase.web.Constants;
 
 /**
@@ -47,7 +50,10 @@
 	private List<String> mUniqueAlgorithmDescriptions;
 
 	public AnalysisStepCommand() {
-		algorithmMap.put(Constants.ALGORITHM_DISTANCE, new DistanceAlgorithm());
+		algorithmMap.put(Constants.ALGORITHM_Bayesian, new BayesianAlgorithm());
+		algorithmMap.put(Constants.ALGORITHM_Evolution, new EvolutionAlgorithm());
+		algorithmMap.put(Constants.ALGORITHM_Joining, new JoiningAlgorithm());
+		algorithmMap.put(Constants.ALGORITHM_UPGMA, new UPGMAAlgorithm());
 		algorithmMap.put(Constants.ALGORITHM_LIKELIHOOD, new LikelihoodAlgorithm());
 		algorithmMap.put(Constants.ALGORITHM_OTHER, new OtherAlgorithm());
 		// ParsimonyAlgorithm parsimonyAlgorithm = new ParsimonyAlgorithm();

Modified: trunk/treebase-web/src/main/java/org/cipres/treebase/web/util/ControllerUtil.java
===================================================================
--- trunk/treebase-web/src/main/java/org/cipres/treebase/web/util/ControllerUtil.java	2010-02-19 21:50:27 UTC (rev 516)
+++ trunk/treebase-web/src/main/java/org/cipres/treebase/web/util/ControllerUtil.java	2010-02-24 14:53:46 UTC (rev 517)
@@ -17,12 +17,15 @@
 import org.cipres.treebase.domain.study.Algorithm;
 import org.cipres.treebase.domain.study.Analysis;
 import org.cipres.treebase.domain.study.AnalysisStep;
-import org.cipres.treebase.domain.study.DistanceAlgorithm;
+import org.cipres.treebase.domain.study.BayesianAlgorithm;
+import org.cipres.treebase.domain.study.EvolutionAlgorithm;
+import org.cipres.treebase.domain.study.JoiningAlgorithm;
 import org.cipres.treebase.domain.study.LikelihoodAlgorithm;
 import org.cipres.treebase.domain.study.OtherAlgorithm;
 import org.cipres.treebase.domain.study.ParsimonyAlgorithm;
 import org.cipres.treebase.domain.study.Study;
 import org.cipres.treebase.domain.study.StudyService;
+import org.cipres.treebase.domain.study.UPGMAAlgorithm;
 import org.cipres.treebase.web.Constants;
 import org.cipres.treebase.web.exceptions.EmptyStudyException;
 
@@ -246,8 +249,14 @@
 			type = Constants.ALGORITHM_LIKELIHOOD;
 		} else if (algorithm instanceof ParsimonyAlgorithm) {
 			type = Constants.ALGORITHM_PARSIMONY;
-		} else if (algorithm instanceof DistanceAlgorithm) {
-			type = Constants.ALGORITHM_DISTANCE;
+		} else if (algorithm instanceof BayesianAlgorithm) {
+			type = Constants.ALGORITHM_Bayesian;
+		} else if (algorithm instanceof EvolutionAlgorithm) {
+			type = Constants.ALGORITHM_Evolution;
+		} else if (algorithm instanceof JoiningAlgorithm) {
+			type = Constants.ALGORITHM_Joining;
+		} else if (algorithm instanceof UPGMAAlgorithm) {
+			type = Constants.ALGORITHM_UPGMA;	
 		} else if (algorithm instanceof OtherAlgorithm) {
 			type = Constants.ALGORITHM_OTHER;
 		}

Modified: trunk/treebase-web/src/main/webapp/WEB-INF/pages/submissionSummaryView.jsp
===================================================================
--- trunk/treebase-web/src/main/webapp/WEB-INF/pages/submissionSummaryView.jsp	2010-02-19 21:50:27 UTC (rev 516)
+++ trunk/treebase-web/src/main/webapp/WEB-INF/pages/submissionSummaryView.jsp	2010-02-24 14:53:46 UTC (rev 517)
@@ -11,16 +11,31 @@
 </legend>
 Submission: <c:out value="${submissionNumber}"/>, <c:out value="${studyStatus}"/>, <a href="/treebase-web/admin/changeStudyStatus.html"> Update Status</a><br/>
 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Submission initiated:&nbsp;&nbsp;<c:out value="${initiatedDate}"/><br/>
-<a href='mailto:${submission.submitter.emailAddressString}?subject=TreeBASE Submission S${submission.id}'>
-	<img class="iconButton" alt="mail" src="<fmt:message key="icons.email"/>" />
-	Contact Submitter
-</a>
-<br/>
-<a href="<c:out value="${submission.study.phyloWSPath.purl}"/>?x-access-code=<c:out value="${submission.study.namespacedGUID.hashedIDString}"/>&format=html">
-	<img class="iconButton" alt="link" src="<fmt:message key="icons.weblink"/>" />
-	Reviewer access URL: right-click and copy
-</a>
 
+<%if(request.isUserInRole("Admin") || request.isUserInRole("Associate Editor")){%>
+	<a href='mailto:${submission.submitter.emailAddressString}?subject=TreeBASE Submission S${submission.id}'>
+		<img class="iconButton" alt="mail" src="<fmt:message key="icons.email"/>" />
+		Contact Submitter
+	</a>
+	<br/>
+	<a href="<c:out value="${submission.study.phyloWSPath.purl}"/>?x-access-code=<c:out value="${submission.study.namespacedGUID.hashedIDString}"/>&format=html">
+		<img class="iconButton" alt="link" src="<fmt:message key="icons.weblink"/>" />
+		Reviewer access URL: right-click and copy
+	</a>
+<% } %>
+
+<%if(request.isUserInRole("User") ){%>
+	<a href='mailto:he...@tr...?subject=TreeBASE Submission S${submission.id}'>
+		<img class="iconButton" alt="mail" src="<fmt:message key="icons.email"/>" />
+		Contact TreeBASE Help 
+	</a>
+	<br/>
+	<a href="<c:out value="http://purl.org/phylo/treebase/phylows/study/TB2:S"/><c:out value="${submission.study.id}"/>">
+		<img class="iconButton" alt="link" src="<fmt:message key="icons.weblink"/>" />
+		Study Accession URL: right-click and copy
+	</a>
+<% } %>
+
 <c:if test="${not empty citationsummary.study}">
 	<c:if test="${not empty citationsummary.study.name}">
 		&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Study name:&nbsp;&nbsp;<c:out value="${citationsummary.study.name}"/>


This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.

[Treebase-guts] [ treebase-Bugs-2949882 ] List of default algorithms needs updating

[SourceForge.net <no...@so...>]

Bugs item #2949882, was opened at 2010-02-11 13:57
Message generated for change (Settings changed) made by youjun
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=1126676&aid=2949882&group_id=248804

Please note that this message will contain a full copy of the comment thread,
including the initial issue submission, for this request,
not just the latest update.
Category: ui
Group: None
>Status: Closed
Priority: 9
Private: No
Submitted By: William Piel (sfrgpiel)
Assigned to: youjun guo (youjun)
Summary: List of default algorithms needs updating

Initial Comment:
When a submitter creates an analysis step, e.g.:

http://treebasedb-dev.nescent.org/treebase-web/user/analysisStepForm.html?analysis_id=4922

and then clicks the "Edit analysis step details" pencil button, he is able to pick from a popup list of default choices for the "Algorithm".

The current choices are:

maximum likelihood
parsimony
distance
other algorithm

These choices should be changed to the following:

bayesian inference
maximum likelihood
minimum evolution
neighbor joining
parsimony
UPGMA
other algorithm

I could not find a place in the database to modify this list, so I'm assuming that this is hard-coded somewhere.



----------------------------------------------------------------------

You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=1126676&aid=2949882&group_id=248804

[Treebase-guts] [ treebase-Bugs-2950258 ] Row Segment Spreasheet covered by menu

[SourceForge.net <no...@so...>]

Bugs item #2950258, was opened at 2010-02-11 22:01
Message generated for change (Settings changed) made by youjun
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=1126676&aid=2950258&group_id=248804

Please note that this message will contain a full copy of the comment thread,
including the initial issue submission, for this request,
not just the latest update.
Category: ui
Group: None
Status: Open
Priority: 9
Private: No
Submitted By: William Piel (sfrgpiel)
>Assigned to: youjun guo (youjun)
Summary: Row Segment Spreasheet covered by menu

Initial Comment:
Two things: 

(1) the row segment upload spreadsheet is covered by the menu div which prevents access to popups. 
(2) the row segment upload spreadsheet is missing the little help icon. 

For (1): Using Safari, when I upload an edited Row Segment Template, I first download a blank template from here:

http://treebasedb-dev.nescent.org/treebase-web/user/uploadRowSegmentData.html

And then upload a finished spreadsheet here:

http://treebasedb-dev.nescent.org/treebase-web/user/rowSegmentDataTable.html

The result (attached image) shows a spreadsheet where the menu system is hovering over (and blocking access to) several important popups. This can be avoided if the window width is decreased below 944 pixels, but it's not at all obvious to the user that shrinking the window is the solution to the problem. Instead, I think it would be better to hide the menu for this page (or shift the spreadsheet down so that is is not covered by the menu). 

Then for (2) we just need to add a help icon so that I can enter instructions for the user on the row segment upload spreadsheet.


----------------------------------------------------------------------

You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=1126676&aid=2950258&group_id=248804

[Treebase-guts] [ treebase-Bugs-2947955 ] Submission Summary Info

[SourceForge.net <no...@so...>]

Bugs item #2947955, was opened at 2010-02-08 12:16
Message generated for change (Settings changed) made by youjun
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=1126676&aid=2947955&group_id=248804

Please note that this message will contain a full copy of the comment thread,
including the initial issue submission, for this request,
not just the latest update.
Category: ui
Group: None
>Status: Closed
Priority: 9
Private: No
Submitted By: William Piel (sfrgpiel)
Assigned to: youjun guo (youjun)
Summary: Submission Summary Info

Initial Comment:
When the user enter a submission, s/he sees a submission summary info page, like so:

http://treebasedb-dev.nescent.org/treebase-web/user/summary.html?id=1

The information presented is incomplete. In addition to a reviewer-access URL, we want the user to see the Study Accession URL; and we want the user to have a "Contact TreeBASE Help" link. This email link needs to include the submission ID in the email subject header. 

Ideally, the Study Accession URL only shows up when the submission is "ready" or "published". Ideally, the Reviewer-access URL does not show up when the submission is "published". But these status-sensitive features are not strictly critical, and it's fine if everything is always displayed.  Another non-critical feature is that in addition to a "submission initiated" date, it would be nice if there also was a "last modified" date. 

Attached, please find an image showing what this might look like. 



----------------------------------------------------------------------

You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=1126676&aid=2947955&group_id=248804

[Treebase-guts] [ treebase-Bugs-2953212 ] Hang-up in Search Result tabs

[SourceForge.net <no...@so...>]

Bugs item #2953212, was opened at 2010-02-16 19:48
Message generated for change (Settings changed) made by youjun
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=1126676&aid=2953212&group_id=248804

Please note that this message will contain a full copy of the comment thread,
including the initial issue submission, for this request,
not just the latest update.
Category: data
Group: None
Status: Open
Priority: 9
Private: No
Submitted By: Vladimir Gapeyev (vgapeyev)
>Assigned to: youjun guo (youjun)
Summary: Hang-up in Search Result tabs

Initial Comment:

After Jon and I addressed #2945410 [Proxy Error for taxon->tree search] by increasing Apache timeout to 10 min and #2945391 [New Indices Needed], a new issue transpired on search results: some "click paths" through the result tabs lead to a hang-up.  

To reproduce: 
- Search for "oak", clicking "All Text".
- After this finishes, click on result tabs, avoiding the "Taxa" tab.  I.e., keep clicking on "Matrices", "Trees", and "Tree topologies", in any order, as long as you wish.  They all should be finishing after a short delay. 
- Now, click on "Taxa".  It should finish fairly soon. 
- Now, click on "Matrices". This hangs up forever. 

A bonus observation: If (prior to "Taxa") you continue clicking on "Matrices" several times in a row, it appears you get a new result list each time.  This looks like a bug, unless it is a clever feature.

We spent a couple hours trying to figure out what is going on.  No major revelations, but here are a few observations: 

- When the hang up happens, there is a query process stuck on postgres that does not finish even for an hour. 

- The query process takes about 33% CPU, so something active might be going on during the hang-up. Also, responsiveness of the server to concurrent sessions degrades. 

- After looking up tips on the internets, we tried increasing size of the Tomcat's JDBC connections pool, with no effect. 

- Following a hunch that the switch from the c3p0 connection pool manager to that of Tomcat might have contributed, we rebuilt the WAR from SVN #422, i.e. right before the switch to JNDI.  That one hangs up, too. 

My best guess at the moment is that there is some kind of interaction     between queries on this page that leads to a "busy" deadlock.

----------------------------------------------------------------------

You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=1126676&aid=2953212&group_id=248804

[Treebase-guts] [ treebase-Bugs-2949882 ] List of default algorithms needs updating

[SourceForge.net <no...@so...>]

Bugs item #2949882, was opened at 2010-02-11 13:57
Message generated for change (Settings changed) made by youjun
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=1126676&aid=2949882&group_id=248804

Please note that this message will contain a full copy of the comment thread,
including the initial issue submission, for this request,
not just the latest update.
Category: ui
Group: None
Status: Open
Priority: 9
Private: No
Submitted By: William Piel (sfrgpiel)
>Assigned to: youjun guo (youjun)
Summary: List of default algorithms needs updating

Initial Comment:
When a submitter creates an analysis step, e.g.:

http://treebasedb-dev.nescent.org/treebase-web/user/analysisStepForm.html?analysis_id=4922

and then clicks the "Edit analysis step details" pencil button, he is able to pick from a popup list of default choices for the "Algorithm".

The current choices are:

maximum likelihood
parsimony
distance
other algorithm

These choices should be changed to the following:

bayesian inference
maximum likelihood
minimum evolution
neighbor joining
parsimony
UPGMA
other algorithm

I could not find a place in the database to modify this list, so I'm assuming that this is hard-coded somewhere.



----------------------------------------------------------------------

You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=1126676&aid=2949882&group_id=248804

[Treebase-guts] [ treebase-Bugs-2947955 ] Submission Summary Info

[SourceForge.net <no...@so...>]

Bugs item #2947955, was opened at 2010-02-08 12:16
Message generated for change (Settings changed) made by youjun
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=1126676&aid=2947955&group_id=248804

Please note that this message will contain a full copy of the comment thread,
including the initial issue submission, for this request,
not just the latest update.
Category: ui
Group: None
Status: Open
Priority: 9
Private: No
Submitted By: William Piel (sfrgpiel)
>Assigned to: youjun guo (youjun)
Summary: Submission Summary Info

Initial Comment:
When the user enter a submission, s/he sees a submission summary info page, like so:

http://treebasedb-dev.nescent.org/treebase-web/user/summary.html?id=1

The information presented is incomplete. In addition to a reviewer-access URL, we want the user to see the Study Accession URL; and we want the user to have a "Contact TreeBASE Help" link. This email link needs to include the submission ID in the email subject header. 

Ideally, the Study Accession URL only shows up when the submission is "ready" or "published". Ideally, the Reviewer-access URL does not show up when the submission is "published". But these status-sensitive features are not strictly critical, and it's fine if everything is always displayed.  Another non-critical feature is that in addition to a "submission initiated" date, it would be nice if there also was a "last modified" date. 

Attached, please find an image showing what this might look like. 



----------------------------------------------------------------------

You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=1126676&aid=2947955&group_id=248804

[Treebase-guts] SF.net SVN: treebase:[516] trunk/treebase-core/src/main/java/org/cipres/ treebase/dao/study/SubmissionDAO.java

[<yo...@us...>]

Revision: 516
          http://treebase.svn.sourceforge.net/treebase/?rev=516&view=rev
Author:   youjun
Date:     2010-02-19 21:50:27 +0000 (Fri, 19 Feb 2010)

Log Message:
-----------
update hql for last commit

Modified Paths:
--------------
    trunk/treebase-core/src/main/java/org/cipres/treebase/dao/study/SubmissionDAO.java

Modified: trunk/treebase-core/src/main/java/org/cipres/treebase/dao/study/SubmissionDAO.java
===================================================================
--- trunk/treebase-core/src/main/java/org/cipres/treebase/dao/study/SubmissionDAO.java	2010-02-19 20:31:56 UTC (rev 515)
+++ trunk/treebase-core/src/main/java/org/cipres/treebase/dao/study/SubmissionDAO.java	2010-02-19 21:50:27 UTC (rev 516)
@@ -198,7 +198,7 @@
 		Submission returnVal = null;
 			Query q = getSession()
 				.createQuery(
-					"select sub from Submission as sub where sub.id = :mID");
+					"select sub from Submission as sub where sub.study.id = :mID");
 
 			q.setLong("mID", pID);
 			Object o = q.uniqueResult();


This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.

[Treebase-guts] SF.net SVN: treebase:[515] trunk

[<yo...@us...>]

Revision: 515
          http://treebase.svn.sourceforge.net/treebase/?rev=515&view=rev
Author:   youjun
Date:     2010-02-19 20:31:56 +0000 (Fri, 19 Feb 2010)

Log Message:
-----------
add search by studyID option @ /admin/searchbysubmissionID

Modified Paths:
--------------
    trunk/treebase-core/src/main/java/org/cipres/treebase/dao/study/SubmissionDAO.java
    trunk/treebase-core/src/main/java/org/cipres/treebase/domain/study/SubmissionHome.java
    trunk/treebase-web/src/main/java/org/cipres/treebase/web/controllers/SearchBySubmissionIDController.java
    trunk/treebase-web/src/main/webapp/WEB-INF/pages/searchBySubmissionID.jsp

Modified: trunk/treebase-core/src/main/java/org/cipres/treebase/dao/study/SubmissionDAO.java
===================================================================
--- trunk/treebase-core/src/main/java/org/cipres/treebase/dao/study/SubmissionDAO.java	2010-02-19 20:13:59 UTC (rev 514)
+++ trunk/treebase-core/src/main/java/org/cipres/treebase/dao/study/SubmissionDAO.java	2010-02-19 20:31:56 UTC (rev 515)
@@ -193,4 +193,19 @@
 		return q.list();
 	}
 
+	public Submission findByStudyID(Long pID) {
+		// TODO Auto-generated method stub
+		Submission returnVal = null;
+			Query q = getSession()
+				.createQuery(
+					"select sub from Submission as sub where sub.id = :mID");
+
+			q.setLong("mID", pID);
+			Object o = q.uniqueResult();
+
+			returnVal = (Submission) o;
+		
+		return returnVal;
+	}
+
 }

Modified: trunk/treebase-core/src/main/java/org/cipres/treebase/domain/study/SubmissionHome.java
===================================================================
--- trunk/treebase-core/src/main/java/org/cipres/treebase/domain/study/SubmissionHome.java	2010-02-19 20:13:59 UTC (rev 514)
+++ trunk/treebase-core/src/main/java/org/cipres/treebase/domain/study/SubmissionHome.java	2010-02-19 20:31:56 UTC (rev 515)
@@ -36,7 +36,7 @@
 	 * @return Submission
 	 */
 	Submission findByStudyAccessionNumber(String pAccNumber);
-
+	Submission findByStudyID(Long pID);
 	/**
 	 * Find by the associated matrix. Returns null if no match is found.
 	 * 

Modified: trunk/treebase-web/src/main/java/org/cipres/treebase/web/controllers/SearchBySubmissionIDController.java
===================================================================
--- trunk/treebase-web/src/main/java/org/cipres/treebase/web/controllers/SearchBySubmissionIDController.java	2010-02-19 20:13:59 UTC (rev 514)
+++ trunk/treebase-web/src/main/java/org/cipres/treebase/web/controllers/SearchBySubmissionIDController.java	2010-02-19 20:31:56 UTC (rev 515)
@@ -70,9 +70,19 @@
 			}
 			sub = getSubmissionHome().findByStudyAccessionNumber(studyAccession);
 		}
-		else {
+		else if( identifierType.equals("TB2") ){
 			sub = getSubmissionHome().findBySubmissionNumber(studyAccession);
 		}
+		else{
+			Long pID;
+			try{
+				pID=Long.parseLong(studyAccession);
+				sub = getSubmissionHome().findByStudyID(pID);
+			}catch(NumberFormatException nfe){
+				sub=null;
+			}
+				
+		}
 		
 		if (sub == null) {
 			return setAttributeAndShowForm(request, response, bindExp, "errors", "Submission accession: '"

Modified: trunk/treebase-web/src/main/webapp/WEB-INF/pages/searchBySubmissionID.jsp
===================================================================
--- trunk/treebase-web/src/main/webapp/WEB-INF/pages/searchBySubmissionID.jsp	2010-02-19 20:13:59 UTC (rev 514)
+++ trunk/treebase-web/src/main/webapp/WEB-INF/pages/searchBySubmissionID.jsp	2010-02-19 20:31:56 UTC (rev 515)
@@ -17,7 +17,8 @@
         <th><fmt:message key="submission.accession"/>:</th>
         <td>  	
         	<input type="radio" name="identifierType" value="TB2" checked="checked"/>TreeBASE2 Id   
-        	<input type="radio" name="identifierType" value="TB1"/>Legacy accession        	
+        	<input type="radio" name="identifierType" value="TB1"/>Legacy accession 
+        	<input type="radio" name="identifierType" value="TB0"/>TreeBase2 Study ID        	       	
         	<input type="text" name="submissionaccession"  maxlength = "25"/>       
         </td>
     </tr>


This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.

[Treebase-guts] [ treebase-Bugs-2947925 ] Admin needs to search by study, submission, and legacy ids

[SourceForge.net <no...@so...>]

Bugs item #2947925, was opened at 2010-02-08 11:44
Message generated for change (Settings changed) made by youjun
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=1126676&aid=2947925&group_id=248804

Please note that this message will contain a full copy of the comment thread,
including the initial issue submission, for this request,
not just the latest update.
Category: ui
Group: None
>Status: Closed
Priority: 9
Private: No
Submitted By: William Piel (sfrgpiel)
Assigned to: youjun guo (youjun)
Summary: Admin needs to search by study, submission, and legacy ids

Initial Comment:
http://treebasedb-dev.nescent.org/treebase-web/admin/searchBySubmissionID.html

In the Search Submission page, which is used by editors and admin users, currently the "TreeBASE2 Id" actually searches the submission_id.  We need to make it so that the Admin person can search on (1) TreeBASE2 Submission ID, (2) TreeBASE2 Study ID, and (3) TreeBASE 1 Legacy ID. 


----------------------------------------------------------------------

You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=1126676&aid=2947925&group_id=248804

[Treebase-guts] SF.net SVN: treebase:[514] trunk/treebase-core/src/main/java/org/cipres/ treebase/util/BulkUpload.java

[<vga...@us...>]

Revision: 514
          http://treebase.svn.sourceforge.net/treebase/?rev=514&view=rev
Author:   vgapeyev
Date:     2010-02-19 20:13:59 +0000 (Fri, 19 Feb 2010)

Log Message:
-----------
More granular DB commits during data uploading.

Modified Paths:
--------------
    trunk/treebase-core/src/main/java/org/cipres/treebase/util/BulkUpload.java

Modified: trunk/treebase-core/src/main/java/org/cipres/treebase/util/BulkUpload.java
===================================================================
--- trunk/treebase-core/src/main/java/org/cipres/treebase/util/BulkUpload.java	2010-02-17 22:54:26 UTC (rev 513)
+++ trunk/treebase-core/src/main/java/org/cipres/treebase/util/BulkUpload.java	2010-02-19 20:13:59 UTC (rev 514)
@@ -138,7 +138,8 @@
 		User submitter = null; // No submitter
 
 		//Study s = bulkUpload.addFileSimple(new File(dirName, fn), submitter, session);
-		Study s = addFilesSimple(undoneFiles, submitter, null);
+		//Study s = addFilesSimple(undoneFiles, submitter, null);     //VG 2010-02-18 -- see comment at the method
+		Study s = addFilesOneByOne(undoneFiles, submitter);
 		if (s == null) {
 			log("conversion of files failed");
 		} else {
@@ -161,6 +162,7 @@
 		ContextManager.getTaxonLabelHome().flush();
 	}
 	
+	//VG this is not used: the only use is commented out
 	Study addFileSimple(File f, User aUser, Session session) throws IOException {
 		List<File> theFile = new LinkedList<File> ();
 		theFile.add(f);
@@ -168,6 +170,11 @@
 		return addFilesSimple(theFile, aUser, session);
 	}
 	
+	//VG 2010-02-18  stopped using this method 
+	//This method commits matrices, but not trees or the files. 
+	//Uncommenting the commitTransaction() call does not help much, 
+	//since then all trees and files are bundled into a huge transaction, 
+	//where a single failure scraps uploads of all prior trees and files (but not matrices!)
 	public Study addFilesSimple(List<File> files, User aUser, Session session) throws IOException { 
 		//Transaction t = session.beginTransaction();
 		Submission sub = getPseudoSubmission();
@@ -182,6 +189,27 @@
 		return study;
 	}
 	
+	//VG 2010-02-18 This fixes the deficiency of addFilesSimple by committing each file upload. 
+	public Study addFilesOneByOne(List<File> files, User aUser) throws IOException {
+		Submission sub = getPseudoSubmission();
+		Study study = sub.getStudy();		
+		log("submission ID = " + sub.getId() + " study ID " + study.getId());
+
+		int countMatrices = 0, countTrees = 0;
+		for (File f : files) {
+			beginTransaction();
+			List<File> theFile = new LinkedList<File> ();
+			theFile.add(f);
+			UploadFileResult uploadResult = ContextManager.getSubmissionService().addNexusFilesJDBC(sub, theFile, null);
+			commitTransaction();
+			log("added " + uploadResult.getMatrixCount() + " matrices and " + uploadResult.getTreeCount() + " trees.");
+			countMatrices += uploadResult.getMatrixCount(); 
+			countTrees += uploadResult.getTreeCount(); 
+		}
+		log("Added in his batch: " + countMatrices + " matrices, " + countTrees + " trees."); 
+		return study;		
+	}
+	
 	private static Submission getPseudoSubmission() {
 		return PseudoSubmission.getPseudoSubmission("TEST");
 	}


This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.

[Treebase-guts] [ treebase-Bugs-2947925 ] Admin needs to search by study, submission, and legacy ids

[SourceForge.net <no...@so...>]

Bugs item #2947925, was opened at 2010-02-08 11:44
Message generated for change (Settings changed) made by youjun
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=1126676&aid=2947925&group_id=248804

Please note that this message will contain a full copy of the comment thread,
including the initial issue submission, for this request,
not just the latest update.
Category: ui
Group: None
Status: Open
Priority: 9
Private: No
Submitted By: William Piel (sfrgpiel)
>Assigned to: youjun guo (youjun)
Summary: Admin needs to search by study, submission, and legacy ids

Initial Comment:
http://treebasedb-dev.nescent.org/treebase-web/admin/searchBySubmissionID.html

In the Search Submission page, which is used by editors and admin users, currently the "TreeBASE2 Id" actually searches the submission_id.  We need to make it so that the Admin person can search on (1) TreeBASE2 Submission ID, (2) TreeBASE2 Study ID, and (3) TreeBASE 1 Legacy ID. 


----------------------------------------------------------------------

You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=1126676&aid=2947925&group_id=248804

[Treebase-guts] [ treebase-Bugs-2954481 ] localhost:8080 hyperlinks still showing up on matrices tab

[SourceForge.net <no...@so...>]

Bugs item #2954481, was opened at 2010-02-18 17:10
Message generated for change (Tracker Item Submitted) made by jonauman
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=1126676&aid=2954481&group_id=248804

Please note that this message will contain a full copy of the comment thread,
including the initial issue submission, for this request,
not just the latest update.
Category: ui
Group: None
Status: Open
Priority: 5
Private: No
Submitted By: Jon Auman (jonauman)
Assigned to: Nobody/Anonymous (nobody)
Summary: localhost:8080 hyperlinks still showing up on matrices tab

Initial Comment:
For example:
http://treebase.nescent.org/treebase-web/search/study/matrices.html?id=1000

Generates
* http://localhost:8080/treebase-web/phylows/matrix/TB2:M553?format=nexml
* http://localhost:8080/treebase-web/phylows/matrix/TB2:M553?format=rdf
*http://localhost:8080/treebase-web/phylows/matrix/TB2:M553?format=html


----------------------------------------------------------------------

You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=1126676&aid=2954481&group_id=248804

[Treebase-guts] SF.net SVN: treebase:[513] trunk/treebase-core/db

[<vga...@us...>]

Revision: 513
          http://treebase.svn.sourceforge.net/treebase/?rev=513&view=rev
Author:   vgapeyev
Date:     2010-02-17 22:54:26 +0000 (Wed, 17 Feb 2010)

Log Message:
-----------
Misc, including
  - a cumulative script for creating a fresh DB (init_db_uptodate.pg)
  - 3 pre-defined user records in a fresh DB

Modified Paths:
--------------
    trunk/treebase-core/db/README.txt
    trunk/treebase-core/db/TODO.txt
    trunk/treebase-core/db/snapshots/0000_DATA_before_patches_start.sql

Added Paths:
-----------
    trunk/treebase-core/db/init_db_uptodate.pg

Modified: trunk/treebase-core/db/README.txt
===================================================================
--- trunk/treebase-core/db/README.txt	2010-02-17 22:50:46 UTC (rev 512)
+++ trunk/treebase-core/db/README.txt	2010-02-17 22:54:26 UTC (rev 513)
@@ -49,7 +49,9 @@
 was only applied to the development DB instance.  When the patch with an error was already 
 applied to production databases, it could be more prudent to develop a new, error-fixing patch. 
 
+When adding a patch, add a line into init_db_uptodate.pg as well. 
 
+
 Applying a patch
 ----------------
 
@@ -79,21 +81,25 @@
 
 Data snapshot is trickier.  The best bet right now is probably by hand-modifying the previous data snapshot. 
 
+After creating a new snapshot, update init_db_uptodate.pg: change the names of the snapshot scripts
+and remove all \i commands for the patch scripts. 
 
+
 Creating a fresh DB
 ------------------
 
+Use the init_db_uptodate.pg script, which should create the DB from the most recent snapshot 
+and apply all the subsequent patches: 
+
 Use the most recent snapshot: 
 
-psql -d yourdb -U yourusername -f nnn_SCHEMA_xxx.sql
-psql -d yourdb -U yourusername -f nnn_DATA_xxx.sql
+psql -d yourdb -U yourusername -f init_db_uptodate.pg
 
 OR 
 
 psql -d yourdb -U yourusername
 yourdb=> begin transaction;
-yourdb=> \i nnn_SCHEMA_xxx.sql
-yourdb=> \i nnn_DATA_xxx.sql
+yourdb=> \i init_db_uptodate.pg
 yourdb=> commit;  
 
 

Modified: trunk/treebase-core/db/TODO.txt
===================================================================
--- trunk/treebase-core/db/TODO.txt	2010-02-17 22:50:46 UTC (rev 512)
+++ trunk/treebase-core/db/TODO.txt	2010-02-17 22:54:26 UTC (rev 513)
@@ -24,6 +24,8 @@
 Pending data cleanups (on staging):
 ===================================
 
+[Could be worthwhile to do PRIOR to Dec 2009 and Feb 2010 migrations.]
+
 * Remove junk, particularly that created by tests
 
 * Known junk: Submission 22 and its related records
@@ -36,7 +38,7 @@
        (study 22 and submission 22 happen to be the same)." 
      VG: these IDs are from end-of-Jan treebasedev; the current treebasestage may have fewer.   
 
-* Replace "owner" nulls by the special "migration" owner (see Bill's message of around 2010-02-02)
+* Replace "owner" nulls (in submission.user_id?) by the special "migration" owner (see Bill's message of around 2010-02-02)
     "Also because we have reverted to an older instance of the data, the migrated records contain have NULL in the user_id of the submission table. This causes null pointer errors for the unit tests. To fix the problem, I did the following:
      1. I created a user with username "migration" and email address "mig...@tr...". This is the dummy "user" who will now "own" all migrated submissions that lack an owner.
      2. I got the user_id for the "migration" user, and it happened to be 9955
@@ -48,5 +50,6 @@
 ============================
 
 * Synch hibernate_sequence and {sub_matrix,sub_treebaseblock}.collection_id
+  [No need if prod is restored from a dump of stage made after ~2010-02-13]
 
     
\ No newline at end of file

Added: trunk/treebase-core/db/init_db_uptodate.pg
===================================================================
--- trunk/treebase-core/db/init_db_uptodate.pg	                        (rev 0)
+++ trunk/treebase-core/db/init_db_uptodate.pg	2010-02-17 22:54:26 UTC (rev 513)
@@ -0,0 +1,12 @@
+-- Populates an empty DB with tables and dictionary data
+-- corresponding to the most recent snapshot and all subsequent patches. 
+
+\i snapshots/0000_SCHEMA_before_patches_start.sql
+\i snapshots/0000_DATA_before_patches_start.sql
+
+\i patches/0001_constraint-names.sql
+\i patches/0002_drop-geospot_id_sequence.sql
+\i patches/0003_rename-stepmatrixelement-pk.sql
+\i patches/0004_create-indexes.sql
+
+

Modified: trunk/treebase-core/db/snapshots/0000_DATA_before_patches_start.sql
===================================================================
--- trunk/treebase-core/db/snapshots/0000_DATA_before_patches_start.sql	2010-02-17 22:50:46 UTC (rev 512)
+++ trunk/treebase-core/db/snapshots/0000_DATA_before_patches_start.sql	2010-02-17 22:54:26 UTC (rev 513)
@@ -7,8 +7,9 @@
 --  - Converted commented-out DB2 RESTART statements on PK columns to working Postgres RESTART statements on sequences.  
 --  - Added (6, 1, 'Retracted') to citationStatus 
 --  - Removed initial data from tables Person, User, Submission, Study
+--  VG 2010-02-27: initial entries into "user" and "person" tables
+ 
 
-
 insert into citationStatus(citationstatus_id, version, description) values 
 (1, 1, 'In Prep'),
 (2, 1, 'In Review'),
@@ -228,6 +229,22 @@
 alter sequence userrole_id_sequence restart with 4;
 
 
+insert into person(person_id, version, lastname, firstname, mname, phone, email) 
+    values
+    (1, 0, 'Tb2', 'Admin',  '', '', 'tb2...@no...'),
+    (2, 0, 'Tb2', 'User',   '', '', 'tb...@no...'),
+    (3, 0, 'Tb2', 'Editor', '', '', 'tb2...@no...');
+alter sequence person_id_sequence restart with 100;
+
+insert into "user"(user_id, version, username, password, userrole_id, person_id) 
+    VALUES
+    (1,  0, 'tb2admin',  '*', 1, 1),
+    (2,  0, 'tb2user',   '*', 2, 2),
+    (3,  0, 'tb2editor', '*', 3, 3);
+alter sequence user_id_sequence restart with 100;
+
+
+
 insert into studyStatus(studystatus_id, version, description) values 
 (1, 1, 'In Progress'),
 (2, 1, 'Ready'),


This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.

[Treebase-guts] SF.net SVN: treebase:[512] trunk/treebase-core/src/main/java/org/cipres/ treebase/dao/jdbc/DiscreteMatrixElementJDBC.java

[<vga...@us...>]

Revision: 512
          http://treebase.svn.sourceforge.net/treebase/?rev=512&view=rev
Author:   vgapeyev
Date:     2010-02-17 22:50:46 +0000 (Wed, 17 Feb 2010)

Log Message:
-----------
Print out a counter before, not after it is reset. 

Modified Paths:
--------------
    trunk/treebase-core/src/main/java/org/cipres/treebase/dao/jdbc/DiscreteMatrixElementJDBC.java

Modified: trunk/treebase-core/src/main/java/org/cipres/treebase/dao/jdbc/DiscreteMatrixElementJDBC.java
===================================================================
--- trunk/treebase-core/src/main/java/org/cipres/treebase/dao/jdbc/DiscreteMatrixElementJDBC.java	2010-02-16 21:15:56 UTC (rev 511)
+++ trunk/treebase-core/src/main/java/org/cipres/treebase/dao/jdbc/DiscreteMatrixElementJDBC.java	2010-02-17 22:50:46 UTC (rev 512)
@@ -110,13 +110,13 @@
 				
 				//IBM JDBC driver has a 32k batch limit:
 				if (count > MatrixJDBC.JDBC_BATCH_LIMIT) {
-					count = 0;
 					ps.executeBatch();
 					pCon.commit();
 
 					if (LOGGER.isDebugEnabled()) {
 						LOGGER.debug("committing count=" + count); //$NON-NLS-1$
 					}
+					count = 0;
 				}
 			}
 


This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.

[Treebase-guts] [ treebase-Bugs-2911673 ] Please repackage war file for Jboss

[SourceForge.net <no...@so...>]

Bugs item #2911673, was opened at 2009-12-09 17:35
Message generated for change (Comment added) made by vgapeyev
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=1126676&aid=2911673&group_id=248804

Please note that this message will contain a full copy of the comment thread,
including the initial issue submission, for this request,
not just the latest update.
Category: None
Group: None
>Status: Closed
Priority: 5
Private: No
Submitted By: Jon Auman (jonauman)
Assigned to: Nobody/Anonymous (nobody)
Summary: Please repackage war file for Jboss

Initial Comment:
The current war file explodes into the current directory, which is the webapps folder in tomcat. In Jboss, all wars go in the deploy folder, and Jboss expects them to create a new folder to expand into. So if the app is named treebase-web.war, the war file should expand into a treebase-web/ directory.

----------------------------------------------------------------------

>Comment By: Vladimir Gapeyev (vgapeyev)
Date: 2010-02-17 10:19

Message:
This ticket lost relevance: We decided to deploy with Tomcat rather than
JBoss. 

----------------------------------------------------------------------

You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=1126676&aid=2911673&group_id=248804

[Treebase-guts] [ treebase-Bugs-2953212 ] Hang-up in Search Result tabs

[SourceForge.net <no...@so...>]

Bugs item #2953212, was opened at 2010-02-16 19:48
Message generated for change (Settings changed) made by vgapeyev
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=1126676&aid=2953212&group_id=248804

Please note that this message will contain a full copy of the comment thread,
including the initial issue submission, for this request,
not just the latest update.
Category: data
Group: None
Status: Open
Priority: 9
Private: No
Submitted By: Vladimir Gapeyev (vgapeyev)
>Assigned to: Nobody/Anonymous (nobody)
Summary: Hang-up in Search Result tabs

Initial Comment:

After Jon and I addressed #2945410 [Proxy Error for taxon->tree search] by increasing Apache timeout to 10 min and #2945391 [New Indices Needed], a new issue transpired on search results: some "click paths" through the result tabs lead to a hang-up.  

To reproduce: 
- Search for "oak", clicking "All Text".
- After this finishes, click on result tabs, avoiding the "Taxa" tab.  I.e., keep clicking on "Matrices", "Trees", and "Tree topologies", in any order, as long as you wish.  They all should be finishing after a short delay. 
- Now, click on "Taxa".  It should finish fairly soon. 
- Now, click on "Matrices". This hangs up forever. 

A bonus observation: If (prior to "Taxa") you continue clicking on "Matrices" several times in a row, it appears you get a new result list each time.  This looks like a bug, unless it is a clever feature.

We spent a couple hours trying to figure out what is going on.  No major revelations, but here are a few observations: 

- When the hang up happens, there is a query process stuck on postgres that does not finish even for an hour. 

- The query process takes about 33% CPU, so something active might be going on during the hang-up. Also, responsiveness of the server to concurrent sessions degrades. 

- After looking up tips on the internets, we tried increasing size of the Tomcat's JDBC connections pool, with no effect. 

- Following a hunch that the switch from the c3p0 connection pool manager to that of Tomcat might have contributed, we rebuilt the WAR from SVN #422, i.e. right before the switch to JNDI.  That one hangs up, too. 

My best guess at the moment is that there is some kind of interaction     between queries on this page that leads to a "busy" deadlock.

----------------------------------------------------------------------

You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=1126676&aid=2953212&group_id=248804

[Treebase-guts] [ treebase-Bugs-2945410 ] Proxy Error for taxon->tree search

[SourceForge.net <no...@so...>]

Bugs item #2945410, was opened at 2010-02-03 13:36
Message generated for change (Comment added) made by vgapeyev
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=1126676&aid=2945410&group_id=248804

Please note that this message will contain a full copy of the comment thread,
including the initial issue submission, for this request,
not just the latest update.
Category: performance
Group: None
>Status: Closed
Priority: 9
Private: No
Submitted By: William Piel (sfrgpiel)
Assigned to: youjun guo (youjun)
Summary: Proxy Error for taxon->tree search

Initial Comment:
If I do a taxon search (e.g. search for "Homo sapiens" as taxon variant name under the Taxa tab), and then click on the Trees tab (i.e. to get a selection of trees based on the selection of two Homo sapiens records) I get the proxy error below after exactly 2 minutes. That suggests to me that there is a time-out in operation, but it also says that the query is taking too long. By comparison, the exact same query on the SDSC/DB2 version (http://8ball.sdsc.edu:6666/treebase-web/) resulted in 630 trees returned in 31 seconds. I have created an artifact with priority 8 (https://sourceforge.net/tracker/?func=detail&aid=2945391&group_id=248804&atid=1126676) requesting that new indexes be added to speed up this query. But in addition, we should probably increase the 2 minute limit before getting a proxy error, seeing as some queries will take longer than two minutes no matter what we do -- and since a taxon search is such a basic type of query, I'm giving this proxy time-out error a priority of 9.  

Bill

===========
Proxy Error

The proxy server received an invalid response from an upstream server.
The proxy server could not handle the request GET /treebase-web/search/treeSearch.html.

Reason: Error reading from remote server

Apache/2.2.3 (CentOS) Server at treebasedb-dev.nescent.org Port 80


----------------------------------------------------------------------

>Comment By: Vladimir Gapeyev (vgapeyev)
Date: 2010-02-16 19:51

Message:
Jon has increased Apache proxy timeout to 10min. 

There problem, however, persists, see #2953212, 
https://sourceforge.net/tracker/index.php?func=detail&aid=2953212&group_id=248804&atid=1126676

----------------------------------------------------------------------

Comment By: Vladimir Gapeyev (vgapeyev)
Date: 2010-02-16 19:51

Message:
Your bug has been resolved.  Thanks for the report.


----------------------------------------------------------------------

You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=1126676&aid=2945410&group_id=248804

[Treebase-guts] [ treebase-Bugs-2945391 ] New Indices Needed

[SourceForge.net <no...@so...>]

Bugs item #2945391, was opened at 2010-02-03 13:04
Message generated for change (Comment added) made by vgapeyev
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=1126676&aid=2945391&group_id=248804

Please note that this message will contain a full copy of the comment thread,
including the initial issue submission, for this request,
not just the latest update.
Category: data
Group: None
>Status: Closed
Priority: 9
Private: No
Submitted By: William Piel (sfrgpiel)
>Assigned to: Vladimir Gapeyev (vgapeyev)
Summary: New Indices Needed

Initial Comment:
Because we have reverted to an older instance of the data (and schema), there are some changes that we may need to make. One is to create indexes on fields that are likely to be queried a lot using something like "CREATE INDEX taxonlabel_i ON taxonlabel USING btree (taxonlabel);". 

At the bottom is a list of all our existing indices -- although all the PKs are covered other fields would seem to benefit from an index. I would assume that the following fields need to be indexed:

Table			Field
--------			-------
taxon			name
taxonvariant		fullname
taxonlabel		taxonlabel
citation			title
citation			abstract
person			lastname

In addition, I would think that FKs need to be indexed as well, although I don't know which are the critical ones. What is clear is that some queries are very very slow. For example, do a taxon variant name search on "Homo sapiens" (this results in a selection of two taxon records) and then click the "Trees" tab -- after 2 minutes it returned a "Proxy Error: The proxy server received an invalid response from an upstream server. Reason: Error reading from remote host" which I'm guessing is a time-out of some sort. At any rate, the FK called taxonlabel_id and the FK called phylotree_id in the phylotreenode table probably should be indexed to speed up querying for trees based on a selection of taxa. 

Undoubtedly there are other FKs that need indexing, but this requires knowledge on how common queries are made via hibernate.

These changes to the schema should be applied to both the dev and stage databases, as well as noted in the instantiation scripts.

bp



SELECT n.nspname AS "Schema", t.relname AS "Table", c.relname AS "Index"
FROM pg_catalog.pg_class c
     JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
     JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
     JOIN pg_catalog.pg_class t ON i.indrelid = t.oid
WHERE c.relkind = 'i'
    AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
    AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY n.nspname, t.relname, c.relname;

 Schema |            Table            |                   Index                    
--------+-----------------------------+--------------------------------------------
 public | algorithm                   | algorithm_pkey
 public | analysis                    | analysis_pkey
 public | analysis                    | analysis_study_id_idx
 public | analysisstep                | analysisstep_pkey
 public | analyzeddata                | analyzeddata_pkey
 public | ancestralstate              | ancestralstate_pkey
 public | ancstateset                 | ancstateset_pkey
 public | chargroup                   | chargroup_pkey
 public | chargroup_colrange          | chargroup_colrange_columnrange_id_key
 public | charpartition               | charpartition_pkey
 public | charset                     | charset_pkey
 public | charset_colrange            | charset_colrange_columnrange_id_key
 public | charweight                  | charweight_pkey
 public | charweight_colrange         | charweight_colrange_columnrange_id_key
 public | charweightset               | charweightset_pkey
 public | citation                    | citation_pkey
 public | citation_author             | citation_author_pkey
 public | citation_editor             | citation_editor_pkey
 public | citationstatus              | citationstatus_pkey
 public | coderecord_colrange         | coderecord_colrange_columnrange_id_key
 public | codonchar1_colrange         | codonchar1_colrange_columnrange_id_key
 public | codonchar2_colrange         | codonchar2_colrange_columnrange_id_key
 public | codonchar3_colrange         | codonchar3_colrange_columnrange_id_key
 public | codonnoncoding_colrange     | codonnoncoding_colrange_columnrange_id_key
 public | codonpositionset            | codonpositionset_pkey
 public | columnrange                 | columnrange_pkey
 public | compound_element            | compound_element_element_id_key
 public | compound_element            | compound_element_pkey
 public | cstreenode                  | cstreenode_pkey
 public | discretecharstate           | discretecharstate_phylochar_id_idx
 public | discretecharstate           | discretecharstate_pkey
 public | distancematrixelement       | distancematrixelement_pkey
 public | gapmode                     | gapmode_pkey
 public | geneticcode                 | geneticcode_pkey
 public | geneticcoderecord           | geneticcoderecord_pkey
 public | geneticcodeset              | geneticcodeset_pkey
 public | help                        | help_pkey
 public | itemdefinition              | itemdefinition_pkey
 public | itemvalue                   | itemvalue_pkey
 public | matrix                      | matrix_pkey
 public | matrix_itemdefinition       | matrix_itemdefinition_pkey
 public | matrixcolumn                | matrixcolumn_phylochar_id_idx
 public | matrixcolumn                | matrixcolumn_pkey
 public | matrixcolumn_itemdefinition | matrixcolumn_itemdefinition_pkey
 public | matrixdatatype              | matrixdatatype_phylochar_id_idx
 public | matrixdatatype              | matrixdatatype_pkey
 public | matrixelement               | matrixelement_discretecharstate_id_idx
 public | matrixelement               | matrixelement_itemdefinition_id_idx
 public | matrixelement               | matrixelement_pkey
 public | matrixkind                  | matrixkind_pkey
 public | matrixrow                   | matrixrow_matrix_id_idx
 public | matrixrow                   | matrixrow_pkey
 public | matrixrow                   | matrixrow_taxonlabel_id_idx
 public | nodeattribute               | nodeattribute_pkey
 public | person                      | person_pkey
 public | phylochar                   | phylochar_pkey
 public | phylotree                   | phylotree_pkey
 public | phylotreenode               | phylotreenode_pkey
 public | polytcount                  | polytcount_pkey
 public | rowsegment                  | rowsegment_matrixrow_id_idx
 public | rowsegment                  | rowsegment_pkey
 public | software                    | software_pkey
 public | statechangeset              | statechangeset_pkey
 public | stateformat                 | stateformat_pkey
 public | statemodifier               | statemodifier_pkey
 public | stateset                    | stateset_pkey
 public | stepmatrixelement           | stepmatrixelement_pkey
 public | study                       | study_citation_id_idx
 public | study                       | study_pkey
 public | study_nexusfile             | study_nexusfile_pkey
 public | studystatus                 | studystatus_pkey
 public | sub_matrix                  | sub_matrix_matrix_id_key
 public | sub_matrix                  | sub_matrix_pkey
 public | sub_taxonlabel              | sub_taxonlabel_pkey
 public | sub_taxonlabel              | sub_taxonlabel_taxonlabel_id_key
 public | sub_treeblock               | sub_treeblock_pkey
 public | sub_treeblock               | sub_treeblock_treeblock_id_key
 public | submission                  | submission_pkey
 public | submission                  | submission_study_id_idx
 public | submission                  | submission_study_id_key
 public | taxon                       | taxon_pkey
 public | taxonauthority              | taxonauthority_pkey
 public | taxonlabel                  | taxonlabel_pkey
 public | taxonlabelgroup             | taxonlabelgroup_pkey
 public | taxonlabelpartition         | taxonlabelpartition_pkey
 public | taxonlabelset               | taxonlabelset_pkey
 public | taxonlabelset_taxonlabel    | taxonlabelset_taxonlabel_pkey
 public | taxonlink                   | taxonlink_pkey
 public | taxonset                    | taxonset_pkey
 public | taxonset_taxon              | taxonset_taxon_pkey
 public | taxonvariant                | taxonvariant_pkey
 public | treeattribute               | treeattribute_pkey
 public | treeblock                   | treeblock_pkey
 public | treegroup                   | treegroup_pkey
 public | treegroup_phylotree         | treegroup_phylotree_pkey
 public | treekind                    | treekind_pkey
 public | treenodeedge                | treenodeedge_pkey
 public | treepartition               | treepartition_pkey
 public | treequality                 | treequality_pkey
 public | treeset                     | treeset_pkey
 public | treeset_phylotree           | treeset_phylotree_pkey
 public | treetype                    | treetype_pkey
 public | typeset                     | typeset_pkey
 public | user                        | user_pkey
 public | user                        | user_username_key
 public | userrole                    | userrole_pkey
 public | usertype                    | usertype_pkey
 public | usertyperecord              | usertyperecord_pkey
 public | usertyperrd_colrange        | usertyperrd_colrange_columnrange_id_key
(109 rows)


----------------------------------------------------------------------

>Comment By: Vladimir Gapeyev (vgapeyev)
Date: 2010-02-16 20:06

Message:
For fixes requested in the ticket, see below.  The problem, however,
persists, see #2953212, 
https://sourceforge.net/tracker/index.php?func=detail&aid=2953212&group_id=248804&atid=1126676

I have created indexes for the fields mentioned in the request, except for
citation(abstract).  I doubt, however, these would lead to any efficiency
gains: any standard DB index (btree, hash, ...) helps with "full-field"
comparisons, while the request probably meant google-style
"within-the-field" searches.  I did not index citation(abstract) exactly
because the point is the most apparent for this field. 

I have created indexes for FKs that participate in tree reconstruction. 
It is possible some FKs that participate in matrix reconstruction would
also benefit -- we can do those later, when we catch slow queries.


----------------------------------------------------------------------

Comment By: William Piel (sfrgpiel)
Date: 2010-02-08 10:55

Message:
This is assigned to Hilmar.

----------------------------------------------------------------------

You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=1126676&aid=2945391&group_id=248804

[Treebase-guts] [ treebase-Bugs-2953212 ] Hang-up in Search Result tabs

[SourceForge.net <no...@so...>]

Bugs item #2953212, was opened at 2010-02-16 19:48
Message generated for change (Tracker Item Submitted) made by vgapeyev
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=1126676&aid=2953212&group_id=248804

Please note that this message will contain a full copy of the comment thread,
including the initial issue submission, for this request,
not just the latest update.
Category: data
Group: None
Status: Open
Priority: 9
Private: No
Submitted By: Vladimir Gapeyev (vgapeyev)
Assigned to: Mark Dominus (mjdominus)
Summary: Hang-up in Search Result tabs

Initial Comment:

After Jon and I addressed #2945410 [Proxy Error for taxon->tree search] by increasing Apache timeout to 10 min and #2945391 [New Indices Needed], a new issue transpired on search results: some "click paths" through the result tabs lead to a hang-up.  

To reproduce: 
- Search for "oak", clicking "All Text".
- After this finishes, click on result tabs, avoiding the "Taxa" tab.  I.e., keep clicking on "Matrices", "Trees", and "Tree topologies", in any order, as long as you wish.  They all should be finishing after a short delay. 
- Now, click on "Taxa".  It should finish fairly soon. 
- Now, click on "Matrices". This hangs up forever. 

A bonus observation: If (prior to "Taxa") you continue clicking on "Matrices" several times in a row, it appears you get a new result list each time.  This looks like a bug, unless it is a clever feature.

We spent a couple hours trying to figure out what is going on.  No major revelations, but here are a few observations: 

- When the hang up happens, there is a query process stuck on postgres that does not finish even for an hour. 

- The query process takes about 33% CPU, so something active might be going on during the hang-up. Also, responsiveness of the server to concurrent sessions degrades. 

- After looking up tips on the internets, we tried increasing size of the Tomcat's JDBC connections pool, with no effect. 

- Following a hunch that the switch from the c3p0 connection pool manager to that of Tomcat might have contributed, we rebuilt the WAR from SVN #422, i.e. right before the switch to JNDI.  That one hangs up, too. 

My best guess at the moment is that there is some kind of interaction     between queries on this page that leads to a "busy" deadlock.

----------------------------------------------------------------------

You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=1126676&aid=2953212&group_id=248804

[Treebase-guts] SF.net SVN: treebase:[511] trunk/treebase-core/db

[<vga...@us...>]

Revision: 511
          http://treebase.svn.sourceforge.net/treebase/?rev=511&view=rev
Author:   vgapeyev
Date:     2010-02-16 21:15:56 +0000 (Tue, 16 Feb 2010)

Log Message:
-----------
Documenting the schema patching setup. 

Modified Paths:
--------------
    trunk/treebase-core/db/README.txt
    trunk/treebase-core/db/TODO.txt
    trunk/treebase-core/db/versionhistory.sql

Modified: trunk/treebase-core/db/README.txt
===================================================================
--- trunk/treebase-core/db/README.txt	2010-02-16 14:37:32 UTC (rev 510)
+++ trunk/treebase-core/db/README.txt	2010-02-16 21:15:56 UTC (rev 511)
@@ -1,60 +1,90 @@
-[This will contain instructions on how to do and commit patches. 
-For now, it's just a message from my email 2010-02-03  --VG]
+This directory keeps track of schema patches applied to the DB as the application evolves, 
+as well as occasional snapshots of the schema and pre-loaded dictionary-like data. 
 
-"A setup/discipline for schema changes"
+Outline
+======= 
 
-This brings up something that was on my back burner for a while:  
-setting up a workflow and a file/directory structure to maintain  
-schema patches. That would be something similar to migration scripts  
-in Ruby or Python (but with scripts written in SQL and no automation).
+The setup is inspired by database migrations in Ruby and Python, but is not nearly as comprehensive. 
 
-The idea is that we store in SVN the initial snapshot of the DB schema  
-and sequential patches that we have been developing w.r.t  
-treebasedev.  These patches will be used to bring up-to-date  
-treebasestage, and then treebaseprod.   After that we can erase the  
-patches, refresh the snapshot, and continue for another cycle.   
-Official TB2 code releases are to be declared at the start of a cycle,  
-when there are no patches yet.  The snapshot is what should be used to  
-create a fresh TB2 instance by someone who wants to run their own. 
+The 'patches' directory stores SQL scripts (nnnn_descr_label.sql) that move the schema 
+from one version to the next.  Occasionally, we put into the 'snaphots' subdirectory 
+nnnn_SCHEMA...sql and nnnn_DATA...sql that capture the state of the DB after the 
+patches/nnnn.sql patch has been applied.
 
-Patches are to be sequentially numbered (by hand).  To keep track  
-which DB instance has already been brought up to which patch version,  
-I suggest creating a special table with a number column.  Each patch  
-will have an UPDATE setting the number in the table to the patch's  
-number.
+Patches are sequentially numbered (by hand). If a snapshot nnnn is present, it reflects 
+the state of the DB after patch nnnn was applied. 
 
-If this sounds ok, I hope to set this up by end of tomorrow.
+The schema (staring with the very first 0000 snapshot) contains the 'versionhistory' table, 
+which is used to keep track which patches have already been applied to a particular DB instance. 
+This is particularly useful in multi-instance installations (e.g., when there are 
+development, staging, production, etc., instances).  [See versionhistory.sql for the table's definition.]
 
 
-Some more details: 
+Task-specific instructions
+==========================
 
-* Patch naming: 0001_short_name.sql
-* Each patch starts by incrementing the number in the version table.
-* Each patch will be executed as a transaction, but there is no need to insert transaction commands into the patch.  
-* Patches and snapshots must not include any ownership or permissions commands.
-* Version table schema: 
-CREATE TABLE versionhistory
-(
-  id serial NOT NULL,
-  patchnumber integer,
-  patchlabel character varying(63) NOT NULL,
-  patchdescription character varying(1023),
-  applied timestamp (0) without time zone DEFAULT now(),
-  CONSTRAINT versionhistory_pkey PRIMARY KEY (id),
-  CONSTRAINT versionhistory_unique_patchnumber UNIQUE (patchnumber)
-)
-COMMENT ON TABLE versionhistory IS 'VersionHistory table is NOT a part of the TreeBase application. It is only used within the development and deployment process to keep track of schema patches applied to a DB instance. ';
+Creating a patch 
+----------------
 
+A patch is generally committed into SVN together with Java code for the application.  
+It is expected that, for a given SVN version, the application code expects to work
+w.r.t. the database containing all the patches from this version.  
 
-To get a current schema snapshot
-================================
+Use the next sequential number and a short descriptive label to name a patch. 
+To avoid numbering conflicts, developers must coordinate their patching activity. 
 
-pg_dump -h treebase-dev.nescent.org -U treebase_app --format=p --no-owner --no-privileges --schema-only  treebasestage > schema_snapshot_treebasestage_2010-02-01.sql
+Each patch must start by an INSERT of a row describing the patch into the versionhistory table.  
+(See existing patches for examples.)
 
+Each patch will be executed as a transaction, so there is no need to insert 
+transaction commands into the patch.  
 
-To restore the schema into a new DB: 
-====================================
+Patches must not include any ownership or permissions commands: ownership and 
+permissions are installation-specific, and therefore should be handled by the 
+instance maintainer.
 
+When an error is discovered in a recent patch, it may be ok to fix it and commit 
+a new version under the same patch number, provided it is possible to adjust all DB 
+instances to conform with the new patch version.  This can be doable when the patch
+was only applied to the development DB instance.  When the patch with an error was already 
+applied to production databases, it could be more prudent to develop a new, error-fixing patch. 
+
+
+Applying a patch
+----------------
+
+Apply each patch within a transaction, e.g.,  
+psql -d yourdb -U yourusername
+yourdb=> begin transaction;
+yourdb=> \i nnnn_your_patch.sql
+yourdb=> commit;  OR rollback;
+
+After that, adjust ownership and permissions on newly created objects, if any, 
+as required by the DB instance.  
+
+Use your judgment on what to do in case the patch application generated errors 
+and you had to roll back.  Most likely, someone has to work on improving the patch. 
+
+
+Creating a current snapshot
+---------------------------
+
+Schema snapshots must not include any ownership or permissions commands, since these 
+are installation-specific.  This command,  
+
+pg_dump -h your.host.url -U your_username --format=p --no-owner --no-privileges --schema-only  yourdb >  nnnn_your_label.sql
+
+creates a reasonably lean dump of the schema.  However, if the DB contained any objects 
+not created by prior snapshots and patches, they should be removed by hand. 
+
+Data snapshot is trickier.  The best bet right now is probably by hand-modifying the previous data snapshot. 
+
+
+Creating a fresh DB
+------------------
+
+Use the most recent snapshot: 
+
 psql -d yourdb -U yourusername -f nnn_SCHEMA_xxx.sql
 psql -d yourdb -U yourusername -f nnn_DATA_xxx.sql
 
@@ -64,4 +94,35 @@
 yourdb=> begin transaction;
 yourdb=> \i nnn_SCHEMA_xxx.sql
 yourdb=> \i nnn_DATA_xxx.sql
-yourdb=> end transaction 
+yourdb=> commit;  
+
+
+Recommended development - production - staging workflow 
+=======================================================
+
+Local instances:  It is recommended that each developer has his own DB instance to use for writing 
+and initially testing new versions of the application and schema patches. This ensures 
+at least some basic compatibility between SQL and Java within a given SVN commit. 
+These instances may contain only minimal data, as useful for the developer. 
+
+Development instance:  The dev DB instance is frequently re-built to track 
+the most recent SVN version of the application and DB patches. It is used to verify correctness 
+of new versions and to communicate with non-programmer project participants. 
+This instance should contain sizable and representative amount of data, 
+but not necessarily as much or as good quality as the production instance. 
+
+Staging instance:  The stage instance is a testbed for rolling out a new version on production.  
+When development on dev reaches  release, a staging instance is created by cloning the 
+current production instance.  This ensures that the possible new data quirks accumulated 
+since the previous release are present.  Then patches spanning from the previous to the current 
+release are applied, new version of the application is installed, and the whole system is tested
+and verified as necessary.  If all works well, then the release roll-out may proceed on production. 
+Otherwise, back to development.    
+
+Production instance: This is the instance with the authoritative data that is constantly being 
+accessed by external uses and can tolerate only brief periods of downtime.  Make a backup before
+rolling out a new release and only roll out releases that were successfully verified on staging. 
+After a new release roll-out, it makes sense to use the prod instance to create a new 
+schema-and-dictionaries snapshot. 
+
+  
\ No newline at end of file

Modified: trunk/treebase-core/db/TODO.txt
===================================================================
--- trunk/treebase-core/db/TODO.txt	2010-02-16 14:37:32 UTC (rev 510)
+++ trunk/treebase-core/db/TODO.txt	2010-02-16 21:15:56 UTC (rev 511)
@@ -1,43 +1,29 @@
 Pending framework issues 
 ========================
 
-* Prior to taking the 1st schema snapshot (from treestage or treedev?): 
-  - Create table versionhistory
-
-* Develop a script for getting a current snapshot of the initial (dictionary) data, 
+* Would like a script for getting a current snapshot of the initial (dictionary) data, 
   to be used alongside with the schema snapshot command. 
+  [A problem: simply designating some tables as "dictionaries" and others as "facts" would not work -- 
+   at least some tables (Phylochar, DiscreteCharState) contains both "dictionary" and "fact" records.]
+   
+* Automation scripts for tasks, especially patch application, so that the sysadmin does not forget to wrap a transaction. 
+  
+* A better way for handling ownership and permissions in particular DB instances -- there is 
+  too much manual work at the moment.
+  
+* Less-intrusive permissions/ownership management.  Currently, ownerships and permissions are handled by pg_owner, pg_admin functions that the 
+  sysadmin creates in DB after the initial installation.  Consequently, they get into the schema snapshops and have to be removed by hand.
 
 
 Pending schema patches: 
 ======================
 
-[To be put in as patches after the storage structure for them is in place in this directory -- VG 2010-02-04]:
+* Maybe some indices around matrices (in addition to the indices around trees that are already there). 
 
-* Give explicit names to all constraints
 
-* Drop geospot_id_sequence -- there is no corresponding table
+Pending data cleanups (on staging):
+===================================
 
-* Correct PK name in stepmatrixelement and create stepmatrixelement_id_sequence
-
-* Create indexes, as suggested by Bill 2010-02-03 (and maybe later)
-        Table				Field
-        --------			-------
-        taxon				name
-        taxonvariant		fullname
-        taxonlabel			taxonlabel
-        citation			title
-        citation			abstract
-        person				lastname
-        phylotreenode    	taxonlabel_id, phylotree_id 
-    See: 
-      -  Bill's message of 2010-02-03:    
-      -  https://sourceforge.net/tracker/?func=detail&atid=1126676&aid=2945391&group_id=248804, 
-      -  Also, compare to the end-of-Jan schema snapshot of treebasedev   
-
-
-Pending data cleanups (mostly, on staging):
-===========================================
-
 * Remove junk, particularly that created by tests
 
 * Known junk: Submission 22 and its related records

Modified: trunk/treebase-core/db/versionhistory.sql
===================================================================
--- trunk/treebase-core/db/versionhistory.sql	2010-02-16 14:37:32 UTC (rev 510)
+++ trunk/treebase-core/db/versionhistory.sql	2010-02-16 21:15:56 UTC (rev 511)
@@ -1,3 +1,6 @@
+-- Table for tracking which schema patches have been applied to the current DB instance. 
+-- This table is already in the snapshot 0000_SCHEMA_before_patches_start.sql 
+
 CREATE TABLE versionhistory
 (
   id serial NOT NULL,


This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.

[Treebase-guts] SF.net SVN: treebase:[510] trunk/treebase-core

[<vga...@us...>]

Revision: 510
          http://treebase.svn.sourceforge.net/treebase/?rev=510&view=rev
Author:   vgapeyev
Date:     2010-02-16 14:37:32 +0000 (Tue, 16 Feb 2010)

Log Message:
-----------
Introducing schema patching setup, as well as a few first patches: some cleanup and a few indexes.

Modified Paths:
--------------
    trunk/treebase-core/db/README.txt
    trunk/treebase-core/db/TODO.txt
    trunk/treebase-core/src/main/java/org/cipres/treebase/domain/matrix/StepMatrixElement.java

Added Paths:
-----------
    trunk/treebase-core/db/patches/
    trunk/treebase-core/db/patches/0001_constraint-names.sql
    trunk/treebase-core/db/patches/0002_drop-geospot_id_sequence.sql
    trunk/treebase-core/db/patches/0003_rename-stepmatrixelement-pk.sql
    trunk/treebase-core/db/patches/0004_create-indexes.sql
    trunk/treebase-core/db/snapshots/
    trunk/treebase-core/db/snapshots/0000_DATA_before_patches_start.sql
    trunk/treebase-core/db/snapshots/0000_SCHEMA_before_patches_start.sql
    trunk/treebase-core/db/versionhistory.sql

Modified: trunk/treebase-core/db/README.txt
===================================================================
--- trunk/treebase-core/db/README.txt	2010-02-15 16:05:19 UTC (rev 509)
+++ trunk/treebase-core/db/README.txt	2010-02-16 14:37:32 UTC (rev 510)
@@ -29,15 +29,39 @@
 Some more details: 
 
 * Patch naming: 0001_short_name.sql
-* Each patch is wrapped into a transaction and starts by incrementing the number in the version table. 
+* Each patch starts by incrementing the number in the version table.
+* Each patch will be executed as a transaction, but there is no need to insert transaction commands into the patch.  
 * Patches and snapshots must not include any ownership or permissions commands.
 * Version table schema: 
 CREATE TABLE versionhistory
 (
   id serial NOT NULL,
   patchnumber integer,
-  patchlabel character varying(50) NOT NULL,
-  patchdescription character varying(1000),
-  applied timestamp with time zone,
-  CONSTRAINT versionhistory_pkey PRIMARY KEY (id)
-)
\ No newline at end of file
+  patchlabel character varying(63) NOT NULL,
+  patchdescription character varying(1023),
+  applied timestamp (0) without time zone DEFAULT now(),
+  CONSTRAINT versionhistory_pkey PRIMARY KEY (id),
+  CONSTRAINT versionhistory_unique_patchnumber UNIQUE (patchnumber)
+)
+COMMENT ON TABLE versionhistory IS 'VersionHistory table is NOT a part of the TreeBase application. It is only used within the development and deployment process to keep track of schema patches applied to a DB instance. ';
+
+
+To get a current schema snapshot
+================================
+
+pg_dump -h treebase-dev.nescent.org -U treebase_app --format=p --no-owner --no-privileges --schema-only  treebasestage > schema_snapshot_treebasestage_2010-02-01.sql
+
+
+To restore the schema into a new DB: 
+====================================
+
+psql -d yourdb -U yourusername -f nnn_SCHEMA_xxx.sql
+psql -d yourdb -U yourusername -f nnn_DATA_xxx.sql
+
+OR 
+
+psql -d yourdb -U yourusername
+yourdb=> begin transaction;
+yourdb=> \i nnn_SCHEMA_xxx.sql
+yourdb=> \i nnn_DATA_xxx.sql
+yourdb=> end transaction 

Modified: trunk/treebase-core/db/TODO.txt
===================================================================
--- trunk/treebase-core/db/TODO.txt	2010-02-15 16:05:19 UTC (rev 509)
+++ trunk/treebase-core/db/TODO.txt	2010-02-16 14:37:32 UTC (rev 510)
@@ -1,27 +1,42 @@
+Pending framework issues 
+========================
+
+* Prior to taking the 1st schema snapshot (from treestage or treedev?): 
+  - Create table versionhistory
+
+* Develop a script for getting a current snapshot of the initial (dictionary) data, 
+  to be used alongside with the schema snapshot command. 
+
+
 Pending schema patches: 
+======================
 
 [To be put in as patches after the storage structure for them is in place in this directory -- VG 2010-02-04]:
 
-* Drop geospot_id_sequence -- there is no corresponding sequence
+* Give explicit names to all constraints
 
+* Drop geospot_id_sequence -- there is no corresponding table
+
 * Correct PK name in stepmatrixelement and create stepmatrixelement_id_sequence
 
 * Create indexes, as suggested by Bill 2010-02-03 (and maybe later)
-     Bill's message of 2010-02-03: "Of the top of my head, I would assume that the following fields need to be indexed:
-        Table			Field
+        Table				Field
         --------			-------
-        taxon			name
+        taxon				name
         taxonvariant		fullname
-        taxonlabel		taxonlabel
+        taxonlabel			taxonlabel
         citation			title
         citation			abstract
-        person			lastname"
-     More details in bug https://sourceforge.net/tracker/?func=detail&atid=1126676&aid=2945391&group_id=248804, 
-        esp phylotree, phylonode indexes. 
-     Also, compare to the end-of-Jan schema snapshot of treebasedev   
+        person				lastname
+        phylotreenode    	taxonlabel_id, phylotree_id 
+    See: 
+      -  Bill's message of 2010-02-03:    
+      -  https://sourceforge.net/tracker/?func=detail&atid=1126676&aid=2945391&group_id=248804, 
+      -  Also, compare to the end-of-Jan schema snapshot of treebasedev   
 
 
 Pending data cleanups (mostly, on staging):
+===========================================
 
 * Remove junk, particularly that created by tests
 
@@ -41,4 +56,11 @@
      2. I got the user_id for the "migration" user, and it happened to be 9955
      3. I ran this statement: "UPDATE submission SET user_id = 9955 WHERE user_id IS NULL"
      Now all migrated data belong to the user "migration". "
+     
+     
+Data cleanups on production: 
+============================
+
+* Synch hibernate_sequence and {sub_matrix,sub_treebaseblock}.collection_id
+
     
\ No newline at end of file

Added: trunk/treebase-core/db/patches/0001_constraint-names.sql
===================================================================
--- trunk/treebase-core/db/patches/0001_constraint-names.sql	                        (rev 0)
+++ trunk/treebase-core/db/patches/0001_constraint-names.sql	2010-02-16 14:37:32 UTC (rev 510)
@@ -0,0 +1,880 @@
+insert into versionhistory(patchnumber, patchlabel, patchdescription) 
+  values (1, 'constraint-names', 
+          'Replace system-generated FK constraint names by explicit ones'); 
+
+
+ALTER TABLE ONLY usertyperecord 
+  DROP CONSTRAINT fk136af6164d737e46, 
+  ADD CONSTRAINT usertyperecord_fkto_typeset 
+    FOREIGN KEY (typeset_id)  REFERENCES typeset(typeset_id);
+
+ALTER TABLE ONLY usertyperecord 
+  DROP CONSTRAINT fk136af61692e6a38e, 
+  ADD CONSTRAINT usertyperecord_fkto_usertype 
+    FOREIGN KEY (usertype_id)  REFERENCES usertype(usertype_id);
+
+ALTER TABLE ONLY analysisstep 
+  DROP CONSTRAINT fk21f853a848a2817c, 
+  ADD CONSTRAINT analysisstep_fkto_algorithm 
+    FOREIGN KEY (algorithm_id)  REFERENCES algorithm(algorithm_id);
+
+ALTER TABLE ONLY analysisstep 
+  DROP CONSTRAINT fk21f853a865edd5f8, 
+  ADD CONSTRAINT analysisstep_fkto_software 
+    FOREIGN KEY (software_id)  REFERENCES software(software_id);
+
+ALTER TABLE ONLY analysisstep 
+  DROP CONSTRAINT fk21f853a8d1884dd8, 
+  ADD CONSTRAINT analysisstep_fkto_analysis 
+    FOREIGN KEY (analysis_id)  REFERENCES analysis(analysis_id);
+
+ALTER TABLE ONLY geneticcoderecord 
+  DROP CONSTRAINT fk237932b76cb73fc6, 
+  ADD CONSTRAINT geneticcoderecord_fkto_geneticcode 
+    FOREIGN KEY (geneticcode_id)  REFERENCES geneticcode(geneticcode_id);
+
+ALTER TABLE ONLY geneticcoderecord 
+  DROP CONSTRAINT fk237932b782d8ecce, 
+  ADD CONSTRAINT geneticcoderecord_fkto_geneticcodeset 
+    FOREIGN KEY (geneticcodeset_id)  REFERENCES geneticcodeset(geneticcodeset_id);
+
+ALTER TABLE ONLY citation_author 
+  DROP CONSTRAINT fk24aa55e36707573b, 
+  ADD CONSTRAINT citation_author_fkto_authors_person 
+    FOREIGN KEY (authors_person_id) REFERENCES person(person_id);
+
+ALTER TABLE ONLY citation_author 
+  DROP CONSTRAINT fk24aa55e38e1e4df8, 
+  ADD CONSTRAINT citation_author_fkto_citation 
+    FOREIGN KEY (citation_id) REFERENCES citation(citation_id);
+
+ALTER TABLE ONLY itemvalue 
+  DROP CONSTRAINT fk27b41a1e459091e5, 
+  ADD CONSTRAINT itemvalue_fkto_element 
+    FOREIGN KEY (element_id)  REFERENCES matrixelement(matrixelement_id);
+
+ALTER TABLE ONLY taxonlabelset 
+  DROP CONSTRAINT Fk28d3a5983c572c3c, 
+  ADD CONSTRAINT taxonlabelset_fkto_study 
+    FOREIGN KEY (study_id)  REFERENCES study(study_id);
+
+ALTER TABLE ONLY citation_editor 
+  DROP CONSTRAINT fk2a8955c568f6f619, 
+  ADD CONSTRAINT citation_editor_fkto_editors_person 
+    FOREIGN KEY (editors_person_id) REFERENCES person(person_id);
+
+ALTER TABLE ONLY citation_editor 
+  DROP CONSTRAINT fk2a8955c5ec93f501, 
+  ADD CONSTRAINT citation_editor_fkto_citation 
+    FOREIGN KEY (citation_id) REFERENCES citation(citation_id);
+
+ALTER TABLE ONLY charset_colrange 
+  DROP CONSTRAINT fk2cbd90416d5b50, 
+  ADD CONSTRAINT charset_colrange_fkto_charset 
+    FOREIGN KEY (charset_id) REFERENCES charset(charset_id);
+
+ALTER TABLE ONLY charset_colrange 
+  DROP CONSTRAINT fk2cbd9079a523e6, 
+  ADD CONSTRAINT charset_colrange_fkto_columnrange 
+    FOREIGN KEY (columnrange_id) REFERENCES columnrange(columnrange_id);
+
+ALTER TABLE ONLY taxonlabelset_taxonlabel 
+  DROP CONSTRAINT fk2eb54b7163ab9fd7, 
+  ADD CONSTRAINT taxonlabelset_taxonlabel_fkto_taxonlabel 
+    FOREIGN KEY (taxonlabel_id) REFERENCES taxonlabel(taxonlabel_id);
+
+ALTER TABLE ONLY taxonlabelset_taxonlabel 
+  DROP CONSTRAINT fk2eb54b71e310471d, 
+  ADD CONSTRAINT taxonlabelset_taxonlabel_fkto_taxonlabelset 
+    FOREIGN KEY (taxonlabelset_id) REFERENCES taxonlabelset(taxonlabelset_id);
+
+ALTER TABLE ONLY discretecharstate 
+  DROP CONSTRAINT fk2eef2802163c67ce, 
+  ADD CONSTRAINT discretecharstate_fkto_stateset 
+    FOREIGN KEY (stateset_id)  REFERENCES stateset(stateset_id);
+
+ALTER TABLE ONLY discretecharstate 
+  DROP CONSTRAINT fk2eef2802c7beaafe, 
+  ADD CONSTRAINT discretecharstate_fkto_ancestralstate 
+    FOREIGN KEY (ancestralstate_id)  REFERENCES ancestralstate(ancestralstate_id);
+
+ALTER TABLE ONLY discretecharstate 
+  DROP CONSTRAINT fk2eef2802fe41a723, 
+  ADD CONSTRAINT discretecharstate_fkto_phylochar 
+    FOREIGN KEY (phylochar_id)  REFERENCES phylochar(phylochar_id);
+
+ALTER TABLE ONLY codonnoncoding_colrange 
+  DROP CONSTRAINT fk307897fc491db20e, 
+  ADD CONSTRAINT codonnoncoding_colrange_fkto_codonpositionset 
+    FOREIGN KEY (codonpositionset_id) REFERENCES codonpositionset(codonpositionset_id);
+
+ALTER TABLE ONLY codonnoncoding_colrange 
+  DROP CONSTRAINT fk307897fc79a523e6, 
+  ADD CONSTRAINT codonnoncoding_colrange_fkto_columnrange 
+    FOREIGN KEY (columnrange_id) REFERENCES columnrange(columnrange_id);
+
+ALTER TABLE ONLY compound_element 
+  DROP CONSTRAINT fk31183048459091e5,
+  ADD CONSTRAINT compound_element_fkto_element 
+    FOREIGN KEY (element_id) REFERENCES matrixelement(matrixelement_id);
+
+ALTER TABLE ONLY compound_element 
+  DROP CONSTRAINT fk311830485f4f7ceb, 
+  ADD CONSTRAINT compound_element_fkto_compound 
+    FOREIGN KEY (compound_id) REFERENCES matrixelement(matrixelement_id);
+
+ALTER TABLE ONLY treeset_phylotree 
+  DROP CONSTRAINT fk31fc19a7b710cb23, 
+  ADD CONSTRAINT treeset_phylotree_fkto_phylotree 
+    FOREIGN KEY (phylotree_id) REFERENCES phylotree(phylotree_id);
+
+ALTER TABLE ONLY treeset_phylotree 
+  DROP CONSTRAINT fk31fc19a7ec59b1e3, 
+  ADD CONSTRAINT treeset_phylotree_fkto_treeset 
+    FOREIGN KEY (treeset_id) REFERENCES treeset(treeset_id);
+
+ALTER TABLE ONLY "user"
+  DROP CONSTRAINT fk36ebcbe3910672,   
+  ADD CONSTRAINT  user_fkto_person
+    FOREIGN KEY (person_id) REFERENCES person(person_id);
+
+ALTER TABLE ONLY "user"
+  DROP CONSTRAINT fk36ebcbf59dd12,   
+  ADD CONSTRAINT user_fkto_userrole
+    FOREIGN KEY (userrole_id) REFERENCES userrole(userrole_id);
+
+ALTER TABLE ONLY chargroup 
+  DROP CONSTRAINT fk3af18c91aa11dc6, 
+  ADD CONSTRAINT chargroup_fkto_charpartition 
+    FOREIGN KEY (charpartition_id)  REFERENCES charpartition(charpartition_id);
+
+ALTER TABLE ONLY phylotreenode 
+  DROP CONSTRAINT fk3ea79944271a5763, 
+  ADD CONSTRAINT phylotreenode_fkto_nodeattribute 
+    FOREIGN KEY (nodeattribute_id)  REFERENCES nodeattribute(nodeattribute_id);
+
+ALTER TABLE ONLY phylotreenode 
+  DROP CONSTRAINT fk3ea799443c348165, 
+  ADD CONSTRAINT phylotreenode_fkto_sibling 
+    FOREIGN KEY (sibling_id)  REFERENCES phylotreenode(phylotreenode_id);
+
+ALTER TABLE ONLY phylotreenode 
+  DROP CONSTRAINT fk3ea79944434fdccb, 
+  ADD CONSTRAINT phylotreenode_fkto_child 
+    FOREIGN KEY (child_id)  REFERENCES phylotreenode(phylotreenode_id);
+
+ALTER TABLE ONLY phylotreenode 
+  DROP CONSTRAINT fk3ea799445bcc767d, 
+  ADD CONSTRAINT phylotreenode_fkto_parent 
+    FOREIGN KEY (parent_id)  REFERENCES phylotreenode(phylotreenode_id);
+
+ALTER TABLE ONLY phylotreenode 
+  DROP CONSTRAINT fk3ea7994463ab9fd7, 
+  ADD CONSTRAINT phylotreenode_fkto_taxonlabel 
+    FOREIGN KEY (taxonlabel_id)  REFERENCES taxonlabel(taxonlabel_id);
+
+ALTER TABLE ONLY phylotreenode 
+  DROP CONSTRAINT fk3ea79944b710cb23, 
+  ADD CONSTRAINT phylotreenode_fkto_phylotree 
+    FOREIGN KEY (phylotree_id)  REFERENCES phylotree(phylotree_id);
+
+ALTER TABLE ONLY ancestralstate 
+  DROP CONSTRAINT fk3fa14284684f6406, 
+  ADD CONSTRAINT ancestralstate_fkto_ancstateset 
+    FOREIGN KEY (ancstateset_id)  REFERENCES ancstateset(ancstateset_id);
+
+ALTER TABLE ONLY ancestralstate 
+  DROP CONSTRAINT fk3fa14284f4803ce6, 
+  ADD CONSTRAINT ancestralstate_fkto_discretecharstate 
+    FOREIGN KEY (discretecharstate_id)  REFERENCES discretecharstate(discretecharstate_id);
+
+ALTER TABLE ONLY usertyperrd_colrange 
+  DROP CONSTRAINT fk405805dd79a523e6, 
+  ADD CONSTRAINT usertyperrd_colrange_fkto_columnrange 
+    FOREIGN KEY (columnrange_id) REFERENCES columnrange(columnrange_id);
+
+ALTER TABLE ONLY usertyperrd_colrange 
+  DROP CONSTRAINT fk405805ddc009330e, 
+  ADD CONSTRAINT usertyperrd_colrange_fkto_usertyperecord 
+    FOREIGN KEY (usertyperecord_id) REFERENCES usertyperecord(usertyperecord_id);
+
+ALTER TABLE ONLY study_nexusfile 
+  DROP CONSTRAINT fk47fc1ee53c572c3c, 
+  ADD CONSTRAINT study_nexusfile_fkto_study 
+    FOREIGN KEY (study_id) REFERENCES study(study_id);
+
+ALTER TABLE ONLY taxonlabelgroup 
+  DROP CONSTRAINT fk4ae2663513a813dd, 
+  ADD CONSTRAINT taxonlabelgroup_fkto_taxonlabelpartition 
+    FOREIGN KEY (taxonlabelpartition_id)  REFERENCES taxonlabelpartition(taxonlabelpartition_id);
+
+ALTER TABLE ONLY study 
+  DROP CONSTRAINT fk4b915a9255a519c, 
+  ADD CONSTRAINT study_fkto_studystatus 
+    FOREIGN KEY (studystatus_id)  REFERENCES studystatus(studystatus_id);
+
+ALTER TABLE ONLY study 
+  DROP CONSTRAINT fk4b915a98e1e4df8, 
+  ADD CONSTRAINT study_fkto_citation 
+    FOREIGN KEY (citation_id)  REFERENCES citation(citation_id);
+
+ALTER TABLE ONLY study 
+  DROP CONSTRAINT fk4b915a9bef300b2, 
+  ADD CONSTRAINT study_fkto_user 
+    FOREIGN KEY (user_id)  REFERENCES "user"(user_id);
+
+ALTER TABLE ONLY codonpositionset 
+  DROP CONSTRAINT fk4e501cc2a414944f, 
+  ADD CONSTRAINT codonpositionset_fkto_matrix 
+    FOREIGN KEY (matrix_id)  REFERENCES matrix(matrix_id);
+
+ALTER TABLE ONLY charweight_colrange 
+  DROP CONSTRAINT fk54ca674e79a523e6, 
+  ADD CONSTRAINT charweight_colrange_fkto_columnrange 
+    FOREIGN KEY (columnrange_id) REFERENCES columnrange(columnrange_id);
+
+ALTER TABLE ONLY charweight_colrange 
+  DROP CONSTRAINT fk54ca674ef9a75ee, 
+  ADD CONSTRAINT charweight_colrange_fkto_charweight 
+    FOREIGN KEY (charweight_id) REFERENCES charweight(charweight_id);
+
+ALTER TABLE ONLY charset 
+  DROP CONSTRAINT fk56d8ed2ca414944f, 
+  ADD CONSTRAINT charset_fkto_matrix 
+    FOREIGN KEY (matrix_id)  REFERENCES matrix(matrix_id);
+
+ALTER TABLE ONLY matrix_itemdefinition 
+  DROP CONSTRAINT fk5717e0e4405a7cee, 
+  ADD CONSTRAINT matrix_itemdefinition_fkto_itemdefinition 
+    FOREIGN KEY (itemdefinition_id) REFERENCES itemdefinition(itemdefinition_id);
+
+ALTER TABLE ONLY matrix_itemdefinition 
+  DROP CONSTRAINT fk5717e0e4ac5c19dd, 
+  ADD CONSTRAINT matrix_itemdefinition_fkto_matrix 
+    FOREIGN KEY (matrix_id) REFERENCES matrix(matrix_id);
+
+ALTER TABLE ONLY sub_matrix 
+  DROP CONSTRAINT fk5f26a2c055961aee, 
+  ADD CONSTRAINT sub_matrix_fkto_matrix 
+    FOREIGN KEY (matrix_id) REFERENCES matrix(matrix_id);
+
+ALTER TABLE ONLY sub_matrix 
+  DROP CONSTRAINT fk5f26a2c08dfe4858, 
+  ADD CONSTRAINT sub_matrix_fkto_submission 
+    FOREIGN KEY (submission_id) REFERENCES submission(submission_id);
+
+ALTER TABLE ONLY taxonlabel 
+  DROP CONSTRAINT fk5f548a6a3c1b6f7, 
+  ADD CONSTRAINT taxonlabel_fkto_taxonvariant 
+    FOREIGN KEY (taxonvariant_id)  REFERENCES taxonvariant(taxonvariant_id);
+
+ALTER TABLE ONLY taxonlabel 
+  DROP CONSTRAINT fk5f548a6a3c572c3c, 
+  ADD CONSTRAINT taxonlabel_fkto_study 
+    FOREIGN KEY (study_id)  REFERENCES study(study_id);
+
+ALTER TABLE ONLY geneticcodeset 
+  DROP CONSTRAINT fk6a12f7dca414944f, 
+  ADD CONSTRAINT geneticcodeset_fkto_matrix 
+    FOREIGN KEY (matrix_id)  REFERENCES matrix(matrix_id);
+
+ALTER TABLE ONLY taxonlabelgroup_taxonlabel 
+  DROP CONSTRAINT fk73bbf6b4294b993d, 
+  ADD CONSTRAINT taxonlabelgroup_taxonlabel_fkto_taxonlabelgroup 
+    FOREIGN KEY (taxonlabelgroup_id) REFERENCES taxonlabelgroup(taxonlabelgroup_id);
+
+
+
+ALTER TABLE ONLY taxonlabelgroup_taxonlabel 
+  DROP CONSTRAINT fk73bbf6b463ab9fd7, 
+  ADD CONSTRAINT taxonlabelgroup_taxonlabel_fkto_taxonlabel 
+    FOREIGN KEY (taxonlabel_id) REFERENCES taxonlabel(taxonlabel_id);
+
+
+
+ALTER TABLE ONLY phylotree 
+  DROP CONSTRAINT fk76f2e4423c572c3c, 
+  ADD CONSTRAINT phylotree_fkto_study 
+    FOREIGN KEY (study_id)  REFERENCES study(study_id);
+
+
+
+ALTER TABLE ONLY phylotree 
+  DROP CONSTRAINT fk76f2e442a3015ce3, 
+  ADD CONSTRAINT phylotree_fkto_treeattribute 
+    FOREIGN KEY (treeattribute_id)  REFERENCES treeattribute(treeattribute_id);
+
+
+
+ALTER TABLE ONLY phylotree 
+  DROP CONSTRAINT fk76f2e442bfd107c3, 
+  ADD CONSTRAINT phylotree_fkto_treeblock 
+    FOREIGN KEY (treeblock_id)  REFERENCES treeblock(treeblock_id);
+
+
+
+ALTER TABLE ONLY phylotree 
+  DROP CONSTRAINT fk76f2e442ea08b443, 
+  ADD CONSTRAINT phylotree_fkto_treequality 
+    FOREIGN KEY (treequality_id)  REFERENCES treequality(treequality_id);
+
+
+
+ALTER TABLE ONLY phylotree 
+  DROP CONSTRAINT fk76f2e442f5aea931, 
+  ADD CONSTRAINT phylotree_fkto_treetype 
+    FOREIGN KEY (treetype_id)  REFERENCES treetype(treetype_id);
+
+
+
+ALTER TABLE ONLY phylotree 
+  DROP CONSTRAINT fk76f2e442fe2812f1, 
+  ADD CONSTRAINT phylotree_fkto_treekind 
+    FOREIGN KEY (treekind_id)  REFERENCES treekind(treekind_id);
+
+
+
+ALTER TABLE ONLY treenodeedge 
+  DROP CONSTRAINT fk7767285d1851763b, 
+  ADD CONSTRAINT treenodeedge_fkto_parentnode 
+    FOREIGN KEY (parentnode_id)  REFERENCES phylotreenode(phylotreenode_id);
+
+
+
+ALTER TABLE ONLY treenodeedge 
+  DROP CONSTRAINT fk7767285d27fd0589, 
+  ADD CONSTRAINT treenodeedge_fkto_childnode 
+    FOREIGN KEY (childnode_id)  REFERENCES phylotreenode(phylotreenode_id);
+
+
+
+ALTER TABLE ONLY stateset 
+  DROP CONSTRAINT fk7d38523150cbab47, 
+  ADD CONSTRAINT stateset_fkto_matrix 
+    FOREIGN KEY (matrix_id)  REFERENCES matrix(matrix_id);
+
+
+
+ALTER TABLE ONLY cstreenode 
+  DROP CONSTRAINT fk82b9ab10c6999fce, 
+  ADD CONSTRAINT cstreenode_fkto_cstree 
+    FOREIGN KEY (cstree_id)  REFERENCES usertype(usertype_id);
+
+
+
+ALTER TABLE ONLY cstreenode 
+  DROP CONSTRAINT fk82b9ab10f4803ce6, 
+  ADD CONSTRAINT cstreenode_fkto_discretecharstate 
+    FOREIGN KEY (discretecharstate_id)  REFERENCES discretecharstate(discretecharstate_id);
+
+
+
+ALTER TABLE ONLY cstreenode 
+  DROP CONSTRAINT fk82b9ab10f572a92, 
+  ADD CONSTRAINT cstreenode_fkto_parentnode 
+    FOREIGN KEY (parentnode_id)  REFERENCES cstreenode(cstreenode_id);
+
+
+
+ALTER TABLE ONLY sub_taxonlabel 
+  DROP CONSTRAINT fk86909e963ab9fd7, 
+  ADD CONSTRAINT sub_taxonlabel_fkto_taxonlabel 
+    FOREIGN KEY (taxonlabel_id) REFERENCES taxonlabel(taxonlabel_id);
+
+
+
+ALTER TABLE ONLY sub_taxonlabel 
+  DROP CONSTRAINT fk86909e98dfe4858, 
+  ADD CONSTRAINT sub_taxonlabel_fkto_submission 
+    FOREIGN KEY (submission_id) REFERENCES submission(submission_id);
+
+
+
+ALTER TABLE ONLY matrix 
+  DROP CONSTRAINT fk87208bc13c572c3c, 
+  ADD CONSTRAINT matrix_fkto_study 
+    FOREIGN KEY (study_id)  REFERENCES study(study_id);
+
+
+
+ALTER TABLE ONLY matrix 
+  DROP CONSTRAINT fk87208bc145534a9c, 
+  ADD CONSTRAINT matrix_fkto_charset 
+    FOREIGN KEY (charset_id)  REFERENCES charset(charset_id);
+
+
+
+ALTER TABLE ONLY matrix 
+  DROP CONSTRAINT fk87208bc1491db20e, 
+  ADD CONSTRAINT matrix_fkto_codonpositionset 
+    FOREIGN KEY (codonpositionset_id)  REFERENCES codonpositionset(codonpositionset_id);
+
+
+
+ALTER TABLE ONLY matrix 
+  DROP CONSTRAINT fk87208bc14ce484ae, 
+  ADD CONSTRAINT matrix_fkto_matrixdatatype 
+    FOREIGN KEY (matrixdatatype_id)  REFERENCES matrixdatatype(matrixdatatype_id);
+
+
+
+ALTER TABLE ONLY matrix 
+  DROP CONSTRAINT fk87208bc14d737e46, 
+  ADD CONSTRAINT matrix_fkto_typeset 
+    FOREIGN KEY (typeset_id)  REFERENCES typeset(typeset_id);
+
+
+
+ALTER TABLE ONLY matrix 
+  DROP CONSTRAINT fk87208bc1684f6406, 
+  ADD CONSTRAINT matrix_fkto_ancstateset 
+    FOREIGN KEY (ancstateset_id)  REFERENCES ancstateset(ancstateset_id);
+
+
+
+ALTER TABLE ONLY matrix 
+  DROP CONSTRAINT fk87208bc191bd3c8e, 
+  ADD CONSTRAINT matrix_fkto_matrixkind 
+    FOREIGN KEY (matrixkind_id)  REFERENCES matrixkind(matrixkind_id);
+
+
+
+ALTER TABLE ONLY matrix 
+  DROP CONSTRAINT fk87208bc1e310471d, 
+  ADD CONSTRAINT matrix_fkto_taxonlabelset 
+    FOREIGN KEY (taxonlabelset_id)  REFERENCES taxonlabelset(taxonlabelset_id);
+
+
+
+ALTER TABLE ONLY matrix 
+  DROP CONSTRAINT fk87208bc1e9b425a6, 
+  ADD CONSTRAINT matrix_fkto_charweightset 
+    FOREIGN KEY (charweightset_id)  REFERENCES charweightset(charweightset_id);
+
+
+
+ALTER TABLE ONLY ancstateset 
+  DROP CONSTRAINT fk879339e7a414944f, 
+  ADD CONSTRAINT ancstateset_fkto_matrix 
+    FOREIGN KEY (matrix_id)  REFERENCES matrix(matrix_id);
+
+
+
+ALTER TABLE ONLY stepmatrixelement 
+  DROP CONSTRAINT fk891100af6e972a88, 
+  ADD CONSTRAINT stepmatrixelement_fkto_state1 
+    FOREIGN KEY (state1_id)  REFERENCES discretecharstate(discretecharstate_id);
+
+
+
+ALTER TABLE ONLY stepmatrixelement 
+  DROP CONSTRAINT fk891100af6e979ee7, 
+  ADD CONSTRAINT stepmatrixelement_fkto_state2 
+    FOREIGN KEY (state2_id)  REFERENCES discretecharstate(discretecharstate_id);
+
+
+
+ALTER TABLE ONLY stepmatrixelement 
+  DROP CONSTRAINT fk891100af9e8198e, 
+  ADD CONSTRAINT stepmatrixelement_fkto_stepmatrix 
+    FOREIGN KEY (stepmatrix_id)  REFERENCES usertype(usertype_id);
+
+
+
+ALTER TABLE ONLY analyzeddata 
+  DROP CONSTRAINT fk8c9618424ba97f78, 
+  ADD CONSTRAINT analyzeddata_fkto_analysisstep 
+    FOREIGN KEY (analysisstep_id)  REFERENCES analysisstep(analysisstep_id);
+
+
+
+ALTER TABLE ONLY analyzeddata 
+  DROP CONSTRAINT fk8c96184255961aee, 
+  ADD CONSTRAINT analyzeddata_fkto_matrix 
+    FOREIGN KEY (matrix_id)  REFERENCES matrix(matrix_id);
+
+
+
+ALTER TABLE ONLY analyzeddata 
+  DROP CONSTRAINT fk8c961842b710cb23, 
+  ADD CONSTRAINT analyzeddata_fkto_phylotree 
+    FOREIGN KEY (phylotree_id)  REFERENCES phylotree(phylotree_id);
+
+
+
+ALTER TABLE ONLY charweight 
+  DROP CONSTRAINT fk8cc7694ee9b425a6, 
+  ADD CONSTRAINT charweight_fkto_charweightset 
+    FOREIGN KEY (charweightset_id)  REFERENCES charweightset(charweightset_id);
+
+
+
+ALTER TABLE ONLY distancematrixelement 
+  DROP CONSTRAINT fk92d3dde61f08bae7, 
+  ADD CONSTRAINT distancematrixelement_fkto_rowlabel 
+    FOREIGN KEY (rowlabel_id)  REFERENCES taxonlabel(taxonlabel_id);
+
+
+
+ALTER TABLE ONLY distancematrixelement 
+  DROP CONSTRAINT fk92d3dde64b8ef343, 
+  ADD CONSTRAINT distancematrixelement_fkto_matrix 
+    FOREIGN KEY (matrix_id)  REFERENCES matrix(matrix_id);
+
+
+
+ALTER TABLE ONLY distancematrixelement 
+  DROP CONSTRAINT fk92d3dde6c1429863, 
+  ADD CONSTRAINT distancematrixelement_fkto_columnlabel 
+    FOREIGN KEY (columnlabel_id)  REFERENCES taxonlabel(taxonlabel_id);
+
+
+
+ALTER TABLE ONLY sub_treeblock
+  DROP CONSTRAINT fk94d508308dfe4858, 
+  ADD CONSTRAINT  sub_treeblock_fkto_submission
+    FOREIGN KEY (submission_id) REFERENCES submission(submission_id);
+
+
+
+ALTER TABLE ONLY sub_treeblock 
+  DROP CONSTRAINT fk94d50830bfd107c3, 
+  ADD CONSTRAINT sub_treeblock_fkto_treeblock 
+    FOREIGN KEY (treeblock_id) REFERENCES treeblock(treeblock_id);
+
+
+
+ALTER TABLE ONLY charweightset 
+  DROP CONSTRAINT fk99b8ac34a414944f, 
+  ADD CONSTRAINT charweightset_fkto_matrix 
+    FOREIGN KEY (matrix_id)  REFERENCES matrix(matrix_id);
+
+
+
+ALTER TABLE ONLY matrixcolumn 
+  DROP CONSTRAINT fk9b0be57a414944f, 
+  ADD CONSTRAINT matrixcolumn_fkto_matrix 
+    FOREIGN KEY (matrix_id)  REFERENCES matrix(matrix_id);
+
+
+
+ALTER TABLE ONLY matrixcolumn 
+  DROP CONSTRAINT fk9b0be57bed40086, 
+  ADD CONSTRAINT matrixcolumn_fkto_stateformat 
+    FOREIGN KEY (stateformat_id)  REFERENCES stateformat(stateformat_id);
+
+
+
+ALTER TABLE ONLY matrixcolumn 
+  DROP CONSTRAINT fk9b0be57f5deca46, 
+  ADD CONSTRAINT matrixcolumn_fkto_phylochar 
+    FOREIGN KEY (phylochar_id)  REFERENCES phylochar(phylochar_id);
+
+
+
+ALTER TABLE ONLY submission 
+  DROP CONSTRAINT fka120274c3c572c3c, 
+  ADD CONSTRAINT submission_fkto_study 
+    FOREIGN KEY (study_id)  REFERENCES study(study_id);
+
+
+
+ALTER TABLE ONLY submission 
+  DROP CONSTRAINT fka120274cbef300b2, 
+  ADD CONSTRAINT submission_fkto_user 
+    FOREIGN KEY (user_id)  REFERENCES "user"(user_id);
+
+
+
+ALTER TABLE ONLY coderecord_colrange 
+  DROP CONSTRAINT fka3e6c61e79a523e6, 
+  ADD CONSTRAINT coderecord_colrange_fkto_columnrange 
+    FOREIGN KEY (columnrange_id) REFERENCES columnrange(columnrange_id);
+
+
+
+ALTER TABLE ONLY coderecord_colrange 
+  DROP CONSTRAINT fka3e6c61edc51c546, 
+  ADD CONSTRAINT coderecord_colrange_fkto_geneticcoderecord 
+    FOREIGN KEY (geneticcoderecord_id) REFERENCES geneticcoderecord(geneticcoderecord_id);
+
+
+
+ALTER TABLE ONLY treeblock 
+  DROP CONSTRAINT fka826f38fe310471d, 
+  ADD CONSTRAINT treeblock_fkto_taxonlabelset 
+    FOREIGN KEY (taxonlabelset_id)  REFERENCES taxonlabelset(taxonlabelset_id);
+
+
+
+ALTER TABLE ONLY contancstate_value 
+  DROP CONSTRAINT fka84b8c3fd450d2bd, 
+  ADD CONSTRAINT contancstate_value_fkto_ancstate 
+    FOREIGN KEY (ancstate_id) REFERENCES ancestralstate(ancestralstate_id);
+
+
+
+ALTER TABLE ONLY treegroup 
+  DROP CONSTRAINT fka870258180cbd223, 
+  ADD CONSTRAINT treegroup_fkto_treepartition 
+    FOREIGN KEY (treepartition_id)  REFERENCES treepartition(treepartition_id);
+
+
+
+ALTER TABLE ONLY chargroup_colrange 
+  DROP CONSTRAINT fkb2d8c29379a523e6, 
+  ADD CONSTRAINT chargroup_colrange_fkto_columnrange 
+    FOREIGN KEY (columnrange_id) REFERENCES columnrange(columnrange_id);
+
+
+
+ALTER TABLE ONLY chargroup_colrange 
+  DROP CONSTRAINT fkb2d8c293971fffa6, 
+  ADD CONSTRAINT chargroup_colrange_fkto_chargroup 
+    FOREIGN KEY (chargroup_id) REFERENCES chargroup(chargroup_id);
+
+
+
+ALTER TABLE ONLY algorithm 
+  DROP CONSTRAINT fkb388c44f92e6a38e, 
+  ADD CONSTRAINT algorithm_fkto_usertype 
+    FOREIGN KEY (usertype_id)  REFERENCES usertype(usertype_id);
+
+
+
+ALTER TABLE ONLY algorithm 
+  DROP CONSTRAINT fkb388c44f9698d32e, 
+  ADD CONSTRAINT algorithm_fkto_polytcount 
+    FOREIGN KEY (polytcount_id)  REFERENCES polytcount(polytcount_id);
+
+
+
+ALTER TABLE ONLY algorithm 
+  DROP CONSTRAINT fkb388c44fc6e814e6, 
+  ADD CONSTRAINT algorithm_fkto_gapmode 
+    FOREIGN KEY (gapmode_id)  REFERENCES gapmode(gapmode_id);
+
+
+
+ALTER TABLE ONLY matrixcolumn_itemdefinition 
+  DROP CONSTRAINT fkb556f8e405a7cee, 
+  ADD CONSTRAINT matrixcolumn_itemdefinition_fkto_itemdefinition 
+    FOREIGN KEY (itemdefinition_id) REFERENCES itemdefinition(itemdefinition_id);
+
+
+
+ALTER TABLE ONLY matrixcolumn_itemdefinition 
+  DROP CONSTRAINT fkb556f8e8b2e884e, 
+  ADD CONSTRAINT matrixcolumn_itemdefinition_fkto_matrixcolumn 
+    FOREIGN KEY (matrixcolumn_id) REFERENCES matrixcolumn(matrixcolumn_id);
+
+
+
+ALTER TABLE ONLY taxonlink 
+  DROP CONSTRAINT fkc102d9a41de2fcdd, 
+  ADD CONSTRAINT taxonlink_fkto_taxon 
+    FOREIGN KEY (taxon_id)  REFERENCES taxon(taxon_id);
+
+
+
+ALTER TABLE ONLY taxonlink 
+  DROP CONSTRAINT fkc102d9a43bf5f2f7, 
+  ADD CONSTRAINT taxonlink_fkto_taxonauthority 
+    FOREIGN KEY (taxonauthority_id)  REFERENCES taxonauthority(taxonauthority_id);
+
+
+
+ALTER TABLE ONLY codonchar3_colrange 
+  DROP CONSTRAINT fkc2edb736491db20e, 
+  ADD CONSTRAINT codonchar3_colrange_fkto_codonpositionset 
+    FOREIGN KEY (codonpositionset_id) REFERENCES codonpositionset(codonpositionset_id);
+
+
+
+ALTER TABLE ONLY codonchar3_colrange 
+  DROP CONSTRAINT fkc2edb73679a523e6, 
+  ADD CONSTRAINT codonchar3_colrange_fkto_columnrange 
+    FOREIGN KEY (columnrange_id) REFERENCES columnrange(columnrange_id);
+
+
+
+ALTER TABLE ONLY taxonset_taxon 
+  DROP CONSTRAINT fkc656c90313c28457, 
+  ADD CONSTRAINT taxonset_taxon_fkto_taxonset 
+    FOREIGN KEY (taxonset_id) REFERENCES taxonset(taxonset_id);
+
+
+
+ALTER TABLE ONLY taxonset_taxon 
+  DROP CONSTRAINT fkc656c9031de2fcdd, 
+  ADD CONSTRAINT taxonset_taxon_fkto_taxon 
+    FOREIGN KEY (taxon_id) REFERENCES taxon(taxon_id);
+
+
+
+ALTER TABLE ONLY leftchangeset_charstate 
+  DROP CONSTRAINT fkc887720775763eae, 
+  ADD CONSTRAINT leftchangeset_charstate_fkto_statechangeset 
+    FOREIGN KEY (statechangeset_id) REFERENCES statechangeset(statechangeset_id);
+
+
+
+ALTER TABLE ONLY leftchangeset_charstate 
+  DROP CONSTRAINT fkc8877207f4803ce6, 
+  ADD CONSTRAINT leftchangeset_charstate_fkto_discretecharstate 
+    FOREIGN KEY (discretecharstate_id) REFERENCES discretecharstate(discretecharstate_id);
+
+
+
+ALTER TABLE ONLY rightchangeset_charstate 
+  DROP CONSTRAINT fkc8afc9f275763eae, 
+  ADD CONSTRAINT rightchangeset_charstate_fkto_statechangeset 
+    FOREIGN KEY (statechangeset_id) REFERENCES statechangeset(statechangeset_id);
+
+
+
+ALTER TABLE ONLY rightchangeset_charstate 
+  DROP CONSTRAINT fkc8afc9f2f4803ce6, 
+  ADD CONSTRAINT rightchangeset_charstate_fkto_discretecharstate 
+    FOREIGN KEY (discretecharstate_id) REFERENCES discretecharstate(discretecharstate_id);
+
+
+
+ALTER TABLE ONLY codonchar2_colrange 
+  DROP CONSTRAINT fkcea44617491db20e, 
+  ADD CONSTRAINT codonchar2_colrange_fkto_codonpositionset 
+    FOREIGN KEY (codonpositionset_id) REFERENCES codonpositionset(codonpositionset_id);
+
+
+
+ALTER TABLE ONLY codonchar2_colrange 
+  DROP CONSTRAINT fkcea4461779a523e6, 
+  ADD CONSTRAINT codonchar2_colrange_fkto_columnrange 
+    FOREIGN KEY (columnrange_id) REFERENCES columnrange(columnrange_id);
+
+
+
+ALTER TABLE ONLY statemodifier 
+  DROP CONSTRAINT fkd157be48459091e5, 
+  ADD CONSTRAINT statemodifier_fkto_element 
+    FOREIGN KEY (element_id)  REFERENCES matrixelement(matrixelement_id);
+
+
+
+ALTER TABLE ONLY statemodifier 
+  DROP CONSTRAINT fkd157be48bed40086, 
+  ADD CONSTRAINT statemodifier_fkto_stateformat 
+    FOREIGN KEY (stateformat_id)  REFERENCES stateformat(stateformat_id);
+
+
+
+ALTER TABLE ONLY statemodifier 
+  DROP CONSTRAINT fkd157be48f4803ce6, 
+  ADD CONSTRAINT statemodifier_fkto_discretecharstate 
+    FOREIGN KEY (discretecharstate_id)  REFERENCES discretecharstate(discretecharstate_id);
+
+
+
+ALTER TABLE ONLY citation 
+  DROP CONSTRAINT fkd8a7fae74c983658, 
+  ADD CONSTRAINT citation_fkto_citationstatus 
+    FOREIGN KEY (citationstatus_id)  REFERENCES citationstatus(citationstatus_id);
+
+
+
+ALTER TABLE ONLY codonchar1_colrange 
+  DROP CONSTRAINT fkda5ad4f8491db20e, 
+  ADD CONSTRAINT codonchar1_colrange_fkto_codonpositionset 
+    FOREIGN KEY (codonpositionset_id) REFERENCES codonpositionset(codonpositionset_id);
+
+
+
+ALTER TABLE ONLY codonchar1_colrange 
+  DROP CONSTRAINT fkda5ad4f879a523e6, 
+  ADD CONSTRAINT codonchar1_colrange_fkto_columnrange 
+    FOREIGN KEY (columnrange_id) REFERENCES columnrange(columnrange_id);
+
+
+
+ALTER TABLE ONLY charpartition 
+  DROP CONSTRAINT fkdea9f834a414944f, 
+  ADD CONSTRAINT charpartition_fkto_matrix 
+    FOREIGN KEY (matrix_id)  REFERENCES matrix(matrix_id);
+
+
+
+ALTER TABLE ONLY matrixrow 
+  DROP CONSTRAINT fke4688e5963ab9fd7, 
+  ADD CONSTRAINT matrixrow_fkto_taxonlabel 
+    FOREIGN KEY (taxonlabel_id) REFERENCES taxonlabel(taxonlabel_id);
+
+
+
+ALTER TABLE ONLY matrixrow 
+  DROP CONSTRAINT fke4688e59a414944f, 
+  ADD CONSTRAINT matrixrow_fkto_matrix 
+    FOREIGN KEY (matrix_id)  REFERENCES matrix(matrix_id);
+
+
+
+ALTER TABLE ONLY taxonvariant 
+  DROP CONSTRAINT fkee3d127b1de2fcdd, 
+  ADD CONSTRAINT taxonvariant_fkto_taxon 
+    FOREIGN KEY (taxon_id)  REFERENCES taxon(taxon_id);
+
+
+
+ALTER TABLE ONLY rowsegment 
+  DROP CONSTRAINT fkee9ae81963ab9fd7, 
+  ADD CONSTRAINT rowsegment_fkto_taxonlabel 
+    FOREIGN KEY (taxonlabel_id)  REFERENCES taxonlabel(taxonlabel_id);
+
+
+
+ALTER TABLE ONLY rowsegment 
+  DROP CONSTRAINT fkee9ae819e7b3cda6, 
+  ADD CONSTRAINT rowsegment_fkto_matrixrow 
+    FOREIGN KEY (matrixrow_id)  REFERENCES matrixrow(matrixrow_id);
+
+
+
+ALTER TABLE ONLY analysis 
+  DROP CONSTRAINT fkf19622dc3c572c3c, 
+  ADD CONSTRAINT analysis_fkto_study 
+    FOREIGN KEY (study_id)  REFERENCES study(study_id);
+
+
+
+ALTER TABLE ONLY typeset 
+  DROP CONSTRAINT fkf7f2b6c8a414944f, 
+  ADD CONSTRAINT typeset_fkto_matrix 
+    FOREIGN KEY (matrix_id)  REFERENCES matrix(matrix_id);
+
+
+
+ALTER TABLE ONLY treegroup_phylotree
+  DROP CONSTRAINT fkfb7d7045e56a83, 
+  ADD CONSTRAINT  treegroup_phylotree_fkto_treegroup
+    FOREIGN KEY (treegroup_id) REFERENCES treegroup(treegroup_id);
+
+
+
+ALTER TABLE ONLY treegroup_phylotree
+  DROP CONSTRAINT  fkfb7d704b710cb23, 
+  ADD CONSTRAINT  treegroup_phylotree_fkto_phylotree
+    FOREIGN KEY (phylotree_id) REFERENCES phylotree(phylotree_id);
+
+
+
+ALTER TABLE ONLY matrixdatatype
+  DROP CONSTRAINT  matrixdatatype_phylochar_id_fkey, 
+  ADD CONSTRAINT  matrixdatatype_fkto_phylochar
+    FOREIGN KEY (phylochar_id) REFERENCES phylochar(phylochar_id);
+
+
+ALTER TABLE ONLY phylotree
+  DROP CONSTRAINT rootnode_id_fk, 
+  ADD CONSTRAINT phylotree_fk_rootnode
+    FOREIGN KEY (rootnode_id) REFERENCES phylotreenode(phylotreenode_id);
+
+

Added: trunk/treebase-core/db/patches/0002_drop-geospot_id_sequence.sql
===================================================================
--- trunk/treebase-core/db/patches/0002_drop-geospot_id_sequence.sql	                        (rev 0)
+++ trunk/treebase-core/db/patches/0002_drop-geospot_id_sequence.sql	2010-02-16 14:37:32 UTC (rev 510)
@@ -0,0 +1,6 @@
+insert into versionhistory(patchnumber, patchlabel, patchdescription) 
+       values (2, 'drop-geospot_id_sequence', 
+       'Drop geospot_id_sequence'); 
+
+drop sequence geospot_id_sequence; 
+

Added: trunk/treebase-core/db/patches/0003_rename-stepmatrixelement-pk.sql
===================================================================
--- trunk/treebase-core/db/patches/0003_rename-stepmatrixelement-pk.sql	                        (rev 0)
+++ trunk/treebase-core/db/patches/0003_rename-stepmatrixelement-pk.sql	2010-02-16 14:37:32 UTC (rev 510)
@@ -0,0 +1,14 @@
+insert into versionhistory(patchnumber, patchlabel, patchdescription) 
+       values (3, 'rename-stepmatrixelement-pk', 
+       'Correct PK name in stepmatrixelement and make it use its own newly created sequence.'); 
+
+create sequence stepmatrixelement_id_sequence; 
+
+alter table stepmatrixelement
+    rename column discretecharstate_id to stepmatrixelement_id; 
+
+alter table stepmatrixelement
+  alter column stepmatrixelement_id  
+    set default  nextval('stepmatrixelement_id_sequence'); 
+
+

Added: trunk/treebase-core/db/patches/0004_create-indexes.sql
===================================================================
--- trunk/treebase-core/db/patches/0004_create-indexes.sql	                        (rev 0)
+++ trunk/treebase-core/db/patches/0004_create-indexes.sql	2010-02-16 14:37:32 UTC (rev 510)
@@ -0,0 +1,43 @@
+insert into versionhistory(patchnumber, patchlabel, patchdescription) 
+       values (4, 'create-indexes', 
+       'Create several indexes to improve query performance.'); 
+
+
+create index taxon_idx_name 
+  on taxon(name); 
+create index taxonvariant_id_fullname 
+  on taxonvariant(fullname); 
+create index taxonlabel_idx_taxonlabel 
+  on taxonlabel(taxonlabel);
+create index citation_idx_title 
+  on citation(title);
+create index person_idx_lastname 
+  on person(lastname);
+
+create index phylotree_idx_treeblock_id
+  on phylotree(treeblock_id);
+
+create index phylotreenode_idx_phylotree_id 
+  on phylotreenode(phylotree_id);
+create index phylotreenode_idx_taxonlabel_id 
+  on phylotreenode(taxonlabel_id);
+
+create index phylotreenode_idx_parent_id
+  on phylotreenode(parent_id);
+create index phylotreenode_idx_child_id
+  on phylotreenode(child_id);
+create index phylotreenode_idx_sibling_id
+  on phylotreenode(sibling_id);
+
+
+create index treeblock_idx_taxonlabelset_id 
+  on treeblock(taxonlabelset_id); 
+
+create index taxonlabelset_taxonlabel_idx_taxonlabelset_id 
+  on taxonlabelset_taxonlabel(taxonlabelset_id); 
+create index taxonlabelset_taxonlabel_idx_taxonlabel_id 
+  on taxonlabelset_taxonlabel(taxonlabel_id); 
+
+create index taxonlabel_idx_taxonvariant_id 
+  on taxonlabel(taxonvariant_id); 
+ 

Added: trunk/treebase-core/db/snapshots/0000_DATA_before_patches_start.sql
===================================================================
--- trunk/treebase-core/db/snapshots/0000_DATA_before_patches_start.sql	                        (rev 0)
+++ trunk/treebase-core/db/snapshots/0000_DATA_before_patches_start.sql	2010-02-16 14:37:32 UTC (rev 510)
@@ -0,0 +1,236 @@
+-- Populates a DB with "dictionary" data. 
+-- Run this after creating tables, etc., with 0000_SCHEMA_before_patches_start.sql
+
+-- Created 2010-01-27 by VG, 
+-- based on treebase-core/src/main/resources/initTreebase.sql by mjdominus
+--  - Removed COMMIT and DELETE statements. 
+--  - Converted commented-out DB2 RESTART statements on PK columns to working Postgres RESTART statements on sequences.  
+--  - Added (6, 1, 'Retracted') to citationStatus 
+--  - Removed initial data from tables Person, User, Submission, Study
+
+
+insert into citationStatus(citationstatus_id, version, description) values 
+(1, 1, 'In Prep'),
+(2, 1, 'In Review'),
+(3, 1, 'Accepted with Minor Changes'),
+(4, 1, 'In Press'),
+(5, 1, 'Published'),
+(6, 1, 'Retracted');
+alter sequence citationstatus_id_sequence restart with 7;
+
+
+insert into gapMode(gapMode_id, VERSION, Description) values 
+(1, 1, 'Missing'),
+(2, 1, 'NewState');
+alter sequence gapMode_id_sequence restart with 3;
+
+
+insert into itemDefinition(itemDefinition_id, VERSION, Description) values 
+(1, 1, 'Min'),
+(2, 1, 'Max'),
+(3, 1, 'Median'),
+(4, 1, 'Avg'),
+(5, 1, 'Variance'),
+(6, 1, 'StdError'),
+(7, 1, 'SampleSize'),
+(8, 1, 'States');
+alter sequence itemDefinition_id_sequence restart with 9;
+
+
+
+insert into PolyTCount(polyTCount_id, VERSION, Description) values 
+(1, 1, 'MinSteps'),
+(2, 1, 'MaxSteps');
+alter sequence polyTCount_id_sequence restart with 3;
+
+
+insert into stateFormat(stateFormat_id, VERSION, Description) values 
+(1, 1, 'Count'),
+(2, 1, 'Frequency'), 
+(3, 1, 'StatePresent'),
+(4, 1, 'Individuals');
+alter sequence stateformat_id_sequence restart with 5;
+
+
+INSERT INTO TREEKIND(TREEKIND_ID, VERSION, DESCRIPTION) values 
+(1, 1, 'Species Tree'),
+(2, 1, 'Gene Tree'),
+(3, 1, 'Language Tree'),
+(4, 1, 'Area Tree'),
+(5, 1, 'Barcode Tree'),
+(6, 1, 'Object Classification Tree');
+alter sequence treekind_id_sequence restart with 7;
+
+
+INSERT INTO TREETYPE(TREETYPE_ID, VERSION, DESCRIPTION) values 
+(1, 1, 'Single'),
+(2, 1, 'Consensus'),
+(3, 1, 'SuperTree');
+alter sequence treetype_id_sequence restart with 4;
+
+
+INSERT INTO TREEQUALITY(TREEQUALITY_ID, VERSION, DESCRIPTION) values 
+(1, 1, 'Unrated'),
+(2, 1, 'Preferred Tree'),
+(3, 1, 'Alternative Tree'),
+(4, 1, 'Suboptimal Tree');
+alter sequence treequality_id_sequence restart with 5;
+
+
+INSERT INTO MATRIXKIND(MATRIXKIND_ID, VERSION, DESCRIPTION) values 
+(1, 1, 'Unspecified'),
+(2, 1, 'Allozyme'),
+(3, 1, 'Amino Acid'),
+(4, 1, 'Behavior'),
+(5, 1, 'Combination'),
+(6, 1, 'Karyotype'),
+(7, 1, 'Matrix Representation'),
+(8, 1, 'Morphological'),
+(9, 1, 'Nucleic Acid'),
+(10, 1, 'Restriction Site'),
+(11, 1, 'Secondary Chemistry');
+alter sequence matrixkind_id_sequence restart with 12;
+
+
+-- DiscreteCharState and MatrixDataType depend on PhyloChar, so we must handle them together
+--
+-- If you update the PHYLOCHAR section, be sure to adjust
+-- org.cipres.treebase.Constants.MIN_INTERESTING_PHYLOCHAR_ID 
+-- also.  20090317 MJD
+--
+-- BEGIN DiscreteCharState, MatrixDataTpe, and PhyloChar
+
+insert into phylochar (TYPE, PHYLOCHAR_ID, VERSION, DESCRIPTION) values
+('D', 1, 1, 'Standard'),
+('D', 2, 1, 'DNA'),
+('D', 3, 1, 'RNA'),
+('D', 4, 1, 'Nucleotide'),
+('D', 5, 1, 'Protein');
+--('C', 6, 1, 'Continuous');
+-- 1:Standard, 2:DNA, 3:RNA, 4:Nucleotide, 5:Protein, 6:Continuous
+alter sequence phylochar_id_sequence restart with 7;
+
+
+insert into MatrixDataType (MatrixDataType_ID, Version, PhyloChar_ID, Description) values
+(1, 1, null, 'Standard'),
+(2, 1, 2, 'DNA'),
+(3, 1, 3, 'RNA'),
+(4, 1, 4, 'Nucleotide'),
+(5, 1, 5, 'Protein'),
+(6, 1, null, 'Continuous'),
+(7, 1, null, 'Distance');
+alter sequence MatrixDataType_id_sequence restart with 10;
+
+
+-- DNA symbols
+insert into DiscreteCharState (DiscreteCharState_ID, Version, Description, PhyloChar_ID, StateSet_ID, AncestralState_ID) values
+(1, 1, 'A', 2, null, null),
+(2, 1, 'C', 2, null, null),
+(3, 1, 'G', 2, null, null),
+(4, 1, 'T', 2, null, null),
+(5, 1, '?', 2, null, null),
+(6, 1, 'R', 2, null, null),
+(7, 1, 'Y', 2, null, null),
+(8, 1, 'M', 2, null, null),
+(9, 1, 'K', 2, null, null),
+(10, 1, 'S', 2, null, null),
+(11, 1, 'W', 2, null, null),
+(12, 1, 'H', 2, null, null),
+(13, 1, 'B', 2, null, null),
+(14, 1, 'V', 2, null, null),
+(15, 1, 'D', 2, null, null),
+(16, 1, 'N', 2, null, null);
+
+
+-- RNA symbols
+insert into DiscreteCharState (DiscreteCharState_ID, Version, Description, PhyloChar_ID, StateSet_ID, AncestralState_ID) values
+(21, 1, 'A', 3, null, null),
+(22, 1, 'C', 3, null, null),
+(23, 1, 'G', 3, null, null),
+(24, 1, 'U', 3, null, null),
+(25, 1, '?', 3, null, null),
+(26, 1, 'R', 3, null, null),
+(27, 1, 'Y', 3, null, null),
+(28, 1, 'M', 3, null, null),
+(29, 1, 'K', 3, null, null),
+(30, 1, 'S', 3, null, null),
+(31, 1, 'W', 3, null, null),
+(32, 1, 'H', 3, null, null),
+(33, 1, 'B', 3, null, null),
+(34, 1, 'V', 3, null, null),
+(35, 1, 'D', 3, null, null),
+(36, 1, 'N', 3, null, null);
+
+
+-- Nucleotide symbols
+insert into DiscreteCharState (DiscreteCharState_ID, Version, Description, PhyloChar_ID, StateSet_ID, AncestralState_ID) values
+(41, 1, 'A', 4, null, null),
+(42, 1, 'C', 4, null, null),
+(43, 1, 'G', 4, null, null),
+(44, 1, 'T', 4, null, null),
+(45, 1, 'U', 4, null, null),
+(46, 1, '?', 4, null, null),
+(47, 1, 'R', 4, null, null),
+(48, 1, 'Y', 4, null, null),
+(49, 1, 'M', 4, null, null),
+(50, 1, 'K', 4, null, null),
+(51, 1, 'S', 4, null, null),
+(52, 1, 'W', 4, null, null),
+(53, 1, 'H', 4, null, null),
+(54, 1, 'B', 4, null, null),
+(55, 1, 'V', 4, null, null),
+(56, 1, 'D', 4, null, null),
+(57, 1, 'N', 4, null, null);
+
+
+-- protein symbols
+insert into DiscreteCharState (DiscreteCharState_ID, Version, Description, PhyloChar_ID, StateSet_ID, AncestralState_ID) values
+(81, 1, 'A', 5, null, null),
+(82, 1, 'C', 5, null, null),
+(83, 1, 'D', 5, null, null),
+(84, 1, 'E', 5, null, null),
+(85, 1, 'F', 5, null, null),
+(86, 1, 'G', 5, null, null),
+(87, 1, 'H', 5, null, null),
+(88, 1, 'I', 5, null, null),
+(89, 1, 'K', 5, null, null),
+(90, 1, 'L', 5, null, null),
+(91, 1, 'M', 5, null, null),
+(92, 1, 'N', 5, null, null),
+(93, 1, 'P', 5, null, null),
+(94, 1, 'Q', 5, null, null),
+(95, 1, 'R', 5, null, null),
+(96, 1, 'S', 5, null, null),
+(97, 1, 'T', 5, null, null),
+(98, 1, 'V', 5, null, null),
+(99, 1, 'W', 5, null, null),
+(100, 1, 'Y', 5, null, null),
+(101, 1, '*', 5, null, null),
+(102, 1, '?', 5, null, null),
+(103, 1, 'X', 5, null, null),
+(104, 1, 'B', 5, null, null),
+(105, 1, 'Z', 5, null, null);
+
+alter sequence discretecharstate_id_sequence restart with 120;
+
+
+UPDATE DISCRETECHARSTATE SET SYMBOL=DESCRIPTION
+    WHERE length(DESCRIPTION) = 1;
+
+-- END DiscreteCharState, MatrixDataTpe, and PhyloChar
+
+
+
+insert into userRole(userRole_id, VERSION, AUTHORITY) values 
+	(1, 1, 'Admin'),
+	(2, 1, 'User'),
+	(3, 1, 'Associate Editor');
+alter sequence userrole_id_sequence restart with 4;
+
+
+insert into studyStatus(studystatus_id, version, description) values 
+(1, 1, 'In Progress'),
+(2, 1, 'Ready'),
+(3, 1, 'Published');
+alter sequence studystatus_id_sequence restart with 4;
+

Added: trunk/treebase-core/db/snapshots/0000_SCHEMA_before_patches_start.sql
===================================================================
--- trunk/treebase-core/db/snapshots/0000_SCHEMA_before_patches_start.sql	                        (rev 0)
+++ trunk/treebase-core/db/snapshots/0000_SCHEMA_before_patches_start.sql	2010-02-16 14:37:32 UTC (rev 510)
@@ -0,0 +1,4181 @@
+-- Initial Treebase schema, just prior to rolling out schema patching. 
+-- Modified by VG from this 2010-02-15 dump: 
+-- pg_dump -h treebase-dev.nescent.org -U treebase_app --format=p --no-owner --no-privileges --schema-only  treebasestage > 0000_SCHEMA_before_patches_start.sql
+--  - Removed CREATE PROCEDURAL LANGUAGE plpgsql;
+--  - Removed functions: pg_grant, pg_owner, pg_revoke
+
+SET statement_timeout = 0;
+SET client_encoding = 'UTF8';
+SET standard_conforming_strings = off;
+SET check_function_bodies = false;
+SET client_min_messages = warning;
+SET escape_string_warning = off;
+
+SET search_path = public, pg_catalog;
+
+
+--
+-- Name: algorithm_id_sequence; Type: SEQUENCE; Schema: public; Owner: -
+--
+
+CREATE SEQUENCE algorithm_id_sequence
+    START WITH 1
+    INCREMENT BY 1
+    NO MAXVALUE
+    NO MINVALUE
+    CACHE 1;
+
+
+SET default_tablespace = '';
+
+SET default_with_oids = false;
+
+--
+-- Name: algorithm; Type: TABLE; Schema: public; Owner: -; Tablespace: 
+--
+
+CREATE TABLE algorithm (
+    type character(1) NOT NULL,
+    algorithm_id bigint DEFAULT nextval('algorithm_id_sequence'::regclass) NOT NULL,
+    version integer,
+    description character varying(2000),
+    propertyname character varying(255),
+    propertyvalue character varying(255),
+    usertype_id bigint,
+    gapmode_id bigint,
+    polytcount_id bigint
+);
+
+
+--
+-- Name: analysis_id_sequence; Type: SEQUENCE; Schema: public; Owner: -
+--
+
+CREATE SEQUENCE analysis_id_sequence
+    START WITH 1
+    INCREMENT BY 1
+    NO MAXVALUE
+    NO MINVALUE
+    CACHE 1;
+
+
+--
+-- Name: analysis; Type: TABLE; Schema: public; Owner: -; Tablespace: 
+--
+
+CREATE TABLE analysis (
+    analysis_id bigint DEFAULT nextval('analysis_id_sequence'::regclass) NOT NULL,
+    version integer,
+    name character varying(255),
+    notes character varying(2000),
+    validated boolean,
+    study_id bigint,
+    analysis_order integer
+);
+
+
+--
+-- Name: analysisstep_id_sequence; Type: SEQUENCE; Schema: public; Owner: -
+--
+
+CREATE SEQUENCE analysisstep_id_sequence
+    START WITH 1
+    INCREMENT BY 1
+    NO MAXVALUE
+    NO MINVALUE
+    CACHE 1;
+
+
+--
+-- Name: analysisstep; Type: TABLE; Schema: public; Owner: -; Tablespace: 
+--
+
+CREATE TABLE analysisstep (
+    analysisstep_id bigint DEFAULT nextval('analysisstep_id_sequence'::regclass) NOT NULL,
+    version integer,
+    commands character varying(2000),
+    name character varying(255),
+    notes character varying(2000),
+    algorithm_id bigint,
+    analysis_id bigint,
+    software_id bigint,
+    step_order integer
+);
+
+
+--
+-- Name: analyzeddata_id_sequence; Type: SEQUENCE; Schema: public; Owner: -
+--
+
+CREATE SEQUENCE analyzeddata_id_sequence
+    START WITH 1
+    INCREMENT BY 1
+    NO MAXVALUE
+    NO MINVALUE
+    CACHE 1;
+
+
+--
+-- Name: analyzeddata; Type: TABLE; Schema: public; Owner: -; Tablespace: 
+--
+
+CREATE TABLE analyzeddata (
+    type character(1) NOT NULL,
+    analyzeddata_id bigint DEFAULT nextval('analyzeddata_id_sequence'::regclass) NOT NULL,
+    version integer,
+    input boolean,
+    notes character varying(2000),
+    treelength integer,
+    analysisstep_id bigint NOT NULL,
+    matrix_id bigint,
+    phylotree_id bigint
+);
+
+
+--
+-- Name: ancestralstate_id_sequence; Type: SEQUENCE; Schema: public; Owner: -
+--
+
+CREATE SEQUENCE ancestralstate_id_sequence
+    START WITH 1
+    INCREMENT BY 1
+    NO MAXVALUE
+    NO MINVALUE
+    CACHE 1;
+
+
+--
+-- Name: ancestralstate; Type: TABLE; Schema: public; Owner: -; Tablespace: 
+--
+
+CREATE TABLE ancestralstate (
+    type character(1) NOT NULL,
+    ancestralstate_id bigint DEFAULT nextval('ancestralstate_id_sequence'::regclass) NOT NULL,
+    version integer,
+    title character varying(255),
+    ancvalue character varying(255),
+    discretecharstate_id bigint,
+    ancstateset_id bigint
+);
+
+
+--
+-- Name: ancstateset_id_sequence; Type: SEQUENCE; Schema: public; Owner: -
+--
+
+CREATE SEQUENCE ancstateset_id_sequence
+    START WITH 1
+    INCREMENT BY 1
+    NO MAXVALUE
+    NO MINVALUE
+    CACHE 1;
+
+
+--
+-- Name: ancstateset; Type: TABLE; Schema: public; Owner: -; Tablespace: 
+--
+
+CREATE TABLE ancstateset (
+    ancstateset_id bigint DEFAULT nextval('ancstateset_id_sequence'::regclass) NOT NULL,
+    version integer,
+    title character varying(255),
+    matrix_id bigint
+);
+
+
+--
+-- Name: chargroup_id_sequence; Type: SEQUENCE; Schema: public; Owner: -
+--
+
+CREATE SEQUENCE chargroup_id_sequence
+    START WITH 1
+    INCREMENT BY 1
+    NO MAXVALUE
+    NO MINVALUE
+    CACHE 1;
+
+
+--
+-- Name: chargroup; Type: TABLE; Schema: public; Owner: -; Tablespace: 
+--
+
+CREATE TABLE chargroup (
+    chargroup_id bigint DEFAULT nextval('chargroup_id_sequence'::regclass) NOT NULL,
+    version integer,
+    title character varying(255),
+    charpartition_id bigint
+);
+
+
+--
+-- Name: chargroup_colrange; Type: TABLE; Schema: public; Owner: -; Tablespace: 
+--
+
+CREATE TABLE chargroup_colrange (
+    chargroup_id bigint NOT NULL,
+    columnrange_id bigint NOT NULL
+);
+
+
+--
+-- Name: charpartition_id_sequence; Type: SEQUENCE; Schema: public; Owner: -
+--
+
+CREATE SEQUENCE charpartition_id_sequence
+    START WITH 1
+    INCREMENT BY 1
+    NO MAXVALUE
+    NO MINVALUE
+    CACHE 1;
+
+
+--
+-- Name: charpartition; Type: TABLE; Schema: public; Owner: -; Tablespace: 
+--
+
+CREATE TABLE charpartition (
+    charpartition_id bigint DEFAULT nextval('charpartition_id_sequence'::regclass) NOT NULL,
+    version integer,
+    title character varying(255),
+    matrix_id bigint
+);
+
+
+--
+-- Name: charset_id_sequence; Type: SEQUENCE; Schema: public; Owner: -
+--
+
+CREATE SEQUENCE charset_id_sequence
+    START WITH 1
+    INCREMENT BY 1
+    NO MAXVALUE
+    NO MINVALUE
+    CACHE 1;
+
+
+--
+-- Name: charset; Type: TABLE; Schema: public; Owner: -; Tablespace: 
+--
+
+CREATE TABLE charset (
+    type character(1) NOT NULL,
+    charset_id bigint DEFAULT nextval('charset_id_sequence'::regclass) NOT NULL,
+    version integer,
+    title character varying(255),
+    matrix_id bigint
+);
+
+
+--
+-- Name: charset_colrange; Type: TABLE; Schema: public; Owner: -; Tablespace: 
+--
+
+CREATE TABLE charset_colrange (
+    charset_id bigint NOT NULL,
+    columnrange_id bigint NOT NULL
+);
+
+
+--
+-- Name: charweight_id_sequence; Type: SEQUENCE; Schema: public; Owner: -
+--
+
+CREATE SEQUENCE charweight_id_sequence
+    START WITH 1
+    INCREMENT BY 1
+    NO MAXVALUE
+    NO MINVALUE
+    CACHE 1;
+
+
+--
+-- Name: charweight; Type: TABLE; Schema: public; Owner: -; Tablespace: 
+--
+
+CREATE TABLE charweight (
+    type character(1) NOT NULL,
+    charweight_id bigint DEFAULT nextval('charweight_id_sequence'::regclass) NOT NULL,
+    version integer,
+    weight integer,
+    realweight double precision,
+    charweightset_id bigint
+);
+
+
+--
+-- Name: charweight_colrange; Type: TABLE; Schema: public; Owner: -; Tablespace: 
+--
+
+CREATE TABLE charweight_colrange (
+    charweight_id bigint NOT NULL,
+    columnrange_id bigint NOT NULL
+);
+
+
+--
+-- Name: charweightset_id_sequence; Type: SEQUENCE; Schema: public; Owner: -
+--
+
+CREATE SEQUENCE charweightset_id_sequence
+    START WITH 1
+    INCREMENT BY 1
+    NO MAXVALUE
+    NO MINVALUE
+    CACHE 1;
+
+
+--
+-- Name: charweightset; Type: TABLE; Schema: public; Owner: -; Tablespace: 
+--
+
+CREATE TABLE charweightset (
+    charweightset_id bigint DEFAULT nextval('charweightset_id_sequence'::regclass) NOT NULL,
+    version integer,
+    title character varying(255),
+    matrix_id bigint
+);
+
+
+--
+-- Name: citation_id_sequence; Type: SEQUENCE; Schema: public; Owner: -
+--
+
+CREATE SEQUENCE citation_id_sequence
+    START WITH 1
+    INCREMENT BY 1
+    NO MAXVALUE
+    NO MINVALUE
+    CACHE 1;
+
+
+--
+-- Name: citation; Type: TABLE; Schema: public; Owner: -; Tablespace: 
+--
+
+CREATE TABLE citation (
+    type character(1) NOT NULL,
+    citation_id bigint DEFAULT nextval('citation_id_sequence'::regclass) NOT NULL,
+    version integer,
+    pmid character varying(255),
+    url character varying(255),
+    abstract character varying(10000),
+    doi character varying(255),
+    keywords character varying(255),
+    pages character varying(255),
+    publishyear integer,
+    published boolean,
+    title character varying(500),
+    issue character varying(255),
+    journal character varying(255),
+    volume character varying(255),
+    isbn character varying(255),
+    booktitle character varying(255),
+    city character varying(255),
+    publisher character varying(255),
+    citationstatus_id bigint
+);
+
+
+--
+-- Name: citation_author; Type: TABLE; Schema: public; Owner: -; Tablespace: 
+--
+
+CREATE TABLE citation_author (
+    citation_id bigint NOT NULL,
+    authors_person_id bigint NOT NULL,
+    author_order integer NOT NULL
+);
+
+
+--
+-- Name: citation_editor; Type: TABLE; Schema: public; Owner: -; Tablespace: 
+--
+
+CREATE TABLE citation_editor (
+    citation_id bigint NOT NULL,
+    editors_person_id bigint NOT NULL,
+    editor_order integer NOT NULL
+);
+
+
+--
+-- Name: citationstatus_id_sequence; Type: SEQUENCE; Schema: public; Owner: -
+--
+
+CREATE SEQUENCE citationstatus_id_sequence
+    START WITH 1
+    INCREMENT BY 1
+    NO MAXVALUE
+    NO MINVALUE
+    CACHE 1;
+
+
+--
+-- Name: citationstatus; Type: TABLE; Schema: public; Owner: -; Tablespace: 
+--
+
+CREATE TABLE citationstatus (
+    citationstatus_id bigint DEFAULT nextval('citationstatus_id_sequence'::regclass) NOT NULL,
+    version integer,
+    description character varying(50)
+);
+
+
+--
+-- Name: coderecord_colrange; Type: TABLE; Schema: public; Owner: -; Tablespace: 
+--
+
+CREATE TABLE coderecord_colrange (
+    geneticcoderecord_id bigint NOT NULL,
+    columnrange_id bigint NOT NULL
+);
+
+
+--
+-- Name: codonchar1_colrange; Type: TABLE; Schema: public; Owner: -; Tablespace: 
+--
+
+CREATE TABLE codonchar1_colrange (
+    codonpositionset_id bigint NOT NULL,
+    columnrange_id bigint NOT NULL
+);
+
+
+--
+-- Name: codonchar2_colrange; Type: TABLE; Schema: public; Owner: -; Tablespace: 
+--
+
+CREATE TABLE codonchar2_colrange (
+    codonpositionset_id bigint NOT NULL,
+    columnrange_id bigint NOT NULL
+);
+
+
+--
+-- Name: codonchar3_colrange; Type: TABLE; Schema: public; Owner: -; Tablespace: 
+--
+
+CREATE TABLE codonchar3_colrange (
+    codonpositionset_id bigint NOT NULL,
+    columnrange_id bigint NOT NULL
+);
+
+
+--
+-- Name: codonnoncoding_colrange; Type: TABLE; Schema: public; Owner: -; Tablespace: 
+--
+
+CREATE TABLE codonnoncoding_colrange (
+    codonpositionset_id bigint NOT NULL,
+    columnrange_id bigint NOT NULL
+);
+
+
+--
+-- Name: codonpositionset_id_sequence; Type: SEQUENCE; Schema: public; Owner: -
+--
+
+CREATE SEQUENCE codonpositionset_id_sequence
+    START WITH 1
+    INCREMENT BY 1
+    NO MAXVALUE
+    NO MINVALUE
+    CACHE 1;
+
+
+--
+-- Name: codonpositionset; Type: TABLE; Schema: public; Owner: -; Tablespace: 
+--
+
+CREATE TABLE codonpositionset (
+    codonpositionset_id bigint DEFAULT nextval('codonpositionset_id_sequence'::regclass) NOT NULL,
+    version integer,
+    title character varying(255),
+    matrix_id bigint
+);
+
+
+--
+-- Name: columnrange_id_sequence; Type: SEQUENCE; Schema: public; Owner: -
+--
+
+CREATE SEQUENCE columnrange_id_sequence
+    START WITH 1
+    INCREMENT BY 1
+    NO MAXVALUE
+    NO MINVALUE
+    CACHE 1;
+
+
+--
+-- Name: columnrange; Type: TABLE; Schema: public; Owner: -; Tablespace: 
+--
+
+CREATE TABLE columnrange (
+    columnrange_id bigint DEFAULT nextval('columnrange_id_sequence'::regclass) NOT NULL,
+    version integer,
+    endcolindex integer,
+    repeatinterval integer,
+    startcolindex integer
+);
+
+
+--
+-- Name: compound_element; Type: TABLE; Schema: public; Owner: -; Tablespace: 
+--
+
+CREATE TABLE compound_element (
+    compound_id bigint NOT NULL,
+    element_id bigint NOT NULL
+);
+
+
+--
+-- Name: contancstate_value; Type: TABLE; Schema: public; Owner: -; Tablespace: 
+--
+
+CREATE TABLE contancstate_value (
+    ancstate_id bigint NOT NULL,
+    element character varying(255)
+);
+
+
+--
+-- Name: cstreenode_id_sequence; Type: SEQUENCE; Schema: public; Owner: -
+--
+
+CREATE SEQUENCE cstreenode_id_sequence
+    START WITH 1
+    INCREMENT BY 1
+    NO MAXVALUE
+    NO MINVALUE
+    CACHE 1;
+
+
+--
+-- Name: cstreenode; Type: TABLE; Schema: public; Owner: -; Tablespace: 
+--
+
+CREATE TABLE cstreenode (
+    cstreenode_id bigint DEFAULT nextval('cstreenode_id_sequence'::regclass) NOT NULL,
+    version integer,
+    discretecharstate_id bigint,
+    parentnode_id bigint,
+    cstree_id bigint NOT NULL
+);
+
+
+--
+-- Name: discretecharstate_id_sequence; Type: SEQUENCE; Schema: public; Owner: -
+--
+
+CREATE SEQUENCE discretecharstate_id_sequence
+    START WITH 1
+    INCREMENT BY 1
+    NO MAXVALUE
+    NO MINVALUE
+    CACHE 1;
+
+
+--
+-- Name: discretecharstate; Type: TABLE; Schema: public; Owner: -; Tablespace: 
+--
+
+CREATE TABLE discretecharstate (
+    discretecharstate_id bigint DEFAULT nextval('discretecharstate_id_sequence'::regclass) NOT NULL,
+    version integer,
+    description character varying(255),
+    notes character varying(255),
+    symbol character(1),
+    phylochar_id bigint NOT NULL,
+    stateset_id bigint,
+    ancestralstate_id bigint
+);
+
+
+--
+-- Name: distancematrixelement_id_sequence; Type: SEQUENCE; Schema: public; Owner: -
+--
+
+CREATE SEQUENCE distancematrixelement_id_sequence
+    START WITH 1
+    INCREMENT BY 1
+    NO MAXVALUE
+    NO MINVALUE
+    CACHE 1;
+
+
+--
+-- Name: distancematrixelement; Type: TABLE; Schema: public; Owner: -; Tablespace: 
+--
+
+CREATE TABLE distancematrixelement (
+    distancematrixelement_id bigint DEFAULT nextval('distancematrixelement_id_sequence'::regclass) NOT NULL,
+    version integer,
+    distance double precision,
+    columnlabel_id bigint,
+    matrix_id bigint NOT NULL,
+    rowlabel_id bigint NOT NULL
+);
+
+
+--
+-- Name: gapmode_id_sequence; Type: SEQUENCE; Schema: public; Owner: -
+--
+
+CREATE SEQUENCE gapmode_id_sequence
+    START WITH 1
+    INCREMENT BY 1
+    NO MAXVALUE
+    NO MINVALUE
+    CACHE 1;
+
+
+--
+-- Name: gapmode; Type: TABLE; Schema: public; Owner: -; Tablespace: 
+--
+
+CREATE TABLE gapmode (
+    gapmode_id bigint DEFAULT nextval('gapmode_id_sequence'::regclass) NOT NULL,
+    version integer,
+    description character varying(255)
+);
+
+
+--
+-- Name: geneticcode_id_sequence; Type: SEQUENCE; Schema: public; Owner: -
+--
+
+CREATE SEQUENCE geneticcode_id_sequence
+    START WITH 1
+    INCREMENT BY 1
+    NO MAXVALUE
+    NO MINVALUE
+    CACHE 1;
+
+
+--
+-- Name: geneticcode; Type: TABLE; Schema: public; Owner: -; Tablespace: 
+--
+
+CREATE TABLE geneticcode (
+    geneticcode_id bigint DEFAULT nextval('geneticcode_id_sequence'::regclass) NOT NULL,
+    version integer,
+    codedescription character varying(1000),
+    codeorder character varying(255),
+    extensions character varying(255),
+    nucorder character varying(255),
+    predefined boolean,
+    title character varying(255)
+);
+
+
+--
+-- Name: geneticcodereco...
 
[truncated message content]