public async Task Begin() { // Fetch all members in the system and log their names and hids var members = await _conn.QueryAsync <PKMember>("select id, hid, name from members where system = @System", new { System = _systemId }); foreach (var m in members) { _existingMembersByHid[m.Hid] = m; _existingMembersByName[m.Name] = m; } }
public async Task <IEnumerable <SwitchListEntry> > GetPeriodFronters(IPKConnection conn, SystemId system, Instant periodStart, Instant periodEnd) { // TODO: IAsyncEnumerable-ify this one // TODO: this doesn't belong in the repo // Returns the timestamps and member IDs of switches overlapping the range, in chronological (newest first) order var switchMembers = await GetSwitchMembersList(conn, system, periodStart, periodEnd).ToListAsync(); // query DB for all members involved in any of the switches above and collect into a dictionary for future use // this makes sure the return list has the same instances of PKMember throughout, which is important for the dictionary // key used in GetPerMemberSwitchDuration below var membersList = await conn.QueryAsync <PKMember>( "select * from members where id = any(@Switches)", // lol postgres specific `= any()` syntax new { Switches = switchMembers.Select(m => m.Member.Value).Distinct().ToList() }); var memberObjects = membersList.ToDictionary(m => m.Id); // Initialize entries - still need to loop to determine the TimespanEnd below var entries = from item in switchMembers group item by item.Timestamp into g select new SwitchListEntry { TimespanStart = g.Key, Members = g.Where(x => x.Member != default(MemberId)).Select(x => memberObjects[x.Member]) .ToList() }; // Loop through every switch that overlaps the range and add it to the output list // end time is the *FOLLOWING* switch's timestamp - we cheat by working backwards from the range end, so no dates need to be compared var endTime = periodEnd; var outList = new List <SwitchListEntry>(); foreach (var e in entries) { // Override the start time of the switch if it's outside the range (only true for the "out of range" switch we included above) var switchStartClamped = e.TimespanStart < periodStart ? periodStart : e.TimespanStart; outList.Add(new SwitchListEntry { Members = e.Members, TimespanStart = switchStartClamped, TimespanEnd = endTime }); // next switch's end is this switch's start (we're working backward in time) endTime = e.TimespanStart; } return(outList); }
public async Task <FullMessage> GetMessage(IPKConnection conn, ulong id) { FullMessage Mapper(PKMessage msg, PKMember member, PKSystem system) => new FullMessage { Message = msg, System = system, Member = member }; var result = await conn.QueryAsync <PKMessage, PKMember, PKSystem, FullMessage>( "select messages.*, members.*, systems.* from messages, members, systems where (mid = @Id or original_mid = @Id) and messages.member = members.id and systems.id = members.system", Mapper, new { Id = id }); return(result.FirstOrDefault()); }
// todo: add a Mapper to QuerySingle and move this to SqlKata public async Task <FullMessage?> GetMessage(IPKConnection conn, ulong id) { FullMessage Mapper(PKMessage msg, PKMember member, PKSystem system) => new() { Message = msg, System = system, Member = member }; var query = "select * from messages" + " left join members on messages.member = members.id" + " left join systems on members.system = systems.id" + " where (mid = @Id or original_mid = @Id)"; var result = await conn.QueryAsync <PKMessage, PKMember, PKSystem, FullMessage>( query, Mapper, new { Id = id }); return(result.FirstOrDefault()); }
public static Task <IEnumerable <SystemFronter> > QueryCurrentFronters(this IPKConnection conn, SystemId system) => conn.QueryAsync <SystemFronter>("select * from system_fronters where system = @system", new { system });
public static Task <IEnumerable <PKGroup> > QueryMemberGroups(this IPKConnection conn, MemberId id) => conn.QueryAsync <PKGroup>( "select groups.* from group_members inner join groups on group_members.group_id = groups.id where group_members.member_id = @Id", new { Id = id });
public static Task <IEnumerable <ulong> > GetLinkedAccounts(this IPKConnection conn, SystemId id) => conn.QueryAsync <ulong>("select uid from accounts where system = @Id", new { Id = id });
public Task SaveCommandMessage(IPKConnection conn, ulong messageId, ulong authorId) => conn.QueryAsync("insert into command_messages (message_id, author_id) values (@Message, @Author)", new { Message = messageId, Author = authorId });
public static Task <IEnumerable <ProxyMember> > QueryProxyMembers(this IPKConnection conn, ulong account, ulong guild) { return(conn.QueryAsync <ProxyMember>("proxy_members", new { account_id = account, guild_id = guild }, commandType: CommandType.StoredProcedure)); }
public Task <IEnumerable <ulong> > GetSystemAccounts(IPKConnection conn, SystemId system) => conn.QueryAsync <ulong>("select uid from accounts where system = @Id", new { Id = system });
public Task <IEnumerable <PKShardInfo> > GetShards(IPKConnection conn) => conn.QueryAsync <PKShardInfo>("select * from shards");
public static Task <IEnumerable <ListedGroup> > QueryGroupList(this IPKConnection conn, SystemId system) => conn.QueryAsync <ListedGroup>("select * from group_list where system = @System", new { System = system });