private static async Task <string> ReadAsJsonString(IScenarioContext context, TableItAllColumnTypes table)
{
using var ms = new MemoryStream();
using Utf8JsonWriter writer = new Utf8JsonWriter(ms);
writer.WriteStartObject();
writer.WriteStartArray(table.FullName.AsExprTableFullName().TableName.Name);
await SqQueryBuilder
.Select(table.Columns)
.From(table)
.Query(context.Database,
r =>
{
writer.WriteStartArray();
foreach (var column in table.Columns)
{
var readAsString = column.ReadAsString(r);
writer.WriteStringValue(readAsString);
}
writer.WriteEndArray();
});
writer.WriteEndArray();
writer.WriteEndObject();
writer.Flush();
var jsonString = Encoding.UTF8.GetString(ms.ToArray());
return(jsonString);
}
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 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));
}
public async Task Exec(IScenarioContext context)
{
var tUser = AllTables.GetItUser();
var users = await SqQueryBuilder.Select(UserName.GetColumns(tUser))
.From(tUser)
.OrderBy(tUser.FirstName)
.OffsetFetch(0, 10)
.QueryList(context.Database, r => UserName.Read(r, tUser));
var modifiedUsers = users.Select(u => u.WithFirstName(u.FirstName + "Modified")).ToList();
await SqQueryBuilder.UpdateData(tUser, modifiedUsers)
.MapDataKeys(UserName.GetUpdateKeyMapping)
.MapData(UserName.GetUpdateMapping)
.AlsoSet(s => s.Set(s.Target.Version, s.Target.Version + 1))
.Exec(context.Database);
var usersAfterMod = await SqQueryBuilder.Select(UserName.GetColumns(tUser))
.From(tUser)
.OrderBy(tUser.FirstName)
.OffsetFetch(0, 10)
.QueryList(context.Database, r => UserName.Read(r, tUser));
for (var index = 0; index < usersAfterMod.Count; index++)
{
if (usersAfterMod[index].FirstName != modifiedUsers[index].FirstName)
{
throw new Exception("Name was not updated");
}
if (usersAfterMod[index].LastName != modifiedUsers[index].LastName)
{
throw new Exception("Name was not updated");
}
}
await SqQueryBuilder.UpdateData(tUser, users)
.MapDataKeys(UserName.GetUpdateKeyMapping)
.MapData(UserName.GetUpdateMapping)
.AlsoSet(s => s.Set(s.Target.Version, s.Target.Version + 1))
.Done()
.Exec(context.Database);
var usersAfterMod2 = await SqQueryBuilder.Select(UserName.GetColumns(tUser))
.From(tUser)
.OrderBy(tUser.FirstName)
.OffsetFetch(0, 10)
.QueryList(context.Database, r => UserName.Read(r, tUser));
for (var index = 0; index < usersAfterMod2.Count; index++)
{
if (usersAfterMod2[index].FirstName != users[index].FirstName)
{
throw new Exception("Name was not updated");
}
if (usersAfterMod2[index].LastName != users[index].LastName)
{
throw new Exception("Name was not updated");
}
}
}
public async Task Exec(IScenarioContext context)
{
var tt = new TestMergeTmpTable();
await context.Database.Statement(tt.Script.Create());
var testData = new List <TestMergeData>
{
new TestMergeData(1, 10),
new TestMergeData(2, 11)
};
//Insert
context.WriteLine("Inserting using MERGE..");
await SqQueryBuilder.MergeDataInto(tt, testData)
.MapDataKeys(TestMergeData.GetUpdateKeyMapping)
.MapData(TestMergeData.GetUpdateMapping)
.WhenMatchedThenUpdate()
.AlsoSet(s => s.Set(s.Target.Version, s.Target.Version + 1))
.WhenNotMatchedByTargetThenInsert()
.Done()
.Exec(context.Database);
var dataFromDb = await SqQueryBuilder.Select(TestMergeDataRow.GetColumns(tt))
.From(tt)
.OrderBy(tt.Id)
.QueryList(context.Database, r => TestMergeDataRow.Read(r, tt));
if (RowDataToString(dataFromDb) != "1,10,0;2,11,0")
{
throw new Exception("Incorrect data");
}
context.WriteLine("Updating using MERGE..");
testData.Add(new TestMergeData(3, 12));
testData[0] = testData[0].WithValue(100);
await SqQueryBuilder.MergeDataInto(tt, testData)
.MapDataKeys(TestMergeData.GetUpdateKeyMapping)
.MapData(TestMergeData.GetUpdateMapping)
.WhenMatchedThenUpdate((s, t) => tt.Value.WithSource(t) != s.Value.WithSource(s.Alias))
.AlsoSet(s => s.Set(s.Target.Version, s.Target.Version + 1))
.WhenNotMatchedByTargetThenInsert()
.Done()
.Exec(context.Database);
dataFromDb = await SqQueryBuilder.Select(TestMergeDataRow.GetColumns(tt))
.From(tt)
.OrderBy(tt.Id)
.QueryList(context.Database, r => TestMergeDataRow.Read(r, tt));
if (RowDataToString(dataFromDb) != "1,100,1;2,11,0;3,12,0")
{
throw new Exception("Incorrect data");
}
context.WriteLine("Updating (BY SOURCE) using MERGE..");
testData = new List <TestMergeData>
{
new TestMergeData(1, 17),
};
await SqQueryBuilder.MergeDataInto(tt, testData)
.MapDataKeys(TestMergeData.GetUpdateKeyMapping)
.MapData(TestMergeData.GetUpdateMapping)
.WhenMatchedThenUpdate((s, t) => tt.Value.WithSource(t) != s.Value.WithSource(s.Alias))
.AlsoSet(s => s.Set(s.Target.Version, s.Target.Version + 1))
.WhenNotMatchedBySourceThenUpdate(t => t.Value == 12).Set(s => s.Set(s.Target.Version, s.Target.Version + 10))
.Done()
.Exec(context.Database);
dataFromDb = await SqQueryBuilder.Select(TestMergeDataRow.GetColumns(tt))
.From(tt)
.OrderBy(tt.Id)
.QueryList(context.Database, r => TestMergeDataRow.Read(r, tt));
if (RowDataToString(dataFromDb) != "1,17,2;2,11,0;3,12,10")
{
throw new Exception("Incorrect data");
}
context.WriteLine("Deleting (BY SOURCE) using MERGE..");
await SqQueryBuilder.MergeDataInto(tt, testData)
.MapDataKeys(TestMergeData.GetUpdateKeyMapping)
.MapData(TestMergeData.GetUpdateMapping)
.WhenMatchedThenUpdate()
.AlsoSet(s => s.Set(s.Target.Version, s.Target.Version + 1))
.WhenNotMatchedBySourceThenDelete(t => t.Value == 12)
.Done()
.Exec(context.Database);
dataFromDb = await SqQueryBuilder.Select(TestMergeDataRow.GetColumns(tt))
.From(tt)
.OrderBy(tt.Id)
.QueryList(context.Database, r => TestMergeDataRow.Read(r, tt));
if (RowDataToString(dataFromDb) != "1,17,3;2,11,0")
{
throw new Exception("Incorrect data");
}
context.WriteLine("Deleting ON MATCH using MERGE..");
await SqQueryBuilder.MergeDataInto(tt, testData)
.MapDataKeys(TestMergeData.GetUpdateKeyMapping)
.MapData(TestMergeData.GetUpdateMapping)
.WhenMatchedThenDelete()
.Done()
.Exec(context.Database);
dataFromDb = await SqQueryBuilder.Select(TestMergeDataRow.GetColumns(tt))
.From(tt)
.OrderBy(tt.Id)
.QueryList(context.Database, r => TestMergeDataRow.Read(r, tt));
if (RowDataToString(dataFromDb) != "2,11,0")
{
throw new Exception("Incorrect data");
}
await context.Database.Statement(tt.Script.Drop());
}