def insert_user_invite(invite_json):
user_invited = False
res = {}
# connection = None
username = invite_json['username']
workspace = invite_json['workspace']
invited_by = invite_json['invitedBy']
loop = asyncio.new_event_loop()
user_id = loop.run_until_complete(get_user_id(username))
loop = asyncio.new_event_loop()
invited_by_id = loop.run_until_complete(get_user_id(invited_by))
loop = asyncio.new_event_loop()
workspace_id = loop.run_until_complete(get_workspace_id(workspace))
try:
# connection = psycopg2.connect(
# database="ssc")
# cursor = connection.cursor()
connection = getDBConnection()
cursor = connection.cursor()
is_inviter_admin_sql = """select * from workspace_users where user_id=%s and
workspace_id=%s and is_admin=True"""
cursor.execute(is_inviter_admin_sql, (invited_by_id, workspace_id))
cursor.fetchone()
if (cursor.rowcount != 0):
insert_to_invites_sql = """insert into invites (user_id, workspace_id, invited_by_id)
values(%s, %s, %s)"""
cursor.execute(insert_to_invites_sql,
(user_id, workspace_id, invited_by_id))
connection.commit()
count = cursor.rowcount
if (count != 0):
user_invited = True
else:
res['error'] = 'User could not be invited to workspace'
else:
res['error'] = 'Could not match admin to workspace'
except (Exception, psycopg2.Error) as error:
print("Error while connecting to PostgreSQL", error)
res['error'] = str(error)
finally:
# closing database connection.
if (connection):
cursor.close()
connection.close()
print("PostgreSQL connection is closed")
res['user_invited'] = user_invited
return res
def delete_workspace(delete_request):
# connection = None
workspace_deleted = False
res = {}
deleted_by = delete_request['deleted_by']
workspace = delete_request['workspace']
loop = asyncio.new_event_loop()
workspace_id = loop.run_until_complete(get_workspace_id(workspace))
loop = asyncio.new_event_loop()
deleted_by_id = loop.run_until_complete(get_user_id(deleted_by))
try:
if (workspace_id == -1 | deleted_by_id == -1):
res['error'] = 'Could not locate workspace or user deleting the workspace'
else:
connection = getDBConnection()
cursor = connection.cursor()
loop = asyncio.new_event_loop()
admin_status = loop.run_until_complete(is_user_admin(deleted_by_id, workspace_id))
if (admin_status == 0):
res['error'] = 'User is not an admin of the workspace'
else:
delete_workspace_sql = "delete from workspaces where workspace_id=%s"
cursor.execute(delete_workspace_sql, (workspace_id,))
connection.commit()
count = cursor.rowcount
if (count != 0):
workspace_deleted = True
except (Exception, psycopg2.Error) as error:
print("Error while connecting to PostgreSQL", error)
res['error'] = str(error)
finally:
# closing database connection.
if (connection):
cursor.close()
connection.close()
print("PostgreSQL connection is closed")
res['workspace_deleted'] = workspace_deleted
return res
def fetch_workspace_users(name):
list_of_users = []
res = {}
try:
connection = getDBConnection()
cursor = connection.cursor()
if (name == None):
res["error"] = "Workspace name is invalid"
else:
loop = asyncio.new_event_loop()
workspace_id = loop.run_until_complete(get_workspace_id(name))
if (workspace_id == -1):
res["error"] = "Workspace name is invalid"
else:
cursor.execute(
"""SELECT u.username, wu.is_admin FROM workspace_users wu
INNER JOIN workspaces w ON w.workspace_id = wu.workspace_id
INNER JOIN users u ON wu.user_id=u.user_id
WHERE w.workspace_id = %s
""", (workspace_id, ))
workspace_users = cursor.fetchall()
for row in workspace_users:
list_of_users.append({
'username': row[0],
'is_admin': str(row[1])
})
except (Exception, psycopg2.Error) as error:
print("Error while connecting to PostgreSQL", error)
res["error"] = str(error)
finally:
# closing database connection.
if (connection):
cursor.close()
connection.close()
print("PostgreSQL connection is closed")
if ((len(list_of_users) == 0) & ("error" not in res)):
res["error"] = "There are no users in this workspace"
res["users"] = list_of_users
return res
def fetch_workspace_files(name):
list_of_files = []
res = {}
connection = None
try:
connection = psycopg2.connect(
database="ssc")
cursor = connection.cursor()
if (name == None):
res["error"] = "Workspace name is invalid"
else:
loop = asyncio.new_event_loop()
workspace_id = loop.run_until_complete(get_workspace_id(name))
if (workspace_id == -1):
res["error"] = "Workspace name is invalid"
else:
cursor.execute("""SELECT file_name FROM workspace_files
INNER JOIN workspaces ON workspaces.workspace_id = workspace_files.workspace_id
WHERE workspaces.workspace_id = %s
""", (workspace_id,))
workspace_files = cursor.fetchall()
for row in workspace_files:
list_of_files.append(
{'file_name': row[0]})
except (Exception, psycopg2.Error) as error:
print("Error while connecting to PostgreSQL", error)
res["error"] = str(error)
finally:
# closing database connection.
if (connection):
cursor.close()
connection.close()
print("PostgreSQL connection is closed")
if ((len(list_of_files) == 0) & ("error" not in res)):
res["error"] = "There are no files in this workspace"
res["files"] = list_of_files
return res
def update_admin(workspace, admin_request):
workspace_admin_updated = False
res = {}
username = admin_request['username']
admin_username = admin_request['admin_username']
make_admin = admin_request['make_admin']
loop = asyncio.new_event_loop()
workspace_id = loop.run_until_complete(get_workspace_id(workspace))
loop = asyncio.new_event_loop()
user_id = loop.run_until_complete(get_user_id(username))
loop = asyncio.new_event_loop()
admin_id = loop.run_until_complete(get_user_id(admin_username))
try:
if (workspace_id == -1 | admin_id == -1 | user_id == -1):
res['error'] = 'Invalid input. Check username, admin and workspace are correct'
else:
connection = getDBConnection()
cursor = connection.cursor()
loop = asyncio.new_event_loop()
admin_status = loop.run_until_complete(
is_user_admin(admin_id, workspace_id))
if (admin_status == 0):
res['error'] = 'Admin is not actual admin of workspace'
else:
if (make_admin == 'True'):
make_admin_bool = True
else:
make_admin_bool = False
update_admin_sql = "update workspace_users set is_admin=%s where workspace_id=%s" \
"and user_id=%s"
cursor.execute(update_admin_sql,
(make_admin_bool, workspace_id, user_id))
connection.commit()
count = cursor.rowcount
if (count == 0):
res['error'] = 'Could not make user as admin'
else:
workspace_admin_updated = True
except (Exception, psycopg2.Error) as error:
print("Error while connecting to PostgreSQL", error)
res['error'] = str(error)
finally:
# closing database connection.
if (connection):
cursor.close()
connection.close()
print("PostgreSQL connection is closed")
res['workspace_admin_updated'] = workspace_admin_updated
return res
def encrypt_file(f, bucket_name, audio_key):
f.save(secure_filename(f.filename))
s3 = boto3.client('s3')
key_string = bytes(audio_key, 'utf-8')
encoded_key = base64.b64encode(key_string)
res = {}
try:
# convert key from 56 into 64
key = encoded_key
connection = getDBConnection()
cursor = connection.cursor()
actualFile = secure_filename(f.filename)
time_stamp = str(time.time())
file_start = str(secure_filename(f.filename))[0:-4]
file_end = str(secure_filename(f.filename))[-4:]
filename = (file_start + '-' + time_stamp + '-' + file_end)
with open(actualFile, 'rb') as f:
file = f.read()
fernet = Fernet(key)
encrypted = fernet.encrypt(file)
with open(filename, 'wb') as f:
f.write(encrypted)
s3.upload_file(filename, bucket_name, filename)
loop = asyncio.new_event_loop()
workspace_id = loop.run_until_complete(get_workspace_id(bucket_name))
if (workspace_id == -1):
res["error"] = "Workspace name is invalid"
else:
add_file_to_workspace = "INSERT INTO workspace_files (workspace_id, file_name) " \
"VALUES (%s, %s) RETURNING *; "
cursor.execute(add_file_to_workspace, (workspace_id, filename))
connection.commit()
# if cursor.rowcount == 0:
# res["error"] = ""
except (Exception, psycopg2.Error) as error:
print('Error while connecting to PostgresQL', error)
finally:
os.remove(filename)
os.remove(actualFile)
if (connection):
# close the connection and the cursor
cursor.close()
connection.close()
print("PostgresSQL connection is closed")
return 'encrypted'