ALTER TABLE "JBPM_VARIABLEINSTANCE" drop CONSTRAINT "FK_VAR_TSKINST";
ALTER TABLE "JBPM_VARIABLEINSTANCE" drop CONSTRAINT "FK_VARINST_TKVARMP";
ALTER TABLE "JBPM_VARIABLEINSTANCE" drop CONSTRAINT "FK_BYTEINST_ARRAY";

drop index IDX_JBPM_VARIABLEINSTANCE_01;
drop index IDX_JBPM_VARIABLEINSTANCE_02;
drop index IDX_JBPM_VARIABLEINSTANCE_03;
drop index IDX_JBPM_VARIABLEINSTANCE_04;
alter table JBPM_LOG drop constraint JBPM_LOG#JBPM_VARIABLEINSTANCE;
ALTER TABLE "JBPM_VARIABLEINSTANCE" drop constraint PK_JBPM_VARIABLEINSTANCE;
drop index PK_JBPM_VARIABLEINSTANCE;

drop trigger TRG_JBPM_VARIABLEINSTANCE_SEQ;
drop trigger TRG_JBPM_VARIABLEINSTANCE_UPD;

alter table JBPM_VARIABLEINSTANCE rename to JBPM_VARIABLEINSTANCE_BACKUP;

CREATE TABLE "JBPM_VARIABLEINSTANCE" 
 (	"ID_" , 
"CLASS_" , 
"NAME_" , 
"CONVERTER_" , 
"TOKEN_" , 
"TOKENVARIABLEMAP_" , 
"PROCESSINSTANCE_" , 
"BYTEARRAYVALUE_" , 
"DATEVALUE_" , 
"DOUBLEVALUE_" , 
"LONGIDCLASS_" , 
"LONGVALUE_" , 
"STRINGIDCLASS_" , 
"STRINGVALUE_" , 
"TASKINSTANCE_" , 
"CREATEDATE" , 
"UPDATEDATE" 
 ) nologging parallel 2
 AS SELECT "ID_" , 
"CLASS_" , 
"NAME_" , 
"CONVERTER_" , 
"TOKEN_" , 
"TOKENVARIABLEMAP_" , 
"PROCESSINSTANCE_" , 
"BYTEARRAYVALUE_" , 
"DATEVALUE_" , 
"DOUBLEVALUE_" , 
"LONGIDCLASS_" , 
"LONGVALUE_" , 
"STRINGIDCLASS_" , 
case when "STRINGVALUE_" = ' ' then null else stringvalue_ end , 
"TASKINSTANCE_" , 
"CREATEDATE" , 
"UPDATEDATE" from JBPM_VARIABLEINSTANCE_BACKUP;
 
alter table jbpm_variableinstance logging parallel 1;

alter table jbpm_variableinstance modify createdate default systimestamp;
alter table jbpm_variableinstance modify updatedate default systimestamp;

ALTER TABLE "JBPM_VARIABLEINSTANCE" ADD CONSTRAINT "PK_JBPM_VARIABLEINSTANCE" PRIMARY KEY ("ID_") ENABLE;
ALTER TABLE "JBPM_LOG" ADD CONSTRAINT JBPM_LOG#JBPM_VARIABLEINSTANCE FOREIGN KEY ("VARIABLEINSTANCE_") REFERENCES "JBPM_VARIABLEINSTANCE" ("ID_") ENABLE;
ALTER TABLE "JBPM_VARIABLEINSTANCE" ADD CONSTRAINT "FK_BYTEINST_ARRAY" FOREIGN KEY ("BYTEARRAYVALUE_")  REFERENCES "JBPM_BYTEARRAY" ("ID_") ENABLE;
ALTER TABLE "JBPM_VARIABLEINSTANCE" ADD CONSTRAINT "FK_VARINST_TKVARMP" FOREIGN KEY ("TOKENVARIABLEMAP_")  REFERENCES "JBPM_TOKENVARIABLEMAP" ("ID_") ENABLE;
ALTER TABLE "JBPM_VARIABLEINSTANCE" ADD CONSTRAINT "FK_VAR_TSKINST" FOREIGN KEY ("TASKINSTANCE_")  REFERENCES "JBPM_TASKINSTANCE" ("ID_") ENABLE;
 
CREATE INDEX "IDX_JBPM_VARIABLEINSTANCE_01" ON "JBPM_VARIABLEINSTANCE" ("NAME_", "STRINGVALUE_") nologging;
alter index  IDX_JBPM_VARIABLEINSTANCE_01 logging;

CREATE INDEX "IDX_JBPM_VARIABLEINSTANCE_02" ON "JBPM_VARIABLEINSTANCE" ("PROCESSINSTANCE_", "NAME_", "STRINGVALUE_", "TOKENVARIABLEMAP_", "CLASS_")  nologging;
alter index  IDX_JBPM_VARIABLEINSTANCE_02 logging; 

CREATE INDEX "IDX_JBPM_VARIABLEINSTANCE_03" ON "JBPM_VARIABLEINSTANCE" ("TASKINSTANCE_") nologging;
alter index  IDX_JBPM_VARIABLEINSTANCE_03 logging;

CREATE INDEX "IDX_JBPM_VARIABLEINSTANCE_04" ON "JBPM_VARIABLEINSTANCE" ("TOKENVARIABLEMAP_") nologging;
alter index  IDX_JBPM_VARIABLEINSTANCE_03 logging;

CREATE OR REPLACE TRIGGER "TRG_JBPM_VARIABLEINSTANCE_SEQ" 
  BEFORE INSERT ON WFS.JBPM_VARIABLEINSTANCE
  FOR EACH ROW
  BEGIN
    IF :NEW.ID_ IS NULL THEN
      SELECT WFS.JBPM_VARIABLEINSTANCE_SEQ.NEXTVAL INTO :NEW.ID_ FROM DUAL;
    END IF;
END;
/

ALTER TRIGGER "TRG_JBPM_VARIABLEINSTANCE_SEQ" ENABLE;
 
CREATE OR REPLACE TRIGGER "TRG_JBPM_VARIABLEINSTANCE_UPD" BEFORE
   UPDATE ON WFS.JBPM_VARIABLEINSTANCE FOR EACH ROW BEGIN
   SELECT SYSTIMESTAMP INTO :NEW.UpdateDate FROM DUAL;
END;
/

ALTER TRIGGER "TRG_JBPM_VARIABLEINSTANCE_UPD" ENABLE;
 
