public IAsyncEnumerable <PKSwitch> GetSwitches(IPKConnection conn, SystemId system)
{
// TODO: refactor the PKSwitch data structure to somehow include a hydrated member list
return(conn.QueryStreamAsync <PKSwitch>(
"select * from switches where system = @System order by timestamp desc",
new { System = system }));
}
public async Task AddSwitch(SystemId system, IEnumerable <PKMember> members)
{
// Use a transaction here since we're doing multiple executed commands in one
await using var conn = await _conn.Obtain();
await using var tx = await conn.BeginTransactionAsync();
// First, we insert the switch itself
var sw = await conn.QuerySingleAsync <PKSwitch>("insert into switches(system) values (@System) returning *",
new { System = system });
// Then we insert each member in the switch in the switch_members table
// TODO: can we parallelize this or send it in bulk somehow?
foreach (var member in members)
{
await conn.ExecuteAsync(
"insert into switch_members(switch, member) values(@Switch, @Member)",
new { Switch = sw.Id, Member = member.Id });
}
// Finally we commit the tx, since the using block will otherwise rollback it
await tx.CommitAsync();
_logger.Information("Registered switch {Switch} in system {System} with members {@Members}", sw.Id, system, members.Select(m => m.Id));
}
public async Task RemoveAccount(IPKConnection conn, SystemId system, ulong accountId)
{
await conn.ExecuteAsync("delete from accounts where uid = @Id and system = @SystemId",
new { Id = accountId, SystemId = system });
_logger.Information("Unlinked account {UserId} from {SystemId}", accountId, system);
}
public static Task <PKSystem> UpdateSystem(this IPKConnection conn, SystemId id, SystemPatch patch)
{
var(query, pms) = patch.Apply(UpdateQueryBuilder.Update("systems", "id = @id"))
.WithConstant("id", id)
.Build("returning *");
return(conn.QueryFirstAsync <PKSystem>(query, pms));
}
public async Task AddSwitch(IPKConnection conn, SystemId system, IReadOnlyCollection <MemberId> members)
{
// Use a transaction here since we're doing multiple executed commands in one
await using var tx = await conn.BeginTransactionAsync();
// First, we insert the switch itself
var sw = await conn.QuerySingleAsync <PKSwitch>("insert into switches(system) values (@System) returning *",
new { System = system });
// Then we insert each member in the switch in the switch_members table
await using (var w = conn.BeginBinaryImport("copy switch_members (switch, member) from stdin (format binary)"))
{
foreach (var member in members)
{
await w.StartRowAsync();
await w.WriteAsync(sw.Id.Value, NpgsqlDbType.Integer);
await w.WriteAsync(member.Value, NpgsqlDbType.Integer);
}
await w.CompleteAsync();
}
// Finally we commit the tx, since the using block will otherwise rollback it
await tx.CommitAsync();
_logger.Information("Created {SwitchId} in {SystemId}: {Members}", sw.Id, system, members);
}
public IAsyncEnumerable <PKSwitch> GetSwitches(SystemId system)
{
// TODO: refactor the PKSwitch data structure to somehow include a hydrated member list
// (maybe when we get caching in?)
return(_conn.QueryStreamAsync <PKSwitch>(
"select * from switches where system = @System order by timestamp desc",
new { System = system }));
}
public Task <PKSystem> UpdateSystem(IPKConnection conn, SystemId id, SystemPatch patch)
{
_logger.Information("Updated {SystemId}: {@SystemPatch}", id, patch);
var(query, pms) = patch.Apply(UpdateQueryBuilder.Update("systems", "id = @id"))
.WithConstant("id", id)
.Build("returning *");
return(conn.QueryFirstAsync <PKSystem>(query, pms));
}
public async Task AddAccount(IPKConnection conn, SystemId system, ulong accountId)
{
// We have "on conflict do nothing" since linking an account when it's already linked to the same system is idempotent
// This is used in import/export, although the pk;link command checks for this case beforehand
await conn.ExecuteAsync("insert into accounts (uid, system) values (@Id, @SystemId) on conflict do nothing",
new { Id = accountId, SystemId = system });
_logger.Information("Linked account {UserId} to {SystemId}", accountId, system);
}
public static Task UpsertSystemGuild(this IPKConnection conn, SystemId system, ulong guild,
SystemGuildPatch patch)
{
var(query, pms) = patch.Apply(UpdateQueryBuilder.Upsert("system_guild", "system, guild"))
.WithConstant("system", system)
.WithConstant("guild", guild)
.Build();
return(conn.ExecuteAsync(query, pms));
}
public async Task <PKGroup> CreateGroup(IPKConnection conn, SystemId system, string name)
{
var group = await conn.QueryFirstAsync <PKGroup>(
"insert into groups (hid, system, name) values (find_free_group_hid(), @System, @Name) returning *",
new { System = system, Name = name });
_logger.Information("Created group {GroupId} in system {SystemId}: {GroupName}", group.Id, system, name);
return(group);
}
public Task UpsertSystemGuild(IPKConnection conn, SystemId system, ulong guild,
SystemGuildPatch patch)
{
_logger.Information("Updated {SystemId} in guild {GuildId}: {@SystemGuildPatch}", system, guild, patch);
var(query, pms) = patch.Apply(UpdateQueryBuilder.Upsert("system_guild", "system, guild"))
.WithConstant("system", system)
.WithConstant("guild", guild)
.Build();
return(conn.ExecuteAsync(query, pms));
}
public async Task <PKMember> CreateMember(IPKConnection conn, SystemId id, string memberName, IDbTransaction?transaction = null)
{
var member = await conn.QueryFirstAsync <PKMember>(
"insert into members (hid, system, name) values (find_free_member_hid(), @SystemId, @Name) returning *",
new { SystemId = id, Name = memberName }, transaction);
_logger.Information("Created {MemberId} in {SystemId}: {MemberName}",
member.Id, id, memberName);
return(member);
}
public static Task <int> GetSystemMemberCount(this IPKConnection conn, SystemId id, PrivacyLevel?privacyFilter = null)
{
var query = new StringBuilder("select count(*) from members where system = @Id");
if (privacyFilter != null)
{
query.Append($" and member_visibility = {(int) privacyFilter.Value}");
}
return(conn.QuerySingleAsync <int>(query.ToString(), new { Id = id }));
}
public async Task <IEnumerable <SwitchListEntry> > GetPeriodFronters(IPKConnection conn,
SystemId system, Instant periodStart,
Instant periodEnd)
{
// TODO: IAsyncEnumerable-ify this one
// TODO: this doesn't belong in the repo
// Returns the timestamps and member IDs of switches overlapping the range, in chronological (newest first) order
var switchMembers = await GetSwitchMembersList(conn, system, periodStart, periodEnd).ToListAsync();
// query DB for all members involved in any of the switches above and collect into a dictionary for future use
// this makes sure the return list has the same instances of PKMember throughout, which is important for the dictionary
// key used in GetPerMemberSwitchDuration below
var membersList = await conn.QueryAsync <PKMember>(
"select * from members where id = any(@Switches)", // lol postgres specific `= any()` syntax
new { Switches = switchMembers.Select(m => m.Member.Value).Distinct().ToList() });
var memberObjects = membersList.ToDictionary(m => m.Id);
// Initialize entries - still need to loop to determine the TimespanEnd below
var entries =
from item in switchMembers
group item by item.Timestamp
into g
select new SwitchListEntry
{
TimespanStart = g.Key,
Members = g.Where(x => x.Member != default(MemberId)).Select(x => memberObjects[x.Member])
.ToList()
};
// Loop through every switch that overlaps the range and add it to the output list
// end time is the *FOLLOWING* switch's timestamp - we cheat by working backwards from the range end, so no dates need to be compared
var endTime = periodEnd;
var outList = new List <SwitchListEntry>();
foreach (var e in entries)
{
// Override the start time of the switch if it's outside the range (only true for the "out of range" switch we included above)
var switchStartClamped = e.TimespanStart < periodStart
? periodStart
: e.TimespanStart;
outList.Add(new SwitchListEntry
{
Members = e.Members, TimespanStart = switchStartClamped, TimespanEnd = endTime
});
// next switch's end is this switch's start (we're working backward in time)
endTime = e.TimespanStart;
}
return(outList);
}
public async Task <int> GetSystemMemberCount(SystemId id, bool includePrivate)
{
var query = "select count(*) from members where system = @Id";
if (!includePrivate)
{
query += " and member_visibility = 1"; // 1 = public
}
using (var conn = await _conn.Obtain())
return(await conn.ExecuteScalarAsync <int>(query, new { id }));
}
public async Task <FrontBreakdown> GetFrontBreakdown(IPKConnection conn, SystemId system, Instant periodStart,
Instant periodEnd)
{
// TODO: this doesn't belong in the repo
var dict = new Dictionary <PKMember, Duration>();
var noFronterDuration = Duration.Zero;
// Sum up all switch durations for each member
// switches with multiple members will result in the duration to add up to more than the actual period range
var actualStart = periodEnd; // will be "pulled" down
var actualEnd = periodStart; // will be "pulled" up
foreach (var sw in await GetPeriodFronters(conn, system, periodStart, periodEnd))
{
var span = sw.TimespanEnd - sw.TimespanStart;
foreach (var member in sw.Members)
{
if (!dict.ContainsKey(member))
{
dict.Add(member, span);
}
else
{
dict[member] += span;
}
}
if (sw.Members.Count == 0)
{
noFronterDuration += span;
}
if (sw.TimespanStart < actualStart)
{
actualStart = sw.TimespanStart;
}
if (sw.TimespanEnd > actualEnd)
{
actualEnd = sw.TimespanEnd;
}
}
return(new FrontBreakdown
{
MemberSwitchDurations = dict,
NoFronterDuration = noFronterDuration,
RangeStart = actualStart,
RangeEnd = actualEnd
});
}
public async Task <PKMember> CreateMember(SystemId system, string name)
{
PKMember member;
using (var conn = await _conn.Obtain())
member = await conn.QuerySingleAsync <PKMember>("insert into members (hid, system, name) values (find_free_member_hid(), @SystemId, @Name) returning *", new {
SystemID = system,
Name = name
});
_logger.Information("Created member {Member}", member.Id);
return(member);
}
public async IAsyncEnumerable <SwitchMembersListEntry> GetSwitchMembersList(IPKConnection conn,
SystemId system, Instant start, Instant end)
{
// Wrap multiple commands in a single transaction for performance
await using var tx = await conn.BeginTransactionAsync();
// Find the time of the last switch outside the range as it overlaps the range
// If no prior switch exists, the lower bound of the range remains the start time
var lastSwitch = await conn.QuerySingleOrDefaultAsync <Instant>(
@"SELECT COALESCE(MAX(timestamp), @Start)
FROM switches
WHERE switches.system = @System
AND switches.timestamp < @Start",
new { System = system, Start = start });
// Then collect the time and members of all switches that overlap the range
var switchMembersEntries = conn.QueryStreamAsync <SwitchMembersListEntry>(
@"SELECT switch_members.member, switches.timestamp
FROM switches
LEFT JOIN switch_members
ON switches.id = switch_members.switch
WHERE switches.system = @System
AND (
switches.timestamp >= @Start
OR switches.timestamp = @LastSwitch
)
AND switches.timestamp < @End
ORDER BY switches.timestamp DESC",
new { System = system, Start = start, End = end, LastSwitch = lastSwitch });
// Yield each value here
await foreach (var entry in switchMembersEntries)
{
yield return(entry);
}
// Don't really need to worry about the transaction here, we're not doing any *writes*
}
public static Task <IEnumerable <ulong> > GetLinkedAccounts(this IPKConnection conn, SystemId id) =>
conn.QueryAsync <ulong>("select uid from accounts where system = @Id", new { Id = id });
public static Task <PKSystem?> QuerySystem(this IPKConnection conn, SystemId id) =>
conn.QueryFirstOrDefaultAsync <PKSystem?>("select * from systems where id = @id", new { id });
public async Task <int> GetSwitchCount(IPKConnection conn, SystemId system)
{
return(await conn.QuerySingleAsync <int>("select count(*) from switches where system = @Id", new { Id = system }));
}
private BulkImporter(SystemId systemId, IPKConnection conn, IPKTransaction tx)
{
_systemId = systemId;
_conn = conn;
_tx = tx;
}
public static Task <PKGroup?> QueryGroupByName(this IPKConnection conn, SystemId system, string name) =>
conn.QueryFirstOrDefaultAsync <PKGroup?>("select * from groups where system = @System and lower(Name) = lower(@Name)", new { System = system, Name = name });
public Task <PKMember?> GetMemberByDisplayName(IPKConnection conn, SystemId system, string name) =>
conn.QueryFirstOrDefaultAsync <PKMember?>("select * from members where lower(display_name) = lower(@Name) and system = @SystemID", new { Name = name, SystemID = system });
public Task DeleteSystem(IPKConnection conn, SystemId id)
{
_logger.Information("Deleted {SystemId}", id);
return(conn.ExecuteAsync("delete from systems where id = @Id", new { Id = id }));
}
public static Task <IEnumerable <SystemFronter> > QueryCurrentFronters(this IPKConnection conn, SystemId system) =>
conn.QueryAsync <SystemFronter>("select * from system_fronters where system = @system", new { system });
public static Task <SystemGuildSettings> QueryOrInsertSystemGuildConfig(this IPKConnection conn, ulong guild, SystemId system) =>
conn.QueryFirstAsync <SystemGuildSettings>(
"insert into system_guild (guild, system) values (@guild, @system) on conflict (guild, system) do update set guild = @guild, system = @system returning *",
new { guild, system });
public Task <IEnumerable <ulong> > GetSystemAccounts(IPKConnection conn, SystemId system) =>
conn.QueryAsync <ulong>("select uid from accounts where system = @Id", new { Id = system });
public static Task <IEnumerable <ListedMember> > QueryMemberList(this IPKConnection conn, SystemId system, PrivacyLevel?privacyFilter = null, string?filter = null, bool includeDescriptionInNameFilter = false)
{
StringBuilder query = new StringBuilder("select * from member_list where system = @system");
if (privacyFilter != null)
{
query.Append($" and member_privacy = {(int) privacyFilter}");
}
if (filter != null)
{
public IAsyncEnumerable <PKMember> GetSystemMembers(IPKConnection conn, SystemId system) =>
conn.QueryStreamAsync <PKMember>("select * from members where system = @SystemID", new { SystemID = system });