public bool VisitExprMerge(ExprMerge expr, TCtx arg) { var res = this.Visit(expr, "Merge", arg, out var argOut) && this.Accept("TargetTable", expr.TargetTable, argOut) && this.Accept("Source", expr.Source, argOut) && this.Accept("On", expr.On, argOut) && this.Accept("WhenMatched", expr.WhenMatched, argOut) && this.Accept("WhenNotMatchedByTarget", expr.WhenNotMatchedByTarget, argOut) && this.Accept("WhenNotMatchedBySource", expr.WhenNotMatchedBySource, argOut); this._visitor.EndVisitExpr(expr, arg); return(res); }
//Merge public bool VisitExprMerge(ExprMerge merge, IExpr?parent) { this.Builder.Append("MERGE "); merge.TargetTable.Accept(this, merge); this.Builder.Append(" USING "); merge.Source.Accept(this, merge); this.Builder.Append(" ON "); merge.On.Accept(this, merge); if (merge.WhenMatched != null) { this.Builder.Append(" WHEN MATCHED"); merge.WhenMatched.Accept(this, merge); } if (merge.WhenNotMatchedByTarget != null) { this.Builder.Append(" WHEN NOT MATCHED"); merge.WhenNotMatchedByTarget.Accept(this, merge); } if (merge.WhenNotMatchedBySource != null) { this.Builder.Append(" WHEN NOT MATCHED BY SOURCE"); merge.WhenNotMatchedBySource.Accept(this, merge); } this.Builder.Append(';'); return(true); }
private static IExprExec?WhenMatched(ExprMerge merge, TempTableBase tempTable) { IExprExec?e = null; if (merge.WhenMatched != null) { if (merge.WhenMatched is ExprMergeMatchedUpdate update) { e = SqQueryBuilder .Update(merge.TargetTable) .Set(update.Set) .From(merge.TargetTable) .InnerJoin(tempTable, merge.On) .Where(update.And); } else if (merge.WhenMatched is ExprMergeMatchedDelete delete) { ExprBoolean filter = SqQueryBuilder.Exists(SqQueryBuilder.SelectOne() .From(tempTable) .Where(merge.On)); if (delete.And != null) { filter = filter & delete.And; } e = SqQueryBuilder.Delete(merge.TargetTable).From(merge.TargetTable).Where(filter); } else { throw new SqExpressException($"Unknown type: '{merge.WhenMatched.GetType().Name}'"); } } return(e); }
private static ExtractKeysResult ExtractKeys(ExprMerge merge, IExprAlias sourceAlias) { IExprAlias targetAlias = merge.TargetTable.Alias?.Alias ?? throw new SqExpressException("Target table should have an alias"); var accSource = new List <ExprColumnName>(); var accTarget = new List <ExprColumnName>(); var eqs = merge.On.SyntaxTree().DescendantsAndSelf().OfType <ExprBooleanEq>(); foreach (var exprBooleanEq in eqs) { if (exprBooleanEq.Left is ExprColumn left && exprBooleanEq.Right is ExprColumn right && left.Source is ExprTableAlias ta && right.Source is ExprTableAlias sa) { if (sa.Alias.Equals(sourceAlias) && ta.Alias.Equals(targetAlias)) { accTarget.Add(left.ColumnName); accSource.Add(right.ColumnName); } else if (ta.Alias.Equals(sourceAlias) && sa.Alias.Equals(targetAlias)) { accTarget.Add(right.ColumnName); accSource.Add(left.ColumnName); } } } return(new ExtractKeysResult(accTarget, accSource)); }
private static IExprExec?WhenNotMatchedByTarget(ExprMerge merge, TempTableBase tempTable, ExtractKeysResult keys) { IExprExec?e = null; if (merge.WhenNotMatchedByTarget != null) { if (merge.WhenNotMatchedByTarget is ExprExprMergeNotMatchedInsert insert) { var filter = !SqQueryBuilder.Exists(SqQueryBuilder .SelectOne() .From(merge.TargetTable) .Where(merge.On)); if (insert.And != null) { filter = filter & insert.And; } e = SqQueryBuilder.InsertInto(merge.TargetTable, insert.Columns) .From(SqQueryBuilder.Select(insert.Values.SelectToReadOnlyList(i => i is ExprValue v ? v : throw new SqExpressException("DEFAULT value cannot be used in MERGE polyfill"))) .From(tempTable) .Where(filter)); } else if (merge.WhenNotMatchedByTarget is ExprExprMergeNotMatchedInsertDefault insertDefault) { var filter = !SqQueryBuilder.Exists(SqQueryBuilder .SelectOne() .From(merge.TargetTable) .Where(merge.On)); if (insertDefault.And != null) { filter = filter & insertDefault.And; } e = SqQueryBuilder.InsertInto(merge.TargetTable, keys.TargetKeys) .From(SqQueryBuilder.Select(keys.SourceKeys) .From(tempTable) .Where(filter)); } else { throw new SqExpressException($"Unknown type: '{merge.WhenNotMatchedByTarget.GetType().Name}'"); } } return(e); }
public static ExprList ConvertMerge(ExprMerge merge, string tempTableName) { var derivedTableValues = merge.Source as ExprDerivedTableValues; if (derivedTableValues == null) { throw new SqExpressException("Only derived table values can be used as a source in MERGE simulation"); } var acc = new List <IExprExec>(); var keys = ExtractKeys(merge, derivedTableValues.Alias.Alias); var exprInsertIntoTmp = TempTableData.FromDerivedTableValuesInsert(derivedTableValues, keys.SourceKeys, out var tempTable, name: tempTableName, alias: Alias.From(derivedTableValues.Alias.Alias)); acc.AddRange(exprInsertIntoTmp.Expressions); //MATCHED var e = WhenMatched(merge, tempTable); if (e != null) { acc.Add(e); } //NOT MATCHED BY TARGET e = WhenNotMatchedByTarget(merge, tempTable, keys); if (e != null) { acc.Add(e); } //NOT MATCHED BY SOURCE e = WhenNotMatchedBySource(merge, tempTable); if (e != null) { acc.Add(e); } acc.Add(new ExprStatement(tempTable.Script.Drop())); return(new ExprList(acc)); }
public static ExprMerge WithWhenNotMatchedBySource(this ExprMerge original, IExprMergeMatched?newWhenNotMatchedBySource) => new ExprMerge(targetTable: original.TargetTable, source: original.Source, on: original.On, whenMatched: original.WhenMatched, whenNotMatchedByTarget: original.WhenNotMatchedByTarget, whenNotMatchedBySource: newWhenNotMatchedBySource);
public static ExprMerge WithOn(this ExprMerge original, ExprBoolean newOn) => new ExprMerge(targetTable: original.TargetTable, source: original.Source, on: newOn, whenMatched: original.WhenMatched, whenNotMatchedByTarget: original.WhenNotMatchedByTarget, whenNotMatchedBySource: original.WhenNotMatchedBySource);