public async Task Begin()
{
// Fetch all members in the system and log their names and hids
var members = await _conn.QueryAsync <PKMember>("select id, hid, name from members where system = @System",
new { System = _systemId });
foreach (var m in members)
{
_existingMembersByHid[m.Hid] = m;
_existingMembersByName[m.Name] = m;
}
}
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 <FullMessage> GetMessage(IPKConnection conn, ulong id)
{
FullMessage Mapper(PKMessage msg, PKMember member, PKSystem system) =>
new FullMessage
{
Message = msg, System = system, Member = member
};
var result = await conn.QueryAsync <PKMessage, PKMember, PKSystem, FullMessage>(
"select messages.*, members.*, systems.* from messages, members, systems where (mid = @Id or original_mid = @Id) and messages.member = members.id and systems.id = members.system",
Mapper, new { Id = id });
return(result.FirstOrDefault());
}
// todo: add a Mapper to QuerySingle and move this to SqlKata
public async Task <FullMessage?> GetMessage(IPKConnection conn, ulong id)
{
FullMessage Mapper(PKMessage msg, PKMember member, PKSystem system) =>
new()
{
Message = msg, System = system, Member = member
};
var query = "select * from messages"
+ " left join members on messages.member = members.id"
+ " left join systems on members.system = systems.id"
+ " where (mid = @Id or original_mid = @Id)";
var result = await conn.QueryAsync <PKMessage, PKMember, PKSystem, FullMessage>(
query, Mapper, new { Id = id });
return(result.FirstOrDefault());
}
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 <IEnumerable <PKGroup> > QueryMemberGroups(this IPKConnection conn, MemberId id) =>
conn.QueryAsync <PKGroup>(
"select groups.* from group_members inner join groups on group_members.group_id = groups.id where group_members.member_id = @Id",
new { Id = id });
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 Task SaveCommandMessage(IPKConnection conn, ulong messageId, ulong authorId) =>
conn.QueryAsync("insert into command_messages (message_id, author_id) values (@Message, @Author)",
new { Message = messageId, Author = authorId });
public static Task <IEnumerable <ProxyMember> > QueryProxyMembers(this IPKConnection conn, ulong account, ulong guild)
{
return(conn.QueryAsync <ProxyMember>("proxy_members",
new { account_id = account, guild_id = guild },
commandType: CommandType.StoredProcedure));
}
public Task <IEnumerable <ulong> > GetSystemAccounts(IPKConnection conn, SystemId system) =>
conn.QueryAsync <ulong>("select uid from accounts where system = @Id", new { Id = system });
public Task <IEnumerable <PKShardInfo> > GetShards(IPKConnection conn) =>
conn.QueryAsync <PKShardInfo>("select * from shards");
public static Task <IEnumerable <ListedGroup> > QueryGroupList(this IPKConnection conn, SystemId system) =>
conn.QueryAsync <ListedGroup>("select * from group_list where system = @System", new { System = system });
