Database Model plugin_tmp.xml

analysis
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
analysis_id INTEGER(10) PK NN UNSIGNED     AI
evidence_id INTEGER   NN UNSIGNED      
name VARCHAR(45)   NN     NC  
method_id INTEGER(10)   NN UNSIGNED      
cell_id INTEGER(10)     UNSIGNED      
time_id INTEGER(10)     UNSIGNED      
ref_id INTEGER(10)     UNSIGNED   NC  
comments TEXT            
edit_date TIMESTAMP   NN   0000-00-00    
project_id INTEGER(10)   NN UNSIGNED   NC  
IndexName IndexType Columns
PRIMARY PRIMARY analysis_id
analysis_U1 Index name
ref_id
project_id


analysis_i_link
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
analysis_i_link_id INTEGER PK NN UNSIGNED     AI
analysis_input_id INTEGER(10)   NN UNSIGNED   NC, null if single entry in this table links to many analysis_o_link entries  
analysis_o_link_id INTEGER     UNSIGNED   NC  
IndexName IndexType Columns
PRIMARY PRIMARY analysis_i_link_id
analysis_i_link_U1 Index analysis_input_id
analysis_o_link_id


analysis_input
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
analysis_input_id INTEGER(10) PK NN UNSIGNED     AI
analysis_id INTEGER(10)   NN UNSIGNED      
io_type_id INTEGER(10)   NN UNSIGNED      
edit_date TIMESTAMP   NN   0000-00-00    
project_id INTEGER(10)   NN UNSIGNED      
IndexName IndexType Columns
PRIMARY PRIMARY analysis_input_id


analysis_o_link
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
analysis_o_link_id INTEGER PK NN UNSIGNED     AI
analysis_i_link_id INTEGER     UNSIGNED   NC, null if single entry in this table links to many analysis_i_link entries  
analysis_output_id INTEGER(10)   NN UNSIGNED   NC  
IndexName IndexType Columns
PRIMARY PRIMARY analysis_o_link_id
analysis_o_link_U1 Index analysis_i_link_id
analysis_output_id


analysis_output
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
analysis_output_id INTEGER(10) PK NN UNSIGNED     AI
io_type_id INTEGER(10)   NN UNSIGNED      
edit_date TIMESTAMP   NN   0000-00-00    
project_id INTEGER(10)   NN UNSIGNED      
IndexName IndexType Columns
PRIMARY PRIMARY analysis_output_id


anchor_reg_seq
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
anchor_reg_seq_id INTEGER PK NN UNSIGNED     AI
marker_id INTEGER(10)     UNSIGNED   NC  
tsr_id INTEGER     UNSIGNED   NC  
reg_seq_id INTEGER(10)   NN UNSIGNED   NC  
IndexName IndexType Columns
PRIMARY PRIMARY anchor_reg_seq_id


cell
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
cell_id INTEGER(10) PK NN UNSIGNED     AI
name VARCHAR(45)         NC  
tissue_ontology VARCHAR(255)            
status ENUM('na','primary','cell line')            
description VARCHAR(255)            
species VARCHAR(20)   NN     NC  
edit_date TIMESTAMP   NN   0000-00-00    
project_id INTEGER(10)   NN UNSIGNED   NC  
IndexName IndexType Columns
PRIMARY PRIMARY cell_id
cell_U1 Index name
species
project_id


condition
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
condition_id INTEGER(10) PK NN UNSIGNED     AI
cond_type VARCHAR(45)   NN        
molecule VARCHAR(20)   NN        
description TEXT            
concentration VARCHAR(20)   NN        
scale VARCHAR(20)   NN        
edit_date TIMESTAMP   NN   0000-00-00    
project_id INTEGER(10)   NN UNSIGNED      
IndexName IndexType Columns
PRIMARY PRIMARY condition_id


