drop database IF EXISTS PIRPSD;

create database PIRPSD;

USE PIRPSD;

create table c_psdmain
(PIRID    	VARCHAR(6) 	NOT NULL,
 CREATEDT  	DATE 		NOT NULL,
 SEQREVDT  	DATE 		NOT NULL,
 TXTREVDT  	DATE 		NOT NULL,
 TITLE     	TEXT 	        NOT NULL,
 TTLSTATUS 	VARCHAR(10),
 ALTNAME   	TEXT,
 CONTAINS  	TEXT,
 OGSOURCE  	TEXT            NOT NULL,
 OGCOMMON  	TEXT,
 OGFORMAL  	TEXT    	NOT NULL,
 OGVARIETY 	TEXT,
 OGNOTE    	TEXT,
 SEQLEN    	INT     	NOT NULL,
 SEQTYPE   	VARCHAR(10) 	NOT NULL,
 SEQSTATUS 	VARCHAR(50),
 COMMENTS  	TEXT,
 PRIMARY KEY (pirid));

create table c_psdref
(PIRID 		VARCHAR(6) 	NOT NULL,
 REFID 		VARCHAR(6) 	NOT NULL,
 RFORDER 	INT 	        NOT NULL,
 CONTENT 	TEXT,
 NOTE      	TEXT,
 PRIMARY KEY (pirid, refid));

create table c_litbas
(REFID 		VARCHAR(6) 	NOT NULL,
 CITID 		CHAR(7) 	NOT NULL,
 VOLUME  	VARCHAR(50),
 MONTH  	VARCHAR(10),
 YEAR   	INT,
 PAGES   	VARCHAR(50),
 TITLE   	TEXT,
 PRIMARY KEY (refid));

create table c_litauthor
(REFID 		VARCHAR(6) 	NOT NULL,
 AUORDER 	INT     	NOT NULL,
 LASTNAME 	VARCHAR(50) 	NOT NULL,
 INITNAME  	TEXT,
 PRIMARY KEY (refid, auorder));

create table c_litcitation
(CITID 		CHAR(7) 	NOT NULL,
 CITTYPE 	CHAR(1) 	NOT NULL,
 CITNAME 	TEXT    	NOT NULL,
 primary key (citid));

create table c_litxref
(REFID 		VARCHAR(6) 	NOT NULL,
 DBNAME 	VARCHAR(10) 	NOT NULL,
 XREFID 	VARCHAR(10) 	NOT NULL,
 PRIMARY KEY (refid, dbname, xrefid));

create table c_accession
(PIRID 		VARCHAR(6) 	NOT NULL,
 ACNUM 		VARCHAR(6) 	NOT NULL,
 ACORDER 	INT     	NOT NULL,
 ACFLAG 	VARCHAR(2) 	NOT NULL,
 REFID  	VARCHAR(6),
 ACLABEL  	VARCHAR(10),
 GNLINK  	VARCHAR(10),
 ACSTATUS  	TEXT,
 MOLTYPE   	TEXT,
 ACSEQSPEC  	TEXT,
 EXPSOURCE  	TEXT,
 ACNOTE  	TEXT,
 PRIMARY KEY (pirid, acnum));

create table c_accxref
(ACNUM 		VARCHAR(6) 	NOT NULL,
 DBNAME 	VARCHAR(10) 	NOT NULL,
 XREFID 	VARCHAR(100) 	NOT NULL,
 KEY (acnum, dbname, xrefid));

create table c_psdclass
(PIRID 		VARCHAR(6) 	NOT NULL,
 CLASSNAME 	VARCHAR(100) 	NOT NULL,
 CLASSTYPE 	CHAR(1) 	NOT NULL,
 PRIMARY KEY (pirid, classname, classtype));

create table c_psdkeyword
(PIRID 		VARCHAR(6) 	NOT NULL,
 KEYWORD 	VARCHAR(50) 	NOT NULL,
 KWORDER 	INT     	NOT NULL,
 PRIMARY KEY (pirid, keyword));

create table c_psdcomplex
(PIRID 		VARCHAR(6) 	NOT NULL,
 CPORDER 	INT     	NOT NULL,
 CPLABEL 	VARCHAR(10),
 CPLINK  	VARCHAR(10),
 CPDESCRPT 	TEXT    	NOT NULL,
 CPSTATUS  	VARCHAR(50),
 PIRXREF  	VARCHAR(200),
 MUID  		VARCHAR(50),
 PRIMARY KEY (pirid, cporder));

create table c_psdfunction
(PIRID 		VARCHAR(6) 	NOT NULL,
 FNORDER 	INT     	NOT NULL,
 FNLABEL 	VARCHAR(10),
 FNDESCRPT 	TEXT,
 FNSTATUS 	VARCHAR(50),
 MUID 		VARCHAR(50),
 PATHWAY 	TEXT,
 FNNOTE 	TEXT,
 PRIMARY KEY (pirid, fnorder));

create table c_psdfeature
(PIRID 		VARCHAR(6) 	NOT NULL,
 FTORDER 	INT     	NOT NULL,
 FTLABEL 	VARCHAR(10),
 FTLINK 	VARCHAR(10),
 FTTYPE 	VARCHAR(50) 	NOT NULL,
 FTDESCRPT 	VARCHAR(200),
 FTSEQSPEC  	TEXT,
 FTSTATUS  	VARCHAR(50),
 FTNOTE  	TEXT,
 PRIMARY KEY (pirid, ftorder));

create table c_psdsequence
(PIRID 		VARCHAR(6) 	NOT NULL,
 SEQUENCES 	LONGBLOB	NOT NULL,
 primary key (pirid));

create table c_psdgenetics
(PIRID 		VARCHAR(6) 	NOT NULL,
 GNORDER 	INT     	NOT NULL,
 GNLABEL  	VARCHAR(10),
 GENEID  	TEXT,
 MAPPSTN  	VARCHAR(100),
 GENOME  	VARCHAR(200),
 MOBILELM 	VARCHAR(100),
 GNORIGIN 	VARCHAR(100),
 GNCODE  	VARCHAR(10),
 STARTCDN  	VARCHAR(4),
 INTRONS  	TEXT,
 INTSTATUS  	VARCHAR(50),
 OTRPROD  	VARCHAR(200),
 GNNOTE  	TEXT,
 PRIMARY KEY (pirid, gnorder));

create table c_psdgnxref
(PIRID 		VARCHAR(6) 	NOT NULL,
 GNORDER 	INT     	NOT NULL,
 DBNAME 	VARCHAR(10) 	NOT NULL,
 XREFID 	VARCHAR(50) 	NOT NULL,
 PRIMARY KEY (pirid, gnorder, dbname, xrefid));








create index 
clau_idx1_refid
on c_litauthor (refid);

create index 
clxr_idx1_refid
on c_litxref (refid);

create index 
cacidx1_pirid
on c_accession (pirid);

create index 
caxr_idx1_acnum
on c_accxref (acnum);

create index 
cplidx1_pirid
on c_psdclass (pirid);

create index 
cpkw_idx1_pirid
on c_psdkeyword (pirid);

create index 
cprf_idx1_pirid
on c_psdref (pirid);

create index 
cpcp_idx1_pirid
on c_psdcomplex (pirid);

create index 
cpfn_idx1_pirid
on c_psdfunction (pirid);

create index 
cpft_idx1_pirid
on c_psdfeature (pirid);

create index 
cpgn_idx1_pirid
on c_psdgenetics (pirid);

create index 
cpgx_idx1_pirid
on c_psdgnxref (pirid);

