SQL Query for JIRA issues

0
votes

I have to get some data from JIRA database and create a view to show them.

In our JIRA, we have around 100 projects. We need to create a Oracle view to visualize following data.

Our work-flow has following statuses Open Fix Resolved Test Validate Closed Rejected

For a single project, if issues are Delivery-CR, Fault-Bug, Fault-Specification, Fault-Configuration or Fault-Testing we need to create a view to visualize following data.

•   transition date – open to fix
•   transition date – fix to resolved
•   transition date – resolved to test
•   transition date – test to validate
•   Issue type
•   Status 
•   Fix version

Note that Issue type, Status and Fix version are on CHANGEITEM table. I can't find a table to get transaction dates. 

We are using JIRA 6.3.7 and Oracle 11g database

I have tried with following query but no success.

select created, issueid, ci.oldstring, ci.newstring, ci.oldvalue, ci.newvalue from changegroup inner join changeitem ci on changegroup.ID = ci.groupid where issueid in (select ID from JIRAISSUE where PROJECT in (select ID from PROJECT where PNAME = 'Singapore Exchange Post Trade') and ISSUETYPE in (select ID from ISSUETYPE where PNAME in ('Delivery-CR', 'Fault-Bug', 'Fault-Specification', 'Fault-Configuration', 'Fault-Testing'))) where ci.oldstring in('Open','Fix','Resolved','Test','Validate','Closed','Rejected');

Can some one tell me where I have gone wrong.

2
sqldatabaseoracle11gjira

2 Answers

2
votes

I suppose you want to have one issue per row.

This report won't be an easy one, there are some factors in the JIRA database structure which will make this slow.

I've built a query that joins columns through rejoining the same tables to get the different values of interests. You can add columns by repeating the CH_n left joins ("CHange").

WITH JIRAISSUE_W AS (
    SELECT
      JI.ID                                ,
      P.PKEY ||'-' || JI.ISSUENUM AS "KEY",
      IT.PNAME  AS ISSUETYPE                ,
      IST.PNAME AS ISSUESTATUS              ,
      CG.ISSUEID                            ,
      CG.AUTHOR                             AS "WHO",
      CG.CREATED                            AS "WHEN",
      CI.FIELD                              AS "WHAT",
      DBMS_LOB.SUBSTR(CI.OLDSTRING, 256, 1) AS "FROM",
      DBMS_LOB.SUBSTR(CI.NEWSTRING, 256, 1) AS "TO"
    FROM JIRAISSUE JI
    INNER JOIN ISSUESTATUS IST ON IST.ID = JI.ISSUESTATUS
    INNER JOIN PROJECT P ON P.ID = JI.PROJECT
    INNER JOIN ISSUETYPE IT ON JI.ISSUETYPE = IT.ID
    LEFT JOIN CHANGEGROUP CG ON CG.ISSUEID = JI.ID
    INNER JOIN CHANGEITEM CI ON CI.GROUPID = CG.ID
    WHERE IT.PNAME IN ('Delivery-CR', 'Fault-Bug', 'Fault-Specification',
                       'Fault-Configuration', 'Fault-Testing')
    ORDER BY JI.CREATED
  )
SELECT
  CH_1.KEY                   ,
  CH_1.ISSUETYPE             ,
  CH_1.ISSUESTATUS           ,
  FIXV."TO"   AS FIXVERSION  ,
  CH_1."WHEN" AS "TO_FIX"    ,
  CH_2."WHEN" AS "TO_RESOLVE",
  CH_3."WHEN" AS "TO_TEST"   ,
  CH_4."WHEN" AS "TO_VALIDATE"
FROM JIRAISSUE_W CH_1
LEFT JOIN JIRAISSUE_W FIXV ON 
  FIXV.ISSUEID = CH_1.ID AND 
  FIXV."WHAT"  = 'Fix Version'
LEFT JOIN JIRAISSUE_W CH_2 ON
  CH_2.ISSUEID = CH_1.ID AND
  CH_2."WHAT"  = 'status' AND
  CH_2."TO"    = 'Resolved'
LEFT JOIN JIRAISSUE_W CH_3 ON
  CH_3.ISSUEID = CH_1.ID AND
  CH_3."WHAT"  = 'status' AND
  CH_3."TO"    = 'Test'
LEFT JOIN JIRAISSUE_W CH_4 ON
  CH_4.ISSUEID = CH_1.ID AND
  CH_4."WHAT"  = 'status' AND
  CH_4."TO"    = 'Validated'
WHERE
  CH_1."WHAT" = 'status' AND
  CH_1."TO"   = 'Fix';

Not the best under an hour, but you can go along with this for starters.

1
votes

I've been tasked with recreating the transition view in Jira, and thought my solution might be of help to your problem.

We are using Postgres, so I guess you need to change the age function. This performs very well:

select 
                ci.oldstring        as OldStatus
                , ci.newstring as NewStatus
                , AGE(cg.created, coalesce (LEAD (cg.created, -1) OVER closedTime, ji.created)) AS age 
                , cg.created as TransitionCreated
                , ji.created as IssueCreated
                , proj.pname as ProjectName
                , ji.id as IssueId
                , ji.summary
                , ji.reporter
                , ji.assignee
from jiraissue ji
JOIN project proj on ji.project = proj.id
join changegroup cg ON ji.id= cg.issueId
JOIN changeItem ci 
                on cg.id = ci.groupId 
                and ci.field = 'status'
WINDOW
                closedTime AS (PARTITION BY cg.issueId order by cg.id)
order by cg.issueid, cg.id