We have upgraded our test instance of SonarQube from the previous LTS version or 5.9 to the current LTS version of 6.7. We utilize several direct database queries (PostgreSQL 9.4) to extract data for usage in a data warehouse. Understandably, there have been some db schema changes between the two versions, however, we’re having difficulty re-writing our queries due to the extent of changes.
For example, this query worked in SonarQube 5.9:
SELECT a.id, to_timestamp(a.created_at/1000) as created_at, CAST(b.value as integer) as defects, b.id as defect_id FROM snapshots as a INNER JOIN project_measures as b ON (a.id=b.snapshot_id and a.scope='PRJ' and a.qualifier='TRK' and b.metric_id = 31 and b.rule_id is null) WHERE to_timestamp(a.created_at/1000) >= '2014-01-01'
However, in SonarQube 6.7,
project_id fields have been dropped from table
snapshot_id” and “
rule_id” have been dropped from table “
Can you provide any guidance on how these tables are now linked and how to rewrite the queries? Is there a database scheme guide available?