public static IList <FieldPairReference> GetFieldPairs(
this MergeStatement mergeStatement,
ILogger logger,
SchemaFile file
)
{
var ctePairs = mergeStatement
.WithCtesAndXmlNamespaces
?.CommonTableExpressions
.GetFieldPairReferences(logger, file)
.ToList()
?? new List <FieldPairReference>();
var cteReferences = mergeStatement
.WithCtesAndXmlNamespaces
?.CommonTableExpressions
.GetSchemaObjectReferences(logger, file)
.ToList()
?? new List <SchemaObjectReference>();
using (new StatementContext(file.FileContext, cteReferences))
{
var mergeSpecificationPairs = mergeStatement
.MergeSpecification
.GetFieldPairReferences(logger, file)
.ToList();
return(ctePairs
.Concat(mergeSpecificationPairs)
.ToList());
}
}
public CommandServiceTests()
{
// Create Alter Query
_alterQuery = new AlterTableStatement <TestClass>("testtableName", AlterTableStatement <TestClass> .AlterType.Create);
// Create Delete Query
_deleteQuery = new DeleteStatement <TestClass>(m => m.Id > 10);
// Create Update Query
Expression <Func <TestClass> > newExpression = () => new TestClass()
{
Id = 3
};
MemberInitExpression memberInit = newExpression.Body as MemberInitExpression;
_updateQuery = new UpdateStatement <TestClass>(
memberInit.Bindings.Cast <MemberAssignment>(),
null
);
// Create Merge Query
Expression <Func <TestClass, int> > expression = t => t.Id;
MemberExpression memberExpression = expression.Body as MemberExpression;
var mergeKeys = new List <MemberExpression>()
{
memberExpression
};
_mergeQuery = new MergeStatement <TestClass>(mergeKeys, "temporarytable", MergeStatement <TestClass> .MergeType.InsertOnly);
}
public (string, IEnumerable <object>) TranslateQuery(MergeStatement <TEntity> mergeQuery)
{
var queryBuilder = new SqlQueryBuilder();
// Merge Statement
AppendOperation(queryBuilder, "MERGE INTO", tableAlias: TARGET_TABLE_ALIAS);
// Using Statement
AppendOperation(queryBuilder, " USING", mergeQuery.TemporaryTableName, SOURCE_TABLE_ALIAS);
queryBuilder.Append(" ON ");
AppendMergeJoinKeys(queryBuilder, mergeQuery.Keys);
// Update Into
if (mergeQuery.Type == MergeStatement <TEntity> .MergeType.UpdateOnly || mergeQuery.Type == MergeStatement <TEntity> .MergeType.InsertOrUpdate)
{
AppendMergeStatement(queryBuilder);
}
// Insert Statement
if (mergeQuery.Type == MergeStatement <TEntity> .MergeType.InsertOnly || mergeQuery.Type == MergeStatement <TEntity> .MergeType.InsertOrUpdate)
{
AppendInsertMergeStatement(queryBuilder);
}
queryBuilder.Append(";");
return(queryBuilder.Build());
}
public static void OnCodeElement(MergeStatement statement, CodeElementsParser.MergeStatementContext context)
{
if (statement.InputFiles.Length == 1)
{
DiagnosticUtils.AddError(statement, "MERGE: USING needs 2 filenames or more", context?.usingFilenames());
}
}
private void DumpMergeText(MergeStatement merge)
{
//
var script = merge.GetScript();
OutputWindowMessage.WriteMessage("Script Parser: About to Parse Script: \r\n======================================\r\n{0}\r\n======================================\r\n", script);
}
public override void Visit(MergeStatement node)
{
var mergeSpecification = node.MergeSpecification;
ProcessTableReferences(new List <TableReference> {
mergeSpecification.Target, mergeSpecification.TableReference
}, SqlAction.Merge);
if (mergeSpecification.ActionClauses != null)
{
foreach (var actionClause in mergeSpecification.ActionClauses)
{
var action = actionClause.Action;
if (action is InsertMergeAction)
{
ProcessTableReferences(new List <TableReference> {
mergeSpecification.Target
}, SqlAction.Insert);
}
else if (action is UpdateMergeAction)
{
ProcessTableReferences(new List <TableReference> {
mergeSpecification.Target
}, SqlAction.Update);
}
else if (action is DeleteMergeAction)
{
ProcessTableReferences(new List <TableReference> {
mergeSpecification.Target
}, SqlAction.Delete);
}
}
}
}
public void MergeTables(IEnumerable <MemberExpression> mergeKeys, bool isUpdateOnly)
{
MergeStatement <TRecord> .MergeType mergeType = GetMergeType(isUpdateOnly);
var mergeQuery = new MergeStatement <TRecord>(mergeKeys, TEMP_TABLE, mergeType);
_commandService.Merge(mergeQuery);
}
public int Merge(MergeStatement <TEntity> mergeQuery)
{
Check.NotNull(mergeQuery, nameof(mergeQuery));
(string query, IEnumerable <object> parameters) = _queryTranslator.TranslateQuery(mergeQuery);
return(_queryProcessor.ExecuteCommand(
query: query,
parameters: parameters
));
}
public string GenerateMergeFromTemplate(List <TableMapping> tableColumns, string tableName, string schemaName)
{
try
{
return(MergeStatement.Build(tableColumns, tableName, schemaName));
}
catch (Exception ex)
{
Console.Write(ex);
throw;
}
}
public static EngineResult Evaluate(StatementWithCtesAndXmlNamespaces statement, Scope scope)
{
if (statement.WithCtesAndXmlNamespaces?.CommonTableExpressions != null)
{
scope = Evaluate(statement.WithCtesAndXmlNamespaces.CommonTableExpressions, scope);
}
return(statement switch
{
SelectStatement select => Evaluate(select, scope),
InsertStatement insert => Evaluate(insert.InsertSpecification, scope),
UpdateStatement update => Evaluate(update.UpdateSpecification, scope),
DeleteStatement delete => Evaluate(delete.DeleteSpecification, scope),
MergeStatement merge => Evaluate(merge.MergeSpecification, scope),
_ => throw FeatureNotSupportedException.Subtype(statement)
});
public MergeStatement Build()
{
var merge = new MergeStatement();
var specification = merge.MergeSpecification = new MergeSpecification();
SetTableAlias(specification);
SetInlineTableReference(specification);
SetTargetName(specification);
BuildSearchCondition(specification);
BuildActions(specification);
return merge;
}
public void ConstructorShouldAssignValues()
{
// Arrange
const string tableName = "TemporaryTableName";
var mergeType = MergeStatement <TestClass> .MergeType.InsertOrUpdate;
// Act
var mergeStatement = new MergeStatement <TestClass>(
_mergeKeys, tableName, mergeType
);
// Assert
Assert.Same(_mergeKeys, mergeStatement.Keys);
Assert.Same(tableName, mergeStatement.TemporaryTableName);
Assert.Equal(mergeType, mergeStatement.Type);
}
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();
}
}
}
public MergeStatement Build()
{
var merge = new MergeStatement();
var specification = merge.MergeSpecification = new MergeSpecification();
SetTableAlias(specification);
SetInlineTableReference(specification);
SetTargetName(specification);
BuildSearchCondition(specification);
BuildActions(specification);
return(merge);
}
public static void SetInlineTableData(this MergeStatement source, DataTable data, List <ColumnDescriptor> columns)
{
var table = source.MergeSpecification.TableReference as InlineDerivedTable;
if (null == table)
{
throw new NotImplementedException("only support merges from inline table reference");
}
table.RowValues.Clear();
var columnsToRemove = new List <DataColumn>();
for (var i = 0; i < data.Columns.Count; i++)
{
if (columns.FirstOrDefault(p => p.Name.Value == data.Columns[i].ColumnName) == null)
{
columnsToRemove.Add(data.Columns[i]);
}
}
foreach (var column in columnsToRemove)
{
data.Columns.Remove(column);
}
foreach (DataRow row in data.Rows)
{
var rowValue = new RowValue();
for (var i = 0; i < row.ItemArray.Length; i++)
{
if (row.ItemArray[i] == DBNull.Value)
{
rowValue.ColumnValues.Add(new NullLiteral());
}
else
{
rowValue.ColumnValues.Add(GetColumnValue(row.ItemArray[i].ToString(), columns[i].LiteralType));
}
}
table.RowValues.Add(rowValue);
}
}
private void AnalyzeMergeStatement(MergeStatement mergeStatement, ParserResults results)
{
mergeStatement.PrintTSqlStatementBlockToDebugConsole();
List <TableParsingResult> temp = new List <TableParsingResult>();
if (mergeStatement.MergeSpecification.Target is NamedTableReference mergeNamedTableReference)
{
string tableName = ExtraceTableNameFromNamedTableRefernce(mergeNamedTableReference, out string alias);
temp.AddIfNotExists(tableName, SqlOperationType.INSERT, alias);
temp.AddIfNotExists(tableName, SqlOperationType.UPDATE, alias);
if (mergeStatement.MergeSpecification.TableReference is QueryDerivedTable mergeQueryDerivedTable && mergeQueryDerivedTable.QueryExpression is QuerySpecification querySpecification)
{
var result = ExtractTablesFromQuerySpecification(querySpecification); //ExtractTablesUsedInFromClause(mergeQuerySpecification.FromClause);
temp.AddIfNotExists(result);
}
}
mergeStatement.PrintTablesToDebugConsole(temp);
results.AddIfNotExists(temp);
}
protected void VisitWhenNotMatchedBySourceToken(MergeStatement statement
, string targetAlias
, string targetTable
, string targetColumnOn
, string sourceAlias
, string sourceTable
, string sourceColumnOn
, bool isTop
)
{
if (statement.WhenNotMatchedBySource.Count != 0)
{
foreach (var item in statement.WhenNotMatchedBySource)
{
var tempExpression = Sql.Name(targetAlias, targetColumnOn)
.NotIn(Sql.Select.Output(sourceColumnOn).From(sourceTable));
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)));
}
var whenNotMatchedBySourceDelete =
Sql.Delete.From(Sql.NameAs(targetTable, targetAlias))
.Where(tempExpression);
VisitStatement(whenNotMatchedBySourceDelete);
State.WriteStatementTerminator();
}
}
}
public static string GetScript(this MergeStatement source)
{
var script = "";
var generator =
VersionDetector.ScriptGeneratorFactory(new SqlScriptGeneratorOptions
{
IncludeSemicolons = true,
AlignClauseBodies = true,
AlignColumnDefinitionFields = true,
AsKeywordOnOwnLine = true,
MultilineInsertSourcesList = true,
MultilineInsertTargetsList = true,
MultilineSelectElementsList = true,
NewLineBeforeOpenParenthesisInMultilineList = true
});
generator.GenerateScript(source, out script);
if (!script.EndsWith(";"))
{
script = script + ";";
}
return(script);
}
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();
}
}
}
}
public override void ExplicitVisit(MergeStatement node)
{
Merges.Add(node);
base.ExplicitVisit(node);
}
public MergeStatementParser(MergeStatement merge)
{
_merge = merge;
}
protected override void VisitMerge(MergeStatement statement)
{
throw new NotImplementedException();
}
public virtual void OnMergeStatement(MergeStatement stmt)
{
Enter(new Merge(stmt), stmt);
Exit();
}
protected abstract void VisitMerge(MergeStatement statement);
private DataTable GetDataFromMerge(MergeStatement mergeStatement, TableDescriptor table)
{
var dataTable = new DataTable();
foreach (var col in table.Columns)
{
dataTable.Columns.Add(new DataColumn(col.Name.GetName()));
}
var inlineTable = mergeStatement.MergeSpecification.TableReference as InlineDerivedTable;
foreach (var row in inlineTable.RowValues)
{
var dataTableRow = dataTable.NewRow();
var index = 0;
foreach (var col in row.ColumnValues)
{
if (col as NullLiteral != null)
{
dataTableRow[index++] = DBNull.Value;
}
else
{
var value = col as Literal;
if (value == null)
{
Log.WriteInfo("Error Parsing Merge Statement, Could not convert column to Literal: {0}", col);
MessageBox.Show("Error we expected to get a literal parsing a merge statement for the " +
table.Name.GetName() + " table but we got a " + col.ToString()
+
"\r\nCheck that you don't have an unquoted string or another impossible type");
return null;
}
dataTableRow[index++] = value.Value;
}
}
dataTable.Rows.Add(dataTableRow);
}
dataTable.AcceptChanges();
dataTable.RowChanged += (sender, args) =>
{
dataTable.ExtendedProperties["Changed"] = true;
};
dataTable.TableNewRow += (sender, args) =>
{
dataTable.ExtendedProperties["Changed"] = true;
};
dataTable.RowDeleting += (sender, args) =>
{
dataTable.ExtendedProperties["Changed"] = true;
};
return dataTable;
}
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 VisitWhenNotMatchedThenInsertToken(MergeStatement statement
, IList <string> tempUpdateAliases
, string targetAlias
, string targetColumnOn
, string targetTable
, string sourceAlias
, string sourceTable
, string sourceColumnOn)
{
var tempSourceAlias = "tmp_" + sourceAlias;
if (statement.WhenNotMatched.Count != 0)
{
foreach (var item in statement.WhenNotMatched)
{
var tempExpression = Sql.Name(sourceAlias, sourceColumnOn)
.NotIn(Sql.Select.Output(targetColumnOn).From(targetTable));
if (item.AndCondition != null)
{
tempExpression = tempExpression.And(AddPrefixToExpressionToken((ExpressionToken)item.AndCondition, sourceAlias));
}
var createTempTable =
Sql.CreateTemporaryTable(tempSourceAlias)
.As(Sql.Select.From(Sql.NameAs(sourceTable, sourceAlias)).Where(tempExpression));
VisitStatement(createTempTable);
State.WriteStatementTerminator();
var 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 (var columnName in ((WhenNotMatchedTokenThenInsertToken)item).Columns)
{
insertStatement.Columns.Add(Sql.Name(columnName.LastPart));
}
var fromSelect = Sql.Select.From(tempSourceAlias);
foreach (var outputColumn in ((WhenNotMatchedTokenThenInsertToken)item).Values)
{
fromSelect.Output.Add(Sql.Name(((Name)outputColumn).LastPart));
}
insertStatement.From(fromSelect);
}
VisitStatement(insertStatement);
State.WriteStatementTerminator();
VisitStatement(Sql.DropTable(tempSourceAlias, true));
State.WriteStatementTerminator();
}
}
}
private DataTable GetDataFromMerge(MergeStatement mergeStatement, TableDescriptor table)
{
var dataTable = new DataTable();
foreach (var col in table.Columns)
{
dataTable.Columns.Add(new DataColumn(col.Name.GetName()));
}
var inlineTable = mergeStatement.MergeSpecification.TableReference as InlineDerivedTable;
foreach (var row in inlineTable.RowValues)
{
var dataTableRow = dataTable.NewRow();
var index = 0;
foreach (var col in row.ColumnValues)
{
if (col as NullLiteral != null)
{
dataTableRow[index++] = DBNull.Value;
}
else
{
var value = col as Literal;
if (value == null)
{
Log.WriteInfo("Error Parsing Merge Statement, Could not convert column to Literal: {0}", col);
MessageBox.Show("Error we expected to get a literal parsing a merge statement for the " +
table.Name.GetName() + " table but we got a " + col.ToString()
+
"\r\nCheck that you don't have an unquoted string or another impossible type");
return(null);
}
dataTableRow[index++] = value.Value;
}
}
dataTable.Rows.Add(dataTableRow);
}
dataTable.AcceptChanges();
dataTable.RowChanged += (sender, args) =>
{
dataTable.ExtendedProperties["Changed"] = true;
};
dataTable.TableNewRow += (sender, args) =>
{
dataTable.ExtendedProperties["Changed"] = true;
};
dataTable.RowDeleting += (sender, args) =>
{
dataTable.ExtendedProperties["Changed"] = true;
};
return(dataTable);
}
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();
}
}
}
}
private void DumpMergeText(MergeStatement merge)
{
//
var script = merge.GetScript();
OutputWindowMessage.WriteMessage("Script Parser: About to Parse Script: \r\n======================================\r\n{0}\r\n======================================\r\n", script);
}
protected override void VisitMerge(MergeStatement statement)
{
VisitStatement(Sql.BeginTransaction());
State.WriteStatementTerminator();
bool isTop = false;
string prefixToAlias = "alias_";
IList <string> tempUpdateAliases = new List <string>();
string targetTable = statement.Into.GetFullName();
string targetAlias = (String.IsNullOrWhiteSpace(statement.Into.Alias)) ? prefixToAlias + statement.Into.LastPart : statement.Into.Alias;
string sourceTable = ((Name)statement.Using).GetFullName();
string sourceAlias = (String.IsNullOrWhiteSpace(((Name)statement.Using).Alias)) ? prefixToAlias + ((Name)statement.Using).LastPart : ((Name)statement.Using).Alias;
string targetColumnOn = string.Empty;
string sourceColumnOn = string.Empty;
string firstOn = ((Name)((BinaryToken)statement.On).First).FirstPart;
if ((firstOn).Equals(targetTable) || (firstOn).Equals(targetAlias))
{
targetColumnOn = ((Name)((BinaryToken)statement.On).First).LastPart;
sourceColumnOn = ((Name)((BinaryToken)statement.On).Second).LastPart;
}
else
{
sourceColumnOn = ((Name)((BinaryToken)statement.On).First).LastPart;
targetColumnOn = ((Name)((BinaryToken)statement.On).Second).LastPart;
}
((BinaryToken)statement.On).First = Sql.Name(targetAlias, targetColumnOn);
((BinaryToken)statement.On).Second = Sql.Name(sourceAlias, sourceColumnOn);
if (statement.Top != null)
{
CreateTableStatement createTable = Sql.CreateTemporaryTable(Sql.Name(TopAlias), true).As(Sql.Select.Output(Sql.Name(targetColumnOn)).From(Sql.Name(targetTable)).Top(((int)((Scalar)statement.Top.Value).Value), statement.Top.Percent));
isTop = true;
VisitStatement(createTable);
State.WriteStatementTerminator();
}
VisitWhenMatchedUpdateToken(statement
, tempUpdateAliases
, targetAlias
, targetTable
, targetColumnOn
, sourceAlias
, sourceTable
, sourceColumnOn
, isTop);
VisitWhenMatchedDeleteToken(statement
, targetAlias
, targetTable
, targetColumnOn
, sourceAlias
, sourceTable
, sourceColumnOn
, isTop);
VisitWhenNotMatchedBySourceToken(statement
, targetAlias
, targetTable
, targetColumnOn
, sourceAlias
, sourceTable
, sourceColumnOn
, isTop);
VisitWhenNotMatchedThenInsertToken(statement
, tempUpdateAliases
, targetAlias
, targetColumnOn
, targetTable
, sourceAlias
, sourceTable
, sourceColumnOn);
if (tempUpdateAliases.Count != 0)
{
foreach (string tempTable in tempUpdateAliases)
{
DropTableStatement dropTable = Sql.DropTemporaryTable(tempTable, true);
VisitStatement(dropTable);
State.WriteStatementTerminator();
}
}
if (isTop)
{
DropTableStatement dropTable = Sql.DropTemporaryTable(TopAlias, true);
VisitStatement(dropTable);
State.WriteStatementTerminator();
}
VisitStatement(Sql.CommitTransaction());
}
public override void ExplicitVisit(MergeStatement node)
{
this.MergeStatements.Add(node);
}
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();
}
}
}
}
public override void ExplicitVisit(MergeStatement fragment)
{
_fragments.Add(fragment);
}
public override void ExplicitVisit(MergeStatement merge)
{
Merges.Add(merge);
}
public override void Visit(MergeStatement node) { this.action(node); }
private static IToken BuildCodeElementFromType(CodeElementType type, Token lastTokenBeforeError, string informationText)
{
CodeElement codeElement = null;
switch (type)
{
case CodeElementType.ProgramIdentification:
codeElement = new ProgramIdentification();
break;
case CodeElementType.ProgramEnd:
codeElement = new ProgramEnd();
break;
case CodeElementType.ClassIdentification:
codeElement = new ClassIdentification();
break;
case CodeElementType.ClassEnd:
codeElement = new ClassEnd();
break;
case CodeElementType.FactoryIdentification:
codeElement = new FactoryIdentification();
break;
case CodeElementType.FactoryEnd:
codeElement = new FactoryEnd();
break;
case CodeElementType.ObjectIdentification:
codeElement = new ObjectIdentification();
break;
case CodeElementType.ObjectEnd:
codeElement = new ObjectEnd();
break;
case CodeElementType.MethodIdentification:
codeElement = new MethodIdentification();
break;
case CodeElementType.MethodEnd:
codeElement = new MethodEnd();
break;
case CodeElementType.EnvironmentDivisionHeader:
codeElement = new EnvironmentDivisionHeader();
break;
case CodeElementType.DataDivisionHeader:
codeElement = new DataDivisionHeader();
break;
case CodeElementType.ProcedureDivisionHeader:
codeElement = new ProcedureDivisionHeader();
break;
case CodeElementType.DeclarativesHeader:
codeElement = new DeclarativesHeader();
break;
case CodeElementType.DeclarativesEnd:
codeElement = new DeclarativesEnd();
break;
case CodeElementType.SectionHeader:
codeElement = new SectionHeader();
break;
case CodeElementType.ConfigurationSectionHeader:
codeElement = new ConfigurationSectionHeader();
break;
case CodeElementType.InputOutputSectionHeader:
codeElement = new InputOutputSectionHeader();
break;
case CodeElementType.FileSectionHeader:
codeElement = new FileSectionHeader();
break;
case CodeElementType.WorkingStorageSectionHeader:
codeElement = new WorkingStorageSectionHeader();
break;
case CodeElementType.LocalStorageSectionHeader:
codeElement = new LocalStorageSectionHeader();
break;
case CodeElementType.LinkageSectionHeader:
codeElement = new LinkageSectionHeader();
break;
case CodeElementType.ParagraphHeader:
codeElement = new ParagraphHeader();
break;
case CodeElementType.FileControlParagraphHeader:
codeElement = new FileControlParagraphHeader();
break;
case CodeElementType.IOControlParagraphHeader:
codeElement = new IOControlParagraphHeader();
break;
case CodeElementType.SentenceEnd:
codeElement = new SentenceEnd();
break;
case CodeElementType.FileDescriptionEntry:
codeElement = new FileDescriptionEntry();
break;
case CodeElementType.DataDescriptionEntry:
codeElement = new DataDescriptionEntry();
break;
case CodeElementType.DataRedefinesEntry:
codeElement = new DataRedefinesEntry();
break;
case CodeElementType.DataRenamesEntry:
codeElement = new DataRenamesEntry();
break;
case CodeElementType.DataConditionEntry:
codeElement = new DataConditionEntry();
break;
case CodeElementType.FileControlEntry:
codeElement = new FileControlEntry();
break;
case CodeElementType.IOControlEntry:
codeElement = new RerunIOControlEntry();
break;
case CodeElementType.SourceComputerParagraph:
codeElement = new SourceComputerParagraph();
break;
case CodeElementType.ObjectComputerParagraph:
codeElement = new ObjectComputerParagraph();
break;
case CodeElementType.SpecialNamesParagraph:
codeElement = new SpecialNamesParagraph();
break;
case CodeElementType.RepositoryParagraph:
codeElement = new RepositoryParagraph();
break;
case CodeElementType.AcceptStatement:
codeElement = new AcceptFromInputDeviceStatement();
break;
case CodeElementType.AddStatement:
codeElement = new AddSimpleStatement();
break;
case CodeElementType.AlterStatement:
codeElement = new AlterStatement();
break;
case CodeElementType.CallStatement:
codeElement = new CallStatement();
break;
case CodeElementType.CancelStatement:
codeElement = new CancelStatement();
break;
case CodeElementType.CloseStatement:
codeElement = new CloseStatement();
break;
case CodeElementType.ComputeStatement:
codeElement = new ComputeStatement();
break;
case CodeElementType.ContinueStatement:
codeElement = new ContinueStatement();
break;
case CodeElementType.DeleteStatement:
codeElement = new DeleteStatement();
break;
case CodeElementType.DisplayStatement:
codeElement = new DisplayStatement();
break;
case CodeElementType.DivideStatement:
codeElement = new DivideSimpleStatement();
break;
case CodeElementType.EntryStatement:
codeElement = new EntryStatement();
break;
case CodeElementType.EvaluateStatement:
codeElement = new EvaluateStatement();
break;
case CodeElementType.ExecStatement:
codeElement = new ExecStatement();
break;
case CodeElementType.ExitMethodStatement:
codeElement = new ExitMethodStatement();
break;
case CodeElementType.ExitProgramStatement:
codeElement = new ExitProgramStatement();
break;
case CodeElementType.ExitStatement:
codeElement = new ExitStatement();
break;
case CodeElementType.GobackStatement:
codeElement = new GobackStatement();
break;
case CodeElementType.GotoStatement:
codeElement = new GotoSimpleStatement();
break;
case CodeElementType.IfStatement:
codeElement = new IfStatement();
break;
case CodeElementType.InitializeStatement:
codeElement = new InitializeStatement();
break;
case CodeElementType.InspectStatement:
codeElement = new InspectTallyingStatement();
break;
case CodeElementType.InvokeStatement:
codeElement = new InvokeStatement();
break;
case CodeElementType.MergeStatement:
codeElement = new MergeStatement();
break;
case CodeElementType.MoveStatement:
codeElement = new MoveSimpleStatement(null, null, null);
break;
case CodeElementType.MultiplyStatement:
codeElement = new MultiplySimpleStatement();
break;
case CodeElementType.NextSentenceStatement:
codeElement = new NextSentenceStatement();
break;
case CodeElementType.OpenStatement:
codeElement = new OpenStatement();
break;
case CodeElementType.PerformProcedureStatement:
codeElement = new PerformProcedureStatement();
break;
case CodeElementType.PerformStatement:
codeElement = new PerformStatement();
break;
case CodeElementType.ReadStatement:
codeElement = new ReadStatement();
break;
case CodeElementType.ReleaseStatement:
codeElement = new ReleaseStatement();
break;
case CodeElementType.ReturnStatement:
codeElement = new ReturnStatement();
break;
case CodeElementType.RewriteStatement:
codeElement = new RewriteStatement();
break;
case CodeElementType.SearchStatement:
codeElement = new SearchSerialStatement();
break;
case CodeElementType.SetStatement:
codeElement = new SetStatementForAssignment();
break;
case CodeElementType.SortStatement:
codeElement = new SortStatement();
break;
case CodeElementType.StartStatement:
codeElement = new StartStatement();
break;
case CodeElementType.StopStatement:
codeElement = new StopStatement();
break;
case CodeElementType.StringStatement:
codeElement = new StringStatement();
break;
case CodeElementType.SubtractStatement:
codeElement = new SubtractSimpleStatement();
break;
case CodeElementType.UnstringStatement:
codeElement = new UnstringStatement();
break;
case CodeElementType.UseStatement:
codeElement = new UseAfterIOExceptionStatement();
break;
case CodeElementType.WriteStatement:
codeElement = new WriteStatement();
break;
case CodeElementType.XmlGenerateStatement:
codeElement = new XmlGenerateStatement();
break;
case CodeElementType.XmlParseStatement:
codeElement = new XmlParseStatement();
break;
case CodeElementType.AtEndCondition:
codeElement = new AtEndCondition();
break;
case CodeElementType.NotAtEndCondition:
codeElement = new NotAtEndCondition();
break;
case CodeElementType.AtEndOfPageCondition:
codeElement = new AtEndOfPageCondition();
break;
case CodeElementType.NotAtEndOfPageCondition:
codeElement = new NotAtEndOfPageCondition();
break;
case CodeElementType.OnExceptionCondition:
codeElement = new OnExceptionCondition();
break;
case CodeElementType.NotOnExceptionCondition:
codeElement = new NotOnExceptionCondition();
break;
case CodeElementType.OnOverflowCondition:
codeElement = new OnOverflowCondition();
break;
case CodeElementType.NotOnOverflowCondition:
codeElement = new NotOnOverflowCondition();
break;
case CodeElementType.InvalidKeyCondition:
codeElement = new InvalidKeyCondition();
break;
case CodeElementType.NotInvalidKeyCondition:
codeElement = new NotInvalidKeyCondition();
break;
case CodeElementType.OnSizeErrorCondition:
codeElement = new OnSizeErrorCondition();
break;
case CodeElementType.NotOnSizeErrorCondition:
codeElement = new NotOnSizeErrorCondition();
break;
case CodeElementType.ElseCondition:
codeElement = new ElseCondition();
break;
case CodeElementType.WhenCondition:
codeElement = new WhenCondition();
break;
case CodeElementType.WhenOtherCondition:
codeElement = new WhenOtherCondition();
break;
case CodeElementType.WhenSearchCondition:
codeElement = new WhenSearchCondition();
break;
case CodeElementType.AddStatementEnd:
codeElement = new AddStatementEnd();
break;
case CodeElementType.CallStatementEnd:
codeElement = new CallStatementEnd();
break;
case CodeElementType.ComputeStatementEnd:
codeElement = new ComputeStatementEnd();
break;
case CodeElementType.DeleteStatementEnd:
codeElement = new DeleteStatementEnd();
break;
case CodeElementType.DivideStatementEnd:
codeElement = new DivideStatementEnd();
break;
case CodeElementType.EvaluateStatementEnd:
codeElement = new EvaluateStatementEnd();
break;
case CodeElementType.IfStatementEnd:
codeElement = new IfStatementEnd();
break;
case CodeElementType.InvokeStatementEnd:
codeElement = new InvokeStatementEnd();
break;
case CodeElementType.MultiplyStatementEnd:
codeElement = new MultiplyStatementEnd();
break;
case CodeElementType.PerformStatementEnd:
codeElement = new PerformStatementEnd();
break;
case CodeElementType.ReadStatementEnd:
codeElement = new ReadStatementEnd();
break;
case CodeElementType.ReturnStatementEnd:
codeElement = new ReturnStatementEnd();
break;
case CodeElementType.RewriteStatementEnd:
codeElement = new RewriteStatementEnd();
break;
case CodeElementType.SearchStatementEnd:
codeElement = new SearchStatementEnd();
break;
case CodeElementType.StartStatementEnd:
codeElement = new StartStatementEnd();
break;
case CodeElementType.StringStatementEnd:
codeElement = new StringStatementEnd();
break;
case CodeElementType.SubtractStatementEnd:
codeElement = new SubtractStatementEnd();
break;
case CodeElementType.UnstringStatementEnd:
codeElement = new UnstringStatementEnd();
break;
case CodeElementType.WriteStatementEnd:
codeElement = new WriteStatementEnd();
break;
case CodeElementType.XmlStatementEnd:
codeElement = new XmlStatementEnd();
break;
case CodeElementType.LibraryCopy:
codeElement = new LibraryCopyCodeElement();
break;
case CodeElementType.FunctionDeclarationHeader:
codeElement = new FunctionDeclarationHeader(null, AccessModifier.Private, FunctionType.Undefined);
break;
case CodeElementType.FunctionDeclarationEnd:
codeElement = new FunctionDeclarationEnd();
break;
default:
throw new NotImplementedException();
}
if (lastTokenBeforeError != null)
{
var missingToken = new MissingToken(TokenType.InvalidToken, informationText, lastTokenBeforeError.TokensLine, lastTokenBeforeError.StopIndex);
codeElement.ConsumedTokens.Add(missingToken);
}
return(codeElement);
}
public override void ExplicitVisit(MergeStatement merge)
{
Merges.Add(merge);
}
