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();
}
}
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));
}
}
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.");
}
}