private static async Task Step11AnalyticAndWindowFunctions(ISqDatabase database) { var cUserName = CustomColumnFactory.String("Name"); var cNum = CustomColumnFactory.Int64("Num"); var cFirst = CustomColumnFactory.String("First"); var cLast = CustomColumnFactory.String("Last"); var user = new TableUser(); await Select( (user.FirstName + " " + user.LastName) .As(cUserName), RowNumber() /*.OverPartitionBy(some fields)*/ .OverOrderBy(user.FirstName) .As(cNum), FirstValue(user.FirstName + " " + user.LastName) /*.OverPartitionBy(some fields)*/ .OverOrderBy(user.FirstName) .FrameClauseEmpty() .As(cFirst), LastValue(user.FirstName + " " + user.LastName) /*.OverPartitionBy(some fields)*/ .OverOrderBy(user.FirstName) .FrameClause( FrameBorder.UnboundedPreceding, FrameBorder.UnboundedFollowing) .As(cLast)) .From(user) .Query(database, r => Console.WriteLine( $"Num: {cNum.Read(r)}, Name: {cUserName.Read(r)}, " + $"First: {cFirst.Read(r)}, Last: {cLast.Read(r)}")); }
private static async Task InsertCustomers(IScenarioContext context) { var userTable = AllTables.GetItUser(); var customerTable = AllTables.GetItCustomer(); await InsertInto(customerTable, customerTable.UserId) .From(Select(userTable.UserId) .From(userTable) .Where(!Exists(SelectOne() .From(customerTable) .Where(customerTable.UserId == userTable.UserId)))) .Exec(context.Database); context.WriteLine("Customers inserted:"); var clCount = CustomColumnFactory.Int64("Count"); var res = await SelectDistinct(customerTable.CustomerId, userTable.UserId, Cast(CountOneOver(), SqlType.Int64).As(clCount)) .From(customerTable) .InnerJoin(userTable, @on: customerTable.UserId == userTable.UserId) .OrderBy(userTable.UserId) .OffsetFetch(0, 5) .QueryList(context.Database, r => (UserId: userTable.UserId.Read(r), CustomerId: customerTable.CustomerId.Read(r), Count: clCount.Read(r))); foreach (var tuple in res) { Console.WriteLine(tuple); } }
private static async Task Step12Merge(ISqDatabase database) { var data = new[] { new { FirstName = "Francois", LastName = "Sturman2" }, new { FirstName = "Allina", LastName = "Freeborne2" }, new { FirstName = "Maye", LastName = "Malloy" }, }; var action = CustomColumnFactory.String("Actions"); var inserted = CustomColumnFactory.NullableInt32("Inserted"); var deleted = CustomColumnFactory.NullableInt32("Deleted"); var tableUser = new TableUser(); await MergeDataInto(tableUser, data) .MapDataKeys(s => s .Set(s.Target.FirstName, s.Source.FirstName)) .MapData(s => s .Set(s.Target.LastName, s.Source.LastName)) .WhenMatchedThenUpdate() .AlsoSet(s => s .Set(s.Target.Version, s.Target.Version + 1) .Set(s.Target.ModifiedAt, GetUtcDate())) .WhenNotMatchedByTargetThenInsert() .AlsoInsert(s => s .Set(s.Target.Version, 1) .Set(s.Target.ModifiedAt, GetUtcDate())) .Output((t, s, m) => m.Inserted(t.UserId.As(inserted)).Deleted(t.UserId.As(deleted)).Action(action)) .Done() .Query(database, r => Console.WriteLine( $"UserId Inserted: {inserted.Read(r)},UserId Deleted: {deleted.Read(r)} , Action: {action.Read(r)}")); }
private static async Task Step14TreeExploring(ISqDatabase database) { //Var some external filter.. ExprBoolean filter = CustomColumnFactory.Int16("Type") == 2 /*Company*/; var tableCustomer = new TableCustomer(); var baseSelect = Select(tableCustomer.CustomerId) .From(tableCustomer) .Where(filter) .Done(); //Checking that filter has "Type" column var hasVirtualColumn = filter.SyntaxTree() .FirstOrDefault <ExprColumnName>(e => e.Name == "Type") != null; if (hasVirtualColumn) { baseSelect = (ExprQuerySpecification)baseSelect.SyntaxTree() .Modify(e => { var result = e; //Joining with the sub query if (e is TableCustomer table) { var derivedTable = new DerivedTableCustomer(); result = new ExprJoinedTable( table, ExprJoinedTable.ExprJoinType.Inner, derivedTable, table.CustomerId == derivedTable.CustomerId); } return(result); }); } await baseSelect ! .Query(database, r => Console.WriteLine($"Id: {tableCustomer.CustomerId.Read(r)}")); }
private static async Task Step8JoinTables(ISqDatabase database) { var tUser = new TableUser(); var tCompany = new TableCompany(); var tCustomer = new TableCustomer(); var cType = CustomColumnFactory.Int16("Type"); var cName = CustomColumnFactory.String("Name"); var customers = await Select( tCustomer.CustomerId, Case() .When(IsNotNull(tUser.UserId)) .Then(Cast(1, SqlType.Int16)) .When(IsNotNull(tCompany.CompanyId)) .Then(Cast(2, SqlType.Int16)) .Else(Null) .As(cType), Case() .When(IsNotNull(tUser.UserId)) .Then(tUser.FirstName + " " + tUser.LastName) .When(IsNotNull(tCompany.CompanyId)) .Then(tCompany.CompanyName) .Else(Null) .As(cName) ) .From(tCustomer) .LeftJoin(tUser, on: tUser.UserId == tCustomer.UserId) .LeftJoin(tCompany, on: tCompany.CompanyId == tCustomer.CompanyId) .QueryList(database, r => (Id: tCustomer.CustomerId.Read(r), CustomerType: cType.Read(r), Name: cName.Read(r))); foreach (var customer in customers) { Console.WriteLine($"Id: {customer.Id}, Name: {customer.Name}, Type: {customer.CustomerType}"); } }
public void InValuesTest() { var a = CustomColumnFactory.Int32("a"); Assert.AreEqual("[a] IN(1)", a.In(Literal(1)).ToSql()); Assert.AreEqual("[a] IN(1,2,3)", a.In(Literal(1), Literal(2), Literal(3)).ToSql()); Assert.AreEqual("[a] IN(1,2)", a.In(new [] { Literal(1), Literal(2) }).ToSql()); Assert.AreEqual("[a] IN(1)", a.In(1).ToSql()); Assert.AreEqual("[a] IN(1,2,3)", a.In(1, 2, 3).ToSql()); Assert.AreEqual("[a] IN(1,2)", a.In(new [] { 1, 2 }).ToSql()); Assert.AreEqual("[a] IN('1')", a.In("1").ToSql()); Assert.AreEqual("[a] IN('1','2','3')", a.In("1", "2", "3").ToSql()); Assert.AreEqual("[a] IN('1','2')", a.In(new [] { "1", "2" }).ToSql()); var g1 = Guid.Parse("F46E2EC5-E08F-4CB9-8FD5-62DAC1A90C85"); var g2 = Guid.Parse("FE716966-74D9-4449-83CE-16371698E8D0"); var g3 = Guid.Parse("9614F808-E9EA-4BFE-8432-3711EB7E235C"); Assert.AreEqual("[a] IN('f46e2ec5-e08f-4cb9-8fd5-62dac1a90c85')", a.In(g1).ToSql()); Assert.AreEqual("[a] IN('f46e2ec5-e08f-4cb9-8fd5-62dac1a90c85','fe716966-74d9-4449-83ce-16371698e8d0','9614f808-e9ea-4bfe-8432-3711eb7e235c')", a.In(g1, g2, g3).ToSql()); Assert.AreEqual("[a] IN('f46e2ec5-e08f-4cb9-8fd5-62dac1a90c85','fe716966-74d9-4449-83ce-16371698e8d0')", a.In(new [] { g1, g2 }).ToSql()); }
private static async Task Step11SubQueries(ISqDatabase database) { var num = CustomColumnFactory.Int32("3"); //Note: "3" (the first value) is for compatibility with MySql //which does not properly support values constructors var sum = CustomColumnFactory.Int32("Sum"); var numbers = Values(3, 1, 1, 7, 3, 7, 3, 7, 7, 8).AsColumns(num); var numbersSubQuery = TableAlias(); var mostFrequentNum = (int) await SelectTop(1, numbersSubQuery.Column(num)) .From( Select(numbers.Column(num), CountOne().As(sum)) .From(numbers) .GroupBy(numbers.Column(num)) .As(numbersSubQuery) ) .OrderBy(Desc(numbersSubQuery.Column(sum))) .QueryScalar(database); Console.WriteLine("The most frequent number: " + mostFrequentNum); }
public void TopTest() { var actual = SelectDistinct(Literal(2)).Done().ToSql(); Assert.AreEqual("SELECT DISTINCT 2", actual); actual = SelectTopDistinct(Literal(3), Literal(2)).Done().ToSql(); Assert.AreEqual("SELECT DISTINCT TOP 3 2", actual); actual = SelectTopDistinct(4, Literal(2)).Done().ToSql(); Assert.AreEqual("SELECT DISTINCT TOP 4 2", actual); actual = SelectTop(5, Literal(2)).Done().ToSql(); Assert.AreEqual("SELECT TOP 5 2", actual); actual = SelectTop(Literal(6), Literal(2)).Done().ToSql(); Assert.AreEqual("SELECT TOP 6 2", actual); actual = SelectDistinct(2).Done().ToSql(); Assert.AreEqual("SELECT DISTINCT 2", actual); actual = SelectTopDistinct(Literal(3), 2).Done().ToSql(); Assert.AreEqual("SELECT DISTINCT TOP 3 2", actual); actual = SelectTopDistinct(4, 2).Done().ToSql(); Assert.AreEqual("SELECT DISTINCT TOP 4 2", actual); actual = SelectTop(5, 2).Done().ToSql(); Assert.AreEqual("SELECT TOP 5 2", actual); actual = SelectTop(Literal(6), 2, "Hi").Done().ToSql(); Assert.AreEqual("SELECT TOP 6 2,'Hi'", actual); actual = SelectTop(Literal(9) % 7, 2, AllColumns()).Done().ToSql(); Assert.AreEqual("SELECT TOP 9%7 2,*", actual); actual = SelectDistinct(2, AllColumns()).Done().ToSql(); Assert.AreEqual("SELECT DISTINCT 2,*", actual); actual = SelectTopDistinct(Literal(3), 2, AllColumns()).Done().ToSql(); Assert.AreEqual("SELECT DISTINCT TOP 3 2,*", actual); actual = SelectTopDistinct(4, 2, AllColumns()).Done().ToSql(); Assert.AreEqual("SELECT DISTINCT TOP 4 2,*", actual); var columns = new [] { CustomColumnFactory.Int32("a"), CustomColumnFactory.Int32("b") }; var columns2 = new [] { CustomColumnFactory.Int32("c"), CustomColumnFactory.Int32("d") }; var columnE = CustomColumnFactory.Int32("e"); var columnF = CustomColumnFactory.Int32("f"); actual = Select(columns).Done().ToSql(); Assert.AreEqual("SELECT [a],[b]", actual); actual = SelectTop(2, columns).Done().ToPgSql(); Assert.AreEqual("SELECT \"a\",\"b\" LIMIT 2", actual); actual = SelectTop(Literal(2), columns).Done().ToMySql(); Assert.AreEqual("SELECT `a`,`b` LIMIT 2", actual); actual = SelectDistinct(columns.Concat(columns2)).Done().ToSql(); Assert.AreEqual("SELECT DISTINCT [a],[b],[c],[d]", actual); actual = SelectTopDistinct(2, columns.Concat(columns2).Concat(columnE, columnF)).Done().ToSql(); Assert.AreEqual("SELECT DISTINCT TOP 2 [a],[b],[c],[d],[e],[f]", actual); actual = SelectTopDistinct(Literal(2), columns.Concat(columns2).Concat(columnE)).Done().ToSql(); Assert.AreEqual("SELECT DISTINCT TOP 2 [a],[b],[c],[d],[e]", actual); }
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); }
public async Task Exec(IScenarioContext context) { var tbl = new IdModified(); DateTime now = new DateTime(2020, 10, 18); await context.Database.Statement(tbl.Script.Create()); await InsertDataInto(tbl, GetItems()) .MapData(s => s.Set(s.Target.Modified, s.Source)) .Exec(context.Database); context.WriteLine("Data from temporary table:"); var clYear = CustomColumnFactory.DateTime(nameof(DateAddDatePart.Year)); var clMonth = CustomColumnFactory.DateTime(nameof(DateAddDatePart.Month)); var clWeek = CustomColumnFactory.DateTime(nameof(DateAddDatePart.Week)); var clDay = CustomColumnFactory.DateTime(nameof(DateAddDatePart.Day)); var clHour = CustomColumnFactory.DateTime(nameof(DateAddDatePart.Hour)); var clMinute = CustomColumnFactory.DateTime(nameof(DateAddDatePart.Minute)); var clSecond = CustomColumnFactory.DateTime(nameof(DateAddDatePart.Second)); var clMillisecond = CustomColumnFactory.DateTime(nameof(DateAddDatePart.Millisecond)); var number = 3; var result = await Select( tbl.Id, tbl.Modified, DateAdd(DateAddDatePart.Year, number, tbl.Modified).As(clYear), DateAdd(DateAddDatePart.Month, number, tbl.Modified).As(clMonth), DateAdd(DateAddDatePart.Week, number, tbl.Modified).As(clWeek), DateAdd(DateAddDatePart.Day, number, tbl.Modified).As(clDay), DateAdd(DateAddDatePart.Hour, number, tbl.Modified).As(clHour), DateAdd(DateAddDatePart.Minute, number, tbl.Modified).As(clMinute), DateAdd(DateAddDatePart.Second, number, tbl.Modified).As(clSecond), DateAdd(DateAddDatePart.Millisecond, number, tbl.Modified).As(clMillisecond) ) .From(tbl) .QueryList(context.Database, r => new { Original = tbl.Modified.Read(r), Year = clYear.Read(r), Month = clMonth.Read(r), Week = clWeek.Read(r), Day = clDay.Read(r), Hour = clHour.Read(r), Minute = clMinute.Read(r), Second = clSecond.Read(r), Millisecond = clMillisecond.Read(r) }); //Checking Items foreach (var r in result) { AssertDatesEqual(r.Original.AddYears(number), r.Year, "Year"); AssertDatesEqual(r.Original.AddMonths(number), r.Month, "Month"); AssertDatesEqual(r.Original.AddDays(number * 7), r.Week, "Week"); AssertDatesEqual(r.Original.AddDays(number), r.Day, "Day"); AssertDatesEqual(r.Original.AddHours(number), r.Hour, "Hour"); AssertDatesEqual(r.Original.AddMinutes(number), r.Minute, "Minute"); AssertDatesEqual(r.Original.AddSeconds(number), r.Second, "Second"); AssertDatesEqual(r.Original.AddMilliseconds(number), r.Millisecond, "Millisecond"); } context.WriteLine("All dates are correct!"); await context.Database.Statement(tbl.Script.Drop()); await context.Database.Statement(tbl.Script.DropIfExist()); IEnumerable <DateTime> GetItems() { for (int i = 0; i < 10; i++) { now = now.AddDays(-1); yield return(now); } } void AssertDatesEqual(DateTime expected, DateTime actual, string mode) { if (expected != actual) { throw new SqExpressException($"{context.Dialect} {mode} - Expected '{expected:O}' does not equal to actual '{actual:O}'"); } } }