Statistics
| Branch: | Revision:

root / mm9 / makeDb.sql @ 4bbad921

History | View | Annotate | Download (9.4 kB)

1
-- -------------------
2
--                  --
3
--    mm9heatmap    --
4
--                  --
5
-- -------------------
6
drop table if exists config;
7
create table config (
8
  bbiPath text not null,
9
  seqPath text null,
10
  defaultTracks text null,
11
  defaultMdcategory varchar(255) not null,
12
  defaultGenelist text not null,
13
  defaultCustomtracks text not null,
14
  defaultPosition varchar(255) not null,
15
  defaultDataset varchar(255) not null,
16
  defaultDecor text null,
17
  defaultScaffold text null,
18
  ideogram_wiggle1 varchar(255) null,
19
  ideogram_wiggle2 varchar(255) null,
20
  hasGene boolean not null,
21
  allowJuxtaposition boolean not null,
22
  keggSpeciesCode varchar(255) not null,
23
  information text not null,
24
  runmode tinyint not null,
25
  initmatplot boolean not null
26
);
27
insert into config values(
28
"/srv/epgg/data/data/subtleKnife/mm9/",
29
"/srv/epgg/data/data/subtleKnife/seq/mm9.gz",
30
\N,
31
-- "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
"Sample,Assay,Institution",
33
"CYP4Z1\\nCYP2A7\\nCYP2A6\\nCYP3A4\\nCYP1A1\\nCYP4V2\\nCYP51A1\\nCYP2C19\\nCYP26B1\\nCYP11B2\\nCYP24A1\\nCYP4B1\\nCYP2C8",
34
"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
"chr6,51999773,chr6,52368420",
36
"longrange,encode",
37
"knownGene,rmsk_ensemble",
38
"chr1,chr2,chr3,chr4,chr5,chr6,chr7,chr8,chr9,chr10,chr11,chr12,chr13,chr14,chr15,chr16,chr17,chr18,chr19,chrX,chrY,chrM",
39
\N,\N,
40
true,
41
true,
42
"mmu",
43
"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
0,
45
false
46
);
47

    
48

    
49
-- grouping types on genomic features
50
-- table name defined in macro: TBN_GF_GRP
51
drop table if exists gfGrouping;
52
create table gfGrouping (
53
  id TINYINT not null primary key,
54
  name char(50) not null
55
);
56
insert into gfGrouping values (2, "Genes");
57
-- insert into gfGrouping values (3, "non-coding RNA");
58
insert into gfGrouping values (4, "RepeatMasker");
59
insert into gfGrouping values (6, "Sequence conservation");
60
insert into gfGrouping values (5, "Others");
61
insert into gfGrouping values (7, "Mappability (ENCODE)");
62

    
63

    
64
drop table if exists decorInfo;
65
create table decorInfo (
66
  name char(50) not null primary key,
67
  printname char(100) not null,
68
  parent char(50) null,
69
  grp tinyint not null,
70
  fileType tinyint not null,
71
  hasStruct tinyint null,
72
  queryUrl varchar(255) null
73
);
74
load data local infile 'decorInfo' into table decorInfo;
75

    
76
/*
77
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
insert into decorInfo values('ccdsGeneexons','exons (CCDS genes)','ccdsGene',2,0,0,\N);
79
insert into decorInfo values('ccdsGeneintrons','introns (CCDS genes)','ccdsGene',2,0,0,\N);
80
insert into decorInfo values('vegaGene','Vega genes',\N,2,0,1,'http://vega.sanger.ac.uk/Mus_musculus/geneview?gene=');
81
insert into decorInfo values('vegaGenepromoter','promoters (Vega genes)','vegaGene',2,0,0,\N);
82
insert into decorInfo values('vegaGeneutr3',"3' UTRs (Vega genes)",'vegaGene',2,0,0,\N);
83
insert into decorInfo values('vegaGeneutr5',"5' UTRs (Vega genes)",'vegaGene',2,0,0,\N);
84
insert into decorInfo values('vegaGeneexons','exons (Vega genes)','vegaGene',2,0,0,\N);
85
insert into decorInfo values('vegaGeneintrons','introns (Vega genes)','vegaGene',2,0,0,\N);
86
*/
87

    
88
drop table if exists track2Label;
89
create table track2Label (
90
  name varchar(255) not null primary key,
91
  label text null
92
);
93
load data local infile 'track2Label_encode' into table track2Label;
94
load data local infile 'track2Label_longrange' into table track2Label;
95

    
96
drop table if exists track2ProcessInfo;
97
create table track2ProcessInfo (
98
  name varchar(255) not null primary key,
99
  detail text null
100
);
101
load data local infile 'track2ProcessInfo_encode' into table track2ProcessInfo;
102
load data local infile 'track2ProcessInfo_longrange' into table track2ProcessInfo;
103

    
104
drop table if exists track2BamInfo;
105
create table track2BamInfo (
106
  name varchar(255) not null,
107
  bamfile varchar(255) not null,
108
  bamfilelabel varchar(255) not null
109
);
110
load data local infile "track2BamInfo" into table track2BamInfo;
111

    
112
drop table if exists track2Detail;
113
create table track2Detail (
114
  name varchar(255) not null primary key,
115
  detail text null
116
);
117
load data local infile 'track2Detail_decor' into table track2Detail;
118
load data local infile 'track2Detail_encode' into table track2Detail;
119
load data local infile 'track2Detail_longrange' into table track2Detail;
120

    
121
drop table if exists track2GEO;
122
create table track2GEO (
123
  name varchar(255) not null primary key,
124
  geo char(20) not null
125
);
126
load data local infile 'track2GEO_encode' into table track2GEO;
127
load data local infile 'track2GEO_longrange' into table track2GEO;
128

    
129
drop table if exists track2VersionInfo;
130
create table track2VersionInfo (
131
  name varchar(255) not null primary key,
132
  info varchar(255) not null
133
);
134
load data local infile 'track2VersionInfo_encode' into table track2VersionInfo;
135

    
136
-- new trackDetail end here
137

    
138
drop table if exists track2Annotation;
139
create table track2Annotation (
140
  name varchar(255) not null primary key,
141
  attridx varchar(255) not null
142
);
143
load data local infile "track2Annotation_encode" into table track2Annotation;
144
load data local infile "track2Annotation_longrange" into table track2Annotation;
145

    
146
drop table if exists track2Ft;
147
create table track2Ft (
148
  name varchar(255) not null primary key,
149
  ft tinyint not null
150
);
151
load data local infile "track2Ft_encode" into table track2Ft;
152
load data local infile "track2Ft_longrange" into table track2Ft;
153
load data local infile "track2Ft_mock" into table track2Ft;
154

    
155
drop table if exists track2Categorical;
156
create table track2Categorical (
157
  name varchar(255) not null primary key,
158
  info text not null
159
);
160
load data local infile 'track2Categorical' into table track2Categorical;
161

    
162

    
163
drop table if exists track2Style;
164
create table track2Style (
165
  name varchar(255) not null primary key,
166
  style text not null
167
);
168
load data local infile "track2Style" into table track2Style;
169
load data local infile "track2Style_encode" into table track2Style;
170

    
171
drop table if exists track2Regions;
172
create table track2Regions (
173
  name varchar(255) not null primary key,
174
    regionname varchar(255) not null,
175
          regions text not null
176
           );
