JIRA数据库操作详细解读(有详细SQL)

冷不防 2022-05-24 10:20 1625阅读 0赞

转载自：https://blog.csdn.net/weixin\_38727188/article/details/79192868

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

一、jira数据库的安装

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

![79192868][] ![79192868][] ![Center][]

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

[https://confluence.atlassian.com/adminjiraserver073/supported-platforms-861253018.html][https_confluence.atlassian.com_adminjiraserver073_supported-platforms-861253018.html]

二、jira数据库相关资料

①数据库模式图

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

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

[http://confluence.gjingao.com/display/RMJC/entitymodel ][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：注意字符编码格式要统一  
![Center 1][]  
  
  
  
使用JIRA’S XML备份  
1：使用管理员账号登陆  
2：定时备份  
      设置->system->Services  
       填充name->点击Built-in services（系统将class自动填充）  
       -> Add Service->填充日期格式->update

![Center 2][]

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  
![Center 3][]

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安装文件中查找以下文件进行配置

![Center 4][]

六、Sql日志打印

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

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

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

配置文件地址：

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

![Center 5][]![Center 6][]

[79192868]: https://blog.csdn.net/weixin_38727188/article/details/79192868
[Center]: /images/20220524/b43a60ad75044e618a737c98d7d65ec3.png
[https_confluence.atlassian.com_adminjiraserver073_supported-platforms-861253018.html]: https://confluence.atlassian.com/adminjiraserver073/supported-platforms-861253018.html
[http_confluence.gjingao.com_pages_viewpage.action_pageId_6097154]: http://confluence.gjingao.com/pages/viewpage.action?pageId=6097154http://
[http_confluence.gjingao.com_display_RMJC_entitymodel]: http://confluence.gjingao.com/display/RMJC/entitymodel
[Center 1]: /images/20220524/a959fc09288d4b81967e5a06f9e1157e.png
[Center 2]: /images/20220524/5177f099cbf34c0f85c8da398ffa0248.png
[Center 3]: /images/20220524/6703d68c8b324cc89252e698bbeb254a.png
[Center 4]: /images/20220524/82f3f0940be04ae8ae2e3fbda9846a94.png
[Center 5]: https://img-blog.csdn.net/20180129112722328?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvd2VpeGluXzM4NzI3MTg4/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center
[Center 6]: /images/20220524/998b0ff65e36490ab8bff417a7b900ca.png