How to find where COT functions are used

Description

This is a bunch of queries to show where COT functions are used related to workflows and project. These are examples against JIRA 5.2 on PostgreSQL, your query may need to be adjusted for JIRA version or database differences.

Workflows using COT functions

select * from jiraworkflows where descriptor ilike '%org.swift.jira.cot%'

Workflow schemes with workflows using COT functions

select 
(select name from workflowscheme where id = scheme) as "Workflow Scheme"
from workflowschemeentity where workflow in (select workflowname from jiraworkflows where descriptor ilike '%org.swift.jira.cot%')

Project Workflow Scheme

select 
(select pname from project where id = source_node_id) as "Project",
(select name from workflowscheme where id = sink_node_id) as "Workflow Scheme"
from nodeassociation 
where source_node_entity = 'Project' and sink_node_entity = 'WorkflowScheme'

Putting these together to get projects using schemes with workflows that contain COT functions

Project's Using COT functions

select 
(select pname from project where id = source_node_id) as "Project",
(select name from workflowscheme where id = sink_node_id) as "Workflow Scheme"
from nodeassociation 
where source_node_entity = 'Project' and sink_node_entity = 'WorkflowScheme'
and sink_node_id in (select id from workflowscheme where name in (select workflowname from jiraworkflows where descriptor ilike '%org.swift.jira.cot%'))