[Treebase-guts] SF.net SVN: treebase:[611] trunk/treebase-core/db/tb1load

[<vga...@us...>]

Revision: 611
          http://treebase.svn.sourceforge.net/treebase/?rev=611&view=rev
Author:   vgapeyev
Date:     2010-03-16 19:47:08 +0000 (Tue, 16 Mar 2010)

Log Message:
-----------
A catch-up fix to set phylotree.treetype_id, based on an additional dump from TB1

Added Paths:
-----------
    trunk/treebase-core/db/tb1load/zzFixTreeTypes/
    trunk/treebase-core/db/tb1load/zzFixTreeTypes/README.txt
    trunk/treebase-core/db/tb1load/zzFixTreeTypes/data/
    trunk/treebase-core/db/tb1load/zzFixTreeTypes/scripts/
    trunk/treebase-core/db/tb1load/zzFixTreeTypes/scripts/all.sql
    trunk/treebase-core/db/tb1load/zzFixTreeTypes/scripts/drop_tmp.sql
    trunk/treebase-core/db/tb1load/zzFixTreeTypes/scripts/misc.sql
    trunk/treebase-core/db/tb1load/zzFixTreeTypes/scripts/update_treetypes.sql
    trunk/treebase-core/db/tb1load/zzFixTreeTypes/scripts/upload_tmp.sql

Added: trunk/treebase-core/db/tb1load/zzFixTreeTypes/README.txt
===================================================================
--- trunk/treebase-core/db/tb1load/zzFixTreeTypes/README.txt	                        (rev 0)
+++ trunk/treebase-core/db/tb1load/zzFixTreeTypes/README.txt	2010-03-16 19:47:08 UTC (rev 611)
@@ -0,0 +1,21 @@
+Uploading in Step 1e somehow failed to set phylotree.treetype_id, at least for some of the records.  
+
+This directory contains scripts to upload this data from a file dumped by Bill from TB1. 
+The scripts assume that the dump is in data/tree_types.txt 
+
+The original data file was downloaded from
+http://www.treebase.org/treebase/migration/Mar-10/tree_types.txt
+It had to be modified, with Bill's approval: 
+  -   "Simple Tree" --> "single"
+  -   T391c2x7x96c15c45c54 got annotated "simple" (it had a blank)
+  
+  
+To run, in a single transaction,  
+
+$ cd zzFixTreeTypes/scripts 
+$ psql yourdatabase 
+$ \i all.sql 
+
+
+The file misc.sql contains a few useful diagnostic queries. 
+

Added: trunk/treebase-core/db/tb1load/zzFixTreeTypes/scripts/all.sql
===================================================================
--- trunk/treebase-core/db/tb1load/zzFixTreeTypes/scripts/all.sql	                        (rev 0)
+++ trunk/treebase-core/db/tb1load/zzFixTreeTypes/scripts/all.sql	2010-03-16 19:47:08 UTC (rev 611)
@@ -0,0 +1,8 @@
+
+begin transaction; 
+
+\i upload_tmp.sql 
+\i update_treetypes.sql
+\i drop_tmp.sql 
+
+end transaction; 
\ No newline at end of file

Added: trunk/treebase-core/db/tb1load/zzFixTreeTypes/scripts/drop_tmp.sql
===================================================================
--- trunk/treebase-core/db/tb1load/zzFixTreeTypes/scripts/drop_tmp.sql	                        (rev 0)
+++ trunk/treebase-core/db/tb1load/zzFixTreeTypes/scripts/drop_tmp.sql	2010-03-16 19:47:08 UTC (rev 611)
@@ -0,0 +1,3 @@
+
+drop table phtrtype; 
+

Added: trunk/treebase-core/db/tb1load/zzFixTreeTypes/scripts/misc.sql
===================================================================
--- trunk/treebase-core/db/tb1load/zzFixTreeTypes/scripts/misc.sql	                        (rev 0)
+++ trunk/treebase-core/db/tb1load/zzFixTreeTypes/scripts/misc.sql	2010-03-16 19:47:08 UTC (rev 611)
@@ -0,0 +1,27 @@
+-- Number of trees with the null treetype
+select count(*)
+from phylotree
+where treetype_id is null; 
+
+-- The trees with the null treetype
+select phylotree_id, tb1_treeid, treetype_id 
+from phylotree
+where treetype_id is null; 
+
+
+-- All treetypes from the DB "dictionary"
+select * from treetype
+
+-- Distinct treetypes from the uploaded dump
+select treetypename, count(*) from phtrtype group by treetypename
+
+-- See treetype data from PhyloTree and the dump, side-by-side
+
+select p.phylotree_id, p.tb1_treeid, p.treetype_id,
+       Y.tb1_treeid, y.treetypename, 
+       t.treetype_id, t.description
+from phylotree p, phtrtype y, treetype t 
+where p.tb1_treeid = y.tb1_treeid
+  and y.treetypename = lower(t.description)
+
+

Added: trunk/treebase-core/db/tb1load/zzFixTreeTypes/scripts/update_treetypes.sql
===================================================================
--- trunk/treebase-core/db/tb1load/zzFixTreeTypes/scripts/update_treetypes.sql	                        (rev 0)
+++ trunk/treebase-core/db/tb1load/zzFixTreeTypes/scripts/update_treetypes.sql	2010-03-16 19:47:08 UTC (rev 611)
@@ -0,0 +1,10 @@
+-- This assumes that the original Bill's file was modified: 
+--   "Simple Tree" --> "supertree"
+--   T391c2x7x96c15c45c54 is annotated "simple"
+
+update phylotree p 
+   set treetype_id = t.treetype_id
+from phtrtype y, treetype t 
+where p.tb1_treeid = y.tb1_treeid
+  and y.treetypename = lower(t.description)
+

Added: trunk/treebase-core/db/tb1load/zzFixTreeTypes/scripts/upload_tmp.sql
===================================================================
--- trunk/treebase-core/db/tb1load/zzFixTreeTypes/scripts/upload_tmp.sql	                        (rev 0)
+++ trunk/treebase-core/db/tb1load/zzFixTreeTypes/scripts/upload_tmp.sql	2010-03-16 19:47:08 UTC (rev 611)
@@ -0,0 +1,10 @@
+
+-- begin transaction; 
+
+create table phtrtype (
+  tb1_treeid character varying (30), 
+  treetypename character varying (255)
+); 
+
+\copy phtrtype (tb1_treeid, treetypename) from '../data/tree_types.txt'
+


This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.