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), 
)

gene_strain (
gene_id     INTEGER, 
strain      VARCHAR(150), 
SID         INTEGER, 
type        VARCHAR(30) 
)

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) 
)

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)) 
) 

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

expressionData (
GENEID         INTEGER,
AFFY           VARCHAR(35),
frontal_cortex DECIMAL(10,1),
cerebral_cortex DECIMAL(10,1),
substantia_nigra DECIMAL(10,1),
cerebellum DECIMAL(10,1),
amygdala DECIMAL(10,1),
hypothalamus DECIMAL(10,1),
hippocampus DECIMAL(10,1),
dorsal_striatum DECIMAL(10,1),
preoptic DECIMAL(10,1),
eye DECIMAL(10,1),
olfactory_bulb DECIMAL(10,1),
spinal_cord_upper DECIMAL(10,1),
spinal_cord_lower DECIMAL(10,1),
trigeminal DECIMAL(10,1),
dorsal_root_ganglia DECIMAL(10,1),
thymus DECIMAL(10,1),
spleen DECIMAL(10,1),
lymph_node DECIMAL(10,1),
bone_marrow DECIMAL(10,1),
B220_B_cells DECIMAL(10,1),
CD4_T_cells DECIMAL(10,1),
CD8_T_cells DECIMAL(10,1),
mammary_gland DECIMAL(10,1),
adrenal_gland DECIMAL(10,1),
prostate DECIMAL(10,1),
pituitary DECIMAL(10,1),
thyroid DECIMAL(10,1),
bone DECIMAL(10,1),
adipose_tissue DECIMAL(10,1),
brown_fat DECIMAL(10,1),
bladder DECIMAL(10,1),
heart DECIMAL(10,1),
skeletal_muscle DECIMAL(10,1),
uterus DECIMAL(10,1),
salivary_gland DECIMAL(10,1),
stomach DECIMAL(10,1),
small_intestine DECIMAL(10,1),
large_intestine DECIMAL(10,1),
pancreas DECIMAL(10,1),
kidney DECIMAL(10,1),
liver DECIMAL(10,1),
lung DECIMAL(10,1),
trachea DECIMAL(10,1),
digits DECIMAL(10,1),
epidermis DECIMAL(10,1),
snout_epidermis DECIMAL(10,1),
tongue DECIMAL(10,1),
med_olfactory_epi DECIMAL(10,1),
vomeralnasal_organ DECIMAL(10,1),
placenta DECIMAL(10,1),
umbilical_cord DECIMAL(10,1),
testis DECIMAL(10,1),
ovary DECIMAL(10,1),
oocyte DECIMAL(10,1),
fertilized_egg DECIMAL(10,1),
blastocysts DECIMAL(10,1),
embryo_day_65 DECIMAL(10,1),
embryo_day_75 DECIMAL(10,1),
embryo_day_85 DECIMAL(10,1),
embryo_day_95 DECIMAL(10,1),
embryo_day_105 DECIMAL(10,1)
) 

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)
)