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)
