Statistics
| Branch: | Revision:

root / danRer7 / makeDb.sql @ 8c368a17

History | View | Annotate | Download (7.3 kB)

1
drop table if exists config;
2
create table config (
3
  bbiPath text not null,
4
  seqPath text null,
5
  defaultTracks text not null,
6
  defaultMdcategory varchar(255) not null,
7
  defaultGenelist text not null,
8
  defaultCustomtracks text not null,
9
  defaultPosition varchar(255) not null,
10
  defaultDataset varchar(255) not null,
11
  defaultDecor text null,
12
  defaultScaffold text not null,
13
  ideogram_wiggle1 varchar(255) null,
14
  ideogram_wiggle2 varchar(255) null,
15
  hasGene boolean not null,
16
  allowJuxtaposition boolean not null,
17
  keggSpeciesCode varchar(255) null,
18
  information text not null,
19
  runmode tinyint not null,
20
  initmatplot boolean not null
21
);
22
insert into config values(
23
"/srv/epgg/data/data/subtleKnife/danRer7/",
24
"/srv/epgg/data/data/subtleKnife/seq/danRer7.gz",
25
"BisSeq01_sperm.bedGraph,BisSeq02_egg.bedGraph,BisSeq03_2-16cell.bedGraph,BisSeq04_64cell.bedGraph,BisSeq05_256cell.bedGraph,BisSeq06_sphere.bedGraph,BisSeq07_muscle.bedGraph,BisSeq08_sphere_parthenogenesis_control.bedGraph,BisSeq09_sphere_parthenogenesis_treatment.bedGraph",
26
"Sample,Assay,Lab",
27
"cyp2v1\\ncyp2aa4\\ncyp2x8\\ncyp2p9\\ncyp3c4\\ncyp24a1\\ncyp2p6\\ncyp2x12\\ncyp19a1a\\ncyp20a1\\ncyp2aa2\\ncyp11a1\\ncyp2ad2",
28
"3,http://vizhub.wustl.edu/hubSample/danRer7/num1.gz,1,http://vizhub.wustl.edu/hubSample/danRer7/bed.gz,100,http://vizhub.wustl.edu/hubSample/danRer7/hub.txt",
29
"chr19,18966019,chr19,19564024",
30
"published",
31
"refGene,rmsk_ensemble",
32
"chr1,chr2,chr3,chr4,chr5,chr6,chr7,chr8,chr9,chr10,chr11,chr12,chr13,chr14,chr15,chr16,chr17,chr18,chr19,chr20,chr21,chr22,chr23,chr24,chr25",
33
\N,\N,
34
true,
35
true,
36
"dre",
37
"Assembly version|danRer7|Sequence source|<a href=http://hgdownload.cse.ucsc.edu/goldenPath/danRer7/bigZips/ target=_blank>UCSC browser</a>|Date parsed|September 1, 2011|Chromosomes|26|Contigs|1107|Total bases|1,412,464,843|Logo art|<a href=http://en.wikipedia.org/wiki/File:Zebrafisch.jpg target=_blank>link</a>",
38
0,
39
false
40
);
41

    
42
-- grouping types on genomic features
43
-- table name defined in macro: TBN_GF_GRP
44
drop table if exists gfGrouping;
45
create table gfGrouping (
46
  id TINYINT not null primary key,
47
  name char(50) not null
48
);
49
insert into gfGrouping values (2, "Genes");
50
insert into gfGrouping values (4, "RepeatMasker");
51
-- insert into gfGrouping values (3, "non-coding RNA");
52
insert into gfGrouping values (6, "Sequence conservation");
53
insert into gfGrouping values (5, "Others");
54

    
55

    
56

    
57

    
58
drop table if exists decorInfo;
59
create table decorInfo (
60
  name char(50) not null primary key,
61
  printname char(100) not null,
62
  parent char(50) null,
63
  grp tinyint not null,
64
  fileType tinyint not null,
65
  hasStruct tinyint null,
66
  queryUrl varchar(255) null
67
);
68
load data local infile 'decorInfo' into table decorInfo;
69

    
70
drop table if exists track2Label;
71
create table track2Label (
72
  name varchar(255) not null primary key,
73
  label text null
74
);
75
load data local infile 'track2Label' into table track2Label;
76

    
77
drop table if exists track2ProcessInfo;
78
create table track2ProcessInfo (
79
  name varchar(255) not null primary key,
80
  detail text null
81
);
82
load data local infile 'track2ProcessInfo' into table track2ProcessInfo;
83

    
84
drop table if exists track2BamInfo;
85
create table track2BamInfo (
86
  name varchar(255) not null,
87
  bamfile varchar(255) not null,
88
  bamfilelabel varchar(255) not null
89
);
90
load data local infile "track2BamInfo" into table track2BamInfo;
91

    
92
drop table if exists track2Detail;
93
create table track2Detail (
94
  name varchar(255) not null primary key,
95
  detail text null
96
);
97
load data local infile 'track2Detail' into table track2Detail;
98

    
99
drop table if exists track2GEO;
100
create table track2GEO (
101
  name varchar(255) not null primary key,
102
  geo char(20) not null
103
);
104
load data local infile 'track2GEO' into table track2GEO;
105

    
106
drop table if exists track2VersionInfo;
107
create table track2VersionInfo (
108
  name varchar(255) not null primary key,
109
  info varchar(255) not null
110
);
111
load data local infile 'track2VersionInfo' into table track2VersionInfo;
112

    
113
-- new trackDetail end here
114

    
115
drop table if exists track2Annotation;
116
create table track2Annotation (
117
  name varchar(255) not null primary key,
118
  attridx varchar(255) not null
119
);
120
load data local infile "track2Annotation" into table track2Annotation;
121

    
122
drop table if exists track2Ft;
123
create table track2Ft (
124
  name varchar(255) not null primary key,
125
  ft tinyint not null
126
);
127
load data local infile "track2Ft" into table track2Ft;
128

    
129
drop table if exists track2Style;
130
create table track2Style (
131
  name varchar(255) not null primary key,
132
  style text not null
133
);
134
load data local infile "track2Style_decor" into table track2Style;
135
load data local infile "track2Style" into table track2Style;
136

    
137
drop table if exists track2Regions;
138
create table track2Regions (
139
  name varchar(255) not null primary key,
140
  regionname varchar(255) not null,
141
  regions text not null
142
);
143

    
144
drop table if exists track2Categorical;
145
create table track2Categorical (
146
  name varchar(255) not null primary key,
147
  info text not null
148
);
149
load data local infile 'track2Categorical' into table track2Categorical;
150

    
151

    
152

    
153
drop table if exists metadataVocabulary;
154
create table metadataVocabulary (
155
  child varchar(255) not null,
156
  parent varchar(255) not null
157
);
158
load data local infile "metadataVocabulary" into table metadataVocabulary;
159

    
160
drop table if exists trackAttr2idx;
161
create table trackAttr2idx (
162
  idx varchar(255) not null primary key,
163
  attr varchar(255) not null,
164
  note varchar(255) null,
165
  description text null
166
);
167
load data local infile "trackAttr2idx" into table trackAttr2idx;
168

    
169

    
170
drop table if exists tempURL;
171
create table tempURL (
172
  session varchar(100) not null,
173
  offset INT unsigned not null,
174
  urlpiece text not null
175
);
176

    
177

    
178

    
179
drop table if exists dataset;
180
create table dataset (
181
  tablename varchar(255) not null,
182
  logo varchar(255) null,
183
  name varchar(255) not null,
184
  url varchar(255) null,
185
  description text not null
186
);
187
load data local infile "dataset" into table dataset;
188

    
189
drop table if exists published;
190
create table published (
191
  tkname varchar(255) not null
192
);
193
load data local infile "track2Name" into table published;
194

    
195
drop table if exists scaffoldInfo;
196
create table scaffoldInfo (
197
  parent varchar(255) not null,
198
  child varchar(255) not null,
199
  childLength int unsigned not null
200
  );
