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 void TestUpdateDefault()
{
var data = new[]
{
new UserData
{
UserId = 1,
FirstName = "First",
LastName = "Last",
EMail = "*****@*****.**",
RegDate = new DateTime(2020, 01, 02)
}
};
var merge = SqQueryBuilder.MergeDataInto(Tables.User("T"), data)
.MapDataKeys(s => s.Set(s.Target.UserId, s.Source.UserId))
.MapData(s => s
.Set(s.Target.FirstName, s.Source.FirstName)
.Set(s.Target.LastName, s.Source.LastName))
.WhenMatchedThenUpdate()
.AlsoSet(s => s
.Set(s.Target.Version, -1)
.SetDefault(s.Target.RegDate))
.WhenNotMatchedByTargetThenInsert()
.ExcludeKeys()
.AlsoInsert(s => s.SetDefault(s.Target.RegDate))
.Done();
var sql = merge.ToSql();
var expected = "MERGE [dbo].[user] [T] USING (VALUES (1,'First','Last'))[A0]([UserId],[FirstName],[LastName]) " +
"ON [T].[UserId]=[A0].[UserId] " +
"WHEN MATCHED THEN UPDATE SET [T].[FirstName]=[A0].[FirstName],[T].[LastName]=[A0].[LastName],[T].[Version]=-1,[T].[RegDate]=DEFAULT " +
"WHEN NOT MATCHED THEN INSERT([FirstName],[LastName],[RegDate]) VALUES([A0].[FirstName],[A0].[LastName],DEFAULT);";
Assert.AreEqual(expected, sql);
}
public TNext Set(NullableByteTableColumn column, byte?value)
{
return(this.SetGeneric(column, SqQueryBuilder.Literal(value)));
}
public TableItUser(Alias alias) : base(schema : "dbo", name : "ItUser", alias : alias)
{
this.UserId = this.CreateInt32Column("UserId", ColumnMeta.PrimaryKey().Identity());
this.ExternalId = this.CreateGuidColumn("ExternalId", null);
this.FirstName = this.CreateStringColumn(name: "FirstName", size: 255, isUnicode: false, isText: false, columnMeta: null);
this.LastName = this.CreateStringColumn(name: "LastName", size: 255, isUnicode: false, isText: false, columnMeta: null);
this.Email = this.CreateStringColumn(name: "Email", size: 255, isUnicode: false, isText: false, columnMeta: null);
this.RegDate = this.CreateDateTimeColumn("RegDate", false, null);
this.Version = this.CreateInt32Column("Version", ColumnMeta.DefaultValue(0));
this.Created = this.CreateDateTimeColumn("Created", false, ColumnMeta.DefaultValue(SqQueryBuilder.GetUtcDate()));
this.Modified = this.CreateDateTimeColumn("Modified", false, ColumnMeta.DefaultValue(SqQueryBuilder.GetUtcDate()));
this.AddUniqueClusteredIndex(this.ExternalId);
this.AddIndex(this.FirstName);
this.AddIndex(IndexMetaColumn.Desc(this.LastName));
}
public static ExprStringConcat operator +(string a, ExprValue b) => new ExprStringConcat(SqQueryBuilder.Literal(a), b);
public static ExprModulo operator %(int?a, ExprValue b) => new ExprModulo(SqQueryBuilder.Literal(a), b);
public static ExprDiv operator /(double?a, ExprValue b) => new ExprDiv(SqQueryBuilder.Literal(a), b);
public static ExprMul operator *(double?a, ExprValue b) => new ExprMul(SqQueryBuilder.Literal(a), b);
public static ExprSub operator -(double?a, ExprValue b) => new ExprSub(SqQueryBuilder.Literal(a), b);
public void FullTest()
{
const int usersCount = 3;
var data = new List <UserData>(usersCount);
for (int i = 0; i < usersCount; i++)
{
data.Add(new UserData
{
UserId = i % 2 == 0 ? 0 : i,
FirstName = "First" + i,
LastName = "Last" + i,
EMail = $"user{i}@company.com",
RegDate = new DateTime(2020, 01, 02)
});
}
DateTime utcNow = new DateTime(2020, 10, 03, 10, 17, 12, 131);
var recordIndex = CustomColumnFactory.Int32("Index");
var inserted = CustomColumnFactory.Int32("InsertedUserId");
var deleted = CustomColumnFactory.Int32("DeletedUserId");
var action = CustomColumnFactory.String("Action");
var mergeOutput = SqQueryBuilder
.MergeDataInto(Tables.User(), data)
.MapDataKeys(s => s.Set(s.Target.UserId, s.Source.UserId))
.MapData(s => s
.Set(s.Target.FirstName, s.Source.FirstName)
.Set(s.Target.LastName, s.Source.LastName)
.Set(s.Target.Email, s.Source.EMail)
.Set(s.Target.RegDate, s.Source.RegDate))
.MapExtraData(s => s.Set(recordIndex, s.Index))
.AndOn((t, s) => t.UserId.WithSource(s) != 0)
.WhenMatchedThenUpdate()
.AlsoSet(s =>
s.Set(s.Target.Version, s.Target.Version + 1)
.Set(s.Target.Modified, utcNow))
.WhenNotMatchedByTargetThenInsert()
.ExcludeKeys()
.Exclude(t => new[] { t.Email.ColumnName, t.LastName.ColumnName })
.AlsoInsert(s => s
.Set(s.Target.LastName, "Fake")
.Set(s.Target.Created, utcNow)
.Set(s.Target.Modified, utcNow)
.Set(s.Target.Version, 1))
.WhenNotMatchedBySourceThenDelete()
.Output((t, s, m) => m
.Inserted(t.UserId.As(inserted))
.Inserted(t.UserId.As(deleted))
.Column(recordIndex.WithSource(s))
.Action(action))
.Done();
var actual = mergeOutput?.ToSql();
var expected = "MERGE [dbo].[user] [A0] USING (" +
"VALUES (0,'First0','Last0','*****@*****.**','2020-01-02',0)," +
"(1,'First1','Last1','*****@*****.**','2020-01-02',1)," +
"(0,'First2','Last2','*****@*****.**','2020-01-02',2)" +
")[A1]([UserId],[FirstName],[LastName],[Email],[RegDate],[Index]) " +
"ON [A0].[UserId]=[A1].[UserId] AND [A1].[UserId]!=0 " +
"WHEN MATCHED THEN UPDATE SET " +
"[A0].[FirstName]=[A1].[FirstName]," +
"[A0].[LastName]=[A1].[LastName]," +
"[A0].[Email]=[A1].[Email]," +
"[A0].[RegDate]=[A1].[RegDate]," +
"[A0].[Version]=[A0].[Version]+1," +
"[A0].[Modified]='2020-10-03T10:17:12.131' " +
"WHEN NOT MATCHED THEN INSERT" +
"([FirstName],[RegDate],[LastName],[Created],[Modified],[Version]) " +
"VALUES([A1].[FirstName],[A1].[RegDate],'Fake','2020-10-03T10:17:12.131','2020-10-03T10:17:12.131',1) " +
"WHEN NOT MATCHED BY SOURCE THEN DELETE " +
"OUTPUT INSERTED.[UserId] [InsertedUserId],INSERTED.[UserId] [DeletedUserId],[A1].[Index],$ACTION [Action];";
Assert.AreEqual(expected, actual);
}
private static string AppendStringEscapeMySql(string original)
{
var sql = SqQueryBuilder.Literal(original).ToMySql();
return(sql.Substring(1, sql.Length - 2));
}
protected override IExprSubQuery CreateQuery()
{
return(SqQueryBuilder.SelectOne().Done());
}
public TNext Set(NullableDecimalTableColumn column, decimal?value)
{
return(this.SetGeneric(column, SqQueryBuilder.Literal(value)));
}
public TNext Set(NullableInt64TableColumn column, long?value)
{
return(this.SetGeneric(column, SqQueryBuilder.Literal(value)));
}
public TNext Set(NullableByteArrayTableColumn column, IReadOnlyList <byte>?value)
{
return(this.SetGeneric(column, SqQueryBuilder.Literal(value)));
}
public static ExprSum operator +(double a, ExprValue b) => new ExprSum(SqQueryBuilder.Literal(a), b);
public static ExprSub operator -(ExprValue a, double?b) => new ExprSub(a, SqQueryBuilder.Literal(b));
public CaseThenNext Then(string value) => this.Then(SqQueryBuilder.Literal(value));
public static ExprMul operator *(ExprValue a, double?b) => new ExprMul(a, SqQueryBuilder.Literal(b));
public CaseThenNext Then(DateTime?value) => this.Then(SqQueryBuilder.LiteralCast(value));
public static ExprDiv operator /(ExprValue a, double?b) => new ExprDiv(a, SqQueryBuilder.Literal(b));
public CaseThenNext Then(double value) => this.Then(SqQueryBuilder.LiteralCast(value));
public static ExprModulo operator %(ExprValue a, int?b) => new ExprModulo(a, SqQueryBuilder.Literal(b));
public CaseThenNext Then(IReadOnlyList <byte> value) => this.Then(SqQueryBuilder.Literal(value));
//String public static ExprStringConcat operator +(ExprValue a, string b) => new ExprStringConcat(a, SqQueryBuilder.Literal(b));
public TableItOrder(Alias alias) : base(schema : "dbo", name : "ItOrder", alias : alias)
{
this.OrderId = this.CreateInt32Column("OrderId", ColumnMeta.PrimaryKey().Identity());
this.CustomerId = this.CreateInt32Column("CustomerId", ColumnMeta.ForeignKey <TableItCustomer>(t => t.CustomerId));
this.DateCreated = this.CreateDateTimeColumn("DateCreated", false, ColumnMeta.DefaultValue(SqQueryBuilder.GetUtcDate()));
this.Notes = this.CreateNullableStringColumn(name: "Notes", size: 100, isUnicode: true, isText: false, columnMeta: null);
}
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 TableCompany(Alias alias) : base("dbo", "Company", alias)
{
this.CompanyId = this.CreateInt32Column(nameof(this.CompanyId), ColumnMeta.PrimaryKey().Identity());
this.CompanyName = this.CreateStringColumn(nameof(this.CompanyName), 250);
this.Version = this.CreateInt32Column("Version",
ColumnMeta.DefaultValue(0));
this.ModifiedAt = this.CreateDateTimeColumn("ModifiedAt",
columnMeta: ColumnMeta.DefaultValue(SqQueryBuilder.GetUtcDate()));
}
public UpdateBuilderSetter Set(ExprColumn col, double value) => this.Set(col, SqQueryBuilder.Literal(value));
//Double public static ExprSum operator +(ExprValue a, double b) => new ExprSum(a, SqQueryBuilder.Literal(b));
