public async Task QueryDictionaryTest(bool predicate)
{
var testData = new Tuple <int, string>[]
{
new Tuple <int, string>(1, "v1"),
new Tuple <int, string>(2, "v2"),
new Tuple <int, string>(3, "v3"),
};
var query = SqQueryBuilder.SelectOne();
TestSqDatabase database = new TestSqDatabase(BuildQueryDelegate(query: query, testData: testData));
Func <int, string, bool>?p = null;
if (predicate)
{
p = (k, v) => k != 3 && v != "v3";
}
var result = await query.QueryDictionary(database, r => r.GetInt32("K"), r => r.GetString("V"), predicate : p);
var count = predicate ? 2 : 3;
Assert.AreEqual(count, result.Count);
for (int i = 0; i < count; i++)
{
Assert.AreEqual(testData[i].Item2, result[testData[i].Item1]);
}
}
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 async Task QueryDictionaryTest_KeyDuplication()
{
var testData = new Tuple <int, string>[]
{
new Tuple <int, string>(1, "v1"),
new Tuple <int, string>(2, "v2"),
new Tuple <int, string>(2, "v3"),
};
var query = SqQueryBuilder.SelectOne();
TestSqDatabase database = new TestSqDatabase(queryImplementation: BuildQueryDelegate(query, testData));
var result = await query.QueryDictionary(database, r => r.GetInt32("K"), r => r.GetString("V"), KeyDuplicationHandler);
var count = 3;
Assert.AreEqual(count, result.Count);
for (int i = 0; i < count; i++)
{
var item1 = testData[i].Item2 == "v3" ? 3 : testData[i].Item1;
Assert.AreEqual(testData[i].Item2, result[item1]);
}
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));
}
protected override IExprSubQuery CreateQuery()
{
return(SqQueryBuilder.SelectOne().Done());
}