1 CREATE TABLE Allele (
\r
2 allele_id int NOT NULL ,
\r
3 allele varchar (255) NOT NULL ,
\r
4 create_time TIMESTAMP NOT NULL ,
\r
5 rev_allele_id int NULL ,
\r
6 src varchar (10) NULL ,
\r
7 last_updated_time TIMESTAMP NULL
\r
11 CREATE TABLE AlleleFlagCode (
\r
12 code smallint NOT NULL ,
\r
13 abbrev varchar (12) NOT NULL ,
\r
14 descrip varchar (255) NOT NULL ,
\r
15 create_time TIMESTAMP NOT NULL
\r
19 CREATE TABLE AlleleMotif (
\r
20 allele_id int NOT NULL ,
\r
21 motif_order int NOT NULL ,
\r
22 motif_id int NOT NULL ,
\r
23 repeat_cnt real NOT NULL ,
\r
24 create_time TIMESTAMP NOT NULL
\r
28 CREATE TABLE AllocIds (
\r
29 name varchar (30) NOT NULL ,
\r
32 create_time TIMESTAMP NULL ,
\r
33 last_updated_time TIMESTAMP NULL ,
\r
34 comment varchar (255) NULL
\r
38 CREATE TABLE Author (
\r
39 pub_id int NOT NULL ,
\r
40 position smallint NOT NULL ,
\r
41 author varchar (255) NOT NULL ,
\r
42 create_time TIMESTAMP NULL ,
\r
43 last_updated_time TIMESTAMP NULL
\r
47 CREATE TABLE Batch_tax_id (
\r
48 batch_id int NOT NULL ,
\r
49 tax_id int NOT NULL ,
\r
50 update_time TIMESTAMP NOT NULL
\r
54 CREATE TABLE ChiSqPValueLookUp (
\r
55 df smallint NOT NULL ,
\r
56 chisq_from float NOT NULL ,
\r
57 chisq_to float NOT NULL ,
\r
58 pvalue_upper_bound float NOT NULL
\r
62 CREATE TABLE CpGCode (
\r
63 code smallint NOT NULL ,
\r
64 abbrev varchar (12) NOT NULL ,
\r
65 descrip varchar (255) NOT NULL
\r
69 CREATE TABLE GenBankDivisionCode (
\r
70 code varchar (6) NOT NULL ,
\r
71 abbrev varchar (40) NOT NULL ,
\r
72 descrip varchar (255) NOT NULL ,
\r
73 display_order smallint NOT NULL ,
\r
74 create_time TIMESTAMP NOT NULL
\r
78 CREATE TABLE GenderCode (
\r
79 code char (1) NOT NULL ,
\r
80 gender varchar (6) NOT NULL
\r
84 CREATE TABLE GenomeBuildInfo (
\r
85 snp_build_id int NOT NULL ,
\r
86 tax_id int NOT NULL ,
\r
87 database_name varchar (32) NOT NULL ,
\r
88 ncbi_build_id int NOT NULL ,
\r
89 ncbi_build_ver smallint NOT NULL ,
\r
90 insert_time TIMESTAMP DEFAULT NOW() NOT NULL ,
\r
91 is_major smallint DEFAULT 1 NOT NULL ,
\r
92 ncbi_genome_tax_id int DEFAULT 1 NOT NULL ,
\r
93 ref_assembly varchar (255) NULL
\r
97 CREATE TABLE GtyAllele (
\r
98 gty_id int NOT NULL ,
\r
99 rev_flag bit NOT NULL ,
\r
100 chr_num smallint NOT NULL ,
\r
101 fwd_allele_id int NOT NULL ,
\r
102 unigty_id int NULL ,
\r
103 create_time TIMESTAMP NOT NULL ,
\r
104 last_updated_time TIMESTAMP NOT NULL
\r
108 CREATE TABLE IUPACna (
\r
109 allele varchar (1) NOT NULL ,
\r
110 meaning varchar (10) NOT NULL ,
\r
111 bitcode varchar (4) NOT NULL ,
\r
112 value smallint NOT NULL ,
\r
113 rev_base char (1) NULL
\r
117 CREATE TABLE LoadHistory (
\r
118 build_id int NOT NULL ,
\r
119 loaddate TIMESTAMP NOT NULL ,
\r
120 status char (30) NOT NULL ,
\r
121 comments varchar (255) NULL ,
\r
122 script varchar (255) NULL ,
\r
123 ftp_done_date TIMESTAMP NULL ,
\r
124 entrez_done_date TIMESTAMP NULL ,
\r
125 blastdb_done_date TIMESTAMP NULL ,
\r
126 web_date TIMESTAMP NULL
\r
130 CREATE TABLE LocTypeCode (
\r
131 code smallint NOT NULL ,
\r
132 abbrev varchar (12) NOT NULL ,
\r
133 descrip varchar (255) NOT NULL ,
\r
134 create_time TIMESTAMP DEFAULT NOW() NOT NULL
\r
138 CREATE TABLE MapLinkCode (
\r
139 which_code varchar (20) NOT NULL ,
\r
140 code smallint NOT NULL ,
\r
141 abbr varchar (10) NULL ,
\r
142 note varchar (255) NULL ,
\r
143 create_time TIMESTAMP NULL ,
\r
144 last_updated_time TIMESTAMP NULL
\r
148 CREATE TABLE Method (
\r
149 method_id int NOT NULL ,
\r
150 handle varchar (20) NOT NULL ,
\r
151 loc_method_id varchar (64) NOT NULL ,
\r
152 loc_method_id_upp varchar (64) NOT NULL ,
\r
153 method_class smallint NULL ,
\r
154 template_type smallint NULL ,
\r
155 seq_both_strands varchar (3) NULL ,
\r
156 mult_pcr_amplification varchar (3) NULL ,
\r
157 mult_clones_tested varchar (3) NULL ,
\r
158 create_time TIMESTAMP NULL ,
\r
159 last_updated_time TIMESTAMP NULL
\r
163 CREATE TABLE MethodClass (
\r
164 meth_class_id smallint NOT NULL ,
\r
165 name varchar (64) NOT NULL ,
\r
166 last_updated_time TIMESTAMP NOT NULL ,
\r
167 validation_status smallint NOT NULL
\r
171 CREATE TABLE MethodLine (
\r
172 method_id int NOT NULL ,
\r
173 line_num smallint NOT NULL ,
\r
174 line varchar (255) NOT NULL ,
\r
175 create_time TIMESTAMP NULL ,
\r
176 last_updated_time TIMESTAMP NULL
\r
180 CREATE TABLE Moltype (
\r
181 code smallint NOT NULL ,
\r
182 moltype varchar (10) NOT NULL ,
\r
183 descrip varchar (255) NOT NULL
\r
187 CREATE TABLE Motif (
\r
188 motif_id int NOT NULL ,
\r
189 motif varchar (253) NOT NULL ,
\r
190 rev_motif_id int NULL ,
\r
191 last_updated_time TIMESTAMP NULL
\r
195 CREATE TABLE ObsGenotype (
\r
196 gty_id int NOT NULL ,
\r
197 obs varchar (512) NOT NULL ,
\r
198 obs_upp_fix varchar (512) NOT NULL ,
\r
199 last_updated_time TIMESTAMP NOT NULL
\r
203 CREATE TABLE ObsVariation (
\r
204 var_id int NOT NULL ,
\r
205 pattern varchar (255) NOT NULL ,
\r
206 create_time TIMESTAMP DEFAULT NOW() NOT NULL ,
\r
207 last_updated_time TIMESTAMP DEFAULT NOW() NULL ,
\r
208 univar_id int NULL ,
\r
209 var_flag smallint NULL
\r
213 CREATE TABLE OrgDbStatus (
\r
214 database_name varchar (32) NOT NULL ,
\r
216 SubSNP_cnt int NULL ,
\r
217 cluster_cnt int NULL ,
\r
218 unmapped_rs_cnt int NULL ,
\r
219 SubInd_cnt int NULL ,
\r
221 SubInd_ss_cnt int NULL ,
\r
222 SubPop_cnt int NULL ,
\r
224 SubPop_ss_cnt int NULL ,
\r
225 GtyFreqBySsPop_ss_cnt int NULL ,
\r
226 AlleleFreqBySsPop_ss_cnt int NULL ,
\r
227 SNPGtyFreq_rs_cnt int NULL ,
\r
228 SNPAlleleFreq_rs_cnt int NULL ,
\r
229 snp_build_max int NULL ,
\r
230 genome_build_max varchar (8) NULL ,
\r
231 map_time TIMESTAMP NULL ,
\r
232 cluster_time_max TIMESTAMP NULL ,
\r
233 create_time TIMESTAMP NULL ,
\r
234 last_SNPBatch_time TIMESTAMP NULL ,
\r
235 last_POPBatch_time TIMESTAMP NULL ,
\r
236 last_INDBatch_time TIMESTAMP NULL ,
\r
238 rsMissenseMax int NULL ,
\r
239 copy2FTP_time TIMESTAMP NULL
\r
243 CREATE TABLE OrganismTax (
\r
244 organism varchar (40) NOT NULL ,
\r
245 tax_id int NOT NULL ,
\r
246 common_name varchar (32) NULL ,
\r
247 gpipe_abbr varchar (7) NOT NULL ,
\r
248 create_time TIMESTAMP NOT NULL ,
\r
249 last_updated_time TIMESTAMP NOT NULL ,
\r
250 comment varchar (255) NULL ,
\r
251 division_cd varchar (6) NULL ,
\r
252 database_name varchar (32) NOT NULL ,
\r
253 short_common_name varchar (32) NOT NULL ,
\r
254 tax_id_rank varchar (5) NOT NULL ,
\r
255 species_tax_id int NOT NULL ,
\r
256 no_freq_summary bit NULL ,
\r
257 entrez_index smallint NULL ,
\r
258 pub_genome_build decimal(4, 1) NULL
\r
262 CREATE TABLE PopClass (
\r
263 pop_id int NOT NULL ,
\r
264 pop_class_id int NOT NULL ,
\r
265 snp_count int NULL
\r
269 CREATE TABLE PopClassCode (
\r
270 pop_class_id int NOT NULL ,
\r
271 pop_class char (50) NOT NULL ,
\r
272 pop_class_text char (255) NOT NULL
\r
276 CREATE TABLE Publication (
\r
277 pub_id int NOT NULL ,
\r
279 handle varchar (20) NOT NULL ,
\r
281 title varchar (235) NOT NULL ,
\r
282 journal varchar (255) NULL ,
\r
283 vol varchar (255) NULL ,
\r
284 suppl varchar (128) NULL ,
\r
285 issue varchar (128) NULL ,
\r
286 i_suppl varchar (128) NULL ,
\r
287 pages varchar (255) NULL ,
\r
288 year smallint NOT NULL ,
\r
289 status smallint NOT NULL ,
\r
290 blobflag smallint NULL ,
\r
291 last_updated TIMESTAMP NOT NULL ,
\r
292 create_time TIMESTAMP NULL ,
\r
293 last_updated_time TIMESTAMP NULL
\r
297 CREATE TABLE SNPGlossary (
\r
298 term varchar (256) NOT NULL ,
\r
299 description varchar (4000) NULL ,
\r
300 last_updated TIMESTAMP DEFAULT NOW() NULL ,
\r
301 used_in varchar (32) NULL
\r
305 CREATE TABLE SNP_tax_id (
\r
306 snp_id int NOT NULL ,
\r
308 update_time TIMESTAMP NULL ,
\r
309 status char (1) DEFAULT 'C' NULL
\r
313 CREATE TABLE SnpChrCode (
\r
314 code varchar (8) NOT NULL ,
\r
315 abbrev varchar (20) NOT NULL ,
\r
316 descrip varchar (255) NOT NULL ,
\r
317 create_time TIMESTAMP NOT NULL ,
\r
318 sort_order smallint NULL ,
\r
319 db_name varchar (32) NULL ,
\r
320 NC_acc varchar (16) NULL
\r
324 CREATE TABLE SnpClassCode (
\r
325 code smallint NOT NULL ,
\r
326 abbrev varchar (20) NOT NULL ,
\r
327 descrip varchar (255) NOT NULL ,
\r
328 display_str varchar (255) NULL
\r
332 CREATE TABLE SnpFunctionCode (
\r
333 code smallint NOT NULL ,
\r
334 abbrev varchar (20) NOT NULL ,
\r
335 descrip varchar (255) NOT NULL ,
\r
336 create_time TIMESTAMP NOT NULL ,
\r
337 top_level_class char (5) NOT NULL ,
\r
338 is_coding smallint NOT NULL ,
\r
343 CREATE TABLE SnpValidationCode (
\r
344 code smallint NOT NULL ,
\r
345 abbrev varchar (64) NOT NULL ,
\r
346 descrip varchar (255) NOT NULL ,
\r
347 create_time TIMESTAMP NULL ,
\r
348 last_updated_time TIMESTAMP NULL
\r
352 CREATE TABLE StrandCode (
\r
353 code smallint NOT NULL ,
\r
354 abbrev varchar (20) NOT NULL ,
\r
355 rs_to_ss_orien bit NOT NULL ,
\r
356 descrip varchar (255) NOT NULL ,
\r
357 create_time TIMESTAMP NOT NULL
\r
361 CREATE TABLE SubSNPDelComm (
\r
362 comment_id smallint NOT NULL ,
\r
363 comment varchar (255) NOT NULL ,
\r
364 create_time TIMESTAMP NULL
\r
368 CREATE TABLE SubSNPSeqTypeCode (
\r
369 code smallint NOT NULL ,
\r
370 abbrev varchar (20) NOT NULL ,
\r
371 descrip varchar (255) NOT NULL
\r
375 CREATE TABLE Submitter (
\r
376 handle varchar (20) NOT NULL ,
\r
377 name varchar (255) NOT NULL ,
\r
378 fax varchar (255) NULL ,
\r
379 phone varchar (255) NULL ,
\r
380 email varchar (255) NULL ,
\r
381 lab varchar (255) NULL ,
\r
382 institution varchar (255) NULL ,
\r
383 address varchar (255) NULL ,
\r
384 create_time TIMESTAMP DEFAULT NOW() NULL ,
\r
385 last_updated_time TIMESTAMP NULL
\r
389 CREATE TABLE TemplateType (
\r
390 temp_type_id smallint NOT NULL ,
\r
391 name varchar (64) NOT NULL ,
\r
392 last_updated_time TIMESTAMP NOT NULL
\r
396 CREATE TABLE UniGty (
\r
397 unigty_id int NOT NULL ,
\r
398 gty_str varchar (255) NULL ,
\r
399 allele_id_1 int NOT NULL ,
\r
400 allele_id_2 int NULL ,
\r
401 create_time TIMESTAMP NOT NULL
\r
405 CREATE TABLE UniVariAllele (
\r
406 univar_id int NOT NULL ,
\r
407 allele_id int NOT NULL ,
\r
408 create_time TIMESTAMP NOT NULL
\r
412 CREATE TABLE UniVariation (
\r
413 univar_id int NOT NULL ,
\r
414 var_str varchar (900) NULL ,
\r
415 allele_cnt smallint NOT NULL ,
\r
416 subsnp_class smallint NOT NULL ,
\r
417 iupack_code char (1) NOT NULL ,
\r
418 top_or_bot_strand char (1) NOT NULL ,
\r
419 create_time TIMESTAMP NOT NULL ,
\r
420 last_updated_time TIMESTAMP NOT NULL ,
\r
421 src_code smallint NULL ,
\r
422 rev_univar_id int NULL
\r
426 CREATE TABLE UniVariationSrcCode (
\r
427 code smallint NOT NULL ,
\r
428 abbrev varchar (20) NOT NULL ,
\r
429 descrip varchar (255) NOT NULL
\r
433 CREATE TABLE VariAllele (
\r
434 var_id int NOT NULL ,
\r
435 allele_id int NOT NULL ,
\r
436 create_time TIMESTAMP NOT NULL
\r
440 CREATE TABLE db_ftp_table_list (
\r
441 table_name varchar (32) NOT NULL ,
\r
442 db_str varchar (64) NOT NULL ,
\r
443 create_time TIMESTAMP DEFAULT NOW() NULL
\r
447 CREATE TABLE db_map_table_name (
\r
448 table_name varchar (32) NOT NULL ,
\r
449 to_index bit NULL ,
\r
450 isCurrent char (1) NULL ,
\r
451 inNonHuman char (1) DEFAULT 'N' NOT NULL
\r
455 CREATE TABLE dn_Allele_rev (
\r
456 allele_id int NOT NULL ,
\r
457 rev_flag smallint NOT NULL ,
\r
458 fwd_allele_id int NOT NULL ,
\r
459 fwd_allele varchar (255) NOT NULL
\r
463 CREATE TABLE dn_Motif_rev (
\r
464 motif_id int NOT NULL ,
\r
465 rev_flag smallint NOT NULL ,
\r
466 fwd_motif varchar (253) NULL ,
\r
467 fwd_motif_id int NULL
\r
471 CREATE TABLE dn_UniGty_allele (
\r
472 unigty_id int NOT NULL ,
\r
473 chr_num smallint NOT NULL ,
\r
474 allele_id int NOT NULL ,
\r
475 create_time TIMESTAMP NOT NULL
\r
479 CREATE TABLE dn_UniGty_rev (
\r
480 unigty_id int NOT NULL ,
\r
481 rev_flag smallint NOT NULL ,
\r
482 fwd_unigty_id int NOT NULL ,
\r
483 fwd_gty_str varchar (255) NULL
\r
487 CREATE TABLE dn_UniVariation_rev (
\r
488 univar_id int NOT NULL ,
\r
489 rev_flag smallint NOT NULL ,
\r
490 fwd_univar_id int NULL ,
\r
491 fwd_univar_str varchar (900) NULL
\r
495 CREATE TABLE dn_baseFlip (
\r
496 base char (1) NULL ,
\r
497 rev_flag smallint NULL ,
\r
498 fwd_base char (1) NULL
\r
502 CREATE TABLE dn_gty2unigty_trueSNP (
\r
503 gty_id int NOT NULL ,
\r
504 rev_flag bit NOT NULL ,
\r
505 unigty_id int NULL ,
\r
506 obs varchar (512) NOT NULL ,
\r
507 gty_str varchar (255) NULL
\r
511 CREATE TABLE dn_summary (
\r
512 tax_id int NOT NULL ,
\r
513 build_id int NOT NULL ,
\r
514 type varchar (20) NOT NULL ,
\r
516 create_time TIMESTAMP DEFAULT NOW() NOT NULL ,
\r
517 last_updated_time TIMESTAMP DEFAULT NOW() NOT NULL
\r