关闭

JIRA学习之路之数据库表介绍

70人阅读 评论(0) 收藏 举报
分类:

JIRA 数据库表介绍

Issue Fields
jiraissue表格,这个表格存储了大部分的issue信息。

mysql> desc jiraissue;
+----------------------+---------------+------+-----+---------+-------+
| Field                | Type          | Null | Key | Default | Extra |
+----------------------+---------------+------+-----+---------+-------+
| ID                   | decimal(18,0) | NO   | PRI | NULL    |       |
| pkey                 | varchar(255)  | YES  | UNI | 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    |       |
| RESOLUTIONDATE       | datetime      | YES  |     | NULL    |       |
| VOTES                | decimal(18,0) | YES  |     | NULL    |       |
| WATCHES              | 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  | MUL | NULL    |       |
| SECURITY             | decimal(18,0) | YES  |     | NULL    |       |
| FIXFOR               | decimal(18,0) | YES  |     | NULL    |       |
| COMPONENT            | decimal(18,0) | YES  |     | NULL    |       |
+----------------------+---------------+------+-----+---------+-------+
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
用户相关的一个表格

mysql> select user_name, directory_id, display_name, email_address from cwd_user where user_name = 'bright.ma';
+-----------+--------------+--------------+--------------------------+
| user_name | directory_id | display_name | email_address            |
+-----------+--------------+--------------+--------------------------+
| bright.ma |        10700 | Minghui Ma   | bright.ma@example.com |
+-----------+--------------+--------------+--------------------------+

Components and versions

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)

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)

Custom fields
Custom fields defined in the system are stored in the customfield table, and instances of custom fields are stored in customfieldvalue:

mysql> desc customfieldvalue;
+-------------+---------------+------+-----+---------+-------+
| Field       | Type          | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| ID          | decimal(18,0) | NO   | PRI |         |       |
| ISSUE       | decimal(18,0) | YES  | MUL | NULL    |       |
| CUSTOMFIELD | decimal(18,0) | YES  |     | NULL    |       |
| PARENTKEY   | varchar(255)  | YES  |     | NULL    |       |
| STRINGVALUE | varchar(255)  | YES  |     | NULL    |       |
| NUMBERVALUE | decimal(18,6) | YES  |     | NULL    |       |
| TEXTVALUE   | longtext      | YES  |     | NULL    |       |
| DATEVALUE   | datetime      | YES  |     | NULL    |       |
| VALUETYPE   | varchar(255)  | YES  |     | NULL    |       |
+-------------+---------------+------+-----+---------+-------+

We can print all custom field values for an issue with:

mysql> select * from customfieldvalue where issue=(select id from jiraissue where pkey='JRA-5448');
+-------+-------+-------------+-----------+-------------+-------------+-----------+---------------------+-----------+
| ID    | ISSUE | CUSTOMFIELD | PARENTKEY | STRINGVALUE | NUMBERVALUE | TEXTVALUE | DATEVALUE           | VALUETYPE |
+-------+-------+-------------+-----------+-------------+-------------+-----------+---------------------+-----------+
| 23276 | 22160 |       10190 | NULL      | NULL        |        NULL | NULL      | 2004-12-07 17:25:58 | NULL      |
+-------+-------+-------------+-----------+-------------+-------------+-----------+---------------------+-----------+

and we can see what type of custom field this (10190) is with:

mysql> select * from customfield where id=10190;
+-------+------------------------------------------------+--------------------------------------------------------+-----------------+-------------+--------------+-----------+---------+-----------+
| ID    | CUSTOMFIELDTYPEKEY                             | CUSTOMFIELDSEARCHERKEY                                 | cfname          | DESCRIPTION | defaultvalue | FIELDTYPE | PROJECT | ISSUETYPE |
+-------+------------------------------------------------+--------------------------------------------------------+-----------------+-------------+--------------+-----------+---------+-----------+
| 10190 | com.atlassian.jira.ext.charting:resolutiondate | com.atlassian.jira.ext.charting:resolutiondatesearcher | Resolution Date | NULL        | NULL         |      NULL |    NULL | NULL      |
+-------+------------------------------------------------+--------------------------------------------------------+-----------------+-------------+--------------+-----------+---------+-----------+

