JQL performance - natural sort for custom text field

Question

I am struggling with a JQL query.

We have a custom field called 'Build Reported' which is a text field. It has values like '4.7.323H', '5.1.123L', '3.1.456E', etc.

I need to write a simple query that will give me all issues reported after the user-specified version.

JQL function prototype: searchIssues('Build Integrated', '>', '4.7.323B')

To do this, I am firing a JQL Query that gives me the Build Reported for all the issues, I then iterate through each issue and perform a char-by-char comparison to determine if the Build Reported version of the current issue is greater than the one specified by the user. This seems to take too long to execute since I have to retrieve all the issues from jira database.

Is there a faster way to achieve this? Here is what I have so far:

// Get all the arguments
java.util.List args = operand.getArgs();
CustomField cf = customFieldManager.getCustomFieldObjectByName((String)args.get(0));
Long cfID = cf.getIdAsLong();
String operator = (String)args.get(1);
String userVersion = (String)args.get(2);
String jiraVersion = "";

java.util.List issues;
Iterator issuesIterator;
Issue issue;

issues = getAllIssues(user, interestedInVersion, cfID);
issuesIterator = issues.iterator();

// Iterate over all the issues
while(issuesIterator.hasNext())
{
    issue = (Issue)issuesIterator.next();

    // Get the Build reported value
    jiraVersion = (String)issue.getCustomFieldValue(cf);

    if(jiraVersion != null &&
       !jiraVersion.equals(""))
    {
        // Compare user-specified version to the one retrieved from database
        if(compareVersions(jiraVersion, userVersion, operator))
        {
            // Add the issue to the result set
            literals.add(new QueryLiteral(operand, issue.getId()));
        }
    }
}

// cfID is the ID for the custom field Build Reported
private java.util.List getAllIssues(User user, Long cfID) throws SearchException, ParseException
{
    JqlQueryBuilder builder = JqlQueryBuilder.newBuilder();
    builder.where().project("SDEV").and().customField(cfID).isNotEmpty();
    Query query = builder.buildQuery();
    SearchResults results = searchService.search(user, query, PagerFilter.getUnlimitedFilter());
    return results.getIssues();
}

Please note that I do not have any other filters that I could use for the JQL Query Builder to help me reduce the size of the result set.

Answer 1

I found an alternative to the issue I described in my question. Instead of using JQL, I ended up firing a direct SELECT and this turned out to be way quicker. The function below is a part of the JQL Plugin. Here is the code:

This is what I ended up doing:

public java.util.List getValues(@NotNull QueryCreationContext queryCreationContext, @NotNull FunctionOperand operand, @NotNull TerminalClause terminalClause)
{
    try
    {
        // User
        User user = queryCreationContext.getUser();

        // Args
        java.util.List args = operand.getArgs();
        CustomField cf = customFieldManager.getCustomFieldObjectByName((String)args.get(0));
        Long cfID = cf.getIdAsLong();
        String operator = (String)args.get(1);
        String userVersion = (String)args.get(2);

        // Locals
        java.util.List literals = new java.util.LinkedList();
        MutableIssue issue = null;
        String issueId = "";
        String jiraVersion = "";

        // DB
        Connection conn = null;
        String url = "jdbc:jtds:sqlserver://*****:*****/jiradb";
        String driver = "net.sourceforge.jtds.jdbc.Driver";
        String userName = "*******";
        String password = "*******";
        String sqlQuery = null;
        Statement statement = null;
        ResultSet resultSet = null;

        Class.forName(driver).newInstance();
        conn = DriverManager.getConnection(url, userName, password);

        // Get all the issues that has the custom field set
        sqlQuery = " SELECT t2.id AS IssueId, t1.stringvalue AS JiraVersion " + "\n" +
                        " FROM   jiradb.jiraschema.customfieldvalue t1 " + "\n" +
                        " INNER JOIN jiradb.jiraschema.jiraissue t2 " + "\n" +
                        " ON     t1.issue = t2.id " + "\n" +
                        " WHERE  t1.customfield = " + Long.toString(cfID) + " " + "\n" +
                        " AND    t1.stringvalue IS NOT NULL " + "\n" +
                        " AND    t1.stringvalue != '' " + "\n" +
                        " AND    t2.pkey LIKE 'SDEV-%' ";

        // Iterate over the result set
        statement = conn.createStatement();
        resultSet = statement.executeQuery(sqlQuery);
        while (resultSet.next()) 
        {  
            issueId = resultSet.getString("IssueId");  
            jiraVersion = resultSet.getString("JiraVersion");

            // Compare the version from jira with the user provided version
            // This is my own function that does char-by-char comparison
            if(compareVersions(jiraVersion, userVersion, operator))
            {
                // Get the issue object to add to the result
                issue = ComponentManager.getInstance().getIssueManager().getIssueObject(Long.parseLong(issueId));

                // Add the issue to the result
                literals.add(new QueryLiteral(operand, issue.getId()));
            }
        }

        // Return all the matching issues here
        return literals;
    }
    catch(Exception e)
    {
        // Exception handling
    }

    return null;
}

And this is how the plugin is used:

issue in searchIssues('Build Reported', '>', '5.1.104');