Custom Rules with variable search parameter and debtRemediationFunctionCoefficient about sonar-sql-plugin

[gretard]

Hi,

I would suggest starting by parsing each dml statement separately, at first find all Sql_clauseContext, then based on that look into children. Maybe something like this:

<ruleImplementation>
			<names>
				<textItem>Sql_clauseContext</textItem>
			</names>
			<childrenRules>
				<ruleImplementation>
					<names>
						<textItem>Select_statementContext</textItem>
						<textItem>Delete_statementContext</textItem>
						<textItem>Update_statementContext</textItem>
						<textItem>Merge_statementContext</textItem>
						<textItem>Insert_statementContext</textItem>
					</names>
					<childrenRules>
						<ruleImplementation>
							<names>
								<textItem>
									<textItem>Table_source_itemContext</textItem>
								</textItem>
							</names>
							<childrenRules>
								<ruleImplementation>
									<names>
										<textItem>With_table_hintsContext</textItem>
									</names>
									<indexCheckType>Default</indexCheckType>
									<distanceCheckType>Default</distanceCheckType>
									<ruleMatchType>ClassOnly</ruleMatchType>
									<ruleResultType>FailIfNotFound</ruleResultType>
									<textCheckType>Default</textCheckType>
								</ruleImplementation>
							</childrenRules>
							<ruleViolationMessage>test</ruleViolationMessage>
							<indexCheckType>Default</indexCheckType>
							<distanceCheckType>Default</distanceCheckType>
							<ruleMatchType>ClassOnly</ruleMatchType>
							<ruleResultType>Default</ruleResultType>
							<textCheckType>Default</textCheckType>
						</ruleImplementation>
					</childrenRules>
					<indexCheckType>Default</indexCheckType>
					<distanceCheckType>Default</distanceCheckType>
					<ruleMatchType>ClassOnly</ruleMatchType>
					<ruleResultType>Default</ruleResultType>
					<textCheckType>Default</textCheckType>
				</ruleImplementation>
			</childrenRules>
			<indexCheckType>Default</indexCheckType>
			<distanceCheckType>Default</distanceCheckType>
			<ruleMatchType>ClassOnly</ruleMatchType>
			<ruleResultType>Default</ruleResultType>
			<textCheckType>Default</textCheckType>
			<violatingRulesCodeExamples>
				<ruleCodeExample>Select DistributorID, FirstName, LastName From
					dbo.Distributor D;
				</ruleCodeExample>
				<ruleCodeExample>with cte as (Select DistributorID, FirstName,
					LastName From
					dbo.Distributor) select * from cte;
				</ruleCodeExample>
			</violatingRulesCodeExamples>
			<compliantRulesCodeExamples>
				<ruleCodeExample>Select DistributorID, FirstName, LastName From
					dbo.Distributor D WITH (NOLOCK);
				</ruleCodeExample>
				<ruleCodeExample>with cte as (Select DistributorID, FirstName,
					LastName From
					dbo.Distributor D WITH (NOLOCK)) select * from cte;
				</ruleCodeExample>
			</compliantRulesCodeExamples>
		</ruleImplementation>

[gretard]

And with regards to debtRemediationFunctionCoefficient - there is a catch that your custom rules are reported as adhoc and SonarQube does not support adding this. If there is a need for commercial plugin support you can ping me on gitter :)

[tgov01]

Thanks for your response back. After taking out the extra itemText node from Table_source_itemContext, and adding SkipIfNotFound to this, and changing With_table_hintsContext to Table_hintContext adding <textToFind>NoLock</textToFind>, I'm still not getting what I'm looking for.

I think that I'd need to start at the Table_source_itemContext because this is the item that I want highlighted. Ideally, for the sample below, I would want the ones marked with "Offending table not using NOLOCK" to be the offending items not using hint "With (NoLock)" to be violating the rules.

;With CTE As
(
	Select PK_FileType
	From dbo.FileType -- Offending table not using NOLOCK
)
Select PK_FileType From CTE

;With CTE As
(
	Select PK_FileType
	From dbo.FileType With (NoLock)
)
Select PK_FileType From CTE

Update dbo.FileType
Set PK_FileType = 1
From dbo.FileType F -- Should not be highlight because it is item being updated
	Inner Join dbo.FileAddress A -- Offending table not using NOLOCK
		On F.PK_FileType = A.PK_FileType

Update F
Set PK_FileType = 1
From dbo.FileType F -- Should not be highlight because it is item being updated
	Inner Join dbo.FileAddress A -- Offending table not using NOLOCK
		On F.PK_FileType = A.PK_FileType

Update dbo.FileType
Set PK_FileType = 1
From dbo.FileType F -- Should not be highlight because it is item being updated
	Inner Join dbo.FileAddress A With (NoLock)
		On F.PK_FileType = A.PK_FileType

