Menu
▾
▴
[Treebase-guts] SF.net SVN: treebase:[510] trunk/treebase-core
|
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 (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] |
