1. 查询Jira问题的固定或级联字段
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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. 查一个问题的修复版本
1
2
3
4
5
6
7
8
9
10
11
12
13
14
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. 查某个时间段更新过的所有问题
1
2
3
4
5
6
7
8
9
10
11
12
13
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. 查找一个项目的某个时间的所有问题的状态
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
45
46
47
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. 查某个项目所有状态的数量
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
45
46
47
48
49
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. 查询有多少问题被改变了状态
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
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. 获得一个问题的所有组件
1
2
3
4
5
6
7
8
9
10
11
12
SELECT
jiraissue.pkey,
component.cname
FROM
nodeassociation,
component,
jiraissue
WHERE
component.ID = nodeassociation.SINK_NODE_ID
AND jiraissue.id = nodeassociation.SOURCE_NODE_ID
AND nodeassociation.ASSOCIATION_TYPE = 'IssueComponent'
AND pkey = '<issue_key>';
8. 查找已经关闭的问题
1
2
3
4
5
6
7
8
9
10
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. 简单的连接
1
2
3
4
5
6
7
8
9
10
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. 返回所有项目问题
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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.id
LEFT OUTER JOIN priority pri ON ji.priority = pri.id
LEFT OUTER JOIN issuestatus ist ON ji.issuestatus = ist.id
WHERE
p.pname = '<project_name>'
ORDER BY
ji.pkey;
11. 查询项目的所有版本
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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;
12. 返回项目的所有用户
1
2
3
4
5
6
7
8
9
10
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>';
13. 返回项目工作流
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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_id
LEFT OUTER JOIN workflowschemeentity wse ON wse.scheme = ws.id
LEFT OUTER JOIN jiraworkflows jw ON jw.workflowname = wse.workflow
WHERE
p.pname = '<project_name>';
14. 查询问题各个字段的显示模式
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
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;
15. 查看一个问题的fix—for versions
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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'
)
);
16. 查看一个问提的影响版本
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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'
)
);
17. 查看一个问题的所属模块
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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'
)
);
18. 查看两个项目之间的超链接
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
select
*
from
issuelink
where
SOURCE =(
select
id
from
jiraissue
where
pkey = 'TP-1'
)
and DESTINATION =(
select
id
from
jiraissue
where
pkey = 'TP-2'
);
19. 查看两个项目间的链接类型
1
2
3
4
5
6
7
8
9
10
11
12
13
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;
20. 查看某个项目下的所有自定义字段
1
2
3
4
5
6
7
8
9
10
11
12
13
select
*
from
customfieldvalue
where
issue =(
select
id
from
jiraissue
where
pkey = 'JRA-5448'
);
21. 查看某个自定义字段的详细信息
1
select * from customfield where id = 10190;
22. 查看自定义字段在某个项目的值
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
select
stringvalue
from
customfieldvalue
where
customfield =(
select
id
from
customfield
where
cfname = 'Urgency'
)
and issue =(
select
id
from
jiraissue
where
pkey = 'FOR-845'
);
23. 查看有多个值的自定义字段值
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
select
stringvalue
from
customfieldvalue
where
customfield =(
select
id
from
customfield
where
cfname = 'Urgency'
)
and issue =(
select
id
from
jiraissue
where
pkey = 'FOR-845'
);
24. 查看自定义字段的可选值
1
select * from customfieldoption where customfieldconfig = 10031;
25. 查看自定义字段的默认值
1
select * from genericconfiguration where ID = 10031;
26. 查看改动记录
1
changegroup 和 changeItem
27. 查看表的最大使用id
1
2
-- 每次申请100，对应的名字配置在entitymodel.xml
select * from SEQUENCE_VALUE_ITEM;
28. 工作日志表
1
worklog
29. 用户和用户组表
1
cwd_user, cwd_group, cwd_membership
30. 用户权限表
1
cwd_directory_operation
31. 用户详细和自定义信息
1
cwd_directory_attribute
32. 关注和投票相关表
1
userassociation
33. 状态和工作流，查看问题的状态
1
select issuestatus from jiraissue where pkey = 'TP-1';
34. 状态定义表issuestatus
1
2
3
select pname from issuestatus, jiraissue
where issuestatus.id = jiraissue.issuestatus
and pkey = 'TP-1';
35. 工作流名字和id对应表
1
OS_WFENTRY
36. 查看所有问题的工作流跳转
1
2
3
4
5
6
7
8
9
10
11
12
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;
37. 查询某个issue影响到的项目版本
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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'
)
);
38. 查询某个issue解决的项目版本
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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'
)
);
39. 查询某个issue所在的项目模块
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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'
)
);
40. 查询issue状态转换的详情
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
select
cu.display_name,
cu.user_name,
cg.created,
dbms_lob.substr(ci.oldstring, 4000),
dbms_lob.substr(ci.newstring, 4000),
ji.issuetype,
p.pkey || '-' || ji.issuenum,
ji.summary,
p.pname
from
changeitem ci
left join changegroup cg on ci.groupid = cg.id
left join jiraissue ji on ji.id = cg.issueid
left join project p on p.id = ji.project
left join app_user au on au.user_key = cg.author
left join cwd_user cu on cu.lower_user_name = au.lower_user_name
where
p.pname = '<projectName>'
and ji.id = '<issueId>'
and ci.field = 'status'