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)

Answer 2

You can get The above Information Using The below table.

1)projects, snapshots , metrics and project_measures. where projects table contain the Projects name . And for each project one snapshot id is created in certain period of time in snapshot table. then from snapshot table take the snapshot id and search it projects_measure table. and search the value of the descibed attribute using metric id.

select distinct proj.name NAME_OF_PROJ, metric.description Description,
                projdesc.value, snap.created_at CREATED_DATE
  from projects proj
    inner join snapshots snap on snap.project_id=proj.id 
  inner join (select max(snap2.created_at) as date_of_creation,id from snapshots snap2 
              where Date(snap2.created_at)   in ('2011-12-20','2012-02-21') 
               and snap2.project_id  in (5507,35252,9807,38954,23018,32390) 
              GROUP BY DAY(snap2.created_at),snap2.project_id ) as Lookup on Lookup.id=snap.id 

  inner join project_measures projdesc on  projdesc.snapshot_id=snap.id 
  inner join metrics metric on  projdesc.metric_id =metric.id 
  where  metric.id in( 1,2...)