chrom_info (
chrom		VARCHAR(25),
start_pt	INTEGER,
stop_pt		INTEGER,
species		VARCHAR(25),
)

gene_code_info ( 
code    	VARCHAR(2), 
evidence        VARCHAR(200) 
)

repeat_family_info ( 
family  	VARCHAR(50)  
)

repeat_class_info ( 
class   	VARCHAR(50) 
)

repeat_lineage_info (
lineage		VARCHAR(50)
)

tfbind_info ( 
factor		VARCHAR(25), 
id		VARCHAR(20) 
)

genes ( 
id 		INTEGER,
symbol  	VARCHAR(50), 
official	VARCHAR(1), 
description	VARCHAR(200), 
type_of_gene	VARCHAR(55), 
chrom		VARCHAR(10), 
start_pt 	INTEGER, 
stop_pt		INTEGER, 
orientation 	VARCHAR(1), 
cds_start 	INTEGER, 
cds_stop 	INTEGER, 
function_summary VARCHAR(500), 
source		VARCHAR(10), 
num_of_exons	SMALLINT, 
evidence	VARCHAR(2) 
)

gene_dbIDs ( 
id      	INTEGER, 
db		VARCHAR(50), 
dbID		VARCHAR(50),
)

exons ( 
name		VARCHAR(20), 
chrom		VARCHAR(10), 
start_pt 	INTEGER, 
stop_pt 	INTEGER, 
gene_id		INTEGER,
orientation 	VARCHAR(1), 
)

conserved_regions ( 
local_id 	INTEGER, 
chrom    	VARCHAR(10), 
start_pt 	INTEGER, 
stop_pt  	INTEGER, 
species  	VARCHAR(50)
min_perc_id 	SMALLINT, 
ave_percent_identity DECIMAL(5,2), 
num_of_segs 	SMALLINT, 
)

seq_file ( 
ID		VARCHAR(9),
seq_file	VARCHAR(500), 
start_pt	INTEGER, 
stop_pt		INTEGER, 
header		VARCHAR(500) 
)

local_alignments ( 
chrom  		VARCHAR(10), 
start_pt 	INTEGER, 
stop_pt 	INTEGER, 
score 		INTEGER, 
ID		INTEGER, 
orientation 	VARCHAR(30), 
species 	VARCHAR(50), 
start2		INTEGER, 
stop2		INTEGER, 
other_seq   	VARCHAR(9), 
)

gap_free_align ( 
local_id 	INTEGER, 
chrom  		VARCHAR(10), 
start_pt 	INTEGER , 
stop_pt 	INTEGER , 
percent_identity SMALLINT, 
orientation 	VARCHAR(30), 
species 	VARCHAR(50), 
chrom2		VARCHAR(25),
start2  	INTEGER, 
stop2   	INTEGER, 
len   		GENERATED ALWAYS AS (stop_pt - start_pt + 1), 
pct_bin 	GENERATED ALWAYS AS (INTEGER(percent_identity / 10)), 
)

gene_alias ( 
gene_id 	INTEGER,
name		VARCHAR(100), 
source  	VARCHAR(50), 
)

gene_cdd ( 
gene_id		INTEGER,
name		VARCHAR(300), 
domain_acc	VARCHAR(50), 
)

gene_model_prots ( 
gene_id      	INTEGER,
protein		VARCHAR(50), 
acc_no		VARCHAR(50) 
)

gene_product ( 
gene_id		INTEGER,
name		VARCHAR(200) 
)

gene_ontology ( 
gene_id		INTEGER,
category	VARCHAR(100), 
term		VARCHAR(250), 
evidence_code 	VARCHAR(20), 
source		VARCHAR(50), 
goID		VARCHAR(50) 
)

go_defs_info ( 
term		VARCHAR(200), 
goID		VARCHAR(20), 
definition	VARCHAR(2000), 
comments	VARCHAR(1000) 
)

