def vote(type_, direction, id, answer_id=0):
if type_ == "question":
query = "SELECT vote_number FROM {0} WHERE id={1}".format(type_, id)
elif type_ == "answer":
query = "SELECT vote_number FROM {0} WHERE id={2} AND question_id = {1}".format(
type_, id, answer_id)
else:
pass
updated_number = 0
vote_num = data_manager.run_query(query)
if direction == "vote-up" and vote_num:
updated_number = vote_num[0][0] + 1
elif direction == "vote-down" and vote_num:
updated_number = vote_num[0][0] - 1
else:
pass
if type_ == "question":
update_query = """UPDATE {0} SET vote_number = {1} WHERE id = {2}""".format(
type_, updated_number, id)
elif type_ == "answer":
update_query = """UPDATE {0} SET vote_number = {1} WHERE id = {3}
AND question_id = {2}""".format(type_, updated_number,
id, answer_id)
else:
pass
data_manager.run_query(update_query)
def add_new_question():
"""Add new story to list, then redirect to /list page"""
title = request.form["title"]
message = request.form["message"]
username = request.form["username"]
query = """SELECT id From users WHERE name='{}';""".format(username, )
users_id = data_manager.run_query(query)[0][0]
columns = ('title', 'message', 'users_id')
values = (title, message, users_id)
data_manager.safe_insert('question', columns, values)
query = "SELECT id FROM question WHERE title='{0}';".format(title)
question_id = data_manager.run_query(query)
selected_tag_name = []
tag_names = common.tag_names()
lit_of_tag_names = [i[0] for i in tag_names]
for request_string in request.form:
if request_string in lit_of_tag_names:
selected_tag_name.append(request_string)
ids = []
for name in selected_tag_name:
ids.append(common.id_of_tag_where_name_is(name)[0][0])
for tag_id in ids:
common.update_tag(tag_id, question_id[0][0])
return redirect("/question/" + str(question_id[0][0]))
def edit_question(question_id):
"""Update question in the database, then go back to question's page"""
title = request.form["title"]
message = request.form["message"]
query = "UPDATE question SET title = '{0}', message = '{1}' WHERE id = '{2}';".format(
title, message, question_id)
data_manager.run_query(query)
selected_tag_name = []
tag_names = common.tag_names()
lit_of_tag_names = [i[0] for i in tag_names]
for request_string in request.form:
if request_string in lit_of_tag_names:
selected_tag_name.append(request_string)
ids = []
for name in selected_tag_name:
ids.append(common.id_of_tag_where_name_is(name)[0][0])
for tag_id in ids:
common.delete_edit_tag(question_id)
for tag_id in ids:
common.update_tag(tag_id, question_id)
return redirect("/question/" + question_id)
def delete_comment(comment_id):
"""
routed to url: /comments/<comment_id>/delete
Deletes seleted comment from database
"""
question_id = get_question_for_comment(comment_id)
sql_query = "DELETE FROM comment WHERE id={}".format(int(comment_id))
data_manager.run_query(sql_query)
return redirect('/question/' + question_id)
def update(table, id, column, value):
"""
Build an update sql query in the format:
UPDATE {table} SET {column}='{value}' WHERE id='{id}';
and call it.
"""
query = "UPDATE {0} SET {1}='{2}' WHERE id='{3}';".format(table, column, value, id)
data_manager.run_query(query)
return
def delete(table, id):
"""
Build a delete sql query in the format:
DELETE FROM {table} WHERE id={id};
and call it.
"""
query = "DELETE FROM {0} WHERE id={1};".format(table, id)
data_manager.run_query(query)
return
def get_question_for_comment(comment_id):
"""
Utility function, gets the appropriate question_id for a comment_id
"""
sql_query = "SELECT question_id, answer_id FROM comment WHERE id={}".format(
int(comment_id))
ids = data_manager.run_query(sql_query)[0]
question_id = ids[0]
answer_id = ids[1]
if question_id is None:
question_id = data_manager.run_query(
"SELECT question_id FROM answer WHERE id={}".format(
int(answer_id)))[0][0]
return str(question_id)
def print_table():
"""
Based on sorting priorities stored in the query string,
lists Questions with list.html
"""
pairs = {
'ID': 'id',
'Question': 'title',
'Date': 'submission_time',
'Number of Views': 'view_number',
'Votes': 'vote_number'
}
sortingcols = request.args
order = ','.join(
[pairs[col] + ' ' + sortingcols[col] for col in sortingcols][::-1])
sql_query = """SELECT q.id, q.title, q.submission_time, q.view_number, q.vote_number, q.image, u.name
FROM question q JOIN users u ON (q.users_id = u.id)"""
if order:
sql_query += '\nORDER BY q.' + order + ';'
questions = data_manager.run_query(sql_query)
headers = [
'question_id', 'title', 'submission_time', 'view_number',
'vote_number', 'image', 'user_name'
]
questions = data_manager.build_dict(questions, headers)
url = '&'.join([key + '=' + sortingcols[key] for key in sortingcols])
return render_template('list.html', questions=questions, url=url)
def get_users():
"""
Return the list of users from database
"""
query = """SELECT name FROM users;"""
users = data_manager.run_query(query)
simple_list = [x[0] for x in users]
return simple_list
def get_question(id):
"""
Return a single question (dict) by its ID
"""
question = data_manager.run_query("SELECT * FROM question WHERE id={};".format(id))
question = data_manager.build_dict(
question, ["question_id", "submission_time", "view_number", "vote_number", "title", "message", "image"])
return question[0]
def read_tags(question_id):
list_of_keys_of_tag = ["tag_id", "name", "question_id", "color"]
query_tag = """SELECT tag.id, tag.name, question_tag.question_id, tag.color FROM tag JOIN question_tag
ON tag.id = question_tag.tag_id WHERE question_tag.question_id={0} ORDER BY tag_id""".format(question_id)
data = data_manager.run_query(query_tag)
tags = data_manager.build_dict(data, list_of_keys_of_tag)
return tags
def update_comment_in_db(comment_id, comment):
"""
routed to url: /update_comment/<comment_id>
Saves modifications of selected comment into the database
"""
counter = data_manager.run_query(
"SELECT edited_count FROM comment WHERE id={}".format(
int(comment_id)))[0][0]
curr_time = str(datetime.datetime.now())[:16]
counter = 1 if not counter else counter + 1
sql_query = ("""UPDATE comment
SET submission_time='{}', message='{}', edited_count={}
WHERE id={}""".format(curr_time, comment, counter,
int(comment_id)))
data_manager.run_query(sql_query)
question_id = get_question_for_comment(comment_id)
return redirect('/question/' + question_id)
def get_users_with_id():
"""
Return the list of users with their id from database
in the format of dictinoary (keywords: id, name)
"""
query = """SELECT id, name FROM users;"""
user_list = data_manager.run_query(query)
user_list = data_manager.build_dict(user_list, ["id", "name"])
return user_list
def group_by_question_with_tags():
query = """SELECT tag.name, COUNT(question_tag.tag_id), tag.color
FROM tag
LEFT JOIN question_tag
ON tag.id=question_tag.tag_id
GROUP BY tag.id
ORDER BY COUNT(question_tag.tag_id) DESC;"""
tags_number_of_question = data_manager.run_query(query)
list_of_tag_number_key = ["name", "question_number", "color"]
dict_of_tags = data_manager.build_dict(tags_number_of_question, list_of_tag_number_key)
return dict_of_tags
def list_users():
sql_query = """
SELECT u.name, u.submission_time,
(SELECT COUNT(question.users_id) FROM question WHERE question.users_id=u.id),
(SELECT COUNT(answer.users_id) FROM answer WHERE answer.users_id=u.id)
FROM users u
ORDER BY u.submission_time;
"""
header = ('Username', 'Join Date', 'Number of Questions', 'Number of Answers')
table = data_manager.run_query(sql_query)
return render_template('print_users.html', title='List of Users', table=table, header=header)
def add_comment_to_db(q_or_a, id, comment, user):
"""
Routed to url: /add_comment_to_db/<q_or_a>/<id>
Saves to new entry in the database
"""
sql_query = ("""SELECT id FROM users WHERE name='{}';""".format(user))
user_id = data_manager.run_query(sql_query)[0][0]
if q_or_a == 'question':
sql_query = ("""INSERT INTO comment (question_id, message, users_id)
VALUES ({}, '{}', '{}');""".format(int(id), comment, user_id))
question_id = id
else:
sql_query = ("""INSERT INTO comment (answer_id, message, users_id)
VALUES ({}, '{}', '{}' )""".format(int(id), comment, user_id))
question_id = str(
data_manager.run_query(
"""SELECT question_id FROM answer WHERE id={}""".format(id))[0]
[0])
data_manager.run_query(sql_query)
return redirect('/question/' + question_id)
def add_new_answer_comment(answer_id):
"""
Routed to url: /answer/answer_id/new-comment
Renders comment.html with arguments for adding new comment for an answer
"""
question_id = data_manager.run_query(
"""SELECT question_id FROM answer WHERE id={}""".format(
answer_id))[0][0]
sql_query = """SELECT message FROM answer WHERE id={}""".format(answer_id)
answer_content = data_manager.run_query(sql_query)[0][0]
users = common.get_users()
return render_template('comment.html',
basis='answer',
mode='Add_new',
button='Create',
comment='',
content=answer_content,
question_id=str(question_id),
answer_id=str(answer_id),
title='Add a',
users=users)
def search_questions(term):
"""search for answers and questions with the given search term and display it
# search for answers with search term in their message"""
searchterm = term.lower()
# select questions
question_query = """SELECT DISTINCT id, title, message, submission_time
FROM question
WHERE id = ANY (SELECT question_id
FROM answer
WHERE LOWER(title) LIKE '%{0}%' OR LOWER(message) LIKE '%{0}%')
OR LOWER(message) LIKE '%{0}%' OR LOWER(title) LIKE '%{0}%';""".format(searchterm)
table = data_manager.run_query(question_query)
if table:
keys = ("question_id", "title", "message", "submission_time")
questions = data_manager.build_dict(table, keys)
fancy_questions = make_fancy(term, questions, "title")
fancy_questions = make_fancy(term, questions, "message")
else:
return render_template('search_results.html', questions=[], answers=[])
# select related answers
answer_query = """SELECT DISTINCT id, message, question_id
FROM answer
WHERE question_id = ANY (SELECT id
FROM question
WHERE LOWER(title) LIKE '%{0}%' OR LOWER(message) LIKE '%{0}%')
OR LOWER(message) LIKE '%{0}%';""".format(searchterm)
table = data_manager.run_query(answer_query)
keys = ("answer_id", "message", "question_id")
answers = data_manager.build_dict(table, keys)
fancy_answers = make_fancy(term, answers, "message")
return render_template('search_results.html', questions=fancy_questions, answers=fancy_answers)
def contacts():
sql_query = """
SELECT s.name, m.first_name, m.last_name
FROM schools s LEFT OUTER JOIN mentors m
ON (s.contact_person = m.id)
ORDER BY s.name;"""
table = dm.run_query(sql_query)
title = 'Contacts page'
header = ('School', 'Contact First Name', 'Last Name')
return render_template('print_table.html',
title=title,
header=header,
table=table)
def all_school():
sql_query = """
SELECT m.first_name, m.last_name, s.name, s.country
FROM mentors m RIGHT OUTER JOIN schools s
ON (m.city = s.city)
ORDER BY m.id;"""
table = dm.run_query(sql_query)
title = "All the schools page"
header = ('First Name', 'Last Name', 'School Name', 'Country')
return render_template('print_table.html',
title=title,
header=header,
table=table)
def edit_comment(comment_id):
"""
Routed to url: /comments/<comment_id>/edit
Renders comment.html with arguments for editing a comment
"""
sql_query = """SELECT name FROM users WHERE id=(SELECT users_id FROM comment WHERE id={})""".format(
comment_id)
name = data_manager.run_query(sql_query)[0][0]
sql_query = """SELECT message FROM comment WHERE id={}""".format(
comment_id)
comment = data_manager.run_query(sql_query)[0][0]
question_id = get_question_for_comment(comment_id)
return render_template('comment.html',
basis='comment',
mode='Edit',
button='Update',
comment=comment,
content='',
question_id=question_id,
answer_id='',
comment_id=comment_id,
title='Edit',
name=name)
def applicants():
sql_query = """
SELECT a.first_name, a.application_code, a_m.creation_date
FROM applicants a LEFT OUTER JOIN applicants_mentors a_m
ON (a.id = a_m.applicant_id)
WHERE a_m.creation_date > '2016-01-01'
ORDER BY a_m.creation_date DESC;"""
table = dm.run_query(sql_query)
title = 'Applicants page'
header = ('Applicant First Name', 'Application Code', 'Creation Date')
return render_template('print_table.html',
title=title,
header=header,
table=table)
def mentors_by_country():
sql_query = """
SELECT s.country, COUNT(*) AS mentor_count
FROM mentors m JOIN schools s
ON (m.city = s.city)
GROUP BY s.country
ORDER BY s.country;"""
table = dm.run_query(sql_query)
title = 'Mentors by country page'
header = ('Country', 'Mentor Count')
return render_template('print_table.html',
title=title,
header=header,
table=table)
def get_answers_for_question(question_id, sort_by, direction):
query = """
SELECT answer.*, users.name
FROM answer
LEFT JOIN users ON answer.users_id = users.id
WHERE question_id={0}
ORDER BY {1} {2};
""".format(question_id, sort_by, direction)
rows = data_manager.run_query(query)
list_of_names = [
"answer_id", "submission_time", "vote_number", "question_id",
"message", "image", "accepted", "user_id", "user_name"
]
answers = data_manager.build_dict(rows, list_of_names)
return answers
def edit_question_route(question_id):
"""Find question details from id and redirect with the data to /new_question page"""
title = "Modify question"
action = "/modify/" + question_id
query = """SELECT q.title, q.message, u.name FROM question q JOIN users u
ON (q.users_id = u.id)
WHERE q.id = '{}';""".format(question_id)
table = data_manager.run_query(query)
titles = "title", "message", "username"
data = data_manager.build_dict(table, titles)[0]
tags = common.show_tags_type()
tag_query = """SELECT tag.name
FROM tag
LEFT JOIN question_tag
ON tag.id=question_tag.tag_id
WHERE question_id = {0};""".format(question_id)
selected_tag = [i[0] for i in data_manager.run_query(tag_query)]
return render_template("new-question.html",
question_id=question_id,
title=title,
data=data,
action=action,
tags=tags,
selected_tag=selected_tag)
def get_comments(comment_type, question_id):
if comment_type == "question":
query = """SELECT *
FROM comment
WHERE question_id = {};
""".format(question_id)
elif comment_type == "answer":
query = """SELECT comment.id, comment.question_id, comment.answer_id, comment.message, comment.submission_time, comment.edited_count
FROM comment
LEFT JOIN answer ON answer_id = answer.id
WHERE answer.question_id = {};
""".format(question_id)
rows = data_manager.run_query(query)
columns = ["id", "question_id", "answer_id", "message", "submission_time", "edited_count"]
comments = data_manager.build_dict(rows, columns)
return comments
def applicants_and_mentors():
sql_query = """
SELECT a.first_name, a.application_code, COALESCE(m.first_name, 'No mentor yet'), COALESCE(m.last_name, 'No mentor yet')
FROM ((applicants a LEFT OUTER JOIN applicants_mentors a_m
ON (a.id = a_m.applicant_id))
LEFT OUTER JOIN mentors m
ON (a_m.mentor_id = m.id))
ORDER BY a_m.applicant_id;"""
table = dm.run_query(sql_query)
title = 'Applicants and mentors page'
header = ('Applicant First Name', 'Application Code', 'Mentor First Name',
'Last Name')
return render_template('print_table.html',
title=title,
header=header,
table=table)
def get_answer(id):
"""
Return a single answer (dict) by its ID with authors name
"""
print(id)
answer = data_manager.run_query(
"""
SELECT answer.*, users.name
FROM answer
LEFT JOIN users ON answer.users_id = users.id
WHERE answer.id={};
""".format(id))
answer = data_manager.build_dict(answer, [
"answer_id",
"submission_time",
"vote_number",
"question_id",
"message",
"image",
"accepted",
"user_id",
"user_name"
])
return answer[0]
def displays_a_single_question(question_id):
list_of_key_of_question = [
"id", "submission_time", "view_number", "vote_number", "title",
"message"
]
title_of_question = [
"ID", "Submisson time", "View number", "Vote number", "Title",
"Message"
]
list_of_key_and_title_of_question = list(
zip(list_of_key_of_question, title_of_question))
list_of_key_of_answer = [
"answer_id", "submission_time", "vote_number", "question_id",
"message", "user_name"
]
title_of_answer = [
"ID", "Submisson time", "Vote number", "Question id", "Message",
"Author"
]
list_of_key_and_title_of_answers = list(
zip(list_of_key_of_answer, title_of_answer))
query = "SELECT * FROM question"
rows = data_manager.run_query(query)
list_of_names = [
"id", "submission_time", "view_number", "vote_number", "title",
"message"
]
all_question = data_manager.build_dict(rows, list_of_names)
for question_ in all_question:
if question_id == question_["id"]:
question = question_
break
query = "SELECT * FROM answer"
rows = data_manager.run_query(query)
list_of_names = [
"id", "submission_time", "vote_number", "question_id", "message"
]
all_answers = data_manager.build_dict(rows, list_of_names)
answers = []
for answer in all_answers:
if question_id == answer["question_id"]:
answers.append(answer)
tags = common.read_tags(question_id)
question_with_answers = {
"tags": tags,
"question_id": question_id,
"question": question,
"answers": answers,
"list_of_key_of_question": list_of_key_of_question,
"list_of_key_of_answer": list_of_key_of_answer,
"title_of_question": title_of_question,
"title_of_answer": title_of_answer,
"list_of_key_and_title_of_question": list_of_key_and_title_of_question,
"list_of_key_and_title_of_answers": list_of_key_and_title_of_answers
}
return question_with_answers
def show_tags_type():
list_of_keys_of_tag = ["id", "name", "color"]
query_tag = "SELECT id,name,color FROM tag ORDER BY id"
data = data_manager.run_query(query_tag)
tags_type = data_manager.build_dict(data, list_of_keys_of_tag)
return tags_type
