How to retrieve reviews and their respective states, commits, JIRA issue keys, number of files and comments directly from database
プラットフォームについて: Data Center のみ。 - This article only applies to Atlassian apps on the Data Center プラットフォーム。
この KB は Data Center バージョンの製品用に作成されています。Data Center 固有ではない機能の Data Center KB は、製品のサーバー バージョンでも動作する可能性はありますが、テストは行われていません。 Server* 製品のサポートは 2024 年 2 月 15 日に終了しました。Server 製品を実行している場合は、 アトラシアン Server サポート終了 のお知らせにアクセスして、移行オプションを確認してください。
*Fisheye および Crucible は除く
要約
For reporting purposes, Crucible administrators might want to retrieve all reviews and their respective states, commits, JIRA issue keys, number of files being reviewed, and number of comments from a Crucible project directly from the database.
ソリューション
NOTE 1: This SQL query below was tested with Fisheye / Crucible 4.8. It may require some adjustments in other versions.
NOTE 2: The SQL query below is compatible with PostgreSQL. It may require some adjustments to run with other database types.
次のクエリを実行します。
select
cru_project.cru_name as "Project Name",
cru_revpermaid.cru_proj_key || '-' || cru_revpermaid.cru_number as "Review Key",
cru_review.cru_state as "Review State",
string_agg(distinct(cru_revision.cru_revision), ',') as "Commits",
cru_review_linked_issues.cru_issue_key as "Review JIRA Key",
(select count(*)
from cru_frx where cru_frx.cru_review_id = cru_review.cru_review_id) as "Number of Files",
(select count (*)
from cru_comment
where cru_comment.cru_review_id = cru_review.cru_review_id
and not cru_comment.cru_draft
and not cru_comment.cru_deleted) as "Number of Comments"
from cru_review
join cru_revpermaid on cru_revpermaid.cru_review_id = cru_review.cru_review_id
join cru_project on cru_project.cru_project_id = cru_review.cru_project
left join cru_frx on cru_frx.cru_review_id = cru_review.cru_review_id
left join cru_frx_revision on cru_frx_revision.cru_frx_id = cru_frx.cru_frx_id
left join cru_revision on cru_revision.cru_revision_id = cru_frx_revision.cru_revision
left join cru_review_linked_issues on cru_review_linked_issues.cru_review_id = cru_review.cru_review_id
where cru_review.cru_review_type = 1
and (cru_frx_revision.cru_revision_order != 0
or cru_revision.cru_added
or cru_frx_revision.cru_frx_rev_id is null) -- review without any revisions
--and cru_revpermaid.cru_proj_key = 'CR' -- use this to restrict by project key
--and cru_revpermaid.cru_number = 9 -- use this to restrict by review number
group by cru_project.cru_name,
cru_revpermaid.cru_proj_key,
cru_revpermaid.cru_number,
cru_review.cru_state,
cru_review_linked_issues.cru_issue_key,
cru_review.cru_review_idSample output:
Project Name Review Key Review State Commits Review JIRA Key Number of Files Number of Comments
--------------- ---------- ------------ ---------------------------------------- --------------- --------------- ------------------
Default Project CR-1 Draft (null) (null) 0 0
Default Project CR-2 Draft 73 (null) 1 0
Default Project CR-3 Draft (null) (null) 0 0
Default Project CR-4 Draft 72,73 ECS-5378 1 1
Default Project CR-5 Draft 73 (null) 1 0
Default Project CR-6 Draft 1:0:T (null) 1 0
Default Project CR-7 Draft 2:0:T (null) 1 0
Default Project CR-8 Draft 59,60 JRA-1 1 1
Default Project CR-9 Draft 101,80 JRA-1 2 6
Default Project CR-10 Closed 7b5ce3ee4fbd740b6fdc3e53791db2615ab5c690 (null) 1 0 この内容はお役に立ちましたか?