Update F
Set PK_FileType = 1
From dbo.FileType F -- Should not be highlight because it is item being updated
	Inner Join dbo.FileAddress A With (NoLock)
		On F.PK_FileType = A.PK_FileType

[gretard]

Hi, I have actually found a small bug with uses rules. You can download snapshot version from (https://dev.azure.com/kgreta/sonar-sql-plugin/_build/results?buildId=75&view=results), for example this.

Then I would suggest trying something like this, otherwise I am thinking you might need to split rule into separate ones or raise Feature Request :) :

	<ruleImplementation>
			<names>
				<textItem>Table_source_itemContext</textItem>
			</names>
			<textToFind>
			</textToFind>
			<parentRules>
				<ruleImplementation>
					<names>
						<textItem>Join_partContext</textItem>
						<textItem>Select_statementContext</textItem>
					</names>
					<textToFind>
					</textToFind>
					<parentRules>
					</parentRules>
					<childrenRules>
						<ruleImplementation>
							<names>
								<textItem>With_table_hintsContext</textItem>
							</names>
							<textToFind>
							</textToFind>
							<parentRules>
							</parentRules>
							<childrenRules>
							</childrenRules>
							<siblingsRules>
							</siblingsRules>
							<usesRules>
							</usesRules>
							<ruleViolationMessage>HINTS</ruleViolationMessage>
							<times>0</times>
							<distance>0</distance>
							<index>0</index>
							<indexCheckType>Default</indexCheckType>
							<distanceCheckType>Default</distanceCheckType>
							<ruleMode />
							<ruleMatchType>ClassOnly</ruleMatchType>
							<ruleResultType>FailIfNotFound</ruleResultType>
							<textCheckType>Default</textCheckType>
						</ruleImplementation>
					</childrenRules>
					<siblingsRules>
					</siblingsRules>
					<usesRules>
					</usesRules>
					<ruleViolationMessage>MAINSELECTS</ruleViolationMessage>
					<times>0</times>
					<distance>0</distance>
					<index>0</index>
					<indexCheckType>Default</indexCheckType>
					<distanceCheckType>Default</distanceCheckType>
					<ruleMode />
					<ruleMatchType>ClassOnly</ruleMatchType>
					<ruleResultType>SkipIfNotFound</ruleResultType>
					<textCheckType>Default</textCheckType>
					<violatingRulesCodeExamples>
						<ruleCodeExample />
					</violatingRulesCodeExamples>
					<compliantRulesCodeExamples>
						<ruleCodeExample />
					</compliantRulesCodeExamples>
				</ruleImplementation>
			</parentRules>
			<childrenRules>
			</childrenRules>
			<siblingsRules>
			</siblingsRules>
			<usesRules>

				<ruleImplementation>
					<names>
						<textItem>IdContext</textItem>
					</names>
					<textToFind>
					</textToFind>
					<parentRules>

						<ruleImplementation>
							<names>
								<textItem>Common_table_expressionContext</textItem>
							</names>
							<textToFind>
							</textToFind>
							<parentRules>
							</parentRules>
							<childrenRules>
							</childrenRules>
							<siblingsRules>
							</siblingsRules>
							<usesRules>
							</usesRules>
							<ruleViolationMessage>CTE</ruleViolationMessage>
							<times>0</times>
							<distance>1</distance>
							<index>0</index>
							<indexCheckType>Default</indexCheckType>
							<distanceCheckType>Equals</distanceCheckType>
							<ruleMode />
							<ruleMatchType>Default</ruleMatchType>
							<ruleResultType>SkipIfFound</ruleResultType>
							<textCheckType>Default</textCheckType>
						</ruleImplementation>

					</parentRules>
					<childrenRules>
					</childrenRules>
					<siblingsRules>
					</siblingsRules>
					<usesRules>
					</usesRules>
					<ruleViolationMessage>ID</ruleViolationMessage>
					<times>0</times>
					<distance>0</distance>
					<index>0</index>
					<indexCheckType>Default</indexCheckType>
					<distanceCheckType>Default</distanceCheckType>
					<ruleMode />
					<ruleMatchType>Full</ruleMatchType>
					<ruleResultType>Default</ruleResultType>
					<textCheckType>Default</textCheckType>
				</ruleImplementation>
			</usesRules>
			<ruleViolationMessage>MAIN</ruleViolationMessage>
			<times>0</times>
			<distance>0</distance>
			<index>0</index>
			<indexCheckType>Default</indexCheckType>
			<distanceCheckType>Default</distanceCheckType>
			<ruleMode />
			<ruleMatchType>ClassOnly</ruleMatchType>
			<ruleResultType>Default</ruleResultType>
			<textCheckType>Default</textCheckType>
			<violatingRulesCodeExamples>
				<ruleCodeExample><![CDATA[
				;With CTE As
(
	Select PK_FileType
	From dbo.FileType 
)
Select PK_FileType From CTE
				]]>
				 </ruleCodeExample>
				 	<ruleCodeExample><![CDATA[

Update dbo.FileType
Set PK_FileType = 1
From dbo.FileType F 
	Inner Join dbo.FileAddress A 
		On F.PK_FileType = A.PK_FileType
				]]>
				 </ruleCodeExample>
				 	 
			</violatingRulesCodeExamples>
			<compliantRulesCodeExamples>
					<ruleCodeExample><![CDATA[
				;With CTE As
(
	Select PK_FileType
	From dbo.FileType   With (NoLock)
)
Select PK_FileType From CTE
				]]>
				 </ruleCodeExample>
				 
				<ruleCodeExample><![CDATA[
				Update F
Set PK_FileType = 1
From dbo.FileType F 
	Inner Join dbo.FileAddress A  With (NoLock)
		On F.PK_FileType = A.PK_FileType
				]]>
				 </ruleCodeExample>
			</compliantRulesCodeExamples>
		</ruleImplementation>

