How to retrieve reviews and their respective states, commits, JIRA issue keys, number of files and comments directly from database
Purpose
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.
Solution
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.
Run the following query:
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
Last modified on Jun 14, 2024
Powered by Confluence and Scroll Viewport.