public static Task <PKMember> UpdateMember(this IPKConnection conn, MemberId id, MemberPatch patch)
{
var(query, pms) = patch.Apply(UpdateQueryBuilder.Update("members", "id = @id"))
.WithConstant("id", id)
.Build("returning *");
return(conn.QueryFirstAsync <PKMember>(query, pms));
}
public async Task <PKMember> AddMember(string identifier, PKMember member)
{
// See if we can find a member that matches this one
// if not, roll a new hid and we'll insert one with that
// (we can't trust the hid given in the member, it might let us overwrite another system's members)
var existingMember = FindExistingMemberInSystem(member.Hid, member.Name);
string newHid = existingMember?.Hid ?? await _conn.QuerySingleAsync <string>("find_free_member_hid", commandType : CommandType.StoredProcedure);
// Upsert member data and return the ID
QueryBuilder qb = QueryBuilder.Upsert("members", "hid")
.Constant("hid", "@Hid")
.Constant("system", "@System")
.Variable("name", "@Name")
.Variable("keep_proxy", "@KeepProxy");
if (member.DisplayName != null)
{
qb.Variable("display_name", "@DisplayName");
}
if (member.Description != null)
{
qb.Variable("description", "@Description");
}
if (member.Color != null)
{
qb.Variable("color", "@Color");
}
if (member.AvatarUrl != null)
{
qb.Variable("avatar_url", "@AvatarUrl");
}
if (member.ProxyTags != null)
{
qb.Variable("proxy_tags", "@ProxyTags");
}
if (member.Birthday != null)
{
qb.Variable("birthday", "@Birthday");
}
var newMember = await _conn.QueryFirstAsync <PKMember>(qb.Build("returning *"),
new
{
Hid = newHid,
System = _systemId,
member.Name,
member.DisplayName,
member.Description,
member.Color,
member.AvatarUrl,
member.KeepProxy,
member.ProxyTags,
member.Birthday
});
// Log this member ID by the given identifier
_knownMembers[identifier] = newMember.Id;
return(newMember);
}
public Task <PKMember> UpdateMember(IPKConnection conn, MemberId id, MemberPatch patch, IDbTransaction?transaction = null)
{
_logger.Information("Updated {MemberId}: {@MemberPatch}", id, patch);
var(query, pms) = patch.Apply(UpdateQueryBuilder.Update("members", "id = @id"))
.WithConstant("id", id)
.Build("returning *");
return(conn.QueryFirstAsync <PKMember>(query, pms, transaction));
}
public Task <PKSystem> UpdateSystem(IPKConnection conn, SystemId id, SystemPatch patch)
{
_logger.Information("Updated {SystemId}: {@SystemPatch}", id, patch);
var(query, pms) = patch.Apply(UpdateQueryBuilder.Update("systems", "id = @id"))
.WithConstant("id", id)
.Build("returning *");
return(conn.QueryFirstAsync <PKSystem>(query, pms));
}
public Task <PKGroup> UpdateGroup(IPKConnection conn, GroupId id, GroupPatch patch)
{
_logger.Information("Updated {GroupId}: {@GroupPatch}", id, patch);
var(query, pms) = patch.Apply(UpdateQueryBuilder.Update("groups", "id = @id"))
.WithConstant("id", id)
.Build("returning *");
return(conn.QueryFirstAsync <PKGroup>(query, pms));
}
public async Task <PKGroup> CreateGroup(IPKConnection conn, SystemId system, string name)
{
var group = await conn.QueryFirstAsync <PKGroup>(
"insert into groups (hid, system, name) values (find_free_group_hid(), @System, @Name) returning *",
new { System = system, Name = name });
_logger.Information("Created group {GroupId} in system {SystemId}: {GroupName}", group.Id, system, name);
return(group);
}
public async Task <PKMember> CreateMember(IPKConnection conn, SystemId id, string memberName, IDbTransaction?transaction = null)
{
var member = await conn.QueryFirstAsync <PKMember>(
"insert into members (hid, system, name) values (find_free_member_hid(), @SystemId, @Name) returning *",
new { SystemId = id, Name = memberName }, transaction);
_logger.Information("Created {MemberId} in {SystemId}: {MemberName}",
member.Id, id, memberName);
return(member);
}
public static Task <MemberGuildSettings> QueryOrInsertMemberGuildConfig(
this IPKConnection conn, ulong guild, MemberId member) =>
conn.QueryFirstAsync <MemberGuildSettings>(
"insert into member_guild (guild, member) values (@guild, @member) on conflict (guild, member) do update set guild = @guild, member = @member returning *",
new { guild, member });
public static Task <SystemGuildSettings> QueryOrInsertSystemGuildConfig(this IPKConnection conn, ulong guild, SystemId system) =>
conn.QueryFirstAsync <SystemGuildSettings>(
"insert into system_guild (guild, system) values (@guild, @system) on conflict (guild, system) do update set guild = @guild, system = @system returning *",
new { guild, system });
public static Task <GuildConfig> QueryOrInsertGuildConfig(this IPKConnection conn, ulong guild) =>
conn.QueryFirstAsync <GuildConfig>("insert into servers (id) values (@guild) on conflict (id) do update set id = @guild returning *", new { guild });
public async Task <PKMember> AddMember(string identifier, string potentialHid, string potentialName, MemberPatch patch)
{
// See if we can find a member that matches this one
// if not, roll a new hid and we'll insert one with that
// (we can't trust the hid given in the member, it might let us overwrite another system's members)
var existingMember = FindExistingMemberInSystem(potentialHid, potentialName);
string newHid = existingMember?.Hid ?? await _conn.QuerySingleAsync <string>("find_free_member_hid", commandType : CommandType.StoredProcedure);
// Upsert member data and return the ID
QueryBuilder qb = QueryBuilder.Upsert("members", "hid")
.Constant("hid", "@Hid")
.Constant("system", "@System");
if (patch.Name.IsPresent)
{
qb.Variable("name", "@Name");
}
if (patch.DisplayName.IsPresent)
{
qb.Variable("display_name", "@DisplayName");
}
if (patch.Description.IsPresent)
{
qb.Variable("description", "@Description");
}
if (patch.Pronouns.IsPresent)
{
qb.Variable("pronouns", "@Pronouns");
}
if (patch.Color.IsPresent)
{
qb.Variable("color", "@Color");
}
if (patch.AvatarUrl.IsPresent)
{
qb.Variable("avatar_url", "@AvatarUrl");
}
if (patch.ProxyTags.IsPresent)
{
qb.Variable("proxy_tags", "@ProxyTags");
}
if (patch.Birthday.IsPresent)
{
qb.Variable("birthday", "@Birthday");
}
if (patch.KeepProxy.IsPresent)
{
qb.Variable("keep_proxy", "@KeepProxy");
}
// don't overwrite message count on existing members
if (existingMember == null)
{
if (patch.MessageCount.IsPresent)
{
qb.Variable("message_count", "@MessageCount");
}
}
var newMember = await _conn.QueryFirstAsync <PKMember>(qb.Build("returning *"),
new
{
Hid = newHid,
System = _systemId,
Name = patch.Name.Value,
DisplayName = patch.DisplayName.Value,
Description = patch.Description.Value,
Pronouns = patch.Pronouns.Value,
Color = patch.Color.Value,
AvatarUrl = patch.AvatarUrl.Value,
KeepProxy = patch.KeepProxy.Value,
ProxyTags = patch.ProxyTags.Value,
Birthday = patch.Birthday.Value,
MessageCount = patch.MessageCount.Value,
});
// Log this member ID by the given identifier
_knownMembers[identifier] = newMember.Id;
return(newMember);
}
public static Task <PKGroup> CreateGroup(this IPKConnection conn, SystemId system, string name) =>
conn.QueryFirstAsync <PKGroup>(
"insert into groups (hid, system, name) values (find_free_group_hid(), @System, @Name) returning *",
new { System = system, Name = name });
public static Task <PKMember> CreateMember(this IPKConnection conn, SystemId system, string memberName) =>
conn.QueryFirstAsync <PKMember>(
"insert into members (hid, system, name) values (find_free_member_hid(), @SystemId, @Name) returning *",
new { SystemId = system, Name = memberName });
public static Task <MessageContext> QueryMessageContext(this IPKConnection conn, ulong account, ulong guild, ulong channel)
{
return(conn.QueryFirstAsync <MessageContext>("message_context",
new { account_id = account, guild_id = guild, channel_id = channel },
commandType: CommandType.StoredProcedure));
}
