def get_user_by_token(token: str) -> namedtuple:
db = get_db()
with db.cursor() as cursor:
cursor.execute('''
SELECT
app_user.user_id,
username,
email,
first_name,
last_name,
created_at,
updated_at,
is_active,
token
FROM app_user
JOIN token ON token.user_id = app_user.user_id
AND token.token = %(token)s
AND token.expires_at > %(expires_at)s
;
''', {
'token': token,
'expires_at': datetime.now(timezone.utc) + timedelta(minutes=1)
})
db.commit()
user = cursor.fetchone()
return user
def log_in(username, password):
db = get_db()
with db.cursor() as cursor:
query = '''
SELECT user_id, is_active, password_hash
FROM app_user
WHERE username = %(username)s
;
'''
cursor.execute(query, {'username': username})
user = cursor.fetchone()
if user and not user.is_active:
db.commit()
raise UserIsNotActive()
elif user and check_password(password, user.password_hash):
token, expires_at = generate_auth_token_with_expiry_date()
cursor.execute('''
INSERT INTO token (user_id, token, expires_at)
VALUES (%(user_id)s, %(token)s, %(expires_at)s);
''', {
'user_id': user.user_id,
'token': token,
'expires_at': expires_at
})
db.commit()
return token, expires_at
raise BadCredentials()
def register_new_user(username, email, password, is_activation_required=True):
db = get_db()
with db.cursor() as cursor:
try:
password_hash = hash_password(password)
cursor.execute('''
INSERT INTO app_user (username, email, password_hash, is_active)
VALUES (%(username)s, %(email)s, %(password_hash)s, %(is_active)s)
RETURNING *;
''', {
'username': username,
'email': email,
'password_hash': password_hash,
'is_active': not is_activation_required
})
db.commit()
new_user = cursor.fetchone()
if is_activation_required:
token = create_activation_token(new_user.user_id).decode('utf-8')
send_activation_link(new_user.email, token)
return new_user
except psycopg2.errors.UniqueViolation as error:
# TODO: you have to look at the database to know which constraint to catch
# https://www.psycopg.org/docs/extensions.html#psycopg2.extensions.Diagnostics
db.rollback()
constraint = getattr(error.diag, 'constraint_name')
if constraint == 'app_user_username_key':
raise DuplicateUsername()
elif constraint == 'app_user_email_key':
raise DuplicateEmail()
raise error
def reset_password(token, new_password):
try:
decoded = jwt.decode(
token,
os.getenv('TASKAFARIAN_SECRET_KEY'),
algorithms=['HS256'],
issuer=os.getenv('TASKAFARIAN_ENV')
)
if 'action' not in decoded or decoded['action'] != ActionType.PASSWORD_RESET.value:
# unexpected action. maybe user tried to reuse token from other flow
raise BadCredentials()
password_hash = hash_password(new_password)
db = get_db()
with db.cursor() as cursor:
cursor.execute('''
UPDATE app_user
SET password_hash = %(password_hash)s
WHERE email = %(email)s
;
''', {'password_hash': password_hash, 'email': decoded['email']})
db.commit()
except jwt.exceptions.PyJWTError:
raise BadCredentials()
def load_fixture(name):
from chalicelib.core.database import get_db
db = get_db()
with db.cursor() as cursor:
with (Path() / 'tests/fixtures' / name).open() as f:
cursor.execute(f.read())
db.commit()
close_db()
def log_out(token):
db = get_db()
with db.cursor() as cursor:
cursor.execute('''
DELETE
FROM token
WHERE token = %(token)s
''', {'token': token})
db.commit()
def activate_user(user_id):
db = get_db()
with db.cursor() as cursor:
cursor.execute('''
UPDATE app_user
SET is_active = true
WHERE user_id = %(user_id)s
;
''', {'user_id': user_id})
db.commit()
def get_user_role_in_team(user_id: int, team_id: int):
db = get_db()
with db.cursor() as cursor:
cursor.execute('''
SELECT user_role
FROM user_to_team
WHERE user_id = %(user_id)s AND team_id = %(team_id)s
;
''', {'user_id': user_id, 'team_id': team_id})
return cursor.fetchone()
def get_teams(user_id: int):
db = get_db()
with db.cursor() as cursor:
cursor.execute('''
SELECT team_id, user_role
FROM user_to_team
WHERE user_id = %(user_id)s
;
''', {'user_id': user_id})
return cursor.fetchall()
def handle_exception(exception):
response = exception.get_response()
response.data = json.dumps({
"status": exception.code,
"detail": exception.description
})
response.content_type = "application/json"
if exception.code == 500:
db = get_db()
if db:
db.rollback()
return response
def is_valid_token(token):
db = get_db()
with db.cursor() as cursor:
cursor.execute('''
SELECT expires_at
FROM token
WHERE token = %(token)s
''', {'token': token})
db.commit()
token = cursor.fetchone()
if token.expires_at > datetime.now(timezone.utc) + timedelta(minutes=1):
return True
else:
return False
def update_task(user: namedtuple, task_id: int, details: dict) -> dict:
"""
Update task details such as name, estimation and etc.
"""
db = get_db()
with db.cursor() as cursor:
changes = [
SQL('{field} = {value}').format(field=Identifier(field),
value=Placeholder(field))
for field in details.keys()
]
query = SQL('''
WITH prepare_task_for_update(task_id) as (
SELECT task.task_id
FROM task
INNER JOIN user_to_team
ON user_to_team.team_id = task.team_id
AND user_to_team.user_id = %(user_id)s
WHERE task.task_id = %(task_id)s
)
UPDATE task
SET {changes}
FROM prepare_task_for_update
WHERE task.task_id = prepare_task_for_update.task_id
RETURNING task.task_id,
task.project_id,
task.team_id,
task.name,
task.description,
task.estimation,
task.status,
task.created_at,
task.created_by,
task.due_date,
task.assignee_id
;
''').format(changes=SQL(', ').join(changes))
params = {**details, 'task_id': task_id, 'user_id': user.user_id}
cursor.execute(query, params)
db.commit()
updated_task = cursor.fetchone()
if not updated_task:
raise EntityNotFound()
return updated_task
def get_user(username):
from chalicelib.core.database import get_db
db = get_db()
with db.cursor() as cursor:
query = '''
SELECT *
FROM app_user
LEFT JOIN token using (user_id)
WHERE username = %(username)s
;
'''
params = {'username': username}
cursor.execute(query, params)
db.commit()
return cursor.fetchone()
def create_task(user,
name,
status,
created_by,
assignee_id=None,
due_date=None,
estimation=None,
description='',
team_id=None,
project_id=None):
db = get_db()
with db.cursor() as cursor:
query = '''
INSERT INTO task(name, status, created_by, assignee_id, due_date, estimation, description, team_id, project_id)
VALUES (
%(name)s,
%(status)s,
%(created_by)s,
%(assignee_id)s,
%(due_date)s,
%(estimation)s,
%(description)s,
%(team_id)s,
%(project_id)s
)
RETURNING task_id
;
'''
params = {
'name': name,
'status': status,
'created_by': created_by,
'assignee_id': assignee_id,
'due_date': due_date,
'estimation': estimation,
'description': description,
'team_id': team_id,
'project_id': project_id
}
cursor.execute(query, params)
insertion_result = cursor.fetchone()
db.commit()
return fetch(user, insertion_result.task_id)
def request_password_reset(email: str):
db = get_db()
with db.cursor() as cursor:
cursor.execute('''
SELECT email
FROM app_user
WHERE email = %(email)s
;
''', {'email': email})
db.commit()
user = cursor.fetchone()
if not user:
# no user with such email
raise UserNotFound()
token = create_token_for_password_reset(email).decode('utf-8')
send_password_reset_by_email(email, token)
def fetch(user: namedtuple, task_id: int):
db = get_db()
with db.cursor() as cursor:
query = '''
SELECT
task.task_id,
task.project_id,
task.team_id,
task.name,
task.description,
task.estimation,
task.status,
task.created_at,
task.due_date,
jsonb_build_object(
'username', creator.username,
'user_id', creator.user_id,
'first_name', creator.first_name,
'last_name', creator.last_name
) as creator,
jsonb_build_object(
'username', assignee.username,
'user_id', assignee.user_id,
'first_name', assignee.first_name,
'last_name', assignee.last_name
) as assignee
FROM task
LEFT JOIN user_to_team
ON user_to_team.team_id = task.team_id AND user_to_team.user_id = %(user_id)s
LEFT JOIN app_user AS creator
ON creator.user_id = task.created_by
LEFT JOIN app_user AS assignee
ON assignee.user_id = task.assignee_id
WHERE task.task_id = %(task_id)s
AND (task.created_by = %(user_id)s OR user_to_team.user_role IS NOT NULL)
;
'''
params = {'task_id': task_id, 'user_id': user.user_id}
cursor.execute(query, params)
db.commit()
return cursor.fetchone()
def get_user_by_id(user_id):
db = get_db()
with db.cursor() as cursor:
cursor.execute('''
SELECT
user_id,
username,
email,
first_name,
last_name,
created_at,
updated_at,
is_active
FROM app_user
WHERE user_id = %(user_id)s
;
''', {'user_id': user_id})
db.commit()
return cursor.fetchone()
def deactivate_user(user_id):
"""Deactivates user by setting the is_active flag to false and removing all tokens."""
db = get_db()
with db.cursor() as cursor:
query = '''
UPDATE app_user
SET is_active = true
WHERE user_id = %(user_id)s
;
DELETE
FROM token
WHERE user_id = %(user_id)s
;
'''
params = {'user_id': user_id}
cursor.execute(query, params)
db.commit()
def delete_tasks(user: namedtuple,
task_ids: Tuple[int, ...],
all_or_nothing=True) -> List[int]:
"""Delete tasks.
all_or_nothing=True - commit if all tasks deleted successfully otherwise abort
all_or_nothing=False - commit even if one or more tasks could not be deleted
"""
db = get_db()
with db.cursor() as cursor:
query = '''
with prepare_for_deletion(task_id) as (
SELECT task_id
FROM task
INNER JOIN user_to_team
ON user_to_team.team_id = task.team_id
AND user_to_team.user_id = %(user_id)s
WHERE task_id IN %(task_ids)s
)
DELETE
FROM task
WHERE task.task_id IN (SELECT task_id FROM prepare_for_deletion)
RETURNING task.task_id
;
'''
params = {'task_ids': tuple(task_ids), 'user_id': user.user_id}
cursor.execute(query, params)
deleted = cursor.fetchall()
deleted_task_ids = [task.task_id for task in deleted]
if all_or_nothing and len(deleted_task_ids) != len(task_ids):
# could not delete some of the tasks, aborting
db.rollback()
raise DeletionError(list(set(task_ids) - set(deleted_task_ids)))
db.commit()
return deleted_task_ids
def fetch_many(user, offset: int = 0, limit: int = 20):
params = {
'user_id': user.user_id,
'limit': limit,
'offset': offset,
}
db = get_db()
with db.cursor() as cursor:
query = SQL('''
WITH extended_task AS (
SELECT task.task_id,
task.project_id,
task.team_id,
task.name,
task.description,
task.estimation,
task.status,
task.created_at,
task.due_date,
task.created_by,
task.assignee_id,
coalesce(jsonb_agg(time_entries) filter ( where time_entries.task_id is not null ), '[]'::jsonb) as time_entries
FROM task
LEFT JOIN LATERAL (
SELECT task_time_entry.time_entry_id,
task_time_entry.task_id,
task_time_entry.assignee_id,
task_time_entry.start_datetime,
task_time_entry.end_datetime
FROM task_time_entry
WHERE task_time_entry.task_id = task.task_id
ORDER BY task_time_entry.start_datetime DESC
) AS time_entries ON true
WHERE task.created_by = %(user_id)s
GROUP BY task.task_id
OFFSET %(offset)s
LIMIT %(limit)s
),
tasks_with_user_and_time_info AS (
SELECT extended_task.task_id,
extended_task.project_id,
extended_task.team_id,
extended_task.name,
extended_task.description,
extended_task.estimation,
extended_task.status,
extended_task.created_at,
extended_task.due_date,
extended_task.time_entries,
jsonb_build_object(
'username', creator.username,
'user_id', creator.user_id,
'first_name', creator.first_name,
'last_name', creator.last_name
) as creator,
jsonb_build_object(
'username', assignee.username,
'user_id', assignee.user_id,
'first_name', assignee.first_name,
'last_name', assignee.last_name
) as assignee
FROM extended_task
LEFT JOIN app_user AS creator
ON creator.user_id = extended_task.created_by
LEFT JOIN app_user AS assignee
ON assignee.user_id = extended_task.assignee_id
)
SELECT *
FROM tasks_with_user_and_time_info
ORDER BY tasks_with_user_and_time_info.created_at DESC
;
''')
cursor.execute(query, params)
tasks = cursor.fetchall()
return {
'entities': tasks,
'meta': {
'count': len(tasks),
'offset': offset,
'limit': limit
}
}
