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 <MemberGuildSettings> QueryOrInsertMemberGuildConfig(
this IPKConnection conn, ulong guild, MemberId member) =>
conn.QueryFirstAsync <MemberGuildSettings>(
"insert into member_guild (guild, member) values (@guild, @member) on conflict (guild, member) do update set guild = @guild, member = @member returning *",
new { guild, member });
public Task DeleteMember(IPKConnection conn, MemberId id)
{
_logger.Information("Deleted {MemberId}", id);
return(conn.ExecuteAsync("delete from members where id = @Id", new { Id = id }));
}
public static Task <PKMember?> QueryMember(this IPKConnection conn, MemberId id) =>
conn.QueryFirstOrDefaultAsync <PKMember?>("select * from members where id = @id", new { id });
public async Task AddMessage(IPKConnection conn, ulong senderId, ulong guildId, ulong channelId, ulong postedMessageId, ulong triggerMessageId, MemberId proxiedMemberId)
{
// "on conflict do nothing" in the (pretty rare) case of duplicate events coming in from Discord, which would lead to a DB error before
await conn.ExecuteAsync("insert into messages(mid, guild, channel, member, sender, original_mid) values(@MessageId, @GuildId, @ChannelId, @MemberId, @SenderId, @OriginalMid) on conflict do nothing", new {
MessageId = postedMessageId,
GuildId = guildId,
ChannelId = channelId,
MemberId = proxiedMemberId,
SenderId = senderId,
OriginalMid = triggerMessageId
});
_logger.Debug("Stored message {Message} in channel {Channel}", postedMessageId, channelId);
}
public async Task <PKMember> GetMemberById(MemberId id)
{
using (var conn = await _conn.Obtain())
return(await conn.QuerySingleOrDefaultAsync <PKMember>("select * from members where id = @Id", new { Id = id }));
}
public static Task DeleteMember(this IPKConnection conn, MemberId id) =>
conn.ExecuteAsync("delete from members where id = @Id", new { Id = id });
public Task RemoveGroupsFromMember(IPKConnection conn, MemberId member, IReadOnlyCollection <GroupId> groups)
{
_logger.Information("Removed groups from {MemberId}: {GroupIds}", member, groups);
return(conn.ExecuteAsync("delete from group_members where member_id = @Member and group_id = any(@Groups)",
new { Member = @member, Groups = groups.ToArray() }));
}
public IAsyncEnumerable <PKGroup> GetMemberGroups(IPKConnection conn, MemberId id) =>
conn.QueryStreamAsync <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 });