Repository files navigation

db* CODECOP supports custom validators. A validator must implement thePLSQLCopValidator Java interface and has to be a direct or indirect descendant of thePLSQLValidator class. Such a class can be used in the command line utility and the SQL Developer extension.

For SonarQube aValidationConfig is required. A config defines the validator with its rules and quality profile for SonarQube. SeeGLPValidatorConfig. The referenced XML files are generated based on the validator and the optionalsample guidelines.

You may use these validators as is or amend/extend them to suit your needs.

This project provides the following four custom validators in the packagecom.trivadis.tvdcc.validators:

This validator implements 15 guidelines to cover the chapter2.2 Naming Conventions of the Trivadis PL/SQL & SQL Coding Guidelines.

These regular expressions can be customized by using aTrivadisPlsqlNaming.properties file. This file must be placed in the user's home directory ($HOME for Linux or macOS and%HOMEDRIVE%%HOMEPATH% for Windows). If a property is omitted it will fall back to the default value (see table above). Furthermore, you can use Java system properties to configure the naming conventions, e.g.-DREGEX_CONSTANT_NAME=^k_.+$. However, theTrivadisPlsqlNaming.properties file overrides system properties that where set when starting the JVM.

Here's an example of theTrivadisPlsqlNaming.properties file content using default values for all properties:

# Override default for TrivadisPlsqlNaming validatorREGEX_GLOBAL_VARIABLE_NAME = ^g_.+$REGEX_LOCAL_VARIABLE_NAME = ^l_.+$REGEX_CURSOR_NAME = ^c_.+$REGEX_RECORD_NAME = ^r_.+$REGEX_ARRAY_NAME = ^t_.+$REGEX_OBJECT_NAME = ^o_.+$REGEX_CURSOR_PARAMETER_NAME = ^p_.+$REGEX_IN_PARAMETER_NAME = ^in_.+$REGEX_OUT_PARAMETER_NAME = ^out_.+$REGEX_IN_OUT_PARAMETER_NAME = ^io_.+$REGEX_RECORD_TYPE_NAME = ^r_.+_type$REGEX_ARRAY_TYPE_NAME = ^t_.+_type$REGEX_EXCEPTION_NAME = ^e_.+$REGEX_CONSTANT_NAME = ^co_.+$REGEX_SUBTYPE_NAME = ^.+_type$# Override defaults for TrivadisGuidelines3 validatorcop.1050.threshold = 2cop.2185.threshold = 4cop.2410.boolean.strings = true,false,t,f,0,1,2,yes,no,y,n,ja,nein,j,si,s,oui,non,o,l_true,l_false,co_true,co_false,co_numeric_true,co_numeric_falsecop.5050.threshold.from = 20000cop.5050.threshold.to = 20999cop.7210.threshold = 2000

If you are using theTrivadisGuidelines3Plus validator, the properties for theTrivadisGuidelines3 validator can also be configured in this properties file.

This is a simple validator to check the following naming convention guidelines:

This validator checks just these three guidelines. It does not extend theTrivadis PL/SQL & SQL Coding Guidelines.

This validator implements the following guideline:

It looks for unasserted parameters used inEXECUTE IMMEDIATE statements andOPEN FOR statements. All parameters used in these statements must be asserted with one of the subprograms provided byDBMS_ASSERT.

Seeexample

This validator implements the following guidelines:

Only the first comment containing hints is considered by the optimizer, therefore all hints violatingG-9600 are treated as ordinary comments by the Oracle Database.

Using unknown hints might invalidate all subsequent hints. This happens when you use for exampleNOLOGGING. That's expected and not a bug. See MOS note 285285.1 or bug 8432870 for details. So, do not ignoreG-9601 violations.

There are various hints that reference a table. The validator checks if the reference is valid. If an alias is defined for a table, but the table name is used in the hint then aG-9602 violation is reported. If the table reference in the hint is neither a table name nor an alias then aG-9603 violation is thrown. These violations should not be ignored either.

However, the vadiator ignores the optional query block in the hint and assumes that all table references belong to the current query block. As a result some false positives are possible. Furthermore references to a table in a query block (e.g.emp@qb1) are not checked. This might lead to some false negatives.

The guidelinesG-9604 andG-9605 check the validity of themethod andkeyword in thetable_stats hint.

This validator shows how existing guideline checks can be overridden.

The following guideline is overriden:

Literals as part of aLogger package call are not reported (see alsoissue 8).

This validator combines the validators

• TrivadisPlsqlNaming,
• SQLInjection,
• Hint and
• OverrideTrivadisGuidelines

This way you can deal with an unbound number of validators without comproming the maintainablity.

