Menu
▾
▴
[Treebase-guts] SF.net SVN: treebase:[530] trunk/treebase-core/db
|
From: <vga...@us...> - 2010-03-05 21:19:15
|
Revision: 530
http://treebase.svn.sourceforge.net/treebase/?rev=530&view=rev
Author: vgapeyev
Date: 2010-03-05 21:19:07 +0000 (Fri, 05 Mar 2010)
Log Message:
-----------
Moving snapshots and patches to the new location
Added Paths:
-----------
trunk/treebase-core/db/schema/README.txt
trunk/treebase-core/db/schema/TODO.txt
trunk/treebase-core/db/schema/init_db_uptodate.pg
trunk/treebase-core/db/schema/patches/
trunk/treebase-core/db/schema/patches/0005_add-taxonabel-tb1legacyid.sql
trunk/treebase-core/db/schema/snapshots/
trunk/treebase-core/db/schema/snapshots/0000_DATA_before_patches_start.sql
trunk/treebase-core/db/schema/versionhistory.sql
Removed Paths:
-------------
trunk/treebase-core/db/README.txt
trunk/treebase-core/db/TODO.txt
trunk/treebase-core/db/init_db_uptodate.pg
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/patches/0005_add-taxonabel-tb1legacyid.sql
trunk/treebase-core/db/schema/snapshots/0000_DATA_before_patches_start.sql
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
Deleted: trunk/treebase-core/db/README.txt
===================================================================
--- trunk/treebase-core/db/README.txt 2010-03-05 21:11:36 UTC (rev 529)
+++ trunk/treebase-core/db/README.txt 2010-03-05 21:19:07 UTC (rev 530)
@@ -1,134 +0,0 @@
-This directory keeps track of schema patches applied to the DB as the application evolves,
-as well as occasional snapshots of the schema and pre-loaded dictionary-like data.
-
-Outline
-=======
-
-The setup is inspired by database migrations in Ruby and Python, but is not nearly as comprehensive.
-
-The 'patches' directory stores SQL scripts (nnnn_descr_label.sql) that move the schema
-from one version to the next. Occasionally, we put into the 'snaphots' subdirectory
-nnnn_SCHEMA...sql and nnnn_DATA...sql that capture the state of the DB after the
-patches/nnnn.sql patch has been applied.
-
-Patches are sequentially numbered (by hand). If a snapshot nnnn is present, it reflects
-the state of the DB after patch nnnn was applied.
-
-The schema (staring with the very first 0000 snapshot) contains the 'versionhistory' table,
-which is used to keep track which patches have already been applied to a particular DB instance.
-This is particularly useful in multi-instance installations (e.g., when there are
-development, staging, production, etc., instances). [See versionhistory.sql for the table's definition.]
-
-
-Task-specific instructions
-==========================
-
-Creating a patch
-----------------
-
-A patch is generally committed into SVN together with Java code for the application.
-It is expected that, for a given SVN version, the application code expects to work
-w.r.t. the database containing all the patches from this version.
-
-Use the next sequential number and a short descriptive label to name a patch.
-To avoid numbering conflicts, developers must coordinate their patching activity.
-
-Each patch must start by an INSERT of a row describing the patch into the versionhistory table.
-(See existing patches for examples.)
-
-Each patch will be executed as a transaction, so there is no need to insert
-transaction commands into the patch.
-
-Patches must not include any ownership or permissions commands: ownership and
-permissions are installation-specific, and therefore should be handled by the
-instance maintainer.
-
-When an error is discovered in a recent patch, it may be ok to fix it and commit
-a new version under the same patch number, provided it is possible to adjust all DB
-instances to conform with the new patch version. This can be doable when the patch
-was only applied to the development DB instance. When the patch with an error was already
-applied to production databases, it could be more prudent to develop a new, error-fixing patch.
-
-When adding a patch, add a line into init_db_uptodate.pg as well.
-
-
-Applying a patch
-----------------
-
-Apply each patch within a transaction, e.g.,
-psql -d yourdb -U yourusername
-yourdb=> begin transaction;
-yourdb=> \i nnnn_your_patch.sql
-yourdb=> commit; OR rollback;
-
-After that, adjust ownership and permissions on newly created objects, if any,
-as required by the DB instance.
-
-Use your judgment on what to do in case the patch application generated errors
-and you had to roll back. Most likely, someone has to work on improving the patch.
-
-
-Creating a current snapshot
----------------------------
-
-Schema snapshots must not include any ownership or permissions commands, since these
-are installation-specific. This command,
-
-pg_dump -h your.host.url -U your_username --format=p --no-owner --no-privileges --schema-only yourdb > nnnn_your_label.sql
-
-creates a reasonably lean dump of the schema. However, if the DB contained any objects
-not created by prior snapshots and patches, they should be removed by hand.
-
-Data snapshot is trickier. The best bet right now is probably by hand-modifying the previous data snapshot.
-
-After creating a new snapshot, update init_db_uptodate.pg: change the names of the snapshot scripts
-and remove all \i commands for the patch scripts.
-
-
-Creating a fresh DB
-------------------
-
-Use the init_db_uptodate.pg script, which should create the DB from the most recent snapshot
-and apply all the subsequent patches:
-
-Use the most recent snapshot:
-
-psql -d yourdb -U yourusername -f init_db_uptodate.pg
-
-OR
-
-psql -d yourdb -U yourusername
-yourdb=> begin transaction;
-yourdb=> \i init_db_uptodate.pg
-yourdb=> commit;
-
-
-Recommended development - production - staging workflow
-=======================================================
-
-Local instances: It is recommended that each developer has his own DB instance to use for writing
-and initially testing new versions of the application and schema patches. This ensures
-at least some basic compatibility between SQL and Java within a given SVN commit.
-These instances may contain only minimal data, as useful for the developer.
-
-Development instance: The dev DB instance is frequently re-built to track
-the most recent SVN version of the application and DB patches. It is used to verify correctness
-of new versions and to communicate with non-programmer project participants.
-This instance should contain sizable and representative amount of data,
-but not necessarily as much or as good quality as the production instance.
-
-Staging instance: The stage instance is a testbed for rolling out a new version on production.
-When development on dev reaches release, a staging instance is created by cloning the
-current production instance. This ensures that the possible new data quirks accumulated
-since the previous release are present. Then patches spanning from the previous to the current
-release are applied, new version of the application is installed, and the whole system is tested
-and verified as necessary. If all works well, then the release roll-out may proceed on production.
-Otherwise, back to development.
-
-Production instance: This is the instance with the authoritative data that is constantly being
-accessed by external uses and can tolerate only brief periods of downtime. Make a backup before
-rolling out a new release and only roll out releases that were successfully verified on staging.
-After a new release roll-out, it makes sense to use the prod instance to create a new
-schema-and-dictionaries snapshot.
-
-
\ No newline at end of file
Deleted: trunk/treebase-core/db/TODO.txt
===================================================================
--- trunk/treebase-core/db/TODO.txt 2010-03-05 21:11:36 UTC (rev 529)
+++ trunk/treebase-core/db/TODO.txt 2010-03-05 21:19:07 UTC (rev 530)
@@ -1,57 +0,0 @@
-Pending framework issues
-========================
-
-* Would like a script for getting a current snapshot of the initial (dictionary) data,
- to be used alongside with the schema snapshot command.
- [A problem: simply designating some tables as "dictionaries" and others as "facts" would not work --
- at least some tables (Phylochar, DiscreteCharState) contains both "dictionary" and "fact" records.]
-
-* Automation scripts for tasks, especially patch application, so that the sysadmin does not forget to wrap a transaction.
-
-* A better way for handling ownership and permissions in particular DB instances -- there is
- too much manual work at the moment.
-
-* Less-intrusive permissions/ownership management. Currently, ownerships and permissions are handled by pg_owner, pg_admin functions that the
- sysadmin creates in DB after the initial installation. Consequently, they get into the schema snapshops and have to be removed by hand.
-
-
-Pending schema patches:
-======================
-
-* Maybe some indices around matrices (in addition to the indices around trees that are already there).
-
-
-Pending data cleanups (on staging):
-===================================
-
-[Could be worthwhile to do PRIOR to Dec 2009 and Feb 2010 migrations.]
-
-* Remove junk, particularly that created by tests
-
-* Known junk: Submission 22 and its related records
- Youjun's email of 2010-01-28:
- "In table phylotree, many trees do not have a study_id value, but their phylotreenode related to study 22 via table taxonlabel.
- Their phylotree_id are:
- 1129,1130,1131,1132, 2333,2334,2335,2336,2337,2338, 2339,2340,2341,2343,2556,2557,2686,2726,2727,2787,2788,2789,2790,
- 3446,3671,3766,3767,3901,3902,3903,3904,3905,3906,3907,3908,3909,3910,3911,3912, 4062,4063, 5705,5706,5707,5708,5720,5721,5921,5941,5981,160000022341
- I will delete those trees because their foreign key constrain prevent me from cleaning up about 10,000 dummy taxonlabel records related to submission 22
- (study 22 and submission 22 happen to be the same)."
- VG: these IDs are from end-of-Jan treebasedev; the current treebasestage may have fewer.
-
-* Replace "owner" nulls (in submission.user_id?) by the special "migration" owner (see Bill's message of around 2010-02-02)
- "Also because we have reverted to an older instance of the data, the migrated records contain have NULL in the user_id of the submission table. This causes null pointer errors for the unit tests. To fix the problem, I did the following:
- 1. I created a user with username "migration" and email address "mig...@tr...". This is the dummy "user" who will now "own" all migrated submissions that lack an owner.
- 2. I got the user_id for the "migration" user, and it happened to be 9955
- 3. I ran this statement: "UPDATE submission SET user_id = 9955 WHERE user_id IS NULL"
- Now all migrated data belong to the user "migration". "
- Also, see "null->'tb1'; http://www.treebase.org/~piel/taxlabels_fix.zip; Study 22 (Jan 8)"
-
-
-
-Data cleanups on production:
-============================
-
-* Synch hibernate_sequence and {sub_matrix,sub_treebaseblock}.collection_id
- [No need if prod is restored from a dump of stage made after ~2010-02-13]
-
-
\ No newline at end of file
Deleted: trunk/treebase-core/db/init_db_uptodate.pg
===================================================================
--- trunk/treebase-core/db/init_db_uptodate.pg 2010-03-05 21:11:36 UTC (rev 529)
+++ trunk/treebase-core/db/init_db_uptodate.pg 2010-03-05 21:19:07 UTC (rev 530)
@@ -1,14 +0,0 @@
--- Populates an empty DB with tables and dictionary data
--- corresponding to the most recent snapshot and all subsequent patches.
-
-\i snapshots/0000_SCHEMA_before_patches_start.sql
-\i snapshots/0000_DATA_before_patches_start.sql
-
-\i patches/0001_constraint-names.sql
-\i patches/0002_drop-geospot_id_sequence.sql
-\i patches/0003_rename-stepmatrixelement-pk.sql
-\i patches/0004_create-indexes.sql
-\i patches/0005_add-taxonabel-tb1legacyid.sql
-
-
-
Deleted: trunk/treebase-core/db/patches/0001_constraint-names.sql
===================================================================
--- trunk/treebase-core/db/patches/0001_constraint-names.sql 2010-03-05 21:11:36 UTC (rev 529)
+++ trunk/treebase-core/db/patches/0001_constraint-names.sql 2010-03-05 21:19:07 UTC (rev 530)
@@ -1,880 +0,0 @@
-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);
-
-
Deleted: trunk/treebase-core/db/patches/0002_drop-geospot_id_sequence.sql
===================================================================
--- trunk/treebase-core/db/patches/0002_drop-geospot_id_sequence.sql 2010-03-05 21:11:36 UTC (rev 529)
+++ trunk/treebase-core/db/patches/0002_drop-geospot_id_sequence.sql 2010-03-05 21:19:07 UTC (rev 530)
@@ -1,6 +0,0 @@
-insert into versionhistory(patchnumber, patchlabel, patchdescription)
- values (2, 'drop-geospot_id_sequence',
- 'Drop geospot_id_sequence');
-
-drop sequence geospot_id_sequence;
-
Deleted: trunk/treebase-core/db/patches/0003_rename-stepmatrixelement-pk.sql
===================================================================
--- trunk/treebase-core/db/patches/0003_rename-stepmatrixelement-pk.sql 2010-03-05 21:11:36 UTC (rev 529)
+++ trunk/treebase-core/db/patches/0003_rename-stepmatrixelement-pk.sql 2010-03-05 21:19:07 UTC (rev 530)
@@ -1,14 +0,0 @@
-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');
-
-
Deleted: trunk/treebase-core/db/patches/0004_create-indexes.sql
===================================================================
--- trunk/treebase-core/db/patches/0004_create-indexes.sql 2010-03-05 21:11:36 UTC (rev 529)
+++ trunk/treebase-core/db/patches/0004_create-indexes.sql 2010-03-05 21:19:07 UTC (rev 530)
@@ -1,43 +0,0 @@
-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);
-
Deleted: trunk/treebase-core/db/patches/0005_add-taxonabel-tb1legacyid.sql
===================================================================
--- trunk/treebase-core/db/patches/0005_add-taxonabel-tb1legacyid.sql 2010-03-05 21:11:36 UTC (rev 529)
+++ trunk/treebase-core/db/patches/0005_add-taxonabel-tb1legacyid.sql 2010-03-05 21:19:07 UTC (rev 530)
@@ -1,8 +0,0 @@
-insert into versionhistory(patchnumber, patchlabel, patchdescription)
- values (5, 'add-taxonlabel-tb1legacyid',
- 'Add tb1legacyid field to the taxonlabel table');
-
-alter table taxonlabel add column tb1legacyid varchar(20);
-
-
-
\ No newline at end of file
Copied: trunk/treebase-core/db/schema/README.txt (from rev 513, trunk/treebase-core/db/README.txt)
===================================================================
--- trunk/treebase-core/db/schema/README.txt (rev 0)
+++ trunk/treebase-core/db/schema/README.txt 2010-03-05 21:19:07 UTC (rev 530)
@@ -0,0 +1,134 @@
+This directory keeps track of schema patches applied to the DB as the application evolves,
+as well as occasional snapshots of the schema and pre-loaded dictionary-like data.
+
+Outline
+=======
+
+The setup is inspired by database migrations in Ruby and Python, but is not nearly as comprehensive.
+
+The 'patches' directory stores SQL scripts (nnnn_descr_label.sql) that move the schema
+from one version to the next. Occasionally, we put into the 'snaphots' subdirectory
+nnnn_SCHEMA...sql and nnnn_DATA...sql that capture the state of the DB after the
+patches/nnnn.sql patch has been applied.
+
+Patches are sequentially numbered (by hand). If a snapshot nnnn is present, it reflects
+the state of the DB after patch nnnn was applied.
+
+The schema (staring with the very first 0000 snapshot) contains the 'versionhistory' table,
+which is used to keep track which patches have already been applied to a particular DB instance.
+This is particularly useful in multi-instance installations (e.g., when there are
+development, staging, production, etc., instances). [See versionhistory.sql for the table's definition.]
+
+
+Task-specific instructions
+==========================
+
+Creating a patch
+----------------
+
+A patch is generally committed into SVN together with Java code for the application.
+It is expected that, for a given SVN version, the application code expects to work
+w.r.t. the database containing all the patches from this version.
+
+Use the next sequential number and a short descriptive label to name a patch.
+To avoid numbering conflicts, developers must coordinate their patching activity.
+
+Each patch must start by an INSERT of a row describing the patch into the versionhistory table.
+(See existing patches for examples.)
+
+Each patch will be executed as a transaction, so there is no need to insert
+transaction commands into the patch.
+
+Patches must not include any ownership or permissions commands: ownership and
+permissions are installation-specific, and therefore should be handled by the
+instance maintainer.
+
+When an error is discovered in a recent patch, it may be ok to fix it and commit
+a new version under the same patch number, provided it is possible to adjust all DB
+instances to conform with the new patch version. This can be doable when the patch
+was only applied to the development DB instance. When the patch with an error was already
+applied to production databases, it could be more prudent to develop a new, error-fixing patch.
+
+When adding a patch, add a line into init_db_uptodate.pg as well.
+
+
+Applying a patch
+----------------
+
+Apply each patch within a transaction, e.g.,
+psql -d yourdb -U yourusername
+yourdb=> begin transaction;
+yourdb=> \i nnnn_your_patch.sql
+yourdb=> commit; OR rollback;
+
+After that, adjust ownership and permissions on newly created objects, if any,
+as required by the DB instance.
+
+Use your judgment on what to do in case the patch application generated errors
+and you had to roll back. Most likely, someone has to work on improving the patch.
+
+
+Creating a current snapshot
+---------------------------
+
+Schema snapshots must not include any ownership or permissions commands, since these
+are installation-specific. This command,
+
+pg_dump -h your.host.url -U your_username --format=p --no-owner --no-privileges --schema-only yourdb > nnnn_your_label.sql
+
+creates a reasonably lean dump of the schema. However, if the DB contained any objects
+not created by prior snapshots and patches, they should be removed by hand.
+
+Data snapshot is trickier. The best bet right now is probably by hand-modifying the previous data snapshot.
+
+After creating a new snapshot, update init_db_uptodate.pg: change the names of the snapshot scripts
+and remove all \i commands for the patch scripts.
+
+
+Creating a fresh DB
+------------------
+
+Use the init_db_uptodate.pg script, which should create the DB from the most recent snapshot
+and apply all the subsequent patches:
+
+Use the most recent snapshot:
+
+psql -d yourdb -U yourusername -f init_db_uptodate.pg
+
+OR
+
+psql -d yourdb -U yourusername
+yourdb=> begin transaction;
+yourdb=> \i init_db_uptodate.pg
+yourdb=> commit;
+
+
+Recommended development - production - staging workflow
+=======================================================
+
+Local instances: It is recommended that each developer has his own DB instance to use for writing
+and initially testing new versions of the application and schema patches. This ensures
+at least some basic compatibility between SQL and Java within a given SVN commit.
+These instances may contain only minimal data, as useful for the developer.
+
+Development instance: The dev DB instance is frequently re-built to track
+the most recent SVN version of the application and DB patches. It is used to verify correctness
+of new versions and to communicate with non-programmer project participants.
+This instance should contain sizable and representative amount of data,
+but not necessarily as much or as good quality as the production instance.
+
+Staging instance: The stage instance is a testbed for rolling out a new version on production.
+When development on dev reaches release, a staging instance is created by cloning the
+current production instance. This ensures that the possible new data quirks accumulated
+since the previous release are present. Then patches spanning from the previous to the current
+release are applied, new version of the application is installed, and the whole system is tested
+and verified as necessary. If all works well, then the release roll-out may proceed on production.
+Otherwise, back to development.
+
+Production instance: This is the instance with the authoritative data that is constantly being
+accessed by external uses and can tolerate only brief periods of downtime. Make a backup before
+rolling out a new release and only roll out releases that were successfully verified on staging.
+After a new release roll-out, it makes sense to use the prod instance to create a new
+schema-and-dictionaries snapshot.
+
+
\ No newline at end of file
Copied: trunk/treebase-core/db/schema/TODO.txt (from rev 519, trunk/treebase-core/db/TODO.txt)
===================================================================
--- trunk/treebase-core/db/schema/TODO.txt (rev 0)
+++ trunk/treebase-core/db/schema/TODO.txt 2010-03-05 21:19:07 UTC (rev 530)
@@ -0,0 +1,62 @@
+Pending framework issues
+========================
+
+* Would like a script for getting a current snapshot of the initial (dictionary) data,
+ to be used alongside with the schema snapshot command.
+ [A problem: simply designating some tables as "dictionaries" and others as "facts" would not work --
+ at least some tables (Phylochar, DiscreteCharState) contains both "dictionary" and "fact" records.]
+
+* Automation scripts for tasks, especially patch application, so that the sysadmin does not forget to wrap a transaction.
+
+* A better way for handling ownership and permissions in particular DB instances -- there is
+ too much manual work at the moment.
+
+* Less-intrusive permissions/ownership management. Currently, ownerships and permissions are handled by pg_owner, pg_admin functions that the
+ sysadmin creates in DB after the initial installation. Consequently, they get into the schema snapshops and have to be removed by hand.
+
+
+Pending schema patches:
+======================
+
+* Maybe some indices around matrices (in addition to the indices around trees that are already there).
+
+
+Pending data cleanups (on staging):
+===================================
+
+[Was promised to be done after migration]
+
+* fixlabels_trans.zip -- removes duplicate taxon labels - email of 2010-03-03
+
+
+[Could be worthwhile to do PRIOR to Dec 2009 and Feb 2010 migrations.]
+
+* Remove junk, particularly that created by tests
+
+* Known junk: Submission 22 and its related records
+ Youjun's email of 2010-01-28:
+ "In table phylotree, many trees do not have a study_id value, but their phylotreenode related to study 22 via table taxonlabel.
+ Their phylotree_id are:
+ 1129,1130,1131,1132, 2333,2334,2335,2336,2337,2338, 2339,2340,2341,2343,2556,2557,2686,2726,2727,2787,2788,2789,2790,
+ 3446,3671,3766,3767,3901,3902,3903,3904,3905,3906,3907,3908,3909,3910,3911,3912, 4062,4063, 5705,5706,5707,5708,5720,5721,5921,5941,5981,160000022341
+ I will delete those trees because their foreign key constrain prevent me from cleaning up about 10,000 dummy taxonlabel records related to submission 22
+ (study 22 and submission 22 happen to be the same)."
+ VG: these IDs are from end-of-Jan treebasedev; the current treebasestage may have fewer.
+
+* Replace "owner" nulls (in submission.user_id?) by the special "migration" owner (see Bill's message of around 2010-02-02)
+ "Also because we have reverted to an older instance of the data, the migrated records contain have NULL in the user_id of the submission table. This causes null pointer errors for the unit tests. To fix the problem, I did the following:
+ 1. I created a user with username "migration" and email address "mig...@tr...". This is the dummy "user" who will now "own" all migrated submissions that lack an owner.
+ 2. I got the user_id for the "migration" user, and it happened to be 9955
+ 3. I ran this statement: "UPDATE submission SET user_id = 9955 WHERE user_id IS NULL"
+ Now all migrated data belong to the user "migration". "
+ Also, see "null->'tb1'; http://www.treebase.org/~piel/taxlabels_fix.zip; Study 22 (Jan 8)"
+
+
+
+Data cleanups on production:
+============================
+
+* Synch hibernate_sequence and {sub_matrix,sub_treebaseblock}.collection_id
+ [No need if prod is restored from a dump of stage made after ~2010-02-13]
+
+
\ No newline at end of file
Copied: trunk/treebase-core/db/schema/init_db_uptodate.pg (from rev 520, trunk/treebase-core/db/init_db_uptodate.pg)
===================================================================
--- trunk/treebase-core/db/schema/init_db_uptodate.pg (rev 0)
+++ trunk/treebase-core/db/schema/init_db_uptodate.pg 2010-03-05 21:19:07 UTC (rev 530)
@@ -0,0 +1,14 @@
+-- Populates an empty DB with tables and dictionary data
+-- corresponding to the most recent snapshot and all subsequent patches.
+
+\i snapshots/0000_SCHEMA_before_patches_start.sql
+\i snapshots/0000_DATA_before_patches_start.sql
+
+\i patches/0001_constraint-names.sql
+\i patches/0002_drop-geospot_id_sequence.sql
+\i patches/0003_rename-stepmatrixelement-pk.sql
+\i patches/0004_create-indexes.sql
+\i patches/0005_add-taxonabel-tb1legacyid.sql
+
+
+
Copied: trunk/treebase-core/db/schema/patches/0005_add-taxonabel-tb1legacyid.sql (from rev 520, trunk/treebase-core/db/patches/0005_add-taxonabel-tb1legacyid.sql)
===================================================================
--- trunk/treebase-core/db/schema/patches/0005_add-taxonabel-tb1legacyid.sql (rev 0)
+++ trunk/treebase-core/db/schema/patches/0005_add-taxonabel-tb1legacyid.sql 2010-03-05 21:19:07 UTC (rev 530)
@@ -0,0 +1,8 @@
+insert into versionhistory(patchnumber, patchlabel, patchdescription)
+ values (5, 'add-taxonlabel-tb1legacyid',
+ 'Add tb1legacyid field to the taxonlabel table');
+
+alter table taxonlabel add column tb1legacyid varchar(20);
+
+
+
\ No newline at end of file
Deleted: trunk/treebase-core/db/schema/snapshots/0000_DATA_before_patches_start.sql
===================================================================
--- trunk/treebase-core/db/snapshots/0000_DATA_before_patches_start.sql 2010-02-16 14:37:32 UTC (rev 510)
+++ trunk/treebase-core/db/schema/snapshots/0000_DATA_before_patches_start.sql 2010-03-05 21:19:07 UTC (rev 530)
@@ -1,236 +0,0 @@
--- 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...
[truncated message content] |
