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.
+++ /dev/null
-#!/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."
--- /dev/null
+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:])
+++ /dev/null
-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;
+++ /dev/null
-#!/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