Sonar 第二弹

作者: jaymz明 | 来源:发表于2019-03-06 10:53 被阅读15次

    前文讲述了sonar的一些指标以及配置的相关信息,https://www.jianshu.com/p/2de96cfcff30,本文继续聊一下sonar指标背后的设计。

    故事的开端还是起源于需求,boss不仅仅想在邮件中看到5个维度的分值数据(bugs,vulnerabilities,code smells,coverages,duplications),而且还想知道bugs、vulnerabilities的严重程度(critical/blocker),并且能知道这次build analysis和上次甚至(base)的趋势。仔细想想这样的要求也是合理的。画出简图如下:


    index

    那么单独给出sonar的dashboard显然不能达到要求,需要对sonar的数据库进行研究(一开始的思路是研究sonar的web service api,后来发现sonar的api局限性比较大,而且针对我这种整合性的需求,需要调用很多api组合,对性能来说不是明智之举)。

    sonar domain

    网上资源较少,如有解释不对之处,请不吝赐教

    sonar domain

    为了达到上述提到的需求,需要用到这几个表(当然这几个表也是比较核心的,图中列举了少数重要字段,其余的大家可以自行查阅)。

    • projects,项目表。存储你扫描的项目的name,kee等。这个字段kee就是定义的项目的属性key,类似于这样:com.jaymz.test:test.projectName。为什么说他重要呢,因为后面的数据可以通过它来唯一确认。project_uuid该项目的唯一id。
    • ce_activity,存储了项目与分析次数的关联数据。其中is_last字段定义了是否为最后一次分析,可以通过这个表获取每个分析数据的analysis_id,sonar也是通过这个表来得到同一个项目不同时间分析各个指标的趋势。
    • projects_measures,关联项目和metrics,存储分析数据的值。
    • metrics,定义了维度的名字。比如(bugs,new_bugs,coverages...)
    • issues,定义了issues的严重程度,并关联rules。
    • rules,定义各个语言关联的扫描规则,可以自定义rule。

    必要的关联字段,domain图中已经给出。

    查询

    我们来写一下实现的sql。(使用的postgreSQL,字段上面一定要加个引号让我很不习惯...而且执行计划和mysql和oracle也是不一样Orz)

     select (select count(1) from  rules r,issues i where r."rule_type"=2 and r."id" = i."rule_id" and i."project_uuid" = p."project_uuid") as "bugs",
     (select count(1) from  rules r,issues i where r."rule_type"=2 and i."severity"='BLOCKER' and r."id" = i."rule_id" and i."project_uuid" = p."project_uuid") as "bug_blocker",
     (select count(1) from  rules r,issues i where r."rule_type"=2 and i."severity"='CRITICAL' and r."id" = i."rule_id" and i."project_uuid" = p."project_uuid") as "bug_critical",
    (select count(1) from  rules r,issues i where r."rule_type"=3 and r."id" = i."rule_id" and i."project_uuid" = p."project_uuid") as "vulnerabilities",
    (select count(1) from  rules r,issues i where r."rule_type"=3 and i."severity"='BLOCKER' and r."id" = i."rule_id" and i."project_uuid" = p."project_uuid") as "vulnerabilities_blocker",
    (select count(1) from  rules r,issues i where r."rule_type"=3 and i."severity"='CRITICAL' and r."id" = i."rule_id" and i."project_uuid" = p."project_uuid") as "vulnerabilities_critical",
    (select s."value" from metrics m,project_measures s where m."id" = s."metric_id" and ce."analysis_uuid" = s."analysis_uuid" and s."component_uuid" = ce."component_uuid" and m."name"='new_bugs') as "new_bugs",
    (select s."value" from metrics m,project_measures s where m."id" = s."metric_id" and ce."analysis_uuid" = s."analysis_uuid" and s."component_uuid" = ce."component_uuid" and m."name"='new_vulnerabilities') as 
    "new_vulnerabilities",
    (select s."value" from metrics m,project_measures s where m."id" = s."metric_id" and ce."analysis_uuid" = s."analysis_uuid" and s."component_uuid" = ce."component_uuid" and m."name"='coverage') as 
    "coverage",
    (select s."value" from metrics m,project_measures s where m."id" = s."metric_id" and ce."analysis_uuid" = s."analysis_uuid" and s."component_uuid" = ce."component_uuid" and m."name"='duplicated_lines_density') as 
    "duplicated_lines_density",
    (select s."value" from metrics m,project_measures s where m."id" = s."metric_id" and ce."analysis_uuid" = s."analysis_uuid" and s."component_uuid" = ce."component_uuid" and m."name"='new_duplicated_lines_density') as 
    "new_duplicated_lines_density"
    
    from projects p,ce_activity ce
    where p."kee"='替换成自己的项目kee' and p."project_uuid"=ce."component_uuid" and ce."is_last"=true ;
    

    rule_type有几个值:1-code smell,2-bugs,3-vulnerabilities

    万事具备,只等build

    通过上面的分析,数据也有了,通过python方式拿到数据,然后嵌入到html页面中。通过jenkins job 实时分析,并将结果和上次的缩略图放到邮件里,完成需求。

    相关文章

      网友评论

        本文标题:Sonar 第二弹

        本文链接:https://www.haomeiwen.com/subject/lcftpqtx.html