public void InsertWithExtra()
{
var db = new OrmTestSession();
db.CreateTable <TestObj2>();
var obj1 = new TestObj2 {
Id = 1, Text = "GLaDOS loves testing!"
};
var obj2 = new TestObj2 {
Id = 1, Text = "Keep testing, just keep testing"
};
var obj3 = new TestObj2 {
Id = 1, Text = "Done testing"
};
db.Insert(obj1);
// "Expected unique constraint violation"
ExceptionAssert.Throws <SqliteException>(() => db.Insert(obj2));
db.Insert(obj2, ConflictResolution.Replace);
// "Expected unique constraint violation"
ExceptionAssert.Throws <SqliteException>(() => db.Insert(obj3));
db.Insert(obj3, ConflictResolution.Ignore);
List <TestObj> result = db.Query <TestObj>("select * from TestObj2").ToList();
Assert.AreEqual(1, result.Count);
Assert.AreEqual(obj2.Text, result.First().Text);
db.Close();
}
public void InsertTwoTimes()
{
var db = new OrmTestSession();
db.CreateTable <TestObj>();
var obj1 = new TestObj {
Text = "GLaDOS loves testing!"
};
var obj2 = new TestObj {
Text = "Keep testing, just keep testing"
};
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);
db.Close();
}
public void InsertUsingNestedSystemTransactions()
{
var options = new TransactionOptions {
IsolationLevel = IsolationLevel.ReadCommitted
};
using (var db = new OrmTestSession())
{
using (var trans = new TransactionScope(TransactionScopeOption.Required, options))
{
db.CreateTable <TestObj>();
db.Insert(new TestObj {
Text = "My Text"
});
using (var trans2 = new TransactionScope(TransactionScopeOption.Required, options))
{
db.Insert(new TestObj {
Text = "My Text"
});
trans2.Complete();
}
trans.Complete();
}
Assert.AreEqual(2, db.Table <TestObj>().Count());
}
}
public void StringQueryOnQueryableTable()
{
using (var db = new OrmTestSession())
{
db.CreateTable <QueryableTable>();
db.Insert(new QueryableTable {
Name = "sqlite"
});
db.Insert(new QueryableTable {
Name = "sqlevy"
});
db.Insert(new QueryableTable {
Name = "cars"
});
db.Insert(new QueryableTable {
Name = "we know sqlite is cool"
});
db.Insert(new QueryableTable {
Name = "we think sqlites"
});
var count = db.ExecuteScalar <int>(
"SELECT COUNT(*) FROM [QueryableTable] WHERE [Name] MATCH ?", "sqlite");
Assert.AreEqual(3, count);
}
}
public void InsertIntoTwoTables()
{
var db = new OrmTestSession();
db.CreateTable <TestObj>();
db.CreateTable <TestObj2>();
var obj1 = new TestObj {
Text = "GLaDOS loves testing!"
};
var obj2 = new TestObj2 {
Text = "Keep testing, just keep testing"
};
int numIn1 = db.Insert(obj1);
Assert.AreEqual(1, numIn1);
int numIn2 = db.Insert(obj2);
Assert.AreEqual(1, numIn2);
List <TestObj> result1 = db.Table <TestObj>().ToList();
Assert.AreEqual(numIn1, result1.Count);
Assert.AreEqual(obj1.Text, result1.First().Text);
List <TestObj> result2 = db.Query <TestObj>("select * from TestObj2").ToList();
Assert.AreEqual(numIn2, result2.Count);
db.Close();
}
public void TableQueryOnQueryableTable()
{
using (var db = new OrmTestSession())
{
db.CreateTable <QueryableTable>();
db.Insert(new QueryableTable {
Name = "sqlite"
});
db.Insert(new QueryableTable {
Name = "sqlevy"
});
db.Insert(new QueryableTable {
Name = "cars"
});
db.Insert(new QueryableTable {
Name = "we know sqlite is cool"
});
db.Insert(new QueryableTable {
Name = "we think sqlites"
});
var count = db.Table <QueryableTable>().Where(x => x.Name.Matches("sqlite")).Count();
Assert.AreEqual(3, count);
}
}
public void BitwiseAndTest()
{
using (var db = new OrmTestSession())
{
db.CreateTable <BitwiseTable>();
db.Insert(new BitwiseTable {
Flags = 0
});
db.Insert(new BitwiseTable {
Flags = 1
});
db.Insert(new BitwiseTable {
Flags = 2
});
db.Insert(new BitwiseTable {
Flags = 2 | 1
});
var bit = db.Table <BitwiseTable>().Where(x => (x.Flags & 2) != 0).Select(x => x.Flags).ToArray();
ArrayAssert.AreEqual(new[] { 2, 3 }, bit);
bit = db.Table <BitwiseTable>().Where(x => (x.Flags & 1) != 0).Select(x => x.Flags).ToArray();
ArrayAssert.AreEqual(new[] { 1, 3 }, bit);
bit = db.Table <BitwiseTable>().Where(x => (x.Flags & 4) != 0).Select(x => x.Flags).ToArray();
ArrayAssert.AreEqual(new int[] { }, bit);
}
}
public void OrderByCast()
{
var db = new OrmTestSession();
db.CreateTable <CoolTable>();
db.Insert(new CoolTable {
Name = "A", Price = 20
});
db.Insert(new CoolTable {
Name = "B", Price = 100
});
var nocast = (from p in db.Table <CoolTable>()
orderby p.Price descending
select p).ToList();
Assert.AreEqual(2, nocast.Count);
Assert.AreEqual("B", nocast[0].Name);
var cast = (from p in db.Table <CoolTable>()
orderby(int) p.Price descending
select p).ToList();
Assert.AreEqual(2, cast.Count);
Assert.AreEqual("B", cast[0].Name);
}
public void CreateThem()
{
var db = new OrmTestSession();
db.CreateTable <Product>();
db.CreateTable <OrderLine>();
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.OrderLines;
Assert.AreEqual(lines.Length, 2, "Has 2 order lines");
Assert.AreEqual(foo.Connection, db, "foo.Connection was set");
Assert.AreEqual(lines[0].Connection, db, "lines[0].Connection was set");
}
public void ClearTableTest()
{
// setup
var db = new OrmTestSession();
db.CreateTable <Product>();
// insert
db.Insert(new Product {
Name = "Hello", Price = 16,
});
db.Insert(new Product {
Name = "Hello", Price = 16,
});
// confirm
Assert.AreEqual(2, db.Table <Product>().Count());
db.Get <Product>(1);
// clear
Assert.AreEqual(2, db.ClearTable <Product>());
// confirm
Assert.AreEqual(0, db.Table <Product>().Count());
// insert
db.Insert(new Product {
Name = "Hello", Price = 16,
});
// confirm that the Ids have not reset
Assert.AreEqual(1, db.Table <Product>().Count());
db.Get <Product>(3);
}
public void WhereNull()
{
var db = new OrmTestSession();
db.CreateTable <NullableIntClass>();
var withNull = new NullableIntClass {
NullableInt = null
};
var with0 = new NullableIntClass {
NullableInt = 0
};
var with1 = new NullableIntClass {
NullableInt = 1
};
var withMinus1 = new NullableIntClass {
NullableInt = -1
};
db.Insert(withNull);
db.Insert(with0);
db.Insert(with1);
db.Insert(withMinus1);
NullableIntClass[] results = db.Table <NullableIntClass>().Where(x => x.NullableInt == null).OrderBy(x => x.Id).ToArray();
Assert.AreEqual(1, results.Length);
Assert.AreEqual(withNull, results[0]);
}
public void StringWhereNotNull()
{
var db = new OrmTestSession();
db.CreateTable <StringClass>();
var withNull = new StringClass {
StringData = null
};
var withEmpty = new StringClass {
StringData = ""
};
var withData = new StringClass {
StringData = "data"
};
db.Insert(withNull);
db.Insert(withEmpty);
db.Insert(withData);
StringClass[] results = db.Table <StringClass>().Where(x => x.StringData != null).OrderBy(x => x.Id).ToArray();
Assert.AreEqual(2, results.Length);
Assert.AreEqual(withEmpty, results[0]);
Assert.AreEqual(withData, results[1]);
}
public void TestElementAtOrDefault()
{
OrmTestSession 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());
var correct = db.Table <Product>().ElementAtOrDefault(1);
Assert.AreEqual("B", correct.Name);
var incorrect = db.Table <Product>().ElementAtOrDefault(2);
Assert.IsNull(incorrect);
}
// [Description("Create a table with a nullable int column then insert and select against it")]
public void NullableFloat()
{
var db = new OrmTestSession();
db.CreateTable <NullableFloatClass>();
var withNull = new NullableFloatClass {
NullableFloat = null
};
var with0 = new NullableFloatClass {
NullableFloat = 0
};
var with1 = new NullableFloatClass {
NullableFloat = 1
};
var withMinus1 = new NullableFloatClass {
NullableFloat = -1
};
db.Insert(withNull);
db.Insert(with0);
db.Insert(with1);
db.Insert(withMinus1);
NullableFloatClass[] results = db.Table <NullableFloatClass>().OrderBy(x => x.Id).ToArray();
Assert.AreEqual(4, results.Length);
Assert.AreEqual(withNull, results[0]);
Assert.AreEqual(with0, results[1]);
Assert.AreEqual(with1, results[2]);
Assert.AreEqual(withMinus1, results[3]);
}
public void WhereGreaterThan()
{
OrmTestSession 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 FunctionParameter()
{
OrmTestSession db = CreateDb();
db.Insert(new Product
{
Name = "A",
Price = 20,
});
db.Insert(new Product
{
Name = "B",
Price = 10,
});
Func <decimal, List <Product> > getProductsWithPriceAtLeast = val => (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 InsertUsingInterweavingSavePoints()
{
var obj = new TestObj {
Text = "Matthew"
};
using (var db = new OrmTestSession())
{
db.CreateTable <TestObj>();
using (var trans = db.BeginTransaction())
{
db.Insert(obj);
Assert.AreEqual(db.Table <TestObj>().Count(), 1);
trans.CreateSavepoint("First");
db.Insert(obj);
Assert.AreEqual(db.Table <TestObj>().Count(), 2);
trans.CreateSavepoint("Second");
db.Insert(obj);
Assert.AreEqual(db.Table <TestObj>().Count(), 3);
trans.RollbackSavepoint("First");
trans.Commit();
}
Assert.AreEqual(db.Table <TestObj>().Count(), 1);
}
}
public void ClearTableTest()
{
// setup
var db = new OrmTestSession();
db.CreateTable<Product>();
// insert
db.Insert(new Product { Name = "Hello", Price = 16, });
db.Insert(new Product { Name = "Hello", Price = 16, });
// confirm
Assert.AreEqual(2, db.Table<Product>().Count());
db.Get<Product>(1);
// clear
Assert.AreEqual(2, db.ClearTable<Product>());
// confirm
Assert.AreEqual(0, db.Table<Product>().Count());
// insert
db.Insert(new Product { Name = "Hello", Price = 16, });
// confirm that the Ids have not reset
Assert.AreEqual(1, db.Table<Product>().Count());
db.Get<Product>(3);
}
public void CreateIndexedTable()
{
var db = new OrmTestSession();
db.CreateTable <IndexedTable>();
db.Insert(new IndexedTable {
Indexed = 1
});
ExceptionAssert.Throws <SqliteException>(
() => db.Insert(new IndexedTable {
Indexed = 1
}));
}
public void TestBoolean()
{
var db = new OrmTestSession();
db.CreateTable<Vo>();
for (int i = 0; i < 10; i++)
{
db.Insert(new Vo {Flag = (i%3 == 0), Text = String.Format("VO{0}", i)});
}
// count vo which flag is true
Assert.AreEqual(4, CountWithFlag(db, true));
Assert.AreEqual(6, CountWithFlag(db, false));
Debug.WriteLine("VO with true flag:");
foreach (Vo vo in db.Query<Vo>("SELECT * FROM VO Where Flag = ?", true))
{
Debug.WriteLine(vo.ToString());
}
Debug.WriteLine("VO with false flag:");
foreach (Vo vo in db.Query<Vo>("SELECT * FROM VO Where Flag = ?", false))
{
Debug.WriteLine(vo.ToString());
}
}
public void NestedTransactionsTest()
{
var obj = new TestObj {
Text = "Matthew"
};
using (var db = new OrmTestSession())
{
db.CreateTable <TestObj>();
using (var trans1 = db.BeginTransaction())
{
trans1.CreateSavepoint("First");
db.Insert(obj);
using (var trans2 = db.BeginTransaction())
{
// rollback on the second trans affects the first as
// it is the same transaction
trans2.RollbackSavepoint("First");
}
Assert.AreEqual(db.Table <TestObj>().Count(), 0);
}
}
}
public void InsertObjectWithValueInPrimaryKey()
{
var db = new OrmTestSession();
db.CreateTable <TestObj2>();
var obj2 = new TestObj2 {
Id = 5, Text = "Old"
};
int numIn1 = db.Insert(obj2);
Assert.AreEqual(1, numIn1);
var result = db.Table <TestObj2>().Single(i => i.Id == 5);
Assert.AreEqual("Old", result.Text);
result.Text = "New";
var upd = db.Update(result);
result = db.Table <TestObj2>().Single(i => i.Id == 5);
Assert.AreEqual("New", result.Text);
db.Close();
}
public void CreateInsertDrop()
{
var db = new OrmTestSession();
db.CreateTable<Product>();
db.Insert(new Product
{
Name = "Hello",
Price = 16,
});
int n = db.Table<Product>().Count();
Assert.AreEqual(1, n);
db.DropTable<Product>();
try
{
// Should throw SqliteException
db.Table<Product>().Count();
Assert.Fail("Expeced 'table does not exist' error.");
}
catch (SqliteException)
{
}
catch
{
Assert.Fail();
}
}
public void CreateInsertDrop()
{
var db = new OrmTestSession();
db.CreateTable <Product>();
db.Insert(new Product
{
Name = "Hello",
Price = 16,
});
int n = db.Table <Product>().Count();
Assert.AreEqual(1, n);
db.DropTable <Product>();
try
{
// Should throw SqliteException
db.Table <Product>().Count();
Assert.Fail("Expeced 'table does not exist' error.");
}
catch (SqliteException)
{
}
catch
{
Assert.Fail();
}
}
public void Collate()
{
var obj = new TestObj
{
CollateDefault = "Alpha ",
CollateBinary = "Alpha ",
CollateRTrim = "Alpha ",
CollateNoCase = "Alpha ",
};
var db = new OrmTestSession();
db.CreateTable <TestObj>();
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());
}
// [Description("Create A large byte array and check it can be stored and retrieved correctly")]
public void LargeByteArrayTest()
{
const int byteArraySize = 1024 * 1024;
var bytes = new byte[byteArraySize];
for (int i = 0; i < byteArraySize; i++)
{
bytes[i] = (byte)(i % 256);
}
var byteArray = new ByteArrayClass {
Bytes = bytes
};
var database = new OrmTestSession();
database.CreateTable <ByteArrayClass>();
//Insert the ByteArrayClass
database.Insert(byteArray);
//Get it back out
ByteArrayClass[] fetchedByteArrays = database.Table <ByteArrayClass>().ToArray();
Assert.AreEqual(fetchedByteArrays.Length, 1);
//Check they are the same
byteArray.AssertEquals(fetchedByteArrays[0]);
}
public void Collate()
{
var obj = new TestObj
{
CollateDefault = "Alpha ",
CollateBinary = "Alpha ",
CollateRTrim = "Alpha ",
CollateNoCase = "Alpha ",
};
var db = new OrmTestSession();
db.CreateTable<TestObj>();
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 CanCompareAnyField()
{
var db = new OrmTestSession();
db.CreateTable <TestObjString>();
db.Insert(new TestObjString
{
Data = Convert.ToString(3),
Date = new DateTime(2013, 1, 3)
});
var results = db.Table <TestObjString>().Where(o => o.Data.Equals("3"));
Assert.AreEqual(1, results.Count());
Assert.AreEqual("3", results.FirstOrDefault().Data);
results = db.Table <TestObjString>().Where(o => o.Id.Equals(1));
Assert.AreEqual(1, results.Count());
Assert.AreEqual("3", results.FirstOrDefault().Data);
var date = new DateTime(2013, 1, 3);
results = db.Table <TestObjString>().Where(o => o.Date.Equals(date));
Assert.AreEqual(1, results.Count());
Assert.AreEqual("3", results.FirstOrDefault().Data);
}
public void TestBoolean()
{
var db = new OrmTestSession();
db.CreateTable <Vo>();
for (int i = 0; i < 10; i++)
{
db.Insert(new Vo {
Flag = (i % 3 == 0), Text = String.Format("VO{0}", i)
});
}
// count vo which flag is true
Assert.AreEqual(4, CountWithFlag(db, true));
Assert.AreEqual(6, CountWithFlag(db, false));
Debug.WriteLine("VO with true flag:");
foreach (Vo vo in db.Query <Vo>("SELECT * FROM VO Where Flag = ?", true))
{
Debug.WriteLine(vo.ToString());
}
Debug.WriteLine("VO with false flag:");
foreach (Vo vo in db.Query <Vo>("SELECT * FROM VO Where Flag = ?", false))
{
Debug.WriteLine(vo.ToString());
}
}
public void StringPrimaryKeys()
{
using (var db = new OrmTestSession())
{
db.CreateTable <StringKeyObject>();
// insert 2 items
db.Insert(new StringKeyObject {
Key = "Name", Value = "Matthew"
});
db.Insert(new StringKeyObject {
Key = "Age", Value = "19"
});
// see if they saved
Assert.AreEqual(2, db.Table <StringKeyObject>().Count());
// get the age
var fromDb = db.Table <StringKeyObject>().Where(x => x.Key == "Age").Single();
// make sure they are correct
Assert.AreEqual("Age", fromDb.Key);
Assert.AreEqual("19", fromDb.Value);
// try updating
db.Update(new StringKeyObject {
Key = "Name", Value = "Matthew Leibowitz"
});
// make sure it wasn't an add
Assert.AreEqual(2, db.Table <StringKeyObject>().Count());
// get the name
fromDb = db.Table <StringKeyObject>().Where(x => x.Key == "Name").Single();
// make sure the name has changed
Assert.AreEqual("Matthew Leibowitz", fromDb.Value);
// try a delete
db.Delete(new StringKeyObject {
Key = "Age"
});
// ensure it was really deleted
Assert.AreEqual(1, db.Table <StringKeyObject>().Count());
}
}
public void EnumDataConverterTest()
{
var db = new OrmTestSession();
db.CreateTable <EnumTestTable>();
db.Insert(new EnumTestTable {
EnumColumn = MyEnum.Second
});
db.Insert(new EnumTestTable {
EnumColumn = null
});
var plain = db.Get <EnumTestTablePlain>(1);
Assert.AreEqual("2", plain.EnumColumn);
plain = db.Get <EnumTestTablePlain>(2);
Assert.IsNull(plain.EnumColumn);
var rich = db.Get <EnumTestTable>(1);
Assert.AreEqual(MyEnum.Second, rich.EnumColumn);
rich = db.Get <EnumTestTable>(2);
Assert.IsNull(rich.EnumColumn);
db.Insert(new EnumTestTablePlain {
EnumColumn = "2"
});
db.Insert(new EnumTestTablePlain {
EnumColumn = null
});
plain = db.Get <EnumTestTablePlain>(3);
Assert.AreEqual("2", plain.EnumColumn);
plain = db.Get <EnumTestTablePlain>(4);
Assert.IsNull(plain.EnumColumn);
rich = db.Get <EnumTestTable>(3);
Assert.AreEqual(MyEnum.Second, rich.EnumColumn);
rich = db.Get <EnumTestTable>(4);
Assert.IsNull(rich.EnumColumn);
}
public void RunMergeUntilOptimalTest()
{
using (var db = new OrmTestSession())
{
db.CreateTable<SimpleTable>();
db.Insert(new SimpleTable { Name = RandomString() });
db.RunMergeUntilOptimal<SimpleTable>();
}
}
public void MergeTest()
{
using (var db = new OrmTestSession())
{
db.CreateTable<SimpleTable>();
db.Insert(new SimpleTable { Name = RandomString() });
db.Merge<SimpleTable>();
}
}
public void IntegrityCheckTest()
{
using (var db = new OrmTestSession())
{
db.CreateTable<SimpleTable>();
db.Insert(new SimpleTable { Name = RandomString() });
db.IntegrityCheck<SimpleTable>();
}
}
public void CreateInstanceHookTestNonGeneric()
{
var db = new OrmTestSession();
db.InstanceCreated += InstanceCreated;
db.CreateTable<HookTestTable>();
db.Insert(new HookTestTable { Text = InsertedTest });
var got = db.Get(typeof(HookTestTable), 1);
Assert.AreEqual(ReplacedText, ((HookTestTable)got).Text);
}
public void CreateInstanceHookTest()
{
var db = new OrmTestSession();
db.InstanceCreated += InstanceCreated;
db.CreateTable<HookTestTable>();
db.Insert(new HookTestTable { Text = InsertedTest });
var got = db.Get<HookTestTable>(1);
Assert.AreEqual(ReplacedText, got.Text);
}
public void StringQueryOnQueryableTable()
{
using (var db = new OrmTestSession())
{
db.CreateTable<QueryableTable>();
db.Insert(new QueryableTable { Name = "sqlite" });
db.Insert(new QueryableTable { Name = "sqlevy" });
db.Insert(new QueryableTable { Name = "cars" });
db.Insert(new QueryableTable { Name = "we know sqlite is cool" });
db.Insert(new QueryableTable { Name = "we think sqlites" });
var count = db.ExecuteScalar<int>(
"SELECT COUNT(*) FROM [QueryableTable] WHERE [Name] MATCH ?",
"sqlite");
Assert.AreEqual(3, count);
}
}
public void Insert()
{
var db = new OrmTestSession();
db.CreateTable<Product>();
db.Insert(new Product { Name = TestString, });
var p = db.Get<Product>(1);
Assert.AreEqual(TestString, p.Name);
}
public void IntegrityCheckTest()
{
using (var db = new OrmTestSession())
{
db.CreateTable <SimpleTable>();
db.Insert(new SimpleTable {
Name = RandomString()
});
db.IntegrityCheck <SimpleTable>();
}
}
public void AutoMergeTest()
{
using (var db = new OrmTestSession())
{
db.CreateTable <SimpleTable>();
db.Insert(new SimpleTable {
Name = RandomString()
});
db.AutoMerge <SimpleTable>();
}
}
public void RunMergeUntilOptimalTest()
{
using (var db = new OrmTestSession())
{
db.CreateTable <SimpleTable>();
db.Insert(new SimpleTable {
Name = RandomString()
});
db.RunMergeUntilOptimal <SimpleTable>();
}
}
public void CreateThem()
{
var db = new OrmTestSession();
db.CreateTable<Product>();
db.CreateTable<OrderLine>();
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.OrderLines;
Assert.AreEqual(lines.Length, 2, "Has 2 order lines");
Assert.AreEqual(foo.Connection, db, "foo.Connection was set");
Assert.AreEqual(lines[0].Connection, db, "lines[0].Connection was set");
}
public void Query()
{
var db = new OrmTestSession();
db.CreateTable<Product>();
db.Insert(new Product { Name = TestString, });
var ps = (from p in db.Table<Product>() where p.Name == TestString select p).ToList();
Assert.AreEqual(1, ps.Count);
Assert.AreEqual(TestString, ps[0].Name);
}
public void InterfaceTest()
{
var db = new OrmTestSession();
db.CreateTable<Product>();
var obj1 = new Product { Name = "Some Cool Name" };
int numIn1 = db.Insert(obj1);
Assert.AreEqual(1, numIn1);
db.Close();
}
public void StringContains()
{
var db = new OrmTestSession();
db.CreateTable<TestObj>();
var testObj = new TestObj { Name = "This is a Good name" };
db.Insert(testObj);
var stringContainsTest = (from n in db.Table<TestObj>() where n.Name.Contains("Good") select n).Single();
Assert.AreEqual(testObj.Id, stringContainsTest.Id);
var finder = "good";
stringContainsTest = (from n in db.Table<TestObj>() where n.Name.Contains(finder) select n).Single();
Assert.AreEqual(testObj.Id, stringContainsTest.Id);
}
public void StringStartsWith()
{
var db = new OrmTestSession();
db.CreateTable<TestObj>();
var testObj = new TestObj { Name = "This is a Good name" };
db.Insert(testObj);
var stringContainsTest = (from n in db.Table<TestObj>() where n.Name.StartsWith("This") select n).Single();
Assert.AreEqual(testObj.Id, stringContainsTest.Id);
var finder = "name";
stringContainsTest = (from n in db.Table<TestObj>() where n.Name.StartsWith(finder) select n).SingleOrDefault();
Assert.IsNull(stringContainsTest);
}
public void ColumnsSaveLoadMaxCorrectly()
{
var db = new OrmTestSession();
db.CreateTable<Test>();
var test = new Test
{
Id = 0,
Int32 = Int32.MaxValue,
String = "A unicode string \u2022 <- bullet point",
Byte = Byte.MaxValue,
UInt16 = UInt16.MaxValue,
SByte = SByte.MaxValue,
Int16 = Int16.MaxValue,
Boolean = true,
UInt32 = UInt32.MaxValue,
Int64 = Int64.MaxValue,
Single = Single.MaxValue,
Double = Double.MaxValue,
Decimal = 79228162514264300000000000000M,
Enum1 = EEnum.EnumVal1,
Enum2 = EEnum.EnumVal2,
Timestamp = DateTime.Parse("2012-04-05 15:08:24.723"),
Blob = new byte[] {1, 2, 3, 4, 5, 6, 7, 8, 9, 10},
Guid = Guid.NewGuid()
};
db.Insert(test);
var res = db.Get<Test>(test.Id);
Assert.AreEqual(test.Id, res.Id);
Assert.AreEqual(test.Int32, res.Int32);
Assert.AreEqual(test.String, res.String);
Assert.AreEqual(test.Byte, res.Byte);
Assert.AreEqual(test.UInt16, res.UInt16);
Assert.AreEqual(test.SByte, res.SByte);
Assert.AreEqual(test.Int16, res.Int16);
Assert.AreEqual(test.Boolean, res.Boolean);
Assert.AreEqual(test.UInt32, res.UInt32);
Assert.AreEqual(test.Int64, res.Int64);
Assert.AreEqual(test.Single, res.Single);
Assert.AreEqual(test.Double, res.Double);
Assert.AreEqual(test.Decimal, res.Decimal);
Assert.AreEqual(test.Enum1, res.Enum1);
Assert.AreEqual(test.Enum2, res.Enum2);
Assert.AreEqual(test.Timestamp, res.Timestamp);
ArrayAssert.AreEqual(test.Blob, res.Blob);
Assert.AreEqual(test.Guid, res.Guid);
}
public CreateTest(OrmTestSession db)
{
db.CreateTable<OrderLine>();
var orderLine = db.GetMapping<OrderLine>();
Assert.AreEqual(4, orderLine.Columns.Count, "OrderLine has 4 columns");
var l = new OrderLine { Status = OrderLineStatus.Shipped };
db.Insert(l);
OrderLine lo = db.Table<OrderLine>().First(x => x.Status == OrderLineStatus.Shipped);
Assert.AreEqual(lo.Id, l.Id);
Id = lo.Id;
}
public void CreateInsertDrop()
{
var db = new OrmTestSession();
db.CreateTable<Product>();
db.Insert(new Product { Name = "Hello", Price = 16, });
int n = db.Table<Product>().Count();
Assert.AreEqual(1, n);
db.DropTable<Product>();
ExceptionAssert.Throws<SqliteException>(() => db.Table<Product>().Count());
}
public void SqlQuery()
{
var expected = "абвг";
var db = new OrmTestSession();
db.CreateTable<Product>();
var product = new Product { Name = TestString };
db.Insert(product);
db.Execute("UPDATE Product SET Name = '" + expected + "' WHERE Id = " + product.Id);
var ps = (from p in db.Table<Product>()
where p.Name == expected
select p).ToList();
Assert.AreEqual(1, ps.Count);
Assert.AreEqual(expected, ps[0].Name);
}
public void InsertUsingNestedSystemTransactions()
{
var options = new TransactionOptions { IsolationLevel = IsolationLevel.ReadCommitted };
using (var db = new OrmTestSession())
{
using (var trans = new TransactionScope(TransactionScopeOption.Required, options))
{
db.CreateTable<TestObj>();
db.Insert(new TestObj { Text = "My Text" });
using (var trans2 = new TransactionScope(TransactionScopeOption.Required, options))
{
db.Insert(new TestObj { Text = "My Text" });
trans2.Complete();
}
trans.Complete();
}
Assert.AreEqual(2, db.Table<TestObj>().Count());
}
}
// [Description("Create objects with various byte arrays and check they can be stored and retrieved correctly")]
public void ByteArrays()
{
//Byte Arrays for comparisson
var byteArrays = new[]
{
new ByteArrayClass {Bytes = new byte[] {1, 2, 3, 4, 250, 252, 253, 254, 255}}, //Range check
new ByteArrayClass {Bytes = new byte[] {0}}, //null bytes need to be handled correctly
new ByteArrayClass {Bytes = new byte[] {0, 0}},
new ByteArrayClass {Bytes = new byte[] {0, 1, 0}},
new ByteArrayClass {Bytes = new byte[] {1, 0, 1}},
new ByteArrayClass {Bytes = new byte[] {}}, //Empty byte array should stay empty (and not become null)
new ByteArrayClass {Bytes = null} //Null should be supported
};
var database = new OrmTestSession();
database.CreateTable<ByteArrayClass>();
//Insert all of the ByteArrayClass
foreach (ByteArrayClass b in byteArrays)
database.Insert(b);
//Get them back out
ByteArrayClass[] fetchedByteArrays = database.Table<ByteArrayClass>().OrderBy(x => x.Id).ToArray();
Assert.AreEqual(fetchedByteArrays.Length, byteArrays.Length);
//Check they are the same
for (int i = 0; i < byteArrays.Length; i++)
{
var byteArrayClass = byteArrays[i];
var other = fetchedByteArrays[i];
var actual = byteArrayClass.Bytes;
var expected = other.Bytes;
byteArrayClass.AssertEquals(other);
}
}
// [Description("Create objects with various byte arrays and check they can be stored and retrieved correctly")]
public void ByteArraysSavedCorrectlyTest()
{
//Byte Arrays for comparisson
var byteArrays = new[]
{
new ByteArrayClass { Bytes = new byte[] { 1, 2, 3, 4, 250, 252, 253, 254, 255 } }, // Range check
new ByteArrayClass { Bytes = new byte[] { 0, 0 } },
new ByteArrayClass { Bytes = new byte[] { 0, 1, 0 } },
new ByteArrayClass { Bytes = new byte[] { 1, 0, 1 } },
};
var database = new OrmTestSession();
database.CreateTable<ByteArrayClass>();
//Insert all of the ByteArrayClass
foreach (ByteArrayClass b in byteArrays)
{
database.Insert(b);
}
//Get them back out
ByteArrayClass[] fetchedByteArrays = database.Table<ByteArrayClass>().OrderBy(x => x.Id).ToArray();
Assert.AreEqual(fetchedByteArrays.Length, byteArrays.Length);
//Check they are the same
for (int i = 0; i < byteArrays.Length; i++)
{
var byteArrayClass = byteArrays[i];
var other = fetchedByteArrays[i];
var actual = byteArrayClass.Bytes;
var expected = other.Bytes;
byteArrayClass.AssertEquals(other);
}
}
public void CreateTableWithoutIndexesTest()
{
var db = new OrmTestSession();
// the table
db.CreateTable<IndexedTable>(false);
// the indexes
var map = db.GetMapping<IndexedTable>();
foreach (var index in map.Indexes)
{
db.Execute(index.GetCreateSql(map.TableName));
}
// the test
db.Insert(new IndexedTable { Indexed = 1 });
ExceptionAssert.Throws<SqliteException>(
() => db.Insert(new IndexedTable { Indexed = 1 }));
}
public void CreateEnumTable()
{
using (var db = new OrmTestSession())
{
db.CreateTable<EnumTable>();
var sql = new TableMapping(typeof(EnumTable)).GetCreateSql();
var correct = @"CREATE TABLE [EnumTable] (
[Type] varchar(1) NOT NULL,
[Age] integer NOT NULL,
[Kind] text NOT NULL
);";
Assert.AreEqual(correct, sql);
db.Insert(
new EnumTable
{
Type = EnumTable.PersonType.Child,
Age = EnumTable.PersonAge.ChildAge,
Kind = EnumTable.PersonKind.Junior
});
}
}
public void CreateIndexedTable()
{
var db = new OrmTestSession();
db.CreateTable<IndexedTable>();
db.Insert(new IndexedTable { Indexed = 1 });
ExceptionAssert.Throws<SqliteException>(
() => db.Insert(new IndexedTable { Indexed = 1 }));
}
public void ColumnsSaveLoadZerosCorrectly()
{
var db = new OrmTestSession();
db.CreateTable<Test>();
var test = new Test
{
Id = 0,
Int32 = 0,
String = "A unicode string \u2022 <- bullet point",
Byte = 0,
UInt16 = 0,
SByte = 0,
Int16 = 0,
Boolean = false,
UInt32 = 0,
Int64 = 0,
Single = Single.Epsilon,
Double = Double.Epsilon,
Decimal = Decimal.Zero,
Enum1 = EEnum.EnumVal1,
Enum2 = EEnum.EnumVal2,
Timestamp = DateTime.Parse("0001-01-01 00:00:00.000"),
Blob = new byte[] { 0 },
Guid = Guid.NewGuid()
};
db.Insert(test);
var res = db.Get<Test>(test.Id);
Assert.AreEqual(test.Id, res.Id);
Assert.AreEqual(test.Int32, res.Int32);
Assert.AreEqual(test.String, res.String);
Assert.AreEqual(test.Byte, res.Byte);
Assert.AreEqual(test.UInt16, res.UInt16);
Assert.AreEqual(test.SByte, res.SByte);
Assert.AreEqual(test.Int16, res.Int16);
Assert.AreEqual(test.Boolean, res.Boolean);
Assert.AreEqual(test.UInt32, res.UInt32);
Assert.AreEqual(test.Int64, res.Int64);
Assert.AreEqual(test.Single, res.Single);
Assert.AreEqual(test.Double, res.Double);
Assert.AreEqual(test.Decimal, res.Decimal);
Assert.AreEqual(test.Enum1, res.Enum1);
Assert.AreEqual(test.Enum2, res.Enum2);
Assert.AreEqual(test.Timestamp, res.Timestamp);
ArrayAssert.AreEqual(test.Blob, res.Blob);
Assert.AreEqual(test.Guid, res.Guid);
}
public void ColumnsSaveLoadRandomValuesCorrectly()
{
var db = new OrmTestSession();
db.CreateTable<Test>();
var test = new Test
{
Id = 0,
Int32 = 0x1337beef,
String = "A unicode string \u2022 <- bullet point",
Byte = 0xEA,
UInt16 = 65535,
SByte = -128,
Int16 = -32768,
Boolean = false,
UInt32 = 0xdeadbeef,
Int64 = 0x123456789abcdef,
Single = 6.283185f,
Double = 6.283185307179586476925286766559,
Decimal = (decimal)6.283185307179586476925286766559,
Enum1 = EEnum.EnumVal1,
Enum2 = EEnum.EnumVal2,
Timestamp = DateTime.Parse("2012-04-05 15:08:24.723"),
Blob = new byte[] { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 },
Guid = Guid.NewGuid()
};
db.Insert(test);
var res = db.Get<Test>(test.Id);
Assert.AreEqual(test.Id, res.Id);
Assert.AreEqual(test.Int32, res.Int32);
Assert.AreEqual(test.String, res.String);
Assert.AreEqual(test.Byte, res.Byte);
Assert.AreEqual(test.UInt16, res.UInt16);
Assert.AreEqual(test.SByte, res.SByte);
Assert.AreEqual(test.Int16, res.Int16);
Assert.AreEqual(test.Boolean, res.Boolean);
Assert.AreEqual(test.UInt32, res.UInt32);
Assert.AreEqual(test.Int64, res.Int64);
Assert.AreEqual(test.Single, res.Single);
Assert.AreEqual(test.Double, res.Double);
Assert.AreEqual(test.Decimal, res.Decimal);
Assert.AreEqual(test.Enum1, res.Enum1);
Assert.AreEqual(test.Enum2, res.Enum2);
Assert.AreEqual(test.Timestamp, res.Timestamp);
ArrayAssert.AreEqual(test.Blob, res.Blob);
Assert.AreEqual(test.Guid, res.Guid);
}
public void InsertUsingSavePoints()
{
var obj = new TestObj { Text = "Matthew" };
using (var db = new OrmTestSession())
{
db.CreateTable<TestObj>();
using (var trans = db.BeginTransaction())
{
db.Insert(obj);
Assert.AreEqual(db.Table<TestObj>().Count(), 1);
trans.CreateSavepoint("First");
db.Insert(obj);
Assert.AreEqual(db.Table<TestObj>().Count(), 2);
trans.RollbackSavepoint("First");
trans.Commit();
}
Assert.AreEqual(db.Table<TestObj>().Count(), 1);
}
}
public void MultiplePkOperations()
{
var db = new OrmTestSession();
db.CreateTable<TestObj>();
// insert
const int n = 10 , m= 5;
var objs = new TestObj[n * m];
for (int j = 0; j != n; ++j)
{
for (int i = 0; i != m; ++i)
{
objs[j * m + i] = new TestObj {Id = j, SubId = i, Text = "I am (" + j + "," + i + ")"};
}
}
int numIn = db.InsertAll(objs);
Assert.AreEqual(numIn, n * m, "Num inserted must = num objects");
var obj = db.Get<TestObj>(5, 3);
Assert.AreEqual(5, obj.Id);
Assert.AreEqual(3, obj.SubId);
Assert.AreEqual("I am (5,3)", obj.Text);
try
{
db.Insert(obj);
}
catch (SqliteException ex)
{
Assert.AreEqual(SQLiteErrorCode.Constraint, ex.ErrorCode);
}
// update
obj.Text = "I've been changed";
db.Update(obj);
db.Update<TestObj>("Text", "I've been changed also", 8, 2);
obj = db.Get<TestObj>(5, 3);
Assert.AreEqual("I've been changed", obj.Text);
obj = db.Get<TestObj>(8, 2);
Assert.AreEqual("I've been changed also", obj.Text);
db.UpdateAll<TestObj>("Text", "All changed");
IEnumerable<TestObj> q1 = from o in db.Table<TestObj>() select o;
foreach (TestObj o in q1)
Assert.AreEqual("All changed", o.Text);
TestObj[] q2 = (from o in db.Table<TestObj>() where o.SubId == 3 select o).ToArray();
Assert.AreEqual(10, q2.Length);
for (int i = 0; i != 10; ++i)
{
Assert.AreEqual(i, q2[i].Id);
}
object numCount = db.Table<TestObj>().Count();
Assert.AreEqual(numCount, objs.Length, "Num counted must = num objects");
// delete
obj = db.Get<TestObj>(8, 2);
db.Delete(obj);
try
{
var item = db.Get<TestObj>(8, 2);
Assert.Fail();
}
catch (InvalidOperationException)
{
}
catch (Exception ex)
{
Assert.Fail(ex.Message);
}
db.Execute("delete from TestObj where SubId=2");
numCount = db.ExecuteScalar<int>("select count(*) from TestObj");
Assert.AreEqual(numCount, objs.Length - 10);
foreach (TestObj o in (from o in db.Table<TestObj>() select o))
Assert.AreNotEqual(2, o.SubId);
}
