/// <summary>
/// Loads basic information about all pages.
/// </summary>
public static async Task <RelationContext> LoadContextAsync(AppDbContext db, RelationContextOptions opts = null)
{
if (opts == null)
{
opts = new RelationContextOptions();
}
var pages = await LoadPagesAsync(db, opts);
var rels = await LoadRelationsAsync(db, opts);
return(new RelationContext
{
Pages = pages.ToDictionary(x => x.Id, x => x),
Relations = rels
});
}
/// <summary>
/// Loads the relations from the database.
/// </summary>
private static async Task <IReadOnlyDictionary <Guid, IReadOnlyList <RelationExcerpt> > > LoadRelationsAsync(AppDbContext db, RelationContextOptions opts)
{
if (opts.PagesOnly)
{
return(null);
}
var query = db.Relations
.Where(x => x.Source.IsDeleted == false &&
x.Destination.IsDeleted == false &&
x.IsDeleted == false);
if (opts.PeopleOnly)
{
query = query.Where(x => x.Source.Type == PageType.Person &&
x.Destination.Type == PageType.Person);
}
var data = await query.Select(x => new RelationExcerpt
{
Id = x.Id,
SourceId = x.SourceId,
DestinationId = x.DestinationId,
EventId = x.Event != null && x.Event.IsDeleted == false ? x.EventId : null,
Duration = FuzzyRange.TryParse(x.Duration),
Type = x.Type,
IsComplementary = x.IsComplementary
})
.ToListAsync();
return(data.GroupBy(x => x.SourceId).ToDictionary(x => x.Key, x => (IReadOnlyList <RelationExcerpt>)x.ToList()));
}
/// <summary>
/// Loads the pages from the database.
/// </summary>
private static async Task <List <PageExcerpt> > LoadPagesAsync(AppDbContext db, RelationContextOptions opts)
{
var filterByPeople = opts.PeopleOnly
? @"AND p.""Type"" = 0"
: "";
using (var conn = db.GetConnection())
{
var pagesSource = await conn.QueryAsync <PageExcerpt>(@"
SELECT
t.""Id"",
t.""Title"",
t.""Key"",
t.""Type"",
t.""BirthDate"",
t.""DeathDate"",
t.""IsDead"",
t.""Gender"",
COALESCE(
t.""Nickname"",
CASE
WHEN t.""LastName"" IS NULL THEN NULL
ELSE CONCAT(t.""FirstName"", ' ', t.""LastName"")
END
) AS ""ShortName"",
CASE
WHEN t.""MaidenName"" = t.""LastName"" THEN NULL
ELSE t.""MaidenName""
END AS ""MaidenName"",
t.""MainPhotoPath""
FROM (
SELECT
p.""Id"",
p.""Title"",
p.""Key"",
p.""Type"",
p.""Facts""::json#>>'{Main.Name,Values,-1,FirstName}' AS ""FirstName"",
p.""Facts""::json#>>'{Main.Name,Values,-1,LastName}' AS ""LastName"",
p.""Facts""::json#>>'{Main.Name,Value}' AS ""Nickname"",
p.""Facts""::json#>>'{Main.Name,Values,0,LastName}' AS ""MaidenName"",
p.""Facts""::json#>>'{Birth.Date,Value}' AS ""BirthDate"",
p.""Facts""::json#>>'{Death.Date,Value}' AS ""DeathDate"",
p.""Facts""::json->'Death.Date' IS NOT NULL AS ""IsDead"",
CAST(p.""Facts""::json#>>'{Bio.Gender,IsMale}' AS BOOLEAN) AS ""Gender"",
m.""FilePath"" AS ""MainPhotoPath""
FROM ""Pages"" AS p
LEFT JOIN ""Media"" AS m ON m.""Id"" = p.""MainPhotoId"" AND m.""IsDeleted"" = false
WHERE p.""IsDeleted"" = false " + filterByPeople + @"
) AS t
");
return(pagesSource.ToList());
}
}
