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 Task <int> GetGroupMemberCount(IPKConnection conn, GroupId id, PrivacyLevel?privacyFilter = null)
{
var query = new StringBuilder("select count(*) from group_members");
if (privacyFilter != null)
{
query.Append(" inner join members on group_members.member_id = members.id");
}
query.Append(" where group_members.group_id = @Id");
if (privacyFilter != null)
{
query.Append(" and members.member_visibility = @PrivacyFilter");
}
return(conn.QuerySingleOrDefaultAsync <int>(query.ToString(), new { Id = id, PrivacyFilter = privacyFilter }));
}
private async Task<int> GetCurrentDatabaseVersion(IPKConnection conn)
{
// First, check if the "info" table exists (it may not, if this is a *really* old database)
var hasInfoTable =
await conn.QuerySingleOrDefaultAsync<int>(
"select count(*) from information_schema.tables where table_name = 'info'") == 1;
// If we have the table, read the schema version
if (hasInfoTable)
return await conn.QuerySingleOrDefaultAsync<int>("select schema_version from info");
// If not, we return version "-1"
// This means migration 0 will get executed, getting us into a consistent state
// Then, migration 1 gets executed, which creates the info table and sets version to 1
return -1;
}
private async Task ExecuteProxy(Shard shard, IPKConnection conn, Message trigger, MessageContext ctx,
ProxyMatch match, bool allowEveryone, bool allowEmbeds)
{
// Create reply embed
var embeds = new List <Embed>();
if (trigger.Type == Message.MessageType.Reply && trigger.MessageReference?.ChannelId == trigger.ChannelId)
{
var repliedTo = trigger.ReferencedMessage.Value;
if (repliedTo != null)
{
var nickname = await FetchReferencedMessageAuthorNickname(trigger, repliedTo);
var embed = CreateReplyEmbed(match, trigger, repliedTo, nickname);
if (embed != null)
{
embeds.Add(embed);
}
}
// TODO: have a clean error for when message can't be fetched instead of just being silent
}
// Send the webhook
var content = match.ProxyContent;
if (!allowEmbeds)
{
content = content.BreakLinkEmbeds();
}
var messageChannel = _cache.GetChannel(trigger.ChannelId);
var rootChannel = _cache.GetRootChannel(trigger.ChannelId);
var threadId = messageChannel.IsThread() ? messageChannel.Id : (ulong?)null;
var proxyMessage = await _webhookExecutor.ExecuteWebhook(new ProxyRequest
{
GuildId = trigger.GuildId !.Value,
ChannelId = rootChannel.Id,
ThreadId = threadId,
Name = match.Member.ProxyName(ctx),
AvatarUrl = match.Member.ProxyAvatar(ctx),
Content = content,
Attachments = trigger.Attachments,
Embeds = embeds.ToArray(),
AllowEveryone = allowEveryone,
});
private async Task ExecuteSqlFile(string resourceName, IPKConnection conn, IDbTransaction tx = null)
{
await using var stream = typeof(Database).Assembly.GetManifestResourceStream(resourceName);
if (stream == null)
{
throw new ArgumentException($"Invalid resource name '{resourceName}'");
}
using var reader = new StreamReader(stream);
var query = await reader.ReadToEndAsync();
await conn.ExecuteAsync(query, transaction : tx);
// If the above creates new enum/composite types, we must tell Npgsql to reload the internal type caches
// This will propagate to every other connection as well, since it marks the global type mapper collection dirty.
((PKConnection)conn).ReloadTypes();
}
public async Task AddGroupsToMember(IPKConnection conn, MemberId member, IReadOnlyCollection <GroupId> groups)
{
await using var w =
conn.BeginBinaryImport("copy group_members (group_id, member_id) from stdin (format binary)");
foreach (var group in groups)
{
await w.StartRowAsync();
await w.WriteAsync(group.Value);
await w.WriteAsync(member.Value);
}
await w.CompleteAsync();
_logger.Information("Added member {MemberId} to groups {GroupIds}", member, groups);
}
// 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 async Task AddMembersToGroup(IPKConnection conn, GroupId group,
IReadOnlyCollection <MemberId> members)
{
await using var w =
conn.BeginBinaryImport("copy group_members (group_id, member_id) from stdin (format binary)");
foreach (var member in members)
{
await w.StartRowAsync();
await w.WriteAsync(group.Value);
await w.WriteAsync(member.Value);
}
await w.CompleteAsync();
_logger.Information("Added members to {GroupId}: {MemberIds}", group, members);
}
public async Task ApplyMigrations(IPKConnection conn)
{
// Run everything in a transaction
await using var tx = await conn.BeginTransactionAsync();
// Before applying migrations, clean out views/functions to prevent type errors
await ExecuteSqlFile($"{RootPath}.clean.sql", conn, tx);
// Apply all migrations between the current database version and the target version
await ApplyMigrations(conn, tx);
// Now, reapply views/functions (we deleted them above, no need to worry about conflicts)
await ExecuteSqlFile($"{RootPath}.Views.views.sql", conn, tx);
await ExecuteSqlFile($"{RootPath}.Functions.functions.sql", conn, tx);
// Finally, commit tx
await tx.CommitAsync();
}
private async Task HandleProxyExecutedActions(DiscordClient shard, IPKConnection conn, MessageContext ctx,
DiscordMessage triggerMessage, DiscordMessage proxyMessage,
ProxyMatch match)
{
Task SaveMessageInDatabase() => _repo.AddMessage(conn, new PKMessage
{
Channel = triggerMessage.ChannelId,
Guild = triggerMessage.Channel.GuildId,
Member = match.Member.Id,
Mid = proxyMessage.Id,
OriginalMid = triggerMessage.Id,
Sender = triggerMessage.Author.Id
});
Task LogMessageToChannel() => _logChannel.LogMessage(shard, ctx, match, triggerMessage, proxyMessage.Id).AsTask();
async Task DeleteProxyTriggerMessage()
{
// Wait a second or so before deleting the original message
await Task.Delay(MessageDeletionDelay);
try
{
await triggerMessage.DeleteAsync();
}
catch (NotFoundException)
{
_logger.Debug("Trigger message {TriggerMessageId} was already deleted when we attempted to; deleting proxy message {ProxyMessageId} also",
triggerMessage.Id, proxyMessage.Id);
await HandleTriggerAlreadyDeleted(proxyMessage);
// Swallow the exception, we don't need it
}
}
// Run post-proxy actions (simultaneously; order doesn't matter)
// Note that only AddMessage is using our passed-in connection, careful not to pass it elsewhere and run into conflicts
await Task.WhenAll(
DeleteProxyTriggerMessage(),
SaveMessageInDatabase(),
LogMessageToChannel()
);
}
public async Task <PKSwitch> 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);
_ = _dispatch.Dispatch(sw.Id, new UpdateDispatchData
{
Event = DispatchEvent.CREATE_SWITCH,
EventData = JObject.FromObject(new
{
id = sw.Uuid.ToString(),
timestamp = sw.Timestamp.FormatExport(),
members = await GetMemberGuids(members),
}),
});
return(sw);
}
public static Task <IEnumerable <ListedMember> > QueryMemberList(this IPKConnection conn, SystemId system, MemberListQueryOptions opts)
{
StringBuilder query;
if (opts.GroupFilter == null)
{
query = new StringBuilder("select * from member_list where system = @system");
}
else
{
query = new StringBuilder("select member_list.* from group_members inner join member_list on member_list.id = group_members.member_id where group_id = @groupFilter");
}
if (opts.PrivacyFilter != null)
{
query.Append($" and member_visibility = {(int) opts.PrivacyFilter}");
}
if (opts.Search != null)
{
private async Task ExecuteProxy(IPKConnection conn, DiscordMessage trigger, MessageContext ctx,
ProxyMatch match, bool allowEveryone, bool allowEmbeds)
{
// Send the webhook
var content = match.ProxyContent;
if (!allowEmbeds)
{
content = content.BreakLinkEmbeds();
}
var id = await _webhookExecutor.ExecuteWebhook(trigger.Channel, match.Member.ProxyName(ctx),
match.Member.ProxyAvatar(ctx),
content, trigger.Attachments, allowEveryone);
Task SaveMessage() => _data.AddMessage(conn, trigger.Author.Id, trigger.Channel.GuildId, trigger.Channel.Id, id, trigger.Id, match.Member.Id);
Task LogMessage() => _logChannel.LogMessage(ctx, match, trigger, id).AsTask();
async Task DeleteMessage()
{
// Wait a second or so before deleting the original message
await Task.Delay(MessageDeletionDelay);
try
{
await trigger.DeleteAsync();
}
catch (NotFoundException)
{
// If it's already deleted, we just log and swallow the exception
_logger.Warning("Attempted to delete already deleted proxy trigger message {Message}", trigger.Id);
}
}
// Run post-proxy actions (simultaneously; order doesn't matter)
// Note that only AddMessage is using our passed-in connection, careful not to pass it elsewhere and run into conflicts
await Task.WhenAll(
DeleteMessage(),
SaveMessage(),
LogMessage()
);
}
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 async Task EditSwitch(IPKConnection conn, SwitchId switchId, IReadOnlyCollection <MemberId> members)
{
// Use a transaction here since we're doing multiple executed commands in one
await using var tx = await conn.BeginTransactionAsync();
// Remove the old members from the switch
await conn.ExecuteAsync("delete from switch_members where switch = @Switch",
new { Switch = switchId });
// Add the new members
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(switchId.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();
_ = _dispatch.Dispatch(switchId, new UpdateDispatchData
{
Event = DispatchEvent.UPDATE_SWITCH,
EventData = JObject.FromObject(new
{
members = await GetMemberGuids(members),
}),
});
_logger.Information("Updated {SwitchId} members: {Members}", switchId, members);
}
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 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 static Task <GuildConfig> QueryOrInsertGuildConfig(this IPKConnection conn, ulong guild) =>
conn.QueryFirstAsync <GuildConfig>("insert into servers (id) values (@guild) on conflict (id) do update set id = @guild returning *", new { guild });
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 <PKGroup?> QueryGroupByHid(this IPKConnection conn, string hid) =>
conn.QueryFirstOrDefaultAsync <PKGroup?>("select * from groups where hid = @hid", new { hid = hid.ToLowerInvariant() });
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 static Task <PKMember?> QueryMember(this IPKConnection conn, MemberId id) =>
conn.QueryFirstOrDefaultAsync <PKMember?>("select * from members where id = @id", new { 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 static Task <PKSystem?> QuerySystem(this IPKConnection conn, SystemId id) =>
conn.QueryFirstOrDefaultAsync <PKSystem?>("select * from systems where id = @id", new { 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 Task <int> DeleteCommandMessagesBefore(IPKConnection conn, ulong messageIdThreshold) =>
conn.ExecuteAsync("delete from command_messages where message_id < @Threshold",
new { Threshold = messageIdThreshold });
public async Task <CommandMessage> GetCommandMessage(IPKConnection conn, ulong messageId)
{
return(await _repo.GetCommandMessage(conn, messageId));
}
private BulkImporter(SystemId systemId, IPKConnection conn, IPKTransaction tx)
{
_systemId = systemId;
_conn = conn;
_tx = tx;
}
public static Task <IEnumerable <SystemFronter> > QueryCurrentFronters(this IPKConnection conn, SystemId system) =>
conn.QueryAsync <SystemFronter>("select * from system_fronters where system = @system", new { system });
public Task <CommandMessage> GetCommandMessage(IPKConnection conn, ulong messageId) =>
conn.QuerySingleOrDefaultAsync <CommandMessage>("select * from command_messages where message_id = @Message",
new { Message = messageId });
