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 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 override void VisitIfTableExists(StatementIfTableExists statementIfExists)
{
if (statementIfExists.ExprTable.DbSchema == null)
{
throw new SqExpressException("Table schema name is mandatory to check the table existence");
}
string?databaseName = statementIfExists.ExprTable.DbSchema.Database?.Name;
var tbl = new InformationSchemaTables(databaseName, Alias.Empty);
ExprBoolean condition = tbl.TableSchema == this.Options.MapSchema(statementIfExists.ExprTable.DbSchema.Schema.Name)
& tbl.TableName == statementIfExists.ExprTable.TableName.Name;
var test = SqQueryBuilder.SelectTopOne()
.From(tbl)
.Where(condition);
new StatementIf(SqQueryBuilder.Exists(test), statementIfExists.Statements, statementIfExists.ElseStatements).Accept(this);
}
public ExprInsert Done()
{
var checkExistence = this._checkExistenceByColumns != null && this._checkExistenceByColumns.Count > 0;
var useDerivedTable = this._targetInsertSelectMapping != null || checkExistence;
var mapping = this._dataMapping.AssertFatalNotNull(nameof(this._dataMapping));
int?capacity = this._data.TryToCheckLength(out var c) ? c : (int?)null;
if (capacity != null && capacity.Value < 1)
{
throw new SqExpressException("Input data should not be empty");
}
List <ExprValueRow>? recordsS = null;
List <ExprInsertValueRow>?recordsI = null;
if (useDerivedTable)
{
recordsS = capacity.HasValue ? new List <ExprValueRow>(capacity.Value) : new List <ExprValueRow>();
}
else
{
recordsI = capacity.HasValue ? new List <ExprInsertValueRow>(capacity.Value) : new List <ExprInsertValueRow>();
}
DataMapSetter <TTable, TItem>? dataMapSetter = null;
IReadOnlyList <ExprColumnName>?columns = null;
foreach (var item in this._data)
{
dataMapSetter ??= new DataMapSetter <TTable, TItem>(this._target, item);
dataMapSetter.NextItem(item, columns?.Count);
mapping(dataMapSetter);
columns ??= dataMapSetter.Columns;
dataMapSetter.EnsureRecordLength();
recordsS?.Add(new ExprValueRow(dataMapSetter.Record.AssertFatalNotNull(nameof(dataMapSetter.Record))));
recordsI?.Add(new ExprInsertValueRow(dataMapSetter.Record.AssertFatalNotNull(nameof(dataMapSetter.Record))));
}
if ((recordsS?.Count ?? 0 + recordsI?.Count ?? 0) < 1 || columns == null)
{
//In case of empty IEnumerable
throw new SqExpressException("Input data should not be empty");
}
IExprInsertSource insertSource;
if (recordsI != null)
{
insertSource = new ExprInsertValues(recordsI);
}
else if (recordsS != null && useDerivedTable)
{
var valuesConstructor = new ExprTableValueConstructor(recordsS);
var values = new ExprDerivedTableValues(
valuesConstructor,
new ExprTableAlias(Alias.Auto.BuildAliasExpression().AssertNotNull("Alias cannot be null")),
columns);
IReadOnlyList <ColumnValueInsertSelectMap>?additionalMaps = null;
if (this._targetInsertSelectMapping != null)
{
var targetUpdateSetter = new TargetInsertSelectSetter <TTable>(this._target);
this._targetInsertSelectMapping.Invoke(targetUpdateSetter);
additionalMaps = targetUpdateSetter.Maps;
if (additionalMaps.Count < 1)
{
throw new SqExpressException("Additional insertion cannot be null");
}
}
var selectValues = new List <IExprSelecting>(columns.Count + (additionalMaps?.Count ?? 0));
foreach (var exprColumnName in values.Columns)
{
selectValues.Add(exprColumnName);
}
if (additionalMaps != null)
{
foreach (var m in additionalMaps)
{
selectValues.Add(m.Value);
}
}
IExprQuery query;
var queryBuilder = SqQueryBuilder.Select(selectValues).From(values);
if (checkExistence && this._checkExistenceByColumns != null)
{
var tbl = this._target.WithAlias(new ExprTableAlias(Alias.Auto.BuildAliasExpression() !));
var existsFilter = !SqQueryBuilder.Exists(SqQueryBuilder
.SelectOne()
.From(tbl)
.Where(this._checkExistenceByColumns
.Select(column => column.WithSource(tbl.Alias) == column.WithSource(values.Alias))
.JoinAsAnd()));
query = queryBuilder.Where(existsFilter).Done();
}
else
{
query = queryBuilder.Done();
}
insertSource = new ExprInsertQuery(query);
if (additionalMaps != null)
{
var extraInsertCols = additionalMaps.SelectToReadOnlyList(m => m.Column);
columns = Helpers.Combine(columns, extraInsertCols);
}
}
else
{
//Actually C# should have detected that this brunch cannot be invoked
throw new SqExpressException("Fatal logic error!");
}
return(new ExprInsert(this._target.FullName, columns, insertSource));
}
