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 · Accepted solution]

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 

[Alexander Taler]

Yes, your function looks pretty reasonable to me. Of course you'll need to adjust it to handle multiple reviews of the same revision. Even more frustratingly, don't forget that it's possible for the author to include a revision in a review but actually remove most of the changes using the edit review contents feature. As for the Flyover plugin, earlier versions did have performance problems, but with Crucible >= 3.0 the current version is fairly quick.

[TuomasL]

This query links review even if the revision was never reviewed, but just when one of the files was modified later. Not good. Did not yet find how to filter those "base" revisions in sql.

[Hans-Joachim Zimmer]

Hi Mircea,

I am currently also try to get a rough overview obaut commits without reviews and the review coverage for the commits using VB in Excel and Fisheye/crucible REST API, I have read, that you did something similar. Would it be possible to get an example of your VBA implementation as we actually feel a bit lost with the crucible REST API.

Thanks in advance

Feel free to contact me directly at hans-joachim.zimmer[at]sick.de

[Alexander Taler]

The biggest challenge is mapping from revision -> review. I used the Crucible API to solve this problem, and to be honest it was quite a bit of work. I've made it available on the marketplace as a plugin which does exactly what you're trying to achieve:

https://marketplace.atlassian.com/plugins/biz.deref.flyover.flyoverForCrucible

A new version will be available soon that includes a spreadsheet (.csv) report along the lines of what you describe.

[Mircea Marin]

Hi Alexander, I convinced my manager to let me try your plugin once again (we had actually bought it 2 years ago but never renewed since it didn't seem to work for us). I installed it in our FeCru server but I get a message that the plugin is incompatible. We're using version 3.3.1. I sent you an "Update request".

[Alexander Taler]

Are you running both Fisheye and Crucible? This is a known issue with Fisheye servers, and Atlassian have been slow at looking at the issue. You should be able to download and install the plugin anyway and it will work normally. I'll remind Atlassian of the issue.

[Alexander Taler]

Atlassian have raised the priority of this issue and a developer is looking at it at their end. You can follow along here: https://ecosystem.atlassian.net/browse/UPM-4921 To install the plugin manually visit the Marketplace listing: https://marketplace.atlassian.com/plugins/biz.deref.flyover.flyoverForCrucible From there you download the .jar file using the download link at the bottom ( https://marketplace.atlassian.com/download/plugins/biz.deref.flyover.flyoverForCrucible/version/10400 ) and generate a license using the "Try it free" button at top. Within Crucible admin you can upload a plugin from a local .jar file, and then apply the license. I hope that's all clear, Alex

[Alexander Taler]

In case you're interested, a new version of Flyover Reports has just been released including the CSV format of the report.

[bitabita]

Hi Alexander,

When would the cloud version of your plugin (Flyover Reports )  be available to download? we are moving to Jira cloud in a month and would be great to have this. currently we have built our own custom SSRS reports which was very painful as you mentioned above. would be great to install this plugin to be able to audit our code reviews easily and cleanly.

thanks!

[Alexander Taler]

Hi Bita A,

Flyover Reports is plugin for Crucible, which only has a self-hosted install model, it is not available in the cloud. Because of this, there is no need for cloud version of the plugin.

I am not sure if JIRA cloud can connect with Crucible.

Alex

[Mircea Marin]

Nevermind, found it. There is a second table, called "cru_revpermaid" which "translates" cru_review_id"s to CR-XXXX.

[Mircea Marin]

Just a note: In the table "cru_review" I see a column called "cru_review_id" but the ID from there doesn't match the ID that I have for the review in FeCru. Ex: cru_review_id = 2725; Crucible ID = CR-2689. I would like to get the CR-2689 from somewhere in the database if possible.