sonar-collector

SonarQube metrics collector

This is a utility to collect some key numbers for each SonarQube analysis triggered by maven builds.

The purpose of this is to log statistics that shows the improvement of the code quality of the projects over time.

This utility consists of a servlet that serves as a webhook that is called by Sonar when completing an analysis. The webhook POST data doesn't have the necessary information (which are some key metrics of the build).

So when receiving a POST, the servlet will do a callback to the SonarQube REST API to retrieve the metrics, which will then be stored in a PostgreSQL database.

Status of the project

file:https://travis-ci.org/steinarb/sonar-collector.svg?branch=master file:https://coveralls.io/repos/steinarb/sonar-collector/badge.svg

SonarCloud

file:https://sonarcloud.io/api/badges/measure?key=no.priv.bang.sonar.sonar-collector%3Aparent&metric=lines#.svg file:https://sonarcloud.io/api/badges/measure?key=no.priv.bang.sonar.sonar-collector%3Aparent&metric=bugs#.svg file:https://sonarcloud.io/api/badges/measure?key=no.priv.bang.sonar.sonar-collector%3Aparent&metric=new_bugs#.svg file:https://sonarcloud.io/api/badges/measure?key=no.priv.bang.sonar.sonar-collector%3Aparent&metric=vulnerabilities#.svg file:https://sonarcloud.io/api/badges/measure?key=no.priv.bang.sonar.sonar-collector%3Aparent&metric=new_vulnerabilities#.svg file:https://sonarcloud.io/api/badges/measure?key=no.priv.bang.sonar.sonar-collector%3Aparent&metric=code_smells#.svg file:https://sonarcloud.io/api/badges/measure?key=no.priv.bang.sonar.sonar-collector%3Aparent&metric=new_code_smells#.svg file:https://sonarcloud.io/api/badges/measure?key=no.priv.bang.sonar.sonar-collector%3Aparent&metric=coverage#.svg file:https://sonarcloud.io/api/badges/measure?key=no.priv.bang.sonar.sonar-collector%3Aparent&metric=new_coverage#.svg

How to build the application

Procedure:
1. Clone the project
#+BEGIN_EXAMPLE mkdir -p ~/git cd ~/git git clone https://github.com/steinarb/sonar-collector.git #+END_EXAMPLE
2. Build the project with maven
#+BEGIN_EXAMPLE cd ~/git/sonar-collector mvn clean install #+END_EXAMPLE

How to install and run the application

This describes how to install and run the program on a debian GNU/linux system.

Install the required software

As root, do the following command:

  apt-get update
  apt-get postgresql install git maven openjdk-8-jdk postgresql ruby ruby-dev build-essential

Create the database

Procedure:
1. Create a PostgreSQL user matching the karaf user:
#+BEGIN_EXAMPLE /usr/bin/sudo -u postgres createuser --pwprompt karaf #+END_EXAMPLE
2. At the prompt "Enter password for new role", enter the JDBC password for user "karaf"
3. At the prompt "Enter it again", enter the same password again
Make a note of this password, since it will be needed later, when [[Using a database running on a different host][setting up a password authenticated connection]]
4. Create an empty database owned by the karaf user:
#+BEGIN_EXAMPLE /usr/bin/sudo -u postgres createdb -O karaf sonarcollector #+END_EXAMPLE

Install apache karaf

Install the application in karaf

Do the following steps as root
1. Build the debian package
#+BEGIN_EXAMPLE gem install fpm cd /tmp git clone https://github.com/steinarb/karaf-deb-packaging cd karaf-deb-packaging ./dist_karaf.sh mkdir -p /root/debs cp *.deb /root/debs #+END_EXAMPLE
2. Install the debian package
#+BEGIN_EXAMPLE dpkg --install /tmp/karaf-deb-packaging/karaf_4.1.2-1_all.deb #+END_EXAMPLE

Using a database running on a different host

Procedure:
1. SSH into karaf
#+BEGIN_EXAMPLE ssh -p 8101 karaf@localhost #+END_EXAMPLE The password is "karaf" (without the quotes)
2. In the karaf command shell, add a maven-repo snapshot repo that contains sonarcollector, built by travis CI:
#+BEGIN_EXAMPLE config:edit org.ops4j.pax.url.mvn config:property-append org.ops4j.pax.url.mvn.repositories ", https://maven.bang.priv.no/repository/@id=sonar-collector@snapshots, http://maven.vaadin.com/vaadin-addons@id=vaadin" config:property-set org.ops4j.pax.url.mvn.globalUpdatePolicy always config:update #+END_EXAMPLE
3. Install the application
#+BEGIN_EXAMPLE feature:repo-add mvn:no.priv.bang.sonar.sonar-collector/sonar-collector-webhook/LATEST/xml/features feature:install sonar-collector-webhook #+END_EXAMPLE

The above example shows connecting to a PostgreSQL database running on localhost, authenticating with ident authentication (ie. no password).

This example shows how to connect to a PostgreSQL database running on a different host, authenticating using username and password.

