root / Gmax_189 / makeDb.sql @ master
History | View | Annotate | Download (7.4 kB)
1 | 52e54a06 | Daofeng Li | -- -------------------
|
---|---|---|---|
2 | 52e54a06 | Daofeng Li | -- --
|
3 | 52e54a06 | Daofeng Li | -- Gmax_189
|
4 | 52e54a06 | Daofeng Li | -- --
|
5 | 52e54a06 | Daofeng Li | -- -------------------
|
6 | 52e54a06 | Daofeng Li | drop table if exists config; |
7 | 52e54a06 | Daofeng Li | create table config ( |
8 | 52e54a06 | Daofeng Li | bbiPath text not null, |
9 | 52e54a06 | Daofeng Li | seqPath text null, |
10 | 52e54a06 | Daofeng Li | defaultTracks text null, |
11 | 52e54a06 | Daofeng Li | defaultMdcategory varchar(255) not null, |
12 | 52e54a06 | Daofeng Li | defaultGenelist text null, |
13 | 52e54a06 | Daofeng Li | defaultCustomtracks text null, |
14 | 52e54a06 | Daofeng Li | defaultPosition varchar(255) not null, |
15 | 52e54a06 | Daofeng Li | defaultDataset varchar(255) not null, |
16 | 52e54a06 | Daofeng Li | defaultDecor text null, |
17 | 52e54a06 | Daofeng Li | defaultScaffold text not null, |
18 | 52e54a06 | Daofeng Li | ideogram_wiggle1 varchar(255) null, |
19 | 52e54a06 | Daofeng Li | ideogram_wiggle2 varchar(255) null, |
20 | 52e54a06 | Daofeng Li | hasGene boolean not null, |
21 | 52e54a06 | Daofeng Li | allowJuxtaposition boolean not null, |
22 | 52e54a06 | Daofeng Li | keggSpeciesCode varchar(255) null, |
23 | 52e54a06 | Daofeng Li | information text not null, |
24 | 52e54a06 | Daofeng Li | runmode tinyint not null, |
25 | 52e54a06 | Daofeng Li | initmatplot boolean not null |
26 | 52e54a06 | Daofeng Li | ); |
27 | 52e54a06 | Daofeng Li | insert into config values( |
28 | 52e54a06 | Daofeng Li | "/srv/epgg/data/data/subtleKnife/Gmax_189/",
|
29 | 52e54a06 | Daofeng Li | "/srv/epgg/data/data/subtleKnife/seq/Gmax_189.gz",
|
30 | 52e54a06 | Daofeng Li | \N,
|
31 | 52e54a06 | Daofeng Li | "Sample",
|
32 | 52e54a06 | Daofeng Li | \N,
|
33 | 52e54a06 | Daofeng Li | \N,
|
34 | 52e54a06 | Daofeng Li | "Gm01,14872,Gm01,374047",
|
35 | 52e54a06 | Daofeng Li | "mock",
|
36 | 52e54a06 | Daofeng Li | "gene,gc5Base",
|
37 | 4bbad921 | Daofeng Li | "Gm01,Gm02,Gm03,Gm04,Gm05,Gm06,Gm07,Gm08,Gm09,Gm10,Gm11,Gm12,Gm13,Gm14,Gm15,Gm16,Gm17,Gm18,Gm19,Gm20",
|
38 | 52e54a06 | Daofeng Li | \N,\N, |
39 | 52e54a06 | Daofeng Li | true,
|
40 | 52e54a06 | Daofeng Li | true,
|
41 | 52e54a06 | Daofeng Li | "Gmax",
|
42 | 52e54a06 | Daofeng Li | "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 | 52e54a06 | Daofeng Li | 0,
|
44 | 52e54a06 | Daofeng Li | false
|
45 | 52e54a06 | Daofeng Li | ); |
46 | 52e54a06 | Daofeng Li | |
47 | 52e54a06 | Daofeng Li | |
48 | 52e54a06 | Daofeng Li | -- grouping types on genomic features
|
49 | 52e54a06 | Daofeng Li | -- table name defined in macro: TBN_GF_GRP
|
50 | 52e54a06 | Daofeng Li | drop table if exists gfGrouping; |
51 | 52e54a06 | Daofeng Li | create table gfGrouping ( |
52 | 52e54a06 | Daofeng Li | id TINYINT not null primary key, |
53 | 52e54a06 | Daofeng Li | name char(50) not null |
54 | 52e54a06 | Daofeng Li | ); |
55 | 52e54a06 | Daofeng Li | insert into gfGrouping values (2, "Genes"); |
56 | 52e54a06 | Daofeng Li | -- insert into gfGrouping values (3, "non-coding RNA");
|
57 | 52e54a06 | Daofeng Li | -- insert into gfGrouping values (4, "RepeatMasker");
|
58 | 52e54a06 | Daofeng Li | -- insert into gfGrouping values (6, "Sequence conservation");
|
59 | 52e54a06 | Daofeng Li | insert into gfGrouping values (5, "Others"); |
60 | 52e54a06 | Daofeng Li | |
61 | 52e54a06 | Daofeng Li | |
62 | 52e54a06 | Daofeng Li | |
63 | 52e54a06 | Daofeng Li | drop table if exists decorInfo; |
64 | 52e54a06 | Daofeng Li | create table decorInfo ( |
65 | 52e54a06 | Daofeng Li | name char(50) not null primary key, |
66 | 52e54a06 | Daofeng Li | printname char(100) not null, |
67 | 52e54a06 | Daofeng Li | parent char(50) null, |
68 | 52e54a06 | Daofeng Li | grp tinyint not null, |
69 | 52e54a06 | Daofeng Li | fileType tinyint not null, |
70 | 52e54a06 | Daofeng Li | hasStruct tinyint null, |
71 | 52e54a06 | Daofeng Li | queryUrl varchar(255) null |
72 | 52e54a06 | Daofeng Li | ); |
73 | 52e54a06 | Daofeng Li | load data local infile 'decorInfo' into table decorInfo; |
74 | 52e54a06 | Daofeng Li | |
75 | 52e54a06 | Daofeng Li | drop table if exists track2Label; |
76 | 52e54a06 | Daofeng Li | create table track2Label ( |
77 | 52e54a06 | Daofeng Li | name varchar(255) not null primary key, |
78 | 52e54a06 | Daofeng Li | label text null |
79 | 52e54a06 | Daofeng Li | ); |
80 | 52e54a06 | Daofeng Li | load data local infile 'track2Label' into table track2Label; |
81 | 52e54a06 | Daofeng Li | |
82 | 52e54a06 | Daofeng Li | drop table if exists track2ProcessInfo; |
83 | 52e54a06 | Daofeng Li | create table track2ProcessInfo ( |
84 | 52e54a06 | Daofeng Li | name varchar(255) not null primary key, |
85 | 52e54a06 | Daofeng Li | detail text null |
86 | 52e54a06 | Daofeng Li | ); |
87 | 52e54a06 | Daofeng Li | load data local infile 'track2ProcessInfo' into table track2ProcessInfo; |
88 | 52e54a06 | Daofeng Li | |
89 | 52e54a06 | Daofeng Li | drop table if exists track2BamInfo; |
90 | 52e54a06 | Daofeng Li | create table track2BamInfo ( |
91 | 52e54a06 | Daofeng Li | name varchar(255) not null, |
92 | 52e54a06 | Daofeng Li | bamfile varchar(255) not null, |
93 | 52e54a06 | Daofeng Li | bamfilelabel varchar(255) not null |
94 | 52e54a06 | Daofeng Li | ); |
95 | 52e54a06 | Daofeng Li | load data local infile "track2BamInfo" into table track2BamInfo; |
96 | 52e54a06 | Daofeng Li | |
97 | 52e54a06 | Daofeng Li | drop table if exists track2Detail; |
98 | 52e54a06 | Daofeng Li | create table track2Detail ( |
99 | 52e54a06 | Daofeng Li | name varchar(255) not null primary key, |
100 | 52e54a06 | Daofeng Li | detail text null |
101 | 52e54a06 | Daofeng Li | ); |
102 | 52e54a06 | Daofeng Li | load data local infile 'track2Detail' into table track2Detail; |
103 | 52e54a06 | Daofeng Li | load data local infile 'track2Detail_decor' into table track2Detail; |
104 | 52e54a06 | Daofeng Li | |
105 | 52e54a06 | Daofeng Li | drop table if exists track2GEO; |
106 | 52e54a06 | Daofeng Li | create table track2GEO ( |
107 | 52e54a06 | Daofeng Li | name varchar(255) not null primary key, |
108 | 52e54a06 | Daofeng Li | geo char(20) not null |
109 | 52e54a06 | Daofeng Li | ); |
110 | 52e54a06 | Daofeng Li | load data local infile 'track2GEO' into table track2GEO; |
111 | 52e54a06 | Daofeng Li | |
112 | 52e54a06 | Daofeng Li | drop table if exists track2Categorical; |
113 | 52e54a06 | Daofeng Li | create table track2Categorical ( |
114 | 52e54a06 | Daofeng Li | name varchar(255) not null primary key, |
115 | 52e54a06 | Daofeng Li | info text not null |
116 | 52e54a06 | Daofeng Li | ); |
117 | 52e54a06 | Daofeng Li | load data local infile 'track2Categorical' into table track2Categorical; |
118 | 52e54a06 | Daofeng Li | |
119 | 52e54a06 | Daofeng Li | |
120 | 52e54a06 | Daofeng Li | drop table if exists track2VersionInfo; |
121 | 52e54a06 | Daofeng Li | create table track2VersionInfo ( |
122 | 52e54a06 | Daofeng Li | name varchar(255) not null primary key, |
123 | 52e54a06 | Daofeng Li | info varchar(255) not null |
124 | 52e54a06 | Daofeng Li | ); |
125 | 52e54a06 | Daofeng Li | load data local infile 'track2VersionInfo' into table track2VersionInfo; |
126 | 52e54a06 | Daofeng Li | |
127 | 52e54a06 | Daofeng Li | |
128 | 52e54a06 | Daofeng Li | drop table if exists track2Annotation; |
129 | 52e54a06 | Daofeng Li | create table track2Annotation ( |
130 | 52e54a06 | Daofeng Li | name varchar(255) not null primary key, |
131 | 52e54a06 | Daofeng Li | attridx varchar(255) not null |
132 | 52e54a06 | Daofeng Li | ); |
133 | 52e54a06 | Daofeng Li | load data local infile "track2Annotation" into table track2Annotation; |
134 | 52e54a06 | Daofeng Li | |
135 | 52e54a06 | Daofeng Li | drop table if exists track2Ft; |
136 | 52e54a06 | Daofeng Li | create table track2Ft ( |
137 | 52e54a06 | Daofeng Li | name varchar(255) not null primary key, |
138 | 52e54a06 | Daofeng Li | ft tinyint not null |
139 | 52e54a06 | Daofeng Li | ); |
140 | 52e54a06 | Daofeng Li | load data local infile "track2Ft" into table track2Ft; |
141 | 52e54a06 | Daofeng Li | |
142 | 52e54a06 | Daofeng Li | drop table if exists track2Style; |
143 | 52e54a06 | Daofeng Li | create table track2Style ( |
144 | 52e54a06 | Daofeng Li | name varchar(255) not null primary key, |
145 | 52e54a06 | Daofeng Li | style text not null |
146 | 52e54a06 | Daofeng Li | ); |
147 | 52e54a06 | Daofeng Li | load data local infile "track2Style" into table track2Style; |
148 | 52e54a06 | Daofeng Li | |
149 | 52e54a06 | Daofeng Li | drop table if exists track2Regions; |
150 | 52e54a06 | Daofeng Li | create table track2Regions ( |
151 | 52e54a06 | Daofeng Li | name varchar(255) not null primary key, |
152 | 52e54a06 | Daofeng Li | regionname varchar(255) not null, |
153 | 52e54a06 | Daofeng Li | regions text not null |
154 | 52e54a06 | Daofeng Li | ); |
155 | 52e54a06 | Daofeng Li | |
156 | 52e54a06 | Daofeng Li | |
157 | 52e54a06 | Daofeng Li | drop table if exists metadataVocabulary; |
158 | 52e54a06 | Daofeng Li | create table metadataVocabulary ( |
159 | 52e54a06 | Daofeng Li | child varchar(255) not null, |
160 | 52e54a06 | Daofeng Li | parent varchar(255) not null |
161 | 52e54a06 | Daofeng Li | ); |
162 | 52e54a06 | Daofeng Li | load data local infile "metadataVocabulary" into table metadataVocabulary; |
163 | 52e54a06 | Daofeng Li | |
164 | 52e54a06 | Daofeng Li | drop table if exists trackAttr2idx; |
165 | 52e54a06 | Daofeng Li | create table trackAttr2idx ( |
166 | 52e54a06 | Daofeng Li | idx varchar(255) not null primary key, |
167 | 52e54a06 | Daofeng Li | attr varchar(255) not null, |
168 | 52e54a06 | Daofeng Li | note varchar(255) null, |
169 | 52e54a06 | Daofeng Li | description text null |
170 | 52e54a06 | Daofeng Li | ); |
171 | 52e54a06 | Daofeng Li | load data local infile "trackAttr2idx" into table trackAttr2idx; |
172 | 52e54a06 | Daofeng Li | |
173 | 52e54a06 | Daofeng Li | |
174 | 52e54a06 | Daofeng Li | drop table if exists tempURL; |
175 | 52e54a06 | Daofeng Li | create table tempURL ( |
176 | 52e54a06 | Daofeng Li | session varchar(100) not null, |
177 | 52e54a06 | Daofeng Li | offset INT unsigned not null, |
178 | 52e54a06 | Daofeng Li | urlpiece text not null |
179 | 52e54a06 | Daofeng Li | ); |
180 | 52e54a06 | Daofeng Li | |
181 | 52e54a06 | Daofeng Li | |
182 | 52e54a06 | Daofeng Li | drop table if exists dataset; |
183 | 52e54a06 | Daofeng Li | create table dataset ( |
184 | 52e54a06 | Daofeng Li | tablename varchar(255) not null, |
185 | 52e54a06 | Daofeng Li | logo varchar(255) null, |
186 | 52e54a06 | Daofeng Li | name varchar(255) not null, |
187 | 52e54a06 | Daofeng Li | url varchar(255) null, |
188 | 52e54a06 | Daofeng Li | description text not null |
189 | 52e54a06 | Daofeng Li | ); |
190 | 52e54a06 | Daofeng Li | -- load data local infile "dataset" into table dataset;
|
191 | 52e54a06 | Daofeng Li | |
192 | 52e54a06 | Daofeng Li | drop table if exists mock; |
193 | 52e54a06 | Daofeng Li | create table mock ( |
194 | 52e54a06 | Daofeng Li | tkname varchar(255) not null |
195 | 52e54a06 | Daofeng Li | ); |
196 | 52e54a06 | Daofeng Li | load data local infile "mock" into table mock; |
197 | 52e54a06 | Daofeng Li | |
198 | 52e54a06 | Daofeng Li | |
199 | 52e54a06 | Daofeng Li | drop table if exists scaffoldInfo; |
200 | 52e54a06 | Daofeng Li | create table scaffoldInfo ( |
201 | 52e54a06 | Daofeng Li | parent varchar(255) not null, |
202 | 52e54a06 | Daofeng Li | child varchar(255) not null, |
203 | 52e54a06 | Daofeng Li | childLength int unsigned not null |
204 | 52e54a06 | Daofeng Li | ); |
205 | 52e54a06 | Daofeng Li | load data local infile "scaffoldInfo" into table scaffoldInfo; |
206 | 52e54a06 | Daofeng Li | |
207 | 52e54a06 | Daofeng Li | |
208 | 52e54a06 | Daofeng Li | drop table if exists cytoband; |
209 | 52e54a06 | Daofeng Li | create table cytoband ( |
210 | 52e54a06 | Daofeng Li | id int null auto_increment primary key, |
211 | 52e54a06 | Daofeng Li | chrom char(20) not null, |
212 | 52e54a06 | Daofeng Li | start int not null, |
213 | 52e54a06 | Daofeng Li | stop int not null, |
214 | 52e54a06 | Daofeng Li | name char(20) not null, |
215 | 52e54a06 | Daofeng Li | colorIdx int not null |
216 | 52e54a06 | Daofeng Li | ); |
217 | 52e54a06 | Daofeng Li | /*
|
218 | 52e54a06 | Daofeng Li | load data local infile "cytoband" into table cytoband; |
219 | 52e54a06 | Daofeng Li | |
220 | 52e54a06 | Daofeng Li | DROP TABLE IF EXISTS `rmsk`; |
221 | 52e54a06 | Daofeng Li | CREATE TABLE `rmsk` ( |
222 | 52e54a06 | Daofeng Li | `bin` smallint(5) unsigned NOT NULL default '0', |
223 | 52e54a06 | Daofeng Li | `swScore` int(10) unsigned NOT NULL default '0', |
224 | 52e54a06 | Daofeng Li | `milliDiv` int(10) unsigned NOT NULL default '0', |
225 | 52e54a06 | Daofeng Li | `milliDel` int(10) unsigned NOT NULL default '0', |
226 | 52e54a06 | Daofeng Li | `milliIns` int(10) unsigned NOT NULL default '0', |
227 | 52e54a06 | Daofeng Li | `genoName` varchar(255) NOT NULL default '', |
228 | 52e54a06 | Daofeng Li | `genoStart` int(10) unsigned NOT NULL default '0', |
229 | 52e54a06 | Daofeng Li | `genoEnd` int(10) unsigned NOT NULL default '0', |
230 | 52e54a06 | Daofeng Li | `genoLeft` int(11) NOT NULL default '0', |
231 | 52e54a06 | Daofeng Li | `strand` char(1) NOT NULL default '', |
232 | 52e54a06 | Daofeng Li | `repName` varchar(255) NOT NULL default '', |
233 | 52e54a06 | Daofeng Li | `repClass` varchar(255) NOT NULL default '', |
234 | 52e54a06 | Daofeng Li | `repFamily` varchar(255) NOT NULL default '', |
235 | 52e54a06 | Daofeng Li | `repStart` int(11) NOT NULL default '0', |
236 | 52e54a06 | Daofeng Li | `repEnd` int(11) NOT NULL default '0', |
237 | 52e54a06 | Daofeng Li | `repLeft` int(11) NOT NULL default '0', |
238 | 52e54a06 | Daofeng Li | `id` char(1) NOT NULL default '', |
239 | 52e54a06 | Daofeng Li | KEY `genoName` (`genoName`(14),`bin`) |
240 | 52e54a06 | Daofeng Li | ); |
241 | 52e54a06 | Daofeng Li | load data local infile 'rmsk.txt' into table rmsk; |
242 | 52e54a06 | Daofeng Li | */ |
243 | 52e54a06 | Daofeng Li | |
244 | 52e54a06 | Daofeng Li | |
245 | 52e54a06 | Daofeng Li | drop table if exists genestruct; |
246 | 52e54a06 | Daofeng Li | create table genestruct ( |
247 | 52e54a06 | Daofeng Li | id int unsigned not null primary key, |
248 | 52e54a06 | Daofeng Li | chrom varchar(255) not null, |
249 | 52e54a06 | Daofeng Li | strand char(1) not null, |
250 | 52e54a06 | Daofeng Li | txStart int unsigned not null, |
251 | 52e54a06 | Daofeng Li | txEnd int unsigned not null, |
252 | 52e54a06 | Daofeng Li | cdsStart int unsigned not null, |
253 | 52e54a06 | Daofeng Li | cdsEnd int unsigned not null, |
254 | 52e54a06 | Daofeng Li | exonCount int unsigned not null, |
255 | 52e54a06 | Daofeng Li | exonStarts text not null, |
256 | 52e54a06 | Daofeng Li | exonEnds text not null, |
257 | 52e54a06 | Daofeng Li | name varchar(255) not null |
258 | 52e54a06 | Daofeng Li | ); |
259 | 52e54a06 | Daofeng Li | load data local infile 'geneStruct' into table genestruct; |
260 | 52e54a06 | Daofeng Li | |
261 | 52e54a06 | Daofeng Li | |
262 | 52e54a06 | Daofeng Li | drop table if exists genesymbol; |
263 | 52e54a06 | Daofeng Li | create table genesymbol ( |
264 | 52e54a06 | Daofeng Li | name varchar(255) not null, |
265 | 52e54a06 | Daofeng Li | symbol varchar(255) null, |
266 | 52e54a06 | Daofeng Li | description text null, |
267 | 52e54a06 | Daofeng Li | id int unsigned not null primary key, |
268 | 52e54a06 | Daofeng Li | index(name)
|
269 | 52e54a06 | Daofeng Li | ); |
270 | 52e54a06 | Daofeng Li | load data local infile 'geneSymbol' into table genesymbol; |