--#SET TERMINATOR @
--This value should be changed, use the same user that BPMS is using to access the DB2
SET CURRENT SCHEMA BPMS@

-- to execute this script open connect in the DB2 console: db2 connect to BPMS
-- Then perform this command: db2 -tf bpms-6.0-to-6.1.sql

-- update context mapping info table with owner id (deployment id) for per process instance strategies

alter table ContextMappingInfo add OWNER_ID varchar(255)@
update ContextMappingInfo set OWNER_ID = (select externalId from ProcessInstanceLog where processInstanceId = cast(CONTEXT_ID as bigint))@


create table AuditTaskImpl (
        id bigint generated by default as identity,
        activationTime date,
        actualOwner varchar(255),
        createdBy varchar(255),
        createdOn date,
        deploymentId varchar(255),
        description varchar(255),
        dueDate date,
        name varchar(255),
        parentId bigint not null,
        priority integer not null,
        processId varchar(255),
        processInstanceId bigint not null,
        processSessionId integer not null,
        status varchar(255),
        taskId bigint,
        primary key (id))@

CREATE OR REPLACE PROCEDURE UPDATE_IDENTITY()
MODIFIES SQL DATA
BEGIN
  DECLARE counter INTEGER;
  DECLARE queryDrop VARCHAR(255);
  DECLARE queryAlterSessionInfo VARCHAR(255);
  DECLARE queryGenerateIdentity VARCHAR(255);
  SET (counter) = (SELECT NVL(MAX(id), 0) AS id FROM SessionInfo);
  SET queryDrop = ('ALTER TABLE SessionInfo ALTER COLUMN id DROP IDENTITY');
  EXECUTE IMMEDIATE queryDrop;
  SET queryAlterSessionInfo = ('ALTER TABLE SessionInfo ALTER COLUMN id SET DATA TYPE BIGINT');
  EXECUTE IMMEDIATE queryAlterSessionInfo;
  SET queryGenerateIdentity = ('ALTER TABLE SessionInfo ALTER COLUMN id SET GENERATED ALWAYS AS IDENTITY (start with  '|| counter ||')');
  EXECUTE IMMEDIATE queryGenerateIdentity;
END
@

call UPDATE_IDENTITY()@
DROP PROCEDURE UPDATE_IDENTITY@
Call SYSPROC.ADMIN_CMD('reorg table SessionInfo')@
Call SYSPROC.ADMIN_CMD('runstats on table SessionInfo and indexes all')@


alter table AuditTaskImpl alter column processSessionId set data type bigint@
alter table AuditTaskImpl alter column activationTime set data type timestamp@
alter table AuditTaskImpl alter column createdOn set data type timestamp@
alter table ContextMappingInfo alter column KSESSION_ID set data type bigint@
alter table Task alter column processSessionId set data type bigint@

-- the following statement works in the SQL clients like Squirrel and db2' console, Note: When using db2 console it is necessary to use SCHEMA.TABLENAME
-- Call SYSPROC.ADMIN_CMD('reorg table SessionInfo')
-- Call SYSPROC.ADMIN_CMD('reorg table AuditTaskImpl')
-- Call SYSPROC.ADMIN_CMD('reorg table ContextMappingInfo')
-- Call SYSPROC.ADMIN_CMD('reorg table Task')
-- Call SYSPROC.ADMIN_CMD('reorg table TaskEvent')
-- it should be executed after to reorg the tables
Call SYSPROC.ADMIN_CMD('reorg table AuditTaskImpl')@
alter table AuditTaskImpl alter column dueDate set data type timestamp@

create table DeploymentStore (
    id bigint generated by default as identity,
    attributes varchar(255),
    DEPLOYMENT_ID varchar(255),
    deploymentUnit clob(65535),
    state integer,
    updateDate timestamp,
    primary key (id)
)@


create unique index UK_DeploymentStore_1 on DeploymentStore (DEPLOYMENT_ID)@

alter table ProcessInstanceLog add processInstanceDescription varchar(255)@
alter table RequestInfo add owner varchar(255)@
alter table Task add description varchar(255)@
alter table Task add name varchar(255)@
alter table Task add subject varchar(255)@


-- update all tasks with its name, subject and description
Call SYSPROC.ADMIN_CMD('reorg table Task')@
update Task t set name = (select shortText from I18NText where Task_Names_Id = t.id)@
update Task t set subject = (select shortText from I18NText where Task_Subjects_Id = t.id)@
update Task t set description = (select shortText from I18NText where Task_Descriptions_Id = t.id)@

-- It is necessary to reorg again to work
Call SYSPROC.ADMIN_CMD('reorg table AuditTaskImpl')@
INSERT INTO AuditTaskImpl (activationTime, actualOwner, createdBy, createdOn, deploymentId, description, dueDate, name, parentId, priority, processId, processInstanceId, processSessionId, status, taskId)
SELECT activationTime, actualOwner_id, createdBy_id, createdOn, deploymentId, description, expirationTime, name, parentId, priority,processId, processInstanceId, processSessionId, status, id FROM Task@


alter table TaskEvent add workItemId bigint@
alter table TaskEvent add processInstanceId bigint@
update TaskEvent t set workItemId = (select workItemId from Task where id = t.taskId)@
update TaskEvent t set processInstanceId = (select processInstanceId from Task where id = t.taskId)@
