Need help in scripting Oracle query to determine which plugin(app) modules used by the jira workflow

[Kiran Panduga {Appfire}]

Need help in scripting Oracle query to determine which plugin(app) modules used by the jira workflow post function or validator.

Something which  goes straight to the jiraworkflows table and parses out the XML of the workflow and checks each of the 3 usual sections (initial-actions, common-actions, and global-actions), for conditions, validators, and post-functions, for the arg named "class.name" that contains something like "com.googlecode.jsu.workflow.validator.FieldsRequiredValidator".

The below query works for MySQL

-- This MySQL query obtains the Validators, Conditions, and Postfunctions referenced in a Jira Workflow, by 
-- parsing the workflow's XML using the ExtractValue() function. Since it returns an empty Class value if 
-- that xpath isn't found in the XML, we then need to put the long Selects & Unions into a subselect 
-- so we can apply a WHERE clause to eliminate rows with nothing in the Class column. We also use a 
-- REPLACE() function on the Class column to turn the space-delimited set of values into a 
-- comma-delimited set of values to make it a bit easier to read.
SELECT X.Workflow, X.Type, X.Subtype, REPLACE(X.Classes," ",", ") AS Classes
FROM (SELECT 
            workflowname AS Workflow,
            'Validator ' AS Type,
            'Initial Action' AS Subtype,
            ExtractValue(descriptor, '/workflow/initial-actions/action/validators/validator/arg[@name="class.name"]') AS Classes
         FROM jiraworkflows 
      UNION
      SELECT 
            workflowname AS Workflow,
            'PostFunction' AS Type,
            'Initial Action' AS Subtype,
            ExtractValue(descriptor, '/workflow/initial-actions/action/results/unconditional-result/post-functions/function/arg[@name="class.name"]') AS Classes
         FROM jiraworkflows 
      -- /////////////////// COMMON ACTIONS ///////////////////       UNION
      SELECT 
            workflowname AS Workflow,
            'Validator' AS Type,
            'Common Action' AS Subtype,
            ExtractValue(descriptor, '/workflow/common-actions/action/validators/validator/arg[@name="class.name"]') AS Classes
         FROM jiraworkflows 
      UNION 
      SELECT
            workflowname AS Workflow,
            'Condition' AS Type,
            'Common Action' AS Subtype,
            ExtractValue(descriptor, '/workflow/common-actions/action/restrict-to/conditions/condition/arg[@name="class.name"]') AS Classes
         FROM jiraworkflows 
      UNION
      SELECT
            workflowname AS Workflow,
            'PostFunction' AS Type,
            'Common Action' AS Subtype,
            ExtractValue(descriptor, '/workflow/common-actions/action/results/unconditional-result/post-functions/function/arg[@name="class.name"]') AS Classes
         FROM jiraworkflows
     -- /////////////////// GLOBAL ACTIONS ///////////////////       UNION
      SELECT 
            workflowname AS Workflow,
            'Validator' AS Type,
            'Global Action' AS Subtype,
            ExtractValue(descriptor, '/workflow/global-actions/action/validators/validator/arg[@name="class.name"]') AS Classes
         FROM jiraworkflows 
      UNION
      SELECT
            workflowname AS Workflow,
            'Condition' AS Type,
            'Global Action' AS Subtype,
            ExtractValue(descriptor, '/workflow/global-actions/action/restrict-to/conditions/condition/arg[@name="class.name"]') AS Classes
         FROM jiraworkflows 
      UNION
      SELECT
            workflowname AS Workflow,
            'PostFunction' AS Type,
            'Global Action' AS Subtype,
            ExtractValue(descriptor, '/workflow/global-actions/action/results/unconditional-result/post-functions/function/arg[@name="class.name"]') AS Classes
         FROM jiraworkflows
ORDER BY Workflow, Type, Subtype) AS X
WHERE X.Classes <> "" 

Need help to get this work in Oracle.

thanks,

Kiran.

[Andy Heinzer]

Hi Kiran,

I understand that you are trying to use Oracle SQL to build a database query that can help you find workflows in Jira that might be using functions that come from plugins to Jira.  

I have tried my own hand at trying to parse out the xpath() command, as the Oracle equivalent to the MySQL extract() command, to get this XML data in the database in the manner that I think would work for that flavor of SQL. Unfortunately, I have not found the correct syntax here yet either.

But I did stumble across a free plugin for Jira Server called Search by workflows.  It appears to let you search workflows within Jira for just this kind of information.  Apologies that this is not exactly a SQL query that I know you requested, but I think it might be able to provide you with the information in another format that I think you are seeking here.

Cheers,

Andy