public override object VisitOuterJoin([NotNull] PostgresParser.OuterJoinContext context)
{
var res = new JoinedTable();
if (context.LEFT() != null)
{
res.JoinType = JoinType.LEFT_OUTER;
}
else if (context.RIGHT() != null)
{
res.JoinType = JoinType.RIGHT_OUTER;
}
else if (context.FULL() != null)
{
res.JoinType = JoinType.FULL_OUTER;
}
res.SecondTable = (SingleTable)Visit(context.tableSourceItem());
if (context.ON() != null)
{
var exp = (BooleanEquals)Visit(context.expression());
res.FirstColumn = (ColumnRef)exp.left;
res.SecondColumn = (ColumnRef)exp.right;
}
return(res);
}
public TableView Join(Table inner,
FakeFunc <T, Projection> outerKeySelector,
FakeFunc <Table, Projection> innerKeySelector,
FakeFunc <T, Table, Projection[]> resultSelector)
{
var join = new JoinedTable(table, inner,
new EqualsCondition(outerKeySelector(aliases), innerKeySelector(inner)));
return(join.Select(resultSelector(aliases, inner)));
}
public IntermediateJoin <U> Join <U>(Table inner,
FakeFunc <T, Projection> outerKeySelector,
FakeFunc <Table, Projection> innerKeySelector,
FakeFunc <T, Table, U> resultSelector)
{
var join = new JoinedTable(table, inner,
new EqualsCondition(outerKeySelector(aliases), innerKeySelector(inner)));
var newAliases = resultSelector(aliases, inner);
return(new IntermediateJoin <U>(join, newAliases));
}
internal Join(EJoinType joinType, ISqlTableSource table, string alias, bool isWeak, IReadOnlyList <IJoin> joins)
{
JoinedTable = new JoinedTable(joinType, table, alias, isWeak);
if (joins != null && joins.Count > 0)
{
for (var index = 0; index < joins.Count; index++)
{
JoinedTable.Table.Joins.AddLast(joins[index].JoinedTable);
}
}
}
/// <summary>
/// 支持单主键插入新增数据
/// </summary>
/// <param name="sql"></param>
/// <param name="dt"></param>
/// <returns></returns>
public int SaveNewList(string sql, DataTable dt)
{
if (OpenConnect() == false)
{
return(-1);
}
DataTable insertTab = null;
DataTable newDt = null;
try
{
newDt = new DataTable();
SqlCommand selectCMD = new SqlCommand(sql, conn);
SqlDataAdapter sda = new SqlDataAdapter(selectCMD);
sda.MissingSchemaAction = MissingSchemaAction.AddWithKey;
//上面的语句中使用select 0，不是为了查询出数据，而是要查询出表结构以向DataTable中填充表结构
sda.Fill(newDt);
DataColumn[] pcols = newDt.PrimaryKey;
if (pcols.Length != 1)
{
return(0);
}
var queryNew =
from rNew in dt.AsEnumerable()
join rOld in newDt.AsEnumerable()
on rNew.Field <Int64>(pcols[0].ColumnName) equals rOld.Field <Int64>(pcols[0].ColumnName)
into JoinedTable
from Addtional in JoinedTable.DefaultIfEmpty()
select new {
newData = rNew, oldData = Addtional
};
insertTab = newDt.Clone();
foreach (var obj in queryNew)
{
if (obj != null && obj.newData != null && obj.oldData == null)
{
insertTab.Rows.Add(obj.newData.ItemArray);
}
}
SqlCommandBuilder scb = new SqlCommandBuilder(sda);
//执行更新
return(sda.Update(insertTab));
//使DataTable保存更新
//dt.AcceptChanges();
}
catch (Exception ce)
{
Log("错误", "保存新增数据错误", string.Format("{0}:{1}", sql, ce.Message));
return(-1);
}
}
public override object VisitInnerJoin([NotNull] PostgresParser.InnerJoinContext context)
{
var res = new JoinedTable();
res.JoinType = JoinType.INNER;
if (context.CROSS() != null)
{
res.JoinType = JoinType.CROSS;
}
res.SecondTable = (SingleTable)Visit(context.tableSourceItem());
if (context.ON() != null)
{
var exp = (BooleanEquals)Visit(context.expression());
res.FirstColumn = (ColumnRef)exp.left;
res.SecondColumn = (ColumnRef)exp.right;
}
return(res);
}
public IntermediateJoin(JoinedTable table, T aliases)
{
this.table = table;
this.aliases = aliases;
}
private static string GetQueryToValidateUniqueness(DataSourceView dsv, DataItem[] child, DataItem[] parent)
{
//need to do GetColumnBindingForDataItem
StringBuilder select = new StringBuilder();
StringBuilder outerSelect = new StringBuilder();
StringBuilder groupBy = new StringBuilder();
StringBuilder join = new StringBuilder();
StringBuilder topLevelColumns = new StringBuilder();
Dictionary<DataTable, JoinedTable> tables = new Dictionary<DataTable, JoinedTable>();
List<string> previousColumns = new List<string>();
foreach (DataItem di in child)
{
ColumnBinding col = GetColumnBindingForDataItem(di);
if (!previousColumns.Contains("[" + col.TableID + "].[" + col.ColumnID + "]"))
{
string colAlias = System.Guid.NewGuid().ToString();
DataColumn dc = dsv.Schema.Tables[col.TableID].Columns[col.ColumnID];
groupBy.Append((select.Length == 0 ? "group by " : ","));
outerSelect.Append((select.Length == 0 ? "select " : ","));
select.Append((select.Length == 0 ? "select distinct " : ","));
string sIsNull = "";
//select.Append(" /*" + dc.DataType.FullName + "*/ "); //for troubleshooting data types
if (dc.DataType == typeof(string))
{
if (di.NullProcessing == NullProcessing.Preserve)
sIsNull = "'__BIDS_HELPER_DIMENSION_HEALTH_CHECK_UNIQUE_STRING__'"; //a unique value that shouldn't ever occur in the real data
else
sIsNull = "''";
}
else if (dc.DataType == typeof(DateTime))
{
if (di.NullProcessing == NullProcessing.Preserve)
sIsNull = "'1/1/1899 01:02:03 AM'"; //a unique value that shouldn't ever occur in the real data
else
sIsNull = "'12/30/1899'"; //think this is what SSAS converts null dates to
}
else //numeric
{
if (di.NullProcessing == NullProcessing.Preserve)
sIsNull = "-987654321.123456789"; //a unique value that shouldn't ever occur in the real data
else
sIsNull = "0";
}
join.Append((join.Length == 0 ? "on " : "and ")).Append("coalesce(y.[").Append(colAlias).Append("],").Append(sIsNull).Append(") = coalesce(z.[").Append(colAlias).Append("],").Append(sIsNull).AppendLine(")");
groupBy.Append("[").Append(colAlias).AppendLine("]");
outerSelect.Append("[").Append(colAlias).AppendLine("]");
if (topLevelColumns.Length > 0) topLevelColumns.Append(",");
topLevelColumns.Append("y.[").Append(colAlias).Append("] as [").Append(dc.ColumnName).AppendLine("]");
if (!dc.ExtendedProperties.ContainsKey("ComputedColumnExpression"))
{
select.Append("[").Append(colAlias).Append("] = [").Append(dsv.Schema.Tables[col.TableID].ExtendedProperties["FriendlyName"].ToString()).Append("].[").Append((dc.ExtendedProperties["DbColumnName"] ?? dc.ColumnName).ToString()).AppendLine("]");
}
else
{
select.Append("[").Append(colAlias).Append("]");
select.Append(" = ").AppendLine(dc.ExtendedProperties["ComputedColumnExpression"].ToString());
}
if (!tables.ContainsKey(dsv.Schema.Tables[col.TableID]))
{
tables.Add(dsv.Schema.Tables[col.TableID], new JoinedTable(dsv.Schema.Tables[col.TableID]));
}
previousColumns.Add("[" + col.TableID + "].[" + col.ColumnID + "]");
}
}
foreach (DataItem di in parent)
{
ColumnBinding col = GetColumnBindingForDataItem(di);
if (!previousColumns.Contains("[" + col.TableID + "].[" + col.ColumnID + "]"))
{
string colAlias = System.Guid.NewGuid().ToString();
DataColumn dc = dsv.Schema.Tables[col.TableID].Columns[col.ColumnID];
select.Append(",");
//use the __PARENT__ prefix in case there's a column with the same name but different table
if (!dc.ExtendedProperties.ContainsKey("ComputedColumnExpression"))
{
select.Append("[").Append(colAlias).Append("] = [").Append(dsv.Schema.Tables[col.TableID].ExtendedProperties["FriendlyName"].ToString()).Append("].[").Append((dc.ExtendedProperties["DbColumnName"] ?? dc.ColumnName).ToString()).AppendLine("]");
}
else
{
select.Append("[").Append(colAlias).Append("]");
select.Append(" = ").AppendLine(dc.ExtendedProperties["ComputedColumnExpression"].ToString());
}
if (topLevelColumns.Length > 0) topLevelColumns.Append(",");
topLevelColumns.Append("y.[").Append(colAlias).Append("] as [").Append(dc.ColumnName).AppendLine("]");
if (!tables.ContainsKey(dsv.Schema.Tables[col.TableID]))
{
tables.Add(dsv.Schema.Tables[col.TableID], new JoinedTable(dsv.Schema.Tables[col.TableID]));
}
previousColumns.Add("[" + col.TableID + "].[" + col.ColumnID + "]");
}
}
int iLastTableCount = 0;
while (iLastTableCount != tables.Values.Count)
{
iLastTableCount = tables.Values.Count;
JoinedTable[] arrJt = new JoinedTable[iLastTableCount];
tables.Values.CopyTo(arrJt, 0); //because you can't iterate the dictionary keys while they are changing
foreach (JoinedTable jt in arrJt)
{
TraverseParentRelationshipsAndAddNewTables(tables, jt.table);
}
}
//check that all but one table have a valid join path to them
DataTable baseTable = null;
foreach (JoinedTable t in tables.Values)
{
if (!t.Joined)
{
if (baseTable == null)
{
baseTable = t.table;
}
else
{
throw new Exception("Cannot find join path for table " + t.table.TableName + " or " + baseTable.TableName + ". Only one table can be the starting table for the joins.");
}
}
}
if (baseTable == null)
{
//there are joins to and from the base table, so guess at the base table based on the child parameter to this function
ColumnBinding col = GetColumnBindingForDataItem(child[0]);
baseTable = dsv.Schema.Tables[col.TableID];
}
//by now, all tables needed for joins will be in the dictionary
select.Append("\r\nfrom ").AppendLine(GetFromClauseForTable(baseTable));
select.Append(TraverseParentRelationshipsAndGetFromClause(tables, baseTable));
outerSelect.AppendLine("\r\nfrom (").Append(select).AppendLine(") x").Append(groupBy).AppendLine("\r\nhaving count(*)>1");
string invalidValuesInner = outerSelect.ToString();
outerSelect = new StringBuilder();
outerSelect.Append("select ").AppendLine(topLevelColumns.ToString()).AppendLine(" from (").Append(select).AppendLine(") as y");
outerSelect.AppendLine("join (").AppendLine(invalidValuesInner).AppendLine(") z").AppendLine(join.ToString());
return outerSelect.ToString();
}
internal TableLink(JoinedTable staging, string parentAlias, byte relativePosition)
: this(staging)
{
_RelativePosition = relativePosition;
ParentAlias = parentAlias;
}
internal TableLink(JoinedTable staging)
{
_Staging = staging;
}
/// <summary>
/// 执行非删除以外的所有更新，数据表需要指定唯一的key
/// </summary>
/// <param name="sql"></param>
/// <param name="dt"></param>
/// <returns></returns>
public int UpdateOrNewList(string sql, DataTable dt)
{
object lockobj = new object();
lock (lockobj)
{
if (OpenConnect() == false)
{
return(-1);
}
DataTable insertTab = null;
DataTable updateTab = null;
DataTable oldDt = null;
try
{
oldDt = new DataTable();
SqlCommand selectCMD = new SqlCommand(sql, conn);
SqlDataAdapter sda = new SqlDataAdapter(selectCMD);
sda.MissingSchemaAction = MissingSchemaAction.AddWithKey;
//上面的语句中使用select 0，不是为了查询出数据，而是要查询出表结构以向DataTable中填充表结构
sda.Fill(oldDt);
DataColumn[] pcols = oldDt.PrimaryKey;
if (pcols.Length != 1)
{
return(0);
}
var queryNew =
from rNew in dt.AsEnumerable()
join rOld in oldDt.AsEnumerable()
on rNew.Field <Int64?>(pcols[0].ColumnName) equals rOld.Field <Int64?>(pcols[0].ColumnName)
into JoinedTable
from Addtional in JoinedTable.DefaultIfEmpty()
select new
{
newData = rNew,
oldData = Addtional
};
insertTab = oldDt.Clone();
updateTab = oldDt.Clone();
foreach (var obj in queryNew)
{
if (obj != null && obj.newData != null && obj.oldData == null)
{
insertTab.Rows.Add(obj.newData.ItemArray);
}
if (obj != null && obj.newData != null && obj.oldData != null)
{
updateTab.Rows.Add(obj.newData.ItemArray);
}
}
SqlCommandBuilder scb = new SqlCommandBuilder(sda);
//执行更新
oldDt.AcceptChanges();
DataTable dtNew = oldDt.Clone();
for (int i = 0; i < updateTab.Rows.Count; i++)
{
DataRow dr = updateTab.Rows[i];
for (int j = 0; j < oldDt.Rows.Count; j++) //遍历原表中所有记录
{
if (dr[pcols[0].ColumnName].Equals(oldDt.Rows[j][pcols[0]])) //主键相等
{
oldDt.Rows[j].SetModified();
//oldDt.Rows[j].ItemArray = dr.ItemArray;//用新表中替代
for (int c = 0; c < oldDt.Columns.Count; c++)
{
if (oldDt.Columns[c].ColumnName.Equals(pcols[0].ColumnName))//主键跳过
{
continue;
}
oldDt.Rows[j][oldDt.Columns[c].ColumnName] = dr[oldDt.Columns[c].ColumnName];
}
break;
}
}
}
int SameRet = sda.Update(oldDt);
int NewRet = sda.Update(insertTab);
return(SameRet + NewRet);
//使DataTable保存更新
//dt.AcceptChanges();
}
catch (Exception ce)
{
Log("错误", "新增/更新数据错误", string.Format("{0}:{1}", sql, ce.StackTrace));
return(-1);
}
}
}
private static string GetQueryToValidateUniqueness(DataSourceView dsv, DataItem[] child, DataItem[] parent)
{
//need to do GetColumnBindingForDataItem
StringBuilder select = new StringBuilder();
StringBuilder outerSelect = new StringBuilder();
StringBuilder groupBy = new StringBuilder();
StringBuilder orderBy = new StringBuilder();
StringBuilder join = new StringBuilder();
StringBuilder topLevelColumns = new StringBuilder();
Dictionary <DataTable, JoinedTable> tables = new Dictionary <DataTable, JoinedTable>();
List <string> previousColumns = new List <string>();
foreach (DataItem di in child)
{
ColumnBinding col = GetColumnBindingForDataItem(di);
if (!previousColumns.Contains("[" + col.TableID + "].[" + col.ColumnID + "]"))
{
string colAlias = GetNextUniqueColumnIdentifier();
DataColumn dc = dsv.Schema.Tables[col.TableID].Columns[col.ColumnID];
groupBy.Append((select.Length == 0 ? "group by " : ","));
outerSelect.Append((select.Length == 0 ? "select " : ","));
select.Append((select.Length == 0 ? "select distinct " : ","));
string sIsNull = "";
string sForceDataTypeOnJoin = "";
if (dc.DataType == typeof(string))
{
if (DBServerName == "Oracle")
{
if (di.NullProcessing == NullProcessing.Preserve)
{
sIsNull = "'__BIDS_HELPER_DIMENSION_HEALTH_CHECK_UNIQUE_STRING__'"; //a unique value that shouldn't ever occur in the real data
}
else
{
sIsNull = "' '"; //oracle treats empty string as null
}
}
else
{
if (di.NullProcessing == NullProcessing.Preserve)
{
sIsNull = "'__BIDS_HELPER_DIMENSION_HEALTH_CHECK_UNIQUE_STRING__'"; //a unique value that shouldn't ever occur in the real data
}
else
{
sIsNull = "''";
}
}
}
else if (dc.DataType == typeof(DateTime))
{
if (DBServerName == "Oracle")
{
if (di.NullProcessing == NullProcessing.Preserve)
{
sIsNull = "to_date('1/1/1899 01:02:03','MM/DD/YYYY HH:MI:SS')"; //a unique value that shouldn't ever occur in the real data
}
else
{
sIsNull = "to_date('12/30/1899','MM/DD/YYYY')"; //think this is what SSAS converts null dates to
}
}
else if (DBServerName == "Teradata")
{
sForceDataTypeOnJoin = "timestamp";
if (di.NullProcessing == NullProcessing.Preserve)
{
sIsNull = "cast('1899/12/30 01:02:03.456789' as timestamp)"; //a unique value that shouldn't ever occur in the real data
}
else
{
sIsNull = "cast('1899/12/30 00:00:00' as timestamp)"; //think this is what SSAS converts null dates to
}
}
else
{
if (di.NullProcessing == NullProcessing.Preserve)
{
sIsNull = "'1/1/1899 01:02:03 AM'"; //a unique value that shouldn't ever occur in the real data
}
else
{
sIsNull = "'12/30/1899'"; //think this is what SSAS converts null dates to
}
}
}
else if (dc.DataType == typeof(Guid)) // Guid
{
if (di.NullProcessing == NullProcessing.Preserve)
{
sIsNull = "'" + (new Guid()).ToString() + "'"; //a unique value that shouldn't ever occur in the real data
}
else
{
sIsNull = "'" + Guid.Empty.ToString() + "'";
}
}
else //numeric
{
if (di.NullProcessing == NullProcessing.Preserve)
{
sIsNull = "-987654321.123456789"; //a unique value that shouldn't ever occur in the real data
}
else
{
sIsNull = "0";
}
}
if (!string.IsNullOrEmpty(sForceDataTypeOnJoin))
{
join.Append((join.Length == 0 ? "on " : "and ")).Append("coalesce(cast(y.").Append(sq).Append(colAlias).Append(fq).Append(" as ").Append(sForceDataTypeOnJoin).Append("),").Append(sIsNull).Append(") = coalesce(cast(z.").Append(sq).Append(colAlias).Append(fq).Append(" as ").Append(sForceDataTypeOnJoin).Append("),").Append(sIsNull).AppendLine(")");
}
else
{
join.Append((join.Length == 0 ? "on " : "and ")).Append("coalesce(y.").Append(sq).Append(colAlias).Append(fq).Append(",").Append(sIsNull).Append(") = coalesce(z.").Append(sq).Append(colAlias).Append(fq).Append(",").Append(sIsNull).AppendLine(")");
}
groupBy.Append(sq).Append(colAlias).AppendLine(fq);
outerSelect.Append(sq).Append(colAlias).AppendLine(fq);
if (topLevelColumns.Length > 0)
{
topLevelColumns.Append(",");
}
topLevelColumns.Append("y.").Append(sq).Append(colAlias).Append(fq).Append(" as ").Append(sq).Append(dc.ColumnName).AppendLine(fq);
orderBy.Append((orderBy.Length == 0 ? "order by " : ","));
orderBy.Append("y.").Append(sq).Append(colAlias).AppendLine(fq);
if (!dc.ExtendedProperties.ContainsKey("ComputedColumnExpression"))
{
select.Append(sq).Append(dsv.Schema.Tables[col.TableID].ExtendedProperties["FriendlyName"].ToString()).Append(fq).Append(".").Append(sq).Append((dc.ExtendedProperties["DbColumnName"] ?? dc.ColumnName).ToString()).Append(fq).Append(" as ").Append(sq).Append(colAlias).AppendLine(fq);
}
else
{
select.AppendLine(dc.ExtendedProperties["ComputedColumnExpression"].ToString());
select.Append(" as ").Append(sq).Append(colAlias).AppendLine(fq);
}
if (!tables.ContainsKey(dsv.Schema.Tables[col.TableID]))
{
tables.Add(dsv.Schema.Tables[col.TableID], new JoinedTable(dsv.Schema.Tables[col.TableID]));
}
previousColumns.Add("[" + col.TableID + "].[" + col.ColumnID + "]");
}
}
foreach (DataItem di in parent)
{
ColumnBinding col = GetColumnBindingForDataItem(di);
if (!previousColumns.Contains("[" + col.TableID + "].[" + col.ColumnID + "]"))
{
string colAlias = GetNextUniqueColumnIdentifier();
DataColumn dc = dsv.Schema.Tables[col.TableID].Columns[col.ColumnID];
select.Append(",");
//use the __PARENT__ prefix in case there's a column with the same name but different table
if (!dc.ExtendedProperties.ContainsKey("ComputedColumnExpression"))
{
select.Append(sq).Append(dsv.Schema.Tables[col.TableID].ExtendedProperties["FriendlyName"].ToString()).Append(fq).Append(".").Append(sq).Append((dc.ExtendedProperties["DbColumnName"] ?? dc.ColumnName).ToString()).Append(fq).Append(" as ").Append(sq).Append(colAlias).AppendLine(fq);
}
else
{
select.AppendLine(dc.ExtendedProperties["ComputedColumnExpression"].ToString());
select.Append(" as ").Append(sq).Append(colAlias).AppendLine(fq);
}
if (topLevelColumns.Length > 0)
{
topLevelColumns.Append(",");
}
topLevelColumns.Append("y.").Append(sq).Append(colAlias).Append(fq).Append(" as ").Append(sq).Append(dc.ColumnName).AppendLine(fq);
orderBy.Append((orderBy.Length == 0 ? "order by " : ","));
orderBy.Append("y.").Append(sq).Append(colAlias).AppendLine(fq);
if (!tables.ContainsKey(dsv.Schema.Tables[col.TableID]))
{
tables.Add(dsv.Schema.Tables[col.TableID], new JoinedTable(dsv.Schema.Tables[col.TableID]));
}
previousColumns.Add("[" + col.TableID + "].[" + col.ColumnID + "]");
}
}
int iLastTableCount = 0;
while (iLastTableCount != tables.Values.Count)
{
iLastTableCount = tables.Values.Count;
JoinedTable[] arrJt = new JoinedTable[iLastTableCount];
tables.Values.CopyTo(arrJt, 0); //because you can't iterate the dictionary keys while they are changing
foreach (JoinedTable jt in arrJt)
{
TraverseParentRelationshipsAndAddNewTables(tables, jt.table);
}
}
//check that all but one table have a valid join path to them
DataTable baseTable = null;
foreach (JoinedTable t in tables.Values)
{
if (!t.Joined)
{
if (baseTable == null)
{
baseTable = t.table;
}
else
{
throw new Exception("Cannot find join path for table " + t.table.TableName + " or " + baseTable.TableName + ". Only one table can be the starting table for the joins.");
}
}
}
if (baseTable == null)
{
//there are joins to and from the base table, so guess at the base table based on the child parameter to this function
ColumnBinding col = GetColumnBindingForDataItem(child[0]);
baseTable = dsv.Schema.Tables[col.TableID];
}
//by now, all tables needed for joins will be in the dictionary
select.Append("\r\nfrom ").AppendLine(GetFromClauseForTable(baseTable));
select.Append(TraverseParentRelationshipsAndGetFromClause(tables, baseTable));
outerSelect.AppendLine("\r\nfrom (").Append(select).AppendLine(") x").Append(groupBy).AppendLine("\r\nhaving count(*)>1");
string invalidValuesInner = outerSelect.ToString();
outerSelect = new StringBuilder();
outerSelect.Append("select ").AppendLine(topLevelColumns.ToString()).AppendLine(" from (").Append(select).AppendLine(") y");
outerSelect.AppendLine("join (").AppendLine(invalidValuesInner).AppendLine(") z").AppendLine(join.ToString()).AppendLine(orderBy.ToString());
return(outerSelect.ToString());
}
public void Visit(JoinedTable table)
{
Visit(table.Type);
table.RigthTable.Accept(this);
if (table.SearchCondition != null)
{
_Query.Append("ON ");
table.SearchCondition.Accept(this);
}
}
private IQueryElement ConvertInternal(IQueryElement element, Func <IQueryElement, IQueryElement> action)
{
if (element == null)
{
return(null);
}
IQueryElement newElement;
if (_visitedElements.TryGetValue(element, out newElement))
{
return(newElement);
}
switch (element.ElementType)
{
case EQueryElementType.SqlFunction:
{
var func = (ISqlFunction)element;
var parms = Convert(func.Parameters, action);
if (parms != null && !ReferenceEquals(parms, func.Parameters))
{
newElement = new SqlFunction(func.SystemType, func.Name, func.Precedence, parms);
}
break;
}
case EQueryElementType.SqlExpression:
{
var expr = (ISqlExpression)element;
var parameter = Convert(expr.Parameters, action);
if (parameter != null && !ReferenceEquals(parameter, expr.Parameters))
{
newElement = new SqlExpression(expr.SystemType, expr.Expr, expr.Precedence, parameter);
}
break;
}
case EQueryElementType.SqlBinaryExpression:
{
var bexpr = (ISqlBinaryExpression)element;
var expr1 = (IQueryExpression)ConvertInternal(bexpr.Expr1, action);
var expr2 = (IQueryExpression)ConvertInternal(bexpr.Expr2, action);
if (expr1 != null && !ReferenceEquals(expr1, bexpr.Expr1) ||
expr2 != null && !ReferenceEquals(expr2, bexpr.Expr2))
{
newElement = new SqlBinaryExpression(bexpr.SystemType, expr1 ?? bexpr.Expr1, bexpr.Operation,
expr2 ?? bexpr.Expr2, bexpr.Precedence);
}
break;
}
case EQueryElementType.SqlTable:
{
var table = (ISqlTable)element;
var fields1 = ToArray(table.Fields);
var fields2 = Convert(fields1, action, f => new SqlField(f));
var targs = table.TableArguments == null
? null
: Convert(table.TableArguments, action);
var fe = fields2 == null || ReferenceEquals(fields1, fields2);
var ta = ReferenceEquals(table.TableArguments, targs);
if (!fe || !ta)
{
if (fe)
{
fields2 = fields1;
for (var i = 0; i < fields2.Length; i++)
{
var field = fields2[i];
fields2[i] = new SqlField(field);
_visitedElements[field] = fields2[i];
}
}
newElement = new SqlTable(table, fields2, targs ?? table.TableArguments);
}
break;
}
case EQueryElementType.Column:
{
var col = (IColumn)element;
var expr = (IQueryExpression)ConvertInternal(col.Expression, action);
IQueryElement parent;
_visitedElements.TryGetValue(col.Parent, out parent);
if (parent != null || expr != null && !ReferenceEquals(expr, col.Expression))
{
newElement = new Column(parent == null
? col.Parent
: (ISelectQuery)parent, expr ?? col.Expression, col.Alias);
}
break;
}
case EQueryElementType.TableSource:
{
var table = (ITableSource)element;
var source = (ISqlTableSource)ConvertInternal(table.Source, action);
var joins = Convert(table.Joins, action);
if (source != null && !ReferenceEquals(source, table.Source) ||
joins != null && !ReferenceEquals(table.Joins, joins))
{
newElement = new TableSource(source ?? table.Source, table.Alias, joins ?? table.Joins);
}
break;
}
case EQueryElementType.JoinedTable:
{
var join = (IJoinedTable)element;
var table = (ITableSource)ConvertInternal(join.Table, action);
var cond = (ISearchCondition)ConvertInternal(join.Condition, action);
if (table != null && !ReferenceEquals(table, join.Table) ||
cond != null && !ReferenceEquals(cond, join.Condition))
{
newElement = new JoinedTable(join.JoinType, table ?? join.Table, join.IsWeak,
cond ?? join.Condition);
}
break;
}
case EQueryElementType.SearchCondition:
{
var sc = (ISearchCondition)element;
var conds = Convert(sc.Conditions, action);
if (conds != null && !ReferenceEquals(sc.Conditions, conds))
{
newElement = new SearchCondition(conds);
}
break;
}
case EQueryElementType.Condition:
{
var c = (Condition)element;
var p = (ISqlPredicate)ConvertInternal(c.Predicate, action);
if (p != null && !ReferenceEquals(c.Predicate, p))
{
newElement = new Condition(c.IsNot, p, c.IsOr);
}
break;
}
case EQueryElementType.ExprPredicate:
{
var p = (IExpr)element;
var e = (IQueryExpression)ConvertInternal(p.Expr1, action);
if (e != null && !ReferenceEquals(p.Expr1, e))
{
newElement = new Expr(e, p.Precedence);
}
break;
}
case EQueryElementType.NotExprPredicate:
{
var p = (INotExpr)element;
var e = (IQueryExpression)ConvertInternal(p.Expr1, action);
if (e != null && !ReferenceEquals(p.Expr1, e))
{
newElement = new NotExpr(e, p.IsNot, p.Precedence);
}
break;
}
case EQueryElementType.ExprExprPredicate:
{
var p = (IExprExpr)element;
var e1 = (IQueryExpression)ConvertInternal(p.Expr1, action);
var e2 = (IQueryExpression)ConvertInternal(p.Expr2, action);
if (e1 != null && !ReferenceEquals(p.Expr1, e1) || e2 != null && !ReferenceEquals(p.Expr2, e2))
{
newElement = new ExprExpr(e1 ?? p.Expr1, p.EOperator, e2 ?? p.Expr2);
}
break;
}
case EQueryElementType.LikePredicate:
{
var p = (ILike)element;
var e1 = (IQueryExpression)ConvertInternal(p.Expr1, action);
var e2 = (IQueryExpression)ConvertInternal(p.Expr2, action);
var es = (IQueryExpression)ConvertInternal(p.Escape, action);
if (e1 != null && !ReferenceEquals(p.Expr1, e1) || e2 != null && !ReferenceEquals(p.Expr2, e2) ||
es != null && !ReferenceEquals(p.Escape, es))
{
newElement = new Like(e1 ?? p.Expr1, p.IsNot, e2 ?? p.Expr2, es ?? p.Escape);
}
break;
}
case EQueryElementType.HierarhicalPredicate:
{
var p = (IHierarhicalPredicate)element;
var e1 = (IQueryExpression)ConvertInternal(p.Expr1, action);
var e2 = (IQueryExpression)ConvertInternal(p.Expr2, action);
if (e1 != null && !ReferenceEquals(p.Expr1, e1) || e2 != null && !ReferenceEquals(p.Expr2, e2))
{
newElement = new HierarhicalPredicate(e1 ?? p.Expr1, e2 ?? p.Expr2, p.Flow);
}
break;
}
case EQueryElementType.BetweenPredicate:
{
var p = (IBetween)element;
var e1 = (IQueryExpression)ConvertInternal(p.Expr1, action);
var e2 = (IQueryExpression)ConvertInternal(p.Expr2, action);
var e3 = (IQueryExpression)ConvertInternal(p.Expr3, action);
if (e1 != null && !ReferenceEquals(p.Expr1, e1) || e2 != null && !ReferenceEquals(p.Expr2, e2) ||
e3 != null && !ReferenceEquals(p.Expr3, e3))
{
newElement = new Between(e1 ?? p.Expr1, p.IsNot, e2 ?? p.Expr2, e3 ?? p.Expr3);
}
break;
}
case EQueryElementType.IsNullPredicate:
{
var p = (IIsNull)element;
var e = (IQueryExpression)ConvertInternal(p.Expr1, action);
if (e != null && !ReferenceEquals(p.Expr1, e))
{
newElement = new IsNull(e, p.IsNot);
}
break;
}
case EQueryElementType.InSubQueryPredicate:
{
var p = (IInSubQuery)element;
var e = (IQueryExpression)ConvertInternal(p.Expr1, action);
var q = (ISelectQuery)ConvertInternal(p.SubQuery, action);
if (e != null && !ReferenceEquals(p.Expr1, e) || q != null && !ReferenceEquals(p.SubQuery, q))
{
newElement = new InSubQuery(e ?? p.Expr1, p.IsNot, q ?? p.SubQuery);
}
break;
}
case EQueryElementType.InListPredicate:
{
var p = (IInList)element;
var e = (IQueryExpression)ConvertInternal(p.Expr1, action);
var v = Convert(p.Values, action);
if (e != null && !ReferenceEquals(p.Expr1, e) || v != null && !ReferenceEquals(p.Values, v))
{
newElement = new InList(e ?? p.Expr1, p.IsNot, v ?? p.Values);
}
break;
}
case EQueryElementType.FuncLikePredicate:
{
var p = (IFuncLike)element;
var f = (ISqlFunction)ConvertInternal(p.Function, action);
if (f != null && !ReferenceEquals(p.Function, f))
{
newElement = new FuncLike(f);
}
break;
}
case EQueryElementType.SetExpression:
{
var s = (ISetExpression)element;
var c = (IQueryExpression)ConvertInternal(s.Column, action);
var e = (IQueryExpression)ConvertInternal(s.Expression, action);
if (c != null && !ReferenceEquals(s.Column, c) || e != null && !ReferenceEquals(s.Expression, e))
{
newElement = new SetExpression(c ?? s.Column, e ?? s.Expression);
}
break;
}
case EQueryElementType.InsertClause:
{
var s = (IInsertClause)element;
var t = s.Into != null
? (ISqlTable)ConvertInternal(s.Into, action)
: null;
var i = Convert(s.Items, action);
if (t != null && !ReferenceEquals(s.Into, t) || i != null && !ReferenceEquals(s.Items, i))
{
var sc = new InsertClause
{
Into = t ?? s.Into
};
(i ?? s.Items).ForEach(node => sc.Items.AddLast(node.Value));
sc.WithIdentity = s.WithIdentity;
newElement = sc;
}
break;
}
case EQueryElementType.UpdateClause:
{
var s = (IUpdateClause)element;
var t = s.Table != null
? (ISqlTable)ConvertInternal(s.Table, action)
: null;
var i = Convert(s.Items, action);
var k = Convert(s.Keys, action);
if (t != null && !ReferenceEquals(s.Table, t) || i != null && !ReferenceEquals(s.Items, i) ||
k != null && !ReferenceEquals(s.Keys, k))
{
var sc = new UpdateClause
{
Table = t ?? s.Table
};
(i ?? s.Items).ForEach(node => sc.Items.AddLast(node.Value));
(k ?? s.Keys).ForEach(node => sc.Keys.AddLast(node.Value));
newElement = sc;
}
break;
}
case EQueryElementType.DeleteClause:
{
var s = (IDeleteClause)element;
var t = s.Table != null
? (ISqlTable)ConvertInternal(s.Table, action)
: null;
if (t != null && !ReferenceEquals(s.Table, t))
{
newElement = new DeleteClause
{
Table = t
};
}
break;
}
case EQueryElementType.CreateTableStatement:
{
var s = (ICreateTableStatement)element;
var t = s.Table != null
? (ISqlTable)ConvertInternal(s.Table, action)
: null;
if (t != null && !ReferenceEquals(s.Table, t))
{
newElement = new CreateTableStatement
{
Table = t,
IsDrop = s.IsDrop
};
}
break;
}
case EQueryElementType.SelectClause:
{
var sc = (ISelectClause)element;
var cols = Convert(sc.Columns, action);
var take = (IQueryExpression)ConvertInternal(sc.TakeValue, action);
var skip = (IQueryExpression)ConvertInternal(sc.SkipValue, action);
IQueryElement parent;
_visitedElements.TryGetValue(sc.SelectQuery, out parent);
if (parent != null || cols != null && !ReferenceEquals(sc.Columns, cols) ||
take != null && !ReferenceEquals(sc.TakeValue, take) ||
skip != null && !ReferenceEquals(sc.SkipValue, skip))
{
newElement = new SelectClause(sc.IsDistinct, take ?? sc.TakeValue, skip ?? sc.SkipValue,
cols ?? sc.Columns);
((ISelectClause)newElement).SetSqlQuery((ISelectQuery)parent);
}
break;
}
case EQueryElementType.FromClause:
{
var fc = (IFromClause)element;
var ts = Convert(fc.Tables, action);
IQueryElement parent;
_visitedElements.TryGetValue(fc.SelectQuery, out parent);
if (parent != null || ts != null && !ReferenceEquals(fc.Tables, ts))
{
newElement = new FromClause(ts ?? fc.Tables);
((IFromClause)newElement).SetSqlQuery((ISelectQuery)parent);
}
break;
}
case EQueryElementType.WhereClause:
{
var wc = (IWhereClause)element;
var cond = (ISearchCondition)ConvertInternal(wc.Search, action);
IQueryElement parent;
_visitedElements.TryGetValue(wc.SelectQuery, out parent);
if (parent != null || cond != null && !ReferenceEquals(wc.Search, cond))
{
newElement = new WhereClause(cond ?? wc.Search);
((IWhereClause)newElement).SetSqlQuery((ISelectQuery)parent);
}
break;
}
case EQueryElementType.GroupByClause:
{
var gc = (IGroupByClause)element;
var es = Convert(gc.Items, action);
IQueryElement parent;
_visitedElements.TryGetValue(gc.SelectQuery, out parent);
if (parent != null || es != null && !ReferenceEquals(gc.Items, es))
{
newElement = new GroupByClause(es ?? gc.Items);
((IGroupByClause)newElement).SetSqlQuery((ISelectQuery)parent);
}
break;
}
case EQueryElementType.OrderByClause:
{
var oc = (IOrderByClause)element;
var es = Convert(oc.Items, action);
IQueryElement parent;
_visitedElements.TryGetValue(oc.SelectQuery, out parent);
if (parent != null || es != null && !ReferenceEquals(oc.Items, es))
{
newElement = new OrderByClause(es ?? oc.Items);
((IOrderByClause)newElement).SetSqlQuery((ISelectQuery)parent);
}
break;
}
case EQueryElementType.OrderByItem:
{
var i = (IOrderByItem)element;
var e = (IQueryExpression)ConvertInternal(i.Expression, action);
if (e != null && !ReferenceEquals(i.Expression, e))
{
newElement = new OrderByItem(e, i.IsDescending);
}
break;
}
case EQueryElementType.Union:
{
var u = (IUnion)element;
var q = (ISelectQuery)ConvertInternal(u.SelectQuery, action);
if (q != null && !ReferenceEquals(u.SelectQuery, q))
{
newElement = new Union(q, u.IsAll);
}
break;
}
case EQueryElementType.SqlQuery:
{
var q = (ISelectQuery)element;
IQueryElement parent = null;
var doConvert = false;
if (q.ParentSelect != null)
{
if (!_visitedElements.TryGetValue(q.ParentSelect, out parent))
{
doConvert = true;
parent = q.ParentSelect;
}
}
if (!doConvert)
{
doConvert = null != FindFirstOrDefault <IQueryElement>(q, e =>
{
if (_visitedElements.ContainsKey(e) && _visitedElements[e] != e)
{
return(true);
}
var ret = action(e);
if (ret != null && !ReferenceEquals(e, ret))
{
_visitedElements.Add(e, ret);
return(true);
}
return(false);
});
}
if (!doConvert)
{
break;
}
var nq = new SelectQuery
{
EQueryType = q.EQueryType
};
_visitedElements.Add(q, nq);
var fc = (IFromClause)ConvertInternal(q.From, action) ?? q.From;
var sc = (ISelectClause)ConvertInternal(q.Select, action) ?? q.Select;
var ic = q.IsInsert
? ((IInsertClause)ConvertInternal(q.Insert, action) ?? q.Insert)
: null;
var uc = q.IsUpdate
? ((IUpdateClause)ConvertInternal(q.Update, action) ?? q.Update)
: null;
var dc = q.IsDelete
? ((IDeleteClause)ConvertInternal(q.Delete, action) ?? q.Delete)
: null;
var wc = (IWhereClause)ConvertInternal(q.Where, action) ?? q.Where;
var gc = (IGroupByClause)ConvertInternal(q.GroupBy, action) ?? q.GroupBy;
var hc = (IWhereClause)ConvertInternal(q.Having, action) ?? q.Having;
var oc = (IOrderByClause)ConvertInternal(q.OrderBy, action) ?? q.OrderBy;
var us = q.HasUnion
? Convert(q.Unions, action)
: q.Unions;
var ps = new List <ISqlParameter>(q.Parameters.Count);
foreach (var p in q.Parameters)
{
IQueryElement e;
if (_visitedElements.TryGetValue(p, out e))
{
if (e == null)
{
ps.Add(p);
}
else
{
var sqlParameter = e as ISqlParameter;
if (sqlParameter != null)
{
ps.Add(sqlParameter);
}
}
}
}
nq.Init(ic, uc, dc, sc, fc, wc, gc, hc, oc, us, (ISelectQuery)parent, q.CreateTable,
q.IsParameterDependent, ps);
_visitedElements[q] = action(nq) ?? nq;
return(nq);
}
}
newElement = newElement == null
? action(element)
: (action(newElement) ?? newElement);
_visitedElements.Add(element, newElement);
return(newElement);
}
private static string GetSnowflakeQuery(DataSourceView dsv, ColumnBinding[] child, ColumnBinding[] parent, ColumnBinding prefer)
{
StringBuilder select = new StringBuilder();
Dictionary<DataTable, JoinedTable> tables = new Dictionary<DataTable, JoinedTable>();
List<string> previousColumns = new List<string>();
foreach (ColumnBinding col in child)
{
if (!previousColumns.Contains("[" + col.TableID + "].[" + col.ColumnID + "]"))
{
DataColumn dc = dsv.Schema.Tables[col.TableID].Columns[col.ColumnID];
select.Append((select.Length == 0 ? "select " : ","));
if (!dc.ExtendedProperties.ContainsKey("ComputedColumnExpression"))
{
select.Append("[").Append(dsv.Schema.Tables[col.TableID].ExtendedProperties["FriendlyName"].ToString()).Append("].[").Append(GetColumnName(dc)).AppendLine("]");
}
else
{
select.Append("[").Append(GetColumnName(dc)).Append("] = ");
select.AppendLine(dc.ExtendedProperties["ComputedColumnExpression"].ToString());
}
if (!tables.ContainsKey(dsv.Schema.Tables[col.TableID]))
{
tables.Add(dsv.Schema.Tables[col.TableID], new JoinedTable(dsv.Schema.Tables[col.TableID]));
}
previousColumns.Add("[" + col.TableID + "].[" + col.ColumnID + "]");
}
}
foreach (ColumnBinding col in parent)
{
if (!previousColumns.Contains("[" + col.TableID + "].[" + col.ColumnID + "]"))
{
DataColumn dc = dsv.Schema.Tables[col.TableID].Columns[col.ColumnID];
select.Append(",");
if (!dc.ExtendedProperties.ContainsKey("ComputedColumnExpression"))
{
select.Append("[").Append(dsv.Schema.Tables[col.TableID].ExtendedProperties["FriendlyName"].ToString()).Append("].[").Append(GetColumnName(dc)).AppendLine("]");
}
else
{
select.Append("[").Append(GetColumnName(dc)).Append("] = ");
select.AppendLine(dc.ExtendedProperties["ComputedColumnExpression"].ToString());
}
if (!tables.ContainsKey(dsv.Schema.Tables[col.TableID]))
{
tables.Add(dsv.Schema.Tables[col.TableID], new JoinedTable(dsv.Schema.Tables[col.TableID]));
}
previousColumns.Add("[" + col.TableID + "].[" + col.ColumnID + "]");
}
}
int iLastTableCount = 0;
while (iLastTableCount != tables.Values.Count)
{
iLastTableCount = tables.Values.Count;
JoinedTable[] arrJt = new JoinedTable[iLastTableCount];
tables.Values.CopyTo(arrJt, 0); //because you can't iterate the dictionary keys while they are changing
foreach (JoinedTable jt in arrJt)
{
TraverseParentRelationshipsAndAddNewTables(tables, jt.table);
}
}
//check that all but one table have a valid join path to them
DataTable baseTable = null;
foreach (JoinedTable t in tables.Values)
{
if (!t.Joined)
{
if (baseTable == null)
{
baseTable = t.table;
}
else
{
throw new Exception("Cannot find join path for table " + t.table.TableName + " or " + baseTable.TableName + ". Only one table can be the starting table for the joins.");
}
}
}
//by now, all tables needed for joins will be in the dictionary
select.Append("\r\nfrom ").AppendLine(GetFromClauseForTable(baseTable));
select.Append(TraverseParentRelationshipsAndGetFromClause(tables, baseTable, prefer));
return select.ToString();
}
