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