From f11f224dd132264d996d3ba24f7071e72df49351 Mon Sep 17 00:00:00 2001 From: Diane Trout Date: Wed, 12 Aug 2009 22:44:08 +0000 Subject: [PATCH] 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. --- docs/upgrade_from_v0.2.6_to_v0.3.sh | 10 -- .../upgrade_v0.2.6_to_v0.3.py | 53 +++++-- scripts/migrate_to_lane_table.py | 141 ------------------ 3 files changed, 44 insertions(+), 160 deletions(-) delete mode 100755 docs/upgrade_from_v0.2.6_to_v0.3.sh rename scripts/drop_lanes_from_flowcell.sql => docs/upgrade_v0.2.6_to_v0.3.py (62%) delete mode 100755 scripts/migrate_to_lane_table.py 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/scripts/drop_lanes_from_flowcell.sql b/docs/upgrade_v0.2.6_to_v0.3.py similarity index 62% rename from scripts/drop_lanes_from_flowcell.sql rename to docs/upgrade_v0.2.6_to_v0.3.py index 6bfdd57..98772b0 100644 --- a/scripts/drop_lanes_from_flowcell.sql +++ b/docs/upgrade_v0.2.6_to_v0.3.py @@ -1,5 +1,16 @@ -BEGIN TRANSACTION; -CREATE TEMPORARY TABLE experiments_flowcell_temp ( +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, @@ -33,10 +44,11 @@ CREATE TEMPORARY TABLE experiments_flowcell_temp ( "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 ( +);""") + 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, @@ -47,6 +59,29 @@ CREATE TABLE experiments_flowcell ( "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; +""") + 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/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 -- 2.30.2