7

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

Jon Heller
  • 34,999
  • 6
  • 74
  • 132
aejaz
  • 105
  • 1
  • 10
  • I would simply wrap everything with `BEGIN END` block – Lukasz Szozda Jun 05 '18 at 15:25
  • I need to write XPATH for detecting any semicolon missing in the SQL Script file , for the Sonarqube analysis – aejaz Jun 05 '18 at 15:26
  • @lad2025 this isn't just for PLSQL ,plain SQL commands don't have begin end blocks ! – aejaz Jun 05 '18 at 15:27
  • @aejaz I'm not familiar with SonarQube, but for any parsing program that seems like a difficult requirement. In your examples it's obvious that the commands should end and need a semicolon. But there are many cases where it would be ambiguous. For example: `select * from dual begin null; end; /` In that case, the `begin` could be either an alias for the table or the beginning of a PL/SQL block. You may need to limit your requirement and instead simply report on any errors where the program can't be parsed. – Jon Heller Jun 05 '18 at 21:26
  • @JonHeller I simply need a sql script parser tool , which analysis my script file for any incorrect sql terminator (semicolon) related issues. just like SQLPLUS or Toad does it . – aejaz Jun 05 '18 at 23:01
  • @aejaz I believe this problem is incredibly difficult, if not theoretically impossible. How can the parser know when a semicolon is missing instead of some other error? Take this code for example: `select * from a select * from b;`. What's missing in the middle of that code - is it a semicolon, or a `union all`? Even this tiny amount of parsing is incredibly difficult to get right. I fear the best you'll get is to find a generic compilation error. But hopefully someone will answer this question and prove me wrong. – Jon Heller Jun 06 '18 at 02:10
  • @JonHeller yes, even a generic compilation error would be ok , I just need to know a parser like sonarqube analysis to detect compilation errors in the script file. – aejaz Jun 06 '18 at 07:51

2 Answers2

3

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.

VonC
  • 1,262,500
  • 529
  • 4,410
  • 5,250
0

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

Stan E
  • 3,396
  • 20
  • 31