This post was originally posted on LinkedIn.
Atlassian Jira is an incredible tool for task tracking and structural activity monitoring. Many business processes, software development, inventory, and asset tracking activities can be effectively implemented using Jira.
Everything in Jira is an Issue which is a basic building block. Jira supports Basic and Advanced Search of these issues. Behind the scene, the search for issues is realized against Lucene index which is up to date the fastest and quite a general way for implementing blazing fast searches in the enterprise applications. For its Advanced Searches, Jira supports its own query language known as JQL, i.e. Jira Query Language. The platform translates the JQL syntax into Lucene compatible queries for further execution.
However, the default JQL functionality has its own limitations. In order to set the stage for this article and illustrate this limitation, I will use a real-world practical scenario. Let’s dive into the problem!
Information is the greatest asset an organization or individual may have today. The access to that Information must be protected and regularly monitored. Usually, all activities related to a particular business problem should be organized into Projects. Each Project will have one Project Lead and several Project Members. What are the assets associated with a Project? For Software Development Project usually, we have Document Management System, Source Code Repository, Task Management System, etc. Team Members must have appropriate access to these Project-related assets. Projects are dynamic categories. In other words, in time, additional members will join the Project and some of the existing Team Members will leave the Project. Security-aware organizations, especially ISO 27001 certified ones, must organize and structure the Project’s Add/Remove activities as a well-defined procedure. Jira is an excellent tool for that. For example, we may have the following two Jira issue types for that purpose:
- Add Member to Project: This task will have multiple sub-tasks such as: Assign Member to the Document Management System, Assign Member to the Source Code Repository, etc.
- Remove Member from Project: This task will have multiple sub-tasks: Remove Member from the Document Management System, Remove Member from the Source Code Repository, etc.
Adding members to the Projects is an easier task because it is based on the current and immediate need for resource capacity. However, the opposite is not true. Once a member is not part of the Project anymore, it might happen that the access has not been removed according to the procedures. This could happen from several reasons and situations where the time is tight (Disaster Recovery, unexpected incidents, virus epidemics, etc.). No matter how strong your Security procedures and awareness are, you MUST continuously monitor the access level to the Information.
Jira has an excellent feature that will support this “remainder” scenario, Jira Filter Subscriptions! The basic sketch is:
- There are ACL (Access Control List) tasks for Adding members to Projects
- Each ACL task has a Due Date field with a value of N days (30 days for example)
- Each Project Lead shall receive a notification e-mail with a list of ACL tasks that will reach their Due Date after several days (5 for example)
- The Project Lead shall verify the ACL list received. If the Project Member in the ACL issue is still a member, the Project Lead shall update the Due Date and move it forward in the future (+30 days). In the opposite case, the Project Lead shall initiate Removal.
Now, how can we construct this list of Project Members led by the Project Lead? If we are dealing with Relational Databases, the SQL query could be something like this (Due Date has been omitted for simplicity):
SELECT acl_id, project_id, member_id, member_name, member_position FROM ACL WHERE project_id IN (SELECT DISTINCT project_id FROM ACL WHERE member_id=<<CURRENT_USER>> AND member_position=’Project Lead’)
This is a quite usual SQL query for application and database developers dealing with Relational Databases. In addition, each database has its own optimization of the query execution plan.
However, this kind of sub-queries are not possible with JQL out of the box!
Fortunately, Jira’s rich plugin system allows us to create custom JQL functions that will cover custom functionalities. This article won’t start from the beginning and explain how to initialize Jira Plugin project and implement JQL function. The task is simple and well explained in the Jira Developers documentation.
Let’s assume that:
- ACL issue has a Project Name custom field which is a list of existing Projects. Without losing generality we will say that the id of this field is cf[10000]. In practice, this field contains the name of the Project we are adding Project Members to;
- We have implemented the custom JQL function already and its name is filterAndProjectCustomField.
With these assumptions the JQL query with sub-query functionality could be:
project = ACL AND cf[10000] IN
filterAndProjectCustomField('project = ACL AND Member =currentUser() AND "Project Role" = "Project Lead"','10000','true')
The function filterAndProjectCustomField receives 3 parameters:
- jqlQuery: This is a string value that represents the JQL sub-query to be executed
- customFieldId: The custom field within the issues to be projected into the output result list
- cached: Cache the sub-query execution results (true/false). I will explain the caching functionality later in the article.
Having this query in place, we can create a Jira filter and subscribe a particular group of users to that filter (jira-users for example). Jira will repeat the query for each user that belongs to that group and e-mail the results to each user (Project Lead).
The sub-query functionality with the filtering and projection logic is:
private List<QueryLiteral> queryValues(QueryCreationContext queryCreationContext, FunctionOperand operand, String jqlQuery, String customFieldId) {
log.debug("queryValues start...");
if (!customFieldId.startsWith(CUSTOMFIELD))
customFieldId = CUSTOMFIELD + customFieldId;
final CustomFieldManager customFieldManager = ComponentAccessor.getCustomFieldManager();
final CustomField customField = customFieldManager.getCustomFieldObject(customFieldId);
if (customField == null)
throw new RuntimeException("The custom field with id '" + customFieldId
+ "' does not exist! Use the format 'customfield_<<field_id>>' or '<<field_id>>'");
final SearchService searchService = (SearchService) ComponentAccessor.getComponent(SearchService.class);
final ApplicationUser currentUser = queryCreationContext.getApplicationUser();
try {
final ParseResult parseResult = searchService.parseQuery(currentUser, jqlQuery);
if (parseResult.getErrors().hasAnyErrors())
throw new RuntimeException(
"Query parse error:" + parseResult.getErrors().getErrorMessages().toString());
final Query query = parseResult.getQuery();
final SearchResults searchResults = searchService.search(currentUser, query,
PagerFilter.getUnlimitedFilter());
@SuppressWarnings("unchecked")
final List<Issue> issues = (List<Issue>) getResults.invoke(searchResults);
final Set<String> values = new HashSet<String>();
for (Issue issue : issues) {
Object object = issue.getCustomFieldValue(customField);
if (object != null) {
final String value = object.toString();
if (value != null)
values.add(value);
}
}
final List<QueryLiteral> literals = new LinkedList<>();
for (String value : values) {
literals.add(new QueryLiteral(operand, value));
log.debug("Adding '{}' in the project list.", value);
}
log.debug("queryValues end.");
return literals;
} catch (RuntimeException re) {
throw re;
} catch (Throwable t) {
throw new RuntimeException(t.getClass().getName() + ":" + t.getMessage());
}
}
The results retrieving logic that takes into account cached results are wrapped as:
public List<QueryLiteral> getValues(QueryCreationContext queryCreationContext, FunctionOperand operand, TerminalClause terminalClause) {
log.debug("getValues start...");
final String jqlQuery = operand.getArgs().get(0);
final String customFieldId = operand.getArgs().get(1);
final Boolean cached = Boolean.valueOf(operand.getArgs().get(2));
final ApplicationUser currentUser = queryCreationContext.getApplicationUser();
List<QueryLiteral> results = null;
if (Boolean.TRUE.equals(cached)) {
String key = String.format("%s_%s_%s", jqlQuery, customFieldId, currentUser.getUsername());
results = cache.getIfPresent(key);
if (results != null)
log.debug("Cache hit for key '{}'!", key);
else {
results = queryValues(queryCreationContext, operand, jqlQuery, customFieldId);
cache.put(key, results);
log.debug("Entering cache value '{}' for cache key '{}'.", results.toString(), key);
}
} else {
results = queryValues(queryCreationContext, operand, jqlQuery, customFieldId);
}
log.debug("getValues end.");
return results;
}
Why do we need a sub-query result caching at all? Without caching, the custom function and JQL sub-query within will be executed for each ACL issue. However, in our case the results returned from the sub-query are not ACL specific, i.e. these results (a list of Project Names) are the same for all ACL tasks returned for the current user. Caching will speed up the execution a lot. The Cache is implemented with the Google Guava library:
public FilterAndProjectCustomField() {
cache = CacheBuilder.newBuilder()
.maximumSize(50)
.expireAfterWrite(1, TimeUnit.MINUTES)
.build();
resolveGetResultsMethod();
}
This cache has limited capacity in order to control the memory consumption. In addition, each result entry will expire after a minute.
At the end a bonus trick!
Maybe you have noticed the following line in the code above:
@SuppressWarnings("unchecked")
final List<Issue> issues = (List<Issue>) getResults.invoke(searchResults);
What is getResults? It seems like a Java Reflection invocation! Why can’t we use just:
searchResults.getResults()
Well, it seems at the moment there is some discrepancy among Atlassian SDK, Jira 7.x and Jira 8.x API. In Jira 7.x the method name is:
searchResults.getIssues()
On the contrary, the method name in Jira 8.x is:
searchResults.getResults()
In order to work with the current SDK and create a plugin that will work on both Jira 7.x and Jira 8.x, we must use a little bit of the Java Reflection magic:
private void resolveGetResultsMethod() {
if (getResults != null)
return;
try {
getResults = SearchResults.class.getMethod("getResults");
} catch (Throwable t) {
log.warn(t.getClass() + ":" + t.getMessage());
}
if (getResults == null)
try {
getResults = SearchResults.class.getMethod("getIssues");
} catch (Throwable t) {
throw new RuntimeException(t.getClass() + ":" + t.getMessage());
}
}
}
The getResults method is resolved during class instance initialization. From that point further, each JQL function execution uses the correct method signature. The solution has been tested successfully on both Jira 7.x and Jira 8.x instances.
This article was a little bit longer, but I hope it is insightful and inspiring. Jira is a powerful platform with a lot of extension points. If something is not available out of the box, be quite sure that there is a custom solution that will fit your requirements in a simple and elegant way.
If you want to discuss about Jira Query Sub-filter Plugin contact us.
