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

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

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.