def edit_question_query(_id):
"""Update question table by question_id(input parameter: _id=WHERE condition)"""
question_title = request.form['title']
question_message = request.form['message']
sql_to_edit_question = ("UPDATE question SET title= '{}', message='{}' WHERE id= {};".format(
question_title, question_message, _id))
db_connection.db_update(str(sql_to_edit_question))
def update_view_count_query(question_id):
'''Increases the question's view counter by 1'''
view_count = select_query_where("view_number", "question", "id", question_id)
new_view_count = int(view_count[0][0]) + 1
print(view_count)
view_update_query = """UPDATE question SET view_number={} WHERE id={};""".format(new_view_count, question_id)
db_connection.db_update(view_update_query)
def add_new_answer(question_id):
"""Insert new answer into the answer table by question_id.Input parameter:question_id"""
submission_time = datetime.datetime.now()
vote_number = '0'
answer_message = request.form["newanswer"]
user_id = request.form["user"]
sql_to_insert_answer = ("INSERT INTO answer (submission_time,vote_number,question_id,message,user_id) VALUES ('{}','{}','{}','{}','{}');".format(
submission_time, vote_number, question_id, answer_message, user_id))
db_connection.db_update(str(sql_to_insert_answer))
def add_new_comment(_id, id_type):
"""Insert a new comment into the comment table.
Input parameters: _id(query value)= question_id or answer_id, id_type(sql coloumn)=question_id or answer_id"""
submission_time = datetime.datetime.now()
edit_number = '0'
comment_message = request.form["newcomment"]
user_id = request.form["user"]
sql_to_insert_comment = ("INSERT INTO comment ({},message,submission_time,edited_count,user_id) VALUES ('{}','{}','{}','{}','{}');".format(
id_type, _id, comment_message, submission_time, edit_number, user_id))
db_connection.db_update(str(sql_to_insert_comment))
def vote_update_sql_query(table, _id, direction):
"""The function increase or decrease the vote_number.
Input parameters:table=given table(answer or question), _id = answer_id or question_id,
direction= 'up' or 'down'"""
select_query = ("SELECT vote_number FROM {} WHERE id={};".format(table, _id))
vote_number = db_connection.db_request(select_query)
votes = int(vote_number[0][0])
if direction == 'up':
votes += 1
elif direction == 'down':
votes -= 1
sql_to_edit_vote = ("UPDATE {} SET vote_number= {} WHERE id= {};".format(
table, votes, _id))
db_connection.db_update(str(sql_to_edit_vote))
def add_new_question():
"""INSERT new question into question table"""
submission_time = datetime.datetime.now()
view_number = '0'
vote_number = '0'
question_title = request.form["title"]
question_message = request.form["message"]
question_user = request.form["user"]
print(question_user)
sql_to_insert = ("INSERT INTO question (submission_time,view_number,vote_number,title,message,user_id) VALUES ('{}','{}','{}','{}','{}','{}');".format(
submission_time, view_number, vote_number, question_title, question_message, question_user))
return db_connection.db_update(str(sql_to_insert))
def delete_query(table, criteria, condition):
"""DELETE row or rows from given table.
Input parameters: table=given table, criteria= WHERE criteria, condition= WHERE condition"""
sql_to_delete = ("DELETE FROM {} WHERE {}={};".format(table, criteria, condition))
return db_connection.db_update(str(sql_to_delete))
def insert_tag_query(tag_to_add):
tag_insert_query = """INSERT INTO tag (name) VALUES ('{}');""".format(tag_to_add)
db_connection.db_update(tag_insert_query)
def insert_register_user(user_name, date):
register_user_query = """INSERT INTO users
(username, date)
VALUES ('{}','{}')""".format(user_name, date)
db_connection.db_update(str(register_user_query))
def question_tag_insert_query(question_id, tag_item):
question_tag_insert = ("""INSERT INTO question_tag (question_id,tag_id)
VALUES ('{}','{}');""".format(question_id, int(tag_item)))
return db_connection.db_update(question_tag_insert)
def delete_tag_query(question_id, tag_id):
sql_to_delete = ("DELETE FROM question_tag WHERE tag_id={} AND question_id={};".format(tag_id, question_id))
db_connection.db_update(str(sql_to_delete))
def delete_answer_comment_query(question_id):
answer_comment_delete = ("""DELETE FROM comment
WHERE answer_id
IN(SELECT answer_id FROM answer WHERE question_id={});""".format(question_id))
return db_connection.db_update(str(answer_comment_delete))