sonar-collector

SonarQube metrics collector

SonarQube is a code analysis tool that shows key numbers about code quality, e.g. code coverage, code complexity and various code practices.

SonarQube has a web GUI that allows exploring the analysis results.

However, SonarQube has no storage of build quality history. To keep statistics about code quality one either have to manually type the key numbers of each analysed application into a spreadsheet, or use something like this tool.

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.

The servlet has been written as a microservice that can be installed into an apache karaf instance.

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 file:https://sonarcloud.io/api/project_badges/measure?project=no.priv.bang.sonar.sonar-collector%3Asonar-collector&metric=alert_status#.svg file:https://maven-badges.herokuapp.com/maven-central/no.priv.bang.sonar.sonar-collector/sonar-collector/badge.svg

SonarCloud

file:https://sonarcloud.io/api/project_badges/measure?project=no.priv.bang.sonar.sonar-collector%3Asonar-collector&metric=ncloc#.svg file:https://sonarcloud.io/api/project_badges/measure?project=no.priv.bang.sonar.sonar-collector%3Asonar-collector&metric=bugs#.svg file:https://sonarcloud.io/api/project_badges/measure?project=no.priv.bang.sonar.sonar-collector%3Asonar-collector&metric=vulnerabilities#.svg file:https://sonarcloud.io/api/project_badges/measure?project=no.priv.bang.sonar.sonar-collector%3Asonar-collector&metric=code_smells#.svg file:https://sonarcloud.io/api/project_badges/measure?project=no.priv.bang.sonar.sonar-collector%3Asonar-collector&metric=coverage#.svg

Release history

Version	Date	Description
1.2.0	<2020-04-20 Mon>	Use jackson-databind 2.9.10.3, make liquibase script work with h2
1.1.0	<2019-11-14 Thu>	Use pax-jdbc-config to set up the database, build with openjdk 11, jackson security upgrade
1.0.0	<2017-12-18 Mon>	First release

How to build the application

(this assumes that you have an apache karaf already installed on your development computer, as well as git, maven, and a Java development kit)

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
3. Give the following commands from the karaf console to install the REST service:
#+BEGIN_EXAMPLE feature:repo-add mvn:no.priv.bang.sonar.sonar-collector/sonar-collector-webhook/LATEST/xml/features feature:install sonar-collector-webhook-with-postgresql #+END_EXAMPLE

