/// <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); }