JIRA数据库操作详细解读

在先前的jira二次开发过程中，整理了一部分jira数据库的基础知识，在这里分享出来给大家作为参考

一、jira数据库的安装

安装jira时配置指定数据库，jira支持多种数据库

注意：具体jira支持数据库可以参考官方地址

https://confluence.atlassian.com/adminjiraserver073/supported-platforms-861253018.html

 

二、jira数据库相关资料

①数据库模式图

http://confluence.gjingao.com/pages/viewpage.action?pageId=6097154  PDF格式，请点击查看

② 数据库表结构详细信息，包括表名，字段名，字段类型，主键，索引及对应实体等。jira安装目录下的\atlassian-jira\WEB-INF\classes\entitydefs

http://confluence.gjingao.com/display/RMJC/entitymodel    参考资料

③jira与数据库通信模块

JIRA使用OfBiz套件的实体引擎模块与数据库通信，OFBiz是一个非常著名的电子商务平台，是一个非常著名的开源项目，提供了创建基于最新J2EE/XML规范和技术标准，构建大中型企业级、跨平台、跨数据库、跨应用服务器的多层、分布式电子商务类WEB应用系统的框架。 OFBiz最主要的特点是OFBiz提供了一整套的开发基于Java的web应用程序的组件和工具。包括实体引擎, 服务引擎, 消息引擎, 工作流引擎, 规则引擎等。如果你使用jira’s api，你会发现会有很多代码来处理GenericValue objects，GenericValue是一个OFBiz实体引擎对象，一个GenericValue代表数据库的一条记录
例如你想从GenericValue获得一个字段信息，你可能会使用如下代码：
GenericValue project = ...
String name = project.getString("name");
Long id = project.getLong("id");

④数据库访问

    1：不建议直接操作数据库
    2：使用JIRA’S REST APIS，JIRA RPC SERVICES 或者Jelly Tags
    3：操作数据库前先备份

三、数据备份

为何要备份？
      1：jira做版本升级
      2：将jira实例分散到多台服务器上
      3：jira服务迁移
      4：测试环境搭建，恢复


      如何备份？
      官方建提供如下两种方式备份
       1：使用本地数据库备份工具
       2：使用JIRA’S XML备份
       3：官方强烈建议使用本地数据库工具备份
使用本地数据库备份工具备份注意事项
1：在单个时间点进行备份，确保数据库的完整性
2：备份工具相比JIRA’S XML备份更快和使用更少的资源
3：与现有的备份策略集成
4：可以允许增量备份而不是全部备份，尽量的节省我们的磁盘空间
5：注意字符编码格式要统一




使用JIRA’S XML备份
1：使用管理员账号登陆
2：定时备份
      设置->system->Services
       填充name->点击Built-in services（系统将class自动填充）
       -> Add Service->填充日期格式->update

3:直接创建备份
System->import and export->Backup system


4：备份后数据存储目录
C:\Program Files\Atlassian\Application Data\JIRA\export
一般备份出来的一个zip压缩文件，文件里面会有两个xml文件，分别是entities.xml和activeobjects.xml


5：备份jira其他资源数据，如附件，可替换资源等
    备份方式
     1：手动copy备份
     2：写成批处理 备份
     3：使用liunx的job或windows的定时任务定时备份
     4：备份目录， C:\Program Files\Atlassian\Application Data\JIRA\data

四、常用查询

常用数据库查询
1，查询jira问题的固定或级联字段
SELECT jiraissue.*
FROM jiraissue,
OS_HISTORYSTEP,
customfieldvalue,
customfieldoption
WHERE OS_HISTORYSTEP.ENTRY_ID = jiraissue.id
AND OS_HISTORYSTEP.ACTION_ID = <action_id>
AND OS_HISTORYSTEP.CALLER = <user_name>
AND customfieldvalue.issue = jiraissue.id
AND customfieldvalue.PARENTKEY = <parent_key>
AND customfieldvalue.stringvalue = customfieldoption.id
AND customfieldoption.customvalue like '<cf_value>';
Where
<user_name> - the username of the desired user
<action_id> - the id of your transition into the fixed state (may need multiple)
<parent_key> - the id of the Level 1 option in customfieldoption - E.g. 10040
<cf_value> - the Level 2 value of the cascading field. E.g 'realease%'

