Statistics
| Branch: | Revision:

root / Gmax_189 / makeDb.sql @ 4bbad921

History | View | Annotate | Download (7.4 kB)

1
-- -------------------
2
--                  --
3
--  Gmax_189
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 null,
13
  defaultCustomtracks text null,
14
  defaultPosition varchar(255) not null,
15
  defaultDataset varchar(255) not null,
16
  defaultDecor text null,
17
  defaultScaffold text not 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) 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/Gmax_189/",
29
"/srv/epgg/data/data/subtleKnife/seq/Gmax_189.gz",
30
\N,
31
"Sample",
32
\N,
33
\N,
34
"Gm01,14872,Gm01,374047",
35
"mock",
36
"gene,gc5Base",
37
"Gm01,Gm02,Gm03,Gm04,Gm05,Gm06,Gm07,Gm08,Gm09,Gm10,Gm11,Gm12,Gm13,Gm14,Gm15,Gm16,Gm17,Gm18,Gm19,Gm20",
38
\N,\N,
39
true,
40
true,
41
"Gmax",
42
"Assembly version|Gmax_189|Sequence source|<a href=ftp://ftp.jgi-psf.org/pub/compgen/phytozome/v9.0/Gmax/ target=_blank>phytozome</a>|Date parsed|August 21, 2013|Chromosomes|20|Misc|1148|Total bases|973,344,380|Logo art|<a href=http://www.gov.pe.ca/af/agweb/index.php3?number=79367&lang=E target=_blank>link</a>",
43
0,
44
false
45
);
46

    
47

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

    
61

    
62

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

    
75
drop table if exists track2Label;
76
create table track2Label (
77
  name varchar(255) not null primary key,
78
  label text null
79
);
80
load data local infile 'track2Label' into table track2Label;
81

    
82
drop table if exists track2ProcessInfo;
83
create table track2ProcessInfo (
84
  name varchar(255) not null primary key,
85
  detail text null
86
);
87
load data local infile 'track2ProcessInfo' into table track2ProcessInfo;
88

    
89
drop table if exists track2BamInfo;
90
create table track2BamInfo (
91
  name varchar(255) not null,
92
  bamfile varchar(255) not null,
93
  bamfilelabel varchar(255) not null
94
);
95
load data local infile "track2BamInfo" into table track2BamInfo;
96

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

    
105
drop table if exists track2GEO;
106
create table track2GEO (
107
  name varchar(255) not null primary key,
108
  geo char(20) not null
109
);
110
load data local infile 'track2GEO' into table track2GEO;
111

    
112
drop table if exists track2Categorical;
113
create table track2Categorical (
114
  name varchar(255) not null primary key,
115
  info text not null
116
);
117
load data local infile 'track2Categorical' into table track2Categorical;
118

    
119

    
120
drop table if exists track2VersionInfo;
121
create table track2VersionInfo (
122
  name varchar(255) not null primary key,
123
  info varchar(255) not null
124
);
125
load data local infile 'track2VersionInfo' into table track2VersionInfo;
126

    
127

    
128
drop table if exists track2Annotation;
129
create table track2Annotation (
130
  name varchar(255) not null primary key,
131
  attridx varchar(255) not null
132
);
133
load data local infile "track2Annotation" into table track2Annotation;
134

    
135
drop table if exists track2Ft;
136
create table track2Ft (
137
  name varchar(255) not null primary key,
138
  ft tinyint not null
139
);
140
load data local infile "track2Ft" into table track2Ft;
141

    
142
drop table if exists track2Style;
143
create table track2Style (
144
  name varchar(255) not null primary key,
145
  style text not null
146
);
147
load data local infile "track2Style" into table track2Style;
148

    
149
drop table if exists track2Regions;
150
create table track2Regions (
151
  name varchar(255) not null primary key,
152
    regionname varchar(255) not null,
153
          regions text not null
154
           );
155

    
156

    
157
drop table if exists metadataVocabulary;
158
create table metadataVocabulary (
159
  child varchar(255) not null,
160
  parent varchar(255) not null
161
);
162
load data local infile "metadataVocabulary" into table metadataVocabulary;
163

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

    
173

    
174
drop table if exists tempURL;
175
create table tempURL (
176
  session varchar(100) not null,
177
  offset INT unsigned not null,
178
  urlpiece text not null
179
);
180

    
181

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

    
192
drop table if exists mock;
193
create table mock (
194
  tkname varchar(255) not null
195
);
196
load data local infile "mock" into table mock;
197

    
198

    
199
drop table if exists scaffoldInfo;
200
create table scaffoldInfo (
201
  parent varchar(255) not null,
202
  child varchar(255) not null,
203
  childLength int unsigned not null
204
);
205
load data local infile "scaffoldInfo" into table scaffoldInfo;
206

    
207

    
208
drop table if exists cytoband;
209
create table cytoband (
210
  id int null auto_increment primary key,
211
  chrom char(20) not null,
212
  start int not null,
213
  stop int not null,
214
  name char(20) not null,
215
  colorIdx int not null
216
);
217
/*
218
load data local infile "cytoband" into table cytoband;
219

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

    
244

    
245
drop table if exists genestruct;
246
create table genestruct (
247
id int unsigned not null primary key,
248
chrom varchar(255) not null,
249
strand char(1) not null,
250
txStart int unsigned not null,
251
txEnd int unsigned not null,
252
cdsStart int unsigned not null,
253
cdsEnd int unsigned not null,
254
exonCount int unsigned not null,
255
exonStarts text not null,
256
exonEnds text not null,
257
name varchar(255) not null
258
);
259
load data local infile 'geneStruct' into table genestruct;
260

    
261

    
262
drop table if exists genesymbol;
263
create table genesymbol (
264
name varchar(255) not null,
265
symbol varchar(255) null,
266
description text null,
267
id int unsigned not null primary key,
268
index(name)
269
);
270
load data local infile 'geneSymbol' into table genesymbol;
271