Get code review coverage by using Crucible Database queries

Mircea Marin

Hi,

as we well know, the built-in code review coverage capabilities of Crucible are somewhat limited. Our team needs to generate a code review coverage for an entire program (might be anywhere from 3 month to 1 year). We also would use this to find checkins / revisions with missing code reviews.

Currently I have created a tool using Excel and VB that does what we need but I would like to "migrate" this tool to something web-based. My idea would be to use database queries as follows:

1. Get all checkins on a certain branch starting with Date XX-XX-XXXX
2. For each checkin ensure that there is at least 1 Code Review
3. For each Code Review, check who the participants are and it's status
4. Finally, return a list with the following format:

Rev. # | Rev. Date | Changes in Rev. | Change Author | Code Review ID | CR Participants | Status / Participant

The Rev# will be the "unique" identifier in this case.

I looked in the available databases (we use PostgreSQL) but I could not find, for example, the Code Review ID (ex: CR-1234) in any of the tables. Is there anywhere a description of the tables that crucible uses and the data that each contains?

Is there any other way to generate flexible code review reports?

Thanks,

Mircea

 

Mircea Marin

Hi Alexander,
I know someone in my team (the person left in the meantime) tried your plugin maybe 1.5 - 2 years ago and for some reason they decided against it (seems it was using somehow a lot of resources and also broke some things in our setup - no idea what exactly). I could try to convince my manager to let me try it again.
Eitherway, I managed to get the coverage that we need by using a predefined filter, exporting to CSV, importing in Excel and then with the help of VBA and REST get all the information we need.
I also found out how to do it directly in the database. Here's an example of an SQL function I made to get the Code Review ID with a revision number as input (we use PostgreSQL and I created the function by using pgAdmin III):

CREATE OR REPLACE FUNCTION cr_for_rev(revision character varying)
  RETURNS integer AS
$BODY$declare cr int;
BEGIN
	SELECT min(cru_revpermaid.cru_number) INTO cr
	FROM cru_revpermaid
	JOIN cru_frx ON cru_frx.cru_review_id = cru_revpermaid.cru_review_id
	JOIN cru_frx_revision ON cru_frx_revision.cru_frx_id = cru_frx.cru_frx_id
	JOIN cru_revision ON cru_revision.cru_revision_id = cru_frx_revision.cru_revision
	WHERE cru_revision.cru_revision = revision;
	RETURN cr;
END;$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION cr_for_rev(character varying)
  OWNER TO atlassian;
COMMENT ON FUNCTION cr_for_rev(character varying) IS 'Returns a Code Review ID for a given revision';

To use the function you can do something like this:

SELECT cr_for_rev('12345')

This then returns the Code Review ID as displayed in Crucible (not the internal ID).

The other information I need is relatively easy to get as they are directly available in different databases or require 1 or 2 JOINs.

Now I would only have to create an (web) interface for it and use my new queries to get the data I need