This page last changed on Sep 24, 2010 by mdoar.

This page shows how to examine each of a JIRA issue's fields via SQL. We will use JRA-3166 as a sample issue in our queries.

Simple fields

Most fields in JIRA are kept in the jiraissue table:

mysql> desc jiraissue;
+----------------------+---------------+------+-----+---------+-------+
| Field                | Type          | Null | Key | Default | Extra |
+----------------------+---------------+------+-----+---------+-------+
| ID                   | decimal(18,0) | NO   | PRI |         |       |
| pkey                 | varchar(255)  | YES  | MUL | NULL    |       |
| PROJECT              | decimal(18,0) | YES  | MUL | NULL    |       |
| REPORTER             | varchar(255)  | YES  |     | NULL    |       |
| ASSIGNEE             | varchar(255)  | YES  | MUL | NULL    |       |
| issuetype            | varchar(255)  | YES  |     | NULL    |       |
| SUMMARY              | varchar(255)  | YES  |     | NULL    |       |
| DESCRIPTION          | longtext      | YES  |     | NULL    |       |
| ENVIRONMENT          | longtext      | YES  |     | NULL    |       |
| PRIORITY             | varchar(255)  | YES  |     | NULL    |       |
| RESOLUTION           | varchar(255)  | YES  |     | NULL    |       |
| issuestatus          | varchar(255)  | YES  |     | NULL    |       |
| CREATED              | datetime      | YES  |     | NULL    |       |
| UPDATED              | datetime      | YES  |     | NULL    |       |
| DUEDATE              | datetime      | YES  |     | NULL    |       |
| VOTES                | decimal(18,0) | YES  |     | NULL    |       |
| TIMEORIGINALESTIMATE | decimal(18,0) | YES  |     | NULL    |       |
| TIMEESTIMATE         | decimal(18,0) | YES  |     | NULL    |       |
| TIMESPENT            | decimal(18,0) | YES  |     | NULL    |       |
| WORKFLOW_ID          | decimal(18,0) | YES  |     | NULL    |       |
| SECURITY             | decimal(18,0) | YES  |     | NULL    |       |
| FIXFOR               | decimal(18,0) | YES  |     | NULL    |       |
| COMPONENT            | decimal(18,0) | YES  |     | NULL    |       |
+----------------------+---------------+------+-----+---------+-------+

They can be retrieved with a regular select:

mysql> select id, pkey, project, reporter, assignee, issuetype, summary from jiraissue where pkey='JRA-3166';
+-------+----------+---------+-----------+----------+-----------+---------------------------------+
| id    | pkey     | project | reporter  | assignee | issuetype | summary                         |
+-------+----------+---------+-----------+----------+-----------+---------------------------------+
| 16550 | JRA-3166 |   10240 | mvleeuwen | NULL     | 2         | Database consistency check tool |
+-------+----------+---------+-----------+----------+-----------+---------------------------------+

User details

Say we wish to find out the email address and other details about our reporter, mvleeuwen. First we find this user's ID:

mysql> select id from userbase where username='mvleeuwen';
+-------+
| id    |
+-------+
| 13841 |
+-------+

