1 CREATE TABLE AlleleFreqBySsPop (
2 subsnp_id int NOT NULL ,
4 allele_id int NOT NULL ,
5 source varchar (2) NOT NULL ,
8 last_updated_time TIMESTAMP NOT NULL
13 batch_id int NOT NULL ,
14 handle varchar (20) NOT NULL ,
15 loc_batch_id varchar (64) NOT NULL ,
16 loc_batch_id_upp varchar (64) NOT NULL ,
17 batch_type char (3) NOT NULL ,
18 status smallint NULL ,
19 simul_sts_status smallint NOT NULL ,
20 moltype varchar (8) NOT NULL ,
21 method_id int NOT NULL ,
23 synonym_type varchar (255) NULL ,
24 submitted_time TIMESTAMP NOT NULL ,
25 linkout_url varchar (255) NULL ,
27 last_updated_time TIMESTAMP NULL ,
28 success_rate_int int NULL ,
35 CREATE TABLE BatchCita (
36 batch_id int NOT NULL ,
37 position int NOT NULL ,
39 citation varchar (255) NOT NULL ,
40 create_time TIMESTAMP NULL ,
41 last_updated_time TIMESTAMP NULL
45 CREATE TABLE BatchCommLine (
46 batch_id int NOT NULL ,
47 line_num smallint NOT NULL ,
48 line varchar (255) NOT NULL ,
49 create_time TIMESTAMP NULL ,
50 last_updated_time TIMESTAMP NULL
54 CREATE TABLE BatchCultivar (
55 batch_id int NOT NULL ,
56 line_num smallint NOT NULL ,
57 line varchar (255) NULL ,
58 create_time TIMESTAMP NULL ,
59 last_updated_time TIMESTAMP NULL
63 CREATE TABLE BatchMeExLine (
64 batch_id int NOT NULL ,
65 line_num smallint NOT NULL ,
66 line varchar (255) NOT NULL ,
67 create_time TIMESTAMP NULL ,
68 last_updated_time TIMESTAMP NULL
72 CREATE TABLE BatchStrain (
73 batch_id int NOT NULL ,
74 line_num smallint NOT NULL ,
75 line varchar (255) NOT NULL ,
76 create_time TIMESTAMP NULL ,
77 last_updated_time TIMESTAMP NULL
81 CREATE TABLE BatchValCode (
82 batch_id int NOT NULL ,
83 validation_status smallint NOT NULL
87 CREATE TABLE Contact (
88 batch_id int NOT NULL ,
89 handle varchar (20) NOT NULL ,
90 name varchar (255) NOT NULL ,
91 fax varchar (255) NULL ,
92 phone varchar (255) NULL ,
93 email varchar (255) NULL ,
94 lab varchar (255) NULL ,
95 institution varchar (255) NULL ,
96 address varchar (255) NULL ,
97 create_time TIMESTAMP NULL ,
98 last_updated_time TIMESTAMP NULL
102 CREATE TABLE FreqSummaryBySsPop (
103 subsnp_id int NOT NULL ,
104 pop_id int NOT NULL ,
105 source varchar (1) NOT NULL ,
106 chr_cnt int NOT NULL ,
107 ind_cnt int NOT NULL ,
108 non_founder_ind_cnt int NOT NULL ,
114 last_updated_time TIMESTAMP NOT NULL
118 CREATE TABLE GeneIdToName (
119 gene_id int NOT NULL ,
120 gene_symbol varchar (64) NOT NULL ,
121 gene_name varchar (255) NULL ,
122 gene_type varchar (255) NULL ,
123 tax_id int NOT NULL ,
124 last_update_time TIMESTAMP NOT NULL ,
125 ref_tax_id int NOT NULL ,
126 dbSNP_tax_id int NOT NULL ,
127 ins_time TIMESTAMP NULL
131 CREATE TABLE GtyFreqBySsPop (
132 subsnp_id int NOT NULL ,
133 pop_id int NOT NULL ,
134 unigty_id int NOT NULL ,
135 source varchar (1) NULL ,
138 last_updated_time TIMESTAMP NOT NULL
142 CREATE TABLE IndGrpCode (
143 code smallint NOT NULL ,
144 name varchar (32) NOT NULL ,
145 descrip varchar (255) NOT NULL
149 CREATE TABLE IndivBySource (
150 ind_id int NOT NULL ,
151 src_id int NOT NULL ,
152 src_ind_id varchar (64) NOT NULL ,
153 create_time TIMESTAMP NOT NULL ,
154 src_ind_grp varchar (64) NULL
158 CREATE TABLE IndivSourceCode (
160 name varchar (22) NOT NULL ,
161 descrip varchar (255) NULL ,
162 create_time TIMESTAMP NOT NULL ,
163 src_type varchar (10) NULL ,
164 display_order smallint NULL
168 CREATE TABLE Individual (
169 ind_id int NOT NULL ,
170 descrip varchar (255) NULL ,
171 create_time TIMESTAMP NOT NULL ,
173 ind_grp smallint NULL
177 CREATE TABLE OmimVarLocusIdSNP (
178 omim_id int NOT NULL ,
180 omimvar_id char (4) NULL ,
181 locus_symbol char (10) NULL ,
182 var1 char (20) NULL ,
183 aa_position int NULL ,
184 var2 char (20) NULL ,
185 var_class int NOT NULL ,
190 CREATE TABLE Pedigree (
191 ped_id numeric(7, 0) NOT NULL ,
192 curator varchar (12) NOT NULL ,
193 curator_ped_id varchar (12) NOT NULL ,
194 create_time TIMESTAMP NOT NULL
198 CREATE TABLE PedigreeIndividual (
199 ped_id decimal(7, 0) NOT NULL ,
200 ind_id int NOT NULL ,
204 create_time TIMESTAMP NOT NULL
208 CREATE TABLE PopLine (
209 pop_id int NOT NULL ,
210 line_num int NOT NULL ,
211 line varchar (255) NOT NULL ,
212 create_time TIMESTAMP NULL ,
213 last_updated_time TIMESTAMP NULL
217 CREATE TABLE PopMandLine (
218 pop_id int NOT NULL ,
219 line_num smallint NOT NULL ,
220 line varchar (255) NOT NULL ,
221 create_time TIMESTAMP NULL ,
222 last_updated_time TIMESTAMP NULL
226 CREATE TABLE Population (
227 pop_id int NOT NULL ,
228 handle varchar (20) NOT NULL ,
229 loc_pop_id varchar (64) NOT NULL ,
230 loc_pop_id_upp varchar (64) NOT NULL ,
231 create_time TIMESTAMP NULL ,
232 last_updated_time TIMESTAMP NULL ,
237 CREATE TABLE RsMergeArch (
241 orien smallint NULL ,
242 create_time TIMESTAMP NOT NULL ,
243 last_updated_time TIMESTAMP NOT NULL ,
245 orien2Current smallint NULL ,
246 comment varchar (255) NULL
252 avg_heterozygosity real NULL ,
254 create_time TIMESTAMP NULL ,
255 last_updated_time TIMESTAMP NULL ,
256 CpG_code smallint NULL ,
258 validation_status smallint NULL ,
259 exemplar_subsnp_id int NULL ,
261 cnt_subsnp int NULL ,
262 map_property smallint NULL
267 snp_id int NOT NULL ,
268 protein_acc char (50) NOT NULL ,
269 master_gi int NOT NULL ,
270 neighbor_gi int NOT NULL ,
271 aa_position int NOT NULL ,
272 var_res char (1) NOT NULL ,
273 contig_res char (1) NOT NULL ,
274 neighbor_res char (1) NOT NULL ,
275 neighbor_pos int NOT NULL ,
276 var_color int NOT NULL ,
277 var_label int NOT NULL
281 CREATE TABLE SNPAlleleFreq (
282 snp_id int NOT NULL ,
283 allele_id int NOT NULL ,
286 last_updated_time TIMESTAMP NOT NULL
290 CREATE TABLE SNPAncestralAllele (
291 snp_id int NOT NULL ,
292 ancestral_allele_id int NOT NULL ,
293 batch_id int DEFAULT 0 NOT NULL
297 CREATE TABLE SNPGtyFreq (
298 snp_id int NOT NULL ,
299 unigty_id int NOT NULL ,
302 last_updated_time TIMESTAMP NOT NULL
306 CREATE TABLE SNPHWProb (
307 snp_id int NOT NULL ,
311 ind_cnt smallint NULL ,
312 last_updated_time TIMESTAMP NULL
316 CREATE TABLE SNPHistory (
317 snp_id int NOT NULL ,
318 create_time TIMESTAMP NULL ,
319 last_updated_time TIMESTAMP NOT NULL ,
320 history_create_time TIMESTAMP NULL ,
321 comment varchar (255) NULL
325 CREATE TABLE SNPPubmed (
326 snp_id int NOT NULL ,
327 subsnp_id int NOT NULL ,
328 pubmed_id int NOT NULL ,
329 type varchar (16) NOT NULL ,
331 upd_date TIMESTAMP NOT NULL
335 CREATE TABLE SNPSubSNPLink (
338 substrand_reversed_flag smallint NULL ,
339 create_time TIMESTAMP NULL ,
340 last_updated_time TIMESTAMP NULL ,
342 comment varchar (255) NULL
346 CREATE TABLE SNPSubSNPLinkHistory (
350 history_create_time TIMESTAMP NOT NULL ,
351 link_create_time TIMESTAMP NULL ,
352 link_last_updated_time TIMESTAMP NULL ,
353 orien smallint NULL ,
354 build_id_when_history_made int NULL ,
355 comment varchar (255) NULL
359 CREATE TABLE SNPVal (
360 batch_id int NOT NULL ,
365 CREATE TABLE SNP_HGVS (
367 hgvs_name varchar (256) NULL ,
368 source varchar (8) NOT NULL ,
369 upd_time TIMESTAMP NOT NULL
373 CREATE TABLE SNP_bitfield (
374 snp_id int NOT NULL ,
375 ver_code smallint NULL ,
376 link_prop_b1 smallint NULL ,
377 link_prop_b2 smallint NULL ,
378 gene_prop_b1 smallint NULL ,
379 gene_prop_b2 smallint NULL ,
380 map_prop smallint NULL ,
381 freq_prop smallint NULL ,
382 gty_prop smallint NULL ,
383 hapmap_prop smallint NULL ,
384 pheno_prop smallint NULL ,
385 variation_class smallint NOT NULL ,
386 quality_check smallint NULL ,
387 upd_time TIMESTAMP NOT NULL ,
392 CREATE TABLE SubPop (
393 batch_id int NOT NULL ,
394 subsnp_id int NOT NULL ,
395 pop_id int NOT NULL ,
396 type char (3) NOT NULL ,
397 samplesize int NOT NULL ,
398 submitted_strand_code smallint NULL ,
399 submitted_rs int NULL ,
400 allele_flag smallint NULL ,
401 ambiguity_status smallint NULL ,
402 sub_heterozygosity real NULL ,
403 est_heterozygosity real NULL ,
404 est_het_se_sq real NULL ,
405 last_updated_time TIMESTAMP DEFAULT NOW() NOT NULL ,
406 observed varchar (1000) NULL ,
407 sub_het_se_sq real NULL ,
408 subpop_id SERIAL NOT NULL
412 CREATE TABLE SubPopAllele (
413 batch_id int NOT NULL ,
414 subsnp_id int NOT NULL ,
415 pop_id int NOT NULL ,
416 allele char (1) NOT NULL ,
417 other varchar (255) NULL ,
422 data_src varchar (6) NULL ,
424 last_updated_time TIMESTAMP NULL ,
425 allele_flag smallint NULL ,
428 subpop_id int NOT NULL
432 CREATE TABLE SubPopGty (
433 subpop_id int NOT NULL ,
434 gty_id int NOT NULL ,
435 gty_str varchar (255) NULL ,
438 last_updated_time TIMESTAMP NOT NULL
442 CREATE TABLE SubSNP (
443 subsnp_id int NOT NULL ,
444 known_snp_handle varchar (20) NULL ,
445 known_snp_loc_id varchar (64) NULL ,
446 known_snp_loc_id_upp varchar (64) NULL ,
447 batch_id int NOT NULL ,
448 loc_snp_id varchar (64) NULL ,
449 loc_snp_id_upp varchar (64) NULL ,
450 synonym_names varchar (255) NULL ,
451 loc_sts_id varchar (64) NULL ,
452 loc_sts_id_upp varchar (64) NULL ,
453 segregate char (1) NOT NULL ,
454 indiv_homozygosity_detected char (1) NULL ,
455 PCR_confirmed_ind char (1) NULL ,
456 gene_name varchar (64) NULL ,
457 sequence_len int NULL ,
458 samplesize int NULL ,
459 EXPRESSED_SEQUENCE_ind char (1) NULL ,
460 SOMATIC_ind char (1) NULL ,
461 sub_locus_id int NULL ,
462 create_time TIMESTAMP NULL ,
463 last_updated_time TIMESTAMP NULL ,
464 ancestral_allele varchar (255) NULL ,
465 CpG_code smallint NULL ,
466 variation_id int NULL ,
467 top_or_bot_strand char (1) NULL ,
468 validation_status smallint NULL ,
470 tax_id int NOT NULL ,
475 CREATE TABLE SubSNPAcc_ins (
476 subsnp_id int NOT NULL ,
477 acc_type_ind char (1) NOT NULL ,
478 acc_part varchar (16) NOT NULL ,
483 CREATE TABLE SubSNPCommLine_ins (
484 subsnp_id int NOT NULL ,
485 line_num smallint NOT NULL ,
486 line varchar (255) NOT NULL
490 CREATE TABLE SubSNPHGVS (
491 subsnp_id int NOT NULL ,
492 sub_hgvs_c varchar (32) NULL ,
493 sub_hgvs_g varchar (32) NULL ,
494 sub_hgvs_p varchar (32) NULL ,
495 cal_hgvs_c varchar (32) NULL ,
496 cal_hgvs_g varchar (32) NULL ,
497 cal_hgvs_p varchar (32) NULL ,
498 upd_time TIMESTAMP NULL ,
503 CREATE TABLE SubSNPLinkout (
504 subsnp_id int NOT NULL ,
505 url_val varchar (255) NOT NULL ,
506 updated_time TIMESTAMP NULL ,
507 link_type varchar (3) DEFAULT 'NA' NOT NULL
511 CREATE TABLE SubSNPMdFailLn (
512 subsnp_id int NOT NULL ,
513 line_num smallint NOT NULL ,
514 line varchar (255) NOT NULL
518 CREATE TABLE SubSNPNoVariSeq (
519 subsnp_id int NOT NULL ,
520 line_num smallint NOT NULL ,
521 line varchar (255) NOT NULL
525 CREATE TABLE SubSNPOmim (
526 subsnp_id int NOT NULL ,
527 omim_id int NOT NULL ,
528 allele_variant_id varchar (32) NULL ,
529 update_time TIMESTAMP NULL ,
534 CREATE TABLE SubSNPPubmed (
535 subsnp_id int NOT NULL ,
536 line_num int NOT NULL ,
537 pubmed_id int NOT NULL ,
538 updated_time TIMESTAMP NULL
542 CREATE TABLE SubSNPSeq3_ins (
543 subsnp_id int NOT NULL ,
544 type smallint NOT NULL ,
545 line_num smallint NOT NULL ,
546 line varchar (255) NOT NULL
550 CREATE TABLE SubSNPSeq5_ins (
551 subsnp_id int NOT NULL ,
552 type smallint NOT NULL ,
553 line_num smallint NOT NULL ,
554 line varchar (255) NOT NULL
558 CREATE TABLE SubSNPSeqPos (
559 subsnp_id int NOT NULL ,
560 contig_acc varchar (20) NOT NULL ,
561 contig_pos int NOT NULL ,
562 chr varchar (2) NULL ,
563 upstream_len int NOT NULL ,
564 downstream_len int NOT NULL ,
565 last_update_time TIMESTAMP NOT NULL ,
566 mrna_acc varchar (24) NULL
570 CREATE TABLE SubSNP_top_or_bot (
571 subsnp_id int NOT NULL ,
572 top_or_bot char (1) NULL ,
574 last_updated_time TIMESTAMP NULL
578 CREATE TABLE SubmittedIndividual (
579 submitted_ind_id int NOT NULL ,
580 pop_id int NOT NULL ,
581 loc_ind_id_upp varchar (64) NOT NULL ,
583 create_time TIMESTAMP NOT NULL ,
584 last_updated_time TIMESTAMP NULL ,
585 tax_id int NOT NULL ,
586 loc_ind_alias varchar (64) NULL ,
587 loc_ind_id varchar (64) NULL ,
588 loc_ind_grp varchar (64) NULL ,
589 ploidy smallint DEFAULT 2 NULL
593 CREATE TABLE Synonym (
594 subsnp_id int NOT NULL ,
595 type varchar (64) NOT NULL ,
596 name varchar (64) NULL
600 CREATE TABLE b132_ContigInfo_37_1 (
601 ctg_id int NOT NULL ,
602 tax_id int NOT NULL ,
603 contig_acc varchar (32) NOT NULL ,
604 contig_ver smallint NULL ,
605 contig_name varchar (32) NOT NULL ,
606 contig_chr varchar (32) NULL ,
607 contig_start int NULL ,
608 contig_end int NULL ,
609 orient smallint NULL ,
611 group_term varchar (32) NULL ,
612 group_label varchar (32) NULL ,
613 contig_label varchar (32) NULL ,
616 last_updated_time TIMESTAMP NOT NULL ,
617 placement_status smallint NOT NULL
621 CREATE TABLE b132_GenomeAssemblyInfo_37_1 (
622 assembly varchar (32) NOT NULL ,
623 assembly_type varchar (8) NOT NULL ,
624 rep_order smallint NULL ,
625 comment varchar (255) NULL ,
626 upd_time TIMESTAMP NULL
630 CREATE TABLE b132_MapLinkInfo_37_1 (
632 accession varchar (127) NULL ,
633 accession_ver int NULL
637 CREATE TABLE b132_MapLink_37_1 (
638 snp_type char (2) NOT NULL ,
641 accession_how_cd int NULL ,
648 loc_type smallint NULL ,
650 process_time TIMESTAMP NULL ,
651 process_status int NULL ,
652 orientation int NULL ,
653 allele varchar (1024) NULL ,
654 aln_quality float NULL ,
662 ctg_orient smallint NULL ,
663 source varchar (5) NOT NULL
667 CREATE TABLE b132_ProteinInfo_37_1 (
669 prot_acc varchar (128) NULL ,
674 CREATE TABLE b132_SNPChrPosOnRef_37_1 (
676 chr varchar (32) NULL ,
679 neighbor_snp_list int NULL ,
680 isPAR varchar (1) NOT NULL
684 CREATE TABLE b132_SNPContigLoc_37_1 (
685 snp_type char (2) NOT NULL ,
686 snp_id int NOT NULL ,
687 ctg_id int NOT NULL ,
688 asn_from int NOT NULL ,
689 asn_to int NOT NULL ,
692 lc_ngbr int NOT NULL ,
693 rc_ngbr int NOT NULL ,
694 loc_type smallint NOT NULL ,
695 phys_pos_from int NULL ,
696 snp_bld_id int NOT NULL ,
697 last_updated_time TIMESTAMP NOT NULL ,
698 process_status int NOT NULL ,
699 orientation smallint NULL ,
700 allele varchar (1024) NULL ,
701 loc_sts_uid int NULL ,
702 aln_quality float NULL ,
710 CREATE TABLE b132_SNPContigLocusId_37_1 (
712 contig_acc varchar (32) NULL ,
713 contig_ver smallint NULL ,
717 locus_symbol varchar (128) NULL ,
718 mrna_acc varchar (128) NULL ,
720 protein_acc varchar (128) NULL ,
721 protein_ver int NULL ,
723 reading_frame int NULL ,
724 allele varchar (256) NULL ,
725 residue varchar (1024) NULL ,
726 aa_position int NULL ,
727 build_id varchar (4) NOT NULL ,
729 mrna_start int NULL ,
731 codon varchar (1024) NULL ,
732 protRes char (3) NULL ,
735 mrna_orien smallint NULL ,
736 cp_mrna_ver int NULL ,
737 cp_mrna_gi int NULL ,
738 verComp varchar (7) NOT NULL
742 CREATE TABLE b132_SNPMapInfo_37_1 (
743 snp_type char (2) NOT NULL ,
744 snp_id int NOT NULL ,
745 chr_cnt int NOT NULL ,
746 contig_cnt int NOT NULL ,
747 loc_cnt int NOT NULL ,
748 weight int NOT NULL ,
749 hap_cnt int NOT NULL ,
750 placed_cnt int NOT NULL ,
751 grouped_cnt int NOT NULL ,
752 unplaced_cnt int NOT NULL ,
754 assembly varchar (32) NULL
758 CREATE TABLE dn_IND_batchCount (
759 batch_id int NOT NULL ,
760 pop_id int NOT NULL ,
761 ss_cnt int NOT NULL ,
762 rs_cnt int NOT NULL ,
763 ind_cnt int NOT NULL ,
764 create_time TIMESTAMP NOT NULL
768 CREATE TABLE dn_IND_batch_pop (
769 batch_id smallint NOT NULL ,
770 pop_id int NOT NULL ,
771 update_time TIMESTAMP NOT NULL
775 CREATE TABLE dn_PopulationIndGrp (
776 pop_id int NOT NULL ,
777 ind_grp_name varchar (32) NOT NULL ,
778 ind_grp_code smallint NOT NULL
782 CREATE TABLE dn_batchCount (
783 batch_id int NOT NULL ,
784 ss_cnt int NOT NULL ,
785 rs_cnt int NOT NULL ,
786 rs_validated_cnt int NOT NULL ,
787 create_time TIMESTAMP NOT NULL ,
793 CREATE TABLE dn_handleCount (
794 handle varchar (20) NOT NULL ,
795 batch_type char (3) NOT NULL ,
796 ss_cnt int NOT NULL ,
798 rs_validated_cnt int NULL ,
799 create_time TIMESTAMP NOT NULL
803 CREATE TABLE dn_snpFxnCnt (
804 build_id int NOT NULL ,
805 fxn_class smallint NULL ,
806 snp_cnt int NOT NULL ,
807 gene_cnt int NOT NULL ,
808 create_time TIMESTAMP NOT NULL ,
809 last_updated_time TIMESTAMP NOT NULL ,
814 CREATE TABLE dn_table_rowcount (
815 tabname varchar (64) NOT NULL ,
816 row_cnt int NOT NULL ,
817 build_id int NOT NULL ,
818 update_time TIMESTAMP NOT NULL ,
819 rows_in_spaceused int NULL ,
820 reserved_KB_spaceused int NULL ,
821 data_KB_spaceused int NULL ,
822 index_size_KB_spaceused int NULL ,
823 unused_KB_spaceused int NULL