conserved_el
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
conserved_el_id INTEGER(10) PK NN UNSIGNED     AI
conserved_group_id INTEGER(10)   NN UNSIGNED   NC  
reg_seq_id INTEGER(10)   NN UNSIGNED   NC  
homolog_set_id INTEGER(10)   NN UNSIGNED   NC  
edit_date TIMESTAMP   NN   0000-00-00    
project_id INTEGER(10)   NN UNSIGNED      
IndexName IndexType Columns
PRIMARY PRIMARY conserved_el_id
conserved_el_U1 Index conserved_group_id
reg_seq_id
homolog_set_id


construct
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
construct_id INTEGER(10) PK NN UNSIGNED     AI
construct_name VARCHAR(45)   NN     NC  
description TEXT            
sequence TEXT   NN        
edit_date TIMESTAMP   NN   0000-00-00    
project_id INTEGER(10)   NN UNSIGNED   NC  
IndexName IndexType Columns
PRIMARY PRIMARY construct_id
construct_U1 Index construct_name
project_id


coordinate
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
coordinate_id INTEGER(10) PK NN UNSIGNED     AI
location_id INTEGER(10)   NN UNSIGNED   NC  
strand ENUM('+','-')   NN     NC,+/-  
begin INTEGER(10)   NN UNSIGNED   NC, chromosome choordinate system  
end INTEGER(10)   NN UNSIGNED   same  
length INTEGER(10)   NN UNSIGNED   NC  
edit_date TIMESTAMP   NN   0000-00-00    
project_id INTEGER(10)   NN UNSIGNED   NC  
IndexName IndexType Columns
PRIMARY PRIMARY coordinate_id
coordinate_U1 Index location_id
strand
begin
end
project_id


dataset
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
dataset_id INTEGER PK NN UNSIGNED     AI
reg_seq_id INTEGER(10)     UNSIGNED   NC  
construct_id INTEGER(10)     UNSIGNED   NC  
dataset_name VARCHAR(45)   NN     NC  
edit_date TIMESTAMP   NN        
project_id INTEGER   NN UNSIGNED   NC  
IndexName IndexType Columns
PRIMARY PRIMARY dataset_id


db_source
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
db_source_id INTEGER(10) PK NN UNSIGNED     AI
db_name VARCHAR(45)   NN     NC  
db_subset VARCHAR(255)         NC  
edit_date TIMESTAMP   NN   0000-00-00    
project_id INTEGER(10)   NN UNSIGNED      
IndexName IndexType Columns
PRIMARY PRIMARY db_source_id
db_source_U1 Index db_name
db_subset


evidence
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
evidence_id INTEGER PK NN UNSIGNED     AI
type_evid ENUM('curated','ADMC','prediction')   NN     NC  
status_evid ENUM('approved','provisional','archivable','removable')   NN     NC  
edit_date TIMESTAMP   NN        
IndexName IndexType Columns
PRIMARY PRIMARY evidence_id
evidence_U1 Unique Index type_evid
status_evid


expression
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
expression_id INTEGER(10) PK NN UNSIGNED     AI
qualitative ENUM('highly induced','induced','no change','repressed','strongly repressed')            
quantitative VARCHAR(250)            
scale VARCHAR(45)            
comments TEXT            
edit_date TIMESTAMP   NN   0000-00-00    
project_id INTEGER(10)   NN UNSIGNED      
IndexName IndexType Columns
PRIMARY PRIMARY expression_id


funct_tf
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
funct_tf_id INTEGER(10) PK NN UNSIGNED     AI
funct_tf_name VARCHAR(255)   NN     NC  
ref_id INTEGER(10)     UNSIGNED      
edit_date TIMESTAMP   NN   0000-00-00    
project_id INTEGER(10)   NN UNSIGNED   NC  
IndexName IndexType Columns
PRIMARY PRIMARY funct_tf_id
funct_tf_U1 Index funct_tf_name
project_id


gene_source
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
gene_source_id INTEGER(10) PK NN UNSIGNED     AI
db_source_id INTEGER(10)   NN UNSIGNED   NC  
db_accn VARCHAR(50)   NN     NC, ensembl gene stable id  
description VARCHAR(255)            
edit_date TIMESTAMP   NN   0000-00-00    
project_id INTEGER(10)   NN UNSIGNED      
IndexName IndexType Columns
PRIMARY PRIMARY gene_source_id
gene_source_U1 Index db_source_id
db_accn


