How to retrieve review comments and their respective authors, files, defect classifications and rankings directly from database

お困りですか?

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

コミュニティに質問

目的

For reporting purposes, Crucible administrators might want retrieve review comments and their respective authors, files, defect classifications and rankings, from a specific date onwards, directly from database.

ソリューション

NOTE 1: This SQL query below was tested with Fisheye / Crucible 4.1.1. 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.

 次のクエリを実行します。

select distinct
        cru_revpermaid.cru_proj_key || '-' || cru_revpermaid.cru_number as "Review Key",
        TO_CHAR(TO_TIMESTAMP(cru_review.cru_create_date / 1000), 'MM-DD-YYYY') as "Review Creation Date",
        cc.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(cc.cru_message, 50) as "Comment Message",
        coalesce (
                cru_stored_path_inline.cru_path, 
                cru_stored_path_frx.cru_path, 
                case 
                        when cc.cru_reply_to_comment_id is not null 
                        then '<REPLY TO: ' || cc.cru_reply_to_comment_id || '>' 
                        else '<GLOBAL COMMENT>' end
                ) as "File Path",
        cru_inline_comment_to_frx_rev.cru_line_range as "Line Range",
        (select (
                btrim(
                        xpath(
                                '/n:metrics/n:comment/n:field[@name=''classification'']/n:select/n:option[@value='|| cru_field.cru_int_val ||']/text()', 
                                cru_metric_definition.cru_xml::xml, 
                                '{{n,http://www.cenqua.com/crucible/metrics-1}}'
                        )::text,
                '{""}'))
        from cru_metric_definition 
                join cru_field on cru_metric_definition.cru_metric_id = cru_field.cru_config_version
                join cru_comment_field on cru_field.cru_field_id = cru_comment_field.cru_field_id
                join cru_comment c on c.cru_comment_id = cru_comment_field.cru_comment_id
        where c.cru_comment_id = cc.cru_comment_id
        and cru_field.cru_name = 'classification'
        ) as "Classification",
        (select (
                btrim(
                        xpath(
                                '/n:metrics/n:comment/n:field[@name=''rank'']/n:select/n:option[@value='|| cru_field.cru_int_val ||']/text()', 
                                cru_metric_definition.cru_xml::xml, 
                                '{{n,http://www.cenqua.com/crucible/metrics-1}}'
                        )::text, 
                '{""}'))
        from cru_metric_definition 
                join cru_field on cru_metric_definition.cru_metric_id = cru_field.cru_config_version
                join cru_comment_field on cru_field.cru_field_id = cru_comment_field.cru_field_id
                join cru_comment c on c.cru_comment_id = cru_comment_field.cru_comment_id
        where c.cru_comment_id = cc.cru_comment_id
        and cru_field.cru_name = 'rank'
        ) as "Ranking"  
from cru_comment cc
        join cru_comment_field on cru_comment_field.cru_comment_id = cc.cru_comment_id
        join cru_field on cru_comment_field.cru_field_id = cru_field.cru_field_id
        join cru_metric_definition on cru_metric_definition.cru_metric_id = cru_field.cru_config_version
        join cru_comment_read_status on cru_comment_read_status.cru_comment = cc.cru_comment_id
        join cru_user cru_user_comment_author on cru_user_comment_author.cru_user_id = cc.cru_user_id
        join cru_review on cru_review.cru_review_id = cc.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 = cc.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 = cc.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 cc.cru_deleted
        and not cc.cru_draft
        and TO_TIMESTAMP(cru_review.cru_create_date / 1000) >= '10-25-2016'
        -- 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  Review Creation Date  Comment ID  Comment Author  Comment Status  Comment Message                 File Path            Line Range  Classification  Ranking  
----------  --------------------  ----------  --------------  --------------  ------------------------------  -------------------  ----------  --------------  -------  
CR-3        10-25-2016            1           admin           Read            Comment in html file            trunk/htmlTest.html  10          Ambiguous       Major    
CR-4        10-26-2016            2           admin           Read            Comment in line range 13 - 18.  trunk/htmlTest.html  13-18       Editorial       Minor    
最終更新日 2018 年 7 月 31 日

この内容はお役に立ちましたか?

はい
いいえ
この記事についてのフィードバックを送信する
Powered by Confluence and Scroll Viewport.