Help with custom PL/SQL rule

Dear G Ann Campbell, could you please explain me how custom rule works?
I’m trying to write custom rule for PL/SQL script.sql

I learned how SonarQube works with custom rules. Correct me please if I’m wrong. I use maven for scanning my project.

Let’s go in order:

  1. I made a sonar project
  2. I configured are: pom.xml, cacerts, token
  3. I made Quality profile, Custom rule for PL/SQL (XPath1.0)
  4. I run initial check for my stable branch with success response

My primary task is find first_value function without order by. For instances:

select NAME, first_value ( value ) over (partition by value) order by NAME - this is wrong query, because partition by haven’t order by construction between brackets.
I want to build correct XPath1.0 rule to find this one.

Another correct query is:
select NAME, first_value (some.lrc RESPECT NULLS ) over (partition by some order by lrc) order by NAME
I should be skipped by Sonar, cause the query has order by between open and closed brackets.

I used to check my XPath1.0 query. Unfortunately this resource works with XPath2.0 only. But I built regexp query which solve my task, it’s
Regexp is:
first_value\s{0,}\(((,?\s{0,},?\.?)(\w+)\s{0,}){1,4}\)(?=((,?\s{0,},?)over(,?\s{0,},?)\(((?!order(\s{1,})by).)+\))) it works well for XPath2.0, I sure, I checked it.

I know that Sonar get’s raw code, use pmd (programming mistake detector) to find mistakes, use proxy, use database to store results. I think is’t brief workflow.

I know that I can write java plugin to solve my task. In my case the Sonar is just service, another team administrating.
I also know that I can use Jenkins job for parsing file with grep, sed, awk for finding mistakes.

But I would like to figure out with my task with native Sonar’s capabilities.
I guess that I don’t fully understand how pmd translating raw PL/SQL code into XML. I also installed PMD Designer for making a XPath1.0 request but my PL/SQL code translates to a weird XML (look at pic.).

Dear G Ann Campbell, could you please help me with this case?


Sonar does not use PMD. We write our own language engines.

The docs recommend using XPath Tester and Evaluator online to test your XPath. They also provide links to download the SSLR Toolkit for some languages.