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

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

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), 
Fetal_brain     DECIMAL(10,1), 
Cerebellum      DECIMAL(10,1), 
Whole_Brain     DECIMAL(10,1), 
Cortex        	DECIMAL(10,1), 
Caudate_nucleus DECIMAL(10,1), 
Amygdala        DECIMAL(10,1), 
Thalamus        DECIMAL(10,1), 
Corpus_callosum DECIMAL(10,1), 
Spinal_cord     DECIMAL(10,1), 
Whole_Blood     DECIMAL(10,1), 
Testis        	DECIMAL(10,1), 
Pancreas        DECIMAL(10,1), 
Placenta        DECIMAL(10,1), 
Pituitary_gland DECIMAL(10,1), 
Thyroid       	DECIMAL(10,1), 
Prostate_Cancer DECIMAL(10,1), 
Prostate        DECIMAL(10,1), 
Ovary         	DECIMAL(10,1), 
OVR278E        	DECIMAL(10,1), 
OVR278S        	DECIMAL(10,1), 
Uterus        	DECIMAL(10,1), 
DRG           	DECIMAL(10,1), 
Salivary_gland  DECIMAL(10,1), 
Trachea       	DECIMAL(10,1), 
Lung          	DECIMAL(10,1), 
Thymus        	DECIMAL(10,1), 
Spleen         	DECIMAL(10,1), 
Adrenal_gland   DECIMAL(10,1), 
Kidney        	DECIMAL(10,1), 
Fetal_liver     DECIMAL(10,1), 
Liver         	DECIMAL(10,1), 
Heart         	DECIMAL(10,1), 
HUVEC         	DECIMAL(10,1), 
THYplus        	DECIMAL(10,1), 
THYminus        DECIMAL(10,1), 
Myelogenous_k562 DECIMAL(10,1), 
Lymphoblastic_molt4 DECIMAL(10,1), 
Burkitts_Daudi  DECIMAL(10,1), 
Burkitts_Raji   DECIMAL(10,1), 
Hep3b         	DECIMAL(10,1), 
A2058         	DECIMAL(10,1), 
DOHH2         	DECIMAL(10,1), 
GA10          	DECIMAL(10,1), 
HL60          	DECIMAL(10,1), 
K422          	DECIMAL(10,1), 
Ramos         	DECIMAL(10,1), 
WSU           	DECIMAL(10,1) 
)

expressionTissue_info ( 
TissueName    	VARCHAR(25)
)

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

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