public override IList <SqlRuleProblem> Analyze(SqlRuleExecutionContext ruleExecutionContext)
{
var problems = new List <SqlRuleProblem>();
var sqlObj = ruleExecutionContext.ModelElement;
if (sqlObj == null || sqlObj.IsWhiteListed())
{
return(problems);
}
var fragment = ruleExecutionContext.ScriptFragment.GetFragment(ProgrammingAndViewSchemaTypes);
var whereClauseVisitor = new WhereClauseVisitor();
fragment.Accept(whereClauseVisitor);
foreach (var whereClause in whereClauseVisitor.Statements)
{
var booleanComparisonVisitor = new BooleanComparisonVisitor();
whereClause.Accept(booleanComparisonVisitor);
var offenders = booleanComparisonVisitor.NotIgnoredStatements(RuleId)
.Where(c =>
c.ComparisonType == BooleanComparisonType.NotEqualToBrackets ||
c.ComparisonType == BooleanComparisonType.NotEqualToExclamation);
var sqlObjName = ruleExecutionContext.GetObjectName(sqlObj);
problems.AddRange(offenders.Select(t => new SqlRuleProblem(Message, sqlObj, t)));
}
return(problems);
}
public override IList <SqlRuleProblem> Analyze(SqlRuleExecutionContext ruleExecutionContext)
{
var problems = new List <SqlRuleProblem>();
var sqlObj = ruleExecutionContext.ModelElement; //proc / view / function
try
{
if (sqlObj != null)
{
var fragment = ruleExecutionContext.ScriptFragment;
//get the combined parameters and declare variables into one searchable list
var variablesVisitor = new VariablesVisitor();
fragment.AcceptChildren(variablesVisitor);
var variables = variablesVisitor.GetVariables();
var selectStatementVisitor = new SelectStatementVisitor();
fragment.Accept(selectStatementVisitor);
foreach (var select in selectStatementVisitor.Statements)
{
var query = select.QueryExpression as QuerySpecification;
if (query != null && query.WhereClause != null)
{
var booleanComparisonVisitor = new BooleanComparisonVisitor();
query.WhereClause.Accept(booleanComparisonVisitor);
foreach (var comparison in booleanComparisonVisitor.Statements)
{
var datatype1 = GetDataType(sqlObj, query, comparison.FirstExpression, variables);
if (string.IsNullOrEmpty(datatype1))
{
continue;
}
var datatype2 = GetDataType(sqlObj, query, comparison.SecondExpression, variables);
if (string.IsNullOrEmpty(datatype2))
{
continue;
}
//when checking the numeric literal I am not sure if it is a bit or tinyint.
if ((_comparer.Equals(datatype1, "bit") && _comparer.Equals(datatype2, "tinyint")) || (_comparer.Equals(datatype1, "tinyint") && _comparer.Equals(datatype2, "bit")))
{
continue;
}
if (!_comparer.Equals(datatype1, datatype2))
{
string msg = string.Format(Message, sqlObj.ObjectType.Name, RuleUtils.GetElementName(ruleExecutionContext, sqlObj));
problems.Add(new SqlRuleProblem(msg, sqlObj, comparison));
}
}
}
}
}
}
catch (System.Exception ex)
{
//TODO: PROPERLY LOG THIS ERROR
Debug.WriteLine(ex.ToString());
//throw;
}
return(problems);
}
/// <summary>
/// Performs analysis and returns a list of problems detected
/// </summary>
/// <param name="ruleExecutionContext">Contains the schema model and model element to analyze</param>
/// <returns>
/// The problems detected by the rule in the given element
/// </returns>
public override IList <SqlRuleProblem> Analyze(SqlRuleExecutionContext ruleExecutionContext)
{
var problems = new List <SqlRuleProblem>();
var sqlObj = ruleExecutionContext.ModelElement; //proc / view / function
if (sqlObj == null || sqlObj.IsWhiteListed())
{
return(problems);
}
try
{
var fragment = ruleExecutionContext.ScriptFragment.GetFragment(ProgrammingAndViewSchemaTypes);
//get the combined parameters and declare variables into one search-able list
var variablesVisitor = new VariablesVisitor();
fragment.AcceptChildren(variablesVisitor);
var variables = variablesVisitor.GetVariables();
var selectStatementVisitor = new SelectStatementVisitor();
fragment.Accept(selectStatementVisitor);
foreach (var select in selectStatementVisitor.Statements)
{
if (select.QueryExpression is QuerySpecification query && query.WhereClause != null)
{
var booleanComparisonVisitor = new BooleanComparisonVisitor();
query.WhereClause.Accept(booleanComparisonVisitor);
foreach (var comparison in booleanComparisonVisitor.Statements)
{
if ((comparison.FirstExpression is NullLiteral || comparison.SecondExpression is NullLiteral) &&
(comparison.ComparisonType == BooleanComparisonType.Equals ||
comparison.ComparisonType == BooleanComparisonType.NotEqualToBrackets ||
comparison.ComparisonType == BooleanComparisonType.NotEqualToExclamation) //probably can remove the ComparisonTypeCheck
)
{
problems.Add(new SqlRuleProblem(Message, sqlObj, comparison));
}
}
}
}
}
catch (System.Exception ex)
{
//TODO: PROPERLY LOG THIS ERROR
Debug.WriteLine(ex.ToString());
//throw;
}
return(problems);
}
public override IList <SqlRuleProblem> Analyze(SqlRuleExecutionContext ruleExecutionContext)
{
var problems = new List <SqlRuleProblem>();
var sqlObj = ruleExecutionContext.ModelElement;
if (sqlObj == null || sqlObj.IsWhiteListed())
{
return(problems);
}
var fragment = ruleExecutionContext.ScriptFragment.GetFragment(ProgrammingAndViewSchemaTypes);
var selectStatementVisitor = new SelectStatementVisitor();
fragment.Accept(selectStatementVisitor);
foreach (var select in selectStatementVisitor.Statements)
{
var booleanCompareVisitor = new BooleanComparisonVisitor();
select.Accept(booleanCompareVisitor);
var offenders =
from cmp in booleanCompareVisitor.NotIgnoredStatements(RuleId)
where TestCompare(cmp)
select cmp;
problems.AddRange(offenders.Select(t => new SqlRuleProblem(Message, sqlObj, t)));
}
var actionStatementVisitor = new ActionStatementVisitor();
fragment.Accept(actionStatementVisitor);
foreach (var action in actionStatementVisitor.Statements)
{
var booleanCompareVisitor = new BooleanComparisonVisitor();
action.Accept(booleanCompareVisitor);
var offenders =
from cmp in booleanCompareVisitor.NotIgnoredStatements(RuleId)
where TestCompare(cmp)
select cmp;
problems.AddRange(offenders.Select(t => new SqlRuleProblem(Message, sqlObj, t)));
}
return(problems);
}
/// <summary>
/// Performs analysis and returns a list of problems detected
/// </summary>
/// <param name="ruleExecutionContext">Contains the schema model and model element to analyze</param>
/// <returns>
/// The problems detected by the rule in the given element
/// </returns>
public override IList <SqlRuleProblem> Analyze(SqlRuleExecutionContext ruleExecutionContext)
{
var problems = new List <SqlRuleProblem>();
var sqlObj = ruleExecutionContext.ModelElement;
if (sqlObj == null || sqlObj.IsWhiteListed())
{
return(problems);
}
var fragment = ruleExecutionContext.ScriptFragment.GetFragment(ProgrammingAndViewSchemaTypes);
var scalarSubqueryVisitor = new ScalarSubqueryVisitor();
fragment.Accept(scalarSubqueryVisitor);
var offenders = scalarSubqueryVisitor.NotIgnoredStatements(RuleId).Where(s =>
{
var whereClause = (s.QueryExpression as QuerySpecification)?.WhereClause;
if (whereClause == null)
{
return(false);
}
var booleanCompares = new BooleanComparisonVisitor();
whereClause.Accept(booleanCompares);
foreach (var booleanCompare in booleanCompares.Statements)
{
var colVisitor = new ColumnReferenceExpressionVisitor();
booleanCompare.AcceptChildren(colVisitor);
if (colVisitor.Count > 1)
{
return(true);
}
}
return(false);
}).ToList();
problems.AddRange(offenders.Select(o => new SqlRuleProblem(Message, sqlObj, o)));
return(problems);
}
public override IList <SqlRuleProblem> Analyze(SqlRuleExecutionContext ruleExecutionContext)
{
var problems = new List <SqlRuleProblem>();
var sqlObj = ruleExecutionContext.ModelElement; //proc / view / function
if (sqlObj == null || sqlObj.IsWhiteListed())
{
return(problems);
}
try
{
var fragment = ruleExecutionContext.ScriptFragment.GetFragment(ProgrammingAndViewSchemaTypes);
//get the combined parameters and declare variables into one searchable list
var variablesVisitor = new VariablesVisitor();
fragment.AcceptChildren(variablesVisitor);
var variables = variablesVisitor.GetVariables();
var selectStatementVisitor = new SelectStatementVisitor();
fragment.Accept(selectStatementVisitor);
foreach (var select in selectStatementVisitor.Statements)
{
if (select.QueryExpression is QuerySpecification query && query.WhereClause != null && query.FromClause != null)
{
var booleanComparisonVisitor = new BooleanComparisonVisitor();
query.WhereClause.Accept(booleanComparisonVisitor);
var comparisons = booleanComparisonVisitor.Statements
.Where(x =>
(x.FirstExpression is ColumnReferenceExpression ||
x.SecondExpression is ColumnReferenceExpression));
if (!comparisons.Any())
{
continue;
}
var dataTypesList = new Dictionary <NamedTableView, IDictionary <string, DataTypeView> >();
select.GetTableColumnDataTypes(dataTypesList, ruleExecutionContext.SchemaModel);
foreach (var comparison in comparisons)
{
var col1 = comparison.FirstExpression as ColumnReferenceExpression;
var col2 = comparison.SecondExpression as ColumnReferenceExpression;
var datatype1 = string.Empty;
var datatype2 = string.Empty;
if (col1 != null)
{
var dtView = dataTypesList.GetDataTypeView(col1);
if (dtView != null)
{
datatype1 = dtView.DataType;
}
}
else
{
datatype1 = GetDataType(sqlObj,
query,
comparison.FirstExpression,
variables,
ruleExecutionContext.SchemaModel);
}
if (col2 != null)
{
var dtView = dataTypesList.GetDataTypeView(col2);
if (dtView != null)
{
datatype2 = dtView.DataType;
}
}
else
{
datatype2 = GetDataType(sqlObj,
query,
comparison.SecondExpression,
variables,
ruleExecutionContext.SchemaModel);
}
if (string.IsNullOrWhiteSpace(datatype1) || string.IsNullOrWhiteSpace(datatype2))
{
continue;
}
//when checking the numeric literal I am not sure if it is a bit or tinyint.
if ((_comparer.Equals(datatype1, "bit") && _comparer.Equals(datatype2, "tinyint")) ||
(_comparer.Equals(datatype1, "tinyint") && _comparer.Equals(datatype2, "bit")))
{
continue;
}
if (!_comparer.Equals(datatype1, datatype2))
{
problems.Add(new SqlRuleProblem(Message, sqlObj, comparison));
}
}
}
}
}
catch (System.Exception ex)
{
//TODO: PROPERLY LOG THIS ERROR
Debug.WriteLine(ex.ToString());
//throw;
}
return(problems);
}
public static IList <JoinInfo> GetFromClauseJoinTables(this FromClause from)
{
if (from == null)
{
throw new ArgumentNullException(nameof(from));
}
var joins = new List <JoinInfo>();
if (from.TableReferences.Count == 0 || from.TableReferences.First().GetType() != typeof(QualifiedJoin))
{
return(joins);
}
var joinVisitor = new JoinVisitor();
from.Accept(joinVisitor);
//build the list of pure tables along with the list of boolean comparisons
foreach (var join in joinVisitor.QualifiedJoins)
{
var joinInfo = new JoinInfo {
};
var boolVisitor = new BooleanComparisonVisitor();
join.SearchCondition.Accept(boolVisitor);
joinInfo.Compares = new List <BooleanComparisonExpression>(boolVisitor.Statements);
if (join.FirstTableReference.GetType() == typeof(NamedTableReference))
{
joinInfo.Table1 = join.FirstTableReference as NamedTableReference;
}
if (join.SecondTableReference.GetType() == typeof(NamedTableReference))
{
joinInfo.Table2 = join.SecondTableReference as NamedTableReference;
}
joins.Add(joinInfo);
}
//table2 should always have a table..... maybe. unless the table is actually a sub-select. Then we will ignore it
foreach (var join in joins.Where(j => j.Table2 != null))
{
var table1 = join.Table1;
var table2 = join.Table2;
var table2Alias = table2.Alias?.Value;
var table2Name = new ObjectIdentifier(table2.SchemaObject.Identifiers.Select(x => x.Value));
//we need to figure out which side of the comparison goes to which table..... PITA. yes.....
foreach (var compare in join.Compares
.Where(x => x.FirstExpression is ColumnReferenceExpression && x.SecondExpression is ColumnReferenceExpression))
{
//we use a loop as we need to check both the first expression and the second expression to see which table the columns belong to
for (int i = 0; i < 2; i++)
{
var col = (i == 0 ? compare.FirstExpression : compare.SecondExpression) as ColumnReferenceExpression;
var colTblName = GetTableOrAliasName(col.MultiPartIdentifier.Identifiers);
if (table2Alias.StringEquals(colTblName.First()) || table2Name.CompareTo(colTblName) >= 5)
{
join.Table2JoinColumns.Add(col);
continue;
}
//use table1 if it was supplied in the compare. else scan the joins to find the matching table to the column
var tbl = table1 ?? joins.Select(x =>
{
if (CheckName(x.Table2, col))
{
return(x.Table2);
}
if (CheckName(x.Table1, col))
{
return(x.Table1);
}
return(null);
}).FirstOrDefault(x => x != null);
if (tbl != null)
{
var tblAlias = tbl.Alias?.Value;
var tblName = new ObjectIdentifier(tbl.SchemaObject.Identifiers.Select(x => x.Value));
if (join.Table1 == null)
{
join.Table1 = tbl;
}
if (tblAlias.StringEquals(colTblName.First()) || tblName.CompareTo(colTblName) >= 5)
{
join.Table1JoinColumns.Add(col);
}
}
}
}
}
return(joins);
}
