Sonar数据库相关操作

projects：表中存放所有的项目和模块
issues：表中存放执行sonar扫描项目后的结果
rules：表中存放规则

1. 查询所有接入sonar扫描的应用 FIL：文件；DIR：目录；PRJ：项目或模块
   SELECT * FROM projects WHERE scope='PRJ';

2. 查询某个projectKey对应的项目记录； kee的值就是自己定义sonar.projectKey

SELECT * FROM projects WHERE scope='PRJ' AND kee='Public_SonarScan_iris';

3. 查询所有问题信息（包含处理方式为未处理+解决+误判+不会修复+删除）

SELECT * FROM issues 
WHERE project_uuid =(SELECT project_uuid FROM projects WHERE kee = 'Public_SonarScan_iris' );

4. 查询所有未处理的问题（打开状态，全量bug+全量漏洞）
   status：CONFIRMED--确认，RESOLVED--解决，OPEN--打开，
   REOPENED--重开，CLOSED--关闭，TO_REVIEW--需要审核（安全热点问题）

SELECT * FROM issues 
WHERE project_uuid =(SELECT project_uuid FROM projects WHERE kee = 'Public_SonarScan_iris' ) AND STATUS IN ( 'OPEN', 'REOPENED' );

5. 具体的问题描述（去除重复项，打开的），以及触发的规则

SELECT DISTINCT (i.message),i.rule_id,r.NAME 
FROM issues AS i LEFT JOIN rules AS r 
ON i.rule_id=r.ID 
WHERE i.project_uuid=(SELECT P.project_uuid FROM projects P WHERE P.kee='Public_SonarScan_iris') AND i.status IN ('OPEN','REOPENED');

6. 触发了哪些规则

SELECT DISTINCT (r.NAME) 
FROM issues AS i LEFT JOIN rules AS r 
ON i.rule_id=r.ID 
WHERE i.project_uuid=(SELECT P.project_uuid FROM projects P WHERE P.kee='Public_SonarScan_iris') AND i.status IN ('OPEN','REOPENED') ;

7. 发现的bug数量
   issue_type = 1是异味类型，issue_type = 2是bug类型，issue_type = 3是漏洞类型，issue_type = 4是安全热点类型

SELECT * FROM issues i 
WHERE i.status IN ('OPEN','REOPENED') AND i.issue_type=2 
AND i.project_uuid=(SELECT P.project_uuid FROM projects P WHERE P.kee='Public_SonarScan_iris');

常用字段说明

1.issue_type：1--异味，2--bugs，3--漏洞，4--安全热点
2.rule_type：1--异味，2--bugs，3--漏洞，4--安全热点
3.severity：BLOCKER--阻断，CRITICAL--严重，MAJOR--主要，MINOR--次要，MINOR--bugs，INFO--提示
4.status：CONFIRMED--确认，RESOLVED--解决，OPEN--打开，REOPENED--重开，CLOSED--关闭，TO_REVIEW--需要审核（安全热点问题）
5.resolution：REMOVED--删除，FIXED--解决，FALSE-POSITIVE--误判，WONTFIX--不会修复，