How to retrieve a list of reviews with more than one repository ?

Question

I have access to our SQL Server DB and trying to retrieve a list of reviews with more than one repository.

For example

Review Key - Number of repos used 
Review-332 - 1
Review-334 - 3
Review-333 - 1
Review-335 - 2

I tried this : https://confluence.atlassian.com/fishkb/how-to-retrieve-a-list-of-users-and-groups-granted-permissions-to-repositories-and-projects-from-the-database-976169896.html

but I am not looking for permissions , Just how to see which review is linked to which repos .

pmaisenovich · Answer

select nested2.cru_review_id, nested2.repos, '/cru/' + cru_revpermaid.cru_proj_key + '-' + CAST(cru_revpermaid.cru_number as varchar) from ( select nested1.cru_review_id, count(1) repos from ( select distinct cru_review.cru_review_id, cru_revision.cru_source_name from .dbo.cru_review join .dbo.cru_frx on cru_review.cru_review_id = cru_frx.cru_review_id join .dbo.cru_frx_revision on cru_frx.cru_frx_id = cru_frx_revision.cru_frx_id join .dbo.cru_revision on cru_frx_revision.cru_revision = cru_revision.cru_revision_id where cru_review.cru_review_id < 150 ) nested1 group by cru_review_id ) nested2 join .dbo.cru_revpermaid on cru_revpermaid.cru_review_id = nested2.cru_review_id where nested2.repos > 1 Thank you @Danila Sudyko ! I constructed the above query based on your recommendation, and it seems to do exactly what we are looking to do!

Danila Sudyko · Answer

Hello colleague.

FishEye + Crucible has no explicit link between revision and repositories, but I have found a hack. The SQL query below gives you all review items. In cru_source_name attribute you will find something like this -> PATCH:49 or UPLOAD:49. The number 49 means an ID in tables cru_patch and cru_upload_item.

select cru_review.cru_review_id, cru_default_source, cru_source_name
from cru_review
join cru_frx on cru_review.cru_review_id = cru_frx.cru_review_id
join cru_frx_revision on cru_frx.cru_frx_id = cru_frx_revision.cru_frx_id
join cru_revision on cru_frx_revision.cru_revision = cru_revision.cru_revision_id
order by cru_review_id
limit 100

For plugin I wrote the functions below.