I have access to our SQL Server DB and trying to retrieve a list of reviews with more than one repository.
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 .
select nested2.cru_review_id, nested2.repos, '<fisheye_url>/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
join <FishEyeDB>.dbo.cru_frx on cru_review.cru_review_id = cru_frx.cru_review_id
join <FishEyeDB>.dbo.cru_frx_revision on cru_frx.cru_frx_id = cru_frx_revision.cru_frx_id
join <FishEyeDB>.dbo.cru_revision on cru_frx_revision.cru_revision = cru_revision.cru_revision_id
where cru_review.cru_review_id < 150
group by cru_review_id
join <FishEyeDB>.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!
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
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
For plugin I wrote the functions below.