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