Information about an issue is scattered around in multiple tables in the JIRA database. However, a good starting point is the jiraissue table, which is where the issue record is stored. It has foreign keys referencing other tables and, at the same time, the issue id is referenced in a few other tables.
The following diagram captures the important tables that the jiraissue table has a parent relationship with. Depending on the JIRA version, there might be slight variations but this is probably a good starting point:
As you can see, critical information about an issue, such as the project, issue type, status, priority, resolution, security level, and workflow, is all stored in the respective tables but is referenced from the jiraissue table, using a foreign key. The foreign key points to the id of the other tables in all cases, but there are no foreign key constraints enforced on any of these tables.
From JIRA 6.1, JIRA supports project renaming and hence the pkey column doesn't include the JIRA issue key anymore. Instead, issuenum holds the numeric part of a JIRA issue key and the project key has to be found from the project table, using the project column in the jiraissue table. We will see an example in this recipe.
The following diagram shows the tables that the jiraissue table has a child relationship with:
Here, the tables customfieldvalue, changegroup, jiraaction, label, worklog, fileattachment, issuelink, trackback_ping, and so on have a foreign key with the name issueid or issue (or source or destination) pointing to the relevant issue's id.
In this recipe, we will learn how to access some of the issue's information with the help of the previous diagrams.
When there is a parent-child relationship between tables, we can do a join operation to get most of the information we are looking for. For example, all the issues along with their project names can be retrieved by the following query:
SELECT ji.id, ji.issuenum, pr.pname
FROM jiraissue ji INNER JOIN project pr
ON ji.project = pr.id;
Here we do an inner join on the condition that the project's id is the same as the project column value in the jiraissue table.
The results will look like the following:
|
ID |
ISSUENUM |
PNAME |
|
10000 |
1 |
DEMO Project |
|
10001 |
2 |
DEMO Project |
As you probably noticed, the above query returns only the numeric part of the JIRA issue key. We can use the SQL functions or operands, depending on the database server you are using, to combine the issuenum column with pkey column in the project table. For example, we can use the concat function in the H2 database, as shown below:
SELECT ji.id, concat(pr.pkey, '-',ji.issuenum) as issuekey, pr.pname
FROM jiraissue ji INNER JOIN project pr
ON ji.project = pr.id;
The results will now look like the following:
|
ID |
ISSUEKEY |
PNAME |
|
10000 |
DEMO-1 |
DEMO Project |
|
10001 |
DEMO-2 |
DEMO Project |
Similarly, all the comments on an issue can be retrieved by the following query:
SELECT ji.id, concat(pr.pkey, '-',ji.issuenum) as issuekey, ja.actionbody, ja.created, ja.author
FROM jiraissue ji
LEFT JOIN jiraaction ja ON ji.id = ja.issueid
INNER JOIN project pr ON ji.project = pr.id;
And, the results will look like the following:
|
ID |
ISSUEKEY |
ACTIONBODY |
CREATED |
AUTHOR |
|
10000 |
DEMO-1 |
Sample Comment 1 |
2015-10-27 22:43:14.569 |
jobinkk |
|
10000 |
DEMO-1 |
Sample Comment 2 |
2015-10-29 15:43:14.569 |
testuser |
In the example, we retrieve the comments on issues with their author and created date. The same approach can be used with all tables in the previous diagrams.
Accessing version and component information on an issue is slightly different. Even though you see the fixfor and component columns in the jiraissue table, they are not used anymore!
Each issue can have multiple versions or components and hence there is a join table between the jiraissue and version/component tables, called nodeassociation. source_node_entity will be the issue and the source_node_id represents the issue id. The sink_node_entity value will be Component or Versions in this case, and sink_node_id will hold the id of the respective component or version.
There is a third column, association_type, which will be IssueFixVersion, IssueVersion, or IssueComponent for the fix for versions, affected versions, or components respectively.
We can access the components of an issue as follows:
SELECT concat(pr.pkey, '-',ji.issuenum) as issuekey, comp.cname
FROM nodeassociation na, component comp, jiraissue ji, project pr
WHERE comp.id = na.sink_node_id
AND ji.id = na.source_node_id
AND na.association_type = 'IssueComponent'
AND pr.pkey='DEMO'
AND ji.issuenum='123';
Here, DEMO-123 is the issue. We can also retrieve the affected versions and fix versions in a similar fashion.