How to retrieve review comments and their respective authors, files, defect classifications and rankings directly from database
注意:
- This SQL query below was tested with the Fisheye /Crucible versions listed below. It may require some adjustments in other versions.
- 4.1.1
- 4.5.2
- 4.8.4
- This SQL query below is compatible with PostgreSQL. It may require some adjustments in order to run with other database types.
目的
For reporting purposes, Crucible administrators might want to retrieve review comments and their respective authors, files, defect classifications and rankings (when marked as defects), from a specific date onwards, directly from the underlying database.
ソリューション
Run the following query (PostgreSQL version):
PostgreSQL version
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 AS "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",
cc.cru_message 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 COMMENT ID: ' || cc.cru_reply_to_comment_id || '>'
ELSE '<GENERAL COMMENTS>' 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 AS cc
LEFT JOIN cru_comment_field ON cru_comment_field.cru_comment_id = cc.cru_comment_id
LEFT JOIN cru_field ON cru_comment_field.cru_field_id = cru_field.cru_field_id
LEFT JOIN cru_metric_definition ON cru_metric_definition.cru_metric_id = cru_field.cru_config_version
LEFT JOIN cru_comment_read_status ON cru_comment_read_status.cru_comment = cc.cru_comment_id
LEFT JOIN cru_user cru_user_comment_author ON cru_user_comment_author.cru_user_id = cc.cru_user_id
LEFT JOIN cru_review ON cru_review.cru_review_id = cc.cru_review_id
LEFT 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) >= '9-25-2020'
AND cru_revpermaid.cru_proj_key LIKE 'CR' -- use this to restrict by project key
AND cru_revpermaid.cru_number = 1 -- use this to restrict by review number
ORDER BY "Review Key"出力サンプル
Review Key Review Creation Date Comment ID Comment Author Comment Status Comment Message File Path Line Range Classification Ranking
---------- -------------------- ---------- -------------- -------------- -------------------------------- ------------------------ ---------- -------------- -------
CR-1 10-12-2020 1 user1 Read Comment in a single line file2.txt 24 Ambiguous Major
CR-1 10-12-2020 2 user2 Read Comment in line range file2.txt 27-31 Inconsistent Minor
CR-1 10-12-2020 3 user3 Read Comment in another file file.txt 2 Missing Major
CR-1 10-12-2020 4 user1 Read General comment <GENERAL COMMENTS> (null) (null) (null)
CR-1 10-12-2020 5 user1 Read Reply to comment in single line <REPLY TO COMMENT ID: 1> (null) (null) (null)
CR-1 10-12-2020 6 user1 Read Reply to comment in line range <REPLY TO COMMENT ID: 2> (null) (null) (null)
最終更新日 2021 年 7 月 29 日
Powered by Confluence and Scroll Viewport.