REM Copyright (c) 2002 Konrad Buessow

REM This program is free software; you can redistribute and/or modify 
REM it under the terms of the GNU General Public Licence as published by
REM the Free Software Foundation; eiher version 2, or (at your option) any
REM later version.

REM This program is distributed in the hope that it will be useful, but 
REM WITHOUT ANY WARRANTY; without even the implied warranty of 
REM MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
REM GNU General Public Licence for more details. 

REM You should have received a copy of the GNU General Public Licence 
REM along with this program; if not, write to the Free Software 
REM Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA

REM Storage of species names
REM Field GENUS, e.g. 'Homo'
REM Field SPECIES, e.g. 'sapiens'
REM Field GENUS_SPECIES, e.g. 'Homo sapiens'.
REM The GENUS_SPECIES field takes the taxname, if Genus/Species 
REM is not available, e.g. for viruses
REM The ORFER Package delivers new unique ID from the sequence ORFER_SEQ.
REM The trigger SPECIES_INSTRIG provides new SPECIES records with a new SPECIESNO.



CREATE TABLE SPECIES(SPECIESNO NUMBER(11) NOT NULL, 
    GENUS VARCHAR2(50), 
    SPECIES VARCHAR2(50), 
    GENUS_SPECIES VARCHAR2(50) NOT NULL, 
    COMMENT_ VARCHAR2(500) , 
    CONSTRAINT SPECIES_PK PRIMARY KEY(SPECIESNO)
);

CREATE TABLE SEQ_DNA(
    SEQDNA_ID NUMBER(11) NOT NULL, 
    SEQ CLOB NOT NULL, 
    CONSTRAINT SEQDNA_PK PRIMARY KEY(SEQDNA_ID)
);

CREATE TABLE SEQ_PEP(
    SEQPEP_ID NUMBER(11) NOT NULL, 
    SEQ CLOB NOT NULL, 
    CONSTRAINT SEQPEP_PK PRIMARY KEY(SEQPEP_ID)
); 

CREATE TABLE GENBANKENTRIES(
    GI NUMBER(11), 
    GENBANKACC VARCHAR2(50) NOT NULL, 
    GENENAME VARCHAR2(255), 
    LENGTH_ NUMBER(11) NOT NULL, 
    COMPLETECDS NUMBER(1), 
    SPECIESNO NUMBER(11), 
    TYPE_ VARCHAR2(50) NOT NULL, 
    GENEDESCRIPTION VARCHAR2(255) NOT NULL, 
    SEQ_DB VARCHAR2(50) NOT NULL, 
    SEQDNA_ID NUMBER(11), 
    SEQPEP_ID NUMBER(11), 
    DATE_IN DATE DEFAULT sysdate NOT NULL, 
    DATE_UPDATE DATE DEFAULT sysdate NOT NULL, 
    ORFER_PROBLEM NUMBER(3),
    CONSTRAINT GB_SEQDNA_REF_1 FOREIGN KEY(SEQDNA_ID) 
    REFERENCES SEQ_DNA(SEQDNA_ID), 
    CONSTRAINT GB_SEQPEP_ID_1 FOREIGN KEY(SEQPEP_ID) 
    REFERENCES SEQ_PEP(SEQPEP_ID), 
    CONSTRAINT GB_SPECIES_REF_1 FOREIGN KEY(SPECIESNO) 
    REFERENCES SPECIES(SPECIESNO), 
    CONSTRAINT GENBANKENTR_PK PRIMARY KEY(GI) 
);


CREATE TABLE RESTRICTIONENZYMES (
	ENZYMEID NUMBER(11),
	ENZYMENAME VARCHAR2(50),
	SITESEQUENCE VARCHAR2(50),
	CUTUPPERSTRAND NUMBER(11),
	LENGTH5PROVERHANG NUMBER(11),
	FREQUENTLYUSED NUMBER (11),
	CONSTRAINT ENZYMEID_PK PRIMARY KEY(ENZYMEID)
);
	
	
CREATE TABLE PCRPRIMER (
	ID NUMBER(11),
	PRIMERNAME VARCHAR2(50),
	PRPRIMERNO NUMBER(11),
	PRIMERSEQ VARCHAR2(250) UNIQUE,
	UPPERLOWERSTRAND VARCHAR2(50),
	ENZYMEID NUMBER(11),
	CONSTRAINT ENZYMEID_FK FOREIGN KEY(ENZYMEID) REFERENCES NILS_RESTRICTIONENZYMES(ENZYMEID),
	CONSTRAINT PCRPRIMER_PK PRIMARY KEY(ID)
);

CREATE TABLE PCRPROT (
	PCRPRID NUMBER(11),
	PR_STARTBP NUMBER(11),
	PR_STOPBP NUMBER(11),
	G_STARTBP NUMBER(11),
	G_STOPBP NUMBER(11),
	SEQDNA_ID NUMBER(11),
	CONSTRAINT PCRPRID_FK FOREIGN KEY(PCRPRID) REFERENCES NILS_PCRPRIMER(ID),
	CONSTRAINT PCRPRID_SEQDNA_ID_PK PRIMARY KEY(PCRPRID)
);

CREATE SEQUENCE ORFER_SEQ INCREMENT BY 1 START WITH 1
    MAXVALUE 1.0E27 MINVALUE 1 NOCYCLE
    CACHE 20 NOORDER
;

CREATE PACKAGE ORFER IS
  FUNCTION GET_NEW_ID RETURN NUMBER;
END ORFER
;
/
CREATE PACKAGE BODY ORFER IS
  FUNCTION GET_NEW_ID RETURN NUMBER IS
    NEW_ID NUMBER;
  BEGIN
    SELECT ORFER_SEQ.NEXTVAL INTO NEW_ID FROM DUAL;
    RETURN NEW_ID;
  END;
END ORFER
;
/
CREATE TRIGGER SPECIES_INSTRIG BEFORE INSERT 
    ON SPECIES 
    FOR EACH ROW 
BEGIN
  SELECT ORFER_SEQ.NEXTVAL INTO :NEW.SPECIESNO FROM DUAL;
END;
/
COMMIT;
/