evid_code_info ( 
code		VARCHAR(20), 
description	VARCHAR(200) 
)

snps ( 
snp_id		INTEGER, 
dbsource	VARCHAR(10), 
chrom		VARCHAR(10), 
start_pt	INTEGER, 
stop_pt		INTEGER, 
)

snp_allele ( 
snp_id		INTEGER , 
allele		VARCHAR(700) , 
freq		DECIMAL(20,19), 
type_of		VARCHAR(50), 
)

repeats ( 
name		VARCHAR(50), 
class		VARCHAR(50), 
family		VARCHAR(50), 
strand		VARCHAR(1), 
chrom		VARCHAR(10), 
start_pt	INTEGER, 
stop_pt		INTEGER, 
milliDiv	SMALLINT, 
lineage		VARCHAR(30), 
)

gene_expr ( 
gene_id		INTEGER, 
tissue		VARCHAR(100), 
dbsource	VARCHAR(50) 
)

gene_disorders ( 
gene_id		INTEGER, 
disorder	VARCHAR(150), 
pos_by		VARCHAR(50) 
)

tfbinding_sites ( 
chrom           VARCHAR(10), 
start_pt        INTEGER, 
stop_pt         INTEGER, 
strand          VARCHAR(1), 
score           DECIMAL(3,2), 
id              VARCHAR(20) 
)

conserved_binding_sites ( 
chrom           VARCHAR(10), 
start_pt        INTEGER, 
stop_pt         INTEGER, 
strand          VARCHAR(5), 
score           DECIMAL(3,2), 
id              VARCHAR(20) 
)

recomb_rate ( 
chrom 	        VARCHAR(10), 
start_pt        INTEGER, 
stop_pt         INTEGER, 
name		VARCHAR(20), 
recomb_rate	INTEGER, 
recomb_rateF	INTEGER, 
recomb_rateM	INTEGER, 
source		VARCHAR(20), 
)

CpGislands (  
chrom		VARCHAR(10), 
start_pt	INTEGER, 
stop_pt		INTEGER, 
name		VARCHAR(50), 
)

other_genes ( 
name		VARCHAR(255), 
chrom		VARCHAR(10), 
strand		VARCHAR(1), 
start_pt        INTEGER, 
stop_pt         INTEGER, 
cds_start	INTEGER, 
cds_stop	INTEGER, 
exon_cnt	SMALLINT, 
id		INTEGER, 
track_name	VARCHAR(50) 
)

other_exons ( 
name		SMALLINT, 
chrom           VARCHAR(10), 
strand		VARCHAR(1), 
start_pt        INTEGER, 
stop_pt         INTEGER, 
gene_id		INTEGER 
)

genename_search ( 
gene_id		INTEGER, 
names		VARCHAR(4000) 
)

lscores ( 
chrom           VARCHAR(10), 
start_pt        INTEGER, 
stop_pt         INTEGER, 
score           DECIMAL(4,3), 
dim_bins	GENERATED ALWAYS AS (INTEGER(score)) 
)

regulatory_regions ( 
chrom           VARCHAR(10), 
start_pt        INTEGER, 
stop_pt         INTEGER, 
name		VARCHAR(25) 
)

regulatory_potential ( 
chrom           VARCHAR(10), 
start_pt        INTEGER, 
stop_pt         INTEGER, 
score           DECIMAL(4,3), 
dim_bins        GENERATED ALWAYS AS (INTEGER(score)) 
) 

hbvar_ranges ( 
ID		INTEGER, 
name		VARCHAR(150), 
type_of 	VARCHAR(15), 
chrom 		VARCHAR(10), 
start_pt        INTEGER, 
stop_pt         INTEGER 
)

dberge_ranges ( 
id              INTEGER, 
type_of_experiment VARCHAR(50), 
chrom           VARCHAR(10), 
start_pt        INTEGER, 
stop_pt         INTEGER 
)

