SonarQube:数据库统计项目质量情况

场景

        使用SonarQube进行代码质量检查是，想导出检查结果编译记录和展现，除了一些导出插件外，可以通过数据库查询的方式根据需要查询想要的结果。

方法说明

相关数据表说明

• projects：项目字典表，uuid-项目主键，name项目名，
• project_branches：项目代码分支，project_uuid项目主键，updated_at项目分支最后检查时间
• metrics：检查指标，uuid指标主键，name指标名
• analysis_properties：检查记录，uuid，analysis_uuid检查主键，text_value-检查场景（scm全量代码库，ci-增量代码），created_at检查时间
• project_measures：检查结果，metric_uuid指标主键，analysis_uuid检查主键，component_uuid项目主键

分项目统计

• 最近的检查结果

select ps.name,ms.name,pm.value,to_char(to_timestamp(pb.updated_at/1000),'YYYY-MM-DD HH24:MI:SS') updated_at
from project_branches pb,project_measures pm, analysis_properties ap ,projects ps,metrics ms
where   ap.analysis_uuid = pm.analysis_uuid and ap.text_value='git' and abs(ap.created_at-pb.updated_at)<60000
and  ps.uuid = pb.project_uuid
and  pb.project_uuid = pm.component_uuid
and ms.uuid=pm.metric_uuid
and ms.name in('bugs','vulnerabilities','code_smells','duplicated_lines_density','ncloc')
order by name

分团队项目汇总

• 可以根据项目名前几位进行分组时

select substr(ps.name, 0,10) team,ms.name,ROUND(sum(pm.value),2)
from project_branches pb,project_measures pm, analysis_properties ap ,projects ps,metrics ms
where   ap.analysis_uuid = pm.analysis_uuid and ap.text_value='git' and abs(ap.created_at-pb.updated_at)<60000
and  ps.uuid = pb.project_uuid
and  pb.project_uuid = pm.component_uuid
and ms.uuid=pm.metric_uuid
and ms.name in('bugs','vulnerabilities','code_smells','duplicated_lines_density','ncloc')
group by substr(ps.name, 0,10),ms.name
ORDER BY team