More JIRA shenanigans

As part of my ongoing project to put every management process possible onto JIRA, I have been working on a way to make our internal account codes smarter. Previously, I was using a custom field with predefined values for each account code – this worked well until I wanted to automate certain tests and actions based on account code metadata such as project start/end dates and approval matrices (this issue was also a significant problem). Two methods sprung to mind: either I could define a custom field that could contain structured data, or I could implement the account codes as Jira issues and use issue linking. In my solution I ended up doing a bit of both.

The advantage of using JIRA issues to store account codes is twofold: firstly I can define whatever custom fields I require to hold structured data (e.g. budget holder userids, timestamps) in a format that JIRA can easily understand; and secondly the financial administrators and I can manage this data through a familiar interface. The big disadvantage is that issue linking in JIRA is done through a separate interface from issue creation/editing, meaning a two-stage process for my users. The ideal solution would be a drop-down menu in the issue creation/editing screen, but this means using a custom field – and one I would probably have to write myself.

Being a typical sysadmin, I decided to hack something together with sticky tape and string. I found a likely-looking custom field in the form of the JIRA Database Values Plugin. Using this to tap directly into JIRA’s back-end database I can populate a drop-down menu using some horrendous left joins:

sql.query=select, cv1.stringvalue, cv2.stringvalue, cv3.stringvalue from (select id from jiraissue where project=10200 and issuetype=13) as i left join (select issue, stringvalue from customfieldvalue where customfield=10140) as cv1 on left join (select issue, stringvalue from customfieldvalue where customfield=10141) as cv2 on left join (select issue, stringvalue from customfieldvalue where customfield=10142) as cv3 on order by cv2.stringvalue, cv3.stringvalue;

rendering.viewpattern={1,(none)} {2} {3}

The joins are necessary because JIRA uses a relational back end which assumes a fixed number of columns in each table. It handles issues with custom-defined fields by storing the custom field values in a separate table with back-references to the appropriate row in the main table.

The JDVP plugin provides a pretty front end to a custom field that stores the primary key of a database row, which in this case is I can then recall the account code issue in a JSS script by passing the primary key to getIssueObject() – the example below is a post-function that I use in an approval workflow to ensure that nobody can approve his own request:

from com.atlassian.jira import ComponentManager

# Only perform functions on Travel Request issues
if(issue.getIssueTypeObject().getId()=="8") :
  cm = ComponentManager.getInstance()
  cfm = cm.getCustomFieldManager()
  im = cm.getIssueManager()

  accountCodeID = issue.getCustomFieldValue(cfm.getCustomFieldObject('customfield_10134'))
  acIssue = im.getIssueObject(int(accountCodeID))
  acBudgetHolder = acIssue.getCustomFieldValue(cfm.getCustomFieldObject('customfield_10070'))
  acEmergencyBudgetHolder = acIssue.getCustomFieldValue(cfm.getCustomFieldObject('customfield_10151'))

  # Set the budget holder to the account code's budget holder, unless that is the reporter.
  # The Java API for custom fields is crap. I'm using a trick here.
  # DO NOT USE issue.setCustomFieldValue() - it does NOT persist. This is a feature (!)

  if issue.getReporter() == acBudgetHolder :
  else :

I have two things to say about the above. Firstly, scripts in a language where leading whitespace is significant are very difficult to post to blogs. Secondly, I meant everything I said about the custom fields API. In my previous post, I complained about the Java standard library requiring double references – well here’s another example. Read the last ten lines and try to tell me that the developer in question was sober when he designed the interface.

This does exactly what I want (like all good sticky-tape solutions should). The only thing the JDVP did that annoyed me was to always render “Unknown” (or the localisation equivalent) in the case that one of my database fields contained NULL (some of our accounting codes have optional subcodes, for example). After discussions with the developer, I submitted a patch which made the NULL behaviour configurable, and he has included it in the latest release. Thanks, Wim.

2 thoughts on “More JIRA shenanigans

  1. As you were using JIRA 4.1.1 at the time or there abouts… Using today’s JIRA how would the above code now work? Bearing in mind some of the classes may or may not be different?

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s