Sonarqube PLSQL Custom rule for detecting correct SQL terminator semicolon within a SQL script file

Question

I am using Jenkins and SONARQUBE PL/SQL plugin for Oracle SQL code analysis, I need to create Custom rules using XPATH for Quality Analysis of the SQL Script files that are sent for deployment over Jenkins.

I am trying to create a custom rule that detects if a semicolon (" ; ") is missing at the end of any SQL commands. SQL termination ("semicolon") is of importance for deploying SQL scripts with SQLPLUS.

example of code

 insert into table_name values('wait','for','completion'); -- compliant with script 
 insert into table_name values('somename','for','good'); -- compliant with script 
 **insert into table_name values('someplace','for','game')** -- non compliant as semicolon missing
 insert into table_name values('something','for','change'); -- compliant with script 
 delete from table_name ; -- compliant with script 
 delete from table_name ; -- compliant with script 
 update table_name set name='james' where id='22';

there is a insert query that is missing the semicolon , and hence sonarqube should detect this and fail the jenkins build or fail the SONAR Quality test.

please help creating the PLSQL custom rule for detecting correct SQL termination by semicolon.

example of xpath would be: /COMPILATION_UNIT/ANY_DML_EXPRESSION/following-sibling::SEMICOLON -- something like this

Answer 1

You could follow the guide "Create a plugin with custom rules", using the template project plsql-custom-rules.
That is more complex than adding a rule to XPATH, but you would have more control.

To create a check, you can create a subclass of org.sonar.plsqlopen.checks.AbstractBaseCheck.
You can use the org.sonar.check.Rule and org.sonar.squidbridge.annotations.SqaleConstantRemediation annotations to configure the check metadata (name, description, key...).

Very often you'll need to override just two methods:

• init(): subscribe to the desired grammar rules
• visitNode(AstNode): analyze the nodes that match the subscribed grammar rules

But first, as illustrated in issue 21, do check that your code does not error with an "Unable to parse file" message.

I just need to know a parser like sonarqube analysis to detect compilation errors in the script file

Check that your case is not an optional semicolon one, as in "Semicolon is not required in CREATE VIEW".
Looking at that source code is a good way to check how the parser like sonarqube analysis detects compilation errors in the script file.

Answer 2

We've been using it in the past, with a slight modification. You can start with a rule that implements the same logic here:

https://github.com/gretard/sonar-tsql-plugin/wiki/Custom-rules#creating-more-complex-custom-rule-with-distance