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));