177

    
178

    
179
drop table if exists metadataVocabulary;
180
create table metadataVocabulary (
181
  child varchar(255) not null,
182
  parent varchar(255) not null
183
);
184
load data local infile "metadataVocabulary" into table metadataVocabulary;
185

    
186
drop table if exists trackAttr2idx;
187
create table trackAttr2idx (
188
  idx varchar(255) not null primary key,
189
  attr varchar(255) not null,
190
  note varchar(255) null,
191
  description text null
192
);
193
load data local infile "trackAttr2idx" into table trackAttr2idx;
194

    
195

    
196
drop table if exists tempURL;
197
create table tempURL (
198
  session varchar(100) not null,
199
  offset INT unsigned not null,
200
  urlpiece text not null
201
);
202

    
203

    
204
drop table if exists dataset;
205
create table dataset (
206
  tablename varchar(255) not null,
207
  logo varchar(255) null,
208
  name varchar(255) not null,
209
  url varchar(255) null,
210
  description text not null
211
);
212
load data local infile "dataset" into table dataset;
213

    
214
drop table if exists mock;
215
create table mock (
216
  tkname varchar(255) not null
217
);
218
load data local infile "mock" into table mock;
219

    
220
drop table if exists longrange;
221
create table longrange (
222
  tkname varchar(255) not null
223
);
224
load data local infile "longrange" into table longrange;
225

    
226
drop table if exists encode;
227
create table encode (
228
  tkname varchar(255) not null
229
);
230
load data local infile "encode" into table encode;
231

    
232
/* drop table if exists geocollection;
233
create table geocollection (
234
  tkname varchar(255) not null
235
);
236
load data local infile "geocollection" into table geocollection;
237
*/
238

    
239
drop table if exists scaffoldInfo;
240
create table scaffoldInfo (
241
  parent varchar(255) not null,
242
  child varchar(255) not null,
243
  childLength int unsigned not null
244
);
245
load data local infile "scaffoldInfo" into table scaffoldInfo;
246

    
247
drop table if exists cytoband;
248
create table cytoband (
249
  id int null auto_increment primary key,
250
  chrom char(20) not null,
251
  start int not null,
252
  stop int not null,
253
  name char(20) not null,
254
  colorIdx int not null
255
);
256
load data local infile "cytoband" into table cytoband;
257

    
258

    
259
/*
260
DROP TABLE IF EXISTS `rmsk`;
261
CREATE TABLE `rmsk` (
262
  `bin` smallint(5) unsigned NOT NULL default '0',
263
  `swScore` int(10) unsigned NOT NULL default '0',
264
  `milliDiv` int(10) unsigned NOT NULL default '0',
265
  `milliDel` int(10) unsigned NOT NULL default '0',
266
  `milliIns` int(10) unsigned NOT NULL default '0',
267
  `genoName` varchar(255) NOT NULL default '',
268
  `genoStart` int(10) unsigned NOT NULL default '0',
269
  `genoEnd` int(10) unsigned NOT NULL default '0',
270
  `genoLeft` int(11) NOT NULL default '0',
271
  `strand` char(1) NOT NULL default '',
272
  `repName` varchar(255) NOT NULL default '',
273
  `repClass` varchar(255) NOT NULL default '',
274
  `repFamily` varchar(255) NOT NULL default '',
275
  `repStart` int(11) NOT NULL default '0',
276
  `repEnd` int(11) NOT NULL default '0',
277
  `repLeft` int(11) NOT NULL default '0',
278
  `id` char(1) NOT NULL default '',
279
  KEY `genoName` (`genoName`(14),`bin`)
280
);
281
load data local infile 'rmsk.txt' into table rmsk;
282
*/
283

    
284

    
285