private void VisitCorrelationStatement(CorrelationStatement statement, string functionName)
{
SelectStatement statement1 = (SelectStatement)statement.First;
SelectStatement statement2 = (SelectStatement)statement.Second;
if (((statement1.From.Count != 1) && (!(statement1.From[0].Source is Name))) ||
((statement2.From.Count != 1) && (!(statement2.From[0].Source is Name))))
{
throw new NotImplementedException();
}
else if ((statement1.Output.Count != statement2.Output.Count) && (statement1.Output.Count == 0))
{
throw new NotImplementedException();
}
string table1 = ((Name)(statement1.From[0].Source)).LastPart;
string table2 = ((Name)(statement2.From[0].Source)).LastPart;
ExpressionToken whereExpression = null;
List <ExpressionToken> expressions1 = new List <ExpressionToken>();
List <ExpressionToken> expressions2 = new List <ExpressionToken>();
for (int i = 0; i < statement1.Output.Count; i++)
{
if ((!(statement1.Output[i] is Name)) && (!(statement2.Output[i] is Name)))
{
throw new NotImplementedException();
}
expressions1.Add(Sql.Name(table1, ((Name)(statement1.Output[i])).LastPart));
expressions2.Add(Sql.Name(table2, ((Name)(statement2.Output[i])).LastPart));
if (whereExpression == null)
{
whereExpression = expressions1[i].IsEqual(expressions2[i]);
}
else
{
whereExpression = whereExpression.And(expressions1[i].IsEqual(expressions2[i]));
}
}
if (statement2.Where != null)
{
whereExpression = whereExpression.And(AddPrefixToExpressionToken(((ExpressionToken)statement2.Where), table2));
}
ExpressionToken tempExpression = Sql.Function(functionName, Sql.Select.From(table2).Where(whereExpression));
if (statement1.Where != null)
{
tempExpression = tempExpression.And(AddPrefixToExpressionToken(((ExpressionToken)statement1.Where), table1));
}
SelectStatement correlationStatement =
Sql.Select.Output(expressions1).From(table1)
.Where(tempExpression);
VisitStatement(correlationStatement);
}
protected void VisitWhenNotMatchedBySourceToken(MergeStatement statement
, string targetAlias
, string targetTable
, string targetColumnOn
, string sourceAlias
, string sourceTable
, string sourceColumnOn
, bool isTop
)
{
ExpressionToken tempExpression = new ExpressionToken();
if (statement.WhenNotMatchedBySource.Count != 0)
{
foreach (WhenMatchedToken item in statement.WhenNotMatchedBySource)
{
tempExpression = Sql.Name(targetAlias, targetColumnOn)
.NotIn(Sql.Select.Output(sourceColumnOn).From(sourceTable));
if (item.AndCondition != null)
{
if (item.AndCondition is BinaryToken)
{
((BinaryToken)item.AndCondition).First = Sql.Name(targetAlias, ((Name)((BinaryToken)item.AndCondition).First).LastPart);
}
else if (item.AndCondition is UnaryToken)
{
((UnaryToken)item.AndCondition).Token = Sql.Name(targetAlias, ((Name)((UnaryToken)item.AndCondition).Token).LastPart);
}
tempExpression = tempExpression.And((ExpressionToken)item.AndCondition);
}
if (isTop)
{
tempExpression = tempExpression.And(Sql.Name(targetAlias, targetColumnOn)
.In(Sql.Select.Output(sourceColumnOn).From(TopAlias)));
}
DeleteStatement whenNotMatchedBySourceDelete =
Sql.Delete.From(Sql.NameAs(targetTable, targetAlias))
.Where(tempExpression);
VisitStatement(whenNotMatchedBySourceDelete);
State.WriteStatementTerminator();
}
}
}
protected void VisitWhenNotMatchedThenInsertToken(MergeStatement statement
, IList <string> tempUpdateAliases
, string targetAlias
, string targetColumnOn
, string targetTable
, string sourceAlias
, string sourceTable
, string sourceColumnOn)
{
ExpressionToken tempExpression = new ExpressionToken();
if (statement.WhenNotMatched.Count != 0)
{
int counter = 0;
foreach (WhenMatchedToken item in statement.WhenNotMatched)
{
IList <CTEDefinition> tmpSourceList = new List <CTEDefinition>();
tempExpression = Sql.Name(sourceAlias, sourceColumnOn)
.NotIn(Sql.Select.Output(targetColumnOn).From(targetTable));
if (item.AndCondition != null)
{
if (item.AndCondition is BinaryToken)
{
((BinaryToken)item.AndCondition).First = Sql.Name(sourceAlias, ((Name)((BinaryToken)item.AndCondition).First).LastPart);
}
else if (item.AndCondition is UnaryToken)
{
((UnaryToken)item.AndCondition).Token = Sql.Name(sourceAlias, ((Name)((UnaryToken)item.AndCondition).Token).LastPart);
}
tempExpression = tempExpression.And((ExpressionToken)item.AndCondition);
}
CTEDefinition source = Sql.With(sourceAlias).As(
Sql.Select.From(Sql.NameAs(sourceTable, sourceAlias)).Where(tempExpression));
tmpSourceList.Add(source);
InsertStatement insertStatement = Sql.Insert.Into(targetTable);
if ((((WhenNotMatchedTokenThenInsertToken)item).Values.Count == 0) ||
(((WhenNotMatchedTokenThenInsertToken)item).Columns.Count == 0) ||
(((WhenNotMatchedTokenThenInsertToken)item).Columns.Count != ((WhenNotMatchedTokenThenInsertToken)item).Values.Count))
{
insertStatement.Columns.Add(targetColumnOn);
insertStatement.From(Sql.Select.From(sourceAlias).Output(sourceColumnOn));
}
else
{
foreach (Name columnName in ((WhenNotMatchedTokenThenInsertToken)item).Columns)
{
insertStatement.Columns.Add(Sql.Name(columnName.LastPart));
}
SelectStatement fromSelect = Sql.Select.From(sourceAlias);
foreach (Token outputColumn in ((WhenNotMatchedTokenThenInsertToken)item).Values)
{
fromSelect.Output.Add(Sql.Name(((Name)outputColumn).LastPart));
}
insertStatement.From(fromSelect);
}
VisitCommonTableExpressions(tmpSourceList);
VisitStatement(insertStatement);
State.WriteStatementTerminator();
InsertStatement whenNotMatchedInsert =
Sql.Insert.Into(Sql.Name(targetTable))
.From(Sql.Select.From(Sql.Name(sourceAlias))
.Where(tempExpression));
counter++;
}
}
}
protected void VisitWhenMatchedDeleteToken(MergeStatement statement
, string targetAlias
, string targetTable
, string targetColumnOn
, string sourceAlias
, string sourceTable
, string sourceColumnOn
, bool isTop)
{
ExpressionToken tempExpression = new ExpressionToken();
if (statement.WhenMatched != null)
{
foreach (WhenMatchedToken item in statement.WhenMatched)
{
bool isTargetCondition = true;
SelectStatement sourceSelect = Sql.Select.Output(sourceColumnOn).From(sourceTable);
if (item.AndCondition != null)
{
if ((item.AndCondition is BinaryToken) &&
(
(((Name)((BinaryToken)item.AndCondition).First).FirstPart == sourceAlias) ||
((Name)((BinaryToken)item.AndCondition).First).FirstPart == sourceTable)
)
{
((BinaryToken)item.AndCondition).First = Sql.Name(sourceTable, ((Name)((BinaryToken)item.AndCondition).First).LastPart);
sourceSelect.Where((BinaryToken)item.AndCondition);
isTargetCondition = false;
}
else if ((item.AndCondition is UnaryToken) &&
(
(((Name)((UnaryToken)item.AndCondition).Token).FirstPart == sourceAlias) ||
(((Name)((UnaryToken)item.AndCondition).Token).FirstPart == sourceTable)
)
)
{
((UnaryToken)item.AndCondition).Token = Sql.Name(sourceTable, ((Name)((UnaryToken)item.AndCondition).Token).LastPart);
sourceSelect.Where((UnaryToken)item.AndCondition);
isTargetCondition = false;
}
}
tempExpression = Sql.Name(targetAlias, targetColumnOn)
.In(sourceSelect);
if ((item.AndCondition != null) && isTargetCondition)
{
if (item.AndCondition is BinaryToken)
{
((BinaryToken)item.AndCondition).First = Sql.Name(targetAlias, ((Name)((BinaryToken)item.AndCondition).First).LastPart);
}
else if (item.AndCondition is UnaryToken)
{
((UnaryToken)item.AndCondition).Token = Sql.Name(targetAlias, ((Name)((UnaryToken)item.AndCondition).Token).LastPart);
}
tempExpression = tempExpression.And((ExpressionToken)item.AndCondition);
}
if (isTop)
{
tempExpression = tempExpression.And(Sql.Name(targetAlias, targetColumnOn)
.In(Sql.Select.Output(sourceColumnOn).From(TopAlias)));
}
if ((item is WhenMatchedTokenThenDeleteToken))
{
DeleteStatement deleteStatement = Sql.Delete.From(Sql.NameAs(targetTable, targetAlias))
.Where(tempExpression);
VisitStatement(deleteStatement);
State.WriteStatementTerminator();
}
}
}
}
protected void VisitWhenMatchedUpdateToken(MergeStatement statement
, IList <string> tempUpdateAliases
, string targetAlias
, string targetTable
, string targetColumnOn
, string sourceAlias
, string sourceTable
, string sourceColumnOn
, bool isTop)
{
int counter = 0;
ExpressionToken tempExpression = new ExpressionToken();
if (statement.WhenMatched != null)
{
foreach (WhenMatchedToken item in statement.WhenMatched)
{
if (!(item is WhenMatchedTokenThenDeleteToken))
{
IList <CTEDefinition> updatedList = new List <CTEDefinition>();
string tempAlias = "tmp_" + counter;
tempExpression = (IsEqualsToken)(statement.On);
if (((WhenMatchedTokenThenUpdateSetToken)item).Set.Count != 0)
{
foreach (BinaryEqualToken setItem in ((WhenMatchedTokenThenUpdateSetToken)item).Set)
{
if (setItem.First is Name)
{
setItem.First = Sql.Name(((Name)setItem.First).LastPart);
}
if (setItem.Second is Name)
{
setItem.Second = Sql.Name(sourceAlias, ((Name)setItem.Second).LastPart);
}
}
}
if (item.AndCondition != null)
{
if (item.AndCondition is BinaryToken)
{
((BinaryToken)item.AndCondition).First = Sql.Name(targetAlias, ((Name)((BinaryToken)item.AndCondition).First).LastPart);
}
else if (item.AndCondition is UnaryToken)
{
((UnaryToken)item.AndCondition).Token = Sql.Name(targetAlias, ((Name)((UnaryToken)item.AndCondition).Token).LastPart);
}
tempExpression = tempExpression.And((ExpressionToken)item.AndCondition);
}
if (isTop)
{
tempExpression = tempExpression.And(Sql.Name(targetAlias, targetColumnOn)
.In(Sql.Select.Output(sourceColumnOn).From(TopAlias)));
}
CreateTableStatement createTempTable =
Sql.CreateTemporaryTable(tempAlias)
.As(Sql.With("updated").As(
Sql.Update(Sql.NameAs(targetTable, targetAlias))
.Set(((WhenMatchedTokenThenUpdateSetToken)item).Set)
.From(Sql.NameAs(sourceTable, sourceAlias))
.Where(tempExpression)
.Output(Sql.Name(targetAlias, targetColumnOn)))
.Select().From("updated").Output(targetColumnOn));
VisitStatement(createTempTable);
State.WriteStatementTerminator();
tempUpdateAliases.Add(tempAlias);
counter++;
State.WriteStatementTerminator();
}
}
}
}
private void VisitCorrelationStatement(CorrelationStatement statement, string functionName)
{
SelectStatement statement1 = (SelectStatement)statement.First;
SelectStatement statement2 = (SelectStatement)statement.Second;
if (((statement1.From.Count != 1) && (!(statement1.From[0].Source is Name))) ||
((statement2.From.Count != 1) && (!(statement2.From[0].Source is Name))))
{
throw new NotImplementedException();
}
else if ((statement1.Output.Count != statement2.Output.Count) && (statement1.Output.Count == 0))
{
throw new NotImplementedException();
}
string table1 = ((Name)(statement1.From[0].Source)).LastPart;
string table2 = ((Name)(statement2.From[0].Source)).LastPart;
var table1Name = ((Name)(statement1.From[0].Source));
var table2Name = ((Name)(statement2.From[0].Source));
var statement1Alias = statement1.Alias;
//here we have 2 possible cases
//case 1) table1Name equals table2Name
// In this case "EXIST" statement will not work properly.To fix that, first statement, including it's own "WHERE" expression
//should be placed inside subquery with alias
//case 2) table1Name not equal table2Name
// in this case it is not nessesary to use subquery for first statement. Just place WHERE clause to EXIST part
var isSameTableMode = string.Equals($"{table1Name.FirstPart}.{table1Name.LastPart}"
, $"{table2Name.FirstPart}.{table2Name.LastPart}"
, StringComparison.OrdinalIgnoreCase);
ExpressionToken whereExpression = null;
List <ExpressionToken> expressions1 = new List <ExpressionToken>();
List <ExpressionToken> expressions2 = new List <ExpressionToken>();
for (int i = 0; i < statement1.Output.Count; i++)
{
if ((!(statement1.Output[i] is Name)) && (!(statement2.Output[i] is Name)))
{
throw new NotImplementedException();
}
Name name1 = null;
if (isSameTableMode)
{
// use alias of first statements since we will use subquery for it
name1 = Sql.Name(statement1Alias, ((Name)(statement1.Output[i])).Alias).As(((Name)(statement1.Output[i])).Alias);
}
else
{
//use table name or alias directly
if (!string.IsNullOrWhiteSpace(table1Name.Alias))
{
name1 = Sql.Name(table1Name.Alias, ((Name)(statement1.Output[i])).LastPart).As(((Name)(statement1.Output[i])).Alias);
}
else
{
name1 = Sql.Name(table1Name.FirstPart, table1Name.LastPart, ((Name)(statement1.Output[i])).LastPart).As(((Name)(statement1.Output[i])).Alias);
}
}
expressions1.Add(name1);
if (!string.IsNullOrWhiteSpace(table2Name.Alias))
{
expressions2.Add(Sql.Name(table2Name.Alias, ((Name)(statement2.Output[i])).LastPart).As(((Name)(statement2.Output[i])).Alias));
}
else
{
expressions2.Add(Sql.Name(table2Name.FirstPart, table2Name.LastPart, ((Name)(statement2.Output[i])).LastPart).As(((Name)(statement2.Output[i])).Alias));
}
if (whereExpression == null)
{
whereExpression = expressions1[i].IsEqual(expressions2[i]);
}
else
{
whereExpression = whereExpression.And(expressions1[i].IsEqual(expressions2[i]));
}
}
if (statement2.Where != null)
{
whereExpression = whereExpression.And(AddPrefixToExpressionToken(((ExpressionToken)statement2.Where), table2Name.LastPart));
}
ExpressionToken tempExpression = Sql.Function(functionName, Sql.Select.From(table2Name).Where(whereExpression));
if (statement1.Where != null && !isSameTableMode)
{
tempExpression = tempExpression.And(AddPrefixToExpressionToken(((ExpressionToken)statement1.Where), table1Name.LastPart));
}
SelectStatement correlationStatement = null;
correlationStatement = isSameTableMode
? Sql.Select.Output(expressions1) //case 1
.From(statement1)
.Where(tempExpression)
: Sql.Select.Output(expressions1) //case 2
.From(table1Name)
.Where(tempExpression);
VisitStatement(correlationStatement);
}
protected void VisitWhenMatchedUpdateToken(MergeStatement statement
, IList <string> tempUpdateAliases
, string targetAlias
, string targetTable
, string targetColumnOn
, string sourceAlias
, string sourceTable
, string sourceColumnOn
, bool isTop)
{
var counter = 0;
if (statement.WhenMatched != null)
{
foreach (var item in statement.WhenMatched)
{
if (!(item is WhenMatchedTokenThenDeleteToken))
{
var tempTableSelectStatement = Sql.Select.Output(Sql.Name(targetAlias, targetColumnOn))
.From(targetTable, targetAlias)
.InnerJoin(Sql.Name(sourceTable).As(sourceAlias), Sql.Name(targetAlias, targetColumnOn).IsEqual(Sql.Name(sourceAlias, sourceColumnOn)));
var tempAlias = "tmp_" + counter;
ExpressionToken tempExpression = (IsEqualsToken)(statement.On);
if (item.AndCondition != null)
{
tempExpression = tempExpression.And(AddPrefixToExpressionToken((ExpressionToken)item.AndCondition, targetAlias));
}
if (isTop)
{
tempExpression = tempExpression.And(Sql.Name(targetAlias, targetColumnOn)
.In(Sql.Select.Output(sourceColumnOn).From(TopAlias)));
}
tempTableSelectStatement = tempTableSelectStatement.Where(tempExpression);
var createTempTable =
Sql.CreateTemporaryTable(tempAlias)
.As(tempTableSelectStatement);
VisitStatement(createTempTable);
State.WriteStatementTerminator();
tempUpdateAliases.Add(tempAlias);
if (((WhenMatchedTokenThenUpdateSetToken)item).Set.Count != 0)
{
foreach (var setItem in ((WhenMatchedTokenThenUpdateSetToken)item).Set)
{
if (setItem.First is Name)
{
setItem.First = Sql.Name(targetAlias, ((Name)setItem.First).LastPart);
}
if (setItem.Second is Name)
{
setItem.Second = Sql.Name(sourceAlias, ((Name)setItem.Second).LastPart);
}
}
}
var updateTable =
Sql.Update(Sql.NameAs(targetTable, targetAlias))
.Set(((WhenMatchedTokenThenUpdateSetToken)item).Set)
.From(Sql.NameAs(sourceTable, sourceAlias))
.Where(tempExpression);
counter++;
VisitStatement(updateTable);
State.WriteStatementTerminator();
}
}
}
}
protected void VisitWhenNotMatchedThenInsertToken(MergeStatement statement
, IList <string> tempUpdateAliases
, string targetAlias
, string targetColumnOn
, string targetTable
, string sourceAlias
, string sourceTable
, string sourceColumnOn)
{
string tempSourceAlias = "tmp_" + sourceAlias;
ExpressionToken tempExpression = new ExpressionToken();
if (statement.WhenNotMatched.Count != 0)
{
int counter = 0;
foreach (WhenMatchedToken item in statement.WhenNotMatched)
{
tempExpression = Sql.Name(sourceAlias, sourceColumnOn)
.NotIn(Sql.Select.Output(targetColumnOn).From(targetTable));
if (item.AndCondition != null)
{
tempExpression = tempExpression.And(AddPrefixToExpressionToken((ExpressionToken)item.AndCondition, sourceAlias));
}
CreateTableStatement createTempTable =
Sql.CreateTemporaryTable(tempSourceAlias)
.As(Sql.Select.From(Sql.NameAs(sourceTable, sourceAlias)).Where(tempExpression));
VisitStatement(createTempTable);
State.WriteStatementTerminator();
InsertStatement insertStatement = Sql.Insert.Into(targetTable);
if ((((WhenNotMatchedTokenThenInsertToken)item).Values.Count == 0) ||
(((WhenNotMatchedTokenThenInsertToken)item).Columns.Count == 0) ||
(((WhenNotMatchedTokenThenInsertToken)item).Columns.Count != ((WhenNotMatchedTokenThenInsertToken)item).Values.Count))
{
insertStatement.Columns.Add(targetColumnOn);
insertStatement.From(Sql.Select.From(tempSourceAlias).Output(sourceColumnOn));
}
else
{
foreach (Name columnName in ((WhenNotMatchedTokenThenInsertToken)item).Columns)
{
insertStatement.Columns.Add(Sql.Name(columnName.LastPart));
}
SelectStatement fromSelect = Sql.Select.From(tempSourceAlias);
foreach (Token outputColumn in ((WhenNotMatchedTokenThenInsertToken)item).Values)
{
fromSelect.Output.Add(Sql.Name(((Name)outputColumn).LastPart));
}
insertStatement.From(fromSelect);
}
VisitStatement(insertStatement);
State.WriteStatementTerminator();
counter++;
VisitStatement(Sql.DropTable(tempSourceAlias, true));
State.WriteStatementTerminator();
}
}
}