Analysis Rule
Analysis Rule Parameters
Analysis Rule Expression
XSLT Extensions
Tune And Test Rule Expressions
Analysis Rule
Use the Details tab to change rule name,scope,severity,message and description.
Analysis rules can be two types - Batch and Context Only rules. The Context Only rules can be used to only analyze current database schema. These kind of rules does not depend on the analyzed T-SQL code,but only on the current analysis context. Context Only rules are evaluated only once at the beginning of the analysis process.
The Batch analysis rules also have access to the analysis context,but are focused on the T-SQL code. These rules are evaluated with each analyzed T-SQL batch.
Rule severity affects the way the rule message appears in the SQL Enlight Error List.
Analysis Rule Parameters
Create parameters for the analysis rule using the Parameters tab.
A single analysis rule parameter can have none or several predefined values from which the user will be able to choose before running the Static Code Analysis command.
A parameter value can be referenced inside the analysis rule using the $parameters variable:
$parameters/Param[@Name='ParameterName']/text()
The Always require parameters to be set analysis rule option can be used to make the Analysis Parameters dialog always appear when the rule is applied. If this option is not checked, the default values of the parameters will be used.
Analysis Rule Expression
Analysis rule expression can be defined using the standard XSLT syntax,but with some restrictions to the allowed XSLT elements. The restrictions are meant to disallow output results which are not valid according the SQL Enlight analysis engine. Almost all elements of the XSLT standard syntax are allowed except elements which can directly manipulate the result data(like xsl:call-template,xsl:value-of,xsl:element,xsl:attribute and etc.). This kind of elements are allowed as child elements of xsl:varaiable,xsl:with-param and xsl:param elements. Simple analysis rule matching expression can be defined like this:<xsl:for-each select="/*[1]/g:batch">
<!-- Return rule violation information by calling the "output-message" template. -->
<xsl:call-template name="output-message">
<xsl:with-param name="line" select="@se:sline"/>
<xsl:with-param name="column" select="@se:scol"/>
<xsl:with-param name="msg" select="$v-rulename"/>
<xsl:with-param name="desc" select="concat($v-rulename,' : ',$v-ruledescription)"/>
<xsl:with-param name="near" select="text()"/>
<xsl:with-param name="type" select="$v-ruleseverity"/>
</xsl:call-template>
</xsl:for-each>
The call to the 'output-message' template is the only valid template call ( 'output-error' is still supported, but is obsolete and is not recommended).Its result is the details of the analysis rule along with the source string of the analysis rule violation which eventually to be marked in the code editor. To understand better how to implement analysis rules and see some examples you can check the rule expressions of the integrated analysis rules.
The $v-rulename,$v-ruledescription and $v-ruleseverity variables hold respectively the rule name,description and severity.
Use the Validate button to check the syntax of your analysis rule. The XML or XSLT errors messages will be displayed in the Error List and the error will be marked inside the analysis rule expression.
XSLT Extensions
SQL Enlight analysis rules support several XPath extension functions and modules in order to refine XPath queries and enhance the programming power and flexibility of XPath. The functions are divided into the following groups:EXSLT Extensions - http://www.exslt.org/
Date and time related functions:
Namespace: http://exslt.org/dates-and-times
Reference: http://www.exslt.org/date
Math functions:
Namespace: http://exslt.org/math
Reference: http://www.exslt.org/math/
Random number helper functions:
Namespace: http://exslt.org/random
Reference: http://www.exslt.org/random/
Regular expression functions:
Namespace: http://exslt.org/regular-expressions
Reference: http://www.exslt.org/regexp/
Node-sets manipulation functions:
Namespace: http://exslt.org/sets
Reference: http://www.exslt.org/set/
String helper functions:
Namespace: http://exslt.org/strings
Reference: http://www.exslt.org/str/
Microsoft XPath Extension Functions
Microsoft provided XPath extension functions:
Namespace: urn:schemas-microsoft-com:xslt
Reference: http://msdn.microsoft.com/en-us/library/ms256453.aspx
XPath Functions
Standard XPath functions:
Reference:http://msdn.microsoft.com/en-us/library/ms256453.aspx
Ubitsoft XPath Extension Functions
Namespace: urn:ubitsoft-exslt-strings:xslt
Function | Description |
|---|---|
| string str2:capiralize-case(string target) | Upper case first char of the string. |
| number str2:compare(string first, string second,boolean ignoreCase) | Performs string comparison. |
| boolean str2:contains(string source, string value,boolean ignoreCase) | Checks whether the first argument string contains the second argument string. |
| boolean str2:ends-with(string target, string value) | Returns true if the target string ends with the given value. |
| number str2:index-of(string target, string anyOf) | Reports the index of the first occurrence of any character of the anyOf string inside the target string. |
| number str2:index-of(string target, string anyOf, number startIndex) | Reports the index of the first occurrence of any character of the anyOf string inside the target string. The check starts at startIndex position in the target string. |
| number str2:index-of-string(string target, string value,boolean ignoreCase) | Returns the position of the first occurance of the value string inside the target string. |
| number str2:last-index-of(string target, string anyOf) | Reports the index of the last occurrence of any character of the anyOf string inside the target string. |
| number str2:last-index-of(string target, string anyOf, number startIndex) | Reports the index of the last occurrence of any character of the anyOf string inside the target string. The check starts at startIndex position in the target string. |
| number str2:last-index-of-string(string target, string value,boolean ignoreCase) | Returns the position of the last occurance of the value string inside the target string. |
| string str2:lower-case(string target) | Converts string to lower case. |
| boolean str2:starts-with(string target, string value) | Checks if the target strings starts with the given value string. |
| string str2:upper-case(string target) | Converts string to upper case. |
Namespace: urn:ubitsoft-exslt-sql:xslt
Function | Description |
|---|---|
| string sql:delimit-identifier(string name) | Create square brackets delimited single part identifier. |
| string sql:delimit-multipart-identifier(string name) | Create multipart delimited identifier. |
| string sql:escape-bracket(string s) | Escape sql closing bracket. |
| string sql:escape-quotes(string s) | Escape quotes. |
| string sql:undelimit-identifier(string name) | Undelimit identifier. |
| string sql:unescape-bracket(string s) | Unescape bracket. |
| string sql:unescape-quotes(string s) | Unescape quotes. |
Namespace: urn:ubitsoft-exslt-context:xslt
Function | Description |
|---|---|
| object cmn:if-true-else(boolean condition,object true-result,object false-result) | Helper extension method that to return one of the two values depending on whether the condition is true or false. |
Namespace: urn:ubitsoft-exslt-common:xslt
Function | Description |
|---|---|
| node-set ctx:execute-query(string server, string sql) | Executes the provided SQL code and returns result as node set. The extension method uses Integrated Security to connect to the database and requires that the query uses the FOR XML clause. The script is executed inside a explicit transaction having isolation level READ UNCOMMITTED. |
| node-set ctx:execute-query(string server, string database, string user, string password, string sql) | Executes the provided SQL code and returns result as node set. The extension method uses SQL Server Authentication to connect to the server and requires that the query uses the FOR XML clause. The script is executed inside a explicit transaction having isolation level READ UNCOMMITTED. |
| number ctx:execute-non-query(string server, string sql) | Executes the provided SQL code and returns the number of affected records. The extension method uses Integrated Security to connect to the server and requires that the query uses the FOR XML clause. The script is executed without explicit transaction. |
| number ctx:execute-non-query(string server, string database, string user, string password, string sql) | Executes the provided SQL code and returns the number of affected records. The extension method uses SQL Server Authentication to connect to the server and requires that the query uses the FOR XML clause. The script is executed without explicit transaction. |
| node-set ctx:execute-show-plan(node-set batch,string server,string database, string sql) | Returns the XML query execution plan for the provided SQL code as node set. The extension method uses Integrated Security to connect to the database and stes the SHOWPLAN_XML ON option before executing the SQL code. The script is executed inside a explicit transaction having isolation level READ UNCOMMITTED. The changes made inside the transaction are not commited, but discarded. |
| node-set ctx:execute-show-plan(node-set batch,string server, string database, string user, string password, string sql) | Returns the XML query execution plan for the provided SQL code as node set. The extension method uses SQL Server Authentication to connect to the database and stes the SHOWPLAN_XML ON option before executing the SQL code. The script is executed inside a explicit transaction having isolation level READ UNCOMMITTED. The changes made inside the transaction are not commited, but discarded. |
| node-set ctx:execute-show-plan(node-set batch,string server,string database, string sql,string mode) |
Returns the XML query execution plan for the provided SQL code as node set. Because the CREATE/ALTER PROCEDURE / VIEW T-SQL statements do not produce XML execution plans, the 'mode' parameter is provided to control whether the execute-show-plan function to attempt rewriting these statements in a way that it to be possible for an execution plan to be generated. The rewriting basically extracts the statement body (e.g. the SELECT statement from CREATE VIEW or the stored procedure body from CREATE PROCEDURE) and uses it to get the XML plan. The 'mode' parameter can have the following values:
The extension method uses Intnegrated Security to connect to the database and stes the SHOWPLAN_XML ON option before executing the SQL code. The script is executed inside a explicit transaction having isolation level READ UNCOMMITTED. The changes made inside the transaction are not commited, but discarded. |
| node-set ctx:execute-show-plan(node-set batch,string server, string database, string user, string password, string sql,string mode) |
Returns the XML query execution plan for the provided SQL code as node set. Because the CREATE/ALTER PROCEDURE / VIEW T-SQL statements do not produce XML execution plans, the 'mode' parameter is provided to control whether the execute-show-plan function to attempt rewriting these statements in a way that it to be possible for an execution plan to be generated. The rewriting basically extracts the statement body (e.g. the SELECT statement from CREATE VIEW or the stored procedure body from CREATE PROCEDURE) and uses it to get the XML plan. The 'mode' parameter can have the following values:
The extension method uses Intnegrated Security to connect to the database and stes the SHOWPLAN_XML ON option before executing the SQL code. The script is executed inside a explicit transaction having isolation level READ UNCOMMITTED. The changes made inside the transaction are not commited, but discarded. |
Tune And Test Rule Expressions
Analysis Context
Analysis rules designer comes by default with the analysis context of the AdventureWorks database which is included in the SQL Server 2005/2008 samples. You can use this context to test your analysis rules.
You can provide your own analysis context in two steps:
1. Generate analysis context XML file using the command line tool .
2. Configure the newly generated file as default analysis context at the Settings->Analysis Settings-> General tab-> Test database context.
Use XPath expression text box in order to test different XPath expression before using them in the analysis rule. Evaluating the rule will result either nodes to be selected or resulting value to be shown in the error results list.
Use the Tree View to examine the Analysis Context and also generate sample XPath expression to the selected node.
Analysis context can be accessed inside the rune expression through the $context variable.
The current context database can be accessed with the $database variable.
Example:
<xsl:value-of select="$context/Database[1]/Tables/Table[1]/@Name"/> <xsl:value-of select="$database/Tables/Table[1]/@Name"/>
For more information of the XML schema of the analysis context, check the \Schemas\AnalysisContext.xsd or the provided with SQL Enlight test analysis context of the AdventureWorks database.
The default test analysis context can be changed from the SQL Enlight->Options->Settings tab->Analysis Settings.
Test SQL
When creating an analysis rule you can test it against real T-SQL script and see if it gives expected result. You can also use the XML view of the T-SQL script to check and tune your analysis rule.
Use the Test Rule button to run the analysis rule over the test SQL script and see if the rule is evaluating correctly.
