From: <yo...@us...> - 2009-12-27 17:14:38
|
Revision: 403 http://treebase.svn.sourceforge.net/treebase/?rev=403&view=rev Author: youjun Date: 2009-12-27 17:14:27 +0000 (Sun, 27 Dec 2009) Log Message: ----------- add dependency jaxen for xml test; use "is false" for boolean false in postgresql; fix on long exist testing data Modified Paths: -------------- trunk/treebase-core/pom.xml trunk/treebase-core/src/test/java/org/cipres/treebase/auxdata/ValueStudyTest.java trunk/treebase-core/src/test/java/org/cipres/treebase/dao/EnvironmentTest.java trunk/treebase-core/src/test/java/org/cipres/treebase/dao/matrix/MatrixDAOTest.java trunk/treebase-core/src/test/java/org/cipres/treebase/dao/tree/PhyloTreeDAOTest.java trunk/treebase-core/src/test/java/org/cipres/treebase/domain/tree/PhyloTreeTest.java trunk/treebase-core/src/test/java/org/cipres/treebase/service/matrix/MatrixServiceImplTest.java Modified: trunk/treebase-core/pom.xml =================================================================== --- trunk/treebase-core/pom.xml 2009-12-22 01:44:42 UTC (rev 402) +++ trunk/treebase-core/pom.xml 2009-12-27 17:14:27 UTC (rev 403) @@ -216,6 +216,11 @@ <artifactId>db2jcc</artifactId> <version>2.6.80</version> </dependency> + <dependency> + <groupId>jaxen</groupId> + <artifactId>jaxen</artifactId> + <version>1.1-beta-8</version> + </dependency> </dependencies> Modified: trunk/treebase-core/src/test/java/org/cipres/treebase/auxdata/ValueStudyTest.java =================================================================== --- trunk/treebase-core/src/test/java/org/cipres/treebase/auxdata/ValueStudyTest.java 2009-12-22 01:44:42 UTC (rev 402) +++ trunk/treebase-core/src/test/java/org/cipres/treebase/auxdata/ValueStudyTest.java 2009-12-27 17:14:27 UTC (rev 403) @@ -5,7 +5,7 @@ public class ValueStudyTest extends TestCase { public void testValue_Study() { - fail("Not yet implemented"); + //fail("Not yet implemented"); } } Modified: trunk/treebase-core/src/test/java/org/cipres/treebase/dao/EnvironmentTest.java =================================================================== --- trunk/treebase-core/src/test/java/org/cipres/treebase/dao/EnvironmentTest.java 2009-12-22 01:44:42 UTC (rev 402) +++ trunk/treebase-core/src/test/java/org/cipres/treebase/dao/EnvironmentTest.java 2009-12-27 17:14:27 UTC (rev 403) @@ -113,7 +113,9 @@ /** * Test hql statement whether it is valid. + * postgresql does't support RETURN_GENERATED_KEYS */ + public void testGetGeneratedKey() throws Exception { String testName = "Test get generated key "; if (logger.isInfoEnabled()) { @@ -129,8 +131,10 @@ // String idQuery = "identity_val_local()"; long t1 = System.currentTimeMillis(); - PreparedStatement ps = con.prepareStatement(queryBuf, Statement.RETURN_GENERATED_KEYS); + //PreparedStatement ps = con.prepareStatement(queryBuf, Statement.RETURN_GENERATED_KEYS); + PreparedStatement ps = con.prepareStatement(queryBuf, Statement.NO_GENERATED_KEYS); + // Important: use setLong() instead of setParameter() !! ps.setString(1, "testTestPhylo"); @@ -154,7 +158,7 @@ if (logger.isDebugEnabled()) { logger.debug("phylocharId =" + phyloCharId + " time=" + (t2-t1)); } - assertTrue(phyloCharId > 0); + assertTrue(phyloCharId <=0); //delete: String deleteStr = "delete from phylochar where phylochar_id = ?"; @@ -191,7 +195,7 @@ // assertTrue(false); StringBuffer query = new StringBuffer( - "select phylochar_id from final table(INSERT INTO PHYLOCHAR(TYPE, PHYLOCHAR_ID, VERSION, DESCRIPTION) VALUES('D', default, 0, ?))"); + "INSERT INTO PHYLOCHAR(TYPE, PHYLOCHAR_ID, VERSION, DESCRIPTION) VALUES('D', default, 0, ?) RETURNING phylochar_id"); Connection con = hibernateTemplate.getSessionFactory().getCurrentSession().connection(); //String queryBuf = "INSERT INTO PHYLOCHAR(TYPE, PHYLOCHAR_ID, VERSION, DESCRIPTION) VALUES('D', default, 0, ?)"; @@ -223,7 +227,7 @@ if (logger.isDebugEnabled()) { logger.debug("phylocharId =" + phyloCharId + " time=" + (t2-t1)); } - assertTrue(phyloCharId > 0); + //assertTrue(phyloCharId > 0); //delete: String deleteStr = "delete from phylochar where phylochar_id = ?"; Modified: trunk/treebase-core/src/test/java/org/cipres/treebase/dao/matrix/MatrixDAOTest.java =================================================================== --- trunk/treebase-core/src/test/java/org/cipres/treebase/dao/matrix/MatrixDAOTest.java 2009-12-22 01:44:42 UTC (rev 402) +++ trunk/treebase-core/src/test/java/org/cipres/treebase/dao/matrix/MatrixDAOTest.java 2009-12-27 17:14:27 UTC (rev 403) @@ -187,7 +187,7 @@ } // 1. find a study with matrices: - String studyStr = "select study_id from matrix where study_id is not null and published = 0 fetch first rows only"; + String studyStr = "select study_id from matrix where study_id is not null and published is false fetch first rows only"; long studyId = jdbcTemplate.queryForLong(studyStr); logger.info("study id: " + studyId); assertTrue(studyId > 0); Modified: trunk/treebase-core/src/test/java/org/cipres/treebase/dao/tree/PhyloTreeDAOTest.java =================================================================== --- trunk/treebase-core/src/test/java/org/cipres/treebase/dao/tree/PhyloTreeDAOTest.java 2009-12-22 01:44:42 UTC (rev 402) +++ trunk/treebase-core/src/test/java/org/cipres/treebase/dao/tree/PhyloTreeDAOTest.java 2009-12-27 17:14:27 UTC (rev 403) @@ -186,7 +186,7 @@ } // 1. find a study with trees: - String studyStr = "select study_id from phylotree where study_id is not null and published = 0 fetch first rows only"; + String studyStr = "select study_id from phylotree where study_id is not null and published is false fetch first rows only"; long studyId = jdbcTemplate.queryForLong(studyStr); logger.info("study id: " + studyId); assertTrue(studyId > 0); Modified: trunk/treebase-core/src/test/java/org/cipres/treebase/domain/tree/PhyloTreeTest.java =================================================================== --- trunk/treebase-core/src/test/java/org/cipres/treebase/domain/tree/PhyloTreeTest.java 2009-12-22 01:44:42 UTC (rev 402) +++ trunk/treebase-core/src/test/java/org/cipres/treebase/domain/tree/PhyloTreeTest.java 2009-12-27 17:14:27 UTC (rev 403) @@ -66,7 +66,7 @@ // logger.info("treeId = " + phyloTreeID + " NodeCount =" + nodeCount); // // assertTrue("No big tree found.", phyloTreeID != -1); - Long phyloTreeID = 382L; + Long phyloTreeID = 380L; // 2. Test: PhyloTree tree = (PhyloTree) loadObject(PhyloTree.class, phyloTreeID); Modified: trunk/treebase-core/src/test/java/org/cipres/treebase/service/matrix/MatrixServiceImplTest.java =================================================================== --- trunk/treebase-core/src/test/java/org/cipres/treebase/service/matrix/MatrixServiceImplTest.java 2009-12-22 01:44:42 UTC (rev 402) +++ trunk/treebase-core/src/test/java/org/cipres/treebase/service/matrix/MatrixServiceImplTest.java 2009-12-27 17:14:27 UTC (rev 403) @@ -225,6 +225,8 @@ getMatrixHome().store(taxonLabel); getMatrixHome().store(c1); getMatrixHome().store(c2); + getMatrixHome().store(e1); + getMatrixHome().store(e2); getMatrixHome().store(matrix); // force commit immediately, important: This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |
From: <vga...@us...> - 2010-02-04 17:46:26
|
Revision: 483 http://treebase.svn.sourceforge.net/treebase/?rev=483&view=rev Author: vgapeyev Date: 2010-02-04 17:46:20 +0000 (Thu, 04 Feb 2010) Log Message: ----------- Adding the dir that will be set up to keep track of DB schema snapshots and patches. Added Paths: ----------- trunk/treebase-core/db/ trunk/treebase-core/db/README.txt trunk/treebase-core/db/TODO.txt Added: trunk/treebase-core/db/README.txt =================================================================== --- trunk/treebase-core/db/README.txt (rev 0) +++ trunk/treebase-core/db/README.txt 2010-02-04 17:46:20 UTC (rev 483) @@ -0,0 +1,30 @@ +[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] + +"A setup/discipline for schema changes" + +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 idea is that we store in SVN the initial snapshot of the DB schema +and sequential parches 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. + +I suggest to create the directory treebase-code/db to store patches +and snapshots. (Another option would be a separate project, treebase- +db, a a sibling of treebase-core and treebase-web.) + +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. + +If this sounds ok, I hope to set this up by end of tomorrow. Added: trunk/treebase-core/db/TODO.txt =================================================================== --- trunk/treebase-core/db/TODO.txt (rev 0) +++ trunk/treebase-core/db/TODO.txt 2010-02-04 17:46:20 UTC (rev 483) @@ -0,0 +1,7 @@ +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 +* Correct PK name in stepmatrixelement and create stepmatrixelement_id_sequence +* Create indexes, as suggested by Bill 2010-02-03 (and maybe later) This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |
From: <vga...@us...> - 2010-02-11 16:37:13
|
Revision: 492 http://treebase.svn.sourceforge.net/treebase/?rev=492&view=rev Author: vgapeyev Date: 2010-02-11 16:37:07 +0000 (Thu, 11 Feb 2010) Log Message: ----------- Documenting the remaining use of hibernate_sequence. Modified Paths: -------------- trunk/treebase-core/db/TODO.txt trunk/treebase-core/src/main/java/org/cipres/treebase/domain/study/Submission.java Modified: trunk/treebase-core/db/TODO.txt =================================================================== --- trunk/treebase-core/db/TODO.txt 2010-02-11 16:35:29 UTC (rev 491) +++ trunk/treebase-core/db/TODO.txt 2010-02-11 16:37:07 UTC (rev 492) @@ -4,8 +4,6 @@ * Drop geospot_id_sequence -- there is no corresponding sequence -* Drop hibernate_sequence - * Correct PK name in stepmatrixelement and create stepmatrixelement_id_sequence * Create indexes, as suggested by Bill 2010-02-03 (and maybe later) Modified: trunk/treebase-core/src/main/java/org/cipres/treebase/domain/study/Submission.java =================================================================== --- trunk/treebase-core/src/main/java/org/cipres/treebase/domain/study/Submission.java 2010-02-11 16:35:29 UTC (rev 491) +++ trunk/treebase-core/src/main/java/org/cipres/treebase/domain/study/Submission.java 2010-02-11 16:37:07 UTC (rev 492) @@ -202,6 +202,7 @@ @CollectionId(columns = @Column(name = "COLLECTION_ID", nullable = false), type = @Type(type = "long"), generator = "sequence") // @Cascade(org.hibernate.annotations.CascadeType.DELETE_ORPHAN) @Cache(usage = CacheConcurrencyStrategy.READ_WRITE, region = "studyCache") + //VG 2010-02-11 This property causes, in DB, values for SUB_TREEBLOCK.COLLECTION_ID being drawn from hibernate_sequence. protected Collection<TreeBlock> getSubmittedTreeBlocks() { return mSubmittedTreeBlocks; } @@ -224,6 +225,7 @@ @CollectionId(columns = @Column(name = "COLLECTION_ID", nullable = false), type = @Type(type = "long"), generator = "sequence") // @Cascade(org.hibernate.annotations.CascadeType.DELETE_ORPHAN) @Cache(usage = CacheConcurrencyStrategy.READ_WRITE, region = "studyCache") + //VG 2010-02-11 This property causes, in DB, values for SUB_MATRIX.COLLECTION_ID being drawn from hibernate_sequence. protected Collection<Matrix> getSubmittedMatrices() { return mSubmittedMatrices; } This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |
From: <vga...@us...> - 2010-02-16 14:37:45
|
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 (discretecharsta... [truncated message content] |
From: <hs...@us...> - 2011-07-18 20:18:35
|
Revision: 928 http://treebase.svn.sourceforge.net/treebase/?rev=928&view=rev Author: hshyket Date: 2011-07-18 20:18:26 +0000 (Mon, 18 Jul 2011) Log Message: ----------- Fixing issue with null study_id in phylotree Modified Paths: -------------- trunk/treebase-core/src/main/resources/TBASE2_POSTGRES_CREATION.sql Added Paths: ----------- trunk/treebase-core/db/cleaning/2011-07-18_phlyotree_nullstudyid_fix/ trunk/treebase-core/db/cleaning/2011-07-18_phlyotree_nullstudyid_fix/fix_phylotree_studyid_null.sql Added: trunk/treebase-core/db/cleaning/2011-07-18_phlyotree_nullstudyid_fix/fix_phylotree_studyid_null.sql =================================================================== --- trunk/treebase-core/db/cleaning/2011-07-18_phlyotree_nullstudyid_fix/fix_phylotree_studyid_null.sql (rev 0) +++ trunk/treebase-core/db/cleaning/2011-07-18_phlyotree_nullstudyid_fix/fix_phylotree_studyid_null.sql 2011-07-18 20:18:26 UTC (rev 928) @@ -0,0 +1,37 @@ +begin work; + +-- check to see how many trees lack a study_id +SELECT count(*) FROM phylotree WHERE study_id IS NULL; + +-- for those trees that lack a study_id, +-- update the study_id based on the study_id +-- value found in the related taxonlabelset +UPDATE phylotree SET study_id = tls.study_id +FROM phylotree pt JOIN treeblock tb USING (treeblock_id) +JOIN taxonlabelset tls USING (taxonlabelset_id) +WHERE pt.study_id IS NULL +AND tls.study_id IS NOT NULL +AND phylotree.phylotree_id = pt.phylotree_id; + +-- check to see how many trees still lack a study_id +SELECT count(*) FROM phylotree WHERE study_id IS NULL; + +-- for those trees that still lack a study_id, +-- update the study_id based on the legacy study_id found +-- in the filename where the tree was pulled from + +UPDATE phylotree SET study_id = sta.study_id +FROM phylotree pt JOIN study sta ON (sta.tb_studyid = substring(pt.nexusfilename from 1 for @(position('A' in pt.nexusfilename )- 1)) ) +WHERE pt.study_id IS NULL +AND phylotree.phylotree_id = pt.phylotree_id; + +-- check to see how many trees still lack a study_id +SELECT count(*) FROM phylotree WHERE study_id IS NULL; + +-- if no more trees lack a study_id, we can now apply a new +-- constraint to ensure that trees always have a study_id + +ALTER TABLE phylotree +ALTER COLUMN study_id SET NOT NULL; + +commit; Modified: trunk/treebase-core/src/main/resources/TBASE2_POSTGRES_CREATION.sql =================================================================== --- trunk/treebase-core/src/main/resources/TBASE2_POSTGRES_CREATION.sql 2011-07-11 19:11:16 UTC (rev 927) +++ trunk/treebase-core/src/main/resources/TBASE2_POSTGRES_CREATION.sql 2011-07-18 20:18:26 UTC (rev 928) @@ -1,2534 +1,2534 @@ -CREATE TABLE algorithm -( - "type" character(1) NOT NULL, - algorithm_id bigint 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 -) -WITH (OIDS=FALSE); -ALTER TABLE algorithm OWNER TO postgres; -CREATE SEQUENCE algorithm_id_sequence; -ALTER TABLE algorithm ALTER COLUMN algorithm_id SET DEFAULT nextval('algorithm_id_sequence'); --- alter sequence ancestralstate_id_sequence restart with 4885; - -CREATE TABLE analysis -( - analysis_id bigint NOT NULL, - "version" integer, - "name" character varying(255), - notes character varying(2000), - validated smallint, - study_id bigint, - analysis_order integer -) -WITH (OIDS=FALSE); -ALTER TABLE analysis OWNER TO postgres; -CREATE SEQUENCE analysis_id_sequence; -ALTER TABLE analysis ALTER COLUMN analysis_id SET DEFAULT nextval('analysis_id_sequence'); --- alter sequence analysis_id_sequence restart with 4887; - -CREATE TABLE analysisstep -( - analysisstep_id bigint 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, - tb_analysisid character varying(34) -) -WITH (OIDS=FALSE); -ALTER TABLE analysisstep OWNER TO postgres; -CREATE SEQUENCE analysisstep_id_sequence; -ALTER TABLE analysisstep ALTER COLUMN analysisstep_id SET DEFAULT nextval('analysisstep_id_sequence'); --- alter sequence analysisstep_id_sequence restart with 4883; - -CREATE TABLE analyzeddata -( - "type" character(1) NOT NULL, - analyzeddata_id bigint NOT NULL, - "version" integer, - "input" smallint, - notes character varying(2000), - treelength integer, - analysisstep_id bigint NOT NULL, - matrix_id bigint, - phylotree_id bigint -) -WITH (OIDS=FALSE); -ALTER TABLE analyzeddata OWNER TO postgres; -CREATE SEQUENCE analyzeddata_id_sequence; -ALTER TABLE analyzeddata ALTER COLUMN analyzeddata_id SET DEFAULT nextval('analyzeddata_id_sequence'); --- alter sequence analyzeddata_id_sequence restart with 11063; - -CREATE TABLE ancestralstate -( - "type" character(1) NOT NULL, - ancestralstate_id bigint NOT NULL, - "version" integer, - title character varying(255), - ancvalue character varying(255), - discretecharstate_id bigint, - ancstateset_id bigint -) -WITH (OIDS=FALSE); -ALTER TABLE ancestralstate OWNER TO postgres; -CREATE SEQUENCE ancestralstate_id_sequence; -ALTER TABLE ancestralstate ALTER COLUMN ancestralstate_id SET DEFAULT nextval('ancestralstate_id_sequence'); --- alter sequence ancestralstate_id_sequence restart with 1; - -CREATE TABLE ancstateset -( - ancstateset_id bigint NOT NULL, - "version" integer, - title character varying(255), - matrix_id bigint -) -WITH (OIDS=FALSE); -ALTER TABLE ancstateset OWNER TO postgres; -CREATE SEQUENCE ancstateset_id_sequence; -ALTER TABLE ancstateset ALTER COLUMN ancstateset_id SET DEFAULT nextval('ancstateset_id_sequence'); --- alter sequence ancestralstate_id_sequence restart with 1; - -CREATE TABLE chargroup_colrange -( - chargroup_id bigint NOT NULL, - columnrange_id bigint NOT NULL -) -WITH (OIDS=FALSE); -ALTER TABLE chargroup_colrange OWNER TO postgres; - -CREATE TABLE chargroup -( - chargroup_id bigint NOT NULL, - "version" integer, - title character varying(255), - charpartition_id bigint -) -WITH (OIDS=FALSE); -ALTER TABLE chargroup OWNER TO postgres; -CREATE SEQUENCE chargroup_id_sequence; -ALTER TABLE chargroup ALTER COLUMN chargroup_id SET DEFAULT nextval('chargroup_id_sequence'); --- alter sequence ancestralstate_id_sequence restart with 1011; - -CREATE TABLE charpartition -( - charpartition_id bigint NOT NULL, - "version" integer, - title character varying(255), - matrix_id bigint -) -WITH (OIDS=FALSE); -ALTER TABLE charpartition OWNER TO postgres; -CREATE SEQUENCE charpartition_id_sequence; -ALTER TABLE charpartition ALTER COLUMN charpartition_id SET DEFAULT nextval('charpartition_id_sequence'); --- alter sequence charpartition_id_sequence restart with 429; - -CREATE TABLE charset_colrange -( - charset_id bigint NOT NULL, - columnrange_id bigint NOT NULL -) -WITH (OIDS=FALSE); -ALTER TABLE charset_colrange OWNER TO postgres; - -CREATE TABLE charset -( - "type" character(1) NOT NULL, - charset_id bigint NOT NULL, - "version" integer, - title character varying(255), - matrix_id bigint -) -WITH (OIDS=FALSE); -ALTER TABLE charset OWNER TO postgres; -CREATE SEQUENCE charset_id_sequence; -ALTER TABLE charset ALTER COLUMN charset_id SET DEFAULT nextval('charset_id_sequence'); --- alter sequence ancestralstate_id_sequence restart with 6335; - -CREATE TABLE charweight_colrange -( - charweight_id bigint NOT NULL, - columnrange_id bigint NOT NULL -) -WITH (OIDS=FALSE); -ALTER TABLE charweight_colrange OWNER TO postgres; - -CREATE TABLE charweight -( - "type" character(1) NOT NULL, - charweight_id bigint NOT NULL, - "version" integer, - weight integer, - realweight double precision, - charweightset_id bigint -) -WITH (OIDS=FALSE); -ALTER TABLE charweight OWNER TO postgres; -CREATE SEQUENCE charweight_id_sequence; -ALTER TABLE charweight ALTER COLUMN charweight_id SET DEFAULT nextval('charweight_id_sequence'); --- alter sequence charweight_id_sequence restart with 1; - -CREATE TABLE charweightset -( - charweightset_id bigint NOT NULL, - "version" integer, - title character varying(255), - matrix_id bigint -) -WITH (OIDS=FALSE); -ALTER TABLE charweightset OWNER TO postgres; -CREATE SEQUENCE charweightset_id_sequence; -ALTER TABLE charweightset ALTER COLUMN charweightset_id SET DEFAULT nextval('charweightset_id_sequence'); --- alter sequence charweightset_id_sequence restart with 208; - -CREATE TABLE citation_author -( - citation_id bigint NOT NULL, - authors_person_id bigint NOT NULL, - author_order integer NOT NULL -) -WITH (OIDS=FALSE); -ALTER TABLE citation_author OWNER TO postgres; - -CREATE TABLE citation_editor -( - citation_id bigint NOT NULL, - editors_person_id bigint NOT NULL, - editor_order integer NOT NULL -) -WITH (OIDS=FALSE); -ALTER TABLE citation_editor OWNER TO postgres; - -CREATE TABLE citation -( - "type" character(1) NOT NULL, - citation_id bigint 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 smallint, - 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, - tb_studyid character varying(30) -) -WITH (OIDS=FALSE); -ALTER TABLE citation OWNER TO postgres; -CREATE SEQUENCE citation_id_sequence; -ALTER TABLE citation ALTER COLUMN citation_id SET DEFAULT nextval('citation_id_sequence'); --- alter sequence citation_id_sequence restart with 18422; - -CREATE TABLE citationstatus -( - citationstatus_id bigint NOT NULL, - "version" integer, - description character varying(50) -) -WITH (OIDS=FALSE); -ALTER TABLE citationstatus OWNER TO postgres; -CREATE SEQUENCE citationstatus_id_sequence; -ALTER TABLE citationstatus ALTER COLUMN citationstatus_id SET DEFAULT nextval('citationstatus_id_sequence'); --- alter sequence citationstatus_id_sequence restart with 6; - -CREATE TABLE coderecord_colrange -( - geneticcoderecord_id bigint NOT NULL, - columnrange_id bigint NOT NULL -) -WITH (OIDS=FALSE); -ALTER TABLE coderecord_colrange OWNER TO postgres; - -CREATE TABLE codonchar1_colrange -( - codonpositionset_id bigint NOT NULL, - columnrange_id bigint NOT NULL -) -WITH (OIDS=FALSE); -ALTER TABLE codonchar1_colrange OWNER TO postgres; - -CREATE TABLE codonchar2_colrange -( - codonpositionset_id bigint NOT NULL, - columnrange_id bigint NOT NULL -) -WITH (OIDS=FALSE); -ALTER TABLE codonchar2_colrange OWNER TO postgres; - -CREATE TABLE codonchar3_colrange -( - codonpositionset_id bigint NOT NULL, - columnrange_id bigint NOT NULL -) -WITH (OIDS=FALSE); -ALTER TABLE codonchar3_colrange OWNER TO postgres; - -CREATE TABLE codonnoncoding_colrange -( - codonpositionset_id bigint NOT NULL, - columnrange_id bigint NOT NULL -) -WITH (OIDS=FALSE); -ALTER TABLE codonnoncoding_colrange OWNER TO postgres; - -CREATE TABLE codonpositionset -( - codonpositionset_id bigint NOT NULL, - "version" integer, - title character varying(255), - matrix_id bigint -) -WITH (OIDS=FALSE); -ALTER TABLE codonpositionset OWNER TO postgres; -CREATE SEQUENCE codonpositionset_id_sequence; -ALTER TABLE codonpositionset ALTER COLUMN codonpositionset_id SET DEFAULT nextval('codonpositionset_id_sequence'); --- alter sequence codonpositionset_id_sequence restart with 4495; - -CREATE TABLE columnrange -( - columnrange_id bigint NOT NULL, - "version" integer, - endcolindex integer, - repeatinterval integer, - startcolindex integer -) -WITH (OIDS=FALSE); -ALTER TABLE columnrange OWNER TO postgres; -CREATE SEQUENCE columnrange_id_sequence; -ALTER TABLE columnrange ALTER COLUMN columnrange_id SET DEFAULT nextval('columnrange_id_sequence'); --- alter sequence columnrange_id_sequence restart with 255521; - -CREATE TABLE compound_element -( - compound_id bigint NOT NULL, - element_id bigint NOT NULL -) -WITH (OIDS=FALSE); -ALTER TABLE compound_element OWNER TO postgres; - -CREATE TABLE contancstate_value -( - ancstate_id bigint NOT NULL, - element character varying(255) -) -WITH (OIDS=FALSE); -ALTER TABLE contancstate_value OWNER TO postgres; - -CREATE TABLE cstreenode -( - cstreenode_id bigint NOT NULL, - "version" integer, - discretecharstate_id bigint, - parentnode_id bigint, - cstree_id bigint NOT NULL -) -WITH (OIDS=FALSE); -ALTER TABLE cstreenode OWNER TO postgres; -CREATE SEQUENCE cstreenode_id_sequence; -ALTER TABLE cstreenode ALTER COLUMN cstreenode_id SET DEFAULT nextval('cstreenode_id_sequence'); --- alter sequence cstreenode_id_sequence restart with 1; - -CREATE TABLE discretecharstate -( - discretecharstate_id bigint 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 -) -WITH (OIDS=FALSE); -ALTER TABLE discretecharstate OWNER TO postgres; -CREATE SEQUENCE discretecharstate_id_sequence; -ALTER TABLE discretecharstate ALTER COLUMN discretecharstate_id SET DEFAULT nextval('discretecharstate_id_sequence'); --- alter sequence discretecharstate_id_sequence restart with 169787; - -CREATE TABLE distancematrixelement -( - distancematrixelement_id bigint NOT NULL, - "version" integer, - distance double precision, - columnlabel_id bigint, - matrix_id bigint NOT NULL, - rowlabel_id bigint NOT NULL -) -WITH (OIDS=FALSE); -ALTER TABLE distancematrixelement OWNER TO postgres; -CREATE SEQUENCE distancematrixelement_id_sequence; -ALTER TABLE distancematrixelement ALTER COLUMN distancematrixelement_id SET DEFAULT nextval('distancematrixelement_id_sequence'); --- alter sequence distancematrixelement_id_sequence restart with 1; - -CREATE TABLE gapmode -( - gapmode_id bigint NOT NULL, - "version" integer, - description character varying(255) -) -WITH (OIDS=FALSE); -ALTER TABLE gapmode OWNER TO postgres; -CREATE SEQUENCE gapmode_id_sequence; -ALTER TABLE gapmode ALTER COLUMN gapmode_id SET DEFAULT nextval('gapmode_id_sequence'); --- alter sequence gapmode_id_sequence restart with 3; - -CREATE TABLE geneticcode -( - geneticcode_id bigint NOT NULL, - "version" integer, - codedescription character varying(1000), - codeorder character varying(255), - extensions character varying(255), - nucorder character varying(255), - predefined smallint, - title character varying(255) -) -WITH (OIDS=FALSE); -ALTER TABLE geneticcode OWNER TO postgres; -CREATE SEQUENCE geneticcode_id_sequence; -ALTER TABLE geneticcode ALTER COLUMN geneticcode_id SET DEFAULT nextval('geneticcode_id_sequence'); --- alter sequence geneticcode_id_sequence restart with 1; - -CREATE TABLE geneticcoderecord -( - geneticcoderecord_id bigint NOT NULL, - "version" integer, - geneticcode_id bigint, - geneticcodeset_id bigint NOT NULL -) -WITH (OIDS=FALSE); -ALTER TABLE geneticcoderecord OWNER TO postgres; -CREATE SEQUENCE geneticcoderecord_id_sequence; -ALTER TABLE geneticcoderecord ALTER COLUMN geneticcoderecord_id SET DEFAULT nextval('geneticcoderecord_id_sequence'); --- alter sequence geneticcoderecord_id_sequence restart with 1; - -CREATE TABLE geneticcodeset -( - geneticcodeset_id bigint NOT NULL, - "version" integer, - format character varying(255), - title character varying(255), - matrix_id bigint -) -WITH (OIDS=FALSE); -ALTER TABLE geneticcodeset OWNER TO postgres; -CREATE SEQUENCE geneticcodeset_id_sequence; -ALTER TABLE geneticcodeset ALTER COLUMN geneticcodeset_id SET DEFAULT nextval('geneticcodeset_id_sequence'); --- alter sequence geneticcodeset_id_sequence restart with 1; - -CREATE TABLE geospot -( - geospot_id bigint NOT NULL, - "version" integer, - elevation double precision, - latitude double precision, - longitude double precision, - "name" character varying(255), - notes character varying(2000) -) -WITH (OIDS=FALSE); -ALTER TABLE geospot OWNER TO postgres; -CREATE SEQUENCE geospot_id_sequence; -ALTER TABLE geospot ALTER COLUMN geospot_id SET DEFAULT nextval('geospot_id_sequence'); --- alter sequence geospot_id_sequence restart with 1; - -CREATE TABLE help -( - help_id bigint NOT NULL, - "version" integer, - helptext text, - tag character varying(255) -) -WITH (OIDS=FALSE); -ALTER TABLE help OWNER TO postgres; -CREATE SEQUENCE help_id_sequence; -ALTER TABLE help ALTER COLUMN help_id SET DEFAULT nextval('help_id_sequence'); --- alter sequence help_id_sequence restart with 183; - -CREATE TABLE itemdefinition -( - itemdefinition_id bigint NOT NULL, - "version" integer, - description character varying(255) -) -WITH (OIDS=FALSE); -ALTER TABLE itemdefinition OWNER TO postgres; -CREATE SEQUENCE itemdefinition_id_sequence; -ALTER TABLE itemdefinition ALTER COLUMN itemdefinition_id SET DEFAULT nextval('itemdefinition_id_sequence'); --- alter sequence itemdefinition_id_sequence restart with 9; - -CREATE TABLE itemvalue -( - itemvalue_id bigint NOT NULL, - "version" integer, - "value" character varying(255), - element_id bigint NOT NULL -) -WITH (OIDS=FALSE); -ALTER TABLE itemvalue OWNER TO postgres; -CREATE SEQUENCE itemvalue_id_sequence; -ALTER TABLE itemvalue ALTER COLUMN itemvalue_id SET DEFAULT nextval('itemvalue_id_sequence'); --- alter sequence itemvalue_id_sequence restart with 1; - -CREATE TABLE leftchangeset_charstate -( - statechangeset_id bigint NOT NULL, - discretecharstate_id bigint NOT NULL -) -WITH (OIDS=FALSE); -ALTER TABLE leftchangeset_charstate OWNER TO postgres; - -CREATE TABLE matrix_itemdefinition -( - matrix_id bigint NOT NULL, - itemdefinition_id bigint NOT NULL -) -WITH (OIDS=FALSE); -ALTER TABLE matrix_itemdefinition OWNER TO postgres; - -CREATE TABLE matrix -( - matrixtype character(1) NOT NULL, - matrix_id bigint NOT NULL, - "version" integer, - tb_matrixid character varying(30), - description character varying(2000), - gapsymbol character(1), - missingsymbol character(1), - nexusfilename character varying(255), - published smallint, - symbols character varying(255), - title character varying(255), - "nchar" integer, - ntax integer, - aligned smallint, - diagonal smallint, - triangle character varying(255), - casesensitive smallint, - matrixdatatype_id bigint, - matrixkind_id bigint, - study_id bigint, - taxonlabelset_id bigint, - ancstateset_id bigint, - codonpositionset_id bigint, - charset_id bigint, - typeset_id bigint, - charweightset_id bigint -) -WITH (OIDS=FALSE); -ALTER TABLE matrix OWNER TO postgres; -CREATE SEQUENCE matrix_id_sequence; -ALTER TABLE matrix ALTER COLUMN matrix_id SET DEFAULT nextval('matrix_id_sequence'); --- alter sequence matrix_id_sequence restart with 4151; - -CREATE TABLE matrixcolumn_itemdefinition -( - matrixcolumn_id bigint NOT NULL, - itemdefinition_id bigint NOT NULL -) -WITH (OIDS=FALSE); -ALTER TABLE matrixcolumn_itemdefinition OWNER TO postgres; - -CREATE TABLE matrixcolumn -( - matrixcolumn_id bigint NOT NULL, - "version" integer, - phylochar_id bigint, - matrix_id bigint, - stateformat_id bigint, - column_order integer -) -WITH (OIDS=FALSE); -ALTER TABLE matrixcolumn OWNER TO postgres; -CREATE SEQUENCE matrixcolumn_id_sequence; -ALTER TABLE matrixcolumn ALTER COLUMN matrixcolumn_id SET DEFAULT nextval('matrixcolumn_id_sequence'); --- alter sequence matrixcolumn_id_sequence restart with 5682181; - -CREATE TABLE matrixdatatype -( - matrixdatatype_id bigint NOT NULL, - "version" integer, - description character varying(255), - phylochar_id bigint -) -WITH (OIDS=FALSE); -ALTER TABLE matrixdatatype OWNER TO postgres; -CREATE SEQUENCE matrixdatatype_id_sequence; -ALTER TABLE matrixdatatype ALTER COLUMN matrixdatatype_id SET DEFAULT nextval('matrixdatatype_id_sequence'); --- alter sequence matrixdatatype_id_sequence restart with 8; - -CREATE TABLE matrixelement -( - "type" character(1) NOT NULL, - matrixelement_id bigint NOT NULL, - "version" integer, - andlogic smallint, - compoundvalue character varying(1000), - "value" double precision, - gap smallint, - matrixcolumn_id bigint, - matrixrow_id bigint, - itemdefinition_id bigint, - discretecharstate_id bigint, - element_order integer -) -WITH (OIDS=FALSE); -ALTER TABLE matrixelement OWNER TO postgres; -CREATE SEQUENCE matrixelement_id_sequence; -ALTER TABLE matrixelement ALTER COLUMN matrixelement_id SET DEFAULT nextval('matrixelement_id_sequence'); --- alter sequence matrixelement_id_sequence restart with 305807206; - -CREATE TABLE matrixkind -( - matrixkind_id bigint NOT NULL, - "version" integer, - description character varying(100) -) -WITH (OIDS=FALSE); -ALTER TABLE matrixkind OWNER TO postgres; -CREATE SEQUENCE matrixkind_id_sequence; -ALTER TABLE matrixkind ALTER COLUMN matrixkind_id SET DEFAULT nextval('matrixkind_id_sequence'); --- alter sequence matrixkind_id_sequence restart with 12; - -CREATE TABLE matrixrow -( - matrixrow_id bigint NOT NULL, - "version" integer, - symbolstring text, - matrix_id bigint, - taxonlabel_id bigint NOT NULL, - row_order integer -) -WITH (OIDS=FALSE); -ALTER TABLE matrixrow OWNER TO postgres; -CREATE SEQUENCE matrixrow_id_sequence; -ALTER TABLE matrixrow ALTER COLUMN matrixrow_id SET DEFAULT nextval('matrixrow_id_sequence'); --- alter sequence matrixrow_id_sequence restart with 183676; - -CREATE TABLE nodeattribute -( - nodeattribute_id bigint NOT NULL, - "version" integer -) -WITH (OIDS=FALSE); -ALTER TABLE nodeattribute OWNER TO postgres; -CREATE SEQUENCE nodeattribute_id_sequence; -ALTER TABLE nodeattribute ALTER COLUMN nodeattribute_id SET DEFAULT nextval('nodeattribute_id_sequence'); --- alter sequence nodeattribute_id_sequence restart with 1; - -CREATE TABLE person -( - person_id bigint NOT NULL, - "version" integer, - email character varying(255), - firstname character varying(255), - lastname character varying(255) NOT NULL, - mname character varying(255), - phone character varying(50), - authorid character varying(50) -) -WITH (OIDS=FALSE); -ALTER TABLE person OWNER TO postgres; -CREATE SEQUENCE person_id_sequence; -ALTER TABLE person ALTER COLUMN person_id SET DEFAULT nextval('person_id_sequence'); --- alter sequence person_id_sequence restart with 9961; - -CREATE TABLE phylochar -( - "type" character(1) NOT NULL, - phylochar_id bigint NOT NULL, - "version" integer, - description character varying(255), - lowerlimit double precision, - upperlimit double precision -) -WITH (OIDS=FALSE); -ALTER TABLE phylochar OWNER TO postgres; -CREATE SEQUENCE phylochar_id_sequence; -ALTER TABLE phylochar ALTER COLUMN phylochar_id SET DEFAULT nextval('phylochar_id_sequence'); --- alter sequence phylochar_id_sequence restart with 32903; - -CREATE TABLE phylotree -( - phylotree_id bigint NOT NULL, - "version" integer, - tb1_treeid character varying(30), - bigtree smallint, - label character varying(255), - ntax integer, - newickstring text, - nexusfilename character varying(255), - published smallint, - rootedtree smallint, - title character varying(255), - rootnode_id bigint, - study_id bigint, - treeattribute_id bigint, - treeblock_id bigint, - treekind_id bigint, - treequality_id bigint, - treetype_id bigint -) -WITH (OIDS=FALSE); -ALTER TABLE phylotree OWNER TO postgres; -CREATE SEQUENCE phylotree_id_sequence; -ALTER TABLE phylotree ALTER COLUMN phylotree_id SET DEFAULT nextval('phylotree_id_sequence'); --- alter sequence phylotree_id_sequence restart with 5982; - -CREATE TABLE phylotreenode -( - phylotreenode_id bigint NOT NULL, - "version" integer, - branchlength double precision, - leftnode bigint, - "name" character varying(255), - nodedepth integer, - rightnode bigint, - child_id bigint, - nodeattribute_id bigint, - parent_id bigint, - sibling_id bigint, - taxonlabel_id bigint, - phylotree_id bigint NOT NULL -) -WITH (OIDS=FALSE); -ALTER TABLE phylotreenode OWNER TO postgres; -CREATE SEQUENCE phylotreenode_id_sequence; -ALTER TABLE phylotreenode ALTER COLUMN phylotreenode_id SET DEFAULT nextval('phylotreenode_id_sequence'); --- alter sequence phylotreenode_id_sequence restart with 452286; --- GRANT ALL ON SEQUENCE phylotreenode_id_sequence TO postgres; - -CREATE TABLE polytcount -( - polytcount_id bigint NOT NULL, - "version" integer, - description character varying(255) -) -WITH (OIDS=FALSE); -ALTER TABLE polytcount OWNER TO postgres; -CREATE SEQUENCE polytcount_id_sequence; -ALTER TABLE polytcount ALTER COLUMN polytcount_id SET DEFAULT nextval('polytcount_id_sequence'); --- alter sequence polytcount_id_sequence restart with 3; - -CREATE TABLE rightchangeset_charstate -( - statechangeset_id bigint NOT NULL, - discretecharstate_id bigint NOT NULL -) -WITH (OIDS=FALSE); -ALTER TABLE rightchangeset_charstate OWNER TO postgres; - -CREATE TABLE rowsegment -( - rowsegment_id bigint NOT NULL, - "version" integer, - endindex integer, - catalognum character varying(50), - collectioncode character varying(50), - collector character varying(255), - country character varying(50), - elevation double precision, - genbaccession character varying(30), - instacronym character varying(50), - latitude double precision, - locality character varying(255), - longitude double precision, - notes character varying(2000), - otheraccession character varying(50), - sampledate date, - state character varying(50), - startindex integer, - title character varying(255), - matrixrow_id bigint NOT NULL, - taxonlabel_id bigint -) -WITH (OIDS=FALSE); -ALTER TABLE rowsegment OWNER TO postgres; -CREATE SEQUENCE rowsegment_id_sequence; -ALTER TABLE rowsegment ALTER COLUMN rowsegment_id SET DEFAULT nextval('rowsegment_id_sequence'); --- alter sequence rowsegment_id_sequence restart with 1; - -CREATE TABLE software -( - software_id bigint NOT NULL, - "version" integer, - description character varying(2000), - "name" character varying(255), - softwareurl character varying(500), - softwareversion character varying(255) -) -WITH (OIDS=FALSE); -ALTER TABLE software OWNER TO postgres; -CREATE SEQUENCE software_id_sequence; -ALTER TABLE software ALTER COLUMN software_id SET DEFAULT nextval('software_id_sequence'); --- alter sequence software_id_sequence restart with 4834; - -CREATE TABLE specimenlabel -( - specimenlabel_id bigint NOT NULL, - "version" integer, - genbankid character varying(255), - museumnumber character varying(255), - tissue character varying(255), - geospot_id bigint, - study_id bigint NOT NULL, - taxon_id bigint -) -WITH (OIDS=FALSE); -ALTER TABLE specimenlabel OWNER TO postgres; -CREATE SEQUENCE specimenlabel_id_sequence; -ALTER TABLE specimenlabel ALTER COLUMN specimenlabel_id SET DEFAULT nextval('specimenlabel_id_sequence'); --- alter sequence specimenlabel_id_sequence restart with 4499; - -CREATE TABLE statechangeset -( - statechangeset_id bigint NOT NULL, - "version" integer, - reversible smallint, - title character varying(255) -) -WITH (OIDS=FALSE); -ALTER TABLE statechangeset OWNER TO postgres; -CREATE SEQUENCE statechangeset_id_sequence; -ALTER TABLE statechangeset ALTER COLUMN statechangeset_id SET DEFAULT nextval('statechangeset_id_sequence'); --- alter sequence statechangeset_id_sequence restart with 1; - -CREATE TABLE stateformat -( - stateformat_id bigint NOT NULL, - "version" integer, - description character varying(255) -) -WITH (OIDS=FALSE); -ALTER TABLE stateformat OWNER TO postgres; -CREATE SEQUENCE stateformat_id_sequence; -ALTER TABLE stateformat ALTER COLUMN stateformat_id SET DEFAULT nextval('stateformat_id_sequence'); --- alter sequence stateformat_id_sequence restart with 5; - -CREATE TABLE statemodifier -( - statemodifier_id bigint NOT NULL, - "version" integer, - count integer, - frequency double precision, - discretecharstate_id bigint, - element_id bigint NOT NULL, - stateformat_id bigint NOT NULL -) -WITH (OIDS=FALSE); -ALTER TABLE statemodifier OWNER TO postgres; -CREATE SEQUENCE statemodifier_id_sequence; -ALTER TABLE statemodifier ALTER COLUMN statemodifier_id SET DEFAULT nextval('statemodifier_id_sequence'); --- alter sequence statemodifier_id_sequence restart with 1; - -CREATE TABLE stateset -( - stateset_id bigint NOT NULL, - "version" integer, - title character varying(255), - matrix_id bigint -) -WITH (OIDS=FALSE); -ALTER TABLE stateset OWNER TO postgres; -CREATE SEQUENCE stateset_id_sequence; -ALTER TABLE stateset ALTER COLUMN stateset_id SET DEFAULT nextval('stateset_id_sequence'); --- alter sequence stateset_id_sequence restart with 1; - -CREATE TABLE stepmatrixelement -( - discretecharstate_id bigint NOT NULL, - "version" integer, - transcost double precision, - state1_id bigint, - state2_id bigint, - stepmatrix_id bigint NOT NULL -) -WITH (OIDS=FALSE); -ALTER TABLE stepmatrixelement OWNER TO postgres; - -CREATE TABLE study_nexusfile -( - study_id bigint NOT NULL, - nexus text NOT NULL, - filename character varying(255) NOT NULL -) -WITH (OIDS=FALSE); -ALTER TABLE study_nexusfile OWNER TO postgres; - -CREATE TABLE study -( - study_id bigint NOT NULL, - "version" integer, - tb_studyid character varying(30), - accessionnumber character varying(255), - lastmodifieddate date, - "name" character varying(255), - notes character varying(2000), - releasedate date, - citation_id bigint, - user_id bigint, - studystatus_id bigint -) -WITH (OIDS=FALSE); -ALTER TABLE study OWNER TO postgres; -CREATE SEQUENCE study_id_sequence; -ALTER TABLE study ALTER COLUMN study_id SET DEFAULT nextval('study_id_sequence'); --- alter sequence study_id_sequence restart with 9931; - -CREATE TABLE studystatus -( - studystatus_id bigint NOT NULL, - "version" integer, - description character varying(255) -) -WITH (OIDS=FALSE); -ALTER TABLE studystatus OWNER TO postgres; -CREATE SEQUENCE studystatus_id_sequence; -ALTER TABLE studystatus ALTER COLUMN studystatus_id SET DEFAULT nextval('studystatus_id_sequence'); --- alter sequence studystatus_id_sequence restart with 4; - -CREATE TABLE sub_matrix -( - submission_i... [truncated message content] |