4 def map_library_ids(c):
6 c.execute("""select id, library_id from samples_library""")
8 surrogate_id = unicode(row[0]) # auto key
9 artificial_id = unicode(row[1]) # the id printed on the library tubes
10 lib_ids[surrogate_id] = artificial_id
13 def convert_experiments_lane(c, lib_ids):
15 Change Library ID in experiments_lane table
17 c.execute('alter table experiments_lane rename to old_experiments_lane')
18 c.execute("""CREATE TABLE "experiments_lane" (
19 "id" integer NOT NULL PRIMARY KEY,
20 "flowcell_id" integer NOT NULL REFERENCES "experiments_flowcell" ("id"),
21 "lane_number" integer NOT NULL,
22 "library_id" varchar(10) NOT NULL REFERENCES "samples_library" ("library_id"),
23 "pM" decimal NOT NULL,
24 "cluster_estimate" integer,
27 c.execute("""select id, flowcell_id, lane_number, library_id, pM, cluster_estimate, comment
28 from old_experiments_lane;""")
32 new_rows.append({'id':row[0], 'flowcell_id':row[1], 'lane_number':row[2],
33 'library_id':lib_ids[unicode(row[3])], 'pM':row[4],
34 'cluster_estimate':row[5],
37 sql = '''insert into experiments_lane
38 (id, flowcell_id, lane_number, library_id, pM, cluster_estimate, comment)
40 (:id, :flowcell_id, :lane_number, :library_id, :pM, :cluster_estimate, :comment)'''
41 c.executemany(sql, new_rows)
43 c.execute('drop table old_experiments_lane')
45 def convert_samples_library_affiliations(c, lib_ids):
47 Change Library ID in experiments_lane table
49 c.execute('alter table samples_library_affiliations rename to old_samples_library_affiliations')
50 c.execute('''CREATE TABLE "samples_library_affiliations" (
51 "id" integer NOT NULL PRIMARY KEY,
52 "library_id" varchar(10) NOT NULL REFERENCES "samples_library" ("id"),
53 "affiliation_id" integer NOT NULL REFERENCES "samples_affiliation" ("id"),
54 UNIQUE ("library_id", "affiliation_id")
57 c.execute("""select id, library_id, affiliation_id
58 from old_samples_library_affiliations;""")
62 new_rows.append({'id':row[0], 'library_id': lib_ids[unicode(row[1])], 'affiliation_id':row[2],})
64 sql = '''insert into samples_library_affiliations
65 (id, library_id, affiliation_id)
67 (:id, :library_id, :affiliation_id)'''
68 c.executemany(sql, new_rows)
70 c.execute('drop table old_samples_library_affiliations;')
72 def convert_samples_library_tags(c, lib_ids):
74 Change Library ID in samples_library_tags table
76 c.execute('alter table samples_library_tags rename to old_samples_library_tags')
77 c.execute('''CREATE TABLE "samples_library_tags" (
78 "id" integer NOT NULL PRIMARY KEY,
79 "library_id" varchar(10) NOT NULL REFERENCES "samples_library" ("id"),
80 "tag_id" integer NOT NULL REFERENCES "samples_tag" ("id"),
81 UNIQUE ("library_id", "tag_id")
84 c.execute("""select id, library_id, tag_id
85 from old_samples_library_tags;""")
89 new_rows.append({'id':row[0], 'library_id': lib_ids[unicode(row[1])], 'tag_id':row[2]})
91 sql = '''insert into samples_library_tags
92 (id, library_id, tag_id)
94 (:id, :library_id, :tag_id)'''
95 c.executemany(sql, new_rows)
97 c.execute('drop table old_samples_library_tags;')
100 def convert_samples_library(c, lib_ids):
102 Change Library ID in samples_library_tags table
104 c.execute('alter table samples_library rename to old_samples_library')
105 c.execute('''CREATE TABLE "samples_library" (
106 "id" varchar(10) NOT NULL PRIMARY KEY,
107 "library_name" varchar(100) NOT NULL UNIQUE,
108 "library_species_id" integer NOT NULL REFERENCES "samples_species" ("id"),
109 "hidden" bool NOT NULL,
110 "account_number" varchar(100),
111 "cell_line_id" integer REFERENCES "samples_cellline" ("id"),
112 "condition_id" integer REFERENCES "samples_condition" ("id"),
113 "antibody_id" integer REFERENCES "samples_antibody" ("id"),
114 "replicate" smallint unsigned NOT NULL,
115 "experiment_type_id" integer NOT NULL REFERENCES "samples_experimenttype" ("id"),
116 "library_type_id" integer REFERENCES "samples_librarytype" ("id"),
117 "creation_date" date,
118 "made_for" varchar(50) NOT NULL,
119 "made_by" varchar(50) NOT NULL,
120 "stopping_point" varchar(25) NOT NULL,
121 "amplified_from_sample_id" varchar(10),
122 "undiluted_concentration" decimal,
123 "successful_pM" decimal,
124 "ten_nM_dilution" bool NOT NULL,
125 "avg_lib_size" integer,
126 "notes" text NOT NULL
130 select library_id, library_name, library_species_id, hidden, account_number, cell_line_id,
131 condition_id, antibody_id, replicate, experiment_type_id, library_type_id,
132 creation_date, made_for, made_by, stopping_point, amplified_from_sample_id,
133 undiluted_concentration, successful_pM, ten_nM_dilution, avg_lib_size, notes
134 from old_samples_library;""")
140 'library_name': row[1],
141 'library_species_id': row[2],
143 'account_number': row[4],
144 'cell_line_id': row[5],
145 'condition_id': row[6],
146 'antibody_id': row[7],
148 'experiment_type_id': row[9],
149 'library_type_id': row[10],
150 'creation_date': row[11],
153 'stopping_point': row[14],
154 'amplified_from_sample_id': row[15],
155 'undiluted_concentration': row[16],
156 'successful_pM': row[17],
157 'ten_nM_dilution': row[18],
158 'avg_lib_size': row[19],
162 sql = '''insert into samples_library
163 (id, library_name, library_species_id, hidden, account_number, cell_line_id,
164 condition_id, antibody_id, replicate, experiment_type_id, library_type_id,
165 creation_date, made_for, made_by, stopping_point, amplified_from_sample_id,
166 undiluted_concentration, successful_pM, ten_nM_dilution, avg_lib_size, notes)
168 (:id, :library_name, :library_species_id, :hidden, :account_number, :cell_line_id,
169 :condition_id, :antibody_id, :replicate, :experiment_type_id, :library_type_id,
170 :creation_date, :made_for, :made_by, :stopping_point, :amplified_from_sample_id,
171 :undiluted_concentration, :successful_pM, :ten_nM_dilution, :avg_lib_size, :notes);
173 c.executemany(sql, new_rows)
175 c.execute('drop table old_samples_library;')
177 def convert_library_id(db_path):
178 conn = sqlite3.connect(db_path)
181 lib_ids = map_library_ids(c)
183 convert_experiments_lane(c, lib_ids)
184 convert_samples_library_affiliations(c, lib_ids)
185 convert_samples_library_tags(c, lib_ids)
186 convert_samples_library(c, lib_ids)
191 usage = '%prog: database_filename'
192 parser = optparse.OptionParser(usage)
195 def main(cmdline=None):
196 parser = make_parser()
197 opts, args = parser.parse_args(cmdline)
199 parser.error('requires path to sqlite database file')
203 convert_library_id(db_path)
205 if __name__ == "__main__":