Esempio n. 1
0
        public void Iif()
        {
            if (!Dialect.Functions.ContainsKey("iif"))
            {
                Assert.Ignore(Dialect + "doesn't support iif function.");
            }
            using (ISession s = OpenSession())
            {
                s.Save(new MaterialResource("Flash card 512MB", "A001/07", MaterialResource.MaterialState.Available));
                s.Save(new MaterialResource("Flash card 512MB", "A002/07", MaterialResource.MaterialState.Available));
                s.Save(new MaterialResource("Flash card 512MB", "A003/07", MaterialResource.MaterialState.Reserved));
                s.Save(new MaterialResource("Flash card 512MB", "A004/07", MaterialResource.MaterialState.Reserved));
                s.Save(new MaterialResource("Flash card 512MB", "A005/07", MaterialResource.MaterialState.Discarded));
                s.Flush();
            }

            // Statistic
            using (ISession s = OpenSession())
            {
                string hql =
                    @"select mr.Description, 
sum(iif(mr.State= 0,1,0)), 
sum(iif(mr.State= 1,1,0)), 
sum(iif(mr.State= 2,1,0)) 
from MaterialResource mr
group by mr.Description";

                IList lresult = s.CreateQuery(hql).List();
                Assert.AreEqual("Flash card 512MB", ((IList)lresult[0])[0]);
                Assert.AreEqual(2, ((IList)lresult[0])[1]);
                Assert.AreEqual(2, ((IList)lresult[0])[2]);
                Assert.AreEqual(1, ((IList)lresult[0])[3]);

                hql = "from MaterialResource mr where iif(mr.State=2,true,false)=true";
                MaterialResource result = (MaterialResource)s.CreateQuery(hql).UniqueResult();
                Assert.AreEqual("A005/07", result.SerialNumber);
            }
            // clean up
            using (ISession s = OpenSession())
            {
                s.Delete("from MaterialResource");
                s.Flush();
            }
        }
Esempio n. 2
0
        public async Task IifAsync()
        {
            AssumeFunctionSupported("Iif");
            using (ISession s = OpenSession())
            {
                await(s.SaveAsync(new MaterialResource("Flash card 512MB", "A001/07", MaterialResource.MaterialState.Available)));
                await(s.SaveAsync(new MaterialResource("Flash card 512MB", "A002/07", MaterialResource.MaterialState.Available)));
                await(s.SaveAsync(new MaterialResource("Flash card 512MB", "A003/07", MaterialResource.MaterialState.Reserved)));
                await(s.SaveAsync(new MaterialResource("Flash card 512MB", "A004/07", MaterialResource.MaterialState.Reserved)));
                await(s.SaveAsync(new MaterialResource("Flash card 512MB", "A005/07", MaterialResource.MaterialState.Discarded)));
                await(s.FlushAsync());
            }

            // Statistic
            using (ISession s = OpenSession())
            {
                string hql =
                    @"select mr.Description, 
sum(iif(mr.State= 0,1,0)), 
sum(iif(mr.State= 1,1,0)), 
sum(iif(mr.State= 2,1,0)) 
from MaterialResource mr
group by mr.Description";

                IList lresult = await(s.CreateQuery(hql).ListAsync());
                Assert.AreEqual("Flash card 512MB", ((IList)lresult[0])[0]);
                Assert.AreEqual(2, ((IList)lresult[0])[1]);
                Assert.AreEqual(2, ((IList)lresult[0])[2]);
                Assert.AreEqual(1, ((IList)lresult[0])[3]);

                hql = "from MaterialResource mr where iif(mr.State=2,true,false)=true";
                MaterialResource result = (MaterialResource)await(s.CreateQuery(hql).UniqueResultAsync());
                Assert.AreEqual("A005/07", result.SerialNumber);
            }
            // clean up
            using (ISession s = OpenSession())
            {
                await(s.DeleteAsync("from MaterialResource"));
                await(s.FlushAsync());
            }
        }
        public void Chr()
        {
            AssumeFunctionSupported("chr");

            using (var s = OpenSession())
            {
                var m = new MaterialResource("Blah", "000", (MaterialResource.MaterialState) 32);
                s.Save(m);
                s.Flush();
            }
            using (var s = OpenSession())
            {
                var space = s.CreateQuery("select chr(m.State) from MaterialResource m").UniqueResult <char>();
                Assert.That(space, Is.EqualTo(' '));
                var count =
                    s
                    .CreateQuery("select count(*) from MaterialResource m where chr(m.State) = :c")
                    .SetCharacter("c", ' ')
                    .UniqueResult <long>();
                Assert.That(count, Is.EqualTo(1));
            }
        }
        public void Ascii()
        {
            AssumeFunctionSupported("ascii");

            using (var s = OpenSession())
            {
                var m = new MaterialResource(" ", "000", MaterialResource.MaterialState.Available);
                s.Save(m);
                s.Flush();
            }
            using (var s = OpenSession())
            {
                var space = s.CreateQuery("select ascii(m.Description) from MaterialResource m").UniqueResult <int>();
                Assert.That(space, Is.EqualTo(32));
                var count =
                    s
                    .CreateQuery("select count(*) from MaterialResource m where ascii(m.Description) = :c")
                    .SetInt32("c", 32)
                    .UniqueResult <long>();
                Assert.That(count, Is.EqualTo(1));
            }
        }
