public void TestExportImportJson()
{
var tUser = Tables.User();
var tCustomer = Tables.Customer();
var selectExpr = Select(tUser.UserId, tUser.FirstName, tCustomer.CustomerId, Cast(Literal(12.8m), SqlType.Decimal((10, 2))).As("Salary"))
.From(tUser)
.InnerJoin(tCustomer, on: tCustomer.UserId == tUser.UserId)
.Where(tUser.Version == 5)
.OrderBy(tUser.FirstName)
.OffsetFetch(100, 5)
.Done();
using MemoryStream writer = new MemoryStream();
selectExpr
.SyntaxTree()
.ExportToJson(new System.Text.Json.Utf8JsonWriter(writer));
var jsonText = Encoding.UTF8.GetString(writer.ToArray());
var doc = System.Text.Json.JsonDocument.Parse(jsonText);
var deserialized = ExprDeserializer.DeserializeFormJson(doc.RootElement);
Assert.AreEqual(selectExpr.ToSql(), deserialized.ToSql());
}
private static async Task Step18ExportToJson(ISqDatabase database)
{
var tableUser = new TableUser(Alias.Empty);
var selectExpr = Select(tableUser.FirstName, tableUser.LastName)
.From(tableUser)
.Where(tableUser.LastName == "Sturman")
.Done();
//Exporting
var memoryStream = new MemoryStream();
var jsonWriter = new Utf8JsonWriter(memoryStream);
selectExpr.SyntaxTree().ExportToJson(jsonWriter);
string json = Encoding.UTF8.GetString(memoryStream.ToArray());
Console.WriteLine(json);
//Importing
var restored = (ExprQuerySpecification)ExprDeserializer
.DeserializeFormJson(JsonDocument.Parse(json).RootElement);
var result = await restored
.QueryList(database, r => (tableUser.FirstName.Read(r), tableUser.LastName.Read(r)));
foreach (var name in result)
{
Console.WriteLine(name);
}
}
public void InsertDataWithExtraTest()
{
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)
});
}
var tbl = Tables.User();
var expr = InsertDataInto(tbl, data)
.MapData(i => i
.Set(i.Target.FirstName, i.Source.FirstName)
.Set(i.Target.LastName, i.Source.LastName)
.Set(i.Target.Email, i.Source.EMail)
.Set(i.Target.RegDate, i.Source.RegDate))
.AlsoInsert(s => s.Set(s.Target.Version, 5).Set(s.Target.Created, new DateTime(2020, 01, 02)))
.Output(tbl.UserId)
.Done();
var actual = expr.ToSql();
var expected = "INSERT INTO [dbo].[user]([FirstName],[LastName],[Email],[RegDate],[Version],[Created]) OUTPUT INSERTED.[UserId] SELECT [FirstName],[LastName],[Email],[RegDate],5,'2020-01-02' FROM (VALUES ('First0','Last0','*****@*****.**','2020-01-02'),('First1','Last1','*****@*****.**','2020-01-02'),('First2','Last2','*****@*****.**','2020-01-02'))[A0]([FirstName],[LastName],[Email],[RegDate])";
Assert.AreEqual(actual, expected);
//Serialization
var list = expr.SyntaxTree().ExportToPlainList(PlainItem.Create);
var after = ExprDeserializer.DeserializeFormPlainList(list);
Assert.AreEqual(expected, after.ToSql());
//My SQL
expected = "INSERT INTO `user`(`FirstName`,`LastName`,`Email`,`RegDate`,`Version`,`Created`) SELECT `A0`.*,5,'2020-01-02' FROM (VALUES ('First0','Last0','*****@*****.**','2020-01-02'),('First1','Last1','*****@*****.**','2020-01-02'),('First2','Last2','*****@*****.**','2020-01-02'))`A0` RETURNING `UserId`";
Assert.AreEqual(expected, after.ToMySql());
}
public void TestExportImportPlain()
{
var tUser = Tables.User();
var tCustomer = Tables.Customer();
var selectExpr = Select(tUser.UserId, tUser.FirstName, tCustomer.CustomerId, Cast(Literal(12.8m), SqlType.Decimal(new DecimalPrecisionScale(10, 2))).As("Salary"))
.From(tUser)
.InnerJoin(tCustomer, on: tCustomer.UserId == tUser.UserId)
.Where(tUser.Version == 5 & tUser.RegDate > new DateTime(2020, 10, 18, 1, 2, 3, 400) & tUser.RegDate <= new DateTime(2021, 01, 01))
.OrderBy(tUser.FirstName)
.OffsetFetch(100, 5)
.Done();
var items = selectExpr.SyntaxTree().ExportToPlainList(PlainItem.Create);
var res = ExprDeserializer.DeserializeFormPlainList(items);
Assert.AreEqual(selectExpr.ToSql(), res.ToSql());
}
private static async Task Step20ExportToPlain(ISqDatabase database)
{
var tableUser = new TableUser(Alias.Empty);
ExprBoolean filter1 = tableUser.LastName == "Sturman";
ExprBoolean filter2 = tableUser.LastName == "Freeborne";
var tableFavoriteFilter = new TableFavoriteFilter();
var tableFavoriteFilterItem = new TableFavoriteFilterItem();
var filterIds = await InsertDataInto(tableFavoriteFilter, new[] { "Filter 1", "Filter 2" })
.MapData(s => s.Set(s.Target.Name, s.Source))
.Output(tableFavoriteFilter.FavoriteFilterId)
.QueryList(database, r => tableFavoriteFilterItem.FavoriteFilterId.Read(r));
var filter1Items =
filter1.SyntaxTree()
.ExportToPlainList((i, id, index, b, s, value) =>
FilterPlainItem.Create(filterIds[0], i, id, index, b, s, value));
var filter2Items =
filter2.SyntaxTree()
.ExportToPlainList((i, id, index, b, s, value) =>
FilterPlainItem.Create(filterIds[1], i, id, index, b, s, value));
await InsertDataInto(tableFavoriteFilterItem, filter1Items.Concat(filter2Items))
.MapData(s => s
.Set(s.Target.FavoriteFilterId, s.Source.FavoriteFilterId)
.Set(s.Target.Id, s.Source.Id)
.Set(s.Target.ParentId, s.Source.ParentId)
.Set(s.Target.IsTypeTag, s.Source.IsTypeTag)
.Set(s.Target.ArrayIndex, s.Source.ArrayIndex)
.Set(s.Target.Tag, s.Source.Tag)
.Set(s.Target.Value, s.Source.Value)
)
.Exec(database);
//Restoring
var restoredFilterItems = await Select(tableFavoriteFilterItem.Columns)
.From(tableFavoriteFilterItem)
.Where(tableFavoriteFilterItem.FavoriteFilterId.In(filterIds))
.QueryList(
database,
r => new FilterPlainItem(
favoriteFilterId: tableFavoriteFilterItem.FavoriteFilterId.Read(r),
id: tableFavoriteFilterItem.Id.Read(r),
parentId: tableFavoriteFilterItem.ParentId.Read(r),
isTypeTag: tableFavoriteFilterItem.IsTypeTag.Read(r),
arrayIndex: tableFavoriteFilterItem.ArrayIndex.Read(r),
tag: tableFavoriteFilterItem.Tag.Read(r),
value: tableFavoriteFilterItem.Value.Read(r)));
var restoredFilter1 = (ExprBoolean)ExprDeserializer
.DeserializeFormPlainList(restoredFilterItems.Where(fi =>
fi.FavoriteFilterId == filterIds[0]));
var restoredFilter2 = (ExprBoolean)ExprDeserializer
.DeserializeFormPlainList(restoredFilterItems.Where(fi =>
fi.FavoriteFilterId == filterIds[1]));
Console.WriteLine("Filter 1");
await Select(tableUser.FirstName, tableUser.LastName)
.From(tableUser)
.Where(restoredFilter1)
.Query(database,
(object)null,
(s, r) =>
{
Console.WriteLine($"{tableUser.FirstName.Read(r)} {tableUser.LastName.Read(r)}");
return(s);
});
Console.WriteLine("Filter 2");
await Select(tableUser.FirstName, tableUser.LastName)
.From(tableUser)
.Where(restoredFilter2)
.Query(database,
(object)null,
(s, r) =>
{
Console.WriteLine($"{tableUser.FirstName.Read(r)} {tableUser.LastName.Read(r)}");
return(s);
});
}
