sonarqube - What is the sonar database structure?

Question

I am trying to generate a monthly report base on below factor

1. LoC(lines of code)
2. Rule Compliance %
3. Comment %
4. Public Documented API %
5. Security Violations
6. Violations (excluding Info)
7. Duplicated Line %

I tried to check the Entity relation ship in sonar database,all table are independent . I am not sure from which table I should get the value so as to produce the report .

For the hints below query is mentioned

Hint:

select proj.name as ClassName, -- Class Name for which violation has been found out
       proj.long_name as LongName, -- Long Class Name i.e. with package for which violation has been found out
       rf.failure_level as ErrorLevel, -- Error level of the violation
       rf.message as Violation, -- Cause of Violation 
       rf.line as LineNumber, -- Line number of the class file
       ru.name ViolationName, -- Violation Description
       ru.plugin_name PluginType -- Plugin tool by which this error has been detected i.e. findbug, PMD, etc.
       --  ,ru.description  -- (if violation description is required we can add this column) from projects proj  inner join snapshots snap on  proj.id = snap.project_id inner join rule_failures rf on rf.snapshot_id = snap.id inner join rules ru on ru.id = rf.rule_id

Answer 1

I would suggest using the Sonar REST API to retrieve statistics.

The database schema is deliberately undocumented by the development team. This enables them to make changes which don't break dependent reporting applications. (Obviously this doesn't stop someone running SQL queries)

---

Example

Using the CXF project in nemo as an example.

The "resources" REST API returns the latest value of the metrics your requested

http://nemo.sonarsource.org/api/resources?resource=org.apache.cxf:cxf&verbose=true&metrics=ncloc,violations_density,comment_lines_density,public_documented_api_density,duplicated_lines_density,blocker_violations,critical_violations,major_violations,minor_violations

And "timemachine" REST API returns a raw CSV dump of the data:

http://nemo.sonarsource.org/api/timemachine?resource=org.apache.cxf:cxf&format=csv&metrics=ncloc,violations_density,comment_lines_density,public_documented_api_density,duplicated_lines_density,blocker_violations,critical_violations,major_violations,minor_violations

(My browser will conveniently launch a spreadsheet to read CSV data)