public async Task Exec(IScenarioContext context)
{
var tCustomer = AllTables.GetItCustomer();
var tUser = AllTables.GetItUser();
var topUsers = new TopFirstNameUsers(5);
await Delete(tCustomer)
.From(tCustomer)
.InnerJoin(topUsers, @on: topUsers.UserId == tCustomer.UserId)
.All()
.Exec(context.Database);
context.WriteLine("Deleted:");
var list = await Select(tUser.FirstName, tUser.LastName)
.From(tUser)
.Where(!Exists(SelectOne().From(tCustomer).Where(tCustomer.UserId == tUser.UserId)))
.OrderBy(tUser.FirstName)
.QueryList(context.Database, r => (FirstName: tUser.FirstName.Read(r), LastName: tUser.LastName.Read(r)));
foreach (var name in list)
{
context.WriteLine($"{name.FirstName} {name.LastName}");
}
}
public async Task Exec(IScenarioContext context)
{
foreach (var scenario in this._scenarios)
{
context.WriteLine($"--{scenario.GetType().Name}--");
context.WriteLine(null);
await scenario.Exec(context);
context.WriteLine(null);
}
}
public async Task Exec(IScenarioContext context)
{
bool isPostgres = context.Dialect == SqlDialect.PgSql;
var table = new TableItAllColumnTypes(isPostgres);
await context.Database.Statement(table.Script.DropAndCreate());
var testData = GetTestData(isPostgres);
await InsertDataInto(table, testData)
.MapData(Mapping)
.Exec(context.Database);
var mapper = new Mapper(new MapperConfiguration(cfg =>
{
cfg.AddDataReaderMapping();
var map = cfg.CreateMap <IDataRecord, AllColumnTypesDto>();
map
.ForMember(nameof(table.ColByteArraySmall), c => c.Ignore())
.ForMember(nameof(table.ColByteArrayBig), c => c.Ignore())
.ForMember(nameof(table.ColNullableByteArraySmall), c => c.Ignore())
.ForMember(nameof(table.ColNullableByteArrayBig), c => c.Ignore())
.ForMember(nameof(table.ColNullableFixedSizeByteArray), c => c.Ignore())
.ForMember(nameof(table.ColFixedSizeByteArray), c => c.Ignore());
if (isPostgres)
{
map
.ForMember(nameof(table.ColByte), c => c.Ignore())
.ForMember(nameof(table.ColNullableByte), c => c.Ignore());
}
if (context.Dialect == SqlDialect.MySql)
{
map
.ForMember(nameof(table.ColBoolean), c => c.MapFrom((r, dto) => r.GetBoolean(r.GetOrdinal(nameof(table.ColBoolean)))))
.ForMember(nameof(table.ColNullableBoolean), c => c.MapFrom((r, dto) => r.IsDBNull(r.GetOrdinal(nameof(table.ColNullableBoolean))) ? (bool?)null : r.GetBoolean(r.GetOrdinal(nameof(table.ColNullableBoolean)))))
.ForMember(nameof(table.ColGuid), c => c.MapFrom((r, dto) => r.GetGuid(r.GetOrdinal(nameof(table.ColGuid)))))
.ForMember(nameof(table.ColNullableGuid), c => c.MapFrom((r, dto) => r.IsDBNull(r.GetOrdinal(nameof(table.ColNullableGuid)))? (Guid?)null : r.GetGuid(r.GetOrdinal(nameof(table.ColNullableGuid)))));
}
}));
var expr = Select(table.Columns)
.From(table).Done();
context.WriteLine(PgSqlExporter.Default.ToSql(expr));
var result = await expr
.QueryList(context.Database, r =>
{
var allColumnTypesDto = mapper.Map <IDataRecord, AllColumnTypesDto>(r);
allColumnTypesDto.ColByteArrayBig = StreamToByteArray(table.ColByteArrayBig.GetStream(r));
allColumnTypesDto.ColByteArraySmall = table.ColByteArraySmall.Read(r);
allColumnTypesDto.ColNullableByteArrayBig = table.ColNullableByteArrayBig.Read(r);
allColumnTypesDto.ColNullableByteArraySmall = table.ColNullableByteArraySmall.Read(r);
allColumnTypesDto.ColFixedSizeByteArray = table.ColFixedSizeByteArray.Read(r);
allColumnTypesDto.ColNullableFixedSizeByteArray = table.ColNullableFixedSizeByteArray.Read(r);
return(allColumnTypesDto);
});
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);
}
}
public async Task Exec(IScenarioContext context)
{
var data = this.ReadUserData();
var utcNow = DateTime.UtcNow;
var userTable = AllTables.GetItUser();
var ids = await InsertDataInto(userTable, data)
.MapData(s => s
.Set(s.Target.ExternalId, s.Source.ExternalId)
.Set(s.Target.FirstName, s.Source.FirstName)
.Set(s.Target.LastName, s.Source.LastName)
.Set(s.Target.Email, s.Source.Email)
)
.AlsoInsert(s => s
.Set(s.Target.RegDate, utcNow)
.Set(s.Target.Version, 1)
.Set(s.Target.Created, utcNow)
.Set(s.Target.Modified, utcNow)
)
.Output(userTable.UserId, userTable.FirstName, userTable.LastName)
.QueryList(context.Database, r => $"{userTable.UserId.Read(r)}, {userTable.FirstName.Read(r)} {userTable.LastName.Read(r)}");
foreach (var id in ids.Take(3))
{
context.WriteLine(id);
}
context.WriteLine("...");
context.WriteLine($"Total users inserted: {ids.Count}");
var count = (long) await Select(Cast(CountOne(), SqlType.Int64)).From(userTable).QueryScalar(context.Database);
context.WriteLine($"Users count: {count}");
await InsertCustomers(context);
}
public async Task Exec(IScenarioContext context)
{
string[] ids =
{
"admin'--",
"10; DROP TABLE members /*",
"Line1\r\n\tLine2\\%%\b",
"--",
"\'",
"\\",
"\\'",
"\\\\",
"`\"'",
"``\\`\\\\`\"\\\"\\\\\"'\\'\\\\'",
"\"\\\"'\'\\'*`\\`;"
};
var expr = Select(ids.Select(id => Literal(id).As(id)).ToList()).Done();
var res = await expr.Query(context.Database, new List <string>(),
(acc, r) =>
{
foreach (var id in ids)
{
acc.Add(r.GetString(id));
}
return(acc);
});
for (var index = 0; index < ids.Length; index++)
{
if (res[index] != ids[index])
{
context.WriteLine(context.Dialect.GetExporter().ToSql(expr));
throw new Exception("Sql Injection!");
}
}
}
private static async Task SelectAllColumns(IScenarioContext context)
{
void PrintColumns(ISqDataRecordReader r)
{
for (int i = 0; i < r.FieldCount; i++)
{
context.Write(r.GetName(i) + ",");
}
context.WriteLine(null);
}
var tUser = AllTables.GetItUser();
var tCustomer = AllTables.GetItCustomer();
await SelectTop(1, AllColumns()).From(tUser).Query(context.Database, PrintColumns);
await SelectTop(1, tUser.AllColumns()).From(tUser).Query(context.Database, PrintColumns);
await SelectTop(1, tUser.AllColumns(), tCustomer.AllColumns())
.From(tUser)
.InnerJoin(tCustomer, on: tCustomer.UserId == tUser.UserId)
.Query(context.Database, PrintColumns);
}
public async Task Exec(IScenarioContext context)
{
var company = AllTables.GetItCompany();
DateTime now = DateTime.UtcNow;
var inserted = await InsertDataInto(company, this.ReadCompanyData())
.MapData(s => s
.Set(s.Target.ExternalId, s.Source.ExternalId)
.Set(s.Target.CompanyName, s.Source.Name))
.AlsoInsert(s => s
.Set(s.Target.Modified, now)
.Set(s.Target.Created, now)
.Set(s.Target.Version, 1))
.Output(company.CompanyId)
.QueryList(context.Database, r => company.CompanyId.Read(r));
var customer = AllTables.GetItCustomer();
//Insert customer
await InsertDataInto(customer, inserted)
.MapData(s => s.Set(s.Target.CompanyId, s.Source)).Exec(context.Database);
context.WriteLine($"{inserted.Count} have been inserted into {nameof(TableItCustomer)}");
var tCustomerName = new CustomerName();
var users = await Select(tCustomerName.Columns)
.From(tCustomerName)
.Where(tCustomerName.CustomerTypeId == 1)
.OrderBy(tCustomerName.Name)
.OffsetFetch(0, 5)
.QueryList(context.Database, r => ReadCustomerName(r, tCustomerName));
var companies = await Select(tCustomerName.Columns)
.From(tCustomerName)
.Where(tCustomerName.CustomerTypeId == 2)
.OrderBy(tCustomerName.CustomerId)
.OffsetFetch(0, 5)
.QueryList(context.Database, r => ReadCustomerName(r, tCustomerName));
context.WriteLine(null);
context.WriteLine("Top 5 Users users: ");
context.WriteLine(null);
foreach (var valueTuple in users)
{
Console.WriteLine(valueTuple);
}
context.WriteLine(null);
context.WriteLine("Top 5 Users companies: ");
context.WriteLine(null);
foreach (var valueTuple in companies)
{
Console.WriteLine(valueTuple);
}
(int Id, string Name, short CType) ReadCustomerName(ISqDataRecordReader r, CustomerName customerName)
{
return(Id : customerName.CustomerId.Read(r), Name : customerName.Name.Read(r), CType : customerName.CustomerTypeId.Read(r));
}
}
public async Task Exec(IScenarioContext context)
{
var company = AllTables.GetItCompany();
DateTime now = DateTime.UtcNow;
var inserted = await InsertDataInto(company, this.ReadCompanyData())
.MapData(s => s
.Set(s.Target.ExternalId, s.Source.ExternalId)
.Set(s.Target.CompanyName, s.Source.Name))
.AlsoInsert(s => s
.Set(s.Target.Modified, now)
.Set(s.Target.Created, now)
.Set(s.Target.Version, 1))
.Output(company.CompanyId)
.QueryList(context.Database, r => company.CompanyId.Read(r));
var insertedDuplicates = await InsertDataInto(company, this.ReadCompanyData().Take(2))
.MapData(s => s
.Set(s.Target.ExternalId, s.Source.ExternalId)
.Set(s.Target.CompanyName, s.Source.Name))
.AlsoInsert(s => s
.Set(s.Target.Modified, now)
.Set(s.Target.Created, now)
.Set(s.Target.Version, 1))
.CheckExistenceBy(company.ExternalId)
.Output(company.CompanyId)
.QueryList(context.Database, r => company.CompanyId.Read(r));
if (insertedDuplicates.Count > 0)
{
throw new Exception("CheckExistenceBy does not work");
}
var customer = AllTables.GetItCustomer();
//Insert customer
await InsertDataInto(customer, inserted)
.MapData(s => s.Set(s.Target.CompanyId, s.Source)).Exec(context.Database);
context.WriteLine($"{inserted.Count} have been inserted into {nameof(TableItCustomer)}");
var tCustomerName = new CustomerName();
var users = await Select(CustomerNameData.GetColumns(tCustomerName))
.From(tCustomerName)
.Where(tCustomerName.CustomerTypeId == 1)
.OrderBy(tCustomerName.Name)
.OffsetFetch(0, 5)
.QueryList(context.Database, r => CustomerNameData.Read(r, tCustomerName));
var companies = await Select(CustomerNameData.GetColumns(tCustomerName))
.From(tCustomerName)
.Where(tCustomerName.CustomerTypeId == 2)
.OrderBy(tCustomerName.CustomerId)
.OffsetFetch(0, 5)
.QueryList(context.Database, r => CustomerNameData.Read(r, tCustomerName));
context.WriteLine(null);
context.WriteLine("Top 5 Users users: ");
context.WriteLine(null);
foreach (var valueTuple in users)
{
Console.WriteLine(valueTuple);
}
context.WriteLine(null);
context.WriteLine("Top 5 Users companies: ");
context.WriteLine(null);
foreach (var valueTuple in companies)
{
Console.WriteLine(valueTuple);
}
}
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}'");
}
}
}
public async Task Exec(IScenarioContext context)
{
var tOrder = AllTables.GetItOrder();
var tOrderSub2 = AllTables.GetItOrder();
var vwCustomer = new CustomerName();
var numbers = Values(Enumerable.Range(1, 10).Select(Literal).ToList()).AsColumns("Num");
var getNum = numbers.Column(context.Dialect == SqlDialect.MySql ? "1" : "Num");
await InsertInto(tOrder, tOrder.CustomerId, tOrder.Notes)
.From(
Select(vwCustomer.CustomerId, ("Notes for " + vwCustomer.Name + " No:" + Cast(getNum, SqlType.String(5))).As("Notes"))
.From(vwCustomer)
.CrossJoin(numbers)
.OrderBy(vwCustomer.CustomerId, getNum)
)
.Exec(context.Database);
var count = await Select(Count(1)).From(tOrder).QueryScalar(context.Database);
context.WriteLine("Orders are inserted: " + count);
//Delete % 7
await Delete(tOrder)
.Where(tOrder.OrderId.In(Select(tOrderSub2.OrderId)
.From(tOrderSub2)
.Where(tOrderSub2.OrderId % 7 == 0)))
.Exec(context.Database);
count = await Select(Count(1)).From(tOrder).QueryScalar(context.Database);
context.WriteLine("Some Orders are deleted. Current count: " + count);
//Delete JOIN
await Delete(tOrder)
.From(tOrder)
.InnerJoin(vwCustomer, on: vwCustomer.CustomerId == tOrder.CustomerId)
.Where(tOrder.CustomerId % 7 + 1 == 1)
.Exec(context.Database);
//For my SQL number is different since Auto Increment is not reset on delete (ItCustomer)
count = await Select(Count(1)).From(tOrder).QueryScalar(context.Database);
context.WriteLine("Some Orders are deleted. Current count: " + count);
await Update(tOrder)
.Set(tOrder.Notes, tOrder.Notes + " (Updated 17)")
.Where(tOrder.OrderId % 17 == 0)
.Exec(context.Database);
await Update(tOrder)
.Set(tOrder.Notes, tOrder.Notes + " (Updated 19)")
.From(tOrder)
.Where(tOrder.OrderId % 19 == 0)
.Exec(context.Database);
await Update(tOrder)
.Set(tOrder.Notes, tOrder.Notes + " (Updated 23)")
.From(tOrder)
.InnerJoin(vwCustomer, on: vwCustomer.CustomerId == tOrder.CustomerId)
.Where(tOrder.OrderId % 23 == 0)
.Exec(context.Database);
}
public async Task Exec(IScenarioContext context)
{
var tt = new TestMergeTmpTable();
await context.Database.Statement(tt.Script.Create());
var testData = new List <TestMergeData>
{
new TestMergeData(1, 10),
new TestMergeData(2, 11)
};
//Insert
context.WriteLine("Inserting using MERGE..");
await SqQueryBuilder.MergeDataInto(tt, testData)
.MapDataKeys(TestMergeData.GetUpdateKeyMapping)
.MapData(TestMergeData.GetUpdateMapping)
.WhenMatchedThenUpdate()
.AlsoSet(s => s.Set(s.Target.Version, s.Target.Version + 1))
.WhenNotMatchedByTargetThenInsert()
.Done()
.Exec(context.Database);
var dataFromDb = await SqQueryBuilder.Select(TestMergeDataRow.GetColumns(tt))
.From(tt)
.OrderBy(tt.Id)
.QueryList(context.Database, r => TestMergeDataRow.Read(r, tt));
if (RowDataToString(dataFromDb) != "1,10,0;2,11,0")
{
throw new Exception("Incorrect data");
}
context.WriteLine("Updating using MERGE..");
testData.Add(new TestMergeData(3, 12));
testData[0] = testData[0].WithValue(100);
await SqQueryBuilder.MergeDataInto(tt, testData)
.MapDataKeys(TestMergeData.GetUpdateKeyMapping)
.MapData(TestMergeData.GetUpdateMapping)
.WhenMatchedThenUpdate((s, t) => tt.Value.WithSource(t) != s.Value.WithSource(s.Alias))
.AlsoSet(s => s.Set(s.Target.Version, s.Target.Version + 1))
.WhenNotMatchedByTargetThenInsert()
.Done()
.Exec(context.Database);
dataFromDb = await SqQueryBuilder.Select(TestMergeDataRow.GetColumns(tt))
.From(tt)
.OrderBy(tt.Id)
.QueryList(context.Database, r => TestMergeDataRow.Read(r, tt));
if (RowDataToString(dataFromDb) != "1,100,1;2,11,0;3,12,0")
{
throw new Exception("Incorrect data");
}
context.WriteLine("Updating (BY SOURCE) using MERGE..");
testData = new List <TestMergeData>
{
new TestMergeData(1, 17),
};
await SqQueryBuilder.MergeDataInto(tt, testData)
.MapDataKeys(TestMergeData.GetUpdateKeyMapping)
.MapData(TestMergeData.GetUpdateMapping)
.WhenMatchedThenUpdate((s, t) => tt.Value.WithSource(t) != s.Value.WithSource(s.Alias))
.AlsoSet(s => s.Set(s.Target.Version, s.Target.Version + 1))
.WhenNotMatchedBySourceThenUpdate(t => t.Value == 12).Set(s => s.Set(s.Target.Version, s.Target.Version + 10))
.Done()
.Exec(context.Database);
dataFromDb = await SqQueryBuilder.Select(TestMergeDataRow.GetColumns(tt))
.From(tt)
.OrderBy(tt.Id)
.QueryList(context.Database, r => TestMergeDataRow.Read(r, tt));
if (RowDataToString(dataFromDb) != "1,17,2;2,11,0;3,12,10")
{
throw new Exception("Incorrect data");
}
context.WriteLine("Deleting (BY SOURCE) using MERGE..");
await SqQueryBuilder.MergeDataInto(tt, testData)
.MapDataKeys(TestMergeData.GetUpdateKeyMapping)
.MapData(TestMergeData.GetUpdateMapping)
.WhenMatchedThenUpdate()
.AlsoSet(s => s.Set(s.Target.Version, s.Target.Version + 1))
.WhenNotMatchedBySourceThenDelete(t => t.Value == 12)
.Done()
.Exec(context.Database);
dataFromDb = await SqQueryBuilder.Select(TestMergeDataRow.GetColumns(tt))
.From(tt)
.OrderBy(tt.Id)
.QueryList(context.Database, r => TestMergeDataRow.Read(r, tt));
if (RowDataToString(dataFromDb) != "1,17,3;2,11,0")
{
throw new Exception("Incorrect data");
}
context.WriteLine("Deleting ON MATCH using MERGE..");
await SqQueryBuilder.MergeDataInto(tt, testData)
.MapDataKeys(TestMergeData.GetUpdateKeyMapping)
.MapData(TestMergeData.GetUpdateMapping)
.WhenMatchedThenDelete()
.Done()
.Exec(context.Database);
dataFromDb = await SqQueryBuilder.Select(TestMergeDataRow.GetColumns(tt))
.From(tt)
.OrderBy(tt.Id)
.QueryList(context.Database, r => TestMergeDataRow.Read(r, tt));
if (RowDataToString(dataFromDb) != "2,11,0")
{
throw new Exception("Incorrect data");
}
await context.Database.Statement(tt.Script.Drop());
}