Menu
▾
▴
[Treebase-guts] SF.net SVN: treebase:[554] trunk/treebase-core/db/cleaning
|
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.
|
