目录
通过projectKey和branch查找违法的规则、对应的文件、所在的行数、语言
查询类
通过projectKey和branch查找分析状态
数据库解析
-
通过查找 projects中kee(projectKey)和scope=PRJ找到 project_uuid
-
通过project_uuid匹配project_branches中的project_uuid找到该项目下所有的分支(kee)
-
通过project_uuid和kee(分支名称)查找的project_branches的uuid
-
通过project_branches的uuid匹配ce_activity的component_uuid,通过is_last=t找到该次执行的分析状态(status)
sql展示
SELECT ca.status FROM ce_activity ca LEFT JOIN project_branches pb ON ca.component_uuid = pb.uuid LEFT JOIN projects ps ON pb.project_uuid = ps.project_uuid WHERE ps.kee = 'accuratetest-comparison' AND pb.kee = 'dev' AND ca.is_last='t' AND ps.scope='PRJ' |
通过projectKey和branch查找违法的规则、对应的文件、所在的行数、语言
数据库解析
-
通过查找 projects中kee(projectKey)和scope=PRJ找到 project_uuid
-
通过project_uuid匹配project_branches中的project_uuid找到该项目下所有的分支(kee)
-
通过project_uuid和kee(分支名称)查找的project_branches的uuid
-
通过project_branches的uuid和issues中的project_uuid匹配查找到违反的规则
-
通过issues中违反规则信息中的rule_id和rules中的id匹配规则信息
-
通过issues中违反规则信息中的component_uuid和projects的uuid做匹配查找到 projects中的path(路径) ,其中scope!=PRJ
sql展示
SELECT r.plugin_name || ':' || r.plugin_rule_key AS codeRuleKey, r.NAME AS codeRuleName, r.LANGUAGE AS codeLanguage, i.severity AS codeRuleSeverity, C.kee AS codeRuleDocuments, C.PATH AS subCodeRuleDocuments, CASE
WHEN i.line IS NULL THEN 0 ELSE i.line END AS codeRuleDocumentsRows, CASE
WHEN i.issue_type = '1' THEN 'CODE_SMELL' WHEN i.issue_type = '2' THEN 'BUG' WHEN i.issue_type = '3' THEN 'VULNERABILITY' WHEN i.issue_type = '4' THEN 'SECURITY_HOTSPOT' END AS issueType, i.STATUS AS ruleState FROM issues i INNER JOIN rules r ON r.ID = i.rule_id INNER JOIN projects C ON C.uuid = i.component_uuid WHERE i.project_uuid = ( SELECT project_branches.uuid FROM project_branches LEFT JOIN projects ON projects.project_uuid = project_branches.project_uuid WHERE projects.kee = 'accuratetest-comparison' AND project_branches.kee = 'dev' ) |
通过projectKey和branch 查找行数(lines)、代码行数(ncloc)、重复行数(duplicated_lines)、重复率(duplicated_lines_density)、注释行(comment_lines)、注释率信息(comment_lines_density)、文件信息、文件语言
数据库解析
-
通过查找 projects中kee(projectKey)和scope=PRJ找到 project_uuid
-
通过project_uuid匹配project_branches中的project_uuid找到该项目下所有的分支(kee)
-
通过project_uuid和kee(分支名称)查找的project_branches的uuid
-
通过project_branches的uuid和live_measure中的project_uuid匹配度量数据信息
-
通过live_measure中的metric_id和metrics中的id匹配度量具体指标信息
-
通过live_measure中的component_uuid和projects的uuid做匹配查找到 projects中的path(路径)等信息
sql展示
SELECT CASE WHEN ms."name" = 'ncloc' THEN '代码行' WHEN ms."name" = 'lines' THEN '行数' WHEN ms."name" = 'duplicated_lines' THEN '重复行' WHEN ms."name" = 'duplicated_lines_density' THEN '重复率' WHEN ms."name" = 'comment_lines' THEN '注释行' WHEN ms."name" = 'comment_lines_density' THEN '注释率' END AS "name", ROUND( lm."value", 2 ) AS "value", pj."scope", pj.kee, pj.PATH , pj."language" FROM live_measures lm INNER JOIN metrics ms ON lm.metric_id = ms."id" INNER JOIN projects pj ON lm.component_uuid = pj.uuid WHERE lm.project_uuid = ( SELECT project_branches.uuid FROM project_branches LEFT JOIN projects ON projects.project_uuid = project_branches.project_uuid WHERE projects.kee = 'accuratetest-comparison' AND project_branches.kee = 'dev' ) AND ms."name" IN ( 'ncloc', 'lines', 'duplicated_lines', 'duplicated_lines_density', 'comment_lines', 'comment_lines_density' ) AND pj."scope" IN ( 'FIL', 'PRJ' ); |
统计类
规定时间内扫描次数
SELECT "count" ( 1 ) AS num FROM ce_activity ca WHERE ca.submitted_at BETWEEN ( SELECT EXTRACT ( epoch FROM CAST ( '2024-05-01 00:00:00' AS TIMESTAMP ) ) * 1000 ) AND ( SELECT EXTRACT ( epoch FROM CAST ( '2024-05-02 00:00:00' AS TIMESTAMP ) ) * 1000 ) |
规定时间内扫描BUG数、漏洞数、异味数、安全热点数
SELECT CASE
WHEN issues.issue_type = '1' THEN '异味' WHEN issues.issue_type = '2' THEN 'BUGS' WHEN issues.issue_type = '3' THEN '漏洞' WHEN issues.issue_type = '4' THEN '安全热点' END AS "name", "count" ( 1 ) AS num FROM issues WHERE issues.created_at BETWEEN ( SELECT EXTRACT ( epoch FROM CAST ( '2024-05-01 00:00:00' AS TIMESTAMP ) ) * 1000 ) AND ( SELECT EXTRACT ( epoch FROM CAST ( '2024-05-02 00:00:00' AS TIMESTAMP ) ) * 1000 ) GROUP BY issues.issue_type; |
规定时间内扫描的行数、代码行数、重复行数、注释行
SELECT CASE
WHEN ms."name" = 'ncloc' THEN '代码行' WHEN ms."name" = 'lines' THEN '行数' WHEN ms."name" = 'duplicated_lines' THEN '重复行' WHEN ms."name" = 'comment_lines' THEN '注释行'
END AS "name", ROUND( "sum" ( lm."value" ) , 2 ) AS "value" FROM live_measures lm INNER JOIN metrics ms ON lm.metric_id = ms."id" INNER JOIN projects pj ON lm.component_uuid = pj.uuid WHERE lm.project_uuid IN ( SELECT snapshots.component_uuid FROM "snapshots" WHERE snapshots.created_at BETWEEN ( SELECT EXTRACT ( epoch FROM CAST ( '2024-05-01 00:00:00' AS TIMESTAMP ) ) * 1000 ) AND ( SELECT EXTRACT ( epoch FROM CAST ( '2024-05-02 00:00:00' AS TIMESTAMP ) ) * 1000 ) ) AND ms."name" IN ( 'ncloc', 'lines', 'duplicated_lines','comment_lines' ) AND pj."scope" IN ( 'PRJ' ) GROUP BY ms."name"; |