def auth_logout(token: str) -> dict: """ Given an active token, invalidates the token to log the user out. If a valid token is given, and the user is successfully logged out, it returns true, otherwise false. :param token: user's token :return: a dictionary with key 'is_success' which contains 'True' if user is successfully logged out, 'False' otherwise """ db_connect = DbConnector() db_connect.cursor() sql = 'SELECT token from project.user_data WHERE token =%s' value = (token,) db_connect.execute(sql, value) ret = db_connect.fetchone() if ret is None: logout_success = False else: sql = "UPDATE project.user_data set token=(%s) where token=(%s)" value = (None, token) db_connect.execute(sql, value) logout_success = True # close database connection db_connect.close() return { 'is_success': logout_success, }
def send_standup_message(channel_id): db_connect = DbConnector() db_connect.cursor() sql = "SELECT standup_uid, time_finish, message FROM project.active_data where channel_id=(%s)" value = (channel_id, ) db_connect.execute(sql, value) ret = db_connect.fetchone() if ret is not None: message = ret[2] if message is not None: standup_uid = ret[0] time_finish = ret[1] # send message sql = ''' INSERT INTO project.message_data (channel_id, message, time_created, u_id, is_pinned, react_uid) VALUES (%s, %s, %s, %s, %s, %s); ''' value = (channel_id, message, time_finish, standup_uid, False, []) db_connect.execute(sql, value) # remove active sql = "DELETE FROM project.active_data WHERE channel_id=(%s)" value = (channel_id, ) db_connect.execute(sql, value) # close database connection db_connect.close()
def user_profile_setname(token, name_first, name_last): # check if the token is valid if check_valid(token) is False: raise AccessError(description='error occurred: token is not valid') if len(name_first) > 50 or len( name_first) < 1: # check length of name_first raise InputError( description='error occurred: first name is not between \ 1 and 50 characters inclusively in length') if len(name_last) > 50 or len(name_last) < 1: raise InputError( description='error occurred: last name is not between \ 1 and 50 characters inclusively in length') # get user's u_id from token token_operation = TokenJwt() u_id = token_operation.get_uid(token) db_connect = DbConnector() db_connect.cursor() sql = "UPDATE project.user_data SET name_first=(%s), \ name_last=(%s) WHERE u_id=(%s)" value = (name_first, name_last, u_id) db_connect.execute(sql, value) db_connect.close() return {}
def user_profile_sethandle(token, handle_str): # check if the token is valid if check_valid(token) is False: raise AccessError(description='error occurred: token is not valid') if len(handle_str) > 20 or len(handle_str) < 3: # check handle is valid raise InputError( description= 'error occurred: handle must be between 3 and 20 characters') db_connect = DbConnector() db_connect.cursor() sql = "SELECT handle FROM project.user_data WHERE handle=(%s)" value = (handle_str, ) db_connect.execute(sql, value) ret = db_connect.fetchone() if ret is not None: raise InputError( description='error occurred: handle is already used by another user' ) # get user's u_id from token token_operation = TokenJwt() u_id = token_operation.get_uid(token) sql = "UPDATE project.user_data SET handle=(%s) WHERE u_id=(%s);" value = (handle_str, u_id) db_connect.execute(sql, value) # close database connection db_connect.close() return {}
def standup_active(token, channel_id): # check if the token is valid if check_valid(token) is False: raise AccessError(description='error occurred: token is not valid') # check channel_id is valid db_connect = DbConnector() db_connect.cursor() sql = "SELECT channel_id FROM project.channel_data WHERE channel_id=(%s)" value = (channel_id, ) db_connect.execute(sql, value) ret = db_connect.fetchone() if ret is None: raise InputError( description='error occurred: Channel ID is not a valid channel') # delete finished standup curr_time = int(time.time()) # check if standup is active sql = "SELECT time_finish FROM project.active_data WHERE channel_id=(%s);" value = (channel_id, ) db_connect.execute(sql, value) ret = db_connect.fetchone() if ret is not None: time_finish = ret[0] if int(time_finish) > curr_time: db_connect.close() return {'is_active': True, 'time_finish': time_finish} # close database connection db_connect.close() return {'is_active': False, 'time_finish': None}
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 auth_passwordreset_request(email: str) -> dict: """ Given an email address, if the user is a registered user, send's them an email containing a specific secret code. :param email: user's email :return: """ # check if email valid if not check(email): raise InputError( description='error occurred: Email entered is not a valid email') # check if entered email belongs to a registered user db_connect = DbConnector() db_connect.cursor() sql = "SELECT email from project.user_data WHERE email = (%s)" value = (email, ) db_connect.execute(sql, value) ret = db_connect.fetchone() if ret is None: raise InputError( description= 'error occurred: Email entered does not belong to a user') # generate a random reset_code reset_code = generate_code() sql = "UPDATE project.user_data set reset_code=(%s) where email=(%s)" value = (reset_code, email) db_connect.execute(sql, value) # close database connection db_connect.close() # send an email contained the reset_code to the user # config mail_host = 'XXXXXXXX' mail_user = '******' mail_pass = '******' sender = 'XXXXXXXXX' receivers = [email] # content message = MIMEText(reset_code, 'plain', 'utf-8') message['Subject'] = 'Flockr Password Reset' message['From'] = sender message['To'] = receivers[0] # connect and log on server smtp = smtplib.SMTP() smtp.connect(mail_host, 587) # use 25 if 587 doesn't work smtp.login(mail_user, mail_pass) # send smtp.sendmail(sender, receivers, message.as_string()) # log out server smtp.quit() return {}
def channel_addowner(token: str, channel_id: int, u_id: int) -> dict: """ Make user with user id u_id an owner of this channel :param token: user's token :param channel_id: channel's id :param u_id: user's id :return: an empty dictionary """ # check token_operation = TokenJwt() if check_valid(token) is False: raise AccessError(description='error occurred: token is not valid') # get authorised user's u_id from token authorised_uid = token_operation.get_uid(token) db_connect = DbConnector() db_connect.cursor() sql = "SELECT member, owner FROM project.channel_data WHERE channel_id=(%s)" value = (channel_id, ) db_connect.execute(sql, value) ret = db_connect.fetchone() # check channel is valid if ret is None: raise InputError(description='error occurred: Channel ID is not valid') member_list = ret[0] owner_list = ret[1] # user is already owner if u_id in owner_list: raise InputError( description= 'error occurred: user is already an owner of the channel') # check authorised_uid is valid if authorised_uid not in owner_list: raise AccessError( description= 'error occurred: the authorised user is not an owner of the flockr, ' 'or an owner of this channel') # update database sql = "UPDATE project.channel_data SET member=(%s), owner=(%s) WHERE channel_id=(%s)" owner_list.append(u_id) if u_id not in member_list: member_list.append(u_id) value = (member_list, owner_list, channel_id) db_connect.execute(sql, value) # close database connection db_connect.close() return {}
def user_profile_uploadphoto(token, img_url, x_start, y_start, x_end, y_end): token_operation = TokenJwt() # check if the token is valid if check_valid(token) is False: raise AccessError(description='error occurred: token is not valid') try: urllib.request.urlopen(img_url) except urllib.error.HTTPError: raise InputError(description='error occurred: \ img_url returns an HTTP status other than 200.') # check img is jpg if not img_url.lower().endswith('.jpg'): raise InputError( description='error occurred: Image uploaded is not a JPG') if not os.path.exists('./image'): os.makedirs('./image') # get user's u_id from token u_id = token_operation.get_uid(token) image_address = './image/' + str(u_id) + '.jpg' urllib.request.urlretrieve(img_url, image_address) image_object = Image.open(image_address) width, height = image_object.size if (int(x_start) < 0 or int(x_start) > width or int(x_end) < 0 or int(x_end) > width or int(y_start) < 0 or int(y_start) > height or int(y_end) < 0 or int(y_end) > height): os.remove(image_address) raise InputError(description="x_start, y_start, x_end, y_end are not \ within the dimensions of the image at the URL") if int(x_start) >= int(x_end) or int(y_start) >= int(y_end): os.remove(image_address) raise InputError(description="start value can't exceed end value") cropped = image_object.crop((x_start, y_start, x_end, y_end)) cropped.save(image_address) base_url = flask.request.host_url image_url = base_url + 'image/' + str(u_id) + '.jpg' db_connect = DbConnector() db_connect.cursor() sql = "UPDATE project.user_data SET profile_img_url=(%s) WHERE u_id=(%s)" value = (image_url, u_id) db_connect.execute(sql, value) # close database connection db_connect.close() return {}
def channel_join(token: str, channel_id: int) -> dict: """ :param token: :param channel_id: :return: """ # check token is valid token_operation = TokenJwt() if check_valid(token) is False: raise AccessError(description='error occured: token is not valid') # get user's u_id from token u_id = token_operation.get_uid(token) if not isinstance(channel_id, int): raise InputError(description='error occurred: channel ID is not valid') db_connect = DbConnector() db_connect.cursor() sql = "SELECT is_public FROM project.channel_data WHERE channel_id = (%s)" value = (channel_id, ) db_connect.execute(sql, value) ret = db_connect.fetchone() # check channel is valid if ret is None: raise InputError(description='error occurred: channel ID is not valid') if ret[0] is False and not user_is_flockr_owner(u_id): raise AccessError( description='error occurred: channel ID refers to a private channel' ) sql = "SELECT member, owner FROM project.channel_data WHERE channel_id = (%s)" value = (channel_id, ) db_connect.execute(sql, value) ret = db_connect.fetchone() member_list = ret[0] owner_list = ret[1] # update database sql = "UPDATE project.channel_data SET member=(%s), owner=(%s) WHERE channel_id=(%s)" member_list.append(u_id) # if user is flockr then become onwer if user_is_flockr_owner(u_id): owner_list.append(u_id) value = (member_list, owner_list, channel_id) db_connect.execute(sql, value) # close database connection db_connect.close() return {}
def channel_leave(token: str, channel_id: int): """ Given a channel ID, the user removed as a member of this channel :param token: user's token :param channel_id: channel's id :return: if user leave the channel successfully, it will return an empty dictionary """ # check token if valid token_operation = TokenJwt() 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) db_connect = DbConnector() db_connect.cursor() sql = "SELECT member, owner FROM project.channel_data WHERE channel_id = (%s)" value = (channel_id, ) db_connect.execute(sql, value) ret = db_connect.fetchone() # check if the channel is valid if ret is None: raise InputError( description='error occurred: the channel ID is not a valid channel' ) member_list = ret[0] owner_list = ret[1] # check if the user is valid 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') # remove user from member_list member_list.remove(u_id) # if owner, remove from owner_list if u_id in owner_list: owner_list.remove(u_id) # UPDATE database db_connect.cursor() sql = "UPDATE project.channel_data SET member=(%s),owner=(%s) WHERE channel_id=(%s)" value = (member_list, owner_list, channel_id) db_connect.execute(sql, value) # close database connection db_connect.close() return {}
def auth_login(email: str, password: str) -> dict: """ Given a registered users' email and password and generates a valid token for the user to remain authenticated :param email: the user's email :param password: the user's password :return: dictionary with keys "u_id" and "token" """ # check if email invalid if not check(email): raise InputError(description='error occurred: Email entered is not a valid email') # get user details from database db_connect = DbConnector() db_connect.cursor() sql = "SELECT u_id, password FROM project.user_data WHERE email = (%s)" value = (email,) db_connect.execute(sql, value) ret = db_connect.fetchone() # check if email do not belong to any user if ret is None: raise InputError(description='error occurred: Email entered does not belong to a user') user_uid = ret[0] user_pwd = ret[1] # password if correct hash_password = hashlib.sha256(password.encode()).hexdigest() # if user_pwd != password: if user_pwd != hash_password: raise InputError(description='error occurred: Password is not correct') # generate token token_operation = TokenJwt() user_token = token_operation.encode_token({'u_id': user_uid}) # if not login before, store the token # add token sql = "UPDATE project.user_data set token=(%s) where email=(%s)" value = (user_token, email) db_connect.execute(sql, value) # close database connection db_connect.close() return { 'u_id': user_uid, 'token': user_token }
def check_valid(token): db_connect = DbConnector() db_connect.cursor() sql = "SELECT token FROM project.user_data WHERE token=(%s)" value = (token, ) db_connect.execute(sql, value) ret = db_connect.fetchone() if ret is None: db_connect.close() return False else: db_connect.close() return True
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 auth_passwordreset_reset(reset_code: str, new_password: str) -> dict: ''' Given a reset code for a user, set that user's new password to the password provided. :param reset_code: generated random code sent to user :param new_password: user's new password to be reset :return: ''' # check if new password is valid if len(new_password) < 6: raise InputError( description='error occurred: Password entered is less ' 'than 6 characters long') # check if reset code match db_connect = DbConnector() db_connect.cursor() sql = "SELECT u_id FROM project.user_data WHERE reset_code=(%s)" value = (reset_code, ) db_connect.execute(sql, value) ret = db_connect.fetchone() if ret is None: raise InputError( description='error occurred: Reset code entered does not match') # use for sql query u_id = ret[0] # UPDATE new password and remove the reset code from database password = hashlib.sha256(new_password.encode()).hexdigest() sql = "UPDATE project.user_data set password=(%s), reset_code = (%s) WHERE u_id=(%s)" value = (password, None, u_id) db_connect.execute(sql, value) # close database connection db_connect.close() return {}
def user_profile_setemail(token, email): # check if the token is valid if check_valid(token) is False: raise AccessError(description='error occurred: token is not valid') exist_change = False regex = r'^[a-z0-9]+[\._]?[a-z0-9]+[@]\w+[.]\w{2,3}$' if re.search(regex, email): # check Email entered is valid exist_change = True if exist_change is False: raise InputError( description='error occurred: email entered is not valid') # check email address is independent db_connect = DbConnector() db_connect.cursor() sql = "SELECT email FROM project.user_data WHERE email=(%s);" value = (email, ) db_connect.execute(sql, value) ret = db_connect.fetchone() if ret is not None: raise InputError( description='error occurred: email is already used by another user' ) # get user's u_id from token token_operation = TokenJwt() u_id = token_operation.get_uid(token) sql = "UPDATE project.user_data SET email=(%s) WHERE u_id=(%s)" value = (email, u_id) db_connect.execute(sql, value) db_connect.close() return {}
def user_profile(token, u_id): # check if the token is valid if check_valid(token) is False: raise AccessError(description='error occurred: token is not valid') db_connect = DbConnector() db_connect.cursor() sql = "SELECT email, name_first, name_last, handle, \ profile_img_url FROM project.user_data WHERE u_id=(%s);" value = (u_id, ) db_connect.execute(sql, value) ret = db_connect.fetchone() # check uid is valid if ret is None: raise InputError( description='error occurred: User with u_id is not a valid user') user_dict = {} # for return email = ret[0] name_first = ret[1] name_last = ret[2] handle_str = ret[3] profile_img_url = ret[4] user_dict['u_id'] = u_id user_dict['email'] = email user_dict['name_first'] = name_first user_dict['name_last'] = name_last user_dict['handle_str'] = handle_str user_dict['profile_img_url'] = profile_img_url # close database connection db_connect.close() return {'user': user_dict}
class JobManager(object): def __init__(self): with open('jobs/job_specs.yaml', 'r') as f: self.job_types = yaml.load(f, Loader=yaml.SafeLoader)['type'] dbFilePath = os.path.join(os.path.dirname(os.path.abspath(__file__)), "../db", "job_manager.sqlite") os.makedirs(os.path.dirname(dbFilePath), exist_ok=True) self.db = DbConnector(dbFilePath, read_only=False) def register_job(self, job_info): status = STATUS_OK msg = '' job_id = None self.db.open() try: # Generate unique identifier for this job for JobManager job_id = str(uuid4()).replace("-", "") # Error and return if job type is not defined job_type = job_info['type'] if job_type in self.job_types: status = STATUS_OK else: status = STATUS_ERROR msg = 'Invalid job type' self.db.close() return status, msg job_spec = { "executable": self.job_types[job_type] ['script'], # the program to run on the execute node "output": "{}/{}.out".format( job_info['log_dir'], job_id ), # anything the job prints to standard output will end up in this file "error": "{}/{}.err".format( job_info['log_dir'], job_id ), # anything the job prints to standard error will end up in this file "log": "{}/{}.log".format( job_info['log_dir'], job_id ), # this file will contain a record of what happened to the job "env": job_info['env'], "getenv": "True", } # Add new record to job table # TODO: Ensure that the job_id is not already present in the table sql = ''' INSERT INTO `job` ( `type`, `uuid`, `cluster_id`, `status`, `time_submit`, `spec`, `msg` ) VALUES (?,?,?,?,?,?,?) ''' self.db.db_cursor.execute(sql, ( job_type, job_id, None, 'init', datetime.datetime.utcnow(), json.dumps(job_spec), '', )) except Exception as e: status = STATUS_ERROR msg = str(e).strip() self.db.close() return status, msg, job_id def init(self, job_id): status = STATUS_OK msg = '' # Get the job info from the database job_info = self.db.get_job_info(job_id) # Launch a subprocess that will monitor the data repo for the input data required for the pipeline job. # When the data arrives, launch the job using the JobManager API api_url = '{}://{}:{}{}/monitor/complete'.format( envvars.API_PROTOCOL, envvars.API_DOMAIN, envvars.API_PORT, envvars.API_BASEPATH) subprocess.Popen([ 'python', 'monitor.py', '--id', job_id, '--type', job_info['type'], '--api_url', api_url ]) return status, msg def launch(self, job_id): status = STATUS_OK msg = '' cluster_id = '' # Get the job info from the database job_info = self.db.get_job_info(job_id) # Load environment variables required for the job env = job_info['spec']['env'] executable = job_info['spec']['executable'] log_path = job_info['spec']['log'] out_path = job_info['spec']['output'] err_path = job_info['spec']['error'] for envvar in self.job_types[job_info['type']]['env']: os.environ[envvar] = '{}'.format(env[envvar]) # Create the output log directory if it does not exist os.makedirs(log_path, exist_ok=True) job_spec = { "executable": executable, # the program to run on the execute node "output": out_path, # anything the job prints to standard output will end up in this file "error": err_path, # anything the job prints to standard error will end up in this file "log": log_path, # this file will contain a record of what happened to the job "getenv": "True", } # Submit the HTCondor job htcondor_job = htcondor.Submit(job_spec) htcondor_schedd = htcondor.Schedd( ) # get the Python representation of the scheduler with htcondor_schedd.transaction( ) as txn: # open a transaction, represented by `txn` cluster_id = htcondor_job.queue( txn ) # queues one job in the current transaction; returns job's ClusterID if not isinstance(cluster_id, int): msg = 'Error submitting Condor job' status = STATUS_ERROR self.update_job(job_id, updates={ 'msg': msg, }) else: self.update_job(job_id, updates={ 'cluster_id': cluster_id, 'status': 'submitted', }) return status, msg, cluster_id def status(self, job_id): cluster_id = self.get_cluster_id(job_id) schedd = htcondor.Schedd() # First search the jobs currently in queue (condor_q) attr_list = [ 'ClusterId', 'JobStatus', ] query_results = schedd.query( constraint='ClusterId =?= {}'.format(cluster_id), attr_list=attr_list, ) # Assume only a single result job_status = {} for classad in query_results: job_status = { 'active': True, } for field in attr_list: job_status[field] = classad[field] print('Update for active job:') self.update_job(job_id, updates={ 'status': CONDOR_JOB_STATES[job_status['JobStatus']], }) # Next search job history (condor_history) if not job_status: print('Job is no longer active.') projection = [ 'ClusterId', 'JobStatus', 'LastJobStatus', 'ExitStatus', 'Owner', 'User', 'JobStartDate', 'JobCurrentStartExecutingDate', 'CompletionDate', 'Cmd', 'Out', 'UserLog', 'Err', ] query_results = schedd.history( requirements='ClusterId =?= {}'.format(cluster_id), projection=projection, # projection=["ClusterId", "JobStatus"], ) for classad in query_results: print('Finished job: {}'.format(classad)) job_status = { 'active': False, } for field in projection: job_status[field] = classad[field] print('Update for completed job:') self.update_job(job_id, updates={ 'status': CONDOR_JOB_STATES[job_status['JobStatus']], 'time_start': datetime.datetime.fromtimestamp( job_status['JobStartDate']), 'time_complete': datetime.datetime.fromtimestamp( job_status['CompletionDate']) }) return job_status def update_job(self, job_id, updates={}): self.db.open() for column in updates: if column != 'uuid': sql = ''' UPDATE `job` SET `{}` = ? WHERE `uuid` = ? '''.format(column) self.db.db_cursor.execute(sql, (updates[column], job_id)) print('Updated column "{}" with value "{}"'.format( column, updates[column])) self.db.close() def get_cluster_id(self, job_id): self.db.open() sql = ''' SELECT `cluster_id` FROM `job` WHERE `uuid` = ? ''' results = self.db.db_cursor.execute(sql, (job_id, )) for row in results: cluster_id = row[0] self.db.close() return cluster_id async def monitor(self, filename, duration=5): status = STATUS_OK msg = '' print('Monitoring file "{}" for {} seconds...'.format( filename, duration)) await asyncio.sleep(duration) print('Monitoring complete for file "{}".'.format(filename)) return status, msg
def channel_invite(token: str, channel_id: int, u_id: int) -> dict: """ Invites a user (with user id u_id) to join a channel with ID channel_id. Once invited the user is added to the channel immediately :param token: token of user who try to invite user :param channel_id: the id of channel which the user will be invited in :param u_id: uid of user who has been invited to join the channel :return: if it can successfully invite somebody to this channel, it will return an empty dictionary """ token_operation = TokenJwt() # check if the token is valid if check_valid(token) is False: raise AccessError(description='error occurred: token is not valid') # check if channel id is valid db_connect = DbConnector() db_connect.cursor() sql = "SELECT channel_id FROM project.channel_data WHERE channel_id = (%s)" value = (channel_id, ) db_connect.execute(sql, value) ret = db_connect.fetchone() if ret is None: raise InputError( description= 'error occurred: channel_id does not refer to a valid channel') # check if uid is valid sql = "SELECT u_id FROM project.user_data WHERE u_id = (%s)" value = (u_id, ) db_connect.execute(sql, value) ret = db_connect.fetchone() if ret is None: raise InputError( description='error occurred: u_id does not refer to a valid user') # check if the authorised user( is a member of the channel) authorised_uid = token_operation.get_uid(token) sql = "SELECT member, owner FROM project.channel_data WHERE channel_id =(%s)" value = (channel_id, ) db_connect.execute(sql, value) ret = db_connect.fetchone() member_list = ret[0] owner_list = ret[1] if authorised_uid not in member_list: raise AccessError(description='error occurred: the authorised user ' 'is not a member of the channel') # get flockr owner list sql = "SELECT owner FROM project.flockr_data;" db_connect.execute(sql) ret = db_connect.fetchone() flockr_list = ret[0] # if no error, add the user to the channel if u_id not in member_list: sql = "UPDATE project.channel_data SET member=(%s), owner=(%s) WHERE channel_id=(%s);" # if invite flockr, flockr will be owner if u_id in flockr_list: member_list.append(u_id) owner_list.append(u_id) value = (member_list, owner_list, channel_id) else: member_list.append(u_id) value = (member_list, owner_list, channel_id) db_connect.execute(sql, value) db_connect.close() return {}
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}
def standup_send(token, channel_id, message): token_operation = TokenJwt() # check if the token is valid if check_valid(token) is False: raise AccessError(description='error occurred: token is not valid') # check channel id is valid 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() if ret is None: raise InputError(description='error occurred: channel is not valid') # get member list member_list = ret[0] # check if the message longer than 1000 if len(message) > 1000: raise InputError(description='Message is more than 1000 characters') exist_active = standup_active(token, channel_id)["is_active"] if not exist_active: raise InputError( description='error occurred: no standup is running in this channel' ) u_id = token_operation.get_uid(token) # check user is valid if u_id not in member_list: raise AccessError( description='error occurred: user is not a member of this channel') # get handle sql = "SELECT handle FROM project.user_data WHERE u_id=(%s)" value = (u_id, ) db_connect.execute(sql, value) ret = db_connect.fetchone() handle = ret[0] # get msg from active buffer sql = "SELECT message FROM project.active_data WHERE channel_id=(%s)" value = (channel_id, ) db_connect.execute(sql, value) ret = db_connect.fetchone() if ret[0] is not None: message_stand = ret[0] message_stand += "\n" + handle + ": " + message else: message_stand = handle + ": " + message # add to active data sql = "UPDATE project.active_data SET message=(%s) WHERE channel_id=(%s);" value = (message_stand, channel_id) db_connect.execute(sql, value) # close database connection db_connect.close() return {}
def auth_register(email: str, password: str, name_first: str, name_last: str) -> dict: """ Given a user's first and last name, email address, and password, create a new account for them and return a new token for authentication in their session. :param email: user's email :param password: user's password :param name_first: user's first name :param name_last: user's last name :return: dictionary with keys "u_id" and "token" """ # check if email invalid if not check(email): raise InputError(description='error occurred: Email entered is not a valid email') # check if email has already been used db_connect = DbConnector() db_connect.cursor() # connect to database and get cursor sql = "SELECT email from project.user_data WHERE email = %s" value = (email,) db_connect.execute(sql, value) ret = db_connect.fetchone() if ret is not None: raise InputError(description='error occurred: Email address is already ' 'being used by another user') # check the length of password if len(password) < 6: raise InputError(description='error occurred: Password entered is less ' 'than 6 characters long') if len(name_first) not in range(1, 51): raise InputError(description='error occurred: first name is not ' 'between 1 and 50 characters inclusively in length') if len(name_last) not in range(1, 51): raise InputError(description='error occurred: last name is not ' 'between 1 and 50 characters inclusively in length') # generate u_id for the new user sql = "INSERT INTO project.user_data (email) VALUES (%s)" value = (email,) db_connect.execute(sql, value) sql = "SELECT u_id FROM project.user_data WHERE email=(%s)" value = (email,) db_connect.execute(sql,value) ret = db_connect.fetchone() user_uid = ret[0] # sql = "SELECT COUNT(*) FROM project.user_data" # db_connect.execute(sql) # ret = db_connect.fetchone() # user_uid = ret[0] + 1 # print(user_uid) # generate a handle for the new user # which contains the first letter of the name_first by default # cut off the part where exceeds 20 handle = (name_first[0] + name_last).lower() if len(handle) > 20: handle = handle[0:20] # check if it is unique otherwise sql = "SELECT handle from project.user_data WHERE handle=%s" value = (handle,) db_connect.execute(sql, value) ret = db_connect.fetchone() # if it exceeds 20, cutoff the extra part from the original handle and remain user_uid # add user_uid at the end of the handle if ret is not None: if len(handle + str(user_uid)) > 20: handle = handle[0:(20 - len(str(user_uid)))] + str(user_uid) else: handle = handle + str(user_uid) # generate the token token_operation = TokenJwt() token = token_operation.encode_token({'u_id': user_uid}) # hashing the password hash_password = hashlib.sha256(password.encode()).hexdigest() # add user in database sql = ''' UPDATE project.user_data set password=(%s), name_first=(%s), name_last=(%s), token=(%s), handle=(%s) WHERE email=(%s) ''' value = (hash_password, name_first, name_last, token, handle, email) db_connect.execute(sql, value) if user_uid == 1: sql = "INSERT INTO project.flockr_data (owner) VALUES ('{%s}')" value = [user_uid] db_connect.execute(sql, value) db_connect.close() return { 'u_id': user_uid, 'token': token }