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 });