(ie. it’s a “Resolution Date”).

This query identifies a particular custom field value in a particular issue:

mysql> select stringvalue from customfieldvalue where customfield=(select id from customfield where cfname='Urgency') and issue=(select id from jiraissue where pkey='FOR-845');
+-------------+
| stringvalue |
+-------------+
| Low         | 
+-------------+
1 row in set (0.33 sec)

If the custom field has multiple values (multi-select or multi-user picker), each issue can have multiple customfieldvalue rows:

mysql> select * from customfieldvalue where customfield=(select ID from customfield where cfname='MultiUser');
+-------+-------+-------------+-----------+-------------+-------------+-----------+-----------+-----------+
| ID    | ISSUE | CUSTOMFIELD | PARENTKEY | STRINGVALUE | NUMBERVALUE | TEXTVALUE | DATEVALUE | VALUETYPE |
+-------+-------+-------------+-----------+-------------+-------------+-----------+-----------+-----------+
| 10002 | 10060 |       10000 | NULL      | bob         |        NULL | NULL      | NULL      | NULL      | 
| 10003 | 10060 |       10000 | NULL      | jeff        |        NULL | NULL      | NULL      | NULL      | 
+-------+-------+-------------+-----------+-------------+-------------+-----------+-----------+-----------+
2 rows in set (0.00 sec)

Here issue 10060 has two users, bob and jeff in its MultiUser custom field.

0
0
查看评论
发表评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场

Linux简介及Linux学习路线图

一、Linux 为何物Linux 就是一个操作系统,就像你多少已经了解的 Windows(xp,7,8)和 Max OS ,至于操作系统是什么,就不用过多解释了,如果你学习过前面的入门课程,应该会有个...
  • CoderJYF
  • CoderJYF
  • 2016-12-08 09:47
  • 13781

深度学习小白——TensorFlow(一)简介

我选择tensorFlow作为我学习的第一个神经网络框架,恰巧最近Tensorflow支持了windows,所以让我的学习变得更加便捷。 我的主要参考文章有 http://blog.csdn.net/...
  • MargretWG
  • MargretWG
  • 2017-04-13 19:42
  • 1347

MySQL学习(数据库和表的基本操作)

接下来,主要学习关于sql操作的相关知识点,包括了常用的增删改查sql语句的写法等等,由于日常工作中,有段时间会不太使用sql语句,这就导致了时间长了,对于这一块就又会再次生疏,所以,以博客的形式记录...
  • mockingbirds
  • mockingbirds
  • 2016-03-13 20:56
  • 1515

数据库性能优化学习之路

  • 2017-11-19 14:34
  • 3.52MB
  • 下载

[EntLib]微软企业库5.0 学习之路——第五步、介绍EntLib.Validation模块信息、验证器的实现层级及内置的各种验证器的使用方法——上篇

  • 2014-07-28 10:28
  • 76KB
  • 下载

【Java工程师之路】[2-1.9]数据库视图介绍

什么是视图:   视图(view):从一个或几个基本表中根据用户需要而做成一个虚表   1:视图是虚表,它在存储时只存储视图的定义,而没有存储对应的数据   2:视图只在刚刚打开的一...
  • imalex163
  • imalex163
  • 2013-10-18 15:41
  • 403

Linux专家之路linux数据库编程

  • 2010-06-03 22:46
  • 7.11MB
  • 下载

从2014 到2016,大规模内存数据库演进之路

  • 2015-12-17 13:17
  • 996KB
  • 下载

Java学习之路0810(建立与数据库的连接)

数据库的创建与连接 连接数据库,首先要创建数据库和表 创建数据库的SQL语句如下:CALZZ为数据库的名称 CREATE DATABASE CALZZ 创...
  • wujie5201
  • wujie5201
  • 2015-08-14 21:37
  • 240

java学习之路之数据库知识总结

数据库 创建数据库—————— create database [ if not exists ] db_name [ character set xxx] [ collate...
  • wmumu_
  • wmumu_
  • 2016-04-08 00:56
  • 1265
    个人资料
    • 访问:4358次
    • 积分:355
    • 等级:
    • 排名:千里之外
    • 原创:34篇
    • 转载:0篇
    • 译文:0篇
    • 评论:0条
    文章分类