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 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 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 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 InsertALot()
{
var db = new OrmTestSession();
SqliteSession.Trace = false;
db.CreateTable <TestObj>();
const int n = 10000;
IEnumerable <TestObj> q = Enumerable.Range(1, n).Select(i => new TestObj {
Text = "I am"
});
TestObj[] objs = q.ToArray();
int numIn = db.InsertAll(objs);
Assert.AreEqual(numIn, n, "Num inserted must = num objects");
TestObj[] inObjs = db.Table <TestObj>().ToArray();
for (int i = 0; i < inObjs.Length; i++)
{
Assert.AreEqual(i + 1, objs[i].Id);
Assert.AreEqual(i + 1, inObjs[i].Id);
Assert.AreEqual("I am", inObjs[i].Text);
}
int numCount = db.Table <TestObj>().Count();
Assert.AreEqual(numCount, n, "Num counted must = num objects");
db.Close();
}
public void InsertALotPlain()
{
var db = new OrmTestSession();
SqliteSession.Trace = false;
db.CreateTable <TestObjPlain>();
const int n = 10000;
IEnumerable <TestObjPlain> q = Enumerable.Range(1, n).Select(i => new TestObjPlain {
Text = "I am"
});
TestObjPlain[] objs = q.ToArray();
int numIn = db.InsertAll(objs);
Assert.AreEqual(numIn, n, "Num inserted must = num objects");
TestObjPlain[] inObjs = db.Table <TestObjPlain>().ToArray();
foreach (TestObjPlain t in inObjs)
{
Assert.AreEqual("I am", t.Text);
}
int numCount = db.Table <TestObjPlain>().Count();
Assert.AreEqual(numCount, n, "Num counted must = num objects");
db.Close();
}
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 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 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 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 ContainsQueriedData()
{
int n = 20;
IEnumerable <Product> cq = from i in Enumerable.Range(1, n)
select new Product
{
Name = i.ToString()
};
OrmTestSession db = CreateDb();
db.InsertAll(cq);
var tensq = new[] { "0", "10", "20" };
List <Product> tens = (from o in db.Table <Product>() where tensq.Contains(o.Name) select o).ToList();
Assert.AreEqual(2, tens.Count);
var moreq = new[] { "0", "x", "99", "10", "20", "234324" };
List <Product> more = (from o in db.Table <Product>() where moreq.Contains(o.Name) select o).ToList();
Assert.AreEqual(2, more.Count);
List <string> moreq2 = moreq.ToList();
List <Product> more2 = (from o in db.Table <Product>() where moreq2.Contains(o.Name) select o).ToList();
Assert.AreEqual(2, more2.Count);
}
public void QueriedStringArrayContainsTableColumnTest()
{
int n = 20;
IEnumerable <TestObj> cq = from i in Enumerable.Range(1, n) select new TestObj {
Name = i.ToString()
};
var db = new OrmTestSession();
// temp stop trace for inputs
SqliteSession.Trace = false;
db.CreateTable <TestObj>();
db.InsertAll(cq);
SqliteSession.Trace = true;
var tensArray = new[] { "0", "10", "20" };
var tensResult = from tens in db.Table <TestObj>()
where tensArray.Contains(tens.Name)
select tens.Name;
List <TestObj> more2 = (from all in db.Table <TestObj>()
where tensResult.Contains(all.Name)
select all).ToList();
Assert.AreEqual(2, more2.Count);
}
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);
}
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 ConstantStringArrayContainsTableColumnTest()
{
var db = new OrmTestSession();
db.CreateTable <TestObj>();
const int n = 20;
IEnumerable <TestObj> cq = from i in Enumerable.Range(1, n)
select new TestObj {
Name = i.ToString(CultureInfo.InvariantCulture)
};
db.InsertAll(cq);
var tensq = new[] { "0", "10", "20" };
List <TestObj> tens = (from o in db.Table <TestObj>()
where tensq.Contains(o.Name)
select o).ToList();
Assert.AreEqual(2, tens.Count);
var moreq = new[] { "0", "x", "99", "10", "20", "234324" };
List <TestObj> more = (from o in db.Table <TestObj>()
where moreq.Contains(o.Name)
select o).ToList();
Assert.AreEqual(2, more.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 FirstOrDefaultTest()
{
OrmTestSession db = CreateDb();
db.Insert(new Product {
Name = "A", Price = 20
});
Assert.AreEqual("A", db.Table <Product>().FirstOrDefault().Name);
db.ClearTable <Product>();
Assert.IsNull(db.Table <Product>().FirstOrDefault());
}
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 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 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();
}
// [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 Skip()
{
var db = new OrmTestSession();
db.CreateTable <TestObj>();
const int n = 100;
IEnumerable <TestObj> cq = from i in Enumerable.Range(1, n) select new TestObj {
Order = i
};
TestObj[] objs = cq.ToArray();
int numIn = db.InsertAll(objs);
Assert.AreEqual(numIn, n, "Num inserted must = num objects");
var q = from o in db.Table <TestObj>() orderby o.Order select o;
var qs1 = q.Skip(1);
List <TestObj> s1 = qs1.ToList();
Assert.AreEqual(n - 1, s1.Count);
Assert.AreEqual(2, s1[0].Order);
var qs5 = q.Skip(5);
List <TestObj> s5 = qs5.ToList();
Assert.AreEqual(n - 5, s5.Count);
Assert.AreEqual(6, s5[0].Order);
}
public MigrateTest(OrmTestSession db)
{
db.CreateTable<OrderDetails>();
var orderLine = db.GetMapping<OrderDetails>();
Assert.AreEqual(6, orderLine.Columns.Count, "OrderDetails (OrderLine) has 6 columns");
OrderDetails l = db.Table<OrderDetails>().First(x => x.ShippingStatus == OrderLineStatus.Shipped);
l.Tax = 12;
db.Update(l);
OrderDetails lo = db.Table<OrderDetails>().First(x => x.Tax == 12);
Assert.AreEqual(lo.Id, l.Id);
Id = lo.Id;
}
public void FirstTest()
{
var db = new OrmTestSession();
db.CreateTable <Product>();
db.Insert(new Product {
Name = "A", Price = 20
});
Assert.AreEqual("A", db.Table <Product>().First().Name);
db.ClearTable <Product>();
ExceptionAssert.Throws <InvalidOperationException>(() => db.Table <Product>().First());
}
public void Skip()
{
var db = new OrmTestSession();
db.CreateTable<TestObj>();
const int n = 100;
IEnumerable<TestObj> cq = from i in Enumerable.Range(1, n)
select new TestObj
{
Order = i
};
TestObj[] objs = cq.ToArray();
int numIn = db.InsertAll(objs);
Assert.AreEqual(numIn, n, "Num inserted must = num objects");
TableQuery<TestObj> q = from o in db.Table<TestObj>()
orderby o.Order
select o;
TableQuery<TestObj> qs1 = q.Skip(1);
List<TestObj> s1 = qs1.ToList();
Assert.AreEqual(n - 1, s1.Count);
Assert.AreEqual(2, s1[0].Order);
TableQuery<TestObj> qs5 = q.Skip(5);
List<TestObj> s5 = qs5.ToList();
Assert.AreEqual(n - 5, s5.Count);
Assert.AreEqual(6, s5[0].Order);
}
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 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 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);
}
}
// [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 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 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 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 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 MigrateTest(OrmTestSession db)
{
db.CreateTable <OrderDetails>();
var orderLine = db.GetMapping <OrderDetails>();
Assert.AreEqual(6, orderLine.Columns.Count, "OrderDetails (OrderLine) has 6 columns");
OrderDetails l = db.Table <OrderDetails>().First(x => x.ShippingStatus == OrderLineStatus.Shipped);
l.Tax = 12;
db.Update(l);
OrderDetails lo = db.Table <OrderDetails>().First(x => x.Tax == 12);
Assert.AreEqual(lo.Id, l.Id);
Id = lo.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);
}
// [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);
}
}
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 QueriedStringArrayContainsTableColumnTest()
{
int n = 20;
IEnumerable<TestObj> cq = from i in Enumerable.Range(1, n) select new TestObj { Name = i.ToString() };
var db = new OrmTestSession();
// temp stop trace for inputs
SqliteSession.Trace = false;
db.CreateTable<TestObj>();
db.InsertAll(cq);
SqliteSession.Trace = true;
var tensArray = new[] { "0", "10", "20" };
var tensResult = from tens in db.Table<TestObj>()
where tensArray.Contains(tens.Name)
select tens.Name;
List<TestObj> more2 = (from all in db.Table<TestObj>()
where tensResult.Contains(all.Name)
select all).ToList();
Assert.AreEqual(2, more2.Count);
}
public void Contains()
{
var db = new OrmTestSession();
db.CreateTable<TestObj>();
const int n = 20;
IEnumerable<TestObj> cq = from i in Enumerable.Range(1, n)
select new TestObj
{
Name = i.ToString(CultureInfo.InvariantCulture)
};
db.InsertAll(cq);
var tensq = new[] {"0", "10", "20"};
List<TestObj> tens = (from o in db.Table<TestObj>() where tensq.Contains(o.Name) select o).ToList();
Assert.AreEqual(2, tens.Count);
var moreq = new[] {"0", "x", "99", "10", "20", "234324"};
List<TestObj> more = (from o in db.Table<TestObj>() where moreq.Contains(o.Name) select o).ToList();
Assert.AreEqual(2, more.Count);
}
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 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 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);
}
// [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 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 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);
}
// [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 EmptyByteArraySavedAndRetrievedCorrectlyTest()
{
//Empty byte array should stay empty (and not become null)
var byteArray = new ByteArrayClass { Bytes = new byte[] { } };
var database = new OrmTestSession();
database.CreateTable<ByteArrayClass>();
database.Insert(byteArray);
//Get them back out
var fetchedByteArray = database.Table<ByteArrayClass>().Single();
//Check they are the same
ArrayAssert.AreEqual(byteArray.Bytes, fetchedByteArray.Bytes);
}
// [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 StringWhereNull()
{
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(1, results.Length);
Assert.AreEqual(withNull, results[0]);
}
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 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 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 CheckInertAndUpdateTwiceWorks()
{
var db = new OrmTestSession();
db.CreateTable<TestObj>();
var first = new TestObj { Text = "First" };
db.Insert(first);
first.Text = "Second";
db.Update(first);
first.Text = "Third";
db.Update(first);
Assert.AreEqual("Third", db.Table<TestObj>().Single().Text);
}
public void InsertALotPlain()
{
var db = new OrmTestSession();
SqliteSession.Trace = false;
db.CreateTable<TestObjPlain>();
const int n = 10000;
IEnumerable<TestObjPlain> q = Enumerable.Range(1, n).Select(i => new TestObjPlain { Text = "I am" });
TestObjPlain[] objs = q.ToArray();
int numIn = db.InsertAll(objs);
Assert.AreEqual(numIn, n, "Num inserted must = num objects");
TestObjPlain[] inObjs = db.Table<TestObjPlain>().ToArray();
foreach (TestObjPlain t in inObjs)
{
Assert.AreEqual("I am", t.Text);
}
int numCount = db.Table<TestObjPlain>().Count();
Assert.AreEqual(numCount, n, "Num counted must = num objects");
db.Close();
}
// [Description("Create a table with a nullable Enum column then insert and select against it")]
public void NullableEnum()
{
var db = new OrmTestSession();
db.CreateTable<NullableEnumClass>();
var withNull = new NullableEnumClass { NullableEnum = null };
var withFirst = new NullableEnumClass { NullableEnum = MyEnum.First };
db.Insert(withNull);
db.Insert(withFirst);
NullableEnumClass[] results = db.Table<NullableEnumClass>().OrderBy(x => x.Id).ToArray();
Assert.AreEqual(2, results.Length);
Assert.AreEqual(withNull, results[0]);
Assert.AreEqual(withFirst, results[1]);
}
public void ZeroByteArraySavedAndRetrievedCorrectlyTest()
{
var byteArray = new ByteArrayClass { Bytes = new byte[] { 0 } };
var database = new OrmTestSession();
database.CreateTable<ByteArrayClass>();
database.Insert(byteArray);
//Get them back out
var fetchedByteArray = database.Table<ByteArrayClass>().Single();
//Check they are the same
ArrayAssert.AreEqual(byteArray.Bytes, fetchedByteArray.Bytes);
}
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 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 InsertAllWithinATransactionSucceeds()
{
var db = new OrmTestSession();
db.CreateTable<TestObj>();
var first = new TestObj { Text = "First" };
var second = new TestObj { Text = "Second" };
var third = new TestObj { Text = "Third" };
using (var trans = db.BeginTransaction())
{
var inObjs = db.InsertAll(new[] { first, second, third }, false);
trans.Commit();
}
Assert.AreEqual(3, db.Table<TestObj>().Count());
}