gc_percent ( 
chrom           VARCHAR(10), 
start_pt        INTEGER, 
stop_pt         INTEGER, 
score           DECIMAL(4,1)   
)

predicted_promoters ( 
chrom 		VARCHAR(10), 
start_pt        INTEGER, 
stop_pt         INTEGER, 
name		VARCHAR(15), 
score		SMALLINT, 
strand		VARCHAR(1) 
)

functional_promoters ( 
chrom           VARCHAR(10), 
start_pt        INTEGER, 
stop_pt         INTEGER 
)

expressionData ( 
GENEID         	INTEGER, 
AFFY           	VARCHAR(35), 
Fetal_brain     DECIMAL(10,1), 
whole_brain     DECIMAL(12,3), 
temporal_lobe   DECIMAL(12,3), 
parietal_lobe   DECIMAL(12,3), 
occipital_lobe  DECIMAL(12,3), 
prefrontal_cortex       DECIMAL(12,3), 
cingulate_cortex        DECIMAL(12,3), 
cerebellum      DECIMAL(12,3), 
cerebellum_peduncles    DECIMAL(12,3), 
amygdala        DECIMAL(12,3), 
hypothalamus    DECIMAL(12,3),
thalamus        DECIMAL(12,3), 
subthalamic_nucleus     DECIMAL(12,3), 
caudate_nucleus DECIMAL(12,3), 
globus_pallidus DECIMAL(12,3), 
olfactory_bulb  DECIMAL(12,3), 
pons    DECIMAL(12,3), 
medulla_oblongata       DECIMAL(12,3), 
spinal_cord     DECIMAL(12,3), 
ciliary_ganglion        DECIMAL(12,3), 
trigeminal_ganglion     DECIMAL(12,3), 
superior_cervical_ganglion      DECIMAL(12,3), 
dorsal_root_ganglion    DECIMAL(12,3), 
thymus  DECIMAL(12,3), 
tonsil  DECIMAL(12,3), 
lymph_node      DECIMAL(12,3), 
bone_marrow     DECIMAL(12,3), 
BM_CD71_early_erythroid         DECIMAL(12,3), 
BM_CD33_myeloid         DECIMAL(12,3), 
BM_CD105_endothelial    DECIMAL(12,3), 
BM_CD34         DECIMAL(12,3), 
whole_blood     DECIMAL(12,3), 
PB_BDCA4_dentritic_cells        DECIMAL(12,3), 
PB_CD14_monocytes       DECIMAL(12,3), 
PB_CD56_NKCells DECIMAL(12,3), 
PB_CD4_Tcells   DECIMAL(12,3), 
PB_CD8_Tcells   DECIMAL(12,3), 
PB_CD19_Bcells  DECIMAL(12,3), 
leukemia_lymphoblastic_molt4    DECIMAL(12,3), 
B_lymphoblasts_721      DECIMAL(12,3), 
lymphoma_Burkitts_Raji  DECIMAL(12,3), 
leukemia_promyelocytic_hl60     DECIMAL(12,3), 
lymphoma_Burkitts_Daudi DECIMAL(12,3), 
leukemia_myelogenous_k562       DECIMAL(12,3),
colorectal_adenocarcinoma       DECIMAL(12,3), 
appendix        DECIMAL(12,3), 
skin    DECIMAL(12,3), 
adipocyte       DECIMAL(12,3), 
fetal_thyroid   DECIMAL(12,3), 
thyroid DECIMAL(12,3), 
pituitary_gland DECIMAL(12,3), 
adrenal_gland   DECIMAL(12,3), 
adrenal_cortex  DECIMAL(12,3), 
prostate        DECIMAL(12,3), 
salivary_gland  DECIMAL(12,3), 
pancreas        DECIMAL(12,3), 
pancreatic_islets       DECIMAL(12,3), 
atrioventricular_node   DECIMAL(12,3), 
heart   DECIMAL(12,3), 
cardiac_myocytes        DECIMAL(12,3), 
skeletal_muscle DECIMAL(12,3), 
tongue  DECIMAL(12,3), 
smooth_muscle   DECIMAL(12,3), 
uterus  DECIMAL(12,3), 
uterus_corpus   DECIMAL(12,3), 
trachea DECIMAL(12,3), 
bronchial_epithelial_cells      DECIMAL(12,3), 
fetal_lung      DECIMAL(12,3), 
lung    DECIMAL(12,3), 
kidney  DECIMAL(12,3), 
fetal_liver     DECIMAL(12,3), 
liver   DECIMAL(12,3), 
placenta        DECIMAL(12,3), 
testis  DECIMAL(12,3), 
testis_Leydig_cell      DECIMAL(12,3), 
testis_germ_cell        DECIMAL(12,3), 
testis_interstitial     DECIMAL(12,3), 
testis_seminiferous_tubule      DECIMAL(12,3), 
ovary   DECIMAL(12,3) 
)