homolog
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
homolog_id INTEGER(10) PK NN UNSIGNED     AI
gene_source_id INTEGER(10)   NN UNSIGNED      
homolog_set_id INTEGER(10)   NN UNSIGNED      
db_source_id INTEGER(10)   NN UNSIGNED      
homology_type ENUM('na','ortholog','paralog')   NN        
edit_date TIMESTAMP   NN   0000-00-00    
project_id INTEGER(10)   NN UNSIGNED      
IndexName IndexType Columns
PRIMARY PRIMARY homolog_id


interaction
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
interaction_id INTEGER(10) PK NN UNSIGNED     AI
qualitative VARCHAR(45)         NC  
quantitative FLOAT         NC  
scale VARCHAR(45)         NC  
comments TEXT            
edit_date TIMESTAMP   NN   0000-00-00    
project_id INTEGER(10)   NN UNSIGNED   NC  
IndexName IndexType Columns
PRIMARY PRIMARY interaction_id
interaction_u1 Index qualitative
quantitative
scale
project_id


io_type
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
io_type_id INTEGER(10) PK NN UNSIGNED     AI
io_element_id INTEGER(10)   NN UNSIGNED   NC  
io_element_type ENUM('condition','expression','interaction','reg_seq','construct','TF')   NN     NC  
edit_date TIMESTAMP   NN   0000-00-00    
project_id INTEGER(10)   NN UNSIGNED   NC  
IndexName IndexType Columns
PRIMARY PRIMARY io_type_id
io_type_U1 Index io_element_id
io_element_type
project_id


location
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
location_id INTEGER(10) PK NN UNSIGNED     AI
species VARCHAR(20)   NN     NC, binomial format  
assembly VARCHAR(45)   NN     NC, or genome build  
chr VARCHAR(2)   NN     NC  
db_source_id INTEGER(10)   NN UNSIGNED   NC, database, used to get the data (ensembl 32, etc.)  
band VARCHAR(20)         cyto  
edit_date TIMESTAMP   NN   0000-00-00    
project_id INTEGER(10)   NN UNSIGNED      
IndexName IndexType Columns
PRIMARY PRIMARY location_id
location_U1 Index species
assembly
chr
db_source_id


marker
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
marker_id INTEGER(10) PK NN UNSIGNED     AI
db_source_id INTEGER(10)   NN UNSIGNED      
db_accn VARCHAR(50)   NN     NC, landmark (marker for example)  
description VARCHAR(255)            
edit_date TIMESTAMP   NN   0000-00-00    
project_id INTEGER(10)   NN UNSIGNED      
IndexName IndexType Columns
PRIMARY PRIMARY marker_id
gene_source_U1 Index db_accn


matrix
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
matrix_id INTEGER PK NN UNSIGNED     AI
name VARCHAR(45)   NN     NC  
db_source_id INTEGER(10)   NN UNSIGNED   NC  
db_accn VARCHAR(45)   NN     NC  
vectora VARCHAR(45)   NN BINARY   compressed frequency vector for A  
vectorc VARCHAR(45)   NN BINARY   same  
vectorg VARCHAR(45)   NN BINARY   same  
vectort VARCHAR(45)   NN BINARY   same  
description VARCHAR(255)            
edit_date TIMESTAMP            
project_id INTEGER     UNSIGNED   NC  
IndexName IndexType Columns
PRIMARY PRIMARY matrix_id
matrix_U1 Index name
project_id
db_source_id
db_accn


matrix_info
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
matrix_info_id INTEGER PK NN UNSIGNED     AI
matrix_id INTEGER   NN UNSIGNED   NC  
species VARCHAR(45)            
pubmed INTEGER     UNSIGNED      
exptype VARCHAR(45)            
edit_date TIMESTAMP            
IndexName IndexType Columns
PRIMARY PRIMARY matrix_info_id
matrix_info_U1 Index matrix_id


