public async Task Store(ulong senderId, ulong messageId, ulong channelId, ulong originalMessage, PKMember member)
{
using (var conn = await _conn.Obtain())
await conn.ExecuteAsync("insert into messages(mid, channel, member, sender, original_mid) values(@MessageId, @ChannelId, @MemberId, @SenderId, @OriginalMid)", new {
MessageId = messageId,
ChannelId = channelId,
MemberId = member.Id,
SenderId = senderId,
OriginalMid = originalMessage
});
_logger.Information("Stored message {Message} in channel {Channel}", messageId, channelId);
}
public async Task RegisterSwitch(PKSystem system, ICollection <PKMember> members)
{
// Use a transaction here since we're doing multiple executed commands in one
using (var conn = await _conn.Obtain())
using (var tx = conn.BeginTransaction())
{
// First, we insert the switch itself
var sw = await conn.QuerySingleAsync <PKSwitch>("insert into switches(system) values (@System) returning *",
new { System = system.Id });
// Then we insert each member in the switch in the switch_members table
// TODO: can we parallelize this or send it in bulk somehow?
foreach (var member in members)
{
await conn.ExecuteAsync(
"insert into switch_members(switch, member) values(@Switch, @Member)",
new { Switch = sw.Id, Member = member.Id });
}
// Finally we commit the tx, since the using block will otherwise rollback it
tx.Commit();
_logger.Information("Registered switch {Switch} in system {System} with members {@Members}", sw.Id, system.Id, members.Select(m => m.Id));
}
}
public async Task <PKMember> Create(PKSystem system, string name)
{
string hid;
do
{
hid = Utils.GenerateHid();
} while (await GetByHid(hid) != null);
PKMember member;
using (var conn = await _conn.Obtain())
member = await conn.QuerySingleAsync <PKMember>("insert into members (hid, system, name) values (@Hid, @SystemId, @Name) returning *", new {
Hid = hid,
SystemID = system.Id,
Name = name
});
_logger.Information("Created member {Member}", member.Id);
return(member);
}
private async Task ApplyMigration(int migrationId)
{
// migrationId is the *target* version
using var conn = await _conn.Obtain();
using var tx = conn.BeginTransaction();
// See if we even have the info table... if not, we implicitly define the version as -1
// This means migration 0 will get executed, which ensures we're at a consistent state.
// *Technically* this also means schema version 0 will be identified as -1, but since we're only doing these
// checks in the above for loop, this doesn't matter.
var hasInfoTable = await conn.QuerySingleOrDefaultAsync <int>("select count(*) from information_schema.tables where table_name = 'info'") == 1;
int currentVersion;
if (hasInfoTable)
{
currentVersion = await conn.QuerySingleOrDefaultAsync <int>("select schema_version from info");
}
else
{
currentVersion = -1;
}
if (currentVersion >= migrationId)
{
return; // Don't execute the migration if we're already at the target version.
}
using var stream = typeof(SchemaService).Assembly.GetManifestResourceStream($"PluralKit.Core.Migrations.{migrationId}.sql");
if (stream == null)
{
throw new ArgumentException("Invalid migration ID");
}
using var reader = new StreamReader(stream);
var migrationQuery = await reader.ReadToEndAsync();
_logger.Information("Current schema version is {CurrentVersion}, applying migration {MigrationId}", currentVersion, migrationId);
await conn.ExecuteAsync(migrationQuery, transaction : tx);
tx.Commit();
// If the above migration 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.
// TODO: find a way to get around the cast to our internal tracker wrapper... this could break if that ever changes
((PerformanceTrackingConnection)conn)._impl.ReloadTypes();
}
private async Task ApplyMigration(int migrationId)
{
// migrationId is the *target* version
using var conn = await _conn.Obtain();
using var tx = conn.BeginTransaction();
// See if we even have the info table... if not, we implicitly define the version as -1
// This means migration 0 will get executed, which ensures we're at a consistent state.
// *Technically* this also means schema version 0 will be identified as -1, but since we're only doing these
// checks in the above for loop, this doesn't matter.
var hasInfoTable = await conn.QuerySingleOrDefaultAsync <int>("select count(*) from information_schema.tables where table_name = 'info'") == 1;
int currentVersion;
if (hasInfoTable)
{
currentVersion = await conn.QuerySingleOrDefaultAsync <int>("select schema_version from info");
}
else
{
currentVersion = -1;
}
if (currentVersion >= migrationId)
{
return; // Don't execute the migration if we're already at the target version.
}
using var stream = typeof(SchemaService).Assembly.GetManifestResourceStream($"PluralKit.Core.Migrations.{migrationId}.sql");
if (stream == null)
{
throw new ArgumentException("Invalid migration ID");
}
using var reader = new StreamReader(stream);
var migrationQuery = await reader.ReadToEndAsync();
_logger.Information("Current schema version is {CurrentVersion}, applying migration {MigrationId}", currentVersion, migrationId);
await conn.ExecuteAsync(migrationQuery, transaction : tx);
tx.Commit();
}
public async Task <PKSystem> Create(string systemName = null)
{
string hid;
do
{
hid = Utils.GenerateHid();
} while (await GetByHid(hid) != null);
PKSystem system;
using (var conn = await _conn.Obtain())
system = await conn.QuerySingleAsync <PKSystem>("insert into systems (hid, name) values (@Hid, @Name) returning *", new { Hid = hid, Name = systemName });
_logger.Information("Created system {System}", system.Id);
return(system);
}
public async Task <IEnumerable <PKMember> > GetConflictingProxies(PKSystem system, ProxyTag tag)
{
using (var conn = await _conn.Obtain())
// return await conn.QueryAsync<PKMember>("select * from (select *, (unnest(proxy_tags)).prefix as prefix, (unnest(proxy_tags)).suffix as suffix from members where system = @System) as _ where prefix ilike @Prefix and suffix ilike @Suffix", new
// {
// System = system.Id,
// Prefix = tag.Prefix.Replace("%", "\\%") + "%",
// Suffix = "%" + tag.Suffix.Replace("%", "\\%")
// });
return(await conn.QueryAsync <PKMember>("select * from (select *, (unnest(proxy_tags)).prefix as prefix, (unnest(proxy_tags)).suffix as suffix from members where system = @System) as _ where prefix = @Prefix and suffix = @Suffix", new
{
System = system.Id,
Prefix = tag.Prefix,
Suffix = tag.Suffix
}));
}
