private void AnalyzeJoins(IList <SqlRuleProblem> problems, SqlRuleExecutionContext ruleExecutionContext, QuerySpecification querySpecification) { var visitor = new CollectJoinVisitor(); querySpecification.Accept(visitor); foreach (var join in visitor.Joins) { // We should have already handled the first table reference here, as it's included from an earlier part // of the query. As such, we now handle the second table only. var tablePartitions = CollectFromTableReference(ruleExecutionContext, join.SecondTableReference, join.SecondTableReference).ToList(); if (tablePartitions.Count == 0) { continue; } if (join is QualifiedJoin qualifiedJoin) { var condition = qualifiedJoin.SearchCondition; SearchConstraints(condition, tablePartitions); } foreach (var partitionedColumn in tablePartitions) { SqlRuleProblem problem = CreateProblem(ruleExecutionContext, partitionedColumn); problems.Add(problem); } } }
/// <summary> /// Initializes a new instance of the <see cref="Select"/> class. /// </summary> /// <param name="querySpecification">The query specification.</param> /// <param name="bodyColumnTypes">The body column types.</param> public Select(QuerySpecification querySpecification, IDictionary<string, DataType> bodyColumnTypes) { // Get any table aliases. var aliasResolutionVisitor = new AliasResolutionVisitor(); querySpecification.Accept(aliasResolutionVisitor); this.TableAliases = aliasResolutionVisitor.Aliases; var outerJoinedTables = new List<string>(); if (querySpecification.FromClause != null) { foreach (var join in querySpecification.FromClause.TableReferences.OfType<QualifiedJoin>()) { FillOuterJoins(outerJoinedTables, join, false); } } var topInt = querySpecification.TopRowFilter != null ? querySpecification.TopRowFilter.Expression as IntegerLiteral : null; this.IsSingleRow = topInt != null && topInt.Value == "1" && querySpecification.TopRowFilter.Percent == false; this.Columns = querySpecification.SelectElements.OfType<SelectScalarExpression>().Select(x => new SelectColumn(x, bodyColumnTypes, this.TableAliases, outerJoinedTables)).ToList(); }
//protected string GetColumnDataType(ColumnReferenceExpression value, Dictionary<NamedTableView, IDictionary<string, DataTypeView>> columnDataTypes) //{ // var columnName = value.MultiPartIdentifier.Identifiers.GetName().ToLower(); // var types = columnDataTypes.Where(t => t.Key.Name.ToLower().Contains(columnName)); // //so.... technically this could resolve to multiple columns, but I have no clue which one to pick as the column does not have any reference to the parent query. // var typ = types.FirstOrDefault(); // if (typ.Key != null) // { // //return typ.Value..DataType.; // } // return null; //} /// <summary> /// Gets the data type of the column. /// </summary> /// <param name="sqlObj">The SQL object.</param> /// <param name="query">The query.</param> /// <param name="column">The column.</param> /// <param name="model">The model.</param> /// <param name="variables">The variables.</param> /// <returns></returns> protected static string GetColumnDataType(TSqlObject sqlObj, QuerySpecification query, ColumnReferenceExpression column, TSqlModel model, IList <DataTypeView> variables) { TSqlObject referencedColumn = null; var columnName = column.MultiPartIdentifier.Identifiers.Last().Value.ToLower(); var columns = sqlObj.GetReferenced(DacQueryScopes.All).Where(x => x.ObjectType == Column.TypeClass && x.Name.GetName().ToLower().Contains($"[{columnName}]") ).Distinct().ToList(); if (columns.Count == 0) { //we have an aliased column, probably from a cte, temp table, or sub-select. we need to try to find it var visitor = new SelectScalarExpressionVisitor(); sqlObj.GetFragment().Accept(visitor); //sqlObj.GetFragment() //try to find a select column where the alias matches the column name we are searching for var selectColumns = visitor.Statements.Where(x => _comparer.Equals(x.ColumnName?.Value, columnName)).ToList(); //if we find more than one match, we have no way to determine which is the correct one. if (selectColumns.Count == 1) { return(GetDataType(sqlObj, query, selectColumns.First().Expression, variables)); } else { return(null); } } else if (columns.Count > 1) { var tablesVisitor = new TableReferenceWithAliasVisitor(); if (column.MultiPartIdentifier.Identifiers.Count > 1) { sqlObj.GetFragment().Accept(tablesVisitor); var columnTableAlias = column.MultiPartIdentifier.Identifiers.First().Value; var tbls = tablesVisitor.Statements.Where(x => _comparer.Equals(x.Alias?.Value, columnTableAlias) || _comparer.Equals(x.GetName(), $"[{columnTableAlias}]")); //if we find more than one table with the same alias, we have no idea which one it could be. if (tbls.Count() == 1) { referencedColumn = GetReferencedColumn(tbls.FirstOrDefault(), columns, columnName); } else { foreach (var tbl in tbls) { referencedColumn = GetReferencedColumn(tbl, columns, columnName); if (referencedColumn != null) { break; } } } } else { query.Accept(tablesVisitor); if (tablesVisitor.Count == 1) { referencedColumn = GetReferencedColumn(tablesVisitor.Statements.FirstOrDefault(), columns, columnName); } else { foreach (var tbl in tablesVisitor.Statements) { referencedColumn = GetReferencedColumn(tbl, columns, columnName); if (referencedColumn != null) { break; } } } } } else { referencedColumn = columns.FirstOrDefault(); } if (referencedColumn != null) { TSqlObject dataType = null; //sometimes for some reason, I have to call getreferenced multiple times to get to the datatype. nfc why.... while (dataType == null && referencedColumn != null) { var colReferenced = referencedColumn.GetReferenced(DacQueryScopes.All); dataType = colReferenced.FirstOrDefault(x => _comparer.Equals(x.ObjectType.Name, "DataType")); if (dataType == null) { //try the next? referenced column. referencedColumn = colReferenced.FirstOrDefault(x => x.ObjectType == Column.TypeClass); } else { break; } } if (dataType != null) { return(dataType.Name.Parts.First()); } } return(null); }