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:
- I made a sonar project
- I configured are:
pom.xml, cacerts, token - I made Quality profile, Custom rule for PL/SQL (XPath1.0)
- I run initial check for my
stablebranch 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 xpather.com 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 https://regex101.com/r/WcQ1yl/1
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?
