SQL queries to SonarQube 5.2 database for retrieve information

get_list_of_paths.sh

# get one line comma-separated list of project paths with zero integration test coverage

mysql -h127.0.0.1 -uroot -proot sonar -e "SELECT concat(projects.long_name,'/*,') FROM snapshots JOIN projects ON snapshots.project_id=projects.id JOIN project_measures as m1 ON m1.snapshot_id=snapshots.id JOIN project_measures as m2 ON m2.snapshot_id=snapshots.id WHERE snapshots.islast=1 AND snapshots.scope=\"DIR\" AND m1.metric_id=59 AND m1.value=0 AND m2.metric_id=3;" > /tmp/list.txt
sed -i '1d' /tmp/list.txt
cat /tmp/list.txt | tr -d ' ' | tr -d "\n" > /tmp/complete_list.txt

# or remove leading characters (jenkins limit 300k)
cat /tmp/classes.txt | awk -F"/" '{ print "**/" $(NF-1) "/" $NF }'

# update property in database
UPDATE properties AS p SET p.text_value="**/MyExclusionClass.java" WHERE p.resource_id=(SELECT p.id FROM projects AS p WHERE p.scope="PRJ" AND p.name LIKE "ProjectName" AND p.authorization_updated_at is not null	LIMIT 0,1) AND p.prop_key="sonar.coverage.exclusions";

UPDATE properties AS p 
SET p.text_value="**/MyExclusionClass.java" 
WHER	p.resource_id=(SELECT p.id FROM projects AS p WHERE p.scope="PRJ" AND 
	p.name LIKE "ProjectName" AND 
	p.authorization_updated_at is not null	LIMIT 0,1) 
	AND p.prop_key="sonar.coverage.exclusions";


# query, m2.value contains line of code value
# metric_id = 59 - it line coverage
# metric_id = 3  - number lines of code

SELECT concat(projects.long_name,'/*,') 
FROM snapshots 
JOIN projects ON snapshots.project_id=projects.id 
JOIN project_measures as m1 ON m1.snapshot_id=snapshots.id 
JOIN project_measures as m2 ON m2.snapshot_id=snapshots.id 
WHERE 	snapshots.islast=1 AND
    	snapshots.scope="DIR" AND 
    	m1.metric_id=59 AND 
    	m1.value=0 AND 
    	m2.metric_id=3;
    		
    		
# 
SELECT concat(projects.long_name,'/*,') 
FROM snapshots 
JOIN projects ON snapshots.project_id=projects.id
JOIN project_measures as m1 ON m1.snapshot_id=snapshots.id 
JOIN project_measures as m2 ON m2.snapshot_id=snapshots.id
WHERE 	snapshots.root_project_id=( SELECT p.id 
									FROM projects AS p 
									WHERE   p.scope="PRJ" AND 
											p.name LIKE "ProjectName" AND
											p.authorization_updated_at is not null
											LIMIT 0,1) AND
		snapshots.islast=1 AND
		snapshots.scope="DIR" AND 
		m1.metric_id=59 AND 
		m1.value=0 AND 
		m2.metric_id=3;