public ISublistEntry GetSublistEntry(long id)
{
const string sql = @"SELECT e.Id, e.Title, e.Completed, e.CreatedAtUtc, er.ParentId " +
"FROM Entry AS e " +
"LEFT JOIN EntryRelation AS er ON (e.Id = er.ChildId) " +
"WHERE e.Id = @id";
using (var statement = SharedConnection.Prepare(sql))
{
statement.Binding("@id", id);
if (statement.Step() == SQLiteResult.ROW)
{
return(new SublistEntry(statement.GetValue <DateTime>("CreatedAtUtc"))
{
Id = statement.GetValue <long>("Id"),
ParentId = statement.GetValue <long?>("ParentId"),
Title = statement.GetValue <string>("Title"),
Completed = statement.GetValue <bool>("Completed")
});
}
}
throw new DatabaseException($"No entry with Id {id} found.");
}
public IAppData GetAppData()
{
const string sql = @"SELECT ShowCompleted FROM AppData LIMIT 1";
using (var statement = SharedConnection.Prepare(sql))
{
if (statement.Step() == SQLiteResult.ROW)
{
return(new AppData()
{
ShowCompleted = statement.GetValue <bool>("ShowCompleted")
});
}
}
throw new DatabaseException($"No entry for app data found.");
}
public IEnumerable <ISublistEntry> GetAllSublistEntries()
{
const string sql = @"SELECT e.Id, e.Title, e.Completed, e.CreatedAtUtc, er.ParentId " +
"FROM Entry AS e " +
"LEFT JOIN EntryRelation AS er ON (e.Id = er.ChildId)";
List <ISublistEntry> entries = new List <ISublistEntry>();
using (var statement = SharedConnection.Prepare(sql))
{
while (statement.Step() == SQLiteResult.ROW)
{
entries.Add(new SublistEntry(statement.GetValue <DateTime>("CreatedAtUtc"))
{
Id = statement.GetValue <long>("Id"),
ParentId = statement.GetValue <long?>("ParentId"),
Title = statement.GetValue <string>("Title"),
Completed = statement.GetValue <bool>("Completed")
});
}
}
return(entries);
}