root / mm9 / makeDb.sql @ 8c368a17
History | View | Annotate | Download (9.4 kB)
1 | 8c368a17 | Daofeng Li | -- -------------------
|
---|---|---|---|
2 | 8c368a17 | Daofeng Li | -- --
|
3 | 8c368a17 | Daofeng Li | -- mm9heatmap --
|
4 | 8c368a17 | Daofeng Li | -- --
|
5 | 8c368a17 | Daofeng Li | -- -------------------
|
6 | 8c368a17 | Daofeng Li | drop table if exists config; |
7 | 8c368a17 | Daofeng Li | create table config ( |
8 | 8c368a17 | Daofeng Li | bbiPath text not null, |
9 | 8c368a17 | Daofeng Li | seqPath text null, |
10 | 8c368a17 | Daofeng Li | defaultTracks text null, |
11 | 8c368a17 | Daofeng Li | defaultMdcategory varchar(255) not null, |
12 | 8c368a17 | Daofeng Li | defaultGenelist text not null, |
13 | 8c368a17 | Daofeng Li | defaultCustomtracks text not null, |
14 | 8c368a17 | Daofeng Li | defaultPosition varchar(255) not null, |
15 | 8c368a17 | Daofeng Li | defaultDataset varchar(255) not null, |
16 | 8c368a17 | Daofeng Li | defaultDecor text null, |
17 | 8c368a17 | Daofeng Li | defaultScaffold text null, |
18 | 8c368a17 | Daofeng Li | ideogram_wiggle1 varchar(255) null, |
19 | 8c368a17 | Daofeng Li | ideogram_wiggle2 varchar(255) null, |
20 | 8c368a17 | Daofeng Li | hasGene boolean not null, |
21 | 8c368a17 | Daofeng Li | allowJuxtaposition boolean not null, |
22 | 8c368a17 | Daofeng Li | keggSpeciesCode varchar(255) not null, |
23 | 8c368a17 | Daofeng Li | information text not null, |
24 | 8c368a17 | Daofeng Li | runmode tinyint not null, |
25 | 8c368a17 | Daofeng Li | initmatplot boolean not null |
26 | 8c368a17 | Daofeng Li | ); |
27 | 8c368a17 | Daofeng Li | insert into config values( |
28 | 8c368a17 | Daofeng Li | "/srv/epgg/data/data/subtleKnife/mm9/",
|
29 | 8c368a17 | Daofeng Li | "/srv/epgg/data/data/subtleKnife/seq/mm9.gz",
|
30 | 8c368a17 | Daofeng Li | \N,
|
31 | 8c368a17 | Daofeng Li | -- "GSM929716_1,GSM915181_1,GSM970852_6,GSM900187_4,GSM900195_3,GSM970851_1,GSM915163_1,GSM912935_1,GSM946530_2,GSM915160_1,GSM918746_1,GSM918744_1,GSM918712_1,GSM970874_3,GSM915164_1,GSM923589_1,GSM923584_2",
|
32 | 8c368a17 | Daofeng Li | "Sample,Assay,Institution",
|
33 | 8c368a17 | Daofeng Li | "CYP4Z1\\nCYP2A7\\nCYP2A6\\nCYP3A4\\nCYP1A1\\nCYP4V2\\nCYP51A1\\nCYP2C19\\nCYP26B1\\nCYP11B2\\nCYP24A1\\nCYP4B1\\nCYP2C8",
|
34 | 8c368a17 | Daofeng Li | "3,http://vizhub.wustl.edu/hubSample/mm9/wgEncodeLicrHistoneBatInputMAdult24wksC57bl6StdSig.gz,1,http://vizhub.wustl.edu/hubSample/mm9/bed.gz,5,http://vizhub.wustl.edu/hubSample/mm9/wgEncodeLicrHistoneBatInputMAdult24wksC57bl6StdAlnRep2.gz,100,http://vizhub.wustl.edu/hubSample/mm9/hub.txt",
|
35 | 8c368a17 | Daofeng Li | "chr6,51999773,chr6,52368420",
|
36 | 8c368a17 | Daofeng Li | "longrange,encode",
|
37 | 8c368a17 | Daofeng Li | "knownGene,rmsk_ensemble",
|
38 | 8c368a17 | Daofeng Li | "chr1,chr2,chr3,chr4,chr5,chr6,chr7,chr8,chr9,chr10,chr11,chr12,chr13,chr14,chr15,chr16,chr17,chr18,chr19,chrX,chrY,chrM",
|
39 | 8c368a17 | Daofeng Li | \N,\N, |
40 | 8c368a17 | Daofeng Li | true,
|
41 | 8c368a17 | Daofeng Li | true,
|
42 | 8c368a17 | Daofeng Li | "mmu",
|
43 | 8c368a17 | Daofeng Li | "Assembly version|mm9|Sequence source|<a href=http://hgdownload.cse.ucsc.edu/goldenPath/mm9/bigZips/ target=_blank>UCSC browser</a>|Date parsed|August 1, 2011|Chromosomes|22|Misc|13|Total bases|3,137,144,693|Logo art|<a href=http://free-extras.com/images/mouse-8552.htm target=_blank>link</a>",
|
44 | 8c368a17 | Daofeng Li | 0,
|
45 | 8c368a17 | Daofeng Li | false
|
46 | 8c368a17 | Daofeng Li | ); |
47 | 8c368a17 | Daofeng Li | |
48 | 8c368a17 | Daofeng Li | |
49 | 8c368a17 | Daofeng Li | -- grouping types on genomic features
|
50 | 8c368a17 | Daofeng Li | -- table name defined in macro: TBN_GF_GRP
|
51 | 8c368a17 | Daofeng Li | drop table if exists gfGrouping; |
52 | 8c368a17 | Daofeng Li | create table gfGrouping ( |
53 | 8c368a17 | Daofeng Li | id TINYINT not null primary key, |
54 | 8c368a17 | Daofeng Li | name char(50) not null |
55 | 8c368a17 | Daofeng Li | ); |
56 | 8c368a17 | Daofeng Li | insert into gfGrouping values (2, "Genes"); |
57 | 8c368a17 | Daofeng Li | -- insert into gfGrouping values (3, "non-coding RNA");
|
58 | 8c368a17 | Daofeng Li | insert into gfGrouping values (4, "RepeatMasker"); |
59 | 8c368a17 | Daofeng Li | insert into gfGrouping values (6, "Sequence conservation"); |
60 | 8c368a17 | Daofeng Li | insert into gfGrouping values (5, "Others"); |
61 | 8c368a17 | Daofeng Li | insert into gfGrouping values (7, "Mappability (ENCODE)"); |
62 | 8c368a17 | Daofeng Li | |
63 | 8c368a17 | Daofeng Li | |
64 | 8c368a17 | Daofeng Li | drop table if exists decorInfo; |
65 | 8c368a17 | Daofeng Li | create table decorInfo ( |
66 | 8c368a17 | Daofeng Li | name char(50) not null primary key, |
67 | 8c368a17 | Daofeng Li | printname char(100) not null, |
68 | 8c368a17 | Daofeng Li | parent char(50) null, |
69 | 8c368a17 | Daofeng Li | grp tinyint not null, |
70 | 8c368a17 | Daofeng Li | fileType tinyint not null, |
71 | 8c368a17 | Daofeng Li | hasStruct tinyint null, |
72 | 8c368a17 | Daofeng Li | queryUrl varchar(255) null |
73 | 8c368a17 | Daofeng Li | ); |
74 | 8c368a17 | Daofeng Li | load data local infile 'decorInfo' into table decorInfo; |
75 | 8c368a17 | Daofeng Li | |
76 | 8c368a17 | Daofeng Li | /*
|
77 | 8c368a17 | Daofeng Li | insert into decorInfo values('ccdsGene','CCDS genes',\N,2,0,1,'http://www.ncbi.nlm.nih.gov/CCDS/CcdsBrowse.cgi?REQUEST=CCDS&BUILDS=ALLBUILDS&DATA='); |
78 | 8c368a17 | Daofeng Li | insert into decorInfo values('ccdsGeneexons','exons (CCDS genes)','ccdsGene',2,0,0,\N); |
79 | 8c368a17 | Daofeng Li | insert into decorInfo values('ccdsGeneintrons','introns (CCDS genes)','ccdsGene',2,0,0,\N); |
80 | 8c368a17 | Daofeng Li | insert into decorInfo values('vegaGene','Vega genes',\N,2,0,1,'http://vega.sanger.ac.uk/Mus_musculus/geneview?gene='); |
81 | 8c368a17 | Daofeng Li | insert into decorInfo values('vegaGenepromoter','promoters (Vega genes)','vegaGene',2,0,0,\N); |
82 | 8c368a17 | Daofeng Li | insert into decorInfo values('vegaGeneutr3',"3' UTRs (Vega genes)",'vegaGene',2,0,0,\N); |
83 | 8c368a17 | Daofeng Li | insert into decorInfo values('vegaGeneutr5',"5' UTRs (Vega genes)",'vegaGene',2,0,0,\N); |
84 | 8c368a17 | Daofeng Li | insert into decorInfo values('vegaGeneexons','exons (Vega genes)','vegaGene',2,0,0,\N); |
85 | 8c368a17 | Daofeng Li | insert into decorInfo values('vegaGeneintrons','introns (Vega genes)','vegaGene',2,0,0,\N); |
86 | 8c368a17 | Daofeng Li | */ |
87 | 8c368a17 | Daofeng Li | |
88 | 8c368a17 | Daofeng Li | drop table if exists track2Label; |
89 | 8c368a17 | Daofeng Li | create table track2Label ( |
90 | 8c368a17 | Daofeng Li | name varchar(255) not null primary key, |
91 | 8c368a17 | Daofeng Li | label text null |
92 | 8c368a17 | Daofeng Li | ); |
93 | 8c368a17 | Daofeng Li | load data local infile 'track2Label_encode' into table track2Label; |
94 | 8c368a17 | Daofeng Li | load data local infile 'track2Label_longrange' into table track2Label; |
95 | 8c368a17 | Daofeng Li | |
96 | 8c368a17 | Daofeng Li | drop table if exists track2ProcessInfo; |
97 | 8c368a17 | Daofeng Li | create table track2ProcessInfo ( |
98 | 8c368a17 | Daofeng Li | name varchar(255) not null primary key, |
99 | 8c368a17 | Daofeng Li | detail text null |
100 | 8c368a17 | Daofeng Li | ); |
101 | 8c368a17 | Daofeng Li | load data local infile 'track2ProcessInfo_encode' into table track2ProcessInfo; |
102 | 8c368a17 | Daofeng Li | load data local infile 'track2ProcessInfo_longrange' into table track2ProcessInfo; |
103 | 8c368a17 | Daofeng Li | |
104 | 8c368a17 | Daofeng Li | drop table if exists track2BamInfo; |
105 | 8c368a17 | Daofeng Li | create table track2BamInfo ( |
106 | 8c368a17 | Daofeng Li | name varchar(255) not null, |
107 | 8c368a17 | Daofeng Li | bamfile varchar(255) not null, |
108 | 8c368a17 | Daofeng Li | bamfilelabel varchar(255) not null |
109 | 8c368a17 | Daofeng Li | ); |
110 | 8c368a17 | Daofeng Li | load data local infile "track2BamInfo" into table track2BamInfo; |
111 | 8c368a17 | Daofeng Li | |
112 | 8c368a17 | Daofeng Li | drop table if exists track2Detail; |
113 | 8c368a17 | Daofeng Li | create table track2Detail ( |
114 | 8c368a17 | Daofeng Li | name varchar(255) not null primary key, |
115 | 8c368a17 | Daofeng Li | detail text null |
116 | 8c368a17 | Daofeng Li | ); |
117 | 8c368a17 | Daofeng Li | load data local infile 'track2Detail_decor' into table track2Detail; |
118 | 8c368a17 | Daofeng Li | load data local infile 'track2Detail_encode' into table track2Detail; |
119 | 8c368a17 | Daofeng Li | load data local infile 'track2Detail_longrange' into table track2Detail; |
120 | 8c368a17 | Daofeng Li | |
121 | 8c368a17 | Daofeng Li | drop table if exists track2GEO; |
122 | 8c368a17 | Daofeng Li | create table track2GEO ( |
123 | 8c368a17 | Daofeng Li | name varchar(255) not null primary key, |
124 | 8c368a17 | Daofeng Li | geo char(20) not null |
125 | 8c368a17 | Daofeng Li | ); |
126 | 8c368a17 | Daofeng Li | load data local infile 'track2GEO_encode' into table track2GEO; |
127 | 8c368a17 | Daofeng Li | load data local infile 'track2GEO_longrange' into table track2GEO; |
128 | 8c368a17 | Daofeng Li | |
129 | 8c368a17 | Daofeng Li | drop table if exists track2VersionInfo; |
130 | 8c368a17 | Daofeng Li | create table track2VersionInfo ( |
131 | 8c368a17 | Daofeng Li | name varchar(255) not null primary key, |
132 | 8c368a17 | Daofeng Li | info varchar(255) not null |
133 | 8c368a17 | Daofeng Li | ); |
134 | 8c368a17 | Daofeng Li | load data local infile 'track2VersionInfo_encode' into table track2VersionInfo; |
135 | 8c368a17 | Daofeng Li | |
136 | 8c368a17 | Daofeng Li | -- new trackDetail end here
|
137 | 8c368a17 | Daofeng Li | |
138 | 8c368a17 | Daofeng Li | drop table if exists track2Annotation; |
139 | 8c368a17 | Daofeng Li | create table track2Annotation ( |
140 | 8c368a17 | Daofeng Li | name varchar(255) not null primary key, |
141 | 8c368a17 | Daofeng Li | attridx varchar(255) not null |
142 | 8c368a17 | Daofeng Li | ); |
143 | 8c368a17 | Daofeng Li | load data local infile "track2Annotation_encode" into table track2Annotation; |
144 | 8c368a17 | Daofeng Li | load data local infile "track2Annotation_longrange" into table track2Annotation; |
145 | 8c368a17 | Daofeng Li | |
146 | 8c368a17 | Daofeng Li | drop table if exists track2Ft; |
147 | 8c368a17 | Daofeng Li | create table track2Ft ( |
148 | 8c368a17 | Daofeng Li | name varchar(255) not null primary key, |
149 | 8c368a17 | Daofeng Li | ft tinyint not null |
150 | 8c368a17 | Daofeng Li | ); |
151 | 8c368a17 | Daofeng Li | load data local infile "track2Ft_encode" into table track2Ft; |
152 | 8c368a17 | Daofeng Li | load data local infile "track2Ft_longrange" into table track2Ft; |
153 | 8c368a17 | Daofeng Li | load data local infile "track2Ft_mock" into table track2Ft; |
154 | 8c368a17 | Daofeng Li | |
155 | 8c368a17 | Daofeng Li | drop table if exists track2Categorical; |
156 | 8c368a17 | Daofeng Li | create table track2Categorical ( |
157 | 8c368a17 | Daofeng Li | name varchar(255) not null primary key, |
158 | 8c368a17 | Daofeng Li | info text not null |
159 | 8c368a17 | Daofeng Li | ); |
160 | 8c368a17 | Daofeng Li | load data local infile 'track2Categorical' into table track2Categorical; |
161 | 8c368a17 | Daofeng Li | |
162 | 8c368a17 | Daofeng Li | |
163 | 8c368a17 | Daofeng Li | drop table if exists track2Style; |
164 | 8c368a17 | Daofeng Li | create table track2Style ( |
165 | 8c368a17 | Daofeng Li | name varchar(255) not null primary key, |
166 | 8c368a17 | Daofeng Li | style text not null |
167 | 8c368a17 | Daofeng Li | ); |
168 | 8c368a17 | Daofeng Li | load data local infile "track2Style" into table track2Style; |
169 | 8c368a17 | Daofeng Li | load data local infile "track2Style_encode" into table track2Style; |
170 | 8c368a17 | Daofeng Li | |
171 | 8c368a17 | Daofeng Li | drop table if exists track2Regions; |
172 | 8c368a17 | Daofeng Li | create table track2Regions ( |
173 | 8c368a17 | Daofeng Li | name varchar(255) not null primary key, |
174 | 8c368a17 | Daofeng Li | regionname varchar(255) not null, |
175 | 8c368a17 | Daofeng Li | regions text not null |
176 | 8c368a17 | Daofeng Li | ); |
177 | 8c368a17 | Daofeng Li | |
178 | 8c368a17 | Daofeng Li | |
179 | 8c368a17 | Daofeng Li | drop table if exists metadataVocabulary; |
180 | 8c368a17 | Daofeng Li | create table metadataVocabulary ( |
181 | 8c368a17 | Daofeng Li | child varchar(255) not null, |
182 | 8c368a17 | Daofeng Li | parent varchar(255) not null |
183 | 8c368a17 | Daofeng Li | ); |
184 | 8c368a17 | Daofeng Li | load data local infile "metadataVocabulary" into table metadataVocabulary; |
185 | 8c368a17 | Daofeng Li | |
186 | 8c368a17 | Daofeng Li | drop table if exists trackAttr2idx; |
187 | 8c368a17 | Daofeng Li | create table trackAttr2idx ( |
188 | 8c368a17 | Daofeng Li | idx varchar(255) not null primary key, |
189 | 8c368a17 | Daofeng Li | attr varchar(255) not null, |
190 | 8c368a17 | Daofeng Li | note varchar(255) null, |
191 | 8c368a17 | Daofeng Li | description text null |
192 | 8c368a17 | Daofeng Li | ); |
193 | 8c368a17 | Daofeng Li | load data local infile "trackAttr2idx" into table trackAttr2idx; |
194 | 8c368a17 | Daofeng Li | |
195 | 8c368a17 | Daofeng Li | |
196 | 8c368a17 | Daofeng Li | drop table if exists tempURL; |
197 | 8c368a17 | Daofeng Li | create table tempURL ( |
198 | 8c368a17 | Daofeng Li | session varchar(100) not null, |
199 | 8c368a17 | Daofeng Li | offset INT unsigned not null, |
200 | 8c368a17 | Daofeng Li | urlpiece text not null |
201 | 8c368a17 | Daofeng Li | ); |
202 | 8c368a17 | Daofeng Li | |
203 | 8c368a17 | Daofeng Li | |
204 | 8c368a17 | Daofeng Li | drop table if exists dataset; |
205 | 8c368a17 | Daofeng Li | create table dataset ( |
206 | 8c368a17 | Daofeng Li | tablename varchar(255) not null, |
207 | 8c368a17 | Daofeng Li | logo varchar(255) null, |
208 | 8c368a17 | Daofeng Li | name varchar(255) not null, |
209 | 8c368a17 | Daofeng Li | url varchar(255) null, |
210 | 8c368a17 | Daofeng Li | description text not null |
211 | 8c368a17 | Daofeng Li | ); |
212 | 8c368a17 | Daofeng Li | load data local infile "dataset" into table dataset; |
213 | 8c368a17 | Daofeng Li | |
214 | 8c368a17 | Daofeng Li | drop table if exists mock; |
215 | 8c368a17 | Daofeng Li | create table mock ( |
216 | 8c368a17 | Daofeng Li | tkname varchar(255) not null |
217 | 8c368a17 | Daofeng Li | ); |
218 | 8c368a17 | Daofeng Li | load data local infile "mock" into table mock; |
219 | 8c368a17 | Daofeng Li | |
220 | 8c368a17 | Daofeng Li | drop table if exists longrange; |
221 | 8c368a17 | Daofeng Li | create table longrange ( |
222 | 8c368a17 | Daofeng Li | tkname varchar(255) not null |
223 | 8c368a17 | Daofeng Li | ); |
224 | 8c368a17 | Daofeng Li | load data local infile "longrange" into table longrange; |
225 | 8c368a17 | Daofeng Li | |
226 | 8c368a17 | Daofeng Li | drop table if exists encode; |
227 | 8c368a17 | Daofeng Li | create table encode ( |
228 | 8c368a17 | Daofeng Li | tkname varchar(255) not null |
229 | 8c368a17 | Daofeng Li | ); |
230 | 8c368a17 | Daofeng Li | load data local infile "encode" into table encode; |
231 | 8c368a17 | Daofeng Li | |
232 | 8c368a17 | Daofeng Li | /* drop table if exists geocollection;
|
233 | 8c368a17 | Daofeng Li | create table geocollection ( |
234 | 8c368a17 | Daofeng Li | tkname varchar(255) not null |
235 | 8c368a17 | Daofeng Li | ); |
236 | 8c368a17 | Daofeng Li | load data local infile "geocollection" into table geocollection; |
237 | 8c368a17 | Daofeng Li | */ |
238 | 8c368a17 | Daofeng Li | |
239 | 8c368a17 | Daofeng Li | drop table if exists scaffoldInfo; |
240 | 8c368a17 | Daofeng Li | create table scaffoldInfo ( |
241 | 8c368a17 | Daofeng Li | parent varchar(255) not null, |
242 | 8c368a17 | Daofeng Li | child varchar(255) not null, |
243 | 8c368a17 | Daofeng Li | childLength int unsigned not null |
244 | 8c368a17 | Daofeng Li | ); |
245 | 8c368a17 | Daofeng Li | load data local infile "scaffoldInfo" into table scaffoldInfo; |
246 | 8c368a17 | Daofeng Li | |
247 | 8c368a17 | Daofeng Li | drop table if exists cytoband; |
248 | 8c368a17 | Daofeng Li | create table cytoband ( |
249 | 8c368a17 | Daofeng Li | id int null auto_increment primary key, |
250 | 8c368a17 | Daofeng Li | chrom char(20) not null, |
251 | 8c368a17 | Daofeng Li | start int not null, |
252 | 8c368a17 | Daofeng Li | stop int not null, |
253 | 8c368a17 | Daofeng Li | name char(20) not null, |
254 | 8c368a17 | Daofeng Li | colorIdx int not null |
255 | 8c368a17 | Daofeng Li | ); |
256 | 8c368a17 | Daofeng Li | load data local infile "cytoband" into table cytoband; |
257 | 8c368a17 | Daofeng Li | |
258 | 8c368a17 | Daofeng Li | |
259 | 8c368a17 | Daofeng Li | /*
|
260 | 8c368a17 | Daofeng Li | DROP TABLE IF EXISTS `rmsk`; |
261 | 8c368a17 | Daofeng Li | CREATE TABLE `rmsk` ( |
262 | 8c368a17 | Daofeng Li | `bin` smallint(5) unsigned NOT NULL default '0', |
263 | 8c368a17 | Daofeng Li | `swScore` int(10) unsigned NOT NULL default '0', |
264 | 8c368a17 | Daofeng Li | `milliDiv` int(10) unsigned NOT NULL default '0', |
265 | 8c368a17 | Daofeng Li | `milliDel` int(10) unsigned NOT NULL default '0', |
266 | 8c368a17 | Daofeng Li | `milliIns` int(10) unsigned NOT NULL default '0', |
267 | 8c368a17 | Daofeng Li | `genoName` varchar(255) NOT NULL default '', |
268 | 8c368a17 | Daofeng Li | `genoStart` int(10) unsigned NOT NULL default '0', |
269 | 8c368a17 | Daofeng Li | `genoEnd` int(10) unsigned NOT NULL default '0', |
270 | 8c368a17 | Daofeng Li | `genoLeft` int(11) NOT NULL default '0', |
271 | 8c368a17 | Daofeng Li | `strand` char(1) NOT NULL default '', |
272 | 8c368a17 | Daofeng Li | `repName` varchar(255) NOT NULL default '', |
273 | 8c368a17 | Daofeng Li | `repClass` varchar(255) NOT NULL default '', |
274 | 8c368a17 | Daofeng Li | `repFamily` varchar(255) NOT NULL default '', |
275 | 8c368a17 | Daofeng Li | `repStart` int(11) NOT NULL default '0', |
276 | 8c368a17 | Daofeng Li | `repEnd` int(11) NOT NULL default '0', |
277 | 8c368a17 | Daofeng Li | `repLeft` int(11) NOT NULL default '0', |
278 | 8c368a17 | Daofeng Li | `id` char(1) NOT NULL default '', |
279 | 8c368a17 | Daofeng Li | KEY `genoName` (`genoName`(14),`bin`) |
280 | 8c368a17 | Daofeng Li | ); |
281 | 8c368a17 | Daofeng Li | load data local infile 'rmsk.txt' into table rmsk; |
282 | 8c368a17 | Daofeng Li | */ |
283 | 8c368a17 | Daofeng Li | |
284 | 8c368a17 | Daofeng Li |