Purpose

For reporting purposes, Crucible administrators might want to retrieve all reviews and their respective participants from a Crucible project directly in database.

Solution

• 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