Jira Database schema
2022/2/12 23:48:11
本文主要是介绍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:
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:
Copy1 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:
Copy1 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:
Copy1 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:
Copy1 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.
Copy1 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:
Copy1 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.
这篇关于Jira Database schema的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-05-15PingCAP 黄东旭参与 CCF 秀湖会议,共探开源教育未来
- 2024-05-13PingCAP 戴涛:构建面向未来的金融核心系统
- 2024-05-09flutter3.x_macos桌面os实战
- 2024-05-09Rust中的并发性:Sync 和 Send Traits
- 2024-05-08使用Ollama和OpenWebUI在CPU上玩转Meta Llama3-8B
- 2024-05-08完工标准(DoD)与验收条件(AC)究竟有什么不同?
- 2024-05-084万 star 的 NocoDB 在 sealos 上一键起,轻松把数据库编程智能表格
- 2024-05-08Mac 版Stable Diffusion WebUI的安装
- 2024-05-08解锁CodeGeeX智能问答中3项独有的隐藏技能
- 2024-05-08RAG算法优化+新增代码仓库支持,CodeGeeX的@repo功能效果提升