2：查一个问题的修复版本
SELECT projectversion.id, vname
FROM projectversion,
nodeassociation,
jiraissue
WHERE ASSOCIATION_TYPE = 'IssueFixVersion'
AND SINK_NODE_ID = projectversion.id
AND SOURCE_NODE_ID = jiraissue.id
AND pkey = '<issue_key>';
Where
<issue_key> - the key of an issue. E.g. TEST-10

3：查某个时间段更新过的所有问题
SELECT DISTINCT(j.id) FROM jiraissue j, changegroup g
WHERE j.id = g.issueid
AND g.author = '<user name>'
AND g.created > '<date>';
Where
<date> - the earliest desired date (The date should be in the format 'yyyy-mm-dd hh:mm:s'. E.g '2005-10-06 14:40:28')
<username> - the name of the desired user

4：查找一个项目的某个时间的所有问题的状态
SELECT JI.pkey, STEP.STEP_ID
FROM (SELECT STEP_ID, ENTRY_ID
      FROM OS_CURRENTSTEP
      WHERE OS_CURRENTSTEP.START_DATE < '<your date>'
UNION SELECT STEP_ID, ENTRY_ID
      FROM OS_HISTORYSTEP
      WHERE OS_HISTORYSTEP.START_DATE < '<your date>'
      AND OS_HISTORYSTEP.FINISH_DATE > '<your date>' ) As STEP,
(SELECT changeitem.OLDVALUE AS VAL, changegroup.ISSUEID AS ISSID
      FROM changegroup, changeitem
      WHERE changeitem.FIELD = 'Workflow'
      AND changeitem.GROUPID = changegroup.ID
UNION SELECT jiraissue.WORKFLOW_ID AS VAL, jiraissue.id as ISSID
      FROM jiraissue) As VALID,
jiraissue as JI
WHERE STEP.ENTRY_ID = VALID.VAL
AND VALID.ISSID = JI.id
AND JI.project = <proj_id>;
Where
<your date> is the date you want to check
<proj_id> is the project you want to check

5：查某个项目所有状态的数量
SELECT count(*), STEP.STEP_ID
FROM (SELECT STEP_ID, ENTRY_ID
      FROM OS_CURRENTSTEP
      WHERE OS_CURRENTSTEP.START_DATE < '<your date>'
UNION SELECT STEP_ID, ENTRY_ID
      FROM OS_HISTORYSTEP
      WHERE OS_HISTORYSTEP.START_DATE < '<your date>'
      AND OS_HISTORYSTEP.FINISH_DATE > '<your date>' ) As STEP,
(SELECT changeitem.OLDVALUE AS VAL, changegroup.ISSUEID AS ISSID
      FROM changegroup, changeitem
      WHERE changeitem.FIELD = 'Workflow'
      AND changeitem.GROUPID = changegroup.ID
UNION SELECT jiraissue.WORKFLOW_ID AS VAL, jiraissue.id as ISSID
      FROM jiraissue) As VALID,
jiraissue as JI
WHERE STEP.ENTRY_ID = VALID.VAL
AND VALID.ISSID = JI.id
AND JI.project = <proj_id>
Group By STEP.STEP_ID;
Where
<your date> is the date you want to check
<proj_id> is the project you want to check

6：查询有多少问题被改变了状态
SELECT NEWSTRING AS Status, count(*) AS Number
FROM changeitem, changegroup, jiraissue
WHERE changeitem.field = 'Status'
    AND changeitem.groupid = changegroup.id
    AND changegroup.issueid = jiraissue.id
    AND jiraissue.project = <project_id>
    AND changegroup.CREATED >= '<date_from>'
    AND changegroup.CREATED < '<date_to>'
Group By NEWSTRING
UNION
SELECT 'Created' As Status, count(*) AS Number
FROM jiraissue
WHERE jiraissue.CREATED >= '<date_from>'
    AND jiraissue.CREATED < '<date_to>'
    AND jiraissue.project = <project_id>;
