Skip to content

Gerrit backend

Jump to bottom Edit New page
Jesus M. Gonzalez-Barahona edited this page Jul 3, 2013 · 9 revisions

Some examples of using the Gerrit backend

Bicho includes support for Gerrit. Gerrit code review cycles are mapped to Bicho tickets. Find here some information and examples on how to run this Gerrit backend, and how to query the resulting database.

How to run Bicho to retrieve info from Gerrit

$ ssh -p 29418 xxx@review.openstack.org gerrit ls-projects > openstack-projects.gerrit
$ projects=`cat openstack-projects.gerrit`
$ for project in $projects; \
  do ./bicho -g --db-user-out=XXX --db-password-out="" --db-database-out=XXX -b gerrit \
    -u XXX@review.openstack.org --gerrit-project="$project"; \
  done

As output, this provides a MySQL database with the name specified in --db-database-out, that should be previously created.

Limitations of current database

  • There are still some tricks in the database for Gerrit, given that the current schema is shared by other issue tracking system. Specifically the case of the tables attachments, comments, issues_watchers and related_to tables that are empty.
  • Besides, the old_value field in changes table really contains the patch number and not the old value of such field.
  • The name of the tracker starts with dicortazar, this is due to the URL provided to Bicho, that value should be removed.

Querying the database

Some examples when querying the database:

Playing with patches:

Evolutionary values (example of query):

SELECT YEARWEEK(c.changed_on , 3) AS week,   count(distinct(c.id)) as  approved
FROM  changes c, issues i
WHERE  c.changed_on >= '2012-01-01' AND  c.changed_on < '2013-01-01' AND c.field = 'APRV' and i.id = c.issue_id
GROUP BY YEARWEEK(c.changed_on , 3)
ORDER BY YEARWEEK(c.changed_on , 3)

Aggregated values (example of query)

SELECT   count(distinct(c.id)) as  approved
FROM  changes c, issues i
WHERE  c.changed_on >= '2012-01-01' AND c.changed_on < '2013-01-01' AND c.field = 'APRV' and i.id = c.issue_id

Per type of patch (options in the query using c.field=''):

  • 'verified', " c.field = 'VRIF' "
  • 'approved', " c.field = 'APRV' "
  • 'codereview', " c.field = 'CRVW' "
  • 'sent', " c.field = 'SUBM' "

Playing with reviews:

Evolutionary values (example of query):

SELECT YEARWEEK(i.submitted_on , 3) AS week, count(distinct(i.issue)) as abandoned
FROM issues i  WHERE i.submitted_on >= '2012-01-01' AND i.submitted_on < '2013-01-01' AND i.status = 'ABANDONED'
GROUP BY YEARWEEK(i.submitted_on , 3)
ORDER BY YEARWEEK(i.submitted_on , 3)

Aggregated values (example of query):

SELECT count(distinct(i.issue)) as closed
FROM issues i
WHERE  i.submitted_on >= '2012-01-01' AND  i.submitted_on < '2013-01-01'
 AND (i.status = 'MERGED' or i.status = 'ABANDONED')

Per type of review (options in the query using i.status=''):

  • "submitted" : ""
  • "opened": " (i.status = 'NEW' or i.status = 'WORKINPROGRESS') "
  • "new": " i.status = 'NEW' "
  • "inprogress": " i.status = 'WORKINGPROGRESS' "
  • "closed": " (i.status = 'MERGED' or i.status = 'ABANDONED') "
  • "merged": " i.status = 'MERGED' "
  • "abandoned": " i.status = 'ABANDONED' "

Patches waiting for submitter:

SELECT YEARWEEK(c.changed_on , 3) AS week, count(distinct(c.id)) as WaitingForSubmitter
FROM  changes c, issues i, (
  select c.issue_id as issue_id, c.old_value as old_value, max(c.id) as id
  from changes c, issues i
  where c.issue_id = i.id and i.status='NEW'
  group by c.issue_id, c.old_value) t1
WHERE  c.changed_on >= '2012-01-01' AND c.changed_on < '2013-01-01' AND i.id = c.issue_id and t1.id = c.id
 and (c.field='CRVW' or c.field='VRIF') and (c.new_value=-1 or c.new_value=-2)
GROUP BY YEARWEEK(c.changed_on , 3)
ORDER BY YEARWEEK(c.changed_on , 3)

Patches waiting for reviewer:

SELECT YEARWEEK(  c.changed_on , 3 ) AS week, count(distinct(c.id)) as WaitingForReviewer
FROM changes c, issues i, (
  select c.issue_id as issue_id, c.old_value as old_value, max(c.id) as id
  from changes c, issues i
  where c.issue_id = i.id and i.status='NEW'
  group by c.issue_id, c.old_value) t1
WHERE  c.changed_on >= '2012-01-01' AND  c.changed_on < '2013-01-01' AND i.id = c.issue_id and t1.id = c.id
  and (c.field='CRVW' or c.field='VRIF') and (c.new_value=1 or c.new_value=2)
GROUP BY YEARWEEK(c.changed_on , 3)
ORDER BY YEARWEEK(  c.changed_on , 3 )

Example of longest issues waiting to be closed:

