[Treebase-guts] SF.net SVN: treebase:[595] trunk/treebase-core/db/cleaning

[<sfr...@us...>]

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.