5 def main(cmdline=None):
7 dest='/tmp/fctracker.db'
10 shutil.copy(cmdline[0], dest)
11 conn = sqlite3.connect(dest)
13 c.execute("""CREATE TEMPORARY TABLE experiments_flowcell_temp (
14 "id" integer NOT NULL PRIMARY KEY,
15 "flowcell_id" varchar(20) NOT NULL UNIQUE,
16 "run_date" datetime NOT NULL,
17 "advanced_run" bool NOT NULL,
18 "paired_end" bool NOT NULL,
19 "read_length" integer NOT NULL,
20 "lane_1_library_id" integer NOT NULL REFERENCES "samples_library" ("id"),
21 "lane_2_library_id" integer NOT NULL REFERENCES "samples_library" ("id"),
22 "lane_3_library_id" integer NOT NULL REFERENCES "samples_library" ("id"),
23 "lane_4_library_id" integer NOT NULL REFERENCES "samples_library" ("id"),
24 "lane_5_library_id" integer NOT NULL REFERENCES "samples_library" ("id"),
25 "lane_6_library_id" integer NOT NULL REFERENCES "samples_library" ("id"),
26 "lane_7_library_id" integer NOT NULL REFERENCES "samples_library" ("id"),
27 "lane_8_library_id" integer NOT NULL REFERENCES "samples_library" ("id"),
28 "lane_1_pM" decimal NOT NULL,
29 "lane_2_pM" decimal NOT NULL,
30 "lane_3_pM" decimal NOT NULL,
31 "lane_4_pM" decimal NOT NULL,
32 "lane_5_pM" decimal NOT NULL,
33 "lane_6_pM" decimal NOT NULL,
34 "lane_7_pM" decimal NOT NULL,
35 "lane_8_pM" decimal NOT NULL,
36 "lane_1_cluster_estimate" integer NULL,
37 "lane_2_cluster_estimate" integer NULL,
38 "lane_3_cluster_estimate" integer NULL,
39 "lane_4_cluster_estimate" integer NULL,
40 "lane_5_cluster_estimate" integer NULL,
41 "lane_6_cluster_estimate" integer NULL,
42 "lane_7_cluster_estimate" integer NULL,
43 "lane_8_cluster_estimate" integer NULL,
44 "cluster_station_id" integer NOT NULL REFERENCES "experiments_clusterstation" ("id"),
45 "sequencer_id" integer NOT NULL REFERENCES "experiments_sequencer" ("id"),
48 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;
50 c.execute('DROP TABLE experiments_flowcell;')
51 c.execute("""CREATE TABLE experiments_flowcell (
52 "id" integer NOT NULL PRIMARY KEY,
53 "flowcell_id" varchar(20) NOT NULL UNIQUE,
54 "run_date" datetime NOT NULL,
55 "advanced_run" bool NOT NULL,
56 "paired_end" bool NOT NULL,
57 "read_length" integer NOT NULL,
58 "cluster_station_id" integer NOT NULL REFERENCES "experiments_clusterstation" ("id"),
59 "sequencer_id" integer NOT NULL REFERENCES "experiments_sequencer" ("id"),
63 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;
65 c.execute("""select id from experiments_flowcell;""")
66 flowcell_pks = [ r[0] for r in c ]
68 lane_insert = """INSERT INTO experiments_lane (
74 SELECT id, %(lane)d, lane_%(lane)d_library_id, lane_%(lane)d_pM,
75 lane_%(lane)d_cluster_estimate
76 FROM experiments_flowcell_temp
79 for pk in flowcell_pks:
80 for lane in (1,2,3,4,5,6,7,8):
81 c.execute( lane_insert % {'lane': int(lane), 'id': int(pk)} )
83 c.execute('DROP TABLE experiments_flowcell_temp;')
86 if __name__ == "__main__":