private Customer CreateCustomer()
{
var customer = new Customer
{
FirstName = "foo",
LastName = "bar",
Email = Guid.NewGuid().ToString()
};
return customer;
}
public async Task TestRunInTransactionAsync()
{
// connect...
SQLiteAsyncConnection conn = GetAsyncConnection();
await conn.CreateTableAsync<Customer>();
bool transactionCompleted = false;
// run...
var customer = new Customer();
await conn.RunInTransactionAsync(c =>
{
// insert...
customer.FirstName = "foo";
customer.LastName = "bar";
customer.Email = Guid.NewGuid().ToString();
c.Insert(customer);
// delete it again...
c.Execute("delete from customer where id=?", customer.Id);
// set completion flag
transactionCompleted = true;
});
// check...
Assert.IsTrue(transactionCompleted);
using (var check = GetSyncConnection())
{
// load it back and check - should be deleted...
List<Customer> loaded = check.Table<Customer>().Where(v => v.Id == customer.Id).ToList();
Assert.AreEqual(0, loaded.Count);
}
}
public async Task TestInsertOrReplaceAsync()
{
// create...
var customer = new Customer();
customer.Id = 42;
customer.FirstName = "foo";
customer.LastName = "bar";
customer.Email = Guid.NewGuid().ToString();
// connect...
SQLiteAsyncConnection conn = GetAsyncConnection();
await conn.CreateTableAsync<Customer>();
// run...
await conn.InsertOrReplaceAsync(customer);
// check...
using (var check = GetSyncConnection())
{
// load it back...
var loaded = check.Get<Customer>(customer.Id);
Assert.AreEqual(loaded.Id, customer.Id);
Assert.AreEqual(loaded.FirstName, customer.FirstName);
Assert.AreEqual(loaded.LastName, customer.LastName);
Assert.AreEqual(loaded.Email, customer.Email);
}
// change ...
customer.FirstName = "baz";
customer.LastName = "biz";
customer.Email = Guid.NewGuid().ToString();
// replace...
await conn.InsertOrReplaceAsync(customer);
// check again...
// check...
using (var check = GetSyncConnection())
{
// load it back...
var loaded = check.Get<Customer>(customer.Id);
Assert.AreEqual(loaded.Id, customer.Id);
Assert.AreEqual(loaded.FirstName, customer.FirstName);
Assert.AreEqual(loaded.LastName, customer.LastName);
Assert.AreEqual(loaded.Email, customer.Email);
}
}
public async Task TestInsertOrReplaceAllAsync()
{
// create a bunch of customers...
var customers = new List<Customer>();
for (int index = 0; index < 100; index++)
{
var customer = new Customer();
customer.Id = index;
customer.FirstName = "foo";
customer.LastName = "bar";
customer.Email = Guid.NewGuid().ToString();
customers.Add(customer);
}
// connect...
SQLiteAsyncConnection conn = GetAsyncConnection();
await conn.CreateTableAsync<Customer>();
// insert them all...
await conn.InsertOrReplaceAllAsync(customers);
// change the existing ones...
foreach (var customer in customers)
{
customer.FirstName = "baz";
customer.LastName = "biz";
}
// ... and add a few more
for (int index = 100; index < 200; index++)
{
var customer = new Customer();
customer.Id = index;
customer.FirstName = "foo";
customer.LastName = "bar";
customer.Email = Guid.NewGuid().ToString();
customers.Add(customer);
}
// insert them all, replacing the already existing ones
await conn.InsertOrReplaceAllAsync(customers);
// check...
using (var check = GetSyncConnection())
{
for (int index = 0; index < customers.Count; index++)
{
// load it back and check...
var loaded = check.Get<Customer>(customers[index].Id);
Assert.AreEqual(loaded.FirstName, customers[index].FirstName);
Assert.AreEqual(loaded.LastName, customers[index].LastName);
Assert.AreEqual(loaded.Email, customers[index].Email);
}
}
}
public async Task TestInsertAllAsync()
{
// create a bunch of customers...
var customers = new List<Customer>();
for (int index = 0; index < 100; index++)
{
var customer = new Customer();
customer.FirstName = "foo";
customer.LastName = "bar";
customer.Email = Guid.NewGuid().ToString();
customers.Add(customer);
}
// connect...
SQLiteAsyncConnection conn = GetAsyncConnection();
await conn.CreateTableAsync<Customer>();
// insert them all...
await conn.InsertAllAsync(customers);
// check...
using (var check = GetSyncConnection())
{
for (int index = 0; index < customers.Count; index++)
{
// load it back and check...
var loaded = check.Get<Customer>(customers[index].Id);
Assert.AreEqual(loaded.Email, customers[index].Email);
}
}
}
public async Task StressAsync()
{
SQLiteAsyncConnection globalConn = GetAsyncConnection();
await globalConn.CreateTableAsync<Customer>();
int n = 500;
var errors = new List<string>();
var tasks = new List<Task>();
for (int i = 0; i < n; i++)
{
tasks.Add(Task.Factory.StartNew(async delegate
{
try
{
SQLiteAsyncConnection conn = GetAsyncConnection();
var obj = new Customer
{
FirstName = i.ToString(),
};
await conn.InsertAsync(obj);
if (obj.Id == 0)
{
lock (errors)
{
errors.Add("Bad Id");
}
}
var obj3 = await (from c in conn.Table<Customer>() where c.Id == obj.Id select c).ToListAsync();
Customer obj2 = obj3.FirstOrDefault();
if (obj2 == null)
{
lock (errors)
{
errors.Add("Failed query");
}
}
}
catch (Exception ex)
{
lock (errors)
{
errors.Add(ex.Message);
}
}
}));
}
await Task.WhenAll(tasks);
int count = await globalConn.Table<Customer>().CountAsync();
Assert.AreEqual(0, errors.Count);
Assert.AreEqual(n, count);
}
public async Task GetAsync()
{
// create...
var customer = new Customer();
customer.FirstName = "foo";
customer.LastName = "bar";
customer.Email = Guid.NewGuid().ToString();
// connect and insert...
SQLiteAsyncConnection conn = GetAsyncConnection();
await conn.CreateTableAsync<Customer>();
await conn.InsertAsync(customer);
// check...
Assert.AreNotEqual(0, customer.Id);
// get it back...
var loaded = await conn.GetAsync<Customer>(customer.Id);
// check...
Assert.AreEqual(customer.Id, loaded.Id);
}
public async Task TestTableAsync()
{
// connect...
SQLiteAsyncConnection conn = GetAsyncConnection();
await conn.CreateTableAsync<Customer>();
await conn.ExecuteAsync("delete from customer");
// insert some...
var customers = new List<Customer>();
for (int index = 0; index < 5; index++)
{
var customer = new Customer();
customer.FirstName = "foo";
customer.LastName = "bar";
customer.Email = Guid.NewGuid().ToString();
// insert...
await conn.InsertAsync(customer);
// add...
customers.Add(customer);
}
// run the table operation...
AsyncTableQuery<Customer> query = conn.Table<Customer>();
List<Customer> loaded = await query.ToListAsync();
// check that we got them all back...
Assert.AreEqual(5, loaded.Count);
Assert.IsNotNull(loaded.Where(v => v.Id == customers[0].Id));
Assert.IsNotNull(loaded.Where(v => v.Id == customers[1].Id));
Assert.IsNotNull(loaded.Where(v => v.Id == customers[2].Id));
Assert.IsNotNull(loaded.Where(v => v.Id == customers[3].Id));
Assert.IsNotNull(loaded.Where(v => v.Id == customers[4].Id));
}
public async Task FindAsyncWithExpression()
{
// create...
var customer = new Customer();
customer.FirstName = "foo";
customer.LastName = "bar";
customer.Email = Guid.NewGuid().ToString();
// connect and insert...
string path = null;
SQLiteAsyncConnection conn = GetConnection(ref path);
await conn.CreateTableAsync<Customer>();
await conn.InsertAsync(customer);
// check...
Assert.AreNotEqual(0, customer.Id);
// get it back...
var loaded = await conn.FindAsync<Customer>(x => x.Id == customer.Id);
// check...
Assert.AreEqual(customer.Id, loaded.Id);
}
public async Task StressAsync()
{
const int defaultBusyTimeout = 100;
SQLiteAsyncConnection globalConn = GetAsyncConnection();
// see http://stackoverflow.com/questions/12004426/sqlite-returns-sqlite-busy-in-wal-mode
var journalMode = await globalConn.ExecuteScalarAsync<string>("PRAGMA journal_mode = wal"); // = wal");
Debug.WriteLine("journal_mode: " + journalMode);
//var synchronous = await globalConn.ExecuteScalarAsync<string>("PRAGMA synchronous"); // 2 = FULL
//Debug.WriteLine("synchronous: " + synchronous);
//var pageSize = await globalConn.ExecuteScalarAsync<string>("PRAGMA page_size"); // 1024 default
//Debug.WriteLine("page_size: " + pageSize);
var busyTimeout = await globalConn.ExecuteScalarAsync<string>(
string.Format("PRAGMA busy_timeout = {0}", defaultBusyTimeout));
Debug.WriteLine("busy_timeout: " + busyTimeout);
await globalConn.CreateTableAsync<Customer>();
int n = 500;
var errors = new List<string>();
var tasks = new List<Task>();
for (int i = 0; i < n; i++)
{
int taskId = i;
tasks.Add(Task.Run(async () =>
{
string taskStep = "";
try
{
taskStep = "CONNECT";
SQLiteAsyncConnection conn = GetAsyncConnection();
// each connection retains the global journal_mode but somehow resets busy_timeout to 100
busyTimeout = await globalConn.ExecuteScalarAsync<string>(
string.Format("PRAGMA busy_timeout = {0}", defaultBusyTimeout));
// Debug.WriteLine("busy_timeout: " + busyTimeout);
var obj = new Customer
{
FirstName = taskId.ToString(),
};
taskStep = "INSERT";
await conn.InsertAsync(obj);
if (obj.Id == 0)
{
lock (errors)
{
errors.Add("Bad Id");
}
}
taskStep = "SELECT";
var obj3 = await (from c in conn.Table<Customer>() where c.Id == obj.Id select c).ToListAsync();
Customer obj2 = obj3.FirstOrDefault();
if (obj2 == null)
{
lock (errors)
{
errors.Add("Failed query");
}
}
// Debug.WriteLine("task {0} with id {1} and name {2}", taskId, obj.Id, obj.FirstName);
}
catch (Exception ex)
{
lock (errors)
{
errors.Add(string.Format("{0}: {1}", taskStep, ex.Message));
}
}
}));
}
await Task.WhenAll(tasks);
Assert.AreEqual(n, tasks.Where(t => t.IsCompleted).Count());
//int j = 0;
//foreach (var error in errors)
//{
// Debug.WriteLine("{0} {1}", j++, error);
//}
Assert.AreEqual(0, errors.Count, "Error in task runs");
int count = await globalConn.Table<Customer>().CountAsync();
Assert.AreEqual(n, count, "Not enough items in table");
// TODO: get out of wal mode - currently fails with 'database is locked'
// journalMode = await globalConn.ExecuteScalarAsync<string>("PRAGMA journal_mode = delete");
}
public void TestInsertAllAsync()
{
// create a bunch of customers...
var customers = new List<Customer>();
for (int index = 0; index < 100; index++)
{
var customer = new Customer();
customer.FirstName = "foo";
customer.LastName = "bar";
customer.Email = Guid.NewGuid().ToString();
customers.Add(customer);
}
// connect...
string path = null;
SQLiteAsyncConnection conn = GetConnection(ref path);
conn.CreateTableAsync<Customer>().Wait();
// insert them all...
conn.InsertAllAsync(customers).Wait();
// check...
using (var check = new SQLiteConnection(_sqlite3Platform, path))
{
for (int index = 0; index < customers.Count; index++)
{
// load it back and check...
var loaded = check.Get<Customer>(customers[index].Id);
Assert.AreEqual(loaded.Email, customers[index].Email);
}
}
}
public void StressAsync()
{
string path = null;
SQLiteAsyncConnection globalConn = GetConnection(ref path);
globalConn.CreateTableAsync<Customer>().Wait();
int threadCount = 0;
var doneEvent = new AutoResetEvent(false);
int n = 500;
var errors = new List<string>();
for (int i = 0; i < n; i++)
{
Task.Factory.StartNew(delegate
{
try
{
SQLiteAsyncConnection conn = GetConnection();
var obj = new Customer
{
FirstName = i.ToString(),
};
conn.InsertAsync(obj).Wait();
if (obj.Id == 0)
{
lock (errors)
{
errors.Add("Bad Id");
}
}
Customer obj2 =
(from c in conn.Table<Customer>() where c.Id == obj.Id select c).ToListAsync()
.Result.FirstOrDefault();
if (obj2 == null)
{
lock (errors)
{
errors.Add("Failed query");
}
}
}
catch (Exception ex)
{
lock (errors)
{
errors.Add(ex.Message);
}
}
threadCount++;
if (threadCount == n)
{
doneEvent.Set();
}
});
}
doneEvent.WaitOne();
int count = globalConn.Table<Customer>().CountAsync().Result;
Assert.AreEqual(0, errors.Count);
Assert.AreEqual(n, count);
}
public void GetAsync()
{
// create...
var customer = new Customer();
customer.FirstName = "foo";
customer.LastName = "bar";
customer.Email = Guid.NewGuid().ToString();
// connect and insert...
string path = null;
SQLiteAsyncConnection conn = GetConnection(ref path);
conn.CreateTableAsync<Customer>().Wait();
conn.InsertAsync(customer).Wait();
// check...
Assert.AreNotEqual(0, customer.Id);
// get it back...
Task<Customer> task = conn.GetAsync<Customer>(customer.Id);
task.Wait();
Customer loaded = task.Result;
// check...
Assert.AreEqual(customer.Id, loaded.Id);
}
