def downvote_questions(self, questionid):
conn = get_db_connection()
conn.execute(
'UPDATE question SET question_vote = question_vote - 1 '
'WHERE question_id = ?', (questionid, ))
conn.commit()
conn.close()
def downvote_answers(self, answerid):
conn = get_db_connection()
conn.execute(
'UPDATE answer SET answer_vote = answer_vote - 1 '
'WHERE answer_id = ?', (answerid, ))
conn.commit()
conn.close()
def upd_tags(self, tagid):
conn = get_db_connection()
conn.execute('UPDATE tag SET tag_name = ?'
'WHERE tag_id = ?',
(self.tag_name, tagid))
conn.commit()
conn.close()
def addtag_questions(self, questionid):
conn = get_db_connection()
conn.execute(
'UPDATE question SET question_tag = ?'
'WHERE question_id = ?', (self.question_tagid, questionid))
conn.commit()
conn.close()
def get_tagall():
conn = get_db_connection()
tags = conn.execute(' SELECT tag_id, tag_name FROM tag ').fetchall()
conn.close()
if tags is None:
abort(404)
return tags
def upd_questions(self, questionid):
conn = get_db_connection()
conn.execute(
'UPDATE question SET question_content = ?'
'WHERE question_id = ?', (self.question_content, questionid))
conn.commit()
conn.close()
def addanswer_questions(self, questionid):
conn = get_db_connection()
conn.execute(
' INSERT INTO answer (answer_question, answer_content, answer_vote) \
VALUES (?, ?, ?)',
(self.answer_question, self.answer_content, 0))
conn.commit()
conn.close()
def ins_questions(self):
conn = get_db_connection()
conn.execute(
' INSERT INTO question (question_content, question_vote, question_tag, is_open) \
VALUES (?, ?, ?, ?)',
(self.question_content, 0, self.question_tagid, 1))
conn.commit()
conn.close()
def udp_answer(self, answerid):
conn = get_db_connection()
conn.execute(
'UPDATE answer SET answer_question = ?, answer_content = ?, answer_vote = ?'
'WHERE answer_id = ?',
(self.answer_question, self.answer_content, 0, answerid))
conn.commit()
conn.close()
def get_tag(tag_id):
conn = get_db_connection()
tag = conn.execute('SELECT tag_id, tag_name FROM tag \
WHERE tag_id = ?',(tag_id,)
).fetchone()
conn.close()
if tag is None:
abort(404)
return tag
def get_answerquestion(question_id):
conn = get_db_connection()
answer = conn.execute(
' SELECT a.answer_id, a.answer_content, a.answer_vote, q.question_content, q.question_vote \
FROM answer AS a \
LEFT JOIN question AS q on q.question_id = a.answer_question \
WHERE a.answer_question = ? \
ORDER BY a.answer_vote DESC',
(question_id, )).fetchall()
conn.close()
if answer is None:
abort(404)
return answer
def get_questionall():
conn = get_db_connection()
questions = conn.execute(
' SELECT q.question_id, q.question_content, q.question_vote, q.is_open, tag_name, IFNULL(a.answer_content, \'No answer\') AS answer_content, IFNULL(a.answer_vote, 0) AS answer_vote \
FROM question AS q \
LEFT JOIN answer AS a on a.answer_question = q.question_id \
LEFT JOIN tag AS t ON t.tag_id = q.question_tag \
WHERE q.is_open = 1 \
GROUP BY q.question_id \
HAVING a.answer_vote = max(a.answer_vote) OR a.answer_vote IS NULL \
ORDER BY q.question_vote DESC').fetchall()
conn.close()
if questions is None:
abort(404)
return questions
def get_question(question_id):
conn = get_db_connection()
question = conn.execute(
' SELECT q.question_id, q.question_content, q.question_vote, t.tag_name, a.answer_content, a.answer_vote, q.is_open \
FROM question AS q \
LEFT JOIN answer AS a on a.answer_question = q.question_id \
LEFT JOIN tag AS t ON t.tag_id = q.question_tag \
WHERE q.question_id = ?',
(question_id, )).fetchone()
# Chú ý thứ tự trong mệnh đề Select ảnh hưởng đến hàm questiondetail
conn.close()
if question is None:
abort(404)
return question
def ins_tags(self):
conn = get_db_connection()
conn.execute('INSERT INTO tag (tag_name) VALUES (?)',(self.tag_name,))
conn.commit()
conn.close()
def del_questions(self, questionid):
conn = get_db_connection()
conn.execute('DELETE FROM question WHERE question_id = ?',
(questionid, ))
conn.commit()
conn.close()
def del_tags(self, tagid):
conn = get_db_connection()
conn.execute('DELETE FROM tag WHERE tag_id = ?', (tagid,))
conn.commit()
conn.close()
def open_questions(self, questionid):
conn = get_db_connection()
conn.execute('UPDATE question SET is_open = ?'
'WHERE question_id = ?', (1, questionid))
conn.commit()
conn.close()
