TSQL custom rule implementation

Hi - We are using sonarqube for sql script analysis(create table script, stored proc…etc…).
We have license for TSQL.
Trying to write a custom rule to check if the table has atleast one foriegn key. Can any one please help to write the rule and import into tsql rules?

Thanks
Nithiya

Hi Nithiya,

Welcome to the community!

Unfortunately, custom rules aren’t supported for T-SQL. Would you mind sharing what rules you think are missing?

 
Ann

1 Like

Thanks for your reply. As mentioned, would like to write a custom rule to check if the sql table has atleast one foreign key constraint defined.We will give the input as .tsql file for sonarqube to anlayze. This .tsql file is a script with create table and constraint auto generated from sql server.

Thanks
Nithiya

1 Like

Hi Ann - Can you please let me know if you have any details on it…

Thanks
Nithiya

Hi @Nithiya,

Thank you for suggesting this rule. This would be an “audit” rule, i.e. a rule warning the developer and asking him/her to check if this is what he/she really wants to do. We strive to have a different user experience where developers are only notified when we are sure that there is a problem. Developers tend to ignore every issue if too many of them are wrong.
Thus we sadly can’t add this rule to SonarQube.

One thing I forgot to mention. SonarQube has a generic import format. You could generate a report with a script and import the issues in SonarQube. The main drawback is that it makes the analysis more complex.

Thanks for the reply. We need to implement this rule for our business need. Can you please guide us how to proceed on this ourself.

Hi @Nithiya,

If you want to generate a report and import it in SonarQube using the “generic import format” you will need to implement a basic analyzer. If you have only one SQL query per file a regular expression searching for foreign keys should be enough.
You will have to run your script before running SonarQube analyzer. Then you set in your sonar-project.properties the property sonar.externalIssuesReportPaths to the path of your report. Finally you run SonarQube scanner.

Thanks Nicolas.I am a newbie to this. Can you please provide a sample with full steps need to be run to check a rule on .sql file which has create table script. Would be a great help in getting to know the steps to be followed in detail. Thanks a lot.

Hi @Nithiya,

I’m sorry but providing a script analyzing TSQL code would be equivalent to implementing a third party application. It goes beyond what I can provide on this forum. If you have any question regarding SonarQube documentation I’ll gladly answer them.

I suggest that as a first step you create manually a file, let’s name it report.txt, and write into it a generic report. You have an example in the doc I referenced above. Change the filePath and textRange to match an existing code file.
Then add sonar.externalIssuesReportPaths=report.txt in your sonar-project.properties and run the analyzer. You should see these issues appear in SonarQube.

Once you have all this working you can implement the script generating the report.txt file.