method
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
method_id INTEGER(10) PK NN UNSIGNED     AI
method VARCHAR(45)   NN     NC  
description TEXT            
edit_date TIMESTAMP   NN   0000-00-00    
IndexName IndexType Columns
PRIMARY PRIMARY method_id
method_U1 Index method


mutation
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
mutation_id INTEGER PK NN UNSIGNED     AI
mutation_set_id INTEGER(10)   NN UNSIGNED   NC  
position INTEGER   NN UNSIGNED   NC  
base CHAR   NN     NC  
IndexName IndexType Columns
PRIMARY PRIMARY mutation_id
mutation_U1 Index mutation_set_id
position
base


mutation_set
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
mutation_set_id INTEGER(10) PK NN UNSIGNED     AI
reg_seq_id INTEGER(10)   NN UNSIGNED      
mutant_name VARCHAR(20)   NN     NC  
coordinate_id INTEGER(10)   NN UNSIGNED      
method_id INTEGER(10)   NN UNSIGNED      
ref_id INTEGER(10)     UNSIGNED      
mutated_seq VARCHAR(255)   NN        
comments VARCHAR(255)            
edit_date TIMESTAMP   NN   0000-00-00    
project_id INTEGER(10)   NN UNSIGNED   NC  
IndexName IndexType Columns
PRIMARY PRIMARY mutation_set_id
mutation_set_U1 Index mutant_name
project_id


parameter
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
parameter_id INTEGER(10) PK NN UNSIGNED     AI
tag VARCHAR(20)   NN     NC  
table_name VARCHAR(45)   NN     NC  
table_primary_id INTEGER(10)   NN UNSIGNED   NC  
value VARCHAR(45)   NN        
edit_date TIMESTAMP   NN   0000-00-00    
project_id INTEGER(10)   NN UNSIGNED   NC  
IndexName IndexType Columns
PRIMARY PRIMARY parameter_id
parameter_U1 Index tag
table_name
table_primary_id
project_id


project
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
project_id INTEGER(10) PK NN UNSIGNED     AI
user_id INTEGER(10)   NN UNSIGNED   NC  
project_name VARCHAR(20)   NN     NC  
password VARCHAR(20)   NN        
status ENUM('restricted','published')   NN   restricted    
edit_date TIMESTAMP   NN   0000-00-00    
IndexName IndexType Columns
PRIMARY PRIMARY project_id
project_U1 Index user_id
project_name


ref
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
ref_id INTEGER(10) PK NN UNSIGNED     AI
pmid INTEGER(10)   NN UNSIGNED   NC  
edit_date TIMESTAMP   NN   0000-00-00    
project_id INTEGER(10)   NN UNSIGNED   NC  
IndexName IndexType Columns
PRIMARY PRIMARY ref_id
ref_U1 Index pmid
project_id


reg_seq
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
reg_seq_id INTEGER(10) PK NN UNSIGNED     AI
tfbs_name VARCHAR(20)         obsolete, may be deprecated at any moment  
coordinate_id INTEGER(10)   NN UNSIGNED   NC  
sequence TEXT   NN        
quality ENUM('na','conserved','tested','predicted')   NN        
edit_date TIMESTAMP   NN   0000-00-00    
project_id INTEGER(10)   NN UNSIGNED   NC  
IndexName IndexType Columns
PRIMARY PRIMARY reg_seq_id
reg_seq_U1 Index coordinate_id
project_id


reg_seq_construct
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
reg_seq_construct_id INTEGER(10) PK NN UNSIGNED     AI
construct_id INTEGER(10)   NN UNSIGNED   NK  
reg_seq_id INTEGER(10)   NN UNSIGNED   NK  
comments TEXT            
edit_date TIMESTAMP   NN   0000-00-00    
project_id INTEGER(10)   NN UNSIGNED      
IndexName IndexType Columns
PRIMARY PRIMARY reg_seq_construct_id


reg_seq_set
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
reg_seq_set_id INTEGER PK NN UNSIGNED     AI
reg_seq_id INTEGER(10)   NN UNSIGNED   NC  
matrix_id INTEGER   NN UNSIGNED   NC  
IndexName IndexType Columns
PRIMARY PRIMARY reg_seq_set_id
reg_seq_set_U1 Index reg_seq_id
matrix_id


