CREATE DATABASE orfer;

USE orfer;


CREATE TABLE species(
    speciesno INT(11) PRIMARY KEY AUTO_INCREMENT,
    genus VARCHAR(50), 
    species VARCHAR(50), 
    genus_species VARCHAR(50) NOT NULL, 
    comment_ VARCHAR(255)
);

CREATE TABLE seq_dna(
    SEQDNA_ID INT(11) PRIMARY KEY, 
    SEQ BLOB NOT NULL
);

CREATE TABLE seq_pep(
    SEQPEP_ID INT(11) PRIMARY KEY, 
    SEQ BLOB NOT NULL
); 

CREATE TABLE genbankentries(
    gi INT(11) PRIMARY KEY,
    genbankacc VARCHAR(50) NOT NULL,
    INDEX (genbankacc ),
    genename VARCHAR(255),
    length_ INT(11) NOT NULL,
    completecds INT(1),
    speciesno INT,
    type_ VARCHAR(50) NOT NULL,
    genedescription VARCHAR(255) NOT NULL,
    seq_db VARCHAR(50) NOT NULL,
    seqdna_id INT,
    seqpep_id INT,
    date_in TIMESTAMP default CURRENT_TIMESTAMP,
    date_update TIMESTAMP ,
    orfer_problem INT(3)
);

CREATE TABLE restrictionenzymes (
	ENZYMEID INT(11) PRIMARY KEY AUTO_INCREMENT,
	ENZYMENAME VARCHAR(50) NOT NULL,
	SITESEQUENCE VARCHAR(50) NOT NULL,
	CUTUPPERSTRAND INT(11) NOT NULL,
	LENGTH5PROVERHANG INT(11) NOT NULL,
	FREQUENTLYUSED INT(11)
);
	
	
CREATE TABLE pcrprimer (
	ID INT(11) PRIMARY KEY,
	PRIMERNAME VARCHAR(50) NOT NULL,
	PRPRIMERNO INT(11) NOT NULL,
	PRIMERSEQ VARCHAR(250) UNIQUE,
	UPPERLOWERSTRAND VARCHAR(50) NOT NULL,
	ENZYMEID INT(11) NOT NULL
);

CREATE TABLE pcrprot (
	PCRPRID INT(11) PRIMARY KEY AUTO_INCREMENT,
	PR_STARTBP INT(11),
	PR_STOPBP INT(11),
	G_STARTBP INT(11),
	G_STOPBP INT(11),
	SEQDNA_ID INT(11) NOT NULL
);

CREATE TABLE sequence (id INT(11) NOT NULL);
INSERT INTO sequence VALUES (0);

CREATE TABLE primer_sequence (id INT(11) NOT NULL);
INSERT INTO primer_sequence VALUES (0);

# Statement to increase id by one in the only row of sequence table:
# UPDATE sequence SET id=last_insert_id(id+1);