[tgov01]

I'm looking into this right now. Thanks again for your response. I'll post my findings.

[tgov01]

After updating to the 1.0.4 version that was provided...

2019-10-18T20:05:32.2444965Z 20:05:32.243 INFO: Load/download plugins (done) | time=242ms
2019-10-18T20:05:32.5481329Z 20:05:32.546 DEBUG: Plugins:
2019-10-18T20:05:32.5481965Z 20:05:32.547 DEBUG: * SonarCSS 1.1.1.1010 (cssfamily)
2019-10-18T20:05:32.5482269Z 20:05:32.547 DEBUG: * Svn 1.9.0.1295 (scmsvn)
2019-10-18T20:05:32.5482508Z 20:05:32.547 DEBUG: * SQL language plugin 1.0.4 (sql)

I'm getting the following error from SQ. Any ideas?

2019-10-18T20:05:40.8185465Z java.lang.NoClassDefFoundError: org/sonar/api/internal/apachecommons/lang/StringUtils
2019-10-18T20:05:40.8185767Z at org.antlr.sql.sca.nodes.ParseTreeNode.getUses(ParseTreeNode.java:104)
2019-10-18T20:05:40.8186053Z at org.antlr.sql.sca.ViolationsSearcher.visit(ViolationsSearcher.java:64)
2019-10-18T20:05:40.8186388Z at org.antlr.sql.sca.ViolationsSearcher.search(ViolationsSearcher.java:21)
2019-10-18T20:05:40.8186667Z at org.antlr.sql.sca.IssuesProvider.analyze(IssuesProvider.java:35)
2019-10-18T20:05:40.8186925Z at org.sonar.plugins.sql.fillers.IssuesFiller.fill(IssuesFiller.java:22)
2019-10-18T20:05:40.8187198Z at org.sonar.plugins.sql.sensors.SQLSensor$1.run(SQLSensor.java:83)
2019-10-18T20:05:40.8187471Z at java.base/java.util.concurrent.ForkJoinTask$RunnableExecuteAction.exec(ForkJoinTask.java:1409)
2019-10-18T20:05:40.8187767Z at java.base/java.util.concurrent.ForkJoinTask.doExec(ForkJoinTask.java:283)
2019-10-18T20:05:40.8188065Z at java.base/java.util.concurrent.ForkJoinPool.runWorker(ForkJoinPool.java:1603)
2019-10-18T20:05:40.8188909Z at java.base/java.util.concurrent.ForkJoinWorkerThread.run(ForkJoinWorkerThread.java:175)
2019-10-18T20:05:40.8189218Z Caused by: java.lang.ClassNotFoundException: org.sonar.api.internal.apachecommons.lang.StringUtils
2019-10-18T20:05:40.8189501Z at org.sonar.classloader.ParentFirstStrategy.loadClass(ParentFirstStrategy.java:39)
2019-10-18T20:05:40.8189780Z at org.sonar.classloader.ClassRealm.loadClass(ClassRealm.java:87)
2019-10-18T20:05:40.8190033Z at org.sonar.classloader.ClassRealm.loadClass(ClassRealm.java:76)
2019-10-18T20:05:40.8190275Z ... 10 common frames omitted

By the way... we are on SonarQube 7.9. My colleague wanted me to report this. Thanks.

[gretard]

Thanks for reporting this. This was fixed in https://github.com/gretard/sonar-sql-plugin/releases/tag/1.0.5