def channels_listall(token: str) -> dict: """ Provide a list of all channels (and their associated details) :param token: user's token :return: dictionary of list of dictionary with keys 'channel_id' and 'name' which includes all the channels """ #token_operation = TokenJwt() # check if the token is valid if check_valid(token) is False: raise AccessError(description='error occurred: token is not valid') dictionary = { 'channels': [], } # get info for all list db_connect = DbConnector() db_connect.cursor() sql = "SELECT channel_id, name FROM project.channel_data" db_connect.execute(sql) ret = db_connect.fetchall() for channel in ret: dictionary['channels'].append({ 'channel_id': channel[0], 'name': channel[1] }) # close database connection db_connect.close() return dictionary
def channels_list(token: str) -> dict: """ Provide a list of all channels (and their associated details) that the authorised user is part of :param token: user's token :return: dictionary of list of dictionary with keys 'channel_id' and 'name' which includes the channels that the user is a part of """ dictionary = { 'channels': [], } token_operation = TokenJwt() # check if the token is valid if check_valid(token) is False: raise AccessError(description='error occurred: token is not valid') # get user's u_id from token u_id = token_operation.get_uid(token) # get info in database db_connect = DbConnector() db_connect.cursor() sql = ''' SELECT channel_id, name FROM project.channel_data c INNER JOIN project.user_data u ON u.u_id = ANY (c.member) WHERE u.u_id = (%s); ''' value = (u_id, ) db_connect.execute(sql, value) ret = db_connect.fetchall() for channel in ret: dictionary["channels"].append({ 'channel_id': channel[0], 'name': channel[1] }) db_connect.close() return dictionary
def channel_details(token: str, channel_id: int) -> dict: """ Given a Channel with ID channel_id that the authorised user is part of, provide basic details about the channel :param token: user's token :param channel_id: channel's id :return: a dictionary with keys 'name', 'owner_members' and 'all_members' """ token_operation = TokenJwt() # check if the token is valid if check_valid(token) is False: raise AccessError(description='error occurred: token is not valid') # get the user's u_id from token u_id = token_operation.get_uid(token) # get info for the channel db_connect = DbConnector() db_connect.cursor() sql = "SELECT * FROM project.channel_data WHERE channel_id = (%s)" value = (channel_id, ) db_connect.execute(sql, value) ret = db_connect.fetchall() # check if channel id invalid if len(ret) == 0: raise InputError( description='error occurred: the channel ID is not a valid channel' ) channel_name = ret[0][1] member_list = ret[0][2] owner_list = ret[0][3] # check if the authorised user is member of this channel if u_id not in member_list: raise AccessError(description='error occurred: the authorised user ' 'is not a member of channel with this channel_id') # get channel member basic information db_connect.cursor() sql = ''' DROP TABLE IF EXISTS project.detail_data; CREATE TABLE project.detail_data ( id serial NOT NULL, u_id int ); INSERT INTO project.detail_data(u_id) select unnest(( SELECT member FROM project.channel_data WHERE channel_id = (%s))); SELECT u.u_id, name_last, name_first, profile_img_url FROM project.user_data u INNER JOIN project.detail_data d ON u.u_id = d.u_id ORDER BY d.id; ''' value = (channel_id, ) db_connect.execute(sql, value) ret = db_connect.fetchall() all_members = [] # get member details for detail in ret: all_members.append({ 'u_id': detail[0], 'name_last': detail[1], 'name_first': detail[2], 'profile_img_url': detail[3] }) # get owner details owner_members = [] for member in all_members: if member['u_id'] in owner_list: owner_members.append(member) # close database connection db_connect.close() details = { 'name': channel_name, 'owner_members': owner_members, 'all_members': all_members } return details
def channel_messages(token: str, channel_id: int, start: int): """ Given a Channel with ID channel_id that the authorised user is part of, return up to 50 messages between index "start" and "start + 50" :param token: the authorised user's token :param channel_id: the channel ID :param start: the start number :return: dictionary of messages as required """ token_operation = TokenJwt() # check if the token is valid if check_valid(token) is False: raise AccessError(description='error occurred: token is not valid') # start = int(start) # channel_id = int(channel_id) db_connect = DbConnector() db_connect.cursor() sql = "SELECT member FROM project.channel_data WHERE channel_id=(%s)" value = (channel_id, ) db_connect.execute(sql, value) ret = db_connect.fetchone() # check channel_id is valid if ret is None: raise InputError(description='error occurred: channel id is not valid') member_list = ret[0] # get user's u_id from token u_id = token_operation.get_uid(token) # check u_id is a member for the channel if u_id not in member_list: raise AccessError( description='Authorised user is not a member of channel') # check start valid sql = "SELECT COUNT(*) FROM project.message_data WHERE channel_id=(%s)" value = (channel_id, ) db_connect.execute(sql, value) ret = db_connect.fetchone() total_message = ret[0] # start is greater than the total number of messages if start > total_message: raise InputError(description='error occurred: start is greater than ' 'the total number of messages') # determine end retuen_end = -1 if total_message > start + 50: end = start + 50 retuen_end = end else: end = total_message # store all the required messages msg = [] sql = "SELECT * FROM project.message_data WHERE channel_id=(%s) ORDER BY time_created DESC" value = (channel_id, ) db_connect.execute(sql, value) ret = db_connect.fetchall() for detail in ret: react_uid = detail[6] if u_id in react_uid: react_cond = True else: react_cond = False msg.append({ 'message_id': detail[0], 'channel_id': detail[1], 'time_created': detail[3], 'u_id': detail[4], 'message': detail[2], 'is_pinned': detail[5], 'reacts': [{ 'is_this_user_reacted': react_cond, 'react_id': 1, 'u_ids': react_uid }] }) # close database connection db_connect.close() return {'messages': msg, 'start': start, 'end': retuen_end}