expressionTissue_info ( 
TissueName    	VARCHAR(25)
)

microRNA ( 
chrom           VARCHAR(10), 
start_pt        INTEGER, 
stop_pt         INTEGER, 
name            VARCHAR(50) 
)

multiple_aligns (  
chrom           VARCHAR(25),  
start_pt        INTEGER,  
stop_pt         INTEGER,  
strand		VARCHAR(1), 
align_id	INTEGER,  
local_id	INTEGER,  
species_build   VARCHAR(10),  
align_builds	VARCHAR(50),  
score		DECIMAL(12,5),  
score_bin	INTEGER GENERATED ALWAYS AS (INTEGER(score / 10000))  
)

mult_align_segments (  
chrom           VARCHAR(25), 
start_pt        INTEGER, 
stop_pt         INTEGER, 
gap_size	SMALLINT, 
local_id        INTEGER
)

enzyme_info (
enzyme		VARCHAR(25),
pattern		VARCHAR(25)
)

restriction_sites (
chrom		VARCHAR(10),
start_pt	INTEGER,
stop_pt		INTEGER,
enzyme		VARCHAR(25)
)

parallel_aligns (
chrom           VARCHAR(10),
start_pt        INTEGER,
stop_pt         INTEGER,
align		VARCHAR(20),
repeat		VARCHAR(20)
)

regulatory_potential_mult (
chrom           VARCHAR(10),
start_pt        INTEGER,
stop_pt         INTEGER,
score		DECIMAL(25, 24),
score_bin	INTEGER GENERATED ALWAYS AS (INTEGER(score * 1000))
)

gene_orthologs (
gene_id         INTEGER,
species         VARCHAR(25),
gene_id2        INTEGER,
gene_sym        VARCHAR(50),
chrom           VARCHAR(10),
start_pt        INTEGER,
stop_pt         INTEGER,
protein_identity DECIMAL(5,2),
cds_identity    DECIMAL(5,2)
)

--internal table to keep track of mRNA and EST tables 
--they are multiple tables with same structure
psl_info (
tablename	varchar(50),
label		varchar(50)
)

--this used for many tables
--human_mrna, human_est, nonhuman_est, nonhuman_mrna, spliced_ests
psl (
id 		INTEGER,
matches 	INTEGER,
misMatches 	INTEGER,
repMatches 	INTEGER,
nCount 		INTEGER,
qNumInsert 	INTEGER,
qBaseInsert 	INTEGER,
tNumInsert 	INTEGER,
tBaseInsert 	INTEGER,
strand 		VARCHAR(2),
qchrom 		VARCHAR(25),
qSize 		INTEGER,
qstart_pt 	INTEGER,
qstop_pt 	INTEGER,
chrom 		VARCHAR(10),
chromSize 	INTEGER,
start_pt 	INTEGER,
stop_pt 	INTEGER,
blockCount 	INTEGER
)

