Marco Tech Blog

In Embarcadero, for the RAD Studio project, we have two JIRA systems. One is a public system better known as Quality Portal (https://quality.embarcadero.com), while the second is an internal JIRA system accessible only behind the firewall. The two systems are kept in sync, as I explained in the past, so that a bug opened in the public system is reflected in the internal one; as we close a bug in the internal system and ship the fix, the internal status is reflected in the public system.

Almost always. In fact, there are reasons (timing, specific configurations, unexpected workflows, timeouts during synch operations, scripts that get stuck...) that prevent the regular flow to happen, leaving bugs in an inconsistent state. Specifically, we often have bugs closed internally that remain open (indefinitely) in the public system. I've spent time in the past looking for these orphaned items, but it is a fairly tedous process. So I though of writing an application to automate this. I could have used JIRA own scripting, but the process needs manual overview, which makes it more complex. So why not use Delphi?

Using the CData JIRA Enteprise Connector to run a query against JIRA and storing the data in a dataset takes literally a few minutes. You can use FireDAC query editor to refine your query, and write fairly standard Delphi dataset processing code. This blog post is a description of what I did to achieve my goal -- and the application I build it going to save me a lot of time in the future.

First, I've added to a data module (of a good old VCL application) the components needed to access quality portal and query for all of the open issues:

object QualityportalConnection: TFDConnection
  Params.Strings = (
    'URL=http://quality.embarcadero.com'
    'ConnectionDef=QualityPortal'
    'IncludeCustomFields=true')
  LoginPrompt = False
end
object QPIssuesTable: TFDQuery
  Connection = QualityportalConnection
  SQL.Strings = (
      'SELECT Key, StatusName, IssueTypeName, Summary, InternalID FROM ' +
      'CData.JIRA.Issues where ProjectKey = '#39'RSP'#39' AND StatusName = '#39'Open'#39)
end

The connection uses the IncludeCustomFields flag to retrieve extra custom fields of our JIRA installation, namely the cross reference from one JIRA system to the other, stored in the InternalID field -- and something a regular user without admin permissions should not be able to see. In fact, you need to provide your user name and password, the regular ones you use on the site.

For query I've picked the fields I'm interested in, to reduce network traffic (I'm reading a lot of data!) and filtered on RSP (Data Studio Project) and Open status. You can see the query running in FireDAC query editor below:

I also created static field objects for all fields in the query. For the internal JIRA analysis I've defined a query to retrieve information about an individual item, using a parametric query (like in SQL):

SELECT Key, StatusName, FixVersionsAggregate, StatusName, ResolutionName FROM CData.JIRA.Issues where Key = :key 

With this query I'm retrieving the status, the type of resolution and the fix version. The basic approach of the application is to iterate all of the items open in the public system, fetch the internal status and check for inconsistencies. This is the basic loop:

  QPIssuesTable.Open;

  // for each, check internal status
  QPIssuesTable.First;
  while not QPIssuesTable.EOF do
  begin
    JiraIssuesTable.Close;
    JiraIssuesTable.Params[0].AsString := QPIssuesTableInternalID.AsString;
    JiraIssuesTable.Open;

    // consider only issues closed internally
    if (JiraIssuesTableStatusName.AsString = 'Closed') then
    begin
      ...
    end;

    QPIssuesTable.Next;
  end;

There is another part of the code I'm omitting to examine the fixVersion, which is a string with same complex JSON structure (an array of records). I need to extract the version number to see if the issue was closed in the past or it has just been closed for a not-yet-released version (in which case, it is fine). For all of the matches (issues closed in released versions) I add them to an FDMemTable created with appropriate fields and connected to a DBGrid:

      begin
        FDMemTable1.Insert;
        FDMemTable1QPKey.AsString := QPIssuesTableKey.AsString;
        FDMemTable1JiraKey.AsString := JiraIssuesTableKey.AsString;
        FDMemTable1QPStatus.AsString := QPIssuesTableStatusName.AsString;
        FDMemTable1JIRAStatus.AsString := JiraIssuesTableStatusName.AsString;
        FDMemTable1IssueType.AsString := QPIssuesTableIssueTypeName.AsString;
        FDMemTable1Resolution.AsString := JiraIssuesTableResolutionName.AsString;
        FDMemTable1Summary.AsString := QPIssuesTableSummary.AsString;
        FDMemTable1FixVersions.AsString := fixJSONString;
        FDMemTable1.Post;
      end; 

With this code, I obtain a grid with all items most likely out of synch, as displayed below, which lists the two IDs, the status on both systems, the internal resolution, and the version is was fixed:

The version number are internal ones, but I imagine you can guess which public version they match to. With the help of the data in this grid, I can go over the issues, open them in the internal system (if in doubt), and decide what to do. If I want to synchronize them, I need to add them to a list in the format expected by a JIRA script we use to force the synchronization. By double clicking on the grid, I execute the trivial code below that creates the comma separated list of keys I need:

procedure TForm14.DBGrid1DblClick(Sender: TObject);
begin
  Edit1.Text := Edit1.Text + ', ' + DataModule15.FDMemTable1JiraKey.AsString;
end;

So much less copy and paste! And now I can use the next couple of days to cleanup the Quality Portal system a bit...