def create_workspace_with_users(data):
users = data['users']
admin = data['admin']
workspace = data['name']
res = {}
users_added = False
try:
connection = getDBConnection()
cursor = connection.cursor()
insert_workspace_sql = "insert into workspaces (name) values (%s) " \
"returning workspace_id"
cursor.execute(insert_workspace_sql, (workspace, ))
connection.commit()
count = cursor.rowcount
if (count == 0):
res['error'] = 'Could not create the workspace'
else:
new_workspace_id = cursor.fetchone()[0]
loop = asyncio.new_event_loop()
admin_id = loop.run_until_complete(get_user_id(admin))
admin_added = add_user_to_workspace([admin_id], new_workspace_id,
True)
if (admin_added == 0):
res['error'] = 'Workspace added but could not set admin to workspace.'
else:
user_id_list = []
for user in users:
loop = asyncio.new_event_loop()
single_user_id = loop.run_until_complete(
get_user_id(user['username']))
user_id_list.append(single_user_id)
users_added = add_user_to_workspace(user_id_list,
new_workspace_id)
if (users_added != len(users)):
res['error'] = 'Some users could not be added to workspace. Try again'
else:
users_added = True
except (Exception, psycopg2.Error) as error:
print('Error while conecting to PostgresQL', error)
res['error'] = str(error)
finally:
if (connection):
# close the connection and the cursor
cursor.close()
connection.close()
print("PostgresSQL connection is closed")
res['users_added'] = users_added
return res
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 create_workspace_only(data):
res = {}
workspace_added = False
workspace_name = data['name']
s3 = boto3.client('s3')
s3.create_bucket(Bucket='%s' % workspace_name)
response = s3.list_buckets()
buckets = [bucket['Name'] for bucket in response['Buckets']]
try:
admin = data['admin']
loop = asyncio.new_event_loop()
admin_id = loop.run_until_complete(get_user_id(admin))
if (admin_id == -1):
res['error'] = 'Could not find user in the system so cannot add workspace for user'
else:
connection = getDBConnection()
insert_workspace_name = "insert into workspaces (name) values (%s) returning workspace_id"
cursor = connection.cursor()
cursor.execute(insert_workspace_name, (workspace_name, ))
connection.commit()
count = cursor.rowcount
if (count == 0):
res['error'] = 'Could not add workspace into the system'
else:
new_workspace_id = cursor.fetchone()[0]
admin_added = add_user_to_workspace([admin_id],
new_workspace_id, True)
if (admin_added != 0):
workspace_added = True
else:
res['error'] = 'Workspace created but could not set admin. Contact support'
except (Exception, psycopg2.Error) as error:
print('Error while conecting to PostgresQL', error)
res['error'] = str(error)
finally:
if (connection):
# close the connection and the cursor
cursor.close()
connection.close()
print("PostgresSQL connection is closed")
res['workspace_added'] = workspace_added
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_user_invites(username):
# connection = None
res = {}
list_of_invites = []
try:
connection = getDBConnection()
cursor = connection.cursor()
loop = asyncio.new_event_loop()
user_id = loop.run_until_complete(get_user_id(username))
if (user_id == -1):
res["error"] = "User could not be found"
else:
get_invites_sql = """select w.name, u.username from invites i
inner join workspaces w on i.workspace_id=w.workspace_id
inner join users u on i.invited_by_id = u.user_id
where i.user_id=%s"""
cursor.execute(get_invites_sql, (user_id, ))
user_invites = cursor.fetchall()
for row in user_invites:
list_of_invites.append({
'workspace': row[0],
'invited_by': 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")
res["invites"] = list_of_invites
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