PL/SQL Cop for SonarQube 7.0

For the last two months, my Trivadis colleague Daniel Schutzbach and I have been working on the PL/SQL Cop plugin for SonarQube. The goal was to support the most recent SonarQube versions 5.6 LTS, 6.7 LTS and 7.0. Dani was doing the heavy lifting and my job was testing and minor bug fixing. Today I can proudly announce that we were successful and that we have released the following three plugins:

• PL/SQL Cop for SonarQube 4.5 LTS (tested with SonarQube 4.5, 4.5.7 and 5.1.2)
• PL/SQL Cop for SonarQube 5.6 LTS (tested with SonarQube 5.6, 5.6.7, 6.0, 6.1, 6.2, 6.3, 6.3.1, 6.4, 6.5 and 6.6)
• PL/SQL Cop for SonarQube 6.7 LTS (tested with SonarQube 6.7, 6.7.1, 6.7.2 and 7.0)

In this blog post, I show how to set up a new SonarQube 7.0 server using Docker and analyze a PL/SQL project on my local machine with SonarQube Scanner. This post is a stripped-down version of my Continuous Code Quality for PL/SQL with Docker post. I assume you know about Docker and have it installed on your machine.

Here is the table of contents of the major steps.

1. Create SonarQube Container
2. Install PL/SQL Cop for SonarQube
3. Install PL/SQL Cop (Command Line Utility)
4. Install SonarQube Scanner
5. Configure SonarQube
6. Analyze a PL/SQL Project
7. View the Result in SonarQube
8. Summary

1. Create a SonarQube Container

In this step, I create a standalone container for SonarQube 7.0 using defaults to keep it simple.

docker run -d --name sq7 -p 9000:9000 sonarqube:7.0

2. Install PL/SQL Cop for SonarQube

To install the current version of PL/SQL Cop for SonarQube within the “sq7” container run

docker exec sq7 wget --no-check-certificate \
   https://www.salvis.com/blog?ddownload=8167 \
   -O /opt/sonarqube/extensions/plugins/sonar-plsql-cop-plugin-6.7.0.0.jar

The wget command will be executed within the “sq7” container. Windows user have to replace the “\” with “^” when using CMD or with “`” when using PowerShell.

To load the plugin we need to restart the container.

docker restart sq7

We will complete the installation in step 5.

3. Install PL/SQL Cop (Command Line Utility)

Download PL/SQL Cop and unzip the downloaded file in a directory of your choice. I’ve installed it on my local machine in “/usr/local/bin/tvdcc”.

4. Install SonarQube Scanner

Download SonarQube Scanner and unzip the downloaded file in a directory of your choice. I’ve installed it on my local machine in “/usr/local/opt/sonar-scanner“.

5. Configure SonarQube 7.0

Open “http://localhost:9000” in your web browser and log in with username “admin” and password “admin”.

SonarQube asks you to provide a token name. Enter “cop” and press “Generate” and then “Continue” on the next page. Then the token name and the token will be shown in the upper right corner of the screen as follows:

Copy your token text (39d483241393ddd5600e9c9348ced410c7903c1a) to the clipboard and store it somewhere. We will need it in step 6. Press “Skip this tutorial” in the upper right corner.

Click on “Administration” and the Category “Trivadis PL/SQL Cop” and change the “Path to PL/SQL Cop command line tvdcc executable” to the path according to step 3. In my case this is “/usr/local/bin/tvdcc/tvdcc.sh”. Press “Save” and you are done.

6. Analyze a PL/SQL Project

Create a temporary directory (in my case “/Users/phs/demo” and type the following

git clone https://github.com/PhilippSalvisberg/plscope-utils.git

This will clone the plscope-utils git repository. If you do not have Git installed you may download the repository as a zip file and extract it.

Run the following command to analyze the PL/SQL packages of this project:

cd plscope-utils/database/utils/package
sonar-scanner \
   -Dsonar.projectKey=plscope-utils:main \
   -Dsonar.sources=. \
   -Dsonar.login=39d483241393ddd5600e9c9348ced410c7903c1a

Windows users have to replace the “\” with “^” when using CMD or with “`” when using PowerShell.

7. View the Result in SonarQube

Open “http://localhost:9000” in your web browser.

Click on “plscope-utils:main”,  select the “Issues” tab for this project and select all rules.

Click on an issue to see the source code line causing this issue.

8. Summary

Setting up a SonarQube 7.0 server with Docker is no big deal. Installing the PL/SQL Cop plugin is simple as well. However, I have only shown the minimum configuration. For real projects, you will spend some time configuring your quality profiles and quality gates. A CI environment might help you to implement a fast quality feedback loop.

The audio-less video summarizes the major installation and configuration steps. I hope this will encourage you to try PL/SQL Cop.