sample
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
sample_id INTEGER(10) PK NN UNSIGNED     AI
sample_type VARCHAR(255)   NN     NC  
cell_id INTEGER(10)   NN UNSIGNED   NC  
time_id INTEGER(10)   NN UNSIGNED   NC  
edit_date TIMESTAMP   NN   0000-00-00    
project_id INTEGER(10)   NN UNSIGNED   NC  
IndexName IndexType Columns
PRIMARY PRIMARY sample_id
sample_U1 Index sample_type
cell_id
time_id
project_id


tf
NK: the same protein cannot belong to different family/class unless annotated in different projects (assume one is wrong)
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
tf_id INTEGER(10) PK NN UNSIGNED     AI
transcript_id INTEGER(10)   NN UNSIGNED   NC  
class VARCHAR(100)            
family VARCHAR(100)            
edit_date TIMESTAMP   NN   0000-00-00    
project_id INTEGER(10)   NN UNSIGNED   NC  
IndexName IndexType Columns
PRIMARY PRIMARY tf_id
tf_U1 Index transcript_id
project_id


tf_complex
No unique index to allow homodimers
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
tf_complex_id INTEGER PK NN UNSIGNED     AI
tf_id INTEGER(10)   NN UNSIGNED   NC  
funct_tf_id INTEGER(10)   NN UNSIGNED   NC  
modifications VARCHAR(45)            
IndexName IndexType Columns
PRIMARY PRIMARY tf_complex_id


time
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
time_id INTEGER(10) PK NN UNSIGNED     AI
name VARCHAR(45)            
description VARCHAR(255)            
scale VARCHAR(10)   NN        
edit_date TIMESTAMP   NN   0000-00-00    
project_id INTEGER(10)   NN UNSIGNED      
IndexName IndexType Columns
PRIMARY PRIMARY time_id


transcript
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
transcript_id INTEGER(10) PK NN UNSIGNED     AI
gene_source_id INTEGER(10)   NN UNSIGNED      
db_source_id INTEGER(10)   NN UNSIGNED   NC  
db_accn VARCHAR(50)   NN     NC, refseq or ensembl transcript  
isoform VARCHAR(20)         number, see entrez gene enumaration of transcripts  
comments TEXT            
edit_date TIMESTAMP   NN   0000-00-00    
project_id INTEGER(10)   NN UNSIGNED   NC  
IndexName IndexType Columns
PRIMARY PRIMARY transcript_id
transcript_U1 Index db_source_id
db_accn
project_id


tsr
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
tsr_id INTEGER PK NN UNSIGNED     AI
gene_source_id INTEGER(10)   NN UNSIGNED      
transcript_id INTEGER(10)     UNSIGNED      
fuzzy_start INTEGER   NN UNSIGNED   start of the area that is seen in >95% of the transcript population  
fuzzy_end INTEGER   NN UNSIGNED   end of the area that is seen in >95% of the transcript population  
predominant_start INTEGER     UNSIGNED   strict transcription start site or at least strongly dominating one  
edit_date TIMESTAMP   NN        
project_id INTEGER   NN UNSIGNED      
IndexName IndexType Columns
PRIMARY PRIMARY tsr_id


user_project
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
user_project_id INTEGER PK NN UNSIGNED     AI
user_id INTEGER(10) PK NN UNSIGNED      
project_id INTEGER(10) PK NN UNSIGNED      
IndexName IndexType Columns
PRIMARY PRIMARY user_project_id
user_id
project_id
user_project_u1 Unique Index user_id
project_id


users
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
user_id INTEGER(10) PK NN UNSIGNED     AI
first_name VARCHAR(20)   NN        
last_name VARCHAR(20)   NN        
edit_date TIMESTAMP   NN   0000-00-00    
password VARCHAR(20)   NN        
username VARCHAR(20)   NN     NC  
aff VARCHAR(45)            
IndexName IndexType Columns
PRIMARY PRIMARY user_id
users_IA Unique Index username