def remove_project(user_id, project_id, email): query = f""" SELECT [creator] FROM [prj].[project] WHERE [ProjectId] = '{project_id}' """ conn = Database.connect() cursor = conn.cursor() results = Database.execute_query(query, cursor) conn.close() if user_id == results[0][0]: remove_member = User.get_user_from_email(email) if remove_member is not None: query = """ [prj].[RemoveProject] @UserId = ?, @ProjectId = ? """ params = (user_id, project_id) conn = Database.connect() cursor = conn.cursor() results = Database.execute_sproc(query, params, cursor) if results['Status'] == 201: cursor.commit() conn.close() return results else: return {'Status': 400, 'Message': 'There is no account associated with this email'} else: return {'Status': 400, 'Message':'This user is not the owner of the project'}
def get_users_invitations(user_id): query = f""" SELECT p.[ProjectID] ,p.[Name] ,u.[Email] FROM [MetaData].[prj].[Project] p INNER JOIN [MetaData].[usr].[User] u ON p.[Creator] = u.[UserID] LEFT JOIN [MetaData].[prj].[ProjectMember] pm ON p.[ProjectID] = pm.[ProjectID] WHERE pm.[Pending] = 1 AND pm.[UserID] = '{user_id}' """ conn = Database.connect() cursor = conn.cursor() results = Database.execute_query(query, cursor) projects = [] for row in results: project = Project() project.set_project_id(row[0]) project.set_name(row[1]) project.set_creator(row[2]) projects.append(project.project) conn.close() response = {'Projects': projects} return json.dumps(response)
def download_file_s3(file_path): query = f""" SELECT TOP 1 [Filename] ,[DatasetName] FROM [metadata].[Search] WHERE [FilePath] = '{file_path}' """ conn = Database.connect() cursor = conn.cursor() results = Database.execute_query(query, cursor) conn.close() for row in results: file_name = row[0] dataset_name = row[1] bucket = 'fyp-data-repo' s3_client = boto3.client('s3') local_directory = os.path.join(LANDING_FOLDER, dataset_name) if not os.path.exists(local_directory): os.mkdir(local_directory) full_local_path = os.path.join(local_directory, file_name) s3_client.download_file(bucket, file_path, full_local_path) return send_file(full_local_path, as_attachment=True)
def get_file_history(file_id, project_id): query = f""" SELECT [Filename] ,[DatasetName] ,[ProjectName] ,[Change] ,[Filepath] ,[Previous] ,[PreviousChange] FROM [MetaData].[metadata].[SearchPrivate] WHERE [FileID] = '{file_id}' AND [ProjectID] = '{project_id}' """ conn = Database.connect() cursor = conn.cursor() results = Database.execute_query(query, cursor) conn.close() history = [] for row in results: file = { 'Filename': row[0], 'DatasetName': row[1], 'ProjectName': row[2], 'Change': row[3], 'Filepath': row[4], 'Previous': row[5], 'PreviousChange': row[6] } history.append(file) response = {'History': history} return json.dumps(response)
def add_dataset_to_project(user_id, info): query = f""" SELECT fh.[FileID], fh.[Filepath], fh.[Change] FROM [MetaData].[metadata].[FileHistory] fh INNER JOIN [MetaData].[metadata].[File] f ON fh.[FileID] = f.[FileID] WHERE f.[DataSet] = '{info['DatasetId']}' AND fh.[Change] = 'source' AND fh.[ProjectID] is NULL """ conn = Database.connect() cursor = conn.cursor() results = Database.execute_query(query, cursor) bucket = 'fyp-data-repo' client = boto3.client('s3') for row in results: file_id = row[0] file_path = row[1] change_as_previous = row[2] for project in info['Projects']: new_path = f'{project}/project_source/{file_path}' client.copy_object( Bucket=bucket, CopySource=f'/{bucket}/{file_path}', Key=new_path ) query = f""" INSERT INTO [metadata].[FileHistory] ([FileID] ,[UserID] ,[ProjectID] ,[Change] ,[Filepath] ,[Active] ,[StartDate] ,[EndDate] ,[Previous] ,[PreviousChange]) VALUES ('{file_id}','{user_id}','{project}','project source','{new_path}',1 ,GETDATE(),NULL,'{file_path}', '{change_as_previous}') """ Database.execute_non_query(query, cursor) cursor.commit() conn.close() return {'Status': 200, 'Message': 'Dataset added to projects'}
def generate_unique_identifier(): query = """ SELECT NEWID() """ conn = Database.connect() cursor = conn.cursor() results = Database.execute_query(query, cursor) conn.close() return results[0][0]
def create_institution(institution_model): query = f""" [usr].[CreateInstitution] @Name = ?, @Desc = ?, @Owner = ? """ params = (institution_model.get_name(), institution_model.get_desc(), institution_model.get_owner()) conn = Database.connect() cursor = conn.cursor() results = Database.execute_sproc(query, params, cursor) if results['Status'] == 201: cursor.commit() conn.close() return results
def leave_project(user_id, project_id): query = """ [prj].[RemoveProject] @UserId = ?, @ProjectId = ? """ params = (user_id, project_id) conn = Database.connect() cursor = conn.cursor() results = Database.execute_sproc(query, params, cursor) if results['Status'] == 200: cursor.commit() conn.close() return results
def join_project(user_id, project_id): query = """ [prj].[AcceptProjectInvite] @UserId = ?, @ProjectId = ? """ params = (user_id, project_id) conn = Database.connect() cursor = conn.cursor() results = Database.execute_sproc(query, params, cursor) if results['Status'] == 201: cursor.commit() conn.close() return results
def upload_tags(dataset_id, key, value): query = f""" INSERT INTO [metadata].[Tag] ([DatasetId],[TagKey],[TagValue]) VALUES ('{dataset_id}','{key}','{value}') """ conn = Database.connect() cursor = conn.cursor() Database.execute_non_query(query, cursor) cursor.commit() conn.close()
def create_dataset_metadata(dataset_id, dataset_name): query = f""" [metadata].[GetorInsertDataset] @DatasetName = ?, @DataSetId = ? """ params = (dataset_name, dataset_id) conn = Database.connect() cursor = conn.cursor() response = Database.execute_sproc(query, params, cursor) if response['Status'] == 201: cursor.commit() conn.close() return response
def create_project(user_id, project_info): query = """ [prj].[CreateProject] @Creator = ?, @Name = ?, @Desc = ?, @Public = ? """ params = (user_id, project_info['Name'], project_info['Desc'], project_info['Public']) conn = Database.connect() cursor = conn.cursor() results = Database.execute_sproc(query, params, cursor) if results['Status'] == 201: cursor.commit() conn.close() return results
def init_file_path(file_id, file_path): query = f""" UPDATE [metadata].[FileHistory] SET [FilePath] = '{file_path}' WHERE [FileID] = '{file_id}' """ conn = Database.connect() cursor = conn.cursor() Database.execute_non_query(query, cursor) cursor.commit() conn.close()
def accept_pending_invite(user_id, institution_id): pending = Institution.get_all_pending(user_id) if institution_id in pending: query = f""" [usr].[AcceptInstitutionInvite] ?, ? """ params = (user_id, institution_id) conn = Database.connect() cursor = conn.cursor() results = Database.execute_sproc(query, params, cursor) if results['Status'] == 201: cursor.commit() conn.close() return results return {'Status': 400, 'Message': 'This institution has not invited this user'}
def member_leave(user_id): user = User.get_user_info(user_id) if user.get_institution() is not None: query = f""" [usr].[RemoveUserFromInstitution] ?, ? """ params = (user_id, user.get_institution()) conn = Database.connect() cursor = conn.cursor() results = Database.execute_sproc(query, params, cursor) if results['Status'] == 200: cursor.commit() conn.close() return results else: return {'Status': 400, 'Message': 'User not part of institution'}
def get_institution(institution_id): query = f""" SELECT TOP 1 [InstitutionID] ,[Name] ,[Desc] ,[Owner] FROM [MetaData].[usr].[Institution] WHERE [InstitutionID] = '{institution_id}' """ conn = Database.connect() cursor = conn.cursor() results = Database.execute_query(query, cursor) if len(results) > 0: institution = Institution() institution.set_institution_id(results[0][0]) institution.set_name(results[0][1]) institution.set_desc(results[0][2]) institution.set_owner(results[0][3]) query = f""" SELECT [UserId] ,[Role] ,[Pending] FROM [MetaData].[usr].[InstitutionMember] WHERE [InstitutionID] = '{institution_id}' """ results = Database.execute_query(query, cursor) if len(results) > 0: members = [] for row in results: member = User.get_user_info(row[0]) member.set_institution_role(row[1]) if row[2] == 0: is_pending = False else: is_pending = True member.set_institution_pending(is_pending) members.append(member) institution.set_members(members) conn.close() return institution
def upload_file_metadata(self): query = f""" [metadata].[InsertFileMetaData] ?,?,?,?,?,?,?,?,?,?,?,? """ params = (self.file_id, self.file_name, self.user_id, self.signal_type, self.species, self.gender, self.age, self.target, self.action, self.device, self.dataset_id, self.channel_count) print(params) conn = Database.connect() cursor = conn.cursor() results = Database.execute_sproc(query, params, cursor) if results['Status'] == 201: cursor.commit() response = {'Status': results['Status'], 'FileId': self.file_id, "Message": results['Message']} else: cursor.rollback() response = {'Status': results['Status'], 'FileId': self.file_id, "Message": results['Message']} conn.close() return response
def invite_to_project(project_info): query = """ [prj].[InviteUserToProject] @UserId = ?, @ProjectId = ? """ user_id = User.get_user_from_email(project_info['Email']) if user_id is not None: project_id = project_info['ProjectId'] params = (user_id, project_id) conn = Database.connect() cursor = conn.cursor() results = Database.execute_sproc(query, params, cursor) if results['Status'] == 201: cursor.commit() conn.close() else: results = {'Status': 404, 'Message': 'This email does not have a registered account'} return results
def check_for_dataset(file_id): query = f""" SELECT TOP 1 [DataSet] FROM [metadata].[File] WHERE [FileID] = '{file_id}' """ conn = Database.connect() cursor = conn.cursor() results = Database.execute_query(query, cursor) if not results: return { 'Status': 500, 'Message': 'Could not find a dataset associated with this id' } else: return {'Status': 200, 'Message': results[0][0]}
def get_all_pending(user_id): query = f""" SELECT [InstitutionId] FROM [usr].[InstitutionMember] WHERE [UserId] = '{user_id}' AND [Pending] = 1 """ conn = Database.connect() cursor = conn.cursor() results = Database.execute_query(query, cursor) conn.close() pending_invites = [] for row in results: pending_invites.append(row[0]) return pending_invites
def make_public_or_private(project_id, mode): query = f""" UPDATE [prj].[project] SET [public] = {mode} WHERE [ProjectId] = '{project_id}' """ conn = Database.connect() cursor = conn.cursor() Database.execute_non_query(query, cursor) cursor.commit() conn.close() if mode == '1': message = 'Project now public' else: message = 'Project now private' return{'Status': 200, 'Message': message}
def file_history_insert(user_id, file_info): query = f""" SELECT TOP 1 [FileID] ,[ProjectID] ,[Change] FROM [MetaData].[metadata].[FileHistory] WHERE [Filepath] = '{file_info["PreviousFilepath"]}' """ conn = Database.connect() cursor = conn.cursor() results = Database.execute_query(query, cursor) for row in results: insert_query = f""" INSERT INTO [metadata].[FileHistory] ([FileID] ,[UserID] ,[ProjectID] ,[Change] ,[Filepath] ,[Active] ,[StartDate] ,[EndDate] ,[Previous] ,[PreviousChange]) VALUES ('{row[0]}','{user_id}','{row[1]}','{file_info['Change']}', '{file_info['Filepath']}',1,GETDATE(),NULL, '{file_info['PreviousFilepath']}','{row[2]}') """ Database.execute_non_query(insert_query, cursor) cursor.commit() conn.close() return {'Status': 201, 'Message': 'Change to file has been uploaded'}
def invite_member(user_id, invitation_info): user = User.get_user_info(user_id) if user.get_institution() is not None: invited_user_id = User.get_user_from_email(invitation_info['Email']) if invited_user_id is not None: if user.get_institution() not in Institution.get_all_pending(invited_user_id): query = f""" [usr].[InviteUserToInstitution] ?, ?, ? """ params = (invited_user_id, user.get_institution(), invitation_info['Role']) conn = Database.connect() cursor = conn.cursor() results = Database.execute_sproc(query, params, cursor) if results['Status'] == 201: cursor.commit() conn.close() return results else: return {'Status': 400, 'Message': 'This account has already been invited'} else: return {'Status': 400, 'Message': 'There is no account associated with this email'} else: return {'Status': 400, 'Message': 'User not part of institution'}
def remove_member(user_id, email): user = User.get_user_info(user_id) if user.get_institution() is not None: institution = Institution.get_institution(user.get_institution()) if institution.get_owner() == user_id: remove_user_id = User.get_user_from_email(email) if remove_user_id is not None: query = f""" [usr].[RemoveUserFromInstitution] ?, ? """ params = (remove_user_id, user.get_institution()) conn = Database.connect() cursor = conn.cursor() results = Database.execute_sproc(query, params, cursor) if results['Status'] == 200: cursor.commit() conn.close() return results else: return {'Status': 400, 'Message': 'There is no account associated with this email'} else: return {'Status': 400, 'Message': 'User is not owner of institution'} else: return {'Status': 400, 'Message': 'User not part of institution'}
def execute_search(self): if self.where_clause != 'WHERE ': self.query = self.query + self.where_clause conn = Database.connect() cursor = conn.cursor() results = Database.execute_query(self.query, cursor) all_data = [] for row in results: dataset_id = row[14] tag_query = f""" SELECT [TagKey] ,[TagValue] FROM [metadata].[Tag] WHERE [DatasetId] = '{dataset_id}' """ conn = Database.connect() cursor = conn.cursor() results = Database.execute_query(tag_query, cursor) tags = {} for tag in results: tags[tag[0]] = tag[1] projects = {} projects_query = f""" SELECT s.[ProjectID] ,s.[ProjectName] ,p.[Creator] ,p.[Desc] ,p.[StartDate] ,p.[EndDate] FROM [MetaData].[metadata].[Search] s INNER JOIN [MetaData].[prj].[Project] p ON s.[ProjectID] = p.[ProjectId] WHERE s.[ProjectID] IS NOT NULL AND s.[Change] = 'project source' AND s.[DatasetID] = '{dataset_id}' AND p.[Public] = 1 GROUP BY s.[DatasetId], s.[ProjectId], s.[ProjectName], p.[Creator], p.[Desc], p.[StartDate], p.[EndDate] """ conn = Database.connect() cursor = conn.cursor() results = Database.execute_query(projects_query, cursor) for project in results: projects[project[0]] = { 'Name': project[1], 'Creator': project[2], 'Desc': project[3], 'StartDate': str(project[4]), 'EndDate': str(project[5]) } conn.close() data = { 'FileId': row[0], 'Filename': row[1], 'DatasetName': row[2], 'Projects': projects, 'SignalType': row[4], 'Species': row[5], 'Gender': row[6], 'Age': row[7], 'Target': row[8], 'Action': row[9], 'ChannelCount': row[10], 'Device': row[11], 'Change': row[12], 'Filepath': row[13], 'DatasetId': row[14], 'Previous': row[15], 'PreviousChange': row[16], 'Tags': tags } all_data.append(data) response = {"Results": all_data} return json.dumps(response)
def get_users_projects(user_id): query = f""" SELECT p.[ProjectID] ,p.[Name] ,p.[Desc] ,u.[Email] ,p.[StartDate] ,p.[EndDate] ,p.[Public] FROM [MetaData].[prj].[Project] p INNER JOIN [MetaData].[usr].[User] u ON p.[Creator] = u.[UserID] LEFT JOIN [MetaData].[prj].[ProjectMember] pm ON p.[ProjectID] = pm.[ProjectID] WHERE pm.[Pending] = 0 AND pm.[UserID] = '{user_id}' OR p.[Creator] = '{user_id}' """ conn = Database.connect() cursor = conn.cursor() results = Database.execute_query(query, cursor) projects = [] for row in results: project = Project() project.set_project_id(row[0]) project.set_name(row[1]) project.set_desc(row[2]) project.set_creator(row[3]) project.set_start_date(row[4]) project.set_end_date(row[5]) project.set_public(row[6]) query = f""" SELECT u.[Email], pm.[Pending] FROM [MetaData].[prj].[ProjectMember] pm INNER JOIN [MetaData].[usr].[User] u ON pm.[UserId] = u.[UserID] WHERE [ProjectID] = '{project.get_project_id()}' """ cursor = conn.cursor() results = Database.execute_query(query, cursor) members = [] for member_row in results: user_id = User.get_user_from_email(member_row[0]) user = User.get_user_info(user_id) user.user['pending'] = member_row[1] members.append(user.user) project.set_project_members(members) projects.append(project.project) conn.close() response = {'Projects': projects} return json.dumps(response)