--tables to go with above tables
psl_block (
psl_id 		INTEGER,
chrom 		VARCHAR(10),
start_pt 	INTEGER,
stop_pt 	INTEGER,
qchrom 		VARCHAR(25),
qstart_pt 	INTEGER,
qstop_pt 	INTEGER
)

--another set of general table types, bed table from UCSC
--our tables:tigr_gene_index, unigene
bed_info (
tablename       VARCHAR(50),
label           VARCHAR(50),
tabtype         VARCHAR(50),
blocktab        VARCHAR(50),
xprtab          VARCHAR(50)
)

bed (
id              INTEGER,
chrom           VARCHAR(10),
start_pt        INTEGER,
stop_pt         INTEGER,
name            VARCHAR(100),
score           SMALLINT,
strand          VARCHAR(1),
thickStart      INTEGER,
thickEnd        INTEGER,
reserved        INTEGER,
blockCount      INTEGER
)

bed_blocks (
bed_id          INTEGER,
chrom           VARCHAR(10),
start_pt        INTEGER,
stop_pt         INTEGER
)

net_aligns (
build           VARCHAR(10),
level           SMALLINT,
block           VARCHAR(1),
chrom           VARCHAR(10),
start_pt        INTEGER,
stop_pt         INTEGER,
strand          VARCHAR(1),
chrom2          VARCHAR(25),
start2          INTEGER,
stop2           INTEGER,
chainID         INTEGER,
align_bases     INTEGER,
score           DOUBLE,
qOver           INTEGER,
qDup            INTEGER,
syn_type        VARCHAR(10),
N_bases         INTEGER,
N_bases2        INTEGER,
rep_bases       INTEGER,
rep_bases2      INTEGER,
lsrep_bases     INTEGER,
lsrep_bases2    INTEGER,
ancrep_bases    INTEGER,
ancrep_bases2   INTEGER,
trf_bases       INTEGER,
trf_bases2      INTEGER
)

blat_aligns (
id 		INTEGER,
matches 	INTEGER,
misMatches 	INTEGER,
repMatches 	INTEGER,
nCount 		INTEGER,
qNumInsert 	INTEGER,
qBaseInsert 	INTEGER,
tNumInsert 	INTEGER,
tBaseInsert 	INTEGER,
strand 		VARCHAR(2),
species 	VARCHAR(50),
qchrom 		VARCHAR(25),
qSize 		INTEGER,
qstart_pt 	INTEGER,
qstop_pt 	INTEGER,
chrom 		VARCHAR(10),
chromSize 	INTEGER,
start_pt 	INTEGER,
stop_pt 	INTEGER,
blockCount 	INTEGER
)

rnaGene (
chrom           VARCHAR(10) NOT NULL,
start_pt        INTEGER NOT NULL,
stop_pt         INTEGER NOT NULL,
name            VARCHAR(50),
score           DECIMAL(6,1),
strand          VARCHAR(1) NOT NULL,
source          VARCHAR(40) NOT NULL,
type            VARCHAR(20) NOT NULL,
fullScore       DECIMAL(7,2),
isPsuedo        INTEGER
)

isochore (
chrom           VARCHAR(10) NOT NULL,
start_pt        INTEGER NOT NULL,
stop_pt         INTEGER NOT NULL,
gc_score        DECIMAL(4,1)
)

affy_tfbs ( --ChIP chip data
factor          VARCHAR(25) NOT NULL,
chrom           VARCHAR(10) NOT NULL,
start_pt        INTEGER NOT NULL,
stop_pt         INTEGER NOT NULL,
)

--more tables for internal use
align_info (
align_type      VARCHAR(10),
align_id        SMALLINT,
order_of        SMALLINT,
species         VARCHAR(50),
build           VARCHAR(25)
)

chain_info (
species         VARCHAR(25),
build           VARCHAR(10),
chain_file      VARCHAR(50)
)