Esempio n. 5
0
        public async Task RoundAsync()
        {
            AssumeFunctionSupported("round");

            using (var s = OpenSession())
            {
                var a1 = new Animal("a1", 1.87f);
                await(s.SaveAsync(a1));
                var m1 = new MaterialResource("m1", "18", MaterialResource.MaterialState.Available)
                {
                    Cost = 51.76m
                };
                await(s.SaveAsync(m1));
                await(s.FlushAsync());
            }
            using (var s = OpenSession())
            {
                var roundF = await(s.CreateQuery("select round(a.BodyWeight) from Animal a").UniqueResultAsync <float>());
                Assert.That(roundF, Is.EqualTo(2), "Selecting round(double) failed.");
                var countF =
                    await(s
                          .CreateQuery("select count(*) from Animal a where round(a.BodyWeight) = :c")
                          .SetInt32("c", 2)
                          .UniqueResultAsync <long>());
                Assert.That(countF, Is.EqualTo(1), "Filtering round(double) failed.");

                roundF = await(s.CreateQuery("select round(a.BodyWeight, 1) from Animal a").UniqueResultAsync <float>());
                Assert.That(roundF, Is.EqualTo(1.9f).Within(0.01f), "Selecting round(double, 1) failed.");
                countF =
                    await(s
                          .CreateQuery("select count(*) from Animal a where round(a.BodyWeight, 1) between :c1 and :c2")
                          .SetDouble("c1", 1.89)
                          .SetDouble("c2", 1.91)
                          .UniqueResultAsync <long>());
                Assert.That(countF, Is.EqualTo(1), "Filtering round(double, 1) failed.");

                var roundD = await(s.CreateQuery("select round(m.Cost) from MaterialResource m").UniqueResultAsync <decimal?>());
                Assert.That(roundD, Is.EqualTo(52), "Selecting round(decimal) failed.");
                var count =
                    await(s
                          .CreateQuery("select count(*) from MaterialResource m where round(m.Cost) = :c")
                          .SetInt32("c", 52)
                          .UniqueResultAsync <long>());
                Assert.That(count, Is.EqualTo(1), "Filtering round(decimal) failed.");

                roundD = await(s.CreateQuery("select round(m.Cost, 1) from MaterialResource m").UniqueResultAsync <decimal?>());
                Assert.That(roundD, Is.EqualTo(51.8m), "Selecting round(decimal, 1) failed.");

                if (TestDialect.HasBrokenDecimalType)
                {
                    // SQLite fails the equality test due to using double instead, wich requires a tolerance.
                    return;
                }

                count =
                    await(s
                          .CreateQuery("select count(*) from MaterialResource m where round(m.Cost, 1) = :c")
                          .SetDecimal("c", 51.8m)
                          .UniqueResultAsync <long>());
                Assert.That(count, Is.EqualTo(1), "Filtering round(decimal, 1) failed.");
            }
        }