From: Diane Trout <diane@caltech.edu>
Date: Wed, 12 Aug 2009 22:44:08 +0000 (+0000)
Subject: Converted Brandon's scripts into a single python script that doesn't require
X-Git-Tag: 0.3.0~26
X-Git-Url: http://woldlab.caltech.edu/gitweb/?p=htsworkflow.git;a=commitdiff_plain;h=f11f224dd132264d996d3ba24f7071e72df49351

Converted Brandon's scripts into a single python script that doesn't require
a previous version to run. It may however still require running syncdb.

It also improves on my first attempt of just using straight SQL to split the
flowcell into flowcell + lanes table in that now all the lanes for a single flowcell
are all next to each other.
---

diff --git a/docs/upgrade_from_v0.2.6_to_v0.3.sh b/docs/upgrade_from_v0.2.6_to_v0.3.sh
deleted file mode 100755
index 9469a38..0000000
--- a/docs/upgrade_from_v0.2.6_to_v0.3.sh
+++ /dev/null
@@ -1,10 +0,0 @@
-#!/bin/bash
-export DJANGO_SETTINGS_MODULE=htsworkflow.frontend.settings
-scp king@jumpgate.caltech.edu:/home/www/gaworkflow/fctracker.db /home/king/proj/htsworkflow/trunk/fctracker.db
-cd htsworkflow/frontend/
-python manage.py syncdb
-cd ../..
-./scripts/migrate_to_lane_table.py
-echo "Droping lanes..."
-sqlite3 fctracker.db < scripts/drop_lanes_from_flowcell.sql
-echo "Done."
diff --git a/docs/upgrade_v0.2.6_to_v0.3.py b/docs/upgrade_v0.2.6_to_v0.3.py
new file mode 100644
index 0000000..98772b0
--- /dev/null
+++ b/docs/upgrade_v0.2.6_to_v0.3.py
@@ -0,0 +1,87 @@
+import shutil
+import sqlite3
+import sys
+
+def main(cmdline=None):
+    if len(cmdline) == 1:
+       dest='/tmp/fctracker.db'
+    else:
+      dest = cmdline[1]
+    shutil.copy(cmdline[0], dest)
+    conn = sqlite3.connect(dest)
+    c = conn.cursor()
+    c.execute("""CREATE TEMPORARY TABLE experiments_flowcell_temp (
+    "id" integer NOT NULL PRIMARY KEY,                                     
+    "flowcell_id" varchar(20) NOT NULL UNIQUE,                             
+    "run_date" datetime NOT NULL,                                          
+    "advanced_run" bool NOT NULL,                                          
+    "paired_end" bool NOT NULL,                                            
+    "read_length" integer NOT NULL,                                        
+    "lane_1_library_id" integer NOT NULL REFERENCES "samples_library" ("id"),
+    "lane_2_library_id" integer NOT NULL REFERENCES "samples_library" ("id"),
+    "lane_3_library_id" integer NOT NULL REFERENCES "samples_library" ("id"),
+    "lane_4_library_id" integer NOT NULL REFERENCES "samples_library" ("id"),
+    "lane_5_library_id" integer NOT NULL REFERENCES "samples_library" ("id"),
+    "lane_6_library_id" integer NOT NULL REFERENCES "samples_library" ("id"),
+    "lane_7_library_id" integer NOT NULL REFERENCES "samples_library" ("id"),
+    "lane_8_library_id" integer NOT NULL REFERENCES "samples_library" ("id"),
+    "lane_1_pM" decimal NOT NULL,                                            
+    "lane_2_pM" decimal NOT NULL,                                            
+    "lane_3_pM" decimal NOT NULL,                                            
+    "lane_4_pM" decimal NOT NULL,                                            
+    "lane_5_pM" decimal NOT NULL,                                            
+    "lane_6_pM" decimal NOT NULL,                                            
+    "lane_7_pM" decimal NOT NULL,                                            
+    "lane_8_pM" decimal NOT NULL,                                            
+    "lane_1_cluster_estimate" integer NULL,                                  
+    "lane_2_cluster_estimate" integer NULL,                                  
+    "lane_3_cluster_estimate" integer NULL,                                  
+    "lane_4_cluster_estimate" integer NULL,                                  
+    "lane_5_cluster_estimate" integer NULL,                                  
+    "lane_6_cluster_estimate" integer NULL,                                  
+    "lane_7_cluster_estimate" integer NULL,                                  
+    "lane_8_cluster_estimate" integer NULL,                                  
+    "cluster_station_id" integer NOT NULL REFERENCES "experiments_clusterstation" ("id"),
+    "sequencer_id" integer NOT NULL REFERENCES "experiments_sequencer" ("id"),
+    "notes" text NOT NULL     
+);""")
+    c.execute("""INSERT INTO experiments_flowcell_temp SELECT id,flowcell_id,run_date,advanced_run,paired_end,read_length,lane_1_library_id,lane_2_library_id,lane_3_library_id,lane_4_library_id,lane_5_library_id,lane_6_library_id,lane_7_library_id,lane_8_library_id,lane_1_pM,lane_2_pM,lane_3_pM,lane_4_pM,lane_5_pM,lane_6_pM,lane_7_pM,lane_8_pM,lane_1_cluster_estimate,lane_2_cluster_estimate,lane_3_cluster_estimate,lane_4_cluster_estimate,lane_5_cluster_estimate,lane_6_cluster_estimate,lane_7_cluster_estimate,lane_8_cluster_estimate,cluster_station_id,sequencer_id,notes FROM experiments_flowcell;
+""")
+    c.execute('DROP TABLE experiments_flowcell;')
+    c.execute("""CREATE TABLE experiments_flowcell (                                      
+    "id" integer NOT NULL PRIMARY KEY,                                     
+    "flowcell_id" varchar(20) NOT NULL UNIQUE,                             
+    "run_date" datetime NOT NULL,                                          
+    "advanced_run" bool NOT NULL,                                          
+    "paired_end" bool NOT NULL,                                            
+    "read_length" integer NOT NULL,                                        
+    "cluster_station_id" integer NOT NULL REFERENCES "experiments_clusterstation" ("id"),
+    "sequencer_id" integer NOT NULL REFERENCES "experiments_sequencer" ("id"),
+    "notes" text NOT NULL
+);
+""")    
+    c.execute("""INSERT INTO experiments_flowcell SELECT id,flowcell_id,run_date,advanced_run,paired_end,read_length,cluster_station_id,sequencer_id,notes FROM experiments_flowcell_temp;
+""")    
+    c.execute("""select id from experiments_flowcell;""")
+    flowcell_pks = [ r[0] for r in c ]
+
+    lane_insert = """INSERT INTO experiments_lane (
+  flowcell_id, 
+  lane_number, 
+  library_id,
+  pM,
+  cluster_estimate )
+SELECT id, %(lane)d, lane_%(lane)d_library_id, lane_%(lane)d_pM,
+       lane_%(lane)d_cluster_estimate
+FROM experiments_flowcell_temp
+WHERE id=%(id)d;"""
+
+    for pk in flowcell_pks:
+        for lane in (1,2,3,4,5,6,7,8):
+            c.execute( lane_insert % {'lane': int(lane), 'id': int(pk)} )
+            
+    c.execute('DROP TABLE experiments_flowcell_temp;')
+    conn.commit()
+
+if __name__ == "__main__":
+    main(sys.argv[1:])
diff --git a/scripts/drop_lanes_from_flowcell.sql b/scripts/drop_lanes_from_flowcell.sql
deleted file mode 100644
index 6bfdd57..0000000
--- a/scripts/drop_lanes_from_flowcell.sql
+++ /dev/null
@@ -1,52 +0,0 @@
-BEGIN TRANSACTION;
-CREATE TEMPORARY TABLE experiments_flowcell_temp (
-    "id" integer NOT NULL PRIMARY KEY,                                     
-    "flowcell_id" varchar(20) NOT NULL UNIQUE,                             
-    "run_date" datetime NOT NULL,                                          
-    "advanced_run" bool NOT NULL,                                          
-    "paired_end" bool NOT NULL,                                            
-    "read_length" integer NOT NULL,                                        
-    "lane_1_library_id" integer NOT NULL REFERENCES "samples_library" ("id"),
-    "lane_2_library_id" integer NOT NULL REFERENCES "samples_library" ("id"),
-    "lane_3_library_id" integer NOT NULL REFERENCES "samples_library" ("id"),
-    "lane_4_library_id" integer NOT NULL REFERENCES "samples_library" ("id"),
-    "lane_5_library_id" integer NOT NULL REFERENCES "samples_library" ("id"),
-    "lane_6_library_id" integer NOT NULL REFERENCES "samples_library" ("id"),
-    "lane_7_library_id" integer NOT NULL REFERENCES "samples_library" ("id"),
-    "lane_8_library_id" integer NOT NULL REFERENCES "samples_library" ("id"),
-    "lane_1_pM" decimal NOT NULL,                                            
-    "lane_2_pM" decimal NOT NULL,                                            
-    "lane_3_pM" decimal NOT NULL,                                            
-    "lane_4_pM" decimal NOT NULL,                                            
-    "lane_5_pM" decimal NOT NULL,                                            
-    "lane_6_pM" decimal NOT NULL,                                            
-    "lane_7_pM" decimal NOT NULL,                                            
-    "lane_8_pM" decimal NOT NULL,                                            
-    "lane_1_cluster_estimate" integer NULL,                                  
-    "lane_2_cluster_estimate" integer NULL,                                  
-    "lane_3_cluster_estimate" integer NULL,                                  
-    "lane_4_cluster_estimate" integer NULL,                                  
-    "lane_5_cluster_estimate" integer NULL,                                  
-    "lane_6_cluster_estimate" integer NULL,                                  
-    "lane_7_cluster_estimate" integer NULL,                                  
-    "lane_8_cluster_estimate" integer NULL,                                  
-    "cluster_station_id" integer NOT NULL REFERENCES "experiments_clusterstation" ("id"),
-    "sequencer_id" integer NOT NULL REFERENCES "experiments_sequencer" ("id"),
-    "notes" text NOT NULL     
-);
-INSERT INTO experiments_flowcell_temp SELECT id,flowcell_id,run_date,advanced_run,paired_end,read_length,lane_1_library_id,lane_2_library_id,lane_3_library_id,lane_4_library_id,lane_5_library_id,lane_6_library_id,lane_7_library_id,lane_8_library_id,lane_1_pM,lane_2_pM,lane_3_pM,lane_4_pM,lane_5_pM,lane_6_pM,lane_7_pM,lane_8_pM,lane_1_cluster_estimate,lane_2_cluster_estimate,lane_3_cluster_estimate,lane_4_cluster_estimate,lane_5_cluster_estimate,lane_6_cluster_estimate,lane_7_cluster_estimate,lane_8_cluster_estimate,cluster_station_id,sequencer_id,notes FROM experiments_flowcell;
-DROP TABLE experiments_flowcell;
-CREATE TABLE experiments_flowcell (                                      
-    "id" integer NOT NULL PRIMARY KEY,                                     
-    "flowcell_id" varchar(20) NOT NULL UNIQUE,                             
-    "run_date" datetime NOT NULL,                                          
-    "advanced_run" bool NOT NULL,                                          
-    "paired_end" bool NOT NULL,                                            
-    "read_length" integer NOT NULL,                                        
-    "cluster_station_id" integer NOT NULL REFERENCES "experiments_clusterstation" ("id"),
-    "sequencer_id" integer NOT NULL REFERENCES "experiments_sequencer" ("id"),
-    "notes" text NOT NULL
-);
-INSERT INTO experiments_flowcell SELECT id,flowcell_id,run_date,advanced_run,paired_end,read_length,cluster_station_id,sequencer_id,notes FROM experiments_flowcell_temp;
-DROP TABLE experiments_flowcell_temp;
-COMMIT;
diff --git a/scripts/migrate_to_lane_table.py b/scripts/migrate_to_lane_table.py
deleted file mode 100755
index 7b67c56..0000000
--- a/scripts/migrate_to_lane_table.py
+++ /dev/null
@@ -1,141 +0,0 @@
-#!/usr/bin/env python
-
-from htsworkflow.frontend.experiments.models import FlowCell, Lane
-
-
-if __name__ == '__main__':
-    
-    print "Migration starting..."
-    
-    #Get all flowcells
-    for flowcell in FlowCell.objects.all():
-        
-        ##################
-        # Lane 1
-        lane1 = Lane()
-        
-        # ForeignKey Links
-        lane1.flowcell = flowcell
-        lane1.library = flowcell.lane_1_library
-        
-        # Meta Data
-        lane1.lane_number = 1
-        lane1.pM = flowcell.lane_1_pM
-        lane1.cluster_estimate = flowcell.lane_1_cluster_estimate
-        
-        # Save
-        lane1.save()
-        
-        ##################
-        # Lane 2
-        lane2 = Lane()
-        
-        # ForeignKey Links
-        lane2.flowcell = flowcell
-        lane2.library = flowcell.lane_2_library
-        
-        # Meta Data
-        lane2.lane_number = 2
-        lane2.pM = flowcell.lane_2_pM
-        lane2.cluster_estimate = flowcell.lane_2_cluster_estimate
-        
-        # Save
-        lane2.save()
-        
-        ##################
-        # Lane 3
-        lane3 = Lane()
-        
-        # ForeignKey Links
-        lane3.flowcell = flowcell
-        lane3.library = flowcell.lane_3_library
-        
-        # Meta Data
-        lane3.lane_number = 3
-        lane3.pM = flowcell.lane_3_pM
-        lane3.cluster_estimate = flowcell.lane_3_cluster_estimate
-        
-        # Save
-        lane3.save()
-        
-        ##################
-        # Lane 4
-        lane4 = Lane()
-        
-        # ForeignKey Links
-        lane4.flowcell = flowcell
-        lane4.library = flowcell.lane_4_library
-        
-        # Meta Data
-        lane4.lane_number = 4
-        lane4.pM = flowcell.lane_4_pM
-        lane4.cluster_estimate = flowcell.lane_4_cluster_estimate
-        
-        # Save
-        lane4.save()
-        
-        ##################
-        # Lane 5
-        lane5 = Lane()
-        
-        # ForeignKey Links
-        lane5.flowcell = flowcell
-        lane5.library = flowcell.lane_5_library
-        
-        # Meta Data
-        lane5.lane_number = 5
-        lane5.pM = flowcell.lane_5_pM
-        lane5.cluster_estimate = flowcell.lane_5_cluster_estimate
-        
-        # Save
-        lane5.save()
-        
-        ##################
-        # Lane 6
-        lane6 = Lane()
-        
-        # ForeignKey Links
-        lane6.flowcell = flowcell
-        lane6.library = flowcell.lane_6_library
-        
-        # Meta Data
-        lane6.lane_number = 6
-        lane6.pM = flowcell.lane_6_pM
-        lane6.cluster_estimate = flowcell.lane_6_cluster_estimate
-        
-        # Save
-        lane6.save()
-        
-        ##################
-        # Lane 7
-        lane7 = Lane()
-        
-        # ForeignKey Links
-        lane7.flowcell = flowcell
-        lane7.library = flowcell.lane_7_library
-        
-        # Meta Data
-        lane7.lane_number = 7
-        lane7.pM = flowcell.lane_7_pM
-        lane7.cluster_estimate = flowcell.lane_7_cluster_estimate
-        
-        # Save
-        lane7.save()
-        
-        ##################
-        # Lane 8
-        lane8 = Lane()
-        
-        # ForeignKey Links
-        lane8.flowcell = flowcell
-        lane8.library = flowcell.lane_8_library
-        
-        # Meta Data
-        lane8.lane_number = 8
-        lane8.pM = flowcell.lane_8_pM
-        lane8.cluster_estimate = flowcell.lane_8_cluster_estimate
-        
-        # Save
-        lane8.save()
-
-    print "Migration Complete."
\ No newline at end of file