select i.issue as review, t1.old_value as patch, timestampdiff (HOUR, t1.min_time, t1.max_time) as timeOpened 
from (
  select c.issue_id as issue_id, 
   c.old_value as old_value, 
   min(c.changed_on) as min_time, 
   max(c.changed_on) as max_time 
  from changes c, issues i 
  where c.issue_id = i.id and i.status='NEW'  
  group by c.issue_id, c.old_value) t1,
 issues i
where t1.issue_id = i.id
order by timeOpened desc
limit 20

Time to close an issue. Given that there is no change date when an issue is closed in Gerrit or similar, here we consider the time to close a review as from when it was submitted, till the max(date) of all its patches (for the relevant date we're interested in.

If we're interested in the patches that were merged:

select timestampdiff(HOUR, i.submitted_on, t1.max_date) 
from issues i, (
  select issue_id, max(changed_on) as max_date
  from changes
  group by issue_id) t1 
where i.id = t1.issue_id and i.status="MERGED"

If we're interested in those that were abandoned:

select timestampdiff(HOUR, i.submitted_on, t1.max_date)
from issues i, (
  select issue_id, max(changed_on) as max_date 
  from changes
  group by issue_id) t1 
where i.id = t1.issue_id and i.status="ABANDONED"

If we're interested both in merged and abandoned:

select timestampdiff(HOUR, i.submitted_on, t1.max_date)
from issues i, (
  select issue_id, max(changed_on) as max_date 
  from changes
  group by issue_id) t1 
where i.id = t1.issue_id and (i.status="ABANDONED" or i.status="MERGED")

In many cases, we're interested in getting information only for one tracker (project). Trackers are specified in the issues.tracker_id field, which references to the id field in the trackers table. An example that could be extended to any of the previous queries (in this case for tracker with id 72):

select timestampdiff(HOUR, i.submitted_on, t1.max_date) 
from issues i, (
  select issue_id, max(changed_on) as max_date
  from changes
  group by issue_id) t1 
where i.id = t1.issue_id and i.status="MERGED" and i.tracker_id=72

Example of patches for specific repositories per week:

SELECT YEARWEEK(c.changed_on , 3) AS week, count(distinct(c.id)) as WaitingForSubmitter 
FROM  changes c, issues i, 
    (   select c.issue_id as issue_id, c.old_value as old_value, max(c.id) as id   
        from changes c, issues i   
        where c.issue_id = i.id and i.status='NEW'   
        group by c.issue_id, c.old_value) t1 
WHERE  c.changed_on >= '2012-01-01'  and i.id = c.issue_id and i.tracker_id=72 and t1.id = c.id  and 
       (c.field='CRVW' or c.field='VRIF') and (c.new_value=-1 or c.new_value=-2) 
GROUP BY YEARWEEK(c.changed_on , 3) 
ORDER BY YEARWEEK(c.changed_on , 3);

Time spent in each patch (for all patches!). This is computed, for each review request, from the first change in the table changes till the last change in such patch. In some cases specific patches just do not finish the complete cycle up to be submitted, because they are eg abandoned. Those are also included in this query.

select timestampdiff(hour, t2.min_date, t1.max_date) 
from (select issue_id, old_value, max(changed_on) as max_date
      from changes
      group by issue_id, old_value) t1, 
     (select issue_id, old_value, min(changed_on) as min_date
      from changes
      group by issue_id, old_value) t2 
where t1.issue_id=t2.issue_id and t1.old_value=t2.old_value

Same, per repository:

select timestampdiff(hour, t2.min_date, t1.max_date) 
from issues i, 
     (select issue_id, old_value, max(changed_on) as max_date
      from changes
      group by issue_id, old_value) t1, 
     (select issue_id, old_value, min(changed_on) as min_date
      from changes
      group by issue_id, old_value) t2
where t1.issue_id=t2.issue_id and t1.old_value=t2.old_value and 
      i.id = t1.issue_id and i.tracker_id=72

Now for those that are still open:

select timestampdiff(hour, t2.min_date, t1.max_date) as delay,
  i.tracker_id as tracker
from issues i,       
     (select issue_id, old_value, max(changed_on) as max_date
      from changes
      group by issue_id, old_value) t1,
     (select issue_id, old_value, min(changed_on) as min_date
      from changes
      group by issue_id, old_value) t2 
where t1.issue_id=t2.issue_id and t1.old_value=t2.old_value and        
     i.id = t1.issue_id and i.tracker_id=72 and
     i.status != 'MERGED' and i.status != 'ABANDONED'
order by i.tracker_id;

For those that are already closed (now for tracker 50):

select timestampdiff(hour, t2.min_date, t1.max_date) as delay,
  i.tracker_id as tracker
from issues i,       
     (select issue_id, old_value, max(changed_on) as max_date
      from changes
      group by issue_id, old_value) t1,
     (select issue_id, old_value, min(changed_on) as min_date
      from changes
      group by issue_id, old_value) t2 
where t1.issue_id=t2.issue_id and t1.old_value=t2.old_value and        
     i.id = t1.issue_id and i.tracker_id=50 and
     (i.status='MERGED' or i.status='ABANDONED')
order by i.tracker_id;
Add a custom footer
Add a custom sidebar
Clone this wiki locally