public void SqlInTest()
{
GXSelectArgs arg = GXSelectArgs.Select <TestClass>(x => x.Guid);
arg.Where.And <TestClass>(q => new int[] { 1, 2, 3 }.Contains(q.Id));
Assert.AreEqual("SELECT `Guid` FROM TestClass WHERE TestClass.`ID` IN (1, 2, 3)", arg.ToString());
}
public void SqlOrderTest()
{
GXSelectArgs arg = GXSelectArgs.Select <TestClass>(q => q.Id);
arg.OrderBy.Add <TestClass>(q => new { q.Id, q.Guid });
Assert.AreEqual("SELECT `ID` FROM TestClass ORDER BY TestClass.`ID`, TestClass.`Guid`", arg.ToString());
}
public void ExcludeSelectTest2()
{
GXSelectArgs args = GXSelectArgs.Select <TestClass>(q => new { q.Guid, q.Text });
args.Columns.Exclude <TestClass>(x => new { x.Text, x.Text2, x.Text3, x.Text4, x.BooleanTest, x.IntTest, x.DoubleTest, x.FloatTest, x.Span, x.Object, x.Status });
Assert.AreEqual("SELECT `Guid` FROM TestClass", args.ToString());
}
public void SqlNotIn2Test()
{
GXSelectArgs arg = GXSelectArgs.Select <TestClass>(x => x.Guid);
arg.Where.And <TestClass>(q => !GXSql.In(q.Id, new int[] { 1, 2, 3 }));
Assert.AreEqual("SELECT `Guid` FROM TestClass WHERE TestClass.`ID` NOT IN (1, 2, 3)", arg.ToString());
}
public ActionResult <AttributeTemplateDeleteResponse> Post(AttributeTemplateDelete request)
{
if (request.Ids != null)
{
foreach (UInt64 id in request.Ids)
{
if (id == 0)
{
return(BadRequest("Attribute template ID is invalid."));
}
}
GXSelectArgs arg = GXSelectArgs.Select <GXAttributeTemplate>(a => a.Id, q => request.Ids.Contains(q.Id));
List <GXAttributeTemplate> list = host.Connection.Select <GXAttributeTemplate>(arg);
if (list.Count != 0)
{
DateTime now = DateTime.Now;
foreach (GXAttributeTemplate it in list)
{
it.Removed = now;
host.Connection.Update(GXUpdateArgs.Update(it, q => q.Removed));
}
host.SetChange(TargetType.AttributeTemplate, DateTime.Now);
}
}
else
{
return(BadRequest("Attribute template ID is invalid."));
}
return(new AttributeTemplateDeleteResponse());
}
public void WhereAndTest()
{
GXSelectArgs arg = GXSelectArgs.Select <TestClass>(x => new { x.Guid });
arg.Where.And <TestClass>(q => q.Id > 1 && q.Id != 2);
Assert.AreEqual("SELECT `Guid` FROM TestClass WHERE (TestClass.`ID` > 1) AND (TestClass.`ID` <> 2)", arg.ToString());
}
public void WhereSimpleTest()
{
GXSelectArgs arg = GXSelectArgs.Select <TestClass>(x => x.Guid);
arg.Where.And <TestClass>(q => q.Id == 1);
Assert.AreEqual("SELECT `Guid` FROM TestClass WHERE TestClass.`ID` = 1", arg.ToString());
}
public ActionResult <DeleteScheduleResponse> Post(DeleteSchedule request)
{
//Remove mapping.
if (request.AttributeIds != null && request.ScheduleIds != null)
{
for (int pos = 0; pos != request.AttributeIds.Length; ++pos)
{
GXDeleteArgs arg = GXDeleteArgs.Delete <GXScheduleToAttribute>(q => request.ScheduleIds[pos] == q.ScheduleId);
arg.Where.And <GXScheduleToAttribute>(q => request.AttributeIds[pos] == q.AttributeId);
host.Connection.Delete(arg);
}
host.SetChange(TargetType.Schedule, DateTime.Now);
}
else if (request.ScheduleIds != null)
{
GXSelectArgs arg = GXSelectArgs.Select <GXSchedule>(a => a.Id, q => request.ScheduleIds.Contains(q.Id));
List <GXSchedule> list = host.Connection.Select <GXSchedule>(arg);
DateTime now = DateTime.Now;
foreach (GXSchedule it in list)
{
it.Removed = now;
host.Connection.Update(GXUpdateArgs.Update(it, q => q.Removed));
}
host.SetChange(TargetType.Schedule, now);
}
return(new DeleteScheduleResponse());
}
public void WhereStringNotIsNullOrEmptyTest()
{
GXSelectArgs arg = GXSelectArgs.Select <TestClass>(x => x.Text);
arg.Where.And <TestClass>(q => !string.IsNullOrEmpty(q.Text));
Assert.AreEqual("SELECT `Text` FROM TestClass WHERE (TestClass.`Text` IS NOT NULL AND TestClass.`Text` <> '')", arg.ToString());
}
public void WhereDateTimeTest()
{
GXSelectArgs arg = GXSelectArgs.Select <TestClass>(x => x.Time);
arg.Where.And <TestClass>(q => q.Time > DateTime.MinValue && q.Time < DateTime.MaxValue);
Assert.AreEqual("SELECT `Time` FROM TestClass WHERE (TestClass.`Time` > '0001-01-01 00:00:00') AND (TestClass.`Time` < '9999-12-31 23:59:59')", arg.ToString());
}
public void WhereByReferenceTest()
{
GXSelectArgs arg = GXSelectArgs.Select <DeviceGroup3>(q => q.Id);
arg.Where.And <DeviceGroup3>(q => q.Id == 1);
Assert.AreEqual("SELECT `Id` AS `DG.Id` FROM DeviceGroup3 DG WHERE DG.`Id` = 1", arg.ToString());
}
public void SelectDistinctTest()
{
GXSelectArgs arg = GXSelectArgs.Select <TestClass>(x => x.Guid);
arg.Distinct = true;
Assert.AreEqual("SELECT DISTINCT `Guid` FROM TestClass", arg.ToString());
}
public void WhereOrTest()
{
GXSelectArgs arg = GXSelectArgs.Select <TestClass>(x => x.Guid);
arg.Where.And <TestClass>(q => q.Id == 1 || q.Id == 2 || q.Id == 3);
Assert.AreEqual("SELECT `Guid` FROM TestClass WHERE ((TestClass.`ID` = 1) OR (TestClass.`ID` = 2)) OR (TestClass.`ID` = 3)", arg.ToString());
}
public void RemoveUserRolesTest()
{
GXUser user = new GXUser();
user.Id = "642c8f77-aeeb-4e86-86db-1a8b1b2fc982";
user.UserName = "******";
List <string> roles = new List <string>();
roles.Add("Admin");
roles.Add("User");
roles.Add("DeviceManager");
roles.Add("SystemErrorManager");
GXSelectArgs sel = GXSelectArgs.Select <GXRole>(q => GXSql.One);
sel.Where.And <GXUserRole>(q => q.UserId == user.Id);
sel.Where.And <GXRole>(q => !roles.Contains(q.Name));
GXDeleteArgs d = GXDeleteArgs.Delete <GXUserRole>(q => q.UserId == user.Id);
d.Where.And <GXRole>(q => GXSql.Exists <GXUserRole, GXRole>(q => q.RoleId, q => q.Id, sel));
/*
* GXSelectArgs sel = GXSelectArgs.Select<GXUserRole>(q => q.UserId, q => q.UserId == "642c8f77-aeeb-4e86-86db-1a8b1b2fc982");
* sel.Joins.AddInnerJoin<GXUserRole, GXRole>(a => a.RoleId, b => b.Id);
* sel.Where.And<GXRole>(q => !roles.Contains(q.Name));
* GXDeleteArgs d = GXDeleteArgs.Delete<GXUserRole>(q => GXSql.Exists(sel));
*/
Assert.AreEqual("", d.ToString());
}
public void AddUserRolesTest()
{
GXUser user = new GXUser();
user.Id = "642c8f77-aeeb-4e86-86db-1a8b1b2fc982";
user.UserName = "******";
List <string> roles = new List <string>();
roles.Add("Admin");
roles.Add("User");
roles.Add("DeviceManager");
roles.Add("SystemErrorManager");
GXSelectArgs sel = GXSelectArgs.Select <GXRole>(q => q.Id);
GXSelectArgs sel2 = GXSelectArgs.Select <GXUserRole>(q => q.UserId, q => q.UserId == "642c8f77-aeeb-4e86-86db-1a8b1b2fc982");
sel.Where.And <GXRole>(q => !GXSql.Exists <GXRole, GXUserRole>(q => q.Id, q => q.RoleId, sel2));
sel.Where.And <GXRole>(q => roles.Contains(q.Name));
GXUserRole ur = new GXUserRole();
ur.UserId = user.Id;
GXInsertArgs i = GXInsertArgs.Insert(ur);
i.Add <GXUserRole>(sel, q => q.RoleId);
Assert.AreEqual("", i.ToString());
}
public void WhereEnumAsIntTest()
{
GXSelectArgs arg = GXSelectArgs.Select <TestClass>(x => x.Guid);
arg.Settings.UseEnumStringValue = false;
arg.Where.And <TestClass>(q => q.Status == State.OK);
Assert.AreEqual("SELECT `Guid` FROM TestClass WHERE TestClass.`Status` = 100", arg.ToString());
}
public void FindEmptyDateTime()
{
GXSelectArgs arg = GXSelectArgs.Select <TestClass>(q => q.Guid, x => x.Time == null);
Assert.AreEqual("SELECT `Guid` FROM TestClass WHERE TestClass.`Time` IS NULL", arg.ToString());
arg = GXSelectArgs.Select <TestClass>(q => q.Guid, x => x.Time.Equals(null));
Assert.AreEqual("SELECT `Guid` FROM TestClass WHERE TestClass.`Time` IS NULL", arg.ToString());
}
public void LimitTest()
{
GXSelectArgs arg = GXSelectArgs.Select <TestClass>(x => x.Guid);
arg.Index = 1;
arg.Count = 2;
Assert.AreEqual("SELECT `Guid` FROM TestClass LIMIT 1,2", arg.ToString());
}
public void WhereDateTimeTest()
{
string format = "yyyy-MM-dd HH:mm:ss";
GXSelectArgs arg = GXSelectArgs.Select <TestClass>(x => x.Time);
arg.Where.And <TestClass>(q => q.Time > DateTime.MinValue && q.Time < DateTime.MaxValue);
Assert.AreEqual("SELECT `Time` FROM TestClass WHERE (TestClass.`Time` > '" + DateTime.MinValue.ToString(format) + "') AND (TestClass.`Time` < '" + DateTime.MaxValue.ToString(format) + "')", arg.ToString());
}
public void WhereStringEmptyTest()
{
GXSelectArgs arg = GXSelectArgs.Select <TestClass>(x => x.Text);
arg.Where.And <TestClass>(q => q.Text != string.Empty);
arg.Where.And <TestClass>(q => q.Text != null);
Assert.AreEqual("SELECT `Text` FROM TestClass WHERE ((TestClass.`Text` <> '') AND (TestClass.`Text` IS NOT NULL))", arg.ToString());
}
public void FilterByTest2()
{
TestClass filter = new TestClass();
GXSelectArgs arg = GXSelectArgs.Select <TestClass>(x => x.Guid);
arg.Where.FilterBy(filter);
Assert.AreEqual("SELECT `Guid` FROM TestClass WHERE TestClass.`Status` = 0", arg.ToString());
}
public void SqlOrderDescTest()
{
GXSelectArgs arg = GXSelectArgs.Select <TestClass>(q => q.Id);
arg.OrderBy.Add <TestClass>(q => q.Id);
arg.Descending = true;
Assert.AreEqual("SELECT `ID` FROM TestClass ORDER BY TestClass.`ID` DESC", arg.ToString());
}
public void WhereOr3Test()
{
GXSelectArgs arg = GXSelectArgs.Select <TestClass>(x => x.Guid);
arg.Where.And <TestClass>(q => q.Id == 1);
arg.Where.Or <TestClass>(x => x.Text.StartsWith("Gurux"));
Assert.AreEqual("SELECT `Guid` FROM TestClass WHERE ((TestClass.`ID` = 1) OR (TestClass.`Text` LIKE('Gurux%')))", arg.ToString());
}
public void GuidInTest()
{
List <Guid> list = new List <Guid>();
list.Add(Guid.Empty);
GXSelectArgs arg = GXSelectArgs.Select <TestClass>(q => q.Guid, q => list.Contains(q.Guid));
Assert.AreEqual("SELECT `Guid` FROM TestClass WHERE TestClass.`Guid` IN ('00000000000000000000000000000000')", arg.ToString());
}
public void DateTimeInTest()
{
List <DateTime> list = new List <DateTime>();
list.Add(DateTime.MinValue);
GXSelectArgs arg = GXSelectArgs.Select <TestClass>(q => q.Guid, q => list.Contains(q.Time));
Assert.AreEqual("SELECT `Guid` FROM TestClass WHERE TestClass.`Time` IN ('1.1.0001 0.00.00')", arg.ToString());
}
public void StringInTest()
{
List <string> list = new List <string>();
list.Add("Gurux");
GXSelectArgs arg = GXSelectArgs.Select <TestClass>(q => q.Guid, q => list.Contains(q.Text));
Assert.AreEqual("SELECT `Guid` FROM TestClass WHERE TestClass.`Text` IN ('Gurux')", arg.ToString());
}
public void WhereClassArrayTest()
{
TestClass[] list = new TestClass[] { new TestClass(), new TestClass() };
list[0].Id = 1;
list[1].Id = 2;
GXSelectArgs arg = GXSelectArgs.Select <TestClass>(x => new { x.Guid });
arg.Where.And <TestClass>(q => list);
Assert.AreEqual("SELECT `Guid` FROM TestClass WHERE `ID` IN(1, 2)", arg.ToString());
}
public void WhereSimple2Test()
{
TestClass t = new TestClass();
t.Id = 1;
GXSelectArgs arg = GXSelectArgs.Select <TestClass>(x => new { x.Guid });
arg.Where.And <TestClass>(q => q.Id == t.Id);
Assert.AreEqual("SELECT `Guid` FROM TestClass WHERE TestClass.`ID` = 1", arg.ToString());
}
public void WhereEqualsTest()
{
TestClass t = new TestClass();
t.Id = 1;
GXSelectArgs arg = GXSelectArgs.Select <TestClass>(x => new { x.Guid });
arg.Where.And <TestClass>(q => q.Text.Equals("Gurux", StringComparison.OrdinalIgnoreCase));
Assert.AreEqual("SELECT `Guid` FROM TestClass WHERE UPPER(TestClass.`Text`) LIKE('GURUX')", arg.ToString());
}
public void WhereContainsTest()
{
TestClass t = new TestClass();
t.Id = 1;
GXSelectArgs arg = GXSelectArgs.Select <TestClass>(x => x.Guid);
arg.Where.And <TestClass>(q => q.Text.Contains("Gurux"));
Assert.AreEqual("SELECT `Guid` FROM TestClass WHERE TestClass.`Text` LIKE('%Gurux%')", arg.ToString());
}
