SQL Queries

MSR 2011

Here are the SQL queries used to generate the numbers in the 2011 MSR paper.

Get number of revisions

select count(*) from scmlog
where repository_id = ?

Get number of hunks

select count(*) from hunks h, scmlog s
where h.commit_id = s.id
and s.repository_id = ?

Get number of files

select count(*) from files f, file_types ft
where ft.file_id = f.id
and f.repository_id = ?
and ft.type = 'code'

Get number of bugs

select count(distinct bug_commit_id)
from hunk_blames hb, scmlog s, hunks h, file_types ft, files f
where hb.bug_commit_id = s.id
and hb.hunk_id = h.id
and h.file_id = f.id
and ft.file_id = f.id
and ft.type = 'code' 
and s.repository_id = 1