PL/SQL Cop for SonarQube 7.0

blank

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:

blank

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.

blank

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.

blank

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

blank

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

blank

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.

8 Comments

  1. blank Jhon says:

    Can you share this docker image? Because when i try to run sonar-scanner itg gettin error and in debug mod i could not find any clue.

  2. blank Jhon says:

    Ok, i found the problem, in tvdcc.cmd file the java_home was wrong i changed it and it work.

    @echo off
    set TVDCC_HOME=%~dp0
    set JAVA_HOME=C:\Program Files\Java\jre1.8.0_77
    for %%X in (java.exe) do (set FOUND=%%~$PATH:X)
    if not defined FOUND (
    set JAVA_EXE=\bin\java.exe
    for %%i in (“%JAVA_HOME%”) do if not exist “%%~si\%JAVA_EXE%” (
    echo Cannot find “%JAVA_HOME%%JAVA_EXE%”, please set JAVA_HOME in tvdcc.cmd manually
    goto endTVDCC
    )
    ) else (
    set JAVA_HOME=
    set JAVA_EXE=java
    )

    :startTVDCC
    “%JAVA_HOME%”%JAVA_EXE% -Xmx1024m -jar %TVDCC_HOME%tvdcc.jar %*

    :endTVDCC

     

    Also if its already determined below script can be usable.

     

    @echo off
    set TVDCC_HOME=%~dp0
    set JAVA_EXE=\bin\java.exe
    :startTVDCC
    “%JAVA_HOME%”%JAVA_EXE% -Xmx1024m -jar %TVDCC_HOME%tvdcc.jar %*

    :endTVDCC

     

  3. blank Jhon says:

    What about opensourcing the plugin, are there any future plan?

  4. blank Faye says:

    The result of the scan was not able to published on my SonarQube UI. We have plans to buy this plugin but it is important that we should see first the results of analysis on our Sonar dashboard

    • Hi Faye,

      Could you please open an issue here: https://github.com/Trivadis/plsql-cop-sonar/issues/new ?

      It helps if you provide some details, e.g.
      – OS where you are running SonarQube,
      – Version of SonarQube
      – Version of PL/SQL Cop for SonarQube plugin you’ve installed
      – Directory where you have installed PL/SQL Cop CLI
      – Version of the PL/SQL Cop CLI
      – Screenshot of the configuration screen in SonarQube
      – Version of SonarScanner you are using
      – Output of SonarScanner

      Thank you.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.