/// <summary>
/// Gets the table from column.
/// </summary>
/// <param name="sqlObj">The SQL object.</param>
/// <param name="query">The query.</param>
/// <param name="column">The column.</param>
/// <returns></returns>
protected static TSqlObject GetTableFromColumn(TSqlObject sqlObj, QuerySpecification query, ColumnReferenceExpression column)
{
var tables = new List <NamedTableReference>();
var namedTableVisitor = new NamedTableReferenceVisitor();
query.FromClause.Accept(namedTableVisitor);
if (column.MultiPartIdentifier.Identifiers.Count == 2)
{
tables.AddRange(namedTableVisitor.Statements.Where(x => x.Alias?.Value == column.MultiPartIdentifier.Identifiers[0].Value));
}
else
{
//they did NOT use a two part name, so logic dictates that this column SHOULD only appear once in the list of tables, but we will have to search all of the tables.
tables.AddRange(namedTableVisitor.Statements);
}
var referencedTables = sqlObj.GetReferenced().Where(x => x.ObjectType == Table.TypeClass && tables.Any(t => x.Name.CompareTo(t.SchemaObject.Identifiers) >= 5));
foreach (var referencedTable in referencedTables)
{
string fullColumnName = referencedTable.Name.ToString() + ".[" + column.MultiPartIdentifier.Identifiers.Last().Value + "]";
var retColumn = referencedTable.GetReferencedRelationshipInstances(Table.Columns).FirstOrDefault(p => _comparer.Equals(p.ObjectName.ToString(), fullColumnName));
if (retColumn != null)
{
return(referencedTable);
}
}
return(null);
}
public static void GetTableColumnDataTypes(this TSqlStatement query, IDictionary <NamedTableView, IDictionary <string, DataTypeView> > list, TSqlModel model)
{
if (query == null)
{
return;
}
//new Dictionary<string, string>(StringComparer.InvariantCultureIgnoreCase)
var namedTableReferenceVisitor = new NamedTableReferenceVisitor();
query.Accept(namedTableReferenceVisitor);
foreach (var tbl in namedTableReferenceVisitor.Statements)
{
var tblView = new NamedTableView(tbl);
if (!list.ContainsKey(tblView))
{
list.Add(tblView, tbl.GetColumnsAndDataTypes(model));
}
else if (!string.IsNullOrEmpty(tbl.Alias?.Value))
{
var kv = list.First(x => x.Key.Equals(tblView));
if (!kv.Key.HasAlias(tbl.Alias?.Value))
{
kv.Key.Aliases.Add(tbl.Alias?.Value);
list.Remove(kv.Key);
list.Add(kv.Key, kv.Value);
}
}
}
}
/// <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)
{
return(problems);
}
var fragment = ruleExecutionContext.ScriptFragment.GetFragment(typeof(CreateProcedureStatement));
IfStatementVisitor ifVisitor = new IfStatementVisitor();
fragment.Accept(ifVisitor);
if (!ifVisitor.Statements.Any())
{
return(problems);
}
foreach (var ifStatement in ifVisitor.NotIgnoredStatements(RuleId))
{
var tableVisitor = new NamedTableReferenceVisitor()
{
TypeFilter = ObjectTypeFilter.PermanentOnly
};
ifStatement.ThenStatement?.Accept(tableVisitor);
ifStatement.ElseStatement?.Accept(tableVisitor);
problems.AddRange(tableVisitor.Statements.Select(s => new SqlRuleProblem(Message, sqlObj, s)));
}
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 selectStatementVisitor = new SelectStatementVisitor();
fragment.Accept(selectStatementVisitor);
if (selectStatementVisitor.Count == 0)
{
return(problems);
}
foreach (var select in selectStatementVisitor.Statements)
{
if (select.QueryExpression is QuerySpecification query)
{
var fromClause = query.FromClause;
if (fromClause == null)
{
continue;
}
//check to ensure we have more than one table
var namedTableVisitor = new NamedTableReferenceVisitor();
fromClause.Accept(namedTableVisitor);
if (namedTableVisitor.Count <= 1)
{
continue;
}
var columnReferences = new ColumnReferenceExpressionVisitor();
query.Accept(columnReferences);
var offenders = columnReferences.NotIgnoredStatements(RuleId)
.Where(c => CheckName(c))
.Select(n => n.MultiPartIdentifier.Identifiers[0]);
if (offenders.Any())
{
problems.Add(new SqlRuleProblem(Message, sqlObj, select));
}
}
}
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 selectStatementVisitor = new SelectStatementVisitor();
fragment.Accept(selectStatementVisitor);
if (selectStatementVisitor.Count == 0)
{
return(problems);
}
foreach (var select in selectStatementVisitor.Statements)
{
var fromClause = (select.QueryExpression as QuerySpecification)?.FromClause;
//ignore selects that do not use a from clause with tables
if (fromClause == null)
{
continue;
}
var visitor = new NamedTableReferenceVisitor()
{
TypeFilter = ObjectTypeFilter.PermanentOnly
};
fromClause.Accept(visitor);
//only scan for aliases if there are more than 1 table in the from clause
if (visitor.Count <= 1)
{
continue;
}
var offenders =
from t in visitor.NotIgnoredStatements(RuleId)
where t.Alias == null
select t;
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;
if (sqlObj == null || sqlObj.IsWhiteListed())
{
return(problems);
}
var fragment = ruleExecutionContext.ScriptFragment.GetFragment(ProgrammingAndViewSchemaTypes);
var selectStatementVisitor = new SelectStatementVisitor();
fragment.Accept(selectStatementVisitor);
foreach (var stmt in selectStatementVisitor.Statements)
{
var hasForceOrder = stmt.OptimizerHints?.Any(oh => oh.HintKind == OptimizerHintKind.ForceOrder);
if (hasForceOrder.GetValueOrDefault(false))
{
continue;
}
var querySpecificationVisitor = new QuerySpecificationVisitor();
stmt.QueryExpression.Accept(querySpecificationVisitor);
foreach (var query in querySpecificationVisitor.Statements)
{
var fromClause = query.FromClause;
if (fromClause == null)
{
continue;
}
var namedTableVisitor = new NamedTableReferenceVisitor();
fromClause.Accept(namedTableVisitor);
var tableCount = namedTableVisitor.Count - 1;
if (tableCount > 8)
{
var msg = string.Format(Message, tableCount);
problems.Add(new SqlRuleProblem(msg, sqlObj, fromClause));
}
}
}
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 objName = sqlObj.Name.GetName();
var fragment = ruleExecutionContext.ScriptFragment.GetFragment(ProgrammingAndViewSchemaTypes);
var fromClauseVisitor = new FromClauseVisitor();
var execVisitor = new ExecuteVisitor();
fragment.Accept(fromClauseVisitor, execVisitor);
var tableVisitor = new NamedTableReferenceVisitor()
{
TypeFilter = ObjectTypeFilter.PermanentOnly
};
foreach (var from in fromClauseVisitor.Statements)
{
from.Accept(tableVisitor);
}
var offenders = tableVisitor.Statements.Where(tbl =>
{
var id = tbl.GetObjectIdentifier(null);
return(id.Parts.Count < 2 || string.IsNullOrWhiteSpace(id.Parts.First()));
});
var execOffenders = execVisitor.Statements.Where(proc => CheckProc(proc));
problems.AddRange(offenders.Select(t => new SqlRuleProblem(Message, sqlObj, t)));
problems.AddRange(execOffenders.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;
var model = ruleExecutionContext.SchemaModel;
if (sqlObj == null || sqlObj.IsWhiteListed())
{
return(problems);
}
var fragment = ruleExecutionContext.ScriptFragment.GetFragment(ProgrammingSchemaTypes);
var updateVisitor = new UpdateVisitor();
fragment.Accept(updateVisitor);
foreach (var update in updateVisitor.NotIgnoredStatements(RuleId))
{
if (!(update.UpdateSpecification.Target is NamedTableReference target) || target.GetName().Contains("#"))
{
continue;
}
//we have an aliased table we need to find out what the real table is so we can look up its columns
if (update.UpdateSpecification.FromClause != null)
{
var namedTableVisitor = new NamedTableReferenceVisitor()
{
TypeFilter = ObjectTypeFilter.PermanentOnly
};
update.UpdateSpecification.FromClause.Accept(namedTableVisitor);
target = namedTableVisitor.Statements
.FirstOrDefault(t => _comparer.Equals(t.Alias?.Value, target.SchemaObject.Identifiers.LastOrDefault()?.Value));
if (target == null)
{
continue;
}
}
var targetSqlObj = model.GetObject(Table.TypeClass, target.GetObjectIdentifier(), DacQueryScopes.All);
if (targetSqlObj == null)
{
continue;
}
var pk = targetSqlObj.GetReferencing(PrimaryKeyConstraint.Host, DacQueryScopes.UserDefined).FirstOrDefault();
if (pk == null)
{
continue;
}
var primaryKeyColumns = pk.GetReferenced(PrimaryKeyConstraint.Columns, DacQueryScopes.All);
var hasOffense = update.UpdateSpecification.SetClauses.OfType <AssignmentSetClause>().Any(setClause =>
{
if (setClause.Column?.MultiPartIdentifier == null)
{
return(false);
}
return(primaryKeyColumns.Any(pkc => pkc.Name.CompareTo(setClause.Column?.MultiPartIdentifier) >= 5));
});
if (hasOffense)
{
problems.Add(new SqlRuleProblem(Message, sqlObj, update));
}
}
return(problems);
}
