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(TestPath.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 CanCompareAnyField()
{
var n = 20;
var 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.GetTempFileName());
db.InsertAll(cq);
var 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 ContainsConstantData()
{
int n = 20;
var cq = from i in Enumerable.Range(1, n)
select new TestObj()
{
Name = i.ToString()
};
var db = new TestDb(TestPath.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);
}
public void ListAndCustomTypeTest()
{
_db.Insert(Cruze);
_db.Insert(S600);
_db.Insert(C600);
Assert.AreEqual(3, _db.Table <Car>().Count());
Assert.AreEqual(210, _db.Table <Car>().Where(c => c.Name.Equals("Cruze")).FirstOrDefault().Quality.Speed);
}
public void WithoutRowId()
{
using (var conn = new TestDb())
{
conn.CreateTable <OrderLine> ();
var info = conn.Table <SqliteMaster>().Where(m => m.TableName == "OrderLine").First();
Assert.That(!info.Sql.Contains("without rowid"));
conn.CreateTable <WantsNoRowId> ();
info = conn.Table <SqliteMaster>().Where(m => m.TableName == "WantsNoRowId").First();
Assert.That(info.Sql.Contains("without rowid"));
}
}
public void SetBytesKey()
{
string path;
var rand = new Random();
var key = new byte[32];
rand.NextBytes(key);
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 Insert()
{
var query =
from p in db.Table <Product>()
select p;
Assert.AreEqual(0, changeCount);
Assert.AreEqual(22, query.Count());
db.Insert(new Product {
Name = "Hello", Price = 1001
});
Assert.AreEqual(1, changeCount);
Assert.AreEqual(23, query.Count());
}
public void AddColumns()
{
//
// Init the DB
//
var path = "";
using (var db = new TestDb(true)
{
Trace = true
}) {
path = db.DatabasePath;
db.CreateTable <TestAddBefore> ();
var cols = db.GetTableInfo("TestAdd");
Assert.AreEqual(2, cols.Count);
var o = new TestAddBefore {
Name = "Foo",
};
db.Insert(o);
var oo = db.Table <TestAddBefore> ().First();
Assert.AreEqual("Foo", oo.Name);
}
//
// Migrate and use it
//
using (var db = new SQLiteConnection(path, true)
{
Trace = true
}) {
db.CreateTable <TestAddAfter> ();
var cols = db.GetTableInfo("TestAdd");
Assert.AreEqual(4, cols.Count);
var oo = db.Table <TestAddAfter> ().First();
Assert.AreEqual("Foo", oo.Name);
Assert.AreEqual(0, oo.IntValue);
Assert.AreEqual(null, oo.StringValue);
var o = new TestAddAfter {
Name = "Bar",
IntValue = 42,
StringValue = "Hello",
};
db.Insert(o);
var ooo = db.Get <TestAddAfter> (o.Id);
Assert.AreEqual("Bar", ooo.Name);
Assert.AreEqual(42, ooo.IntValue);
Assert.AreEqual("Hello", ooo.StringValue);
}
}
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);
}
public void SuccessfulSavepointTransaction()
{
db.RunInTransaction(() => {
db.Delete(testObjects[0]);
db.Delete(testObjects[1]);
db.Insert(new TestObj());
});
Assert.AreEqual(testObjects.Count - 1, db.Table <TestObj>().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> ();
ExceptionAssert.Throws <SQLiteException>(() => db.Table <Product> ().Count());
}
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());
return(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);
}
public void InsertAllSuccessOutsideTransaction()
{
var testObjects = Enumerable.Range(1, 20).Select(i => new UniqueObj {
Id = i
}).ToList();
_db.InsertAll(testObjects);
Assert.AreEqual(testObjects.Count, _db.Table <UniqueObj>().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 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 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 DerivedIgnore()
{
var db = new TestDb();
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 BaseIgnores()
{
var db = new TestDb();
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 RedefinedIgnores()
{
var db = new TestDb();
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 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 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 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 OverrideNames()
{
var db = new TestDb();
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);
}
static T GetEntity <T> (TestDb db, int id) where T : IEntity, new ()
{
return(db.Table <T> ().FirstOrDefault(x => x.Id == id));
}
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 StartsWith()
{
var fs = db.Table <Product> ().Where(x => x.Name.StartsWith("F")).ToList();
Assert.AreEqual(2, fs.Count);
var lfs = db.Table <Product>().Where(x => x.Name.StartsWith("f")).ToList();
Assert.AreEqual(0, lfs.Count);
var lfs2 = db.Table <Product>().Where(x => x.Name.StartsWith("f", StringComparison.OrdinalIgnoreCase)).ToList();
Assert.AreEqual(2, lfs2.Count);
var bs = db.Table <Product> ().Where(x => x.Name.StartsWith("B")).ToList();
Assert.AreEqual(1, bs.Count);
}