Where
<date_from> is the date you want to check from
<date_to> is the date you want to check to
<project_id> is the project you want to check

7：获得一个问题的所有组件 
SELECT jiraissue.pkey, component.cname FROM nodeassociation, component, jiraissueWHERE component.ID = nodeassociation.SINK_NODE_IDAND jiraissue.id = nodeassociation.SOURCE_NODE_IDAND nodeassociation.ASSOCIATION_TYPE = 'IssueComponent'AND pkey = '<issue_key>'; 


8：查找已经关闭的问题
SELECT pKey, OS_CURRENTSTEP.STATUS, OS_CURRENTSTEP.START_DATE FROM jiraissue, OS_CURRENTSTEP WHERE  issuestatus = 6 AND OS_CURRENTSTEP.ENTRY_ID = jiraissue.WORKFLOW_ID; 

9：简单的连接
SELECT * FROM jiraissue LEFT JOIN jiraaction ON jiraissue.id = jiraaction.issueid;


SELECT * FROM jiraissue LEFT JOIN changegroup ON jiraissue.id = changegroup.issueid;


SELECT * FROM changegroup LEFT JOIN changeitem ON changegroup.id = changeitem.groupid;


SELECT * FROM jiraissue LEFT JOIN OS_CURRENTSTEP ON jiraissue.WORKFLOW_ID = OS_CURRENTSTEP.ENTRY_ID;


SELECT * FROM jiraissue LEFT JOIN OS_HISTORYSTEP ON jiraissue.WORKFLOW_ID = OS_HISTORYSTEP.ENTRY_ID; 

10：返回所有项目问题
SELECT p.id AS project_id, p.pname AS project_name, p.lead AS project_lead,ji.pkey AS issue_id, ji.reporter AS issue_reporter, pri.pname AS issue_priority, ist.pname AS issue_status, ji.summary AS issue_summary
FROM project p LEFT OUTER JOIN jiraissue ji ON ji.project = p.idLEFT OUTER JOIN priority pri ON ji.priority = pri.idLEFT OUTER JOIN issuestatus ist ON ji.issuestatus = ist.id
WHERE p.pname = '<project_name>'ORDER BY ji.pkey; 


11：查询项目的所有版本
SELECT p.id AS project_id, p.pname AS project_name, p.lead AS project_lead,pv.vname AS version_name, pv.description AS version_desc, pv.sequence AS version_seq, pv.released AS version_released,pv.archived AS version_archived, pv.url AS version_url, pv.releasedate AS version_release_date
FROM project p LEFT OUTER JOIN projectversion pv ON pv.project = p.id
WHERE p.pname = '<project_name>'ORDER BY pv.sequence; 

13：返回项目的所有用户
SELECT p.id AS project_id, p.pname AS project_name, p.lead AS project_lead, prc.roletypeparameter AS project_roles
FROM project p LEFT OUTER JOIN projectroleactor prc ON prc.pid = p.id
WHERE p.pname = '<project_name>';


14：返回项目工作流
SELECT p.id AS project_id, p.pname AS project_name, p.lead AS project_lead, ws.name AS project_associated_workflow_scheme,wse.workflow AS workflow_scheme_associated_workflow, jw.descriptor AS workflow_descriptor
FROM project p LEFT OUTER JOIN nodeassociation na ON na.source_node_id = p.id AND na.sink_node_entity = 'WorkflowScheme'LEFT OUTER JOIN workflowscheme ws ON ws.id = na.sink_node_idLEFT OUTER JOIN workflowschemeentity wse ON wse.scheme = ws.idLEFT OUTER JOIN jiraworkflows jw ON jw.workflowname = wse.workflow
WHERE p.pname = '<project_name>'; 

