Menu
▾
▴
treebase-guts
|
From: <vga...@us...> - 2010-02-08 17:08:24
|
Revision: 486
http://treebase.svn.sourceforge.net/treebase/?rev=486&view=rev
Author: vgapeyev
Date: 2010-02-08 17:07:55 +0000 (Mon, 08 Feb 2010)
Log Message:
-----------
Added a few details.
Modified Paths:
--------------
trunk/treebase-core/db/README.txt
trunk/treebase-core/db/TODO.txt
Modified: trunk/treebase-core/db/README.txt
===================================================================
--- trunk/treebase-core/db/README.txt 2010-02-08 00:23:34 UTC (rev 485)
+++ trunk/treebase-core/db/README.txt 2010-02-08 17:07:55 UTC (rev 486)
@@ -9,18 +9,14 @@
in Ruby or Python (but with scripts written in SQL and no automation).
The idea is that we store in SVN the initial snapshot of the DB schema
-and sequential parches that we have been developing w.r.t
+and sequential patches that we have been developing w.r.t
treebasedev. These patches will be used to bring up-to-date
treebasestage, and then treebaseprod. After that we can erase the
patches, refresh the snapshot, and continue for another cycle.
Official TB2 code releases are to be declared at the start of a cycle,
when there are no patches yet. The snapshot is what should be used to
-create a fresh TB2 instance.
+create a fresh TB2 instance by someone who wants to run their own.
-I suggest to create the directory treebase-code/db to store patches
-and snapshots. (Another option would be a separate project, treebase-
-db, a a sibling of treebase-core and treebase-web.)
-
Patches are to be sequentially numbered (by hand). To keep track
which DB instance has already been brought up to which patch version,
I suggest creating a special table with a number column. Each patch
@@ -28,3 +24,20 @@
number.
If this sounds ok, I hope to set this up by end of tomorrow.
+
+
+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.
+* 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
Modified: trunk/treebase-core/db/TODO.txt
===================================================================
--- trunk/treebase-core/db/TODO.txt 2010-02-08 00:23:34 UTC (rev 485)
+++ trunk/treebase-core/db/TODO.txt 2010-02-08 17:07:55 UTC (rev 486)
@@ -3,5 +3,44 @@
[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
+
+* Drop hibernate_sequence
+
* Correct PK name in stepmatrixelement and create stepmatrixelement_id_sequence
+
* Create indexes, as suggested by Bill 2010-02-03 (and maybe later)
+ 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
+ -------- -------
+ taxon name
+ taxonvariant fullname
+ 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
+
+
+Pending data cleanups (mostly, on staging):
+
+* 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 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". "
+
\ No newline at end of file
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
|
From: <vga...@us...> - 2010-02-16 21:16:05
|
Revision: 511
http://treebase.svn.sourceforge.net/treebase/?rev=511&view=rev
Author: vgapeyev
Date: 2010-02-16 21:15:56 +0000 (Tue, 16 Feb 2010)
Log Message:
-----------
Documenting the schema patching setup.
Modified Paths:
--------------
trunk/treebase-core/db/README.txt
trunk/treebase-core/db/TODO.txt
trunk/treebase-core/db/versionhistory.sql
Modified: trunk/treebase-core/db/README.txt
===================================================================
--- trunk/treebase-core/db/README.txt 2010-02-16 14:37:32 UTC (rev 510)
+++ trunk/treebase-core/db/README.txt 2010-02-16 21:15:56 UTC (rev 511)
@@ -1,60 +1,90 @@
-[This will contain instructions on how to do and commit patches.
-For now, it's just a message from my email 2010-02-03 --VG]
+This directory keeps track of schema patches applied to the DB as the application evolves,
+as well as occasional snapshots of the schema and pre-loaded dictionary-like data.
-"A setup/discipline for schema changes"
+Outline
+=======
-This brings up something that was on my back burner for a while:
-setting up a workflow and a file/directory structure to maintain
-schema patches. That would be something similar to migration scripts
-in Ruby or Python (but with scripts written in SQL and no automation).
+The setup is inspired by database migrations in Ruby and Python, but is not nearly as comprehensive.
-The idea is that we store in SVN the initial snapshot of the DB schema
-and sequential patches that we have been developing w.r.t
-treebasedev. These patches will be used to bring up-to-date
-treebasestage, and then treebaseprod. After that we can erase the
-patches, refresh the snapshot, and continue for another cycle.
-Official TB2 code releases are to be declared at the start of a cycle,
-when there are no patches yet. The snapshot is what should be used to
-create a fresh TB2 instance by someone who wants to run their own.
+The 'patches' directory stores SQL scripts (nnnn_descr_label.sql) that move the schema
+from one version to the next. Occasionally, we put into the 'snaphots' subdirectory
+nnnn_SCHEMA...sql and nnnn_DATA...sql that capture the state of the DB after the
+patches/nnnn.sql patch has been applied.
-Patches are to be sequentially numbered (by hand). To keep track
-which DB instance has already been brought up to which patch version,
-I suggest creating a special table with a number column. Each patch
-will have an UPDATE setting the number in the table to the patch's
-number.
+Patches are sequentially numbered (by hand). If a snapshot nnnn is present, it reflects
+the state of the DB after patch nnnn was applied.
-If this sounds ok, I hope to set this up by end of tomorrow.
+The schema (staring with the very first 0000 snapshot) contains the 'versionhistory' table,
+which is used to keep track which patches have already been applied to a particular DB instance.
+This is particularly useful in multi-instance installations (e.g., when there are
+development, staging, production, etc., instances). [See versionhistory.sql for the table's definition.]
-Some more details:
+Task-specific instructions
+==========================
-* Patch naming: 0001_short_name.sql
-* Each patch starts by incrementing the number in the version table.
-* Each patch will be executed as a transaction, but there is no need to insert transaction commands into the patch.
-* Patches and snapshots must not include any ownership or permissions commands.
-* Version table schema:
-CREATE TABLE versionhistory
-(
- id serial NOT NULL,
- patchnumber integer,
- patchlabel character varying(63) NOT NULL,
- patchdescription character varying(1023),
- applied timestamp (0) without time zone DEFAULT now(),
- CONSTRAINT versionhistory_pkey PRIMARY KEY (id),
- CONSTRAINT versionhistory_unique_patchnumber UNIQUE (patchnumber)
-)
-COMMENT ON TABLE versionhistory IS 'VersionHistory table is NOT a part of the TreeBase application. It is only used within the development and deployment process to keep track of schema patches applied to a DB instance. ';
+Creating a patch
+----------------
+A patch is generally committed into SVN together with Java code for the application.
+It is expected that, for a given SVN version, the application code expects to work
+w.r.t. the database containing all the patches from this version.
-To get a current schema snapshot
-================================
+Use the next sequential number and a short descriptive label to name a patch.
+To avoid numbering conflicts, developers must coordinate their patching activity.
-pg_dump -h treebase-dev.nescent.org -U treebase_app --format=p --no-owner --no-privileges --schema-only treebasestage > schema_snapshot_treebasestage_2010-02-01.sql
+Each patch must start by an INSERT of a row describing the patch into the versionhistory table.
+(See existing patches for examples.)
+Each patch will be executed as a transaction, so there is no need to insert
+transaction commands into the patch.
-To restore the schema into a new DB:
-====================================
+Patches must not include any ownership or permissions commands: ownership and
+permissions are installation-specific, and therefore should be handled by the
+instance maintainer.
+When an error is discovered in a recent patch, it may be ok to fix it and commit
+a new version under the same patch number, provided it is possible to adjust all DB
+instances to conform with the new patch version. This can be doable when the patch
+was only applied to the development DB instance. When the patch with an error was already
+applied to production databases, it could be more prudent to develop a new, error-fixing patch.
+
+
+Applying a patch
+----------------
+
+Apply each patch within a transaction, e.g.,
+psql -d yourdb -U yourusername
+yourdb=> begin transaction;
+yourdb=> \i nnnn_your_patch.sql
+yourdb=> commit; OR rollback;
+
+After that, adjust ownership and permissions on newly created objects, if any,
+as required by the DB instance.
+
+Use your judgment on what to do in case the patch application generated errors
+and you had to roll back. Most likely, someone has to work on improving the patch.
+
+
+Creating a current snapshot
+---------------------------
+
+Schema snapshots must not include any ownership or permissions commands, since these
+are installation-specific. This command,
+
+pg_dump -h your.host.url -U your_username --format=p --no-owner --no-privileges --schema-only yourdb > nnnn_your_label.sql
+
+creates a reasonably lean dump of the schema. However, if the DB contained any objects
+not created by prior snapshots and patches, they should be removed by hand.
+
+Data snapshot is trickier. The best bet right now is probably by hand-modifying the previous data snapshot.
+
+
+Creating a fresh DB
+------------------
+
+Use the most recent snapshot:
+
psql -d yourdb -U yourusername -f nnn_SCHEMA_xxx.sql
psql -d yourdb -U yourusername -f nnn_DATA_xxx.sql
@@ -64,4 +94,35 @@
yourdb=> begin transaction;
yourdb=> \i nnn_SCHEMA_xxx.sql
yourdb=> \i nnn_DATA_xxx.sql
-yourdb=> end transaction
+yourdb=> commit;
+
+
+Recommended development - production - staging workflow
+=======================================================
+
+Local instances: It is recommended that each developer has his own DB instance to use for writing
+and initially testing new versions of the application and schema patches. This ensures
+at least some basic compatibility between SQL and Java within a given SVN commit.
+These instances may contain only minimal data, as useful for the developer.
+
+Development instance: The dev DB instance is frequently re-built to track
+the most recent SVN version of the application and DB patches. It is used to verify correctness
+of new versions and to communicate with non-programmer project participants.
+This instance should contain sizable and representative amount of data,
+but not necessarily as much or as good quality as the production instance.
+
+Staging instance: The stage instance is a testbed for rolling out a new version on production.
+When development on dev reaches release, a staging instance is created by cloning the
+current production instance. This ensures that the possible new data quirks accumulated
+since the previous release are present. Then patches spanning from the previous to the current
+release are applied, new version of the application is installed, and the whole system is tested
+and verified as necessary. If all works well, then the release roll-out may proceed on production.
+Otherwise, back to development.
+
+Production instance: This is the instance with the authoritative data that is constantly being
+accessed by external uses and can tolerate only brief periods of downtime. Make a backup before
+rolling out a new release and only roll out releases that were successfully verified on staging.
+After a new release roll-out, it makes sense to use the prod instance to create a new
+schema-and-dictionaries snapshot.
+
+
\ No newline at end of file
Modified: trunk/treebase-core/db/TODO.txt
===================================================================
--- trunk/treebase-core/db/TODO.txt 2010-02-16 14:37:32 UTC (rev 510)
+++ trunk/treebase-core/db/TODO.txt 2010-02-16 21:15:56 UTC (rev 511)
@@ -1,43 +1,29 @@
Pending framework issues
========================
-* Prior to taking the 1st schema snapshot (from treestage or treedev?):
- - Create table versionhistory
-
-* Develop a script for getting a current snapshot of the initial (dictionary) data,
+* Would like a script for getting a current snapshot of the initial (dictionary) data,
to be used alongside with the schema snapshot command.
+ [A problem: simply designating some tables as "dictionaries" and others as "facts" would not work --
+ at least some tables (Phylochar, DiscreteCharState) contains both "dictionary" and "fact" records.]
+
+* Automation scripts for tasks, especially patch application, so that the sysadmin does not forget to wrap a transaction.
+
+* A better way for handling ownership and permissions in particular DB instances -- there is
+ too much manual work at the moment.
+
+* Less-intrusive permissions/ownership management. Currently, ownerships and permissions are handled by pg_owner, pg_admin functions that the
+ sysadmin creates in DB after the initial installation. Consequently, they get into the schema snapshops and have to be removed by hand.
Pending schema patches:
======================
-[To be put in as patches after the storage structure for them is in place in this directory -- VG 2010-02-04]:
+* Maybe some indices around matrices (in addition to the indices around trees that are already there).
-* Give explicit names to all constraints
-* Drop geospot_id_sequence -- there is no corresponding table
+Pending data cleanups (on staging):
+===================================
-* Correct PK name in stepmatrixelement and create stepmatrixelement_id_sequence
-
-* Create indexes, as suggested by Bill 2010-02-03 (and maybe later)
- Table Field
- -------- -------
- taxon name
- taxonvariant fullname
- taxonlabel taxonlabel
- citation title
- citation abstract
- person lastname
- phylotreenode taxonlabel_id, phylotree_id
- See:
- - Bill's message of 2010-02-03:
- - https://sourceforge.net/tracker/?func=detail&atid=1126676&aid=2945391&group_id=248804,
- - Also, compare to the end-of-Jan schema snapshot of treebasedev
-
-
-Pending data cleanups (mostly, on staging):
-===========================================
-
* Remove junk, particularly that created by tests
* Known junk: Submission 22 and its related records
Modified: trunk/treebase-core/db/versionhistory.sql
===================================================================
--- trunk/treebase-core/db/versionhistory.sql 2010-02-16 14:37:32 UTC (rev 510)
+++ trunk/treebase-core/db/versionhistory.sql 2010-02-16 21:15:56 UTC (rev 511)
@@ -1,3 +1,6 @@
+-- Table for tracking which schema patches have been applied to the current DB instance.
+-- This table is already in the snapshot 0000_SCHEMA_before_patches_start.sql
+
CREATE TABLE versionhistory
(
id serial NOT NULL,
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
|
From: <vga...@us...> - 2010-02-17 22:54:32
|
Revision: 513
http://treebase.svn.sourceforge.net/treebase/?rev=513&view=rev
Author: vgapeyev
Date: 2010-02-17 22:54:26 +0000 (Wed, 17 Feb 2010)
Log Message:
-----------
Misc, including
- a cumulative script for creating a fresh DB (init_db_uptodate.pg)
- 3 pre-defined user records in a fresh DB
Modified Paths:
--------------
trunk/treebase-core/db/README.txt
trunk/treebase-core/db/TODO.txt
trunk/treebase-core/db/snapshots/0000_DATA_before_patches_start.sql
Added Paths:
-----------
trunk/treebase-core/db/init_db_uptodate.pg
Modified: trunk/treebase-core/db/README.txt
===================================================================
--- trunk/treebase-core/db/README.txt 2010-02-17 22:50:46 UTC (rev 512)
+++ trunk/treebase-core/db/README.txt 2010-02-17 22:54:26 UTC (rev 513)
@@ -49,7 +49,9 @@
was only applied to the development DB instance. When the patch with an error was already
applied to production databases, it could be more prudent to develop a new, error-fixing patch.
+When adding a patch, add a line into init_db_uptodate.pg as well.
+
Applying a patch
----------------
@@ -79,21 +81,25 @@
Data snapshot is trickier. The best bet right now is probably by hand-modifying the previous data snapshot.
+After creating a new snapshot, update init_db_uptodate.pg: change the names of the snapshot scripts
+and remove all \i commands for the patch scripts.
+
Creating a fresh DB
------------------
+Use the init_db_uptodate.pg script, which should create the DB from the most recent snapshot
+and apply all the subsequent patches:
+
Use the most recent snapshot:
-psql -d yourdb -U yourusername -f nnn_SCHEMA_xxx.sql
-psql -d yourdb -U yourusername -f nnn_DATA_xxx.sql
+psql -d yourdb -U yourusername -f init_db_uptodate.pg
OR
psql -d yourdb -U yourusername
yourdb=> begin transaction;
-yourdb=> \i nnn_SCHEMA_xxx.sql
-yourdb=> \i nnn_DATA_xxx.sql
+yourdb=> \i init_db_uptodate.pg
yourdb=> commit;
Modified: trunk/treebase-core/db/TODO.txt
===================================================================
--- trunk/treebase-core/db/TODO.txt 2010-02-17 22:50:46 UTC (rev 512)
+++ trunk/treebase-core/db/TODO.txt 2010-02-17 22:54:26 UTC (rev 513)
@@ -24,6 +24,8 @@
Pending data cleanups (on staging):
===================================
+[Could be worthwhile to do PRIOR to Dec 2009 and Feb 2010 migrations.]
+
* Remove junk, particularly that created by tests
* Known junk: Submission 22 and its related records
@@ -36,7 +38,7 @@
(study 22 and submission 22 happen to be the same)."
VG: these IDs are from end-of-Jan treebasedev; the current treebasestage may have fewer.
-* Replace "owner" nulls by the special "migration" owner (see Bill's message of around 2010-02-02)
+* Replace "owner" nulls (in submission.user_id?) by the special "migration" owner (see Bill's message of around 2010-02-02)
"Also because we have reverted to an older instance of the data, the migrated records contain have NULL in the user_id of the submission table. This causes null pointer errors for the unit tests. To fix the problem, I did the following:
1. I created a user with username "migration" and email address "mig...@tr...". This is the dummy "user" who will now "own" all migrated submissions that lack an owner.
2. I got the user_id for the "migration" user, and it happened to be 9955
@@ -48,5 +50,6 @@
============================
* Synch hibernate_sequence and {sub_matrix,sub_treebaseblock}.collection_id
+ [No need if prod is restored from a dump of stage made after ~2010-02-13]
\ No newline at end of file
Added: trunk/treebase-core/db/init_db_uptodate.pg
===================================================================
--- trunk/treebase-core/db/init_db_uptodate.pg (rev 0)
+++ trunk/treebase-core/db/init_db_uptodate.pg 2010-02-17 22:54:26 UTC (rev 513)
@@ -0,0 +1,12 @@
+-- Populates an empty DB with tables and dictionary data
+-- corresponding to the most recent snapshot and all subsequent patches.
+
+\i snapshots/0000_SCHEMA_before_patches_start.sql
+\i snapshots/0000_DATA_before_patches_start.sql
+
+\i patches/0001_constraint-names.sql
+\i patches/0002_drop-geospot_id_sequence.sql
+\i patches/0003_rename-stepmatrixelement-pk.sql
+\i patches/0004_create-indexes.sql
+
+
Modified: trunk/treebase-core/db/snapshots/0000_DATA_before_patches_start.sql
===================================================================
--- trunk/treebase-core/db/snapshots/0000_DATA_before_patches_start.sql 2010-02-17 22:50:46 UTC (rev 512)
+++ trunk/treebase-core/db/snapshots/0000_DATA_before_patches_start.sql 2010-02-17 22:54:26 UTC (rev 513)
@@ -7,8 +7,9 @@
-- - Converted commented-out DB2 RESTART statements on PK columns to working Postgres RESTART statements on sequences.
-- - Added (6, 1, 'Retracted') to citationStatus
-- - Removed initial data from tables Person, User, Submission, Study
+-- VG 2010-02-27: initial entries into "user" and "person" tables
+
-
insert into citationStatus(citationstatus_id, version, description) values
(1, 1, 'In Prep'),
(2, 1, 'In Review'),
@@ -228,6 +229,22 @@
alter sequence userrole_id_sequence restart with 4;
+insert into person(person_id, version, lastname, firstname, mname, phone, email)
+ values
+ (1, 0, 'Tb2', 'Admin', '', '', 'tb2...@no...'),
+ (2, 0, 'Tb2', 'User', '', '', 'tb...@no...'),
+ (3, 0, 'Tb2', 'Editor', '', '', 'tb2...@no...');
+alter sequence person_id_sequence restart with 100;
+
+insert into "user"(user_id, version, username, password, userrole_id, person_id)
+ VALUES
+ (1, 0, 'tb2admin', '*', 1, 1),
+ (2, 0, 'tb2user', '*', 2, 2),
+ (3, 0, 'tb2editor', '*', 3, 3);
+alter sequence user_id_sequence restart with 100;
+
+
+
insert into studyStatus(studystatus_id, version, description) values
(1, 1, 'In Progress'),
(2, 1, 'Ready'),
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
|
From: <vga...@us...> - 2010-02-26 16:02:40
|
Revision: 520
http://treebase.svn.sourceforge.net/treebase/?rev=520&view=rev
Author: vgapeyev
Date: 2010-02-26 16:02:32 +0000 (Fri, 26 Feb 2010)
Log Message:
-----------
Patch to add taxonlabel.tb1legacyid field
Modified Paths:
--------------
trunk/treebase-core/db/init_db_uptodate.pg
Added Paths:
-----------
trunk/treebase-core/db/patches/0005_add-taxonabel-tb1legacyid.sql
Modified: trunk/treebase-core/db/init_db_uptodate.pg
===================================================================
--- trunk/treebase-core/db/init_db_uptodate.pg 2010-02-25 15:33:06 UTC (rev 519)
+++ trunk/treebase-core/db/init_db_uptodate.pg 2010-02-26 16:02:32 UTC (rev 520)
@@ -8,5 +8,7 @@
\i patches/0002_drop-geospot_id_sequence.sql
\i patches/0003_rename-stepmatrixelement-pk.sql
\i patches/0004_create-indexes.sql
+\i patches/0005_add-taxonabel-tb1legacyid.sql
+
Added: trunk/treebase-core/db/patches/0005_add-taxonabel-tb1legacyid.sql
===================================================================
--- trunk/treebase-core/db/patches/0005_add-taxonabel-tb1legacyid.sql (rev 0)
+++ trunk/treebase-core/db/patches/0005_add-taxonabel-tb1legacyid.sql 2010-02-26 16:02:32 UTC (rev 520)
@@ -0,0 +1,8 @@
+insert into versionhistory(patchnumber, patchlabel, patchdescription)
+ values (5, 'add-taxonlabel-tb1legacyid',
+ 'Add tb1legacyid field to the taxonlabel table');
+
+alter table taxonlabel add column tb1legacyid varchar(20);
+
+
+
\ No newline at end of file
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
|
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] |
|
From: <vga...@us...> - 2010-03-05 21:30:42
|
Revision: 531
http://treebase.svn.sourceforge.net/treebase/?rev=531&view=rev
Author: vgapeyev
Date: 2010-03-05 21:30:36 +0000 (Fri, 05 Mar 2010)
Log Message:
-----------
resolving dir deletion conflict
Removed Paths:
-------------
trunk/treebase-core/db/patches/
trunk/treebase-core/db/snapshots/
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
|
From: <vga...@us...> - 2010-03-09 20:45:40
|
Revision: 533
http://treebase.svn.sourceforge.net/treebase/?rev=533&view=rev
Author: vgapeyev
Date: 2010-03-09 20:45:33 +0000 (Tue, 09 Mar 2010)
Log Message:
-----------
Scripts for loading data from TB1 dumps
Added Paths:
-----------
trunk/treebase-core/db/tb1load/
trunk/treebase-core/db/tb1load/README.txt
trunk/treebase-core/db/tb1load/apple/
trunk/treebase-core/db/tb1load/data/
trunk/treebase-core/db/tb1load/doc-mjd.txt
trunk/treebase-core/db/tb1load/mesquite/
trunk/treebase-core/db/tb1load/scripts/
trunk/treebase-core/db/tb1load/scripts/all_steps.sh
trunk/treebase-core/db/tb1load/scripts/clean.sh
trunk/treebase-core/db/tb1load/scripts/common.sh
trunk/treebase-core/db/tb1load/scripts/copy_tb2code.sh
trunk/treebase-core/db/tb1load/scripts/dbinfo.sh
trunk/treebase-core/db/tb1load/scripts/deletematrix.sql
trunk/treebase-core/db/tb1load/scripts/step1a_load_matrices.sh
trunk/treebase-core/db/tb1load/scripts/step1b_load_trees.sh
trunk/treebase-core/db/tb1load/scripts/step1c_fix_matrices.sh
trunk/treebase-core/db/tb1load/scripts/step1d_fix_trees.sh
trunk/treebase-core/db/tb1load/scripts/step1e_load_dump.sh
trunk/treebase-core/db/tb1load/scripts/step2_taxon_intell.sh
trunk/treebase-core/db/tb1load/scripts/step2_taxon_intell.sql
trunk/treebase-core/db/tb1load/scripts/step3_load_citations.sh
trunk/treebase-core/db/tb1load/tb2classes/
trunk/treebase-core/db/tb1load/tb2jars/
Added: trunk/treebase-core/db/tb1load/README.txt
===================================================================
--- trunk/treebase-core/db/tb1load/README.txt (rev 0)
+++ trunk/treebase-core/db/tb1load/README.txt 2010-03-09 20:45:33 UTC (rev 533)
@@ -0,0 +1,151 @@
+Instructions and scripts for performing data loading from TB1 dumps to a TB2 postrges instance.
+[Put together by Vladimir Gapeyev in Feb-Mar 2010, based on Java tools developed Mark-Jason Dominus (see doc-mjd.txt)]
+
+The are two stages to the process: (1) setting up working environment and (2) running the loading scripts. The goal of the first stage is to extract a subset of the TB2 development environment sufficient to run loading on the server -- this circumvents the more complex task of re-creating a full-fledged development environment on the server.
+
+
+Contents of this directory
+==========================
+
+ tb2jars/ - placeholder for dependent JARs of Treebase2
+ tb2classes/ - placeholder for Treebase2 classes
+ apple/ - placeholder for MacOSX-specific JARs
+ mesquite/ - placeholder for Mesquite code
+ data/ - placeholder for the data files to be migrated
+
+ scripts/ - shell and SQL scripts to run for loading;
+ the scripts invoke code from tb2classes, which
+ relies on jars in tb2jars, mesquite, and apple.
+
+
+Stage 1: Set up working environment
+===================================
+
+You must have checked out from SVN and configured a working TB2 build environment (for the treebase, treebase-core, and treebase-web projects). This directory (treebase-core/db/tb1load) is a part of this environment and some of the following instructions rely on its relative location.
+
+Bring in necessary Treebase2 code
+---------------------------------
+
+ cd scripts; ./copy_tb2code.sh
+
+Assuming Treebase has been built, this script
+ copies JARS from treebase-web/target/treebase-web/WEB-INF/lib/
+ to tb2jars/
+ copies contents of treebase-core/target/classes/
+ to tb2classes/
+
+Bring in Mesquite code
+----------------------
+
+ copy contents of your Mesquite installation
+ to mesquite/
+
+- If you already have a working TB2 instance running under tomcat, it should point to a Mesquite installation from its config file $CATALINA_HOME/conf/Catalina/localhost/treebase-web.xml. Copy the contents of that directory into mesquite/.
+
+- Alternatively, if you have successfully built TB2 with Maven, you will have Mesquite in ~/.m2/repository/mesquite/mesquite/2.01.tb/mesquite-2.01.tb.jar.
+Unzip this archive and place its *contents* into mesquite/.
+
+Bring in Apple JARs
+-------------------
+
+Only if the run location will not be a Mac OSX unix:
+ JARs MRJToolkit.jar and ui.jar should be placed in tb2apple/
+
+[These are Apple-specific JARs, apparently loaded by Mesquite. Since Mesquite runs in "headless" mode, code from these JARs probably is not executed, so they are harmless.]
+
+On a Mac OSX machine, these could be at:
+
+/System/Library/Java/Extensions/MRJToolkit.jar /System/Library/Frameworks/JavaVM.framework/Versions/1.5.0/Classes/ui.jar
+
+Relocate to the run location
+----------------------------
+
+* Copy the whole tb1load/ directory (the current directory) to the run location, i.e. the location from which loading will be performed.
+
+ *** The rest of these instructions are for the run location. ***
+
+[Tip: The run location should probably be at the server machine that runs the postgresl instance, to avoid sending large amounts of data over the network.]
+
+
+Specify DB credentials and the Mesquite location
+------------------------------------------------
+
+* Edit tb2classes/jdbc.properties file, providing appropriate values for the properties:
+ jdbc.url
+ jdbc.username
+ jdbc.password
+ mesquite.folder_dir
+
+If the file does not exist, create it from tb2classes/jdbc.properties.example.
+
+mesquite.folder_dir should contain a path to the mesquite/ directory populated above (a relative path might work).
+
+* Edit scripts/dbinfo.sh to provide the same DB credentials, to be used in Step 2. Note: The user that runs this step must have full DDL rights, as it creates and removes several tables, constraints and functions.
+
+
+Stage 2: Prepare the data
+========================
+
+Download the data to be migrated and place it in the data/ directory, under the exact file names as listed below. The scripts expect these locations and file names.
+
+ data/
+ characters/ - contains Nexus matrix files ***.nex
+ trees/ - contains Nexus tree files ***.tre
+ dump.txt - metadata about studies
+ TI/ - taxon intelligence, 3 tab-separated files
+ taxa.tab
+ taxon_variants.tab
+ taxon_labels.tab
+ citations.txt - tab-separated EndNote file
+
+[Note: If you have several data sets, e.g. a testing set or several separate delta data sets, it could make sense to keep them in separate directories (with this structure) and link or move them to data/ when it is time to run the scripts.]
+
+
+Stage : Run the scripts
+========================
+
+There is a script, all_steps.sh that performs full loading.
+
+ cd scripts; ./all_steps.sh
+
+However, each loading step can be run separately. Consider doing this, as well as intermittent backups, if failures at each step are likely.
+
+On sizable data, the scripts can take hours (especially Steps 1a,b). Consider running them via cron, by putting in crontab something like
+35 11 * * * (cd ..../tb1load/scripts; ./step3_load_citations.sh ) &> migr_log.log
+
+
+Steps 1a,b: load matrices and trees
+-----------------------------------
+ ./step1a_load_matrices.sh
+ ./step1b_load_trees.sh
+
+These load matrices and trees into a study with the name "UPLOAD", creating one if needed. If you already have a "UPLOAD" study and do not want it to be affected, rename it first.
+
+Code for Step1a appears to contain memory leak and may run out oh heap space midway. The matrix being uploaded at that time will be left inconsistent. It should be scraped out of the DB (use deletematrix.sql) and reloaded in a separate run of Step1a.
+
+
+Step 1c: fix counts in the uploaded data
+----------------------------------------
+ ./step1c_fix_matrices.sh
+ ./step1d_fix_trees.sh
+
+
+Step 1e: load the dump containing metadata for studies
+------------------------------------------------------
+ ./step1e_load_dump.sh
+
+
+Step 2: load taxon intelligence
+-------------------------------
+ ./step2_taxon_intell.sh
+or
+ psql -f step2_taxon_intell.sql -h dbhost -U dbuser dbname
+
+This one requies the DB schema to be at least at Patch 0005.
+
+
+Step 3: load citations
+----------------------
+ ./step3_load_citations.sh
+
+This code does not work for "Book Section" enties. Comment them out and enter manually later.
Added: trunk/treebase-core/db/tb1load/doc-mjd.txt
===================================================================
--- trunk/treebase-core/db/tb1load/doc-mjd.txt (rev 0)
+++ trunk/treebase-core/db/tb1load/doc-mjd.txt 2010-03-09 20:45:33 UTC (rev 533)
@@ -0,0 +1,188 @@
+Most scripts in scripts/* invoke Java tools developed Mark-Jason Dominus.
+These tools are in treebase-core, in the package org.cipres.treebase.util.
+They are described in a message from Mark posted at
+http://sourceforge.net/apps/mediawiki/treebase/index.php?title=DataDumps#Migrating_TB1_-.3E_TB2_data
+and copied below.
+
+Understanding of this text is not necessary in order to run scripts/*, but may help if you need to work on the underlying Java tools.
+
+
+----------------------------------------------------------------------
+
+= Migrating TB1 -> TB2 data =
+
+1. Bill supplied the following data:
+
+ a. Nexus files, each containing either one tree or one matrix.
+
+ i. Matrix files are in ~/treebase-data/matrix/
+ ii. Tree files are in ~/treebase-data/tree/
+
+ b. "Dump.txt" file, describing the structure of studies, analyses, and
+ including author and citation information.
+
+ i. Most recent version in ~/treebase-data/Dump.txt
+ ii. Older versions in ~/treebase-data/Dump-YYYYMMDD.txt
+
+ c. Taxon data, as three tab-separated files in ~/treebase-data/taxon/:
+
+ i. T.tax, the taxon data
+ ii. TV.tab, the taxon variant data
+ iii. TL.tab, the taxon label data
+
+ Bill had a document explaining the format of these files, but I
+ don't know where my copy is; get it from him if you need it. The
+ format is also documented in the util/LoadTaxonData.java file.
+
+2. My importing programs are in three places:
+
+ a. Nexus file importer and miscellaneous utilities are in:
+
+ treebase-core/src/main/java/org/cipres/treebase/util
+
+ henceforward called "~util".
+
+ b. Dump.txt loader:
+
+ treebase-core/src/main/java/org/cipres/treebase/auxdata
+
+ henceforward called "~auxdata".
+
+ c. Perl utilities:
+
+ /home/mjd/t/treebase-core/src/main/perl
+
+ henceforward called "~perl". There are a number of useful
+ utilities in ~perl/bin that you should take a look at.
+
+3. Running standalone programs
+
+ It was not clear to me that there was an easy way to run a
+ standalone command-line-type utility in the Spring/Hibernate
+ environment without binding it to a web page. I built the
+ Standalone interface to address this.
+
+ To run one of these programs, say BulkUpload, do this:
+
+ cd treebase-core/target/classes
+ jrun -D M2_REPO=path/to/m2_repository org.cipress.treebase.util.BulkUpload <args...>
+
+
+ I use $HOME/.m2/repository for M2_REPO.
+
+ The purpose of "jrun" is to assemble the correct classpath (by
+ examining the .classpath files written out by Eclipse) and execute
+ the Java interpreter. The complete classpath is really big, and it
+ was helpful to have this utility to figure it out.
+
+ "jrun" is in ~perl/jrun. It has some other command-line options;
+ see the source code.
+
+3. To import data:
+
+ a. Load in the nexus files with ~util/BulkUpload. BulkUpload gets
+ a list of file or directory names as arguments. If given a
+ directory name, it processes all the files contained in the
+ directory.
+
+ b. The code Jin wrote to import nexus files forgets to set the row
+ and column count on matrices, and the node count on trees. So
+ now run ~util/SetMatrixNChar and ~util/SetTreeNChar to adjust
+ these correctly. These get no arguments.
+
+ c. Process the Dump.txt file. The utility for this in
+ util/AuxiliaryDataImporter. To invoke:
+
+ jrun -D M2_REPO=... org.cipres.treebase.util.AuiliaryDataImporter [flags...] Dump.txt
+
+ If you omit "Dump.txt", it will try to read from some hardwired
+ default path.
+
+ The program will read and parse the Dump.txt file, which
+ contains a series of sections, one for each study. The program
+ will construct a representation of each study, and perform an
+ action for each one.
+
+ The default action, defined by CommitStudy.java, is to create a
+ study and its associated analyses, link the appropriate Matrix
+ and Tree objects, and generally try to build the study as
+ defined in the Dump.txt file. If a section calls for it to
+ create a study with a TB1 legacy ID number that is already in
+ the database, it will skip that section.
+
+ Command-line flags:
+
+ -a ActionClass. The ActionClass must implement interface
+ CompleteStudyAction. The default is CommitStudy.
+
+ -l logFileName. Write diagnostics to specified log file instead
+ of standard error.
+
+ -n. Say what would be done, but don't actually do anything.
+
+ -t. Set "testing mode" flag. Interpretation of this is up to
+ the specific action class. For the default, it probebly tells
+ the action to set the "TestMode" flag in Study and Submission
+ objects that it creates.
+
+ -s ID,ID,ID... Process only those studies with the specified
+ legacy ID numbers. (Default: Process all studies.)
+
+4. Check results
+
+ a. Look for missing objects.
+
+ util/WhatsMissing checks the contents of the database against a
+ census file that lists the objects that should be there
+ post-migration. It uses the Java resrouce path to find the
+ census files. The most recent census files I have are in
+ treebase-core/src/main/resources/{matrices,studies,trees}.lst.
+
+ Command-line options:
+
+ -s : Check for missing studie.
+ -m : Check for missing matrices.
+ -t : Check for missing trees.
+
+ If none of -s, -m, or -t is supplied, the program checks all three.
+
+ -Q : Quiet mode.
+
+ b. Check database consistency.
+
+ perl/bin/check is a program that understands a lot of the
+ proper structure of TB2 objects and does a variety of
+ consistency checks on the database. To run:
+
+ check ObjectType ID#
+
+ Where ObjectType is something like "Study" or "Matrix". Use
+ "check -X" to get a list of legal values for this argument.
+
+ To extend the checker to understand more object types, add them
+ to ~perl/lib/CIPRES/TreeBase/TreeBaseObjects.pm. Email me if
+ you need to do this; I will be able to explain what is needed.
+
+ I suggest that you do something like this:
+
+ for id in `sel study_id from study`; do
+ echo $id
+ check Study $id > S$id.out 2> S$id.err
+ done
+
+ And then go away for a while. Any consistency failures will be
+ reported on a line with a leading '***'. The program in
+ ~perl/bin/digester may be useful in going over the consistency
+ checker's reports: It aggregates similar error messages into
+ groups, and creates a file for each group of messages. So after
+ running 'check' as above, you may
+
+ grep '\*\*\*' S*.err | digester
+
+ to get an overview of what sorts of failures occurred, and how
+ many times each. Use '-d directoryName' to have it put the
+ digested reports into some subdirectory.
+
+
+
+As always, please email me if you have any questions.
Added: trunk/treebase-core/db/tb1load/scripts/all_steps.sh
===================================================================
--- trunk/treebase-core/db/tb1load/scripts/all_steps.sh (rev 0)
+++ trunk/treebase-core/db/tb1load/scripts/all_steps.sh 2010-03-09 20:45:33 UTC (rev 533)
@@ -0,0 +1,18 @@
+#!/bin/bash -e
+# -e Exit immediately if a command exits with a non-zero status.
+
+
+./step1a_load_matrices.sh
+
+./step1b_load_trees.sh
+
+./step1c_fix_matrices.sh
+
+./step1d_fix_trees.sh
+
+./step1e_load_dump.sh
+
+./step2_taxon_intell.sh
+
+./step3_load_citations.sh
+
Property changes on: trunk/treebase-core/db/tb1load/scripts/all_steps.sh
___________________________________________________________________
Added: svn:executable
+ *
Added: trunk/treebase-core/db/tb1load/scripts/clean.sh
===================================================================
--- trunk/treebase-core/db/tb1load/scripts/clean.sh (rev 0)
+++ trunk/treebase-core/db/tb1load/scripts/clean.sh 2010-03-09 20:45:33 UTC (rev 533)
@@ -0,0 +1,7 @@
+#!/bin/bash
+
+rm -rf ../mesquite/*
+rm -rf ../apple/*
+rm -rf ../tb2classes/*
+rm -rf ../tb2jars/*
+rm -rf ../data/*
Property changes on: trunk/treebase-core/db/tb1load/scripts/clean.sh
___________________________________________________________________
Added: svn:executable
+ *
Added: trunk/treebase-core/db/tb1load/scripts/common.sh
===================================================================
--- trunk/treebase-core/db/tb1load/scripts/common.sh (rev 0)
+++ trunk/treebase-core/db/tb1load/scripts/common.sh 2010-03-09 20:45:33 UTC (rev 533)
@@ -0,0 +1,13 @@
+
+TB2JARS=../tb2jars
+TB2CLASSES=../tb2classes
+APPLE=../apple
+DATA=../data
+
+CP=$TB2JARS/spring-2.0.7.jar:$TB2JARS/hibernate-3.2.6.ga.jar:$TB2JARS/log4j-1.2.13.jar:$TB2JARS/commons-logging-1.1.jar:$TB2JARS/c3p0-0.9.1.2.jar:$TB2JARS/dom4j-1.6.1.jar:$TB2JARS/hibernate-annotations-3.3.1.GA.jar:$TB2JARS/jta-1.1.jar:$TB2JARS/hibernate-commons-annotations-3.0.0.ga.jar:$TB2JARS/acegi-security-1.0.1.jar:$TB2JARS/ejb3-persistence-1.0.1.GA.jar:$TB2JARS/commons-collections-3.1.jar:$TB2JARS/jdom-1.1.jar:$TB2JARS/postgresql-8.3-603.jdbc3.jar:$TB2JARS/ehcache-1.2.3.jar:$TB2JARS/cglib-nodep-2.1_3.jar:$TB2JARS/mesquite-2.01.tb.jar:$TB2JARS/antlr-2.7.2.jar:$TB2JARS/tolbaseclasses-mesquite.jar:$TB2JARS/nexml-1.5-SNAPSHOT.jar
+
+CP=$CP:$APPLE/MRJToolkit.jar:$APPLE/ui.jar
+
+CP=$CP:$TB2CLASSES
+
+JAVA="java -Xmx2048M -cp $CP"
Property changes on: trunk/treebase-core/db/tb1load/scripts/common.sh
___________________________________________________________________
Added: svn:executable
+ *
Added: trunk/treebase-core/db/tb1load/scripts/copy_tb2code.sh
===================================================================
--- trunk/treebase-core/db/tb1load/scripts/copy_tb2code.sh (rev 0)
+++ trunk/treebase-core/db/tb1load/scripts/copy_tb2code.sh 2010-03-09 20:45:33 UTC (rev 533)
@@ -0,0 +1,12 @@
+#!/bin/bash
+
+# must be executed from data/scripts
+
+CMD="cp -R ../../../../treebase-web/target/treebase-web/WEB-INF/lib/ ../tb2jars/"
+echo $CMD
+$CMD
+
+CMD="cp -R ../../../../treebase-core/target/classes/ ../tb2classes/"
+echo $CMD
+$CMD
+
Property changes on: trunk/treebase-core/db/tb1load/scripts/copy_tb2code.sh
___________________________________________________________________
Added: svn:executable
+ *
Added: trunk/treebase-core/db/tb1load/scripts/dbinfo.sh
===================================================================
--- trunk/treebase-core/db/tb1load/scripts/dbinfo.sh (rev 0)
+++ trunk/treebase-core/db/tb1load/scripts/dbinfo.sh 2010-03-09 20:45:33 UTC (rev 533)
@@ -0,0 +1,3 @@
+DBHOST=localhost
+DBNAME=xxxx
+DBUSER=yyyy
Property changes on: trunk/treebase-core/db/tb1load/scripts/dbinfo.sh
___________________________________________________________________
Added: svn:executable
+ *
Added: trunk/treebase-core/db/tb1load/scripts/deletematrix.sql
===================================================================
--- trunk/treebase-core/db/tb1load/scripts/deletematrix.sql (rev 0)
+++ trunk/treebase-core/db/tb1load/scripts/deletematrix.sql 2010-03-09 20:45:33 UTC (rev 533)
@@ -0,0 +1,44 @@
+-- Use this script to delete a matrix with id NNNN.
+-- (Useful for matrices in which Step1a broke.)
+-- Replace NNNN by your matrix_id
+
+begin transaction;
+
+delete from codonnoncoding_colrange r
+ using codonpositionset p, matrix m
+ where r.codonpositionset_id = p.codonpositionset_id
+ and p.matrix_id = m.matrix_id
+ and m.matrix_id = NNNN
+returning *;
+
+update matrix
+ set codonpositionset_id = null
+where matrix_id = NNNN
+returning *;
+
+delete from codonpositionset where matrix_id = NNNN
+returning *;
+
+delete from sub_matrix where matrix_id = NNNN
+returning *;
+
+delete from matrixelement e
+using matrixrow r, matrixcolumn c
+where e.matrixrow_id = r.matrixrow_id and r.matrix_id = NNNN
+ and e.matrixcolumn_id = c.matrixcolumn_id and c.matrix_id = NNNN
+returning *;
+
+
+delete from matrixcolumn where matrix_id = NNNN
+returning *;
+
+delete from matrixrow where matrix_id = NNNN
+returning *;
+
+
+delete from matrix where matrix_id = NNNN
+returning *;
+
+
+commit transaction;
+
Added: trunk/treebase-core/db/tb1load/scripts/step1a_load_matrices.sh
===================================================================
--- trunk/treebase-core/db/tb1load/scripts/step1a_load_matrices.sh (rev 0)
+++ trunk/treebase-core/db/tb1load/scripts/step1a_load_matrices.sh 2010-03-09 20:45:33 UTC (rev 533)
@@ -0,0 +1,13 @@
+#!/bin/bash -e
+
+source common.sh
+
+MATRICES=$DATA/characters
+
+echo "######################################################################"
+echo "############ Step 1a: load character matrices from $MATRICES #########"
+echo "######################################################################"
+
+CMD="$JAVA org.cipres.treebase.util.BulkUpload $MATRICES"
+echo $CMD
+$CMD
Property changes on: trunk/treebase-core/db/tb1load/scripts/step1a_load_matrices.sh
___________________________________________________________________
Added: svn:executable
+ *
Added: trunk/treebase-core/db/tb1load/scripts/step1b_load_trees.sh
===================================================================
--- trunk/treebase-core/db/tb1load/scripts/step1b_load_trees.sh (rev 0)
+++ trunk/treebase-core/db/tb1load/scripts/step1b_load_trees.sh 2010-03-09 20:45:33 UTC (rev 533)
@@ -0,0 +1,13 @@
+#!/bin/bash -e
+
+source common.sh
+
+TREES=$DATA/trees
+
+echo "######################################################################"
+echo "############ Step 1b: load trees from $TREES ############"
+echo "######################################################################"
+
+CMD="$JAVA org.cipres.treebase.util.BulkUpload $TREES"
+echo $CMD
+$CMD
Property changes on: trunk/treebase-core/db/tb1load/scripts/step1b_load_trees.sh
___________________________________________________________________
Added: svn:executable
+ *
Added: trunk/treebase-core/db/tb1load/scripts/step1c_fix_matrices.sh
===================================================================
--- trunk/treebase-core/db/tb1load/scripts/step1c_fix_matrices.sh (rev 0)
+++ trunk/treebase-core/db/tb1load/scripts/step1c_fix_matrices.sh 2010-03-09 20:45:33 UTC (rev 533)
@@ -0,0 +1,12 @@
+#!/bin/bash -e
+
+source common.sh
+
+echo "######################################################################"
+echo "############ Step 1c: fix counts in matrices ############"
+echo "######################################################################"
+
+CMD="$JAVA org.cipres.treebase.util.SetMatrixNChar"
+echo $CMD
+$CMD
+
Property changes on: trunk/treebase-core/db/tb1load/scripts/step1c_fix_matrices.sh
___________________________________________________________________
Added: svn:executable
+ *
Added: trunk/treebase-core/db/tb1load/scripts/step1d_fix_trees.sh
===================================================================
--- trunk/treebase-core/db/tb1load/scripts/step1d_fix_trees.sh (rev 0)
+++ trunk/treebase-core/db/tb1load/scripts/step1d_fix_trees.sh 2010-03-09 20:45:33 UTC (rev 533)
@@ -0,0 +1,11 @@
+#!/bin/bash -e
+
+source common.sh
+
+echo "######################################################################"
+echo "############ Step 1c: fix counts in trees ############"
+echo "######################################################################"
+
+CMD="$JAVA org.cipres.treebase.util.SetTreeNChar"
+echo $CMD
+$CMD
Property changes on: trunk/treebase-core/db/tb1load/scripts/step1d_fix_trees.sh
___________________________________________________________________
Added: svn:executable
+ *
Added: trunk/treebase-core/db/tb1load/scripts/step1e_load_dump.sh
===================================================================
--- trunk/treebase-core/db/tb1load/scripts/step1e_load_dump.sh (rev 0)
+++ trunk/treebase-core/db/tb1load/scripts/step1e_load_dump.sh 2010-03-09 20:45:33 UTC (rev 533)
@@ -0,0 +1,11 @@
+#!/bin/bash -e
+
+source common.sh
+
+echo "######################################################################"
+echo "############ Step 1d: Loading Study info from the dump ############"
+echo "######################################################################"
+
+CMD="$JAVA org.cipres.treebase.util.AuxiliaryDataImporter $DATA/dump.txt"
+echo $CMD
+$CMD
Property changes on: trunk/treebase-core/db/tb1load/scripts/step1e_load_dump.sh
___________________________________________________________________
Added: svn:executable
+ *
Added: trunk/treebase-core/db/tb1load/scripts/step2_taxon_intell.sh
===================================================================
--- trunk/treebase-core/db/tb1load/scripts/step2_taxon_intell.sh (rev 0)
+++ trunk/treebase-core/db/tb1load/scripts/step2_taxon_intell.sh 2010-03-09 20:45:33 UTC (rev 533)
@@ -0,0 +1,11 @@
+#!/bin/bash -e
+
+source common.sh
+source dbinfo.sh
+
+echo "######################################################################"
+echo "############ Step 2: load taxon intelligence ############"
+echo "######################################################################"
+
+psql -f step2_taxon_intell.sql -h $DBHOST -U $DBUSER $DBNAME
+
Property changes on: trunk/treebase-core/db/tb1load/scripts/step2_taxon_intell.sh
___________________________________________________________________
Added: svn:executable
+ *
Added: trunk/treebase-core/db/tb1load/scripts/step2_taxon_intell.sql
===================================================================
--- trunk/treebase-core/db/tb1load/scripts/step2_taxon_intell.sql (rev 0)
+++ trunk/treebase-core/db/tb1load/scripts/step2_taxon_intell.sql 2010-03-09 20:45:33 UTC (rev 533)
@@ -0,0 +1,117 @@
+-- Upload Taxon Intelligence dumps into DB.
+-- Input data comes from several locations hard-wired into the 3 \copy commands.
+
+-- This script uses DDL to create temporary tables and functions, but it takes care
+-- to clean after itself, so there is no net effect on the schema.
+
+BEGIN TRANSACTION;
+
+----------------------------------------------------------------------
+\echo 'Clean old TI data'
+update taxonlabel
+ set taxonvariant_id = NULL;
+
+-- Drop the constraints temporarily.
+-- Their presence slows down immencely deletions from TaxonVariant Taxon.
+
+alter table taxonlabel drop constraint taxonlabel_fkto_taxonvariant;
+
+delete from taxonvariant;
+
+alter table taxonlabel
+ add constraint taxonlabel_fkto_taxonvariant
+ FOREIGN KEY (taxonvariant_id) REFERENCES taxonvariant (taxonvariant_id);
+
+
+alter table taxonset_taxon drop constraint taxonset_taxon_fkto_taxon;
+alter table taxonlink drop constraint taxonlink_fkto_taxon;
+alter table taxonvariant drop constraint taxonvariant_fkto_taxon;
+
+delete from taxon;
+
+alter table taxonset_taxon
+ add constraint taxonset_taxon_fkto_taxon
+ FOREIGN KEY (taxon_id) REFERENCES taxon (taxon_id);
+alter table taxonlink
+ add constraint taxonlink_fkto_taxon
+ FOREIGN KEY (taxon_id) REFERENCES taxon (taxon_id);
+alter table taxonvariant
+ add constraint taxonvariant_fkto_taxon
+ FOREIGN KEY (taxon_id) REFERENCES taxon (taxon_id);
+
+
+----------------------------------------------------------------------
+\echo 'Upload Taxa and Taxon Variants into their tables'
+
+\copy taxon (taxon_id, ubionamebankid, name, ncbitaxid) from '../data/TI/taxa.tab'
+
+\copy taxonvariant (taxonvariant_id, taxon_id, namebankid, name, fullname, lexicalqualifier) from '../data/TI/taxon_variants.tab'
+
+update taxon set
+ version = 1,
+ tb1legacyid = taxon_id;
+update taxonvariant set
+ version = 1,
+ tb1legacyid = taxonvariant_id;
+
+
+----------------------------------------------------------------------
+\echo 'Upload TaxonLabels into a temporary table tb1taxonlabel'
+CREATE TABLE tb1taxonlabel
+(
+ tb1taxonlabel_id bigint NOT NULL,
+ taxonvariant_id bigint,
+ legacy_id character varying(20),
+ taxonlabel character varying(255),
+ CONSTRAINT tb1taxonlabel_pkey PRIMARY KEY (tb1taxonlabel_id),
+ CONSTRAINT tb1taxonlabel_fkto_taxonvariant FOREIGN KEY (taxonvariant_id)
+ REFERENCES taxonvariant (taxonvariant_id)
+);
+
+\copy tb1taxonlabel (tb1taxonlabel_id, taxonvariant_id, legacy_id, taxonlabel) from '../data/TI/taxon_labels.tab'
+
+
+----------------------------------------------------------------------
+\echo 'Update fields in TaxonLabel with values from tb1taxonlabel'
+update taxonlabel as tb2
+ set taxonvariant_id = tb1.taxonvariant_id,
+ tb1legacyid = tb1.legacy_id,
+ version = 1
+from tb1taxonlabel as tb1
+where tb2.taxonlabel = tb1.taxonlabel;
+
+
+----------------------------------------------------------------------
+\echo 'Drop the temporary table tb1taxonlabel'
+drop table tb1taxonlabel;
+
+
+----------------------------------------------------------------------
+\echo 'Recompute taxon_id_sequence and taxonvariant_id_sequence'
+create or replace function recompute_sequence (tablename varchar) returns void as $$
+BEGIN
+ execute 'create table tmp_newstart(val bigint)';
+ execute 'insert into tmp_newstart '||
+ 'select (div(max('||tablename||'_id), 10000)+1)*10000 from '||tablename;
+ DECLARE
+ start_cursor cursor for select * from tmp_newstart;
+ start_value bigint;
+ BEGIN
+ open start_cursor;
+ fetch start_cursor into start_value;
+ close start_cursor;
+ execute 'alter sequence '||tablename||'_id_sequence restart with '||start_value;
+ execute 'drop table tmp_newstart';
+ raise notice 'New start value for %_id_sequence: %', tablename, start_value;
+ END;
+END;
+$$ language 'plpgsql';
+
+select recompute_sequence ('taxon');
+select recompute_sequence ('taxonvariant');
+
+drop function recompute_sequence(varchar);
+
+----------------------------------------------------------------------
+
+COMMIT TRANSACTION;
Added: trunk/treebase-core/db/tb1load/scripts/step3_load_citations.sh
===================================================================
--- trunk/treebase-core/db/tb1load/scripts/step3_load_citations.sh (rev 0)
+++ trunk/treebase-core/db/tb1load/scripts/step3_load_citations.sh 2010-03-09 20:45:33 UTC (rev 533)
@@ -0,0 +1,14 @@
+#!/bin/bash -e
+
+source common.sh
+
+CITATIONS=$DATA/citations.txt
+
+
+echo "######################################################################"
+echo "############ Step 3: load citations $CITATIONS ############"
+echo "######################################################################"
+
+CMD="$JAVA org.cipres.treebase.util.CitationDataImporter $CITATIONS"
+echo $CMD
+$CMD
Property changes on: trunk/treebase-core/db/tb1load/scripts/step3_load_citations.sh
___________________________________________________________________
Added: svn:executable
+ *
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
|
From: <vga...@us...> - 2010-03-09 22:27:34
|
Revision: 534
http://treebase.svn.sourceforge.net/treebase/?rev=534&view=rev
Author: vgapeyev
Date: 2010-03-09 22:27:21 +0000 (Tue, 09 Mar 2010)
Log Message:
-----------
A directory to keep track of data cleaning tasks
Modified Paths:
--------------
trunk/treebase-core/db/schema/TODO.txt
Added Paths:
-----------
trunk/treebase-core/db/cleaning/
trunk/treebase-core/db/cleaning/2010-03-03_fixlabels/
trunk/treebase-core/db/cleaning/2010-03-03_fixlabels/README.txt
trunk/treebase-core/db/cleaning/2010-03-03_fixlabels/fixlabels_trans.pl
trunk/treebase-core/db/cleaning/2010-03-03_fixlabels/output.txt
trunk/treebase-core/db/cleaning/README.txt
trunk/treebase-core/db/cleaning/TODO.txt
Added: trunk/treebase-core/db/cleaning/2010-03-03_fixlabels/README.txt
===================================================================
--- trunk/treebase-core/db/cleaning/2010-03-03_fixlabels/README.txt (rev 0)
+++ trunk/treebase-core/db/cleaning/2010-03-03_fixlabels/README.txt 2010-03-09 22:27:21 UTC (rev 534)
@@ -0,0 +1,22 @@
+A perl script from Bill to remove duplicate taxon labels.
+
+Before running, adjust passwords in fixlabels_trans.pl
+
+Was run 2010-03-03, after Dec'2009 matrices and trees were loaded, but prior to loading Dec'2009 Taxon intelligence.
+
+output.txt contains the output from this script.
+
+The stdout was:
+The database starts with 101401 redundant records.
+The database ends with 0 redundant records.
+
+
+
+This query checks whether there are duplicate taxon labels.
+
+SELECT COUNT(*) FROM
+(SELECT study_id, taxonlabel, COUNT(taxonlabel_id)
+FROM taxonlabel WHERE study_id IS NOT NULL
+GROUP BY study_id, taxonlabel HAVING COUNT(taxonlabel_id) > 1
+ORDER BY COUNT(taxonlabel_id) DESC) AS countrecs;
+
Added: trunk/treebase-core/db/cleaning/2010-03-03_fixlabels/fixlabels_trans.pl
===================================================================
--- trunk/treebase-core/db/cleaning/2010-03-03_fixlabels/fixlabels_trans.pl (rev 0)
+++ trunk/treebase-core/db/cleaning/2010-03-03_fixlabels/fixlabels_trans.pl 2010-03-09 22:27:21 UTC (rev 534)
@@ -0,0 +1,167 @@
+#!/usr/bin/perl
+
+use strict;
+use warnings;
+use DBI;
+
+# This script is intended to remove all taxonlabel records that are duplicates within the
+# same study. We start by getting a list of all studies. Then for each study_id, we get
+# a unique list of taxonlabels, picking from among the duplicates one taxonlabel_id that
+# will survive the culling; then we pick out a taxonvariant_id so that we can be sure that
+# the surviving taxonlabel has a mapping to taxonvariants. For each taxonlabel_id that
+# is destined to be deleted, we first update all tables that use it as a foreign key
+# and modify them to use the taxonlabel_id of the surviving record. Note that we cannot
+# just update the corresponding record in sub_taxonlabel because that would violate the
+# constraint that taxonlabel_id in sub_taxonlabel be unique. Instead, we delete any
+# sub_taxonlabel records that have a taxonlabel_id that is determined to be redundant.
+
+# For a local database:
+
+my $database = "tb2synops";
+my $username = "piel";
+my $password = "";
+
+# For the remote database, uncomment this (and also uncomment line 142):
+
+# my $database = "treebasedev";
+# my $username = "treebase_app";
+# my $password = "tim5tema";
+
+
+my $dbh = &ConnectToPg($database, $username, $password);
+
+# Let's start by counting the number of redundant taxonlabel_ids:
+
+my $count = q|SELECT COUNT(*) FROM |;
+$count .= q|(SELECT study_id, taxonlabel, COUNT(taxonlabel_id) |;
+$count .= q|FROM taxonlabel WHERE study_id IS NOT NULL |;
+$count .= q|GROUP BY study_id, taxonlabel HAVING COUNT(taxonlabel_id) > 1 |;
+$count .= q|ORDER BY COUNT(taxonlabel_id) DESC) AS countrecs |;
+
+my $totRec = $dbh->selectrow_array ($count);
+print "The database starts with $totRec redundant records.\n";
+
+# Assuming that the study_id column in taxonlabel is accurate,
+# lets get a distinct list of all study_ids
+
+my $query = q|SELECT DISTINCT study_id FROM taxonlabel WHERE study_id IS NOT NULL |;
+
+my $sth = $dbh->prepare($query)
+or die "Can't prepare $query: $dbh->errstr\n";
+my $rv = $sth->execute
+or die "can't execute the query: $sth->errstr\n";
+
+my @studyids; #unique list of all study_ids, as taken from the taxonlabel table
+
+while(my @row = $sth->fetchrow_array) {
+ push(@studyids, $row[0]);
+}
+my $rd = $sth->finish;
+
+$query = qq|SELECT taxonlabel, MIN(taxonlabel_id), MAX(taxonvariant_id) FROM taxonlabel |;
+$query .= qq|WHERE study_id = ? GROUP BY taxonlabel HAVING COUNT(taxonlabel_id) > 1 |;
+my $sth_getuniques = $dbh->prepare($query) or die "Can't prepare $query: $dbh->errstr\n";
+
+my $file ="output.txt";
+open(OUTPUT,">$file") || die "Content-type: text/html\n\nCannot open $file!";
+
+foreach my $stid ( @studyids ) {
+ my %taxonlabels;
+
+ # get a unique list of taxonlabel values and store the lowest taxonlabel_id,
+ # and the highest taxonvariant_id
+
+ my $rv = $sth_getuniques->execute( $stid );
+ while(my @row = $sth_getuniques->fetchrow_array) {
+ $taxonlabels{ $row[0] } = [ $row[1], $row[2] ];
+ }
+ my $rd = $sth_getuniques->finish;
+
+ # get a list of taxonlabel_ids that need to be deleted (ie they are redundant)
+ $query = qq|SELECT taxonlabel_id FROM taxonlabel WHERE taxonlabel = ? AND study_id = ? AND taxonlabel_id <> ? |;
+ my $sth_getdups = $dbh->prepare($query) or die "Can't prepare $query: $dbh->errstr\n";
+
+ foreach my $uniquetaxonlabel ( keys(%taxonlabels) ) {
+
+ eval {
+ my $dup_taxlabid;
+ my $rv = $sth_getdups->execute( $uniquetaxonlabel, $stid, $taxonlabels{$uniquetaxonlabel}[0] );
+ while(my @row = $sth_getdups->fetchrow_array) {
+ $dup_taxlabid = $row[0];
+
+ # first check that the sub_taxonlabel table has a record for the surviving taxonlabel_id
+
+ my $sub_taxonlabel_count = "SELECT COUNT(*) FROM sub_taxonlabel WHERE taxonlabel_id = " . $taxonlabels{$uniquetaxonlabel}[0] ;
+ my $sub_totRec = $dbh->selectrow_array ($sub_taxonlabel_count);
+
+ if ( $sub_totRec == 1) {
+
+ # now that we have a taxonlabel_id that can be deleted, let's update all records in
+ # other tables that use this id so that they use the taxonlabel_id of the surviving taxonlabel record
+
+ # we are ready to delete the sub_taxonlabel record that has our $dup_taxlabid
+ $dbh->do( "DELETE FROM sub_taxonlabel WHERE taxonlabel_id = ? ", undef, $dup_taxlabid );
+
+ # let's update the tables that have a FK with $dup_taxlabid so that they point to the surviving record
+ foreach my $tble ( 'phylotreenode', 'rowsegment', 'taxonlabelgroup_taxonlabel', 'taxonlabelset_taxonlabel', 'matrixrow' ) {
+ $dbh->do( "UPDATE $tble SET taxonlabel_id = ? WHERE taxonlabel_id = ? ", undef, $taxonlabels{$uniquetaxonlabel}[0], $dup_taxlabid );
+ }
+
+ # now we are ready to delete this redundant taxonlabel record
+ $dbh->do( "DELETE FROM taxonlabel WHERE taxonlabel_id = ? ", undef, $dup_taxlabid );
+
+ # optionally: print out a list of all deleted taxonlabel records
+ print OUTPUT "study $stid: taxonlabel $uniquetaxonlabel: $dup_taxlabid deleted in favor of $taxonlabels{$uniquetaxonlabel}[0]\n";
+ } elsif ($sub_totRec == 0) {
+ print "Whoops... sub_taxonlabel lacks a record for the surviving taxonlabel_id $taxonlabels{$uniquetaxonlabel}[0] \n";
+ } else {
+ print "Whoops... I didn't expect sub_taxonlabel to have $sub_totRec records with taxonlabel_id $taxonlabels{$uniquetaxonlabel}[0] \n";
+ print " > $sub_taxonlabel_count\n";
+ }
+
+ }
+ my $rd = $sth_getdups->finish;
+
+ # Make sure that the surviving taxonlabel has a taxonvariant_id
+ $dbh->do( "UPDATE taxonlabel SET taxonvariant_id = ? WHERE taxonlabel_id = ? ", undef, $taxonlabels{$uniquetaxonlabel}[1], $taxonlabels{$uniquetaxonlabel}[0] );
+
+ # If no errors so far, let's commit this set of changes
+ $dbh->commit();
+ };
+
+ if ($@) {
+ warn "Failed to fix $uniquetaxonlabel: $@\n";
+ $dbh->rollback();
+ }
+
+ }
+}
+
+close(OUTPUT);
+
+$totRec = $dbh->selectrow_array ($count);
+print "The database ends with $totRec redundant records.\n";
+
+
+my $rc = $dbh->disconnect;
+
+
+
+# Connect to Postgres using DBI
+#==============================================================
+sub ConnectToPg {
+
+ my ($cstr, $user, $pass) = @_;
+
+ $cstr = "DBI:Pg:dbname="."$cstr";
+ # uncomment this to run against the server at NESCent:
+ # $cstr .= ";host=treebasedb-dev.nescent.org";
+
+
+ my $dbh = DBI->connect($cstr, $user, $pass, {AutoCommit => 0, PrintError => 1, RaiseError => 1});
+ $dbh || &error("DBI connect failed : ",$dbh->errstr);
+
+ return($dbh);
+}
+
+
Property changes on: trunk/treebase-core/db/cleaning/2010-03-03_fixlabels/fixlabels_trans.pl
___________________________________________________________________
Added: svn:executable
+ *
Added: trunk/treebase-core/db/cleaning/2010-03-03_fixlabels/output.txt
===================================================================
--- trunk/treebase-core/db/cleaning/2010-03-03_fixlabels/output.txt (rev 0)
+++ trunk/treebase-core/db/cleaning/2010-03-03_fixlabels/output.txt 2010-03-09 22:27:21 UTC (rev 534)
@@ -0,0 +1,101409 @@
+study 1074: taxonlabel Gaertneriomyces semiglobiferus: 141235 deleted in favor of 18109
+study 1074: taxonlabel Spizellomyces plurigibbosus: 141242 deleted in favor of 18114
+study 1074: taxonlabel Orpinomyces sp: 141218 deleted in favor of 18091
+study 1074: taxonlabel Anaeromyces K9: 141226 deleted in favor of 18100
+study 1074: taxonlabel Powellomyces sp: 141223 deleted in favor of 18111
+study 1074: taxonlabel Anaeromyces sp AUC2: 141224 deleted in favor of 18104
+study 1074: taxonlabel Spizellomyces kniepii: 141219 deleted in favor of 18113
+study 1074: taxonlabel Rhizophlyctis rosea: 141244 deleted in favor of 13180
+study 1074: taxonlabel Anaeromyces sp: 141222 deleted in favor of 18097
+study 1074: taxonlabel Anaeromyces JF1: 141234 deleted in favor of 18093
+study 1074: taxonlabel Orpinomyces sp 1: 141241 deleted in favor of 18101
+study 1074: taxonlabel Anaeromyces K5: 141220 deleted in favor of 18107
+study 1074: taxonlabel Piromyces PrI: 141236 deleted in favor of 18103
+study 1074: taxonlabel Neocallimastix patriciarum: 141230 deleted in favor of 18099
+study 1074: taxonlabel Piromyces polycephalus: 141237 deleted in favor of 18105
+study 1074: taxonlabel Kochiomyces dichotomus: 141233 deleted in favor of 18110
+study 1074: taxonlabel Spizellomyces pseudodichotomus: 141231 deleted in favor of 18108
+study 1074: taxonlabel Neocallimastix hurleyensis: 141232 deleted in favor of 18089
+study 1074: taxonlabel Anaeromyces sp AUC1: 141229 deleted in favor of 18094
+study 1074: taxonlabel Orpinomyces sp 2: 141217 deleted in favor of 18090
+study 1074: taxonlabel Anaeromyces BF1: 141225 deleted in favor of 18096
+study 1074: taxonlabel Orpinomyces KF1: 141238 deleted in favor of 18098
+study 1074: taxonlabel Orpinomyces KF2: 141239 deleted in favor of 18095
+study 1074: taxonlabel Orpinomyces KF3: 141221 deleted in favor of 18102
+study 1074: taxonlabel Caecomyces communis: 141228 deleted in favor of 18092
+study 1074: taxonlabel Neocallimastix frontalis SR4: 141240 deleted in favor of 18106
+study 1548: taxonlabel Gnetum urens: 172470 deleted in favor of 7378
+study 1548: taxonlabel Encephalartos altensteinii: 172456 deleted in favor of 31328
+study 1548: taxonlabel Gnetum sp Takeuchi7049: 172465 deleted in favor of 31302
+study 1548: taxonlabel Gnetum africanum: 172460 deleted in favor of 7345
+study 1548: taxonlabel Gnetum paniculatum: 172453 deleted in favor of 31299
+study 1548: taxonlabel Gnetum raya: 172449 deleted in favor of 31308
+study 1548: taxonlabel Gnetum hainanense Won600: 172448 deleted in favor of 31306
+study 1548: taxonlabel Taxus baccata: 172476 deleted in favor of 7398
+study 1548: taxonlabel Cycas taitungensis: 172463 deleted in favor of 31323
+study 1548: taxonlabel Gnetum schwackeanum: 172486 deleted in favor of 31300
+study 1548: taxonlabel Gnetum parvifolium: 172457 deleted in favor of 7350
+study 1548: taxonlabel Cupressus sempervirens: 172480 deleted in favor of 29195
+study 1548: taxonlabel Gnetum tenuifolium: 172478 deleted in favor of 31316
+study 1548: taxonlabel Gnetum cuspidatum: 172477 deleted in favor of 31307
+study 1548: taxonlabel Picea rubens: 172443 deleted in favor of 31327
+study 1548: taxonlabel Gnetum nodiflorum: 172435 deleted in favor of 7356
+study 1548: taxonlabel Gnetum acutum: 172459 deleted in favor of 31304
+study 1548: taxonlabel Gnetum latifolium Won575: 172432 deleted in favor of 31312
+study 1548: taxonlabel Gnetum hainanense Won580: 172433 deleted in favor of 31313
+study 1548: taxonlabel Cunninghamia lanceolata: 172472 deleted in favor of 31330
+study 1548: taxonlabel Gnetum montanum: 172430 deleted in favor of 7414
+study 1548: taxonlabel Gnetum neglectum: 172461 deleted in favor of 31309
+study 1548: taxonlabel Gnetum diminutum: 172488 deleted in favor of 31315
+study 1548: taxonlabel Gnetum gnemon Ashby: 172479 deleted in favor of 31321
+study 1548: taxonlabel Gnetum gnemonoides: 172440 deleted in favor of 31320
+study 1548: taxonlabel Gnetum woodsonianum: 172471 deleted in favor of 31319
+study 1548: taxonlabel Magnolia denudata: 172439 deleted in favor of 31331
+study 1548: taxonlabel Nageia nagi: 172489 deleted in favor of 8057
+study 1548: taxonlabel Gnetum sp Harder5621: 172454 deleted in favor of 31301
+study 1548: taxonlabel Ephedra sinica: 172464 deleted in favor of 7353
+study 1548: taxonlabel Cephalotaxus oliveri: 172469 deleted in favor of 31334
+study 1548: taxonlabel Gnetum latifolium Won524: 172487 deleted in favor of 31322
+study 1548: taxonlabel Nuphar variegata: 172466 deleted in favor of 31325
+study 1548: taxonlabel Gnetum gnemon Won514: 172473 deleted in favor of 31305
+study 1548: taxonlabel Welwitschia AF280996: 172455 deleted in favor of 31329
+study 1548: taxonlabel Gnetum aff. latifolium Won545: 172474 deleted in favor of 31298
+study 1548: taxonlabel Ephedra trifurca: 172434 deleted in favor of 31324
+study 1548: taxonlabel Calocedrus decurrens: 172429 deleted in favor of 29189
+study 1548: taxonlabel Gnetum ula: 172484 deleted in favor of 31310
+study 1548: taxonlabel Agathis borneensis: 172483 deleted in favor of 31336
+study 1548: taxonlabel Gnetum microstachyum: 172462 deleted in favor of 31314
+study 1548: taxonlabel Phyllocladus trichomanoides: 172428 deleted in favor of 8061
+study 1548: taxonlabel Zamia floridana: 172452 deleted in favor of 18271
+study 1548: taxonlabel Gnetum hainanense RBGE: 172441 deleted in favor of 31311
+study 1548: taxonlabel Gnetum costatum: 172468 deleted in favor of 7359
+study 1548: taxonlabel Bowenia serrulata: 172431 deleted in favor of 7416
+study 1548: taxonlabel Gnetum klossii: 172447 deleted in favor of 31317
+study 1548: taxonlabel Gnetum aff. latifolium SAN151116: 172475 deleted in favor of 31297
+study 1548: taxonlabel Abies firma: 172481 deleted in favor of 31335
+study 1548: taxonlabel Gnetum macrostachyum: 172450 deleted in favor of 31318
+study 1548: taxonlabel Gnetum gnemon clone: 172437 deleted in favor of 31332
+study 1548: taxonlabel Gnetum microcarpum: 172485 deleted in favor of 31303
+study 1548: taxonlabel Araucaria hunsteinii: 172467 deleted in favor of 31337
+study 1548: taxonlabel Gnetum hainanense: 172438 deleted in favor of 31333
+study 2026: taxonlabel Albugo candida ex. Biscutella laevig: 210693 deleted in favor of 78176
+study 2026: taxonlabel Albugo koreana CA2: 210686 deleted in favor of 78197
+study 2026: taxonlabel Albugo candida ex. Arabis turrita CA: 210655 deleted in favor of 78172
+study 2026: taxonlabel Albugo sp. ex. Draba nemorosa CA220: 210683 deleted in favor of 78209
+study 2026: taxonlabel Albugo sp. ex. Draba nemorosa CA10: 210658 deleted in favor of 78211
+study 2026: taxonlabel Albugo occidentalis AY286220: 210665 deleted in favor of 78191
+study 2026: taxonlabel Albugo candida ex. Descurainia sophi: 210678 deleted in favor of 78170
+study 2026: taxonlabel Albugo candida ex. Draba verna BP74: 210682 deleted in favor of 78210
+study 2026: taxonlabel Albugo sp. ex. Draba nemorosa CA220: 210674 deleted in favor of 78173
+study 2026: taxonlabel Pustula tragopogonis AY286221: 210694 deleted in favor of 78200
+study 2026: taxonlabel Albugo candida ex. Diplotaxis erucoi: 210689 deleted in favor of 78177
+study 2026: taxonlabel Wilsoniana portulacae CA17: 210663 deleted in favor of 78179
+study 2026: taxonlabel Albugo trianthemae CA50: 210673 deleted in favor of 78160
+study 2026: taxonlabel Albugo candida ex. Brassica juncea C: 210676 deleted in favor of 78169
+study 2026: taxonlabel Albugo lepidii ex. Lepidium apetalum: 210649 deleted in favor of 78167
+study 2026: taxonlabel Albugo candida ex. Iberis amara CA1: 210644 deleted in favor of 78193
+study 2026: taxonlabel Albugo sp. ex. Draba nemorosa CA319: 210660 deleted in favor of 78185
+study 2026: taxonlabel Albugo candida ex. Cardaminopsis hal: 210653 deleted in favor of 78182
+study 2026: taxonlabel Albugo candida ex. Lunaria sp. CU19: 210672 deleted in favor of 78207
+study 2026: taxonlabel Albugo sp. ex. Draba nemorosa CA319: 210675 deleted in favor of 78204
+study 2026: taxonlabel Albugo candida ex. Erysimum cuspidat: 210687 deleted in favor of 78174
+study 2026: taxonlabel Albugo sp. ex. Draba nemorosa CA10: 210652 deleted in favor of 78188
+study 2026: taxonlabel Albugo candida ex. Thlaspi arvense: 210647 deleted in favor of 78202
+study 2026: taxonlabel Albugo candida ex. Sisymbrium leteum: 210679 deleted in favor of 78175
+study 2026: taxonlabel Albugo candida ex. Aubrietia deltoid: 210661 deleted in favor of 78181
+study 2026: taxonlabel Albugo koreana CA3: 210643 deleted in favor of 78198
+study 2026: taxonlabel Albugo koreana ex. CA2: 210692 deleted in favor of 78180
+study 2026: taxonlabel Albugo sp. ex. Draba nemorosa CA219: 210681 deleted in favor of 78208
+study 2026: taxonlabel Albugo candida ex. Arabis turrita C: 210651 deleted in favor of 78194
+study 2026: taxonlabel Albugo candida ex. Draba lasiocarpa: 210666 deleted in favor of 78201
+study 2026: taxonlabel Albugo candida ex. Iberis amara CA18: 210664 deleted in favor of 78162
+study 2026: taxonlabel Albugo capparis ex. Capparis rupestr: 210667 deleted in favor of 78195
+study 2026: taxonlabel Albugo candida ex. Diptychocarpus st: 210645 deleted in favor of 78171
+study 2026: taxonlabel Albugo chardoni ex. Draba verna CA1: 210646 deleted in favor of 78199
+study 2026: taxonlabel Albugo candida ex. Eruca sativa CA2: 210680 deleted in favor of 78203
+study 2026: taxonlabel Albugo candida ex. C bursa pastoris: 210670 deleted in favor of 78189
+study 2026: taxonlabel Albugo candida ex. Berteroa incana C: 210650 deleted in favor of 78168
+study 2026: taxonlabel Albugo sp. ex. Draba nemorosa CA161: 210657 deleted in favor of 78192
+study 2026: taxonlabel Albugo candida ex. Berteroa incana: 210691 deleted in favor of 78206
+study 2026: taxonlabel Albugo sp. ex. Draba nemorosa CA219: 210684 deleted in favor of 78166
+study 2026: taxonlabel Albugo candida ex. Eruca sativa CA25: 210654 deleted in favor of 78184
+study 2026: taxonlabel Albugo chardoni ex. Cleome anomala C: 210685 deleted in favor of 78205
+study 2026: taxonlabel Albugo candida ex. Brassica juncea: 210677 deleted in favor of 78196
+study 2026: taxonlabel Albugo koreana ex. CA3: 210668 deleted in favor of 78161
+study 2026: taxonlabel Albugo candida ex. Raphanus sativus: 210648 deleted in favor of 78190
+study 2026: taxonlabel Albugo candida ex. Heliophila meyeri: 210669 deleted in favor of 78163
+study 2026: taxonlabel Albugo lepidii ex. Lepidium virginic: 210662 deleted in favor of 78186
+study 2026: taxonlabel Albugo ipomoeae panduratae CA25: 210659 deleted in favor of 78165
+study 2026: taxonlabel Albugo candida ex. Lunaria sp. CU19: 210656 deleted in favor of 78164
+study 2026: taxonlabel Albugo candida ex. Thlaspi arvense C: 210690 deleted in favor of 78183
+study 2026: taxonlabel Wilsoniana achyranthis CA19: 210671 deleted in favor of 78178
+study 2026: taxonlabel Wilsoniana bliti CA24: 210688 deleted in favor of 78187
+study 264: taxonlabel Richea B: 109718 deleted in favor of 19006
+study 264: taxonlabel Styphelia: 109697 deleted in favor of 19025
+study 264: taxonlabel Woollsia: 109713 deleted in favor of 19008
+study 264: taxonlabel Rupicola: 109698 deleted in favor of 19026
+study 264: taxonlabel Oligarrhena: 109721 deleted in favor of 19004
+study 264: taxonlabel Lebetanthus: 109710 deleted in favor of 19023
+study 264: taxonlabel Andersonia B: 109709 deleted in favor of 19019
+study 264: taxonlabel Gaultheria: 109715 deleted in favor of 19022
+study 264: taxonlabel Astroloma: 109719 deleted in favor of 19017
+study 264: taxonlabel Dracophyllum B: 109716 deleted in favor of 19014
+study 264: taxonlabel Melichrus: 109720 deleted in favor of 19009
+study 264: taxonlabel Dracophyllum A: 109712 deleted in favor of 19013
+study 264: taxonlabel Budawangia: 109704 deleted in favor of 19003
+study 264: taxonlabel Sprengelia: 109696 deleted in favor of 19020
+study 264: taxonlabel Cosmelia: 109708 deleted in favor of 19011
+study 264: taxonlabel Erica: 109706 deleted in favor of 19002
+study 264: taxonlabel Needhamiella: 109703 deleted in favor of 19024
+study 264: taxonlabel Lysinema: 109711 deleted in favor of 19015
+study 264: taxonlabel Sphenotoma: 109714 deleted in favor of 19010
+study 264: taxonlabel Prionotes: 109702 deleted in favor of 19001
+study 264: taxonlabel Richea A: 109707 deleted in favor of 19021
+study 264: taxonlabel Andersonia A: 109699 deleted in favor of 19012
+study 264: taxonlabel Dracophyllum C: 109701 deleted in favor of 19007
+study 264: taxonlabel Epacris: 109705 deleted in favor of 19018
+study 887: taxonlabel Diphysa humilis: 132218 deleted in favor of 819
+study 887: taxonlabel Ormocarpum rectangulare: 132184 deleted in favor of 9267
+study 887: taxonlabel Ormocarpum schliebenii: 132187 deleted in favor of 778
+study 887: taxonlabel Ormocarpum somalense: 132209 deleted in favor of 9273
+study 887: taxonlabel Pictetia sulcata: 132199 deleted in favor of 828
+study 887: taxonlabel Ormocarpum orientale: 132250 deleted in favor of 816
+study 887: taxonlabel Ormocarpum trichocarpum: 132201 deleted in favor of 786
+study 887: taxonlabel Diphysa racemosa: 132228 deleted in favor of 9263
+study 887: taxonlabel Pictetia mucronata: 132163 deleted in favor of 803
+study 887: taxonlabel Ormocarpum megalophyllum: 132191 deleted in favor of 821
+study 887: taxonlabel Chapmannia reghidensis: 132180 deleted in favor of 9269
+study 887: taxonlabel Chapmannia somalensis: 132216 deleted in favor of 9280
+study 887: taxonlabel Pictetia nipensis: 132151 deleted in favor of 785
+study 887: taxonlabel Pictetia marginata1: 132152 deleted in favor of 9258
+study 887: taxonlabel Diphysa spinosa: 132224 deleted in favor of 9257
+study 887: taxonlabel Ormocarpum flavum: 132202 deleted in favor of 798
+study 887: taxonlabel Ormocarpum muricatum: 132205 deleted in favor of 9235
+study 887: taxonlabel Diphysa spinosa1: 132149 deleted in favor of 9259
+study 887: taxonlabel Arachis magna: 132229 deleted in favor of 9279
+study 887: taxonlabel Ormocarpum dhofarense: 132211 deleted in favor of 9243
+study 887: taxonlabel Arachis batizocoi: 132185 deleted in favor of 9252
+study 887: taxonlabel Diphysa suberosa1: 132231 deleted in favor of 9245
+study 887: taxonlabel Ormocarpum drakei1: 132196 deleted in favor of 9271
+study 887: taxonlabel Diphysa thurberi: 132175 deleted in favor of 788
+study 887: taxonlabel Ormocarpum kirkii1: 132245 deleted in favor of 9241
+study 887: taxonlabel Stylosanthes guianensis: 132219 deleted in favor of 9253
+study 887: taxonlabel Arachis hermannii: 132241 deleted in favor of 9250
+study 887: taxonlabel Diphysa racemosa1: 132204 deleted in favor of 9265
+study 887: taxonlabel Diphysa americana2: 132203 deleted in favor of 9260
+study 887: taxonlabel Chapmannia tinireana: 132188 deleted in favor of 9248
+study 887: taxonlabel Ormocarpopsis tulearensis: 132162 deleted in favor of 801
+study 887: taxonlabel Arachis palustris: 132249 deleted in favor of 9270
+study 887: taxonlabel Pictetia aculeata1: 132181 deleted in favor of 9249
+study 887: taxonlabel Diphysa ormocarpoides1: 132253 deleted in favor of 9277
+study 887: taxonlabel Ormocarpum sennoides: 132254 deleted in favor of 824
+study 887: taxonlabel Ormocarpum verrucosum: 132234 deleted in favor of 799
+study 887: taxonlabel Diphysa americana1: 132244 deleted in favor of 9244
+study 887: taxonlabel Ormocarpum kirkii2: 132230 deleted in favor of 9242
+study 887: taxonlabel Dalbergia sissoo1: 132176 deleted in favor of 9275
+study 887: taxonlabel Arachis correntina: 132192 deleted in favor of 9264
+study 887: taxonlabel Pictetia obcordata: 132240 deleted in favor of 784
+study 887: taxonlabel Machaerium inundatum: 132171 deleted in favor of 9246
+study 887: taxonlabel Ormocarpum pubescens: 132154 deleted in favor of 825
+study 887: taxonlabel Diphysa suberosa: 132233 deleted in favor of 9240
+study 887: taxonlabel Ormocarpum bernierianum1: 132242 deleted in favor of 9262
+study 887: taxonlabel Ormocarpopsis mandrarensis: 132168 deleted in favor of 780
+study 887: taxonlabel Pictetia mucronata1: 132164 deleted in favor of 9239
+study 887: taxonlabel Ormocarpopsis aspera: 132212 deleted in favor of 820
+study 887: taxonlabel Ormocarpum drakei: 132167 deleted in favor of 9255
+study 887: taxonlabel Adesmia volckmannii1: 132232 deleted in favor of 9247
+study 887: taxonlabel Ormocarpum gillettii: 132195 deleted in favor of 9251
+study 887: taxonlabel Ormocarpum gillettii1: 132200 deleted in favor of 9274
+study 887: taxonlabel Pictetia spinosa: 132189 deleted in favor of 797
+study 887: taxonlabel Ormocarpopsis parvifolia1: 132193 deleted in favor of 9256
+study 887: taxonlabel Diphysa sennoides: 132220 deleted in favor of 818
+study 887: taxonlabel Ormocarpopsis parvifolia: 132252 deleted in favor of 9261
+study 887: taxonlabel Arachis villosa: 132165 deleted in favor of 9276
+study 887: taxonlabel Ormocarpum trachycarpum: 132182 deleted in favor of 795
+study 887: taxonlabel Arachis triseminata: 132227 deleted in favor of 9077
+study 887: taxonlabel Diphysa macrophylla: 132225 deleted in favor of 792
+study 2024: taxonlabel Neotyphodium sp 1081Bpu: 210432 deleted in favor of 77929
+study 2024: taxonlabel Epichloe typhina 200736Lp: 210462 deleted in favor of 77932
+study 2024: taxonlabel Neotyphodium sp 4078LaA: 210427 deleted in favor of 77939
+study 2024: taxonlabel Brachyelytrum erectum: 210437 deleted in favor of 77968
+study 2024: taxonlabel Epichloe amarillans 200743So: 210454 deleted in favor of 77947
+study 2024: taxonlabel Agrostis tenuis: 210416 deleted in favor of 77966
+study 2024: taxonlabel Epichloe bromicola 201558Br: 210426 deleted in favor of 77928
+study 2024: taxonlabel Hordelymus europaeus: 210428 deleted in favor of 77958
+study 2024: taxonlabel Lolium sp P4074: 210413 deleted in favor of 77955
+study 2024: taxonlabel Lolium perenne2: 210442 deleted in favor of 77964
+study 2024: taxonlabel Neotyphodium canariense 989Lc: 210460 deleted in favor of 77948
+study 2024: taxonlabel Glyceria striata: 210443 deleted in favor of 8396
+study 2024: taxonlabel Lolium multiflorum: 210419 deleted in favor of 8410
+study 2024: taxonlabel Epichloe elymi 201551Ec: 210420 deleted in favor of 77944
+study 2024: taxonlabel Calamagrostis villosa: 210418 deleted in favor of 77961
+study 2024: taxonlabel Epichloe festucae 1157Kc: 210423 deleted in favor of 77941
+study 2024: taxonlabel Epichloe festucae 90660Frc: 210436 deleted in favor of 77931
+study 2024: taxonlabel Lolium perenne: 210446 deleted in favor of 13032
+study 2024: taxonlabel Neotyphodium lolii: 210453 deleted in favor of 38665
+study 2024: taxonlabel Bromus purgans: 210431 deleted in favor of 77953
+study 2024: taxonlabel Echinopogon ovatus: 210457 deleted in favor of 77963
+study 2024: taxonlabel Epichloe baconii 200745Cv: 210412 deleted in favor of 77934
+study 2024: taxonlabel Agrostis hiemalis: 210458 deleted in favor of 77967
+study 2024: taxonlabel Bromus erectus: 210444 deleted in favor of 5777
+study 2024: taxonlabel Epichloe bromicola 200749Be: 210461 deleted in favor of 77951
+study 2024: taxonlabel Neotyphodium sp 3635Hbr: 210459 deleted in favor of 77936
+study 2024: taxonlabel Epichloe festucae 28Fl: 210439 deleted in favor of 77949
+study 2024: taxonlabel Neotyphodium gansuense 818Ai: 210415 deleted in favor of 77935
+study 2024: taxonlabel Epichloe glyceriae 200755Gs: 210414 deleted in favor of 77945
+study 2024: taxonlabel Sphenopholis obtusata: 210441 deleted in favor of 77965
+study 2024: taxonlabel Neotyphodium aotearoae 829: 210411 deleted in favor of 77950
+study 2024: taxonlabel Achnatherum inebrians: 210421 deleted in favor of 77957
+study 2024: taxonlabel Bromus ramosus: 210455 deleted in favor of 5795
+study 2024: taxonlabel Lolium sp P4078: 210447 deleted in favor of 77969
+study 2024: taxonlabel Brachypodum sylvaticum: 210430 deleted in favor of 77956
+study 2024: taxonlabel Hordeum brevisublulatum: 210429 deleted in favor of 77970
+study 2024: taxonlabel Neotyphodium occultans 999Lm: 210452 deleted in favor of 77943
+study 2024: taxonlabel Festuca rubra: 210451 deleted in favor of 13044
+study 2024: taxonlabel Koeleria cristata: 210433 deleted in favor of 77952
+study 2024: taxonlabel Lolium arundinaceum: 210425 deleted in favor of 77962
+study 2024: taxonlabel Epichloe baconii 200746At: 210434 deleted in favor of 77946
+study 2024: taxonlabel Epichloe sylvatica 200751Bs: 210445 deleted in favor of 77930
+study 2024: taxonlabel Neotyphodium sp 4074LaT: 210440 deleted in favor of 77942
+study 2024: taxonlabel Holcus mollis: 210417 deleted in favor of 77960
+study 2024: taxonlabel Epichloe amarillans 200744Ah: 210448 deleted in favor of 77937
+study 2024: taxonlabel Epichloe brachyelytri: 210422 deleted in favor of 67495
+study 2024: taxonlabel Elymus canadensis: 210438 deleted in favor of 59186
+study 2024: taxonlabel Neotyphodium sp 362Heur: 210424 deleted in favor of 77938
+study 2024: taxonlabel Neotyphodium coenopialum 19T: 210435 deleted in favor of 77933
+study 2024: taxonlabel Epichloe sp 9924Hm: 210456 deleted in favor of 77940
+study 2024: taxonlabel Lolium edwardii: 210449 deleted in favor of 77959
+study 2024: taxonlabel Festuca longifolia: 210450 deleted in favor of 77954
+study 802: taxonlabel Pirodrilus minutus: 126519 deleted in favor of 4503
+study 802: taxonlabel Tubifex smirnowi: 126513 deleted in favor of 4481
+study 802: taxonlabel Limnodriloides barnardi: 126543 deleted in favor of 4498
+study 802: taxonlabel Bathydrilus formosus: 126526 deleted in favor of 4460
+study 802: taxonlabel Bothrioneurum vejdovskyanum: 126555 deleted in favor of 4463
+study 802: taxonlabel Limnodriloides monothecus: 126552 deleted in favor of 4478
+study 802: taxonlabel Heterodrilus queenslandicus: 126545 deleted in favor of 4455
+study 802: taxonlabel Heterodrilus paucifascis: 126537 deleted in favor of 4458
+study 802: taxonlabel Limnodriloides lateroporus: 126540 deleted in favor of 4444
+study 802: taxonlabel Heterodrilus minisetosus: 126517 deleted in favor of 4487
+study 802: taxonlabel Enchytraeus sp: 126525 deleted in favor of 4497
+study 802: taxonlabel Monopylephorus rubroniveus: 126546 deleted in favor of 4470
+study 802: taxonlabel Slavina appendiculata: 126548 deleted in favor of 4447
+study 802: taxonlabel Limnodriloides baculatus: 126549 deleted in favor of 4492
+study 802: taxonlabel Limnodrilus hoffmeisteri: 126531 deleted in favor of 4507
+study 802: taxonlabel Limnodriloides vespertinus: 126518 deleted in favor of 4453
+study 802: taxonlabel Thalassodrilides bruneti: 126556 deleted in favor of 4484
+study 802: taxonlabel Limnodriloides anxius: 126534 deleted in favor of 4445
+study 802: taxonlabel Chaetogaster diastrophus: 126520 deleted in favor of 4474
+study 802: taxonlabel Ainudrilus lutulentus: 126533 deleted in favor of 4462
+study 802: taxonlabel Tectidrilus verrucosus: 126512 deleted in favor of 4466
+study 802: taxonlabel Pristina longiseta: 126561 deleted in favor of 4469
+study 802: taxonlabel Olavius tantulus: 126544 deleted in favor of 4473
+study 802: taxonlabel Inanidrilus leukodermatus: 126532 deleted in favor of 4479
+study 802: taxonlabel Ainudrilus geminus: 126521 deleted in favor of 4499
+study 802: taxonlabel Olavius albidus: 126539 deleted in favor of 4495
+study 802: taxonlabel Olavius vacuus: 126562 deleted in favor of 4450
+study 802: taxonlabel Stylodrilus heringianus: 126502 deleted in favor of 4456
+study 802: taxonlabel Inanidrilus triangulatus: 126504 deleted in favor of 4446
+study 802: taxonlabel Limnodriloides armatus: 126554 deleted in favor of 4493
+study 802: taxonlabel Heterodrilus jamiesoni: 126505 deleted in favor of 4459
+study 802: taxonlabel Pectinodrilus molestus: 126558 deleted in favor of 4476
+study 802: taxonlabel Tubificoides parviductus: 126563 deleted in favor of 4505
+study 802: taxonlabel Buchholzia fallax: 126564 deleted in favor of 4465
+study 802: taxonlabel Tubificoides pseudogaster: 126514 deleted in favor of 4452
+study 802: taxonlabel Vejdovskyella comata: 126516 deleted in favor of 4488
+study 802: taxonlabel Tectidrilus bori: 126530 deleted in favor of 4477
+study 802: taxonlabel Olavius algarvensis: 126559 deleted in favor of 4485
+study 802: taxonlabel Heronidrilus heronae: 126501 deleted in favor of 4506
+study 802: taxonlabel Clitellio arenarius: 126541 deleted in favor of 4467
+study 802: taxonlabel Tubifex ignotus: 126507 deleted in favor of 4494
+study 802: taxonlabel Bathydrilus litoreus: 126523 deleted in favor of 4500
+study 802: taxonlabel Nais communis: 126542 deleted in favor of 4472
+study 802: taxonlabel Tubificoides benedii: 126529 deleted in favor of 4483
+study 802: taxonlabel Aktedrilus arcticus: 126547 deleted in favor of 4454
+study 802: taxonlabel Heterodrilus decipiens: 126553 deleted in favor of 4504
+study 802: taxonlabel Smithsonidrilus westoni: 126560 deleted in favor of 4468
+study 802: taxonlabel Thalassodrilides ineri: 126515 deleted in favor of 4508
+study 802: taxonlabel Bathydrilus adriaticus: 126538 deleted in favor of 4486
+study 802: taxonlabel Paranais litoralis: 126527 deleted in favor of 4496
+study 802: taxonlabel Inanidrilus aduncosetis: 126509 deleted in favor of 4490
+study 802: taxonlabel Smithsonidrilus hummelincki: 126535 deleted in favor of 4475
+study 802: taxonlabel Lumbricus castaneus: 126503 deleted in favor of 4471
+study 802: taxonlabel Insulodrilus bifidus: 126551 deleted in favor of 4482
+study 802: taxonlabel Limnodriloides rubicundus: 126550 deleted in favor of 4443
+study 802: taxonlabel Heterodrilus flexuosus: 126506 deleted in favor of 4502
+study 802: taxonlabel Thalassodrilides gurwitschi: 126528 deleted in favor of 4442
+study 802: taxonlabel Limnodrilus udekemianus: 126500 deleted in favor of 4491
+study 802: taxonlabel Limnodriloides appendiculatus: 126508 deleted in favor of 4457
+study 802: taxonlabel Bathydrilus rohdei: 126524 deleted in favor of 4501
+study 802: taxonlabel Doliodrilus tener: 126511 deleted in favor of 4449
+study 1513: taxonlabel Begonia symsanguinea: 170247 deleted in favor of 29810
+study 1513: taxonlabel Begonia versicolor: 170241 deleted in favor of 29844
+study 1513: taxonlabel Begonia variabilis: 170269 deleted in favor of 29822
+study 1513: taxonlabel Begonia leprosa: 170272 deleted in favor of 29811
+study 1513: taxonlabel Begonia hernandioides: 170276 deleted in favor of 29830
+study 1513: taxonlabel Begonia hemsleyana: 170275 deleted in favor of 29812
+study 1513: taxonlabel Begonia rajah: 170268 deleted in favor of 29849
+study 1513: taxonlabel Begonia dregei: 170271 deleted in favor of 17872
+study 1513: taxonlabel Begonia acetosella acetosella: 170258 deleted in favor of 29804
+study 1513: taxonlabel Begonia longifolia China: 170256 deleted in favor of 29801
+study 1513: taxonlabel Begonia xanthiana: 170254 deleted in favor of 29814
+study 1513: taxonlabel Begonia halconensis: 170226 deleted in favor of 29840
+study 1513: taxonlabel Begonia silletensis: 170231 deleted in favor of 29829
+study 1513: taxonlabel Begonia sp nov Sulawesi 254: 170229 deleted in favor of 29816
+study 1513: taxonlabel Begonia handelii prostrata: 170242 deleted in favor of 29836
+study 1513: taxonlabel Begonia chloroneura: 170225 deleted in favor of 29821
+study 1513: taxonlabel Begonia masoniana: 170232 deleted in favor of 17865
+study 1513: taxonlabel Begonia longifolia AY044980: 170234 deleted in favor of 29825
+study 1513: taxonlabel Begonia ceratocarpa: 170267 deleted in favor of 29827
+study 1513: taxonlabel Begonia cathayana: 170228 deleted in favor of 29826
+study 1513: taxonlabel Begonia kingiana: 170243 deleted in favor of 29831
+study 1513: taxonlabel Begonia morsei: 170263 deleted in favor of 29819
+study 1513: taxonlabel Begonia hayatae: 170236 deleted in favor of 29808
+study 1513: taxonlabel Begonia circumlobata: 170257 deleted in favor of 29817
+study 1513: taxonlabel Begonia cristata: 170259 deleted in favor of 29845
+study 1513: taxonlabel Begonia goegoensis: 170253 deleted in favor of 29828
+study 1513: taxonlabel Begonia taiwaniana: 170262 deleted in favor of 29832
+study 1513: taxonlabel Begonia longifolia Java: 170261 deleted in favor of 29843
+study 1513: taxonlabel Begonia labordei: 170230 deleted in favor of 29837
+study 1513: taxonlabel Begonia brevirimosa: 170246 deleted in favor of 29807
+study 1513: taxonlabel Begonia rubella: 170260 deleted in favor of 29824
+study 1513: taxonlabel Begonia amphioxis: 170233 deleted in favor of 29848
+study 1513: taxonlabel Begonia diadema: 170265 deleted in favor of 29809
+study 1513: taxonlabel Begonia oxysperma: 170250 deleted in favor of 29835
+study 1513: taxonlabel Begonia balansana: 170240 deleted in favor of 29805
+study 1513: taxonlabel Begonia multangula: 170252 deleted in favor of 29820
+study 1513: taxonlabel Begonia longicarpa: 170237 deleted in favor of 29846
+study 1513: taxonlabel Begonia rex: 170245 deleted in favor of 29803
+study 1513: taxonlabel Begonia pavonina: 170266 deleted in favor of 29841
+study 1513: taxonlabel Begonia roxburghii Female: 170251 deleted in favor of 29842
+study 1513: taxonlabel Begonia alveolata: 170238 deleted in favor of 29839
+study 1513: taxonlabel Begonia robusta: 170270 deleted in favor of 29838
+study 1513: taxonlabel Begonia chlorosticta: 170239 deleted in favor of 29818
+study 1513: taxonlabel Begonia roxburghii Male: 170244 deleted in favor of 29834
+study 1513: taxonlabel Begonia palmata: 170227 deleted in favor of 17870
+study 1513: taxonlabel Begonia malachosticta: 170255 deleted in favor of 29802
+study 1513: taxonlabel Begonia alpina: 170249 deleted in favor of 29823
+study 1513: taxonlabel Begonia muricata: 170235 deleted in favor of 29806
+study 1513: taxonlabel Begonia wenshanensis: 170264 deleted in favor of 29833
+study 1513: taxonlabel Begonia geranioides: 170273 deleted in favor of 29813
+study 1513: taxonlabel Begonia handelii handelii: 170274 deleted in favor of 29815
+study 1513: taxonlabel Begonia boisiana: 170248 deleted in favor of 29847
+study 1070: taxonlabel Phanacis 1: 141045 deleted in favor of 2408
+study 1070: taxonlabel Liposthenes gle: 141031 deleted in favor of 2391
+study 1070: taxonlabel Parnips: 141046 deleted in favor of 2398
+study 1070: taxonlabel Liposthenes ker: 141039 deleted in favor of 2395
+study 1070: taxonlabel Phanacis 2: 141027 deleted in favor of 2404
+study 1070: taxonlabel Aulacidea: 141024 deleted in favor of 2387
+study 1350: taxonlabel Erysiphe pulchra var. japonica: 159102 deleted in favor of 37503
+study 1350: taxonlabel Oidium hevea MUMH2418 Brazil: 159088 deleted in favor of 37462
+study 1350: taxonlabel Erysiphe sinensis: 159099 deleted in favor of 37480
+study 1350: taxonlabel Erysiphe trifolii var. trifolii: 159069 deleted in favor of 8777
+study 1350: taxonlabel Oidium hevea MUMH2602 Thailand: 159100 deleted in favor of 37452
+study 1350: taxonlabel Erysiphe sparsa: 159084 deleted in favor of 37490
+study 1350: taxonlabel Erysiphe sp. MUMH124 Japan: 159072 deleted in favor of 37457
+study 1350: taxonlabel Oidium sp. AF154328: 159086 deleted in favor of 37491
+study 1350: taxonlabel Oidium hevea MUMH2545 Malaysia: 159066 deleted in favor of 37482
+study 1350: taxonlabel Erysiphe pulchra var. japonica AB0009: 159078 deleted in favor of 37476
+study 1350: taxonlabel Oidium hevea MUMH2544 Malaysia: 159053 deleted in favor of 37495
+study 1350: taxonlabel Oidium sp. AB078800: 159075 deleted in favor of 37455
+study 1350: taxonlabel Erysiphe pulchra var. japonica AB0159: 159077 deleted in favor of 37464
+study 1350: taxonlabel Erysiphe friesii var. dahurica: 159057 deleted in favor of 37469
+study 1350: taxonlabel Oidium hevea MUMH2419 Brazil: 159091 deleted in favor of 37461
+study 1080: taxonlabel Synechococcus WH8103: 141623 deleted in favor of 18357
+study 1080: taxonlabel Synechococcus KU43Fr from Chondrilla nucula: 141620 deleted in favor of 18359
+study 1080: taxonlabel Synechococcus NACL5: 141619 deleted in favor of 18358
+study 1080: taxonlabel Prochlorococcus NATL2: 141624 deleted in favor of 18355
+study 1080: taxonlabel Synechococcus from Petrosia ficiformis: 141622 deleted in favor of 18354
+study 1080: taxonlabel Oscillatoria rosea: 141621 deleted in favor of 18356
+study 1209: taxonlabel Capnodium dermatum: 149503 deleted in favor of 3543
+study 1209: taxonlabel Chaetasbolisia falcata: 149522 deleted in favor of 3544
+study 1209: taxonlabel Dothiora rhamni alpinae: 149543 deleted in favor of 18384
+study 1209: taxonlabel Hormonema prunorum: 149534 deleted in favor of 18388
+study 1209: taxonlabel Phaeosclera dematioides: 149526 deleted in favor of 3554
+study 1209: taxonlabel Sarcinomyces crustaceus: 149508 deleted in favor of 3556
+study 1209: taxonlabel Capnobotryella renispora AY220611: 149533 deleted in favor of 3557
+study 1209: taxonlabel Endoconidioma populi UAMH 10297: 149549 deleted in favor of 61632
+study 1209: taxonlabel Kabatiella lini: 149523 deleted in favor of 18387
+study 1209: taxonlabel Aureobasidium pullulans AF013229: 149491 deleted in favor of 18390
+study 1209: taxonlabel Phaeocryptopus gaeumanii: 149550 deleted in favor of 18379
+study 1209: taxonlabel Dothiora europaea: 149504 deleted in favor of 18396
+study 1209: taxonlabel Kabatina juniperi AF182376: 149495 deleted in favor of 18385
+study 1209: taxonlabel Aureobasidium pullulans AF121287: 149551 deleted in favor of 18378
+study 1209: taxonlabel Antennariella californica: 149513 deleted in favor of 3555
+study 1209: taxonlabel Endoconidioma populi: 149515 deleted in favor of 3550
+study 1209: taxonlabel Hormonema sp ATCC74360: 149545 deleted in favor of 61633
+study 1209: taxonlabel Capnobotryella renispora AY220614: 149542 deleted in favor of 3534
+study 1209: taxonlabel Rhizosphaera pini: 149521 deleted in favor of 18394
+study 1209: taxonlabel Endoconidioma populi UAMH 10298: 149509 deleted in favor of 61631
+study 1209: taxonlabel Capnobotryella sp. nov AJ301706: 149520 deleted in favor of 3553
+study 1209: taxonlabel Hyphospora agavaciensis: 149535 deleted in favor of 3539
+study 1209: taxonlabel Aureobasidium pullulans AJ276062: 149512 deleted in favor of 18383
+study 1209: taxonlabel Rhizosphaera macrospora: 149517 deleted in favor of 18395
+study 1209: taxonlabel Rhizosphaera oudemansii: 149546 deleted in favor of 18393
+study 2125: taxonlabel Diplodia mutila: 219214 deleted in favor of 91946
+study 2125: taxonlabel Diplodia medicaginis CBS500.72: 219254 deleted in favor of 91903
+study 2125: taxonlabel Pseudofusicoccum stromaticum CBS117448: 219211 deleted in favor of 91884
+study 2125: taxonlabel Dothiorella iberica CBS115041: 219238 deleted in favor of 91889
+study 2125: taxonlabel Diplodia mutila CBS112553: 219282 deleted in favor of 46070
+study 2125: taxonlabel Diplodia corticola CBS112549: 219279 deleted in favor of 46047
+study 2125: taxonlabel Diplodia mutila CBS230.30: 219304 deleted in favor of 46079
+study 2125: taxonlabel Botryosphaeria tsugae CBS418.64: 219221 deleted in favor of 91906
+study 2125: taxonlabel Lasiodiplodia theobromae: 219236 deleted in favor of 67078
+study 2125: taxonlabel Phaeobotryosphaeria citrigena ICMP16812: 219274 deleted in favor of 91885
+study 2125: taxonlabel Dothiorella JL599: 219290 deleted in favor of 91940
+study 2125: taxonlabel Neofusicoccum ribis: 219213 deleted in favor of 23095
+study 2125: taxonlabel Lasiodiplodia venezuelensis CBS118739: 219298 deleted in favor of 91913
+study 2125: taxonlabel Diplodia cupressi CBS261.85: 219227 deleted in favor of 46068
+study 2125: taxonlabel Phaeobotryon mamane CPC12445: 219301 deleted in favor of 91934
+study 2125: taxonlabel Botryosphaeria dothidea CBS110302: 219295 deleted in favor of 28971
+study 2125: taxonlabel Neodeightonia phoenicum CBS169.34: 219228 deleted in favor of 91893
+study 2125: taxonlabel Diplodia acerina CBS910.73: 219265 deleted in favor of 91938
+study 2125: taxonlabel Neofusicoccum luteum CBS110497: 219223 deleted in favor of 91924
+study 2125: taxonlabel Spencermartinsia viticola CBS117009: 219207 deleted in favor of 91888
+study 2125: taxonlabel Dothidotthia symphoricarpi CPC12929: 219216 deleted in favor of 91945
+study 2125: taxonlabel Diplodia pinea C CBS109943: 219315 deleted in favor of 46066
+study 2125: taxonlabel Neofusicoccum parvum CBS110301: 219232 deleted in favor of 91914
+study 2125: taxonlabel Phaeobotryosphaeria citrigena ICMP16818: 219261 deleted in favor of 91939
+study 2125: taxonlabel Pseudofusicoccum stromaticum CBS117449: 219291 deleted in favor of 91905
+study 2125: taxonlabel Lasiodiplodia theobromae CBS124.13: 219273 deleted in favor of 91918
+study 2125: taxonlabel Phaeobotryon mamane CPC12443: 219250 deleted in favor of 91932
+study 2125: taxonlabel Dothiorella sarmentorum: 219235 deleted in favor of 91941
+study 2125: taxonlabel Diplodia cupressi CBS168.87: 219243 deleted in favor of 46072
+study 2125: taxonlabel Barriopsis fusca CBS174.26: 219287 deleted in favor of 91907
+study 2125: taxonlabel Spencermartinsia ICMP16827: 219313 deleted in favor of 91917
+study 2125: taxonlabel Dothidotthia CPC12933: 219225 deleted in favor of 91943
+study 2125: taxonlabel Neofusicoccum mangiferum CBS118531: 219286 deleted in favor of 91897
+study 2125: taxonlabel Botryosphaeria corticis CBS119047: 219258 deleted in favor of 28975
+study 2125: taxonlabel Piedraia hortae: 219210 deleted in favor of 49808
+study 2125: taxonlabel Lasiodiplodia theobromae CBS164.96: 219310 deleted in favor of 91920
+study 2125: taxonlabel Diplodia rosulata CBS116472: 219257 deleted in favor of 91902
+study 2125: taxonlabel Dothiorella sarmentorum CBS115038: 219240 deleted in favor of 91925
+study 2125: taxonlabel Dothiorella iberica CBS113188: 219249 deleted in favor of 91936
+study 2125: taxonlabel Spencermartinsia ICMP16819: 219283 deleted in favor of 91896
+study 2125: taxonlabel Diplodia pinea C CBS109725: 219260 deleted in favor of 46069
+study 2125: taxonlabel Lasiodiplodia theobromae CAA006: 219288 deleted in favor of 91894
+study 2125: taxonlabel Diplodia scrobiculata CBS113423: 219266 deleted in favor of 90115
+study 2125: taxonlabel Botryosphaeria dothidea CBS115476: 219233 deleted in favor of 91908
+study 2125: taxonlabel Hysteropatella clavispora: 219318 deleted in favor of 13248
+study 2125: taxonlabel Dothidotthia CPC12928: 219259 deleted in favor of 91950
+study 2125: taxonlabel Dothiorella sarmentorum IMI63581b: 219319 deleted in favor of 91923
+study 2125: taxonlabel Diplodia coryli CBS242.51: 219305 deleted in favor of 91916
+study 2125: taxonlabel Dothidotthia CPC12932: 219217 deleted in favor of 91948
+study 2125: taxonlabel Lasiodiplodia rubropurpurea CBS118740: 219263 deleted in favor of 91900
+study 2125: taxonlabel Lasiodiplodia crassispora CBS118741: 219299 deleted in favor of 91887
+study 2125: taxonlabel Diplodia scrobiculata CBS109944: 219220 deleted in favor of 46056
+study 2125: taxonlabel Didymella cucurbitacearum: 219251 deleted in favor of 49774
+study 2125: taxonlabel Phaeobotryosphaeria visci CBS186.97: 219234 deleted in favor of 91892
+study 2125: taxonlabel Neofusicoccum luteum CBS110299: 219226 deleted in favor of 91927
+study 2125: taxonlabel Diplodia juglandis CBS188.87: 219268 deleted in favor of 91895
+study 2125: taxonlabel Neodeightonia subglobosa CBS448.91: 219272 deleted in favor of 91909
+study 2125: taxonlabel Neofusicoccum mangiferum CBS118532: 219222 deleted in favor of 91904
+study 2125: taxonlabel Phaeobotryon mamane CPC12264: 219280 deleted in favor of 91915
+study 2125: taxonlabel Dothidotthia CPC12930: 219292 deleted in favor of 91947
+study 2125: taxonlabel Spencermartinsia ICMP16828: 219244 deleted in favor of 91901
+study 2125: taxonlabel Neodeightonia phoenicum CBS122528: 219311 deleted in favor of 91937
+study 2125: taxonlabel Phaeobotryosphaeria porosum CBS110496: 219229 deleted in favor of 91910
+study 2125: taxonlabel Diplodia spegazziniana CBS302.75: 219317 deleted in favor of 91921
+study 2125: taxonlabel Phaeobotryosphaeria visci CBS100163: 219293 deleted in favor of 91886
+study 2125: taxonlabel Lasiodiplodia pseudotheobromae CBS116459: 219253 deleted in favor of 91935
+study 2125: taxonlabel Lasiodiplodia gonubiensis CBS115812: 219241 deleted in favor of 91926
+study 2125: taxonlabel Diplodia pinea A CBS109727: 219276 deleted in favor of 46054
+study 2125: taxonlabel Spencermartinsia ICMP16824: 219231 deleted in favor of 91919
+study 2125: taxonlabel Diplodia seriata CBS119049: 219303 deleted in favor of 46062
+study 2125: taxonlabel Lasiodiplodia crassispora CBS110492: 219277 deleted in favor of 91933
+study 2125: taxonlabel Spencermartinsia viticola CBS117006: 219289 deleted in favor of 91929
+study 2125: taxonlabel Massarina arundinacea: 219294 deleted in favor of 91949
+study 2125: taxonlabel Diplodia corticola CBS112546: 219215 deleted in favor of 91930
+study 2125: taxonlabel Phaeobotryon mamane CPC12444: 219264 deleted in favor of 91931
+study 2125: taxonlabel Dothiorella CAP187: 219206 deleted in favor of 91890
+study 2125: taxonlabel Neodeightonia phoenicum JL515: 219297 deleted in favor of 91898
+study 2125: taxonlabel Diplodia pinea A CBS393.84: 219284 deleted in favor of 46078
+study 2125: taxonlabel Diplodia rosulata CBS116470: 219208 deleted in favor of 91891
+study 2125: taxonlabel Phaeobotryon mamane CPC12442: 219271 deleted in favor of 91922
+study 2125: taxonlabel Lewia eureka: 219246 deleted in favor of 91944
+study 2125: taxonlabel Lasiodiplodia gonubiensis CBS116355: 219302 deleted in favor of 91928
+study 2125: taxonlabel Phaeobotryon mamane CPC12440: 219270 deleted in favor of 91912
+study 2125: taxonlabel Lasiodiplodia parva CBS356.59: 219307 deleted in favor of 91899
+study 2125: taxonlabel Diplodia seriata CBS112555: 219267 deleted in favor of 46088
+study 2125: taxonlabel Dothiorella CAA005: 219306 deleted in favor of 91911
+study 1420: taxonlabel Vaccinium uliginosum F WGre V53a: 163142 deleted in favor of 40229
+study 1420: taxonlabel Vaccinium uliginosum C NNor V17a: 163148 deleted in favor of 40234
+study 1420: taxonlabel Vaccinium uliginosum L Cali V47a: 163136 deleted in favor of 40227
+study 1420: taxonlabel Vaccinium uliginosum K Icel V11a: 163143 deleted in favor of 40222
+study 1420: taxonlabel Vaccinium uliginosum P USA V78: 163135 deleted in favor of 40226
+study 1420: taxonlabel Vaccinium uliginosum N Russ V115: 163137 deleted in favor of 40232
+study 1420: taxonlabel Vaccinium uliginosum M Alas V85: 163141 deleted in favor of 40238
+study 1420: taxonlabel Vaccinium uliginosum J Chuk V43a: 163152 deleted in favor of 40220
+study 1420: taxonlabel Vaccinium vitis-idaea: 163150 deleted in favor of 40224
+study 1420: taxonlabel Vaccinium uliginosum I Cana V48a: 163138 deleted in favor of 40223
+study 1420: taxonlabel Vaccinium uliginosum G Cana V52a: 163147 deleted in favor of 40221
+study 1420: taxonlabel Vaccinium uliginosum B Pyre V67a: 163149 deleted in favor of 40231
+study 1420: taxonlabel Vaccinium uliginosum A Alps V30a: 163145 deleted in favor of 40219
+study 1420: taxonlabel Vaccinium uliginosum O Alas V150: 163154 deleted in favor of 40235
+study 1420: taxonlabel Vaccinium uliginosum D Alps V31b: 163151 deleted in favor of 40233
+study 1420: taxonlabel Vaccinium uliginosum E Cana V50a: 163140 deleted in favor of 40230
+study 1420: taxonlabel Vaccinium myrtillus: 163144 deleted in favor of 40237
+study 1420: taxonlabel Vaccinium uliginosum R Alas V84: 163153 deleted in favor of 40228
+study 1420: taxonlabel Vaccinium uliginosum H Russ V39a: 163139 deleted in favor of 40236
+study 1420: taxonlabel Vaccinium uliginosum Q Alas V152c vulc: 163146 deleted in favor of 40225
+study 630: taxonlabel Saranga: 106628 deleted in favor of 64586
+study 1087: taxonlabel Botryosphaeria proteae AF452561: 142029 deleted in favor of 18576
+study 1087: taxonlabel Botryosphaeria obtusa STE-U 5032: 141945 deleted in favor of 18607
+study 1087: taxonlabel Botryosphaeria protearum AF452538: 142025 deleted in favor of 18667
+study 1087: taxonlabel Botryosphaeria rhodina AF243401: 142021 deleted in favor of 18562
+study 1087: taxonlabel Diplodia sp. STE-U 5148: 142053 deleted in favor of 18652
+study 1087: taxonlabel Botryosphaeria australis STE-U 5054: 142000 deleted in favor of 18592
+study 1087: taxonlabel Botryosphaeria rhodina STE-U 4422: 141924 deleted in favor of 18618
+study 1087: taxonlabel Botryosphaeria australis STE-U 4430: 142031 deleted in favor of 18615
+study 1087: taxonlabel Cercospora penzigii STE-U 4001: 141974 deleted in favor of 18679
+study 1087: taxonlabel Botryosphaeria obtusa STE-U 4436: 142015 deleted in favor of 18581
+study 1087: taxonlabel Botryosphaeria australis AY339260: 142010 deleted in favor of 18646
+study 1087: taxonlabel Botryosphaeria obtusa STE-U 4542: 141981 deleted in favor of 18664
+study 1087: taxonlabel Botryosphaeria australis STE-U 4528: 141957 deleted in favor of 18551
+study 1087: taxonlabel Botryosphaeria obtusa STE-U 4588: 142059 deleted in favor of 18601
+study 1087: taxonlabel Botryosphaeria obtusa STE-U 4527: 141973 deleted in favor of 18627
+study 1087: taxonlabel Botryosphaeria protearum AF452528: 141930 deleted in favor of 18583
+study 1087: taxonlabel Botryosphaeria australis STE-U 4428: 141999 deleted in favor of 18658
+study 1087: taxonlabel Botryosphaeria obtusa STE-U 4532: 141949 deleted in favor of 18589
+study 1087: taxonlabel Botryosphaeria obtusa STE-U 4441: 141970 deleted in favor of 18653
+study 1087: taxonlabel Botryosphaeria obtusa STE-U 4443: 142033 deleted in favor of 18603
+study 1087: taxonlabel Botryosphaeria parva STE-U 4534: 141975 deleted in favor of 18549
+study 1087: taxonlabel Botryosphaeria obtusa STE-U 5129: 142055 deleted in favor of 18617
+study 1087: taxonlabel Botryosphaeria obtusa STE-U 5163: 141927 deleted in favor of 18677
+study 1087: taxonlabel Botryosphaeria obtusa STE-U 4546: 142020 deleted in favor of 18609
+study 1087: taxonlabel Botryosphaeria obtusa STE-U 5143: 141987 deleted in favor of 18621
+study 1087: taxonlabel Botryosphaeria australis STE-U 4426: 141986 deleted in favor of 18624
+study 1087: taxonlabel Botryosphaeria australis STE-U 4434: 142067 deleted in favor of 18548
+study 1087: taxonlabel Botryosphaeria australis STE-U 4432: 142004 deleted in favor of 18639
+study 1087: taxonlabel Botryosphaeria parva STE-U 4540: 141979 deleted in favor of 18610
+study 1087: taxonlabel Botryosphaeria parva STE-U 4584: 142022 deleted in favor of 18635
+study 1087: taxonlabel Otthia spiraeae IMI63581: 141936 deleted in favor of 18685
+study 1087: taxonlabel Botryosphaeria lutea AF027745: 142047 deleted in favor of 10271
+study 1087: taxonlabel Botryosphaeria parva STE-U 4438: 142060 deleted in favor of 18554
+study 1087: taxonlabel Botryosphaeria dothidea STE-U 5045: 141998 deleted in favor of 18619
+study 1087: taxonlabel Diplodia porosum STE-U 5132: 142026 deleted in favor of 18591
+study 1087: taxonlabel Diplodia porosum STE-U 5046: 141996 deleted in favor of 18550
+study 1087: taxonlabel Botryosphaeria australis STE-U 5039: 142054 deleted in favor of 18558
+study 1087: taxonlabel Botryosphaeria australis STE-U 4596: 141941 deleted in favor of 18611
+study 1087: taxonlabel Botryosphaeria obtusa STE-U 4539: 142002 deleted in favor of 18573
+study 1087: taxonlabel Botryosphaeria australis STE-U 4415: 142052 deleted in favor of 18642
+study 1087: taxonlabel Botryosphaeria rhodina STE-U 4423: 142023 deleted in favor of 18649
+study 1087: taxonlabel Botryosphaeria obtusa STE-U 5140: 142001 deleted in favor of 18565
+study 1087: taxonlabel Botryosphaeria ribis CMW7773 AY236936 and AY236877: 141932 deleted in favor of 18680
+study 1087: taxonlabel Botryosphaeria australis AY339261: 141983 deleted in favor of 18638
+study 1087: taxonlabel Botryosphaeria australis STE-U 4427: 142017 deleted in favor of 18632
+study 1087: taxonlabel Botryosphaeria dothidea STE-U 4595: 141935 deleted in favor of 18662
+study 1087: taxonlabel Botryosphaeria parva STE-U 5035: 142065 deleted in favor of 18659
+study 1087: taxonlabel Botryosphaeria rhodina STE-U 4419: 142064 deleted in favor of 18560
+study 1087: taxonlabel Botryosphaeria parva STE-U 4424: 141988 deleted in favor of 18640
+study 1087: taxonlabel Botryosphaeria stevensii AF243407: 141946 deleted in favor of 18612
+study 1087: taxonlabel Botryosphaeria obtusa STE-U 5053: 141965 deleted in favor of 18556
+study 1087: taxonlabel Botryosphaeria australis STE-U 4598: 141948 deleted in favor of 18557
+study 1087: taxonlabel Botryosphaeria lutea AF293480: 141954 deleted in favor of 10266
+study 1087: taxonlabel Botryosphaeria ribis CMW7772 AY236935: 141985 deleted in favor of 18574
+study 1087: taxonlabel Botryosphaeria obtusa STE-U 5043: 141959 deleted in favor of 18678
+study 1087: taxonlabel Diplodia sp. STE-U 5131: 142009 deleted in favor of 18650
+study 1087: taxonlabel Botryosphaeria lutea STE-U 4592: 141925 deleted in favor of 18569
+study 1087: taxonlabel Botryosphaeria parva CMW9079 AY236941 and AY236888: 141972 deleted in favor of 18681
+study 1087: taxonlabel Botryosphaeria australis STE-U 4435: 142041 deleted in favor of 18570
+study 1087: taxonlabel Botryosphaeria stevensii STE-U 5038: 141934 deleted in favor of 18645
+study 1087: taxonlabel Botryosphaeria obtusa STE-U 5161: 141961 deleted in favor of 18608
+study 1087: taxonlabel Botryosphaeria ribis CMW7773 AY236936: 142051 deleted in favor of 18668
+study 1087: taxonlabel Botryosphaeria rhodina STE-U 4583: 142003 deleted in favor of 18580
+study 1087: taxonlabel Diplodia sp. STE-U 5048: 142008 deleted in favor of 18604
+study 1087: taxonlabel Botryosphaeria lutea STE-U 4594: 141991 deleted in favor of 18598
+study 1087: taxonlabel Botryosphaeria obtusa STE-U 4541: 141933 deleted in favor of 18666
+study 1087: taxonlabel Botryosphaeria obtusa STE-U 4537: 141966 deleted in favor of 18631
+study 1087: taxonlabel Botryosphaeria parva CMW9079 AY236941: 142058 deleted in favor of 18567
+study 1087: taxonlabel Botryosphaeria obtusa STE-U 5141: 141955 deleted in favor of 18578
+study 1087: taxonlabel Botryosphaeria parva STE-U 4530: 142040 deleted in favor of 18600
+study 1087: taxonlabel Diplodia sp. STE-U 5146: 141995 deleted in favor of 18571
+study 1087: taxonlabel Botryosphaeria parva STE-U 4437: 141992 deleted in favor of 18553
+study 1087: taxonlabel Botryosphaeria parva STE-U 4431: 141962 deleted in favor of 18593
+study 1087: taxonlabel Botryosphaeria proteae AF452560: 141958 deleted in favor of 18660
+study 1087: taxonlabel Botryosphaeria australis STE-U 4535: 141968 deleted in favor of 18671
+study 1087: taxonlabel Botryosphaeria ribis AF027741: 141940 deleted in favor of 10230
+study 1087: taxonlabel Botryosphaeria rhodina STE-U 4421: 141950 deleted in favor of 18661
+study 1087: taxonlabel Botryosphaeria obtusa STE-U 5162: 141963 deleted in favor of 18655
+study 1087: taxonlabel Botryosphaeria dothidea AF241175: 142043 deleted in favor of 10223
+study 1087: taxonlabel Botryosphaeria australis STE-U 4418: 142005 deleted in favor of 18575
+study 1087: taxonlabel Botryosphaeria obtusa STE-U 4440: 141994 deleted in favor of 18673
+study 1087: taxonlabel Botryosphaeria parva STE-U 4417: 142013 deleted in favor of 18625
+st...
[truncated message content] |
|
From: <sfr...@us...> - 2011-03-21 20:07:08
|
Revision: 776
http://treebase.svn.sourceforge.net/treebase/?rev=776&view=rev
Author: sfrgpiel
Date: 2011-03-21 20:06:58 +0000 (Mon, 21 Mar 2011)
Log Message:
-----------
These are modifications to reduce the footprint of the database and increase the performance by creating new indices and removing matrixelement records for all discrete character matrices (which at this instance includes all matrices in the database). This deletion should be performed only after a new build has been applied to production, seeing as only the new build will not create new matrixelement records.
Added Paths:
-----------
trunk/treebase-core/db/cleaning/2011-03-21_truncate_matrixelement_table/
trunk/treebase-core/db/cleaning/2011-03-21_truncate_matrixelement_table/README.txt
trunk/treebase-core/db/cleaning/2011-03-21_truncate_matrixelement_table/truncate_matrixelement.sql
trunk/treebase-core/db/schema/patches/0006_create-indices.sql
Added: trunk/treebase-core/db/cleaning/2011-03-21_truncate_matrixelement_table/README.txt
===================================================================
--- trunk/treebase-core/db/cleaning/2011-03-21_truncate_matrixelement_table/README.txt (rev 0)
+++ trunk/treebase-core/db/cleaning/2011-03-21_truncate_matrixelement_table/README.txt 2011-03-21 20:06:58 UTC (rev 776)
@@ -0,0 +1,11 @@
+Serious performance and stability issues are predicted to arise because the
+matrixelement table is highly normalized and requires and excessive footprint
+when storing data matrices. The c. 6000+ matrices in TreeBASE are taking up
+nearly 200GB of space in this table alone. Empirical testing determined that
+the contents of the matrixelement table are not used when downloading discrete
+character data (whether as NEXUS or as NeXML) because the information is
+obtained from the symbolstring field in the matrixrow table. This is not the case
+for matrices of datatype continuous. Since there are no matrices of this type
+currently in TreeBASE, we propose to delete all records in the matrixelement table
+and modify the code so as not to create new ones except in cases of continuous data
+types.
\ No newline at end of file
Added: trunk/treebase-core/db/cleaning/2011-03-21_truncate_matrixelement_table/truncate_matrixelement.sql
===================================================================
--- trunk/treebase-core/db/cleaning/2011-03-21_truncate_matrixelement_table/truncate_matrixelement.sql (rev 0)
+++ trunk/treebase-core/db/cleaning/2011-03-21_truncate_matrixelement_table/truncate_matrixelement.sql 2011-03-21 20:06:58 UTC (rev 776)
@@ -0,0 +1,20 @@
+-- make a backup of the matrixelement table just in
+-- case, edit the -h and database name as needed:
+-- pg_dump -U treebase_app -h treebase.nescent.org -t matrixelement -a -O treebaseprod > matrixelement_bkup.sql
+
+-- remove foreign key constraints
+ALTER TABLE ONLY compound_element DROP CONSTRAINT compound_element_fkto_compound;
+ALTER TABLE ONLY compound_element DROP CONSTRAINT compound_element_fkto_element;
+ALTER TABLE ONLY itemvalue DROP CONSTRAINT itemvalue_fkto_element;
+ALTER TABLE ONLY statemodifier DROP CONSTRAINT statemodifier_fkto_element;
+
+-- delete all records in matrixelement. Be sure to use TRUNCATE instead
+-- of DELETE FROM because this makes it infinitely faster
+TRUNCATE matrixelement;
+
+-- reapply all foreign key constraints
+ALTER TABLE ONLY compound_element ADD CONSTRAINT compound_element_fkto_compound FOREIGN KEY (compound_id) REFERENCES matrixelement(matrixelement_id);
+ALTER TABLE ONLY compound_element ADD CONSTRAINT compound_element_fkto_element FOREIGN KEY (element_id) REFERENCES matrixelement(matrixelement_id);
+ALTER TABLE ONLY itemvalue ADD CONSTRAINT itemvalue_fkto_element FOREIGN KEY (element_id) REFERENCES matrixelement(matrixelement_id);
+ALTER TABLE ONLY statemodifier ADD CONSTRAINT statemodifier_fkto_element FOREIGN KEY (element_id) REFERENCES matrixelement(matrixelement_id);
+
Added: trunk/treebase-core/db/schema/patches/0006_create-indices.sql
===================================================================
--- trunk/treebase-core/db/schema/patches/0006_create-indices.sql (rev 0)
+++ trunk/treebase-core/db/schema/patches/0006_create-indices.sql 2011-03-21 20:06:58 UTC (rev 776)
@@ -0,0 +1,7 @@
+insert into versionhistory(patchnumber, patchlabel, patchdescription)
+ values (6, 'create-indices',
+ 'Create additional indices to improve query performance.');
+
+CREATE INDEX discretecharstate_phylochar_id_idx ON discretecharstate USING btree (phylochar_id);
+CREATE INDEX matrixcolumn_matrix_id_idx ON matrixcolumn USING btree (matrix_id);
+
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
×
Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.
Thanks for helping keep SourceForge clean.
X