def get_one_question(question_id, answers=False):
"""
Returns a single question and corresponding anwers with comments in dict
@param question_id int The id of the question to be displayed
@param answers boolean True if you need only the question, False if you need both
@return dict The result set of questions and answers
"""
answer = None
data = (question_id, )
sql = """SELECT q.title,
q.message AS question_body,
q.id,
to_char(q.submission_time, 'YYYY-MM-DD HH24:MI') AS question_date,
q.view_number,
q.vote_number,
q.user_id AS question_user_id,
u.user_name AS question_user_name,
c.message AS comment_body,
to_char(c.submission_time, 'YYYY-MM-DD HH24:MI') AS comment_date,
c.id as comment_id,
c.user_id as question_comment_user_id,
us.user_name AS question_comment_user_name
FROM question q
LEFT OUTER JOIN comment c ON q.id = c.question_id
LEFT OUTER JOIN users u ON q.user_id = u.id
LEFT OUTER JOIN users us ON c.user_id = us.id
WHERE q.id = %s
ORDER BY c.submission_time DESC;"""
question = db.perform_query(sql, data)
if answers:
sql2 = """SELECT a.message AS answer_body,
a.id AS answer_id,
to_char(a.submission_time, 'YYYY-MM-DD HH24:MI') AS answer_date,
a.vote_number,
a.answered_by,
COALESCE(a.accepted_by, 0) AS accepted_by,
u.user_name AS answer_user_name,
c.message AS comment_body,
to_char(c.submission_time, 'YYYY-MM-DD HH24:MI') AS comment_date,
c.id AS comment_id,
c.answer_id AS comment_answer_id,
c.user_id,
us.user_name AS answer_comment_user_name
FROM answer a
LEFT OUTER JOIN comment c ON a.id = c.answer_id
LEFT OUTER JOIN users u ON a.answered_by = u.id
LEFT OUTER JOIN users us ON c.user_id = us.id
WHERE a.question_id = %s
ORDER BY accepted_by DESC, a.vote_number DESC, a.submission_time DESC, c.submission_time DESC;"""
answer = db.perform_query(sql2, data)
result = {'question': question, 'answer': answer}
return result
def list_users():
'''
List all the registered users with all their attributes except their id.
'''
users_data = db.perform_query(
"""SELECT id, user_name, reputation, reg_time FROM users;""")
return users_data
def select_edit_data(id, mode):
"""
Returns a dict by ID from DB
@param id_to_get int The id of the needed table
@param mode string Which table
@return dict Needed table in a dict
"""
if mode == 'question':
sql = """SELECT id,
title,
message
FROM question
WHERE id = %s;"""
elif mode == 'answer':
sql = """SELECT id,
message,
question_id
FROM answer
WHERE id = %s;"""
elif mode == 'comment':
sql = """SELECT c.id,
c.message,
CASE WHEN c.question_id IS NOT NULL THEN c.question_id ELSE a.question_id END AS question_id,
c.answer_id
FROM comment c
LEFT OUTER JOIN answer a ON a.id = c.answer_id
WHERE c.id = %s;"""
else:
raise ValueError
data = (id, )
result = db.perform_query(sql, data)
return result
def get_questions(sort_order, five=False):
"""
Returns all the questions.
@param sort_order string The request path
@param five bool True if you want only 5 questions, otherwise False
@return list List of dictionaries for each question.
"""
sql = """SELECT id,
title,
message,
view_number,
vote_number,
to_char(submission_time, 'YYYY-MM-DD HH24:MI') AS submission_time
FROM question"""
if sort_order:
fields = {
'time': 'submission_time',
'view': 'view_number',
'vote': 'vote_number',
'title': 'title',
'message': 'message'
}
order_by = ', '.join(fields[item[0]] + ' ' + item[1]
for item in sort_order)
sql = sql + """ ORDER BY """ + order_by
else:
sql = sql + """ ORDER BY submission_time DESC"""
if five:
sql = sql + """ LIMIT 5"""
questions = db.perform_query(sql)
return questions
def user_data(user_id):
'''
List all the data added by a user (comments, answers, questions) by their id.
@param user_id int ID of the user
@return dict keys: question, answer, comments. values: messages
'''
sql = """SELECT id,
title,
submission_time
FROM question
WHERE user_id = %s
ORDER BY id DESC;"""
sql2 = """SELECT q.id,
q.title,
a.message,
a.submission_time,
a.answered_by,
CASE WHEN a.accepted_by IS NULL THEN 0 ELSE 1 END AS accepted
FROM answer a
INNER JOIN question q ON a.question_id = q.id
WHERE a.answered_by = %s
ORDER BY a.id DESC;"""
sql3 = """SELECT q.id,
q.title,
a.message as answer_message,
c.message as comment_message,
c.submission_time
FROM comment c
LEFT OUTER JOIN answer a ON a.id = c.answer_id
LEFT OUTER JOIN question q ON q.id = c.question_id
WHERE c.user_id = %s
ORDER BY c.id DESC;"""
data = (user_id, )
user_question = db.perform_query(sql, data)
user_answer = db.perform_query(sql2, data)
user_comment = db.perform_query(sql3, data)
result = {
"questions": user_question,
'answers': user_answer,
'comments': user_comment
}
return result
def get_user_by_name(user_name):
'''
Get the user by it's name.
@param user_name sting Name of the user
@return id int The chosen user's id
'''
user_id = db.perform_query(
"""SELECT id FROM users WHERE user_name = %s LIMIT 1;""",
(user_name, ))
return user_id[0]['id']
def user_by_id(id):
'''
Get the user by it's ID.
@param id int ID of the user
@return string The username with the chosen id.
'''
sql = """SELECT user_name FROM users WHERE id = %s"""
data = (id, )
user = db.perform_query(sql, data)
return user
def new_user(username):
"""
Insert the new user into the users table
@param username string The chosen name by the user
@return int or None If the insert is done, returns with the new user's id.
If the username is already taken, returns None
"""
sql = """INSERT INTO users (user_name) VALUES (%s) RETURNING id;"""
data = (username, )
return db.perform_query(sql, data)
def get_user_by_username(username):
'''
Get all details of a user by its user name
@param username int The name of the user to be found
@return list All details of the user or empty list if user does not exist in db
'''
result = None
if username:
sql = '''SELECT * FROM mjkwnf_users WHERE username = %s'''
data = (username, )
result = db.perform_query(sql, data)
return result
def get_user_by_id(user_id):
'''
Get all details of a user by its ID
@param user_id int The ID of the user to be found
@return list All details of the user or empty list if user does not exist in db
'''
result = None
if user_id:
sql = '''SELECT * FROM mjkwnf_users WHERE id = %s'''
data = (user_id, )
result = db.perform_query(sql, data)
return result
def get_planet_by_name(planet_name):
'''
Retrieve planet details by its name
@param planet_name string Name of the planet the be found
@return list Contains the details of the planet or empty list
'''
sql = '''SELECT id
FROM mjkwnf_planets
WHERE planet_name = %s'''
data = (planet_name, )
result = db.perform_query(sql, data)
return result
def get_question_by_answer_id(answer_id):
"""
Get corresponding answer for given question id \n
@param answer_id int Which answer you need the corresponding question id to \n
@return int Get the corresponding question id
"""
question_id = False
if answer_id:
sql = """SELECT question_id FROM answer WHERE id = %s;"""
data = (answer_id, )
question_id = db.perform_query(sql, data)[0]['question_id']
return question_id
def update_view_number(question_id):
"""
Set the view numbers.
@param question_id int Question's id
@return bool True, if the update is done else False
"""
status = False
if question_id:
sql = """UPDATE question SET view_number = view_number + 1 WHERE id = %s RETURNING id;"""
data = (question_id, )
status = True if db.perform_query(sql, data) else False
return status
def accepted_answer(answer_id, user_id):
"""
Updates in the answer table the accepted_by field with the user's id.
@answer_id int answer's id
@user_id int user's id
@return int the updated answer's id
"""
sql = """UPDATE answer SET accepted_by = %s WHERE id = %s RETURNING id;"""
data = (user_id, answer_id)
result = db.perform_query(sql, data)
reputation = db.perform_proc('update_reputation',
[user_id, 'accepted_answer'])
return result
def check_user_vote(user_id, planet_id):
'''
Check for existing user vote
@param user_id int User ID
@param planet_id int Planet ID
@return list All details of the vote or empty list
'''
sql = '''SELECT *
FROM mjkwnf_planet_votes
WHERE user_id = %s AND planet_id = %s'''
data = (user_id, planet_id)
result = db.perform_query(sql, data)
return result
def get_votes():
'''
Prepares data for vote statistics, contains TOP 10 voted planets
@return list List of planets with number of votes
'''
sql = '''SELECT p.planet_name, COUNT(p.planet_name) vote_count
FROM mjkwnf_planet_votes pv
INNER JOIN mjkwnf_planets p ON pv.planet_id = p.id
GROUP BY p.planet_name
ORDER BY vote_count DESC, planet_name
LIMIT 10;'''
result = db.perform_query(sql)
return result
def insert_vote(user_id, planet_id):
'''
Insert new user vote
@param user_id int User ID
@param planet_id int Planet ID
@return list All details of inserted vote
'''
sql = '''INSERT INTO mjkwnf_planet_votes (user_id, planet_id)
VALUES (%s, %s)
RETURNING *'''
data = (user_id, planet_id)
result = db.perform_query(sql, data)
return result
def delete_vote(user_id, planet_id):
'''
Delete a user vote
@param user_id int User ID
@param planet_id int Planet ID
@return list All details of deleted vote
'''
sql = '''DELETE FROM mjkwnf_planet_votes
WHERE user_id = %s AND planet_id = %s
RETURNING *'''
data = (user_id, planet_id)
result = db.perform_query(sql, data)
return result
def register_user(username, password):
'''
Register new, validated user, store credentials in db
@param username string User name provided by new user
@param password string Password provided by new user in plain text
@return bool True if successful, otherwise False
'''
status = False
if username and password:
sql = '''INSERT INTO mjkwnf_users (username, password) VALUES (%s, %s) RETURNING id'''
data = (username, hash_password(password))
status = True if db.perform_query(sql, data) else False
return status
def get_votes_by_user_id(user_id):
'''
Get votes of a specific user
@param user_id int User ID
@return list List of planet names the user voted on
'''
sql = '''SELECT string_agg(DISTINCT p.planet_name, ',') as voted_planets
FROM mjkwnf_planet_votes pv
INNER JOIN mjkwnf_planets p ON p.id = pv.planet_id
WHERE user_id = %s
GROUP BY user_id'''
data = (user_id, )
result = db.perform_query(sql, data)[0]['voted_planets'].split(',')
return result
def process_delete(id, table):
"""
Delete a question, answer or comment
@param id int The id of the record to be deleted
@param table string The table from which the id will be deleted
@return bool Status: True if successful, otherwise False
"""
data = (id, )
if table == "question":
sql = """DELETE FROM question WHERE id = %s RETURNING id;"""
elif table == 'answer':
sql = """DELETE FROM answer WHERE id = %s RETURNING id;"""
elif table == 'comment':
sql = """DELETE FROM comment WHERE id = %s RETURNING id;"""
else:
raise ValueError
status = True if db.perform_query(sql, data) else False
return status
def user_search(search_phrase):
"""
Returns search results for user query \n
@param search_phrase string Phrase providid by the user \n
@return list of dicts Records that match the search phrase
"""
data = {'phrase': search_phrase}
sql = """SELECT q.id AS question_id,
REPLACE(q.title, %(phrase)s, CONCAT('<span class="special-format">',
%(phrase)s, '</span>')) AS title,
REPLACE(q.message, %(phrase)s, CONCAT('<span class="special-format">', %(phrase)s, '</span>'))
AS question_body,
q.view_number,
q.vote_number AS question_vote,
to_char(q.submission_time, 'YYYY-MM-DD HH24:MI') AS submission_time,
NULL AS answer_id,
NULL AS answer_body,
NULL AS answer_date,
NULL AS answer_vote
FROM question q
WHERE q.title ILIKE CONCAT('%%', %(phrase)s, '%%') OR q.message ILIKE CONCAT('%%', %(phrase)s, '%%')
UNION ALL
SELECT q.id AS question_id,
REPLACE(q.title, %(phrase)s, CONCAT('<span class="special-format">',
%(phrase)s, '</span>')) AS title,
q.message AS question_body,
q.view_number,
q.vote_number AS question_vote,
to_char(q.submission_time, 'YYYY-MM-DD HH24:MI') AS submission_time,
a.id AS answer_id,
REPLACE(a.message, %(phrase)s, CONCAT('<span class="special-format">',
%(phrase)s, '</span>')) AS answer_body,
to_char(a.submission_time, 'YYYY-MM-DD HH24:MI') AS answer_date,
a.vote_number AS answer_vote
FROM question q
LEFT OUTER JOIN answer a ON q.id = a.question_id
WHERE a.message ILIKE CONCAT('%%', %(phrase)s, '%%')
ORDER BY question_id DESC, answer_id DESC;"""
records = db.perform_query(sql, data)
return records
def process_votes(id, user_id, questions=True, direction='up'):
"""
Handles the voting process.
@param id int id Id of the chosen q/a for upvote
@param user_id int user's id Who owns the q/a
@param questions int true/false Question or answer
@param direction string up/somethig else Is that an upvote or not
"""
status = False
if id:
if direction not in ('up', 'down'):
raise ValueError
if questions is True:
if direction == 'up':
sql = """UPDATE question SET vote_number = vote_number + 1 WHERE id = %s RETURNING id;"""
reputation = db.perform_proc('update_reputation',
[user_id, 'upvoted_question'])
else:
sql = """UPDATE question SET vote_number = vote_number - 1 WHERE id = %s RETURNING id;"""
reputation = db.perform_proc('update_reputation',
[user_id, 'downvoted_question'])
elif questions is False:
if direction == 'up':
sql = """UPDATE answer SET vote_number = vote_number + 1 WHERE id = %s RETURNING id;"""
reputation = db.perform_proc('update_reputation',
[user_id, 'upvoted_answer'])
else:
sql = """UPDATE answer SET vote_number = vote_number - 1 WHERE id = %s RETURNING id;"""
reputation = db.perform_proc('update_reputation',
[user_id, 'downvoted_answer'])
else:
raise ValueError
data = (id, )
status = True if db.perform_query(sql, data) else False
return status
def process_form(form_data):
"""
Handles Insert/Update Q/A/C \n
@param form_data User input
@return dict keys: status, question_id values:true/false, question's id
"""
question_id = False
try:
modID = int(form_data['modID'])
typeID = int(form_data['typeID'])
except ValueError:
status = False
question_id = None
else:
if modID == -1:
# insert
if typeID == 1:
# question
sql = """INSERT INTO question (title, message, user_id)
VALUES (%s, %s, %s) RETURNING id AS question_id;"""
data = (form_data['title'], form_data['message'],
get_user_by_name(form_data['user']))
elif typeID == 2:
# answer
sql = """INSERT INTO answer (question_id, message, answered_by)
VALUES (%s, %s, %s) RETURNING question_id;"""
data = (form_data['question_id'], form_data['message'],
get_user_by_name(form_data['user']))
elif typeID == 3 and form_data.get('answer_id', -10) == -10:
# comment to question
sql = """INSERT INTO comment (question_id, message, user_id)
VALUES (%s, %s, %s) RETURNING question_id;"""
data = (form_data['question_id'], form_data['message'],
get_user_by_name(form_data['user']))
elif typeID == 3 and form_data['answer_id']:
# comment to answer
sql = """INSERT INTO comment (answer_id, message, user_id) VALUES (%s, %s, %s) RETURNING id;"""
data = (form_data['answer_id'], form_data['message'],
get_user_by_name(form_data['user']))
question_id = get_question_by_answer_id(form_data['answer_id'])
else:
raise ValueError
else:
# update
if typeID == 1:
# question
sql = """UPDATE question SET title = %s, message = %s WHERE id = %s RETURNING id AS question_id"""
data = (form_data['title'], form_data['message'], modID)
elif typeID == 2:
# answer
sql = """UPDATE answer SET message = %s WHERE id = %s RETURNING question_id"""
data = (form_data['message'], modID)
elif typeID == 3:
# comment
sql = """UPDATE comment SET message = %s WHERE id = %s RETURNING id;"""
data = (form_data['message'], modID)
question_id = form_data['question_id'] if form_data.get(
'question_id', '') else get_question_by_answer_id(
form_data['answer_id'])
else:
raise ValueError
query_result = db.perform_query(sql, data)
status = True if query_result else False
question_id = question_id if question_id else query_result[0][
'question_id']
result = {'status': status, 'question_id': question_id}
return result
