Пример #1
0
        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);
        }
Пример #2
0
        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);
        }
Пример #3
0
        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));
        }
Пример #4
0
        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);
        }