private SQLiteConnection CreateDb() { var db = new TestDb(); db.CreateTable <TestTable>(); var items = from i in Enumerable.Range(0, Count) select new TestTable { Datum = 1000 + i, Test = "Hello World" }; db.InsertAll(items); Assert.AreEqual(Count, db.Table <TestTable>().Count()); db.CreateTable <TestTableMultiPK>(); var items2 = from i in Enumerable.Range(0, Count) select new TestTableMultiPK { Id = "t" + i, Id2 = "t" + i, Datum = 1000 + i }; db.InsertAll(items2); Assert.AreEqual(Count, db.Table <TestTableMultiPK>().Count()); return(db); }
public void Issue96_NullabelIntsInQueries() { TestDb db = CreateDb(); db.CreateTable <Issue96_A>(); int id = 42; db.Insert(new Issue96_A { ClassB = id, }); db.Insert(new Issue96_A { ClassB = null, }); db.Insert(new Issue96_A { ClassB = null, }); db.Insert(new Issue96_A { ClassB = null, }); Assert.AreEqual(1, db.Table <Issue96_A>().Where(p => p.ClassB == id).Count()); Assert.AreEqual(3, db.Table <Issue96_A>().Where(p => p.ClassB == null).Count()); }
public void CanCompareAnyField() { int n = 20; IEnumerable <TestObjString> cq = from i in Enumerable.Range(1, n) select new TestObjString { Data = Convert.ToString(i), Date = new DateTime(2013, 1, i) }; var db = new TestDb(TestPath.CreateTemporaryDatabase()); db.InsertAll(cq); TableQuery <TestObjString> results = db.Table <TestObjString>().Where(o => o.Data.Equals("10")); Assert.AreEqual(results.Count(), 1); Assert.AreEqual(results.FirstOrDefault().Data, "10"); results = db.Table <TestObjString>().Where(o => o.Id.Equals(10)); Assert.AreEqual(results.Count(), 1); Assert.AreEqual(results.FirstOrDefault().Data, "10"); var date = new DateTime(2013, 1, 10); results = db.Table <TestObjString>().Where(o => o.Date.Equals(date)); Assert.AreEqual(results.Count(), 1); Assert.AreEqual(results.FirstOrDefault().Data, "10"); }
public void Issue86() { var db = new TestDb(); db.CreateTable <Foo>(); db.Insert(new Foo { Bar = 42 }); db.Insert(new Foo { Bar = 69 }); Foo found42 = db.Table <Foo>().Where(f => f.Bar == 42).FirstOrDefault(); Assert.IsNotNull(found42); var ordered = new List <Foo>(db.Table <Foo>().OrderByDescending(f => f.Bar)); Assert.AreEqual(2, ordered.Count); Assert.AreEqual(69, ordered[0].Bar); Assert.AreEqual(42, ordered[1].Bar); }
public void OrderByCast() { TestDb db = CreateDb(); db.Insert(new Product { Name = "A", TotalSales = 1, }); db.Insert(new Product { Name = "B", TotalSales = 100, }); List <Product> nocast = (from p in db.Table <Product>() orderby p.TotalSales descending select p).ToList(); Assert.AreEqual(2, nocast.Count); Assert.AreEqual("B", nocast[0].Name); List <Product> cast = (from p in db.Table <Product>() orderby(int) p.TotalSales descending select p).ToList(); Assert.AreEqual(2, cast.Count); Assert.AreEqual("B", cast[0].Name); }
public void ContainsConstantData() { int n = 20; IEnumerable <TestObj> cq = from i in Enumerable.Range(1, n) select new TestObj { Name = i.ToString() }; var db = new TestDb(TestPath.CreateTemporaryDatabase()); db.InsertAll(cq); db.TraceListener = DebugTraceListener.Instance; 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 ContainsQueriedData() { int n = 20; IEnumerable <TestObj> cq = from i in Enumerable.Range(1, n) select new TestObj { Name = i.ToString() }; var db = new TestDb(TestPath.CreateTemporaryDatabase()); db.InsertAll(cq); db.TraceListener = DebugTraceListener.Instance; 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); // https://github.com/praeclarum/SQLite.Net/issues/28 List <string> moreq2 = moreq.ToList(); List <TestObj> more2 = (from o in db.Table <TestObj>() where moreq2.Contains(o.Name) select o).ToList(); Assert.AreEqual(2, more2.Count); }
public void GetWithExpression() { TestDb db = CreateDb(); db.Insert(new Product { Name = "A", Price = 20, }); db.Insert(new Product { Name = "B", Price = 10, }); db.Insert(new Product { Name = "C", Price = 5, }); Assert.AreEqual(3, db.Table <Product>().Count()); var r = db.Get <Product>(x => x.Price == 10); Assert.IsNotNull(r); Assert.AreEqual("B", r.Name); }
public void BulkInsertAndSelect() { var db = new TestDb(false, new ContractResolver(t => _container.CanResolve(t), (t, op) => _container.Resolve(t))); db.CreateTable <IProduct>(); db.CreateTable <IOrder>(); db.CreateTable <IOrderLine>(); db.CreateTable <IOrderHistory>(); var rnd = new Random(); var data = new List <IOrderLine>(); for (var i = 0; i < 100; i++) { var l = _container.Resolve <IOrderLine>(); l.OrderId = rnd.Next(1, 100); l.ProductId = rnd.Next(1, 100); l.Quantity = rnd.Next(1, 25); l.Status = OrderLineStatus.Shipped; l.UnitPrice = rnd.Next(0, 100); data.Add(l); } db.InsertAll(data.ToArray()); var results = db.Table <IOrderLine>(); Assert.AreEqual(data.Count, results.Count()); Assert.AreEqual(data.First().UnitPrice, results.First().UnitPrice); Assert.AreEqual(data.Last().UnitPrice, results.Last().UnitPrice); }
public void Skip() { int n = 100; IEnumerable <TestObj> cq = from i in Enumerable.Range(1, n) select new TestObj { Order = i }; TestObj[] objs = cq.ToArray(); var db = new TestDb(TestPath.CreateTemporaryDatabase()); 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 MultipleSkipsWillSkipTheSumOfTheSkips() { int n = 100; IEnumerable <TestObj> cq = from i in Enumerable.Range(1, n) select new TestObj { Order = i }; TestObj[] objs = cq.ToArray(); var db = new TestDb(TestPath.CreateTemporaryDatabase()); 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).Skip(5); List <TestObj> s1 = qs1.ToList(); Assert.AreEqual(n - 6, s1.Count, "Should have skipped 5 + 1 = 6 objects."); Assert.AreEqual(7, s1[0].Order); }
public void InsertAllFailureInsideTransaction() { List <UniqueObj> testObjects = Enumerable.Range(1, 20).Select(i => new UniqueObj { Id = i }).ToList(); testObjects[testObjects.Count - 1].Id = 1; // causes the insert to fail because of duplicate key ExceptionAssert.Throws <SQLiteException>(() => _db.RunInTransaction(() => { _db.InsertAll(testObjects); })); Assert.AreEqual(0, _db.Table <UniqueObj>().Count()); }
public void CreateInsertDrop() { var db = new TestDb(); db.CreateTable <Product>(); db.Insert(new Product { Name = "Hello", Price = 16, }); int n = db.Table <Product>().Count(); Assert.AreEqual(1, n); db.DropTable <Product>(); Assert.Throws <SQLiteException>(() => db.Table <Product>().Count()); }
public void WhereGreaterThan() { TestDb db = CreateDb(); db.Insert(new Product { Name = "A", Price = 20, }); db.Insert(new Product { Name = "B", Price = 10, }); Assert.AreEqual(2, db.Table <Product>().Count()); List <Product> r = (from p in db.Table <Product>() where p.Price > 15 select p).ToList(); Assert.AreEqual(1, r.Count); Assert.AreEqual("A", r[0].Name); }
public void FailNestedSavepointTransaction() { try { db.RunInTransaction(() => { db.Delete(testObjects[0]); db.RunInTransaction(() => { db.Delete(testObjects[1]); throw new TransactionTestException(); }); }); } catch (TransactionTestException) { // ignore } Assert.AreEqual(testObjects.Count, db.Table <TestObj>().Count()); }
private SQLiteConnection CreateDb() { var db = new TestDb(); db.CreateTable <TestTable>(); IEnumerable <TestTable> items = from i in Enumerable.Range(0, Count) select new TestTable { Two = 2 }; db.InsertAll(items); Assert.AreEqual(Count, db.Table <TestTable>().Count()); return(db); }
public void Contains() { List <Product> fs = db.Table <Product>().Where(x => x.Name.Contains("o")).ToList(); Assert.AreEqual(2, fs.Count); List <Product> bs = db.Table <Product>().Where(x => x.Name.Contains("a")).ToList(); Assert.AreEqual(2, bs.Count); }
private static void VerifyCreations(TestDb db) { var orderLine = db.GetMapping(typeof(OrderLine)); Assert.AreEqual(6, orderLine.Columns.Length); var l = new OrderLine { Status = OrderLineStatus.Shipped }; db.Insert(l); var lo = db.Table <OrderLine>().First(x => x.Status == OrderLineStatus.Shipped); Assert.AreEqual(lo.Id, l.Id); }
public void ToUpper() { var db = new TestDb(); db.CreateTable <TestTable>(); var testTable = new TestTable() { Name = "test" }; db.Insert(testTable); var x = db.Table <TestTable>().Where(t => t.Name.ToUpper() == "TEST"); Assert.AreEqual(1, x.Count()); }
public void SelectWorks() { using (var db = new TestDb(TestPath.CreateTemporaryDatabase())) { db.Insert(new TestObj() { Order = 5 }); try { Assert.That(db.Table <TestObj>().Select(obj => obj.Order * 2).First(), Is.EqualTo(10)); } catch (NotImplementedException) { //Allow Not implemented exceptions as the selection may be too complex. } } }
public void Query() { var db = new TestDb(); db.CreateTable <UnicodeProduct>(); string testString = "\u2329\u221E\u232A"; db.Insert(new UnicodeProduct { Name = testString, }); var ps = (from p in db.Table <UnicodeProduct>() where p.Name == testString select p).ToList(); Assert.AreEqual(1, ps.Count); Assert.AreEqual(testString, ps[0].Name); }
public void AddForStringsMeansConcatenate() { int n = 20; IEnumerable <TestObjString> cq = from i in Enumerable.Range(1, n) select new TestObjString() { Data = i.ToString(), }; var db = new TestDb(TestPath.CreateTemporaryDatabase()); db.InsertAll(cq); TableQuery <TestObjString> results = db.Table <TestObjString>().Where(o => o.Data + "1" == "11"); Assert.AreEqual(1, results.Count()); Assert.AreEqual("1", results.OrderBy(o => o.Data).FirstOrDefault().Data); }
public void CanHaveSubtractInWhereClause() { int n = 20; IEnumerable <TestObjInt> cq = from i in Enumerable.Range(1, n) select new TestObjInt() { Data = i, }; var db = new TestDb(TestPath.CreateTemporaryDatabase()); db.InsertAll(cq); TableQuery <TestObjInt> results = db.Table <TestObjInt>().Where(o => o.Data - 10 >= 0); Assert.AreEqual(results.Count(), 11); Assert.AreEqual(results.OrderBy(o => o.Data).FirstOrDefault().Data, 10); }
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), }); TableQuery <Issue115_MyObject> query = conn.Table <Issue115_MyObject>(); foreach (Issue115_MyObject itm in query) { itm.OtherValue++; Assert.AreEqual(1, conn.Update(itm, typeof(Issue115_MyObject))); } } }
public void Issue303_WhereNot_A() { using (var db = new TestDb()) { db.CreateTable <Issue303_A>(); db.Insert(new Issue303_A { Id = 1, Name = "aa" }); db.Insert(new Issue303_A { Id = 2, Name = null }); db.Insert(new Issue303_A { Id = 3, Name = "test" }); db.Insert(new Issue303_A { Id = 4, Name = null }); var r = (from p in db.Table <Issue303_A>() where !(p.Name == null) select p).ToList(); Assert.AreEqual(2, r.Count); Assert.AreEqual(1, r[0].Id); Assert.AreEqual(3, r[1].Id); } }
public void Issue303_WhereNot_B() { using (var db = new TestDb()) { db.CreateTable <Issue303_B>(); db.Insert(new Issue303_B { Id = 1, Flag = true }); db.Insert(new Issue303_B { Id = 2, Flag = false }); db.Insert(new Issue303_B { Id = 3, Flag = true }); db.Insert(new Issue303_B { Id = 4, Flag = false }); var r = (from p in db.Table <Issue303_B>() where !p.Flag select p).ToList(); Assert.AreEqual(2, r.Count); Assert.AreEqual(2, r[0].Id); Assert.AreEqual(4, r[1].Id); } }
public void FunctionParameter() { TestDb db = CreateDb(); db.Insert(new Product { Name = "A", Price = 20, }); db.Insert(new Product { Name = "B", Price = 10, }); Func <decimal, List <Product> > GetProductsWithPriceAtLeast = delegate(decimal val) { return((from p in db.Table <Product>() where p.Price > val select p).ToList()); }; List <Product> r = GetProductsWithPriceAtLeast(15); Assert.AreEqual(1, r.Count); Assert.AreEqual("A", r[0].Name); }
public void ReplaceInWhere() { string testElement = "Element"; string alternateElement = "Alternate"; string replacedElement = "ReplacedElement"; int n = 20; IEnumerable <TestObj> cq = from i in Enumerable.Range(1, n) select new TestObj { Name = (i % 2 == 0) ? testElement : alternateElement }; var db = new TestDb(TestPath.CreateTemporaryDatabase()); db.InsertAll(cq); db.TraceListener = DebugTraceListener.Instance; List <TestObj> result = (from o in db.Table <TestObj>() where o.Name.Replace(testElement, replacedElement) == replacedElement select o).ToList(); Assert.AreEqual(10, result.Count); }
public OrderLine[] GetOrderLines(TestDb db) { return(db.Table <OrderLine>().Where(o => o.ProductId == Id).ToArray()); }
public void Collate() { var obj = new TestObj { CollateDefault = "Alpha ", CollateBinary = "Alpha ", CollateRTrim = "Alpha ", CollateNoCase = "Alpha ", }; var db = new TestDb(TestPath.CreateTemporaryDatabase()); db.Insert(obj); Assert.AreEqual(1, (from o in db.Table <TestObj>() where o.CollateDefault == "Alpha " select o).Count()); Assert.AreEqual(0, (from o in db.Table <TestObj>() where o.CollateDefault == "ALPHA " select o).Count()); Assert.AreEqual(0, (from o in db.Table <TestObj>() where o.CollateDefault == "Alpha" select o).Count()); Assert.AreEqual(0, (from o in db.Table <TestObj>() where o.CollateDefault == "ALPHA" select o).Count()); Assert.AreEqual(1, (from o in db.Table <TestObj>() where o.CollateBinary == "Alpha " select o).Count()); Assert.AreEqual(0, (from o in db.Table <TestObj>() where o.CollateBinary == "ALPHA " select o).Count()); Assert.AreEqual(0, (from o in db.Table <TestObj>() where o.CollateBinary == "Alpha" select o).Count()); Assert.AreEqual(0, (from o in db.Table <TestObj>() where o.CollateBinary == "ALPHA" select o).Count()); Assert.AreEqual(1, (from o in db.Table <TestObj>() where o.CollateRTrim == "Alpha " select o).Count()); Assert.AreEqual(0, (from o in db.Table <TestObj>() where o.CollateRTrim == "ALPHA " select o).Count()); Assert.AreEqual(1, (from o in db.Table <TestObj>() where o.CollateRTrim == "Alpha" select o).Count()); Assert.AreEqual(0, (from o in db.Table <TestObj>() where o.CollateRTrim == "ALPHA" select o).Count()); Assert.AreEqual(1, (from o in db.Table <TestObj>() where o.CollateNoCase == "Alpha " select o).Count()); Assert.AreEqual(1, (from o in db.Table <TestObj>() where o.CollateNoCase == "ALPHA " select o).Count()); Assert.AreEqual(0, (from o in db.Table <TestObj>() where o.CollateNoCase == "Alpha" select o).Count()); Assert.AreEqual(0, (from o in db.Table <TestObj>() where o.CollateNoCase == "ALPHA" select o).Count()); }