Menu
▾
▴
treebase-guts
|
From: <vga...@us...> - 2010-03-12 19:34:13
|
Revision: 554
http://treebase.svn.sourceforge.net/treebase/?rev=554&view=rev
Author: vgapeyev
Date: 2010-03-12 19:34:05 +0000 (Fri, 12 Mar 2010)
Log Message:
-----------
Miscellaneous post-migration data fixes (see README)
Added Paths:
-----------
trunk/treebase-core/db/cleaning/2010-03-12_postload_fixes/
trunk/treebase-core/db/cleaning/2010-03-12_postload_fixes/README.txt
trunk/treebase-core/db/cleaning/2010-03-12_postload_fixes/all_postload_fixes.sql
trunk/treebase-core/db/cleaning/2010-03-12_postload_fixes/field_defaults.sql
trunk/treebase-core/db/cleaning/2010-03-12_postload_fixes/matrix_ntax_nchar.sql
trunk/treebase-core/db/cleaning/2010-03-12_postload_fixes/out_treebasedev.txt
trunk/treebase-core/db/cleaning/2010-03-12_postload_fixes/out_treebasestage.txt
trunk/treebase-core/db/cleaning/2010-03-12_postload_fixes/user_tb1import.sql
Added: trunk/treebase-core/db/cleaning/2010-03-12_postload_fixes/README.txt
===================================================================
--- trunk/treebase-core/db/cleaning/2010-03-12_postload_fixes/README.txt (rev 0)
+++ trunk/treebase-core/db/cleaning/2010-03-12_postload_fixes/README.txt 2010-03-12 19:34:05 UTC (rev 554)
@@ -0,0 +1,20 @@
+Miscellaneous data fixes applied after all TB1 nexus data was imported.
+To run them all, use
+ all_postload_fixes.sql
+
+ Were applied to treebasedev and treebasestage (the reference instance at the time)
+ on 2010-03-12 -- see out_*.txt files for output.
+
+
+Notes on the individual fixes:
+
+user_tb1import.sql
+ Assigns the special user 'tb1import' to all studies and submissions with null user_id.
+
+field_defaults.sql
+ Puts default values into phylotree.treekind, phylotree.treequality, and matrix.description.
+
+matrix_ntax_nchar.sql
+ Updates matrix.{ntax,nchar} to the actual counts of rows and columns.
+
+
\ No newline at end of file
Added: trunk/treebase-core/db/cleaning/2010-03-12_postload_fixes/all_postload_fixes.sql
===================================================================
--- trunk/treebase-core/db/cleaning/2010-03-12_postload_fixes/all_postload_fixes.sql (rev 0)
+++ trunk/treebase-core/db/cleaning/2010-03-12_postload_fixes/all_postload_fixes.sql 2010-03-12 19:34:05 UTC (rev 554)
@@ -0,0 +1,9 @@
+
+\echo 'Assigning the special user tb1import to all studies and submissions with null user_id...'
+\i user_tb1import.sql
+
+\echo 'Putting default values into phylotree.treekind, phylotree.treequality, and matrix.description...'
+\i field_defaults.sql
+
+\echo 'Updating matrix.{ntax,nchar} to the actual counts of rows and columns...'
+\i matrix_ntax_nchar.sql
Added: trunk/treebase-core/db/cleaning/2010-03-12_postload_fixes/field_defaults.sql
===================================================================
--- trunk/treebase-core/db/cleaning/2010-03-12_postload_fixes/field_defaults.sql (rev 0)
+++ trunk/treebase-core/db/cleaning/2010-03-12_postload_fixes/field_defaults.sql 2010-03-12 19:34:05 UTC (rev 554)
@@ -0,0 +1,16 @@
+BEGIN transaction;
+
+update phylotree
+ set treekind_id = 1
+where treekind_id is null;
+
+update phylotree
+ set treequality_id = 1
+where treequality_id is null;
+
+update matrix
+ set description = 'Legacy TreeBASE Matrix ID = ' || tb_matrixid
+where description is null
+ and tb_matrixid is not null;
+
+COMMIT;
\ No newline at end of file
Added: trunk/treebase-core/db/cleaning/2010-03-12_postload_fixes/matrix_ntax_nchar.sql
===================================================================
--- trunk/treebase-core/db/cleaning/2010-03-12_postload_fixes/matrix_ntax_nchar.sql (rev 0)
+++ trunk/treebase-core/db/cleaning/2010-03-12_postload_fixes/matrix_ntax_nchar.sql 2010-03-12 19:34:05 UTC (rev 554)
@@ -0,0 +1,17 @@
+BEGIN transaction;
+
+update matrix as m
+ set nTax = r.rows_counted,
+ nChar = c.columns_counted
+from (select matrix_id, count(*) as rows_counted
+ from matrixrow group by matrix_id) as r,
+ (select matrix_id, count(*) as columns_counted
+ from matrixcolumn group by matrix_id) as c
+where m.matrix_id = r.matrix_id and m.matrix_id = c.matrix_id
+ and ((m.ntax is null or m.ntax <> r.rows_counted) or
+ (m.nchar is null or m.nchar <> c.columns_counted))
+returning m.matrix_id, m.tb_matrixid, m.nexusfilename, m.published,
+ m.nTax as new_ntax,
+ m.nChar as new_nchar;
+
+COMMIT;
\ No newline at end of file
Added: trunk/treebase-core/db/cleaning/2010-03-12_postload_fixes/out_treebasedev.txt
===================================================================
--- trunk/treebase-core/db/cleaning/2010-03-12_postload_fixes/out_treebasedev.txt (rev 0)
+++ trunk/treebase-core/db/cleaning/2010-03-12_postload_fixes/out_treebasedev.txt 2010-03-12 19:34:05 UTC (rev 554)
@@ -0,0 +1,32 @@
+treebasedev=> \i ./all_postload_fixes.sql
+BEGIN
+UPDATE 2079
+UPDATE 2044
+COMMIT
+BEGIN
+UPDATE 7
+UPDATE 7
+UPDATE 53
+COMMIT
+BEGIN
+ matrix_id | tb_matrixid | nexusfilename | published | new_ntax | new_nchar
+-----------+----------------------+--------------------------+-----------+----------+-----------
+ 4050 | | 18S-28S | f | 72 | 1818
+ 4070 | | zztip | f | 12 | 542
+ 4090 | M1728 | M1728.nex | f | 26 | 30
+ 4091 | M1729 | M1729.nex | f | 46 | 30
+ 4092 | M2056 | M2056.nex | f | 49 | 161
+ 4093 | M2313 | M2313.nex | f | 43 | 1902
+ 4094 | M2765 | M2765.nex | f | 74 | 2266
+ 4095 | M2767 | M2767.nex | f | 66 | 806
+ 4096 | M62c1x28x96c15c56c19 | M62c1x28x96c15c56c19.nex | f | 72 | 82
+ 4097 | M644 | M644.nex | f | 28 | 86
+ 4098 | M64c1x28x96c16c00c36 | M64c1x28x96c16c00c36.nex | f | 46 | 84
+ 4099 | M76c1x29x96c15c31c23 | M76c1x29x96c15c31c23.nex | f | 15 | 31
+ 4110 | | M2496.nex | f | 34 | 16837
+ 4130 | | 12Tx432C.nex | f | 12 | 432
+ 4150 | | withstates.nex | f | 7 | 28
+(15 rows)
+
+UPDATE 15
+COMMIT
Added: trunk/treebase-core/db/cleaning/2010-03-12_postload_fixes/out_treebasestage.txt
===================================================================
--- trunk/treebase-core/db/cleaning/2010-03-12_postload_fixes/out_treebasestage.txt (rev 0)
+++ trunk/treebase-core/db/cleaning/2010-03-12_postload_fixes/out_treebasestage.txt 2010-03-12 19:34:05 UTC (rev 554)
@@ -0,0 +1,39 @@
+treebasestage=> \i ./all_postload_fixes.sql
+Assigning the special user tb1import to all studies and submissions with null user_id...
+BEGIN
+UPDATE 2451
+UPDATE 2427
+COMMIT
+Putting default values into phylotree.treekind, phylotree.treequality, and matrix.description...
+BEGIN
+UPDATE 983
+UPDATE 983
+UPDATE 834
+COMMIT
+Updating matrix.{ntax,nchar} to the actual counts of rows and columns...
+BEGIN
+
+
+ matrix_id | tb_matrixid | nexusfilename | published | new_ntax | new_nchar
+-----------+----------------------+--------------------------+-----------+----------+-----------
+ 4050 | | 18S-28S | f | 72 | 1818
+ 4070 | | zztip | f | 12 | 542
+ 4090 | M1728 | M1728.nex | f | 26 | 30
+ 4091 | M1729 | M1729.nex | f | 46 | 30
+ 4092 | M2056 | M2056.nex | f | 49 | 161
+ 4093 | M2313 | M2313.nex | f | 43 | 1902
+ 4094 | M2765 | M2765.nex | f | 74 | 2266
+ 4095 | M2767 | M2767.nex | f | 66 | 806
+ 4096 | M62c1x28x96c15c56c19 | M62c1x28x96c15c56c19.nex | f | 72 | 82
+ 4097 | M644 | M644.nex | f | 28 | 86
+ 4098 | M64c1x28x96c16c00c36 | M64c1x28x96c16c00c36.nex | f | 46 | 84
+ 4099 | M76c1x29x96c15c31c23 | M76c1x29x96c15c31c23.nex | f | 15 | 31
+ 4110 | | M2496.nex | f | 34 | 16837
+ 4130 | | 12Tx432C.nex | f | 12 | 432
+ 4150 | | withstates.nex | f | 7 | 28
+ 4374 | M4524 | M4524.nex | f | 205 | 11335
+ 4622 | M4460 | M4460.nex | f | 54 | 9173
+(17 rows)
+
+UPDATE 17
+COMMIT
Added: trunk/treebase-core/db/cleaning/2010-03-12_postload_fixes/user_tb1import.sql
===================================================================
--- trunk/treebase-core/db/cleaning/2010-03-12_postload_fixes/user_tb1import.sql (rev 0)
+++ trunk/treebase-core/db/cleaning/2010-03-12_postload_fixes/user_tb1import.sql 2010-03-12 19:34:05 UTC (rev 554)
@@ -0,0 +1,15 @@
+BEGIN transaction;
+
+update study as s
+ set user_id = u.user_id
+from "user" as u
+where s.user_id is null
+ and u.username = 'tb1import';
+
+update submission as s
+ set user_id = u.user_id
+from "user" as u
+where s.user_id is null
+ and u.username = 'tb1import';
+
+COMMIT;
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
|
From: <sfr...@us...> - 2010-03-15 16:23:39
|
Revision: 595
http://treebase.svn.sourceforge.net/treebase/?rev=595&view=rev
Author: sfrgpiel
Date: 2010-03-15 16:23:32 +0000 (Mon, 15 Mar 2010)
Log Message:
-----------
Added some UPDATE SQL statements that fix problems with taxonlabelsets not being properly mapped to their respective matrices and tree blocks.
Added Paths:
-----------
trunk/treebase-core/db/cleaning/2010-03-14_fix_taxonlabelset/
trunk/treebase-core/db/cleaning/2010-03-14_fix_taxonlabelset/README.txt
trunk/treebase-core/db/cleaning/2010-03-14_fix_taxonlabelset/map_treeblock_to_taxonlabelset.sql
trunk/treebase-core/db/cleaning/2010-03-14_fix_taxonlabelset/update_taxonlabelset.sql
Added: trunk/treebase-core/db/cleaning/2010-03-14_fix_taxonlabelset/README.txt
===================================================================
--- trunk/treebase-core/db/cleaning/2010-03-14_fix_taxonlabelset/README.txt (rev 0)
+++ trunk/treebase-core/db/cleaning/2010-03-14_fix_taxonlabelset/README.txt 2010-03-15 16:23:32 UTC (rev 595)
@@ -0,0 +1,5 @@
+
+In the March migration, newly imported records were parked under a study_id with study_id 10215. The taxonlabelset table failed to have its study_id remapped to the newly created studies, so instead it retained the original temporary study_id. The update_taxonlabelset.sql query updates the study_id field in the taxonlabelset
+
+In the March migration, newly created treeblock records received NULL values for taxonlabelset_id, causing the download of trees to fail and causing a selection of trees under the Trees tab to fail to show the related selection of taxa after clicking on the Taxa tab. The map_treeblock_to_taxonlabelset.sql query addresses the problem by updating the treeblock's taxonlabelset_id using a value from one of the matrices related by way of an analysisstep.
+
Added: trunk/treebase-core/db/cleaning/2010-03-14_fix_taxonlabelset/map_treeblock_to_taxonlabelset.sql
===================================================================
--- trunk/treebase-core/db/cleaning/2010-03-14_fix_taxonlabelset/map_treeblock_to_taxonlabelset.sql (rev 0)
+++ trunk/treebase-core/db/cleaning/2010-03-14_fix_taxonlabelset/map_treeblock_to_taxonlabelset.sql 2010-03-15 16:23:32 UTC (rev 595)
@@ -0,0 +1,21 @@
+-- In the March migration, newly created treeblock records received NULL values
+-- for taxonlabelset_id, causing the download of trees to fail and causing
+-- a selection of trees under the Trees tab to fail to show the related selection
+-- of taxa after clicking on the Taxa tab. This query addresses the problem
+-- by updating the treeblock's taxonlabelset_id using a value from one of the matrices
+-- related by way of an analysisstep
+
+
+UPDATE treeblock SET taxonlabelset_id = newtlsid FROM
+(SELECT DISTINCT ON (tb.treeblock_id) tb.treeblock_id AS tblid, mx.taxonlabelset_id AS newtlsid
+FROM matrix mx JOIN analyzeddata am ON (mx.matrix_id = am.matrix_id)
+JOIN analyzeddata at ON (am.analysisstep_id = at.analysisstep_id)
+JOIN phylotree pt ON (at.phylotree_id = pt.phylotree_id)
+JOIN treeblock tb ON (pt.treeblock_id = tb.treeblock_id)
+WHERE tb.taxonlabelset_id IS NULL) AS nm
+WHERE treeblock.treeblock_id = tblid
+
+
+-- note Hilar's comment: "note that UPDATE ... FROM is non-standard SQL; normally
+-- one would alias the table being updated and have the update value be a subquery"
+
Added: trunk/treebase-core/db/cleaning/2010-03-14_fix_taxonlabelset/update_taxonlabelset.sql
===================================================================
--- trunk/treebase-core/db/cleaning/2010-03-14_fix_taxonlabelset/update_taxonlabelset.sql (rev 0)
+++ trunk/treebase-core/db/cleaning/2010-03-14_fix_taxonlabelset/update_taxonlabelset.sql 2010-03-15 16:23:32 UTC (rev 595)
@@ -0,0 +1,13 @@
+-- In the March migration, newly imported records were parked under a study_id
+-- with study_id 10215. The taxonlabelset table failed to have its study_id
+-- remapped to the newly created studies, so instead it retained the original
+-- temporary study_id. This query updates the study_id field in the taxonlabelset
+
+
+UPDATE taxonlabelset SET study_id = mx.study_id
+FROM matrix mx JOIN taxonlabelset tls USING (taxonlabelset_id)
+WHERE tls.study_id = 10215
+AND mx.study_id <> 10215
+AND taxonlabelset.taxonlabelset_id = tls.taxonlabelset_id
+
+
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
|
From: <vga...@us...> - 2010-03-16 20:03:37
|
Revision: 612
http://treebase.svn.sourceforge.net/treebase/?rev=612&view=rev
Author: vgapeyev
Date: 2010-03-16 20:03:30 +0000 (Tue, 16 Mar 2010)
Log Message:
-----------
Correct privileges on a sequence.
Added Paths:
-----------
trunk/treebase-core/db/cleaning/2010-03-12_stepmatrixelement_seq_priveleges/
trunk/treebase-core/db/cleaning/2010-03-12_stepmatrixelement_seq_priveleges/README
trunk/treebase-core/db/cleaning/2010-03-12_stepmatrixelement_seq_priveleges/stepmatrixelement_seq_priveleges.sql
Added: trunk/treebase-core/db/cleaning/2010-03-12_stepmatrixelement_seq_priveleges/README
===================================================================
--- trunk/treebase-core/db/cleaning/2010-03-12_stepmatrixelement_seq_priveleges/README (rev 0)
+++ trunk/treebase-core/db/cleaning/2010-03-12_stepmatrixelement_seq_priveleges/README 2010-03-16 20:03:30 UTC (rev 612)
@@ -0,0 +1 @@
+Was applied to treebasedev and treebasestage by postgres (Jon) on 2010-03-12.
Added: trunk/treebase-core/db/cleaning/2010-03-12_stepmatrixelement_seq_priveleges/stepmatrixelement_seq_priveleges.sql
===================================================================
--- trunk/treebase-core/db/cleaning/2010-03-12_stepmatrixelement_seq_priveleges/stepmatrixelement_seq_priveleges.sql (rev 0)
+++ trunk/treebase-core/db/cleaning/2010-03-12_stepmatrixelement_seq_priveleges/stepmatrixelement_seq_priveleges.sql 2010-03-16 20:03:30 UTC (rev 612)
@@ -0,0 +1,5 @@
+BEGIN transaction;
+select pg_owner('postgres', 'treebase_owner', 'stepmatrixelement_id_sequence', 'public');
+select pg_grant('treebase_owner', 'ALL', 'stepmatrixelement_id_sequence', 'public');
+select pg_grant('treebase_app', 'ALL', 'stepmatrixelement_id_sequence', 'public');
+END transaction;
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
|
From: <vga...@us...> - 2010-03-19 21:44:20
|
Revision: 630
http://treebase.svn.sourceforge.net/treebase/?rev=630&view=rev
Author: vgapeyev
Date: 2010-03-19 21:44:13 +0000 (Fri, 19 Mar 2010)
Log Message:
-----------
Fixes to run on Production before the release
Added Paths:
-----------
trunk/treebase-core/db/cleaning/pre-release_hotfixes/
trunk/treebase-core/db/cleaning/pre-release_hotfixes/README.txt
trunk/treebase-core/db/cleaning/pre-release_hotfixes/fix_taxonlabelset.sql
Added: trunk/treebase-core/db/cleaning/pre-release_hotfixes/README.txt
===================================================================
--- trunk/treebase-core/db/cleaning/pre-release_hotfixes/README.txt (rev 0)
+++ trunk/treebase-core/db/cleaning/pre-release_hotfixes/README.txt 2010-03-19 21:44:13 UTC (rev 630)
@@ -0,0 +1,65 @@
+fix_taxonlabels.sql
+ detects TaxonLabelSets whose study_id still refers to a dummy study used for loading from TB1 dumps
+ and updates it to the proper real study.
+
+
+
+=============================================================
+Some relevant correspondence:
+
+From: vla...@du...
+ Subject: Re: [Treebase-devel] Some results of testing with a user
+ Date: March 19, 2010 4:00:22 PM EDT
+ To: wil...@ya...
+ Cc: Tre...@li...
+
+On Mar 19, 2010, at 3:25 PM, William Piel wrote:
+
+
+On Mar 19, 2010, at 12:07 PM, William Piel wrote:
+
+
+On Mar 19, 2010, at 11:44 AM, Hilmar Lapp wrote:
+
+On Mar 18, 2010, at 8:06 PM, Vladimir Gapeyev wrote:
+
+(1) Unexpected different results on the Taxa tab -- a feature or a bug? E.g., find a single study, e.g. 10051.
+ -- Click the study (which goes to Citation tab), then to Taxa tab ==> "Nothing to display"
+ -- Go to Matrices tab; click on "View Taxa" in the table ==> it goes back to the Taxa tab, showing lots of stuff
+ -- Go back to Citation tab; then Taxa tab ==> "Nothing found to display"
+ -- Go to Trees tab; click on "View Taxa" in the table ==> it goes back to the Taxa tab, showing lots of stuff.
+
+This is for Bill to judge in terms of what kind of problem it may signal, and hence how severe it is.
+
+This may be severe -- let's at least understand why it is happening. It is affecting a lot of studies.
+
+I think I've figured out what's going on here. I took one taxon label that maps nicely and compared it with another taxon label that does not map nicely, and then I ran queries on tables related to these taxon labels. The main difference is that in the good case the taxonlabelset table has the correct study_id, while in the bad case the taxonlabelset has "2264" as study_id instead of "10053", which is what it should be.
+
+Last weekend I had solved this problem using an update query that assumed that study_id 10215 was the only "placeholder" for migrated records. Turns out that there seems to be another one: 2264. Vladimir, can you confirm that study_id 2264 is another placeholder? And if so, can you let me know if there are any other placeholder study_ids that I'm not aware of.
+
+If this is the problem as I think it is, the solution is the following update:
+
+UPDATE taxonlabelset SET study_id = mx.study_id
+FROM matrix mx JOIN taxonlabelset tls USING (taxonlabelset_id)
+WHERE tls.study_id = 2264
+AND mx.study_id <> 2264
+AND taxonlabelset.taxonlabelset_id = tls.taxonlabelset_id
+
+I followed a similar path and was reaching a similar conclusion. Here is a query that shows all TaxonLabelSets whose tls.study_id is not the same as the study reachable from the TLS via a matrix:
+
+select s.study_id, m.matrix_id, tls.*
+from study s, taxonlabelset tls, matrix m
+where s.study_id = m.study_id and tls.taxonlabelset_id = m.taxonlabelset_id
+ and s.study_id <> tls.study_id
+order by s.study_id
+
+The only Study IDs showing up as tls.study_id in the result are 22 and 2264.
+
+#2264 was picked up by the migration scripts during my 1st migration -- not something I could have known ahead of time, since the scripts were creating a fresh dummy study in my testing daabase! By the time of the 2nd migration batch I realized it was going on and created the 10215 study.
+
+I see Bill has already run a fix on the 2264 TLSs. Should we do it for 22-TLSs as well?
+
+
+
+
+
\ No newline at end of file
Added: trunk/treebase-core/db/cleaning/pre-release_hotfixes/fix_taxonlabelset.sql
===================================================================
--- trunk/treebase-core/db/cleaning/pre-release_hotfixes/fix_taxonlabelset.sql (rev 0)
+++ trunk/treebase-core/db/cleaning/pre-release_hotfixes/fix_taxonlabelset.sql 2010-03-19 21:44:13 UTC (rev 630)
@@ -0,0 +1,22 @@
+BEGIN TRANSACTION;
+
+\echo These TaxonLabelSet.study_ids will be updated:
+
+select s.study_id, s.tb_studyid, m.matrix_id, tls.*
+from study s, taxonlabelset tls, matrix m
+where s.study_id = m.study_id and tls.taxonlabelset_id = m.taxonlabelset_id
+ and s.study_id <> tls.study_id
+order by s.study_id;
+
+
+\echo =================== Updating ===================
+
+update taxonlabelset tls
+ set study_id = s.study_id
+from study s, matrix m
+where tls.taxonlabelset_id = m.taxonlabelset_id and m.study_id = s.study_id
+ and s.study_id <> tls.study_id
+returning s.study_id, s.tb_studyid, m.matrix_id, tls.*;
+
+
+COMMIT;
\ 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-31 13:54:57
|
Revision: 661
http://treebase.svn.sourceforge.net/treebase/?rev=661&view=rev
Author: vgapeyev
Date: 2010-03-31 13:54:49 +0000 (Wed, 31 Mar 2010)
Log Message:
-----------
Putting the date on the last pre-release data cleaning patch.
Modified Paths:
--------------
trunk/treebase-core/db/cleaning/2010-03-20_pre-release_hotfixes/README.txt
Added Paths:
-----------
trunk/treebase-core/db/cleaning/2010-03-20_pre-release_hotfixes/
Removed Paths:
-------------
trunk/treebase-core/db/cleaning/pre-release_hotfixes/
Modified: trunk/treebase-core/db/cleaning/2010-03-20_pre-release_hotfixes/README.txt
===================================================================
--- trunk/treebase-core/db/cleaning/pre-release_hotfixes/README.txt 2010-03-19 21:44:13 UTC (rev 630)
+++ trunk/treebase-core/db/cleaning/2010-03-20_pre-release_hotfixes/README.txt 2010-03-31 13:54:49 UTC (rev 661)
@@ -2,6 +2,7 @@
detects TaxonLabelSets whose study_id still refers to a dummy study used for loading from TB1 dumps
and updates it to the proper real study.
+This was applied to the production DB at treebase.nescent.org by Jon on 2010-03-20.
=============================================================
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
|
From: <vga...@us...> - 2010-04-13 14:39:52
|
Revision: 684
http://treebase.svn.sourceforge.net/treebase/?rev=684&view=rev
Author: vgapeyev
Date: 2010-04-13 14:39:33 +0000 (Tue, 13 Apr 2010)
Log Message:
-----------
A data patch from Bill
Added Paths:
-----------
trunk/treebase-core/db/cleaning/2010-04-13_set_study_in_phylotrees/
trunk/treebase-core/db/cleaning/2010-04-13_set_study_in_phylotrees/README.txt
trunk/treebase-core/db/cleaning/2010-04-13_set_study_in_phylotrees/set_study_in_phylotrees.sql
Added: trunk/treebase-core/db/cleaning/2010-04-13_set_study_in_phylotrees/README.txt
===================================================================
--- trunk/treebase-core/db/cleaning/2010-04-13_set_study_in_phylotrees/README.txt (rev 0)
+++ trunk/treebase-core/db/cleaning/2010-04-13_set_study_in_phylotrees/README.txt 2010-04-13 14:39:33 UTC (rev 684)
@@ -0,0 +1,22 @@
+To apply, run set_study_in_phylotrees.sql
+
+--------
+
+From: wil...@ya...
+ Subject: Re: [help.nescent.org #7817] Access to TreeBASE data
+ Date: April 13, 2010 1:03:36 AM EDT
+ To: he...@ne...
+ Cc: vga...@ne..., hl...@ne...
+
+
+So indeed, the study_id value is missing from the phylotree table. This is quite weird because it's very sporadic -- only 10 trees
+(among 433 trees that are new to TreeBASE since we went live) have this problem. It will be hard to replicate the bug, wherever it is.
+
+Bill ran this on treebase-stage 2010-04-13, and after that he was able to go here:
+http://treebase-stage.nescent.org/treebase-web/phylows/study/TB2:S10349?x-access-code=d5039fb25843a2c8a19d7ec93cbe1541&format=html
+... and view and download tree Tr7238 without a problem.
+
+
+---------
+
+Jon will run this on production 2010-04-13
\ No newline at end of file
Added: trunk/treebase-core/db/cleaning/2010-04-13_set_study_in_phylotrees/set_study_in_phylotrees.sql
===================================================================
--- trunk/treebase-core/db/cleaning/2010-04-13_set_study_in_phylotrees/set_study_in_phylotrees.sql (rev 0)
+++ trunk/treebase-core/db/cleaning/2010-04-13_set_study_in_phylotrees/set_study_in_phylotrees.sql 2010-04-13 14:39:33 UTC (rev 684)
@@ -0,0 +1,21 @@
+begin work;
+
+SELECT phylotree_id, study_id from phylotree where phylotree_id = 7238;
+
+-- should result in a NULL value for study_id
+
+UPDATE phylotree pt
+ SET study_id = sub.study_id
+FROM treeblock tb JOIN sub_treeblock stb USING (treeblock_id)
+JOIN submission sub USING (submission_id)
+WHERE pt.treeblock_id = tb.treeblock_id
+AND pt.study_id IS NULL
+AND sub.study_id > 10000;
+
+-- should result in about 10 records updated
+
+SELECT phylotree_id, study_id from phylotree where phylotree_id = 7238;
+
+-- should result in "10349" for study_id
+
+commit;
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
|
From: <sfr...@us...> - 2010-04-13 15:43:46
|
Revision: 685
http://treebase.svn.sourceforge.net/treebase/?rev=685&view=rev
Author: sfrgpiel
Date: 2010-04-13 15:43:40 +0000 (Tue, 13 Apr 2010)
Log Message:
-----------
New patch to standardize the journal name "Organisms Diversity & Evolution"
Added Paths:
-----------
trunk/treebase-core/db/cleaning/2010-04-13_standardize_journal_names/
trunk/treebase-core/db/cleaning/2010-04-13_standardize_journal_names/README.txt
trunk/treebase-core/db/cleaning/2010-04-13_standardize_journal_names/standardize_journal_names.sql
Added: trunk/treebase-core/db/cleaning/2010-04-13_standardize_journal_names/README.txt
===================================================================
--- trunk/treebase-core/db/cleaning/2010-04-13_standardize_journal_names/README.txt (rev 0)
+++ trunk/treebase-core/db/cleaning/2010-04-13_standardize_journal_names/README.txt 2010-04-13 15:43:40 UTC (rev 685)
@@ -0,0 +1,9 @@
+To apply, run standardize_journal_names.sql
+
+--------
+
+This statement will standardize how we name the journal "Organisms Diversity & Evolution"
+
+
+---------
+
Added: trunk/treebase-core/db/cleaning/2010-04-13_standardize_journal_names/standardize_journal_names.sql
===================================================================
--- trunk/treebase-core/db/cleaning/2010-04-13_standardize_journal_names/standardize_journal_names.sql (rev 0)
+++ trunk/treebase-core/db/cleaning/2010-04-13_standardize_journal_names/standardize_journal_names.sql 2010-04-13 15:43:40 UTC (rev 685)
@@ -0,0 +1,10 @@
+
+begin work;
+
+UPDATE citation SET journal = 'Organisms Diversity & Evolution'
+WHERE journal = 'Organisms, Diversity and Evolution'
+OR journal = 'Organisms Diversity and Evolution';
+
+-- should result in about 11 changes
+
+commit;
\ 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: <sfr...@us...> - 2010-04-13 17:23:09
|
Revision: 686
http://treebase.svn.sourceforge.net/treebase/?rev=686&view=rev
Author: sfrgpiel
Date: 2010-04-13 17:23:02 +0000 (Tue, 13 Apr 2010)
Log Message:
-----------
Fixes a missing-semicolon problem with some newickstrings that prevent the tree from being viewed in PhyloWidget
Added Paths:
-----------
trunk/treebase-core/db/cleaning/2010-04-13_missing_semi_in_newick/
trunk/treebase-core/db/cleaning/2010-04-13_missing_semi_in_newick/README.txt
trunk/treebase-core/db/cleaning/2010-04-13_missing_semi_in_newick/newickstring_semi.sql
Added: trunk/treebase-core/db/cleaning/2010-04-13_missing_semi_in_newick/README.txt
===================================================================
--- trunk/treebase-core/db/cleaning/2010-04-13_missing_semi_in_newick/README.txt (rev 0)
+++ trunk/treebase-core/db/cleaning/2010-04-13_missing_semi_in_newick/README.txt 2010-04-13 17:23:02 UTC (rev 686)
@@ -0,0 +1,7 @@
+To apply, run newickstring_semi.sql
+
+--------
+
+This statement will add semicolons to the ends of newickstrings that, for some reason, lack them
+
+---------
Added: trunk/treebase-core/db/cleaning/2010-04-13_missing_semi_in_newick/newickstring_semi.sql
===================================================================
--- trunk/treebase-core/db/cleaning/2010-04-13_missing_semi_in_newick/newickstring_semi.sql (rev 0)
+++ trunk/treebase-core/db/cleaning/2010-04-13_missing_semi_in_newick/newickstring_semi.sql 2010-04-13 17:23:02 UTC (rev 686)
@@ -0,0 +1,14 @@
+
+
+begin work;
+
+UPDATE phylotree
+SET newickstring = newickstring || ';'
+WHERE newickstring NOT LIKE '%;'
+AND newickstring NOT LIKE '% '
+AND newickstring IS NOT NULL;
+
+-- results in 3 changes
+
+commit;
+
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
|
From: <sfr...@us...> - 2010-04-15 06:22:34
|
Revision: 687
http://treebase.svn.sourceforge.net/treebase/?rev=687&view=rev
Author: sfrgpiel
Date: 2010-04-15 06:22:27 +0000 (Thu, 15 Apr 2010)
Log Message:
-----------
Set of edits to the taxonlabel-taxonvariant-taxon tables.
Added Paths:
-----------
trunk/treebase-core/db/cleaning/2010-04-15_taxon_fixes/
trunk/treebase-core/db/cleaning/2010-04-15_taxon_fixes/README.txt
trunk/treebase-core/db/cleaning/2010-04-15_taxon_fixes/taxon_fixes_4-15-10.sql
Added: trunk/treebase-core/db/cleaning/2010-04-15_taxon_fixes/README.txt
===================================================================
--- trunk/treebase-core/db/cleaning/2010-04-15_taxon_fixes/README.txt (rev 0)
+++ trunk/treebase-core/db/cleaning/2010-04-15_taxon_fixes/README.txt 2010-04-15 06:22:27 UTC (rev 687)
@@ -0,0 +1,17 @@
+To apply, run taxon_fixes_4-15-10.sql
+
+--------
+
+These statements help clean up the mapping between taxonlabel, taxonvariant, and
+taxon records. They include INSERTS in which the primary key is being supplied:
+please verify that this is okay, or will it confuse Hibernate's autoincrement?
+
+New taxon_id starts at 600001
+New taxonvariant_id starts at 800000
+
+Please verify that these numbers are beyond the current highest numbers.
+
+---------
+
+Apply changes as soon as convenient, because it is not impossible that new submissions
+in the interim will map to taxonvariants that this script wants to delete.
\ No newline at end of file
Added: trunk/treebase-core/db/cleaning/2010-04-15_taxon_fixes/taxon_fixes_4-15-10.sql
===================================================================
--- trunk/treebase-core/db/cleaning/2010-04-15_taxon_fixes/taxon_fixes_4-15-10.sql (rev 0)
+++ trunk/treebase-core/db/cleaning/2010-04-15_taxon_fixes/taxon_fixes_4-15-10.sql 2010-04-15 06:22:27 UTC (rev 687)
@@ -0,0 +1,160 @@
+
+being work;
+
+-- start by creating new records in taxon
+-- I'm assuming that it is safe to start taxon_id at 600001
+
+INSERT INTO taxon (taxon_id, ubionamebankid, ncbitaxid, name) VALUES (600001, 3877253, NULL, 'Medicago sativa sativa');
+
+-- next create new records in taxonvariant.
+-- I'm assuming that it is safe to start taxonvariant_ids at 800000
+
+INSERT INTO taxonvariant (taxonvariant_id, taxon_id, lexicalqualifier, namebankid, fullname, name) VALUES (800000, 487876, NULL, 3530048, 'Golenkinia parvula', 'Golenkinia parvula');
+INSERT INTO taxonvariant (taxonvariant_id, taxon_id, lexicalqualifier, namebankid, fullname, name) VALUES (800001, 487876, NULL, 1982801, 'Golenkinia parvula Woronchin', 'Golenkinia parvula');
+INSERT INTO taxonvariant (taxonvariant_id, taxon_id, lexicalqualifier, namebankid, fullname, name) VALUES (800002, 570355, NULL, 3307644, 'Dictyosphaerium tetrachotomum', 'Dictyosphaerium tetrachotomum');
+INSERT INTO taxonvariant (taxonvariant_id, taxon_id, lexicalqualifier, namebankid, fullname, name) VALUES (800003, 570355, NULL, 1471279, 'Dictyosphaerium tetrachotomum Printz', 'Dictyosphaerium tetrachotomum');
+INSERT INTO taxonvariant (taxonvariant_id, taxon_id, lexicalqualifier, namebankid, fullname, name) VALUES (800004, 5878, NULL, 10129595, 'Parachlorella kessleri (Fott et Novakova) Krienitz et al.', 'Parachlorella kessleri');
+INSERT INTO taxonvariant (taxonvariant_id, taxon_id, lexicalqualifier, namebankid, fullname, name) VALUES (800006, 570269, NULL, 5394354, 'Danthonia chilensis var. chilensis', 'Danthonia chilensis chilensis');
+INSERT INTO taxonvariant (taxonvariant_id, taxon_id, lexicalqualifier, namebankid, fullname, name) VALUES (800007, 570269, NULL, NULL, 'Danthonia chilensis chilensis', 'Danthonia chilensis chilensis');
+INSERT INTO taxonvariant (taxonvariant_id, taxon_id, lexicalqualifier, namebankid, fullname, name) VALUES (800008, 570268, NULL, 5394356, 'Danthonia chilensis var. aureofulva (E. Desv.) C. Baeza, 1996', 'Danthonia chilensis aureofulva');
+INSERT INTO taxonvariant (taxonvariant_id, taxon_id, lexicalqualifier, namebankid, fullname, name) VALUES (800009, 570268, NULL, 8585092, 'Danthonia chilensis E. Desv. var. aureofulva (E. Desv.) C. Baeza', 'Danthonia chilensis aureofulva');
+INSERT INTO taxonvariant (taxonvariant_id, taxon_id, lexicalqualifier, namebankid, fullname, name) VALUES (800010, 570268, NULL, 10905520, 'Danthonia chilensis var. aureofulva', 'Danthonia chilensis aureofulva');
+INSERT INTO taxonvariant (taxonvariant_id, taxon_id, lexicalqualifier, namebankid, fullname, name) VALUES (800011, 570268, NULL, NULL, 'Danthonia chilensis aureofulva', 'Danthonia chilensis aureofulva');
+INSERT INTO taxonvariant (taxonvariant_id, taxon_id, lexicalqualifier, namebankid, fullname, name) VALUES (800012, 428221, NULL, 8583992, 'Cortaderia boliviensis M. Lyle', 'Cortaderia boliviensis');
+INSERT INTO taxonvariant (taxonvariant_id, taxon_id, lexicalqualifier, namebankid, fullname, name) VALUES (800013, 428221, NULL, 5393919, 'Cortaderia boliviensis M. Lyle, 1996', 'Cortaderia boliviensis');
+INSERT INTO taxonvariant (taxonvariant_id, taxon_id, lexicalqualifier, namebankid, fullname, name) VALUES (800014, 452694, NULL, 10751874, 'Danthonia paschalis', 'Danthonia paschalis');
+INSERT INTO taxonvariant (taxonvariant_id, taxon_id, lexicalqualifier, namebankid, fullname, name) VALUES (800016, 452694, NULL, 5409860, 'Rytidosperma paschale (Pilg.) C. Baeza, 1990 [1991]', 'Rytidosperma paschale');
+INSERT INTO taxonvariant (taxonvariant_id, taxon_id, lexicalqualifier, namebankid, fullname, name) VALUES (800018, 570088, NULL, 1142512, 'Hebeloma victoriense A. A. Holland & Pegler 1983', 'Hebeloma victoriense');
+INSERT INTO taxonvariant (taxonvariant_id, taxon_id, lexicalqualifier, namebankid, fullname, name) VALUES (800019, 570015, NULL, NULL, 'Medicago sativa subsp. x hemicycla', 'Medicago sativa hemicycla');
+INSERT INTO taxonvariant (taxonvariant_id, taxon_id, lexicalqualifier, namebankid, fullname, name) VALUES (800020, 62238, NULL, 10139860, 'Medicago sativa subsp. x varia', 'Medicago sativa x varia');
+INSERT INTO taxonvariant (taxonvariant_id, taxon_id, lexicalqualifier, namebankid, fullname, name) VALUES (800021, 39943, NULL, NULL, 'Hylarana plancyi', 'Hylarana plancyi');
+INSERT INTO taxonvariant (taxonvariant_id, taxon_id, lexicalqualifier, namebankid, fullname, name) VALUES (800022, 24813, NULL, 10259115, 'Sierrana maculata', 'Sierrana maculata');
+
+-- now remap taxonlabel records
+
+UPDATE taxonlabel SET taxonvariant_id = 599278 WHERE taxonlabel_id = 280817;
+UPDATE taxonlabel SET taxonvariant_id = 113112 WHERE taxonlabel_id = 287452;
+UPDATE taxonlabel SET taxonvariant_id = 457054 WHERE taxonlabel_id = 84843;
+UPDATE taxonlabel SET taxonvariant_id = 790039 WHERE taxonlabel_id = 277319;
+UPDATE taxonlabel SET taxonvariant_id = 790138 WHERE taxonlabel_id = 279223;
+UPDATE taxonlabel SET taxonvariant_id = 457054 WHERE taxonlabel_id = 279257;
+UPDATE taxonlabel SET taxonvariant_id = 564067 WHERE taxonlabel_id = 280963;
+UPDATE taxonlabel SET taxonvariant_id = 800011 WHERE taxonlabel_id = 281078;
+UPDATE taxonlabel SET taxonvariant_id = 800007 WHERE taxonlabel_id = 281099;
+UPDATE taxonlabel SET taxonvariant_id = 27564 WHERE taxonlabel_id = 283820;
+UPDATE taxonlabel SET taxonvariant_id = 790655 WHERE taxonlabel_id = 287465;
+UPDATE taxonlabel SET taxonvariant_id = 646502 WHERE taxonlabel_id = 287472;
+UPDATE taxonlabel SET taxonvariant_id = 646502 WHERE taxonlabel_id = 287478;
+
+-- now remap and/or edit taxonvariant records
+
+UPDATE taxonvariant SET lexicalqualifier = 'synonym', namebankid = 10139861, fullname = 'Medicago sativa x Medicago falcata', name = 'Medicago sativa x Medicago falcata', taxon_id = 50823 WHERE taxonvariant_id = 136320;
+UPDATE taxonvariant SET lexicalqualifier = 'synonym', namebankid = 10197530, fullname = 'Fusarium fujikuroi complex', name = 'Fusarium fujikuroi complex', taxon_id = 11549 WHERE taxonvariant_id = 236540;
+UPDATE taxonvariant SET lexicalqualifier = 'scientific name', namebankid = 5985800, fullname = 'Gibberella fujikuroi complex', name = 'Gibberella fujikuroi complex', taxon_id = 11549 WHERE taxonvariant_id = 236541;
+UPDATE taxonvariant SET lexicalqualifier = 'scientific name', namebankid = 10203168, fullname = 'Ditylenchus dipsaci from Cirsium setosum', name = 'Ditylenchus dipsaci', taxon_id = 157494 WHERE taxonvariant_id = 246611;
+UPDATE taxonvariant SET lexicalqualifier = 'scientific name', namebankid = 10203169, fullname = 'Ditylenchus dipsaci from Fragaria sp.', name = 'Ditylenchus dipsaci from Fragaria sp.', taxon_id = 157494 WHERE taxonvariant_id = 246612;
+UPDATE taxonvariant SET lexicalqualifier = 'scientific name', namebankid = 10203170, fullname = 'Ditylenchus dipsaci from Medicago sativa', name = 'Ditylenchus dipsaci', taxon_id = 157494 WHERE taxonvariant_id = 246613;
+UPDATE taxonvariant SET lexicalqualifier = 'scientific name', namebankid = 10203171, fullname = 'Ditylenchus dipsaci from Trifolium pratense', name = 'Ditylenchus dipsaci', taxon_id = 157494 WHERE taxonvariant_id = 246614;
+UPDATE taxonvariant SET lexicalqualifier = 'scientific name', namebankid = 10203172, fullname = 'Ditylenchus dipsaci from Vicia faba', name = 'Ditylenchus dipsaci', taxon_id = 157494 WHERE taxonvariant_id = 246615;
+UPDATE taxonvariant SET lexicalqualifier = 'scientific name', namebankid = 10264573, fullname = 'Ditylenchus dipsaci from Daucus carota', name = 'Ditylenchus dipsaci', taxon_id = 157494 WHERE taxonvariant_id = 327113;
+UPDATE taxonvariant SET lexicalqualifier = NULL, namebankid = 10231531, fullname = 'Alnicola cf. scolecina GLM 37718', name = 'Alnicola scolecina', taxon_id = 221176 WHERE taxonvariant_id = 457054;
+UPDATE taxonvariant SET lexicalqualifier = NULL, namebankid = 3877253, fullname = 'Medicago sativa sativa', name = 'Medicago sativa sativa', taxon_id = 600001 WHERE taxonvariant_id = 558258;
+UPDATE taxonvariant SET lexicalqualifier = NULL, namebankid = 6054839, fullname = 'Phrynosoma Douglassi', name = 'Phrynosoma', taxon_id = 34517 WHERE taxonvariant_id = 564155;
+UPDATE taxonvariant SET lexicalqualifier = 'scientific name', namebankid = NULL, fullname = 'Rytidosperma paschale', name = 'Rytidosperma paschale', taxon_id = 452694 WHERE taxonvariant_id = 599276;
+UPDATE taxonvariant SET lexicalqualifier = 'synonym', namebankid = 8588062, fullname = 'Rytidosperma paschale (Pilger) C.M.Baeza', name = 'Rytidosperma paschale', taxon_id = 452694 WHERE taxonvariant_id = 599277;
+UPDATE taxonvariant SET lexicalqualifier = NULL, namebankid = 10259114, fullname = 'Lithobates maculata', name = 'Lithobates maculata', taxon_id = 24813 WHERE taxonvariant_id = 785042;
+UPDATE taxonvariant SET lexicalqualifier = NULL, namebankid = 2648840, fullname = 'lunaria', name = 'lunaria', taxon_id = 148165 WHERE taxonvariant_id = 785047;
+UPDATE taxonvariant SET lexicalqualifier = NULL, namebankid = 10168536, fullname = 'Pelophylax plancyi', name = 'Pelophylax plancyi', taxon_id = 39943 WHERE taxonvariant_id = 785189;
+UPDATE taxonvariant SET lexicalqualifier = NULL, namebankid = 10127185, fullname = 'Peudomonas aeruginosa', name = 'Peudomonas aeruginosa', taxon_id = 24053 WHERE taxonvariant_id = 785319;
+UPDATE taxonvariant SET lexicalqualifier = NULL, namebankid = 3877252, fullname = 'Medicago sativa falcata', name = 'Medicago sativa falcata', taxon_id = 50823 WHERE taxonvariant_id = 790033;
+UPDATE taxonvariant SET lexicalqualifier = 'canonical form', namebankid = 10576538, fullname = 'Commiphora angustata', name = 'Commiphora angustata', taxon_id = 570026 WHERE taxonvariant_id = 790049;
+UPDATE taxonvariant SET lexicalqualifier = 'canonical form', namebankid = 10576579, fullname = 'Commiphora glauca', name = 'Commiphora glauca', taxon_id = 570027 WHERE taxonvariant_id = 790050;
+UPDATE taxonvariant SET lexicalqualifier = 'canonical form', namebankid = 10294310, fullname = 'Mucronella sp DJM1309', name = 'Mucronella sp DJM1309', taxon_id = 314202 WHERE taxonvariant_id = 790059;
+UPDATE taxonvariant SET lexicalqualifier = 'canonical form', namebankid = 2648378, fullname = 'Polanisia uniglandulosa', name = 'Polanisia uniglandulosa C349', taxon_id = 570071 WHERE taxonvariant_id = 790111;
+UPDATE taxonvariant SET lexicalqualifier = 'canonical form', namebankid = 10584783, fullname = 'Cleome uncifera', name = 'Cleome uncifera', taxon_id = 570073 WHERE taxonvariant_id = 790113;
+UPDATE taxonvariant SET lexicalqualifier = 'canonical form', namebankid = 10584706, fullname = 'Cleome oxalidea', name = 'Cleome oxalidea', taxon_id = 570074 WHERE taxonvariant_id = 790114;
+UPDATE taxonvariant SET lexicalqualifier = 'canonical form', namebankid = 10584531, fullname = 'Cleome africana', name = 'Cleome africana', taxon_id = 570075 WHERE taxonvariant_id = 790122;
+UPDATE taxonvariant SET lexicalqualifier = 'canonical form', namebankid = 10584553, fullname = 'Cleome breyeri', name = 'Cleome breyeri', taxon_id = 570076 WHERE taxonvariant_id = 790123;
+UPDATE taxonvariant SET lexicalqualifier = 'canonical form', namebankid = 10584683, fullname = 'Cleome microaustralica', name = 'Cleome microaustralica', taxon_id = 570078 WHERE taxonvariant_id = 790126;
+UPDATE taxonvariant SET lexicalqualifier = 'canonical form', namebankid = 10584566, fullname = 'Cleome cleomoides', name = 'Cleome cleomoides', taxon_id = 570079 WHERE taxonvariant_id = 790127;
+UPDATE taxonvariant SET lexicalqualifier = 'canonical form', namebankid = 10584670, fullname = 'Cleome luederitziana', name = 'Cleome luederitziana', taxon_id = 570081 WHERE taxonvariant_id = 790129;
+UPDATE taxonvariant SET lexicalqualifier = 'canonical form', namebankid = 2661891, fullname = 'Oryzopsis asperifolia', name = 'Oryzopsis asperifolia', taxon_id = 570096 WHERE taxonvariant_id = 790164;
+UPDATE taxonvariant SET lexicalqualifier = 'canonical form', namebankid = 10748019, fullname = 'Amelichloa brevipes', name = 'Amelichloa brevipes', taxon_id = 570097 WHERE taxonvariant_id = 790165;
+UPDATE taxonvariant SET lexicalqualifier = 'canonical form', namebankid = 10748017, fullname = 'Amelichloa ambigua', name = 'Amelichloa ambigua', taxon_id = 570098 WHERE taxonvariant_id = 790166;
+UPDATE taxonvariant SET lexicalqualifier = 'canonical form', namebankid = 10751855, fullname = 'Danthonia filifolia', name = 'Danthonia filifolia', taxon_id = 570265 WHERE taxonvariant_id = 790391;
+UPDATE taxonvariant SET lexicalqualifier = 'canonical form', namebankid = 9739171, fullname = 'Danthonia chilensis var. aureofulva (E. Desv.) C. Baeza', name = 'Danthonia chilensis aureofulva', taxon_id = 570268 WHERE taxonvariant_id = 790394;
+UPDATE taxonvariant SET lexicalqualifier = 'canonical form', namebankid = 8886481, fullname = 'Danthonia chilensis E. Desv. var. chilensis', name = 'Danthonia chilensis chilensis', taxon_id = 570269 WHERE taxonvariant_id = 790395;
+UPDATE taxonvariant SET lexicalqualifier = 'canonical form', namebankid = 3870064, fullname = 'Dictyosphaerium ehrenbergianum', name = 'Dictyosphaerium ehrenbergianum', taxon_id = 570349 WHERE taxonvariant_id = 790655;
+UPDATE taxonvariant SET lexicalqualifier = NULL, namebankid = 433256, fullname = 'Dictyosphaerium ehrenbergianum Naegeli, 1849', name = 'Dictyosphaerium ehrenbergianum', taxon_id = 570349 WHERE taxonvariant_id = 790656;
+UPDATE taxonvariant SET lexicalqualifier = 'canonical form', namebankid = NULL, fullname = 'Hindakia tetrachotoma', name = 'Hindakia tetrachotoma', taxon_id = 570355 WHERE taxonvariant_id = 790661;
+UPDATE taxonvariant SET lexicalqualifier = NULL, namebankid = 10751874, fullname = 'Danthonia paschalis', name = 'Danthonia paschalis', taxon_id = 452694 WHERE taxonvariant_id = 800014;
+UPDATE taxonvariant SET lexicalqualifier = NULL, namebankid = 10139860, fullname = 'Medicago sativa subsp. x varia', name = 'Medicago sativa x varia', taxon_id = 62238 WHERE taxonvariant_id = 800020;
+
+-- now edit taxon records
+
+UPDATE taxon SET ncbitaxid = 245165, ubionamebankid = 10231531, name = 'Alnicola cf. scolecina GLM 37718' WHERE taxon_id = 221176;
+UPDATE taxon SET ncbitaxid = 296618, ubionamebankid = 10257505, name = 'Rytidosperma aff. pumilum CHR562184' WHERE taxon_id = 261258;
+UPDATE taxon SET ncbitaxid = NULL, ubionamebankid = 10576538, name = 'Commiphora angustata' WHERE taxon_id = 570026;
+UPDATE taxon SET ncbitaxid = NULL, ubionamebankid = 10576579, name = 'Commiphora glauca' WHERE taxon_id = 570027;
+UPDATE taxon SET ncbitaxid = NULL, ubionamebankid = 2648378, name = 'Polanisia uniglandulosa' WHERE taxon_id = 570071;
+UPDATE taxon SET ncbitaxid = NULL, ubionamebankid = 10584783, name = 'Cleome uncifera' WHERE taxon_id = 570073;
+UPDATE taxon SET ncbitaxid = NULL, ubionamebankid = 10584706, name = 'Cleome oxalidea' WHERE taxon_id = 570074;
+UPDATE taxon SET ncbitaxid = NULL, ubionamebankid = 10584531, name = 'Cleome africana' WHERE taxon_id = 570075;
+UPDATE taxon SET ncbitaxid = NULL, ubionamebankid = 10584553, name = 'Cleome breyeri' WHERE taxon_id = 570076;
+UPDATE taxon SET ncbitaxid = NULL, ubionamebankid = 10584683, name = 'Cleome microaustralica' WHERE taxon_id = 570078;
+UPDATE taxon SET ncbitaxid = NULL, ubionamebankid = 10584566, name = 'Cleome cleomoides' WHERE taxon_id = 570079;
+UPDATE taxon SET ncbitaxid = NULL, ubionamebankid = 10584670, name = 'Cleome luederitziana' WHERE taxon_id = 570081;
+UPDATE taxon SET ncbitaxid = NULL, ubionamebankid = 2661891, name = 'Oryzopsis asperifolia' WHERE taxon_id = 570096;
+UPDATE taxon SET ncbitaxid = NULL, ubionamebankid = 10748019, name = 'Amelichloa brevipes' WHERE taxon_id = 570097;
+UPDATE taxon SET ncbitaxid = NULL, ubionamebankid = 10748017, name = 'Amelichloa ambigua' WHERE taxon_id = 570098;
+UPDATE taxon SET ncbitaxid = NULL, ubionamebankid = 10751855, name = 'Danthonia filifolia' WHERE taxon_id = 570265;
+UPDATE taxon SET ncbitaxid = NULL, ubionamebankid = NULL, name = 'Danthonia chilensis aureofulva' WHERE taxon_id = 570268;
+UPDATE taxon SET ncbitaxid = NULL, ubionamebankid = NULL, name = 'Danthonia chilensis chilensis' WHERE taxon_id = 570269;
+UPDATE taxon SET ncbitaxid = 745283, ubionamebankid = 3870064, name = 'Dictyosphaerium ehrenbergianum' WHERE taxon_id = 570349;
+UPDATE taxon SET ncbitaxid = 745284, ubionamebankid = 3307644, name = 'Dictyosphaerium tetrachotomum' WHERE taxon_id = 570355;
+
+-- taxonvariant records can now be deleted
+
+DELETE FROM taxonvariant WHERE taxonvariant_id = 790047;
+DELETE FROM taxonvariant WHERE taxonvariant_id = 790055;
+DELETE FROM taxonvariant WHERE taxonvariant_id = 790136;
+DELETE FROM taxonvariant WHERE taxonvariant_id = 790140;
+DELETE FROM taxonvariant WHERE taxonvariant_id = 790373;
+DELETE FROM taxonvariant WHERE taxonvariant_id = 790390;
+DELETE FROM taxonvariant WHERE taxonvariant_id = 790393;
+DELETE FROM taxonvariant WHERE taxonvariant_id = 790584;
+DELETE FROM taxonvariant WHERE taxonvariant_id = 790633;
+DELETE FROM taxonvariant WHERE taxonvariant_id = 790634;
+DELETE FROM taxonvariant WHERE taxonvariant_id = 790635;
+DELETE FROM taxonvariant WHERE taxonvariant_id = 790658;
+DELETE FROM taxonvariant WHERE taxonvariant_id = 790663;
+DELETE FROM taxonvariant WHERE taxonvariant_id = 790664;
+
+-- finally, taxon records can be deleted
+
+DELETE FROM taxon WHERE taxon_id = 161564;
+DELETE FROM taxon WHERE taxon_id = 172733;
+DELETE FROM taxon WHERE taxon_id = 172734;
+DELETE FROM taxon WHERE taxon_id = 172735;
+DELETE FROM taxon WHERE taxon_id = 172736;
+DELETE FROM taxon WHERE taxon_id = 172737;
+DELETE FROM taxon WHERE taxon_id = 271867;
+DELETE FROM taxon WHERE taxon_id = 428261;
+DELETE FROM taxon WHERE taxon_id = 564233;
+DELETE FROM taxon WHERE taxon_id = 564366;
+DELETE FROM taxon WHERE taxon_id = 564472;
+DELETE FROM taxon WHERE taxon_id = 570024;
+DELETE FROM taxon WHERE taxon_id = 570032;
+DELETE FROM taxon WHERE taxon_id = 570036;
+DELETE FROM taxon WHERE taxon_id = 570086;
+DELETE FROM taxon WHERE taxon_id = 570264;
+DELETE FROM taxon WHERE taxon_id = 570267;
+DELETE FROM taxon WHERE taxon_id = 570329;
+DELETE FROM taxon WHERE taxon_id = 570330;
+DELETE FROM taxon WHERE taxon_id = 570331;
+DELETE FROM taxon WHERE taxon_id = 570352;
+DELETE FROM taxon WHERE taxon_id = 570357;
+DELETE FROM taxon WHERE taxon_id = 570358;
+
+commit;
+
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
|
From: <sfr...@us...> - 2010-06-02 12:43:04
|
Revision: 704
http://treebase.svn.sourceforge.net/treebase/?rev=704&view=rev
Author: sfrgpiel
Date: 2010-06-02 12:42:58 +0000 (Wed, 02 Jun 2010)
Log Message:
-----------
This is the solution recommended by Rutger for fixing issue 2992022, which is an inability to view study 2164
Added Paths:
-----------
trunk/treebase-core/db/cleaning/2010-06-02_fix_S2164/
trunk/treebase-core/db/cleaning/2010-06-02_fix_S2164/README.txt
trunk/treebase-core/db/cleaning/2010-06-02_fix_S2164/fix_S2164.sql
Added: trunk/treebase-core/db/cleaning/2010-06-02_fix_S2164/README.txt
===================================================================
--- trunk/treebase-core/db/cleaning/2010-06-02_fix_S2164/README.txt (rev 0)
+++ trunk/treebase-core/db/cleaning/2010-06-02_fix_S2164/README.txt 2010-06-02 12:42:58 UTC (rev 704)
@@ -0,0 +1,36 @@
+To apply, run fix_S2164.sql
+
+--------
+
+Hi all,
+
+I'm trying to fix issue 2992022, which was hypothesised to be a
+character encoding problem. This is not the case, it has nothing to do
+with UTF-8 handling. It's a database inconsistency:
+
+* in the person table, there's both an entry for "Balázs Tímea",
+person_id=3971 and for "Tímea Balázs", person_id=5609
+
+* in the latter record, first and last name are in the correct order,
+but the author's email address is missing, and the author order index
+is 5, on a three-author paper. In the former record, the first and
+last name are switched around, but everything else is in the correct
+order. It looks like the nullpointer exception is thrown because there
+is no author 4 (see:
+http://www.mycologia.org/cgi/content/abstract/101/2/247).
+
+* here's my suggested fix: correct the name order in record 3971,
+purge record 5609 from person and from citation_author, i.e.:
+
+update person set firstname='Tímea', lastname='Balázs' where person_id=3971;
+delete from citation_author where authors_person_id=5609;
+delete from person where person_id=5609;
+
+On the test instance this fixes the exception, the citation is
+serialised correctly. Can someone fix this on the production instance?
+I re-assigned this bug to Bill, but whoever has access to the prod
+server can run those SQL snippets and everything should be OK.
+
+Cheers,
+
+Rutger
\ No newline at end of file
Added: trunk/treebase-core/db/cleaning/2010-06-02_fix_S2164/fix_S2164.sql
===================================================================
--- trunk/treebase-core/db/cleaning/2010-06-02_fix_S2164/fix_S2164.sql (rev 0)
+++ trunk/treebase-core/db/cleaning/2010-06-02_fix_S2164/fix_S2164.sql 2010-06-02 12:42:58 UTC (rev 704)
@@ -0,0 +1,13 @@
+-- beware that the inspection tool to apply these statements needs to be
+-- utf-8 aware
+
+BEGIN WORK;
+
+UPDATE person SET firstname='Tímea', lastname='Balázs' WHERE person_id=3971;
+
+DELETE FROM citation_author WHERE authors_person_id=5609;
+
+DELETE FROM person WHERE person_id=5609;
+
+COMMIT;
+
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
|
From: <sfr...@us...> - 2012-01-31 21:59:08
|
Revision: 1027
http://treebase.svn.sourceforge.net/treebase/?rev=1027&view=rev
Author: sfrgpiel
Date: 2012-01-31 21:58:59 +0000 (Tue, 31 Jan 2012)
Log Message:
-----------
Script and data file for updating TB1 citations
Added Paths:
-----------
trunk/treebase-core/db/cleaning/2012-01-31_Update_TB1_Citations/
trunk/treebase-core/db/cleaning/2012-01-31_Update_TB1_Citations/README
trunk/treebase-core/db/cleaning/2012-01-31_Update_TB1_Citations/citationdata.txt
trunk/treebase-core/db/cleaning/2012-01-31_Update_TB1_Citations/update_citations.pl
Added: trunk/treebase-core/db/cleaning/2012-01-31_Update_TB1_Citations/README
===================================================================
--- trunk/treebase-core/db/cleaning/2012-01-31_Update_TB1_Citations/README (rev 0)
+++ trunk/treebase-core/db/cleaning/2012-01-31_Update_TB1_Citations/README 2012-01-31 21:58:59 UTC (rev 1027)
@@ -0,0 +1,11 @@
+This script is intended to update citation information from a UTF8-encoded tab-separated
+export of EndNote citation metadata. The records are identified by the legacy study_id.
+Author names are unaffected. The order of the columns in the input file is as follows:
+
+ legacyID, authors, year, title, journal, vol, num, pages, doi, url, keywords, abstract, citation_id
+
+To apply, edit the database credentials on lines 18-21, and then type:
+
+ perl update_citations.pl citationdata.txt
+
+ ... where citationdata.txt is the citation metadata file.
\ No newline at end of file
Added: trunk/treebase-core/db/cleaning/2012-01-31_Update_TB1_Citations/citationdata.txt
===================================================================
--- trunk/treebase-core/db/cleaning/2012-01-31_Update_TB1_Citations/citationdata.txt (rev 0)
+++ trunk/treebase-core/db/cleaning/2012-01-31_Update_TB1_Citations/citationdata.txt 2012-01-31 21:58:59 UTC (rev 1027)
@@ -0,0 +1,2284 @@
+S1847 K. Voigt;L. Olsson 2008 Molecular phylogenetic and scanning electron microscopical analyses places the Choanephoraceae and the Gilbertellaceae in a monophyletic group within the Mucorales (Zygomycetes, Fungi) Acta Biologica Hungarica 59 3 A multigene genealogy based on maximum parsimony and distance analyses of the exonic genes for actin (act) and translation elongation factor 1 alpha (tef), the nuclear genes for the small (18S) and large (28S) subunit ribosomal RNA (comprising 807, 1092, 1863, 389 characters, respectively) of essentially all fifty genera of the Mucorales (Zygomycetes) suggests that the Choanephoraceae Schröter form a monophyletic group. The monotypic genus Gilbertella Hesseltine, which is classified into the monogeneric family Gilbertellaceae Benny, appears in close phylogenetic relatedness to the Choanephoraceae. The monophyly of the Choanephoraceae is supported by aberrantly from low to higher ranging bootstrap proportions (67 and 98 % in distance and maximum parsimony analyses, respectively), whereas the monophyly of the Choanephoraceae-Gilbertellaceae clade is supported by high bootstrap values (98 and 100 %) proposing that both families can be joined into one family which leads to the elimination of the Gilbertellaceae as a separate family. In order to validate these relationships single-locus Neighbor-joining analyses were performed on nuclear genes of the 18S, 5.8S, 28S and internal transcribed spacer (ITS) 1 ribosomal RNA and the elongation factor 1 alpha (tef) and beta tubulin (btub) nucleotide sequences. The monophyly of the Choanephoraceae-Gilbertellaceae clade could be confirmed in all gene trees. The ultrastructure of sporangia, sporangiola and sporangiospores show several morphological characters (e.g. longitudinal sutures splitting the sporangial walls, striations and polar ciliate appendages on the sporangiospores) which occur only in the Choanephoraceae-Gilbertellaceae clade. Based on these results and reanalysis of other morphological and physiological evidences from the literature, we propose to fuse the two families Choanephoraceae and Gilbertellaceae into one family, the Choanephoraceae.
+S465 J. A. Coddington 1991 Cladistics and spider classification: Araneomorph phylogeny and the monophyly of orb weavers (Araneae: Araneomorphae; Orbiculariae) Acta Zoologica Fennica 190 75-87 Nineteen new characters pertinent to the phylogeny of the spider suborder Araneomorphae are reviewed and tested against a previous data set, using quantitative cladistic techniques. An hypothesis of cladistic structure within Araneomorphae in the form of a quantitatively analysed cladogram is presented for the first time. The analysis supports orb weaving spiders (Orbiculariae: Deinopoidea and Araneoidea) as a monophyletic group (apparently sister to Dictynoidea) and Linyphiidae as an araneoid family. Likely precursors of orbs are discussed, as are relevant fossil evidence and possible times of origin
+S11x5x95c12c16c30 L. E. Graham;C. F. Delwiche;B. D. Mishler 1991 Phylogenetic connections between the 'green algae' and the 'bryophytes' Advances in Bryology 4 213-244 An updated cladistic analysis of charophycean green algae, focusing on divergence of the embryophyte clade, was derived by application of Wagner parsimony to a data set consisting of morphological, ultrastructural, and biochemical characters. Characters not previously used in such analyses include: sexual growth response (cell-to cell interactions between gametes or zygotes and vegetative thallus cells), post-fertilization enlargement of zygotes, polyphenolic deposition induced by zygote formation, diagonal cell divisions in spermatid ontogeny, cortical microtubule arrays, preprophase microtubule bands, bicentriolar centrosomes, and sporic meiosis. Despite the addition of these new characters, the topology of the most parsimonious tree resulting from their use was remarkably similar to a cladogram previously published by Mishler and Churchill. The present analyses suggest that future biochemical and cellular studies should be focused on the following: the pattern of occurrence of glycolate oxidase and class I aldolases in lower charophytes and putative ancestral flagellates; cell-to-cell growth interactions related to sexual reproduction; cell division and sexual reproduction in Chaetosphaeridium; patterns of involvement of callose in reproductive development; biochemical pathways leading to flavonoid synthesis and polyphenolic deposition in cell walls; cytoskeleton dynamics, phragmoplast development, and structure and rob of plasmodesmata; comparative structure of chloroplasts and pyrenoids; behavior of peroxisomes during cell division-- and the nature of the surface layer present on thalli of some species of Coleochaete. Evidence is presented to support the view that while the archegonium bearing plants may well be polyphyletic or paraphyletic, as has been effectively argued by others, the embryophytes are most likely monophyletic. The molecular, biochemical, and structural evidence suggests that embryophytes are derived from charophycean ancestors similar to Charales and Coleochaete. It is concluded that charophycean algae should be the taxa of choice for use as model experimental systems in which to study the cellular, biochemical, and molecular bases of many embryophyte features, rather than more distantly related green algae.
+S1404 D. A. Morrison 2006 Phylogenetic analyses of parasites in the new millennium Advances in Parasitology 63 Phylogenetic analysis has changed greatly in the last decade, and the most important themes in that change are reviewed here. Sequence data have become the most common source of phylogenetic information. This means that explicit models for evolutionary processes have been developed in a likelihood context, which allow more realistic data analyses. These models are becoming increasingly complex, both for nucleotides and for amino acid sequences, and so all such models need to be quantitatively assessed for each data set, to find the most appropriate one for use in the tree-building analysis. Bayesian analysis has been developed for tree-building and is greatly increasing in popularity. This is because a good heuristic strategy exists, which allows large data sets to be analysed with complex evolutionary models in a practical time. Perhaps the most disappointing aspect of tree interpretation is the ongoing confusion between rooted and unrooted trees, the latter being of very limited practical use for most biological questions. The review finishes with a detailed consideration of the analysis of a multi-gene data set for several dozen taxa of Cryptosporidium (Apicomplexa), illustrating many of the theoretical and practical points highlighted elsewhere.
+S1098 A. L. Hipp;P. E. Rothrock;A. A. Reznicek;P. E. Berry 2004 Chromosome number changes associated with speciation in sedges: A phylogenetic study in Carex section Ovales (Cyperaceae) using AFLP data Aliso 23 193-203 AFLP data recover a tree that is topologically congruent with previous phylogenetic estimates based on nrDNA sequences and provide support for four species groups within ENA Clade I. A maximum likelihood method designed for analysis of restriction site data is used to evaluate the strength of support for alternative topologies. While there is little support for the precise placement of the root, likelihood of topologies in which any of the four clades identified within ENA Clade I is forced to be paraphyletic is much lower than likelihood of the optimal tree. Chromosome counts for a sampling of species from throughout section Ovales are mapped onto the tree as well as counts for all species in ENA Clade I. Parsimony reconstruction of ancestral character states suggest that (1) Heilborn's hypothesis that more highly derived species in Carex have higher chromosome counts does not apply within section Ovales, (2) the migration to Eastern North America involved a decrease in average chromosome count within section Ovales, and (3) intermediate chromosome counts are ancestral within ENA Clade I. A more precise understanding of chromosomal evolution in Carex should be possible using likelihood analyses that take into account the intraspecific polymorphism and wide range of chromosome counts that characterize the genus.
+S1890 J. T. Columbus;R. Cerros-Tlatilpa;M. S. Kinney;M. E. Siqueiros-Delgado;H. L. Bell;M. P. Griffith;N. F. Refulio-Rodriguez 2007 Phylogenetics of Chloridoideae (Gramineae): a preliminary study based on nuclear ribosomal internal transcribed spacer and chloroplast trnL-F sequences Aliso 23 587-601 The phylogeny of Chloridoideae (Gramineae) was inferred from parsimony analyses of DNA sequences from two genomes˜the chloroplast trnL intron, trnL 3? exon, and trnLˆF intergenic spacer, and the nuclear ribosomal internal transcribed spacer region (ITS1 + 5.8S + ITS2). Eighty species representing 66 chloridoid genera were sampled, including all but four of the native New World genera. Analyses of the individual and combined data sets were performed. The phylogenies were found to be highly congruent. Of the four tribes and seven subtribes of Chloridoideae sensu Clayton and Renvoize (1986) whose phylogenetic status could be tested with our taxon sample, only Orcuttieae and Uniolinae were monophyletic. The phylogenies suggested significant homoplasy in morphological traits, including inflorescence type, number of florets per spikelet, and number of lemma nerves. We propose a new classification based on the three main clades in the phylogenies˜tribes Cynodonteae, Eragrostideae, and Zoysieae. The Eragrostideae clade is well resolved and supported and is further divided into three subtribes, Cotteinae, Eragrostidinae, and Uniolinae. Cynodonteae include most of the genera in our study, but the clade is poorly resolved. However, a clade formed of Muhlenbergia and nine other genera is present in both phylogenies and is well resolved and supported. A number of interesting, well-supported relationships are evident in the phylogenies, including PappophorumˆTridens flavus, TragusˆWillkommia, and GouiniaˆTridens muticusˆTriplasisˆVaseyochloa. Except for Bouteloua, no genus represented by multiple species proved to be monophyletic in the phylogenies.
+S1141 J. R. Starr;G. Gravel;A. Bruneau;A. M. Muasya 2007 Phylogenetic Implications of a Unique 5.8S nrDNA Insertion in Cyperaceae. Monocots III/Grasses IV Aliso 23 84-98 The purpose of this study was to assess the phylogenetic utility of a large insertion (3 bp) in the 5.8S gene of nuclear ribosomal DNA (rDNA) in the Cyperaceae and selected Juncaceae. This was done by reconstructing the character evolution of the insertion on a phylogeny derived from rbcL sequences. Results suggest that the insertion was gained once at the base of the Cyperaceae followed by multiple losses in its most derived taxa. Despite several homoplastic loses (CI = 0.20), the pattern of insertion loss (RI = 0.88) and base pair variation within the insertion were useful for defining sedge clades at various taxonomic levels. For example, whereas an indel loss appeared to characterise a major terminal clade within the Cyperaceae, both insertion losses and sequence variation were consistent with infrageneric clades previously discovered in an ITS phylogeny of the genus Eleocharis. The presence/absence of the insertion also supported previous conclusions based on morphological and molecular data that the tribe Scirpeae and the genus Scirpus s.l. are polyphyletic. In the context of our current understanding of Cyperaceae relationships, evolutionary patterns related to this insertion provide additional support for groups defined in prior phylogenetic analyses. Differences between maximum likelihood and parsimony analyses indicated that the paraphyly of Juncaceae and Cyperaceae, as detected in previous molecular analyses, is based upon a chimeric rbcL sequence for the controversial genus Oxychloe (Juncaceae). When the Cyperaceae portion of the molecule is removed, Juncaceae and Cyperaceae are monophyletic, with Oxychloe positioned within a Southern Hemisphere clade of single-flowered genera in the Juncaceae. Keywords: Cyperaceae, 5.8S insertion, ITS region, Juncaceae, Oxychloe, rDNA.
+S249 R. K. Jansen;J. D. Palmer 1988 Phylogenetic implications of chloroplast DNA restriction site variation in the Mutisieae (Asteraceae) American Journal of Botany 75 753-766 Phylogenetic relationships among 13 species in the tribe Mutisieae and a single species from each of three other tribes in the Asteraceae were assessed by chloroplast DNA restriction site mapping Initially, 211 restriction site mutations were detected among 16 species using 10 restriction enzymes. Examination of 12 of these species using nine more enzymes revealed 179 additional restriction site mutations. Phylogenetic analyses of restriction site mutations were performed using both Dollo and Wagner parsimony, and the resulting monophyletic groups were statistically tested by the bootstrap method. The phylogenetic trees confirm an ancient evolutionary split in the Asteraceae that was previously suggested by the distribution of a chloroplast DNA inversion. The subtribe Barnadesiinae of the tribe Mutisieae is shown to be the ancestral group within the Asteraceae. The molecular phylogenies also confirm the paraphyly of the Mutisieae and provide statistical support for the monophyly of three of its four currently recognized subtribes (Barnadesiinae, Mutisiinae, and Nassauviinae). The fourth subtribe, Gochnatiinae, is shown to be paraphyletic. Within the subtribes, several closely related generic pairs are identified. Chloroplast DNA sequence divergence among genera of the Asteraceae ranges between 0.7 and 5.4%, which is relatively low in comparison to other angiosperm groups. This suggests that the Asteraceae is either a relatively young family or that its chloroplast DNA has evolved at a slower rate than in other families.
+S2x2x96c16c32c09 E. A. Kellogg 1989 Comments on genomic genera in the Triticeae (Poaceae) American Journal of Botany 76 796-805 Generic delimitation in the Triticeae has long been problematical because the extensive hybridization in the group is not readily accomodated in a hierarchical classification. The genomic genera of Love (1984) are one solution, but fully one-third of them are polyphyletic, incorporating 2 or more genomes. The suggestion that the tribe be considered a single genus (Stebbins, 1956) is theoretically defensible—the genus would be strictly monophyletic—but probably impractical. This paper presents a cladistic analysis of the tribe that differs from previous analyses in that it uses only strictly monophyletic (monogenomic) groups as terminal taxa; hybrids (heterogenomic groups) are shown as reticulations. Monophyletic groups can then be delimited to minimize the number of polyphyletic genera. A classification incorporating genomic information is derived from the phylogeny.
+S11x6x95c09c31c53 R. J. Soreng 1990 Chloroplast-DNA phylogenetics and biogeography in a reticulating group: Study in Poa (Poaceae) American Journal of Botany 77 1383-1400 Cladistic analysis of Poa chloroplast DNA (cpDNA) restriction sites tested previously hypothesized relationships within the genus. Forty-six taxa representing 19 sections or groups and three subgenera of Poa and two out-group genera, Puccinellia and Bellardiochloa, are analyzed. Five major and several minor cpDNA groups are identified. The cpDNA cladogram is generally congruent with the subgeneric taxonomy of Poa. Exceptions are reclassified or are discussed in terms of character incompatibilities and possible reticulation events. The cpDNA tree detected relationships among sections that were unresolved using traditional character sets and provides a basis for polarization of morphological character states. An assessment of biogeographic events based on the cpDNA tree suggests: 1 ) Poa originated in Eurasia; 2) at least six groups of species independently colonized North America; and 3) two of the latter groups colonized South America, and one closely related group colonized New Zealand and Australia. The cpDNA tree provided a conservative estimate of the number of amphi-neotropical disjunctions when compared to the known number of species disjunctions.
+S2x4x96c13c37c50 P. G. Wolf;D. E. Soltis;P. S. Soltis 1990 Chloroplast-DNA and allozymic variation in diploid and autotetraploid Heuchera Grossulariifolia (Saxifragaceae) American Journal of Botany 77 232-244 Diploid and autotetraploid populations of Heuchera grossulariifolia occur throughout mountainous regions of the Pacific Northwest. Controlled greenhouse crosses indicated that the two cytotypes are largely reproductively isolated. Fourteen diploid and 11 tetraploid populations were analyzed electrophoretically. Individual tetraploid plants expressed up to four alleles per isozyme locus, and tetraploid populations had significantly higher levels of heterozygosity than diploids. Mean observed heterozygosity was 0.159 for tetraploid populations and 0.058 for diploid populations. The patterns of allelic distribution betwecn cytotypes suggested multiple origins of autotetraploids. This hypothesis was supported by restriction-site analysis of chloroplast-DNA (cpDNA) variation which indicated that there had been at least three independent origins of tetraploids. Electrophoretic data, in conjunction with a cpDNA-based phylogeny and geographic distribution of populations, suggest that autopolyploid populations evolved several times as migration of diploids occurred down river systems. This study further supports the contention that autopolyploidy can be a common and successful speciation process in some groups of plants.
+S11x5x95c20c33c35 B. Bremer;R. K. Jansen 1991 Comparative restriction site mapping of chloroplast DNA implies new phylogenetic relationships within Rubiaceae American Journal of Botany 78 198-213 Phylogenetic analyses of 33 species of Rubiaceae were performed using chloroplast DNA restriction site mutations. Complete cleavage maps of eight enzymes were constructed for Psychotria bacteriophila and used as a reference in comparisons among other species. The species examined represent 33 genera from 18 tribes and four subfamilies of the Rubiaceae. A total of 268 restriction site mutations was detected, 161 of which were phylogenetically informative. Wagner and Dollo parsimony trees were compared to the classifications of Verdcourt, Bremekamp, and Robbrecht. The Wagner analysis resulted in six equally parsimonious trees with 348 steps and 54% homoplasy. Dollo analysis resulted in a single most parsimonious tree. Most clades were identical in the two analyses. The subfamily Cinchonoideae is paraphyletic. The subfamilies Antirheoideae, Ixoroideae, and Rubioideae are monophyletic, although their circumscriptions differ from previous classifications. Several new phylogenetic relationships are indicated: the tribe Chiococceae (Ixoroideae) groups with Exostema and Coutarea (Cinchonoideae); the subfamily Ixoroideae including tribe Vanguerieae is closely related to Pogonopus, Pinckneya, Calycophyllum, and Mussaenda (Cinchonoideae); and tribe Hamelieae forms a monophyletic group outside the subfamily Rubioideae.
+S279 K. J. Curry;L. M. McDowell;W. S. Judd;W. L. Stern 1991 Osmophores, floral features, and systematics of Stanhopea (Orchidaceae) American Journal of Botany 78 610-623 The floral fragrance glands (osmophores) of 18 species of Stanhopea and Sievekingia were examined through a series of developmental studies at light and electron microscope levels including late bud stages through postanthesis. Various characters were identified to be of potential systematic value and were recorded for each species. These characters included: texture of the osmophore surface, number of distinct cell layers comprising the osmophore, nature of lipid inclusions in osmophore cells, and presence or absence of plastoglobuli in osmophore amyloplasts. These characters were combined with traditional features of floral lip morphology for cladistic analysis. Sievekingia was the postulated outgroup. Stanhopea ecornuta showed the largest number of plesiomorphic characters. Stanhopea pulla, S. annulata, and S. candida were only slightly more derived. Stanhopea anfracta, S. gibbosa, S. martiana, S. oculata, S. radiosa, S. ruckeri, S. saccata, S. shuttleworthii, S. tigrina, S. vasquezii, and S. wardii form a monophyletic group that can be recognized by a labellum with an articulated epichile and a bicornuate mesochile (or hypochile). Stanhopea tricornis may be a hybrid between a species of Sievekingia and Stanhopea.
+S2x3x96c13c09c47 M. Lavin;S. Mathews;C. Hughes 1991 Cholorplast DNA variation in Gliricidia sepium (Leguminosae): Intraspecific phylogeny and tokogeny American Journal of Botany 78 1576-1585 An analysis of chloroplast DNA restriction site variation in Gliricidia sepium reveals two geographically distinct chloroplast lineages, one in the Yucatan Peninsula and the other along the Pacific Coast in Mexico and Central America. Geographical, morphological, biochemical, and habitat distinctions suggest that these two chloroplast lineages reflect organismal lineages. Within the Pacific coastal chloroplast lineage, there exist sublineages that most likely reflect tokogenetic systems of relationship rather than organismal phylogeny, a hypothesis supported by the co-occurrence of more than one of these chloroplast sublineages in a single population. The genetic distance between any two of these chloroplast lineages ranges from 0.0001 to 0.0024 nucleotide substitutions per site and reveals relatively high levels of intraspecific divergence. We suggest that assessing intraspecific chloroplast DNA variation is important generally in higher level phylogenetic analysis because it enables one to obtain truer estimates of homoplasy, detect potentially cryptic species, and distinguish among molecular markers that reflect phylogenetic vs. tokogenetic relationships. This is in addition to determining the extent of potential complicating factors such as introgression and lineage sorting from polymorphic ancestry.
+S2x3x96c16c45c53 L. H. Rieseberg 1991 Homoploid reticulate evolution in Helianthus (Asteraceae): Evidence from ribosomal genes American Journal of Botany 78 1218-1237 Phylogenetic relationships among the 21 taxa comprising Helianthus sect. Helianthus and three outgroup species were assessed by restriction site mapping of the 1 8S-25S nuclear ribosomal RNA gene family. Wagner parsimony analysis of the 41 restriction site or length mutations observed produced a single 59-step most parsimonious tree. This tree was then compared to a cytoplasmic-based plastid phylogeny for this group. Several major discrepancies were observed between the two phylogenies suggesting both recent and ancient introgression. Furthermore, three cases of diploid hybrid speciation are unambiguously documented and a fourth case is suggested. These data are interpreted to suggest that evolution in Heliathus is reticulate rather than exclusively dichotomous and branching.
+S11x6x95c09c42c30 L. E. Talbert;G. M. Magyar;M. Lavin;T. K. Blake;S. L. Moylan 1991 Molecular evidence for the origin of the S-derived genomes of polyploid Triticum species American Journal of Botany 78 340-349 The genus Triticum includes several polyploid species that arose due to hybridization between two or more diploid species. Section Sitopsis is comprised of five diploid species given the genome designation S. Four polyploid species are recognized that contain an S or S-derived genome. We have used two repetitive DNA sequences found primarily in the S genomes of Triticum to determine the likely diploid progenitors of the polyploid species. Comparison of restriction fragments that hybridize to probes for these sequences suggests that T. speltoides is distinct from other members of section Sitopsis (i.e., T. longissimum, T. bicorne, T. searsii, and T. sharonense). The S-derived genome of T. aestivum is more closely related to T. speltoides than to the other Sitopsis diploids. The restriction fragment pattern of T. timopheevii is 98% identical to that of T. speltoides, while those of T. kotschyi and T. syriacum are identical to the group of diploids represented by T. longissimum, T. bicorne, T. searsii, and T. sharonense. Our results are compatible with previous molecular and biochemical data regarding relationships among Triticum species containing an S or S-derived genome.
+S2x7x96c15c33c46 D. A. Waters;M. A. Buchheim;R. A. Dewey;R. L. Chapman 1992 Preliminary inferences of the phylogeny of bryophytes from nuclear-encoded ribosomal RNA sequences American Journal of Botany 79 459-466 Ribosomal RNA sequences and cladistic analysis were used to infer a phylogeny for eight bryophyte taxa. Portions of the cytoplasmic large (26S-like) and small (18S-like) subunit ribosomal RNA genes were sequenced for three marchantioid liverworts (Asterella, Conocephalum, and Riccia), three mosses (Atrichum, Fissidens, and Plagiomnium), and two hornworts (Phaeoceros and Notothylas). Cladistic analysis of these data suggests that the hornworts are the sister group to the mosses, the mosses and hornworts form a clade that is sister to the tracheophytes, and the liverworts form a clade sister to the other land plants. These results differ from previous cladistic analyses based on morphology, ultrastructure, and biochemistry, wherein the mosses alone are sister group to the tracheophytes. We conclude that cladistic analysis of molecular data can provide an independent data set for the study of bryophyte phylogeny, but the differences between the molecular and morphological results are a topic for further investigation.
+S11x6x95c09c20c33 D. E. Soltis;D. R. Morgan;A. Grable;P. S. Soltis;R. Kuzoff 1993 Molecular systematics of Saxifragaceae sensu stricto American Journal of Botany 80 1056-1081 To circumscribe Saxifragaceae sensu stricto better, as well as to elucidate generic relationships within this group, we sequenced the chloroplast gene rbcL and its 3' flanking region (yielding 1,471 bp) from 19 genera considered to represent core members of Saxifragaceae. In addition, we conducted a restriction site analysis of chloroplast DNA (cpDNA) for 21 core genera using 23 restriction endonucleases. Phylogenetic analyses using both data sets corroborate the results obtained from surveying the distribution of the loss of the intron in the chloroplast gene rpl2 in delimiting a well-defined Saxifragaceae sensu stricto. Within the Saxifragaceae s.s. clade, a number of poorly resolved, basal phylogenetic branches supports the hypothesis that Saxifragaceae s.s. radiated rapidly very early in its evolutionary history. Molecular data also indicate the presence of several strongly supported groups of genera, such as the Boykinia group (Boykinia, Suksdorfia, Bolandra, Sullivantia, Jepsonia, and Telesonix), the Heuchera group (Heuchera, Bensoniella, Conimitella, Elmera, Lithophragma, Mitella, Tellima, Tiarella, and Tolmiea) the Leptarrhena/Tanakaea group, and the Darmera group (Darmera, Astilboides, Mukdenia, Bergenia, and Rodgersia). Significantly, molecular data suggest that the very large, taxonomically complex genus Saxifraga may not be monophyletic. DNA data have also helped to resolve the generic relationships of problematic taxa, indicating, for example, that Telesonix and the enigmatic Jepsonia are sister taxa. In addition to its phylogenetic implications, this study provides insight into basic trends in morphological, chemical, and cytological evolution within Saxifragaceae s.s. The molecular-based phylogenies suggest multiple origins and/or losses of several classes of flavonoid compounds, as well as several independent instances of reduction in stamen and petal number, hypanthium-ovary fusion, and aneuploidy. This study also illustrates the ability of rbcL sequence data to resolve generic-level relationships in some taxonomic groups.
+S2x6x96c17c48c00 M. F. Wojciechowski;M. J. Sanderson;B. G. Baldwin;M. J. Donoghue 1993 Monophyly of aneuploid Astragalus (Fabaceae): Evidence from nuclear ribosomal DNA internal transcribed spacer sequences American Journal of Botany 80 711-722 Evolutionary relationships within Astragalus L. (Fabaceae) were inferred from nucleotide sequence variation in nuclear ribosomal DNA of both New World and Old World species. The internal transcribed spacer regions (ITS) of 18S-26S nuclear ribosomal DNA from representatives of 26 species of Astragalus, three species of Oxytropis DC., and two outgroup taxa were analyzed by polymerase chain reaction amplification and direct DNA sequencing. The length of the ITS I region within these taxa varied from 221 to 231 bp, while ITS 2 varied in length from 207 to 217 bp. Of the aligned, unambiguous positions, approximately 34% were variable in each spacer region. In pairwise comparisons among Astragalus species and outgroup taxa, sequence divergence at these sites ranged from 0 to 1 8.8% in ITS I and from 0 to 21.7% in ITS 2. Parsimony analyses of these sequences resulted in a well-resolved phylogeny that is highly concordant with previous cytogenetic and chloroplast DNA evidence for a major phylogenetic division in the genus. These data suggest that the New World aneuploid species of Astragalus form a monophyletic but morphologically cryptic group derived from euploid species of Old World (Eurasian) origin, which are consequently paraphyletic.
+S2x4x96c14c20c12 J. V. Freudenstein 1994 Character transformation and relationships in Corallorhiza (Orchidaceae: Epidendroideae). II. Morphological variation and phylogenetic analysis American Journal of Botany 81 1458-1467 Morphological and anatomical study of Corallorhiza. a genus of primarily New World leafless mycoparasitic orchids, was undertaken in order to produce a hypothesis of relationships among the species and to gain some understanding of character transformations. Cladistic analysis of the resulting data set gave two most parsimonious trees. Analysis of combined plastid DNA and morphological data yielded a single topology, identical to one of the two from the analysis of morphological data alone. Molecular data do not conflict with the morphological data set, and provide more resolution within the C. maculata complex. The combined data indicate that C. striata is the sister group to the remainder of the genus; the circumboreal C. trifida also occupies a basal position. Corallorhiza wisteriana and C. odontorhiza comprise the sister group to the C. maculata+ C. mertensiana + C. bulbosa clade. Only two synapomorphies, presence of the coralloid rhizome and loss of leaves, unite the species of Corallorhiza. The coralloid rhizome appears to be a paedomorphic development, due to its similarity to a protocorm; if so, it too is a loss character and may be considered only weak support for monophyly of the genus. Predominant autogamy, seen in C. trifida and cleistogamous C. odontorhiza, has probably arisen independently in these taxa.
+S228 J. V. Freudenstein;J. J. Doyle 1994 Character transformation and relationships in Corallorhiza (Orchidaceae: Epidendroideae). I. Plastid DNA American Journal of Botany 81 1449-1457 As part of a phylogenetic study of Corallorhiza. a genus of ten species of leafless mycoparasitic orchids, the large single copy region (LSC) and inverted repeat of plastid DNA were examined with restriction fragment analysis to obtain information on relationships among the species, as well as to determine whether structural changes had occurred in the plastome, Deletion areas of between 1.3 and 6.0 kb were detected in the LSC of the C. maculata complex and C. striata. but most of the genome was not significantly affected. Cladistic analysis of restriction site characters produced one most parsimonious tree; additional accessions screened were shown to be most closely related to the primary accession for each species. No synapomorphy was found to unite Corallorhiza. The same topology resulted when deletion characters were added and indicates that deletions in the region of rpo and psa genes have occurred independently in the C. maculata and C striata groups. Because of the coarse level of resolution in the probing, it is not clear to what extent the genes themselves have been affected. Only one species, the undeleted C. trifida, has been examined physiologically, and it was found to be photosynthetic. This fact, and the deletion pattern, suggest that species of Corallorhiza may show a range of levels of autotrophy.
+S2x7x96c17c37c03 T. Mione;R. C. Olmstead;R. K. Jansen;G. J. Anderson 1994 Systematic implications of chloroplast DNA variation in Jaltomata and selected Physaloid genera (Solanaceae) American Journal of Botany 81 912-918 Chloroplast DNA restriction site data were used to assess relationships among the solanaceous genera Jaltomata, Hebecladus, Old and New World Physalis, Chamaesaracha, Leucophysalis, Margaranthus, Nicandra, and Saracha, and to assess interspecific relationships within Jaltomata. Cladograms rooted with Nicotiana tabacum were constructed with Wagner and Dollo parsimony. Strict consensus trees indicate that Hebecladus onginated from within Jaltomata; together these genera are monophyletic and constitute the recently circumscribed genus Jaltomata. There are two primary clades in Jaltomata: one a morphologically diverse group confined to western (largely Andean) South America, the Greater Antilles, and the Galapagos Islands; and the othcr a morphologically homogeneous group widely distributed from the southwestern United States to Bolivia. The controversial Leucophysalis viscosa, formerly treated as Jaltomata viscosa, is related to Leucophysalis, Physalis, Chamaesaracha, and Margaranthus; it does not group with any of the sampled species of Jaltomata. Physalis appears to be polyphyletic since P. alkekengi of the Old World branches off prior to a clade including Chamaesaracha, Margaranthus, and the two New World Physalis species sampled.
+S2x4x96c12c57c00 G. A. Verboom;H. P. Linder;N. P. Barker 1994 Haustorial synergids: An important character in the systematics of Danthonioid grasses (Arundinoideae: Poaceae) American Journal of Botany 81 1601-1610 The mature ovule and megagametophyte of 42 grass species (23 genera), mostly from the taxonomically troublesome subfamily Arundinoideae, was investigated. Haustorial synergids, first described from the Arundineae, are reported for a further 26 species (ten genera) of danthonioid grasses, a group for which they appear to be synapomorphic. The evolution of this character and a suite of associated integument and nucellar characters is discussed. The danthonioid clade, as defined by haustorial synergids, includes Cortaderia (often treated as nondanthonioid) but excludes a number of taxa currently contained in the Arundineae. The full extent of the group remains undetermined, however, since several of its potential members remain unstudied. The data provide some indication of phylogenetic structure within the clade. At least two of the species of Merxmuellera studied appear to occupy a basal position, while three do not, suggesting that this genus may be paraphyletic, or even polyphyletic. Also, the reduction or absence of synergid haustoria in Prionanthium, Pentaschistis, and Pentameris is considered to be secondarily derived and weakly corroborates the monophyly of this generic cluster as indicated by spikelet morphology.
+S2x4x96c13c48c42 A. D. Wolfe;W. J. Elisens 1994 Nuclear ribosomal DNA restriction-site variation in Penstemon section Peltanthera (Scrophulariaceae): An evaluation of diploid hybrid speciation and evidence for introgression American Journal of Botany 81 1627-1635 Penstemon spectabilis is a putative stabilized diploid hybrid of P. centranthifolius and P. grinnellii. It is morphologically intermediate, and all three species have different pollinators. Penstemon centranthifolius and P. spectabilis have been proposed as parents of P. clevelandii, which is purportedly isolated by ecological factors. Although hybridization between the proposed parental species has been reported, hybrid swarm formation has not been observed and introgression is purported to be minimal. We tested hypotheses of diploid hybrid speciation and introgression among these species based on rDNA restriction-site and length variation for 56 populations within and outside of the hybrid complex. Unambiguous molecular markers clearly differentiated P. centranthifolius, P. grinnellii, and P. spectabilis, whereas P. spectabilis and P. clevelandii had the same rDNA type. The P. centranthifolius rDNA type was found in some populations of P. spectabilis and P. clevelandii, but there was no evidence of the additive profile documented for other recent diploid hybrid species. In contrast, the rDNA profile of P. x parishii had a completely additive profile of its proposed parental species P. centranthifolius and P. spectabilis. Ribosomal DNA markers for P. gnnnellii were restricted to populations within the species and were not found in any population of P. spectabilis. Our data did not support hybrid-speciation hypotheses, but were in accord with allozyme data that provided evidence for introgression between P. centranthifolius and P. grinnellii, P. spectabilis and P. clevelandii. These results were used to propose criteria to differentiate ancient diploid hybrid speciation from patterns of introgression.
+S1x29x96c17c45c48 M. H. G. Gustafsson;K. Bremer 1995 Morphology and phylogenetic interrelationships of the Asteraceae, Calyceraceae, Campanulaceae, Goodeniaceae, and related families (Asterales) American Journal of Botany 82 2 250-265 http://www.jstor.org/stable/2445532 In search for the sister group of the Asteraceae, morphological evidence was assembled for investigating the relationship between the Asteraceae and those families most frequently considered to be their closest relatives, in particular the Calyceraceae, Campanulaceae (along with the frequently included Lobeliaceae, Cyphiaceae, Cyphocarpaceae, and Nemacladaceae), and Goodeniaceae (and the sometimes included Brunoniaceae). Several other families that have been associated with this group of families, the Asterales-Campanulales-complex, were also considered: Pentaphragmataceae, Sphenocleaceae, Stylidiaceae, Donatiaceae, Menyanthaceae, and Argophyllaceae. In order to delineate the complex more precisely, another eight putatively related families were also included in the analysis. Cladistic parsimony analysis of 46 morphological and chemical characters for the 23 families was undertaken. Stability of the branches was estimated by the number of extra steps necessary to lose the group, as well as by the number of reweighted extra steps (using rescaled consistency indices) necessary to lose the group (a new approach). The results indicate that there is a monophyletic group of 14 families comprising those of the Asterales-Campanulales-complex as well as Pentaphragmataceae, Sphenocleaceae, Stylidiaceae, Donatiaceae, and Menyanthaceae; this group is recognized as the order Asterales. Within the order, the Asteraceae, Calyceraceae, Brunoniaceae, and Goodeniaceae form one comparatively well-supported clade and the five families of the Campanulaceae sensu lato form another well-supported clade.
+S2x3x96c13c29c27 R. T. Pennington 1995 Cladistic analysis of chloroplast DNA restriction site characters in Andira (Leguminosae: Dalbergieae) American Journal of Botany 82 4 526-534 http://www.jstor.org/stable/2445701 Chloroplast DNA (cpDNA) restriction site variation was examined in 29 accessions, representing 29 populations of 17 species of Andira (Leguminosae, Papilionoideae, tribe Dalbergieae). This sample spans the morphological and geographical diversity of this poorly known, woody genus of approximately 30 species, which occurs throughout the neotropics, with one species in Africa. Cladistic analysis of 38 restriction site characters generated 182 equally most-parsimonious trees. The strict consensus tree preserved four well-supported clades, which are incongruent with previous infrageneric classifications of Andira. None of these clades has been recognized by previous workers, and three are not marked by any major morphological innovations. There were few restriction site differences between closely related species, but two (possibly three) species showed intraspecific cpDNA polymorphism, which probably reflects processes of hybridization with subsequent introgression, or lineage sorting.
+S2x3x96c13c46c17 M. Pillay;K. W. Hilu 1995 Chloroplast-DNA restriction site analysis in the genus Bromus (Poaceae) American Journal of Botany 82 2 239-249 http://www.jstor.org/stable/2445531 Chloroplast DNA (cpDNA) restriction site variation was examined in 32 species, representing five subgenera, of Bromus (Poaceae). Thirty-seven phylogenetically informative restriction sites were detected. Cladistic analysis of the restriction site data produced a single most-parsimonious tree of 50 steps. The cladogram indicated two major clades within the genus. One clade included B. trinii of subgenus Neobromus and species of subgenus Ceratochloa. The other was composed of subgenera Festucaria, Stenobromus, and Bromus. Within the second clade, species of subgenus Festucaria appeared in three lineages. The second clade also contained an assemblage of species belonging to subgenera Stenobromus and Bromus in a separate lineage. There was very little resolution of relationships in this assemblage since several species appeared individually in separate lineages. The cpDNA phylogenetic hypothesis did not separate species of subgenera Stenobromus and Bromus into well-defined clades as circumscribed by morphology and cytogenetics. The cpDNA tree is in agreement with the phylogenetic scheme based on traditional data in that: 1) subgenera Neobromus and Ceratochloa were the first to diverge, while Bromus and Stenobromus diverged later; 2) within the genus Bromus species with small chromosomes are ancestral; and 3) subgenera Bromus and Stenobromus probably originated from similar ancestors as Festucaria. The tree based on cpDNA data does not support that: 1) subgenera Neobromus and Ceratochloa did not have a common origin; 2) subgenus Festucaria is monophyletic; and 3) subgenera Stenobromus and Bromus are distinct entities. The mean nucleotide sequence divergence values between pairs of subgenera ranged from p = 0.0 to 0.9. These values suggest that cpDNA evolution in Bromus is slow.
+S447 D. J. Bogler;B. B. Simpson 1996 Phylogeny of Agavaceae based on ITS rDNA sequence variation American Journal of Botany 83 9 1225-1235 http://www.jstor.org/stable/2446206 Agavaceae; Convallariaceae; Dracaenaceae; internal transcribed spacer; molecular systematics; Nolinaceae Several systems of classification have been proposed for the family Agavaceae. A distinctive bimodal karyotype and similarities of fruits and seeds strongly support close relationships among Yucca, Hesperaloe, Beschorneria, Furcraea, Agave, Manfreda, Polianthes, Prochnyanthes, and perhaps Hosta. However, Dasylirion, Beaucarnea, Nolina, Calibanus, Dracaena, and Sansevieria differ in so many cytological and morphological features that many have concluded they should be excluded from Agavaceae and separated into two families, Nolinaceae and Dracaenaceae Chloroplast DNA restriction site data support these separations and indicate that Nolinaceae and Dracaenaceae are very close to Convallariaceae (Maianthemum, Convallaria, Aspidistra, Liriope, etc.) In this paper we report the results of an ITS rDNA sequencing study of 40 taxa in Agavaceae sensu lato and related groups in the order Asparagales. Sequence alignments were optimized using the Consistency Index, Retention Index, and Rescaled Consistency Index to find the alignment that exhibited the least amount of homoplasy. The results of our study are congruent with the conclusions drawn from cytological, immunological, cpDNA, and rbcL studies, which support a narrow interpretation of Agavaceae and a close relationship among Convallariaceae, Dracaenaceae, and Nolinaceae. In addition, the ITS sequence data provide evidence for some interesting relationships within these families.
+S753 M. Lavin;E. Eshbaugh;J. M. Hu;S. Mathews;R. A. Sharrock 1998 Monophyletic subgroups of the tribe Millettieae (Leguminosae) as revealed by phytochrome nucleotide sequence data American Journal of Botany 85 3 412-433 http://www.amjbot.org/cgi/content/abstract/85/3/412 Leguminosae; phylogeny; phytochrome genes Phylogenetic analysis of phytochrome (PHY) genes reveals the identity and relationships of four PHY loci among papilionoid Leguminosae. A phylogenetic analysis of loci combined according to species suggests that most of the tribe Millettieae belongs to one of two monophyletic clades: the Derris-Lonchocarpus or the Tephrosia clade. Together these two form a monophyletic group that is sister to a lineage represented by Millettia grandis of Millettia sect. Compresso-gemmatae. Collectively, this large monophyletic group is referred to as the Millettieae-core groups, which based on our sampling, includes species of Millettieae that do not accumulate the nonprotein amino acid canavanine and that mostly have pseudoracemose or pseudopaniculate inflorescences. This new phylogenetic framework assists in targeting additional taxa for future sampling. For example, the American Derris (Deguelia), which accumulate canavanine, might not be members of the Millettieae core group. Afgekia is also predicted not to be a member because it accumulates canavanine and has an inflorescence of terminal racemes. PHY gene analysis specifically reveals that certain genera traditionally classified in Millettieae are actually distantly related to the Millettieae core groups, such as Austrosteenisia, Callerya, Craibia, Cyclolobium, Fordia, Platycyamus, Poecilanthe, and Wisteria.
+S506 J. E. Rodman;P. S. Soltis;D. E. Soltis;K. J. Sytsma;K. G. Karol 1998 Parallel evolution of glucosinolate biosynthesis inferred from congruent nuclear and plastid gene phylogenies American Journal of Botany 85 7 997-1006 http://www.amjbot.org/cgi/content/abstract/85/7/997 Capparales s.l.; DNA sequencing; glucosinolates; phylogeny; rDNA (18S) The phytochemical system of mustard-oil glucosides (glucosinolates) accompanied by the hydrolytic enzyme myrosinase (beta-thioglucosidase), the latter usually compartmented in special myrosin cells, characterizes plants in 16 families of angiosperms. Traditional classifications place these taxa in many separate orders, and thus imply multiple convergences in the origin of this chemical defense system. DNA sequencing of the chloroplast rbcL gene for representatives of all 16 families and several putative relatives, with phylogenetic analyses by parsimony and maximum likelihood methods, demonstrated instead a single major clade of mustard-oil plants and one phylogenetic outlier. In a further independent test, DNA sequencing of the nuclear 18S ribosomal RNA gene for all these exemplars has yielded the same result, a major mustard-oil clade of 15 families (Akaniaceae, Bataceae, Brassicaceae, Bretschneideraceae, Capparaceae, Caricaceae, Gyrostemonaceae, Koeberliniaceae, Limnanthaceae, Moringaceae, Pentadiplandraceae, Resedaceae, Salvadoraceae, Tovariaceae, and Tropaeolaceae) and one outlier, the genus Drypetes, traditionally placed in Euphorbiaceae. Concatenating the two gene sequences (for a total of 3254 nucleotides) in a data set for 33 taxa, we obtain robust support for this finding of parallel origins of glucosinolate biosynthesis. From likely cyanogenic ancestors, the mustard oil bomb was invented twice.
+S227 D. A. Steane;R. W. Scotland;D. J. Mabberly;R. G. Olmstead 1998 Molecular systematics of Clerodendrum (Lamiaceae): ITS sequencesces and total evidence American Journal of Botany 86 1 98–107 http://www.amjbot.org/cgi/content/abstract/86/1/98 chloroplast DNA; Clerodendrum; Cyclonema; internal transcribed spacer; Lamiaceae Thirty-three species of Clerodendrum s.l. and five outgroup genera were included in a sequence analysis of internal transcribed spacers of the nuclear ribosomal DNA. The results of the cladistic analysis were compared to and combined with cpDNA restriction site data from a previous study. All molecular data identified four major clades within Clerodendrum s.l. and showed the genus to be polyphyletic. Clerodendrum s.s., minus Konocalyx and Cyclonema, is monophyletic and the genus should be restricted to this group. Cyclonema and Konocalyx form a clade distinct from Clerodendrum s.s. which has been recognized as Rotheca Raf. (Steane and Mabberley, 1998).
+S388 W. S. Alverson;B. A. Whitlock;R. Nyffeler;C. Bayer;D. A. Baum 1999 Phylogeny of the core Malvales: evidence from ndhF sequence data American Journal of Botany 86 10 1474-1486 http://www.amjbot.org/cgi/content/abstract/86/10/1474 angiosperm phylogeny; Bombacaceae; Malvaceae; Malvales; ndhF; phylogenetic nomenclature; stamen evolution; Sterculiaceae; Tiliaceae William S. Alverson, Barbara A. Whitlock, Reto Nyffeler, Clemens Bayer, and David A. Baum. In press. Phylogeny of the core Malvales: evidence from ndhF data. American Journal of Botany (12/21/98 version here). The monophyly of the group comprising the core malvalean families, Bombacaceae, Malvaceae, Sterculiaceae, and Tiliaceae, was recently confirmed by molecular studies, but the internal structure of this clade is poorly understood. In this study, we examined sequences of the chloroplast ndhF gene (aligned length 2226 bp) from 70 exemplars representing 35 of the 40 putative tribes or subfamilies of core Malvales. The monophyly of one traditional family, the Malvaceae, was strongly supported in the trees resulting from these data, but the other three families, as traditionally circumscribed, are nonmonophyletic. In addition, the following relationships were well supported: (1) a clade (/Malvatheca) consisting of traditional Malvaceae and Bombacaceae (except some members of tribe Durioneae), plus Fremontodendron and Chiranthodendron, which are usually treated as Sterculiaceae; (2) a clade (/Malvadendrina) supported by a unique 21-bp (base pair) deletion and consisting of /Malvatheca, plus five additional subclades, including representatives of Sterculiaceae and Tiliaceae, plus Durio and Neesia (both Durionieae); 3) a clade (/Byttneriina) with genera traditionally assigned to several tribes of Tiliaceae, plus exemplars of tribes Byttnerieae, Hermannieae, and Lasiopetaleae of Sterculiaceae. The most striking departures from traditional classifications are the following: Durio and relatives appear to be more closely related to Helicteres and Reevesia (Sterculiaceae) than to Bombacaceae; several genera traditionally considered as Bombacaceae (Camptostemon, Matisia, Phragmotheca, and Quararibea) or Sterculiaceae (Chiranthodendron and Fremontodendron) appear as sister lineages to the traditional Malvaceae; tribe Helictereae (Sterculiaceae) is polyphyletic; and Sterculiaceae and Tiliaceae, as traditionally circumscribed, represent polyphyletic groups that cannot sensibly be maintained with their traditional limits for purposes of classification. We discuss morphological characters supporting the clades indicated by molecular data. These findings indicate extensive homoplasy of many morphological characters previously used to delineate taxonomic groups in core Malvales. The topologies here also suggest that /Malvatheca (the malvoid and bombacoid clades) do not have as a synapormophy monothecate anthers, as has been previously supposed but, instead, may be united by the dithecate, transversely septate (polysporangiate) anthers in basal members of both clades, from which monothecate anthers may have been derived at least twice. Key words: angiosperm phylogeny; phylogenetic nomenclature; rankless taxa; stamen evolution; Bombacaceae; Malvaceae; Malvales; Sterculiaceae; Tiliaceae. Baum, D. A., W. S. Alverson, and R. N. Nyffeler. 1998. A durian by any other name: taxonomy and nomenclature of the core Malvales. Harvard Papers in Botany 3: 317-332. The phylogeny of the core Malvales (including plants traditionally placed in the families Bombacaceae, Malvaceae, Sterculiaceae, and Tiliaceae) has been greatly clarified by recent molecular systematic studies. In this paper we explore the options available for incorporating this phylogenetic information into a classification for the group. We first discuss a range of options within the traditional system of nomenclature with the added constraint of only recognizing monophyletic groups. These schemes differ in the ranks applied and in the degree to which currently used names continue to apply to familiar groups. We then propose an alternative set of unranked names within the framework of phylogenetic nomenclature. This phylogenetic classification shares the same names as a recently published traditional classification, except that the phylogenetic names are distinguished by a clademark. We note some advantages of phylogenetic nomenclature: monophyly of taxa can be ass med; there is no limit to the number of nested clades that can be named; classifications are stable so long as phylogenetic knowledge does not change, and; it will be easy to establish useful data base resources. The traditional and phylogenetic classifications of the core Malvales are almost identical given current knowledge of phylogeny. Therefore, this group provides a controlled experiment with which to see how the two nomenclatural codes perform in practice as phylogenetic knowledge increases.
+S759 J. M. Hu;M. Lavin;M. F. Wojciechowski;M. J. Sanderson 2000 Phylogenetic systematics of the tribe Millettieae (Leguminosae) based on chloroplast trnK/matK sequences and its implications for evolutionary patterns in Papilionoideae American Journal of Botany 87 3 418-430 http://www.amjbot.org/cgi/content/abstract/87/3/418 Fabaceae; matK; Millettieae; Papilionoideae; phylogeny; trnK Phylogenetic relationships in the tribe Millettieae and allies in the subfamily Papilionoideae (Leguminosae) were recon-structed from chloroplast trnK/matK sequences. Sixty-two accessions representing 57 traditionally recognized genera of Papilionoideae were sampled, including 27 samples from Millettieae. Phylogenies were constructed using maximum parsi-mony and are well resolved and supported by high bootstrap values. A well-supported "core Millettieae" clade is recognized, comprising the four large genera Millettia, Lonchocarpus, Derris, and Tephrosia. Several other small genera of Millettieae are not in the core Millettieae clade. Platycyamus is grouped with Phaseoleae (in part). Ostryocarpus, Austrosteenisia, and Dalbergiella are neither in the core Millettieae or Phaseoleae clade. These taxa, along with core Millettieae and Phaseoleae, form a monophyletic sister group to Indigofereae. Cyclolobium and Poecilanthe are close to Brongniartieae. Callerya and Wisteria belong to a large clade that includes all the legumes that lack the inverted repeat in their chloroplast genome, which confirms previous rbcL and phytochrome gene family phylogenies. The evolutionary history of four characters was examined in Millettieae and allies: the presence of canavanine, inflorescence types, the dehiscence of pods, and the presence of winged pods. trnK/matK sequence analysis suggests that the presence of a pseudoraceme or pseudopanicle and the accumulation of nonprotein amino acids are phylogenetically informative for Millettieae and allies with only a few exceptions.
+S1974 G. D. Weiblen 2000 Phylogenetic relationships of functionally dioecious Ficus (Moraceae) based on ribosomal DNA sequences and morphology American Journal of Botany 87 9 1342-1357 http://www.amjbot.org/cgi/content/abstract/87/9/1342 breeding system evolution; coevolution; dioecy; Ficus; Moraceae; phylogeny; pollination Figs (Ficus, Moraceae) are either monoecious or gynodioecious depending on the arrangement of unisexual florets within the specialized inflorescence or syconium. The gynodioecious species are functionally dioecious due to the impact of pollinating fig wasps (Hymenoptera: Agaonidae) on the maturation of fig seeds. The evolutionary relationships of functionally dioecious figs (Ficus subg. Ficus) were examined through phylogenetic analyses based on the internal transcribed spacer (ITS) region of nuclear ribosomal DNA and morphology. Forty-six species representing each monoecious subgenus and each section of functionally dioecious subg. Ficus were included in parsimony analyses based on 180 molecular characters and 61 morphological characters that were potentially informative. Separate and combined analyses of molecular and morphological data sets suggested that functionally dioecious figs are not monophyletic and that monoecious subg. Sycomorus is derived within a dioecious clade. The combined analysis indicated one or two origins of functional dioecy in the genus and at least two reversals to monoecy within a functionally dioecious lineage. The exclusion of breeding system and related characters from the analysis also indicated two shifts from monoecy to functional dioecy and two reversals. The associations of pollinating fig wasps were congruent with host fig phylogeny and further supported a revised classification of Ficus.
+S690 L. M. Giussani;J. H. Cota-Sánchez;F. O. Zuloaga;E. A. Kellogg 2001 A molecular phylogeny of the grass subfamily Panicoideae (Poaceae) shows multiple origins of C4 photosynthesis American Journal of Botany 88 11 1993–2012 http://www.amjbot.org/cgi/content/abstract/88/11/1993 C4 photosynthesis; ndhF; Panicoideae; Poaceae DNA sequence data from the chloroplast gene ndhF were analyzed to estimate the phylogeny of the subfamily Panicoideae, with emphasis on the tribe Paniceae. Our data suggest that the subfamily is divided into three strongly supported clades, corresponding to groups with largely identical base chromosome numbers. Relationships among the three clades are unclear. In unweighted parsimony analyses, the two major clades with x = 10 (Andropogoneae and x = 10 Paniceae) are weakly supported as sister taxa. The third large clade corresponds to x = 9 Paniceae. In analyses under implied weight, the two clades of Paniceae are sisters, making the tribe monophyletic. Neither resolution is strongly supported.; Our molecular phylogen...
[truncated message content] |
|
From: <sfr...@us...> - 2010-10-07 01:00:34
|
Revision: 757
http://treebase.svn.sourceforge.net/treebase/?rev=757&view=rev
Author: sfrgpiel
Date: 2010-10-07 01:00:23 +0000 (Thu, 07 Oct 2010)
Log Message:
-----------
Perl script for deleting treeblocks that otherwise can't be deleted using the usual user interface
Modified Paths:
--------------
trunk/treebase-core/db/cleaning/2010-03-03_fixlabels/fixlabels_trans.pl
Added Paths:
-----------
trunk/treebase-core/db/cleaning/2010-10-06_delete_treeblock/
trunk/treebase-core/db/cleaning/2010-10-06_delete_treeblock/README.txt
trunk/treebase-core/db/cleaning/2010-10-06_delete_treeblock/delete_treeblock.pl
Modified: trunk/treebase-core/db/cleaning/2010-03-03_fixlabels/fixlabels_trans.pl
===================================================================
--- trunk/treebase-core/db/cleaning/2010-03-03_fixlabels/fixlabels_trans.pl 2010-10-06 17:31:22 UTC (rev 756)
+++ trunk/treebase-core/db/cleaning/2010-03-03_fixlabels/fixlabels_trans.pl 2010-10-07 01:00:23 UTC (rev 757)
@@ -15,19 +15,11 @@
# 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 $database = "";
+my $username = "treebase_app";
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:
@@ -155,7 +147,7 @@
$cstr = "DBI:Pg:dbname="."$cstr";
# uncomment this to run against the server at NESCent:
- # $cstr .= ";host=treebasedb-dev.nescent.org";
+ $cstr .= ";host=treebasedb-dev.nescent.org";
my $dbh = DBI->connect($cstr, $user, $pass, {AutoCommit => 0, PrintError => 1, RaiseError => 1});
Added: trunk/treebase-core/db/cleaning/2010-10-06_delete_treeblock/README.txt
===================================================================
--- trunk/treebase-core/db/cleaning/2010-10-06_delete_treeblock/README.txt (rev 0)
+++ trunk/treebase-core/db/cleaning/2010-10-06_delete_treeblock/README.txt 2010-10-07 01:00:23 UTC (rev 757)
@@ -0,0 +1,10 @@
+A perl script from Bill to delete treeblocks and the trees inside them.
+
+Before running, adjust database name and password in delete_treeblock.pl.
+
+The parameter to provide is the treeblock_id, e.g.:
+
+perl delete_treeblock.pl 12016
+
+... where 12016 is the treeblock_id. You are then asked to confirm the number of trees in the tree block.
+
Added: trunk/treebase-core/db/cleaning/2010-10-06_delete_treeblock/delete_treeblock.pl
===================================================================
--- trunk/treebase-core/db/cleaning/2010-10-06_delete_treeblock/delete_treeblock.pl (rev 0)
+++ trunk/treebase-core/db/cleaning/2010-10-06_delete_treeblock/delete_treeblock.pl 2010-10-07 01:00:23 UTC (rev 757)
@@ -0,0 +1,163 @@
+#!/usr/bin/perl
+
+use strict;
+use DBI;
+
+# This script is intended to delete a treeblock, cascading to all dependent tables
+
+my $database = "";
+my $username = "treebase_app";
+my $password = "";
+
+my $treeblock_id = shift;
+
+# Number of nodes to delete at a time
+my $sets = 1000;
+
+# check that the treeblock_id looks like a number:
+if ( $treeblock_id =~ m/^\d+$/ ) {
+
+ my $dbh = &ConnectToPg($database, $username, $password);
+
+ my $count = "SELECT COUNT(*) FROM treeblock WHERE treeblock_id = $treeblock_id ";
+ my $totRec = $dbh->selectrow_array ($count);
+
+ # check to see that the treeblock in question exists
+ if ($totRec == 1) {
+
+ $count = "SELECT COUNT(*) FROM phylotree WHERE treeblock_id = $treeblock_id ";
+ $totRec = $dbh->selectrow_array ($count);
+
+ my $a;
+ do {
+ print "There are $totRec trees in treeblock $treeblock_id, does that sound right? [yn]\n";
+ $a = <STDIN>;
+ chop $a;
+ } until (($a eq "y") || ($a eq "n"));
+
+ # offer a chance to cancel
+ if ($a eq "y") {
+
+ # run the whole thing in a single transaction
+ eval {
+
+ # phylotreenode and phylotree have mutual FK constraints
+ # first NULL the phylotree.rootnode_id to remove constraint
+ $dbh->do( "UPDATE phylotree SET rootnode_id = DEFAULT WHERE treeblock_id = ?", undef, $treeblock_id );
+
+ # since we've been getting a time-out when deleting phylonodes, lets delete them in
+ # batches of $sets
+
+ my $statement = "SELECT COUNT(*) FROM phylotreenode WHERE phylotree_id IN (
+ SELECT phylotree_id FROM phylotree WHERE treeblock_id = $treeblock_id ) ";
+ $totRec = $dbh->selectrow_array ($statement);
+
+ print "There are $totRec phylotreenode records that we need to delete.\n";
+ print "Lets attempt to do this ". int($totRec/$sets) ." times in sets of $sets records\n";
+
+ $statement = "DELETE FROM phylotreenode WHERE phylotree_id IN (
+ SELECT phylotree_id FROM phylotree WHERE treeblock_id = ? LIMIT $sets
+ )";
+
+ my $delete_phylonodes = $dbh->prepare("$statement");
+
+ foreach my $cnt (1 .. int($totRec/$sets) ) {
+ $delete_phylonodes->execute( $treeblock_id );
+ print "Deletion $cnt for batch of $sets phylotreenode records \n";
+ }
+ # one more for good measure
+ $delete_phylonodes->execute( $treeblock_id );
+
+ # taxonlabels are referenced by nodes, matrix rows, taxon blocks *and* submissions
+ # let's remove the connection to submissions. This assumes that there are no matrices
+ # or other treeblocks that will continue to reference these taxonlabels
+ $statement = "DELETE FROM sub_taxonlabel WHERE taxonlabel_id IN (
+ SELECT DISTINCT taxonlabel_id
+ FROM taxonlabelset_taxonlabel JOIN taxonlabelset USING (taxonlabelset_id)
+ JOIN treeblock USING (taxonlabelset_id)
+ WHERE treeblock_id = ?
+ )";
+ $dbh->do( $statement, undef, $treeblock_id );
+
+ # remove the many-to-many join between taxonlabelset and taxonlabel
+ $statement = "DELETE FROM taxonlabelset_taxonlabel WHERE taxonlabelset_id IN (
+ SELECT taxonlabelset_id FROM treeblock WHERE treeblock_id = ?
+ )";
+ $dbh->do( $statement, undef, $treeblock_id );
+
+ # delete taxonlabel records after having deleted the taxonlabelset_taxonlabel records
+ $statement = "DELETE FROM taxonlabel WHERE taxonlabel_id IN (
+ SELECT tl.taxonlabel_id
+ FROM taxonlabel tl LEFT JOIN taxonlabelset_taxonlabel USING (taxonlabel_id)
+ WHERE study_id = (SELECT DISTINCT study_id FROM phylotree WHERE treeblock_id = ?)
+ AND taxonlabelset_id IS NULL
+ AND NOT EXISTS (SELECT 1 FROM matrixrow mr WHERE mr.taxonlabel_id = tl.taxonlabel_id)
+ AND NOT EXISTS (SELECT 1 FROM phylotreenode ptn WHERE ptn.taxonlabel_id = tl.taxonlabel_id)
+ )";
+ $dbh->do( $statement, undef, $treeblock_id );
+
+ # Before we delete the taxonlabelset, we need to remove the FK constraint
+ # with treeblock
+ $statement = "UPDATE treeblock SET taxonlabelset_id = DEFAULT WHERE treeblock_id = ?";
+ $dbh->do( $statement, undef, $treeblock_id );
+
+ # Now delete the taxonlabelset
+ $statement = "DELETE FROM taxonlabelset WHERE taxonlabelset_id IN (
+ SELECT DISTINCT taxonlabelset_id
+ FROM taxonlabelset tls LEFT JOIN taxonlabelset_taxonlabel tltl USING (taxonlabelset_id)
+ WHERE study_id = (
+ SELECT DISTINCT study_id FROM phylotree
+ WHERE treeblock_id = ?
+ )
+ AND tltl.taxonlabel_id IS NULL
+ AND NOT EXISTS (SELECT 1 FROM treeblock tb WHERE tb.taxonlabelset_id = tls.taxonlabelset_id AND tb.treeblock_id <> ?)
+ AND NOT EXISTS (SELECT 1 FROM matrix mx WHERE mx.taxonlabelset_id = tls.taxonlabelset_id )
+ )";
+ $dbh->do( $statement, undef, $treeblock_id, $treeblock_id );
+
+ $dbh->do( "DELETE FROM phylotree WHERE treeblock_id = ?", undef, $treeblock_id );
+ $dbh->do( "DELETE FROM sub_treeblock WHERE treeblock_id = ?", undef, $treeblock_id );
+ $dbh->do( "DELETE FROM treeblock WHERE treeblock_id = ?", undef, $treeblock_id );
+
+ # If no errors so far, let's commit
+ $dbh->commit();
+ print "Congratulations: treeblock_id $treeblock_id has been deleted and the deletes committed\n";
+ };
+
+ if ($@) {
+ warn "Failed to delete treeblock_id $treeblock_id: $@\n";
+ $dbh->rollback();
+ print "rollback!!\n";
+ }
+
+ } else {
+ print "Deletion cancelled on user request\n";
+ }
+ } else {
+ print "$totRec record(s) found, there should be only one.\n";
+ }
+
+ my $rc = $dbh->disconnect;
+} else {
+ print "The treeblock_id ($treeblock_id) does not look like a number.\n";
+}
+
+
+# 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);
+}
+
+
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
|
From: <sfr...@us...> - 2011-01-19 19:59:08
|
Revision: 771
http://treebase.svn.sourceforge.net/treebase/?rev=771&view=rev
Author: sfrgpiel
Date: 2011-01-19 19:59:02 +0000 (Wed, 19 Jan 2011)
Log Message:
-----------
SQL for fixing the records where study.releasedate IS NULL.
Added Paths:
-----------
trunk/treebase-core/db/cleaning/2011-01-19_releasedate_fix/
trunk/treebase-core/db/cleaning/2011-01-19_releasedate_fix/README.txt
trunk/treebase-core/db/cleaning/2011-01-19_releasedate_fix/fix_releasedate.sql
Added: trunk/treebase-core/db/cleaning/2011-01-19_releasedate_fix/README.txt
===================================================================
--- trunk/treebase-core/db/cleaning/2011-01-19_releasedate_fix/README.txt (rev 0)
+++ trunk/treebase-core/db/cleaning/2011-01-19_releasedate_fix/README.txt 2011-01-19 19:59:02 UTC (rev 771)
@@ -0,0 +1,16 @@
+To apply, execute the queries in fix_releasedate.sql
+
+--------
+
+Studies that lack a study.releasedate value fail to return a <pubDate> in the PhyloWS RSS,
+and this causes Safari's RSS reader to subsitute this value with a last-checked timestamp.
+The result is that these records appear to be new additions to the database, when in fact
+they are quite old. The NULL values in study.releasedate is an artefact of the data migration
+from TB1. Fix the releasedate and lastmodifieddate for studies that were migrated from TB1
+by using the submission.createdate.
+
+To see how the effect, use Safari to compare the "fixed" data in treebase-dev:
+http://treebase-dev.nescent.org/treebase-web/phylows/study/find?query=dcterms.contributor=Huelsenbeck&format=rss1
+
+with the results from the same request on production here:
+http://purl.org/phylo/treebase/phylows/study/find?query=dcterms.contributor=Huelsenbeck&format=rss1
Added: trunk/treebase-core/db/cleaning/2011-01-19_releasedate_fix/fix_releasedate.sql
===================================================================
--- trunk/treebase-core/db/cleaning/2011-01-19_releasedate_fix/fix_releasedate.sql (rev 0)
+++ trunk/treebase-core/db/cleaning/2011-01-19_releasedate_fix/fix_releasedate.sql 2011-01-19 19:59:02 UTC (rev 771)
@@ -0,0 +1,38 @@
+-- Studies that lack a study.releasedate value fail to return
+-- a <pubDate> in the PhyloWS RSS, and this causes Safari's
+-- RSS reader to subsitute this value with a last-checked timestamp.
+-- The result is that these records appear to be new additions
+-- to the database, when in fact they are quite old. The NULL
+-- values in study.releasedate is an artefact of the data migration
+-- from TB1. Fix the releasedate and lastmodifieddate for studies
+-- that were migrated from TB1 by using the submission.createdate.
+
+-- To see how the effect, use Safari to compare the "fixed" data in treebase-dev:
+-- http://treebase-dev.nescent.org/treebase-web/phylows/study/find?query=dcterms.contributor=Huelsenbeck&format=rss1
+-- with the results from the same request on production here:
+-- http://purl.org/phylo/treebase/phylows/study/find?query=dcterms.contributor=Huelsenbeck&format=rss1
+
+-- Test to count the number of records with a NULL value in study.releasedate
+SELECT count(*) FROM study st LEFT JOIN submission sb USING (study_id)
+WHERE st.studystatus_id = 3
+AND st.releasedate IS NULL;
+
+-- should result in a largish number like "2282"
+
+begin work;
+
+UPDATE study SET releasedate = sb.createdate, lastmodifieddate = sb.createdate
+FROM submission sb
+WHERE study.studystatus_id = 3
+AND study.releasedate IS NULL
+AND study.study_id = sb.study_id
+
+-- now let's check this again
+
+SELECT count(*) FROM study st LEFT JOIN submission sb USING (study_id)
+WHERE st.studystatus_id = 3
+AND st.releasedate IS NULL;
+
+-- result should be zero
+-- if so:
+commit;
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
|
From: <hs...@us...> - 2011-07-18 21:56:08
|
Revision: 929
http://treebase.svn.sourceforge.net/treebase/?rev=929&view=rev
Author: hshyket
Date: 2011-07-18 21:56:02 +0000 (Mon, 18 Jul 2011)
Log Message:
-----------
Aligning study id with submission id in database
Added Paths:
-----------
trunk/treebase-core/db/cleaning/2011-07-18_align_studyid_submissionid/
trunk/treebase-core/db/cleaning/2011-07-18_align_studyid_submissionid/align_studyid_submissionid.sql
Added: trunk/treebase-core/db/cleaning/2011-07-18_align_studyid_submissionid/align_studyid_submissionid.sql
===================================================================
--- trunk/treebase-core/db/cleaning/2011-07-18_align_studyid_submissionid/align_studyid_submissionid.sql (rev 0)
+++ trunk/treebase-core/db/cleaning/2011-07-18_align_studyid_submissionid/align_studyid_submissionid.sql 2011-07-18 21:56:02 UTC (rev 929)
@@ -0,0 +1,10 @@
+SELECT
+pg_catalog.nextval('study_id_sequence'), pg_catalog.nextval('submission_id_sequence'),
+ CASE
+ WHEN ( pg_catalog.currval('study_id_sequence') - pg_catalog.currval('submission_id_sequence') ) > 0
+ THEN pg_catalog.setval('submission_id_sequence', ( pg_catalog.currval('study_id_sequence') ) )
+ WHEN ( pg_catalog.currval('study_id_sequence') - pg_catalog.currval('submission_id_sequence') ) < 0
+ THEN pg_catalog.setval('study_id_sequence', ( pg_catalog.currval('submission_id_sequence') ) )
+ WHEN ( pg_catalog.currval('study_id_sequence') - pg_catalog.currval('submission_id_sequence') ) = 0
+ THEN 0
+ END;
\ 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: <sfr...@us...> - 2011-12-06 19:24:14
|
Revision: 994
http://treebase.svn.sourceforge.net/treebase/?rev=994&view=rev
Author: sfrgpiel
Date: 2011-12-06 19:24:05 +0000 (Tue, 06 Dec 2011)
Log Message:
-----------
PERL script to clean the database of orphaned taxonlabelsets.
Added Paths:
-----------
trunk/treebase-core/db/cleaning/2011-12-06_orphaned_taxonlabelsets/
trunk/treebase-core/db/cleaning/2011-12-06_orphaned_taxonlabelsets/README
trunk/treebase-core/db/cleaning/2011-12-06_orphaned_taxonlabelsets/affected_studies.sql
trunk/treebase-core/db/cleaning/2011-12-06_orphaned_taxonlabelsets/delete_orphaned_taxonlabelsets.pl
trunk/treebase-core/db/cleaning/2011-12-06_orphaned_taxonlabelsets/fixing_orphaned_taxonlabelsets.sql
trunk/treebase-core/db/cleaning/2011-12-06_orphaned_taxonlabelsets/output.txt
Added: trunk/treebase-core/db/cleaning/2011-12-06_orphaned_taxonlabelsets/README
===================================================================
--- trunk/treebase-core/db/cleaning/2011-12-06_orphaned_taxonlabelsets/README (rev 0)
+++ trunk/treebase-core/db/cleaning/2011-12-06_orphaned_taxonlabelsets/README 2011-12-06 19:24:05 UTC (rev 994)
@@ -0,0 +1,18 @@
+The script delete_orphaned_taxonlabelsets.pl is intended to delete orphaned taxonlabelsets.
+
+As of Dec 6 2012, orphans were being generated as a result of a bug. Our delete treeblock
+function inadvertently failed to include a cleanup step to delete related, but now orphaned,
+taxonlabelset records. The result is studies that (sometimes) generate a "yikes" error when
+you try to get a list of taxa.
+
+For example, this page generates a Yikes:
+
+http://treebase.org/treebase-web/search/study/taxa.html?id=12037
+
+While this page (after fixing the data with delete_orphaned_taxonlabelsets.pl) does not:
+
+http://treebase-dev.nescent.org/treebase-web/search/study/taxa.html?id=12037
+
+To run the script, first edit lines 12-15 to point to production, then execute. It takes no
+parameters. Example output is provided with "output.txt".
+
Added: trunk/treebase-core/db/cleaning/2011-12-06_orphaned_taxonlabelsets/affected_studies.sql
===================================================================
--- trunk/treebase-core/db/cleaning/2011-12-06_orphaned_taxonlabelsets/affected_studies.sql (rev 0)
+++ trunk/treebase-core/db/cleaning/2011-12-06_orphaned_taxonlabelsets/affected_studies.sql 2011-12-06 19:24:05 UTC (rev 994)
@@ -0,0 +1,361 @@
+
+SELECT DISTINCT ON (tls.study_id) tls.taxonlabelset_id, tls.study_id, ss.description
+FROM
+taxonlabelset tls LEFT JOIN
+matrix mx ON (tls.taxonlabelset_id = mx.taxonlabelset_id) LEFT JOIN
+treeblock tb ON (tls.taxonlabelset_id = tb.taxonlabelset_id) JOIN
+study s ON (tls.study_id = s.study_id) JOIN
+studystatus ss USING (studystatus_id)
+WHERE
+mx.matrix_id IS NULL
+AND
+tb.treeblock_id IS NULL
+
+-- 345 studies are affected
+
+-- 1394 22 In Progress
+-- 4810 23 In Progress
+-- 9450 44 In Progress
+-- 11200 1019 Published
+-- 13335 1919 Published
+-- 14190 2264 In Progress
+-- 16071 10215 In Progress
+-- 16132 10317 Published
+-- 16464 10335 Published
+-- 16188 10339 In Progress
+-- 18753 10347 In Progress
+-- 16228 10356 In Progress
+-- 16280 10367 In Progress
+-- 16399 10399 Published
+-- 16402 10400 Published
+-- 16434 10413 In Progress
+-- 16456 10424 In Progress
+-- 16466 10425 In Progress
+-- 16477 10429 Published
+-- 16567 10431 In Progress
+-- 16484 10433 Published
+-- 16501 10434 In Progress
+-- 16507 10435 Published
+-- 18799 10436 Published
+-- 16514 10438 Published
+-- 16545 10449 In Progress
+-- 16554 10452 In Progress
+-- 16668 10462 In Progress
+-- 16784 10464 Published
+-- 16633 10468 In Progress
+-- 16638 10469 Published
+-- 16643 10470 In Progress
+-- 16653 10474 In Progress
+-- 16749 10485 Published
+-- 16757 10486 In Progress
+-- 16702 10489 Published
+-- 16696 10492 Published
+-- 16700 10493 Published
+-- 16731 10504 Published
+-- 16734 10507 Published
+-- 16738 10508 In Progress
+-- 16772 10511 Published
+-- 21310 10512 Published
+-- 16860 10529 In Progress
+-- 16888 10530 In Progress
+-- 16940 10536 Published
+-- 16972 10541 Published
+-- 16969 10542 Published
+-- 17064 10553 Published
+-- 17057 10569 In Progress
+-- 17076 10570 Published
+-- 17093 10583 In Progress
+-- 17289 10589 In Progress
+-- 17293 10593 Published
+-- 17206 10603 Published
+-- 17138 10604 In Progress
+-- 18665 10605 In Progress
+-- 17671 10608 Published
+-- 17174 10613 Published
+-- 17192 10623 Published
+-- 17237 10635 Published
+-- 17255 10639 In Progress
+-- 17379 10644 Published
+-- 17291 10648 Published
+-- 17338 10652 Published
+-- 22206 10653 Published
+-- 17347 10654 Published
+-- 17355 10658 Published
+-- 17468 10665 Published
+-- 18527 10673 In Progress
+-- 17453 10679 Published
+-- 17568 10680 Published
+-- 17429 10684 Published
+-- 17463 10689 Published
+-- 17471 10692 Published
+-- 17514 10698 In Progress
+-- 17528 10699 In Progress
+-- 17572 10703 Published
+-- 17584 10711 In Progress
+-- 22854 10713 Published
+-- 17605 10714 Published
+-- 18102 10724 Published
+-- 17643 10727 Published
+-- 17659 10732 Published
+-- 17669 10733 In Progress
+-- 17740 10736 Published
+-- 17752 10740 In Progress
+-- 17784 10749 Published
+-- 17821 10759 In Progress
+-- 17836 10766 Published
+-- 17850 10772 Published
+-- 17856 10774 Published
+-- 17895 10786 In Progress
+-- 17950 10795 In Progress
+-- 17992 10797 Published
+-- 17987 10803 Published
+-- 17997 10812 In Progress
+-- 18004 10814 In Progress
+-- 20216 10817 In Progress
+-- 18030 10821 Published
+-- 18044 10828 In Progress
+-- 18748 10830 Published
+-- 18138 10831 In Progress
+-- 18123 10847 Published
+-- 18216 10859 In Progress
+-- 18286 10861 In Progress
+-- 18238 10870 In Progress
+-- 18244 10876 Published
+-- 18346 10888 Published
+-- 18283 10898 Published
+-- 18366 10902 In Progress
+-- 18566 10904 In Progress
+-- 21995 10905 Published
+-- 18410 10908 Published
+-- 18478 10927 Published
+-- 18564 10938 Published
+-- 18580 10940 Published
+-- 18509 10942 Published
+-- 18591 10950 Published
+-- 18593 10951 Published
+-- 18619 10961 In Progress
+-- 18638 10963 Published
+-- 18633 10966 Published
+-- 18657 10971 In Progress
+-- 18673 10977 In Progress
+-- 18683 10983 Published
+-- 18687 10984 Published
+-- 18737 10996 Published
+-- 18726 10999 In Progress
+-- 18760 11009 Published
+-- 18822 11020 Published
+-- 22845 11021 Published
+-- 18826 11022 Published
+-- 18887 11032 Published
+-- 18983 11056 In Progress
+-- 19367 11058 Published
+-- 19007 11061 Published
+-- 19045 11078 In Progress
+-- 19055 11079 Published
+-- 19062 11080 Published
+-- 19077 11083 Published
+-- 19095 11088 Published
+-- 19107 11089 In Progress
+-- 19104 11090 Published
+-- 19429 11091 In Progress
+-- 19127 11093 Published
+-- 19126 11097 Published
+-- 19160 11111 Published
+-- 19173 11112 Published
+-- 19186 11114 Published
+-- 19191 11115 Published
+-- 19199 11116 Ready
+-- 19394 11117 In Progress
+-- 19205 11118 Published
+-- 19213 11122 Published
+-- 19260 11126 Published
+-- 19274 11128 Published
+-- 19318 11132 Published
+-- 19337 11136 In Progress
+-- 19359 11140 Published
+-- 21258 11159 In Progress
+-- 19424 11161 Published
+-- 19454 11174 Published
+-- 19462 11177 In Progress
+-- 19494 11181 In Progress
+-- 19497 11183 Published
+-- 19503 11184 Published
+-- 19513 11192 Published
+-- 19523 11197 Published
+-- 19540 11200 Published
+-- 20145 11204 Published
+-- 19682 11210 Published
+-- 19576 11211 Published
+-- 19601 11216 Published
+-- 19662 11219 In Progress
+-- 19654 11226 Published
+-- 19793 11252 In Progress
+-- 19828 11260 Published
+-- 19829 11261 In Progress
+-- 19844 11267 Published
+-- 19854 11269 Published
+-- 19883 11273 In Progress
+-- 19893 11276 In Progress
+-- 19918 11284 Published
+-- 19942 11289 Published
+-- 19962 11294 In Progress
+-- 19979 11296 In Progress
+-- 20000 11298 Published
+-- 20317 11303 Published
+-- 20140 11312 Published
+-- 20142 11313 Published
+-- 20177 11327 In Progress
+-- 20207 11331 Published
+-- 20190 11333 In Progress
+-- 20222 11336 Published
+-- 20252 11346 In Progress
+-- 20258 11348 In Progress
+-- 20284 11352 In Progress
+-- 20283 11355 Published
+-- 22141 11367 In Progress
+-- 20338 11369 In Progress
+-- 20802 11378 Published
+-- 20375 11380 In Progress
+-- 20520 11383 In Progress
+-- 20424 11391 Published
+-- 20419 11392 Published
+-- 20468 11397 In Progress
+-- 22095 11398 In Progress
+-- 20503 11404 Published
+-- 22263 11409 In Progress
+-- 20610 11415 Published
+-- 23182 11416 Published
+-- 20628 11417 In Progress
+-- 20631 11418 Published
+-- 20670 11439 Published
+-- 20719 11448 In Progress
+-- 21327 11461 In Progress
+-- 20964 11465 In Progress
+-- 21181 11468 Published
+-- 21278 11472 Published
+-- 21062 11476 Published
+-- 20901 11480 Published
+-- 21036 11487 In Progress
+-- 20941 11489 Published
+-- 20962 11499 In Progress
+-- 20976 11504 In Progress
+-- 21067 11512 Published
+-- 21052 11517 Published
+-- 21141 11524 In Progress
+-- 21513 11527 In Progress
+-- 21106 11535 Published
+-- 21112 11536 In Progress
+-- 21135 11537 In Progress
+-- 21188 11552 Published
+-- 22436 11557 Published
+-- 21216 11561 In Progress
+-- 21673 11563 Published
+-- 21343 11568 In Progress
+-- 21243 11570 Published
+-- 21246 11571 In Progress
+-- 21264 11575 Published
+-- 21273 11586 Published
+-- 21305 11593 Published
+-- 21320 11596 Published
+-- 21413 11600 In Progress
+-- 21349 11601 In Progress
+-- 21340 11607 In Progress
+-- 21345 11608 In Progress
+-- 21369 11616 Published
+-- 21380 11619 In Progress
+-- 21390 11623 Published
+-- 21387 11625 In Progress
+-- 22145 11644 Published
+-- 21461 11651 Published
+-- 21568 11652 In Progress
+-- 21520 11654 In Progress
+-- 21485 11659 In Progress
+-- 21750 11662 Published
+-- 21611 11670 In Progress
+-- 21696 11681 In Progress
+-- 21712 11683 In Progress
+-- 22040 11687 In Progress
+-- 21762 11689 Published
+-- 21774 11692 In Progress
+-- 21999 11695 Published
+-- 22004 11705 In Progress
+-- 22019 11711 In Progress
+-- 22027 11713 Published
+-- 22052 11719 In Progress
+-- 22089 11729 In Progress
+-- 22153 11747 Published
+-- 22170 11756 In Progress
+-- 22183 11759 Published
+-- 22225 11768 In Progress
+-- 22636 11779 Published
+-- 22256 11780 In Progress
+-- 22293 11798 Published
+-- 22348 11815 Published
+-- 22362 11822 Published
+-- 22390 11823 In Progress
+-- 22374 11825 In Progress
+-- 22387 11829 In Progress
+-- 22405 11832 In Progress
+-- 22450 11848 In Progress
+-- 22480 11849 Published
+-- 22706 11852 In Progress
+-- 22494 11863 In Progress
+-- 22489 11864 In Progress
+-- 22519 11865 Published
+-- 22496 11866 In Progress
+-- 22514 11868 Published
+-- 22529 11872 Published
+-- 22600 11876 Published
+-- 22551 11878 Published
+-- 22574 11891 Published
+-- 22581 11892 Published
+-- 22661 11893 In Progress
+-- 22608 11906 Published
+-- 23365 11908 Published
+-- 22632 11911 In Progress
+-- 22638 11914 In Progress
+-- 22652 11917 In Progress
+-- 22723 11918 Published
+-- 22727 11936 In Progress
+-- 22883 11943 In Progress
+-- 22872 11949 Published
+-- 22828 11950 Published
+-- 22876 11956 In Progress
+-- 23160 11959 In Progress
+-- 22895 11960 In Progress
+-- 23072 11963 In Progress
+-- 22914 11975 Published
+-- 22930 11980 In Progress
+-- 23021 11985 Published
+-- 23399 11988 Published
+-- 23539 11991 Published
+-- 22979 11995 In Progress
+-- 22996 12003 Published
+-- 23012 12006 In Progress
+-- 23061 12009 Published
+-- 23080 12029 In Progress
+-- 23095 12034 In Progress
+-- 23146 12036 Published
+-- 23102 12037 Published
+-- 23203 12043 In Progress
+-- 23158 12048 In Progress
+-- 23174 12050 In Progress
+-- 23200 12055 In Progress
+-- 23211 12057 Published
+-- 23226 12065 In Progress
+-- 23247 12072 In Progress
+-- 23274 12079 Published
+-- 23290 12087 In Progress
+-- 23292 12088 In Progress
+-- 23342 12096 In Progress
+-- 23357 12101 In Progress
+-- 23401 12120 In Progress
+-- 23408 12121 In Progress
+-- 23428 12128 In Progress
+-- 23491 12145 In Progress
+-- 23509 12149 In Progress
+-- 23606 12153 In Progress
+-- 23532 12160 Published
+-- 23590 12162 Published
+-- 23608 12167 In Progress
+
Added: trunk/treebase-core/db/cleaning/2011-12-06_orphaned_taxonlabelsets/delete_orphaned_taxonlabelsets.pl
===================================================================
--- trunk/treebase-core/db/cleaning/2011-12-06_orphaned_taxonlabelsets/delete_orphaned_taxonlabelsets.pl (rev 0)
+++ trunk/treebase-core/db/cleaning/2011-12-06_orphaned_taxonlabelsets/delete_orphaned_taxonlabelsets.pl 2011-12-06 19:24:05 UTC (rev 994)
@@ -0,0 +1,97 @@
+#!/usr/bin/perl
+
+use strict;
+use DBI;
+
+# This script is intended to delete orphaned taxonlabelsets. As of Dec 6 2012, orphans
+# were being generated as a result of a bug. When our delete-treeblock function inadvertently
+# failed to include a cleanup step to delete related, but now orphaned, taxonlabelset records.
+# The result are studies that generate a "yikes" error when you try to get a list of taxa.
+
+
+my $database = "treebasedev";
+my $username = "treebase_app";
+my $password = "";
+my $host = "treebasedb-dev.nescent.org";
+
+
+my $dbh = &ConnectToPg($database, $username, $password, $host);
+
+# start by asking how many taxonlabelset records are orphaned
+
+my $recs = "SELECT count(*) FROM
+taxonlabelset tls LEFT JOIN
+matrix mx ON (tls.taxonlabelset_id = mx.taxonlabelset_id) LEFT JOIN
+treeblock tb ON (tls.taxonlabelset_id = tb.taxonlabelset_id) JOIN
+study s ON (tls.study_id = s.study_id) JOIN
+studystatus ss USING (studystatus_id)
+WHERE mx.matrix_id IS NULL
+AND tb.treeblock_id IS NULL ";
+my $totRec = $dbh->selectrow_array ($recs);
+
+print "$totRec orphaned taxonlabelset records need to be deleted\n\n";
+
+# get a list of all these taxonlabelset IDs
+
+my $statement = "SELECT tls.taxonlabelset_id FROM
+taxonlabelset tls LEFT JOIN
+matrix mx ON (tls.taxonlabelset_id = mx.taxonlabelset_id) LEFT JOIN
+treeblock tb ON (tls.taxonlabelset_id = tb.taxonlabelset_id) JOIN
+study s ON (tls.study_id = s.study_id) JOIN
+studystatus ss USING (studystatus_id)
+WHERE mx.matrix_id IS NULL
+AND tb.treeblock_id IS NULL ";
+
+my @orphaned;
+my $orphanedlist = $dbh->prepare($statement) or die "Can't prepare $statement: $dbh->errstr\n";
+$orphanedlist->execute;
+while(my @row = $orphanedlist->fetchrow_array) {
+ push(@orphaned, $row[0]);
+}
+
+foreach my $orph ( @orphaned ) {
+ print "preparing to delete $orph\n";
+
+ # run the whole thing in a single transaction
+ eval {
+
+ # First delete the records that reference this taxonlabelset
+ $dbh->do( "DELETE FROM taxonlabelset_taxonlabel WHERE taxonlabelset_id = ?", undef, $orph );
+
+ # Next delete the taxonlabelset
+ $dbh->do( "DELETE FROM taxonlabelset WHERE taxonlabelset_id = ?", undef, $orph );
+
+ # If no errors so far, let's commit
+ $dbh->commit();
+ };
+
+ if ($@) {
+ warn "Failed to delete taxonlabelset_id $orph: $@\n";
+ $dbh->rollback();
+ print "rollback!!\n";
+ }
+
+}
+
+$totRec = $dbh->selectrow_array ($recs);
+
+print "\n$totRec orphaned taxonlabelset records need to be deleted\n";
+
+exit;
+
+
+
+# Connect to Postgres using DBI
+#==============================================================
+sub ConnectToPg {
+
+ my ($cstr, $user, $pass, $host) = @_;
+
+ $cstr = "DBI:Pg:dbname="."$cstr";
+ $cstr .= ";host=$host" if ($host);
+
+ my $dbh = DBI->connect($cstr, $user, $pass, {AutoCommit => 0, PrintError => 1, RaiseError => 1});
+ $dbh || &error("DBI connect failed : ",$dbh->errstr);
+
+ return($dbh);
+}
Added: trunk/treebase-core/db/cleaning/2011-12-06_orphaned_taxonlabelsets/fixing_orphaned_taxonlabelsets.sql
===================================================================
--- trunk/treebase-core/db/cleaning/2011-12-06_orphaned_taxonlabelsets/fixing_orphaned_taxonlabelsets.sql (rev 0)
+++ trunk/treebase-core/db/cleaning/2011-12-06_orphaned_taxonlabelsets/fixing_orphaned_taxonlabelsets.sql 2011-12-06 19:24:05 UTC (rev 994)
@@ -0,0 +1,405 @@
+-- psql -U treebase_app -d treebasedev -h treebasedb-dev.nescent.org
+-- qLoMjwRz
+
+
+12037
+
+SELECT tls.taxonlabelset_id, count(tt.*)
+FROM
+taxonlabelset tls JOIN
+taxonlabelset_taxonlabel tt USING (taxonlabelset_id) JOIN
+taxonlabel tl USING (taxonlabel_id)
+WHERE tls.study_id = 12037
+GROUP BY tls.taxonlabelset_id
+
+23165 55
+23161 100
+23102 94
+
+Set 23102 is probably orphaned
+
+SELECT tls.*, mx.matrix_id, tb.treeblock_id
+FROM
+taxonlabelset tls LEFT JOIN
+matrix mx ON (tls.taxonlabelset_id = mx.taxonlabelset_id) LEFT JOIN
+treeblock tb ON (tls.taxonlabelset_id = tb.taxonlabelset_id)
+WHERE
+tls.taxonlabelset_id IN (23165, 23161, 23102)
+
+SELECT DISTINCT ON (tls.study_id) tls.taxonlabelset_id, tls.study_id, ss.description
+FROM
+taxonlabelset tls LEFT JOIN
+matrix mx ON (tls.taxonlabelset_id = mx.taxonlabelset_id) LEFT JOIN
+treeblock tb ON (tls.taxonlabelset_id = tb.taxonlabelset_id) JOIN
+study s ON (tls.study_id = s.study_id) JOIN
+studystatus ss USING (studystatus_id)
+WHERE
+mx.matrix_id IS NULL
+AND
+tb.treeblock_id IS NULL
+
+-- 345 studies are affected
+
+-- 1394 22 In Progress
+-- 4810 23 In Progress
+-- 9450 44 In Progress
+-- 11200 1019 Published
+-- 13335 1919 Published
+-- 14190 2264 In Progress
+-- 16071 10215 In Progress
+-- 16132 10317 Published
+-- 16464 10335 Published
+-- 16188 10339 In Progress
+-- 18753 10347 In Progress
+-- 16228 10356 In Progress
+-- 16280 10367 In Progress
+-- 16399 10399 Published
+-- 16402 10400 Published
+-- 16434 10413 In Progress
+-- 16456 10424 In Progress
+-- 16466 10425 In Progress
+-- 16477 10429 Published
+-- 16567 10431 In Progress
+-- 16484 10433 Published
+-- 16501 10434 In Progress
+-- 16507 10435 Published
+-- 18799 10436 Published
+-- 16514 10438 Published
+-- 16545 10449 In Progress
+-- 16554 10452 In Progress
+-- 16668 10462 In Progress
+-- 16784 10464 Published
+-- 16633 10468 In Progress
+-- 16638 10469 Published
+-- 16643 10470 In Progress
+-- 16653 10474 In Progress
+-- 16749 10485 Published
+-- 16757 10486 In Progress
+-- 16702 10489 Published
+-- 16696 10492 Published
+-- 16700 10493 Published
+-- 16731 10504 Published
+-- 16734 10507 Published
+-- 16738 10508 In Progress
+-- 16772 10511 Published
+-- 21310 10512 Published
+-- 16860 10529 In Progress
+-- 16888 10530 In Progress
+-- 16940 10536 Published
+-- 16972 10541 Published
+-- 16969 10542 Published
+-- 17064 10553 Published
+-- 17057 10569 In Progress
+-- 17076 10570 Published
+-- 17093 10583 In Progress
+-- 17289 10589 In Progress
+-- 17293 10593 Published
+-- 17206 10603 Published
+-- 17138 10604 In Progress
+-- 18665 10605 In Progress
+-- 17671 10608 Published
+-- 17174 10613 Published
+-- 17192 10623 Published
+-- 17237 10635 Published
+-- 17255 10639 In Progress
+-- 17379 10644 Published
+-- 17291 10648 Published
+-- 17338 10652 Published
+-- 22206 10653 Published
+-- 17347 10654 Published
+-- 17355 10658 Published
+-- 17468 10665 Published
+-- 18527 10673 In Progress
+-- 17453 10679 Published
+-- 17568 10680 Published
+-- 17429 10684 Published
+-- 17463 10689 Published
+-- 17471 10692 Published
+-- 17514 10698 In Progress
+-- 17528 10699 In Progress
+-- 17572 10703 Published
+-- 17584 10711 In Progress
+-- 22854 10713 Published
+-- 17605 10714 Published
+-- 18102 10724 Published
+-- 17643 10727 Published
+-- 17659 10732 Published
+-- 17669 10733 In Progress
+-- 17740 10736 Published
+-- 17752 10740 In Progress
+-- 17784 10749 Published
+-- 17821 10759 In Progress
+-- 17836 10766 Published
+-- 17850 10772 Published
+-- 17856 10774 Published
+-- 17895 10786 In Progress
+-- 17950 10795 In Progress
+-- 17992 10797 Published
+-- 17987 10803 Published
+-- 17997 10812 In Progress
+-- 18004 10814 In Progress
+-- 20216 10817 In Progress
+-- 18030 10821 Published
+-- 18044 10828 In Progress
+-- 18748 10830 Published
+-- 18138 10831 In Progress
+-- 18123 10847 Published
+-- 18216 10859 In Progress
+-- 18286 10861 In Progress
+-- 18238 10870 In Progress
+-- 18244 10876 Published
+-- 18346 10888 Published
+-- 18283 10898 Published
+-- 18366 10902 In Progress
+-- 18566 10904 In Progress
+-- 21995 10905 Published
+-- 18410 10908 Published
+-- 18478 10927 Published
+-- 18564 10938 Published
+-- 18580 10940 Published
+-- 18509 10942 Published
+-- 18591 10950 Published
+-- 18593 10951 Published
+-- 18619 10961 In Progress
+-- 18638 10963 Published
+-- 18633 10966 Published
+-- 18657 10971 In Progress
+-- 18673 10977 In Progress
+-- 18683 10983 Published
+-- 18687 10984 Published
+-- 18737 10996 Published
+-- 18726 10999 In Progress
+-- 18760 11009 Published
+-- 18822 11020 Published
+-- 22845 11021 Published
+-- 18826 11022 Published
+-- 18887 11032 Published
+-- 18983 11056 In Progress
+-- 19367 11058 Published
+-- 19007 11061 Published
+-- 19045 11078 In Progress
+-- 19055 11079 Published
+-- 19062 11080 Published
+-- 19077 11083 Published
+-- 19095 11088 Published
+-- 19107 11089 In Progress
+-- 19104 11090 Published
+-- 19429 11091 In Progress
+-- 19127 11093 Published
+-- 19126 11097 Published
+-- 19160 11111 Published
+-- 19173 11112 Published
+-- 19186 11114 Published
+-- 19191 11115 Published
+-- 19199 11116 Ready
+-- 19394 11117 In Progress
+-- 19205 11118 Published
+-- 19213 11122 Published
+-- 19260 11126 Published
+-- 19274 11128 Published
+-- 19318 11132 Published
+-- 19337 11136 In Progress
+-- 19359 11140 Published
+-- 21258 11159 In Progress
+-- 19424 11161 Published
+-- 19454 11174 Published
+-- 19462 11177 In Progress
+-- 19494 11181 In Progress
+-- 19497 11183 Published
+-- 19503 11184 Published
+-- 19513 11192 Published
+-- 19523 11197 Published
+-- 19540 11200 Published
+-- 20145 11204 Published
+-- 19682 11210 Published
+-- 19576 11211 Published
+-- 19601 11216 Published
+-- 19662 11219 In Progress
+-- 19654 11226 Published
+-- 19793 11252 In Progress
+-- 19828 11260 Published
+-- 19829 11261 In Progress
+-- 19844 11267 Published
+-- 19854 11269 Published
+-- 19883 11273 In Progress
+-- 19893 11276 In Progress
+-- 19918 11284 Published
+-- 19942 11289 Published
+-- 19962 11294 In Progress
+-- 19979 11296 In Progress
+-- 20000 11298 Published
+-- 20317 11303 Published
+-- 20140 11312 Published
+-- 20142 11313 Published
+-- 20177 11327 In Progress
+-- 20207 11331 Published
+-- 20190 11333 In Progress
+-- 20222 11336 Published
+-- 20252 11346 In Progress
+-- 20258 11348 In Progress
+-- 20284 11352 In Progress
+-- 20283 11355 Published
+-- 22141 11367 In Progress
+-- 20338 11369 In Progress
+-- 20802 11378 Published
+-- 20375 11380 In Progress
+-- 20520 11383 In Progress
+-- 20424 11391 Published
+-- 20419 11392 Published
+-- 20468 11397 In Progress
+-- 22095 11398 In Progress
+-- 20503 11404 Published
+-- 22263 11409 In Progress
+-- 20610 11415 Published
+-- 23182 11416 Published
+-- 20628 11417 In Progress
+-- 20631 11418 Published
+-- 20670 11439 Published
+-- 20719 11448 In Progress
+-- 21327 11461 In Progress
+-- 20964 11465 In Progress
+-- 21181 11468 Published
+-- 21278 11472 Published
+-- 21062 11476 Published
+-- 20901 11480 Published
+-- 21036 11487 In Progress
+-- 20941 11489 Published
+-- 20962 11499 In Progress
+-- 20976 11504 In Progress
+-- 21067 11512 Published
+-- 21052 11517 Published
+-- 21141 11524 In Progress
+-- 21513 11527 In Progress
+-- 21106 11535 Published
+-- 21112 11536 In Progress
+-- 21135 11537 In Progress
+-- 21188 11552 Published
+-- 22436 11557 Published
+-- 21216 11561 In Progress
+-- 21673 11563 Published
+-- 21343 11568 In Progress
+-- 21243 11570 Published
+-- 21246 11571 In Progress
+-- 21264 11575 Published
+-- 21273 11586 Published
+-- 21305 11593 Published
+-- 21320 11596 Published
+-- 21413 11600 In Progress
+-- 21349 11601 In Progress
+-- 21340 11607 In Progress
+-- 21345 11608 In Progress
+-- 21369 11616 Published
+-- 21380 11619 In Progress
+-- 21390 11623 Published
+-- 21387 11625 In Progress
+-- 22145 11644 Published
+-- 21461 11651 Published
+-- 21568 11652 In Progress
+-- 21520 11654 In Progress
+-- 21485 11659 In Progress
+-- 21750 11662 Published
+-- 21611 11670 In Progress
+-- 21696 11681 In Progress
+-- 21712 11683 In Progress
+-- 22040 11687 In Progress
+-- 21762 11689 Published
+-- 21774 11692 In Progress
+-- 21999 11695 Published
+-- 22004 11705 In Progress
+-- 22019 11711 In Progress
+-- 22027 11713 Published
+-- 22052 11719 In Progress
+-- 22089 11729 In Progress
+-- 22153 11747 Published
+-- 22170 11756 In Progress
+-- 22183 11759 Published
+-- 22225 11768 In Progress
+-- 22636 11779 Published
+-- 22256 11780 In Progress
+-- 22293 11798 Published
+-- 22348 11815 Published
+-- 22362 11822 Published
+-- 22390 11823 In Progress
+-- 22374 11825 In Progress
+-- 22387 11829 In Progress
+-- 22405 11832 In Progress
+-- 22450 11848 In Progress
+-- 22480 11849 Published
+-- 22706 11852 In Progress
+-- 22494 11863 In Progress
+-- 22489 11864 In Progress
+-- 22519 11865 Published
+-- 22496 11866 In Progress
+-- 22514 11868 Published
+-- 22529 11872 Published
+-- 22600 11876 Published
+-- 22551 11878 Published
+-- 22574 11891 Published
+-- 22581 11892 Published
+-- 22661 11893 In Progress
+-- 22608 11906 Published
+-- 23365 11908 Published
+-- 22632 11911 In Progress
+-- 22638 11914 In Progress
+-- 22652 11917 In Progress
+-- 22723 11918 Published
+-- 22727 11936 In Progress
+-- 22883 11943 In Progress
+-- 22872 11949 Published
+-- 22828 11950 Published
+-- 22876 11956 In Progress
+-- 23160 11959 In Progress
+-- 22895 11960 In Progress
+-- 23072 11963 In Progress
+-- 22914 11975 Published
+-- 22930 11980 In Progress
+-- 23021 11985 Published
+-- 23399 11988 Published
+-- 23539 11991 Published
+-- 22979 11995 In Progress
+-- 22996 12003 Published
+-- 23012 12006 In Progress
+-- 23061 12009 Published
+-- 23080 12029 In Progress
+-- 23095 12034 In Progress
+-- 23146 12036 Published
+-- 23102 12037 Published
+-- 23203 12043 In Progress
+-- 23158 12048 In Progress
+-- 23174 12050 In Progress
+-- 23200 12055 In Progress
+-- 23211 12057 Published
+-- 23226 12065 In Progress
+-- 23247 12072 In Progress
+-- 23274 12079 Published
+-- 23290 12087 In Progress
+-- 23292 12088 In Progress
+-- 23342 12096 In Progress
+-- 23357 12101 In Progress
+-- 23401 12120 In Progress
+-- 23408 12121 In Progress
+-- 23428 12128 In Progress
+-- 23491 12145 In Progress
+-- 23509 12149 In Progress
+-- 23606 12153 In Progress
+-- 23532 12160 Published
+-- 23590 12162 Published
+-- 23608 12167 In Progress
+
+
+SELECT tb.treeblock_id, stb.submission_id, pt.study_id
+FROM treeblock tb LEFT JOIN
+sub_treeblock stb USING (treeblock_id) LEFT JOIN
+phylotree pt ON (tb.treeblock_id = pt.treeblock_id)
+WHERE tb.taxonlabelset_id IS NULL
+
+-- 4781 66
+-- 4782 67
+-- 4783 68
+-- 4784 69
+-- 4785 70
+-- 4786 71
+-- 4787 72
+-- 4788 73
+
+
Added: trunk/treebase-core/db/cleaning/2011-12-06_orphaned_taxonlabelsets/output.txt
===================================================================
--- trunk/treebase-core/db/cleaning/2011-12-06_orphaned_taxonlabelsets/output.txt (rev 0)
+++ trunk/treebase-core/db/cleaning/2011-12-06_orphaned_taxonlabelsets/output.txt 2011-12-06 19:24:05 UTC (rev 994)
@@ -0,0 +1,5102 @@
+5098 orphaned taxonlabelset records need to be deleted
+
+preparing to delete 81
+preparing to delete 82
+preparing to delete 83
+preparing to delete 84
+preparing to delete 85
+preparing to delete 86
+preparing to delete 87
+preparing to delete 88
+preparing to delete 89
+preparing to delete 90
+preparing to delete 91
+preparing to delete 92
+preparing to delete 93
+preparing to delete 94
+preparing to delete 95
+preparing to delete 96
+preparing to delete 97
+preparing to delete 98
+preparing to delete 99
+preparing to delete 100
+preparing to delete 101
+preparing to delete 102
+preparing to delete 103
+preparing to delete 104
+preparing to delete 105
+preparing to delete 106
+preparing to delete 107
+preparing to delete 108
+preparing to delete 109
+preparing to delete 110
+preparing to delete 111
+preparing to delete 112
+preparing to delete 113
+preparing to delete 114
+preparing to delete 115
+preparing to delete 116
+preparing to delete 117
+preparing to delete 118
+preparing to delete 119
+preparing to delete 120
+preparing to delete 121
+preparing to delete 122
+preparing to delete 123
+preparing to delete 124
+preparing to delete 125
+preparing to delete 126
+preparing to delete 127
+preparing to delete 128
+preparing to delete 129
+preparing to delete 130
+preparing to delete 131
+preparing to delete 132
+preparing to delete 133
+preparing to delete 134
+preparing to delete 135
+preparing to delete 136
+preparing to delete 137
+preparing to delete 138
+preparing to delete 139
+preparing to delete 140
+preparing to delete 141
+preparing to delete 142
+preparing to delete 143
+preparing to delete 144
+preparing to delete 145
+preparing to delete 146
+preparing to delete 147
+preparing to delete 148
+preparing to delete 149
+preparing to delete 150
+preparing to delete 151
+preparing to delete 152
+preparing to delete 153
+preparing to delete 154
+preparing to delete 155
+preparing to delete 156
+preparing to delete 157
+preparing to delete 158
+preparing to delete 159
+preparing to delete 160
+preparing to delete 161
+preparing to delete 162
+preparing to delete 163
+preparing to delete 164
+preparing to delete 165
+preparing to delete 166
+preparing to delete 167
+preparing to delete 168
+preparing to delete 169
+preparing to delete 170
+preparing to delete 171
+preparing to delete 172
+preparing to delete 173
+preparing to delete 174
+preparing to delete 175
+preparing to delete 176
+preparing to delete 177
+preparing to delete 178
+preparing to delete 179
+preparing to delete 180
+preparing to delete 181
+preparing to delete 185
+preparing to delete 189
+preparing to delete 190
+preparing to delete 191
+preparing to delete 192
+preparing to delete 193
+preparing to delete 194
+preparing to delete 195
+preparing to delete 196
+preparing to delete 197
+preparing to delete 198
+preparing to delete 199
+preparing to delete 200
+preparing to delete 201
+preparing to delete 202
+preparing to delete 203
+preparing to delete 204
+preparing to delete 205
+preparing to delete 206
+preparing to delete 207
+preparing to delete 208
+preparing to delete 209
+preparing to delete 210
+preparing to delete 211
+preparing to delete 212
+preparing to delete 213
+preparing to delete 214
+preparing to delete 215
+preparing to delete 216
+preparing to delete 217
+preparing to delete 218
+preparing to delete 219
+preparing to delete 220
+preparing to delete 221
+preparing to delete 222
+preparing to delete 223
+preparing to delete 224
+preparing to delete 225
+preparing to delete 226
+preparing to delete 227
+preparing to delete 228
+preparing to delete 229
+preparing to delete 230
+preparing to delete 231
+preparing to delete 232
+preparing to delete 233
+preparing to delete 234
+preparing to delete 235
+preparing to delete 236
+preparing to delete 237
+preparing to delete 238
+preparing to delete 239
+preparing to delete 240
+preparing to delete 241
+preparing to delete 242
+preparing to delete 243
+preparing to delete 244
+preparing to delete 245
+preparing to delete 246
+preparing to delete 247
+preparing to delete 248
+preparing to delete 249
+preparing to delete 250
+preparing to delete 251
+preparing to delete 252
+preparing to delete 253
+preparing to delete 254
+preparing to delete 255
+preparing to delete 256
+preparing to delete 257
+preparing to delete 258
+preparing to delete 259
+preparing to delete 260
+preparing to delete 261
+preparing to delete 262
+preparing to delete 263
+preparing to delete 264
+preparing to delete 265
+preparing to delete 266
+preparing to delete 267
+preparing to delete 268
+preparing to delete 269
+preparing to delete 270
+preparing to delete 271
+preparing to delete 272
+preparing to delete 273
+preparing to delete 274
+preparing to delete 275
+preparing to delete 276
+preparing to delete 277
+preparing to delete 278
+preparing to delete 279
+preparing to delete 280
+preparing to delete 281
+preparing to delete 282
+preparing to delete 283
+preparing to delete 284
+preparing to delete 285
+preparing to delete 286
+preparing to delete 287
+preparing to delete 288
+preparing to delete 289
+preparing to delete 290
+preparing to delete 291
+preparing to delete 292
+preparing to delete 293
+preparing to delete 294
+preparing to delete 295
+preparing to delete 296
+preparing to delete 297
+preparing to delete 298
+preparing to delete 299
+preparing to delete 300
+preparing to delete 301
+preparing to delete 302
+preparing to delete 303
+preparing to delete 304
+preparing to delete 305
+preparing to delete 306
+preparing to delete 307
+preparing to delete 308
+preparing to delete 309
+preparing to delete 310
+preparing to delete 311
+preparing to delete 312
+preparing to delete 313
+preparing to delete 314
+preparing to delete 315
+preparing to delete 316
+preparing to delete 317
+preparing to delete 318
+preparing to delete 319
+preparing to delete 320
+preparing to delete 321
+preparing to delete 322
+preparing to delete 323
+preparing to delete 324
+preparing to delete 325
+preparing to delete 326
+preparing to delete 327
+preparing to delete 328
+preparing to delete 329
+preparing to delete 330
+preparing to delete 331
+preparing to delete 332
+preparing to delete 333
+preparing to delete 334
+preparing to delete 335
+preparing to delete 336
+preparing to delete 337
+preparing to delete 338
+preparing to delete 339
+preparing to delete 340
+preparing to delete 341
+preparing to delete 342
+preparing to delete 343
+preparing to delete 344
+preparing to delete 345
+preparing to delete 346
+preparing to delete 347
+preparing to delete 348
+preparing to delete 349
+preparing to delete 350
+preparing to delete 351
+preparing to delete 352
+preparing to delete 353
+preparing to delete 354
+preparing to delete 355
+preparing to delete 356
+preparing to delete 357
+preparing to delete 358
+preparing to delete 359
+preparing to delete 361
+preparing to delete 362
+preparing to delete 363
+preparing to delete 364
+preparing to delete 365
+preparing to delete 366
+preparing to delete 367
+preparing to delete 368
+preparing to delete 369
+preparing to delete 370
+preparing to delete 371
+preparing to delete 372
+preparing to delete 373
+preparing to delete 374
+preparing to delete 375
+preparing to delete 376
+preparing to delete 377
+preparing to delete 378
+preparing to delete 380
+preparing to delete 381
+preparing to delete 382
+preparing to delete 383
+preparing to delete 384
+preparing to delete 385
+preparing to delete 386
+preparing to delete 387
+preparing to delete 388
+preparing to delete 389
+preparing to delete 390
+preparing to delete 391
+preparing to delete 392
+preparing to delete 393
+preparing to delete 394
+preparing to delete 395
+preparing to delete 396
+preparing to delete 397
+preparing to delete 398
+preparing to delete 399
+preparing to delete 400
+preparing to delete 401
+preparing to delete 402
+preparing to delete 403
+preparing to delete 404
+preparing to delete 405
+preparing to delete 406
+preparing to delete 407
+preparing to delete 408
+preparing to delete 409
+preparing to delete 410
+preparing to delete 411
+preparing to delete 412
+preparing to delete 413
+preparing to delete 414
+preparing to delete 415
+preparing to delete 416
+preparing to delete 417
+preparing to delete 418
+preparing to delete 419
+preparing to delete 420
+preparing to delete 421
+preparing to delete 422
+preparing to delete 423
+preparing to delete 424
+preparing to delete 425
+preparing to delete 426
+preparing to delete 427
+preparing to delete 428
+preparing to delete 429
+preparing to delete 430
+preparing to delete 431
+preparing to delete 432
+preparing to delete 433
+preparing to delete 434
+preparing to delete 435
+preparing to delete 436
+preparing to delete 437
+preparing to delete 438
+preparing to delete 439
+preparing to delete 440
+preparing to delete 441
+preparing to delete 442
+preparing to delete 443
+preparing to delete 444
+preparing to delete 445
+preparing to delete 446
+preparing to delete 447
+preparing to delete 448
+preparing to delete 449
+preparing to delete 450
+preparing to delete 451
+preparing to delete 452
+preparing to delete 453
+preparing to delete 454
+preparing to delete 455
+preparing to delete 456
+preparing to delete 457
+preparing to delete 458
+preparing to delete 459
+preparing to delete 460
+preparing to delete 461
+preparing to delete 462
+preparing to delete 463
+preparing to delete 464
+preparing to delete 465
+preparing to delete 466
+preparing to delete 467
+preparing to delete 468
+preparing to delete 469
+preparing to delete 470
+preparing to delete 471
+preparing to delete 472
+preparing to delete 473
+preparing to delete 474
+preparing to delete 475
+preparing to delete 476
+preparing to delete 477
+preparing to delete 478
+preparing to delete 479
+preparing to delete 480
+preparing to delete 481
+preparing to delete 482
+preparing to delete 483
+preparing to delete 484
+preparing to delete 485
+preparing to delete 486
+preparing to delete 487
+preparing to delete 488
+preparing to delete 489
+preparing to delete 490
+preparing to delete 491
+preparing to delete 492
+preparing to delete 493
+preparing to delete 494
+preparing to delete 495
+preparing to delete 496
+preparing to delete 497
+preparing to delete 498
+preparing to delete 499
+preparing to delete 500
+preparing to delete 501
+preparing to delete 502
+preparing to delete 503
+preparing to delete 504
+preparing to delete 505
+preparing to delete 506
+preparing to delete 507
+preparing to delete 508
+preparing to delete 509
+preparing to delete 510
+preparing to delete 511
+preparing to delete 512
+preparing to delete 513
+preparing to delete 514
+preparing to delete 515
+preparing to delete 516
+preparing to delete 517
+preparing to delete 518
+preparing to delete 519
+preparing to delete 520
+preparing to delete 521
+preparing to delete 522
+preparing to delete 523
+preparing to delete 524
+preparing to delete 525
+preparing to delete 526
+preparing to delete 527
+preparing to delete 528
+preparing to delete 529
+preparing to delete 530
+preparing to delete 531
+preparing to delete 532
+preparing to delete 533
+preparing to delete 534
+preparing to delete 535
+preparing to delete 536
+preparing to delete 537
+preparing to delete 538
+preparing to delete 539
+preparing to delete 540
+preparing to delete 541
+preparing to delete 542
+preparing to delete 543
+preparing to delete 544
+preparing to delete 545
+preparing to delete 546
+preparing to delete 547
+preparing to delete 548
+preparing to delete 549
+preparing to delete 550
+preparing to delete 551
+preparing to delete 552
+preparing to delete 553
+preparing to delete 554
+preparing to delete 555
+preparing to delete 556
+preparing to delete 557
+preparing to delete 558
+preparing to delete 559
+preparing to delete 560
+preparing to delete 561
+preparing to delete 562
+preparing to delete 563
+preparing to delete 564
+preparing to delete 565
+preparing to delete 566
+preparing to delete 567
+preparing to delete 568
+preparing to delete 569
+preparing to delete 570
+preparing to delete 571
+preparing to delete 572
+preparing to delete 573
+preparing to delete 574
+preparing to delete 575
+preparing to delete 576
+preparing to delete 577
+preparing to delete 578
+preparing to delete 579
+preparing to delete 580
+preparing to delete 581
+preparing to delete 582
+preparing to delete 583
+preparing to delete 584
+preparing to delete 585
+preparing to delete 586
+preparing to delete 587
+preparing to delete 588
+preparing to delete 589
+preparing to delete 590
+preparing to delete 591
+preparing to delete 592
+preparing to delete 593
+preparing to delete 594
+preparing to delete 595
+preparing to delete 596
+preparing to delete 597
+preparing to delete 598
+preparing to delete 599
+preparing to delete 600
+preparing to delete 601
+preparing to delete 602
+preparing to delete 603
+preparing to delete 604
+preparing to delete 605
+preparing to delete 606
+preparing to delete 607
+preparing to delete 608
+preparing to delete 609
+preparing to delete 610
+preparing to delete 611
+preparing to delete 612
+preparing to delete 613
+preparing to delete 614
+preparing to delete 615
+preparing to delete 616
+preparing to delete 617
+preparing to delete 618
+preparing to delete 619
+preparing to delete 620
+preparing to delete 621
+preparing to delete 622
+preparing to delete 623
+preparing to delete 624
+preparing to delete 625
+preparing to delete 626
+preparing to delete 627
+preparing to delete 628
+preparing to delete 629
+preparing to delete 630
+preparing to delete 631
+preparing to delete 632
+preparing to delete 633
+preparing to delete 634
+preparing to delete 635
+preparing to delete 636
+preparing to delete 637
+preparing to delete 638
+preparing to delete 639
+preparing to delete 640
+preparing to delete 641
+preparing to delete 642
+preparing to delete 643
+preparing to delete 644
+preparing to delete 645
+preparing to delete 646
+preparing to delete 647
+preparing to delete 648
+preparing to delete 649
+preparing to delete 650
+preparing to delete 651
+preparing to delete 652
+preparing to delete 653
+preparing to delete 654
+preparing to delete 655
+preparing to delete 656
+preparing to delete 657
+preparing to delete 658
+preparing to delete 659
+preparing to delete 660
+preparing to delete 661
+preparing to delete 662
+preparing to delete 663
+preparing to delete 664
+preparing to delete 665
+preparing to delete 666
+preparing to delete 667
+preparing to delete 668
+preparing to delete 669
+preparing to delete 670
+preparing to delete 671
+preparing to delete 672
+preparing to delete 673
+preparing to delete 674
+preparing to delete 675
+preparing to delete 676
+preparing to delete 677
+preparing to delete 678
+preparing to delete 679
+preparing to delete 680
+preparing to delete 681
+preparing to delete 682
+preparing to delete 683
+preparing to delete 684
+preparing to delete 685
+preparing to delete 686
+preparing to delete 687
+preparing to delete 688
+preparing to delete 689
+preparing to delete 690
+preparing to delete 691
+preparing to delete 692
+preparing to delete 693
+preparing to delete 694
+preparing to delete 695
+preparing to delete 696
+preparing to delete 697
+preparing to delete 698
+preparing to delete 699
+preparing to delete 700
+preparing to delete 701
+preparing to delete 702
+preparing to delete 703
+preparing to delete 704
+preparing to delete 705
+preparing to delete 706
+preparing to delete 707
+preparing to delete 708
+preparing to delete 709
+preparing to delete 710
+preparing to delete 711
+preparing to delete 712
+preparing to delete 713
+preparing to delete 714
+preparing to delete 715
+preparing to delete 716
+preparing to delete 717
+preparing to delete 718
+preparing to delete 719
+preparing to delete 720
+preparing to delete 721
+preparing to delete 722
+preparing to delete 723
+preparing to delete 724
+preparing to delete 725
+preparing to delete 726
+preparing to delete 727
+preparing to delete 728
+preparing to delete 729
+preparing to delete 730
+preparing to delete 731
+preparing to delete 732
+preparing to delete 733
+preparing to delete 734
+preparing to delete 735
+preparing to delete 736
+preparing to delete 737
+preparing to delete 738
+preparing to delete 739
+preparing to delete 740
+preparing to delete 741
+preparing to delete 742
+preparing to delete 743
+preparing to delete 744
+preparing to delete 745
+preparing to delete 746
+preparing to delete 747
+preparing to delete 748
+preparing to delete 749
+preparing to delete 750
+preparing to delete 751
+preparing to delete 752
+preparing to delete 753
+preparing to delete 754
+preparing to delete 755
+preparing to delete 756
+preparing to delete 757
+preparing to delete 758
+preparing to delete 759
+preparing to delete 760
+preparing to delete 761
+preparing to delete 762
+preparing to delete 763
+preparing to delete 764
+preparing to delete 765
+preparing to delete 766
+preparing to delete 767
+preparing to delete 768
+preparing to delete 781
+preparing to delete 782
+preparing to delete 783
+preparing to delete 784
+preparing to delete 785
+preparing to delete 786
+preparing to delete 787
+preparing to delete 788
+preparing to delete 789
+preparing to delete 790
+preparing to delete 791
+preparing to delete 792
+preparing to delete 793
+preparing to delete 794
+preparing to delete 795
+preparing to delete 796
+preparing to delete 797
+preparing to delete 798
+preparing to delete 799
+preparing to delete 800
+preparing to delete 801
+preparing to delete 802
+preparing to delete 803
+preparing to delete 804
+preparing to delete 805
+preparing to delete 806
+preparing to delete 807
+preparing to delete 808
+preparing to delete 809
+preparing to delete 810
+preparing to delete 811
+preparing to delete 812
+preparing to delete 813
+preparing to delete 814
+preparing to delete 815
+preparing to delete 816
+preparing to delete 817
+preparing to delete 818
+preparing to delete 819
+preparing to delete 820
+preparing to delete 821
+preparing to delete 822
+preparing to delete 823
+preparing to delete 824
+preparing to delete 825
+preparing to delete 826
+preparing to delete 827
+preparing to delete 828
+preparing to delete 829
+preparing to delete 830
+preparing to delete 831
+preparing to delete 832
+preparing to delete 833
+preparing to delete 834
+preparing to delete 835
+preparing to delete 836
+preparing to delete 837
+preparing to delete 838
+preparing to delete 839
+preparing to delete 840
+preparing to delete 841
+preparing to delete 842
+preparing to delete 843
+preparing to delete 851
+preparing to delete 853
+preparing to delete 854
+preparing to delete 855
+preparing to delete 856
+preparing to delete 857
+preparing to delete 858
+preparing to delete 859
+preparing to delete 860
+preparing to delete 861
+preparing to delete 862
+preparing to delete 863
+preparing to delete 864
+preparing to delete 865
+preparing to delete 866
+preparing to delete 867
+preparing to delete 868
+preparing to delete 869
+preparing to delete 870
+preparing to delete 871
+preparing to delete 872
+preparing to delete 873
+preparing to delete 874
+preparing to delete 875
+preparing to delete 876
+preparing to delete 877
+preparing to delete 878
+preparing to delete 879
+preparing to delete 880
+preparing to delete 881
+preparing to delete 882
+preparing to delete 883
+preparing to delete 884
+preparing to delete 885
+preparing to delete 886
+preparing to delete 887
+preparing to delete 888
+preparing to delete 889
+preparing to delete 890
+preparing to delete 891
+preparing to delete 892
+preparing to delete 893
+preparing to delete 894
+preparing to delete 895
+preparing to delete 896
+preparing to delete 897
+preparing to delete 898
+preparing to delete 899
+preparing to delete 900
+preparing to delete 901
+preparing to delete 902
+preparing to delete 903
+preparing to delete 904
+preparing to delete 905
+preparing to delete 906
+preparing to delete 907
+preparing to delete 908
+preparing to delete 909
+preparing to delete 910
+preparing to delete 911
+preparing to delete 912
+preparing to delete 913
+preparing to delete 914
+preparing to delete 915
+preparing to delete 916
+preparing to delete 917
+preparing to delete 918
+preparing to delete 919
+preparing to delete 920
+preparing to delete 921
+preparing to delete 922
+preparing to delete 923
+preparing to delete 924
+preparing to delete 925
+preparing to delete 926
+preparing to delete 927
+preparing to delete 928
+preparing to delete 929
+preparing to delete 930
+preparing to delete 931
+preparing to delete 932
+preparing to delete 933
+preparing to delete 934
+preparing to delete 935
+preparing to delete 936
+preparing to delete 937
+preparing to delete 938
+preparing to delete 939
+preparing to delete 940
+preparing to delete 941
+preparing to delete 942
+preparing to delete 943
+preparing to delete 944
+preparing to delete 945
+preparing to delete 946
+preparing to delete 947
+preparing to delete 948
+preparing to delete 949
+preparing to delete 950
+preparing to delete 951
+preparing to delete 952
+preparing to delete 953
+preparing to delete 954
+preparing to delete 955
+preparing to delete 956
+preparing to delete 957
+preparing to delete 958
+preparing to delete 959
+preparing to delete 960
+preparing to delete 961
+preparing to delete 962
+preparing to delete 963
+preparing to delete 964
+preparing to delete 965
+preparing to delete 966
+preparing to delete 967
+preparing to delete 968
+preparing to delete 969
+preparing to delete 970
+preparing to delete 971
+preparing to delete 972
+preparing to delete 973
+preparing to delete 974
+preparing to delete 975
+preparing to delete 976
+preparing to delete 977
+preparing to delete 978
+preparing to delete 979
+preparing to delete 980
+preparing to delete 981
+preparing to delete 982
+preparing to delete 983
+preparing to delete 984
+preparing to delete 985
+preparing to delete 986
+preparing to delete 987
+preparing to delete 988
+preparing to delete 989
+preparing to delete 990
+preparing to delete 991
+preparing to delete 992
+preparing to delete 993
+preparing to delete 994
+preparing to delete 995
+preparing to delete 996
+preparing to delete 997
+preparing to delete 998
+preparing to delete 999
+preparing to delete 1000
+preparing to delete 1001
+preparing to delete 1002
+preparing to delete 1003
+preparing to delete 1004
+preparing to delete 1005
+preparing to delete 1006
+preparing to delete 1007
+preparing to delete 1008
+preparing to delete 1009
+preparing to delete 1011
+preparing to delete 1012
+preparing to delete 1013
+preparing to delete 1014
+preparing to delete 1015
+preparing to delete 1016
+preparing to delete 1017
+preparing to delete 1018
+preparing to delete 1019
+preparing to delete 1020
+preparing to delete 1021
+preparing to delete 1022
+preparing to delete 1023
+preparing to delete 1024
+preparing to delete 1025
+preparing to delete 1026
+preparing to delete 1027
+preparing to delete 1028
+preparing to delete 1029
+preparing to delete 1030
+preparing to delete 1031
+preparing to delete 1032
+preparing to delete 1033
+preparing to delete 1034
+preparing to delete 1035
+preparing to delete 1036
+preparing to delete 1037
+preparing to delete 1038
+preparing to delete 1039
+preparing to delete 1040
+preparing to delete 1041
+preparing to delete 1042
+preparing to delete 1043
+preparing to delete 1044
+preparing to delete 1045
+preparing to delete 1051
+preparing to delete 1052
+preparing to delete 1058
+preparing to delete 1059
+preparing to delete 1060
+preparing to delete 1061
+preparing to delete 1062
+preparing to delete 1063
+preparing to delete 1064
+preparing to delete 1065
+preparing to delete 1066
+preparing to delete 1067
+preparing to delete 1068
+preparing to delete 1069
+preparing to delete 1070
+preparing to delete 1071
+preparing to delete 1072
+preparing to delete 1073
+preparing to delete 1074
+preparing to delete 1075
+preparing to delete 1076
+preparing to delete 1077
+preparing to delete 1078
+preparing to delete 1079
+preparing to delete 1080
+preparing to delete 1081
+preparing to delete 1082
+preparing to delete 1083
+preparing to delete 1084
+preparing to delete 1085
+preparing to delete 1086
+preparing to delete 1087
+preparing to delete 1088
+preparing to delete 1089
+preparing to delete 1090
+preparing to delete 1091
+preparing to delete 1092
+preparing to delete 1093
+preparing to delete 1094
+preparing to delete 1095
+preparing to delete 1096
+preparing to delete 1097
+preparing to delete 1098
+preparing to delete 1099
+preparing to delete 1100
+preparing to delete 1101
+preparing to delete 1102
+preparing to delete 1103
+preparing to delete 1104
+preparing to delete 1105
+preparing to delete 1106
+preparing to delete 1107
+preparing to delete 1108
+preparing to delete 1109
+preparing to delete 1110
+preparing to delete 1111
+preparing to delete 1112
+preparing to delete 1113
+preparing to delete 1114
+preparing to delete 1115
+preparing to delete 1116
+preparing to delete 1117
+preparing to delete 1118
+preparing to delete 1119
+preparing to delete 1120
+preparing to delete 1121
+preparing to delete 1122
+preparing to delete 1123
+preparing to delete 1124
+preparing to delete 1125
+preparing to delete 1126
+preparing to delete 1127
+preparing to delete 1128
+preparing to delete 1129
+preparing to delete 1130
+preparing to delete 1131
+preparing to delete 1132
+preparing to delete 1133
+preparing to delete 1134
+preparing to delete 1135
+preparing to delete 1136
+preparing to delete 1137
+preparing to delete 1138
+preparing to delete 1139
+preparing to delete 1140
+preparing to delete 1141
+preparing to delete 1142
+preparing to delete 1143
+preparing to delete 1144
+preparing to delete 1145
+preparing to delete 1146
+preparing to delete 1147
+preparing to delete 1148
+preparing to delete 1149
+preparing to delete 1150
+preparing to delete 1151
+preparing to delete 1152
+preparing to delete 1153
+preparing to delete 1154
+preparing to delete 1155
+preparing to delete 1156
+preparing to delete 1157
+preparing to delete 1158
+preparing to delete 1159
+preparing to delete 1160
+preparing to delete 1161
+preparing to delete 1162
+preparing to delete 1163
+preparing to delete 1164
+preparing to delete 1165
+preparing to delete 1166
+preparing to delete 1167
+preparing to delete 1168
+preparing to delete 1169
+preparing to delete 1170
+preparing to delete 1171
+preparing to delete 1172
+preparing to delete 1173
+preparing to delete 1174
+preparing to delete 1175
+preparing to delete 1176
+preparing to delete 1177
+preparing to delete 1178
+preparing to delete 1179
+preparing to delete 1180
+preparing to delete 1181
+preparing to delete 1182
+preparing to delete 1183
+preparing to delete 1184
+preparing to delete 1185
+preparing to delete 1186
+preparing to delete 1187
+preparing to delete 1188
+preparing to delete 1189
+preparing to delete 1190
+preparing to delete 1191
+preparing to delete 1192
+preparing to delete 1193
+preparing to delete 1194
+preparing to delete 1195
+preparing to delete 1196
+preparing to delete 1197
+preparing to delete 1198
+preparing to delete 1199
+preparing to delete 1200
+preparing to delete 1201
+preparing to delete 1202
+preparing to delete 1203
+preparing to delete 1204
+preparing to delete 1205
+preparing to delete 1206
+preparing to delete 1207
+preparing to delete 1208
+preparing to delete 1209
+preparing to delete 1210
+preparing to delete 1211
+preparing to delete 1212
+preparing to delete 1213
+preparing to delete 1214
+preparing to delete 1215
+preparing to delete 1216
+preparing to delete 1217
+preparing to delete 1218
+preparing to delete 1219
+preparing to delete 1220
+preparing to delete 1221
+preparing to delete 1222
+preparing to delete 1223
+preparing to delete 1224
+preparing to delete 1225
+preparing to delete 1226
+preparing to delete 1227
+preparing to delete 1228
+preparing to delete 1229
+preparing to delete 1230
+preparing to delete 1231
+preparing to delete 1232
+preparing to delete 1233
+preparing to delete 1234
+preparing to delete 1235
+preparing to delete 1236
+preparing to delete 1237
+preparing to delete 1238
+preparing to delete 1239
+preparing to delete 1240
+preparing to delete 1241
+preparing to delete 1242
+preparing to delete 1243
+preparing to delete 1244
+preparing to delete 1245
+preparing to delete 1246
+preparing to delete 1247
+preparing to delete 1248
+preparing to delete 1249
+preparing to delete 1250
+preparing to delete 1251
+preparing to delete 1252
+preparing to delete 1253
+preparing to delete 1254
+preparing to delete 1255
+preparing to delete 1256
+preparing to delete 1257
+preparing to delete 1258
+preparing to delete 1259
+preparing to delete 1260
+preparing to delete 1261
+preparing to delete 1262
+preparing to delete 1263
+preparing to delete 1264
+preparing to delete 1265
+preparing to delete 1266
+preparing to delete 1267
+preparing to delete 1268
+preparing to delete 1269
+preparing to delete 1270
+preparing to delete 1271
+preparing to delete 1272
+preparing to delete 1273
+preparing to delete 1274
+preparing to delete 1275
+preparing to delete 1276
+preparing to delete 1277
+preparing to delete 1278
+preparing to delete 1279
+preparing to delete 1280
+preparing to delete 1281
+preparing to delete 1282
+preparing to delete 1283
+preparing to delete 1284
+preparing to delete 1285
+preparing to delete 1286
+preparing to delete 1287
+preparing to delete 1288
+preparing to delete 1289
+preparing to delete 1290
+preparing to delete 1291
+preparing to delete 1292
+preparing to delete 1293
+preparing to delete 1294
+preparing to delete 1295
+preparing to delete 1296
+preparing to delete 1297
+preparing to delete 1298
+preparing to delete 1299
+preparing to delete 1300
+preparing to delete 1301
+preparing to delete 1302
+preparing to delete 1303
+preparing to delete 1304
+preparing to delete 1305
+preparing to delete 1306
+preparing to delete 1307
+preparing to delete 1308
+preparing to delete 1309
+preparing to delete 1310
+preparing to delete 1311
+preparing to delete 1312
+preparing to delete 1313
+preparing to delete 1314
+preparing to delete 1315
+preparing to delete 1316
+preparing to delete 1317
+preparing to delete 1318
+preparing to delete 1319
+preparing to delete 1320
+preparing to delete 1321
+preparing to delete 1322
+preparing to delete 1323
+preparing to delete 1324
+preparing to delete 1325
+preparing to delete 1326
+preparing to delete 1327
+preparing to delete 1328
+preparing to delete 1329
+preparing to delete 1330
+preparing to delete 1331
+preparing to delete 1332
+preparing to delete 1333
+preparing to delete 1334
+preparing to delete 1335
+preparing to delete 1336
+preparing to delete 1337
+preparing to delete 1338
+preparing to delete 1339
+preparing to delete 1340
+preparing to delete 1341
+preparing to delete 1342
+preparing to delete 1343
+preparing to delete 1344
+preparing to delete 1345
+preparing to delete 1346
+preparing to delete 1347
+preparing to delete 1348
+preparing to delete 1349
+preparing to delete 1350
+preparing to delete 1351
+preparing to delete 1352
+preparing to delete 1353
+preparing to delete 1354
+preparing to delete 1355
+preparing to delete 1356
+preparing to delete 1357
+preparing to delete 1358
+preparing to delete 1359
+preparing to delete 1360
+preparing to delete 1361
+preparing to delete 1362
+preparing to delete 1363
+preparing to delete 1364
+preparing to delete 1365
+preparing to delete 1366
+preparing to delete 1367
+preparing to delete 1368
+preparing to delete 1369
+preparing to delete 1370
+preparing to delete 1371
+preparing to delete 1372
+preparing to delete 1373
+preparing to delete 1374
+preparing to delete 1375
+preparing to delete 1376
+preparing to delete 1377
+preparing to delete 1378
+preparing to delete 1379
+preparing to delete 1380
+preparing to delete 1381
+preparing to delete 1382
+preparing to delete 1383
+preparing to delete 1384
+preparing to delete 1385
+preparing to delete 1386
+preparing to delete 1387
+preparing to delete 1388
+preparing to delete 1389
+preparing to delete 1390
+preparing to delete 1391
+preparing to delete 1392
+preparing to delete 1393
+preparing to delete 1394
+preparing to delete 1395
+preparing to delete 1396
+preparing to delete 1397
+preparing to delete 1398
+preparing to delete 1399
+preparing to delete 1400
+preparing to delete 1401
+preparing to delete 1402
+preparing to delete 1403
+preparing to delete 1404
+preparing to delete 1405
+preparing to delete 1406
+preparing to delete 1407
+preparing to delete 1408
+preparing to delete 1409
+preparing to delete 1410
+preparing to delete 1411
+preparing to delete 1412
+preparing to delete 1413
+preparing to delete 1414
+preparing to delete 1415
+preparing to delete 1416
+preparing to delete 1417
+preparing to delete 1418
+preparing to delete 1419
+preparing to delete 1420
+preparing to delete 1421
+preparing to delete 1422
+preparing to delete 1423
+preparing to delete 1424
+preparing to delete 1425
+preparing to delete 1426
+preparing to delete 1427
+preparing to delete 1428
+preparing to delete 1429
+preparing to delete 1430
+preparing to delete 1431
+preparing to delete 1432
+preparing to delete 1433
+preparing to delete 1434
+preparing to delete 1435
+preparing to delete 1436
+preparing to delete 1437
+preparing to delete 1438
+preparing to delete 1439
+preparing to delete 1440
+preparing to delete 1441
...
[truncated message content] |
|
From: <sfr...@us...> - 2011-12-06 21:02:02
|
Revision: 996
http://treebase.svn.sourceforge.net/treebase/?rev=996&view=rev
Author: sfrgpiel
Date: 2011-12-06 21:01:56 +0000 (Tue, 06 Dec 2011)
Log Message:
-----------
Fix problem where matrix records lack a study_id value
Added Paths:
-----------
trunk/treebase-core/db/cleaning/2011-12-06_matrix_nullstudyid_fix/
trunk/treebase-core/db/cleaning/2011-12-06_matrix_nullstudyid_fix/fix_matrix_study_id_null.sql
Added: trunk/treebase-core/db/cleaning/2011-12-06_matrix_nullstudyid_fix/fix_matrix_study_id_null.sql
===================================================================
--- trunk/treebase-core/db/cleaning/2011-12-06_matrix_nullstudyid_fix/fix_matrix_study_id_null.sql (rev 0)
+++ trunk/treebase-core/db/cleaning/2011-12-06_matrix_nullstudyid_fix/fix_matrix_study_id_null.sql 2011-12-06 21:01:56 UTC (rev 996)
@@ -0,0 +1,25 @@
+begin work;
+
+-- check to see how many matrices lack a study_id
+SELECT count(*) FROM matrix WHERE study_id IS NULL;
+
+-- for those matrices that lack a study_id,
+-- update the study_id based on the study_id
+-- value found in the related taxonlabelset
+
+UPDATE matrix SET study_id = tls.study_id
+FROM matrix mx JOIN taxonlabelset tls USING (taxonlabelset_id)
+WHERE mx.study_id IS NULL
+AND tls.study_id IS NOT NULL
+AND matrix.matrix_id = mx.matrix_id;
+
+-- check to see how many trees still lack a study_id
+SELECT count(*) FROM matrix WHERE study_id IS NULL;
+
+-- if no more trees lack a study_id, we can now apply a new
+-- constraint to ensure that trees always have a study_id
+
+ALTER TABLE matrix
+ALTER COLUMN study_id SET NOT NULL;
+
+commit;
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
|
From: <sfr...@us...> - 2012-01-31 22:03:20
|
Revision: 1028
http://treebase.svn.sourceforge.net/treebase/?rev=1028&view=rev
Author: sfrgpiel
Date: 2012-01-31 22:03:14 +0000 (Tue, 31 Jan 2012)
Log Message:
-----------
Update SQL statement to fix older TB1 citations that are still called "in prep" when they are likely to be published
Added Paths:
-----------
trunk/treebase-core/db/cleaning/2012-01-31_Update_TB1_citation_status/
trunk/treebase-core/db/cleaning/2012-01-31_Update_TB1_citation_status/update_citation_status.sql
Added: trunk/treebase-core/db/cleaning/2012-01-31_Update_TB1_citation_status/update_citation_status.sql
===================================================================
--- trunk/treebase-core/db/cleaning/2012-01-31_Update_TB1_citation_status/update_citation_status.sql (rev 0)
+++ trunk/treebase-core/db/cleaning/2012-01-31_Update_TB1_citation_status/update_citation_status.sql 2012-01-31 22:03:14 UTC (rev 1028)
@@ -0,0 +1,16 @@
+-- For old TreeBASE1 data (i.e. those with a value for tb_studyid) that have
+-- page numbers in their citation metadata, it is reasonable to assume
+-- that these papers are published. This takes all such records that
+-- are still called "In Prep" and toggles them to "Published"
+
+begin work;
+
+UPDATE citation SET citationstatus_id = (SELECT citationstatus_id FROM citationstatus WHERE description = 'Published')
+FROM study st JOIN citation ct USING (citation_id)
+JOIN citationstatus cs USING (citationstatus_id)
+WHERE cs.description = 'In Prep'
+AND length(st.tb_studyid) > 1
+AND length(ct.pages) > 0
+AND citation.citation_id = ct.citation_id;
+
+commit;
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