201
load data local infile "scaffoldInfo" into table scaffoldInfo;
202

    
203

    
204
drop table if exists cytoband;
205
create table cytoband (
206
  id int null auto_increment primary key,
207
  chrom char(20) not null,
208
  start int not null,
209
  stop int not null,
210
  name char(20) not null,
211
  colorIdx int not null
212
);
213
load data local infile "cytoband" into table cytoband;
214

    
215
/*
216
DROP TABLE IF EXISTS `rmsk`;
217
CREATE TABLE `rmsk` (
218
  `bin` smallint(5) unsigned NOT NULL default '0',
219
  `swScore` int(10) unsigned NOT NULL default '0',
220
  `milliDiv` int(10) unsigned NOT NULL default '0',
221
  `milliDel` int(10) unsigned NOT NULL default '0',
222
  `milliIns` int(10) unsigned NOT NULL default '0',
223
  `genoName` varchar(255) NOT NULL default '',
224
  `genoStart` int(10) unsigned NOT NULL default '0',
225
  `genoEnd` int(10) unsigned NOT NULL default '0',
226
  `genoLeft` int(11) NOT NULL default '0',
227
  `strand` char(1) NOT NULL default '',
228
  `repName` varchar(255) NOT NULL default '',
229
  `repClass` varchar(255) NOT NULL default '',
230
  `repFamily` varchar(255) NOT NULL default '',
231
  `repStart` int(11) NOT NULL default '0',
232
  `repEnd` int(11) NOT NULL default '0',
233
  `repLeft` int(11) NOT NULL default '0',
234
  `id` char(1) NOT NULL default '',
235
  KEY `genoName` (`genoName`(14),`bin`)
236
);
237
load data local infile 'rmsk.txt' into table rmsk;
238
*/
239

    
240

    
241