JIRA: Check workflow current step entries

An issue can have an invalid status (one that does not exist for the workflow) if a Workflow Migration or Bulk Move died half way through.

An issue must have exactly 1 (one) currentstep. It tells, how did we got here.
If it has zero, we should insert one for the current wfentry based on the workflow.
If it has more than one, we should only keep the latest. (Not covered by this blog entry.)

How to check:

SELECT COUNT(*) AS PROBLEM_CNT
FROM JIRAISSUE I    
LEFT JOIN OS_CURRENTSTEP C ON I.WORKFLOW_ID = C.ENTRY_ID
INNER JOIN TEMP_JIRAWORKFLOWS WF ON I.ISSUESTATUS = WF.LINKEDSTATUSID 
INNER JOIN OS_WFENTRY W ON I.WORKFLOW_ID = W.ID AND WF.WORKFLOWNAME = W.NAME
WHERE C.STEP_ID != WF.STEPID OR C.ENTRY_ID IS NULL;

Essentially, this check is an antijoin, looking for issues without a corresponding currentstep (however the condition also gives us the multiple ones too) OR with an incorrect step.

How to fix:

UPDATE: Now, as converting the XML-s to a table is pretty expensive, we'll create a temporary table, and fill it in with the workflow-step-status tuples.

-- Create me once.
CREATE GLOBAL TEMPORARY TABLE TEMP_JIRAWORKFLOWS
(WORKFLOWNAME VARCHAR2(255 CHAR),
STEPID NUMBER,
LINKEDSTATUSID VARCHAR2(16))
ON COMMIT PRESERVE ROWS;

Every time we connect, we'll have to fill it out.
Note: you can create a real table with triggers to reflect the changes on the original, or have a materialized view. I choose this cumbersome way, because our DBA-s are afraid of triggers (wtf?!), and also they does not let me create materialized views.

INSERT INTO TEMP_JIRAWORKFLOWS
WITH W_JIRAWORKFLOWS AS
  ( SELECT
        WORKFLOWNAME,
        XMLTYPE.CREATEXML(REPLACE(DESCRIPTOR,
        '<!DOCTYPE workflow PUBLIC "-//OpenSymphony Group//DTD OSWorkflow 2.8//EN" "http://www.opensymphony.com/osworkflow/workflow_2_8.dtd">'
        )) AS DESCRIPTOR
     FROM JIRAWORKFLOWS)
SELECT
  W_JIRAWORKFLOWS.WORKFLOWNAME,
  WF.STEPID                   ,
  WF.LINKEDSTATUSID
FROM
  W_JIRAWORKFLOWS,
  XMLTABLE('/workflow/steps/step[@id]' PASSING W_JIRAWORKFLOWS.DESCRIPTOR
     COLUMNS
        "STEPID" NUMBER PATH '@id',
        "LINKEDSTATUSID" VARCHAR2(16) PATH 'meta[@name="jira.status.id"]/text()'
     ) WF;

COMMIT;
 
SET TRANSACTION ISOLATION LEVEL READ COMMITTED NAME 'Check workflow current step entries';
 
MERGE INTO OS_CURRENTSTEP USING
( SELECT
       ROWNUM+(SELECT SEQ_ID FROM SEQUENCE_VALUE_ITEM WHERE SEQ_NAME = 'OSCurrentStep') AS N,
       I.WORKFLOW_ID     AS ENTRY_ID,
       WF.STEPID         AS STEP_ID ,
       WF.LINKEDSTATUSID AS STATUS
  FROM JIRAISSUE I
  LEFT JOIN OS_CURRENTSTEP C ON I.WORKFLOW_ID = C.ENTRY_ID
  INNER JOIN TEMP_JIRAWORKFLOWS WF ON I.ISSUESTATUS = WF.LINKEDSTATUSID
  INNER JOIN OS_WFENTRY W ON I.WORKFLOW_ID = W.ID AND WF.WORKFLOWNAME = W.NAME
  WHERE C.STEP_ID != WF.STEPID OR C.ENTRY_ID IS NULL
) GOODSTEP ON (GOODSTEP.ENTRY_ID = OS_CURRENTSTEP.ENTRY_ID)
WHEN MATCHED THEN UPDATE
    SET OS_CURRENTSTEP.STEP_ID = GOODSTEP.STEP_ID
WHEN NOT MATCHED THEN INSERT
VALUES (GOODSTEP.N, GOODSTEP.ENTRY_ID, GOODSTEP.STEP_ID, 0, NULL, SYSDATE, NULL, NULL, GOODSTEP.STATUS, NULL);
 
UPDATE SEQUENCE_VALUE_ITEM 
   SET SEQ_ID = (1 + (SELECT MAX(ID) FROM OS_CURRENTSTEP)) 
   WHERE SEQ_NAME = 'OSCurrentStep';
 
COMMIT;

The fix is more complex than the others. We need an ID range taken from the SEQUENCE_VALUE_ITEM table, so later nothing tries to use our id-s. I wonder, why Atlassian does not utilize things like autoincrement fields or sequences...
So we can fetch the step_id and status_id that took us to the current step, essentially everything we need in the currentstep. Then, we increment the sequence.