1. Download db* CODECOP Command Line

   Download db* CODECOP Command Line fromhere.

2. Install db* CODECOP Command Line

   • Uncompress the distributed db* CODECOP archive file (e.g. tvdcc-4.x.x.zip) into a folder of your choice (hereinafter referred to asTVDCC_HOME). I use$HOME/tvdcc forTVDCC_HOME on my MacBook Pro.

   • For Windows platforms only: Amend the settings for JAVA_HOME in the tvdcc.cmd file to meet your environment settings. Use at least a Java 8 runtime environment (JRE) or development kit (JDK).

   • IncludeTVDCC_HOME in your PATH environment variable for handy interactive usage.

   • Optionally copy your commercial license file into theTVDCC_HOME directory. For simplicity name the file tvdcc.lic.

3. Download Custom Validators

   Downloadsonar-plsql-cop-custom-validators-plugin-4.x.x.jar fromhere.

4. Install Custom Validators

   Copy the previously downloaded jar file into theplugin folder of yourTVDCC_HOME folder.

5. Run db* CODECOP with a Custom Validator

   Open a terminal window, change to theTVDCC_HOME directory and run the following command to all files in$HOME/github/utPLSQL/source with the custom validatorcom.trivadis.tvdcc.validators.TrivadisGuidelines3Plus:

   ./tvdcc.sh path=$HOME/github/utPLSQL/source validator=com.trivadis.tvdcc.validators.TrivadisGuidelines3Plus

   Thetvdcc_report.html file contain the results. Here's an excerpt:

   

1. Install db* CODECOP Command Line

   As explainedabove.

2. Download db* CODECOP for SQL Developer

   Download db* CODECOP for SQL Developer fromhere.

3. Install db* CODECOP for SQL Developer

   • Start SQL Developer
   • SelectCheck for Updates… in the help menu.
   • Use theInstall From Local File(s) option to install the previously downloadedTVDCC_for_SQLDev-*.zip file.
   • Restart SQL Developer

4. Configure Validator

   Configure the validator in SQL Developer as shown in the following screenshot:

   

5. Check Code

   Open the code to be checked in an editor and selectCheck from the context menu.

   

   The check result is shown by default at the bottom of your SQL Developer workspace.

   

1. Install SonarQube

   See documentation.

2. Install Standalone or Secondary Plugin

   See documentation.

3. Install Child Plugin (Custom Validator Plugin)

   Download thesonar-plsql-cop-custom-validators-plugin-x.x.x.jar fromreleases. Then copy it to the extensions/plugins folder of yourSonarQube installation and restart the SonarQube server.

4. Configure Validator Config Class

   Login as Administrator in SonarQube. Go toAdministration. SelectGeneral Settings fromConfiguration and click ondb* CODECOP. Type one of the following into theValidator Config class field:

   • com.trivadis.sonar.plugin.GLPValidatorConfig
   • com.trivadis.sonar.plugin.TrivadisGuidelines3PlusValidatorConfig

5. Restart SonarQube

   SelectSystem in theAdministration menu and click onRestart Server.

   Important: Starting with SonarQube 9.1, rules and quality profiles are cached to improve the startup time (seeSONAR-15237). To apply changes toLanguage Key andValidator Config class, the PL/SQL specific plugins must be uninstalled and then reinstalled. Configuration changes are not affected by this process.

6. Run Analysis

   Start an analysis from the command line as follows (seedocs for more information):

   cd $HOME/github/trivadis/plsql-cop-validators/src/main/resourcessonar-scanner -Dsonar.projectKey="plsql-cop-validators"

   By default the source code in the current directory is analyzed. Here's the result:

   

1. Install db* CODECOP Command Line

   As explainedabove.

2. Install Maven

   Download and install Apache Maven 3.8.6

3. Clone the cop-validators repository

   Clone or download this repository.

4. Install the required db* CODECOP libraries

   These libraries are not available in public Maven repositories. However, they are partof the db* CODECOP Command Line installed in step 1. You need to install these librariesinto your local Maven repository.

   Open a terminal window in the cop-validators root folder and run Run the following shell script:

   ./install_tvdcc_libs.sh

   The shell script expects to find the librarytvdcc.jar in$HOME/tvdcc. If it is not there, pass the path to the directory as parameter to this script. For example

   ./install_tvdcc_libs.sh $HOME/tvdcc

5. Build validator jar file

   Open a terminal window in the cop-validators root folder and run the Maven build by the following command

    mvn clean package

The db* CODECOP Validators are licensed under the Creative Commons Attribution-NonCommercial-NoDerivs 3.0 Unported License. You may obtain a copy of the License athttps://creativecommons.org/licenses/by-nc-nd/3.0/.