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 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 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 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 IPTest()
{
GXUser user = new GXUser();
user.Id = "Gurux";
//GXSelectArgs args = GXSelectArgs.Select<GXIpAddress>(s => s.Id, where => where.User == user && where.IPAddress == 0);
GXSelectArgs args = GXSelectArgs.Select <GXIpAddress>(s => s.Id, where => where.User == user);
Assert.AreEqual("Mikko", args.ToString());
}
public void WhereClassTest()
{
TestClass t = new TestClass();
t.Id = 1;
GXSelectArgs arg = GXSelectArgs.Select <TestClass>(x => x.Guid);
arg.Where.And <TestClass>(q => t);
Assert.AreEqual("SELECT `Guid` FROM TestClass WHERE `ID` = 1", 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 MikkoTest()
{
GXWorkflow workflow = new GXWorkflow();
workflow.Id = Guid.Parse("17f9f275-7dba-44a8-b9c5-5c404283cf7e");
GXSelectArgs arg = GXSelectArgs.SelectAll <GXJob>(q => q.Workflow == workflow && q.Removed == null);
arg.Columns.Add <GXModule>();
arg.Columns.Exclude <GXModule>(e => e.Jobs);
arg.Joins.AddLeftJoin <GXModule, GXJob>(j => j.Id, j => j.Module);
Assert.AreEqual("Mikko", arg.ToString());
}
public void NotExistsTest()
{
GXSelectArgs arg2 = GXSelectArgs.Select <Company>(q => q.Id);
arg2.Where.And <Company>(q => q.Name.Equals("Gurux"));
GXSelectArgs arg = GXSelectArgs.SelectAll <Country>();
arg.Where.And <Country>(q => !GXSql.Exists <Company, Country>(a => a.Country, b => b.Id, arg2));
string expected = "SELECT `ID`, `CountryName` FROM Country WHERE NOT EXISTS (SELECT `Id` FROM Company WHERE UPPER(Company.`Name`) LIKE('GURUX') AND Country.`ID` = Company.`CountryID`)";
string actual = arg.ToString();
Assert.AreEqual(expected, actual);
}
public void MultipleTablesTest()
{
GXUser user = new GXUser()
{
Id = "Gurux"
};
GXUserGroup userGroup = new GXUserGroup();
userGroup.Users.Add(user);
GXSelectArgs arg = GXSelectArgs.Select <GXUserGroup>(s => s.Id, where => where.Removed == null);
arg.Where.FilterBy(userGroup, false);
arg.Joins.AddInnerJoin <GXUserGroup, GXUserGroupUser>(j => j.Id, j => j.UserGroupId);
arg.Joins.AddInnerJoin <GXUserGroupUser, GXUser>(j => j.UserId, j => j.Id);
string[] userIds = new string[] { "Gurux" };
arg.Where.And <GXUser>(where => where.Removed == null && userIds.Contains(where.Id));
Assert.AreEqual("Mikko", arg.ToString());
}
public void SqlNotInTest()
{
int[] list = new int[] { 1, 2, 3 };
GXSelectArgs arg = GXSelectArgs.Select <TestClass>(x => x.Guid);
arg.Where.And <TestClass>(q => !list.Contains(q.Id));
Assert.AreEqual("SELECT `Guid` FROM TestClass WHERE TestClass.`ID` NOT IN (1, 2, 3)", arg.ToString());
}
public void WhereAnd2Test()
{
GXSelectArgs arg = GXSelectArgs.Select <TestClass>(x => x.Guid);
arg.Where.And <TestClass>(q => q.Id > 1);
arg.Where.And <TestClass>(q => q.Id != 2);
Assert.AreEqual("SELECT `Guid` FROM TestClass WHERE ((TestClass.`ID` > 1) AND (TestClass.`ID` <> 2))", 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 WhereOr2Test()
{
GXSelectArgs arg = GXSelectArgs.Select <TestClass>(x => x.Guid);
arg.Where.And <TestClass>(q => q.Id == 1 || q.Id == 2);
arg.Where.Or <TestClass>(q => 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 WhereEquals5Test()
{
TestClass t = new TestClass();
t.Id = 1;
t.Text = "Gurux";
GXSelectArgs arg = GXSelectArgs.Select <TestClass>(x => new { x.Guid });
string mikko = t.Text;
arg.Where.And <TestClass>(q => q.Text.Equals(t.Text) && q.Id == t.Id);
Assert.AreEqual("SELECT `Guid` FROM TestClass WHERE (UPPER(TestClass.`Text`) LIKE('GURUX')) AND (TestClass.`ID` = 1)", arg.ToString());
}
public void WhereEquals3Test()
{
TestClass t = new TestClass();
t.Id = 1;
t.Text = "Gurux";
GXSelectArgs arg = GXSelectArgs.Select <TestClass>(x => new { x.Guid });
arg.Where.And <TestClass>(q => q.Text == t.Text);
Assert.AreEqual("SELECT `Guid` FROM TestClass WHERE TestClass.`Text` = 'Gurux'", 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 SelectSingleColumnTest()
{
GXSelectArgs arg = GXSelectArgs.Select <TestClass>(q => q.Text);
Assert.AreEqual("SELECT `Text` FROM TestClass", arg.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 void SelectTablesTest()
{
GXSelectArgs arg = GXSelectArgs.Select <TestClass2>(q => q.Parent);
arg.Columns.Clear();
arg.Columns.Add <TestClass2>(q => q.Name);
arg.Columns.Add <TestClass>(q => q.Guid);
arg.Joins.AddRightJoin <TestClass2, TestClass>(x => x.Parent, x => x.Id);
Assert.AreEqual("SELECT TestClass2.`Name`, TestClass.`Guid` FROM TestClass2 RIGHT OUTER JOIN TestClass ON TestClass2.`ParentID`=TestClass.`ID`", arg.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 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 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 SqlOrder2Test()
{
GXSelectArgs arg = GXSelectArgs.Select <TestClass>(q => q.Id);
arg.OrderBy.Add <TestClass>(q => q.Id);
arg.OrderBy.Add <TestClass>(q => q.Guid);
Assert.AreEqual("SELECT `ID` FROM TestClass ORDER BY TestClass.`ID`, TestClass.`Guid`", arg.ToString());
}
public void WhereEquals2Test()
{
TestClass t = new TestClass();
t.Id = 1;
t.Text = "Gurux";
GXSelectArgs arg = GXSelectArgs.Select <TestClass>(x => new { x.Guid });
arg.Where.And <TestClass>(q => q.Text.Equals(t.Text, StringComparison.OrdinalIgnoreCase));
Assert.AreEqual("SELECT `Guid` FROM TestClass WHERE UPPER(TestClass.`Text`) LIKE('GURUX')", 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 LeftJoinTest()
{
GXSelectArgs arg = GXSelectArgs.SelectAll <TestClass>();
arg.Columns.Add <TestClass2>();
arg.Joins.AddLeftJoin <TestClass2, TestClass>(x => x.Parent, x => x.Id);
Assert.AreEqual("SELECT TestClass.`ID`, TestClass.`Guid`, TestClass.`Time`, TestClass.`Text`, TestClass.`SimpleText`, TestClass.`Text3`, TestClass.`Text4`, TestClass.`BooleanTest`, TestClass.`IntTest`, TestClass.`DoubleTest`, TestClass.`FloatTest`, TestClass.`Span`, TestClass.`Object`, TestClass.`Status`, TestClass2.`Id`, TestClass2.`ParentID`, TestClass2.`Name` FROM TestClass2 LEFT OUTER JOIN TestClass ON TestClass2.`ParentID`=TestClass.`ID`", 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());
}
public void SelectColumnsTest()
{
GXSelectArgs arg = GXSelectArgs.Select <TestClass>(x => new { x.Guid, x.Text });
Assert.AreEqual("SELECT `Guid`, `Text` FROM TestClass", arg.ToString());
}