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 i.id, 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 i.id=cv1.issue left join (select issue, stringvalue from customfieldvalue where customfield=10141) as cv2 on i.id=cv2.issue left join (select issue, stringvalue from customfieldvalue where customfield=10142) as cv3 on i.id=cv3.issue order by cv2.stringvalue, cv3.stringvalue;

primarykey.column.number=0
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 jiraissue.id. 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.
  # http://confluence.atlassian.com/pages/viewpage.action?pageId=160835&focusedCommentId=224398519&#comment-224398519
  # DO NOT USE issue.setCustomFieldValue() - it does NOT persist. This is a feature (!)

  cf=cfm.getCustomFieldObject('customfield_10070')
  if issue.getReporter() == acBudgetHolder :
    cf.getCustomFieldType().updateValue(cf,issue,acEmergencyBudgetHolder)
  else :
    cf.getCustomFieldType().updateValue(cf,issue,acBudgetHolder)

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.

Getting Jira to enforce a date field is in the future on issue creation

As part of a new Jira process I am developing, I need to ensure that the date stored in a certain custom field is in the future. I was using the “compare date and time” validator for this, checking to see that the value of the custom field was greater than or equal to that of the issue Created field. This was working well while I was using it on a standard workflow transition, but it bothered me that the user needed to create an issue and then immediately transition it in order for the validation to work. My users are a technical lot, but even they are likely to forget the second step of the process.

You can use validators on the Create Issue transition (if you can find it –  go to the initial step of your workflow and look for “incoming transitions”), but the technique I was using doesn’t work, as the Created field isn’t updated until after the validator is processed and I was thus comparing with Null. I have Misc Workflow Extensions, Jira Suite Utilities and Jira Toolkit installed, but none provided a validator that would compare a date field with anything other than another date field. Luckily I also have the Jira Scripting Suite installed, and I thought this would be a trivial use case. Grab the custom field data using a jython one-liner and compare with the system date.

It wasn’t so simple. Jira returns date custom field values as java.sql.Timestamp objects, which are nontrivial to compare with jython date objects. I then tried using java.util.Calendar instead of the native jython datetime library, thinking that a java.util.Date would be more easily compared with a java.sql.Timestamp because they implement the same interface. This is technically true, but I didn’t want to compare instantaneous times, only dates, and there is no native method for extracting a date-only component (as a Julian date, say) from a java.util.Date object. One might expect to be able to obtain the raw timestamp data and discard the fractional day components (say, by performing an integer division), but this falls foul of time zones.

Jira treats dates as timestamps with zeroed time-of-day components, but the database backend stores this as milliseconds since the Epoch, and this is what is returned in the Timestamp object. A bare date is thus stored as the timestamp of the immediately previous local midnight. In the case of Ireland in summertime, this will cause an off-by-one error in the algorithm if one naïvely performs an integer division by the number of milliseconds in a day. To find the correct number of local days since the Epoch, one must add the current timezone offset to both timestamps before discarding fractional days. The resulting algorithm is thus far from the desired one-liner:

from com.atlassian.jira import ComponentManager
from java.util import Calendar

cfm = ComponentManager.getInstance().getCustomFieldManager()
leavingDate = issue.getCustomFieldValue(cfm.getCustomFieldObject('customfield_10072'))
today = Calendar.getInstance()
tzoffset = today.getTimeZone().getOffset(today.getTimeInMillis())
leavingDateEpoch = (leavingDate.getTime()+tzoffset)//86400000
todayEpoch = (today.getTimeInMillis()+tzoffset)//86400000

if leavingDateEpoch < todayEpoch :
 result = False
 description = "Leaving date cannot be in the past"
 invalid_fields['customfield_10072'] = u"Leaving date cannot be in the past"

It works, but it’s damn ugly. I particularly love the double reference to the today object in line 7. How the Java standard library manages to be so verbose and still so lacking in basic functionality is beyond me. They actually seem to be going in the wrong direction – I found this wonderful piece of documentation on my travels:

int     Date.getTimezoneOffset()
Deprecated. As of JDK version 1.1, replaced by
-(Calendar.get(Calendar.ZONE_OFFSET) + Calendar.get(Calendar.DST_OFFSET)) / (60 * 1000)

Says it all, really.