--- GRAMENE ONTOLOGY
--- based on GENE ONTOLOGY


CREATE TABLE dbxref
(
	dbxref_id	number(11) 		CONSTRAINT pk_dbxref PRIMARY KEY,
	xref_key  	varchar2(255) not null,
	xref_keytype  	varchar2(32),
	xref_dbname 	varchar2(32) not null,
	xref_desc	varchar2(255),
	UNIQUE(xref_key, xref_keytype, xref_dbname)
);
--- 	xref_extra	varchar2(255),

CREATE TABLE term_dbxref
(
	term_id	    	number(11) not null	REFERENCES  term,
	dbxref_id   	number(11) not null	REFERENCES  dbxref,
	refers_to	varchar2(16),
	UNIQUE(term_id, dbxref_id)
);
--- a dbxref can (optionally) refer to a synonym of a term
--- rather than the whole term
--- anatomy, term, definition, synonym

CREATE TABLE species 
(
	species_id 	 	number(11) not null CONSTRAINT pk_species PRIMARY KEY,
	ncbi_taxa_id 		number(11),
	common_name 		varchar2(64),
	lineage_string 		varchar2(2048),
	genus 			varchar2(32),
	species 		varchar2(32)
);

CREATE TABLE gene_product 
(
	gene_product_id		number(11) CONSTRAINT pk_gene_product PRIMARY KEY,
	gene_product_symbol	varchar2(128) not null,
	organism_dbxref_id	number(11) not null	REFERENCES  dbxref(dbxref_id),
	species_id		number(11),
	gene_product_full_name	varchar2(1024),
	UNIQUE(gene_product_symbol, organism_dbxref_id)
);
--- NOTICE: Ideally it should have
--- 	species_id		number(11) not null	REFERENCES species(id),

CREATE TABLE gene_product_synonym 
(
	gene_product_id		number(11) not null 	REFERENCES gene_product,
	gene_product_synonym_symbol	varchar2(128) not null,
	UNIQUE(gene_product_id, gene_product_synonym_symbol)
);



CREATE TABLE association 
(
	association_id  	number(11) 		CONSTRAINT pk_association PRIMARY KEY,
	term_id	        	number(11) not null	REFERENCES term,
	gene_product_id		number(11) not null,	
	object_class 		varchar2(32) not null,
	is_not 			number(1)		CHECK (is_not = 0 OR is_not = 1),
	role_group		number(11),
	UNIQUE (term_id, gene_product_id)
);
COMMENT on column association.gene_product_id is
'The internal id of whatever object is associated, not necessarily a gene_product';
COMMENT on column association.object_class is
'the class of the "gene_product_id": "gene_product", "mutant", "riceseq.translation", ... ';
--- boolean, indicating if this is negated
--- boolean, indicating if this is a primary association
--- NOTICE: Ideally it should have 
--- gene_product_id             number(11) not null     REFERENCES gene_product 
--- The data does not keep is_primary any more.         
--- is_primary          number(1)               CHECK (is_primary = 0 OR is_primary = 1)
--- role group is added in 02/2002  (GO copies Gramene without giving credits)


CREATE TABLE evidence 
(
	evidence_id  		number(11) 		CONSTRAINT pk_evidence PRIMARY KEY,
	evidence_code		varchar2(8) not null,
	association_id		number(11) not null,
	object_class 		varchar2(32) not null,
	dbxref_id		number(11) not null	REFERENCES dbxref,
	seq_acc			varchar2(48)
);
COMMENT on column evidence.association_id is
'The internal id of whatever object the evidence is for, not necessarily an association';
COMMENT on column evidence.object_class is
'the class of the "association_id": "association", "candidate_gene", ... ';
--- NOTICE: Ideally it should have
--- association_id              number(11) not null     REFERENCES association
--- UNIQUE (association_id, dbxref_id, evidence_code),
--- old: seq_acc                varchar2(16)


CREATE TABLE sequence_to_feature 
(
	sequence_to_feature_id	    NUMBER NOT NULL	 	 CONSTRAINT pk_seq_to_feature PRIMARY KEY,	
	gene_product_id	            NUMBER                       NOT NULL,
	feature_id	            NUMBER                       NOT NULL,
	from_endpoint               NUMBER,
        to_endpoint                 NUMBER,
        UNIQUE(gene_product_id, feature_id),
	FOREIGN KEY ( gene_product_id ) REFERENCES gene_product,
	FOREIGN KEY ( feature_id ) REFERENCES sequence_feature
);


CREATE TABLE objectxref (
    objectxref_id                       NUMBER(11)                  NOT NULL,
    table_name                          VARCHAR2(60)                NOT NULL,
    row_id                              NUMBER(11)                  NOT NULL,
    dbxref_id                           NUMBER(11)                  NOT NULL,
    PRIMARY KEY (objectxref_id),
    FOREIGN KEY (dbxref_id) REFERENCES dbxref
);




CREATE TABLE subspecies (
    subspecies_id	           NUMBER(11)                       NOT NULL,
    species_id	                   NUMBER(11)                       NOT NULL,
    subspecies_name                VARCHAR2(100)                    NOT NULL,
    PRIMARY KEY (subspecies_id),
    FOREIGN KEY (species_id) REFERENCES species
);
COMMENT on table subspecies is
 'Includes a row for "Unknown" -for cultivar';



CREATE TABLE cultivar (
    cultivar_id	                   NUMBER(11)                        NOT NULL,
    subspecies_id	           NUMBER(11)                        NOT NULL,
    species_id	                   NUMBER(11)                        NOT NULL,
    cultivar_name                  VARCHAR2(100)                     NOT NULL,
    PRIMARY KEY (cultivar_id),
    FOREIGN KEY (species_id) REFERENCES species,
    FOREIGN KEY (subspecies_id) REFERENCES subspecies
);
COMMENT on column cultivar.subspecies_id is
 ' may point to the "Unknown" row';

