--
-- Type: TABLE; Owner: DEAPP; Name: DE_VARIANT_SUBJECT_DETAIL
--
 CREATE TABLE "DEAPP"."DE_VARIANT_SUBJECT_DETAIL" 
  (	"VARIANT_SUBJECT_DETAIL_ID" NUMBER NOT NULL ENABLE, 
"DATASET_ID" VARCHAR2(50 BYTE), 
"CHR" VARCHAR2(50 BYTE), 
"POS" NUMBER, 
"RS_ID" VARCHAR2(50 BYTE), 
"REF" VARCHAR2(500 BYTE), 
"ALT" VARCHAR2(500 BYTE), 
"QUAL" VARCHAR2(100 BYTE), 
"FILTER" VARCHAR2(50 BYTE), 
"INFO" CLOB, 
"FORMAT" VARCHAR2(500 BYTE), 
"VARIANT_VALUE" CLOB, 
 CONSTRAINT "VARIANT_SUBJECT_DETAIL_ID" PRIMARY KEY ("VARIANT_SUBJECT_DETAIL_ID")
 USING INDEX
 TABLESPACE "TRANSMART"  ENABLE
  ) SEGMENT CREATION IMMEDIATE
 TABLESPACE "TRANSMART" 
LOB ("INFO") STORE AS SECUREFILE (
 TABLESPACE "TRANSMART" ENABLE STORAGE IN ROW CHUNK 8192
 NOCACHE LOGGING  NOCOMPRESS  KEEP_DUPLICATES ) 
LOB ("VARIANT_VALUE") STORE AS BASICFILE (
 TABLESPACE "TRANSMART" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION 
 NOCACHE LOGGING ) ;
--
-- Type: REF_CONSTRAINT; Owner: DEAPP; Name: VARIANT_SUBJECT_DETAIL_FK
--
ALTER TABLE "DEAPP"."DE_VARIANT_SUBJECT_DETAIL" ADD CONSTRAINT "VARIANT_SUBJECT_DETAIL_FK" FOREIGN KEY ("DATASET_ID")
 REFERENCES "DEAPP"."DE_VARIANT_DATASET" ("DATASET_ID") ENABLE;

--
-- Type: SEQUENCE; Owner: DEAPP; Name: DE_VARIANT_SUBJECT_DETAIL_SEQ
--
CREATE SEQUENCE  "DEAPP"."DE_VARIANT_SUBJECT_DETAIL_SEQ"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE  NOORDER  NOCYCLE ;

--
-- Type: TRIGGER; Owner: DEAPP; Name: TRG_DE_VARIANT_SUBJ_DETAIL_ID
--
  CREATE OR REPLACE TRIGGER "DEAPP"."TRG_DE_VARIANT_SUBJ_DETAIL_ID" 
before insert on "DEAPP"."DE_VARIANT_SUBJECT_DETAIL"
for each row begin
       	if inserting then
               	if :NEW."VARIANT_SUBJECT_DETAIL_ID" is null then
                       	select DE_VARIANT_SUBJECT_DETAIL_SEQ.nextval into :NEW."VARIANT_SUBJECT_DETAIL_ID" from dual;
               	end if;
       	end if;
end;
/
ALTER TRIGGER "DEAPP"."TRG_DE_VARIANT_SUBJ_DETAIL_ID" ENABLE;
 
--
-- Type: INDEX; Owner: DEAPP; Name: DE_VARIANT_SUB_DT_IDX1
--
CREATE INDEX "DEAPP"."DE_VARIANT_SUB_DT_IDX1" ON "DEAPP"."DE_VARIANT_SUBJECT_DETAIL" ("DATASET_ID", "RS_ID")
TABLESPACE "TRANSMART" ;

--
-- Type: INDEX; Owner: DEAPP; Name: DE_VARIANT_SUB_DETAIL_IDX2
--
CREATE INDEX "DEAPP"."DE_VARIANT_SUB_DETAIL_IDX2" ON "DEAPP"."DE_VARIANT_SUBJECT_DETAIL" ("DATASET_ID", "CHR")
TABLESPACE "TRANSMART" ;

CREATE INDEX "DEAPP"."GEN_VARIANT_SUBJ_DET_CHR_POS" ON "DEAPP"."DE_VARIANT_SUBJECT_DETAIL" ("CHR", "POS");
