JIRA 4.3 : Issue fields
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 fieldsMost 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 detailsSay 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:
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 versionsSince 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
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) SubtasksAs 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 |
![]() |
Document generated by Confluence on Mar 27, 2011 18:54 |