public async Task 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);
}
public static async Task <BulkImporter> Begin(PKSystem system, IPKConnection conn)
{
var tx = await conn.BeginTransactionAsync();
var importer = new BulkImporter(system.Id, conn, tx);
await importer.Begin();
return(importer);
}
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();
}
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 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 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*
}
