public (IEnumerable <Cte>, IEnumerable <Column>, IEnumerable <Table>) HandleCtes(WithCtesAndXmlNamespaces withCtesAndXmlNamespaces, Cte[] parentCtes, Table[] parentTables) { _level++; var tables = new List <Table>(); var columns = new List <Column>(); var ctes = new List <Cte>(); if (withCtesAndXmlNamespaces == null) { Dump($"{_pad}WithCtesAndXmlNamespaces is NULL"); _level--; return(Enumerable.Empty <Cte>(), Enumerable.Empty <Column>(), Enumerable.Empty <Table>()); } var i = 0; foreach (var commonTableExpression in withCtesAndXmlNamespaces.CommonTableExpressions) { Dump($"{_pad}| CommonTableExpressions[{i++}]"); var cte = new Cte { Name = commonTableExpression.ExpressionName?.Value }; ctes.Add(cte); parentCtes = parentCtes.Concat(new[] { cte }).Distinct().ToArray(); if (commonTableExpression.QueryExpression != null) { Dump($"{_pad}PROPERTY [QueryExpression]: handling..."); var result = HandleQuery(commonTableExpression.QueryExpression, parentCtes.ToArray(), parentTables.Distinct().ToArray()); columns.AddRange(result.Item1); tables.AddRange(result.Item2); } cte.LinkedTables = tables.Distinct().ToList(); foreach (var linkedTable in cte.LinkedTables) { linkedTable.SelectColumns = columns .Where(c => ReferenceEquals(c.AbsoluteTableReference, linkedTable)).Select(c => c.Name.ToLowerInvariant()) .ToArray(); linkedTable.PossibleSelectColumns = columns .Where(c => c.AmbiguousTableReferences != null && c.AmbiguousTableReferences.Any(t => ReferenceEquals(t, linkedTable))) .Select(c => c.Name.ToLowerInvariant()) .ToArray(); } if (cte.LinkedTables.Any()) { Dump($"{_pad}FOUND CTE-LINKED TABLES: {cte.Name}, tables: {string.Join(", ", cte.LinkedTables.Select(t => t.FullyQualifiedName))}"); } } _level--; return(ctes, columns.Where(c => c != null), tables); }
public IEnumerable <Column> TraverseObject(object obj, Cte[] ctes, Table[] parentTables = null) { if (obj == null) { return(Enumerable.Empty <Column>()); } if (parentTables == null) { parentTables = new Table[0]; } if (ctes == null) { ctes = new Cte[0]; } var columns = new List <Column>(); Dump($"{_pad}TRAVERSING: {obj} | BLACKLIST: [{string.Join(", ", ctes.Select(c => c.Name))}]"); foreach (var property in obj.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance) .Where(p => !p.PropertyType.IsValueType && !p.Name.Equals("ScriptTokenStream", StringComparison.OrdinalIgnoreCase) && !p.Name.Equals("Type", StringComparison.OrdinalIgnoreCase) && !p.PropertyType.FullName.Equals("System.String", StringComparison.OrdinalIgnoreCase)) .OrderBy(p => !p.Name.Equals("WithCtesAndXmlNamespaces", StringComparison.OrdinalIgnoreCase))) { object value = property.GetIndexParameters().Length > 0 ? Helpers.AsEnumerable(property, obj) : property.GetValue(obj, null); if (value == null) { continue; } Dump($"{_pad}PROPERTY [{property.Name}]: {value.GetType().Name}"); if (value.GetType().IsAssignableFrom(typeof(WithCtesAndXmlNamespaces))) { Dump($"{_pad}ANALYZING WithCtesAndXmlNamespaces"); var result = HandleCtes(value as WithCtesAndXmlNamespaces, ctes, parentTables); ctes = ctes.Concat(result.Item1).Distinct().ToArray(); columns.AddRange(result.Item2); } else if (value is QueryExpression) { Dump($"{_pad}ANALYZING QueryExpression"); columns.AddRange(HandleQuery(value, ctes, parentTables).Item1); } else if (property.GetIndexParameters().Length > 0 || typeof(IEnumerable).IsAssignableFrom(property.PropertyType)) { var collection = (IEnumerable)value; _level++; var i = 0; foreach (var item in collection) { Dump($"{_pad}| {property.Name}[{i++}]"); columns.AddRange(TraverseObject(item, ctes)); } _level--; } else { Dump($"{_pad}->"); _level++; columns.AddRange(TraverseObject(value, ctes)); _level--; } } return(columns); }
public (IEnumerable <Cte>, IEnumerable <Table>) HandleTables(object obj, Cte[] parentCtes, Table[] parentTables = null) { if (obj == null) { return(Enumerable.Empty <Cte>(), Enumerable.Empty <Table>()); } ; var tables = new List <Table>(); var ctes = new List <Cte>(); if (obj.GetType().IsAssignableFrom(typeof(NamedTableReference))) { tables.Add(GetTableFromReference(obj as NamedTableReference)); } else if (obj.GetType().IsAssignableFrom(typeof(QueryDerivedTable))) { var derived = obj as QueryDerivedTable; Dump($"{_pad}ANALYZING QueryDerivedTable..."); if (derived?.QueryExpression != null) { Dump($"{_pad}PROPERTY [QueryExpression]: handling..."); var result = HandleQuery(derived.QueryExpression, parentCtes, parentTables); var cte = new Cte { Name = derived.Alias?.Value, LinkedTables = result.Item2.Distinct().ToList() }; foreach (var linkedTable in cte.LinkedTables) { linkedTable.SelectColumns = result.Item1 .Where(c => ReferenceEquals(c.AbsoluteTableReference, linkedTable)).Select(c => c.Name.ToLowerInvariant()) .ToArray(); linkedTable.PossibleSelectColumns = result.Item1 .Where(c => c.AmbiguousTableReferences != null && c.AmbiguousTableReferences.Any(t => ReferenceEquals(t, linkedTable))) .Select(c => c.Name.ToLowerInvariant()) .ToArray(); } if (cte.LinkedTables.Any()) { Dump($"{_pad}FOUND DERIVED-LINKED TABLES: {cte.Name}, tables: {string.Join(", ", cte.LinkedTables.Select(t => t.FullyQualifiedName))}"); } ctes.Add(cte); } } else { var references = Helpers.GetPropertiesWithNames(obj, "FirstTableReference", "SecondTableReference", "TableReference", "Join").ToArray(); if (references.Any()) { _level++; } foreach (var reference in references.Where(r => r != null)) { var result = HandleTables(reference.GetValue(obj, null), parentCtes, parentTables); tables.AddRange(result.Item2); ctes.AddRange(result.Item1); } if (references.Any()) { _level--; } } return(ctes.Where(c => c != null), tables.Where(t => t != null)); }
public Column GetColumnFromIdentifiers(string[] identifiers, string alias, IEnumerable <Table> containers, IEnumerable <Table> parentContainers, IEnumerable <Cte> ctes, bool inPivot = false) { if (identifiers == null) { return(null); } if (containers == null) { containers = new Table[0]; } if (parentContainers == null) { parentContainers = new Table[0]; } if (ctes == null) { ctes = new Cte[0]; } Dump($"{_pad}PARSING COLUMN: tables: { string.Join(", ", containers.Count()) }, parent tables: { string.Join(", ", parentContainers.Count()) }, ctes: { string.Join(", ", ctes.Count()) }"); var allTables = containers.Concat(parentContainers).Distinct().ToArray(); var column = new Column { Alias = alias, Name = identifiers.Last() }; var matches = new List <Table>(); var linkedMatches = new List <Table>(); var cteMatches = new List <Cte>(); Dump($"{_pad}> FOUND [{identifiers.Length}] IDENTIFIERS: {string.Join(", ", identifiers)}"); if (identifiers.Length == 1) { matches.AddRange(containers); cteMatches.AddRange(ctes); linkedMatches.AddRange(cteMatches.Where(cte => cte.LinkedTables.Count == 1 && (cte.LinkedTables.First().SelectColumns.Contains(column.Name.ToLowerInvariant()) || cte.LinkedTables.First().SelectColumns.Contains("*") || inPivot)) .SelectMany(cte => cte.LinkedTables) .Distinct()); matches.AddRange(cteMatches.Where(cte => cte.LinkedTables.Any(lt => lt.PossibleSelectColumns.Contains(column.Name.ToLowerInvariant()) || lt.PossibleSelectColumns.Contains("*") || inPivot)) .SelectMany(cte => cte.LinkedTables) .Distinct()); } else if (identifiers.Length == 2) { var tableNameOrAlias = identifiers.First(); var aliasMatches = allTables.Where(t => !string.IsNullOrWhiteSpace(t.Alias) && t.Alias.Equals(tableNameOrAlias, StringComparison.OrdinalIgnoreCase)).ToArray(); if (aliasMatches.Length == 0) { var nameMatches = allTables.Where(t => t.Alias == null && t.Name.Equals(tableNameOrAlias, StringComparison.OrdinalIgnoreCase)).ToArray(); matches.AddRange(nameMatches); } else { matches.Add(aliasMatches.First()); } cteMatches.AddRange(ctes.Where(cte => !string.IsNullOrWhiteSpace(cte.Alias) && cte.Alias.Equals(tableNameOrAlias, StringComparison.OrdinalIgnoreCase) || cte.Alias == null && cte.Name.Equals(tableNameOrAlias, StringComparison.OrdinalIgnoreCase))); linkedMatches.AddRange(cteMatches .Where(cte => cte.LinkedTables.Count == 1 && (cte.LinkedTables.First().SelectColumns.Contains(column.Name.ToLowerInvariant()) || cte.LinkedTables.First().SelectColumns.Contains("*") || inPivot)) .SelectMany(cte => cte.LinkedTables) .Distinct()); matches.AddRange(cteMatches.Where(cte => cte.LinkedTables.Any(lt => lt.PossibleSelectColumns.Contains(column.Name.ToLowerInvariant()) || lt.PossibleSelectColumns.Contains("*") || inPivot)) .SelectMany(cte => cte.LinkedTables) .Distinct()); } else if (identifiers.Length == 3) { var schemaName = identifiers[0]; var tableName = identifiers[1]; matches.AddRange(allTables.Where(t => t.Name.Equals(tableName, StringComparison.OrdinalIgnoreCase) && t.Schema.Equals(schemaName, StringComparison.OrdinalIgnoreCase))); } else if (identifiers.Length == 4) { var databaseName = identifiers[0]; var schemaName = identifiers[1]; var tableName = identifiers[2]; matches.AddRange(allTables.Where(t => t.Name.Equals(tableName, StringComparison.OrdinalIgnoreCase) && t.Schema.Equals(schemaName, StringComparison.OrdinalIgnoreCase) && t.Database.Equals(databaseName, StringComparison.OrdinalIgnoreCase))); } else if (identifiers.Length == 5) { var serverName = identifiers[0]; var databaseName = identifiers[1]; var schemaName = identifiers[2]; var tableName = identifiers[3]; matches.AddRange(allTables.Where(t => t.Name.Equals(tableName, StringComparison.OrdinalIgnoreCase) && t.Schema.Equals(schemaName, StringComparison.OrdinalIgnoreCase) && t.Database.Equals(databaseName, StringComparison.OrdinalIgnoreCase) && t.Server.Equals(serverName, StringComparison.OrdinalIgnoreCase))); } var tableNames = matches.Where(m => m.Server == null && m.Database == null && m.Schema == null) .Select(m => m.Name.ToLowerInvariant()).ToArray(); cteMatches.AddRange(ctes.Where(cte => tableNames.Contains(cte.Name.ToLowerInvariant()))); if (cteMatches.Any()) { column.CteReferences = cteMatches.Select(t => new Cte { Name = t.Name, Alias = t.Alias }).ToArray(); var cteNames = ctes.Select(cte => cte.Name.ToLowerInvariant()).ToArray(); var reject = matches.Where(m => m.Server == null && m.Database == null && m.Schema == null && cteNames.Contains(m.Name.ToLowerInvariant())); matches = matches.Except(reject).ToList(); } if (linkedMatches.Any() && !matches.Any()) { Dump($"{_pad}> FOUND CTE-LINKED TABLES (MATCHES: {linkedMatches.Count}): [{ string.Join(", ", linkedMatches.Select(c => c.FullyQualifiedName)) }]"); if (linkedMatches.Count > 1) { column.AmbiguousTableReferences = linkedMatches; } else if (linkedMatches.Count == 1) { column.AbsoluteTableReference = linkedMatches.First(); } } else { Dump($"{_pad}> FOUND TABLES (MATCHES: {matches.Count}): [{ string.Join(", ", matches.Select(c => c.FullyQualifiedName)) }]"); if (matches.Count > 1) { column.AmbiguousTableReferences = matches; } else if (matches.Count == 1) { column.AbsoluteTableReference = matches.First(); } } var matchingTable = column.AbsoluteTableReference != null ? $" | MATCHING TABLE: {column.AbsoluteTableReference.FullyQualifiedName}" : string.Empty; var matchingTables = column.AmbiguousTableReferences != null ? $" | MATCHING TABLES: {string.Join(", ", column.AmbiguousTableReferences.Select(t => t.FullyQualifiedName))}" : string.Empty; var matchingCtes = column.CteReferences != null ? $" | MATCHING CTES: {string.Join(", ", column.CteReferences.Select(t => t.Name))}" : string.Empty; Dump($"{_pad}> FOUND COLUMN: {column.FullyQualifiedName}{matchingTable}{matchingTables}{matchingCtes}"); return(column); }