CREATE TABLE gene_product_to_cultivar (
    gene_product_id	           NUMBER(11)                         NOT NULL,
    cultivar_id	                   NUMBER(11)                         NOT NULL,
    PRIMARY KEY (gene_product_id, cultivar_id),
    FOREIGN KEY (gene_product_id) REFERENCES gene_product, 
    FOREIGN KEY (cultivar_id) REFERENCES cultivar
);

CREATE TABLE gene_product_to_subspecies ( 
    gene_product_id	           NUMBER(11)                         NOT NULL,
    subspecies_id	           NUMBER(11)                         NOT NULL,
    PRIMARY KEY (gene_product_id, subspecies_id),
    FOREIGN KEY (gene_product_id) REFERENCES gene_product, 
    FOREIGN KEY (subspecies_id) REFERENCES subspecies
);
--- COMMENT on table GENE_PRODUCT_TO_SUBSPECIES IS
---    'Necessary because don't always know cultivar';



CREATE TABLE gene_product_to_species (
    gene_product_id	           NUMBER(11)                         NOT NULL,
    species_id	                   NUMBER(11)                         NOT NULL,
    PRIMARY KEY (gene_product_id, species_id),
    FOREIGN KEY (gene_product_id) REFERENCES gene_product, 
    FOREIGN KEY (species_id) REFERENCES species
);
---COMMENT on table GENE_PRODUCT_TO_SPECIES IS
---    'Necessary because don't always know subspecies or cultivar';


CREATE TABLE expression (
    expression_id                   NUMBER(11)                        NOT NULL,
    gene_product_id                 NUMBER(11)                        NOT NULL,
    expression_annotation           VARCHAR2(4000),
    PRIMARY KEY (expression_id),
    FOREIGN KEY (gene_product_id) REFERENCES gene_product 
);


--- CREATE TABLE PROTEIN_CLEAVING (
---   protein_cleaving_id  NUMBER(11) NOT NULL,
---   from_gene_product_id      NUMBER(11) NOT NULL,
---   N_end_gene_product_id  NUMBER(11),
---   C_end_gene_product_id  NUMBER(11),
---   by_gene_product_id      NUMBER(11) NOT NULL,
---   cleaving_association_id NUMBER(11),
---   peptide_position  NUMBER(7),
---  PRIMARY KEY (protein_cleaving_id),
---   FOREIGN KEY (from_gene_product_id) REFERENCES GENE_PRODUCT (gene_product_id),
---   FOREIGN KEY (N_end_gene_product_id) REFERENCES GENE_PRODUCT (gene_product_id),
---   FOREIGN KEY (C_end_gene_product_id) REFERENCES GENE_PRODUCT (gene_product_id)
--- );

--- CREATE TABLE PROTEIN_COMPLEX (
---   complex_gene_product_id      NUMBER(11) NOT NULL,
---   part_gene_product_id      NUMBER(11) NOT NULL,
---   PRIMARY KEY (complex_gene_product_id,part_gene_product_id),
---   FOREIGN KEY (part_gene_product_id) REFERENCES GENE_PRODUCT (gene_product_id),
---   FOREIGN KEY (complex_gene_product_id) REFERENCES GENE_PRODUCT (gene_product_id) 
--- );


CREATE TABLE gene_product_to_keyword 
(
   gene_product_id  NUMBER(11) REFERENCES GENE_PRODUCT (gene_product_id),
   keyword_id       NUMBER(11) REFERENCES KEYWORD (keyword_id)
);


CREATE TABLE gene_product_gene_name
(
	gene_product_id	       number(11)          not null,
	gene_name  	       varchar2(255)       not null,
	UNIQUE (gene_product_id, gene_name), 
	FOREIGN KEY (gene_product_id) REFERENCES gene_product
);


CREATE TABLE gene_product_type
(
	gene_product_id	       number(11)          not null,
	gene_product_type      varchar2(255)       not null,
	PRIMARY KEY (gene_product_id),
	FOREIGN KEY (gene_product_id) REFERENCES gene_product
);
---For data from Gene Ontology, gene_product_type is "GO".
---For data from Swiss-Prot, gene_product_type is "SPTrembl". (Including data from Trembl-New).



CREATE TABLE gene_product_trembl_new 
(
	gene_product_id		number(11)         not null,
	swissprot_acc	        varchar2(128)      not null,
	FOREIGN KEY (gene_product_id) REFERENCES gene_product
);
---Proteins in this table are from Trembl-New.
---Should use gene_product_type table?


CREATE TABLE gene_product_to_gi_number 
(
	gene_product_id		number(11)         not null,
	gi_number	        varchar2(30)       not null,
	FOREIGN KEY (gene_product_id) REFERENCES gene_product
);
---Should use dbxref table?


CREATE TABLE gene_product_tissue
(
	gene_product_id	       number(11)          not null,
	tissue  	       varchar2(255)       not null,
	UNIQUE (gene_product_id, tissue), 
	FOREIGN KEY (gene_product_id) REFERENCES gene_product
);


CREATE TABLE gene_product_extra 
(
	gene_product_id		number(11)         not null,
	gene_product_name	varchar2(1024)     not null,
	swissprot_acc	        varchar2(128)      not null,
	swissprot_id	        varchar2(128)      not null,
	FOREIGN KEY (gene_product_id) REFERENCES gene_product
);
---For protein search only. This is actually a redundant table, but it improves the performance. 


