def update_group(group: Group) -> bool:
"""
Update a group in the database. Certain fields (group_name, group_title) can't be modified.
:param group: Object representing an updated group.
:return: True if the group is updated in the database, False otherwise.
"""
db.session.execute(
'''
UPDATE groups SET
grouppic=:grouppic,
grouppic_name=:grouppic_name,
description=:description,
week_start=:week_start,
modified_date=:modified_date,
modified_app=:modified_app
WHERE group_name=:group_name
AND deleted IS FALSE
''', {
'grouppic': group.grouppic,
'grouppic_name': group.grouppic_name,
'description': group.description,
'week_start': group.week_start,
'group_name': group.group_name,
'modified_date': group.modified_date,
'modified_app': group.modified_app
})
return BasicDao.safe_commit()
def update_group_member(group_id: int, username: str, status: str, user: str) -> bool:
"""
Update a group membership for a user.
:param group_id: Unique id of a group.
:param username: Unique name for a user.
:param status: Status of a group membership - whether it is accepted or pending.
:param user: User type of a group membership - whether the user is an admin or a regular user.
:return: True if the group membership was updated, False otherwise.
"""
db.session.execute(
'''
UPDATE groupmembers SET
status=:status,
user=:user,
modified_date=:modified_date,
modified_app=:modified_app
WHERE group_id=:group_id
AND username=:username
AND deleted IS FALSE
''',
{
'group_id': group_id,
'username': username,
'modified_date': datetime.now(),
'modified_app': 'saints-xctf-api',
'status': status,
'user': user
}
)
return BasicDao.safe_commit()
def soft_delete_comment(comment: Comment) -> bool:
"""
Soft Delete a comment from the database.
:param comment: Object representing a comment to soft delete.
:return: True if the soft deletion was successful without error, False otherwise.
"""
db.session.execute(
'''
UPDATE comments SET
deleted=:deleted,
modified_date=:modified_date,
modified_app=:modified_app,
deleted_date=:deleted_date,
deleted_app=:deleted_app
WHERE comment_id=:comment_id
AND deleted IS FALSE
''',
{
'comment_id': comment.comment_id,
'deleted': comment.deleted,
'modified_date': comment.modified_date,
'modified_app': comment.modified_app,
'deleted_date': comment.deleted_date,
'deleted_app': comment.deleted_app
}
)
return BasicDao.safe_commit()
def soft_delete_user(user: User) -> bool:
"""
Soft Delete a user from the database.
:param user: Object representing a user to soft delete.
:return: True if the soft deletion was successful without error, False otherwise.
"""
db.session.execute(
'''
UPDATE users SET
deleted=:deleted,
modified_date=:modified_date,
modified_app=:modified_app,
deleted_date=:deleted_date,
deleted_app=:deleted_app
WHERE username=:username
AND deleted IS FALSE
''', {
'username': user.username,
'deleted': user.deleted,
'modified_date': user.modified_date,
'modified_app': user.modified_app,
'deleted_date': user.deleted_date,
'deleted_app': user.deleted_app
})
return BasicDao.safe_commit()
def update_comment(comment: Comment) -> bool:
"""
Update a comment in the database. Certain fields (log_id, username, first, last) can't be modified.
:param comment: Object representing an updated comment.
:return: True if the comment is updated in the database, False otherwise.
"""
db.session.execute(
'''
UPDATE comments SET
time=:time,
content=:content,
modified_date=:modified_date,
modified_app=:modified_app
WHERE comment_id=:comment_id
AND deleted IS FALSE
''',
{
'comment_id': comment.comment_id,
'time': comment.time,
'content': comment.content,
'modified_date': comment.modified_date,
'modified_app': comment.modified_app,
}
)
return BasicDao.safe_commit()
def soft_delete_notification(notification: Notification) -> bool:
"""
Soft Delete a user notification from the database.
:param notification: Object representing a notification to soft delete.
:return: True if the soft deletion was successful without error, False otherwise.
"""
db.session.execute(
'''
UPDATE notifications SET
deleted=:deleted,
modified_date=:modified_date,
modified_app=:modified_app,
deleted_date=:deleted_date,
deleted_app=:deleted_app
WHERE notification_id=:notification_id
AND deleted IS FALSE
''', {
'notification_id': notification.notification_id,
'deleted': notification.deleted,
'modified_date': notification.modified_date,
'modified_app': notification.modified_app,
'deleted_date': notification.deleted_date,
'deleted_app': notification.deleted_app
})
return BasicDao.safe_commit()
def soft_delete_group_member(group_id: int, username: str) -> bool:
"""
Soft delete a group membership record.
:param group_id: Unique id of a group.
:param username: Unique name for a user.
:return: True if the group membership was soft deleted, False otherwise.
"""
db.session.execute(
'''
UPDATE groupmembers SET
deleted=:deleted,
deleted_date=:deleted_date,
deleted_app=:deleted_app
WHERE group_id=:group_id
AND username=:username
AND deleted IS FALSE
''',
{
'group_id': group_id,
'username': username,
'deleted': True,
'deleted_date': datetime.now(),
'deleted_app': 'saints-xctf-api'
}
)
return BasicDao.safe_commit()
def soft_delete_message(message: Message) -> bool:
"""
Soft Delete a group/team message from the database.
:param message: Object representing a message to soft delete.
:return: True if the soft deletion was successful without error, False otherwise.
"""
db.session.execute(
'''
UPDATE messages SET
deleted=:deleted,
modified_date=:modified_date,
modified_app=:modified_app,
deleted_date=:deleted_date,
deleted_app=:deleted_app
WHERE message_id=:message_id
AND deleted IS FALSE
''', {
'message_id': message.message_id,
'deleted': message.deleted,
'modified_date': message.modified_date,
'modified_app': message.modified_app,
'deleted_date': message.deleted_date,
'deleted_app': message.deleted_app
})
return BasicDao.safe_commit()
def soft_delete_log(log: Log) -> bool:
"""
Soft Delete an exercise log from the database.
:param log: Object representing a log to soft delete.
:return: True if the soft deletion was successful without error, False otherwise.
"""
db.session.execute(
'''
UPDATE logs SET
deleted=:deleted,
modified_date=:modified_date,
modified_app=:modified_app,
deleted_date=:deleted_date,
deleted_app=:deleted_app
WHERE log_id=:log_id
AND deleted IS FALSE
''',
{
'log_id': log.log_id,
'deleted': log.deleted,
'modified_date': log.modified_date,
'modified_app': log.modified_app,
'deleted_date': log.deleted_date,
'deleted_app': log.deleted_app
}
)
return BasicDao.safe_commit()
def add_flair(flair: Flair) -> bool:
"""
Add a flair item to the database.
:param flair: Object representing a flair for a user in the application.
:return: True if the flair is inserted into the database, False otherwise.
"""
db.session.add(flair)
return BasicDao.safe_commit()
def add_user(user: User) -> bool:
"""
Add a user if it has a valid activation code.
:param user: Object representing a user for the application.
:return: True if the user is inserted into the database, False otherwise.
"""
db.session.add(user)
return BasicDao.safe_commit()
def add_log(new_log: Log) -> bool:
"""
Add an exercise log to the database.
:param new_log: Object representing an exercise log for a user.
:return: True if the log is inserted into the database, False otherwise.
"""
db.session.add(new_log)
return BasicDao.safe_commit()
def add_forgot_password_code(code: ForgotPassword) -> bool:
"""
Insert a forgot password code and corresponding user into the database.
:param code: A ForgotPassword object representing a code that expires after a certain date.
:return: True if the code was inserted successfully, False otherwise.
"""
db.session.add(code)
return BasicDao.safe_commit()
def add_notification(new_notification: Notification) -> bool:
"""
Add a notification for a user to the database.
:param new_notification: Object representing a notification for a user.
:return: True if the notification is inserted into the database, False otherwise.
"""
db.session.add(new_notification)
return BasicDao.safe_commit()
def add_message(new_message: Message) -> bool:
"""
Add a group message to the database.
:param new_message: Object representing a message posted to a group.
:return: True if the message is inserted into the database, False otherwise.
"""
db.session.add(new_message)
return BasicDao.safe_commit()
def add_comment(new_comment: Comment) -> bool:
"""
Add a comment for an exercise log to the database.
:param new_comment: Object representing a comment for an exercise log.
:return: True if the comment is inserted into the database, False otherwise.
"""
db.session.add(new_comment)
return BasicDao.safe_commit()
def delete_notification_by_id(notification_id: int) -> bool:
"""
Delete a notification from the database based on its id.
:param notification_id: ID which uniquely identifies the notification.
:return: True if the deletion was successful without error, False otherwise.
"""
db.session.execute(
'DELETE FROM notifications WHERE notification_id=:notification_id AND deleted IS FALSE',
{'notification_id': notification_id})
return BasicDao.safe_commit()
def remove_code(code: Code) -> bool:
"""
Delete an activation code from the database.
:param code: Object model for a row in the code table.
:return: True if the activation code is deleted, False otherwise.
"""
db.session.execute(
'DELETE FROM codes WHERE activation_code=:activation_code AND deleted IS FALSE',
{'activation_code': code.activation_code})
return BasicDao.safe_commit()
def delete_message_by_id(message_id: int) -> bool:
"""
Delete a message from the database based on its id.
:param message_id: ID which uniquely identifies the message.
:return: True if the deletion was successful without error, False otherwise.
"""
db.session.execute(
'DELETE FROM messages WHERE message_id=:message_id AND deleted IS FALSE',
{'message_id': message_id})
return BasicDao.safe_commit()
def delete_comment_by_id(comment_id: int) -> bool:
"""
Delete a comment from the database based on its id.
:param comment_id: ID which uniquely identifies the comment.
:return: True if the deletion was successful without error, False otherwise.
"""
db.session.execute(
'DELETE FROM comments WHERE comment_id=:comment_id AND deleted IS FALSE',
{'comment_id': comment_id}
)
return BasicDao.safe_commit()
def delete_comments_by_log_id(log_id: int) -> bool:
"""
Delete comments from the database based on the log they are bound 2.
:param log_id: ID which uniquely identifies the log.
:return: True if the deletions were successful without error, False otherwise.
"""
db.session.execute(
'DELETE FROM comments WHERE log_id=:log_id AND deleted IS FALSE',
{'log_id': log_id}
)
return BasicDao.safe_commit()
def delete_log(log_id: int) -> bool:
"""
Delete a log from the database based on its id.
:param log_id: ID which uniquely identifies the log.
:return: True if the deletion was successful without error, False otherwise.
"""
db.session.execute(
'DELETE FROM logs WHERE log_id=:log_id AND deleted IS FALSE',
{'log_id': log_id}
)
return BasicDao.safe_commit()
def delete_user(username: str) -> bool:
"""
Delete a user from the database based on its username.
:param username: Username which uniquely identifies the user.
:return: True if the deletion was successful without error, False otherwise.
"""
db.session.execute('DELETE FROM teammembers WHERE username=:username',
{'username': username})
db.session.execute('DELETE FROM users WHERE username=:username',
{'username': username})
return BasicDao.safe_commit()
def delete_forgot_password_code(code: str) -> bool:
"""
Delete a forgot password code row from the database based on its code.
:param code: Value of the secret forgot password code.
:return: True if the deletion was successful without error, False otherwise.
"""
db.session.execute(
'''
DELETE FROM forgotpassword
WHERE forgot_code=:forgot_code
AND deleted IS FALSE
''', {'forgot_code': code})
return BasicDao.safe_commit()
def update_user_last_login(username: str) -> bool:
"""
Update the date of the previous login for a user.
:param username: Username which uniquely identifies the user.
:return: True if the update was successful, False otherwise
"""
db.session.execute(
'''
UPDATE users
SET last_signin=SYSDATE()
WHERE username=:username
AND deleted IS FALSE
''', {'username': username})
return BasicDao.safe_commit()
def update_notification(notification: Notification) -> bool:
"""
Update a notification in the database. Certain fields (notification_id, username, time, link, description)
can't be modified.
:param notification: Object representing an updated notification.
:return: True if the notification is updated in the database, False otherwise.
"""
db.session.execute(
'''
UPDATE notifications
SET viewed=:viewed
WHERE notification_id=:notification_id
AND deleted IS FALSE
''', {
'notification_id': notification.notification_id,
'viewed': notification.viewed
})
return BasicDao.safe_commit()
def update_user_password(username: str, password: str) -> bool:
"""
Update the password of a user. This operation can't be done using the update_user() function.
:param username: Username which uniquely identifies the user.
:param password: New password for a user.
:return: True if the update was successful, False otherwise
"""
db.session.execute(
'''
UPDATE users
SET password=:password
WHERE username=:username
AND deleted IS FALSE
''', {
'username': username,
'password': password
})
return BasicDao.safe_commit()
def accept_user_team_membership(username: str, team_name: str, updating_username: str) -> bool:
"""
Change the status of a users team membership to 'accepted'.
:param username: Unique identifier for the user who team membership is getting updated.
:param team_name: Unique name of a team.
:param updating_username: Unique identifier for the user who is updating a team membership.
:return: Whether or not the team membership was successfully updated.
"""
db.session.execute(
'''
UPDATE teammembers SET
status = 'accepted',
modified_date=CURRENT_TIMESTAMP(),
modified_user=:updating_username,
modified_app='saints-xctf-api'
WHERE username=:username
AND team_name=:team_name
AND deleted IS FALSE
''',
{'username': username, 'team_name': team_name, 'updating_username': updating_username}
)
return BasicDao.safe_commit()
def update_log(log: Log) -> bool:
"""
Update a log in the database.
:param log: Object representing an updated log.
:return: True if the log is updated in the database, False otherwise.
"""
db.session.execute(
'''
UPDATE logs SET
name=:name,
location=:location,
date=:date,
type=:type,
distance=:distance,
metric=:metric,
time=:time,
miles=:miles,
pace=:pace,
feel=:feel,
description=:description
WHERE log_id=:log_id
AND deleted IS FALSE
''',
{
'name': log.name,
'location': log.location,
'date': log.date,
'type': log.type,
'distance': log.distance,
'metric': log.metric,
'time': log.time,
'miles': log.miles,
'pace': log.pace,
'feel': log.feel,
'description': log.description,
'log_id': log.log_id
}
)
return BasicDao.safe_commit()
def update_message(message: Message) -> bool:
"""
Update a message in the database. Certain fields (message_id, username, first, last, group_name, time)
can't be modified.
:param message: Object representing an updated message.
:return: True if the message is updated in the database, False otherwise.
"""
db.session.execute(
'''
UPDATE messages SET
content=:content,
modified_date=:modified_date,
modified_app=:modified_app
WHERE message_id=:message_id
AND deleted IS FALSE
''', {
'message_id': message.message_id,
'content': message.content,
'modified_date': message.modified_date,
'modified_app': message.modified_app
})
return BasicDao.safe_commit()