Jira Database schema

 

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.

 

 

 

https://subscription.packtpub.com/book/application-development/9781786466860/13/ch13lvl1sec83/the-jiraissue-table

User table

---

The cwd_user table is used to store a user in the system. Let's check the structure of this table.

The table structure

Run the following query:

desc cwd_user;
Copy

The output of the query is as follows:

 

 

Finding the list of inactive JIRA users

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:

SELECT u.user_name,u.first_name,u.last_name,u.email_address,d.directory_name 
from cwd_user u join cwd_directory d on u.directory_id = d.id where u.active 
= 0;
Copy

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

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

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:

Copy

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.

Copy

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:

Copy

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:

Copy

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:

Copy

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:

Copy

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.

Copy

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:

Copy

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:

Copy

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:

Copy

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.