Then use it to look up 'properties' of this userbase record (stored in propertysets. Each property has a record in the propertyentry table specifying its name and type, and a record in one of propertystring, propertydecimal, propertydate, propertytext, propertydata or propertynumber, depending on the type.

mysql> desc propertyentry;
+--------------+---------------+------+-----+---------+-------+
| Field        | Type          | Null | Key | Default | Extra |
+--------------+---------------+------+-----+---------+-------+
| ID           | decimal(18,0) | NO   | PRI |         |       |
| ENTITY_NAME  | varchar(255)  | YES  |     | NULL    |       |
| ENTITY_ID    | decimal(18,0) | YES  | MUL | NULL    |       |
| PROPERTY_KEY | varchar(255)  | YES  |     | NULL    |       |
| propertytype | decimal(9,0)  | YES  |     | NULL    |       |
+--------------+---------------+------+-----+---------+-------+

mysql> select * from propertyentry where ENTITY_NAME='OSUser' and ENTITY_ID=(select id from userbase where username='mvleeuwen');
+-------+-------------+-----------+--------------+--------------+
| ID    | ENTITY_NAME | ENTITY_ID | PROPERTY_KEY | propertytype |
+-------+-------------+-----------+--------------+--------------+
| 18352 | OSUser      |     13841 | email        |            5 |
| 18353 | OSUser      |     13841 | fullName     |            5 |
+-------+-------------+-----------+--------------+--------------+

So email and fullName are of type 5. which means the propertystring table. Here is the list of propertytype to table mappings:

propertyentry.propertytype value Table value is stored in Used for
1 propertynumber Boolean values, eg. user preferences
5 propertystring Most fields, eg. full names, email addresses
6 propertytext Large blocks of text, eg. the introduction text, HTML portletconfigurations
2 propertydecimal (int) Unused in JIRA, but used by Bamboo
3 propertydecimal (long) Unused in JIRA
7 propertydate Unused in JIRA
10 propertydata Unused in JIRA

So the email and fullName properties are strings, and so can be found in the propertystring table:

mysql> select * from propertystring where id in (18352, 18353);
+-------+---------------------+
| ID    | propertyvalue       |
+-------+---------------------+
| 18352 | lemval@zonnet.nl    |
| 18353 | Michael van Leeuwen |
+-------+---------------------+

Components and versions

Since each issue can have multiple components/versions, there is a join table between jiraissue and version/component tables called nodeassociation:

mysql> desc nodeassociation;
+--------------------+---------------+------+-----+---------+-------+
| Field              | Type          | Null | Key | Default | Extra |
+--------------------+---------------+------+-----+---------+-------+
| SOURCE_NODE_ID     | decimal(18,0) | NO   | PRI |         |       |
| SOURCE_NODE_ENTITY | varchar(60)   | NO   | PRI |         |       |
| SINK_NODE_ID       | decimal(18,0) | NO   | PRI |         |       |
| SINK_NODE_ENTITY   | varchar(60)   | NO   | PRI |         |       |
| ASSOCIATION_TYPE   | varchar(60)   | NO   | PRI |         |       |
| SEQUENCE           | decimal(9,0)  | YES  |     | NULL    |       |
+--------------------+---------------+------+-----+---------+-------+

mysql> select distinct SOURCE_NODE_ENTITY from nodeassociation;
+--------------------+
| SOURCE_NODE_ENTITY |
+--------------------+
| Issue              |
| Project            |
+--------------------+

mysql> select distinct SINK_NODE_ENTITY from nodeassociation;
+-----------------------+
| SINK_NODE_ENTITY      |
+-----------------------+
| IssueSecurityScheme   |
| PermissionScheme      |
| IssueTypeScreenScheme |
| NotificationScheme    |
| ProjectCategory       |
| FieldLayoutScheme     |
| Component             |
| Version               |
+-----------------------+

mysql> select distinct ASSOCIATION_TYPE from nodeassociation;
+------------------+
| ASSOCIATION_TYPE |
+------------------+
| IssueVersion     |
| IssueFixVersion  |
| IssueComponent   |
| ProjectScheme    |
| ProjectCategory  |
+------------------+

So to get fix-for versions of an issue, run:

mysql> select * from projectversion where id in (
    select SINK_NODE_ID from nodeassociation where ASSOCIATION_TYPE='IssueFixVersion' and SOURCE_NODE_ID=(
        select id from jiraissue where pkey='JRA-5351')
    );
+-------+---------+-------+-------------+----------+----------+----------+------+-------------+
| ID    | PROJECT | vname | DESCRIPTION | SEQUENCE | RELEASED | ARCHIVED | URL  | RELEASEDATE |
+-------+---------+-------+-------------+----------+----------+----------+------+-------------+
| 11614 |   10240 | 3.6   | NULL        |      131 | NULL     | NULL     | NULL | NULL        |
+-------+---------+-------+-------------+----------+----------+----------+------+-------------+

Similarly with affects versions:

mysql> select * from projectversion where id in (
    select SINK_NODE_ID from nodeassociation where ASSOCIATION_TYPE='IssueVersion' and SOURCE_NODE_ID=(
        select id from jiraissue where pkey='JRA-5351')
    );
+-------+---------+---------------------+-------------+----------+----------+----------+------+---------------------+
| ID    | PROJECT | vname               | DESCRIPTION | SEQUENCE | RELEASED | ARCHIVED | URL  | RELEASEDATE         |
+-------+---------+---------------------+-------------+----------+----------+----------+------+---------------------+
| 10931 |   10240 |  3.0.3 Professional | NULL        |       73 | true     | NULL     | NULL | 2004-11-19 00:00:00 |
| 10930 |   10240 |  3.0.3 Standard     | NULL        |       72 | true     | NULL     | NULL | 2004-11-19 00:00:00 |
| 10932 |   10240 |  3.0.3 Enterprise   | NULL        |       74 | true     | NULL     | NULL | 2004-11-19 00:00:00 |
+-------+---------+---------------------+-------------+----------+----------+----------+------+---------------------+

and components:

mysql> select * from component where id in (
    select SINK_NODE_ID from nodeassociation where ASSOCIATION_TYPE='IssueComponent' and SOURCE_NODE_ID=(
        select id from jiraissue where pkey='JRA-5351')
    );
+-------+---------+---------------+-------------+------+------+--------------+
| ID    | PROJECT | cname         | description | URL  | LEAD | ASSIGNEETYPE |
+-------+---------+---------------+-------------+------+------+--------------+
| 10126 |   10240 | Web interface | NULL        | NULL | NULL |         NULL |
+-------+---------+---------------+-------------+------+------+--------------+

Issue links


JIRA issue links are stored in the issuelink table, which simply links the IDs of two issues together, and records the link type:

mysql> desc issuelink;
+-------------+---------------+------+-----+---------+-------+
| Field       | Type          | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| ID          | decimal(18,0) | NO   | PRI |         |       | 
| LINKTYPE    | decimal(18,0) | YES  | MUL | NULL    |       | 
| SOURCE      | decimal(18,0) | YES  | MUL | NULL    |       | 
| DESTINATION | decimal(18,0) | YES  | MUL | NULL    |       | 
| SEQUENCE    | decimal(18,0) | YES  |     | NULL    |       | 
+-------------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

For instance, to list all links between TP-1 and TP-2:

mysql> select * from issuelink where SOURCE=(select id from jiraissue where pkey='TP-1') and DESTINATION=(select id from jiraissue where pkey='TP-2');
+-------+----------+--------+-------------+----------+
| ID    | LINKTYPE | SOURCE | DESTINATION | SEQUENCE |
+-------+----------+--------+-------------+----------+
| 10020 |    10000 |  10000 |       10010 |     NULL | 
+-------+----------+--------+-------------+----------+
1 row in set (0.00 sec)

Link types are defined in issuelinktype. This query prints all links in the system with their type:

mysql> select j1.pkey, issuelinktype.INWARD, j2.pkey from jiraissue j1, issuelink, issuelinktype, jiraissue j2 where j1.id=issuelink.SOURCE and j2.id=issuelink.DESTINATION and issuelinktype.id=issuelink.linktype;
+-------+---------------------+-------+
| pkey  | INWARD              | pkey  |
+-------+---------------------+-------+
| TP-4  | jira_subtask_inward | TP-5  | 
| TP-4  | jira_subtask_inward | TP-7  | 
| TP-4  | jira_subtask_inward | TP-8  | 
| TP-11 | jira_subtask_inward | TP-12 | 
| TP-4  | jira_subtask_inward | TP-6  | 
| TP-1  | is duplicated by    | TP-2  | 
+-------+---------------------+-------+
6 rows in set (0.00 sec)

Subtasks

As shown in the last query, JIRA records the issue-subtask relation as a link. The "subtask" link type is hidden in the user interface (indicated by the 'pstyle' value below), but visible in the database:

mysql> select * from issuelinktype;
+-------+-------------------+---------------------+----------------------+--------------+
| ID    | LINKNAME          | INWARD              | OUTWARD              | pstyle       |
+-------+-------------------+---------------------+----------------------+--------------+
| 10000 | Duplicate         | is duplicated by    | duplicates           | NULL         | 
| 10001 | jira_subtask_link | jira_subtask_inward | jira_subtask_outward | jira_subtask | 
+-------+-------------------+---------------------+----------------------+--------------+
2 rows in set (0.00 sec)

This means it is possible to convert an issue to a subtask, or vice-versa, by tweaking issuelink records.

Custom fields have their own set of tables. For details, see Custom fields


issuelink.png (image/png)
Document generated by Confluence on Mar 27, 2011 18:54