5 def main(cmdline=None):
7 dest='/tmp/fctracker.db'
10 shutil.copy(cmdline[0], dest)
11 conn = sqlite3.connect(dest)
13 c.execute('drop table fctracker_elandresult');
14 c.execute('''CREATE TABLE "experiments_clusterstation" (
15 "id" integer NOT NULL PRIMARY KEY,
16 "name" varchar(50) NOT NULL UNIQUE);''')
17 c.execute('''INSERT INTO experiments_clusterstation (name) values ("station");''')
18 c.execute('''CREATE TABLE "experiments_sequencer" (
19 "id" integer NOT NULL PRIMARY KEY,
20 "name" varchar(50) NOT NULL UNIQUE);''')
21 c.execute('''INSERT INTO experiments_sequencer (name) values ("HWI-EAS229");''')
23 c.execute('''CREATE TABLE "experiments_flowcell" (
24 "id" integer NOT NULL PRIMARY KEY,
25 "flowcell_id" varchar(20) NOT NULL UNIQUE,
26 "run_date" datetime NOT NULL,
27 "advanced_run" bool NOT NULL,
28 "paired_end" bool NOT NULL,
29 "read_length" integer NOT NULL,
30 "lane_1_library_id" integer NOT NULL REFERENCES "samples_library" ("id"),
31 "lane_2_library_id" integer NOT NULL REFERENCES "samples_library" ("id"),
32 "lane_3_library_id" integer NOT NULL REFERENCES "samples_library" ("id"),
33 "lane_4_library_id" integer NOT NULL REFERENCES "samples_library" ("id"),
34 "lane_5_library_id" integer NOT NULL REFERENCES "samples_library" ("id"),
35 "lane_6_library_id" integer NOT NULL REFERENCES "samples_library" ("id"),
36 "lane_7_library_id" integer NOT NULL REFERENCES "samples_library" ("id"),
37 "lane_8_library_id" integer NOT NULL REFERENCES "samples_library" ("id"),
38 "lane_1_pM" decimal NOT NULL,
39 "lane_2_pM" decimal NOT NULL,
40 "lane_3_pM" decimal NOT NULL,
41 "lane_4_pM" decimal NOT NULL,
42 "lane_5_pM" decimal NOT NULL,
43 "lane_6_pM" decimal NOT NULL,
44 "lane_7_pM" decimal NOT NULL,
45 "lane_8_pM" decimal NOT NULL,
46 "lane_1_cluster_estimate" integer NULL,
47 "lane_2_cluster_estimate" integer NULL,
48 "lane_3_cluster_estimate" integer NULL,
49 "lane_4_cluster_estimate" integer NULL,
50 "lane_5_cluster_estimate" integer NULL,
51 "lane_6_cluster_estimate" integer NULL,
52 "lane_7_cluster_estimate" integer NULL,
53 "lane_8_cluster_estimate" integer NULL,
54 "cluster_station_id" integer NOT NULL REFERENCES "experiments_clusterstation" ("id"),
55 "sequencer_id" integer NOT NULL REFERENCES "experiments_sequencer" ("id"),
58 c.execute('''insert into experiments_flowcell
59 (id, flowcell_id, run_date, advanced_run, paired_end, read_length,
60 lane_1_library_id, lane_2_library_id, lane_3_library_id,
61 lane_4_library_id, lane_5_library_id, lane_6_library_id,
62 lane_7_library_id, lane_8_library_id, lane_1_pm,
63 lane_2_pM, lane_3_pM, lane_4_pM, lane_5_pM, lane_6_pM,
64 lane_7_pM, lane_8_pM, lane_1_cluster_estimate,
65 lane_2_cluster_estimate, lane_3_cluster_estimate,
66 lane_4_cluster_estimate, lane_5_cluster_estimate,
67 lane_6_cluster_estimate, lane_7_cluster_estimate,
68 lane_8_cluster_estimate, cluster_station_id, sequencer_id,
71 id, flowcell_id, run_date, advanced_run, paired_end, read_length,
72 lane_1_library_id, lane_2_library_id, lane_3_library_id,
73 lane_4_library_id, lane_5_library_id, lane_6_library_id,
74 lane_7_library_id, lane_8_library_id, lane_1_pm,
75 lane_2_pM, lane_3_pM, lane_4_pM, lane_5_pM, lane_6_pM,
76 lane_7_pM, lane_8_pM, lane_1_cluster_estimate,
77 lane_2_cluster_estimate, lane_3_cluster_estimate,
78 lane_4_cluster_estimate, lane_5_cluster_estimate,
79 lane_6_cluster_estimate, lane_7_cluster_estimate,
80 lane_8_cluster_estimate, 1, 1,
81 notes from fctracker_flowcell;''')
82 c.execute('''drop table fctracker_flowcell;''')
84 # create samples.cellline
85 c.execute('''CREATE TABLE "samples_cellline" (
86 "id" integer NOT NULL PRIMARY KEY,
87 "cellline_name" varchar(100) NOT NULL UNIQUE,
88 "nickname" varchar(20) NULL,
89 "notes" text NOT NULL);''')
90 c.execute('''insert into samples_cellline (cellline_name,notes) values("Unknown","Unknown");''')
92 # Create samples.condition
93 c.execute('''CREATE TABLE "samples_condition" (
94 "id" integer NOT NULL PRIMARY KEY,
95 "condition_name" varchar(2000) NOT NULL UNIQUE,
96 "nickname" varchar(20) NULL,
97 "notes" text NOT NULL);''')
98 c.execute('''insert into samples_condition (condition_name,notes) values("Unknown","Unknown");''')
100 # create samples.experiment type
101 c.execute('''CREATE TABLE "samples_experimenttype" (
102 "id" integer NOT NULL PRIMARY KEY,
103 "name" varchar(50) NOT NULL UNIQUE);''')
104 for et in [ ('Unknown',),
111 c.execute('insert into samples_experimenttype (name) values (?)', et)
113 # create samples.library
114 c.execute('''CREATE TABLE "samples_library" (
115 "id" integer NOT NULL PRIMARY KEY,
116 "library_id" varchar(30) NOT NULL,
117 "library_name" varchar(100) NOT NULL UNIQUE,
118 "library_species_id" integer NOT NULL REFERENCES "samples_species" ("id"),
119 "cell_line_id" integer NOT NULL REFERENCES "samples_cellline" ("id"),
120 "condition_id" integer NOT NULL REFERENCES "samples_condition" ("id"),
121 "antibody_id" integer NULL REFERENCES "samples_antibody" ("id"),
122 "replicate" smallint unsigned NOT NULL,
123 "experiment_type_id" NOT NULL REFERENCES "samples_experimenttype" ("id"),
124 "creation_date" date NULL,
125 "made_for" varchar(50) NOT NULL,
126 "made_by" varchar(50) NOT NULL,
127 "stopping_point" varchar(25) NOT NULL,
128 "amplified_from_sample_id" integer NULL,
129 "undiluted_concentration" decimal NULL,
130 "successful_pM" decimal NULL,
131 "ten_nM_dilution" bool NOT NULL,
132 "avg_lib_size" integer NULL,
133 "notes" text NOT NULL);''')
134 c.execute('''INSERT INTO samples_library
135 (id,library_id,library_name,library_species_id, experiment_type_id,
136 cell_line_id,condition_id,replicate,made_by,creation_date,
137 made_for,stopping_point,amplified_from_sample_id,
138 undiluted_concentration,ten_nM_dilution,successful_pM,
140 select library_id,library_id,library_name,library_species_id, 1,
141 1, 1, 1, made_by,creation_date,
142 made_for,stopping_point,amplified_from_sample_id,
143 undiluted_concentration,ten_nM_dilution,successful_pM,
144 0,notes from fctracker_library;''');
145 c.execute('select id from samples_experimenttype where name = "RNA-seq";')
146 rna_seq_id = list(c)[0]
147 c.execute('''update samples_library set experiment_type_id=? where library_id in (select library_id from fctracker_library where RNASeq = 1);''', rna_seq_id)
148 #c.execute('''drop table fctracker_library;''')
149 # add many to many tables
150 c.execute('''CREATE TABLE "samples_library_affiliations" (
151 "id" integer NOT NULL PRIMARY KEY,
152 "library_id" integer NOT NULL REFERENCES "samples_library" ("id"),
153 "affiliation_id" integer NOT NULL REFERENCES "samples_affiliation" ("id"),
154 UNIQUE ("library_id", "affiliation_id"));''')
156 c.execute('''CREATE TABLE "samples_library_tags" (
157 "id" integer NOT NULL PRIMARY KEY,
158 "library_id" integer NOT NULL REFERENCES "samples_library" ("id"),
159 "tag_id" integer NOT NULL REFERENCES "samples_tag" ("id"),
160 UNIQUE ("library_id", "tag_id"));''')
165 c.execute('''CREATE TABLE "samples_species" (
166 "id" integer NOT NULL PRIMARY KEY,
167 "scientific_name" varchar(256) NOT NULL,
168 "common_name" varchar(256) NOT NULL);''')
169 c.execute('''insert into samples_species
170 (id, scientific_name, common_name)
172 id, scientific_name, common_name
173 from fctracker_species;''')
174 c.execute('''drop table fctracker_species''')
177 if __name__ == "__main__":