Procedure:
1. SSH into karaf
#+BEGIN_EXAMPLE ssh -p 8101 karaf@localhost #+END_EXAMPLE The password is "karaf" (without the quotes)
2. In the karaf command shell, create configuration for the JDBC connection:
#+BEGIN_EXAMPLE config:edit no.priv.bang.sonar.collector.webhook.SonarCollectorServlet config:property-set sonar.collector.jdbc.url "jdbc:postgresql://lorenzo.hjemme.lan/sonarcollector" config:property-set sonar.collector.jdbc.user "karaf" config:property-set sonar.collector.jdbc.password "karaf" config:update #+END_EXAMPLE (this assumes the username/password combination karaf/karaf, it is recommended to use a different password in a real setting with PostgreSQL accepting network connections)

The "config:update" command will cause the sonar collector to be restarted, it will pick up the new configuration, and connect to the remote server, and if the "sonar-collector" database exists as a blank database, create the schema and be ready to store data there.

Side note: The configuration will be stored in standard .properties file format, in the file /etc/karaf/no.priv.bang.sonar.collector.webhook.SonarCollectorServlet.cfg and be persistent across restarts and reinstallations of the karaf .deb package (the .deb package will only uninstall/reinstall unchanged known files in this directory, and won't touch unknown files at all).

Allowing network connections in PostgreSQL on debian

Note that PostgreSQL out of the box on debian only accepts domain connections and localhost connections.

To make PostgreSQL listen on all network connections, two files must be edited and the PostgreSQL daemon must be restarted.

Procedure, do the following, logged in as root on the server:
1. Do "su" to user postgres to get the right ownership on the files
#+BEGIN_EXAMPLE su - postgres #+END_EXAMPLE
2. Edit the /etc/postgresql/9.6/main/postgresql.conf file, uncomment the listen_address line and edit it to look like this
#+BEGIN_SRC conf listen_addresses = '*' # what IP address(es) to listen on; #+END_SRC
3. Edit the /etc/postgresql/9.6/main/pg_hba.conf, add the following lines
#+BEGIN_SRC conf # IPv4 network connection allow password authentication host all all 0.0.0.0/0 md5 #+END_SRC
4. Log out from user postgres (only root can restart the daemon):
#+BEGIN_EXAMPLE exit #+END_EXAMPLE
5. Restart the postgresql daemon
#+BEGIN_EXAMPLE systemctl restart postgresql #+END_EXAMPLE

Add a webhook to Sonar

Add a webhook to SonarCloud

Procedure:

Open your SonarCloud project in a web browser and log in as a user with ownership to the project (I do login as github user)

In the project select the menu Administration->General Settings

Select the webhooks tab in the tab bar on the left side of the page (you may have to scroll down to see it)

In "Name:", write:

sonar-collecttor
In "URL", write:
https://mydowmain.com:8181/sonar-collector
Click the button "Save"

Add a webhook to a hosted SonarQube instance

In a hosted SonarQube the webhook can be set globally across all projects.

Procedure:

Open your SonarCloud instance in a web browser, e.g. http://localhost:9000 and log in as an admin user (admin/admin in a test instance)

In the top menu, select Administration

Select the tab "Webhooks" in the list to the left of the page (you may have to scroll down to see the tab)

In "Name", type:

sonar-collector
In "URL", type:
http://localhost:8181/sonar-collector
Click the button "Save"

License

This utility is licensend under the Apache license v. 2. See the LICENSE file for details.

List of things left to do

This is my current TODO list for this project. The list is dynamically added to and checked off, as I do things. Things that are already done and committed will be removed.

Here's the list [9/9]:
• [X] Try to remove jackson annotations and figure out if things still work, must be removed from both build dependencies and the karaf feature
• <2017-11-17 fr 22:46> The code worked fine without jackson annotations
• [X] Do logging for PostgreSQL connection errors
• <2017-11-17 fr 21:04> Fixed, the problem was that the order of the injections isn't defined, so the PostgreSQL datasource was injected before the logservice
• [X] Add database setup to the README
• [X] Add travis CI build setup
• [X] Make the database column values be numeric
• [X] Fix the timestamp
• <2017-11-19 sø 12:55> The parsed time was in seconds since epoch and not milliseconds since epoch, multipled the value with 1000
• [X] Index the columns project_key version_is_release of the measures table
• <2017-11-19 sø 13:24> The reason for this, is to make the SQL queries stay fast when the number of rows grow large
• <2017-11-19 sø 13:39> Added a Liquibase changeSet to add the indexes
• [X] Add ftp deploy to the travis build setup
• <2017-11-19 sø 13:48> Added a deploy script to .travis.yml
• [X] Add missing fields "issues" and "complexity"
• <2017-12-16 lør. 21:30> "complexity" was added as a measure together with the others
• <2017-12-16 lør. 21:37> "issues" isn't an actual measure om Sonar, it's the sum of the number of bugs, the number of vulnerabilities and the number of code_smells
• <2017-12-16 lør. 21:39> Created a view, measures_views, which adds an "issues column" which is the sum of the existing three columns