Hello,
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, scope
, qualifier
, and project_id
fields have been dropped from table snapshots
. “snapshot_id
” and “rule_id
” have been dropped from table “project_measures
”
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?
Thanks,
Dave