Database schema changes from 5.9 to 6.7 have broken PostgreSQL queries

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

Hi Dave,

We will not be providing this guidance; the database should be treated as a black box, not an API. If you’d like guidance extracting values via the Web API, then we should be able to help.

 
Ann