15：查询问题各个字段的显示模式
SELECT p.id AS project_id, p.pname AS project_name, p.lead AS project_lead,
itss.name AS project_issue_type_screen_scheme, fss.name AS screen_scheme_of_the_issue_type_screen_scheme,
fs.name AS screen_name_of_the_screen_scheme, fst.name AS screen_tab_name,
fsli.fieldidentifier AS tab_field, fsli.sequence AS tab_field_seq
FROM project p LEFT OUTER JOIN nodeassociation na ON na.source_node_id = p.id AND na.sink_node_entity = 'IssueTypeScreenScheme'
LEFT OUTER JOIN issuetypescreenscheme itss ON itss.id = na.sink_node_id
LEFT OUTER JOIN issuetypescreenschemeentity itsse ON itsse.scheme = itss.id
LEFT OUTER JOIN fieldscreenscheme fss ON itsse.fieldscreenscheme = fss.id
LEFT OUTER JOIN fieldscreenschemeitem fssi ON fss.id = fssi.fieldscreenscheme
LEFT OUTER JOIN fieldscreen fs ON fssi.fieldscreen = fs.id
LEFT OUTER JOIN fieldscreentab fst ON fs.id = fst.fieldscreen
LEFT OUTER JOIN fieldscreenlayoutitem fsli ON fst.id = fsli.fieldscreentab
WHERE p.pname = 'fengdi'
ORDER BY fsli.sequence;

   16：查看一个问题的fix—for versions
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'));


17：查看一个问提的影响版本
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'));




18：查看一个问题的所属模块
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')); 

19：查看两个项目之间的超链接
select * from issuelink where SOURCE=(select id from jiraissue where pkey='TP-1') and DESTINATION=(select id from jiraissue where pkey='TP-2');


20：查看两个项目间的链接类型
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;


21：查看某个项目下的所有自定义字段
select * from customfieldvalue where issue=(select id from jiraissue where pkey='JRA-5448');


22：查看某个自定义字段的详细信息
select * from customfield where id=10190; 


23:查看自定义字段在某个项目的值
select stringvalue from customfieldvalue where customfield=(select id from customfield where cfname='Urgency') and issue=(select id from jiraissue where pkey='FOR-845');
24：查看有多个值的自定义字段值
select stringvalue from customfieldvalue where customfield=(select id from customfield where cfname='Urgency') and issue=(select id from jiraissue where pkey='FOR-845');


25：查看自定义字段的可选值
select * from customfieldoption where customfieldconfig=10031;


26：查看自定义字段的默认值 select * from genericconfiguration where ID=10031;


27：查看改动记录，changegroup和changeItem


28：查看表的最大使用id，每次申请100，对应的名字配置在entitymodel.xmlselect * from SEQUENCE_VALUE_ITEM; 


29:工作日志表worklog


30：用户和用户组表cwd_user,cwd_group,cwd_membership


31:用户权限表cwd_directory_operation


32:用户详细和自定义信息cwd_directory_attribute
33:关注和投票相关表：userassociation


34:状态和工作流，查看问题的状态
select issuestatus from jiraissue where pkey='TP-1';


35：状态定义表
issuestatusselect pname from issuestatus, jiraissue where issuestatus.id=jiraissue.issuestatus and pkey='TP-1';


36：工作流名字和id对应表OS_WFENTRY 


37：工作流定义文件
C:\Program Files\Atlassian\JIRA\atlassian-jira\WEB-INF\classes

38：查看所有问题的工作流跳转
select issuestatus.pname status, issuestatus, OS_CURRENTSTEP.STEP_ID, OS_CURRENTSTEP.STATUS from
   issuestatus, jiraissue, OS_CURRENTSTEP where
   issuestatus.id=jiraissue.issuestatus and jiraissue.workflow_id=OS_CURRENTSTEP.ENTRY_ID;

五、属性配置

属性配置表：很多公共的配置会存储在jira数据库，如：

1，查找索引路径

2，附件路径

3，BaseURL

4，管理员配置->一般配置

5，License信息

可在jira安装文件中查找以下文件进行配置

六、Sql日志打印

1，如果线上出现响应缓慢或者想调试某个功能做了那些数据库访问操作，

我们可以把sql打印开关开起来，通过分析日志来查找或分析遇到的问题。

2，打开方式，jira使用log4j，在配置文件将开关打开即可

配置文件地址：

 C:\ProgramFiles\Atlassian\JIRA\atlassian-jira\WEB-INF\classes