public void DeleteSublistEntry(ISublistEntry entry)
{
using (var transaction = new SQLiteTransaction(SharedConnection))
{
DeleteSublistEntryInternal(entry, transaction);
transaction.Commit();
}
}
public long AddSublistEntry(ISublistEntry entry)
{
const string mainSql = @"INSERT INTO Entry " +
"(Title, Completed, CreatedAtUtc) " +
"VALUES " +
"(@title, @completed, @createdAtUtc)";
const string relationSql = @"INSERT INTO EntryRelation " +
"(ParentId, ChildId) " +
"VALUES " +
"(@parentId, @childId)";
using (var transaction = new SQLiteTransaction(SharedConnection))
{
using (var statement = transaction.Prepare(mainSql))
{
statement.Binding("@title", entry.Title);
statement.Binding("@completed", entry.Completed);
statement.Binding("@createdAtUtc", entry.CreatedAtUtc);
transaction.Execute(statement);
}
entry.Id = transaction.LastInsertRowId();
if (entry.ParentId.HasValue)
{
using (var statement = transaction.Prepare(relationSql))
{
statement.Binding("@parentId", entry.ParentId.Value);
statement.Binding("@childId", entry.Id);
transaction.Execute(statement);
}
}
transaction.Commit();
}
return entry.Id;
}
protected bool UpgradeDatabase()
{
var versionBeforeUpgrades = CurrentDatabaseVersion;
foreach (var version in DatabaseVersions.Where(version => CurrentDatabaseVersion < version.DbVersion).ToList())
{
try
{
using (var transaction = new SQLiteTransaction(SharedConnection))
{
version.Upgrade(transaction);
transaction.Commit();
}
System.Diagnostics.Debug.WriteLine("Database upgrade from {0} to {1} succeeded", CurrentDatabaseVersion, version.DbVersion);
CurrentDatabaseVersion = version.DbVersion;
}
catch (Exception exception)
{
System.Diagnostics.Debug.WriteLine(string.Format("Database upgrade from {0} to {1} failed", CurrentDatabaseVersion, version.DbVersion), exception);
}
}
bool hasUpgraded = versionBeforeUpgrades != CurrentDatabaseVersion;
return hasUpgraded;
}
private void DeleteSublistEntryInternal(ISublistEntry entry, SQLiteTransaction transaction)
{
const string deleteMainSql = @"DELETE FROM Entry WHERE Id = @id";
const string deleteRelationSql = @"DELETE FROM EntryRelation WHERE ChildId = @id OR ParentId = @id";
using (var statement = transaction.Prepare(deleteRelationSql))
{
statement.Binding("@id", entry.Id);
transaction.Execute(statement);
}
using (var statement = transaction.Prepare(deleteMainSql))
{
statement.Binding("@id", entry.Id);
transaction.Execute(statement);
}
foreach (var sublistEntry in entry.SubEntries)
{
using (var statement = transaction.Prepare(deleteRelationSql))
{
statement.Binding("@id", sublistEntry.Id);
transaction.Execute(statement);
}
using (var statement = transaction.Prepare(deleteMainSql))
{
statement.Binding("@id", sublistEntry.Id);
transaction.Execute(statement);
}
DeleteSublistEntryInternal(sublistEntry, transaction);
}
}
public void UpdateSublistEntry(ISublistEntry entry)
{
const string updateMainSql = @"UPDATE Entry " +
"SET Title = @title, Completed = @completed " +
"WHERE Id = @id";
const string updateRelationsSql = @"INSERT OR REPLACE INTO EntryRelation " +
"(ParentId, ChildId) " +
"VALUES (@parentId, @id)";
const string deleteRelationsSql = @"DELETE FROM EntryRelation " +
"WHERE ChildId = @id";
using (var transaction = new SQLiteTransaction(SharedConnection))
{
using (var statement = transaction.Prepare(updateMainSql))
{
statement.Binding("@title", entry.Title);
statement.Binding("@completed", entry.Completed);
statement.Binding("@id", entry.Id);
transaction.Execute(statement);
}
if (entry.ParentId.HasValue && entry.ParentId.Value == 0)
{
using (var statement = transaction.Prepare(deleteRelationsSql))
{
statement.Binding("@id", entry.Id);
transaction.Execute(statement);
}
}
else if (entry.ParentId.HasValue && entry.ParentId.Value != 0)
{
using (var statement = transaction.Prepare(updateRelationsSql))
{
statement.Binding("@parentId", entry.ParentId);
statement.Binding("@id", entry.Id);
transaction.Execute(statement);
}
}
transaction.Commit();
}
}
public void UpdateAppData(IAppData appData)
{
const string mainSql = @"UPDATE AppData SET ShowCompleted = @showCompleted WHERE Id = 1";
using (var transaction = new SQLiteTransaction(SharedConnection))
{
using (var statement = transaction.Prepare(mainSql))
{
statement.Binding("@showCompleted", appData.ShowCompleted);
transaction.Execute(statement);
}
transaction.Commit();
}
}