public void Setup() { testObjects = Enumerable.Range(1, 20).Select(i => new TestObj()).ToList(); db = new TestDb(TestPath.CreateTemporaryDatabase()); db.InsertAll(testObjects); }
public void AutoGuid_HasGuid() { var db = new SQLiteConnection(TestPath.CreateTemporaryDatabase()); db.CreateTable <TestObj>(CreateFlags.AutoIncPK); var guid1 = new Guid("36473164-C9E4-4CDF-B266-A0B287C85623"); var guid2 = new Guid("BC5C4C4A-CA57-4B61-8B53-9FD4673528B6"); var obj1 = new TestObj { Id = guid1, Text = "First Guid Object" }; var obj2 = new TestObj { Id = guid2, Text = "Second Guid Object" }; int numIn1 = db.Insert(obj1); int numIn2 = db.Insert(obj2); Assert.AreEqual(guid1, obj1.Id); Assert.AreEqual(guid2, obj2.Id); db.Close(); }
public void StringWhereNull() { var db = new SQLiteConnection(TestPath.CreateTemporaryDatabase()); 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 Collate() { var obj = new TestObj { CollateDefault = "Alpha ", CollateBinary = "Alpha ", CollateRTrim = "Alpha ", CollateNoCase = "Alpha ", }; var db = new TestDb(TestPath.CreateTemporaryDatabase()); db.Insert(obj); Assert.AreEqual(1, (from o in db.Table <TestObj>() where o.CollateDefault == "Alpha " select o).Count()); Assert.AreEqual(0, (from o in db.Table <TestObj>() where o.CollateDefault == "ALPHA " select o).Count()); Assert.AreEqual(0, (from o in db.Table <TestObj>() where o.CollateDefault == "Alpha" select o).Count()); Assert.AreEqual(0, (from o in db.Table <TestObj>() where o.CollateDefault == "ALPHA" select o).Count()); Assert.AreEqual(1, (from o in db.Table <TestObj>() where o.CollateBinary == "Alpha " select o).Count()); Assert.AreEqual(0, (from o in db.Table <TestObj>() where o.CollateBinary == "ALPHA " select o).Count()); Assert.AreEqual(0, (from o in db.Table <TestObj>() where o.CollateBinary == "Alpha" select o).Count()); Assert.AreEqual(0, (from o in db.Table <TestObj>() where o.CollateBinary == "ALPHA" select o).Count()); Assert.AreEqual(1, (from o in db.Table <TestObj>() where o.CollateRTrim == "Alpha " select o).Count()); Assert.AreEqual(0, (from o in db.Table <TestObj>() where o.CollateRTrim == "ALPHA " select o).Count()); Assert.AreEqual(1, (from o in db.Table <TestObj>() where o.CollateRTrim == "Alpha" select o).Count()); Assert.AreEqual(0, (from o in db.Table <TestObj>() where o.CollateRTrim == "ALPHA" select o).Count()); Assert.AreEqual(1, (from o in db.Table <TestObj>() where o.CollateNoCase == "Alpha " select o).Count()); Assert.AreEqual(1, (from o in db.Table <TestObj>() where o.CollateNoCase == "ALPHA " select o).Count()); Assert.AreEqual(0, (from o in db.Table <TestObj>() where o.CollateNoCase == "Alpha" select o).Count()); Assert.AreEqual(0, (from o in db.Table <TestObj>() where o.CollateNoCase == "ALPHA" select o).Count()); }
public void WhereNull() { var db = new SQLiteConnection(TestPath.CreateTemporaryDatabase()); 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 ShouldPersistAndReadGuid() { var db = new TestDb(TestPath.CreateTemporaryDatabase()); var obj1 = new TestObj { Id = new Guid("36473164-C9E4-4CDF-B266-A0B287C85623"), Text = "First Guid Object" }; var obj2 = new TestObj { Id = new Guid("BC5C4C4A-CA57-4B61-8B53-9FD4673528B6"), Text = "Second Guid Object" }; int numIn1 = db.Insert(obj1); int numIn2 = db.Insert(obj2); Assert.AreEqual(1, numIn1); Assert.AreEqual(1, numIn2); List <TestObj> result = db.Query <TestObj>("select * from TestObj").ToList(); Assert.AreEqual(2, result.Count); Assert.AreEqual(obj1.Text, result[0].Text); Assert.AreEqual(obj2.Text, result[1].Text); Assert.AreEqual(obj1.Id, result[0].Id); Assert.AreEqual(obj2.Id, result[1].Id); db.Close(); }
public void 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 TestBoolean() { string tmpFile = TestPath.CreateTemporaryDatabase(); var db = new DbAcs(tmpFile); db.buildTable(); 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, db.CountWithFlag(true)); Assert.AreEqual(6, db.CountWithFlag(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 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 LargeByteArray() { 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 SQLiteConnection(TestPath.CreateTemporaryDatabase()); 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 TestDb() : base(TestPath.CreateTemporaryDatabase()) { CreateTable <Product>(); CreateTable <OrderLine>(); TraceListener = DebugTraceListener.Instance; }
public void ByteArrayWhereNull() { //Byte Arrays for comparisson ByteArrayClass[] byteArrays = new ByteArrayClass[] { 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 SQLiteConnection(TestPath.CreateTemporaryDatabase()); database.CreateTable <ByteArrayClass>(); byte[] criterion = null; //Insert all of the ByteArrayClass int id = 0; foreach (ByteArrayClass b in byteArrays) { database.Insert(b); if (b.bytes == null) { id = b.ID; } } Assert.AreNotEqual(0, id, "An ID wasn't set"); //Get it back out ByteArrayClass fetchedByteArray = database.Table <ByteArrayClass>().Where(x => x.bytes == criterion).First(); Assert.IsNotNull(fetchedByteArray); //Check they are the same Assert.AreEqual(id, fetchedByteArray.ID); }
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 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 async Task CreateBackup() { var obj1 = new BackupTestObj { Text = "GLaDOS loves testing!" }; var obj2 = new BackupTestObj2 { Text = "Keep testing, just keep testing" }; SQLiteConnection srcDb = new BackupTestDb(TestPath.CreateTemporaryDatabase()); int numIn1 = srcDb.Insert(obj1); Assert.AreEqual(1, numIn1); int numIn2 = srcDb.Insert(obj2); Assert.AreEqual(1, numIn2); const int numInserts = 1000; int inserts = 0; for (int i = 0; i < numInserts; i++) { inserts += srcDb.Insert(new BackupTestObj3()); } Assert.AreEqual(numInserts, inserts); List <BackupTestObj> result1 = srcDb.Query <BackupTestObj>("select * from BackupTestObj").ToList(); Assert.AreEqual(numIn1, result1.Count); Assert.AreEqual(obj1.Text, result1.First().Text); List <BackupTestObj> result2 = srcDb.Query <BackupTestObj>("select * from BackupTestObj2").ToList(); Assert.AreEqual(numIn2, result2.Count); Assert.AreEqual(obj2.Text, result2.First().Text); string destDbPath = await srcDb.CreateDatabaseBackup(); // Assert.IsTrue(File.Exists(destDbPath)); SQLiteConnection destDb = new BackupTestDb(destDbPath); result1 = destDb.Query <BackupTestObj>("select * from BackupTestObj").ToList(); Assert.AreEqual(numIn1, result1.Count); Assert.AreEqual(obj1.Text, result1.First().Text); result2 = destDb.Query <BackupTestObj>("select * from BackupTestObj2").ToList(); Assert.AreEqual(numIn2, result2.Count); Assert.AreEqual(obj2.Text, result2.First().Text); int count = destDb.ExecuteScalar <int>("select count(*) from BackupTestObj3"); Assert.AreEqual(numInserts, count); srcDb.Close(); destDb.Close(); }
public void NullableFloat() { var db = new SQLiteConnection(TestPath.CreateTemporaryDatabase()); // if the Ignored property is not ignore this will cause an exception db.CreateTable <DummyClass>(); }
public void ByteArrays() { var database = new SQLiteConnection(TestPath.CreateTemporaryDatabase()); database.CreateTable <ByteArrayClass>(); //Byte Arrays for comparisson ByteArrayClass[] byteArrays = { 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 //{ // //Rule should be that "Empty byte array should stay empty (and not become null)" // //But "The return value from sqlite3_column_blob() for a zero-length BLOB is a NULL pointer." (https://www.sqlite.org/c3ref/column_blob.html) // //So an empty byte array will become null // bytes = new byte[] {} //}, new ByteArrayClass { bytes = null } //Null should be supported }; //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++) { byteArrays[i].AssertEquals(fetchedByteArrays[i]); } }
public TestDb(bool storeDateTimeAsTicks = true, IContractResolver resolver = null) : base( TestPath.CreateTemporaryDatabase(), storeDateTimeAsTicks, null, extraTypeMappings: null, resolver: resolver) { TraceListener = DebugTraceListener.Instance; }
public void CustomIgnoreAttributeTest() { var db = new SQLiteConnection(TestPath.CreateTemporaryDatabase()); db.ColumnInformationProvider = new TestColumnInformationProvider(); // if the Ignored property is not ignore this will cause an exception db.CreateTable <TestObjIntWithIgnore>(); db.ColumnInformationProvider = null; }
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. } } }
private static TestDb GetTestDBWith100Elements() { 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"); return(db); }
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 NullableScalarInt() { var db = new SQLiteConnection(TestPath.CreateTemporaryDatabase()); 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); var actualShouldBeNull = db.ExecuteScalar <int?>("select NullableInt from NullableIntClass order by ID limit 1"); var actualShouldBe0 = db.ExecuteScalar <int?>("select NullableInt from NullableIntClass order by ID limit 1 offset 1"); var actualShouldBe1 = db.ExecuteScalar <int?>("select NullableInt from NullableIntClass order by ID limit 1 offset 2"); var actualShouldBeMinus1 = db.ExecuteScalar <int?>("select NullableInt from NullableIntClass order by ID limit 1 offset 3"); Assert.AreEqual(null, actualShouldBeNull); Assert.AreEqual(0, actualShouldBe0); Assert.AreEqual(1, actualShouldBe1); Assert.AreEqual(-1, actualShouldBeMinus1); }
public void CollateAttributeSubtype() { var obj = new TestObjWithSubtypedAttributes { CollateDefault = "Alpha ", CollateBinary = "Alpha ", CollateRTrim = "Alpha ", CollateNoCase = "Alpha ", }; var db = new TestDbSubtype(TestPath.CreateTemporaryDatabase()); db.Insert(obj); var testTable = db.Table <TestObjWithSubtypedAttributes>(); Assert.AreEqual(1, (from o in testTable where o.CollateDefault == "Alpha " select o).Count()); Assert.AreEqual(0, (from o in testTable where o.CollateDefault == "ALPHA " select o).Count()); Assert.AreEqual(0, (from o in testTable where o.CollateDefault == "Alpha" select o).Count()); Assert.AreEqual(0, (from o in testTable where o.CollateDefault == "ALPHA" select o).Count()); Assert.AreEqual(1, (from o in testTable where o.CollateBinary == "Alpha " select o).Count()); Assert.AreEqual(0, (from o in testTable where o.CollateBinary == "ALPHA " select o).Count()); Assert.AreEqual(0, (from o in testTable where o.CollateBinary == "Alpha" select o).Count()); Assert.AreEqual(0, (from o in testTable where o.CollateBinary == "ALPHA" select o).Count()); Assert.AreEqual(1, (from o in testTable where o.CollateRTrim == "Alpha " select o).Count()); Assert.AreEqual(0, (from o in testTable where o.CollateRTrim == "ALPHA " select o).Count()); Assert.AreEqual(1, (from o in testTable where o.CollateRTrim == "Alpha" select o).Count()); Assert.AreEqual(0, (from o in testTable where o.CollateRTrim == "ALPHA" select o).Count()); Assert.AreEqual(1, (from o in testTable where o.CollateNoCase == "Alpha " select o).Count()); Assert.AreEqual(1, (from o in testTable where o.CollateNoCase == "ALPHA " select o).Count()); Assert.AreEqual(0, (from o in testTable where o.CollateNoCase == "Alpha" select o).Count()); Assert.AreEqual(0, (from o in testTable where o.CollateNoCase == "ALPHA" select o).Count()); }
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 void Setup() { _db = new TestDb(TestPath.CreateTemporaryDatabase()); }
public void AsyncAsTicks() { var db = new SQLiteConnection(TestPath.CreateTemporaryDatabase()); TestAsyncDateTime(db, true); }
public BlobDatabase(IBlobSerializer serializer) : base(TestPath.CreateTemporaryDatabase(), false, serializer) { DropTable <ComplexOrder>(); }