public async Task MoveSwitch(IPKConnection conn, SwitchId id, Instant time)
{
await conn.ExecuteAsync("update switches set timestamp = @Time where id = @Id",
new { Time = time, Id = id });
_logger.Information("Updated {SwitchId} timestamp: {SwitchTimestamp}", id, time);
}
public async Task AddMessage(IPKConnection conn, PKMessage msg)
{
// "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(@Mid, @Guild, @Channel, @Member, @Sender, @OriginalMid) on conflict do nothing", msg);
_logger.Debug("Stored message {@StoredMessage} in channel {Channel}", msg, msg.Channel);
}
public static Task UpsertGuild(this IPKConnection conn, ulong guild, GuildPatch patch)
{
var(query, pms) = patch.Apply(UpdateQueryBuilder.Upsert("servers", "id"))
.WithConstant("id", guild)
.Build();
return(conn.ExecuteAsync(query, pms));
}
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 Task RemoveMembersFromGroup(IPKConnection conn, GroupId group,
IReadOnlyCollection <MemberId> members)
{
_logger.Information("Removed members from {GroupId}: {MemberIds}", group, members);
return(conn.ExecuteAsync("delete from group_members where group_id = @Group and member_id = any(@Members)",
new { Group = @group, Members = members.ToArray() }));
}
public Task UpsertGuild(IPKConnection conn, ulong guild, GuildPatch patch)
{
_logger.Information("Updated guild {GuildId}: {@GuildPatch}", guild, patch);
var(query, pms) = patch.Apply(UpdateQueryBuilder.Upsert("servers", "id"))
.WithConstant("id", guild)
.Build();
return(conn.ExecuteAsync(query, pms));
}
public async Task UpdateAccount(IPKConnection conn, ulong id, AccountPatch patch)
{
_logger.Information("Updated account {accountId}: {@AccountPatch}", id, patch);
var(query, pms) = patch.Apply(UpdateQueryBuilder.Update("accounts", "uid = @uid"))
.WithConstant("uid", id)
.Build();
await conn.ExecuteAsync(query, pms);
}
public static Task UpsertMemberGuild(this IPKConnection conn, MemberId member, ulong guild,
MemberGuildPatch patch)
{
var(query, pms) = patch.Apply(UpdateQueryBuilder.Upsert("member_guild", "member, guild"))
.WithConstant("member", member)
.WithConstant("guild", guild)
.Build();
return(conn.ExecuteAsync(query, pms));
}
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 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 async Task DeleteMessage(IPKConnection conn, ulong id)
{
var rowCount = await conn.ExecuteAsync("delete from messages where mid = @Id", new { Id = id });
if (rowCount > 0)
{
_logger.Information("Deleted message {MessageId} from database", id);
}
}
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 Task UpsertMemberGuild(IPKConnection conn, MemberId member, ulong guild,
MemberGuildPatch patch)
{
_logger.Information("Updated {MemberId} in guild {GuildId}: {@MemberGuildPatch}", member, guild, patch);
var(query, pms) = patch.Apply(UpdateQueryBuilder.Upsert("member_guild", "member, guild"))
.WithConstant("member", member)
.WithConstant("guild", guild)
.Build();
return(conn.ExecuteAsync(query, pms));
}
public async Task DeleteMessagesBulk(IPKConnection conn, IReadOnlyCollection <ulong> ids)
{
// Npgsql doesn't support ulongs in general - we hacked around it for plain ulongs but tbh not worth it for collections of ulong
// Hence we map them to single longs, which *are* supported (this is ok since they're Technically (tm) stored as signed longs in the db anyway)
var rowCount = await conn.ExecuteAsync("delete from messages where mid = any(@Ids)",
new { Ids = ids.Select(id => (long)id).ToArray() });
if (rowCount > 0)
{
_logger.Information("Bulk deleted messages ({FoundCount} found) from database: {MessageIds}", rowCount,
ids);
}
}
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);
}
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 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 Task RegisterShardHeartbeat(IPKConnection conn, int shard, Duration ping) =>
conn.ExecuteAsync(
"insert into shards (id, last_heartbeat, ping) values (@Id, now(), @Ping) on conflict (id) do update set last_heartbeat = now(), ping = @Ping",
new { Id = shard, Ping = ping.TotalSeconds });
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 Task DeleteMember(IPKConnection conn, MemberId id)
{
_logger.Information("Deleted {MemberId}", id);
return(conn.ExecuteAsync("delete from members where id = @Id", new { Id = id }));
}
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 DeleteSystem(this IPKConnection conn, SystemId id) =>
conn.ExecuteAsync("delete from systems 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 async Task DeleteAllSwitches(IPKConnection conn, SystemId system)
{
await conn.ExecuteAsync("delete from switches where system = @Id", new { Id = system });
_logger.Information("Deleted all switches in {SystemId}", system);
}
public async Task DeleteSwitch(IPKConnection conn, SwitchId id)
{
await conn.ExecuteAsync("delete from switches where id = @Id", new { Id = id });
_logger.Information("Deleted {Switch}", id);
}
public static Task DeleteGroup(this IPKConnection conn, GroupId group) =>
conn.ExecuteAsync("delete from groups where id = @Id", new { Id = group });
public static Task RemoveMembersFromGroup(this IPKConnection conn, GroupId group, IEnumerable <MemberId> members) =>
conn.ExecuteAsync("delete from group_members where group_id = @Group and member_id = any(@Members)",
new { Group = group, Members = members.ToArray() });
public Task SetShardStatus(IPKConnection conn, int shard, PKShardInfo.ShardStatus status) =>
conn.ExecuteAsync(
"insert into shards (id, status) values (@Id, @Status) on conflict (id) do update set status = @Status",
new { Id = shard, Status = status });
public Task <int> DeleteCommandMessagesBefore(IPKConnection conn, ulong messageIdThreshold) =>
conn.ExecuteAsync("delete from command_messages where message_id < @Threshold",
new { Threshold = messageIdThreshold });
public Task RegisterShardConnection(IPKConnection conn, int shard) =>
conn.ExecuteAsync(
"insert into shards (id, last_connection) values (@Id, now()) on conflict (id) do update set last_connection = now()",
new { Id = shard });