Retrieve Project Schemes and Categories for all Jira projects via the database

Question

Hello Team,

MySQL 8.0 Query Retrieve Project Schemes and Categories for all Jira projects via the database,

I hope someone helping me.

EX:

projectname   |  projectlead  |                       fieldlayoutscheme                       | issuesecurityscheme |              issuetypescreenscheme               |              issuetypescheme              |     notificationscheme      |                   permissionscheme                   |              priorityscheme              | projectcategory |                             workflowscheme                              
-----------------+---------------+---------------------------------------------------------------+---------------------+--------------------------------------------------+-------------------------------------------+-----------------------------+------------------------------------------------------+------------------------------------------+-----------------+-------------------------------------------------------------------------
 ADJAS           | JIRAUSER10000 |                                                               |                     | ADJAS: Scrum Issue Type Screen Scheme            |                                           | Default Notification Scheme | Default software scheme                              |                                          |                 | jdg-schema-for-project-adjamdjamensis
 ADRYSA          | JIRAUSER10000 |                                                               |                     | ADRYSA: Scrum Issue Type Screen Scheme           |                                           | Default Notification Scheme | Default software scheme                              |                                          | Linking_CatTwo  | jdg-schema-for-project-adryasa

Florian Bonniec · Answer

Base on the following KB, this should works

https://confluence.atlassian.com/jirakb/retrieve-project-schemes-and-categories-for-all-jira-projects-via-the-database-1085181544.html

select prjkey,prj.lead as Prjlead,cte.AssocScheme as Category,cname from project prj join (select p.id as pid, p.pkey as prjkey,sink_node_id as AssocID, sink_node_entity as AssocScheme from project p join nodeassociation na on p.id = na.source_node_id and source_node_entity='Project') as cte on cte.pid=prj.id join projectcategory PC on cte.AssocID=PC.ID and cte.AssocScheme='ProjectCategory'
union
select prjkey,prj.lead as Prjlead,cte.AssocScheme as Category,NS.name from project prj join (select p.id as pid, p.pkey as prjkey,sink_node_id as AssocID, sink_node_entity as AssocScheme from project p join nodeassociation na on p.id = na.source_node_id and source_node_entity='Project') as cte on cte.pid=prj.id join notificationscheme NS on cte.AssocID=NS.ID and cte.AssocScheme='NotificationScheme'
union
select prjkey,prj.lead as Prjlead,cte.AssocScheme as Category,ISS.name from project prj join (select p.id as pid, p.pkey as prjkey,sink_node_id as AssocID, sink_node_entity as AssocScheme from project p join nodeassociation na on p.id = na.source_node_id and source_node_entity='Project') as cte on cte.pid=prj.id join issuesecurityscheme ISS on cte.AssocID=ISS.ID and cte.AssocScheme='IssueSecurityScheme'
union
select prjkey,prj.lead as Prjlead,cte.AssocScheme as Category,ITSS.name from project prj join (select p.id as pid, p.pkey as prjkey,sink_node_id as AssocID, sink_node_entity as AssocScheme from project p join nodeassociation na on p.id = na.source_node_id and source_node_entity='Project') as cte on cte.pid=prj.id join issuetypescreenscheme ITSS on cte.AssocID=ITSS.ID and cte.AssocScheme='IssueTypeScreenScheme'
union
select prjkey,prj.lead as Prjlead,cte.AssocScheme as Category,WFS.name from project prj join (select p.id as pid, p.pkey as prjkey,sink_node_id as AssocID, sink_node_entity as AssocScheme from project p join nodeassociation na on p.id = na.source_node_id and source_node_entity='Project') as cte on cte.pid=prj.id join workflowscheme WFS on cte.AssocID=WFS.ID and cte.AssocScheme='WorkflowScheme'
union
select prjkey,prj.lead as Prjlead,cte.AssocScheme as Category,PRS.name from project prj join (select p.id as pid, p.pkey as prjkey,sink_node_id as AssocID, sink_node_entity as AssocScheme from project p join nodeassociation na on p.id = na.source_node_id and source_node_entity='Project') as cte on cte.pid=prj.id join permissionscheme PRS on cte.AssocID=PRS.ID and cte.AssocScheme='PermissionScheme'
union
select prjkey,prj.lead as Prjlead,cte.AssocScheme as Category,FLS.name from project prj join (select p.id as pid, p.pkey as prjkey,sink_node_id as AssocID, sink_node_entity as AssocScheme from project p join nodeassociation na on p.id = na.source_node_id and source_node_entity='Project') as cte on cte.pid=prj.id join fieldlayoutscheme FLS on cte.AssocID=FLS.ID and cte.AssocScheme='FieldLayoutScheme'
union
select prj.pkey as prjkey,prj.lead as Prjlead,'PriorityScheme' as Category, fcs.configname from project prj join configurationcontext cct on cct.project=prj.id and customfield='priority' join fieldconfigscheme fcs on fcs.id=cct.fieldconfigscheme and fieldid='priority'
union
select prj.pkey as prjkey,prj.lead as Prjlead,'IssueType Scheme' as Category, fcs.configname from project prj join configurationcontext cct on cct.project=prj.id and customfield='issuetype' join fieldconfigscheme fcs on fcs.id=cct.fieldconfigscheme and fieldid='issuetype'
order by 1,3

Regards

veereshpatil0101 · Answer

@veeru

I have seen but that much of I'm not understand to write the query, can you send the MySQL query.

Florian Bonniec · Answer

Hi @veeru

You can use the database schema shared by atlassian to see table relation.

https://developer.atlassian.com/server/jira/platform/database-schema/

Regards