How to retrieve reviews and their respective states, commits, JIRA issue keys, number of files and comments directly from database


アトラシアン コミュニティをご利用ください。



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.


    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,


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                   

最終更新日 2024 年 6 月 14 日


Powered by Confluence and Scroll Viewport.