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 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 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 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'}