How to get transitions by SQL?

[zaferc]

One of our customers wants to see jira transitions by SQL. We're using Jira Suite Utilities plugin and transitions are saved to os_historystep table. I can get issue and issue status by SQL but couldn't find a way to get workflow transitions on SQL. I think there must be a transitions table to join with os_historystep table through action_id column.

Do you have any idea?

[Jobin Kuruvilla [Adaptavist] · Accepted solution]

Transitions are not stored separately anywhere AFAIK. You can find the workflow XML in JIRAWorkflow table and that is what is used in the code to find the next available actions.

[zaferc]

Thanks for your answer but I couldn't find xml of transitions in the jiraworkflows table. I can only get XML from the admin page. However, as you say, there must be workflow xml which is saved in database. Is there another way to get transitions or transitions name. By the way, our customer is using jira 4.2.4.

[Jobin Kuruvilla [Adaptavist]]

You will find the XML in the descriptor field in that table. What do you mean by another way? via SQL or in a plugin via Java APIs?

[zaferc]

Oh, because of the size of XML, I couldn't see full XML code in descriptor field. Now, I get the XML. Thanks for your help.

[SaravanaKumar]

hi @zaferc   @Jobin Kuruvilla [Adaptavist] how to view full XML file in descriptor field in jiraworkflows table.

[William Crighton [CCC]]

At some time in the recent past (say 5 years) the following query showed a nice little result set of each issue's transition. However, it no longer seems to be working very well - I'm thinking the data integrity of os_currentstep and os_historystep is no longer strictly required:

select ji.id as issue_id
      , ji.workflow_id
      , osc.id as current_status_id
      , osc.start_date as current_start_date
      , osc.finish_date as current_finish_date
      , osc.status as current_status
      , osh.id as history_status_id
      , osh.start_date as history_start_date
      , osh.finish_date as history_finish_date
      , osh.status as history_status
      , osh.caller as history_user
  from jiraissue ji
      , os_historystep osh
      , os_currentstep osc
 where ji.workflow_id = osc.entry_id
   and osc.entry_id = osh.entry_id

-wc

[Logan G Hawkes]

The brute force method for mapping permission names against ids would be to open a permissions scheme and mouse-over the "Add" link for each permission. The URL will have the ID in the permissions parameter. For example, I opened the scheme with ID=11600 and moused over the "Add" link for the "Browse Projects" permission. The below link tells me that the permission id is 10.

http://jira.innitech.com/secure/admin/AddPermission!default.jspa?atl_token=A22lin&schemeId=11600&permissions=10 

[Amber Shelton]

Late to the party but...

https://confluence.cprime.io/display/SIL/hasPermission

This will also show the permission IDs (down in the "Permissions" table). Even if you don't have the SIL plugin, the permission IDs should be the same....