How to retrieve retrieve all comments and their respective IDs, authors, statuses, messages, comment replies, filename, and line range, directly from database

Still need help?

The Atlassian Community is here for you.

Ask the community


For reporting purposes, Crucible administrators might want to retrieve all comments and their respective IDs, authors, statuses, messages, comment replies, filename, and line range, directly from database.


NOTE 1: This SQL query below was tested with Fisheye / Crucible 3.7.x. It may require some adjustments in other versions.

NOTE 2: This SQL query below is compatible with PostgreSQL. It may require some adjustments in order to run with other database types.

Run the following query:

        cru_revpermaid.cru_proj_key || '-' || cru_revpermaid.cru_number as "Review Key",
        cru_comment.cru_comment_id "Comment ID",
        cru_user_comment_author.cru_user_name as "Comment Author", 
                when cru_comment_read_status.cru_read is true 
                then 'Read'
                else 'Unread'
        end) as "Comment Status",
        left(cru_comment.cru_message, 50) as "Comment Message",
        coalesce (
                        when cru_comment.cru_reply_to_comment_id is not null 
                        then '<REPLY TO: ' || cru_comment.cru_reply_to_comment_id || '>' 
                        else '<GLOBAL COMMENT>' end
                ) as "File Path",
        cru_inline_comment_to_frx_rev.cru_line_range as "Line Range"
from cru_comment
        join cru_comment_read_status on cru_comment_read_status.cru_comment = cru_comment.cru_comment_id
        join cru_user cru_user_comment_author on cru_user_comment_author.cru_user_id = cru_comment.cru_user_id
        join cru_review on cru_review.cru_review_id = cru_comment.cru_review_id
        join cru_revpermaid on cru_revpermaid.cru_review_id = cru_review.cru_review_id
        left join cru_inline_comment on cru_inline_comment.cru_comment_id = cru_comment.cru_comment_id -- joins only if inline comment
        left join cru_inline_comment_to_frx_rev on cru_inline_comment_to_frx_rev.cru_inline_comment_id = cru_inline_comment.cru_comment_id
        left join cru_frx_revision cru_frx_revision_inline on cru_frx_revision_inline.cru_frx_rev_id = cru_inline_comment_to_frx_rev.cru_frx_rev_id
        left join cru_revision cru_revision_inline on cru_revision_inline.cru_revision_id = cru_frx_revision_inline.cru_revision
        left join cru_stored_path cru_stored_path_inline on cru_stored_path_inline.cru_path_id = cru_revision_inline.cru_path
        left join cru_frx_comment on cru_frx_comment.cru_comment_id = cru_comment.cru_comment_id -- joins only if frx comment
        left join cru_frx_revision cru_frx_revision_frx on cru_frx_revision_frx.cru_frx_id = cru_frx_comment.cru_frx_id
        left join cru_revision cru_revision_frx on cru_revision_frx.cru_revision_id = cru_frx_revision_frx.cru_revision
        left join cru_stored_path cru_stored_path_frx on cru_stored_path_frx.cru_path_id = cru_revision_frx.cru_path
where not cru_comment.cru_deleted
        and not cru_comment.cru_draft
        --and cru_revpermaid.cru_proj_key LIKE 'CR-FE' -- use this to restrict by project key
        --and cru_revpermaid.cru_number = 10380 -- use this to restrict by review number

Sample output:

Review Key  Comment ID  Comment Author  Comment Status  Comment Message                    File Path                               Line Range  
----------  ----------  --------------  --------------  ---------------------------------  --------------------------------------  ----------  
CR-8        1           fkraemer        Read            qweqwe                             trunk/bamboo-jira-integration-test.txt  2           
CR-9        2           fkraemer        Read            General Comments                   <GLOBAL COMMENT>                        (null)      
CR-9        3           fkraemer        Read            Comment in File                    trunk/Testing.txt                       (null)      
CR-9        3           fkraemer        Read            Comment in File                    trunk/Testing.txt                       (null)      
CR-9        4           fkraemer        Read            Testing this                       trunk/SmartCommitTest.txt               1           
CR-9        5           fkraemer        Read            Testing again.                     trunk/SmartCommitTest.txt               1           
CR-4        6           fkraemer        Read            Comment in CR-4                    trunk/Testing.txt                       1           
CR-9        7           fkraemer        Read            Reply to comment "Testing again."  <REPLY TO: 5>                           (null)      
CR-9        8           fkraemer        Read            asdasd JRA-1                       <REPLY TO: 4>                           (null)      
Last modified on Jul 31, 2018

Was this helpful?

Provide feedback about this article
Powered by Confluence and Scroll Viewport.