public async Task <IEnumerable <SwitchMembersListEntry> > GetSwitchMembersList(PKSystem system, Instant start, Instant end)
{
// Wrap multiple commands in a single transaction for performance
using (var conn = await _conn.Obtain())
using (var tx = conn.BeginTransaction())
{
// 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.Id, Start = start });
// Then collect the time and members of all switches that overlap the range
var switchMembersEntries = await conn.QueryAsync <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.Id, Start = start, End = end, LastSwitch = lastSwitch });
// Commit and return the list
tx.Commit();
return(switchMembersEntries);
}
}
public async Task <AuxillaryProxyInformation> GetAuxillaryProxyInformation(ulong guild, PKSystem system, PKMember member)
{
using var conn = await _conn.Obtain();
var args = new { Guild = guild, System = system.Id, Member = member.Id };
var multi = await conn.QueryMultipleAsync(@"
select servers.* from servers where id = @Guild;
select * from system_guild where guild = @Guild and system = @System;
select * from member_guild where guild = @Guild and member = @Member", args);
return(new AuxillaryProxyInformation
{
Guild = (await multi.ReadSingleOrDefaultAsync <DatabaseCompatibleGuildConfig>()).Into(),
SystemGuild = await multi.ReadSingleOrDefaultAsync <SystemGuildSettings>() ?? new SystemGuildSettings(),
MemberGuild = await multi.ReadSingleOrDefaultAsync <MemberGuildSettings>() ?? new MemberGuildSettings()
});
}
public async Task AddSwitchesBulk(PKSystem system, IEnumerable <ImportedSwitch> switches)
{
// Read existing switches to enforce unique timestamps
var priorSwitches = await GetSwitches(system);
var lastSwitchId = priorSwitches.Any()
? priorSwitches.Max(x => x.Id)
: 0;
using (var conn = (PerformanceTrackingConnection)await _conn.Obtain())
{
using (var tx = conn.BeginTransaction())
{
// Import switches in bulk
using (var importer = conn.BeginBinaryImport("COPY switches (system, timestamp) FROM STDIN (FORMAT BINARY)"))
{
foreach (var sw in switches)
{
// If there's already a switch at this time, move on
if (priorSwitches.Any(x => x.Timestamp.Equals(sw.Timestamp)))
{
continue;
}
// Otherwise, add it to the importer
importer.StartRow();
importer.Write(system.Id, NpgsqlTypes.NpgsqlDbType.Integer);
importer.Write(sw.Timestamp, NpgsqlTypes.NpgsqlDbType.Timestamp);
}
importer.Complete(); // Commits the copy operation so dispose won't roll it back
}
// Get all switches that were created above and don't have members for ID lookup
var switchesWithoutMembers =
await conn.QueryAsync <PKSwitch>(@"
SELECT switches.*
FROM switches
LEFT JOIN switch_members
ON switch_members.switch = switches.id
WHERE switches.id > @LastSwitchId
AND switches.system = @System
AND switch_members.id IS NULL", new { LastSwitchId = lastSwitchId, System = system.Id });
// Import switch_members in bulk
using (var importer = conn.BeginBinaryImport("COPY switch_members (switch, member) FROM STDIN (FORMAT BINARY)"))
{
// Iterate over the switches we created above and set their members
foreach (var pkSwitch in switchesWithoutMembers)
{
// If this isn't in our import set, move on
var sw = switches.Select(x => (ImportedSwitch?)x).FirstOrDefault(x => x.Value.Timestamp.Equals(pkSwitch.Timestamp));
if (sw == null)
{
continue;
}
// Loop through associated members to add each to the switch
foreach (var m in sw.Value.Members)
{
// Skip switch-outs - these don't have switch_members
if (m == null)
{
continue;
}
importer.StartRow();
importer.Write(pkSwitch.Id, NpgsqlTypes.NpgsqlDbType.Integer);
importer.Write(m.Id, NpgsqlTypes.NpgsqlDbType.Integer);
}
}
importer.Complete(); // Commits the copy operation so dispose won't roll it back
}
tx.Commit();
}
}
_logger.Information("Completed bulk import of switches for system {0}", system.Hid);
}
public async Task <IEnumerable <MemberMessageCount> > GetMemberMessageCountBulk(PKSystem system)
{
using (var conn = await _conn.Obtain())
return(await conn.QueryAsync <MemberMessageCount>(
@"SELECT messages.member, COUNT(messages.member) messagecount
FROM members
JOIN messages
ON members.id = messages.member
WHERE members.system = @System
GROUP BY messages.member",
new { System = system.Id }));
}
public async Task <int> GetSystemMemberCount(PKSystem system)
{
using (var conn = await _conn.Obtain())
return(await conn.ExecuteScalarAsync <int>("select count(*) from members where system = @Id", system));
}
public async Task <IEnumerable <PKMember> > GetSystemMembers(PKSystem system)
{
using (var conn = await _conn.Obtain())
return(await conn.QueryAsync <PKMember>("select * from members where system = @SystemID", new { SystemID = system.Id }));
}
public async Task <PKMember> GetMemberByName(PKSystem system, string name)
{
// QueryFirst, since members can (in rare cases) share names
using (var conn = await _conn.Obtain())
return(await conn.QueryFirstOrDefaultAsync <PKMember>("select * from members where lower(name) = lower(@Name) and system = @SystemID", new { Name = name, SystemID = system.Id }));
}
public async Task DeleteAllSwitches(PKSystem system)
{
using (var conn = await _conn.Obtain())
await conn.ExecuteAsync("delete from switches where system = @Id", system);
}
public async Task <IEnumerable <ulong> > GetSystemAccounts(PKSystem system)
{
using (var conn = await _conn.Obtain())
return(await conn.QueryAsync <ulong>("select uid from accounts where system = @Id", new { Id = system.Id }));
}
public async Task DeleteSystem(PKSystem system)
{
using (var conn = await _conn.Obtain())
await conn.ExecuteAsync("delete from systems where id = @Id", system);
_logger.Information("Deleted system {System}", system.Id);
}