https://developer.atlassian.com/server/jira/platform/database-schema/
https://developer.atlassian.com/server/jira/platform/database-issue-fields/
https://developer.atlassian.com/server/jira/platform/database-custom-fields/
https://developer.atlassian.com/server/jira/platform/about-jira-modules/
https://developer.atlassian.com/server/jira/platform/java-apis/
Database – Issue fields ON THIS PAGE Simple fields User details Components and versions Issue links This page shows how to examine each of a Jira issue's fields via SQL. Simple fields Most fields in Jira are kept in the jiraissue table: Copy mysql> desc jiraissue; +----------------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------------+---------------+------+-----+---------+-------+ | ID | decimal(18,0) | NO | PRI | NULL | | | pkey | varchar(255) | YES | | NULL | | | issuenum | decimal(18,0) | YES | MUL | NULL | | | PROJECT | decimal(18,0) | YES | MUL | NULL | | | REPORTER | varchar(255) | YES | MUL | NULL | | | ASSIGNEE | varchar(255) | YES | MUL | NULL | | | CREATOR | varchar(255) | YES | | 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 | MUL | NULL | | | UPDATED | datetime | YES | MUL | NULL | | | DUEDATE | datetime | YES | MUL | NULL | | | RESOLUTIONDATE | datetime | YES | MUL | NULL | | | VOTES | decimal(18,0) | YES | MUL | NULL | | | WATCHES | decimal(18,0) | YES | MUL | 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 | MUL | 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: Copy mysql> select id, issuenum, project, reporter, assignee, issuetype, summary from jiraissue where issuenum=3166 and project = (select id from project where pkey='JRA'); +-------+----------+---------+-----------+----------+-----------+---------------------------------+ | id | issuenum | project | reporter | assignee | issuetype | summary | +-------+----------+---------+-----------+----------+-----------+---------------------------------+ | 16550 | 3166 | 10240 | mvleeuwen | NULL | 2 | Database consistency check tool | +-------+----------+---------+-----------+----------+-----------+---------------------------------+ User details For example, we want to find out the email address and other details about our reporter mvleeuwen. Copy select user_name, directory_id, display_name, email_address from cwd_user where user_name = 'mvleeuwen'; Normally this should return a single row, however, Jira allows you to set up multiple user directories and it is possible that two or more directories contain the same username. For more information, go to User and Group Tables page. Components and versions Because each issue can have multiple components or versions, there is a join table between jiraissue and version/component tables called nodeassociation: Copy 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 the following: Copy 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 issuenum=5351 and project = (select id from project where pkey='JRA')); +-------+---------+-------+-------------+----------+----------+----------+------+-------------------------+ | ID | PROJECT | vname | DESCRIPTION | SEQUENCE | RELEASED | ARCHIVED | URL | STARTDATE | RELEASEDATE | +-------+---------+-------+-------------+----------+----------+----------+------+-------------------------+ | 11614 | 10240 | 3.6 | NULL | 131 | NULL | NULL | NULL | NULL | NULL | +-------+---------+-------+-------------+----------+----------+----------+------+-------------------------+ Similarly with affects versions: Copy 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 issuenum=5351 and project = (select id from project where pkey='JRA'))); +-------+---------+---------------------+-------------+----------+----------+----------+------+---------------------+ | 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 | +-------+---------+---------------------+-------------+----------+----------+----------+------+---------------------+ Similarly with components: Copy 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 issuenum=5351 and project = (select id from project where pkey='JRA'))); +-------+---------+---------------+-------------+------+------+--------------+ | 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. Copy 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: Copy mysql> select * from issuelink where SOURCE=(select id from jiraissue where issuenum=1 and project=(select id from project where pkey='TP')) and DESTINATION=(select id from jiraissue where issuenum=2 and project=(select id from project where pkey='TP')); +-------+----------+--------+-------------+----------+ | 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: Copy 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 "sub-task" link type is hidden in the user interface (indicated by the pstyle value below), but visible in the database: Copy mysql> select * from issuelinktype; +-------+-------------------+---------------------+----------------------+--------------------+ | ID | LINKNAME | INWARD | OUTWARD | pstyle | +-------+-------------------+---------------------+----------------------+--------------------+ | 10000 | Blocks | is blocked by | blocks | NULL | | 10001 | Cloners | is cloned by | clones | NULL | | 10002 | Duplicate | is duplicated by | duplicates | NULL | | 10003 | Relates | relates to | relates to | NULL | | 10100 | jira_subtask_link | jira_subtask_inward | jira_subtask_outward | jira_subtask | | 10200 | Epic-Story Link | has Epic | is Epic of | jira_gh_epic_story | +-------+-------------------+---------------------+----------------------+--------------------+ 6 rows in set (0.00 sec) This means that it is possible to convert an issue to a sub-task, or vice versa, by tweaking issuelink records. Custom fields have their own set of tables. For details, see Database - Custom fields.
User table
The cwd_user table is used to store a user in the system. Let's check the structure of this table.
One of the main responsibilities of JIRA administrators is user management. Let's say you want to find the list of inactive users, along with their directory information. In big JIRA instances, it may be possible that there are users in JIRA's internal directory, as well as users from corporate LDAP.
The following query will return the list of inactive users in JIRA:
The preceding query relies on another table, called cwd_directory. This directory stores the user directory information, whereas whether the user is active or not is stored in the cwd_user table under the active table...
Database – Issue fields
This page shows how to examine each of a Jira issue's fields via SQL.
Simple fields
Most fields in Jira are kept in the jiraissue table:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 mysql> desc jiraissue; +----------------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------------+---------------+------+-----+---------+-------+ | ID | decimal(18,0) | NO | PRI | NULL | | | pkey | varchar(255) | YES | | NULL | | | issuenum | decimal(18,0) | YES | MUL | NULL | | | PROJECT | decimal(18,0) | YES | MUL | NULL | | | REPORTER | varchar(255) | YES | MUL | NULL | | | ASSIGNEE | varchar(255) | YES | MUL | NULL | | | CREATOR | varchar(255) | YES | | 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 | MUL | NULL | | | UPDATED | datetime | YES | MUL | NULL | | | DUEDATE | datetime | YES | MUL | NULL | | | RESOLUTIONDATE | datetime | YES | MUL | NULL | | | VOTES | decimal(18,0) | YES | MUL | NULL | | | WATCHES | decimal(18,0) | YES | MUL | 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 | MUL | 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:
1 2 3 4 5 6 mysql> select id, issuenum, project, reporter, assignee, issuetype, summary from jiraissue where issuenum=3166 and project = (select id from project where pkey='JRA'); +-------+----------+---------+-----------+----------+-----------+---------------------------------+ | id | issuenum | project | reporter | assignee | issuetype | summary | +-------+----------+---------+-----------+----------+-----------+---------------------------------+ | 16550 | 3166 | 10240 | mvleeuwen | NULL | 2 | Database consistency check tool | +-------+----------+---------+-----------+----------+-----------+---------------------------------+User details
For example, we want to find out the email address and other details about our reporter mvleeuwen.
1 2 3 select user_name, directory_id, display_name, email_address from cwd_user where user_name = 'mvleeuwen';Normally this should return a single row, however, Jira allows you to set up multiple user directories and it is possible that two or more directories contain the same username.
For more information, go to User and Group Tables page.
Components and versions
Because each issue can have multiple components or versions, there is a join table between jiraissue and version/component tables called nodeassociation:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 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 the following:
1 2 3 4 5 6 7 8 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 issuenum=5351 and project = (select id from project where pkey='JRA')); +-------+---------+-------+-------------+----------+----------+----------+------+-------------------------+ | ID | PROJECT | vname | DESCRIPTION | SEQUENCE | RELEASED | ARCHIVED | URL | STARTDATE | RELEASEDATE | +-------+---------+-------+-------------+----------+----------+----------+------+-------------------------+ | 11614 | 10240 | 3.6 | NULL | 131 | NULL | NULL | NULL | NULL | NULL | +-------+---------+-------+-------------+----------+----------+----------+------+-------------------------+Similarly with affects versions:
1 2 3 4 5 6 7 8 9 10 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 issuenum=5351 and project = (select id from project where pkey='JRA'))); +-------+---------+---------------------+-------------+----------+----------+----------+------+---------------------+ | 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 | +-------+---------+---------------------+-------------+----------+----------+----------+------+---------------------+Similarly with components:
1 2 3 4 5 6 7 8 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 issuenum=5351 and project = (select id from project where pkey='JRA'))); +-------+---------+---------------+-------------+------+------+--------------+ | 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.
1 2 3 4 5 6 7 8 9 10 11 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:
1 2 3 4 5 6 7 8 mysql> select * from issuelink where SOURCE=(select id from jiraissue where issuenum=1 and project=(select id from project where pkey='TP')) and DESTINATION=(select id from jiraissue where issuenum=2 and project=(select id from project where pkey='TP')); +-------+----------+--------+-------------+----------+ | 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:
1 2 3 4 5 6 7 8 9 10 11 12 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 "sub-task" link type is hidden in the user interface (indicated by the pstyle value below), but visible in the database:
1 2 3 4 5 6 7 8 9 10 11 12 mysql> select * from issuelinktype; +-------+-------------------+---------------------+----------------------+--------------------+ | ID | LINKNAME | INWARD | OUTWARD | pstyle | +-------+-------------------+---------------------+----------------------+--------------------+ | 10000 | Blocks | is blocked by | blocks | NULL | | 10001 | Cloners | is cloned by | clones | NULL | | 10002 | Duplicate | is duplicated by | duplicates | NULL | | 10003 | Relates | relates to | relates to | NULL | | 10100 | jira_subtask_link | jira_subtask_inward | jira_subtask_outward | jira_subtask | | 10200 | Epic-Story Link | has Epic | is Epic of | jira_gh_epic_story | +-------+-------------------+---------------------+----------------------+--------------------+ 6 rows in set (0.00 sec)This means that it is possible to convert an issue to a sub-task, or vice versa, by tweaking issuelink records.
Custom fields have their own set of tables. For details, see Database - Custom fields.