Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,362,301
Community Members
 
Community Events
168
Community Groups

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

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 .

2 answers

0 votes
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
from <FishEyeDB>.dbo.cru_review
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
) nested1
group by cru_review_id
) nested2
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! 

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.

image.pngimage.pngimage.png

Suggest an answer

Log in or Sign up to answer
TAGS

Atlassian Community Events