/Note/: You will need to have a suitable PostgreSQL database to write to for this servlet to be meaningful. See the sections Create the database and Using a database running on a different host for more detail. The database just have to be a blank, freshly created database, that the servlet can write to, either on localhost with the curent user, or using JDBC credentials configured from the karaf console (this is what's covered in Using a database running on a different host).

Run the Sonar metrics collector in docker

The latest version of the sonar-collector is available from docker hub.

/Note/: The docker image is actually provisioned at startup time by the latest sonar-collector release to maven central, so don't be mislead by the creation date of the image. The most recent relase to maven central is what will be run.

To run the sonar-collector in the container:
1. get hold of an RDBMS (preferrably PostgreSQL but any RDMS that has its driver deployed to maven central (i.e. not Oracle) can probably be made to work)
2. The database could be an AWS MicroDB (preferraby PostgreSQL)
3. The database could potentially be an aurora instance (however, this heas not been tried, since AWS aurora instances costs money from day one)
4. add a user to the RDBMS (e.g. "myuser" with password "sosecret")
5. add a blank database to the RDBMS, e.g. named "sonar-collector", with the user created above as owner
6. In the docker container, get the image from docker hub:
#+begin_example pull steinarb/sonar-collector:latest #+end_example
7. Start a container with the image, setting the JDBC credentials as environment variables:
#+begin_example docker run -p 8101:8101 -p 8181:8181 -e "JDBC_URL=jdbc:postgresql://somehost.com/sonar-collector" -e "JDBC_USER=myuser" -e "JDBC_USER=sosecret" -d steinarb/sonar-collector:latest #+end_example
8. [https://docs.sonarqube.org/latest/project-administration/webhooks/][Add the URL of the sonar-collector running inside the container as a webhook to sonar]] (e.g. http://somecontainer.somecompany.com:8181/sonar-collector)
9. After this the key measurements from each sonar run will be stored as a row in the measures table with the time of the run
10. There is no UI, use a database explorer tool to run queries on the measures table to get the desired number

JDBC config that can be set with environment variables

Example docker run command for using a h2 database instead of postgresl

docker run -p 8101:8101 -p 8181:8181 -e 'JDBC_DRIVER_FEATURE=pax-jdbc-h2' -e 'JDBC_DRIVER_NAME=H2 JDBC Driver' -e 'JDBC_URL=jdbc:h2:tcp://somehost.company.com/~/sonar-collector' -e 'JDBC_USER=sa' -e JDBC_PASSWORD='' -d steinarb/sonar-collector:latest

Example values for some RDBMSes

he values that can be overridden by setting environment variables on container start, are
Variable	Default value	Description
JDBC_DRIVER_FEATURE	postgresql	Karaf feature to load the driver
JDBC_DRIVER_NAME	PostgreSQL JDBC Driver	Identify correct driver for OSGi service dependency injection
JDBC_URL	jdbc:postgresql:///sonar-collector
JDBC_USER		No default to make it possible to have no username
JDBC_PASSWORD		No default to make it possible to have no password

RDBMS type	Karaf feature	JDBC_DRIVER_NAME	example JDBC_URL	Default port
PostgreSQL	postgresql	PostgreSQL JDBC Driver	jdbc:postgresql://somehost.company.com/sonar-collector	5432
Derby (aka. JavaDB)	pax-jdbc-derby	derby	jdbc:derby://somehost.company.com:1527/sonar-collector	1527
H2	pax-jdbc-h2	H2 JDBC Driver	jdbc:h2:tcp://somehost.company.com/~/sonar-collector	9092
MSSQL	pax-jdbc-mssql	Microsoft JDBC Driver for SQL Server	jdbc:sqlserver://somehost.company.com:1433;databaseName=sonar-collector	1433
mariadb	pax-jdbc-mariadb	mariadb	jdbc:mariadb://somehost.company.com:3306/sonar-collector	3306
mysql	pax-jdbc-mysql	mysql	jdbc:mysql://somehost.company.com:3306/sonar-collector	3306

How to install and run the application on a debian server

(This procedure doesn't require development tools or building the project yourself. The servlet, and its attached karaf feature has been deployed to maven central)

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 install postgresql

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. Add a key for the apt repo containing the karaf package
#+BEGIN_EXAMPLE wget -O - https://apt.bang.priv.no/apt_pub.gpg | apt-key add - #+END_EXAMPLE
2. Add the repo containing karaf by adding the following lines to /etc/apt/sources.list :
#+BEGIN_EXAMPLE # APT archive for apache karaf deb http://apt.bang.priv.no/public stable main #+END_EXAMPLE
3. Install the debian package
#+BEGIN_EXAMPLE apt-get update apt-get install karaf #+END_EXAMPLE

Procedure:
1. SSH into karaf
#+BEGIN_EXAMPLE ssh -p 8101 karaf@localhost #+END_EXAMPLE The password is "karaf" (without the quotes)
2. 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-with-postgresql #+END_EXAMPLE

Using a database running on a different host

(the application has been installed into maven central, which is a repository that is builtin to karaf)

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 org.ops4j.datasource-sonar-collector config:property-set osgi.jdbc.driver.name "PostgreSQL JDBC Driver" config:property-set dataSourceName "jdbc/sonar-collector" config:property-set url "jdbc:postgresql://lorenzo.hjemme.lan/sonarcollector" config:property-set user "karaf" config:property-set password "karaf" config:property-set org.apache.karaf.features.configKey "org.ops4j.datasource-sonar-collector" 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.

Using a different database than PostgreSQL

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

WARNING! This is not regularily tested (i.e. won't be tested before releases) and I don't plan to actually use sonar-collector with anything except PostgreSQL myself.

To use JDBC against a RDBMS other than PostgreSQL, do the following from the karaf console command line (derby in-memory database used in the examples):
1. Load the component providing the DataSourceFactory OSGi service:
#+BEGIN_EXAMPLE feature:install pax-jdbc-derby #+END_EXAMPLE
2. Add karaf configuration selecting the correct DataSourceFactory and JDBC connection info (url, user and password):
#+BEGIN_EXAMPLE config:edit org.ops4j.datasource-sonar-collector config:property-set osgi.jdbc.driver.name "PostgreSQL JDBC Driver" config:property-set dataSourceName "jdbc/sonar-collector" config:property-set url "jdbc:derby:data/example/derby;create=true" config:property-set osgi.jdbc.driver.name derby config:property-set org.apache.karaf.features.configKey "org.ops4j.datasource-sonar-collector" config:update #+END_EXAMPLE
3. Load sonar-collector using a feature that doesn't unnecessarily pull in the PostgreSQL DataSourceFactory:
#+BEGIN_EXAMPLE feature:repo-add mvn:no.priv.bang.sonar.sonar-collector/sonar-collector-webhook/LATEST/xml/features feature:install sonar-collector-webhook-with-jdbc #+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

Development stuff

Testing and debugging

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

To run the servlet locally and debug into the servlet, the following software is required:
1. A locally installed apache karaf (see the apache karaf quick start guide )
2. A locally installed SonarQube (see SonarQube Get Started in Two Minutes )
3. A locally installed (or at least reachable, see Using a database running on a different host ) PostgreSQL database
4. An IDE that can do remote debugging

Preparation for debugging
1. [Create the database][create user and empty database in PostgreSQL]]
2. Add http://localhost:8181/sonar-collector as a webhook in SonarQube
3. Clone and build the sonar-collector
#+BEGIN_EXAMPLE mkdir -p ~/git cd ~/git/ git clone https://github.com/steinarb/sonar-collector.git cd ~/git/sonar-collector/ mvn clean install #+END_EXAMPLE
4. Start karaf with setup for remote debugging (cd to an unpacked downloaded karaf installation, start karaf as the user you used to do "mvn clean install")
#+BEGIN_EXAMPLE cd ~/Downloads/apache-karaf-4.1.4/ bin/karaf debug #+END_EXAMPLE
5. Install the sonar-collector in karaf, with the following commands in the karaf console:
#+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
6. Connect the IDE to a debugging connection on localhost port 5005 (see your IDE's documentation for this) and set the breakpoint at the desired code

Then just trigger an analysis in the locally installed SonarQube and debug when the breakpoint is triggered:

   mvn clean org.jacoco:jacoco-maven-plugin:prepare-agent package sonar:sonar -Dsonar.host.url=http://localhost:9000 -Dsonar.login=a51f2ab9a8790abd91773f0a7d2f6d2dc9d97975

Building the docker image

(as the sonar.login argument, use the token that SonarQube returns when using the setup wizard of the quick start)

Precondition: docker running on the build server

Procedure:
1. Move to the build directory:
#+begin_example cd docker/ #+end_example
2. Build the image:
#+begin_example mvn clean package docker:build #+end_example
3. Verify with "docker images" that the image has been rebuilt (if the CREATED column shows an old time the image probably hasn't been rebuilt):
#+begin_example sb@lorenzo:~/workspaces/ws02/sonar-collector/docker$ docker images REPOSITORY TAG IMAGE ID CREATED SIZE steinarb/sonar-collector latest 6c578e16f6e0 3 seconds ago 291MB sb@lorenzo:~/workspaces/ws02/sonar-collector/docker$ #+end_example