public void MappingIgnoreColumn()
{
var db = new TestDb();
var m = db.GetMapping<TestObj>();
Assert.AreEqual(2, m.Columns.Length);
}
public void Collate()
{
var obj = new TestObj()
{
CollateDefault = "Alpha ",
CollateBinary = "Alpha ",
CollateRTrim = "Alpha ",
CollateNoCase = "Alpha ",
};
var db = new TestDb(TestPath.GetTempFileName());
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 ContainsQueriedData()
{
int n = 20;
var cq =from i in Enumerable.Range(1, n)
select new TestObj() {
Name = i.ToString()
};
var db = new TestDb(Path.GetTempFileName());
db.InsertAll(cq);
db.Trace = true;
var tensq = new string[] { "0", "10", "20" };
var tens = (from o in db.Table<TestObj>() where tensq.Contains(o.Name) select o).ToList();
Assert.AreEqual(2, tens.Count);
var moreq = new string[] { "0", "x", "99", "10", "20", "234324" };
var more = (from o in db.Table<TestObj>() where moreq.Contains(o.Name) select o).ToList();
Assert.AreEqual(2, more.Count);
// https://github.com/praeclarum/sqlite-net/issues/28
var moreq2 = moreq.ToList ();
var more2 = (from o in db.Table<TestObj>() where moreq2.Contains(o.Name) select o).ToList();
Assert.AreEqual(2, more2.Count);
}
public void SetUp()
{
_db = new TestDb();
_db.CreateTable<Product>();
_db.CreateTable<Order>();
_db.CreateTable<OrderLine>();
var p1 = new Product { Name = "One", };
var p2 = new Product { Name = "Two", };
var p3 = new Product { Name = "Three", };
_db.InsertAll(new[] { p1, p2, p3 });
var o1 = new Order { PlacedTime = DateTime.Now, };
var o2 = new Order { PlacedTime = DateTime.Now, };
_db.InsertAll(new[] { o1, o2 });
_db.InsertAll(new[] {
new OrderLine {
OrderId = o1.Id,
ProductId = p1.Id,
Quantity = 1,
},
new OrderLine {
OrderId = o1.Id,
ProductId = p2.Id,
Quantity = 2,
},
new OrderLine {
OrderId = o2.Id,
ProductId = p3.Id,
Quantity = 3,
},
});
}
public void InsertALot()
{
int n = 100000;
var q = from i in Enumerable.Range(1, n)
select new TestObj() {
Text = "I am"
};
var objs = q.ToArray();
var db = new TestDb(Path.GetTempFileName());
db.Trace = false;
var sw = new Stopwatch();
sw.Start();
var numIn = db.InsertAll(objs);
sw.Stop();
Assert.AreEqual(numIn, n, "Num inserted must = num objects");
var inObjs = db.CreateCommand("select * from TestObj").ExecuteQuery<TestObj>().ToArray();
for (var 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);
}
var numCount = db.CreateCommand("select count(*) from TestObj").ExecuteScalar<int>();
Assert.AreEqual(numCount, n, "Num counted must = num objects");
}
public void Setup()
{
testObjects = Enumerable.Range(1, 20).Select(i => new TestObj()).ToList();
db = new TestDb(TestPath.GetTempFileName());
db.InsertAll(testObjects);
}
public void Skip()
{
var n = 100;
var cq = from i in Enumerable.Range(1, n)
select new TestObj() {
Order = i
};
var objs = cq.ToArray();
var db = new TestDb(TestPath.GetTempFileName());
var 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);
var s1 = qs1.ToList();
Assert.AreEqual(n - 1, s1.Count);
Assert.AreEqual(2, s1[0].Order);
var qs5 = q.Skip(5);
var s5 = qs5.ToList();
Assert.AreEqual(n - 5, s5.Count);
Assert.AreEqual(6, s5[0].Order);
}
TestDb CreateDb()
{
var db = new TestDb ();
db.CreateTable<Product> ();
db.CreateTable<Order> ();
db.CreateTable<OrderLine> ();
db.CreateTable<OrderHistory> ();
return db;
}
public void InheritanceWorks()
{
var db = new TestDb ();
var mapping = db.GetMapping<Derived> ();
Assert.AreEqual (3, mapping.Columns.Length);
Assert.AreEqual ("Id", mapping.PK.Name);
}
SQLiteConnection CreateDb ()
{
var db = new TestDb ();
db.CreateTable<TestTable> ();
var items = from i in Enumerable.Range (0, Count)
select new TestTable { Datum = 1000+i };
db.InsertAll (items);
Assert.AreEqual (Count, db.Table<TestTable> ().Count ());
return db;
}
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 UpperAndLowerColumnNames()
{
using (var db = new TestDb (true) { Trace = true } ) {
db.CreateTable<LowerId> ();
db.CreateTable<UpperId> ();
var cols = db.GetTableInfo ("Test").ToList ();
Assert.That (cols.Count, Is.EqualTo (1));
Assert.That (cols[0].Name, Is.EqualTo ("Id"));
}
}
public void CreateThem ()
{
var db = new TestDb ();
db.CreateTable<Product> ();
db.CreateTable<Order> ();
db.CreateTable<OrderLine> ();
db.CreateTable<OrderHistory> ();
VerifyCreations(db);
}
public void UpperAndLowerColumnNames()
{
using (var db = new TestDb (true) { Trace = true } ) {
db.CreateTable<LowerId> ();
db.CreateTable<UpperId> ();
var cols = db.GetTableInfo ("Test").ToList ();
Assert.AreEqual (1, cols.Count);
Assert.AreEqual ("Id", cols[0].Name);
}
}
public void CreateAsPassedInTypes ()
{
var db = new TestDb();
db.CreateTable(typeof(Product));
db.CreateTable(typeof(Order));
db.CreateTable(typeof(OrderLine));
db.CreateTable(typeof(OrderHistory));
VerifyCreations(db);
}
public void CreateUniqueIndexes()
{
using (var db = new TestDb ()) {
db.CreateTable<TheOne> ();
var indexes = db.Query<IndexInfo> ("PRAGMA INDEX_LIST (\"TheOne\")");
Assert.AreEqual (4, indexes.Count, "# of indexes");
CheckIndex (db, indexes, "UX_Uno", true, "Uno");
CheckIndex (db, indexes, "UX_Dos", true, "Dos", "Tres");
CheckIndex (db, indexes, "UX_Uno_bool", true, "Cuatro");
CheckIndex (db, indexes, "UX_Dos_bool", true, "Cinco", "Seis");
}
}
public void HasGoodNames()
{
var db = new TestDb ();
db.CreateTable<AFunnyTableName> ();
var mapping = db.GetMapping<AFunnyTableName> ();
Assert.AreEqual ("AGoodTableName", mapping.TableName);
Assert.AreEqual ("Id", mapping.Columns [0].Name);
Assert.AreEqual ("AGoodColumnName", mapping.Columns [1].Name);
}
public void SetUp ()
{
db = new TestDb ();
db.CreateTable<Product> ();
var prods = new[] {
new Product { Name = "Foo" },
new Product { Name = "Bar" },
new Product { Name = "Foobar" },
};
db.InsertAll (prods);
}
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);
}
static void CheckIndex(TestDb db, List<IndexInfo> indexes, string iname, bool unique, params string [] columns)
{
if (columns == null)
throw new Exception ("Don't!");
var idx = indexes.SingleOrDefault (i => i.name == iname);
Assert.IsNotNull (idx, String.Format ("Index {0} not found", iname));
Assert.AreEqual (idx.unique, unique, String.Format ("Index {0} unique expected {1} but got {2}", iname, unique, idx.unique));
var idx_columns = db.Query<IndexColumns> (String.Format ("PRAGMA INDEX_INFO (\"{0}\")", iname));
Assert.AreEqual (columns.Length, idx_columns.Count, String.Format ("# of columns: expected {0}, got {1}", columns.Length, idx_columns.Count));
foreach (var col in columns) {
Assert.IsNotNull (idx_columns.SingleOrDefault (c => c.name == col), String.Format ("Column {0} not in index {1}", col, idx.name));
}
}
public void InheritanceWorks()
{
var fluentMapping = new TableBuilder<Derived>()
.AsPrimaryKey(p => p.Id)
.Build();
SQLiteConnection.RegsiterMappings(new List<TableMapping>{ fluentMapping });
var db = new TestDb();
db.EnsureRegisteredMappings();
var mapping = db.GetMapping<Derived>();
Assert.AreEqual(3, mapping.Columns.Length);
Assert.AreEqual("Id", mapping.PK.Name);
}
public void InsertSucceeds ()
{
var db = new TestDb ();
db.CreateTable<TestObj> ();
var o = new TestObj {
Text = "Hello",
IgnoredText = "World",
};
db.Insert (o);
Assert.AreEqual (1, o.Id);
}
public void ImplicitPK()
{
var db = new TestDb();
db.CreateTable<NoAttributes>(CreateFlags.ImplicitPK);
var mapping = db.GetMapping<NoAttributes>();
Assert.IsNotNull(mapping.PK);
Assert.AreEqual("Id", mapping.PK.Name);
Assert.IsTrue(mapping.PK.IsPK);
Assert.IsFalse(mapping.PK.IsAutoInc);
CheckPK(db);
}
public void Issue86 ()
{
var db = new TestDb ();
db.CreateTable<Foo> ();
db.Insert (new Foo { Bar = 42 } );
db.Insert (new Foo { Bar = 69 } );
var 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);
}
void TestDateTime(TestDb db)
{
db.CreateTable<TestObj> ();
TestObj o, o2;
//
// Ticks
//
o = new TestObj {
ModifiedTime = new DateTime (2012, 1, 14, 3, 2, 1, 234),
};
db.Insert (o);
o2 = db.Get<TestObj> (o.Id);
Assert.AreEqual (o.ModifiedTime, o2.ModifiedTime);
}
public void Insert()
{
var db = new TestDb ();
db.CreateTable<Product> ();
string testString = "\u2329\u221E\u232A";
db.Insert (new Product {
Name = testString,
});
var p = db.Get<Product> (1);
Assert.AreEqual (testString, p.Name);
}
public void WithoutImplicitMapping ()
{
var db = new TestDb ();
db.CreateTable<NoAttributes>();
var mapping = db.GetMapping<NoAttributes>();
Assert.IsNull (mapping.PK);
var column = mapping.Columns[2];
Assert.AreEqual("IndexedId", column.Name);
Assert.IsFalse(column.Indices.Any());
Assert.Throws(typeof(AssertionException), () => CheckPK(db));
}
public void CreateTableWithNotNullConstraints ()
{
using (var db = new TestDb ()) {
db.CreateTable<NotNullNoPK> ();
var cols = db.GetTableInfo ("NotNullNoPK");
var joined = from expected in GetExpectedColumnInfos (typeof (NotNullNoPK))
join actual in cols on expected.Name equals actual.Name
where actual.notnull != expected.notnull
select actual.Name;
Assert.AreNotEqual (0, cols.Count (), "Failed to get table info");
Assert.IsTrue (joined.Count () == 0, string.Format ("not null constraint was not created for the following properties: {0}"
, string.Join (", ", joined.ToArray ())));
}
}
SQLiteConnection CreateDbWithOne2Many ()
{
var db = new TestDb ()
.SetForeignKeysPermissions(true);
db.CreateTable<TestObjWithOne2Many> ();
db.CreateTable<TestDependentObj> ();
var items = Enumerable.Range(1,parentCount)
.Select(i => new TestObjWithOne2Many {Id = i,
ObjList = Enumerable.Range(1,childCount)
.Select(x => new TestDependentObj{OwnerId = i})
.ToList()}).ToList();
db.InsertAll (items);
Assert.AreEqual (parentCount, db.Table<TestObjWithOne2Many> ().Count ());
Assert.AreEqual (parentCount * childCount , db.Table<TestDependentObj> ().Count ());
return db;
}
public void Issue115_MissingPrimaryKey()
{
using (var conn = new TestDb()) {
conn.CreateTable <Issue115_MyObject> ();
conn.InsertAll(from i in Enumerable.Range(0, 10) select new Issue115_MyObject {
UniqueId = i.ToString(),
OtherValue = (byte)(i * 10),
});
var query = conn.Table <Issue115_MyObject> ();
foreach (var itm in query)
{
itm.OtherValue++;
Assert.AreEqual(1, conn.Update(itm, typeof(Issue115_MyObject)));
}
}
}
public void SetUp()
{
_db = TestDb.GetMemoryDb();
_db.CreateTable <Product> ();
_db.CreateTable <Order> ();
_db.CreateTable <OrderLine> ();
var p1 = new Product {
Name = "One",
};
var p2 = new Product {
Name = "Two",
};
var p3 = new Product {
Name = "Three",
};
_db.InsertAll(new [] { p1, p2, p3 });
var o1 = new Order {
PlacedTime = DateTime.Now,
};
var o2 = new Order {
PlacedTime = DateTime.Now,
};
_db.InsertAll(new [] { o1, o2 });
_db.InsertAll(new [] {
new OrderLine {
OrderId = o1.Id,
ProductId = p1.Id,
Quantity = 1,
},
new OrderLine {
OrderId = o1.Id,
ProductId = p2.Id,
Quantity = 2,
},
new OrderLine {
OrderId = o2.Id,
ProductId = p3.Id,
Quantity = 3,
},
});
}
void TestTimeSpan(TestDb db, TimeSpan duration, string expected)
{
db.CreateTable <TestObj> ();
TestObj o, o2;
o = new TestObj {
Duration = duration,
};
db.Insert(o);
o2 = db.Get <TestObj> (o.Id);
Assert.AreEqual(o.Duration, o2.Duration);
var stored = db.ExecuteScalar <string> ("SELECT Duration FROM TestObj;");
Assert.AreEqual(expected, stored);
}
public void Query()
{
var db = new TestDb();
db.CreateTable <Product> ();
string testString = "\u2329\u221E\u232A";
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 WithoutImplicitMapping()
{
var db = new TestDb();
db.CreateTable <NoAttributes>();
var mapping = db.GetMapping <NoAttributes>();
Assert.IsNull(mapping.PK);
var column = mapping.Columns[2];
Assert.AreEqual("IndexedId", column.Name);
Assert.IsFalse(column.Indices.Any());
Assert.Throws(typeof(AssertionException), () => CheckPK(db));
}
public void WithoutImplicitMapping()
{
var db = new TestDb();
db.CreateTable <NoAttributes>();
var mapping = db.GetMapping <NoAttributes>();
Assert.IsNull(mapping.PrimaryKey);
var column = mapping.Columns[2];
Assert.AreEqual("IndexedId", column.Name);
Assert.IsFalse(column.Indices.Any());
CheckPK(db);
}
public void Issue115_MissingPrimaryKey ()
{
using (var conn = new TestDb ()) {
conn.CreateTable<Issue115_MyObject> ();
conn.InsertAll (from i in Enumerable.Range (0, 10) select new Issue115_MyObject {
UniqueId = i.ToString (),
OtherValue = (byte)(i * 10),
});
var query = conn.Table<Issue115_MyObject> ();
foreach (var itm in query) {
itm.OtherValue++;
Assert.AreEqual (1, conn.Update (itm, typeof(Issue115_MyObject)));
}
}
}
public void OnlyKey ()
{
var db = TestDb.GetMemoryDb();
db.CreateTable<OnlyKeyModel> ();
db.InsertOrReplace (new OnlyKeyModel { MyModelId = "Foo" });
var foo = db.Get<OnlyKeyModel> ("Foo");
Assert.AreEqual (foo.MyModelId, "Foo");
db.Insert (new OnlyKeyModel { MyModelId = "Bar" });
var bar = db.Get<OnlyKeyModel> ("Bar");
Assert.AreEqual (bar.MyModelId, "Bar");
db.Update (new OnlyKeyModel { MyModelId = "Foo" });
var foo2 = db.Get<OnlyKeyModel> ("Foo");
Assert.AreEqual (foo2.MyModelId, "Foo");
}
public void DerivedIgnore()
{
var db = TestDb.GetMemoryDb();
db.CreateTable <DerivedIgnoreClass> ();
var o = new DerivedIgnoreClass {
Ignored = "Hello",
NotIgnored = "World",
};
db.Insert(o);
var oo = db.Table <DerivedIgnoreClass> ().First();
Assert.AreEqual(null, oo.Ignored);
Assert.AreEqual("World", oo.NotIgnored);
}
public void GetDoesntHaveIgnores()
{
var db = TestDb.GetMemoryDb();
db.CreateTable <TestObj> ();
var o = new TestObj {
Text = "Hello",
IgnoredText = "World",
};
db.Insert(o);
var oo = db.Get <TestObj> (o.Id);
Assert.AreEqual("Hello", oo.Text);
Assert.AreEqual(null, oo.IgnoredText);
}
static void CheckIndex(TestDb db, List <IndexInfo> indexes, string iname, bool unique, params string [] columns)
{
if (columns == null)
{
throw new Exception("Don't!");
}
var idx = indexes.SingleOrDefault(i => i.name == iname);
Assert.IsNotNull(idx, String.Format("Index {0} not found", iname));
Assert.AreEqual(idx.unique, unique, String.Format("Index {0} unique expected {1} but got {2}", iname, unique, idx.unique));
var idx_columns = db.Query <IndexColumns> (String.Format("PRAGMA INDEX_INFO (\"{0}\")", iname));
Assert.AreEqual(columns.Length, idx_columns.Count, String.Format("# of columns: expected {0}, got {1}", columns.Length, idx_columns.Count));
foreach (var col in columns)
{
Assert.IsNotNull(idx_columns.SingleOrDefault(c => c.name == col), String.Format("Column {0} not in index {1}", col, idx.name));
}
}
public void BaseIgnores()
{
var db = TestDb.GetMemoryDb();
db.CreateTable <TableClass> ();
var o = new TableClass {
ToIgnore = "Hello",
Name = "World",
};
db.Insert(o);
var oo = db.Table <TableClass> ().First();
Assert.AreEqual(null, oo.ToIgnore);
Assert.AreEqual("World", oo.Name);
}
public void InsertWithExtra()
{
var db = new TestDb(Path.GetTempFileName());
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);
try {
db.Insert(obj2);
Assert.Fail("Expected unique constraint violation");
}
catch (SQLiteException) {
}
db.Insert(obj2, "OR REPLACE");
try {
db.Insert(obj3);
Assert.Fail("Expected unique constraint violation");
}
catch (SQLiteException) {
}
db.Insert(obj3, "OR IGNORE");
var result = db.Query <TestObj>("select * from TestObj2").ToList();
Assert.AreEqual(1, result.Count);
Assert.AreEqual(obj2.Text, result.First().Text);
db.Close();
}
public void ExecuteNonQueryWithNullThrowsException()
{
using (TestDb db = new TestDb()) {
TableMapping map;
db.CreateTable <NotNullNoPK> ();
try {
NotNullNoPK obj = new NotNullNoPK()
{
AnotherRequiredStringProp = "Another required prop",
RequiredIntProp = 123,
RequiredStringProp = "Required string prop"
};
db.Insert(obj);
map = db.GetMapping <NotNullNoPK> ();
map.GetInsertCommand(db, "OR REPLACE").ExecuteNonQuery(new object[] { 1, null, 123, null, null, null });
}
catch (NotNullConstraintViolationException) {
return;
}
catch (SQLiteException ex) {
#if PCLTESTS
if (ex.Result == SQLite3.Result.Constraint)
{
Inconclusive();
return;
}
#else
if (SQLite3.LibVersionNumber() < 3007017 && ex.Result == SQLite3.Result.Constraint)
{
Inconclusive();
return;
}
#endif
}
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.");
}
public void SetUp()
{
db = new TestDb();
db.CreateTable <Product> ();
var prods = new[] {
new Product {
Name = "Foo"
},
new Product {
Name = "Bar"
},
new Product {
Name = "Foobar"
},
};
db.InsertAll(prods);
}
public void CreateInsertDrop()
{
var db = new TestDb();
db.CreateTable <Product> ();
db.Insert(new Product {
Name = "Hello",
Price = 16,
});
var n = db.Table <Product> ().Count();
Assert.AreEqual(1, n);
db.DropTable <Product> ();
ExceptionAssert.Throws <SQLiteException>(() => db.Table <Product> ().Count());
}
public void CreateInsertDrop()
{
var db = new TestDb();
db.CreateTable <Product> ();
db.Insert(new Product {
Name = "Hello",
Price = 16,
});
var n = db.Table <Product> ().Count();
Assert.AreEqual(1, n);
db.DropTable <Product> ();
db.Table <Product> ().Count(); // Should throw
}
void TestDateTime(TestDb db, DateTime dateTime, string expected)
{
db.CreateTable <TestObj> ();
TestObj o, o2;
//
// Ticks
//
o = new TestObj {
ModifiedTime = dateTime,
};
db.Insert(o);
o2 = db.Get <TestObj> (o.Id);
Assert.AreEqual(o.ModifiedTime, o2.ModifiedTime);
var stored = db.ExecuteScalar <string> ("SELECT ModifiedTime FROM TestObj;");
Assert.AreEqual(expected, stored);
}
public void SetUp()
{
db = new TestDb();
db.Trace = true;
db.CreateTable <Product> ();
db.CreateTable <Order> ();
db.InsertAll(from i in Enumerable.Range(0, 22)
select new Product {
Name = "Thing" + i, Price = (decimal)Math.Pow(2, i)
});
changeCount = 0;
db.TableChanged += (sender, e) => {
if (e.Table.TableName == "Product")
{
changeCount++;
}
};
}
public void OverrideIgnore()
{
var db = new TestDb();
db.CreateTable <OverrideClass> ();
var o = new OverrideClass {
Name = "Foo",
Value = "Bar",
Values = new List <string> {
"hello", "world"
},
};
db.Insert(o);
var oo = db.Table <OverrideClass> ().First();
Assert.AreEqual("Foo", oo.Name);
Assert.AreEqual("Bar", oo.Value);
Assert.AreEqual(null, oo.Values);
}
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 (SQLite3.LibVersionNumber() < 3007017 && ex.Result == SQLite3.Result.Constraint)
{
Inconclusive();
return;
}
}
}
Assert.Fail("Expected an exception of type NotNullConstraintViolationException to be thrown. No exception was thrown.");
}
SQLiteConnection CreateDbWithOne2Many()
{
var db = new TestDb()
.SetForeignKeysPermissions(true);
db.CreateTable <TestObjWithOne2Many> ();
db.CreateTable <TestDependentObj> ();
var items = Enumerable.Range(1, parentCount)
.Select(i => new TestObjWithOne2Many {
Id = i,
ObjList = Enumerable.Range(1, childCount)
.Select(x => new TestDependentObj {
OwnerId = i
})
.ToList()
}).ToList();
db.InsertAll(items);
Assert.AreEqual(parentCount, db.Table <TestObjWithOne2Many> ().Count());
Assert.AreEqual(parentCount * childCount, db.Table <TestDependentObj> ().Count());
return(db);
}
public void OverrideNames ()
{
var db = TestDb.GetMemoryDb();
db.CreateTable<OverrideNamesClass> ();
var cols = db.GetTableInfo ("OverrideNamesClass");
Assert.AreEqual (3, cols.Count);
Assert.IsTrue (cols.Exists (x => x.Name == "n"));
Assert.IsTrue (cols.Exists (x => x.Name == "v"));
var o = new OverrideNamesClass {
Name = "Foo",
Value = "Bar",
};
db.Insert (o);
var oo = db.Table<OverrideNamesClass> ().First ();
Assert.AreEqual ("Foo", oo.Name);
Assert.AreEqual ("Bar", oo.Value);
}
public void RedefinedIgnores()
{
var db = TestDb.GetMemoryDb();
db.CreateTable <RedefinedClass> ();
var o = new RedefinedClass {
Name = "Foo",
Value = "Bar",
Values = new List <string> {
"hello", "world"
},
};
db.Insert(o);
var oo = db.Table <RedefinedClass> ().First();
Assert.AreEqual("Foo", oo.Name);
Assert.AreEqual("Bar", oo.Value);
Assert.AreEqual(null, oo.Values);
}
public void NotNullConstraintExceptionListsOffendingColumnsOnUpdate()
{
// Skip this test if the Dll doesn't support the extended SQLITE_CONSTRAINT codes
using (TestDb db = new TestDb()) {
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 (SQLite3.LibVersionNumber() < 3007017 && ex.Result == SQLite3.Result.Constraint)
{
Inconclusive();
return;
}
}
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.");
}
}
public void UpdateWithPropertySet()
{
using (var db = new TestDb()) {
// Arrange
db.CreateTable <MyUpdatable> ();
db.Insert(new MyUpdatable {
Text1 = "Message a"
});
// Act
var entity = db.Table <MyUpdatable> ().First();
entity.Text2 = "updated message";
int updateCount = db.Update(entity, new[] { nameof(MyUpdatable.Text2) });
var finalEntity = db.Table <MyUpdatable> ().First();
// Assert
Assert.AreEqual(1, updateCount);
Assert.NotNull(finalEntity);
Assert.AreEqual("Message a", finalEntity.Text1);
Assert.AreEqual("updated message", finalEntity.Text2);
}
}
public void NullableEnum()
{
var db = TestDb.GetMemoryDb();
db.CreateTable <NullableEnumClass> ();
var withNull = new NullableEnumClass {
NullableIntEnum = null, NullableTextEnum = null
};
var with1 = new NullableEnumClass {
NullableIntEnum = TestIntEnum.One, NullableTextEnum = null
};
var with2 = new NullableEnumClass {
NullableIntEnum = TestIntEnum.Two, NullableTextEnum = null
};
var withNullA = new NullableEnumClass {
NullableIntEnum = null, NullableTextEnum = TestTextEnum.Alpha
};
var with1B = new NullableEnumClass {
NullableIntEnum = TestIntEnum.One, NullableTextEnum = TestTextEnum.Beta
};
db.Insert(withNull);
db.Insert(with1);
db.Insert(with2);
db.Insert(withNullA);
db.Insert(with1B);
var results = db.Table <NullableEnumClass> ().OrderBy(x => x.ID).ToArray();
Assert.AreEqual(5, results.Length);
Assert.AreEqual(withNull, results[0]);
Assert.AreEqual(with1, results[1]);
Assert.AreEqual(with2, results[2]);
Assert.AreEqual(withNullA, results[3]);
Assert.AreEqual(with1B, results[4]);
}
public void InsertALot()
{
int n = 100000;
var q = from i in Enumerable.Range(1, n)
select new TestObj()
{
Text = "I am"
};
var objs = q.ToArray();
var db = new TestDb(Path.GetTempFileName());
db.Trace = false;
var sw = new Stopwatch();
sw.Start();
var numIn = db.InsertAll(objs);
sw.Stop();
Assert.AreEqual(numIn, n, "Num inserted must = num objects");
var inObjs = db.CreateCommand("select * from TestObj").ExecuteQuery <TestObj>().ToArray();
for (var 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);
}
var numCount = db.CreateCommand("select count(*) from TestObj").ExecuteScalar <int>();
Assert.AreEqual(numCount, n, "Num counted must = num objects");
db.Close();
}
public void SetStringKey()
{
string path;
var key = "SecretPassword";
using (var db = new TestDb(key: key)) {
path = db.DatabasePath;
db.CreateTable <TestTable> ();
db.Insert(new TestTable {
Value = "Hello"
});
}
using (var db = new TestDb(path, key: key)) {
path = db.DatabasePath;
var r = db.Table <TestTable> ().First();
Assert.AreEqual("Hello", r.Value);
}
}
public void CreateThem()
{
var db = new TestDb();
db.CreateTable <Product> ();
db.CreateTable <Order> ();
db.CreateTable <OrderLine> ();
db.CreateTable <OrderHistory> ();
var orderLine = db.GetMapping(typeof(OrderLine));
Assert.AreEqual(6, orderLine.Columns.Length, "Order history has 3 columns");
var l = new OrderLine()
{
Status = OrderLineStatus.Shipped
};
db.Insert(l);
var lo = db.Table <OrderLine>().Where(x => x.Status == CreateTableTest.OrderLineStatus.Shipped).FirstOrDefault();
Assert.AreEqual(lo.Id, l.Id);
}
public void Issue86()
{
var db = new TestDb();
db.CreateTable <Foo> ();
db.Insert(new Foo {
Bar = 42
});
db.Insert(new Foo {
Bar = 69
});
var 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);
}
