How to retrieve all reviews and respective participants from a Crucible project directly in database
For reporting purposes, Crucible administrators might want to retrieve all reviews and their respective participants from a Crucible project directly in database.
Run the following SQL query for PostgreSQL:
SELECT (p.cru_proj_key || '-' || rid.cru_number) as review_key, u.cru_user_name AS review_participants FROM cru_revpermaid rid, cru_project p, cru_review r, cru_review_participant rp, cru_user u WHERE rid.cru_proj_key = p.cru_proj_key AND rid.cru_review_id = r.cru_review_id AND rp.cru_review_id = r.cru_review_id AND rp.cru_user = u.cru_user_id
Sample result, in which there are two projects named PROJ-A and PROJ-B:
REVIEW_KEY REVIEW_PARTICIPANTS PROJ-A-1 user1 PROJ-A-1 user2 PROJ-B-1 user1 PROJ-B-2 user3