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>();
}
