How to retrieve retrieve all comments and their respective IDs, authors, statuses, messages, comment replies, filename, and line range, directly from database
Purpose
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.
Solution
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:
select
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",
(case
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 (
cru_stored_path_inline.cru_path,
cru_stored_path_frx.cru_path,
case
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 numberSample 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
Powered by Confluence and Scroll Viewport.