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}'");
}
}
}
