def get_problem_owner(problem_id):
"""Gets all photos posted by user to problem."""
with db_pool().manager() as conn:
cursor = conn.cursor()
query = """SELECT `user_id` FROM `problem` WHERE `id`=%s;"""
cursor.execute(query, (problem_id,))
return cursor.fetchone()
def get_problem_owner(problem_id):
"""Gets all photos posted by user to problem."""
with db_pool().manager() as conn:
cursor = conn.cursor()
query = """SELECT `user_id` FROM `problem` WHERE `id`=%s;"""
cursor.execute(query, (problem_id, ))
return cursor.fetchone()
def count_users():
"""Users per page
"""
with db_pool().manager() as conn:
cursor = conn.cursor()
query = """SELECT COUNT(*) FROM `user`;"""
cursor.execute(query)
return cursor.fetchone()
def count_users():
"""Users per page
"""
with db_pool().manager() as conn:
cursor = conn.cursor()
query = """SELECT COUNT(*) FROM `user`;"""
cursor.execute(query)
return cursor.fetchone()
def get_all_resources():
"""Get all resources.
:return: tuple of resources
"""
with db_pool().manager() as conn:
cursor = conn.cursor()
query = """SELECT `id`, `resource_name` FROM `resource`;"""
cursor.execute(query)
return cursor.fetchall()
def delete_role_by_id(role_id):
"""Delete role name in db.
:params: role_id - key for searching resource name in DB for deleting
"""
with db_pool().manager() as conn:
cursor = conn.cursor()
query = """DELETE FROM `role` WHERE `id`=%s;"""
cursor.execute(query, (role_id, ))
conn.commit()
def delete_user_avatar(user_id):
"""Deletes user profile photo from db.
:params: user_id - unique id user
"""
with db_pool().manager() as conn:
cursor = conn.cursor()
query = """UPDATE `user` SET `avatar`='' WHERE `id`=%s;"""
cursor.execute(query, (user_id,))
conn.commit()
def get_all_roles():
"""Return all roles in db.
:return: tuple, containing all roles
"""
with db_pool().manager() as conn:
cursor = conn.cursor()
query = """SELECT `id`, `name` FROM `role`;"""
cursor.execute(query)
return cursor.fetchall()
def insert_role(role_name):
"""Insert new role into db.
:params: role_name - name of role
"""
with db_pool().manager() as conn:
cursor = conn.cursor()
query = """INSERT INTO `role` (`name`) VALUES (%s);"""
cursor.execute(query, (role_name,))
conn.commit()
def insert_role(role_name):
"""Insert new role into db.
:params: role_name - name of role
"""
with db_pool().manager() as conn:
cursor = conn.cursor()
query = """INSERT INTO `role` (`name`) VALUES (%s);"""
cursor.execute(query, (role_name, ))
conn.commit()
def delete_user_avatar(user_id):
"""Deletes user profile photo from db.
:params: user_id - unique id user
"""
with db_pool().manager() as conn:
cursor = conn.cursor()
query = """UPDATE `user` SET `avatar`='' WHERE `id`=%s;"""
cursor.execute(query, (user_id, ))
conn.commit()
def delete_permission_by_id(permission_id):
"""Delete permission row in db.
:params: permission_id - key for searching resource name in DB for deleting
"""
with db_pool().manager() as conn:
cursor = conn.cursor()
query = """DELETE FROM `permission` WHERE `id`=%s;"""
cursor.execute(query, (permission_id, ))
conn.commit()
def delete_role_by_id(role_id):
"""Delete role name in db.
:params: role_id - key for searching resource name in DB for deleting
"""
with db_pool().manager() as conn:
cursor = conn.cursor()
query = """DELETE FROM `role` WHERE `id`=%s;"""
cursor.execute(query, (role_id,))
conn.commit()
def get_pages_titles():
"""This method retrieves brief info from db
about all pages(ex-resources).
"""
with db_pool().manager() as conn:
cursor = conn.cursor()
query = """SELECT `id`, `title`, `alias`, `is_enabled` FROM `page`;"""
cursor.execute(query)
return cursor.fetchall()
def get_problem_photos(problem_id):
"""Gets all photos posted by user to problem."""
with db_pool().manager() as conn:
cursor = conn.cursor()
query = """SELECT `name`, `description`, `user_id`
FROM `photo` WHERE `problem_id`=%s;
"""
cursor.execute(query, (problem_id,))
return cursor.fetchall()
def get_pages_titles():
"""This method retrieves brief info from db
about all pages(ex-resources).
"""
with db_pool().manager() as conn:
cursor = conn.cursor()
query = """SELECT `id`, `title`, `alias`, `is_enabled` FROM `page`;"""
cursor.execute(query)
return cursor.fetchall()
def get_problem_photos(problem_id):
"""Gets all photos posted by user to problem."""
with db_pool().manager() as conn:
cursor = conn.cursor()
query = """SELECT `name`, `description`, `user_id`
FROM `photo` WHERE `problem_id`=%s;
"""
cursor.execute(query, (problem_id, ))
return cursor.fetchall()
def delete_permission_by_id(permission_id):
"""Delete permission row in db.
:params: permission_id - key for searching resource name in DB for deleting
"""
with db_pool().manager() as conn:
cursor = conn.cursor()
query = """DELETE FROM `permission` WHERE `id`=%s;"""
cursor.execute(query, (permission_id,))
conn.commit()
def get_all_resources():
"""Get all resources.
:return: tuple of resources
"""
with db_pool().manager() as conn:
cursor = conn.cursor()
query = """SELECT `id`, `resource_name` FROM `resource`;"""
cursor.execute(query)
return cursor.fetchall()
def get_all_roles():
"""Return all roles in db.
:return: tuple, containing all roles
"""
with db_pool().manager() as conn:
cursor = conn.cursor()
query = """SELECT `id`, `name` FROM `role`;"""
cursor.execute(query)
return cursor.fetchall()
def delete_page_by_id(page_id):
"""This method deletes page by it's id from db.
:params: id - id of the page, which needs to be
deleted.
"""
with db_pool().manager() as conn:
cursor = conn.cursor()
query = """DELETE FROM `page` WHERE `id`=%s;"""
cursor.execute(query, (page_id, ))
conn.commit()
def delete_page_by_id(page_id):
"""This method deletes page by it's id from db.
:params: id - id of the page, which needs to be
deleted.
"""
with db_pool().manager() as conn:
cursor = conn.cursor()
query = """DELETE FROM `page` WHERE `id`=%s;"""
cursor.execute(query, (page_id,))
conn.commit()
def change_user_password(user_id, new_password):
"""Change password to user account.
:params: new_password - new password
user_id - id of user
"""
with db_pool().manager() as conn:
cursor = conn.cursor()
query = """UPDATE `user` SET `password`=%s WHERE `id`=%s;"""
cursor.execute(query, (new_password, user_id))
conn.commit()
def change_user_role(role_id, user_id):
"""Update users role.
:params: user_id - id of user
role_id - id of role
"""
with db_pool().manager() as conn:
cursor = conn.cursor()
query = """UPDATE `user_role` SET `role_id`=%s WHERE `user_id`=%s;"""
cursor.execute(query, (role_id, user_id))
conn.commit()
def edit_role(new_role_name, role_id):
"""Edit role name.
:params: role_name - new name of role
role_id - if of role
"""
with db_pool().manager() as conn:
cursor = conn.cursor()
query = """UPDATE `role` SET `name`=%s WHERE `id`=%s;"""
cursor.execute(query, (new_role_name, role_id))
conn.commit()
def get_resource_id(resource_name):
"""Gets resource id.
:params: resource_name - name of resource
:return: tuple, containing id
"""
with db_pool().manager() as conn:
cursor = conn.cursor()
sql = """SELECT `id` FROM `resource` WHERE `resource_name`=%s;"""
cursor.execute(sql, (resource_name,))
return cursor.fetchone()
def change_user_role(role_id, user_id):
"""Update users role.
:params: user_id - id of user
role_id - id of role
"""
with db_pool().manager() as conn:
cursor = conn.cursor()
query = """UPDATE `user_role` SET `role_id`=%s WHERE `user_id`=%s;"""
cursor.execute(query, (role_id, user_id))
conn.commit()
def get_resource_id(resource_name):
"""Gets resource id.
:params: resource_name - name of resource
:return: tuple, containing id
"""
with db_pool().manager() as conn:
cursor = conn.cursor()
sql = """SELECT `id` FROM `resource` WHERE `resource_name`=%s;"""
cursor.execute(sql, (resource_name, ))
return cursor.fetchone()
def insert_user_avatar(user_id, img_path):
"""Insert new user avatar into db.
:params: user_id - unique id user
img_path - path to avatar image
"""
with db_pool().manager() as conn:
cursor = conn.cursor()
query = """UPDATE `user` SET `avatar`=%s WHERE id=%s;"""
cursor.execute(query, (img_path, user_id))
conn.commit()
def edit_role(new_role_name, role_id):
"""Edit role name.
:params: role_name - new name of role
role_id - if of role
"""
with db_pool().manager() as conn:
cursor = conn.cursor()
query = """UPDATE `role` SET `name`=%s WHERE `id`=%s;"""
cursor.execute(query, (new_role_name, role_id))
conn.commit()
def insert_user_avatar(user_id, img_path):
"""Insert new user avatar into db.
:params: user_id - unique id user
img_path - path to avatar image
"""
with db_pool().manager() as conn:
cursor = conn.cursor()
query = """UPDATE `user` SET `avatar`=%s WHERE id=%s;"""
cursor.execute(query, (img_path, user_id))
conn.commit()
def change_user_password(user_id, new_password):
"""Change password to user account.
:params: new_password - new password
user_id - id of user
"""
with db_pool().manager() as conn:
cursor = conn.cursor()
query = """UPDATE `user` SET `password`=%s WHERE `id`=%s;"""
cursor.execute(query, (new_password, user_id))
conn.commit()
def add_resource(resource_name):
"""Adds new resource in db.
:params: resource_name - name of new resource
"""
with db_pool().manager() as conn:
cursor = conn.cursor()
query = """INSERT INTO `resource` (`resource_name`)
VALUES (%s);
"""
cursor.execute(query, (resource_name, ))
conn.commit()
def delete_permissions_by_role_id(role_id):
"""Deletes all permissions from role_permission table by role_id.
:params: role_id - id of role
"""
with db_pool().manager() as conn:
cursor = conn.cursor()
query = """DELETE `role_permission` FROM `role_permission`
WHERE `role_id`=%s;
"""
cursor.execute(query, (role_id, ))
conn.commit()
def delete_permissions_by_role_id(role_id):
"""Deletes all permissions from role_permission table by role_id.
:params: role_id - id of role
"""
with db_pool().manager() as conn:
cursor = conn.cursor()
query = """DELETE `role_permission` FROM `role_permission`
WHERE `role_id`=%s;
"""
cursor.execute(query, (role_id,))
conn.commit()
def get_role_by_name(name='user'):
"""Gets role id by it's name.
:params: name - name of role, default - 'user'
:return: tuple with id of role
"""
with db_pool().manager() as conn:
cursor = conn.cursor()
query = """SELECT `id` FROM `role`
WHERE `name`=%s;"""
cursor.execute(query, (name, ))
return cursor.fetchone()
def get_role_by_name(name='user'):
"""Gets role id by it's name.
:params: name - name of role, default - 'user'
:return: tuple with id of role
"""
with db_pool().manager() as conn:
cursor = conn.cursor()
query = """SELECT `id` FROM `role`
WHERE `name`=%s;"""
cursor.execute(query, (name,))
return cursor.fetchone()
def add_resource(resource_name):
"""Adds new resource in db.
:params: resource_name - name of new resource
"""
with db_pool().manager() as conn:
cursor = conn.cursor()
query = """INSERT INTO `resource` (`resource_name`)
VALUES (%s);
"""
cursor.execute(query, (resource_name,))
conn.commit()
def get_all_permissions_by_resource(resource_id):
"""Find all permissions by resource.
:params: resource_id - id of resource
:return: tuple, containing permissions
"""
with db_pool().manager() as conn:
cursor = conn.cursor()
query = """SELECT `id`, `action`, `modifier`, `description`
FROM `permission` WHERE `resource_id`=%s;
"""
cursor.execute(query, (resource_id, ))
return cursor.fetchall()
def edit_resource_name(new_resource_name, resource_id):
"""Edit resource name.
:params: new_resource_name - new name of resource
resource_id - id of resource we change name
"""
with db_pool().manager() as conn:
cursor = conn.cursor()
query = """UPDATE `resource` SET `resource_name`=%s
WHERE `id`=%s;
"""
cursor.execute(query, (new_resource_name, resource_id))
conn.commit()
def get_all_permissions_by_resource(resource_id):
"""Find all permissions by resource.
:params: resource_id - id of resource
:return: tuple, containing permissions
"""
with db_pool().manager() as conn:
cursor = conn.cursor()
query = """SELECT `id`, `action`, `modifier`, `description`
FROM `permission` WHERE `resource_id`=%s;
"""
cursor.execute(query, (resource_id,))
return cursor.fetchall()
def edit_resource_name(new_resource_name, resource_id):
"""Edit resource name.
:params: new_resource_name - new name of resource
resource_id - id of resource we change name
"""
with db_pool().manager() as conn:
cursor = conn.cursor()
query = """UPDATE `resource` SET `resource_name`=%s
WHERE `id`=%s;
"""
cursor.execute(query, (new_resource_name, resource_id))
conn.commit()
def get_user_role_by_id(user_id):
"""Get all resources.
:return: tuple of resources
"""
with db_pool().manager() as conn:
cursor = conn.cursor()
query = """SELECT r.name FROM `role` AS r
INNER JOIN `user_role` AS ur ON r.id=ur.role_id
WHERE ur.user_id=%s;
"""
cursor.execute(query, (user_id, ))
return cursor.fetchone()
def get_user_by_oauth_id(user_id):
"""Return user, found by id.
:params: user_id - id of user
:return: tuple with user info
"""
with db_pool().manager() as conn:
cursor = conn.cursor()
query = """SELECT `id`, `first_name`, `last_name`, `email`, `password`
FROM `user` WHERE `oauth_uid`=%s;
"""
cursor.execute(query, (user_id, ))
return cursor.fetchone()
def get_user_by_oauth_id(user_id):
"""Return user, found by id.
:params: user_id - id of user
:return: tuple with user info
"""
with db_pool().manager() as conn:
cursor = conn.cursor()
query = """SELECT `id`, `first_name`, `last_name`, `email`, `password`
FROM `user` WHERE `oauth_uid`=%s;
"""
cursor.execute(query, (user_id,))
return cursor.fetchone()
def get_user_role_by_id(user_id):
"""Get all resources.
:return: tuple of resources
"""
with db_pool().manager() as conn:
cursor = conn.cursor()
query = """SELECT r.name FROM `role` AS r
INNER JOIN `user_role` AS ur ON r.id=ur.role_id
WHERE ur.user_id=%s;
"""
cursor.execute(query, (user_id,))
return cursor.fetchone()
def get_all_problems():
"""Return all problems in db.
:return: tuple, containing all problems
"""
with db_pool().manager() as conn:
cursor = conn.cursor()
query = """SELECT `id`, `title`, `latitude`, `longitude`,
`problem_type_id`, `status`, `created_date`
FROM `problem`;
"""
cursor.execute(query)
return cursor.fetchall()
def get_user_problems(user_id):
"""Gets all problems posted by given user."""
with db_pool().manager() as conn:
cursor = conn.cursor()
query = """SELECT `id`, `title`, `latitude`, `longitude`,
`problem_type_id`, `status`, `created_date`, `is_enabled`,
`severity`
FROM `problem`
WHERE `user_id`=%s
"""
cursor.execute(query, (user_id,))
return cursor.fetchall()
def add_users_role(user_id, role_id):
"""Adds to recenty registered user role "User".
:params: user_id - id of recenty created user
role_id - default is "User"
"""
with db_pool().manager() as conn:
conn.autocommit(True)
cursor = conn.cursor()
query = """INSERT INTO `user_role` (`user_id`, `role_id`)
VALUES (%s, %s);
"""
cursor.execute(query, (user_id, role_id))
def get_user_problems(user_id):
"""Gets all problems posted by given user."""
with db_pool().manager() as conn:
cursor = conn.cursor()
query = """SELECT `id`, `title`, `latitude`, `longitude`,
`problem_type_id`, `status`, `created_date`, `is_enabled`,
`severity`
FROM `problem`
WHERE `user_id`=%s
"""
cursor.execute(query, (user_id, ))
return cursor.fetchall()
def add_users_role(user_id, role_id):
"""Adds to recenty registered user role "User".
:params: user_id - id of recenty created user
role_id - default is "User"
"""
with db_pool().manager() as conn:
conn.autocommit(True)
cursor = conn.cursor()
query = """INSERT INTO `user_role` (`user_id`, `role_id`)
VALUES (%s, %s);
"""
cursor.execute(query, (user_id, role_id))
def get_all_problems():
"""Return all problems in db.
:return: tuple, containing all problems
"""
with db_pool().manager() as conn:
cursor = conn.cursor()
query = """SELECT `id`, `title`, `latitude`, `longitude`,
`problem_type_id`, `status`, `created_date`
FROM `problem`;
"""
cursor.execute(query)
return cursor.fetchall()
def get_user_by_email(email):
"""Return user, found by email.
:params: email - user email
:retrun: tuple with user info
"""
with db_pool().manager() as conn:
cursor = conn.cursor()
query = """SELECT `id`, `first_name`, `last_name`, `email`,
`password`, `avatar`
FROM `user` WHERE `email`=%s;
"""
cursor.execute(query, (email,))
return cursor.fetchone()
def get_id_problem_owner(problem_id):
"""Return problem, found by id.
:params: problem_id - id of problem which was selected
:return: tuple with problem_activity info
"""
with db_pool().manager() as conn:
cursor = conn.cursor()
query = """SELECT `created_date`, `problem_id`, `user_id`,
`activity_type` FROM `problem_activity`
WHERE `problem_id`=%s;
"""
cursor.execute(query, (problem_id, ))
return cursor.fetchone()
def get_all_users():
"""Return all registered users from db.
:return: tuples with user info
"""
with db_pool().manager() as conn:
cursor = conn.cursor()
query = """SELECT u.id, u.first_name, u.last_name, u.email, r.name
FROM `user_role` as ur
INNER JOIN `user` as u ON ur.user_id=u.id
INNER JOIN `role` as r ON ur.role_id=r.id;
"""
cursor.execute(query)
return cursor.fetchall()
def get_users_pagination(offset, per_page):
"""Users per page
"""
with db_pool().manager() as conn:
cursor = conn.cursor()
query = """SELECT u.id, u.first_name, u.last_name, u.email, r.name
FROM `user_role` AS ur
INNER JOIN `user` AS u ON ur.user_id=u.id
INNER JOIN `role` AS r ON ur.role_id=r.id
ORDER BY `id` LIMIT %s,%s;
"""
cursor.execute(query % (offset, per_page))
return cursor.fetchall()
def get_all_users():
"""Return all registered users from db.
:return: tuples with user info
"""
with db_pool().manager() as conn:
cursor = conn.cursor()
query = """SELECT u.id, u.first_name, u.last_name, u.email, r.name
FROM `user_role` as ur
INNER JOIN `user` as u ON ur.user_id=u.id
INNER JOIN `role` as r ON ur.role_id=r.id;
"""
cursor.execute(query)
return cursor.fetchall()
def get_users_pagination(offset, per_page):
"""Users per page
"""
with db_pool().manager() as conn:
cursor = conn.cursor()
query = """SELECT u.id, u.first_name, u.last_name, u.email, r.name
FROM `user_role` AS ur
INNER JOIN `user` AS u ON ur.user_id=u.id
INNER JOIN `role` AS r ON ur.role_id=r.id
ORDER BY `id` LIMIT %s,%s;
"""
cursor.execute(query % (offset, per_page))
return cursor.fetchall()
def get_id_problem_owner(problem_id):
"""Return problem, found by id.
:params: problem_id - id of problem which was selected
:return: tuple with problem_activity info
"""
with db_pool().manager() as conn:
cursor = conn.cursor()
query = """SELECT `created_date`, `problem_id`, `user_id`,
`activity_type` FROM `problem_activity`
WHERE `problem_id`=%s;
"""
cursor.execute(query, (problem_id, ))
return cursor.fetchone()
def get_user_by_email(email):
"""Return user, found by email.
:params: email - user email
:retrun: tuple with user info
"""
with db_pool().manager() as conn:
cursor = conn.cursor()
query = """SELECT `id`, `first_name`, `last_name`, `email`,
`password`, `avatar`
FROM `user` WHERE `email`=%s;
"""
cursor.execute(query, (email, ))
return cursor.fetchone()
