public void ShouldPersistAndReadGuid()
{
var db = new TestDb(TestPath.CreateTemporaryDatabase());
var obj1 = new TestObj
{
Id = new Guid("36473164-C9E4-4CDF-B266-A0B287C85623"),
Text = "First Guid Object"
};
var obj2 = new TestObj
{
Id = new Guid("BC5C4C4A-CA57-4B61-8B53-9FD4673528B6"),
Text = "Second Guid Object"
};
int numIn1 = db.Insert(obj1);
int numIn2 = db.Insert(obj2);
Assert.AreEqual(1, numIn1);
Assert.AreEqual(1, numIn2);
List <TestObj> result = db.Query <TestObj>("select * from TestObj").ToList();
Assert.AreEqual(2, result.Count);
Assert.AreEqual(obj1.Text, result[0].Text);
Assert.AreEqual(obj2.Text, result[1].Text);
Assert.AreEqual(obj1.Id, result[0].Id);
Assert.AreEqual(obj2.Id, result[1].Id);
db.Close();
}
public void Issue96_NullabelIntsInQueries()
{
TestDb db = CreateDb();
db.CreateTable <Issue96_A>();
int id = 42;
db.Insert(new Issue96_A
{
ClassB = id,
});
db.Insert(new Issue96_A
{
ClassB = null,
});
db.Insert(new Issue96_A
{
ClassB = null,
});
db.Insert(new Issue96_A
{
ClassB = null,
});
Assert.AreEqual(1, db.Table <Issue96_A>().Where(p => p.ClassB == id).Count());
Assert.AreEqual(3, db.Table <Issue96_A>().Where(p => p.ClassB == null).Count());
}
public void OrderByCast()
{
TestDb db = CreateDb();
db.Insert(new Product
{
Name = "A",
TotalSales = 1,
});
db.Insert(new Product
{
Name = "B",
TotalSales = 100,
});
List <Product> nocast = (from p in db.Table <Product>() orderby p.TotalSales descending select p).ToList();
Assert.AreEqual(2, nocast.Count);
Assert.AreEqual("B", nocast[0].Name);
List <Product> cast = (from p in db.Table <Product>() orderby(int) p.TotalSales descending select p).ToList();
Assert.AreEqual(2, cast.Count);
Assert.AreEqual("B", cast[0].Name);
}
public void GetWithExpression()
{
TestDb db = CreateDb();
db.Insert(new Product
{
Name = "A",
Price = 20,
});
db.Insert(new Product
{
Name = "B",
Price = 10,
});
db.Insert(new Product
{
Name = "C",
Price = 5,
});
Assert.AreEqual(3, db.Table <Product>().Count());
var r = db.Get <Product>(x => x.Price == 10);
Assert.IsNotNull(r);
Assert.AreEqual("B", r.Name);
}
public void Issue86()
{
var db = new TestDb();
db.CreateTable <Foo>();
db.Insert(new Foo
{
Bar = 42
});
db.Insert(new Foo
{
Bar = 69
});
Foo found42 = db.Table <Foo>().Where(f => f.Bar == 42).FirstOrDefault();
Assert.IsNotNull(found42);
var ordered = new List <Foo>(db.Table <Foo>().OrderByDescending(f => f.Bar));
Assert.AreEqual(2, ordered.Count);
Assert.AreEqual(69, ordered[0].Bar);
Assert.AreEqual(42, ordered[1].Bar);
}
public void InsertIntoOneColumnAutoIncrementTable()
{
var obj = new OneColumnObj();
_db.Insert(obj);
var result = _db.Get <OneColumnObj>(1);
Assert.AreEqual(1, result.Id);
}
public void Collate()
{
var obj = new TestObj
{
CollateDefault = "Alpha ",
CollateBinary = "Alpha ",
CollateRTrim = "Alpha ",
CollateNoCase = "Alpha ",
};
var db = new TestDb(TestPath.CreateTemporaryDatabase());
db.Insert(obj);
Assert.AreEqual(1, (from o in db.Table <TestObj>() where o.CollateDefault == "Alpha " select o).Count());
Assert.AreEqual(0, (from o in db.Table <TestObj>() where o.CollateDefault == "ALPHA " select o).Count());
Assert.AreEqual(0, (from o in db.Table <TestObj>() where o.CollateDefault == "Alpha" select o).Count());
Assert.AreEqual(0, (from o in db.Table <TestObj>() where o.CollateDefault == "ALPHA" select o).Count());
Assert.AreEqual(1, (from o in db.Table <TestObj>() where o.CollateBinary == "Alpha " select o).Count());
Assert.AreEqual(0, (from o in db.Table <TestObj>() where o.CollateBinary == "ALPHA " select o).Count());
Assert.AreEqual(0, (from o in db.Table <TestObj>() where o.CollateBinary == "Alpha" select o).Count());
Assert.AreEqual(0, (from o in db.Table <TestObj>() where o.CollateBinary == "ALPHA" select o).Count());
Assert.AreEqual(1, (from o in db.Table <TestObj>() where o.CollateRTrim == "Alpha " select o).Count());
Assert.AreEqual(0, (from o in db.Table <TestObj>() where o.CollateRTrim == "ALPHA " select o).Count());
Assert.AreEqual(1, (from o in db.Table <TestObj>() where o.CollateRTrim == "Alpha" select o).Count());
Assert.AreEqual(0, (from o in db.Table <TestObj>() where o.CollateRTrim == "ALPHA" select o).Count());
Assert.AreEqual(1, (from o in db.Table <TestObj>() where o.CollateNoCase == "Alpha " select o).Count());
Assert.AreEqual(1, (from o in db.Table <TestObj>() where o.CollateNoCase == "ALPHA " select o).Count());
Assert.AreEqual(0, (from o in db.Table <TestObj>() where o.CollateNoCase == "Alpha" select o).Count());
Assert.AreEqual(0, (from o in db.Table <TestObj>() where o.CollateNoCase == "ALPHA" select o).Count());
}
public void NotNullConstraintExceptionListsOffendingColumnsOnInsert()
{
using (TestDb db = new TestDb())
{
db.CreateTable <NotNullNoPK>();
try
{
NotNullNoPK obj = new NotNullNoPK()
{
RequiredStringProp = "Some value"
};
db.Insert(obj);
}
catch (NotNullConstraintViolationException ex)
{
string expected = "AnotherRequiredStringProp, RequiredIntProp";
string actual = string.Join(", ", ex.Columns.Where(c => !c.IsPK).OrderBy(p => p.PropertyName).Select(c => c.PropertyName));
Assert.AreEqual(expected, actual, "NotNullConstraintViolationException did not correctly list the columns that violated the constraint");
return;
}
catch (SQLiteException ex)
{
if (SqliteApi.Instance.LibVersionNumber() < 3007017 && ex.Result == Result.Constraint)
{
Assert.Inconclusive("Detailed constraint information is only available in SQLite3 version 3.7.17 and above.");
}
}
Assert.Fail("Expected an exception of type NotNullConstraintViolationException to be thrown. No exception was thrown.");
}
}
public void InsertWithNullsThrowsException()
{
using (TestDb db = new TestDb())
{
db.CreateTable <NotNullNoPK>();
try
{
NotNullNoPK obj = new NotNullNoPK();
db.Insert(obj);
}
catch (NotNullConstraintViolationException)
{
return;
}
catch (SQLiteException ex)
{
if (SqliteApi.Instance.LibVersionNumber() < 3007017 && ex.Result == Result.Constraint)
{
Assert.Inconclusive("Detailed constraint information is only available in SQLite3 version 3.7.17 and above.");
}
}
}
Assert.Fail("Expected an exception of type NotNullConstraintViolationException to be thrown. No exception was thrown.");
}
public void SuccessfulSavepointTransaction()
{
db.RunInTransaction(() =>
{
db.Delete(testObjects[0]);
db.Delete(testObjects[1]);
db.Insert(new TestObj());
});
Assert.AreEqual(testObjects.Count - 1, db.Table <TestObj>().Count());
}
public void CreateThem()
{
var db = new TestDb();
var foo = new Product
{
Name = "Foo",
Price = 10.0m
};
var bar = new Product
{
Name = "Bar",
Price = 0.10m
};
db.Insert(foo);
db.Insert(bar);
db.Insert(new OrderLine
{
ProductId = foo.Id,
Quantity = 6,
UnitPrice = 10.01m
});
db.Insert(new OrderLine
{
ProductId = foo.Id,
Quantity = 3,
UnitPrice = 0.02m
});
db.Insert(new OrderLine
{
ProductId = bar.Id,
Quantity = 9,
UnitPrice = 100.01m
});
OrderLine[] lines = foo.GetOrderLines(db);
Assert.AreEqual(lines.Length, 2, "Has 2 order lines");
}
public void WhereGreaterThan()
{
TestDb db = CreateDb();
db.Insert(new Product
{
Name = "A",
Price = 20,
});
db.Insert(new Product
{
Name = "B",
Price = 10,
});
Assert.AreEqual(2, db.Table <Product>().Count());
List <Product> r = (from p in db.Table <Product>() where p.Price > 15 select p).ToList();
Assert.AreEqual(1, r.Count);
Assert.AreEqual("A", r[0].Name);
}
public void CanUseSubtypeOfTableAttribute()
{
var db = new TestDb();
db.CreateTable <Cat>();
db.Insert(new Cat()
{
Breed = "Siamese"
});
int numCats = db.ExecuteScalar <int>("select count(*) from Cats");
Assert.That(numCats, Is.EqualTo(1), "The resulting num cats should be 1.");
}
public void Issue303_WhereNot_A()
{
using (var db = new TestDb())
{
db.CreateTable <Issue303_A>();
db.Insert(new Issue303_A {
Id = 1, Name = "aa"
});
db.Insert(new Issue303_A {
Id = 2, Name = null
});
db.Insert(new Issue303_A {
Id = 3, Name = "test"
});
db.Insert(new Issue303_A {
Id = 4, Name = null
});
var r = (from p in db.Table <Issue303_A>() where !(p.Name == null) select p).ToList();
Assert.AreEqual(2, r.Count);
Assert.AreEqual(1, r[0].Id);
Assert.AreEqual(3, r[1].Id);
}
}
public void Issue303_WhereNot_B()
{
using (var db = new TestDb())
{
db.CreateTable <Issue303_B>();
db.Insert(new Issue303_B {
Id = 1, Flag = true
});
db.Insert(new Issue303_B {
Id = 2, Flag = false
});
db.Insert(new Issue303_B {
Id = 3, Flag = true
});
db.Insert(new Issue303_B {
Id = 4, Flag = false
});
var r = (from p in db.Table <Issue303_B>() where !p.Flag select p).ToList();
Assert.AreEqual(2, r.Count);
Assert.AreEqual(2, r[0].Id);
Assert.AreEqual(4, r[1].Id);
}
}
public void FunctionParameter()
{
TestDb db = CreateDb();
db.Insert(new Product
{
Name = "A",
Price = 20,
});
db.Insert(new Product
{
Name = "B",
Price = 10,
});
Func <decimal, List <Product> > GetProductsWithPriceAtLeast =
delegate(decimal val) { return((from p in db.Table <Product>() where p.Price > val select p).ToList()); };
List <Product> r = GetProductsWithPriceAtLeast(15);
Assert.AreEqual(1, r.Count);
Assert.AreEqual("A", r[0].Name);
}
public void Insert()
{
var db = new TestDb();
string testString = "\u2329\u221E\u232A";
db.CreateTable <UnicodeProduct>();
db.Insert(new UnicodeProduct
{
Name = testString,
});
var p = db.Get <UnicodeProduct>(1);
Assert.AreEqual(testString, p.Name);
}
public void ToUpper()
{
var db = new TestDb();
db.CreateTable <TestTable>();
var testTable = new TestTable()
{
Name = "test"
};
db.Insert(testTable);
var x = db.Table <TestTable>().Where(t => t.Name.ToUpper() == "TEST");
Assert.AreEqual(1, x.Count());
}
void TestDateTimeOffset(TestDb db)
{
db.CreateTable <TestObj> ();
TestObj o, o2;
//
// Ticks
//
o = new TestObj {
ModifiedTime = new DateTimeOffset(2012, 1, 14, 3, 2, 1, TimeSpan.Zero),
};
db.Insert(o);
o2 = db.Get <TestObj> (o.Id);
Assert.AreEqual(o.ModifiedTime, o2.ModifiedTime);
}
private static void VerifyCreations(TestDb db)
{
var orderLine = db.GetMapping(typeof(OrderLine));
Assert.AreEqual(6, orderLine.Columns.Length);
var l = new OrderLine
{
Status = OrderLineStatus.Shipped
};
db.Insert(l);
var lo = db.Table <OrderLine>().First(x => x.Status == OrderLineStatus.Shipped);
Assert.AreEqual(lo.Id, l.Id);
}
public void NotNullConstraintExceptionListsOffendingColumnsOnUpdate()
{
using (TestDb db = new TestDb())
{
// Skip this test if the Dll doesn't support the extended SQLITE_CONSTRAINT codes
if (SqliteApi.Instance.LibVersionNumber() >= 3007017)
{
db.CreateTable <NotNullNoPK>();
try
{
NotNullNoPK obj = new NotNullNoPK()
{
AnotherRequiredStringProp = "Another required string",
RequiredIntProp = 123,
RequiredStringProp = "Required string"
};
db.Insert(obj);
obj.RequiredStringProp = null;
db.Update(obj);
}
catch (NotNullConstraintViolationException ex)
{
string expected = "RequiredStringProp";
string actual = string.Join(", ", ex.Columns.Where(c => !c.IsPK).OrderBy(p => p.PropertyName).Select(c => c.PropertyName));
Assert.AreEqual(expected, actual, "NotNullConstraintViolationException did not correctly list the columns that violated the constraint");
return;
}
catch (SQLiteException ex)
{
if (SqliteApi.Instance.LibVersionNumber() < 3007017 && ex.Result == Result.Constraint)
{
Assert.Inconclusive("Detailed constraint information is only available in SQLite3 version 3.7.17 and above.");
}
}
catch (Exception ex)
{
Assert.Fail(
"Expected an exception of type NotNullConstraintViolationException to be thrown. An exception of type {0} was thrown instead.",
ex.GetType().Name);
}
Assert.Fail("Expected an exception of type NotNullConstraintViolationException to be thrown. No exception was thrown.");
}
}
}
private void CheckPK(TestDb db)
{
for (int i = 1; i <= 10; i++)
{
var na = new NoAttributes
{
Id = i,
AColumn = i.ToString(),
IndexedId = 0
};
db.Insert(na);
}
var item = db.Get <NoAttributes>(2);
Assert.IsNotNull(item);
Assert.AreEqual(2, item.Id);
}
public void SelectWorks()
{
using (var db = new TestDb(TestPath.CreateTemporaryDatabase()))
{
db.Insert(new TestObj()
{
Order = 5
});
try
{
Assert.That(db.Table <TestObj>().Select(obj => obj.Order * 2).First(), Is.EqualTo(10));
}
catch (NotImplementedException)
{
//Allow Not implemented exceptions as the selection may be too complex.
}
}
}
public void Query()
{
var db = new TestDb();
db.CreateTable <UnicodeProduct>();
string testString = "\u2329\u221E\u232A";
db.Insert(new UnicodeProduct
{
Name = testString,
});
var ps = (from p in db.Table <UnicodeProduct>() where p.Name == testString select p).ToList();
Assert.AreEqual(1, ps.Count);
Assert.AreEqual(testString, ps[0].Name);
}
public void CreateInsertDrop()
{
var db = new TestDb();
db.CreateTable <Product>();
db.Insert(new Product
{
Name = "Hello",
Price = 16,
});
int n = db.Table <Product>().Count();
Assert.AreEqual(1, n);
db.DropTable <Product>();
Assert.Throws <SQLiteException>(() => db.Table <Product>().Count());
}
private void TestDateTime(TestDb db)
{
db.CreateTable <TestObj>();
//
// Ticks
//
var org = new TestObj
{
Time1 = DateTime.UtcNow,
Time2 = DateTime.Now,
};
db.Insert(org);
var fromDb = db.Get <TestObj>(org.Id);
Assert.AreEqual(fromDb.Time1.ToUniversalTime(), org.Time1.ToUniversalTime());
Assert.AreEqual(fromDb.Time2.ToUniversalTime(), org.Time2.ToUniversalTime());
Assert.AreEqual(fromDb.Time1.ToLocalTime(), org.Time1.ToLocalTime());
Assert.AreEqual(fromDb.Time2.ToLocalTime(), org.Time2.ToLocalTime());
}
