Reports

Instead of shipping its own report module, PeerReviewPlugin relies on Trac's reporting features by utilising custom TracReports using data from the PeerReviewPlugin. These custom reports can be accessed from the regular Trac report page but they are also shown on the report page for reviews.

Note that when opening a report you are automatically redirected to the Trac report module.

There are no reports created during installation. You may use the ones shown below.

Creating Reports

Displaying a report on the review report page

For reports to show up on the review page you must add the following wiki comment to the reports description.

{{{
#!comment
codereview=1
}}}

Example report description:

{{{
#!comment
codereview=1
}}}
* Show all files with status ''approved'' sorted by name.

Open [/peerreviewreport Codereview report page].

Provide links from report results

It is possible to directly link from report results to a referenced review or file. You need to set the realm column in the report as explained in TracReports: Automatically formatted columns.

The following realms are available:

• peerreview
• peerreviewfile

In this example in each result row Id directly links to the corresponding file page:

SELECT f.file_id AS id, f.review_id AS review_id,
       f.path AS path, f.hash AS hash,
       f.revision AS revision, f.status AS status,
       'peerreviewfile' AS _realm,
       (SELECT COUNT(*) FROM peerreviewcomment AS c WHERE f.file_id = c.file_id) AS comments 
FROM peerreviewfile f
WHERE f.status = 'approved' 
GROUP BY f.file_id
ORDER BY f.path, f.revision

Example Reports

All reviews grouped by author

Description:

{{{
#!comment
codereview=1
}}}
Back to [/peerreviewreport Codereview reports].

Query:

SELECT r.owner AS __group__, r.review_id AS id,
       r.name AS name, r.owner AS owner,
       r.created AS created, r.project AS project,
       r.status AS status,
       (CASE WHEN r.parent_id  > 0 THEN r.parent_id ELSE NULL END) AS followup_from,
       (CASE r.status WHEN 'approved' THEN 2 
        WHEN 'disapproved' THEN 1 
        WHEN 'reviewed' THEN 4 
        WHEN 'in-review' THEN 5 END) AS __color__,
       'peerreview' AS _realm
FROM peerreview as r
ORDER BY r.owner, r.review_id

Approved files

Description:

{{{
#!comment
codereview=1
}}}
* Show all ''approved'' files sorted by name.

Back to [/peerreviewreport Codereview reports].

Query:

SELECT f.file_id AS id, f.review_id AS review_id,
       f.path AS path, f.hash AS hash,
       f.revision AS revision, f.status AS status,
       'peerreviewfile' AS _realm,
       (SELECT COUNT(*) FROM peerreviewcomment AS c WHERE f.file_id = c.file_id) AS comments 
FROM peerreviewfile f
WHERE f.status = 'approved' 
GROUP BY f.file_id
ORDER BY f.path, f.revision

All files with comments

Description:

{{{
#!comment
codereview=1
}}}
* Show all files with comments sorted by name.

Back to [/peerreviewreport Codereview reports].

Query:

SELECT f.file_id AS id, f.review_id AS review_id,
       f.path AS path, f.revision AS revision,
       f.status AS status, COUNT(*) AS Comments,
       'peerreviewfile' AS _realm,
       (CASE f.status WHEN 'approved' THEN 2 WHEN 'disapproved' THEN 1 END) AS __color__ 
FROM peerreviewfile f
INNER JOIN peerreviewcomment AS c ON f.file_id = c.file_id
GROUP BY f.file_id
ORDER BY f.file_id, f.status, f.path, f.revision

Files with comments - My reviews

Description:

{{{
#!comment
codereview=1
}}}
* Show all files with comments from reviews assigned to me sorted by name.
* Omit '''closed''' reviews
* Only new files or files which are in-progress. This means no files with status
 * '''approved'''
 * '''disapproved'''
* Mark files with unread comments
  '''Note''': the status ''read/unread'' is set for each user.

Back to [/peerreviewreport Codereview reports].

Query:

SELECT f.file_id AS id, f.review_id AS 'review-Id',
       rev.name AS Reviewname, rev.owner AS reviewauthor,
       f.path AS path, f.revision AS revision,
       f.status AS filestatus, COUNT(*) AS comments,
       (SELECT COUNT(*) FROM peerreviewcomment AS c 
        WHERE f.file_id = c.file_id) -
       (SELECT COUNT(*) FROM peerreviewdata AS d 
        WHERE f.file_id = d.file_id AND owner = $USER) AS unread,
       'peerreviewfile' as _realm,

       (CASE 
        WHEN (SELECT COUNT(*) FROM peerreviewcomment AS c 
        WHERE f.file_id = c.file_id) -
        (SELECT COUNT(*) FROM peerreviewdata AS d 
        WHERE f.file_id = d.file_id AND owner = $USER) > 0 THEN 2 END) AS __color__ 

FROM peerreviewfile AS f
INNER JOIN peerreviewcomment AS c ON f.file_id = c.file_id
INNER JOIN peerreview AS rev ON f.review_id = rev.review_id
WHERE (f.status IS NULL OR f.status NOT IN ('approved', 'disapproved')) 
   AND f.review_id in 
   (SELECT a.review_id FROM peerreviewer a JOIN peerreview r 
   ON a.review_id = r.review_id
   WHERE a.reviewer = $USER 
   AND r.status IS NOT 'closed')
GROUP BY f.file_id
ORDER BY f.file_id, f.status, f.path, f.revision