- Created by 红旗公, last modified by 张扬 on 2024-03-25
File | Modified | |
---|---|---|
PDF File jira70_schema.pdf | 2019-12-17 by 红旗公 | |
Labels
|
||
PDF File Jira7.9.2-db-schema.pdf | 2021-05-10 by 红旗公 | |
Labels
|
||
PDF File jira_8.20_database_schema.pdf | 2022-07-25 by 红旗公 | |
Labels
|
||
Microsoft Word 97 Document jira数据库查询sql示例参考.doc | 2024-03-22 by 马腾飞 | |
Labels
|
示例
示例仅供参考,请勿直接在生产环境运行,否则可能导致生产数据库负载异常!!!
查询项目
GEARS为需要导出的项目的KEY值
SELECT ID,PKEY FROM project WHERE PKEY = 'GEARS'
查询项目下的问题
Expand source
SELECT a.id, b.pkey, issuenum, TIMEORIGINALESTIMATE, TIMEESTIMATE, TIMESPENT, created, updated, creator FROM jiraissue a, project b WHERE b.PKEY = 'GEARS' AND a.project = b.id;
查询问题的字段数据
Expand source
SELECT j.REPORTER, j.ASSIGNEE, j.CREATOR, j.SUMMARY, j.DESCRIPTION, j.CREATED, j.UPDATED, j.DUEDATE, j.RESOLUTIONDATE, j.COMPONENT, cf.cfname, cf.CUSTOMFIELDTYPEKEY, cfv.CUSTOMFIELD, cfv.STRINGVALUE, cfv.TEXTVALUE, cfv.NUMBERVALUE, cfv.DATEVALUE, cfv.VALUETYPE , cfo.customvalue FROM jiraissue j LEFT JOIN customfieldvalue cfv ON j.ID = cfv.ISSUE LEFT JOIN customfield cf ON cfv.CUSTOMFIELD = cf.ID LEFT JOIN customfieldoption cfo on cfv.STRINGVALUE=cfo.ID WHERE j.issuenum = 8 -- 比如issuekey=DEMO-8,这里的issuenum=8 AND j.PROJECT = 10001;
查询评论信息
Expand source
SELECT * FROM jiraaction aa WHERE aa.issueid IN ( SELECT a.id FROM jiraissue a, project b WHERE b.PKEY = 'GEARS' AND a.project = b.id);
查询附件
Expand source
SELECT * FROM fileattachment aa WHERE aa.issueid IN ( SELECT a.id FROM jiraissue a, project b WHERE b.PKEY = 'GEARS' AND a.project = b.id)
查询工时日志
Expand source
SELECT * FROM worklog aa WHERE aa.issueid IN ( SELECT a.id FROM jiraissue a, project b WHERE b.PKEY = 'GEARS' AND a.project = b.id)
查询变更历史
变更历史
Expand source
SELECT * FROM changegroup aa WHERE aa.issueid IN ( SELECT a.id FROM jiraissue a, project b WHERE b.PKEY = 'GEARS' AND a.project = b.id )
变更详情
Expand source
SELECT * FROM changeitem bb WHERE bb.groupid IN ( SELECT id FROM changegroup aa WHERE aa.issueid IN ( SELECT a.id FROM jiraissue a, project b WHERE b.PKEY = 'GEARS' AND a.project = b.id ) )
链接关系
基础数据
Expand source
SELECT * FROM issuelinktype
业务数据
Expand source
SELECT * FROM issuelink aa WHERE source IN( SELECT a.id FROM jiraissue a,project b WHERE b.PKEY = 'GEARS' AND a.project = b.id ) UNION SELECT * FROM issuelink aa WHERE destination IN( SELECT a.id FROM jiraissue a,project b WHERE b.PKEY = 'GEARS' AND a.project = b.id )
关注和投票
Expand source
SELECT * FROM userassociation aa WHERE aa.`SINK_NODE_ENTITY` = 'Issue' AND aa.`ASSOCIATION_TYPE` = 'WatchIssue' AND aa.sink_node_id IN ( SELECT a.id FROM jiraissue a, project b WHERE b.PKEY = 'GEARS' AND a.project = b.id )
人员关系对照表
Expand source
SELECT bb.`lower_user_name`,bb.`user_key`,aa.`email_address`,aa.`display_name` FROM cwd_user aa,app_user bb WHERE aa.`lower_user_name` = bb.`lower_user_name`
模块(版本/问题类型页面方案/通知方案/权限方案/工作流方案/安全级别方案)
- 版本(SINK_NODE_ENTITY = 'Version')
- 问题类型页面方案(SINK_NODE_ENTITY=‘IssueTypeScreenScheme’)
- 通知方案(SINK_NODE_ENTITY =‘NotificationScheme’)
- 权限方案(SINK_NODE_ENTITY =‘PermissionScheme’)
- 工作流方案(SINK_NODE_ENTITY =‘WorkflowScheme’)
- 安全级别方案(SINK_NODE_ENTITY =‘IssueSecurityScheme’)
Expand source
SELECT na.SOURCE_NODE_ID as "问题id", na.SINK_NODE_ID as "模块id", c.cname as "模块名称" FROM nodeassociation na LEFT JOIN component c on na.SINK_NODE_ID=c.ID WHERE SOURCE_NODE_ID = 10939 AND SINK_NODE_ENTITY = 'Component';
- No labels